高性能MySQL笔记 -- 查询性能优化

一、为什么查询速度会变慢

我们需要清楚的是重要的是响应时间。如果把查询看做一个任务，那么它由一系列子任务组成，每个子任务都会消耗一定的时间。
优化查询，实际是优化子任务。

1.1 优化方式

• 消除一些子任务，
• 减少子任务的执行次数
• 让子任务运行的更快

1.2 查询生命周期

客户端 →服务器→在服务器进行解析→生成执行计划→执行
“执行”是整个生命周期最重要，其中包括大量为了检索数据到存储引擎的调用以及调整后的数据处理，包括排序分组等。

1.3 在什么地方会花费时间

网络、CPU计算、生成统计信息和执行计划、所等待（互斥等待）等操作、向底层存储引擎检索数据的调用操作，这些调用需要再内存操作、CPU操作和内存不足时导致的I/O操作的消耗时间。根据存储引擎不同，还会产生大量的系统调用以及上下文切换。

二、慢查询基础：优化数据访问

查询性能低下最基本的原因是访问的数据太多。大部门的性能低下可以通过减少访问的数据量进行优化。

分析步骤

• 确定应用程序是否在检索大量超过需要的数据。通常是访问太多行，也可能是访问了太多列。
• 确定MYSQL服务器层是否在分析大量超过需要的数据行。

2.1 是否向数据库请求了不需要的数据

有些查询会请求超过需要的数据，然而应用程序并不需要。

弊端

• MYSQL服务器带来额外的负担，
• 增加网络开销。
• 消耗应用服务器的CPU和内存资源。

典型案列

• 查询不需要的数据，简单的办法是加limit
• 多表关联返回全部列
  
• 取出全部列（select * ）

相关面试题：为什么不用select * ?
1. 一般情况下，我们的查询并不需要取出全部列。
2.  取出全部列会让优化器无法完成索引覆盖扫描这类优化。
3.  会为服务器带来额外的I/O、内存和CPU消耗。

• 重复查询相同的数据。比如用户的评论每次都查询用户头像（初次查询对头像进行缓存）

2.2 是否在扫描额外的记录

衡量查询开销的三个指标

• 响应时间
• 扫描行数
• 返回行数
  它们大致反映了MYSQL内部执行查询访问多少数据，并可以大致推算出查询运行时间。这三个指标会记录在MYSQL的慢日志中，所以检索慢日志记录是找出扫描行数过多的查询的好办法。

响应时间

响应时间是服务时间和排队时间的和。
服务时间：指数据库处理这个查询花费的真正时间。
排队时间：指服务器因为等待某些资源而没有真正执行查询的时间。可能是等I/O操作完成，也可能等待行锁等等。
“快速上限估算”来估算查询的响应时间。概况的说，了解查询需要哪些索引以及执行计划大概需要多少个顺序和随机I/O，再用其乘具体硬件条件下下次I/O的消耗时间。最后加起来就可以获得一个参考值判断当前响应时间是否是一个合理值。

扫描行数和返回行数

一定程度上能说明改查询找到的需要数据的效率高不高。
不是所有的行的访问代价是相同的。较短的行访问速度更快，内存的行业比磁盘的行访问速度更快。

扫描的行数和访问类型

explain的type列反应了访问类型。访问类型有很多种从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。速度是从慢到快、扫描的行数也是从小到大。
MYSQL使用如下三种方式应用where条件，从好到坏依次是：

如果发现查询需要插叙大量的数据而返回少量的行通常采用以下技巧优化它。

三、 重构查询方式

优化有问题的查询的时候，目标应该是找到一个更优的方法获取实际的结果，而不是一定要从mysql获取一抹一样的结果集。

3.1 切分查询

将大查询切分成小查询，每个查询功能一样，只完成小部分，每次只返回小部分查询结果。
比如删除旧数据。定期清除大量数据时，如果一个大的语句一次性完成的话，则可能一次锁住很多数据，占满整个事务日志，耗尽系统资源，阻塞很多小但是重要的查询。讲一个大的Delete语句切分成多个较小的查询可以尽可能小的影响mysql性能，同时还可以减少MYSQL复制的延迟。

对总的数据集进行删除，比如每次删除10000行数据。同时需要注意的是，如果每次删除数据后都暂停一会再做下一次删除，这样也可以将原本一次性的压力分散到一个很长的时间段，就可以大大降低对服务器的影响，还可以大大减少删除时锁的持有时间。

3.2 分解关联查询

分解关联查询的优势

• 让缓存的效率更高。许多应用可以方便的缓存单表查询对应的结果对象。
• 查询分解后，执行单个查询可以减少锁竞争
• 在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
• 查询本身效率也可能会有所提升。
• 可以减少冗余记录的查询。在应用层做关联说明只需要对一部分数据只需要查询一次，但是在数据库层面做关联查询时可能重复的访问一部分数据。这样可以减少网络和内存的消耗。
• 这样做相当于在应用中做了哈希关联，而不是mysql的嵌套关联。某些场景哈希关联效率高很多。

四、 查询执行的基础

MYSQL执行一个查询的过程

查询步骤：

4.1 MYSQL客户端/服务器通信协议

mysql客户端和服务器的通信协议是“半双工”的，这意味着，在任何一时刻，要么是由服务器向客户端发送数据，要么是有客户端向服务器发送数据。两个动作不能同时发生。

4.2 查询状态

对于一个mysql连接，或者说各异一个线程，任何时刻都有状态。该状态表示mysql现在在做什么。通过SHOW FULL PROCESSLIST可查看当前当前状态。一个查询声明周期，状态会变化很多次。

---

总结

后续继续补充