MySQL查询性能优化
⼀、MySQL查询执⾏基础
1. MySQL查询执⾏流程原理
<1> 客户端发送⼀条查询给服务器。
<2> 服务器先检查查询缓存，如果命中了缓存，则⽴刻返回存储在缓存中的结果。否则进⼊下⼀阶段。
<3> 服务器进⾏SQL解析、预处理，再由优化器⽣成对应的执⾏计划。
<4> MySQL根据优化器⽣成的执⾏计划，调⽤存储引擎的API来执⾏查询。
<5> MySQL将结果返回给客户端，同时保存⼀份到查询缓存中。
2. MySQL客户端/服务器通信协议
sql语句优化方式<1> 协议类型：半双⼯。
<2> Mysql通常需要等所有的数据都已经发送给客户端才能释放这条查询所占⽤的资源。
<3> 在PHP函数中，mysql_query()会将整个查询的结果集缓存到内存中，⽽mysql_unbuffered_query()则不会缓存结果，直接从mysql服务器获取结果。当结果集很⼤时，使⽤后者能减少内存的消耗，但服务器的资源会被这个查询占⽤⽐较长的时间。
3. 查询状态
  可以使⽤命令来查询mysql当前查询的状态：show full processlist。返回结果中的“State”键对应的值就表⽰查询的状态，主要有以下⼏种：
<1> Sleep：线程正在等待客户端发送新的请求。
<2> Query：线程正在执⾏查询或正在将结果发送给客户端。
<3> Locked：在MySQL服务器层，该线程正在等待表锁。（在没⾏锁的引擎出现）
<4> Analyzing and statistics：线程正在收集存储引擎的统计信息，并⽣成查询的执⾏计划。
<5> Copying to tmp [on disk]：线程正在执⾏查询，并且将其结果集都复制到⼀个临时表中，这种状态要么是在做group by操作，要么是⽂件排序操作，或者是union操作。
<6> Sorting result：线程正在对结果集进⾏排序。
<7> Sending data：表⽰多种请况，线程可能在多个状态之间传送数据，或者在⽣成结果集，或者在向客户端返回数据。
4. 查询缓存
<1> 这个检查是通过⼀个对⼤⼩写敏感的哈希查实现的。
<2> 命中查询缓存之后，检查⽤户权限，直接从缓存中返回数据给客户端，不需要解析查询。
5. 查询优化处理
语法解析器和预处理：
<1> 通过关键字对SQL语句进⾏解析，⽣成⼀棵“解析树”。
<2> 解析器使⽤MySQL语法规则验证和解析查询（关键字是否正确...）。
<3> 预处理器根据⼀些MySQL规则进⼀步检查解析树是否合法（表、列是否存在...）。
<4> 预处理器验证权限。
查询优化器：
  MySQL使⽤基于成本的优化器。它将尝试预测⼀个查询使⽤各种执⾏计划时的成本，并选择其中成本最⼩的⼀个。其中，成本是根据存储引擎提供的数据和引擎的统计信息计算得来的，可以通过查询当前会话的Last_query_cost的值来得知MySQL计算的当前查询的成本。优化器在评估成本的时候并不考虑任何层⾯的缓存，它假设读取任何数据都需要⼀次磁盘I/O。有很多原因会导致MySQL优化器选择并不是最优的执⾏计划。MySQL能处理的优化类型：重新定义关联表的顺序、将外连接转化成内连接、使⽤等价变换规则、优化count()、min()和max()、预估并转化为常数表达式、覆盖索引扫描、⼦查询优化、提前终⽌查询、等值传播、列表in()的⽐较等等。
数据和索引的统计信息：
  统计信息由存储引擎实现。Mysql查询优化器在⽣成查询的执⾏计划时需要向存储引擎获取相应的统计信息。
MySQL如何执⾏关联查询：
<1> MySQL认为任何⼀个查询都是⼀次关联，对任何关联都执⾏嵌套循环关联操作，从⼀个表开始⼀直嵌套循环、回溯完成所有表关联。
<2> Union查询：先将⼀系列的单个查询结果放到⼀个临时表中，然后再重新读出临时表数据来完成union查询。（临时表没有任何索引）
<3> MySQL在执⾏⼦查询的时候也是先将⼦查询的结果放到⼀个临时表中。
<4> 遇到右连接的时候mysql会将其改写成等价的左连接。
<5> 将所有的查询类型都转换成类似的执⾏计划。。
<6> 并不是所有的查询都可以通过嵌套循环和回溯的⽅式完成，例如全外连接，所以Mysql并不⽀持全外连接。
执⾏计划：
  对某个查询执⾏explain extended后再执⾏show warnings就可以看到重构出的查询。因为mysql执⾏查询采⽤的总是嵌套循环关联操作，所
