由表及里，彻底解决sql慢查询

解决sql慢查询

---

前言

做服务端开发的同学应该都碰到过慢查询，你真的在对症下药解决问题吗，还是每次碰到后焦头烂额，病急乱投医呢，耐心的看完我的文章吧，将会给你不一样的收获。

一、MySql的索引介绍

1.索引分类

• 主键索引：一张表只能有一个主键索引，不允许重复、不允许为 NULL
• 唯一索引：数据列不允许重复，允许为 NULL 值，一张表可有多个唯一索引，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一（单列app_patient_user.user_telephone，组合app_gift_express.patient_id,activity_id）
• 普通索引：一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入
• 全文索引：它查找的是文本中的关键词，主要用于全文检索
• 以上索引分类是根据功能来划分的，还有一种分类是按照物理存储来分类，即聚簇索引（数据和索引在一颗B+树上，innodb的主键索引）和非聚簇索引（索引和数据是分开的）

2.索引示意图

2.1 InnoDb主键索引

2.1 InnoDb辅助索引

可以很直观的看到，除了主键索引（聚簇索引）外，辅助索引（非聚簇索引）叶子节点存储的不再是行记录，而是主键值。所以通过辅助索引查找时，是先找到主键值，然后根据主键值在主键索引树上找到具体的数据。

2.3 组合索引查找示例

• 我们的联合索引是index(a,b)，a字段是有顺序的，在相同的a字段下，b字段才有顺序；单单b的值是没有顺序的。所以我们想要使索引生效可以根据a或者a，b作为筛选条件，但是不能只用b作为过滤条件。
• 所以联合索引在建立时，需要注意把最常用的列放在最左边。

2.4 B+树示例

推荐一个数据可视化网站

二、Sql慢查询产生的原因

• 未建立索引
• 索引失效
  • Or
    • or连接的非同一个字段
  • Like
    • “%xxx”，“%xxx%”
  • 联合索引不满足最左匹配
  • 对索引列进行运算
    • Where id + 1 = 2，where date(createdate) > date(“1992-07-16 05:04:50”)
  • !=、<>、is not null、not in 等
    • 不等于有时候也会走，可以force index(过滤条件的索引)
    • Where patient_name is not null
  • 关联字段编码不一样
    • utf8mb4 和 utf8
  • 隐式的类型转换
    • 索引是varchar类型，where user_id = 1
• 锁竞争
• 产生临时表
• 配置参数不合理，buff、cache、size等
• 硬件资源不够用

三、Explain Sql执行计划

官网文档

1. select_type

类型	释义
SIMPLE	简单的select查询，查询中不包含子查询或者UNION
PRIMARY	查询中若包含任何复杂的子部分，最外层查询被标记为PRIMARY
SUBQUERY	在SELECT或WHERE列表中包含了子查询
DERIVED	在FROM中包含的子查询被标记为DERIVED(衍生)
UNION	若第二个SELECT出现在UNION之后，则被标记为UNION，若UNION包含在FROM子句的子查询中，外层SELECT将被标记为DERIVED
UNION RESULT	从UNION表获取结果的SELECT

2. Table

• 查询的表

3. type

• system：是const类型的特例，系统表
• const：通过索引一次就找到了
• eq_ref：在连接查询时，副表通过主键或其他唯一索引匹配查询的
• ref：非唯一索引扫描
• range：检索给定的范围，between，in等
• index：另一种形式的全表扫描，order by
• ALL：全表扫描
• type值从优到差排序：system > const > eq_ref > ref > range > index > ALL

4. partitions

• 分区，对于非分区表，值为NULL

5. possible_keys

• 可能用到的索引

6. key

• 实际用到的索引

7. key_len

• 索引里使用的字节数

8. ref

• 关联字段

9. rows

• 大概的扫描行数

10. Extra 非常重要的的扩展信息

• Using filesort：Mysql无法利用索引进行排序导致，称为“文件排序”
• Using temporary：使用临时表保存中间结果，order by，group by等操作
• Using where：使用了where过滤
• Using join buffer：使用了连接缓存，多见于连接字段未建立索引
• Using index condition：命中索引，但是不是所有的列数据都在索引树上
• Using filesort 和 Using temporary 都是需要特别关注的，如果超出sort_buffer_size或者max_heap_table_size，就会从内存中加载到磁盘中操作，此时效率就会极大的打折扣

