第六章 查询性能优化.md

第六章 查询性能优化

查询的生命周期大致可以按照顺序来看：从客户端，到服务器，然后在服务器上执行解析，生成执行计划，执行，并返回数据给客户端。其中的“执行”包括了大量未检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组等。

查询花费的时间包括：网络、CPU计算、生成统计信息和执行计划、锁等待（互斥等待）等操作，尤其是向底层存储引擎检索数据的调用操作，这些调用操作需要在内存操作、CPU操作和内存不足时导致的IO操作上消耗时间。根据存储引擎不同，可能还会产生大量的上下文切换以及系统调用。

优化数据访问

1. 不向数据库请求不需要的数据：
   • 是否向数据库请求了不需要的数据：多余的数据返回应用程序，应用程序不需要，且会为MySQL服务器带来额外的负担，并增加网络开销
   • 是否需要返回全部的列
   • 缓存一些数据
2. mysql是否扫描了额外的记录：响应时间、扫描的行数、返回的行数
   • 响应时间：包括服务时间和排队时间，服务时间是指数据库处理这个查询真正花费的时间。排队时间指服务器因为等待某些资源而没有真正执行查询的时间，可能是等待IO操作完成，也可能是等待行锁等
   • 理想的情况下扫描的行数和返回的函数应该是相同的，但是当关联查询时，服务器必须扫描多行才能生成结果集中的一行
   • 访问类型定义了从几行到一行的过程：EXPLAIN中的type列反映了访问类型，访问类型分为很多种：全表扫描–>索引扫描–>范围扫描–>唯一索引查询–>常数引用，速度到从慢到快
3. mysql使用where判断大致有三个地方：
   • 在索引中使用WHERE条件来过滤不匹配的记录。这是存储引擎层完成的
   • 使用索引覆盖扫描（在Extra列出现了Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务层完成的，但无须再回标查询记录
   • 在数据表中返回数据，然后过滤不满足条件的记录（在Extra列中出现Using where）。在MySQL服务层完成，MySQL需要从数据表读出记录然后过滤。
4. 查看执行计划中的rows，一般表示扫描了多少行，如果扫描的行数与返回的行数相差很大，可以通过如下方式优化：
   • 使用覆盖索引
   • 改变库表结构，使用单独的汇总表
   • 重写查询语句，使用更优化的方式执行查询

重构查询

1. 一个查询还是多个查询：在通用服务器上，可以运行每秒超过10万个查询，即使是一个千兆网卡也能轻松满足每秒超过2000次的查询。所以运行多个小查询已经不是大问题了

2. 切分查询，比如定期清理大量数据时，如果一个大查询语句，可能一次就会锁住很多数据，此时可以用多个小delete语句。比如一次删除10000条数据，执行多次

3. 分解关联查询：每次对一个表进行查询，然后将结果在应用程序中关联

   • 可以使得缓存的效率更高，比如数据库已经缓存了一些单表查询语句的数据了
   • 执行单个查询可以减少锁的竞争
   • 在应用层做关联，可以更容易对数据库进行拆分
   • 查询本身效率也会提高
   • 相当于在应用中做了哈希关联，某些场景下效率更高

查询执行基础

当我们向mysql发送请求时，mysql做了什么：

1. MySQL客户端/服务器通信协议

   • 半双工，意味着在任何一个时刻，要么是由服务器向客户端发送数据，要么是客户端向服务器发送数据
   • 客户端使用一个单独的包将查询发送给服务器，这也是为什么当查询语句长是，可以设置max_allowed_packet
   • 服务器使用多个数据包向客户端发送数据，客户端必须完整的接收整个结果，而不能粗暴的断开连接
   • 查询状态：对于一个mysql连接，任何时候都有一个状态。使用show Full processlist 可以查看状态。状态有sleep、query、Locked、Analyzeing and statistics、copying to tmp table [on disk]、Sorting result、Sending data。如果有大量的时间消耗在一个环节，就可能出现了问题

2. 查询缓存

   在解析语句之前，如果查询缓存是打开的，那么会优先检查这个查询是否会命中查询缓存中的数据。如果命中，还会去检查一次用户权限，如果权限没有问题，则之间返回数据

