为什么要使用索引?
对于一般的系统,读写的比例大概在10:1左右,而且插入操作和更新操作一般很少出现性能问题。容易出现问题的就是我们的复杂查询,一些复杂查询会大大降低我们系统的性能,而索引就是用来解决这个问题的。
什么是索引?
索引在mysql中也叫作一种“键”,是对数据库中一列或多列的值进行排序的一种结构,良好的索引对于性能的影响是至关重要的,尤其当数据量越来越大时,索引对于性能的影响愈发重要!索引的优化是提升查询性能优化最有效的手段,就相当于我们查询字典中的某个字,音序表会帮助我们快速定位到所要查找的字在哪个范围中。
索引的原理
索引的目的在于提高查询效率,和我们查询字典中的某个字是同一个道理,都是先去音序表看第一个字母,然后看第二个,第三个字母。对应数据库就是将数据分块,读取的时候从磁盘中读取出那一块内容
本质就是:通过不断缩小查询范围来筛选出最终想要的结果,由于范围缩小,会将随机事件转变为顺序事件,也就是说,有了索引机制,我们总是用同一种查询方式来锁定数据
对于数据库而言,当数据很多的时候,要将数据像查字典那样进行分类,并且数据库中的数据不是存储在内存中,当我们读取数据的时候,需要从磁盘读取数据到内存,这对性能的影响也是比较大的,因此简单的搜索树是难以满足复杂的情况的
索引的数据结构
MySQL主要用到两种索引结构:B+ Tree索引和Hash索引
B+树的存储结构:
注意:这里的叶子节点应该为双向链表
- 在叶子节点一层,所有记录的主键按照从小到大排列并形成一个双向链表,叶子节点的每个key指向一条记录
- 在非叶子节点一层,每个节点都是一个索引,不存储值,指向下一层最大或者最小的key值
Innodb存储引擎 默认是B+ Tree索引
Memory存储引擎 默认是Hash索引
MySQL中只有Memory存储引擎支持Hash索引,是Memory表(Memory表只存在于内存中,断电即失)的默认索引类型,尽管Memory表也可以使用B+ Tree索引。
Hash由于是以散列的形式存储,所以对于范围查询速度会更慢,对于指定值的查询速度最快
比较:
Hash索引:单条查询快,范围查询慢
B+ Tree索引:b+ 树,层数越多,数据量指数级增长(Innodb默认)
索引的优化
我们都知道MySQL的Innodb存储引擎的使用的是B+树的索引结构,B+树会将索引进行排序,在讲解索引优化之前,先理解什么是最左前缀法则
1、mysql最左前缀法则
最左前缀:在建立复合索引的时候,会先让复合索引最左边的有序,然后让右边的在左边的基础上进行排序,就比如下图:
a的值是有顺序的,b的值是没有顺序的,但是在a的值相同时是有顺序的
总结一下就是最左绝对有序,其他相对有序
如果建立的是复合索引,索引的顺序要按照建立时的顺序,如a --> b --> c (和B+树的数据结构相关),如果查询条件的顺序是按照索引的顺序来查询,那么性能是比较高的,就好像你过桥一样,需要依次经过桥头,桥身,桥尾,其中一个断了都不能顺利经过桥。
如果前面或者中间有一处断了,那么查询效率就变低了
比如:
select name from t_user where a = 2 and b = 1 ;
select name from t_user where b = 1;
最终原因还是最左前缀法则,由于索引是已经排序好的,从前往后容易定位,而如果不从开头开始,就需要整个遍历数据
2、不要对索引做以下处理
- 计算,如:+、-、*、/、!=、is null、is not null、or
- 函数,如:sum(),round()
- 手动/自动类型转换,如:id = “1”,本来是数字,给写成字符串了
使用这些方式进行查询是不会走索引的
3、索引不要放在范围查询右边
比如复合索引 a -> b,使用查询语句select *from t_user where a > 1 and b = 1;
,这个时候只有a索引有效,b索引失效
原因:
由于索引顺序是a -> b,所以查询出来a大于1的数据后,由于b是无序的(参考最左前缀法则),要查询b等于1是要一个一个查找的
4、减少select * 的使用
使用select * 进行查询效率会比较慢,原因如下:
- 比如我们只需要查询几个字段的数据,但是使用selecet *会查询出一些用不到的数据
- 使用*无法利用索引类型的单表查询
5、like模糊查询
比如语句:select * from t_user where name like "zhangsan%;"
或者select * from t_user where name like "%zhangsan";
当%位于右边,查询的是以zhangsan开头的数据,对于索引来说能查询出来,当%位于左边,查询的是以zhangsan结尾的数据,不知道是以什么开头,所以查询的所有的数据