《高性能MySQL》夯实之路(四) 查询性能优化

《高性能MySQL》夯实之路(四) 查询性能优化

---

---

---

一.查询周期

查询的生命周期：客户端->服务器->解析->生成执行计划->执行->返回结果给客户端

---

二. 慢查询

是否检索了太多行(列)
是否分析了太多行(列)

1. 向数据库请求了不必要的数据

• 查询不需要的记录，解决方法：加上limit
• 多表关联时返回全部列
• 总是取出全部列，select *
• 重复查询相同的数据

2. MySQL在扫描额外的记录

• 响应时间：影响因素很多，不太好归因
• 扫描的行数和返回的行数：比例最好接近1:1
• 扫描的行数和访问类型：explain中的type

---

三. 重构查询的方法

1. 一个复杂查询还是多个简单查询

2. 切分查询

• 将大查询分割成多个小查询
• Eg：删除大量数据，如果一次性则会锁住很多行，所以可以将一个大的delete语句切分成很多小的delete。
• 使用前缀索引时，必须保持较高的选择性。

3. 分解关联查询

• 对每一个表进行一次单表查询，然后将结果再进行关联

---

四. 查询执行的基础

1. MySQL客户端/服务器通信协议
   • 半双工：任何时刻，只能由一端向另一端发送数据，不能同时发送
   • 查询状态：SHOW FULL PROCESSLIST，SLEEP表示线程等待客户端发送新的请求，QUERY线程正在将结果返回给客户端，LOCKED该线程正在等待表锁33
2. 查询缓存
   • 优先通过一个大小写敏感的哈希查询查找缓存，如存在返回结果则直接从缓存里返回。
3. 查询优化处理
   • 语法解析器和预处理：
     • 先解析SQL语句，生成”解析树”，验证是否符合mysql语法规则
   • 查询优化器
     • 找到一条查询最好的执行方式
     • 使用基于成本的优化器，用last_query_cost估算操作成本
   • 数据和索引的统计信息
     • 由存储引擎实现，所以服务器的查询优化器需要像存储引擎索要统计信息
   • MySQL如何执行关联查询
     • 嵌套循环关联
   • 执行计划
     • 不会生成字节码，而是生成指令树，按指令树进行
   • 关联查询优化器
     • 决定关联查询的顺序。
   • 排序优化
     • 从性能角度考虑，应尽量避免排序这种成本很高的操作
     • 如果，排序数据量<数据缓冲区，MySQL使用内存进行快速排序
     • 否则，将数据分块之后，对每快进行快速排序，然后合并
     • Extra： using filesort则表示有使用排序
4. 查询执行引擎
5. 返回结果给客户端
   • 逐步返回，生成多少返回多少
   • 可以存储到缓存就会存储到缓存

---

五. MySQL查询优化器的局限性

1. 关联子查询

• MySQL对子查询的实现非常糟糕
• 使用IN加上子查询，性能会非常糟糕，所以可以改用GROUP_CONCAT

2. UNION的限制

• 通过加LIMIT限制中间生成的临时表的大小

3. 索引合并优化

4. 等值传递

• eg：一个非常大的IN列表会让要查询的表变得更大
• 目前还无法解决

5. 并行执行

• 做不了

6. 哈希关联

• 不支持

7. 松散索引扫描

• 不支持，必须要定义一个起点和终点

8. 最大值和最小值优化

• 如果是非索引查询，那么效率不高，需要做全表扫描

9. 在同一个表上查询和更新

• 不允许同时对同一张表进行查询和更新
• 可以通过生成新表的形式来绕过

---

六. 查询优化器的提示

1. HIGH_PRIORITY和LOW_PRIORITY

• 那些语句优先级高那些语句优先级低(对同一个表)

2. DELAYED

• 对INSERT和REPLACE有效，会将插入的语句先放入缓冲区，等表空闲了再插入

3. STRAIGHT_JOIN

• 在SELECT之后：让表按出现顺序关联
• 在两个表之间：固定先后关联顺序

---

七. 优化特定查询类型

1. 优化COUNT()查询

• 聚合函数，可以统计某个列值的数量(非NULL)，也可以统计行数
• 使用：COUNT(colA)， COUNT(*)
• MyISAM：只有没有where的COUNT(*)才非常快
• 优化：
  • 当where id<=3(很小的数)时使用count(*)-count(*) where id<=3,而不是count(*) where id >3，索引的行数太多了
  • 有时可以使用SUM代替COUNT，如数颜色
  • 使用近似值

2. 优化关联查询

• 确保ON和USING上有索引，且索引为第二个表上的列
• 确保GROUP BY和ORDER BY只涉及一个表中的列

3. 优化子查询

• 尽量用关联查询代替

4. 优化GROUP BY和DISTINCT

• 可以相互转化，都可以使用索引优化

5. 优化LIMIT

• 通常与ORDER BY组合使用
• 当偏移量特别大时，可以将limit用在索引上，使用“延迟关联”，扫描尽可能少的页面

6. 优化UNION查询

• 除非需要DISTINCT，不然最好加上UNION ALL

7. 静态查询分析

• Percona Toolkit中的pt-query-advisor

8. 使用用户自定义变量

• 优化排名语句
  • 给自定义变量赋值的同时可以使用
  • Eg: @rownum := 0; select @rownum = @rownum +1 limit 3，返回结果就会为1 ，2，3
  • 可以与ORDER BY组合使用来进行排名
• 避免重复查询刚刚更新的数据
  • UPDATE t1 SET lastUpdated= NOW() WHERE id =1;
  • SELECT lastUpdated FROM t1 WHERE id =1
• 统计更新和插入的数量
  • 可以返回插入INSERT时，因为冲突而改写为更新UPDATE的操作数量
  • INSERT ……. ON …… UPDATE c1 = VALUES(c1)+(0*(@x:=@x+1))
• 确定取值的顺序
• 编写偷懒的UNION