文章目录
前言
以面试题驱动索引的学习:
1.索引底层使用了什么数据结构和算法?
2.为什么 MySQL InnoDB 选择 B+tree 作为索引的数据结构?
3.什么时候适用索引?
4.什么时候不需要创建索引?
5.什么情况下索引会失效?
6.有什么优化索引的方法?
索引介绍
1.什么是索引?
索引是一种用于快速查询和检索数据的数据结构, 这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。
2.优缺点
-
优点:使用索引可以大大加快 数据的检索速度(大大减少检索的数据量)。通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。
-
缺点:创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候,如果数据有索引,那么索引也需要动态的修改,会降低 SQL 执行效率。索引需要使用物理文件存储,也会耗费一定空间。
3.什么时候需要 / 不需要索引?
正如上面讲的索引的优缺点,可以看出,索引并不是万能的,也有自己的适用范围。所以再详细说一下索引的使用场景。
什么时候适用索引?
(1)一般来说,如果一个字段具有唯一性约束(主键),那么索引会有很大好处。
(2)如果要用where对某些字段进行条件查询,那么为这个字段创建索引后会增加查询效率
(3)经常用于 GROUP BY 和 ORDER BY 的字段(B+Tree是排好序的)
什么时候不需要创建索引?
(1)数据量比较少,可以不创建索引
(2)where,group by 等查询条件用不到的字段
(3)字段中存在大量重复数据,不需要创建索引,比如性别字段,只有男女
(4)经常更新的字段不用创建索引,因为索引字段频繁修改,就需要频繁的重建索引
4.语法
(1)创建索引:
CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name ( index_col_name,... ) ;
(2)查看索引
SHOW INDEX FROM table_name ;
(3)删除索引
DROP INDEX index_name ON table_name ;
索引底层结构
MySQL的索引是在存储引擎层实现的,不同的存储引擎有不同的索引结构,常用的有以下几种:
B+Tree 索引、HASH 索引、Full-Text 索引。
InnoDB 是在 MySQL 5.5 之后成为默认的 MySQL 存储引擎,B+Tree 索引类型也是 MySQL 存储引擎采用最多的索引类型。
1.Hash表
哈希索引就是采用一定的hash算法,将键值换算成新的hash值,映射到对应的槽位上,然后存储在hash表中
缺点:只能等值查询(很快),不能范围查询
2.B+Tree
说到为什么要用B+Tree作为索引结构,那演变顺序(选择顺序)应该是:二叉树——二叉搜索树——平衡二叉树——红黑树——B-Tree——B+Tree,详见这篇文章
简单说一下前面几个的缺点:
二叉树:插入没有顺序,查找起来性能肯定很差
二叉搜索树:虽然插入有顺序了,但如果是顺序插入,会形成一个链表,查询性能降低
平衡二叉树&红黑树:都可以自平衡,但是当节点增多时,树的深度会变大
总结一下:其实上面几种都没能解决随着节点增多树的高度变高(导致IO查询次数增多),所以直接想到多叉树——B-Tree & B+Tree,即多路平衡二叉树
B-Tree
B+Tree
可以看出B-Tree和B+Tree的区别:
- B 树的所有节点既存放键(key) 也存放 数据(data),而 B+树只有叶子节点存放 key 和 data,其他内节点只存放 key。
- B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
Mysql对B+Tree做了一点改进,,增加一个指向相邻叶子节点的链表指针,就形成了带有顺序指针的B+Tree,提高区间访问的性能,利于排序。
索引分类
在创建表时,InnoDB 存储引擎会根据不同的场景选择不同的列作为索引:
1.按字段特性
主要分为:主键索引、唯一索引、常规索引、全文索引
2.按物理存储
分为聚簇索引(主键索引)、二级索引(辅助索引)
聚集索引选取规则:
- 如果存在主键,主键索引就是聚集索引。
- 如果不存在主键,将使用第一个唯一(UNIQUE)索引作为聚集索引。
- 如果表没有主键,或没有合适的唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
聚集索引和二级索引如下图所示:
两者的区别是:
- 聚集索引的叶子节点下挂的是这一行的数据 。
- 二级索引的叶子节点下挂的是该字段值对应的主键值
所以引入下面的回表查询
select * from user where name = 'Arm'
主要看这个语句,它的查询条件不是聚集索引,但是又要查询全部字段。所以此时他需要先通过二级索引查询到主键,再通过主键回表查询。
(其实这里也引出了后面的索引优化,怎么查询性能才比较高)
3.按字段个数
单列索引 & 联合索引
索引优化
1.SQL性能分析
一般来说,对于一个数据库表,查询是远高于增删改的,所以我们在说Mysql的性能分析(优化)时,关注的都是对查询的优化。
(1)profile命令
show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了
首先查看mysql是否支持profile(输出yes即支持)
SELECT @@have_profiling ;
默认情况是关闭的,通过以下命令开启
SET profiling = 1;
然后就可以执行一系列的sql语句了,例如:
select * from tb_user;
select * from tb_user where id = 1;
select * from tb_user where name = 'Arm';
select count(*) from tb_sku;
接下来就可以通过命令查看了
-- 查看每一条SQL的耗时基本情况
show profiles;
-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
(2)explain 命令(很重要!)
获取 MySQL 如何执行 SELECT 语句的信息,包括在 SELECT 语句执行过程中表如何连接和连接的顺序
命令如下(直接在查询语句前面加explain)
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;
常见字段含义:
字段 | 含义 |
---|---|
id | select查询的序列号,表示查询中执行select子句或者是操作表的顺序 |
type | 表示连接类型,性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、 index、all |
key | 实际使用的索引 |
select_type | 表示 SELECT 的类型,常见的取值有 SIMPLE(简单表,即不使用表连接或者子查询)、PRIMARY(主查询,即外层的查询)等 |
key_len | 表示索引中使用的字节数, 该值为索引字段最大可能长度,并非实际使用长度,在不损失精确性的前提下, 长度越短越好 。 |
type字段很重要,再说一下它的类型:
type 字段就是描述了找到所需数据时使用的扫描方式是什么,常见扫描效率从高到低为(记住全表扫描性能肯定最差):
- const(结果只有一条的主键或唯一索引扫描)。
- eq_ref(唯一索引扫描);
- ref(非唯一索引扫描);
- range(索引范围扫描);
- index(全索引扫描);
- All(全表扫描);
2.索引失效
有多种情况索引会失效(注意这节判断sql语句是否索引失效的方法就是通过上面的explain语句
)
(1)索引列进行运算
常见的函数运算如count
等会导致索引失效
解释:因为索引保存的是索引字段的原始值,而不是经过函数计算后的值
(2)对索引隐式类型转换
比如字符串类型字段使用时,不加引号(识别成整型),索引将失效(反过来不会失效)
解释:指路MySQL 的数据类型转换规则
(3)模糊查询
如果仅仅是尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引失效。
例如:
// 走索引
select * from t_user where name like '张%';
// 不走索引
select * from t_user where name like '%张';
解释:其实很好理解,因为索引 B+ 树是按照「索引值」有序排列存储的,只能根据前缀进行比较。如果是后缀的话只能走全表扫描了
(4)or连接
用or分割开的条件, 如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到。
(5)联合索引 非最左匹配
如果索引是 联合索引,要遵守最左前缀法则。最左前缀法则 指的是查询从索引的最左列开始,
并且不跳过索引中的列。如果跳跃某一列,索引将会部分失效(后面的字段索引失效)。
注意:最左前缀法则中指的最左边的列,是指在查询时,联合索引的最左边的字段(即是第一个字段)必须存在。但与我们编写SQL时,条件编写的先后顺序无关
解释:在联合索引的情况下,数据是按照索引第一列排序,第一列数据相同时才会按照第二列排序
3.常见索引优化方法
(1)覆盖索引优化
覆盖索引是指 查询使用了索引,并且需要返回的列,在该索引中已经全部能够找到 ——避免了回表查询
举个例子:
比如对一个tb_user表中pro,age,sta字段进行联合索引
当执行
select * from tb_user where profession = '软件工程' and age = 31 and status = '0'
此时会导致回表查询
但是如果执行
select age,status from tb_user where profession = '软件工程' and age = 31 and status = '0'
则直接走二级索引,直接返回数据了
(2)前缀索引优化
当字段类型为字符串(varchar,text,longtext等)时,有时候需要索引很长的字符串,这会让索引变得很大,查询时, 影响查询效率。此时可以只将字符串的一部分前缀,建立索引,这样可以大大节约索引空间,从而提高索引效率
语法:
create index idx_xxxx on table_name(column(n)) ;
(3) 索引最好为Not Null
文章评论