参考文章

• 第10章_索引优化与查询优化.pdf · 资料文件 · 语雀

  1 索引优化的原则

  用不用索引，最终都是MySQL的优化器说了算。优化器是基于cost开销(CostBaseOptimizer)，它不是基于规则(Rule-Based0ptimizer )，也不是基于语义，而是怎么样开销小就怎么来。另外，SQL语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。

  主键插入要有序

  建议：让主键具有 AUTO_INCREMENT ，让存储引擎自己为表生成主键，而不是我们手动插入 。但是核心表建议不用AUTO_INCREMENT ，如果是分布式的情况下，可以使用雪花算法。
  对于一个使用InnoDB存储引擎的表来说，在我们没有显式的创建索引时，表中的数据实际上都是存储在聚簇索引的叶子节点的。而记录又是存储在数据页中的，数据页和记录又是按照记录主键值从小到大的顺序进行排序，所以如果我们插入的记录的主键值是依次增大的话，那我们每插满一个数据页就换到下一个数据页继续插，而如果我们插入的主键值忽大忽小的话，就比较麻烦了，假设某个数据页存储的记录已经满了，它存储的主键值在1~100之间：
  
  如果此时再插入一条主键值为 9 的记录，那它插入的位置就如下图：
  
  可这个数据页已经满了，再插进来咋办呢？我们需要把当前页面分裂成两个页面，把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味性能损耗 ！所以如果我们想尽量
  避免这样无谓的性能损耗，最好让插入的记录的主键值依次递增 ，这样就不会发生这样的性能损耗了。

  全值匹配我最爱

  如果是复合索引，则where根据索引的顺序完整的展示。

  create index idx_nkmobileno on users(nickname,mobileno,email);
  select * from users where nickname='xx' and mobileno='yy'and email='zz';

  最佳左前缀法则

  如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列。原理分析：索引的B+tree的存储结构。
  MySQL可以为多个字段创建索引，一个索引可以包括16个字段。对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。如果查询条件中没有使用这些字段中第1个字段时，多列(或联合)索引不会被使用。

范围右边索引会失效

范围条件右边的列索引失效，范围包括：like,>=,<=,in,or等。
原理：由于age使用的范围，其本身即为range扫描，后续的判断则就是在前面数据基础上的全部扫描。存储引擎不能使用索引中范围条件右边的列。
开发建议：应用开发中范围查询，例如：金额查询，日期查询往往都是范围查询。在创建的联合索引中，务必把范围涉及到的字段写在最后。

案例 create index idx_nkmobileno on users(nickname,age,mobileno);

• 语句1：EXPLAIN select * from users u where u.nickname='xx' and u.age>15;

nickname和age索引会生效

• 语句2：EXPLAIN select * from users u where u.nickname='xx' and u.age>15 and u.mobileno='zz';

nickname和age索引会生效，mobileno不会生效。即索引如果在范围的后面，则匹配不到，key_len和上文一样，则代表mobileno的索引是没有匹配到的

索引禁止做计算

不在索引上做任何操作：包括计算、函数、自动或手动的类型转换，会导致索引失效而转向全表扫描。

create index idx_nkmobileno on users(nickname,mobileno,email);
EXPLAIN select * from users u where left(u.nickname,2)='xx';

索引不做类型转换

不在索引上做任何操作：包括计算、函数、自动或手动的类型转换，会导致索引失效而转向全表扫描。
字符串不加单引号会出现隐式转化，索引会失效（如果是int类型的，加上’’则可以，但是不建议这样做）
EXPLAIN select * from users where nickname='123';

EXPLAIN select * from users where nickname=123;

数据库和表的字符集统一使用utf8mb4

统一使用utf8mb4( 5.5.3版本以上支持)兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同的字符集进行比较前需要进行转换会造成索引失效。

不等于号要小心(!=或<>)

使用不等于!=或<>可能会导致无法使用索引

案例 EXPLAIN select * from users u where u.nickname!='xx';

