MySQL查询优化
面试题一
请简述项目中优化SQL语句执行效率的方法，从那些方面，SQL语句性能如何分析？
优化查询过程中的数据访问、优化长难的查询语句、优化特定类型的查询语句
分析SQL语句方法
1、使用explain或desc 分析单条SQL语句
列如：explain select * from news；
2、记录慢查询日志
分析慢查询日志，不要直接打开慢查询日志进行分析，这样比较浪费时间和精力，可以使用pt-query-disgest工具分析。
3、使用show profile
例如：set profiling = 1；开启，服务器上执行的所有语句都会检测消耗的时间，存到临时表中
show profiles； 显示临时表
show profile for query id 查看单挑语句具体执行时间
4、show status
show status 会返回一些计数器，show global status 查看服务器级别的所有计数。
有时根据这些计数，可以猜测出那些操作代价较高或消耗时间多。
5、show processlist
观察是否右大量线程处于不正常的状态或者特征
优化查询过程中
访问数据太多导致查询性能下降
确定应用程序是否在检索大量超过需要的数据，可能时太多行或列
确认MySQL服务器是否在分析大量不必要的数据行
避免使用如下SQL语句
查询不需要的记录，使用limit解决
多表关联返回全部列，指定返回需要的列
总是取出全部列，select * 会让优化器无法完成索引覆盖扫描的优化
重复查询相同的数据，可以把查询结果做缓存，下次从缓存中获取数据
是否在扫描额外的记录？
使用explain进行分析，如果发现查询需要扫描大量的数据但只返回少数的行，可以通过如下技巧去优化：
使用索引覆盖扫描，把所有用的列都放到索引中，这样存储引擎不需要回表获取对应行就可以返回结果
改变数据库和表的结构，修改数据库范式
重写SQL语句，让优化器可以以更优的方式执行查询
优化长难的查询语句
一个复杂查询还是多个简单查询、
MySQL内部每秒能扫描内存中上百万行数据，相比之下，响应数据给客户端就要慢的多
使用尽可能少的查询是好的，但是有时将一个大的查询分解为多个小的查询是很有必要的
切分查询
将一个大的查询分为多个小的相同的查询
一次性删除1000万的数据要比一次删除1万，暂停一会的方案更加损耗服务器开销
分解关联查询
可以将一条关联语句分解成多条SQL来执行
让缓存的效率更高
执行单个查询可以减少锁的竞争
在应用层做关联可以更容易对数据库进行拆分
特定类型的查询
优化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