首页 > 试题广场 >

你对MySQL的慢查询优化有了解吗

[问答题]
你对MySQL的慢查询优化有了解吗 
添加回答
推荐
可乐加冰upuppp头像 可乐加冰upuppp

得分点

​ 慢查询日志

参考答案

标准回答

​ 慢查询优化的前提是定位到响应慢的SQL，这可以通过启用慢查询日志来实现。默认情况下，MySQL并不启用慢查询日志，我们需要手动开启这个参数。通过日志定位到慢查询的SQL之后，我们可以使用EXPLAIN语句来分析这个SQL，进而发现问题所在。导致慢查询的原因有很多，下面列举几种常见的原因，以及对应的解决方案：

  1. 向数据库请求了多余的数据：

    很多时候，我们的SQL返回的结果会超出我们的需要，例如实际上它返回了更多的行，而我们只要其中的一部分。又或者我们要求返回所有的列，实际上却只有其中少数的列。对于这类问题，我们可以通过LIMIT控制返回的行数，尽量不用SELECT *避免查询到过多的列。

  2. SQL复杂导致无法利用缓存：

    处于业务的需要，我们经常会写出比较复杂的SQL，这自然包括复杂的关联查询。由于复杂SQL返回的结果涉及多张表、多个条件、甚至各种函数，这样的SQL每次返回的结果势必不同，所以很难利用到数据库的缓存。如果我们将复杂SQL进行拆分，变成若干简单的SQL，那么其中有些SQL由于条件不变，就可以利用到数据库的缓存了，从而让查询效率得以提升。

  3. 没有选择正确的索引：

    我们都知道，创建索引是提高查询效率的一个常用手段，事实上我们也经常会这样做。但是，很多时候我们创建了索引，通过EXPLAIN查看会发现并没有走这个索引，最终导致SQL执行变慢。所以，不是把索引创建出来就算完成任务，还要分析索引的选择性，根据业务条件不断的优化索引，从而增加索引的命中率。

加分回答

​ 除上述优化的方向之外，SQL中还有很多地方都有优化的空间，例如COUNT()、关联查询、子查询、GROUP BY、LIMIT、UNION等。总体来说，不同的情况要区别对待，但所有优化的背后是基于慢查询日志的定位。另外，为了能够发现问题的本质，还需要对MySQL执行查询的过程有所了解：

  1. 客户端发送一条查询SQL给服务器。
  2. 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。
  3. 服务器进行SQL解析和预处理，再由优化器生成对应的执行计划。
  4. 服务器根据优化器生成的执行计划，调用存储引擎的API来执行查询。
  5. 将结果返回给客户端。

延伸阅读

​ B+树索引是基于B+树构建出来的有序结构，只有利用上它的有序性才能提高查询的效率。若不满足有序性这个前提，则在这个索引中的查询是离散的，其效率反而更低。查询优化器对索引的选择性，被称为最左前缀原则。

​ 假设有如下一张表：

CREATE TABLE t (
  a VARCHAR(100),
  b VARCHAR(100),
  c VARCHAR(100),
  KEY idx_union(a,b,c)
) ENGINE=INNODB;

​ 假设idx_union的叶子节点数据如下：

(1,3,2), (1,3,3), (1,3,9), (1,3,9), (1,7,4), (1,7,8), (2,1,5), (2,1,7), (2,5,1), (2,5,6)

​ 该索引的选择性示例如下：

-- 匹配左前缀
select * from T where a='';    -- Y
select * from T where b='';    -- N
-- 匹配列前缀
select * from T where a like 'x%';    -- Y
select * from T where a like '%x';    -- N
select * from T where b like 'x%';    -- N
-- 全值匹配
select * from T where a='' and b='' and c='';    -- Y
select * from T where c='' and b='' and a='';    -- Y
-- 匹配范围值
select * from T where a between '' and '';    -- Y
select * from T where b between '' and '';    -- N
-- 全值匹配 + 范围匹配
select * from T where a='' and b between '' and '';    -- Y
select * from T where b='' and c between '' and '';    -- N
select * from T where a between '' and '' and b='';    -- N
编辑于 2021-09-15 15:39:29 回复(0)
~闲人~头像 ~闲人~
一、慢查询原因
要对慢查询进行优化，首先要搞清楚慢查询的原因，原因主要有三：

（1）加载了不需要的数据列

（2）查询条件没有命中索引

（3）数据量太大

