本文转载自微信公众号“微科技”，作者汤哥。转载本文请联系微科公众号。大家好，我是汤姆大哥~我们都知道，业务开发涉及到数据库的SQL操作时，一定要审核索引是否命中。否则会进行全表扫描，如果表数据量很大，会非常慢。如果指数被击中怎么办？不会有慢查询吗？殊不知，我们习以为常的常识有时会误导我们！生活太难了！讲这个话题，必须要有一定的技术基础，需要了解B+树的存储结构。如果不是很清楚的话，先看之前的文章，里面详细介绍了面试题：一个mysqlB+树可以存储多少条数据？1.工作准备：建表并创建数据首先创建一张user表，并在id上创建一个主键索引，在user_name上创建一个普通索引。创建表`user`(`id`bigint(20)NOTNULLAUTO_INCREMENT,`user_name`varchar(128)NOTNULLDEFAULT''COMMENT'username',`age`int(11)NOTNULLCOMMENT'age',`address`varchar(128)COMMENT'地址',PRIMARYKEY(`id`),key`idx_user_name`(user_name),)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COMMENT='用户表';启动程序，向user表插入10000条数据。@GetMapping("/insert_batch")publicObjectinsertBatch(@RequestParam("batch")intbatch){for(intj=1;j<=batch;j++){ListuserList=newArrayList<>();for(inti=1;i<=100;i++){Useruser=User.builder().userName("汤姆哥-"+((j-1)*100+i)).age(29).address("上海").build();userList.add(user);}userMapper.insertBatch(userList);}return"success";}2.查询慢在分析原因之前，我们先来了解一下什么是mysql慢查询？怎么定义呢？慢查询定义：MySQL的慢查询日志是MySQL提供的一条日志记录，用于记录MySQL中响应时间超过阈值的语句，具体是指运行时间超过long_query_time值的SQL，会记录在慢查询日志中间。慢查询相关参数：slow_query_log：是否开启慢查询日志，1表示开启，0表示关闭。log-slow-queries：旧版本（5.6以下版本）MySQL数据库慢查询日志存放路径。可以不设置该参数，系统会默认一个默认文件host_name-slow.logslow-query-log-file：新版本（5.6及以上）MySQL数据库慢查询日志存放路径。这个参数可以不设置，系统会默认一个默认的文件host_name-slow.log不使用索引也被阻塞记录到慢查询日志（可选）。默认情况下，slow_query_log的值为OFF，即关闭慢查询日志。可以通过设置slow_query_log的值来开启，如下所示：usesetglobalslow_query_log=1开启慢查询日志只对当前数据库生效，重启MySQL后失效。如果要永久生效，必须修改配置文件。my.cnflong_query_time默认值为10秒，支持二次修改。我们一般在线设置为1秒。如果业务对延迟比较敏感，我们可以根据需要设置一个较低的值。3.开始实验首先看下以下场景的SQL语句执行时的索引命中情况。1、执行explainselect*fromuser;，发现key列为NULL，说明未命中索引，进行全表扫描。2、执行explainselect*fromuserwhereid=10;，发现key列为PRIMARY，说明使用了主键索引。3、执行explainselectuser_namefromuser;，发现key列为idx_user_name，说明使用了二级普通索引。但是实验发现，虽然走的是二级索引，但是行扫描行为是9968，说明走的是全表扫描。性能很差。本文测试只创建了1W条数据。如果线上环境有千万级的数据，可能需要几秒的时间才能响应出结果。如果并发请求数高，容易导致数据库连接无法及时释放，导致客户端无法获取数据库连接，报错。4.打指标还是很慢。我们知道所有的数据都存储在B+索引树中。在执行explainselect*fromuserwhereid>0;时，发现使用了主键索引。mysql优化器根据主键索引找到第一个id>0的值。虽然索引没有了，其实是全表扫描。未命中索引则进行全表扫描，命中索引则可能进行全表扫描。看来命中索引并不是判断SQL性能的唯一标准。其实还有一个重要指标，那就是扫描线数。当表很大时，不仅要注意是否有索引，还要注意索引的过滤性是否足够好。5.表回优化首先在user表中加入user_name和age的联合索引。ALTERTABLE`user`ADDINDEXidx_user_name_age(`user_name`,`age`);执行explainselect*fromuserwhereuser_namelike'Tombrother-1%'andage=29;执行过程：①首先在idx_user_name_age索引树中，找到第一个以Tom-1开头的记录对应的主键id②根据主键id从主键索引树中找到整行记录，并根据年龄判断过滤，如果等于29则保留，否则丢弃。这个过程也叫回表③然后，在idx_user_name_age联合索引树上向右遍历找到下一个主键id④然后执行第二步⑤然后重复第三步和第四步，直到user_name不是namedafterTom-1,thenend⑥返回所有查询结果分析：由于user_name的前缀匹配，idx_user_name_age二级索引中的age部分没有起作用。导致大量的回表查询，性能很差。什么是优化策略：MySQL5.6版本引入了一个IndexConditionPushdownOptimizationhttps://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html优化后执行过程：①首先在idx_user_name_age索引树中，找到以Tom-1开头的第一条索引记录②然后，判断这条索引记录中的age是否等于29。如果是，则回表取整行数据作为后续结果返回；如果不是，则丢弃③在idx_user_name_age联合索引树上向右遍历，重复第二步，直到user_name不以Tom-1开头，则结束④返回所有查询结果与上述过程的区别在于判断是否age等于29是在遍历联合索引的过程中进行的。无需回表判断，大大减少了回表次数，提高了性能。当然，这个优化还是没有绕过最左前缀原则，索引的可过滤性还有提升的空间。这时候就需要引入一个概念，叫做虚拟列。修改表结构：ALTERTABLE`user`adduser_name_firstvarchar(12)generatedalwaysas(left(user_name,6)),addindex(user_name_first,age);执行explainselect*fromuserwhereuser_name_firstlike'Tom-1%'andage=29;通过对比，发现扫描的行数变小了，证明优化是有效的。6、将上次收集的慢SQL写入slow_query_log，结合explain分析是否命中索引，结合扫描行数，有针对性地优化慢SQL。但是需要注意的是，慢SQL日志中也可能存在正常的SQL。可能是当时CPU等系统资源超载，影响了正常SQL的执行速度。简单来说，慢查询和索引没有必然关系。SQL语句的执行效率最终取决于扫描的行数。此外，可以使用虚拟列和联合索引来提高复杂查询的执行效率。
