MySQL索引原理及其优化
文章目录
- MySQL索引原理及其优化
- 一,索引原理
- 1.底层数据结构
- 1.为什么使用B+树实现索引?
- 2.索引在MyISAM和innodb中的实现
- 索引在MyISAM的实现
- 索引在innodb中的实现
- 聚集索引和非聚集索引
- 3.哈希索引
- 4.联合索引的B+树如何组织?
- 二,索引优化
- 1.尽量不要出现重复索引
- 2.减少冗余索引
- 3.where子句后边的列索引只能用上1个
- 4.多列索引必须遵循最左匹配原则
- 5.尽量使用覆盖索引
- 6.前导模糊查询不能使用索引
- 7.union,in,or都能命中索引推荐使用in
- 8.负向条件查询不能使用索引,可以优化为in查询
- 9.建立索引的列,不能为null,联合索引不存全为null的值
- 10.使用索引查询时,避免强制类型转换
MySQL索引原理及其优化
之前有写过一篇关于索引的文章,但是感觉写的不好,因此重新总结一下,当做复习,为明年的春招做准备,欢迎互关呀,共同学习,进步!
一,索引原理
我们之前可能都使用过索引,但是我们有没有了解过索引的底层原理呢?
1.底层数据结构
我们知道,索引帮助MySQL高效获取数据的排好序的数据结构
既然,索引是一种数据结构,我们了解原理,就有必要吃透这种数据结构
在这里我先抛出结论:
MySQL中的索引是在存储引擎实现的,不同存储引擎对索引的实现可能有所不同,常用的MyISAM和innodb都使用B+树实现索引,但是这两者又有些不同。
1.为什么使用B+树实现索引?
索引绝大多数情况都是用在查询操作当中,那么我们列举下我们知道的查找算法
顺序查找:
对于这样一张表,如果我要做这样的查询
select * from tb_stu where stu_name = 'walker'
如果是顺序查找,那么我就得先从第一行数据,一行一行的比对,运气好的,第一行就找到了符合条件的数据行,运气不好,就得遍历到最后一行才找到,所以这样的时间复杂度为O(N),N是数据表的行数,当数据表的数据非常多时,效率就会原来越差,IO次数随着增多
BST二叉搜索树
有人说,二叉搜索树的时间复杂度可达O(logN),但是二叉搜索树有可能会退化为左斜树或者右斜树
这样时间复杂度就会有O(logN)变为O(N)
AVL平衡树
有人会说,使用AVL平衡树可以避免搜索树退化为左斜树或者右斜树,但是这样又会带来一个问题,当数据量比较大时,树的层次会越来越大,这样即使时间复杂度可以达到O(logN),但是IO次数是随着树的高度增加而增加的。
B树
B树能很好的解决查询效率和IO次数多的问题,但是当我做范围查询:
select * from table where class_id > 5
B树这时就不好处理了,因为B树要多次遍历,比如,上边的sql,B树在找到第一个大于5的数据6后,还需要重新从根节点在遍历找第二个大于5的数据,所以这样IO次数也是比较不稳定的。
B+树:
针对上边B树的情况,使用变种的B树——B+树可以解决,B树的所有数据都储存在叶子节点,所有叶子节点组成是一条链表
当我们进行范围查询时,查到叶节点后可以遍历链表,找到我们想要的数据
所以,B+树要比B树具有更稳定的查询效率,因为B+树树的高度相对稳定,3层的B+树就可以存储百万条数据,所以查询次数也相对稳定O(N),N是树的高度,在树的高度不是很高的情况下,且加上叶子层的链表,IO次数相对B树也大大减少。
所以,大多数数据库采用B+树实现索引,但也有用B树实现索引的,例如MongoDB
2.索引在MyISAM和innodb中的实现
以这个表为例,col是主键
索引在MyISAM的实现
MyIsam引擎使用B+树来实现索引,在B+树的叶子节点数据域中存放的mysql表数据记录的地址
在col2上建立一个普通索引,也是一样的存放
所以特点就是:数据和索引是分开存放的, 在进行sql查询时,会根据查询语句中的索引条件,在b+树中找到相应的叶子节点,取出相应的数据地址,然后根据地址获取相关表记录
索引在innodb中的实现
虽然innerDB也使用b+树作为索引结构,但是实现方式和myisam不同,在主键索引中,b+树叶子节点数据域中存储的是数据库表相关列的完整记录
在其他索引中,比如col3建立了一个普通索引,存放的是该列对应的主键值
这样我们在利用col3查数据时,就会先找出对应的主键值,然后在从主键索引中找出对应的行数据,这种也叫作回表查询, 先定位主键值,再定位行记录,它的性能较扫一遍索引树更低。
所以说,innodb实现的主键索引叶节点保存了完整数据,非主键索引叶节点保存主键值
在innodb中实现索引为还是那么必须有主键?
- 因为innodb中是通过主键索引去组织数据,如果没有主键索引,MySQL会自动找出能唯一标识行数据的一列作为主键列,如果没有则会生成一个默认的隐藏列作为主键,去维护叶数据
为什么尽量使用整形自增主键?
- 比如,我现在不使用整形自增主键,我使用uuid作为主键,也能起到主键唯一标识的作用,但是uuid是字符串,字符串在比较上比整形要麻烦,所以还是推荐使用整形主键
- 对于为什么推荐自增,因为,自增的主键每次都比原来B+树中的主键大,所以,在新增数据时都会将节点添加到最后一个叶子节点,而减少了B+树维护平衡的消耗
聚集索引和非聚集索引
上边提到的
- 在innodb上实现的索引也被称为聚集索引
- 在MyISAM中实现的索引被称为非聚集索引
对比:
- 聚集索引在进行主索引查询时效率很高,直接找到相关叶子节点就可以找到相关数据记录,非聚集索引还需要根据数据地址去寻找数据记录
- 但是聚集索引在辅助索引查询时效率低非聚集索引低,因为需要检索两次b+树,第一次找到主键值,第二次根据主键值找到相关记录、
3.哈希索引
只有memory(内存)存储引擎支持哈希索引,哈希索引用索引列的值计算该值的hashCode,然后在hashCode相应的位置存执该值所在行数据的物理位置,因为使用散列算法,因此访问速度非常快,但是一个值只能对应一个hashCode,而且是散列的分布方式,因此哈希索引不支持范围查找和排序的功能,但是hash适用于 = 或者 in的等值查询
4.联合索引的B+树如何组织?
如果是联合索引的话,一个联合索引还是维护一棵B+树,但是这个B+树每一个节点是多个列的,意思就是说,原来独立索引,一个节点一个列嘛,现在联合索引节点是多列的,列的顺序按照联合索引顺序排,比如联合索引key(a,b),会先检索a,然后在检索b
二,索引优化
1.尽量不要出现重复索引
第一步
尽量不要出现重复索引,何为重复索引?重复索引是指相同的列以相同的顺序建立的同类的索引,比如主键和唯一索引
create table test(
id int not null primay key,
name varchar(10) not null,
title varchar(50) not null,
unique(id)
)engine = innodb
就像这里边的id既是唯一索引也是主键,就是重复索引.
2.减少冗余索引
第二:
减少冗余索引,什么是冗余索引就是指多个索引的前缀列相同,或是在联合索引中包含了主键索引
create table test(
id int not null primay key,
name varchar(10) not null,
title varchar(50) not null,
key(name,id)
)engine = innodb
上边就是在联合索引中包含了主键索引,导致索引冗余,因为innodb的特性,innodb会在没个索引的后边加上主键索引,
还有前缀列相同就是下边说到的联合索引的情况,
建立了联合索引(a,b,c)就没必要建立一个独立索引a
3.where子句后边的列索引只能用上1个
第三:
注意where子句后边的列不能都加上索引
select * from table where age = 1 and price > 100
where子句后边的age和price如果两个都是独立的索引,那么同时只能用上1个,此时应该建立age和price的联合索引
4.多列索引必须遵循最左匹配原则
第四:
多列索引必须遵循最左匹配原则,索引顺序应该遵循离散度大的列放的越前
先说最左匹配原则
所谓最左匹配原则,就是假如我创建了联合索引index(a,b,c)
如果,我在查询中:
where a = 1 and b = 1 and c = 1 #索引中abc三列都走
where a = 1 and b = 1 #索引中ab两列走
where a = 1 and c = 1 #索引中ac两列走
所以从上面可以看出,最左匹配中的一个要求:索引中排第一的列必须出现,索引才会生效,比如上边是索引生效的组合可以有:
abc
ab
ac
a
所以不生效的如:
bc
b
c
上面都是等值查询,下边涉及到范围查询
1. where a = 1 and b = 1 and c > 1
2. where a = 1 and b > 1 and c = 1
上边的where子句中只有第一句会走索引,走索引中的abc列,第二句不走列c,这涉及到多列索引中第二个要求:当在查询中出现范围查询时, 存储引擎不能使用索引中范围条件右边的列
还有,建立了联合索引(a,b,c)就没必要建立独立索引(a)
如果有order by或者group by的情景,也要注意索引的有序性
比如:
where a = ? and b = ? order by c
这样可以建立key(a,b,c)的联合索引,order by 最后的字段是组合索引的一部分且放在组合索引最后,避免出现文件排序fileSort
5.尽量使用覆盖索引
第五:
**尽量使用覆盖索引(只访问索引的查询(索引列包含的查询列)减少select ***
比如在登录验证中:
select user_time from user where user_name = ? and password = ?
可以建立key(user_name,password,user_time)的联合索引
6.前导模糊查询不能使用索引
第六:
前导模糊查询不能使用索引
select * from table where title like '%ax'
该语句属于前导模糊查询,即使title是索引,也不能使用到
非前导模糊查询可以使用索引
select * table from title like 'abc%'
7.union,in,or都能命中索引推荐使用in
第七:
union,in,or都能命中索引,但是cpu耗费上,union < in < or
所以一般推荐使用in
比如:
select * from table where a = 1
union
select * from table where a = 2
直接告诉mysql怎么做,cpu耗费最少,但是一般推荐使用in
select * from table where a in (1,2)
or的话,cpu耗费最大,不建议
select * from table where a = 1 or a = 2
8.负向条件查询不能使用索引,可以优化为in查询
第八:
负向条件查询不能使用索引,可以优化为in查询,负向条件有:!=,< >,not in , not exists , not like
9.建立索引的列,不能为null,联合索引不存全为null的值
第九:
建立索引的列,不能为null,联合索引不存全为null的值
10.使用索引查询时,避免强制类型转换
第十:
在使用索引查询时,避免强制类型转换,强制类型转换会导致全表扫描,例如,phone本来是varchar类型
select * from table where phone = 123456789
这样就不能命中索引.
应改为:
select * from table where phone = '123456789'
还没有评论,来说两句吧...