note如果是数值类型的可以使用!=或<>，如下使用到了age的索引。

create index idx_nkmobileno on users(nickname,age,mobileno);
EXPLAIN select * from users u where u.nickname='xx' and age!=16;

如果是字符串类型，使用<>也可以使用索引，核心是B+树能不能走通。

null判断要小心

is null，is not null可能无法使用索引。
开发建议：最好在设计数据表的时候就将字段设置为 NOT NULL 约来，比如你可以将INT类型的字段，默认值设置为0。将字符类型的默认值设置为空字符串(‘’)。//这样可以在使用的时候不用加where xx is null的判断。
拓展：同理，在查询中使用not like 也无法使用索引，这会导致全表扫描。

案例 create index idx_email on users(email); EXPLAIN select * from users u where u.email is not null;

note：如果某个字段本身就是not null，则使用where is null，则会是ref===========>在B+Tree下直接定位到那条数据了。

like百分写右边或覆盖

案例

• EXPLAIN select * from users where nickname like '%王%';—索引失效

• EXPLAIN select * from users where nickname like '王%';—索引为range

• EXPLAIN select * from users where nickname like '%王';—索引失效

• EXPLAIN select * from users where nickname like '王'; —索引为range
  

问题：解决like ‘%str%’时索引不被使用的方法？
答案：使用覆盖索引，原理：使用了index，只扫描索引表，不扫描聚集索引的其他数据。

案例 show index from users; 案例一：EXPLAIN select nickname,age,mobileno from users where nickname like '%王%'; 使用到覆盖索引 案例二：EXPLAIN select nickname,age,mobileno from users where nickname like '%王%'; 使用到覆盖索引 案例三：EXPLAIN select userid from users where nickname like '%王%'; 使用到覆盖索引—注：由于userid为表的主键，会存储在非聚集索引的叶子节点上(以用于回表到聚集索引)故此处直接index查询非聚集索引的表即可 案例四：如果索引为nickname、age、mobileno，查询的时候加了另外的列，则索引会失效

or的使用要小心

在WHERE子句中，如果在OR前的条件列进行了索引，而在OR后的条件列没有进行索引，那么索引会失效。也就是说，OR前后的两个条件中的列都是索引时，查询中才使用索引。
因为OR的含义就是两个只要满足一个即可，因此只有一个条件列进行了索引是没有意义的，只要有条件列没有进行索引，就会进行全表扫描，因此索引的条件列也会失效。
如果有两个字段：age、name上都有索引，则age=10 or name=’xx’会使用到索引，Extra值为index_merge，简单来说index_merge就是对age和name分别进行了扫描，然后将这两个结果集进行了合并，从而遥免了全表扫描。

★使用总结

简易口诀
全值匹配我最爱，最左前缀要遵守
带头大哥不能死，中间兄弟不能断
索引列上少计算，范围之后会失效，
Like百分写右边，覆盖索引不写星，
不等空值还有or，索引失效要少用
VARCHAR引号不可丢，掌握索引也不难。

SIMPLE：带中范, lovn(like/or/null/varchar)
带头大哥不能死，中间兄弟不能断，范围之后全失效。
不等空值likeor，varchar类型少引号，这些使用很奇妙。

一般性建议

• 对于单列索引，尽量选择针对当前query过滤性更好的索引。
• 在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
• 在选择组合索引的时候，尽量选择能够包含当前query中的where子句中更多字段的索引。
• 在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面。

覆盖索引的优化及限制
覆盖索引是一种非常强大的工具，能大大提高查询性能，只需要读取索引而不需要读取数据，有以下优点：
1、索引项通常比记录要小，所以MySQL访问更少的数据。
2、索引都按值得大小存储，相对于随机访问记录，需要更少的I/O。
3、数据引擎能更好的缓存索引，比如MyISAM只缓存索引。
4、覆盖索引对InnoDB尤其有用，因为InnoDB使用聚集索引组织数据，如果二级索引包含查询所需的数据，就不再需要在聚集索引中查找了。
限制
1、覆盖索引也并不适用于任意的索引类型，索引必须存储列的值。
2、Hash和full-text索引不存储值，因此MySQL只能使用BTree。
3、不同的存储引擎实现覆盖索引都是不同的，并不是所有的存储引擎都支持覆盖索引。
4、如果要使用覆盖索引，一定要注意SELECT列表值取出需要的列，不可以SELECT * ，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

