Mysql(5) - 索引优化与查询优化

一 ， 关联查询优化

Join语句的三种执行方式：SNLJ,INLJ,BNLJ

我们用这条sql举例，假设t1中有n条记录，t2中有m条记录

SNLJ(Simple - Nest - Loop - Join)

最朴素的Join查询方式，执行流程就是：从t1中取出一条数据，拿这条数据的a字段与t2中比较，找到目标行返回，重复此操作。 这样的时间复杂度是最高的，O(N * M)。

INLJ（Index - Nest - Loop - Join）

先看图

可以看到，我们加入了索引，也就是优化了拿t1的数据去匹配t2的操作，在SNLJ中我们需要一条一条的匹配，但是加入了索引之后，这个查询操作的时间复杂度就大大降低了，O(n * logm)。但是增加了一个回表操作

 BNLJ - (Block - Nest - Loop - Join)

在SNLJ中我们是一条一条数据从t1拿出来，现在是一整块数据拿出来，那么自然匹配速度就快了，但是要更多的内存空间

二，子查询优化

        子查询可以帮助用更短的sql解决更复杂的问题，但是它的执行效率不高，原因主要有三个：

1.执行子查询时，mysql需要为内层查询的查询结果建立一个临时表，然后外层查询再从临时表中找到自己需要的记录。查询完毕后要撤销临时表。这对系统资源来说无疑是压力山大。

2.子查询中产生的临时表没有索引，索引查询起来性能很差

3.对于数据量很大的子查询，效率会更低

我们可以通过Join来连接查询以此替代子查询

 

三，排序优化

我们已经对where条件字段创建了索引，为什么还要对Order by也创建索引呢？

1.在数据量大的情况下我们要尽量避免系统排序(file sort)，节省系统资源

2.在无法使用索引的情况下，我们需要对file sort进行优化

filesort的两种排序方式：

单路排序：把需要排序的字段和查询的字段全部拿出来放在sort buffer中进行排序（前提是sort buffer够大）

双路排序：仅将需要排序的字段和可以反映行具体位置的字段取出到sort buffer进行排序，再根据排序的结果回到原表中取数据，这个过程要经历两次的IO操作

所以总体来说单路排序效率要高于双路排序，但是对sort buffer的大小有要求，我们需要尽可能保证其容量足以满足单路排序

四，索引覆盖

什么是索引覆盖？

简单理解就是索引包含了我们查询的所有字段。比如你创建了一个index(a,b,c），而你要查询的字段是a,b，那么此时通过该索引找到的数据就是你所需要的数据，不需要再进行回表操作

索引覆盖的优点：

1.减少了磁盘IO次数

2.将随机IO变为顺序IO

缺点：

索引的维护是需要成本的

五：索引下推（Index Condition Push down）简称ICP

在该sql语句的场景下，假如没有索引下推，那么我们会先根据索引找到符合zipcode找到符合条件的所有行，并且一行一行的回表，去主键索引判断是否符合后面的like条件。  但是有索引下推回怎么样呢？ 它会在根据索引找到所有符合条件的数据后，根据后面的like条件（只能是lastname，因为我们的索引没有包含address）进行一次筛选，留下满足后两个条件的数据，再用这些数据回表。可能一开始有1000条数据，筛选过后就只有100条了，极大地减小了回表的压力和随机IO的次数。

所以索引下推，也可以称为索引条件下推，就相当于是在二级索引和主键索引之间的一道过滤墙，把一些不满足条件的数据先过滤掉，减轻了回表的压力。

六：关于select * 和 select (具体字段)

        建议要查哪些字段就写哪些字段。

        首先，我们有可能不清楚表中到底有多少个字段，万一表非常大，你要查的数据又很少，那就得不偿失了。

        其次，mysql会对*进行解析，解析成对应字段名称，这是比较耗时的操作

        最后，select * 无法使用索引覆盖