SQL优化

1. SQL优化的方法

1. 发现问题
2. 分析sql执行计划
3. 优化索引
4. 改下SQL
5. 数据库垂直切分
6. 数据库水平切分

2. 发现问题

常见问题发现的渠道

• 用户主动上报应用性能问题
• 分析慢查询日志发现存在问题的SQL
• 数据库实时监控长时间运行的SQL

2.1 通过慢查询日志发现问题

配置MySQL慢查询日志

• 打开/关闭慢查询 set global slow_query_log = [ON | OFF]
• 指定慢查询日志文件位置 set global slow_query_log_file=/sql_log/slowlog.log
• 设置慢查询执行时间界限 set global long_query_time=xx.xxx 秒，设置为0，则记录所有查询日志
• 记录所有未使用索引的查询日志 set global log_queries_not_using_indexes=[ON | OFF]

2.2 分析MySQL慢查询日志工具

• MySQL 官方提供

  mysqldumpslow [OPTS...] [LOGS...]
  

• 另一个好用的工具

  pt-query-digest [OPTIONS] [FILES] [DSN]
  

2.3 如何使用慢查询工具

2.3.1 安装percona-toolkit.rpm包

1. 下载安装包 https://www.percona.com/downloads/percona-toolkit/LATEST/
   选择下载版本和安装软件的操作系统版本，如下图：
   

2. 安装依赖包：

   yum install -y perl-DBD-MySQL.x86_64 perl-DBI.x86 perl-Time-HiRes.x86_64 perl-IO
   -Socket-SSL.noarch perl-TermReadKey.x86_64 perl-Digest-MD5
   

3. 安装软件：rpm -ivh percona-toolkit-3.0.13-1.el7.x86_64.rpm

2.3.2 设置数据库慢查询参数

1. 查看long_query_time值，并修改为合适的值

   -- 查看慢查询设置时间
   mysql> show variables like "long_query_time";
   +-----------------+-----------+
   | Variable_name   | Value     |
   +-----------------+-----------+
   | long_query_time | 10.000000 |
   +-----------------+-----------+
   1 row in set (0.00 sec)
   

   -- 设置慢查询时间为100ms
   mysql> set global long_query_time = 0.1;  
   Query OK, 0 rows affected (0.01 sec)
   

2. 打开慢查询

   -- 查看慢查询日志记录位置
   show variables like "slow_query_log_file";
   +---------------------+--------------------------------------+
   | Variable_name       | Value                                |
   +---------------------+--------------------------------------+
   | slow_query_log_file | /usr/local/mysql/sql_log/slowlog.log |
   +---------------------+--------------------------------------+
   1 row in set (0.01 sec)
   

   -- 查看当前慢查询是否打开
   mysql> show variables like "slow_query_log";
   +----------------+-------+
   | Variable_name  | Value |
   +----------------+-------+
   | slow_query_log | OFF   |
   +----------------+-------+
   1 row in set (0.01 sec)
   

   -- 打开慢查询功能
   mysql> set global slow_query_log=on;
   Query OK, 0 rows affected (0.03 sec)
   

2.3.3 慢查询日志格式

# Time: 2020-01-06T23:36:23.334345Z  # 查询发生的时间点
# User@Host: root[root] @ localhost []  Id:    63  # 查询是的用户，和用户IP， 线程ID号
# Query_time: 0.001011  Lock_time: 0.000739 Rows_sent: 1  Rows_examined: 0  # 查询耗时，lock_time， 返回行数， 扫描行数
SET timestamp=1578353783;
select count(*) from imc_course;

2.3.4 使用工具分析慢查询日志

1. mysqldumpslow 工具： mysqldumpslow /usr/local/mysql/sql_log/slowlog.log

   Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost
     select count(*) from imc_user
   
   Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost
     select count(*) from imc_course
   

2. pt-query-digest 工具：pt-query-digest /usr/local/mysql/sql_log/slowlog.log

