Mysql SQL分析优化

小咪咪 2022-11-21 01:18 165阅读 0赞

## 慢SQL诱因 ##

1.  无索引、索引失效
2.  锁等待
    
    1.  InnoDB支持行锁，MyISAM支持表锁
    2.  InnoDB支持行锁更适合高并发场景，但行锁有可能会升级为表锁
        
        1.  一种情况是在批量更新时
        2.  行锁是基于索引加的锁，如果在更新操作时，条件索引失效，那么行锁会升级为表锁
    3.  基于表锁的数据库操作，会导致SQL阻塞等待，影响执行速度
        
        1.  在写大于读的情况下，不建议使用MyISAM
    4.  行锁相对于表锁，虽然粒度更细，并发能力提升，但也带来了新的问题，那就是死锁
3.  不恰当的SQL
    
    1.  SELECT \*
    2.  SELECT COUNT(\*)
    3.  大表中使用LIMIT M,N
    4.  对非索引字段进行排序

## SQL诊断 ##

### EXPLAIN ###

1.  id：每个执行计划都有一个id，如果是一个联合查询，会有多个id
2.  select\_type:
    
    1.  SIMPLE：普通查询，即没有联合查询、子查询
    2.  PRIMARY：主查询
    3.  UNION：UNION中后面的查询
    4.  SUBQUERY：子查询
3.  table：当前执行计划查询的表，如果表有别名，则显示别名
4.  partitions：分区表信息
5.  type
    
    1.  从表中查询到行所执行的方式
    2.  由好到坏：system > const > eq\_ref > ref > range > index > ALL
    3.  system/const： 表中只有一行数据匹配，根据索引查询一次就能找到对应的数据
    4.  eq\_ref： 使用唯一索引扫描，常见于多表连接中使用主键和唯一索引作为关联条件
    5.  ref：使用非唯一索引扫描，还可见于唯一索引最左原则匹配扫描
    6.  range：索引范围扫描，如<、>、between等操作
    7.  index：索引全表扫描，遍历整个索引树
    8.  ALL：全表扫描，遍历全表来找到对应的行
6.  possible\_keys：可能使用到的索引
7.  key：实际使用到的索引
8.  key\_len：当前使用的索引的长度，单位Byte
9.  ref：关联id等信息
10. rows：查找到记录所扫描的行数
11. filtered：查找到所需记录占总扫描记录数的比例
12. Extra：额外信息

### Show Profile ###

1.  通过EXPLAIN分析执行计划，仅仅停留在分析SQL的外部执行情况
    
    1.  如果需要深入MySQL内核，从执行线程的状态和时间来分析，就需要选择Profile
2.  Profile除了可以分析执行线程的状态和时间
    
    1.  还支持查询在ALL、CPU、MEMORY、BLOCK IO、CONTEXT SWITCHES上所消耗的时间
3.  MySQL是从5.0.37才开始支持Show Profile
4.  Show Profile只显示最新发给服务器的SQL语句，默认记录最新15条
    
    1.  可以设置profiling\_history\_size，最大值为100

mysql> select @@version;
    +------------+
    | @@version  |
    +------------+
    | 5.6.37-log |
    +------------+
    
    mysql> select @@have_profiling;
    +------------------+
    | @@have_profiling |
    +------------------+
    | YES              |
    +------------------+
    
    mysql> select @@profiling_history_size;
    +--------------------------+
    | @@profiling_history_size |
    +--------------------------+
    |                       15 |
    +--------------------------+
    
    mysql> select @@profiling;
    +-------------+
    | @@profiling |
    +-------------+
    |           0 |
    +-------------+
    
    mysql> set profiling = 1;
    
    mysql> select @@profiling;
    +-------------+
    | @@profiling |
    +-------------+
    |           1 |
    +-------------+
    
    mysql> show profiles;
    +----------+------------+---------------------------+
    | Query_ID | Duration   | Query                     |
    +----------+------------+---------------------------+
    |        1 | 0.03954925 | SELECT @@profiling        |
    |        2 | 0.01086300 | SELECT COUNT(1) FROM XXXX |
    +----------+------------+---------------------------+
    
    mysql> show profile for query 2;
    +----------------------+----------+
    | Status               | Duration |
    +----------------------+----------+
    | starting             | 0.000032 |
    | checking permissions | 0.000007 |
    | Opening tables       | 0.000012 |
    | init                 | 0.000009 |
    | System lock          | 0.000009 |
    | optimizing           | 0.000014 |
    | statistics           | 0.000013 |
    | preparing            | 0.000012 |
    | executing            | 0.000008 |
    | Sending data         | 0.010665 |
    | end                  | 0.000009 |
    | query end            | 0.000008 |
    | closing tables       | 0.000038 |
    | freeing items        | 0.000016 |
    | cleaning up          | 0.000012 |
    +----------------------+----------+

## SQL优化 ##

### 优化分页查询 ###

