mysql优化查询问题个人笔记

1.如何查看分析以及如何开启分析工具；
set profiling =1;

然后在profiles这张临时表里面就可以查询到任何一条sql语句的执行时间，

show profile for query 2;
可以查看到具体的语句消耗时间在哪一步。

也可以用show status，查看进程的具体情况；
show status会返回一些计数器，show global status 查看服务器级别的所有计数，有时根据这些计数，可以猜测出哪些消耗时间多。
show processorlist,观察是否有线程处于不正常的状态或者特征；

使用explain 分析单条的sql语句，使用desc也是一样的效果

2.如何优化，分为以下几类：

• 确定需求优化

访问数据太多导致性能下降，确定应用程序是否在检索大量超过需要的数据，可能是太多行或者列；避免不合理查询；
避免使用如下sql语句select * from全局遍历，查询不需要的记录，尽量使用limit解决；
多表关联如果返回全部列，最好指定A.id,A.name,A.age，总是取出全部列，select *会让优化器无法完成索引覆盖扫描的优化；
加索引，所需要的列加到索引中；
改变数据库和表的结构，修改数据表范式等等；如果需要关联查询，必要的话可以用空间去换取时间，相关的字段冗余的在该表也存一份；
重写sql语句，让优化器以更优的方式查询；

• 优化长难的sql语句;

使用尽可能少的查询是好的，但是有时将大的查询分解为小的查询也是有必要的；
切分查询，比如一次性删除1000万条的数据，要比一次删除一万，暂停一会的方案更加损耗服务器开销，可以将一条关联语句分解成多条sql来执行，让缓存的效率更高，执行单个查询可以减少锁的竞争
mysql扫描很快，但是响应很慢，

• 优化count查询

优化count查询，count()会忽略所有行，直接统计列数，因此不要使用count(列)；
MYISAM中没有任何WHERE条件下的count()非常快，有了where条件之后就不一定比其他引擎快了
怎么办呢？可以使用EXPLAIN查询近似值，用近似值代替count(*)，增加汇总表，或者使用缓存。

• 优化关联查询

确定ON或者USING子句的列上有索引，避免全表扫描；
GROUPBY 和ORDERBY中只有一个表当中的列，这样才可以使索引生效；

• 优化子查询

关于什么是子查询？参考下文：

https://www.cnblogs.com/zhuiluoyu/p/5822481.html

子查询分为以下几类： 标量子查询：SELECT * FROM article WHERE uid = (SELECT uid FROM
user WHERE status=1 ORDER BY uid DESC LIMIT 1) 列子查询：SELECT * FROM
article WHERE uid IN(SELECT uid FROM user WHERE status=1) 行子查询：SELECT

• FROM table1 WHERE (1,2) = (SELECT column1, column2 FROM table2) 表子查询：SELECT * FROM article WHERE (title,content,uid) IN (SELECT
  title,content,uid FROM blog)

子查询尽量用关联查询来替代；
优化GROUP BY和DISTINCT，
可以使用索引优化也是最有效的方法；
关联查询中，使用标识列来分组的效率更高，比如说主键列，或者autoinrement列来分组；
如果不需要ORDER BY 在GROUP BY后面可以加一个ORDER BY NULL,这样MYSQL就不会进行文件排序了
优化limit分页；
Limit偏移量大的时候，查询效率较低，可以记录上次查询的最大的ID，下次查询时根据该ID来查询；
优化UNION查询，UNION ALL代替UNION;

总结：对于此类问题，先分析定位低效SQL语句，可以用EXPLAIN，show profiles,show processorlists,show status,根据SQL语句对低效的原因做排查，先从索引着手，如果索引没有问题，考虑以上几个访问，数据访问的问题，长难句查询的问题还是一些特定优化的问题，逐一解决。✿✿ヽ(°▽°)ノ✿