读高性能MySQL（第4版）笔记10_查询性能优化（上）

1. 三管齐下

1.1. 不做、少做、快速地做

1.2. 如果查询太大，服务端会拒绝接收更多的数据并抛出相应错误

1.3. 如果查询写得很糟糕，即使库表结构再合理、索引再合适，也无法实现高性能

1.4. 查询优化、索引优化、库表结构优化需要齐头并进，一个不落

1.5. Percona Toolkit中的pt-archiver工具

2. 响应时间

2.1. 如果把查询看作一个任务，那么它由一系列子任务组成，每个子任务都会消耗一定的时间

2.2. 如果要优化查询，实际上要优化其子任务，要么消除其中一些子任务，要么减少子任务的执行次数，要么让子任务运行得更快

2.3. 优化查询的目的就是减少和消除这些操作所花费的时间

2.4. 查询需要在不同的地方花费时间

2.4.1. 网络、CPU计算、生成统计信息和执行计划、锁等待（互斥等待）等操作

2.4.2. 尤其是向底层存储引擎检索数据的调用操作，这些调用需要在内存操作、CPU操作和内存不足时导致的I/O操作上消耗时间

2.5. 两部分之和：服务时间和排队时间

2.5.1. 服务时间是指数据库处理这个查询真正花了多长时间

2.5.2. 排队时间是指服务器因为等待某些资源而没有真正执行查询的时间——可能是等I/O操作完成，也可能是等待行锁

2.6. 响应时间既可能是一个问题的结果也可能是一个问题的原因，不同案例情况不同

2.7. 实际上可以使用“快速上限估计”法来估算查询的响应时间

2.7.1. 了解这个查询需要哪些索引以及它的执行计划是什么

2.7.2. 计算大概需要多少个顺序和随机I/O

2.7.3. 用其乘以在具体硬件条件下一次I/O的消耗时间

2.7.4. 把这些消耗都加起来

2.7.5. 获得一个大概参考值来判断当前响应时间是不是一个合理的值

3. 查询的生命周期

3.1. 从客户端到服务器，然后在服务器上进行语法解析，生成执行计划，执行，并给客户端返回结果

3.2. “执行”可以被认为是整个生命周期中最重要的阶段

4. 优化数据访问

4.1. 如果性能很差，最常见的原因是访问的数据太多

4.2. 是否在检索大量且不必要的数据

4.2.1. 访问了太多的行

4.2.2. 访问了太多的列

4.3. 确认MySQL服务器层是否在分析大量不需要的数据行

4.3.1. 会请求超过实际需要的数据，然后这些多余的数据会被应用程序丢弃

4.3.2. 消耗应用服务器的CPU和内存资源

4.4. 一个常见的错误是，常常会误以为MySQL只会返回需要的数据，实际上MySQL却是先返回全部结果集再进行计算

4.4.1. 最简单有效的解决方法就是在这样的查询后面加上LIMIT子句

4.5. 每次看到SELECT*的时候都需要用怀疑的眼光审视，是不是真的需要返回全部的列，很可能不是必需的

4.5.1. 取出全部列，会让优化器无法完成索引覆盖扫描这类优化

4.5.2. 严格禁止SELECT*的写法，这样做有时候还能避免某些列被修改而带来的问题

4.6. 查询返回超过需要的数据也不总是坏事

4.7. 重复查询相同的数据

4.7.1. 当初次查询的时候将这个数据缓存起来，需要的时候从缓存中取出，这样性能显然会更好

4.8. 检查慢日志记录是找出扫描行数过多的查询的好办法

4.9. 扫描的行数和返回的行数

4.9.1. 查看该查询扫描的行数能够说明该查询找到需要的数据的效率高不高

4.9.2. 理想情况下扫描的行数和返回的行数应该是相同的，但实际中这种“美事”并不多

4.9.3. 扫描的行数与返回的行数的比率通常很低，一般在1：1到10：1之间，不过有时候这个值也可能非常非常大

4.10. 访问类型有很多种，从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等

