前言数据库索引是MySQL核心功能之一如果数据库没有索引机制面对海量的数据进行检索效率是绝对无法接收的毕竟没有索引机制的数据库就跟普通文本文件存储在磁盘中一样在MySQL索引初始篇中讲解了数据库索引机制创建、使用、分类、管理、删除等操作那么我们就以上篇文章为基础对数据库索引机制进一步深入学习虽然通过上篇文章对数据库索引机制有了一个全面的认知但是还缺乏一些经验和方法论才能运用到实际的业务项目中仅根据上一篇文章也还有一些关于索引的问题不能解答索引虽然给MySQL检索数据的效率带来了提升但加入索引没有带来新的问题吗既然索引能提升查询性能那是不是为表中的每个字段建立索引性能会更好呢一张数据表中哪些类型的字段不适合建立索引呢什么原因不适合建立索引呢表中会存在大量的字段但其中哪些字段建立索引才能得到最大性能收益呢MySQL提供的索引种类也不少一个字段上建立什么类型的索引才最好呢当表中存在多个索引时一条查询SQL有多条路径可走此时走那条索引最好在接下来这篇文章中中带你讲解索引应用相关的知识例如各索引的优劣之分、建立索引的原则、使用索引的指南以及索引失效与索引优化等内容MySQL各索引的优劣分析正所谓凡事有利必有弊MySQL的索引机制也是一样的那么引入索引机制给数据库带来的好处在整个数据库中对数据库表的查询速度直线提升如果数据量越大提升效果越明显通过创建唯一索引可以确保数据库表中的数据唯一性不需要额外再去建立唯一约束在使用分组和排序的时候可以显著减少SQL查询的分组和排序时间连表查询时基于主外键字段上建立的索引可以带来十分明显的性能索引默认的是BTree有序结构基于索引字段做范围查询时效率会明显提高从MySQL整体架构而言减少了查询SQL的执行时间提高了数据库整体吞吐量弊端建立索引会生成本地磁盘文件需要额外的磁盘空间来存储索引数据磁盘占用率会变高写入数据时需要额外维护索引结构增删改数据时都需要额外操作索引写入数据时维护索引需要额外的时间开销执行写SQL时效率会降低性能会下降那么整体来说索引带来的优势是大于劣势的但是索引不是越多越好的所以我们要合理建立索引才是最佳选择那么我们接下来就聊一下各类索引的弊端主键索引存在的陷阱在我们数据库的表中主键一般都是使用自增ID但为什么呢很显然就是自增ID不会重复确保了主键唯一性这也没错但是随机的UUID也不会重复那为什么不用UUID呢这就是由于索引存在一个陷阱因为一张表大多数情况下会将主键索引以聚簇索引的形式存在磁盘上上篇文章也说过聚簇索引在存储数据时表数据和索引数据是一起存放的。同时MySQL默认的索引结构是BTree也就代表着索引节点的数据是有序的主键索引是聚簇索引、表数据和索引数据是一块的、索引结构是有序的那么就可以反推为什么不使用UUID呢因为UUID是无序的如果使用UUID作为主键那么每插入一条新的数据都有可能破环原本的树结构如下假如灰色节点就是新插入的数据此时经过计算应该排到第二个位置那就代表着后面的三个节点需要进行移动给灰色节点挪出一个位置存储从而确保索引的有序性那么这个树结构就被破坏了所以使用UUID并不好注意这里知识为逻辑仅仅用于举例演示实际上B树索引结构不长这样在后面的索引原理篇会重新说但是如果我们使用注解ID是有序的就不需要改变树结构所有的新插入的数据都会放到最后面所以我们在创建数据库表的主键的时候最好选用带顺序性的值否则就会掉入上述的陷阱中联合索引存在的矛盾联合索引就是为了在多条件查询时有更高的效率一般会同时对多个字段建立联合索引但之前聊到过联合索引有一个问题就是在查询SQL中必须要包含联合索引的第一个字段否则不会触发联合索引进行检索数据例如假如以id、name、age三个字段作为联合索引进行查询select * from user_table where name JunLa and age 18;显然这条SQL是无法使用联合索引的因为它的查询条件没有包含联合索引的第一个字段想要使用联合索引那么这条查询SQL的查询条件必须包含联合索引的第一个字段select * from user_table where id 1 and name JunLa;这条SQL才能命中多列索引的语句因此在建立索引时需要考虑查询条件这个问题确保建立出来的联合索引命中率够高前缀索引存在的弊端前缀索引就是利用字段的前N个字符去创建索引相比于使用一个完整字段创建索引前缀索引能够更节省存储空间当数据越多时带来的优势越明显虽然前缀索引节省了空间但是因为它的索引节点中未存储一个字段的完整值所以MySQL也无法通过前缀索引来完成order by、 group by等分组排序工作也无法完成覆盖扫描等操作全文索引存在的硬伤在没有使用全文索引的使用我们通常会使用like%模糊查询来进行查询但是随着数据越来越多表越来越多模糊查询的性能会明显下降全文索引就能够完美解决这个问题可以利用全文索引来代替like%模糊查询性能比模糊查询快很多倍但是全文索引也是存在一些问题的由于全文索引是基于分词实现的所以对一个字段建立全文索引后MySQL会对该字段做分词处理这些分词结果也会被存储在全文索引中因此全文索引的文件会额外的大占用的内存会很大由于全文索引对每个字段值都会做分词因此当修改字段值后分词是需要时间的所以修改字段数据后不会立马自动更新全文索引此时需要我们写存储过程并调用它手动更新全文索引中的数据全文索引最大的硬伤就是不支持真类似于英文可以直接通过符号、空格来分词但中文不行无法精确地对一段文字做分词因此全文索引在检索中文时存在些许精度的温度所以如果你的项目规模比较大通常会再引入ElasticSearch、Solr、MeilSearch等搜索引擎唯一索引存在的快慢问题唯一索引的一大好处就是查询数据时会比普通索引效率更高例如走普通索引进行查询假设column_xx字段上建立一个普通索引此时基于这个字段查询数据时当查询到column_xx “xx”的数据后此时会继续走完整个索引树因为可能会存在多条字段值相同的数据效率是比较低的因为要走完整个索引树可能会出现重复的数据select * from table_xx where column_xx xx;但是如果column_xx字段上建立的是唯一索引就确保了数据的唯一性就不需要走完整个索引树不会有重复的数据只要找到一条对应的数据后就会里面停下来检索因此建立唯一索引的字段值就具备唯一性所以在检索数据的时候唯一索引是比普通索引要快的但在插入数据的时候不一样因为唯一索引要确保数据是不重复的所以在插入数据前要检查一遍表中是否存在相同的数据但是普通索引不需要考虑这个问题直接插入就行了哈希索引的致命问题哈希索引就是数据结构为Hash类型的索引哈希索引的查询速度在MySQL中是最快的因为采用哈希结构的索引会以哈希表的形式存储索引字段值当基于该字段查询数据时只需要经过一次哈希计算就可以获取到数据但是哈希结构的致命问题就是无序也就是无法基于哈希索引的字段做排序、分组工作等如果你能确定这个表中不会做排序这类操作的话可以选择哈希结构作为索引的数据结构建立索引的正确姿势经过上面的分析我们知道了每种索引类型存在的缺陷问题那么当我们知道了每种索引存在的缺陷后就能更好地考虑周到通过不同类型的索引进行一个互补的关系设计出更合理的索引在实际项目场景中当SQL查询性能较慢时我们通常会想表中哪个字段建立索引能够带来最大性能收益一般来说是否要在这个字段上添加索引的依据是看这个字段是否被经常用来做查询条件来使用但是仅靠这个规则也不行性别经常查询但是带来的性能提升却不高索引查询时的回表问题什么叫回表呢就是指一条SQL语句在MySQL内部需要经过两次查询过程才能获取到数据这是跟索引机制有关的先来看看索引在MySQL内部真正的面貌参考自竹子爱熊猫在上图用户表中基于自增ID字段先建立一个主键索引然后又基于name字段建立一个普通索引此时MySQL默认会选用主键索引作为聚簇索引将表数据和主键索引存在同一个文件中也就是主键索引的每个索引节点都直接对应着行数据而基于name字段建立的索引其索引节点存放的则时指向聚簇索引的ID值举个例子select * from table_user where name 子竹;首先会走name字段的索引然后找到对应的ID值然后再基于查询到的ID值再走ID字段的主键索引最终得到一整条行数据并返回这个过程SQL经历了两次查询才获取到数据这个过程就叫回表回表会导致额外的查询开销因此尽量可以基于主键做查询如果实在是需要使用非主键字段查询那么尽量写明查询的结果字段而不要使用*建立索引时需要遵守的原则前面说过仅仅考虑一个字段是否被经常查询来判断是否要在这个字段上创建索引是不够的那么下面就看看建立索引时需要遵守的原则经常频繁用作查询条件的字段应酌情考虑为其创建索引因为可能会掉入陷阱表的主外键或连表字段必须建立索引因为这样可以很大程度提升连表查询的性能建立索引的字段一般值的区分性要足够高这样才能提高索引的检索效率建立索引的字段值不应该过长如果较长的字段要建立索引可以选择前缀索引建立联合索引应当遵循最左前缀原则将多个字段之间按优先级顺序组合经常根据范围取值、排序、分组的字段应建立索引因为索引有序能加快排序时间对于唯一索引如果确认不会利用该字段排序那可以将结构改为Hash结构尽量使用联合索引代替单值索引联合索引比多个单值索引查询效率更高建立索引需要注意的一些点值经常会增删改的字段不合适建立索引因为每次改变后需维护索引结构一个字段存在大量的重复值时不适合建立索引比如之前举例的性别字段索引不能参与计算因此经常带函数查询的字段并不适合建立索引一张表中的索引数量并不是越多越好一般控制在3最多不能超过5建立联合索引时一定要考虑优先级查询频率最高的字段应当放首位当表的数据较少不应当建立索引因为数据量不大时维护索引反而开销更大索引的字段值无序时不推荐建立索引因为会造成页分裂尤其是主键索引联合索引的最左前缀原则有一条原则尽量使用联合搜友代替单值索引联合索引比多个单值索引查询效率更高举个例子假如现在基于X,Y,Z这三个字段建立一个联合索引实际上也相当于建立三个索引X,X,Y,X,Y,Z因此只要查询中使用了这三组字段都可以触发联合索引都可以使联合索引生效联合索引的最左前缀原则很简单就是组成联合索引的多个列越靠左优先级越高同时也只有SQL查询条件中包含了最左的字段才能使用联合索引例如# 基于上面创建的X、Y、Z联合索引进行查询 select * from tb where Y .... and Z ...;上面这条SQL显然不会使用联合索引因为不符合最左前缀原则最左侧的字段是X 没有被使用。也正是因为MySQL在使用联合索引的时候会遵循最左前缀原则所以才会在前面建立索引中提出一条建议建立联合索引时一定要考虑优先级查询频率最高的字段应当放首位因为将查询频率越高的字段放首位意味着查询时命中索引的几率越大。同时MySQL的最左前缀原则在匹配到范围查询时会停止匹配例如、、between、like这类范围查询条件并不会继续使用联合索引 例如select * from tb where X ... and Y ... and Z ....;当执行时虽然上述SQL使用到X、Y、Z作为查询条件但由于Y字段是范围查询因此这里只能使用X索引而不能完全匹配使用X、Y、Z索引。对于X、Y索引呢严格意义上来说Y字段的范围查询也会基于索引来完成所以这里其实能用到X和X、Y字段的索引也正是因为基于Y字段做了范围查询从而阻断了Z字段基于索引匹配再举个例子select * from user where name JunLa select * from user where name JunLa and age 18; A. create index index_name on user(name); create index index_name on user(age); B. create index index_name on user(name,age);对于这两条SQL选第一种方式创建索引还是选第二种呢?选B因为两条SQL完全能够利用到第二个创建的联合索引select * from user where name JunLa and age 18; select * from user where age 18 and name JunLa;同时选B建立联合索引后如上两条SQL都会利用到上面创建的联合索引SQL是否走索引查询跟where后的条件顺序无关因为MySQL优化器会优化对SQL查询条件进行重排序。索引失效与使用索引的正确姿势这里指的是我们再编写SQL时需要注意的点毕竟MySQL查询时到底使不使用索引这完全取决于你编写的SQL我们在编写SQL的时候不仅仅要追求实现业务功能还要考虑这条SQL应用到索引中而且索引是一把双刃剑既然我们创建了索引就要好好利用它索引失效想要用好索引就必须知道索引在哪些情况下会失效知道了之后我们在写SQL的时候就会可以去避开这些问题那么我们写出来的SQL就会十有八九是会用到索引的我们下面就聊一下数据库中哪些情况下会导致索引失效举个案例创建两个索引user_id主键索引user_name、user_sex、password组成联合索引如果想要查看一条查询SQL是否使用了索引就要用到一个自带的分析工具Explain下面来简单介绍一下SELECT * FROM users_table; ------------------------------------------------------------- | user_id | user_name | user_sex | password | register_time | ------------------------------------------------------------- | 1 | aa | 女 | 6666 | 2022-08-14 15:22:01 | | 2 | bb | 男 | 1234 | 2022-09-14 16:17:44 | | 3 | cc | 男 | 4321 | 2022-09-16 07:42:21 | ------------------------------------------------------------- ALTER TABLE users_table ADD PRIMARY KEY p_user_id(user_id); ALTER TABLE users_table ADD KEY unite_index(user_name,user_sex,password); 执行分析工具-ExPlain这里先对ExPlain工具做一个简单的介绍后续在SQL优化篇中会详细讲解这个工具的作用当在一天SQL前加上explain命令然后执行这条SQL后就会列出所有的执行方案EXPLAIN SELECT * FROM zz_users; ---------------------------------------------------------------------------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ---------------------------------------------------------------------------------- | 1 | SIMPLE | zz_users | ALL | NULL | NULL | NULL | NULL | 3 | | ---------------------------------------------------------------------------------- id这是执行计划的ID值这个值越大表示执行的优先级越高。select_type当前查询语句的类型有如下几个值simple简单查询primary复杂查询的外层查询subquery包含在查询语句中的子查询derived包含在FROM中的子查询table表示当前这个执行计划是基于那张表执行的type当前执行计划查询的类型有几种情况all表示走了全表查询未命中索引或索引失效system表示要查询的表中仅有一条数据const表示当前SQL语句的查询条件中可以命中索引查询range表示当前查询操作是查某个区间eq_ref表示目前在做多表关联查询ref表示目前使用了普通索引查询index表示目前SQL使用了辅助索引查询possible_keys执行SQL时优化器可能会选择的索引最后执行不一定用key查询语句执行时用到的索引名字key_len这里表示索引字段使用的字节数ref这里显示使用了那种查询的类型rows当前查询语句可能会扫描多少行数据才能检索出结果Extra这里是记录着额外的一些索引使用信息有几种状态using index表示目前使用了覆盖索引查询稍后讲using where表示使用了where子句查询通常表示没使用索引using index condition表示查询条件使用到了联合索引的前面几个字段using temporary表示使用了临时表处理查询结果using filesort表示以索引字段之外的方式进行排序效率较低select tables optimized away表示在索引字段上使用了聚合函数对于上述这么多的字段其实目前不需要完全弄懂本文只需要记住里面的type字段即可all表示走全表扫描const、ref...表示通过索引查询这么多字段不需要全部都弄懂本文只需要记住里面的type字段即可all表示走全表扫描const、ref...表示通过索引查询下面来聊一聊索引失效的场景查询中带有OR会导致索引失效这条SQL包含了主键索引字段user_id还包含了联合索引的第一个字段user_name按道理来说是会走索引查询的但是看看执行结果就知道了结果中看到typeALL显然走的是全表扫描进行查询没有走索引进行检索数据正是因为使用了or导致所有失效EXPLAIN SELECT * FROM zz_users WHERE user_id 1 OR user_name 熊猫;模糊查询中like以%开头导致索引失效我们都知道使用关键字like做模糊查询的时候是可以使用索引进行检索数据的例如explain select * from zz_users where user_name like %JunLa;在这条SQL中以联合索引的第一个字段user_name作为查询条件按道理来说是会使用索引进行查询的但是看看执行结果还是显式typeALL。也就是说进行全表扫描没有使用索引进行数据检索所有使用索引是因为like以%开头了但是如果like不以%开头是不会导致所有失效的explain select * from zz_users where user_name like JunLa%;这里以%结尾是可以使用联合索引来检索数据的不会导致索引失效字符类型查询时不带引号导致索引失效# 先插入一条数据插入一条user_name111的数据 insert into zz_users values (4, 111, 男, 423, 2022-9-17 23:32:32); explain select * from zz_users where user_name 111;# 没有使用双引号联合索引会失效 explain select * from zz_users where user_name 111;# 使用了双引号联合索引可以正常使用没毛病上面的查询SQL按道理来说是没有问题的而且还符合联合索引最左匹配原则但是看看执行结果从结果中可以看出typeALL因为user_name是字符串类型的而查询的时候没有写双引号就会导致索引失效索引字段参与计算导致索引失效explain select * from zz_users where user_id - 1 1;这条SQL实际上就是想要查询ID2的数据这里使用了主键字段正常情况下会使用主键索引进行查询数据的但是由于索引字段user_id参与了计算就会导致主键索引失效就是导致了索引失效了所以记住千万不要让索引字段在SQL中参与计算而且使用聚合函数也会导致索引失效根本原因就是因为索引字段参与了计算导致索引失效运算包括、-、*、/、......等字段被用于函数计算导致索引失效explain select * from zz_users where substring(user_name, 0, 1) JunLa;我们使用的substring函数对user_name字段进行了截取然后再用于条件查询就是这个字段是联合索引中的一个字段被用于函数计算了就会导致索引失效跟上一个失效原因大致相同索引字段参与计算导致索引失效违背最左前缀原则导致索引失效explain select * from zz_users where user_sex 男 and password 1234;这条SQL使用了联合索引的user_sex和password字段但是没有使用user_name字段user_name字段是最左也是第一个字段很显然就违背了最左前缀规则导致了索引失效从而走全表扫描进行检索数据不同字段值对比导致索引失效从一张表中查询出一些值然后根据这些查询出来的值去其他表中筛选数据这个业务也是实际项目中较为常见的场景虾米那简单实现一下用姓名和性别来模拟一下explain select * from zz_users where user_name user_sex;上面的SQL使用了user_name属于联合索引的第一个字段所以上述这条SQL理论应该会走联合索引检索数据但是这个场景是无法使用索引的要记住了直接记住吧反向范围操作导致索引失效一般来说如果SQL属于正向范围查询例如、、between、like、in...等操作时索引是可以正常生效的但是如果SQL执行的是反向范围操作例如not in、 not like、 is not null、 ! 、....等操作时就会出问题例如explain select * from zz_users where user_id not in(1,2,3);上述SQL就是查询user_id不是1,2,3的数据基于注解索引字段user_id查询的但是不会走索引因为反向范围操作会导致索引实现奥正向范围查询索引才有效但是不是所有的正向范围查询都会走索引例如is null就不会走索引但是反向范围查询都不会走索引小结在MySQL中还有一种特殊情况导致索引失效就是当走索引扫描的行数超过表行数的30%时MySQL会默认放弃索引查询转成全表扫描的方式去检索数据所以在这种情况下是走索引的随机磁盘IO不一定有全表的顺序磁盘IO快还有一点索引是否失效还会跟索引的数据结构有关还跟存储引擎有关使用索引的正确姿势我们如何使用索引才是正确的呢查询SQL中尽量不要使用OR关键字可以使用多SQL或子查询代替模糊查询尽量不要以%开头如果实在要实现这个功能可以建立全文索引编写SQL时一定要注意字段的数据类型否则MySQL的隐式转换会导致索引失效一定不要在编写SQL时让索引字段执行计算工作尽量将计算工作放在客户端中完成对于索引字段尽量不要使用计算类函数一定要使用时请记得将函数计算放在后面多条件的查询SQL一定要使用联合索引中的第一个字段否则会打破最左匹配原则对于需要对比多个字段的查询业务时可以拆分为连表查询使用临时表代替在SQL中不要使用反范围性的查询条件大部分反范围性、不等性查询都会让索引失效.......其实就是根据前面学习的索引失效的情况来让自己写的SQL避免这些索引失效的情况让我们自己写出来的SQL能走索引查询就可以很大程度提高检索数据的效率更详细的内容会在SQL优化篇中讲解接下来讲一下几个重点索引覆盖、索引下推、Multi-Range Read机制、索引条约式扫描机制索引覆盖由于表中只能存在一个聚簇索引一般都为主键索引而建立的其他索引都为辅助索引包括联合索引也不例外最终索引节点上存储的都是指向主键索引的值拿前面的用户表为例select * from zz_users where user_name JunLa and user_sex 男虽然这条SQL会走联合索引进行查询但是基于联合索引查询出来的值仅是一个指向主键索引的ID然后用这个ID再去主键索引中查一遍这个过程就是上面说的回表过程那么怎么解决回表问题呢假设此时需要user_name、user_sex、password这三个字段的信息此时SQL语句可以更改成如下情况SELECT user_name,user_sex,password FROM zz_users WHERE user_name JunLa AND user_sex 男; 这条SQL就不会发生回表现象了因为此时需要查询的这三个字段在联合索引中是完全包含的索引可以直接通过联合索引获取到数据但是如果使用*因为联合索引中不具备完整的一行数据只能再次转向聚簇索引中获取完整的行数据所以我们一般在查询数据的时候不用*的原因会导致索引覆盖失效造成回表问题再继续往下看EXPLAIN SELECT user_name,user_sex FROM zz_users WHERE password 1234 AND user_sex 男; 这条SQL显然不满足最左前缀和法则但是执行结果是那么为什么这条SQL使用了索引进行检索数据呢因为索引覆盖即要查询的列在使用的索引中已经包含了被所使用的索引覆盖了那么这种情况就是索引覆盖就会使用索引索引下推索引下推是MySQL5.6版本后引入的一种优化机制还是以用户表为例看一条SQLINSERT INTO zz_users VALUES(5,竹竹,女,8888,2022-09-20 22:17:21); SELECT * FROM zz_users WHERE user_name LIKE 竹% AND user_sex男; 首先为了更加直观的讲清楚索引下推因此先再向用户表中增加一条数据。然后再来看看后面的查询SQL这条SQL会使用联合索引吗答案是会的但只能部分使用因为联合索引的每个节点信息大致如下{ [熊猫,女,6666] : 1, [竹子,男,1234] : 2, [子竹,男,4321] : 3, [1111,男,4321] : 4, [竹竹,女,8888] : 5 } 由于前面使用的是模糊查询但%在结尾因此可以使用竹这个字作为条件在联合索引中查询整个查询过程如下利用联合索引中的user_name字段找出「竹子、竹竹」两个索引节点返回索引节点存储的值「2、5」给Server层然后去逐一做回表扫描在Server层中根据user_sex男这个条件逐条判断最终筛选到「竹子」这条数据有人或许会疑惑为什么user_sex男这个条件不在联合索引中处理呢因为前面是模糊查询所以拼接起来是这样的竹x男由于这个x是未知的因此无法根据最左前缀原则去匹配数据最终这里只能使用联合索引中user_name字段的一部分后续的user_sex男还需要回到Server层处理那什么又叫做索引下推呢也就是将Server层筛选数据的工作下推到引擎层处理。以前面的案例来讲解MySQL5.6加入索引下推机制后其执行过程是什么样子的呢利用联合索引中的user_name字段找出「竹子、竹竹」两个索引节点根据user_sex男这个条件在索引节点中逐个判断从而得到「竹子」这个节点最终将「竹子」这个节点对应的「2」返回给Server层然后聚簇索引中回表拿数据相较于没有索引下推之前原本需要做「2、5」两次回表查询但在拥有索引下推之后仅需做「2」一次回表查询。索引下推在MySQL5.6版本之后是默认开启的可以通过命令set optimizer_switchindex_condition_pushdownoff|on;命令来手动管理。MRR(Multi-Range Read)机制Multi-Range Read简称为MRR机制这也是和索引下推一同在MySQL5.6版本中引入的性能优化措施那什么叫做MRR优化呢一般来说在实际业务中我们应当尽量通过索引覆盖的特性减少回表操作以降低IO次数但在很多时候往往又不得不做回表才能查询到数据但回表显然会导致产生大量磁盘IO同时更严重的一点是还会产生大量的离散IO下面举个例子来理解。sql 体验AI代码助手 代码解读 复制代码SELECT * FROM zz_student_score WHERE score BETWEEN 0 AND 59;上述这条SQL所做的工作很简单就是在学生成绩表中查询所有成绩未及格的学生信息假设成绩字段上存在一个普通索引那思考一下这条SQL的执行流程是什么样的呢先在成绩字段的索引上找到0分的节点然后拿着ID去回表得到成绩零分的学生信息再次回到成绩索引继续找到所有1分的节点继续回表得到1分的学生信息再次回到成绩索引继续找到所有2分的节点......周而复始不断重复这个过程直到将0~59分的所有学生信息全部拿到为止那此时假设此时成绩0~5分的表数据位于磁盘空间的page_01页上而成绩为5~10分的数据位于磁盘空间的page_02页上成绩为10~15分的数据又位于磁盘空间的page_01页上。此时回表查询时就会导致在page_01、page_02两页空间上来回切换但0~5、10~15分的数据完全可以合并然后读一次page_01就可以了既能减少IO次数同时还避免了离散IO。而MRR机制就主要是解决这个问题的针对于辅助索引的回表查询减少离散IO并且将随机IO转换为顺序IO从而提高查询效率。那MRR机制具体是怎么做的呢MRR机制中对于辅助索引中查询出的ID会将其放到缓冲区的read_rnd_buffer中然后等全部的索引检索工作完成后或者缓冲区中的数据达到read_rnd_buffer_size大小时此时MySQL会对缓冲区中的数据排序从而得到一个有序的ID集合rest_sort最终再根据顺序IO去聚簇/主键索引中回表查询数据。SET optimizer_switchmrron|off,mrr_cost_basedon|off;可以通过上述这条命令开启或关闭MRR机制MySQL5.6及以后的版本是默认开启的。Index Skip Scan索引跳跃式扫描在讲联合索引时咱们提到过最左前缀匹配原则也就是SQL的查询条件中必须要包含联合索引的第一个字段这样才能命中联合索引查询但实际上这条规则也并不是100%遵循的。因为在MySQL8.x版本中加入了一个新的优化机制也就是索引跳跃式扫描这种机制使得咱们即使查询条件中没有使用联合索引的第一个字段也依旧可以使用联合索引看起来就像跳过了联合索引中的第一个字段一样这也是跳跃扫描的名称由来。但跳跃扫描究竟是怎么实现的呢上个栗子快速理解一下。比如此时通过(A、B、C)三个列建立了一个联合索引此时有如下一条SQLsql 体验AI代码助手 代码解读 复制代码SELECT * FROM tb_xx WHERE B xxx AND C xxx;按理来说这条SQL既不符合最左前缀原则也不具备使用索引覆盖的条件因此绝对是不会走联合索引查询的但思考一个问题这条SQL中都已经使用了联合索引中的两个字段结果还不能使用索引这似乎有点亏啊对不因此MySQL8.x推出了跳跃扫描机制但跳跃扫描并不是真正的“跳过了”第一个字段而是优化器为你重构了SQL比如上述这条SQL则会重构成如下情况sql 体验AI代码助手 代码解读复制代码SELECT * FROM tb_xx WHERE B xxx AND C xxx UNION ALL SELECT * FROM tb_xx WHERE B xxx AND C xxx AND A yyy ...... SELECT * FROM tb_xx WHERE B xxx AND C xxx AND A zzz;其实也就是MySQL优化器会自动对联合索引中的第一个字段的值去重然后基于去重后的值全部拼接起来查一遍一句话来概述就是虽然你没用第一个字段但我给你加上去今天这个联合索引你就得用不用也得给我用。当然如果熟悉Oracle数据库的小伙伴应该知道跳跃扫描机制在Oracle中早就有了但为什么MySQL8.0版本才推出这个机制呢还记得咱们在《MySQL架构篇》中的闲谈嘛MySQL几经转手后最终归到了Oracle旗下因此跳跃扫描机制仅是Oracle公司从Oracle搬到了“自己的MySQL”上而已。但是跳跃扫描机制也有很多限制比如多表联查时无法触发、SQL条件中有分组操作也无法触发、SQL中用了DISTINCT去重也无法触发.....总之有很多限制条件具体的可以参考《MySQL官网8.0-跳跃扫描》。其实这个跳跃性扫描机制只有在唯一性较差的情况下才能发挥出不错的效果如果你联合索引的第一个字段是一个值具备唯一性的字段那去重一次再拼接几乎就等价于走一次全表。最后可以通过通过set optimizer_switch skip_scanoff|on;命令来选择开启或关闭跳跃式扫描机制。当然该参数仅限MySQL8.0以上的版本如果在此之下的版本暂时就不用考虑了。索引应用篇总结至此MySQL索引应用篇也就是索引中篇就结束了相信大家认真看完本篇之后对于索引的掌握性、熟练程度绝对会更上一层楼因为本章中从索引的优劣分析到建立索引的原则、索引失效的情景、使用索引的正确姿势、MySQL对于索引的优化机制等各方面对索引进行了进一步阐述。经历中、上两篇的阐述后对于MySQL索引这个大体系已经建立出了完整的认知下一篇就是《索引原理篇》啦在中、上两篇中抛出了很多疑惑都留在了索引原理篇中去分析因为只有当你真正搞懂了索引的底层实现才能更好的理解一些前面给出的建议、定论及概念。当然如果你认为我的文章对你有帮助那可以动动发财的小手点上一个免费的小赞赞~点赞量足够多可加快《索引原理篇》的解锁进度更文速度完全取决于诸位的点赞数量当然就算不给赞《索引原理篇》也不会缺席噢最后再给出两条关于索引的查询命令show status like %Handler_read%;查看当前会话的索引使用情况。show global status like Handler_read%;查询全局索引使用情况。这两条命令在之后的《SQL调优篇》中会用到的这些命令中的信息对于调优会有不小的参考价值当然对于详细的分析在后续的篇章中再详细讲解咯
五，MySQL索引应用篇：建立索引的正确姿势和使用索引的最佳指南
发布时间：2026/5/16 3:21:39
前言数据库索引是MySQL核心功能之一如果数据库没有索引机制面对海量的数据进行检索效率是绝对无法接收的毕竟没有索引机制的数据库就跟普通文本文件存储在磁盘中一样在MySQL索引初始篇中讲解了数据库索引机制创建、使用、分类、管理、删除等操作那么我们就以上篇文章为基础对数据库索引机制进一步深入学习虽然通过上篇文章对数据库索引机制有了一个全面的认知但是还缺乏一些经验和方法论才能运用到实际的业务项目中仅根据上一篇文章也还有一些关于索引的问题不能解答索引虽然给MySQL检索数据的效率带来了提升但加入索引没有带来新的问题吗既然索引能提升查询性能那是不是为表中的每个字段建立索引性能会更好呢一张数据表中哪些类型的字段不适合建立索引呢什么原因不适合建立索引呢表中会存在大量的字段但其中哪些字段建立索引才能得到最大性能收益呢MySQL提供的索引种类也不少一个字段上建立什么类型的索引才最好呢当表中存在多个索引时一条查询SQL有多条路径可走此时走那条索引最好在接下来这篇文章中中带你讲解索引应用相关的知识例如各索引的优劣之分、建立索引的原则、使用索引的指南以及索引失效与索引优化等内容MySQL各索引的优劣分析正所谓凡事有利必有弊MySQL的索引机制也是一样的那么引入索引机制给数据库带来的好处在整个数据库中对数据库表的查询速度直线提升如果数据量越大提升效果越明显通过创建唯一索引可以确保数据库表中的数据唯一性不需要额外再去建立唯一约束在使用分组和排序的时候可以显著减少SQL查询的分组和排序时间连表查询时基于主外键字段上建立的索引可以带来十分明显的性能索引默认的是BTree有序结构基于索引字段做范围查询时效率会明显提高从MySQL整体架构而言减少了查询SQL的执行时间提高了数据库整体吞吐量弊端建立索引会生成本地磁盘文件需要额外的磁盘空间来存储索引数据磁盘占用率会变高写入数据时需要额外维护索引结构增删改数据时都需要额外操作索引写入数据时维护索引需要额外的时间开销执行写SQL时效率会降低性能会下降那么整体来说索引带来的优势是大于劣势的但是索引不是越多越好的所以我们要合理建立索引才是最佳选择那么我们接下来就聊一下各类索引的弊端主键索引存在的陷阱在我们数据库的表中主键一般都是使用自增ID但为什么呢很显然就是自增ID不会重复确保了主键唯一性这也没错但是随机的UUID也不会重复那为什么不用UUID呢这就是由于索引存在一个陷阱因为一张表大多数情况下会将主键索引以聚簇索引的形式存在磁盘上上篇文章也说过聚簇索引在存储数据时表数据和索引数据是一起存放的。同时MySQL默认的索引结构是BTree也就代表着索引节点的数据是有序的主键索引是聚簇索引、表数据和索引数据是一块的、索引结构是有序的那么就可以反推为什么不使用UUID呢因为UUID是无序的如果使用UUID作为主键那么每插入一条新的数据都有可能破环原本的树结构如下假如灰色节点就是新插入的数据此时经过计算应该排到第二个位置那就代表着后面的三个节点需要进行移动给灰色节点挪出一个位置存储从而确保索引的有序性那么这个树结构就被破坏了所以使用UUID并不好注意这里知识为逻辑仅仅用于举例演示实际上B树索引结构不长这样在后面的索引原理篇会重新说但是如果我们使用注解ID是有序的就不需要改变树结构所有的新插入的数据都会放到最后面所以我们在创建数据库表的主键的时候最好选用带顺序性的值否则就会掉入上述的陷阱中联合索引存在的矛盾联合索引就是为了在多条件查询时有更高的效率一般会同时对多个字段建立联合索引但之前聊到过联合索引有一个问题就是在查询SQL中必须要包含联合索引的第一个字段否则不会触发联合索引进行检索数据例如假如以id、name、age三个字段作为联合索引进行查询select * from user_table where name JunLa and age 18;显然这条SQL是无法使用联合索引的因为它的查询条件没有包含联合索引的第一个字段想要使用联合索引那么这条查询SQL的查询条件必须包含联合索引的第一个字段select * from user_table where id 1 and name JunLa;这条SQL才能命中多列索引的语句因此在建立索引时需要考虑查询条件这个问题确保建立出来的联合索引命中率够高前缀索引存在的弊端前缀索引就是利用字段的前N个字符去创建索引相比于使用一个完整字段创建索引前缀索引能够更节省存储空间当数据越多时带来的优势越明显虽然前缀索引节省了空间但是因为它的索引节点中未存储一个字段的完整值所以MySQL也无法通过前缀索引来完成order by、 group by等分组排序工作也无法完成覆盖扫描等操作全文索引存在的硬伤在没有使用全文索引的使用我们通常会使用like%模糊查询来进行查询但是随着数据越来越多表越来越多模糊查询的性能会明显下降全文索引就能够完美解决这个问题可以利用全文索引来代替like%模糊查询性能比模糊查询快很多倍但是全文索引也是存在一些问题的由于全文索引是基于分词实现的所以对一个字段建立全文索引后MySQL会对该字段做分词处理这些分词结果也会被存储在全文索引中因此全文索引的文件会额外的大占用的内存会很大由于全文索引对每个字段值都会做分词因此当修改字段值后分词是需要时间的所以修改字段数据后不会立马自动更新全文索引此时需要我们写存储过程并调用它手动更新全文索引中的数据全文索引最大的硬伤就是不支持真类似于英文可以直接通过符号、空格来分词但中文不行无法精确地对一段文字做分词因此全文索引在检索中文时存在些许精度的温度所以如果你的项目规模比较大通常会再引入ElasticSearch、Solr、MeilSearch等搜索引擎唯一索引存在的快慢问题唯一索引的一大好处就是查询数据时会比普通索引效率更高例如走普通索引进行查询假设column_xx字段上建立一个普通索引此时基于这个字段查询数据时当查询到column_xx “xx”的数据后此时会继续走完整个索引树因为可能会存在多条字段值相同的数据效率是比较低的因为要走完整个索引树可能会出现重复的数据select * from table_xx where column_xx xx;但是如果column_xx字段上建立的是唯一索引就确保了数据的唯一性就不需要走完整个索引树不会有重复的数据只要找到一条对应的数据后就会里面停下来检索因此建立唯一索引的字段值就具备唯一性所以在检索数据的时候唯一索引是比普通索引要快的但在插入数据的时候不一样因为唯一索引要确保数据是不重复的所以在插入数据前要检查一遍表中是否存在相同的数据但是普通索引不需要考虑这个问题直接插入就行了哈希索引的致命问题哈希索引就是数据结构为Hash类型的索引哈希索引的查询速度在MySQL中是最快的因为采用哈希结构的索引会以哈希表的形式存储索引字段值当基于该字段查询数据时只需要经过一次哈希计算就可以获取到数据但是哈希结构的致命问题就是无序也就是无法基于哈希索引的字段做排序、分组工作等如果你能确定这个表中不会做排序这类操作的话可以选择哈希结构作为索引的数据结构建立索引的正确姿势经过上面的分析我们知道了每种索引类型存在的缺陷问题那么当我们知道了每种索引存在的缺陷后就能更好地考虑周到通过不同类型的索引进行一个互补的关系设计出更合理的索引在实际项目场景中当SQL查询性能较慢时我们通常会想表中哪个字段建立索引能够带来最大性能收益一般来说是否要在这个字段上添加索引的依据是看这个字段是否被经常用来做查询条件来使用但是仅靠这个规则也不行性别经常查询但是带来的性能提升却不高索引查询时的回表问题什么叫回表呢就是指一条SQL语句在MySQL内部需要经过两次查询过程才能获取到数据这是跟索引机制有关的先来看看索引在MySQL内部真正的面貌参考自竹子爱熊猫在上图用户表中基于自增ID字段先建立一个主键索引然后又基于name字段建立一个普通索引此时MySQL默认会选用主键索引作为聚簇索引将表数据和主键索引存在同一个文件中也就是主键索引的每个索引节点都直接对应着行数据而基于name字段建立的索引其索引节点存放的则时指向聚簇索引的ID值举个例子select * from table_user where name 子竹;首先会走name字段的索引然后找到对应的ID值然后再基于查询到的ID值再走ID字段的主键索引最终得到一整条行数据并返回这个过程SQL经历了两次查询才获取到数据这个过程就叫回表回表会导致额外的查询开销因此尽量可以基于主键做查询如果实在是需要使用非主键字段查询那么尽量写明查询的结果字段而不要使用*建立索引时需要遵守的原则前面说过仅仅考虑一个字段是否被经常查询来判断是否要在这个字段上创建索引是不够的那么下面就看看建立索引时需要遵守的原则经常频繁用作查询条件的字段应酌情考虑为其创建索引因为可能会掉入陷阱表的主外键或连表字段必须建立索引因为这样可以很大程度提升连表查询的性能建立索引的字段一般值的区分性要足够高这样才能提高索引的检索效率建立索引的字段值不应该过长如果较长的字段要建立索引可以选择前缀索引建立联合索引应当遵循最左前缀原则将多个字段之间按优先级顺序组合经常根据范围取值、排序、分组的字段应建立索引因为索引有序能加快排序时间对于唯一索引如果确认不会利用该字段排序那可以将结构改为Hash结构尽量使用联合索引代替单值索引联合索引比多个单值索引查询效率更高建立索引需要注意的一些点值经常会增删改的字段不合适建立索引因为每次改变后需维护索引结构一个字段存在大量的重复值时不适合建立索引比如之前举例的性别字段索引不能参与计算因此经常带函数查询的字段并不适合建立索引一张表中的索引数量并不是越多越好一般控制在3最多不能超过5建立联合索引时一定要考虑优先级查询频率最高的字段应当放首位当表的数据较少不应当建立索引因为数据量不大时维护索引反而开销更大索引的字段值无序时不推荐建立索引因为会造成页分裂尤其是主键索引联合索引的最左前缀原则有一条原则尽量使用联合搜友代替单值索引联合索引比多个单值索引查询效率更高举个例子假如现在基于X,Y,Z这三个字段建立一个联合索引实际上也相当于建立三个索引X,X,Y,X,Y,Z因此只要查询中使用了这三组字段都可以触发联合索引都可以使联合索引生效联合索引的最左前缀原则很简单就是组成联合索引的多个列越靠左优先级越高同时也只有SQL查询条件中包含了最左的字段才能使用联合索引例如# 基于上面创建的X、Y、Z联合索引进行查询 select * from tb where Y .... and Z ...;上面这条SQL显然不会使用联合索引因为不符合最左前缀原则最左侧的字段是X 没有被使用。也正是因为MySQL在使用联合索引的时候会遵循最左前缀原则所以才会在前面建立索引中提出一条建议建立联合索引时一定要考虑优先级查询频率最高的字段应当放首位因为将查询频率越高的字段放首位意味着查询时命中索引的几率越大。同时MySQL的最左前缀原则在匹配到范围查询时会停止匹配例如、、between、like这类范围查询条件并不会继续使用联合索引 例如select * from tb where X ... and Y ... and Z ....;当执行时虽然上述SQL使用到X、Y、Z作为查询条件但由于Y字段是范围查询因此这里只能使用X索引而不能完全匹配使用X、Y、Z索引。对于X、Y索引呢严格意义上来说Y字段的范围查询也会基于索引来完成所以这里其实能用到X和X、Y字段的索引也正是因为基于Y字段做了范围查询从而阻断了Z字段基于索引匹配再举个例子select * from user where name JunLa select * from user where name JunLa and age 18; A. create index index_name on user(name); create index index_name on user(age); B. create index index_name on user(name,age);对于这两条SQL选第一种方式创建索引还是选第二种呢?选B因为两条SQL完全能够利用到第二个创建的联合索引select * from user where name JunLa and age 18; select * from user where age 18 and name JunLa;同时选B建立联合索引后如上两条SQL都会利用到上面创建的联合索引SQL是否走索引查询跟where后的条件顺序无关因为MySQL优化器会优化对SQL查询条件进行重排序。索引失效与使用索引的正确姿势这里指的是我们再编写SQL时需要注意的点毕竟MySQL查询时到底使不使用索引这完全取决于你编写的SQL我们在编写SQL的时候不仅仅要追求实现业务功能还要考虑这条SQL应用到索引中而且索引是一把双刃剑既然我们创建了索引就要好好利用它索引失效想要用好索引就必须知道索引在哪些情况下会失效知道了之后我们在写SQL的时候就会可以去避开这些问题那么我们写出来的SQL就会十有八九是会用到索引的我们下面就聊一下数据库中哪些情况下会导致索引失效举个案例创建两个索引user_id主键索引user_name、user_sex、password组成联合索引如果想要查看一条查询SQL是否使用了索引就要用到一个自带的分析工具Explain下面来简单介绍一下SELECT * FROM users_table; ------------------------------------------------------------- | user_id | user_name | user_sex | password | register_time | ------------------------------------------------------------- | 1 | aa | 女 | 6666 | 2022-08-14 15:22:01 | | 2 | bb | 男 | 1234 | 2022-09-14 16:17:44 | | 3 | cc | 男 | 4321 | 2022-09-16 07:42:21 | ------------------------------------------------------------- ALTER TABLE users_table ADD PRIMARY KEY p_user_id(user_id); ALTER TABLE users_table ADD KEY unite_index(user_name,user_sex,password); 执行分析工具-ExPlain这里先对ExPlain工具做一个简单的介绍后续在SQL优化篇中会详细讲解这个工具的作用当在一天SQL前加上explain命令然后执行这条SQL后就会列出所有的执行方案EXPLAIN SELECT * FROM zz_users; ---------------------------------------------------------------------------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ---------------------------------------------------------------------------------- | 1 | SIMPLE | zz_users | ALL | NULL | NULL | NULL | NULL | 3 | | ---------------------------------------------------------------------------------- id这是执行计划的ID值这个值越大表示执行的优先级越高。select_type当前查询语句的类型有如下几个值simple简单查询primary复杂查询的外层查询subquery包含在查询语句中的子查询derived包含在FROM中的子查询table表示当前这个执行计划是基于那张表执行的type当前执行计划查询的类型有几种情况all表示走了全表查询未命中索引或索引失效system表示要查询的表中仅有一条数据const表示当前SQL语句的查询条件中可以命中索引查询range表示当前查询操作是查某个区间eq_ref表示目前在做多表关联查询ref表示目前使用了普通索引查询index表示目前SQL使用了辅助索引查询possible_keys执行SQL时优化器可能会选择的索引最后执行不一定用key查询语句执行时用到的索引名字key_len这里表示索引字段使用的字节数ref这里显示使用了那种查询的类型rows当前查询语句可能会扫描多少行数据才能检索出结果Extra这里是记录着额外的一些索引使用信息有几种状态using index表示目前使用了覆盖索引查询稍后讲using where表示使用了where子句查询通常表示没使用索引using index condition表示查询条件使用到了联合索引的前面几个字段using temporary表示使用了临时表处理查询结果using filesort表示以索引字段之外的方式进行排序效率较低select tables optimized away表示在索引字段上使用了聚合函数对于上述这么多的字段其实目前不需要完全弄懂本文只需要记住里面的type字段即可all表示走全表扫描const、ref...表示通过索引查询这么多字段不需要全部都弄懂本文只需要记住里面的type字段即可all表示走全表扫描const、ref...表示通过索引查询下面来聊一聊索引失效的场景查询中带有OR会导致索引失效这条SQL包含了主键索引字段user_id还包含了联合索引的第一个字段user_name按道理来说是会走索引查询的但是看看执行结果就知道了结果中看到typeALL显然走的是全表扫描进行查询没有走索引进行检索数据正是因为使用了or导致所有失效EXPLAIN SELECT * FROM zz_users WHERE user_id 1 OR user_name 熊猫;模糊查询中like以%开头导致索引失效我们都知道使用关键字like做模糊查询的时候是可以使用索引进行检索数据的例如explain select * from zz_users where user_name like %JunLa;在这条SQL中以联合索引的第一个字段user_name作为查询条件按道理来说是会使用索引进行查询的但是看看执行结果还是显式typeALL。也就是说进行全表扫描没有使用索引进行数据检索所有使用索引是因为like以%开头了但是如果like不以%开头是不会导致所有失效的explain select * from zz_users where user_name like JunLa%;这里以%结尾是可以使用联合索引来检索数据的不会导致索引失效字符类型查询时不带引号导致索引失效# 先插入一条数据插入一条user_name111的数据 insert into zz_users values (4, 111, 男, 423, 2022-9-17 23:32:32); explain select * from zz_users where user_name 111;# 没有使用双引号联合索引会失效 explain select * from zz_users where user_name 111;# 使用了双引号联合索引可以正常使用没毛病上面的查询SQL按道理来说是没有问题的而且还符合联合索引最左匹配原则但是看看执行结果从结果中可以看出typeALL因为user_name是字符串类型的而查询的时候没有写双引号就会导致索引失效索引字段参与计算导致索引失效explain select * from zz_users where user_id - 1 1;这条SQL实际上就是想要查询ID2的数据这里使用了主键字段正常情况下会使用主键索引进行查询数据的但是由于索引字段user_id参与了计算就会导致主键索引失效就是导致了索引失效了所以记住千万不要让索引字段在SQL中参与计算而且使用聚合函数也会导致索引失效根本原因就是因为索引字段参与了计算导致索引失效运算包括、-、*、/、......等字段被用于函数计算导致索引失效explain select * from zz_users where substring(user_name, 0, 1) JunLa;我们使用的substring函数对user_name字段进行了截取然后再用于条件查询就是这个字段是联合索引中的一个字段被用于函数计算了就会导致索引失效跟上一个失效原因大致相同索引字段参与计算导致索引失效违背最左前缀原则导致索引失效explain select * from zz_users where user_sex 男 and password 1234;这条SQL使用了联合索引的user_sex和password字段但是没有使用user_name字段user_name字段是最左也是第一个字段很显然就违背了最左前缀规则导致了索引失效从而走全表扫描进行检索数据不同字段值对比导致索引失效从一张表中查询出一些值然后根据这些查询出来的值去其他表中筛选数据这个业务也是实际项目中较为常见的场景虾米那简单实现一下用姓名和性别来模拟一下explain select * from zz_users where user_name user_sex;上面的SQL使用了user_name属于联合索引的第一个字段所以上述这条SQL理论应该会走联合索引检索数据但是这个场景是无法使用索引的要记住了直接记住吧反向范围操作导致索引失效一般来说如果SQL属于正向范围查询例如、、between、like、in...等操作时索引是可以正常生效的但是如果SQL执行的是反向范围操作例如not in、 not like、 is not null、 ! 、....等操作时就会出问题例如explain select * from zz_users where user_id not in(1,2,3);上述SQL就是查询user_id不是1,2,3的数据基于注解索引字段user_id查询的但是不会走索引因为反向范围操作会导致索引实现奥正向范围查询索引才有效但是不是所有的正向范围查询都会走索引例如is null就不会走索引但是反向范围查询都不会走索引小结在MySQL中还有一种特殊情况导致索引失效就是当走索引扫描的行数超过表行数的30%时MySQL会默认放弃索引查询转成全表扫描的方式去检索数据所以在这种情况下是走索引的随机磁盘IO不一定有全表的顺序磁盘IO快还有一点索引是否失效还会跟索引的数据结构有关还跟存储引擎有关使用索引的正确姿势我们如何使用索引才是正确的呢查询SQL中尽量不要使用OR关键字可以使用多SQL或子查询代替模糊查询尽量不要以%开头如果实在要实现这个功能可以建立全文索引编写SQL时一定要注意字段的数据类型否则MySQL的隐式转换会导致索引失效一定不要在编写SQL时让索引字段执行计算工作尽量将计算工作放在客户端中完成对于索引字段尽量不要使用计算类函数一定要使用时请记得将函数计算放在后面多条件的查询SQL一定要使用联合索引中的第一个字段否则会打破最左匹配原则对于需要对比多个字段的查询业务时可以拆分为连表查询使用临时表代替在SQL中不要使用反范围性的查询条件大部分反范围性、不等性查询都会让索引失效.......其实就是根据前面学习的索引失效的情况来让自己写的SQL避免这些索引失效的情况让我们自己写出来的SQL能走索引查询就可以很大程度提高检索数据的效率更详细的内容会在SQL优化篇中讲解接下来讲一下几个重点索引覆盖、索引下推、Multi-Range Read机制、索引条约式扫描机制索引覆盖由于表中只能存在一个聚簇索引一般都为主键索引而建立的其他索引都为辅助索引包括联合索引也不例外最终索引节点上存储的都是指向主键索引的值拿前面的用户表为例select * from zz_users where user_name JunLa and user_sex 男虽然这条SQL会走联合索引进行查询但是基于联合索引查询出来的值仅是一个指向主键索引的ID然后用这个ID再去主键索引中查一遍这个过程就是上面说的回表过程那么怎么解决回表问题呢假设此时需要user_name、user_sex、password这三个字段的信息此时SQL语句可以更改成如下情况SELECT user_name,user_sex,password FROM zz_users WHERE user_name JunLa AND user_sex 男; 这条SQL就不会发生回表现象了因为此时需要查询的这三个字段在联合索引中是完全包含的索引可以直接通过联合索引获取到数据但是如果使用*因为联合索引中不具备完整的一行数据只能再次转向聚簇索引中获取完整的行数据所以我们一般在查询数据的时候不用*的原因会导致索引覆盖失效造成回表问题再继续往下看EXPLAIN SELECT user_name,user_sex FROM zz_users WHERE password 1234 AND user_sex 男; 这条SQL显然不满足最左前缀和法则但是执行结果是那么为什么这条SQL使用了索引进行检索数据呢因为索引覆盖即要查询的列在使用的索引中已经包含了被所使用的索引覆盖了那么这种情况就是索引覆盖就会使用索引索引下推索引下推是MySQL5.6版本后引入的一种优化机制还是以用户表为例看一条SQLINSERT INTO zz_users VALUES(5,竹竹,女,8888,2022-09-20 22:17:21); SELECT * FROM zz_users WHERE user_name LIKE 竹% AND user_sex男; 首先为了更加直观的讲清楚索引下推因此先再向用户表中增加一条数据。然后再来看看后面的查询SQL这条SQL会使用联合索引吗答案是会的但只能部分使用因为联合索引的每个节点信息大致如下{ [熊猫,女,6666] : 1, [竹子,男,1234] : 2, [子竹,男,4321] : 3, [1111,男,4321] : 4, [竹竹,女,8888] : 5 } 由于前面使用的是模糊查询但%在结尾因此可以使用竹这个字作为条件在联合索引中查询整个查询过程如下利用联合索引中的user_name字段找出「竹子、竹竹」两个索引节点返回索引节点存储的值「2、5」给Server层然后去逐一做回表扫描在Server层中根据user_sex男这个条件逐条判断最终筛选到「竹子」这条数据有人或许会疑惑为什么user_sex男这个条件不在联合索引中处理呢因为前面是模糊查询所以拼接起来是这样的竹x男由于这个x是未知的因此无法根据最左前缀原则去匹配数据最终这里只能使用联合索引中user_name字段的一部分后续的user_sex男还需要回到Server层处理那什么又叫做索引下推呢也就是将Server层筛选数据的工作下推到引擎层处理。以前面的案例来讲解MySQL5.6加入索引下推机制后其执行过程是什么样子的呢利用联合索引中的user_name字段找出「竹子、竹竹」两个索引节点根据user_sex男这个条件在索引节点中逐个判断从而得到「竹子」这个节点最终将「竹子」这个节点对应的「2」返回给Server层然后聚簇索引中回表拿数据相较于没有索引下推之前原本需要做「2、5」两次回表查询但在拥有索引下推之后仅需做「2」一次回表查询。索引下推在MySQL5.6版本之后是默认开启的可以通过命令set optimizer_switchindex_condition_pushdownoff|on;命令来手动管理。MRR(Multi-Range Read)机制Multi-Range Read简称为MRR机制这也是和索引下推一同在MySQL5.6版本中引入的性能优化措施那什么叫做MRR优化呢一般来说在实际业务中我们应当尽量通过索引覆盖的特性减少回表操作以降低IO次数但在很多时候往往又不得不做回表才能查询到数据但回表显然会导致产生大量磁盘IO同时更严重的一点是还会产生大量的离散IO下面举个例子来理解。sql 体验AI代码助手 代码解读 复制代码SELECT * FROM zz_student_score WHERE score BETWEEN 0 AND 59;上述这条SQL所做的工作很简单就是在学生成绩表中查询所有成绩未及格的学生信息假设成绩字段上存在一个普通索引那思考一下这条SQL的执行流程是什么样的呢先在成绩字段的索引上找到0分的节点然后拿着ID去回表得到成绩零分的学生信息再次回到成绩索引继续找到所有1分的节点继续回表得到1分的学生信息再次回到成绩索引继续找到所有2分的节点......周而复始不断重复这个过程直到将0~59分的所有学生信息全部拿到为止那此时假设此时成绩0~5分的表数据位于磁盘空间的page_01页上而成绩为5~10分的数据位于磁盘空间的page_02页上成绩为10~15分的数据又位于磁盘空间的page_01页上。此时回表查询时就会导致在page_01、page_02两页空间上来回切换但0~5、10~15分的数据完全可以合并然后读一次page_01就可以了既能减少IO次数同时还避免了离散IO。而MRR机制就主要是解决这个问题的针对于辅助索引的回表查询减少离散IO并且将随机IO转换为顺序IO从而提高查询效率。那MRR机制具体是怎么做的呢MRR机制中对于辅助索引中查询出的ID会将其放到缓冲区的read_rnd_buffer中然后等全部的索引检索工作完成后或者缓冲区中的数据达到read_rnd_buffer_size大小时此时MySQL会对缓冲区中的数据排序从而得到一个有序的ID集合rest_sort最终再根据顺序IO去聚簇/主键索引中回表查询数据。SET optimizer_switchmrron|off,mrr_cost_basedon|off;可以通过上述这条命令开启或关闭MRR机制MySQL5.6及以后的版本是默认开启的。Index Skip Scan索引跳跃式扫描在讲联合索引时咱们提到过最左前缀匹配原则也就是SQL的查询条件中必须要包含联合索引的第一个字段这样才能命中联合索引查询但实际上这条规则也并不是100%遵循的。因为在MySQL8.x版本中加入了一个新的优化机制也就是索引跳跃式扫描这种机制使得咱们即使查询条件中没有使用联合索引的第一个字段也依旧可以使用联合索引看起来就像跳过了联合索引中的第一个字段一样这也是跳跃扫描的名称由来。但跳跃扫描究竟是怎么实现的呢上个栗子快速理解一下。比如此时通过(A、B、C)三个列建立了一个联合索引此时有如下一条SQLsql 体验AI代码助手 代码解读 复制代码SELECT * FROM tb_xx WHERE B xxx AND C xxx;按理来说这条SQL既不符合最左前缀原则也不具备使用索引覆盖的条件因此绝对是不会走联合索引查询的但思考一个问题这条SQL中都已经使用了联合索引中的两个字段结果还不能使用索引这似乎有点亏啊对不因此MySQL8.x推出了跳跃扫描机制但跳跃扫描并不是真正的“跳过了”第一个字段而是优化器为你重构了SQL比如上述这条SQL则会重构成如下情况sql 体验AI代码助手 代码解读复制代码SELECT * FROM tb_xx WHERE B xxx AND C xxx UNION ALL SELECT * FROM tb_xx WHERE B xxx AND C xxx AND A yyy ...... SELECT * FROM tb_xx WHERE B xxx AND C xxx AND A zzz;其实也就是MySQL优化器会自动对联合索引中的第一个字段的值去重然后基于去重后的值全部拼接起来查一遍一句话来概述就是虽然你没用第一个字段但我给你加上去今天这个联合索引你就得用不用也得给我用。当然如果熟悉Oracle数据库的小伙伴应该知道跳跃扫描机制在Oracle中早就有了但为什么MySQL8.0版本才推出这个机制呢还记得咱们在《MySQL架构篇》中的闲谈嘛MySQL几经转手后最终归到了Oracle旗下因此跳跃扫描机制仅是Oracle公司从Oracle搬到了“自己的MySQL”上而已。但是跳跃扫描机制也有很多限制比如多表联查时无法触发、SQL条件中有分组操作也无法触发、SQL中用了DISTINCT去重也无法触发.....总之有很多限制条件具体的可以参考《MySQL官网8.0-跳跃扫描》。其实这个跳跃性扫描机制只有在唯一性较差的情况下才能发挥出不错的效果如果你联合索引的第一个字段是一个值具备唯一性的字段那去重一次再拼接几乎就等价于走一次全表。最后可以通过通过set optimizer_switch skip_scanoff|on;命令来选择开启或关闭跳跃式扫描机制。当然该参数仅限MySQL8.0以上的版本如果在此之下的版本暂时就不用考虑了。索引应用篇总结至此MySQL索引应用篇也就是索引中篇就结束了相信大家认真看完本篇之后对于索引的掌握性、熟练程度绝对会更上一层楼因为本章中从索引的优劣分析到建立索引的原则、索引失效的情景、使用索引的正确姿势、MySQL对于索引的优化机制等各方面对索引进行了进一步阐述。经历中、上两篇的阐述后对于MySQL索引这个大体系已经建立出了完整的认知下一篇就是《索引原理篇》啦在中、上两篇中抛出了很多疑惑都留在了索引原理篇中去分析因为只有当你真正搞懂了索引的底层实现才能更好的理解一些前面给出的建议、定论及概念。当然如果你认为我的文章对你有帮助那可以动动发财的小手点上一个免费的小赞赞~点赞量足够多可加快《索引原理篇》的解锁进度更文速度完全取决于诸位的点赞数量当然就算不给赞《索引原理篇》也不会缺席噢最后再给出两条关于索引的查询命令show status like %Handler_read%;查看当前会话的索引使用情况。show global status like Handler_read%;查询全局索引使用情况。这两条命令在之后的《SQL调优篇》中会用到的这些命令中的信息对于调优会有不小的参考价值当然对于详细的分析在后续的篇章中再详细讲解咯