二、优化方案
优化也是针对这三个方向的：

（1）先分析语句，看看是否加载了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，如果有这些问题，则对语句进行分析、重写

（2）分析语句的执行计划，获得其使用索引的情况，然后修改语句或修改索引，使得语句尽可能地命中索引

（3）如果对语句的优化都已经无法进行了，可以考虑是否是表中数据量太大引起的慢查询，如果是，则可以进行横向或者纵向分表

发表于 2022-08-18 15:27:52 回复(0)
牛客535632288号头像 牛客535632288号
慢sql的情况有很多：a 没有查询条件或者有查询条件没有建索引，全表查询；
b 查询过程索引失效；
c 索引建立的混乱，组合索引中区分度越高的列越放在前面；
d 尽量使用索引覆盖（避免回表）、索引下推（尽量让存储引擎来处理）来提高性能；
e 查询出的数据量过大，mysql内存、io、网卡等负载过高

排查慢sql：
a mysql开启慢查询日志
b 在慢查询日志中查看耗时较长的sql
c 使用mysql自带的执行计划查看sql的执行计划，进行分析索引情况
d 使用mysql自带的show profile，查看sql执行过程中硬件资源利用情况 和 具体sql执行每一步的情况

慢sql解决：
a 服务器层面优化，扩大buffer pool，保障数据从内存中读取
b 增大redolog，减少落盘次数
c 设计需要在三范式和查询性能之间取舍，适当字段冗余，减少查询关联
d 多字段表，进行拆分；
e sql语句优化：索引优化，利用索引覆盖和索引下推等
f 对于结果数据量较大时，建议进行分批查询，减少对io和网络的占用
发表于 2023-03-01 15:15:23 回复(0)
牛客381637310号头像 牛客381637310号
方式一： 修改配置文件  在 my.ini 增加几行:  主要是慢查询的定义时间（超过2秒就是慢查询），以及慢查询log日志记录（ slow_query_log）,
（1）索引没起作用的情况
    1. 使用LIKE关键字的查询语句

        在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不会起作用。只有“%”不在第一个位置索引才会起作用。

    2. 使用多列索引的查询语句

        MySQL可以为多个字段创建索引。一个索引最多可以包括16个字段。对于多列索引，只有查询条件使用了这些字段中的第一个字段时，索引才会被使用。

 

 （2）优化数据库结构
        合理的数据库结构不仅可以使数据库占用更小的磁盘空间，而且能够使查询速度更快。数据库结构的设计，需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。

1. 将字段很多的表分解成多个表 

        对于字段比较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

2. 增加中间表

        对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。

 

（3）分解关联查询
    将一个大的查询分解为多个小查询是很有必要的。

  很多高性能的应用都会对关联查询进行分解，就是可以对每一个表进行一次单表查询，然后将查询结果在应用程序中进行关联，很多场景下这样会更高效，
（4）优化LIMIT分页
      在系统中需要分页的操作通常会使用limit加上偏移量的方法实现，同时加上合适的order by 子句。如果有对应的索引，通常效率会不错，否则MySQL需要做大量的文件排序操作。

      一个非常令人头疼问题就是当偏移量非常大的时候，例如可能是limit 10000,20这样的查询，这是mysql需要查询10020条然后只返回最后20条，前面的10000条记录都将被舍弃，这样的代价很高。

        优化此类查询的一个最简单的方法是尽可能的使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候这样做的效率会得到很大提升。

      对于下面的查询：

       select id,title from collect limit 90000,10;

      该语句存在的最大问题在于limit M,N中偏移量M太大（我们暂不考虑筛选字段上要不要添加索引的影响），导致每次查询都要先从整个表中找到满足条件 的前M条记录，之后舍弃这M条记录并从第M+1条记录开始再依次找到N条满足条件的记录。如果表非常大，且筛选字段没有合适的索引，且M特别大那么这样的代价是非常高的。 试想，如我们下一次的查询能从前一次查询结束后标记的位置开始查找，找到满足条件的100条记录，并记下下一次查询应该开始的位置，以便于下一次查询能直接从该位置 开始，这样就不必每次查询都先从整个表中先找到满足条件的前M条记录，舍弃，在从M+1开始再找到100条满足条件的记录了。

方法一：虑筛选字段（title）上加索引
       title字段加索引  （此效率如何未加验证）

 

方法二：先查询出主键id值
select id,title from collect where id>=(select id from collect order by id limit 90000,1) limit 10;

