MySQL：索引和查询优化_mysql order by 非驱动表-优快云博客

MyISAM和InnoDB的表默认创建的索引类型都是BTree索引。MySQL还支持前缀索引，即对索引字段的前N个字符创建索引。前索引的长度跟存储引擎相关，MyISAM存储引擎的表，索引的前缀长度可以达到1000字节长；而InnoDB的表，索引前缀的最大长度是767个字节。需要注意的是，索引前缀的限制应该以字节为单位进行测量，而CREATE TABLE语句中的前缀长度解释为字符数。

MySQL还支持全文本（FullText）索引，该索引可以用于全文搜索。MyISAM支持全文本索引，但是只限于CHAR、VARCHAR和TEXT列。

MEMORY引擎默认使用HASH索引，但是也支持BTree索引。

本文主要介绍InnoDB的BTree索引。

2、设计索引的原则

最适合索引的列是出现在where子句或连接子句中的列，而不是select中的列；
使用唯一索引：索引的列中列的基数越大，索引效果越好；如果索引列只有两个值，索引的作用就很小了；
使用短索引：如果对字符串列进行索引，只要有可能，就应该指定一个前缀长度。较小的索引设计的磁盘IO较少，索引高速缓存中的块能容纳更多的键值，较短的值比较起来更快；
最左前缀原则；
避免过度索引：索引要占用额外的磁盘空间，并降低写操作的性能；MySQL生成执行计划的时候，要考虑各个索引，创建多余的索引也会给查询优化带来更多的工作；
对于InnoDB存储引擎的表，记录会按一定的顺序保存：如果有主键，则按照主键的顺序保存；如果没有主键，但是有唯一索引，就按照唯一索引的顺序保存；如果两者都没有，则表中会自动生成一个内部列，按照这个列的顺序保存。以上三种情况中，按照主键或者内部列来访问是最快的，所以InnoDB表尽量自己指定主键；如果有多个列可以做为主键，当表中同时有几个列可以作为主键的时候，要选择最常用的作为主键，可以提高查询效率。另外，InnoDB表的普通索引一般都会保存主键的值，索引主键要尽可能选择较短的数据类型，可以减少索引的磁盘占用，提高索引的缓存效果。

3、索引生效

3.1 索引生效的场景

1、匹配全值：对索引中所有列都指定具体值，即对索引中所有列都有等值匹配的条件。type为const；

2、匹配值的范围查询：对索引的值进行范围查询。type为range；

3、匹配最左前缀：仅仅使用索引中最左边的列进行查找。这是B-Tree索引使用的首要原则；

4、仅仅对索引进行查询：即查询的列都在索引的字段中，查询的效率更高。Extra中包含using index，即覆盖索引；

5、匹配列前缀：仅仅使用索引中的第一列，并且只包含索引第一列的开头一部分进行查找。一般指使用like模糊匹配的时候，模糊匹配的部分不能在开头，例如'%abc'是不允许的，但是'abc%'是可以的；

6、索引匹配部分精确而其他部分进行范围匹配；

7、如果列名column_name是索引，那么使用column_name is null就会使用索引；

8、ICP（ Index Condition Pushdown ，索引条件下推，将一些可以在存储引擎进行过滤的条件放到存储引擎层面去过滤，减少存储引擎和MySQL server层之间的数据传输）。

3.2 索引不生效的场景

1、以模糊匹配开头的like查询不能使用B-Tree索引；

2、出现数据类型隐式转换的时候不能使用索引，尤其需要注意的是where中的字符串常量一定要用引号引起来；

3、复合索引的情况下，如果查询条件不包含索引列的最左边一项，即不满足最左原则，则不会使用复合索引；

4、如果MySQL任务使用索引比全表扫描更慢，则不会使用索引；