3. 查询优化处理：将一个sql语句转换为一个执行计划，然后再按照这个执行计划和存储引擎进行交互。包括多个子阶段：解析SQL，预处理、优化SQL执行计划

   • 语法解析器与预处理：将SQL语句进行解析，并生成一颗“解析树”，然后校验是否有语法错误，校验表与数据列是否存在，解析名字和别名是否有歧义等。最后验证权限

   • 查询优化器：mysql使用基于成本的优化器，最小单位是随机读取一个4K数据页的成本。使用SHOW STATUS LIKE 'Last_query_cost’查询上一个语句的成本。这个成本值通过一系列的统计信息获取的

   • 导致mysql选择错误执行计划原因：统计信息不准确、执行计划中的成本估算不等同于实际执行的成本、mysql基于成本模型选择最优执行计划，但是这并不一定是时间最短、并不总是基于成本优化（比如全文索引的MATCH()子句）、MySQL不考虑不受其控制的操作成本（比如存储过程或用户自定义函数）、有时可能无法估算所有可能的执行计划

   • mysql能够处理的优化类型：

     • 重新定义关联表的顺序、
     • 将外链接转换为内连使用等价变换规则、
     • 优化COUNT()、MIN()、MAX()：比如查找MAX()时，直接取b-tree最右侧值
     • 预估并转换常数表达式，先将一些能转换为常数的值转换为常数
     • 覆盖索引扫描
     • 子查询优化
     • 提前终止查询，比如LIMIT，或者是判断不存在值
     • 等值传播：select a.column1 from a inner join b using(column1) where a.column1>500
     • 列表in：会先将其中数据排序，然后转换为or查询

   • mysql执行关联子查询：并不是一个一个查询需要到两个表匹配才叫关联，所以在mysql中，每一个查询，每一个片段（包括子查询甚至于单表select）可能是关联。一般使用“嵌套循环关联”。关于多表关联查询，mysql可能与其他数据库不一样，如下图所示：

   • 关联查询优化器：决定多个表关联的顺序

   • 排序优化：当不能使用索引优化时，mysql需要自己进行排序，数据量小，则在内存中，数据量大则在磁盘上，统一称为filesort。两种排序方式，一种为只带行号与排序字段进行排序（两次传输排序），排完后还需要再次去读取数据；一种为带着所有字段进行排序（单次传输排序）。第一种需要两次寻址，第二种占用空间比较大。当所有列的总长度不超过max_length_for_sort_data时，使用单次传输排序，否则使用两次传输排序

4. 查询执行引擎：mysql只是简单的根据执行计划给出的指令逐步执行，在这个过程中有大量的操作需要调用存储引擎的接口来完成。

5. 返回结果给客户端：即使查询不需要返回结果集给客户端，mysql任然会返回这个查询的一些信息，如影响到的行数

   • 注：mysql将结果集返回给客户端是一个增量、逐步返回的过程。一旦服务器处理完最后一个关联表，开始生成最后一条结果时，mysql就可以开始向客户端逐步返回数据了。

查询优化器的局限性

1. mysql子查询实现比较糟糕，比如where条件中包含in()的子查询子句，会将外层表压缩到子查询中。当使用子查询时，最好使用执行计划进行查看

2. union后只取几条数据，可以先分别取两个表的前几条数据，然后再union

3. 索引合并优化：当where子句中包含多个复制条件时，mysql能够访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行。

4. 等值传递：当有非常大的in()列表时，且mysql优化器发现存在where、on或者using子句将这个列表的值和另一个表的某个列相关联，此时会将in()列表都复制到相关联的各个表中，导致优化和执行变得很慢

5. 并行执行：mysql无法利用多核特性来并行执行查询

6. 哈希关联，mysql不支持哈希关联，mysql的所有关联都是嵌套循环关联

7. 松散索引扫描：mysql的索引扫描只能扫描一段连续的位置(所以不支持只使用组合索引的第二列索引扫描、组合索引第一列为范围，第二列为常量值)，所以不支持松散索引扫描。除min与max之外

8. 最大值与最小值优化：select min(actor_id) from actor where first_name=‘abc’; 当first_name上没有索引时，会进行全表扫描，但其实主键是按照actor_id资源大小排序的，没必要进行全表扫描，可以用如下方式进行重写：select actor_id from actor use index(primary) where first_name=‘abc’;