原理：先查询出90000条数据对应的主键id的值，然后直接通过该id的值直接查询该id后面的数据。

 

方法三：“关延迟联”
如果这个表非常大，那么这个查询可以改写成如下的方式：

      Select news.id, news.description from news inner join (select id from news order by title limit 50000,5) as myNew using(id);

        这里的“关延迟联”将大大提升查询的效率，它让MySQL扫描尽可能少的页面，获取需要的记录后再根据关联列回原表查询需要的所有列。这个技术也可以用在优化关联查询中的limit。

 

方法四：建立复合索引 acct_id和create_time
    select * from acct_trans_log WHERE  acct_id = 3095  order by create_time desc limit 0,10

     注意sql查询慢的原因都是:引起filesort

 

（5）分析具体的SQL语句
 1、两个表选哪个为驱动表，表面是可以以数据量的大小作为依据，但是实际经验最好交给mysql查询优化器自己去判断。
  例如：  select * from a where id in (select id from b );  
        对于这条sql语句它的执行计划其实并不是先查询出b表的所有id,然后再与a表的id进行比较。
mysql会把in子查询转换成exists相关子查询，所以它实际等同于这条sql语句：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查询有什么弊端？
      由exists执行原理可知，a表(外表)使用不了索引，必须全表扫描，因为是拿a表的数据到b表查。而且必须得使用a表的数据到b表中查（外表到里表中），顺序是固定死的。

如何优化？
      建索引。但是由上面分析可知，要建索引只能在b表的id字段建，不能在a表的id上，mysql利用不上。

这样优化够了吗？还差一些。
      由于exists查询它的执行计划只能拿着a表的数据到b表查（外表到里表中），虽然可以在b表的id字段建索引来提高查询效率。
但是并不能反过来拿着b表的数据到a表查，exists子查询的查询顺序是固定死的。

为什么要反过来？
       因为首先可以肯定的是反过来的结果也是一样的。这样就又引出了一个更细致的疑问：在双方两个表的id字段上都建有索引时，到底是a表查b表的效率高，还是b表查a表的效率高？

该如何进一步优化？
       把查询修改成inner join连接查询：select * from a inner join b on a.id=b.id; （但是仅此还不够，接着往下看）

为什么不用left join 和 right join？
       这时候表之间的连接的顺序就被固定住了，比如左连接就是必须先查左表全表扫描，然后一条一条的到另外表去查询，右连接同理。仍然不是最好的选择。

为什么使用inner join就可以？
       inner join中的两张表，如： a inner join b，但实际执行的顺序是跟写法的顺序没有半毛钱关系的，最终执行也可能会是b连接a，顺序不是固定死的。如果on条件字段有索引的情况下，同样可以使用上索引。

那我们又怎么能知道a和b什么样的执行顺序效率更高？
       你不知道，我也不知道。谁知道？mysql自己知道。让mysql自己去判断（查询优化器）。具体表的连接顺序和使用索引情况，mysql查询优化器会对每种情况做出成本评估，最终选择最优的那个做为执行计划。

        在inner join的连接中,mysql会自己评估使用a表查b表的效率高还是b表查a表高，如果两个表都建有索引的情况下，mysql同样会评估使用a表条件字段上的索引效率高还是b表的。


利用explain字段查看执行时运用到的key（索引）
       而我们要做的就是：把两个表的连接条件的两个字段都各自建立上索引，然后explain 一下，查看执行计划，看mysql到底利用了哪个索引，最后再把没有使用索引的表的字段索引给去掉就行了。


————————————————
版权声明：本文为CSDN博主「夏至&未至」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_35571554/article/details/82800463


发表于 2021-11-11 11:10:07 回复(1)
从心所欲不逾矩头像 从心所欲不逾矩
最左前缀原则，尽量少使用聚合函数， 尽量扩展索引，不要新建索引，设计好表结构，中间表，字段多的分表，explain关键字分析执行计划。like模糊查询的‘%’不可以放在最前面，不要使用or,union，not in这些进行条件查询。
发表于 2022-07-31 11:16:16 回复(0)
牛客338203469号头像 牛客338203469号
copy:
  1. 最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整；
  2. =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式；
  3. 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录；
  4. 索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
  5. 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

发表于 2022-04-12 16:35:23 回复(0)
提交观点

问题信息

上传者：小小
难度：
6条回答 1621浏览

热门推荐

相关试题

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

扫描二维码，关注牛客公众号