php面试专题---18、MySQL查询优化考点
一、总结
一句话总结：
慢查询：查找分析查询速度慢的原因
数据访问：优化查询过程中的数据访问
长难句：优化长难的查询语句
特定类型：优化特定类型的查询语句
1、mysql中如何查询哪些慢的查询？
慢查询日志：里面会记录那些比较慢的日志，可以使用pt-query-digest工具进行分析
explain语句：可以分析单条语句的查询效率
show profile、show status、show processlist等语句：查询语句执行慢的各种情况以及消息情况或其它
记录慢查询日志：里面记录了那些比较慢的查询
分析查询日志：不要直接打开慢查询日志进行分析，这样比较浪费时间和精力，可以使用pt-query-digest工具进行分析
使用show profile：set profiling=1；开启，服务器上执行的所有语句会检测消耗的时间，存到临时表中
show profile for query 临时表ID：可以查询每条profile临时表中记录花费的时间
使用show status：show status会返回一些计数器，show global status查看服务器级别的所有计数
使用show processlist：观察是否有大量线程处于不正常的状态或者特征
使用explain：分析单条SQL语句
2、mysql中如何优化查询过程中的数据访问？
尽量只取需要的行和列：行方面用索引，列方面用需要的
访问数据太多导致查询性能下降
确定应用程序是否在检索大量超过需要的数据，可能是太多行或列
确认MySQL服务器是否在分析大量不必要的数据行
是否在扫描额外的记录
使用explain来进行分析，如果发现查询需要扫描大量的数据但只返回少数的行，可以通过如下技巧去优化：
使用索引覆盖扫描，把所有用的列都放到索引中，这样存储引擎不需要回表获取对应行就可以返回结果
改变数据库和表的结构，修改数据表范式
重写SQL语句，让优化器可以以更优的方式执行查询
避免使用如下SQL语句
1、查询不需要的记录：使用limit解决
2、多表关联返回全部列：指定A.id，A.name，B.age
3、总是取出全部列：SELECT*会让优化器无法完成索引覆盖扫描的优化
4、重复查询相同的数据，可以缓存数据，下次直接读取缓存
3、mysql的SQL语句中我们应该避免做的？
1、查询不需要的记录：使用limit解决
2、多表关联返回全部列：指定A.id，A.name，B.age
3、总是取出全部列：SELECT*会让优化器无法完成索引覆盖扫描的优化
4、不用缓存：重复查询相同的数据，可以缓存数据，下次直接读取缓存
1、查询不需要的记录：使用limit解决
2、多表关联返回全部列：指定A.id，A.name，B.age
3、总是取出全部列：SELECT*会让优化器无法完成索引覆盖扫描的优化
4、重复查询相同的数据，可以缓存数据，下次直接读取缓存
4、mysql中如何优化长难的查询语句？
切分查询：将一个大的查询分为多个小的相同的查询：一次性删除1000万的数据要比一次删除1万，暂停一会的方案更加损耗服务器开销
分解关联查询：可以将一条关联语句分解成多条SQL来执行
切分查询：将一个大的查询分为多个小的相同的查询：一次性删除1000万的数据要比一次删除1万，暂停一会的方案更加损耗服务器开销
分解关联查询
可以将一条关联语句分解成多条SQL来执行
让缓存的效率更高
执行单个查询可以减少锁的竞争
在应用层做关联可以更容易对数据库进行拆分
查询效率会有大幅提升
较少冗余记录的查询
5、mysql中我们选用一个复杂查询还是用多个简单查询？
内部查询快，和客户端交互慢：MySQL内部每秒能扫描内存中上百万行数据，相比之下，响应数据给客户端就要慢得多
多个简单查询有必要：使用尽可能少的查询是好的，但是有时将一个大的查询分解为多个小的查询是很有必要的
6、mysql中如何优化特定类型的查询语句？
优化count(*)查询：count（*）中的*会忽略所有的列，直接统计所有列数，因此不要使用count（列名）
优化关联查询：确定ON或者USING子句的列上有索引；确保GROUP BY和ORDER BY中只有一个表中的列，这样MySQL才有可能使用索引
优化子查询：尽可能使用关联查询来替代
优化GROUP BY和DISTINCT：这两种查询均可使用索引来优化，是最有效的优化方法
优化count(*)查询
count（*）中的*会忽略所有的列，直接统计所有列数，因此不要使用count（列名）
MyISAM中，没有任何WHERE条件的count（*）非常快；当有WHERE条件，MyISAM的count统计不一定比其他表引擎快
可以使用explain查询近似值，用近似值替代count（*）
增加汇总表
使用缓存
优化关联查询
确定ON或者USING子句的列上有索引
确保GROUP BY和ORDER BY中只有一个表中的列，这样MySQL才有可能使用索引
优化子查询
尽可能使用关联查询来替代
优化GROUP BY和DISTINCT
这两种查询均可使用索引来优化，是最有效的优化方法
关联查询中，使用标识列进行分组的效率会更高
如果不需要ORDER BY，进行GROUP BY时使用ORDER BY NULL，MySQL不会再进行文件排序
WITH ROLLUP超级聚合，可以挪到应用程序处理
优化LIMIT分页
LIMIT偏移量大的时候，查询效率较低，可以记录上次查询的最大ID，下次查询时直接根据该ID来查询
优化UNION查询
UNION ALL的效率高于UNION