一、 慢查询日志

• 在使用mysql的过程中，我们发现系统有时候工作很慢。
• 要想优化这个系统，找到比较慢的sql语句，就要先查日志

1. MySQL的各种日志

• 日志用于记录数据库的运行情况，以及用户对数据库执行的各类操作。
• 当数据库发生故障时，可以根据日志分析和解决问题，从而对数据库进行恢复。
  

2. 认识慢查询日志

• 慢查询日志用于记录MySQL数据库中响应时间超过指定阈值（自己设置）的语句。慢查询日志通常也被称之为慢日志，因为它不仅仅只针对SELECT语句，像INSERT、UPDATE、DELETE等语句，只要响应时间超过所设定阈值都会记录在慢查询日志中

• mysql8.0的参数（5.x版本有区别）
  

3. 开启慢查询日志

• 开启日志会影响性能

• 慢查询日志可以通过命令临时设置，也可以修改配置文件永久设置。

• 语法：

  #查看是否开启慢查询日志
  show variables like 'slow%';#临时开启慢查询日志
  set global slow_query_log='ON';
  set global long_query_time=1; #慢查询日志文件所在位置
  show variables like '%datadir%';#设置慢开始日志阈值为1s
  set long_query_time = 1;#查看各日志的阈值
  show variables like '%long%';#查看日志输出
  show variables like '%log_output%';#查看日志保存位置
  show variables like '%datadir%';
  

4. 示例

# 查看是否开启慢查询日志，看到现在慢开始日志没开，慢开始日志保存位置为D:\programmer\MySQL\..
mysql> show variables like 'slow%';
+---------------------+---------------------------------------------------+
| Variable_name       | Value                                             |
+---------------------+---------------------------------------------------+
| slow_launch_time    | 2                                                 |
| slow_query_log      | OFF                                               |
| slow_query_log_file | D:\programmer\MySQL\Data\7CKJN7W619Q5V0C-slow.log |
+---------------------+---------------------------------------------------+
3 rows in set (0.00 sec)# 开启慢查询日志
mysql> set global slow_query_log='ON';
Query OK, 0 rows affected (0.53 sec)#设置慢开始日志阈值
mysql> set long_query_time = 1;
Query OK, 0 rows affected (0.05 sec)#查看各日志阈值
mysql> show variables like '%long%';
+---------------------------------------------------+----------+
| Variable_name                                     | Value    |
+---------------------------------------------------+----------+
| long_query_time                                   | 1.000000 |
| max_long_data_size                                | 1048576  |
| performance_schema_events_waits_history_long_size | 10000    |
+---------------------------------------------------+----------+
3 rows in set (0.13 sec)#查看日志保存位置，这是所有日志保存的位置
mysql> show variables like '%datadir%';
+---------------+---------------------------+
| Variable_name | Value                     |
+---------------+---------------------------+
| datadir       | D:\programmer\MySQL\Data\ |
+---------------+---------------------------+
1 row in set (0.00 sec)#查看日志输出,看到这是一个表,这个表在mysql用于的管理的数据库mysql中
mysql> show variables like '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)mysql> use mysql
Database changed
mysql> show tables;	#下面查出的slow_log就是慢开始日志输出的表
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
24 rows in set (0.32 sec)

• 下面写两个慢的sql语句，到日志看记录没

  mysql> select sleep(3);
  +----------+
  | sleep(3) |
  +----------+
  |        0 |
  +----------+
  1 row in set (3.37 sec)mysql> select sleep(5);
  +----------+
  | sleep(5) |
  +----------+
  |        0 |
  +----------+
  1 row in set (5.00 sec)

  

二、查询分析器EXPLAIN

• 前面用日志找出慢的sql语句后，可以用explain分析问题出在哪里

• 先给出本文中用于演示的数据表：

  mysql> select * from employee;
  +----+--------+------+--------+
  | id | name   | sex  | salary |
  +----+--------+------+--------+
  |  1 | 张三   | 男   |   5500 |
  |  2 | 李洁   | 女   |   4500 |
  |  3 | 李小梅 | 女   |   4200 |
  |  4 | 欧阳辉 | 男   |   7500 |
  |  5 | 李芳   | 女   |   8500 |
  |  6 | 张江   | 男   |   6800 |
  |  7 | 李四   | 男   |  12000 |
  |  8 | 王五   | 男   |   3600 |
  |  9 | 马小龙 | 男   |   6000 |
  | 10 | 龙五   | 男   |   8000 |
  | 11 | 冯小芳 | 女   |  10000 |
  | 12 | 马小龙 | 女   |   4000 |
  +----+--------+------+--------+
  12 rows in set (0.00 sec)
  

1. explain简介

