MySQL查询优化

3212d

                      一、查询为什么慢

网络，CPU计算，生成的统计信息，执行技术，锁等待，IO等待等。

二、慢查询优化访问低效查询的处理办法：

• 确认是否访问了太多行，或者太多列
  
• 是否分析了大量的数据
  
  

---

问题及应对办法

• 查询不需要的记录：使用Limit
  
• 避免select *，会影响索引覆盖扫描
  
• 避免重复查询相同的数据
  
  

---

WHERE语句的三种使用方式

• 存储引擎直接使用WHERE过滤无效字段
  
• 使用索引扫描，在索引中过滤无效字段（Extra出现Using Index）
  
• 返回所有数据然后WHERE过滤（Extra中出现Using Where）
  
  

---

查询大量数据返回少量行是可以考虑的技巧

• 使用覆盖索引
  
• 修改库表结构例如增加汇总表
  
• 重写复杂查询
  
  

---

三、重构查询方式考虑使用一个复杂查询还是多个简单查询

---

切分查询

• 将服务器的压力分散到一个时间段
  
  

---

分解关联查询

• 让缓存的效率更高
  
• 执行单个查询可以减少所得争用问题
  
• 更易拆分数据表，更容易做到高性能和可扩展
  
• 可能让MySQL按照ID进行顺序查询，而不是随机关联
  
• 可以减少冗余数据的查询
  
• 相当于在应用中实现了HASH关联，而不是使用MySQL的嵌套循环关联
  
  

四、查询执行基础查询执行流程

• 服务器接收一条查询命令
  
• 如果有查询缓存直接返回结果，否则进入下一阶段
  
• SQL解析，预处理
  
• 优化器生成对应的执行计划
  
• 根据执行计划调用存储引擎API，完成查询
  
• 将结果返回
  
  

---

查询缓存

　　使用大小写敏感的hash查找实现，只要查询语句有一点改变，就不会被命中。再此期间还会确认一遍用户的权限。这时候查询语句并未被解析，所以也就解释好了where a>2执行完之后where a>1+1不会被缓存命中的原因。

---

MySQL执行关联策略

MySQL对任何关联都执行嵌套关联操作。

• MySQL先在一个表中循环取出单条数据
  
• 然后再嵌套循环到下一个表中寻找匹配的行
  
• 依次下去直到找到所有表中匹配的行为止
  
• 然后根据根据各个表匹配的行，返回查询中需要的列
  
• MySQL会根据相应的优化策略优化关联的顺序，使遍历多表达到较好的效果
  
  

---

查看重构查询

对该查询执行

explain select * from t1;

之后执行

show warning;

---

排序优化下载地址   

• 旧：读取行指针和需要排序的字段，对其 进行排序，然后在根据排序结果读取所需要的行
  
• 新：先读取查询所需要的所有的列，然后再根据给定列进行排序，最后直接返回排序结果
  
• 比较：
  
  

• 优点：只需要一次顺序IO，比起两次IO（其中还有一次随机IO）有了很大优势
  
• 缺点：引入很多无用的列，白白占用着内存，对排序无影响
  
  

文件排序：

• 对每一个排序记录都会分配一个足够长的定长空间来存放，有时排序占用的空间比数据存储占用的空间还要多
  
• 对于关联查询来数order by子句中所有的列都来自于第一个表，那么MySQL在关联第一张表的时候就进行文件排序（Extra中可看到Using Filesort）
  
• 否则会先将关联的结果放到临时表中，然后在所有表都关联完成后再进行文件排序，5.6版本之后可以将Limit提前执行，避免了排序大量的数据到最后只需要很少的尴尬处境