MYSQL优化

一、慢查询日志

• MYSQL的满查询日志，用来记录在MYSQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中
• long_query_time默认值是10s
• 帮助定位执行时间较长的SQL查询，再通过EXPLAIN来分析，从而定位问题
• 默认MYSQL没有开启慢查询日志，需要手动开启。如果不是调优需要，一般不建议启动该参数，因为开启慢查询日志会对性能带来一定影响

1. 开启慢查询

# 默认是OFF
SHOW VARIABLES LIKE '%slow_query_log%';

# slow_query_log：ON
# slow_query_log_file：/var/lib/mysql/ba42813a711a-slow.log


# 开启慢查询
SET GLOBAL slow_query_log = on;

# 默认时间：10s
SHOW VARIABLES LIKE '%long_query_time%';

# 修改默认时间
SET global long_query_time = 1;   # global
SET long_query_time = 1;          # session
# 或者修改my.conf文件，mysqld下面添加参数
[mysqld]
slow_query_log=ON
slow_query_log_file=/var/lib/mysql/erick-slow.log
long_query_time=3
long_output=FILE

2. 分析

# 查询当前系统有几个慢查询的数据
SHOW status LIKE '%slow_queries%';

# 检查慢查询的SQL,通过explain来解析

# 1. 调优结束后，关闭慢查询
# 2. 调优结束后，删除慢查询日志

二、EXPLAIN

• EXPLAIN 一个SQL时，SQL并不会真正的去执行，只是去进行了执行计划分析

EXPLAIN SELECT * FROM cnip;
DESCRIBE SELECT * FROM cnip;

1. Table

• 表名，查询的每一行记录都对应一个单表
• 涉及到几个表，就会有几行记录

# cnip是驱动表， employee是被驱动表 
# 前面的cnip不一定是驱动表，有可能mysql优化器会优化
EXPLAIN SELECT * FROM cnip INNER JOIN employee e on cnip.name = e.name;
EXPLAIN SELECT * FROM cnip;

2. id

• 在一个大的查询语句中，每个SELECT关键字都对应一个唯一的id
• 所以上面就会有两个相同的为1的id

#  实际可以看到有三条记录，因为去重会用到临时表
EXPLAIN SELECT * FROM cnip UNION SELECT * FROM cnip;

• id如果相同，可以认为是一组，从上往下顺序执行
• 在所有组中，id值越大， 优先级越高，越先执行
• id号每个号码，表示一趟独立的查询，一个sql的查询越少越好

3. select_type

• mysql为每个SELECT关键字代表的小查询定义了该属性，知道小查询的select_type,就知道小查询在整个大查询中扮演的角色

# 1. Simple: 不包含UNION或自查询的都算是Simple 
EXPLAIN SELECT * FROM cnip;
EXPLAIN SELECT * FROM cnip INNER JOIN employee ;

# 2. 包含UNION或UNION ALL 
# 2.1 最左边的那个小查询就是      PRIMARY
# 2.2 其余就是                  UNION
# 2.3 临时表就是                UNION RESULT
# 2.4 UNION ALL没有临时表， UNION包含临时表

# 3. SUBQUERY: 子查询

4. Type

• 执行计划的一条记录代表MYSQL对某个表的执行查询时的访问方法
• 针对单表的访问方法

# 1. system
#  当表中只有一条数据，并且该表使用的存储引擎的统计数据是精确的，比如MyISAM, Memory
#  执行效率是最高的，MyISAM会记录数据，但是INNODB 不会保存这种结果

# 2. const
# 根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是 const
EXPLAIN SELECT * FROM cnip WHERE id = 10;

# 3. eq_ref
# 连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问
# 驱动表是 ALL, 被驱动表是 eq_ref
# 小表驱动大表
EXPLAIN SELECT * FROM cnip INNER JOIN s2 ON cnip.id=s2.id

# 4. ref：
# 通过普通的二级索引列与常量进行等值匹配来查询时，该表的访问方式就是 ref
EXPLAIN SELECT * FROM cnip WHERE name='erick';

# 5. ref_or_null
# 普通二级索引进行等值匹配时，该索引列的值也可能是null
EXPLAIN SELECT * FROM cnip WHERE name ='erick' OR name IS NULL;

# 6. index_merge
# 多个字段进行查找，并且是 or 合并的方式，每个字段都有索引
EXPLAIN SELECT * FROM cnip WHERE name = 'erick' OR address = 'xian';

# 7 range:
# 如果使用索引获取某些范围区间的记录，那么就可能使用到range来访问
EXPLAIN SELECT * FROM cnip WHERE key1 IN ('a','b','c');
EXPLAIN SELECT * FROM cnip WHERE key1 >'a' AND key1<'b';

# 8 all： 
# 全表扫描
EXPLAIN SELECT * FROM cnip WHERE key1=1;

system>const>eq_ref>ref>fulltext>ref_or_null>index_merger>unique_subquery
>index_subquery      >range>index>all

# SQL优化目标： 最少要达到range级别，要求是ref级别，最好是const级别

5. possible_keys/key

• possible_key: 可能使用到的索引
• key：实际使用的索引

# 一般情况，凡是WHERE后面的字段，只要牵涉到索引，就都会列举在 possible_keys中
# key：具体使用到的索引，一般只会有一个，如果为null，则表示没有用到
# 查询优化器根据possible_keys，来判断到底会用到哪个索引

6. key_len

• 实际使用到的索引长度(字节数)
• 主要针对联合索引，值越大越好

7. rows

• 预估的需要读取的数据条数
• 数值越小越好

8. filtered

• 某个表经过搜索条件过滤后，剩余记录条数的百分比
• 记录百分比，越高越好

9. extra

• 更准确的理解MYSQL到底如何执行给定的查询语句

三、EXPLAIN的格式化输出

EXPLAIN SELECT * FROM employee;

EXPLAIN FORMAT = JSON SELECT * FROM employee; 

EXPLAIN FORMAT = tree SELECT * FROM employee;

MYSQL WORKBENCH: 可视化输出

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "0.35"
    },
    "table": {
      "table_name": "employee",
      "access_type": "ALL",
      "rows_examined_per_scan": 1,
      "rows_produced_per_join": 1,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "0.25",
        "eval_cost": "0.10",
        "prefix_cost": "0.35",
        "data_read_per_join": "208"
      },
      "used_columns": [
        "id",
        "name"
      ]
    }
  }
}