本文主要讲解MySQL-索引相关的知识点
- 联合索引
- 前缀索引
- 覆盖索引
- 索引下推
- 索引的优缺点
- 什么时候适合创建索引,什么时候不适合?
- 如何优化索引 ?
- 索引失效场景 ?
- 为什么SQL语句使用了索引,却还是慢查询 ?
- 使用索引有哪些注意事项 ?
- InnoDB引擎中的索引策略
目录
联合索引是什么?为什么需要注意联合索引中的顺序?什么是最左前缀原则?
联合索引范围查询,什么时候索引会失效? 什么时候不会失效 ?
既然索引有那么多优点,为什么不对表总的每一列创建一个索引呢?(索引是创建的越多越好么 ? )
联合索引
为多个列建立的索引叫做联合索引(多列索引).
为多个列建立索引也就是要以这几个列作为排序规则.
举一个例子吧 比如以c1,c2,c3这三个列建立索引.那就是要以c1,c2,c3作为排序规则建立一颗B+树
- 先按照c1列进行排序
- c1列相同,再按照c2列进行排序
- c2列相同的情况下,再按照c3列进行排序
千万要记住联合索引的含义,这是分析联合索引失效or部分实现的关键思路.
联合索引是什么?为什么需要注意联合索引中的顺序?什么是最左前缀原则?
再次回答一下
联合索引就是为多个列建立的索引,换句话说就是要以这多个列作为排序规则建立一颗B+树.
联合索引的含义就是先以第一个列排好序,第一个列相同的情况下,在按照第二个列排序,以此类推.....
最左前缀原则
这个最左前缀原则专门为联合索引而引出的一个规则(因为联合索引会有多个列一起组成索引),也就是按照最左优先,在检索数据的时候从最左边开始匹配. 如果不符合最左前缀法则就会导致索引失效or索引部分失效
最左前缀可以是前M个字符,也可以是前N个字段
举一个例子1 ,假设有一张表table,字段为col1,col2,col3,有一个为col1,col2,col3建立的联合索引key_part
Q1 : select * from table where col1 = 1 and col2 = 2 and col3 = 3 ;
Q2 : select * from table where col1 = 1 and col2 = 2;
Q3 : select * from table where col1 = 1;
这三个SQL语句都能够利用到索引,只不过Q2,Q3是索引部分失效而已(Q1:所有列都利用到了索引Q2:col3没有利用到索引,Q3Lcol2,col3没有利用到索引).
为什么这三个语句都能利用到索引呢 ?
答案是因为他们都符合最左前缀原则,符合联合索引的顺序规则. 第一个列相同,在按照第二个列排序.....依次类推.
但是还有一点注意的就是 : 因为有查询优化器的存在 col1,col2,col3的顺序不重要,因为查询优化器会进行成本的计算看到底使用怎样的顺序查询最快.就会选择怎样的顺序.
举一个例子2 ,假设有一张表table,字段为col1,col2,col3,有一个为col1,col2,col3建立的联合索引key_part
Q1 : select * from table where col2 = 2 and col3 = 3;
Q2 : select * from table where col2 = 2;
Q3 : select * from table where col3 = 3;
这三个语句都没有利用上索引,因为他们都不符合最左前缀法则.
因为联合索引是先按照col1排序,col1相同在按照col2排序,.... 上面的都没有最左前缀,所以都不会利用上索引.
联合索引范围查询,什么时候索引会失效? 什么时候不会失效 ?
假设创建一个联合索引a,b
-- 创建联合索引 key_part
create index key_part on unionIndex1(a,b);
- > or <
当我们使用下面这条语句查询的时候, 有没有使用到联合索引呢 ? 字段都用上索引了么?
select * from unionIndex1 where a>1 and b=2;
因为有联合索引key_part(a,b),所以会先按照a字段排序,a字段相同了在按照b字段排序.
对于这条SQL,满足a>1条件,a字段都是排好序的,但是满足a>1条件,b字段1并不一定是排好序的(因为只有a字段相同的情况下,b字段才是排序的).
综上所述, a字段用上了索引,b字段没有用上索引.
扫描过程 :
找到第一个符合a>1的记录,然后沿着链表往后扫描,直到不符合a>1这一条件.
并且得出一个结论 : 在联合索引中的最左前缀规则中,遇到范围查询(<, > ),就会停止匹配,进行范围查询的字段可以使用上联合索引,范围查询字段的后面字段不能用上索引-->索引部分失效.
- >= or <=
select * from unionIndex1 where a>=1 and b=2;
对于a>=1的条件下,b字段是无序的,但是在a=1的时候,b字段是有序的,因为联合索引(a,b)是先按照a字段进行排序,a字段相同在按照b字段进行排序.
所以a,b两个字段都能利用上索引.可以通过b字段来减少扫描记录的数量.
- between....and
select * from unionIndex1 where a between 1 and 6 and b = 2;
这里使用between....and.... 在MySQL中between 1and 6 值在 [1,6]区间内取出数字,也就是 1<=a<=6,搜索过程就是定位到第一条a=1并且b=2记录, 然后开始向后扫描直到不符合区间[1,6];
虽然a字段[1,6]之间整体是无序的,但是在a字段相同的情况下,就会按照b字段进行排序,所以a,b字段都会使用到索引.
between.....and 不会导致索引失效.
- like
select * from unionIndex1 where a like '张%' and b = 9;
虽然以张为前缀的a字段不一定是有序的,但是当前缀相同的时候,就会按照b字段进行排序
所以会定位到a字段以张为前缀 并且 b=9的第一条记录,然后沿着链表向后扫描.
a,b字段都会使用到联合索引
总结联合索引范围失效情况
对于联合索引的最左前缀规则, 当对字段进行使用 > , < 范围查询的时候,会停止匹配.联合索引范围查询字段可以使用到索引,联合索引范围查询字段之后的字段,索引就会失效.
但是 对于联合索引字段使用 >= , <= , between....and , like模糊匹配的时候 ,不会停止匹配,联合索引的字段都会使用到联合索引.
联合索引相关面试题(给你一个SQL,判断索引是否失效?)
前缀索引
前缀索引,就是为某一个字符串中的几个字符建立索引.
一般来说适用于比较大的字符串为其前几个字符建立前缀索引.
由于大字符串,非常占用索引空间,提取前几个字符建立索引,这样可以大大减少索引项的大小,同时也会增加索引页中索引项的值.
前缀索引还有一些注意事项 :
- 前缀索引不能用于排序(order by)
因为排序(order by)需要扫描整个索引,而前缀索引只有前缀是排好序的,而忽略了未包含在索引上的数据,所以可能会导致错误的排序结果. 因此如果想要使用order by操作的话就需要建立完整的索引
- 无法把前缀索引变为覆盖索引
同理,因为前缀索引只包含字段的某个前缀,忽略了未包含在索引上的数据.对于覆盖索引,它是要求要覆盖整个字段的.
覆盖索引
覆盖索引可以对二级索引进行优化,省去了回表的时间.
因为我们知道对于一般的二级索引来说,查询某一条记录都是先在二级索引的B+Tree中查询,搜索到叶子结点,拿着主键值再去主键索引的B+Tree中查找完整的用户记录,这一过程就叫做回表.
而当我们查询列表中的字段都能在二级索引中找到的话,那就不需要再去主键索引的B+Tree查找用户记录,直接返回给客户端即可.这就是覆盖索引,能够省去回表的操作.
综上 : 当查询列表中的值都能够在二级索引中找到,不需要回表 即 覆盖索引.
可能还是有些抽象 :
比如我创建一个二级索引index(a,b)
select a,b from table where a = 10 and b=20;
建立了二级索引,二级索引的叶子结点就包括索引列(a,b),还有主键,当查找到这一条记录之后,不需要再去回表.直接把这一条记录返回给客户端.
索引我们尽量不要在查询列表中写 * ,尽量还是写索引用的到的/条件用的到的字段
索引下推(针对联合索引)
所谓索引下推,是将server层做的事情下推给存储引擎层去处理.能够减少二级索引在查询时的回表次数
索引下推是MySQL5.6以后优化方法.
举一个例子把 :
比如我现在创建了联合索引(a,b)
查询语句 : select * from table where a > 2 and b = 6;
很显然只有a字段才能用的上索引.因为联合索引遇到 > , < 就会停止匹配.
在没有使用索引下推优化的情况 :
- server层会先调用存储引擎接口定位满足查询条件的第一条记录(a>2的第一条记录)
- 存储引擎会根据二级索引的B+树,找到对应的主键值,进行回表,查找到完整的用户记录返回给server层
- server层再去判断b字段符不符合条件,如果符合返回给客户端,否则跳过当前记录
- 接着server继续向存储引擎要下一调记录,再去根据二级索引查到主键,回表.....如此往复,直到存储引擎把表中的记录全部读取完毕
在使用索引下推优化之后
- server层会先调用存储引擎接口定位满足查询条件的第一条记录(a>2的第一条记录)
- 存储引擎会根据二级索引的B+树,搜索到叶子结点先不进行回表操作,先判断该索引的其他列(b)符不符合条件,如果不符合条件,跳过该记录,如果符合条件进行回表操作,将完整用户记录返回给server
- server再去查看其他条件是否符合,如果符合返回给客户端,否则向存储引擎要下一条记录,如此往复.直到存储引擎把表中的记录全部读取完毕
总结 :
- 使用联合索引,在联合索引的遍历过程中,每次都需要回表,在回表之后还要再去判断其他条件.
- 如果在MySQL5.6以后就会采用索引下推优化,在联合索引的过程中,对联合索引的字段先做判断,只有联合索引中的字段都符合条件再去回表 , 索引下推将过滤条件下推到存储引擎层面进行执行,减少了回表次数,减少磁盘IO,也就提升了查询性能
索引区分(针对联合索引)
实际开发工作中建立联合索引时,要把区分度大的字段排在前面,这样区分度大的字段越有可能被更多的 SQL 使用到。
区分度就是某个字段 column 不同值的个数 / 表的总行数
比如,性别的区分度就很小,不适合建立索引或不适合排在联合索引列的靠前的位置,而 UUID 这类字段就比较适合做索引或排在联合索引列的靠前的位置。
索引的优缺点
索引的优点
- 索引大大减少了服务器需要扫描的数据量 ,提高查询速度
- 索引可以帮助服务器避免排序和临时表
如果需要对结果进行排序,如果有索引的话,会直接使用索引中的排序结果(因为索引中记录会按照主键进行排序) , 而不需要在排序,提高查询效率.
- 索引可以将随机IO变为顺序IO,以减少磁盘I/O次数
比如主键索引在进行范围查询的时候,定位到第一条符合条件的记录,直接遍历链表,直到不符合条件为止. 再比如插入的时候,也是按照主键顺序进行插入的,逐个往后追加就可以了,而不是像随机I/O那样很有可能导致页分裂,增加访问磁盘的数量
随机IO就是读写的内容分散在磁盘的不同位置,需要来回查找所以效率低;
顺序IO就是读写的内容集中存储在磁盘的一块,从前到后依次读取,免去了查找的过程,所以效率高。
索引的缺点
- 索引需要占用物理空间(磁盘),数量越大,占用的空间就越大.
- 创建索引和维护索引都需要耗费时间,随着数据量的增大而增大
- 索引会降低表的增删改的效率,每一次对索引进行增删改,都需要动态维护B+树的有序性.
- 对于非常小的表,大部分情况下简单的全表扫描更高效;
使用索引一定能提升效率吗?
索引的缺点
- 索引需要占用物理空间(磁盘),数量越大,占用的空间就越大.
- 创建索引和维护索引都需要耗费时间,随着数据量的增大而增大
- 索引会降低表的增删改的效率,每一次对索引进行增删改,都需要动态维护B+树的有序性.
- 对于非常小的表,大部分情况下简单的全表扫描更高效;
既然索引有那么多优点,为什么不对表总的每一列创建一个索引呢?(索引是创建的越多越好么 ? )
- 当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。
- 索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立簇索引,那么需要的空间就会更大。
- 创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加
索引是最好的解决方案吗?
不是,需要看需求和数据量。
(1)对于非常小的表,大部分情况下简单的全表扫描更高效。
(2)对于中到大型的表,索引就非常有效。
(3)对于特大型的表,建立和使用索引的代价将随之增长
什么时候需要适合创建索引?
- 字段的数值有唯一性限制,如unique、主键..会自动添加索引
- 经常用于where查询条件的字段,这样能够提高整个表的查询速度. 如果查询条件不是一个字段可以建立联合索引
- 经常用于 order by 和 group by的字段,这样在查询的时候就不需要做一次排序了,因为我们都已经知道了建立索引之后在B+Tree中的记录都是排好序的.
- 为某个字符串的前缀建立索引-->前缀索引
什么时候不不适合创建索引?
- where条件,group by条件,order by条件里面用不到的字段不适合创建索引. 因为索引是用来快速定位的,如果起不到定位的字段加了索引是会占用物理空间的.
- 字段中出现大量重复的数据 不适合创建索引. 比如性别男,女.(男女分布均匀,无论搜索哪个值都会得到一般的数据,辨别度比较低)查询优化器发现某一个值在表中数据行的占比很高的情况下,会忽略索引,走全表扫描.
- 表数据太少的时候,不需要创建索引. 因为很有可能还不如全表扫描来的块.索引也需要创建的开销.占据磁盘空间的/
- 频繁更新的字段不用创建索引. 因为索引字段频繁修改,都要维护B+Tree的有序性,那么可能就需要频繁的重建索引,这个过程会影响数据库的性能.
- 不建议用无序的字段作为索引 , 会产生更多的随机IO(磁盘需要多次寻道)
- 不建议定义冗余或重复的索引 ,冗余的索引只会增加维护成本,不会对搜索有什么效果
索引的创建原则/注意事项
- 只为用于搜索(where条件),排序(order by),分组(group by)的列创建索引
也就是只为用到的列创建索引. 没有用到的列不需要创建索引,起不到定位的字段占据索引空间.
- 索引列的类型尽量小
这里说的索引列类型大小是指 类型表示该数据范围的大小.所以要想为某一个整数列建立索引的话,在表示的整数范围允许的情况下,尽量让索引列使用较小的类型. 比如我们能使用int就不适用bigInt
- 数据类型越小,在查询的时候 进行的比较操作 越快
- 数据类型越小,索引占据的存储空间就越少,一个数据页内就可以存放更多的记录,从而减少磁盘I/O带来的性能消耗,意味着可以更多的数据页缓存在内存中,加快读写效率
对于主键来说更加使用,因为不仅是聚簇索引会存储主键值,其他的所有二级索引的节点都会存储一份记录的主键值,如果主键使用更小的数据类型,意味着更加节省存储空间和更高效的I/O.
- 可以只对字符串值的前缀建立索引(前缀索引)
在字符串比较长的情况下,不仅会让索引的存储空间增加,而且在B+Tree索引的索引列存储的字符串越长,比较会占用更多的时间.
所以我们可以针对这种比较长的字符串,取出前几个字符建立索引,这样虽然不能精准的匹配到记录,但是可以找到前缀的位置,然后在回表查找,既节约了空间又节省了查找的时间
- 缺点就是前缀索引不能使用orderby进行排序
- 因为无法对前几个字符相同后面不同字符的记录进行排序
- 尽可能少的让聚簇索引发生页分裂和记录移位的情况,建议让主键拥有Auto_increment属性(主键索引最好是自增的)
- 定位并删除表的重复和冗余索引
冗余索引只会增加维护这个索引的成本,不会对搜索有帮助
- 尽量使用覆盖索引进行查询,避免回表带来的性能消耗 (覆盖索引)
- 索引最好设置为not null
- 存在NULL的列会使得优化器在做索引选择的时候更加复杂,有NULL的列使得索引,索引统计和比较值都更加复杂, 比如 使用count的时候需要忽略NULL的行
- 并且存在NULL的列还会占用存储空间,因为记录要用额外的1字节去存储NULL值列表
- 防止索引失效
有什么索引优化的方法么
- 前缀索引优化;
- 覆盖索引优化;
- 主键索引最好是自增的;
-
索引最好设置为 NOT NULL
-
防止索引失效;
为什么主键索引最好是自增的
- 当我们使用自增主键的时候 : 每次插入数据都会被顺序添加到索引中,每次插入一条新记录都是追加操作,当页面满的时候,就会自动开辟一个新的页面继续插入,不需要重新移动数据. 插入效率非常高
- 当我们使用非自增主键(比如UUID,随机ID...) : 由于插入的id没有规律,所以InnoDB就无法做到总是把新数据插入到索引的最后,就需要为新数据寻找合适的位置从而来分配新的空间.会导致以下问题 :
- 写入的目标页(新纪录要写入的页)很可能已经刷新到磁盘上并且从缓存中移除 或者 还没有被加载到缓存中, InnoDB在插入之前不得不先找到并从磁盘读取目标页到内存中,这将导致大量的随机IO
- 因为写入是乱序的,InnoDB不得不频繁的做页分裂操作,以便为新的数据分配空间,页分裂导致移动大量的数据 -- > 可能导致造成大量的内存碎片,导致索引结构不紧凑,从而影响查询效率
- 页分裂 : 比如要将记录插入到数据页中间的某个位置,就必须移动其他新的数据来满足新的数据插入,甚至可能需要把数据从一个页面复制到另外的一个页面 ---> 可能导致造成大量的内存碎片,导致索引结构不紧凑,从而影响查询效率
所以 : 使用innodb应该尽可能的按主键的自增顺序插入,并且尽可能使用单调的增加的聚簇键的值来插入新行
索引失效的场景
对索引使用函数
使用相关函数也是不走索引的
为什么对索引使用函数,就无法走索引了呢?
因为索引保存的是索引字段的原始值,而不是经过函数计算后的值,所以就没有办法走索引.
但MySQL8.0开始,索引特性增加了函数索引.可以针对函数计算后得值建立一个索引.索引记录的是计算后的值,所以就可以通过扫描索引来查询数据.
索引列运算
不要在索引列上进行运算操作, 索引将失效
为什么对索引进行表达式计算,就无法走索引了呢?
因为索引保存的是索引字段的原始值,而不是表达式计算后的值. 只能通过把索引字段的取值都取出来,然后依次进行表达式的计算来进行条件判断. 因此采用的就是全表扫描.
对索引隐式类型转换
如果索引字段是字符串类型,但是在条件查询中,输入的参数是整形的话,也就是字符串没有加引号,那么索引失效.
explain select * from t_user where id = '1';
为什么第一个例子会导致索引失效,而第二例子不会呢?
MySQL 的数据类型转换规则 :
- MySQL 在遇到字符串和数字比较的时候,会自动把字符串转为数字,然后再进行比较。
为什么字符串去掉引号就索引失效了呢 ?
因为 MySQL 在遇到字符串和数字比较的时候,会自动把字符串转为数字,然后再进行比较。
select * from tb_user where phone =138456963336; -- phone是字符串
-- 如果遇到字符串与数字进行比较那么,字符串会转换为数字,就会变成如下这样
select * from tb_user where CAST(phone AS signed int) = 138456963336;
CAST函数是作用在phone字段,phone是索引,使用了函数,索引会索引失效.所以会走全表扫描
-- 而对于下面这个语句,索引字符安没有是用人和函数,CAST函数只是用在了输入参数.所以走索引扫描
select * from tb_user where id = "1";
-- -->会转换为下面这条语句
select * from tb_uuser where id = CAST("1" AS signed int);
总结一下 : 当字符串与数字进行比较的时候,MySQL会自动的将字符串转成数字,也就是会自动发生隐式类型转换,导致使用了函数,导致索引失效,走全表扫描
对索引使用左或者左右模糊匹配
当我们使用左或者左右模糊匹配的时候,也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效;
为什么 like 关键字左或者左右模糊匹配无法走索引呢?
如果使用 name like '%x' 方式来查询,因为查询的结果可能是「陈x、张x、周x」等之类的,所以不知道从哪个索引值开始比较,于是就只能通过全表扫描的方式来查询。
or连接条件
在 WHERE 子句中, 只要有条件列有一个不是索引列,索引会失效,走全表扫描.
这是因为 OR 的含义就是两个只要满足一个即可,因此只有一个条件列是索引列是没有意义的,只要有条件列不是索引列,就会进行全表扫描
解决办法就是给另外一个列加上索引.就会避免索引失效.
联合索引非最左匹配,联合索引范围查询
最左匹配原则
怎么理解最左匹配原则呢 ?
如果索引是多列(联合索引),要遵守最左前缀法则。
- 最左前缀法则指的是查询从索引的最左列开始(最左列必须存在),并且中间不跳过索引中的列.如果跳跃某一列,索引将会部分失效(后面的字段索引失效).在没有遵循最左匹配原则的情况下,是无法利用到索引的。
- 因为有查询优化器,所以最左字段的顺序并不重要
总结一下 : 在使用联合索引查询的时候必须遵循最左前缀法则,也就是联合索引的最左侧字段必须包含,否则联合索引全部失效, 如果中间跳过某个字段,那么后面的字段索引失效->部分失效
查看联合索引有没有失效.
联合索引全部失效情况 :
联合索引部分失效情况 :
索引不会失效的特殊情况 :
顺序改变,不会导致索引失效.与放置的位置无关,因为有查询优化器会根据顺序进行调整让其效率最佳
为什么联合索引不遵循最左匹配原则就会失效?
在联合索引的情况下,数据是按照索引的第一列排序,第一列数据相同的时候才会按照第二列排序.
也就是说,如果我们想使用联合索引中尽可能多的列,查询条件中的各个列必须是联合索引中从左边开始的连续的列.
联合索引范围查询
联合索引中,出现范围查询(>,<),范围查询右侧的列索引失效.
所以,在业务允许的情况下,尽可能的使用类似于 >= 或 <= 这类的范围查询,而避免使用 > 或 <
联合索引的最左匹配原则,在遇到范围查询(如 >、<)的时候,就会停止匹配,也就是范围查询的字段可以用到联合索引,但是在范围查询字段的后面的字段无法用到联合索引。注意,对于 >=、<=、BETWEEN、like 前缀匹配的范围查询,并不会停止匹配,
数据分布影响
如果MySQL评估使用索引比全表更慢,则不使用索引。
就是因为MySQL在查询时,会评估使用索引的效率与走全表扫描的效率,如果走全表扫描更快,则放弃索引,走全表扫描。 因为索引是用来索引少量数据的,如果通过索引查询返回大批量的数据,则还不如走全表扫描来的快,此时索引就会失效.
查询时MySQL会评估,走索引快,还是全表扫描快,如果全表扫描更快,则放弃索引走全表扫描.因此,is null,is not null是否走索引,得具体情况具体分析,并不是固定的
总结索引失效
- 当对索引列使用函数的时候或者对索引列进行表达式运算的时候,会导致索引失效,因为,索引列记录的是原始的数值,而不是计算后的数值.
- 当使用模糊匹配的时候,对左或者对左右模糊查询会导致索引失效,因为不知道根据哪个索引值匹配
- 对索引进行隐式类型转换的时候, 比如字符串不加引号导致索引失效. 由于MySQL在遇到字符串与数字比较的时候,会自动把字符串转为数字在进行比较. 当字符串是索引列的时候,而条件语句中的输入参数是数字的话,那么索引列就会发生隐式类型转换,隐式类型转换是通过CAST函数实现的,等同于使用了函数,导致索引失效.
- 不遵循联合索引最左匹配原则; 当使用联合索引时,要遵循最左匹配原则,按照最左优先的方式进行索引的匹配,否则索引会失效
- 当使用联合索引,在进行范围查询时候,如果使用的是> < 会导致索引后面的索引列失效->部分失效.
- 当使用where子句,使用or连接条件,如果有一个条件列不是索引列,就会导致索引失效.
- 会受到数据分布影响,如果MySQL评估使用索引比全表更慢,则不使用索引。