5、用or分割开的条件，如果or前的条件中有索引列，但是后面的列中没有索引列，那么涉及到的索引都不会被用到。因为or后面的条件中没有索引，那么后面的查询肯定会走全表扫描，所以就没必要再多一次索引扫描增加IO访问了。

3.3 查看索引使用情况

handler_read相关的状态变量可以查询索引的使用情况：show status like 'Handler_read%'

其中，Handler_read_key的值很高，代表索引被经常使用，说明索引带来的性能改善比较高。

3.4 创建索引的一般建议

对于单键索引，尽量选择对当前query过滤性更好的索引；

在选择复合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好；

在选择组合索引的时候，尽量选择能够包含当前query中的where子句中更多字段的索引；

尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

二、高性能的索引策略

1、独立的列

索引不能是表达式的一部分，也不能是函数的一部分，必须作为独立字段使用的时候才能生效。例如对于“where TO_DATE(created_at)”，created_at列是无法使用索引的。

2、前缀索引和索引选择性

2.1 前缀索引

值很长的字段，可以使用前缀索引，以减少索引的存储空间，减少IO次数，提升索引效率。

2.2 索引的选择性

索引的选择性：不重复的索引值和数据表的记录总数的比值。索引的选择性越高查询效率越高。

设计前缀索引长度的时候，要考虑索引的选择性。前缀索引的选择性，要基本接近完整列的索引的选择性。

2.3 确定前缀索引长度的方法

可以通过计算完整性，来选择合适的前缀长度，示例：

2.4 前缀索引的缺点

1）无法用于order by和group by；

2）无法作为覆盖索引。

3、多列索引

MySQL支持索引合并：一定程度上使用表上的多个单列索引来定位指定的行，可以使用在and、or以及and与or混合的场景。

4、索引列顺序

1）不需要考虑排序和分组时，将选择性最高的列放到前面；

2）性能也和查询条件的值的分布有关，需要根据运行频率最高的查询来调整索引列的顺序；

3）where子句中的排序、分组和范围条件等因素，可能对查询的性能造成非常大的影响。

5、聚簇索引

InnoDB的聚簇索引是在同一个结构中保存了B-Tree索引和数据行。因为无法把数据存储在两个不同的地方，所以聚簇索引实际上一个表只能有一个。

InnoDB通过主键聚集数据；如果没有主键，将选择一个唯一的非空列聚集数据；如果也没有唯一的非空列，将隐式定义一个主键来聚集数据。

5.1 聚簇索引的优点

1）可以把相关数据保存在一起，减少IO次数。例如实现实现电子邮箱的时候，可以根据用户ID来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘IO；

2）数据访问更快，索引和数据保存在一起，避免了根据索引查询数据的流程；

3）使用覆盖索引扫描的查询，可以直接使用覆盖索引中的主键值。

5.2 聚簇索引的缺点

1）聚簇索引可以减少IO密集型操作的性能，但是如果数据都在内存中，聚簇索引就没有明显的优势了；

2）插入速度严重依赖插入顺序，按被聚集列进行插入是插入速度最快的，如果不是按被聚集列进行插入，最后在插入后使用optimize table优化一下表；

3）更新聚簇索引列的代价很高，因为InnoDB会强制将每个被更新的行移动到新的位置；

4）主键更新或移动行时，可能会面临“页分裂”的问题，导致数据库占据更多的存储空间；

5）聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候；

6）二级索引的叶子节点中包含了引用行的主键列，这增加了存储成本；

7）二级索引叶子节点保存的不是行的物料位置的指针，而是行的主键值。

5.3 InnoDB聚簇索引的其他特点

1）聚簇索引的每个叶子节点都包含了主键值、事务ID、用于事务和MVCC的回滚指针以及所有的剩余列；

2）即使主键是一个列前缀索引，InnoDB也会包含完整的主键列和剩下的其他列；

3）InnoDB二级索引的叶子节点中存储的不是行指针，而是主键值。这减少了行移动或页分裂时的维护成本，但是会增加存储成本和查询成本；