2 Join关联查询优化

结论1：对于内连接来说，查询优化器可以决定谁来作为驱动表，谁作为被驱动表出现。
结论2：对于内连接来讲，如果表的连接条件中只能有一个字段有索引，则有索引的字段所在的表会被作为被驱动表。
结论3：对于内连接来说，在两个表的连接条件都存在索引的情况下，会选择小表作为驱动表。小表驱动大表

优化建议

1. 保证被驱动表的JOIN字段已经创建了索引
2. 需要JOIN 的字段，数据类型保持绝对一致。
3. LEFT JOIN 时，选择小表作为驱动表， 大表作为被驱动表 。减少外层循环的次数。
4. INNER JOIN 时，MySQL会自动将 小结果集的表选为驱动表 。选择相信MySQL优化策略。
5. 能够直接多表关联的尽量直接关联，不用子查询。（减少查询的趟数）
6. 不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用 JOIN 来代替子查询：衍生表建不了索引

详见：4 DQL：数据查询下的JOIN关联查询。

3 SubQuery子查询优化

概述：MySQL从4.1版本开始支持子查询，使用子查询可以进行SELECT语句的嵌套查询，即一个SELECT查询的结果作为另一个SELECT语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。
子查询是MySQL的一项重要的功能，可以帮助我们通过一个SQL语句实现比较复杂的查询。但是，子查询的执行效率不高。原因：

1. 执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。这样会消耗过多的CPU和IO资源，产生大量的慢查询。
2. 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。
3. 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

在MySQL中，可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能就会更好。

4 OrderBy排序优化

问题背景
问题：在WHERE条件字段上加索引，但是为什么在ORDER BY字段上还要加索引呢？
回答：在MySQL中，支持两种排序方式，分别是FileSort和Index排序

• lndex排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高。
• FileSort排序则一般在内存中进行排序，占用CPU较多。如果待排结果较大，会产生临时文件I/O到磁盘进行排序的情况，效率较低。

扩展：关于FileSort的排序算法

1. 双路排序（慢）

MySQL 4.1之前是使用双路排序 ，字面意思就是两次扫描磁盘，最终得到数据。

• 从磁盘读取order by排序字段 ，加载到内存buffer中进行排序。
• 根据上面已经排好序的列，再从磁盘中加载其他select字段值。

缺点：取一批数据，要对磁盘进行两次扫描，众所周知，I/O是很耗时的

1. 单路排序 （快）

从磁盘读取查询需要的 所有列 ，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出。
它的效率更快一些，避免了第二次读取数据。并且把随机I/O变成了顺序I/O，但是它会使用更多的空间， 因为它把每一行都保存在内存中了。

1. 结论及引申出的问题

• 由于单路是后出的，总体而言好过双路
• 但是用单路有问题，优化策略
  • 尝试提高 sort_buffer_size
  • 尝试提高 max_length_for_sort_data。
  • Order by 时select * 是一个大忌。最好只Query需要的字段。

优化建议

1. SQL中，可以在WHERE子句和ORDER BY子句中使用索引，目的是在WHERE子句中避免全表扫描，在ORDER BY子句避免使用FileSort排序。当然，某些情况下全表扫描，或者FileSort排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。
2. 尽量使用Index完成ORDER BY排序。如果WHERE和ORDER BY后面是相同的列就使用单索引列；如果不同就使用联合索引（即覆盖索引）。
3. 无法使用Index时，需要对FileSort方式进行调优。
4. 多个索引同时存在，mysql自动选择最优的方案，但是， 随着数据量的变化，选择的索引也会随之变化的。所以如果当【where范围条件】和【group by 或者 order by】的字段出现二选一时，优先观察条件字段的过滤数量：如果where范围过滤的数据足够多，而需要order/group排序分组的数据并不多时，优先把索引放在where范围字段上。反之，亦然。

