Mysql中怎么优化慢查询

发布时间：2021-06-18 18:03:38 作者：Leah
来源：亿速云阅读：156

今天就跟大家聊聊有关Mysql中怎么优化慢查询，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。

一、打开Mysql的慢查询日志：

方式一：修改my.ini

//定义多少秒的查询算是慢查询，这里定为2秒
long_query_time=2

//5.5及以上版本配置如下
slow-query-log=on
slow_query_log_file="mysql_slow_query.log"

//记录没有使用索引的query
log-query-not-using-indexes

方式二：控制台开启

set global slow_query_lon=ON
set global long_query_time=3600
set global log_querise_not_using_indexes=ON

二、使用EXPLAIN分析sql慢查询语句

Mysql中怎么优化慢查询

上图为使用了索引，下图没有。

type从好到坏依次是：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL

const：使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描
eq_ref：出现在要连接过个表的查询计划中，驱动表只返回一行数据，且这行数据是第二个表的主键或者唯一索引，且必须为not null，唯一索引和主键是多列时，只有所有的列都用作比较时才会出现eq_ref
ref：不像eq_ref那样要求连接顺序，也没有主键和唯一索引的要求，只要使用相等条件检索时就可能出现，常见与辅助索引的等值查找。或者多列主键、唯一索引中，使用第一个列之外的列作为等值查找也会出现，总之，返回数据不唯一的等值查找就可能出现。
range：索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中。
index，ALL：相当与全表查询

阿里规约要求：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好

三、常用慢查询优化

索引失效的情况：

不在索引列上做任何操作（计算，函数，（自动或者手动）类型装换），会导致索引失效而导致全表扫描；
使用不等于(!= 或者<>)的时候，无法使用索引，会导致索引失效
使用is not null 或者 is null会导致无法使用索引
like查询是以%开头，索引会失效变成全表扫描，覆盖索引。
如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
如果mysql使用全表扫描要比使用索引快,则不会使用到索引

优化数据库结构
分表分库
优化LIMIT分页
当limit m,n的m过大时，导致每次查询都要先从整个表中找到满足条件的前M记录，之后舍弃这M条记录并从第M+1条记录开始再依次找到N条满足条件的记录。如果表非常大，且筛选字段没有合适的索引，且M特别大那么这样的代价是非常高的。

传统分页：

Select * from table limit 10000,10；

推荐分页：

Select * from table WHERE id>=23423 limit 11; (每页10条)

Select * from table WHERE id>=23434 limit 11;

Select * from table WHERE id >= ( select id from table limit 10000,1 ) limit 10;

Select * from table INNER JOIN (SELECT id from table limit 10000,10) USING(id)

程序取ID: Select id from table limit 10000,10;

Select * from table WHERE ID in(123,456…);

分页方式四：
分页方式三：
分页方式二：
分页方式一：

SELECT * FROM pre_forum_post ORDER BY pid ASC LIMIT 7332000,1000

select a.* from pre_forum_post a ,(select tid,position from pre_forum_post ORDER BY pid ASC LIMIT 7332000,1000) b where a.tid=b.tid and a.position=b.position

https://blog.csdn.net/qq_35513598/article/details/79813098

https://blog.csdn.net/qq_35571554/article/details/82800463

优化in查询
一条In查询：
select * from a where id in (select id from b );
他等同于：
select * from a where exists(select * from b where b.id=a.id );
而exists相关子查询的执行原理是: 循环取出a表的每一条记录与b表进行比较，比较的条件是a.id=b.id . 看a表的每条记录的id是否在b表存在，如果存在就行返回a表的这条记录。
由exists执行原理可知，a表(外表)使用不了索引，必须全表扫描，因为是拿a表的数据到b表查。而且必须得使用a表的数据到b表中查（外表到里表中），顺序是固定死的。
要想优化，只能在b上建索引，因为a表上的索引mysql利用不上。
进一步优化，把查询修改成inner join连接查询：
select * from a inner join b on a.id=b.id;
为什么不用left join 和 right join？这时候表之间的连接的顺序就被固定住了，比如左连接就是必须先查左表全表扫描，然后一条一条的到另外表去查询，右连接同理。仍然不是最好的选择。而inner join的顺序则是由mysql自己决定，最终选择最优的顺序执行。

看完上述内容，你们对Mysql中怎么优化慢查询有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注亿速云行业资讯频道，感谢大家的支持。

Mysql中怎么优化慢查询

一、打开Mysql的慢查询日志：

二、使用EXPLAIN分析sql慢查询语句

三、常用慢查询优化

相关阅读