9. 在同一个表上查询和更新：mysql不允许一条语句对同一张表同时进行查询和更新。但是可以在执行更新前就完成查询。比如 update tb1 as outer_tb1 set cnt=(select count(*) from tb1 as inner_tb1 where inner_tb1.type=outer_tb1.type);无法执行，可以修改为 update tb1 inner join(select type, couunt(*) as cnt from tb1 group by type) as der using(type) set tb1.cnt=der.cnt;

查询优化器的提示（hint）

如果对优化器的执行计划不满意，可以使用hint控制最终的执行计划

优化特定类型的查询

1. count()函数：两种含义
   • 统计括号内表达式有值的结果数（不统计NULL）
   • 统计结果集的行数，使用count(*)更清晰，性能也更好
   • 在MyISAM中,不使用任何where条件时，count(*)会直接读取一个数
   • 常使用的语句：select count(color=‘blue’ OR NULL), count(color=‘red’ OR NULL) as red from items; 或 select sum(color=‘blue’), sum(color=‘red’) from items;
   • 当count不需要完全精确的值时，可以使用近似值代替，此时可以使用explain出来的优化器估算的行数作为近似值
   • 更复杂的优化，可能就涉及覆盖索引扫描，或者是增加汇总表
2. 优化关联查询
   • 确保on或using子句中的列上有索引
   • 确保任何group by和order by中的表达式只涉及到一个表中的列
3. 优化子查询
   • 优化建议是尽可能使用子查询
4. 优化group by 和distinct
   • mysql优化器会在内部处理的时候转化这两类查询
   • 当无法使用索引时，group by 使用临时表或者文件排序来做分组
   • 有时可以转化分组中的内容到另一列上
5. group by with rollup
   • 类似于分组后，再将分组后的信息聚合起来。产生多维分组。
   • 但是使用后，无法进行排序
   • 很多时候在应用程序中使用超级聚合更好
6. 优化limit分页
   • 当取很靠后的数据时，可以在应用程序中限制分页数量
   • 尽可能使用索引覆盖扫描，然后使用延迟关联来做。即先查出排序后主键，然后使用主键去关联原表，获得数据
   • 如果可以将limit转换为已知位置的值，此时就可以直接使用位置值去查询。
   • limit的优化，主要是优化获得数据的开始位置，如果可以直接指定开始位置，则不需要扫描开始位置之前的数据。可以将本页的开始位置记录下来，下次直接使用
   • 新建表，该表只包含主键列和需要做排序的数据列
7. 优化SQL_CALC_FOUND_ROWS
   • 在limit语句中加上该提示，这样就可以获得去掉LIMIT后满足条件的行数，其实还是扫描了总行数，只不过是扫完后抛弃不需要的行
   • 更好的办法是，只显示下一页按钮，比如分页20条记录，但是每次查询21条，如果第21条存在，则显示下一页按钮，否则不显示
   • 在页面设置区分，比如区分常用的是前1000条，那么将这1000条缓冲起来，也就是页面直接显示50页，点击后50页时，再去查询后50页数据缓冲起来
   • 当需要的总数不需要特别详细时，可以使用explain中的rows作为结果集的近似值。当需要精确结果时，再使用count(*)
8. 优化union查询
   • mysql通过创建并填充临时表来执行union查询，所以经常需要手动将where、limit、order by下推到union的各个子查询中
   • 除非有特殊需求要消除重复行，否则一般使用union all，。如果没有all，mysql会给临时表加上distinct。即便是有all，mysql任然会使用临时表存储结果
9. 使用用户自定义变量
10. 静态查询分析

案例

1. 切分未处理、已处理、正在处理表
2. 尽量避免使用select for update，将其改写为两个语句，且两个语句select语句 不使用事务，避免阻塞其他查询
3. 计算两点之间的距离：可以使用Sphinx。或者尽量使用近似值
4. 使用用户自定函数

说明

查询状态：

• sleep（等待客户端发送新请求）
• query（正在执行查询或正将结果返回客户端）
• Locked（在mysql服务层，线程正在等待表锁）
• Analyzeing and statistics（线程正在收集存储引擎统计信息，并生成查询的执行计划）
• copying to tmp table [on disk]（线程正在执行查询，并将结果集复制到临时表中，要可能是group by、文件排序、union操作。如果有 on disk则表示将一个内存临时表放在磁盘上）
• Sorting result（正在对结果集进行排序）
• Sending data（线程可能在多个状态间传送数据、或正在生成结果集、或在向客户端发送数据）