4）InnoDB的B-Tree索引的非叶子节点中包含了索引列和指向下级节点的指针。

6、覆盖索引

覆盖索引指的是，一个索引包含所有需要查询的字段值。另外，如果覆盖索引包含所有where条件中的列，此时也可以使用覆盖索引，此时会先用覆盖索引过滤掉需要查询的行。

6.1 覆盖索引的优点

1）覆盖索引的字段数远远少于列的长度，所以如果只需要读取索引，可以大大减少数据的访问量，且索引更容易被全部加载到内存中；

2）索引是按照列值顺序存储的，所以对于IO密集型的范围查询会比随机从磁盘读取性能高得多；

3）由于InnoDB的聚簇索引，覆盖索引对InnoDB表尤其有用，因为可以避免第二次去聚簇索引中查询数据的过程。

6.2 延迟关联

可以增加子查询，并在子查询中实现覆盖索引来找到相符的唯一id，然后再在外部查询中根据查到的id去检索。这种延迟对列的访问的方式，叫做延迟关联。

主键天然适合做这个唯一id。

7、使用索引扫描做排序

对于非覆盖索引，按索引顺序读取顺序比顺序全表扫描慢：每扫描一条索引记录就要回表查询对应行，这是随机IO，所以索引的设计应该尽量同时满足排序和查找行；

索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方向都一样时，MySQL才能使用索引来对结果进行排序；

如果前面的列查询的是常量，那么不满足最左前缀要求的索引也可能用于排序；

对驱动表可以直接排序，对非驱动表的字段排序，需要对循环查询的合并结果（临时表）进行排序；

8、冗余索引，重复索引和未使用的索引

重复索引：指的是在相同的列上，用相同的顺序创建多个索引。重复索引应该被避免。

冗余索引：创建了索引(A, B)，再创建索引A就是冗余索引；对已有索引A，扩展成(A, ID)也是冗余索引，因为InnoDB的主键列包含在二级索引中。有时候冗余索引还是有必要的，因为扩展后的索引可能会太大。

查找冗余索引和重复索引的方法：查询INFORMATION_SCHEMA表；或者使用外部工具，例如common_schema等

查询INCORMATION_SCHEMA.INDEX_STATISTICS访问索引的使用频率，对于基本不会被使用的索引，应该尽量移除。

9、索引和锁

InnoDB只有在访问行的时候才会对行加锁，而索引能检索InnoDB访问的行数，从而减少加锁的行的数量。

MySQL5.1及以后，InnoDB可以在过滤掉行之后就释放这些行的锁。

即使使用了索引，InnoDB也可能锁住不需要的数据；但是如果全表扫描，将会更糟糕，因为锁住所有的行；

InnoDB在二级索引上使用共享锁，访问主键索引使用排他锁。对于select for update语句，由于要使用排他锁，所以该查询不可能使用覆盖索引。

10、其他索引优化技巧

使用范围查询的列尽量放到复合索引的后面，以尽可能多的使用索引；
可以将合适的范围查询替换为in语句，这样后面的行也可以使用复合索引，因为多个等值查询列后面的列依然可以使用索引；
如果where子句中包含多个in查询，此时组合数是多个in查询的可能值数量的乘积，组合数可能会非常多。MySQL会在组合数超过一定数量后就不再执行计划评估，这可能导致MySQL不能很好的利用索引，所以要控制组合的数量；
尽量避免多个范围查询，因为会导致第一个范围查询后的索引无法被使用；
大数据量分页查询的性能问题解决方案：技术层面：反范式化、预先计算、缓存；业务：大部分用户并不关心搜索太靠后的内容，可以限制搜索的最大页数；

三、查询优化技巧

衡量查询开销的三个基本指标：响应时间、扫描的行数、返回的行数。

1、慢查询优化

查询性能低下最基本的原因是访问的数据过多，大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。可以通过如下两个步骤解决低效的查询：