• explain命令可以查看SQL语句的执行计划。当explain与SQL语句一起使用时，MySQL将显示来自优化器的有关语句执行计划的信息。也就是说，MySQL解释了它将如何处理语句，包括有关如何联接表以及以何种顺序联接表的信息。

• explain能做什么？

  1. 分析出表的读取顺序
  2. 数据读取操作的操作类型
  3. 哪些索引可以使用
  4. 哪些索引被实际使用
  5. 表之间的引用
  6. 每张表有多少行被优化器查询

2. explain的使用

• explain的使用很简单，只需要在SQL语句之前加上explain命令即可，除select语句外，explain也能分析insert、update和delete语句。（最后加个\G可以进行规格化显示
  ）
  

3. explain的结果解析

• 参数一览
  

  • 连接类型：all是最糟糕的，在表格中越靠左边越好

• 对比where条件不同导致的不同

  mysql> explain select * from employee where name = '张三'\G;
  *************************** 1. row ***************************id: 1select_type: SIMPLEtable: employeetype: ALL
  possible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 11Extra: Using where
  1 row in set (0.53 sec)mysql> explain select * from employee where id = 1\G;
  *************************** 1. row ***************************id: 1select_type: SIMPLEtable: employeetype: const
  possible_keys: PRIMARYkey: PRIMARYkey_len: 4ref: constrows: 1Extra:
  1 row in set (0.11 sec)
  

  • 用name做where条件时
    • type: ALL （最糟糕）
    • key: NULL 没有用索引
    • rows: 11 扫描了11行（是全表扫描）
  • 用id做where条件时
    • type: const 比较好
    • key: PRIMARY 用了主键索引
    • rows: 1 扫描了1行

三、索引的基本使用

1. 什么是索引

• 索引是一种特殊的数据结构，类似于图书的目录，它能够极大地提升数据库的查询效率。如果没有索引，在查询数据时必须扫描表中的所有记录才能找出符合条件的记录，这种全表扫描的查询效率非常低

2. 常见的索引种类

• 索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定记录

• 数据库的索引好比一本书的目录，能够加快数据库的查询速度；

• 索引是快速搜索的关键，如果不加索引，查找任何一条特定的数据都会进行一次全表扫描

• 常见索引种类
  

3. 索引的使用

（1）创建索引

# 创建普通索引
CREATE INDEX indexName ON tableName(columnName(length)); # 创建唯一索引
CREATE UNIQUE INDEX indexName ON tableName(columnName(length)); # 创建复合索引
CREATE INDEX indexName ON tableName(columnName1, columnName2, …);

（2）删除索引

DROP INDEX [indexName] ON tableName; 

（3）查看索引

SHOW INDEX FROM tableName;

4. 实战经验

-

5. 示例

• 先对employee查一下有没有索引

  mysql> show index from employee\G;
  *************************** 1. row ***************************Table: employeeNon_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: idCollation: ACardinality: 11Sub_part: NULLPacked: NULLNull:Index_type: BTREEComment:
  Index_comment:
  1 row in set (0.00 sec)
  

  • 可见：虽然没有手动建索引，但是有一个主键索引
  • 主键会自动建一个索引

• 现在进行一个没有索引的查询

  mysql> explain select * from employee where name = '张三'\G;
  *************************** 1. row ***************************id: 1select_type: SIMPLEtable: employeetype: ALL
  possible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 11Extra: Using where
  1 row in set (0.00 sec)
  

  • 可见：key为空，即没有索引，进行了全表扫描

• 为name字段添加索引

  # 建立一个新索引
  mysql> CREATE INDEX idx_name ON employee(name);
  Query OK, 0 rows affected (0.67 sec)
  Records: 0  Duplicates: 0  Warnings: 0mysql> show index from employee\G;
  *************************** 1. row ***************************Table: employeeNon_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: idCollation: ACardinality: 11Sub_part: NULLPacked: NULLNull:Index_type: BTREEComment:
  Index_comment:
  *************************** 2. row ***************************Table: employeeNon_unique: 1Key_name: idx_nameSeq_in_index: 1Column_name: nameCollation: ACardinality: 11Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment:
  Index_comment:
  2 rows in set (0.00 sec)
  

• 在进行一次字段的查询

  mysql> explain select * from employee where name = '张三'\G;
  *************************** 1. row ***************************id: 1select_type: SIMPLEtable: employeetype: ref
  possible_keys: idx_namekey: idx_namekey_len: 93ref: constrows: 1Extra: Using where
  1 row in set (0.00 sec)
  

  • 可见：现在用idx_name作为索引进行查询，只扫描1行

• 删除刚刚建立的索引

  mysql> DROP INDEX idx_name ON employee;
  Query OK, 0 rows affected (0.67 sec)
  Records: 0  Duplicates: 0  Warnings: 0
  

四、复合索引的前导列特性

1. 复合索引前导列特性