四、解决慢查询的思路

• Explain + sql 查看执行计划，有针对的去优化sql
• 优化数据访问：
  • 是否向数据库请求了不需要的数据
  • 是否扫描了额外的记录
  • 通过建立索引（如经常用createdate进行排序），索引失效的通过优化语句使索引生效
• 重构查询查询方式
  • 一个复杂查询还是多个简单查询，当然这个要好好衡量提高的效率和会增加的工作量
  • 切分查询，一个大的查询切分成小查询，其实我们的分页就是
• 优化特定类型的查询
  • 优化关联查询，order by，group by
  • count，count不加where条件是很快的，巧妙的使用会提升很大的效率，如xxx表数据量很大，假设有100w吧

    select count(1) from xxx where id > 100; 
    
    // 扫描行数会大大的降低
    select (select count(1) from xxx) - (select count(1) from xxx where id <= 100)
    

  • 优化limit

    select * from xxx limit 10000, 20;
    
    select * from xxx where id > 200000 limit 20;
    

五、实战操作

1.优化查询语句，拆分查询

• 我们来看看appview_all_user这个视图

-- appview_all_user

SELECT
        `u`.`user_id` AS `user_id`,
        `u`.`user_telephone` AS `login_name`,
        `u`.`user_password` AS `user_password`,
        `u`.`user_name` AS `nickname`,
        `pd`.`patient_name` AS `name`,
        `u`.`user_level` AS `user_level`,
        `u`.`user_face_url` AS `face_url`,
        `u`.`user_scope` AS `user_scope`,
        `s`.`scope_name` AS `scope_name`
FROM
        (
                (
                        `app_patient_user` `u`
                        LEFT JOIN `app_patient_detail` `pd` ON (
                                (
                                        `u`.`user_id` = `pd`.`patient_id`
                                )
                        )
                )
                LEFT JOIN `scope_info` `s` ON (
                        (
                                `u`.`user_scope` = CONVERT (
                                        `s`.`scope_code` USING utf8mb4
                                )
                        )
                )
        )
WHERE
        (`u`.`user_valid` = 'Y')
UNION
        SELECT
                `u`.`user_id` AS `user_id`,
                `u`.`user_name` AS `login_name`,
                `u`.`user_password` AS `user_password`,
                `ud`.`user_name` AS `nickname`,
                `ud`.`user_name` AS `name`,
                `u`.`user_level` AS `user_level`,
                `a`.`avatar` AS `face_url`,
                `u`.`user_scope` AS `user_scope`,
                `s`.`scope_name` AS `scope_name`
        FROM
                (
                        (
                                (
                                        `userlogininfo` `u`
                                        LEFT JOIN `sys_user` `a` ON ((`u`.`user_id` = `a`.`id`))
                                )
                                LEFT JOIN `user_detailinfo` `ud` ON (
                                        (
                                                `u`.`user_id` = `ud`.`user_id`
                                        )
                                )
                        )
                        LEFT JOIN `scope_info` `s` ON (
                                (
                                        `u`.`user_scope` = `s`.`scope_code`
                                )
                        )
                )
        WHERE
                (`u`.`user_valid` = 'Y')

• 就为了from和to两个名字关联了两次appview_all_user视图，的确方便，但是肯定会慢
• 改进的方法
  • 优化视图，你就写个查询接口费那么大劲干啥，搞不好影响了其他业务
  • 优化我们的sql，是不是快多了

    SELECT
            adcm.*, 
    case adcm.`from` when LENGTH(adcm.`from`) < 30 then (select `name` from sys_user where id = adcm.from) else (select patient_name from app_patient_detail where patient_id = adcm.from) end from_name,
    case adcm.`from` when LENGTH(adcm.`to`) < 30 then (select `name` from sys_user where id = adcm.to) else (select patient_name from app_patient_detail where patient_id = adcm.to) end to_name
    FROM
            app_doctor_consult_message adcm
    WHERE
            adcm.doctor_consult_id = 6824;
    