确认应用程序是否在检索大量超过需求的数据；
确认MySQL的服务层是否在分析大量超过需求的数据行。

使用where条件的三种情况，从好到坏依次有：

在索引中使用where条件过滤不匹配的记录，这是在存储引擎完成的；
使用索引覆盖扫描来返回记录，直接从索引过滤不需要的记录（Using index），并返回结果。这是在MySQL的服务层完成的，但是不需要再回表查询数据；
从数据表中返回数据，然后在MySQL服务层进行过滤（Using where）。

2、重写复杂查询

2.1 拆分大查询

MySQL从设计上让连接和断开连接都很轻量级，在返回一个小的查询结果方面很高效；现代的网络一般也可以轻松负担大量的查询，所以在一定情况下，将大查询分解成多个小查询更有利。

2.2 切分查询

将大查询切分成很多小查询，每次只对一部分数据执行操作。例如对大量数据执行写操作，会导致一次锁住很多数据，阻塞其他小的查询操作等，可以拆分成多个语句，每次只操作一部分。

2.3 分解关联查询

很多时候可以把关联查询拆分成多个单表查询。拆分关联查询的好处：

缓存效率更高：如果拆分后的某个表很少改变，那么这个表的查询就可以利用缓存；
检索锁竞争：每次查询只对单个表加锁，可以减少锁竞争；
降低表的耦合性：表的关联在应用程序中去实现，可以降低数据库拆分的难度；
提升查询效率：使用in代替关联查询，可以有序的去加载in函数中的数值，避免关联的无序导致的随机查询；
减少冗余数据的访问：在应用程序中进行关联，数据只需要访问一遍；而关联查询可能会重复访问一部分数据。

3、MySQL对查询的部分优化

3.1 查询优化器的部分优化

子查询优化：子查询的效率往往比较低，因为子查询会创建和删除临时表；
or优化：有些or操作可以优化成in函数，对于IN()函数，MySQL会先给列表中的数据进行排序，然后通过二分查找确定列表中的值是否满足条件，时间复杂度是O(log n)，比or操作的O(n)性能要高；

3.2 关联查询优化

1、MySQL处理union关联

先将一系列单个查询放入到临时表，再重新读取临时表数据来完成union查询。

2、MySQL执行关联的策略

嵌套循环关联操作。即先在一个表中循环取出单条数据，再嵌套循环到下一个表中寻找匹配的行，然后根据各个表匹配的行，返回查询中需要的各个列。MySQL会尝试在最后一个关联的表中找出所有需要的行，完成后返回上一层次关联表，查找所有关联的行，依次类推。

对于关联主表，MySQL通常会选择预估会读取最少页的表作为关联主表，这样会让查询进行更少的嵌套循环和回溯。

3.3 关联查询执行器

关联优化器会尝试在所有的关联顺序中选择成本最小的来生成执行计划树。如果可能，优化器会遍历每一个表然后逐个做嵌套循环计算每一棵的成本。

对于n表关联，需要检查n的阶乘种关联顺序。当“搜索空间”非常大时，优化器无法逐一评估，会选择“贪婪”搜索方式查找最有的关联顺序。n的门限是optimizer_search_depth。“贪婪”搜索指的是查在当前看来是最好的选择。

有些查询的顺序不能随便安排，例如左查询、一些子查询等，因为后面的查询需要依赖前面的执行结果。MySQL会根据这些规则减小搜索空间，大大减少要扫描的执行计划数量。

3.4 排序优化

1.文件排序

文件排序：Filesort，指的是需要MySQL字节执行排序操作的情况，并不一定都会借助文件，也可能是在内存中实现的。

对于文件排序，如果需要排序的数量少于排序缓冲区，MySQL使用内存进行“快速排序”；如果内存不够，MySQL会先将数据分块，然后分别对每个部分进行快速排序，然后将排序结果持久化到磁盘，然后将排好序的块进行合并，最后返回排序结果。