• 复合索引前导列特性：创建复合索引(name, salary, dept)，相当于创建了三个索引

  • (name, salary, dept)
  • (name, salary)
  • (name)

• 因此在创建复合索引时应该将最常用作查询条件的列放在最左边，依次递减

• 只建立上述复合索引的话

  • 以下查询都不会使用到索引

    select * from employee where salary=8800;
    select * from employee where dept='部门A';
    select * from employee where salary=8800 and dept='部门A';
    

  • 以下查询会使用到索引

    select * from employee where name='liufeng';
    select * from employee where name='liufeng' and salary=8800;
    select * from employee where name='liufeng' and salary=8800 and dept='部门';
    

2. 示例

# 创建一个复合索引(name,salary,dept)
mysql> create index idx_name_salary_dept on employee(name,salary,dept);
Query OK, 0 rows affected (0.62 sec)
Records: 0  Duplicates: 0  Warnings: 0# 查询name（可见用索引）
mysql> explain select * from employee where name='张三'\G;
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: employeetype: ref
possible_keys: idx_name_salary_deptkey: idx_name_salary_deptkey_len: 93ref: constrows: 1Extra: Using where
1 row in set (0.00 sec)# 查询name ane dept（可见用索引）
mysql> explain select * from employee where name='张三' and dept = 'A'\G;
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: employeetype: ref
possible_keys: idx_name_salary_deptkey: idx_name_salary_deptkey_len: 93ref: constrows: 1Extra: Using where
1 row in set (0.00 sec)# 查询dept（可见没有索引）
mysql> explain select * from employee where dept = 'A'\G;
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: employeetype: ALL
possible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 11Extra: Using where
1 row in set (0.00 sec)

五、覆盖索引

• 覆盖索引又称之为索引覆盖，即select的数据列只从索引中就能得到，不必读取数据行，也就是只需扫描索引就可以得到查询结果

• 这就好像：要查看的数据就在书的目录里，看看目录就可以了，不用真正去翻书

• 关于覆盖索引的几点说明：

  1. 使用覆盖索引，只需要从索引中就能检索到需要的数据，而不要再扫描数据表；

  2. 索引的体量往往要比数据表小很多，因此只读取索引速度会非常快，也会极大减少数据访问量；

  3. MySQL的查询优化器会在执行查询前判断，是否有一个索引可以覆盖所有的查询列；

  4. 并非所有类型的索引都可以作为覆盖索引，覆盖索引必须要存储索引列的值。像哈希索引、空间索引、全文索引等并不会真正存储索引列的值

1. 如何判断使用了覆盖索引

• 当一个查询使用了覆盖索引，在查询分析器EXPLAIN的Extra列可以看到Using index
  

2. 使用索引的示例

• 创建复合索引(name, salary, dept)

  mysql> show index from employee\G;
  *************************** 1. row ***************************Table: employeeNon_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: idCollation: ACardinality: 11Sub_part: NULLPacked: NULLNull:Index_type: BTREEComment:
  Index_comment:
  *************************** 2. row ***************************Table: employeeNon_unique: 1Key_name: idx_name_salary_deptSeq_in_index: 1Column_name: nameCollation: ACardinality: 11Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment:
  Index_comment:
  *************************** 3. row ***************************Table: employeeNon_unique: 1Key_name: idx_name_salary_deptSeq_in_index: 2Column_name: salaryCollation: ACardinality: 11Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment:
  Index_comment:
  *************************** 4. row ***************************Table: employeeNon_unique: 1Key_name: idx_name_salary_deptSeq_in_index: 3Column_name: deptCollation: ACardinality: 11Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment:
  Index_comment:
  4 rows in set (0.00 sec)
  

  • 由于复合索引的前导列特性，多建立了三个索引

• 使用覆盖索引（注意查询的列信息必须能从索引中获得）

  # 使用了覆盖索引
  mysql> explain select id from employee\G;
  *************************** 1. row ***************************id: 1select_type: SIMPLEtable: employeetype: index
  possible_keys: NULLkey: idx_name_salary_deptkey_len: 191ref: NULLrows: 11Extra: Using index
  1 row in set (0.34 sec)# 使用了覆盖索引
  mysql> explain select id from employee\G;
  *************************** 1. row ***************************id: 1select_type: SIMPLEtable: employeetype: index
  possible_keys: NULLkey: idx_name_salary_deptkey_len: 191ref: NULLrows: 11Extra: Using index
  1 row in set (0.34 sec)# 多了一个sex字段，索引没有覆盖，这个只能全表扫描了
  mysql> explain select id from employee\G;
  *************************** 1. row ***************************id: 1select_type: SIMPLEtable: employeetype: index
  possible_keys: NULLkey: idx_name_salary_deptkey_len: 191ref: NULLrows: 11Extra: Using index
  1 row in set (0.34 sec)