前几天有同事跟我说：“我写了一个SQL，这个SQL很简单，但是查询速度很慢，针对查询条件创建索引，但是索引不行了，能帮我看看有没有办法优化一下？”。我对他提供的案例进行了优化，整理了优化过程。我们先来看看优化前的表结构、数据量、SQL、执行计划、执行时间等。1.表结构：CREATETABLE`t_order`(`id`bigint(20)unsignedNOTNULLAUTO_INCREMENT,`order_code`char(12)NOTNULL,`order_amount`decimal(12,2)NOTNULL,PRIMARYKEY(`id`),UNIQUEKEY`uni_order_code`(`order_code`)USINGBTREE)ENGINE=InnoDBAUTO_INCREMENT=1DEFAULTCHARSET=utf8;隐藏了一些不相关的字段后，可以看到表很简单，在order_code上创建了唯一索引uni_order_code。2、数据量：316977的数据量还是比较小的，但是如果SQL够差的话，查询也会很慢。3.SQL:selectorder_code,order_amountfromt_orderorderbyorder_codelimit1000;哇，SQL是够简单了，但是有时候越简单越难优化。4.执行计划：idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMPLEt_orderALLNULLNULLNULLNULL316350使用filesort全表扫描和文件排序注定查询很慢！那为什么MySQL不使用索引（uni_order_code）扫描来完成查询呢？因为MySQL认为在这种场景下使用索引扫描并不是一个完美的结果。我们先看执行时间，再分析为什么没有利用索引扫描。5、执行时间：260ms确实，执行时间太长了。如果表数据量继续增长，性能会越来越差。下面分析一下为什么MySQL使用全表扫描和文件排序而不是索引扫描和索引排序：1、全表扫描和文件排序：虽然是全表扫描，但是扫描是顺序的（不管是机械硬盘还是SSD）Sequential读写性能高），数据量也不是特别大，所以这部分消耗的时间应该不会特别多，主要消耗应该在排序上。2、使用索引扫描和索引顺序：uni_order_code是二级索引，(order_code,id)保存在索引上。每次扫描一个索引都需要根据索引上的id定位（随机IO）到数据行读取order_amount。只需1000次随机IO即可完成查询，机械硬盘随机IO效率极低（机械硬盘每秒寻址数百次）。根据我们自己的分析，选择全表扫描相对来说比较好。如果把limit1000改成limit10，执行计划就完全不一样了。既然已经知道了随机IO导致索引无法使用，那么有没有办法杜绝随机IO呢？是的，覆盖索引。让我们看看使用覆盖索引优化后的索引、执行计划和执行时间。1.创建索引：ALTERTABLE`t_order`ADDINDEX`idx_ordercode_orderamount`USINGBTREE(`order_code`ASC,`order_amount`ASC);创建复合索引idx_ordercode_orderamount(order_code,order_amount)，将select列order_amount放入索引。2、执行计划：idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMPLEt_orderindexNULLidx_ordercode_orderamount42NULL1000Usingindex执行计划显示查询将使用覆盖索引，只扫描1000行数据。查询性能应该很好OK3.执行时间：13ms从执行时间来看，SQL的执行时间增加到原来的1/20，达到了我们的预期。总结：覆盖索引就是不读取数据行，只从索引中获取选中的数据列。换句话说，查询列必须被内置索引覆盖。索引字段不仅包括查询列，还包括查询条件、排序等。要写出性能好的SQL，不仅要学习SQL，还要了解数据库执行计划、数据库执行过程、索引数据结构等。