优化案例

综合实测

//背景：创建2个普通的非聚集索引 create index idx_age_classid_stuno on student (age,classid,stuno); create index idx_age_classid_name on student (age,classid,name); //试验一： order by时不加limit，索引失效。 原因：即使已经有索引了也不会用，因为会回表。如果再查询的时候还得先查普通索引，查到后再回表，那么还不如直接用using filesort。 explain select sql_no_cache from student order by age,classid; //没用上 explain select sql_no_cache age,classid,name,id from student order by age,classid; //没用上 explain select sql_no_cache from student order by age,classid limit 10; //用上了索引 //试验二： order by时：顺序错误、方向反时，索引失效 ////索引顺序不一样 explain select from student order by age,classid,stuno limit 10; //匹配上 explain select from student order by age,classid limit 10; //匹配上 explain select from student order by age limit 10; //匹配上 explain select from student order by classid,name limit 10; //没匹配上 explain select from student order by classid limit 10; //没匹配上 ////规则不一样 explain select from student order by age desc,classid,stuno limit 10; //没匹配上。MySQL8.0之后可以设置倒序的索引。 explain select from student order by age asc,classid desc limit 10; //一升一降，没匹配上 explain select from student order by age desc,classid desc limit 10; //两个都逆，匹配上了

5 GroupBy分组优化

• group by使用索引的原则几乎跟order by—致，group by即使没有过滤条件用到索引，也可以直接使用索引。
• group by先排序再分组，遵照索引建的最佳左前缀法则。
• 当无法使用索引列，增大max_length_for_sort_data和sort_buffer_size参数的设置。
• where效率高于having，能写在where限定的条件就不要写在having中了。
• 减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
• 包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。

  6 Limit分页查询优化

  一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是limit 2000000,10，此时需要MySQL排序前2000010记录，仅仅返回2000000~2000010的记录，其他记录丢弃，查询排序的代价非常大。

案例：EXPLAIN SELECT * FROM student LIMIT 2000000, 10;
策略1：在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。
explain select * from student t ，(select id from student order by id limit 2000000,10) a where t.id = a.id; //或使用join

策略2：该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询 。

7 索引的选择优化

• 覆盖索引是一个常用的索引优化手段，详见：14 索引的创建和设计原则

• 索引下推：14 索引的创建和设计原则

  8 其他查询优化

  in和exists的选择

  详见：4 DQL：数据查询

  count的性能分析

  详见：2 变量&函数&流程控制下的聚合函数count

  limit的性能分析

• 如果SQL语句针对的是会扫描全表的，同时可以确定结果集只有一条，那么加上LIMIT 1的时候，当找到一条结果的时候就不会继续扫描了，这样会加快查询速度。

• 如果数据表已经对字段建立了唯一索引，那么可以通过索引进行查询，不会全表扫描的话，就不需要加上LIMIT1了。

  select * 性能分析

  按需索取，尽量使用覆盖索引，减少select 。
  在表查询中，建议明确字段，不要使用作为查询的字段列表，推荐使用SELECT <字段列表>查询。
  原因

• MySQL在解析的过程中，会通过查询数据字典将*按序转换成所有列名，这会大大的耗费资源和时间。

• 无法使用覆盖索引
• 传输数据变大

• 如JoinBuffer、OrderBy的内存Buffer会加大。

  多使用COMMIT(代码侧）

  只要有可能，在程序中尽量多使用COMMIT，这样程序的性能得到提高，需求也会因为COMMIT所释放的资源而减少。
  COMMIT所释放的资源：

• 回滚段上用于恢复数据的信息

• 被程序语句获得的锁
• redo/undo log buffer中的空间。
• 管理上述3种资源中的内部花费