4.10.1. 访问方式可能无须扫描就能返回结果

4.10.2. 访问方式可能需要扫描很多行才能返回一行结果

4.10.3. 没办法找到合适的访问类型，那么最好的解决办法通常就是增加一个合适的索引

4.11. 应用WHERE条件

4.11.1. 在索引中使用WHERE条件来过滤不匹配的记录

4.11.1.1. 在存储引擎层完成的

4.11.2. 使用索引覆盖扫描（在Extra列中出现了Using index）来返回记录

4.11.2.1. 直接从索引中过滤不需要的记录并返回命中的结

4.11.2.2. 在MySQL服务器层完成的，但无须再回表查询记录

4.11.3. 从数据表中返回数据，然后过滤不满足条件的记录（在Extra列中出现Using where）

4.11.3.1. 在MySQL服务器层完成

4.11.3.2. 需要先从数据表中读出记录然后过滤

4.12. 不是说增加索引就能让扫描的行数等于返回的行数

4.13. 优化

4.13.1. 使用索引覆盖扫描，把所有需要用的列都放到索引中，这样存储引擎无须回表获取对应行就可以返回结果了

4.13.2. 改变库表结构

4.13.2.1. 使用单独的汇总表

4.13.3. 重写这个复杂的查询，让MySQL优化器能够以更优化的方式执行

5. 重构查询的方式

5.1. 在优化有问题的查询时，目标应该是找到获得实际需要的结果的替代方法

5.1.1. 但这并不一定意味着从MySQL返回完全相同的结果集

5.1.2. 可以将查询转换为返回相同结果的等价形式，以获得更好的性能

5.2. 以前人们总是认为网络通信、查询解析和优化是一件代价很高的事情

5.2.1. 对于MySQL并不适用

5.2.2. MySQL从设计上让连接和断开连接都很轻量，在返回一个小的查询结果方面很高效

5.2.3. 现代的网络速度比以前要快很多，能在很大程度上降低延迟

5.3. 在MySQL内部，每秒能够扫描内存中上百万行的数据

5.3.1. MySQL响应数据给客户端就慢得多了

5.4. 在其他条件都相同的时候，使用尽可能少的查询当然是更好的

5.4.1. 将一个大查询分解为多个小查询是很有必要的

5.4.2. 如果在一个查询能够胜任时还将其写成多个独立的查询是不明智的

5.5. 切分查询

5.5.1. 删除旧的数据就是一个很好的例子

5.5.1.1. 定期清除大量数据时，如果用一个大的语句一次性完成的话，则可能需要一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询

5.5.1.2. 将一个大的DELETE语句切分成多个较小的查询可以尽可能小地影响MySQL的性能，同时还可以降低MySQL复制的延迟

5.5.2. 一次删除一万行数据一般来说是一个比较高效而且对服务器影响最小的做法（如果是事务型引擎，很多时候小事务能够更高效）

5.5.3. 如果每次删除数据后，都暂停一会儿再做下一次删除，也可以将服务器上原本一次性的压力分散到一个很长的时间段中，可以大大降低对服务器的影响，还可以大大减少删除时锁的持有时间

5.6. 分解联接查询

5.6.1. 很多高性能的应用都会对联接查询进行分解

5.6.2. 可以对每一个表进行一次单表查询，然后将结果在应用程序中进行联接

5.6.3. 让缓存的效率更高

5.6.4. 将查询分解后，执行单个查询可以减少锁的竞争

5.6.5. 在应用层做联接，可以更容易对数据库进行拆分，更容易做到高性能和可扩展

5.6.6. 查询本身的效率也可能会有所提升

5.6.6.1. 使用IN()代替联接查询，可以让MySQL按照ID顺序进行查询，这可能比随机的联接要更高效

5.6.7. 可以减少对冗余记录的访问

5.6.7.1. 在应用层做联接查询，意味着对于某条记录应用只需要查询一次，而在数据库中做联接查询，则可能需要重复地访问一部分数据