以mysql的执⾏计划总是⼀棵左侧深度优先的树。
关联查询优化器：
  MySQL优化器最重要的⼀部分就是关联查询优化。关联优化器通过评估多个表的不同关联顺序的成本来选择⼀个代价最⼩的关联顺序，如果可能，优化器会遍历每⼀个表然后逐个做嵌套循环计算每⼀
棵可能的执⾏计划树的成本，最后返回最优的⼀个执⾏计划。但是当关联表的数量⽐较多的时候，这样做的成本太⾼，当需要关联的表数量超过optimizer_search_depth参数值的时候，优化器会选择使⽤“贪婪”搜索的⽅式查“最优”关联顺序。有时候优化器给出的不是最优关联顺序，这时如果不希望关联优化器改变表的关联顺序的话，可以使⽤straight_join来强制表的连接顺序。
排序优化：
<1> 排序是⼀个成本很⾼的操作，应尽可能避免排序操作。
<2> Mysql的两种排序算法：
  两次传输排序（旧版本使⽤）：读取⾏指针和需要排序的字段，对其进⾏排序，然后再根据排序结果读取所需要的数据⾏。
  单次传输排序（新版本使⽤）：先读取查询所需要的所有列，然后在根据给定列的值进⾏排序，最后直接返回结果。
  两个算法各有优缺点，当查询需要所有列的总长度不超过参数max_length_for_sort的值时，mysql使⽤单次传输排序。
<3> 如果关联查询需要排序，MySQL会分两种情况来处理⽂件排序：
  如果order by⼦句中的所有列都来⾃关联的第⼀个表，那么MySQL在关联处理第⼀个表的时候就进⾏⽂件排序（Explain结果Extra字段“Using filesort”），否则，MySQL都会先将关联的结果存放到⼀个临时表中，然后在所有的关联都结束后再进⾏⽂件排序（Explain结果Extra字段“Using temporary;Using filesort”）。
⼆、MySQL查询优化器的局限性
1. 关联⼦查询
  MySQL的⼦查询实现⾮常糟糕，最糟糕的⼀类查询是where条件中包含IN()的⼦查询语句。MySQL会将相关的外层表压到⼦查询中进⾏关联查询。
包含In的⼦查询优化：
<1> 使⽤group_concat()在in()中构造⼀个由逗号分隔的列表。
<2> 改写成关联查询或使⽤exists代替。
2. Union的限制
  有时，MySQL⽆法将限制条件从外层“下推”到内层，使得原本能够限制部分返回结果的条件⽆法应⽤到内层查询的优化上。在union的各个⼦句中分别使⽤order by和limit，可以减少临时表中的数据，但想获取正确的顺序还需加上⼀个全局的order by和limit操作。
3. 索引合并优化
  在5.0和更新的版本中，当where⼦句中包含多个复杂条件的时候，mysql能够访问单个表的多个索引以合并和交叉过滤的⽅式来定位需要查的⾏。
4. 等值传递
  Mysql优化器会将In()列表复制应⽤到关联的各个表中。
5. 并⾏执⾏
  Mysql⽆法利⽤多核特性来并⾏执⾏查询。
6. 哈希关联
  可以通过创建⼀个哈希索引来曲线实现哈希关联。
7. 松散索引扫描
  Mysql并不⽀持松散索引扫描。
8. 最⼤值和最⼩值优化
  在需要取最⼤/最⼩值的字段上创建索引，然后在查询语句中加⼊“use index”语句强制使⽤索引，当MySQL读到第⼀条满⾜条件的记录的时候就是我们需要的最⼤/最⼩值了。
优化⽰例：
优化前：
优化后：
9. 在同⼀张表上查询和更新
  MySQL不允许对同⼀张表同时进⾏查询和更新。可以通过使⽤⽣成表的形式来绕过这个限制，因为MySQL只会把这个表当成临时表来处理。
优化⽰例：
优化前：
优化后：
10. 查询优化器的提⽰
  可以在查询语句中加⼊⼀些提⽰来控制查询的执⾏计划。
三、优化特定类型的查询
1. 优化count()查询
  如果在count()的括号中指定了列或者列的表达式，那么统计的是这个表达式有值的结果数（不包含NULL）。统计⾏数使⽤count(*)意义更清晰，性能也会更好。
  MyISAM执⾏没有任何where条件的count(*)⾮常快，因为可以利⽤存储引擎的特性直接获得这个值。如果mysql知道某个列col不可能为null值会将count(col)表达式优化为count(*)。可以利⽤MyISAM的这个特性来优化加速⼀些特定条件的count()查询。优化⽰例：
优化前（需要扫描很多的数据⾏）：
优化后（将需要扫描的数据⾏减少到5以内）：
（在查询优化阶段会将其中的⼦查询直接当做⼀个常数来处理）
  在对精确值要求不⾼的情况下，可以通过⼀些途径取得近似值来达到优化查询的⽬的：