2、MySQL的排序策略

两次传输排序：读取行指针和待排序字段，对其进行排序，然后根据排序结果读取需要的数据行。这需要两次数据传输，即从数据表中读取两次数据。由于第二次会产生大量的随机IO，所以性能会非常慢。

单次传输排序：先读取所有列，然后再根据给定的列进行排序，最后直接返回排序结果。由于只有一次顺序IO读取数据，所以性能好了很多。但是如果返回的列非常多、非常大，会占用很多空间，可能会需要更多的排序块进行合并。

max_length_for_sort_data：当查询需要所有列的总长度不超过max_length_for_sort_data时，MySQL选择单次传输排序。

3、注意事项

需要注意的是，MySQL在进行文件排序时会给每一条记录分配足够长的定长空间，例如为varchar分配完整长度；如果使用UTF-8字符集，则会为每个字符预留三个字节。

4、关联查询排序

如果排序字段来自第一个表，则处理第一个表时进行排序，显示Using filesort，MySQL在处理第一个表的数据时，就进行文件排序；

如果来自其他表，则关联完成后再进行文件排序，此时会提示Using temporary; Using filesort，MySQL会先将数据放到临时表中，然后在所有关联都结束后，再进行文件排序。

如果使用了LIMIT子句，则MySQL会根据实际情况抛弃不满足条件的结果，再进行排序。

3.5 返回结果给客户端

MySQL返回结果集是一个逐步的过程，例如对于关联操作，开始生成第一条结果时，MySQL就可以开始返回结果集了。

需要注意的是，具体什么时候开始返回结果集，会受到参数的影响，例如SQL_BUFFER_RESULT。

4、特定类型查询优化

4.1 count()查询

count()的作用：统计列的值的数量，要求这个列值不是null；或者统计结果集的行数。如果括号中写的是某个列或列的表达式，那么会统计这个列不是null的行数。对于count(*)，count()函数会忽略所有的列，而直接统计所有的行数。

count()意味着需要扫描大量的行，可用的优化手段：

使用近似值，去掉一些使查询严重复杂的查询条件；
使用索引覆盖扫描；
修改应用框架，增加统计表。快速、精确和简单实现，三者用于只能满足其二。

4.2 关联查询优化

确保ON或Using子句的列要有索引。所以在创建索引的时候，需要考虑到关联的顺序；
确保group by和order by中的表达式只涉及到一个表中的列。这样MySQL才能使用索引来优化这个过程。

4.3 优化子查询

子查询尽量使用关联查询替代，因为子查询会涉及到临时表的创建和删除，且临时表中是不能使用索引的。

4.4 优化GROUP BY和DISTINCT

当MySQL无法使用索引时，GROUP BY有两种策略来实现：使用临时表或者文件排序来做分组。可以通过SQL_BIG_RESULT和SQL_SMALL_RESULT来让优化器按希望的方式运行。

使用标识列分组的效率会比其他列更高，标识列就是选择性更高的列。

如果没有通过order by子句显式指定排序列，当查询使用group by子句时，结果会自动按分组字段进行排序。如果不关心结果集的顺序，而这种默认排序又导致了需要文件排序，可以使用order by null避免文件排序。

4.5 优化LIMIT分页

使用索引覆盖扫描：可以减少IO次数；
limit分页转换成已知位置的查询：例如先找到待分页字段在本次分页结果中的边界值，然后直接用这个边界值的范围查询，或者边界值内部的多个值的in查询，来替换分页查询；
记录上次分页查询的结果，并从下一个位置开始；
将第几页替换成下一页：例如查询10~20行时，查询出第21条，如果存在，就显示下一页，如果没有，就不显示下一页；
先获取较多的数据并进行缓存：例如直接换成1000条数据，然后从这1000条数据中获取分页结果。