# Query 4: 0 QPS, 0x concurrency, ID 0x2559C506979C78863D61A0997E47CC8F at byte 1687
# Scores: V/M = 0.00
# Time range: all events occurred at 2020-01-06T23:36:07
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         11       1
# Exec time      6     2ms     2ms     2ms     2ms     2ms       0     2ms
# Lock time      6     1ms     1ms     1ms     1ms     1ms       0     1ms
# Rows sent      4       1       1       1       1       1       0       1
# Rows examine   0       0       0       0       0       0       0       0
# Query size    13      29      29      29      29      29       0      29
# String:
# Databases    dba
# Hosts        localhost
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ################################################################
#  10ms
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `dba` LIKE 'imc_user'\G
#    SHOW CREATE TABLE `dba`.`imc_user`\G
# EXPLAIN /*!50100 PARTITIONS*/
select count(*) from imc_user\G

2.2 通过实时监控发现问题

监控长时间运行的SQL

SELECT id, user, host, DB, command, time, state, info 
FROM information_schema.PROCESSLIST
WHERE TIME>=60

mysql> SELECT id, user, host, DB, command, time, state, info  FROM information_schema.PROCESSLIST WHERE time>=10;
+----+-----------------+---------------------+------+---------+---------+------------------------+------+
| id | user            | host                | DB   | command | time    | state                  | info |
+----+-----------------+---------------------+------+---------+---------+------------------------+------+
| 64 | all_ip          | 192.168.1.139:63739 | dba  | Sleep   |     125 |                        | NULL |
|  4 | event_scheduler | localhost           | NULL | Daemon  | 1074626 | Waiting on empty queue | NULL |
+----+-----------------+---------------------+------+---------+---------+------------------------+------+
2 rows in set (0.00 sec)

3. 分析执行计划

• 了解SQL如何访问表中的数据
• 了解SQL如何使用表中的索引
• 了解SQL所使用的查询类型

3.1 执行计划内容分析

mysql> EXPLAIN
    -> SELECT course_id, title, study_cnt
    -> FROM imc_course
    -> WHERE study_cnt>3000;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | imc_course | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  100 |    33.33 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

3.1.1 ID列解释：

1. ID表示查询执行的顺序

2. ID相同时由上到下执行

3. ID不同时，由大到小执行

   mysql> EXPLAIN
       -> SELECT course_id, class_name, level_name, title, study_cnt
       -> FROM imc_course a
       -> JOIN imc_class b ON b.class_id=a.class_id
       -> JOIN imc_level c ON c.level_id=a.level_id
       -> WHERE study_cnt>3000;
   +----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+----------------------------------------------------+
   | id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref            | rows | filtered | Extra                                              |
   +----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+----------------------------------------------------+
   |  1 | SIMPLE      | c     | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL           |    4 |   100.00 | NULL                                               |
   |  1 | SIMPLE      | a     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL           |  100 |     3.33 | Using where; Using join buffer (Block Nested Loop) |
   |  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 2       | dba.a.class_id |    1 |   100.00 | NULL                                               |
   +----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+----------------------------------------------------+
   3 rows in set, 1 warning (0.01 sec)
   

   mysql> EXPLAIN
       -> SELECT a.course_id, a.title
       -> FROM imc_course a
       -> WHERE a.course_id NOT IN (
       -> SELECT course_id 
       -> FROM imc_chapter b
       -> );
   +----+--------------------+-------+------------+----------------+---------------+-------------+---------+------+------+----------+--------------------------+
   | id | select_type        | table | partitions | type           | possible_keys | key         | key_len | ref  | rows | filtered | Extra                    |
   +----+--------------------+-------+------------+----------------+---------------+-------------+---------+------+------+----------+--------------------------+
   |  1 | PRIMARY            | a     | NULL       | index          | NULL          | udx_title   | 62      | NULL |  100 |   100.00 | Using where; Using index |
   |  2 | DEPENDENT SUBQUERY | b     | NULL       | index_subquery | udx_couseid   | udx_couseid | 4       | func |   13 |   100.00 | Using index              |
   +----+--------------------+-------+------------+----------------+---------------+-------------+---------+------+------+----------+--------------------------+
   2 rows in set, 1 warning (0.01 sec)