MySQL——查询性能优化

慢查询优化1：优化数据访问

从两个方向考虑：

1. 确认应用程序是否存在检索大量超过需要的数据
2. 确认MySQL服务器是否在分析大量超过需要的行数据

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

典型案例
查询不需要的数据：MySQL会先返回全部结果再进行计算，看上去像是只返回了需要的数据
多表关联时返回全部的列：一般用不到全部的列，只取我们需要的列
总是去除全部列：喜欢用select * 是一个不好的习惯！
重复查询相同的数据：可以将重复使用的数据缓存起来

MySQL是否在扫描额外的记录

衡量查询开销的三个指标：
· 响应时间
· 扫描行数
· 返回的行数

优化2：重构查询的方式

1. 考虑使用一个复杂查询还是化为多个简单查询
2. 切分查询：将一个大查询切分成多个小查询
3. 分解关联查询：让缓存的效率更高；将查询分解后，执行单个查询可以减少锁竞争；在应用层做关联，更容易对数据库进行拆分，更容易做到高性能和可扩展；查询本身效率也可能会提高；相当于在应用中实现了哈希关联；

查询执行的基础

1. 执行过程：
   
2. MySQL客户端/服务端通信协议
   这个协议是半双工的，所以只能进行单方面的数据传输。且一旦进行传输，就必须等待数据包传输完毕。
   一般会将结果集放入缓存，从而减少服务器的压力。但是当结果集很大时，不适用缓存，而是直接处理。
3. 查询缓存：MySQL优先检查这个查询缓存，通过一个对大小写敏感的哈希查找实现的。如果缓存中没有匹配值，进入下一阶段。
4. 查询优化处理：
   4.1 语法解析器：使用MySQL语法规则验证和解析查询，即验证是否使用正确的关键字，或者关键字顺序是否正确等；
   4.2 预处理器：根据一些MySQL规则进一步检验解析树是否合法，如检查数据表和数据列是否存在，解析名字和别名是否有歧义等；
   4.3 查询优化器：尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。
   ·····查询优化能够处理的优化类型：
   ···········a. 重新定义关联表的顺序
   ···········b. 将外连接转化成内连接
   ···········c. 使用等价变换原则
   ···········d. 优化COUNT() , MIN() , MAX()
   ···········e. 预估并转化为常数表达式
   ···········f. 覆盖索引扫描
   ···········g. 子查询优化
   ···········h. 提前终止查询
   ···········i. 等值传播
   ···········列表IN() 的比较
   ···········等等
   注：优化器并不一定都能给出最优结果
   4.4 数据和索引的统计信息：在生成查询的执行计划时，会向存储引擎获取相应的统计信息。
   4.5 执行关联查询：对任何关联都执行嵌套循环关联操作。
   
   4.6 执行计划：MySQL并不会生成查询字节码来执行，而是生成查询的一棵指令树
   4.7 关联查询优化器：决定多个表关联时的顺序
   4.8 排序优化：当不能使用索引生成排序结果的时候，就需要自己进行排序。如果数据量小于“排序缓冲区”，则在内存中排序；否则将数据分块，对每个独立块进行排序，然后将各块结果存到磁盘，然后将各块进行合并。
   两次传输排序（旧版本）：第一次：读取行指针和排序字段进行排序；第二次：根据排序结果读取需要的数据行；第二次读取会产生大量随机I/O，使得传输成本非常高。
   单次传输排序（新版本）：先读取查询所需要的列，然后对给定列进行排序，返回排序结果。
5. 查询执行引擎：MySQL在优化阶段为每个表创建一个handler实例，优化器根据这些实例的接口可以获取表的相关信息。
6. 返回结果给客户端：开始产生第一条结果时，就可以开始向客户单逐步返回结果集了。

查询优化器的局限性

1. 关联子查询：mysql的子查询性能不佳，特别是在where条件中包含了IN()的子查询语句！可以改写成关联表或者用EXISTS()代替IN(。
2. UNION的限制：有时候无法将限制条件从外层“下推”带内层。这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上。
3. 索引合并优化
4. 等值传递：有时候等值传递也会带来消耗。
5. 并行执行：无法利用多核特性来并行执行查询；
6. 哈希关联：mysql并不支持哈希关联
7. 松散索引扫描：mysql并不支持松散索引扫描
8. 最小值和最大值优化并不智能；
9. 不允许在同一个表上查询和更新

优化特定类型的查询

1. 优化count()查询
   count() 函数统计列值的数量：只统计不是NULL的值得列值；
   还能统计结果集的行数；count(*) 只在没有where条件的查询时效率高

第一种优化：
	select count(*) from world.City where id > 5;
	//如果这条查询结果集很大时 可以反向查询 查询id<5的值
	select (select count(*) from world.City) - count(*) 
	from world.city where id <= 5 
	
第二种优化：使用近似值，在对于要求并不高的场景。

2. 关联表的优化
   · 确保ON 或者 USING 子句中的列上有索引；
   · 确保任何的GOURP BY 和 ORDER BY 中的表达式只涉及到一个表中的列；
   · 当升级MYSQL需要注意关联语法、运算符优先级等可能会发生变化的地方。
3. 优化子查询 ： 尽可能用关联查询代替
4. 优化GROUP BY 和 DISTINCT : 使用索引优化
   如果无法使用索引优化：
   GROUP BY ：使用临时表或文件排序来做分组。
5. 优化 LIMIT 分页：使用覆盖索引扫描，然后根据需要做一次关联操作再返回所需的列。
6. 优化SQL_CALC_FOUND_ROWS：limit加上SQL_CALC_FOUND_ROWS会使得mysql可以获取limit以后满足条件的行数，一般可做分页的总数。所以代价会高：一种设计是将具体的页数换成下一页。另一种先获取并缓存较多的数据，然后每次分页都从缓存中获取。如果大于缓存数，就可以进行一个提示之类的。
7. 优化UNION查询：除非必须要消除重复行，一般使用UNION ALL；
8. 使用用户自定义变量