• 如果嫌上面的sql写的麻烦，且复用性不高，那就写成个函数吧

  CREATE DEFINER=`root`@`%` FUNCTION `function_user_name`(user_id VARCHAR(50) RETURNS varchar(300) CHARSET utf8mb4
  BEGIN
  	DECLARE user_name VARCHAR(300);
      IF LENGTH(user_id) < 30 THEN
        SET user_name = (SELECT `name` FROM sys_user WHERE id = user_id);
      ELSE
        SET user_name = (SELECT patient_name FROM app_patient_detail WHERE patient_id = user_id);
      END IF;
      RETURN user_name;
  END
  

• 如果还嫌麻烦，那就别用一条sql来查了，换个思路吧，我们遍历一下再set，至于为什么不在循环外层查询再遍历呢，有缓存在哪都一样
  

2.增加索引

• 先來看原sql

  EXPLAIN
  SELECT
          t.tele_id,
          t.tele_result,
          t.tele_source,
          t.tele_note,
          t.tele_createdate,
          t.tele_stimId,
          t.tele_model,
          ud.user_name
  FROM
          pinsmed3.tc_telepro_info t
  LEFT JOIN pinsmed3.pp_sn_parser psp ON t.tele_stimId = psp.pp_stimid AND t.tele_model = psp.pp_model
  LEFT JOIN pinsmed3.user_detailinfo ud ON psp.user_id = ud.user_id
  WHERE
          t.user_id = 1054
  ORDER BY
          t.tele_createdate DESC
  LIMIT 10
  

• 在这里插一句，为啥在尝试优化的时候（tele_stimId和pp_stimid还没加索引）用force index（USERID）会提升查询效率，我们来看一下tc_telepro_info有哪些索引
  

• 在有多个索引的情况下，并且有多个索引列参与过滤，不妨对比走单列索引的耗时情况，强制指定性能最高的索引

• 这已经优化过了一次了，在pp_sn_parser的tele_stimId和pp_stimid加了索引
  
  但是你会发现有时候依旧不是很快
  
  如果你把ORDER BY t.tele_createdate DESC会发现非常丝滑
  [图片]
  我们来看看这个tele_result，这这一堆数据都加载出来再来个排序，能快了才有鬼呢

  [129, 13, 183, 96, 18, 0, 0, 3, 1, 1, 0, 19, 2, 3, 0, 0, 1, 0, 81, 9, 0, 0, 1, 0, 205, 245, 190, 178, 183, 210, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 19, 7, 5, 65, 38, 4, 245, 30, 38, 0, 0, 0, 0, 0, 55, 60, 161, 39, 176, 0, 2, 1, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 129, 24, 0, 64, 0, 3, 70, 0, 0, 0, 3, 3, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 24, 0, 70, 0, 3, 70, 0, 0, 0, 3, 3, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 148, 12, 9, 0, 0, 0, 148, 148, 35, 0, 35, 0, 0, 0, 0, 129, 128, 1, 48, 0, 4, 48, 0, 48, 0, 4, 4, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 128, 1, 67, 0, 3, 67, 0, 67, 0, 3, 3, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 128, 29, 9, 0, 0, 4, 128, 128, 3, 0, 3, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 4, 81, 59, 38, 31, 4, 81, 59, 38, 219, 4, 81, 61, 38, 33, 4, 81, 63, 38, 130, 4, 81, 63, 38, 173, 4, 81, 63, 38, 174, 4, 81, 63, 38, 174, 4, 81, 63, 38, 213, 4, 81, 63, 38, 213, 1, 77, 64, 38, 223, 1, 77, 64, 38, 223, 4, 81, 55, 38, 128, 4, 81, 55, 38, 129, 4, 81, 57, 38, 227, 4, 81, 57, 38, 228, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 96, 1, 172, 0, 0]
  

• 再来优化一下吧，可以index(user_id,tele_createdate) 建立一个组合索引，也是非常符合我们的使用场景的，看下图，是不是文件排序消失了

• 

3.再來巩固一下索引的重要性

• 数据是20w条，非常简单的一条查询，这也是我们业务开发中用的非常多的一条sql，create_time未建立索引
  
• 这是对create_time建立的索引，rows从11w降到3000左右，filtered从11.11%到了100%

---

总结

我们来总结一下，我们知道了mqsql的检索原理后，碰到慢查询不用慌，无非就是那几种情况，结合sql执行计划对sql语句进行分析，先优化sql语句和索引，这样就能解决80%的问题。如果都没问题但是还是会有慢查询就要分情况处理了，数据量大我们进行分表，数据量不大那就去检查配置参数和硬件资源配置，我们下一篇再分享这20%的情况。