平武建站

Mysql中怎么优化慢查询

今天就跟大家聊聊有关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中怎么优化慢查询

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 最好

三、常用慢查询优化

  1. 索引失效的情况：

    • 不在索引列上做任何操作（计算，函数，（自动或者手动）类型装换），会导致索引失效而导致全表扫描；

    • 使用不等于(!= 或者<>)的时候，无法使用索引，会导致索引失效

    • 使用is not null 或者 is null会导致无法使用索引

    • like查询是以%开头，索引会失效变成全表扫描，覆盖索引。

    • 如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引

    • 如果mysql使用全表扫描要比使用索引快,则不会使用到索引

  2. 优化数据库结构

    分表分库

  3. 优化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

  1. 优化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中怎么优化慢查询
链接分享：http://pwwzsj.com/article/gesjic.html

其他资讯