MySQL索引
MySQL 正确使用索引
对于如何使用索引才是正确的呢?总结如下: - ①查询SQL中尽量不要使用OR关键字,可以使用多SQL或子查询代替。 - ②模糊查询尽量不要以%开头,如果实在要实现这个功能可以建立全文索引。 - ③编写SQL时一定要注意字段的数据类型,否则MySQL的隐式转换会导致索引失效。 - ④一定不要在编写SQL时让索引字段执行计算工作,尽量将计算工作放在客户端中完成。 - ⑤对于索引字段尽量不要使用计算类函数,一定要使用时请记得将函数计算放在=后面。 - ⑥多条件的查询SQL一定要使用联合索引中的第一个字段,否则会打破最左匹配原则。 - ⑦对于需要对比多个字段的查询业务时,可以拆分为连表查询,使用临时表代替。 - ⑧在SQL中不要使用反范围性的查询条件,大部分反范围性、不等性查询都会让索引失效。 实际上无非就是根据前面给出的索引失效情况,尽量让自己编写的SQL不会导致索引失效即可,写出来的SQL能走索引查询,那就能在很大程度上提升数据检索的效率。 ### 索引覆盖 由于表中只能存在一个聚簇索引,一般都为主键索引,而建立的其他索引都为辅助索引,包括联合索引也例外,最终索引节点上存储的都是指向主键索引的值,拿用户表为例: ```sql SELECT * FROM `zz_users` WHERE `user_name`="竹子" AND `user_sex`="男"; ``` 虽然这条SQL会走联合索引查询,但是基于联合索引查询出来的值仅是一个指向主键索引的ID,然后会拿着这个ID再去主键索引中查一遍,这个过程之前聊过,被称为回表过程。 那么回表问题无法解决吗?必须得经过两次查询才能得到数据吗?答案并非如此。 比如假设此时只需要user_name、user_sex、password这三个字段的信息,此时SQL语句可以更改为如下情况: ```sql SELECT `user_name`,`user_sex`,`password` FROM `zz_users` WHERE `user_name` = "竹子" AND `user_sex` = "男"; ``` 此时将SQL更改为查询所需的列后,就不会发生回表现象,Why?再这里很多小伙伴可能会疑惑,这是什么道理啊?因为此时所需的user_name、user_sex、password三个字段数据,在联合索引中完全包含,因此可以直接通过联合索引获取到数据。 但如果查询时用*,因为联合索引中不具备完整的一行数据,只能再次转向聚簇索引中获取完整的行数据,因此到这里大家应该也明白了为什么查询数据时,不能用*的原因,这是因为会导致索引覆盖失效,造成回表问题。 ### 索引下推 索引下推是MySQL5.6版本以后引入的一种优化机制,在MySQL5.6版本之后是默认开启的,可以通过命令set optimizer_switch='index_condition_pushdown=off|on';命令来手动管理。 以之前的用户表为例,首先为了更加直观的讲清楚索引下推,因此先再向用户表中增加一条数据: ```sql { ["熊猫","女","6666"] : 1, ["竹子","男","1234"] : 2, ["子竹","男","4321"] : 3, ["1111","男","4321"] : 4, ["竹竹","女","8888"] : 5 } ``` 然后再来看一条SQL语句: ```sql SELECT * FROM `zz_users` WHERE `user_name` LIKE "竹%" AND `user_sex`="男"; ``` 这条SQL会使用联合索引吗?答案是会的,但只能部分使用。由于前面使用的是模糊查询,但%在结尾,因此可以使用竹这个字作为条件在联合索引中查询,整个查询过程如下: - ①利用联合索引中的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」一次回表查询。 ### MRR(Multi-Range Read)机制 Multi-Range Read简称为MRR机制,这也是和索引下推一同在MySQL5.6版本中引入的性能优化措施,那什么叫做MRR优化呢? 一般来说,在实际业务中我们应当尽量通过索引覆盖的特性,减少回表操作以降低IO次数,但在很多时候往往又不得不做回表才能查询到数据,但回表显然会导致产生大量磁盘IO,同时更严重的一点是:还会产生大量的离散IO,下面举个例子来理解。 ```sql 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去聚簇/主键索引中回表查询数据。 ```sql SET @@optimizer_switch='mrr=on|off,mrr_cost_based=on|off'; ``` 可以通过上述这条命令开启或关闭MRR机制,MySQL5.6及以后的版本是默认开启的。 ### Index Skip Scan索引跳跃式扫描 在讲联合索引时,咱们提到过最左前缀匹配原则,也就是SQL的查询条件中必须要包含联合索引的第一个字段,这样才能命中联合索引查询,但实际上这条规则也并不是100%遵循的。因为在MySQL8.x版本中加入了一个新的优化机制,也就是索引跳跃式扫描,这种机制使得咱们即使查询条件中,没有使用联合索引的第一个字段,也依旧可以使用联合索引,看起来就像跳过了联合索引中的第一个字段一样,这也是跳跃扫描的名称由来。 但跳跃扫描究竟是怎么实现的呢?上个栗子快速理解一下。 比如此时通过(A、B、C)三个列建立了一个联合索引,此时有如下一条SQL: ```sql SELECT * FROM `tb_xx` WHERE B = `xxx` AND C = `xxx`; ``` 按理来说,这条SQL既不符合最左前缀原则,也不具备使用索引覆盖的条件,因此绝对是不会走联合索引查询的,但思考一个问题,这条SQL中都已经使用了联合索引中的两个字段,结果还不能使用索引,这似乎有点亏啊对不?因此MySQL8.x推出了跳跃扫描机制,但跳跃扫描并不是真正的“跳过了”第一个字段,而是优化器为你重构了SQL,比如上述这条SQL则会重构成如下情况: ```sql 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去重也无法触发.....,总之有很多限制条件。 其实这个跳跃性扫描机制,只有在唯一性较差的情况下,才能发挥出不错的效果,如果你联合索引的第一个字段,是一个值具备唯一性的字段,那去重一次再拼接,几乎就等价于走一次全表。 最后,可以通过通过```sql set @@optimizer_switch = 'skip_scan=off|on'; ```命令来选择开启或关闭跳跃式扫描机制。当然,该参数仅限MySQL8.0以上的版本,如果在此之下的版本暂时就不用考虑了。
顶部
收展
底部
[TOC]
目录
MySQL索引机制概述
MySQL的索引分类
MySQL索引的创建使用方式
MySQL各索引的优劣分析
MySQL索引失效
MySQL 正确建立索引
MySQL 正确使用索引
相关推荐
MySQL教程
MySQL命令
MySQL事务
MySQL锁机制
MySQL版本特性