MySQL-索引(2)

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评估使用索引比全表更慢，则不使用索引。

InnoDB引擎中的索引策略，了解过吗？

厚积薄发ض

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
1
评论
MySQL-索引(2)

再次回答一下联合索引就是为多个列建立的索引,换句话说就是要以这多个列作为排序规则建立一颗B+树.联合索引的含义就是先以第一个列排好序,第一个列相同的情况下,在按照第二个列排序,以此类推.....最左前缀原则这个最左前缀原则专门为联合索引而引出的一个规则(因为联合索引会有多个列一起组成索引),也就是按照最左优先,在检索数据的时候从最左边开始匹配. 如果不符合最左前缀法则就会导致索引失效or索引部分失效最左前缀可以是前M个字符,也可以是前N个字段举一个例子1。
复制链接

扫一扫