查询性能优化
1.慢查询原因
向数据库请求了不需要的数据
- 查询不需要的记录
- 多表关联时返回全部列
- 总是取出全部列: DBA应该严格禁止
SELECT *的写法 - 重复重新相同的数据
衡量查询开销的指标
- 响应时间: 服务时间和排队时间(I/O操作, 等待行锁等)之和
- 扫描的行数
- 返回的行数
2.查询优化: 重构查询方式
重构查询
- 分析使用一个复杂查询还是使用多个简单查询
- 切分查询: 大查询可以需要一次锁住很多数据,占满整个事务日志, 耗尽系统资源, 阻塞很多小但重要的查询
- 分解关联查询
分解关联查询的优势
- 让缓存的效率更高效: 如果某个表很少改变,那么基于该表的查询可以重复利用查询缓存结果
- 将查询分解后, 执行单个查询可以减少锁的竞争
- 在应用层做关联,可以更容易对数据库进行拆分,更容易做到高性能和可扩展
- 某些情况可以提升查询本身的效率
- 加您少冗余记录的查询
- 某些情况相当于在应用中实现了哈希关联,而不是使用MySQL的嵌套循环关联
3.MySQL执行查询方式
查询过程
- 客户端发送一条查询给服务器
- 服务器先检查查询缓存,如果命中了缓存,则立即返回存储在缓存中的结果.否则进入下一阶段
- 服务器端进行SQL解析,预处理, 再由优化器生成对应的执行计划
- MySQL根据优化器生成的执行计划,调用存储引擎的API来进行查询
- 将结果返回给客户端
MySQL客户端和服务器之间的通信协议是"半双工"的: 要么是由服务端项客户端发送数据,要么是由客户端向服务器发送数据,整两个动作不能同时发生,所以,我们无法也无须将一个小小切成小块独立发送.
当查询语句很长的时候,参数: max_allowed_packet就特别重要.
查询状态
可以使用SHOW FULL PROCESSLIST查询当前的状态
| 状态 | 说明 |
|---|---|
Sleep |
线程正在等待客户端发送新的请求 |
Query |
线程正在执行查询或者正在讲结果发送给客户端 |
Locked |
该线程正在等待表锁.(InnoDB的行锁, 并不会体现在线程状态中) |
Analyzing and statistics |
正在收集存储引擎的统计信息,并生成查询的执行计划 |
Coping to tmp table[on disk] |
正在执行查询,并且将结果集复制到一个临时表中.这种状态一般要么是在做GROUP BY操作,要么是文件排序操作,或者是UNION操作. 如果有"on disk"标记,表示MySQL正在讲一个内存临时表放到磁盘上 |
Sorting result |
线程正在对结果集进行排序 |
Sending data |
线程可能在多个状态之间传送数据,或者在生成结果集,或者在向客户端返回数据 |
一些MySQL能够处理的优化类型
- 重新定义关联表的顺序
- 将外链接转换成内连接
- 使用等价变换规则
- 优化 COUNT()、MIN()、MAX()
- 预估并转化为常数表达式
- 覆盖索引扫描
- 子查询优化
- 提前终止查询
- 等值传播
- 列表IN()的比较
4.特定类型查询优化
count()
COUNT()是一个特殊函数, 有两个不同作用:
- 统计某个列值的数量. 统计时要求列值是非空的(不统计NULL).如果在COUNT()的括号中指定了列或者列的表达式,则统计的就是这个表达式有值的结果数(而不是NULL)
- 统计行数. 当我们使用COUNT()的时候,这种情况下通配符``并不会扩展成所有列,实际上,它会忽略所有的列而直接统计所有的行数
优化关联查询
- 确保
ON或者USING字句中的列上有索引 - 确保任何的
GROUP BY和ORDER BY中的表达式只涉及到一个一个表中的列 - 升级MySQL时需注意: 关联语法,运算符优先级等其他可能会发生变化的地方
LIMIT分页
在偏移量非常大的时候(翻页到非常靠后的页面), 例如可能是 LIMIT 10000,20这样的查询,这MySQL需要查询 10020条记录然后只返回最后的20条,前面的10000条记录都将被抛弃,这样的代码非常高.要优化这种查询,要么是在页面中限制分页的数量,要么是优化大偏移量的性能.