<1> 使⽤explain出来的优化器估算的⾏数来替代count(*)，不需要真正去执⾏查询。
<2> 去除⼀些对总数影响很⼩的where条件。
<3> 删除distinct约束避免⽂件排序。
<4> 更复杂的优化：使⽤汇总表、使⽤缓存系统等。
2. 优化关联查询
<1> 确保on或using⼦句中的列上有索引，只需要在关联顺序中的第⼆个表的相应列上创建索引。
<2> 确保任何的group by和order by中的表达式只涉及到⼀个表中的列，这样MySQL才有可能使⽤索引来优化这个过程。
<3> 当升级MySQL的时候需要注意，关联语法、运算符优先级等其他可能会发⽣变化的地⽅。
3. 优化⼦查询
  尽量使⽤关联查询替代（并不绝对，如果使⽤的是MySQL5.6或更新版本或MariaDB的话）。
4. 优化group by和distinct
  优化这两种查询最有效的⽅法是使⽤索引来优化。
  当⽆法使⽤索引的时候，group by使⽤临时表或⽂件排序来做分组，可以通过提⽰SQL_BIG_RESULT和SQL_SMALL_RESULT来让优化器按照我们希望的⽅式运⾏。如果需要对关联查询做分组，通常采⽤查表的标识列分组的效率会⽐其他列更⾼。但如果查询语句⽆法写成在select中直接使⽤⾮分组列的形式或当前sql_mode禁⽌这样做的话（ONLY_FULL_GROUP_BY的sql_mode会返回错误），可以使⽤min()，max()函数来绕过这种限制。
  如果没有通过order by⼦句显式指定排序列，当查询使⽤group by⼦句的时候结果集会⾃动按照分组的字段进⾏排序，可以在其后⾯直接使
⽤asc或desc关键字指定排序⽅向。若不需要进⾏排序，可以加上order by null让MySQL不在进⾏⽂件排序，提⾼查询性能。
5. 优化group by width rollup
  使⽤超级聚合的查询不够优化，可以在from⼦句中嵌套使⽤⼦查询来替代超级聚合，或者是通过⼀个临时表存放中间数据，然后和临时表执
⾏union操作来得到最终结果。但最好的办法还是尽可能将width rollup功能转移到应⽤程序中进⾏处理。
6. 优化limit分页
  在使⽤limit⼦句的查询中，如果没有对应字段的索引，当偏移量很⼤的时候，mysql需要查询⼤量数据⾏但是只返回⼀⼩部分数据，对这种情况进⾏优化的⽅法有：
<1> 使⽤索引覆盖扫描，然后再做⼀次关联操作返回所需的列。
<2> 想办法将limit查询转化为已知位置的查询。
7. 优化union查询
  Mysql总是通过创建并填充临时表的⽅式来执⾏union查询，经常需要⼿⼯将where、limit、order by等⼦句“下推”到union的各个⼦查询中进⾏优化。除⾮确实需要服务器消除重复的⾏，否则⼀定要使⽤union all，不然mysql会给临时表加上distinct选项，这会导致对整个临时表的数据做唯⼀性检查。
8.优化select *查询
  当使⽤select * from tbl_name语句进⾏查询的时候，mysql服务器会先从数据表中解析出全部字段名称，替换掉查询语句中的"*"，然后缓存解析替换之后的查询语句，最后再将解析替换之后的查询语句进⾏执⾏，并且以后遇到select * from tbl_name语句都会直接使⽤缓存中的包含数据表全部字段的语句进⾏查询。所以使⽤"*"⽽不指定字段名称有以下弊端：
（1）会增加SQL的解析成本；
（2）如果不是全部字段都有⽤的话，查询⾮必需字段还会造成资源浪费甚⾄影响服务器性能；
（3）⽆法利⽤索引覆盖查询，不利于查询的性能优化；
（4）若是数据表结构修改之后还使⽤缓存中的语句进⾏查询，还会发⽣字段映射错误问题。
9. 使⽤⽤户⾃定义变量优化查询
  使⽤⽤户⾃定义变量的查询⽆法使⽤查询缓存，⽣命周期只在⼀个连接中有效，是⼀个动态类型，赋值符号:=优先级很低，赋值表达式应该使⽤括号。
优化排名语句：
（演过最多电影的前10名演员，使⽤⼦查询⽣成⼀个中间的临时表来解决⾃定义变量赋值时间和我们预料不同的问题）
避免重复查询刚刚更新的数据：
统计更新和插⼊的数量：
编写偷懒的union：
  第⼀个⼦查询作为分⽀条件先执⾏，如果到了匹配的⾏，则跳过第⼆个分⽀。
（将“热”数据和“冷”数据分别放在两个不同的表，使⽤union去查询）
  有时优化器会把变量当作⼀个编译时常量对待⽽不是对其进⾏赋值，将函数放在类似least()这样的函数中通常可以避免这样的问题。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。