mysql查询优化常见问题包括:查找分析查询速度慢的原因;优化查询过程中的数据访问;优化长难的查询语句;优化特定类型的查询语句;
①查找分析查询速度慢的原因:
A、分析AQL查询慢的方法:
记录慢查询日志:用来记录查询较慢的SQL日志,开启后只要有查询较慢的语句都会被记录;
分析查询日志,不要直接打开慢查询日志进行分析,这样比较浪费时间和经理,可以使用pt-query-digest工具进行分析
B、使用show profile
set profile=1;开启,服务器上执行的所有语句会检测消耗的时间,存储到临时表中,我们还可以通过这个临时表的主键ID查询哪一条SQL语句在什么地方出现了慢操作
(show profiles; show profile for query ID; )
C、使用show status
show status会返回一些计数器,show global status查看服务器级别的所有计数;
有时根据这些计数可以猜测出哪些操作代价较高或者消耗时间多;
D、使用show processlist
观察是否有大量现成处于不正常的状态或者特征
E、使用explain/desc
分析单条SQL语句
②优化查询过程中的数据访问
访问数据太多导致查询性能下降;
确定应用程序是否在检索大量超过需要的数据,可能是太多行或列;
确认MySQL服务器是否在分析大量不必要的数据行;
【避免使用如下SQL数据:
查询不需要的记录,使用limit解决;
多表关联返回全部列,指定A.id,A.name,B.age;
总是取出全部列,SELECT * 会让优化器无法完成索引覆盖扫描的优化
重复查询相同的数据,可以缓存数据,下次直接读取缓存;
是否在扫描额外的记录:
使用explain来进行分析,如果发现查询需要扫描大量的数据,但只返回少数的行,可以通过如下技巧去优化:使用索引覆盖扫描,把所有用的列都放到索引中,这样存储引擎不需要返回表获取对应行就可以返回结果;还可以修改表数据库和表结构,修改数据表范式;还可以重写SQL语句,让优化器可以以更优的方式执行查询;
】
③如何优化长难的查询语句
(一个复杂查询还是多个简单查询好:MySQL内部每秒能烧苗内存中上百万行数据,相比之下,响应数据给客户端就要慢的多;
使用尽可能少的查询是好的,但有时将一个大的查询分解为多个小的查询是很有必要的;
)
A、切分查询
将一个大的查询分为多个小的相同的查询
B、分解关联查询
可以将一条关联语句分解成多条SQL来执行,
让缓存效率更高,
执行单个查询可以减少锁的竞争,
在应用层做关联可以更容易对数据库进行拆分,
查询效率会大幅提升,
较少冗余记录查询
④优化特定类型的查询语句
A、优化 count() 查询:
count(*)中的*会忽略所有的列,直接统计所有列数,因此不要使用 count(列名)
MyISAM中,没有任何WHERE条件的 count(*)非常快;
当有WHERE条件,MyISAM的count统计不一定比其他表引擎快,
可以使用explain查询近似值,用近似值代替 count(*),
增加汇总表
使用缓存
B、优化关联查询:
确定ON或者USIGN字句的列上有索引
确保GROUP BY或者 ORDER BY 中只有一个表中的列,这样MySQL才有可能使用索引
C、优化子查询:
尽可能使用关联查询来替代
D、优化GROUP BY 和DISTINCT:
这两种查询均可使用索引来优化,是最有效的优化方法
关联查询中,使用标识列进行分组的效率会更高,
如果不需要ORDER BY,进行GROUP BY时使用ORDER BY NULL,MySQL不会再进行文件排序,
WITH ROLLUP超级聚合可以挪到应用程序处理;
E、优化LIMIT 分页:
LIMTI 偏移量大的时候,查询效率较低,
可以记录上次查询的最大ID,下次查询时直接根据该ID来查询
F、优化UNION:
UNION ALL 的效率高于UNION
Mysql查询优化问题的处理方法:先说明如何定位低效SQL语句,然后根据SQL语句可能低效的原因做排查,先从索引着手,如果索引没有问题,考虑以上几个方面,数据访问的问题,长难查询语句的问题还是一些特定类型优化的问题。