1.  经常使用LIMIT M,N+ORDER BY来实现分页查询
    
    1.  在没有任何索引条件支持的情况下，需要做大量的文件排序操作（file sort），性能很差
    2.  即便有对应的索引，也只是在刚开始时效率比较理想，越往后，性能越差
        
        1.  使用LIMIT M,N时，偏移量M越大，数据库检索的数据也会越多
        2.  例如LIMIT 10000,10，数据库需要检索10010条记录，但最后只返回10条记录
2.  优化方案：子查询 + 覆盖索引

-- 使用了索引，扫描了100010行
    mysql> explain select * from prop_action_reward order by create_time limit 100000,10;
    +----+-------------+--------------------+-------+---------------+-----------------+---------+------+--------+-------+
    | id | select_type | table              | type  | possible_keys | key             | key_len | ref  | rows   | Extra |
    +----+-------------+--------------------+-------+---------------+-----------------+---------+------+--------+-------+
    |  1 | SIMPLE      | prop_action_reward | index | NULL          | idx_create_time | 5       | NULL | 100010 | NULL  |
    +----+-------------+--------------------+-------+---------------+-----------------+---------+------+--------+-------+
    
    -- 耗费了0.19S，性能不太理想
    mysql> select * from prop_action_reward order by create_time limit 100000,10;
    ....
    10 rows in set (0.19 sec)
    
    -- 查询获取到的100010条记录都返回给客户端了，耗时主要集中在Sending data阶段
    mysql> show profile for query 21;
    +----------------------+----------+
    | Status               | Duration |
    +----------------------+----------+
    | starting             | 0.000037 |
    | checking permissions | 0.000007 |
    | Opening tables       | 0.000016 |
    | init                 | 0.000028 |
    | System lock          | 0.000008 |
    | optimizing           | 0.000006 |
    | statistics           | 0.000010 |
    | preparing            | 0.000011 |
    | Sorting result       | 0.000005 |
    | executing            | 0.000004 |
    | Sending data         | 0.192705 |
    | end                  | 0.000018 |
    | query end            | 0.000008 |
    | closing tables       | 0.000010 |
    | freeing items        | 0.000029 |
    | cleaning up          | 0.000085 |
    +----------------------+----------+

-- 子查询用到了覆盖索引（Using index），无需回表
    mysql> explain select * from prop_action_reward where id > (select id from prop_action_reward order by create_time limit 100000,1) limit 10;
    +----+-------------+--------------------+-------+---------------+-----------------+---------+------+----------+-------------+
    | id | select_type | table              | type  | possible_keys | key             | key_len | ref  | rows     | Extra       |
    +----+-------------+--------------------+-------+---------------+-----------------+---------+------+----------+-------------+
    |  1 | PRIMARY     | prop_action_reward | range | PRIMARY       | PRIMARY         | 8       | NULL | 47244120 | Using where |
    |  2 | SUBQUERY    | prop_action_reward | index | NULL          | idx_create_time | 5       | NULL | 94488240 | Using index |
    +----+-------------+--------------------+-------+---------------+-----------------+---------+------+----------+-------------+
    
    -- 耗费了0.03S，提升很大
    mysql> select * from prop_action_reward where id > (select id from prop_action_reward order by create_time limit 100000,1) limit 10;
    ...
    10 rows in set (0.03 sec)
    
    -- 只会返回10条记录给客户端，所以快很多
    mysql> show profile for query 24;
    +----------------------+----------+
    | Status               | Duration |
    +----------------------+----------+
    | starting             | 0.000064 |
    | checking permissions | 0.000007 |
    | checking permissions | 0.000007 |
    | Opening tables       | 0.000019 |
    | init                 | 0.000030 |
    | System lock          | 0.000009 |
    | optimizing           | 0.000008 |
    | statistics           | 0.000022 |
    | optimizing           | 0.000007 |
    | statistics           | 0.000011 |
    | preparing            | 0.000015 |
    | Sorting result       | 0.000005 |
    | executing            | 0.000004 |
    | Sending data         | 0.028916 |
    | preparing            | 0.000013 |
    | executing            | 0.000005 |
    | Sending data         | 0.000055 |
    | end                  | 0.000006 |
    | query end            | 0.000007 |
    | closing tables       | 0.000009 |
    | freeing items        | 0.000022 |
    | cleaning up          | 0.000013 |
    +----------------------+----------+

### 优化SLECT COUNT(\*) ###

1.  COUNT()是一个聚合函数，用来统计行数或某一列的行数量（不包括NULL值）
2.  常用的是COUNT(\*)和COUNT(1)，两者没有本质区别，在InnoDB，都会利用主键列实现行数的统计
3.  通常没有任何查询条件下的COUNT(\*)，MyISAM的查询速度要明显快于InnoDB
    
    1.  这是因为MyISAM记录了整个表的行数，无需遍历计算，直接获取即可，而InnoDB需要扫描表来统计具体的行数
    2.  如果带上查询条件，MyISAM和InnoDB都需要扫描表来进行行数的统计
4.  优化方案：
    
    1.  使用近似值，借助EXPLAIN中的rows
    2.  增加汇总统计，使用汇总统计表或缓存

### 优化SLECT \* ###

1.  尽量使用覆盖索引

## 记录慢SQL ##