笔记（四）：索引优化实战

有索引但是没有走索引的原因之一

mysql的优化器经过的运算发现不走索引查询可能会更快（回表操作，会有两次查询操作），可通过覆盖索引来优化，减少回表操作。

有索引但是没有走索引的原因之二

索引区分度较低，不能过滤出大多数数据，回表查询的成本大于全表扫描的成本，优化器会直接选择全表扫描。

索引下推

• 关键字：非主键索引，模糊查询（最左前缀匹配原则），减少回表次数，减少服务层和存储引擎的io次数；
  索引下推是在5.6版本引入的，主要是为了充分利用查询条件，减少回表次数，举个例子：
  现在有(A1, 1)->(A1, 2)->(B1, 1)联合索引，当执行查询条件a=‘A%’ and b=1时，因为条件a='A%'是模糊查询，虽然它满足最左前缀原则可以走索引，但是后面的条件b=1是不能走索引的。执行情况是这样的：
• 在5.6版本之前，执行过程是先执行a='A%'条件，找到了(A1, 1)，(A1, 2)这两条数据，然后根据这两条数据上的主键回表，找到整条数据之后在执行查询条件b=1来判断数据是否符合条件，最终查询出(A1,1)这条数据，可以看出来这个时候要回表两次；
• 而在5.6版本引入了索引下推之后，因为查询条件a=‘A%’ and b=1的字段都包含在这个索引里面了，即使因为前面是模糊查询后面的查询用不到索引，服务层还是将这两个查询条件一起下推给存储引擎，让存储引擎只读取符合下推条件的数据。所以执行情况是这样的：执行过程前面不变，找到了(A1, 1)，(A1, 2)这两条数据，然后它会直接在这两条数据上执行b=1这个查询条件，来判断数据是否符合条件，最终只会返回(A1, 1)这条数据，这样就减少了回表次数，也减少了服务层和存储引擎之间的交互。
  所以索引下推的概念就是如果只需要通过索引中的字段就能判断一部分条件是否符合，服务层会将这部分条件一起下推到存储引擎中，让存储引擎使用索引找出符合下推条件的数据。
• 注意：索引下推可以使用不止一个索引。
• 问题：为什么范围查询没有使用索引下推优化？

索引排序和文件排序

• 索引排序：因为索引本身就是有序的，所以用索引排序效率肯定是最高的；
• 文件排序：将数据读取到内存中排序，这个内存区域也就是我们所说的sort_buffer，这个区域大小是可以通过sort_buffer_size来调整的，但是一般不建议调整，这块区域是每个Thread独享的，也就是说可能同时存在多个sort_buffer区域。
• 使用文件排序的情况：
  1、where语句和order by语句使用了不同的索引，或者order by语句就使用了不同的索引，虽然索引是有序的，但是不同的索引之间肯定是无序的；
  2、查询出的结果集行数过多，且没有使用到覆盖索引，回表成本大于了使用索引的成本；
  3、同时使用了ASC和DESC，索引的有序是先按第一个字段排序，再按第二个字段排序，次序方向是一样的，和索引的次序方向不一样，肯定是没办法使用到索引的；
  4、where或者order by中的字段使用了函数表达式；
  5、where条件中有范围查询。
• 文件排序（filesort）分为两种：单路排序和双路排序。

单路排序和双路排序

关键字：内存大小，排序速度，回表，sort_buffer，max_length_for_sort_data；

• 上面我们说过文件排序是将数据读取到内存中排序，内存资源是有限的，这样就涉及到了计算机界经常会碰到的问题：时间成本和空间成本之间的平衡。
• 单路排序：一次性读取结果集中的所有字段，然后在sort_buffer中进行排序，排序完成之后直接返回，只会进行一次磁盘io操作，但是这种排序显然会占用更多的内存资源；
• 双路排序：只读取排序的字段和行ID，然后再sort_buffer中排序，排好序之后再通过ID进行回表，也就是说，相对单路排序，双路排序多了一步回表操作，这样就多了一次io操作；
• 显然使用单路排序会更快，但是会消耗更多的内存资源，也就是说限制单路排序的因素很有可能就是内存资源了。MySql是通过比较字段总长度大小和max_length_for_sort_data值（默认是1024字节）来确定使用哪种排序方式的，当字段长度大于max_length_for_sort_data就会使用双路排序，反之则使用单路排序。

索引设计原则

• 代码先行，索引后上
• 联合索引尽量覆盖条件
• 不要在小基数字段上建立索引，区分度不高会导致索引失效
• 长字符串采用前缀索引
• where和order by冲突时优先where
• 基于慢sql查询做优化

索引优化

• 分页查询可以添加条件来过滤前面的数据来提高性能
• 使用覆盖索引和子查询来优化因为查询结果过多而不走索引的情况
• 关联查询查询的优化

驱动表和被驱动表

select * from t1 inner join t2 on t1.a = t2.a;

上面sql的执行计划：先查询t2表的数据，查询出来的结果再根据条件来关联t1表，所以我们在实际使用中应该尽量让t2是小表，t1是大表，这样扫描的行数会大大减少，这就是小表驱动大表原则，当使用内连接优化器使用了大表驱动小表时，可以使用straight_join关键字来指定驱动表。

嵌套循环连接算法（NLJ）

select * from t1 inner join t2 on t1.a = t2.a;

现在假设t1表有10000条数据，t2表有100条数据，且t1表的a字段有索引，这个时候的执行过程：先会扫描t2表的100条数据，然后根据关联条件，因为t1表的a字段是有索引的，所以关联条件会直接走索引，循环t2表的数据来关联t1表的数据，一共扫描100次t1表，所以这个关联查询总共会扫描200次。

基于块的嵌套循环连接算法（BNL）

关键字：join_buffer

select * from t1 inner join t2 on t1.a = t2.a;

假设t1的a字段是没有索引的，这个时候t2的数据和t1数据关联时就不能走索引了，需要全表扫描，如果还是按照上面的执行过程的话，极端情况下需要进行100*100000=100万次磁盘扫描，显然这是不能接受的；
这个时候，我们就会使用BNL，它的执行过程是：

• 将t2表中的数据全部读取到join_buffer中，这是一块内存；
• 然后扫描t1表，取出每一条数据和join_buffer中的t2数据执行关联条件；
• 返回满足join条件的数据。
  这个时候我们整个执行过程中，总的扫描行数为10000+100=10100（t1表和t2表的总数据行），比较次数是10000*100=100万次，大量减少了磁盘扫描的次数。
• 问题：如果t2数据量比较大，join_buffer放不下怎么办？
  join_buffer的大小是由参数join_buffer_size设置的，默认大小是256k，如果放不下t2表的所有数据时，会分段放，先放一部分t2数据到join_buffer中，扫描t1表所有数据进行比较，然后清空join_buffer再读取t2中剩余的数据，再扫描一次t1表。多扫描一次t1表的成本肯定是很大的，所以对于join_buffer大小的设置也是一个优化点。

关联查询优化原则

• 小表驱动大表
  小表的含义是参与到关联中的数据量大小，不是整张表的数据量大小
• 关联字段使用索引

in和exist

select * from A where id in (select id from B);

这条sql执行的过程：先会执行括号里面的内容select in from B，然后再执行id in (…)，根据小表驱动大表的原则，显然当B表的数据比较少时，执行效率会高一些；

select * from A where exists (select 1 from B where B.id = A.id);

这条sql执行的过程：先会扫描A表中的数据，然后再执行括号内的select 1 from B where B.id = A.id，这种显然当A表中的数据少的时候，执行效率会高一些。