MySQL学习笔记(6)—— 慢查询日志和索引

一、 慢查询日志

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

1. MySQL的各种日志

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

2. 认识慢查询日志

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

  • 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也能分析insertupdatedelete语句。(最后加个\G可以进行规格化显示

    在这里插入图片描述

3. explain的结果解析

  • 参数一览
    在这里插入图片描述

    • 连接类型:all是最糟糕的,在表格中越靠左边越好
  • 对比where条件不同导致的不同

    mysql> explain select * from employee where name = '张三'\G;
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: employee
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 11
            Extra: Using where
    1 row in set (0.53 sec)
    
    
    mysql> explain select * from employee where id = 1\G;
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: employee
             type: const
    possible_keys: PRIMARY
              key: PRIMARY
          key_len: 4
              ref: const
             rows: 1
            Extra:
    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: employee
       Non_unique: 0
         Key_name: PRIMARY
     Seq_in_index: 1
      Column_name: id
        Collation: A
      Cardinality: 11
         Sub_part: NULL
           Packed: NULL
             Null:
       Index_type: BTREE
          Comment:
    Index_comment:
    1 row in set (0.00 sec)
    
    • 可见:虽然没有手动建索引,但是有一个主键索引
    • 主键会自动建一个索引
  • 现在进行一个没有索引的查询

    mysql> explain select * from employee where name = '张三'\G;
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: employee
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 11
            Extra: 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: 0
    
    mysql> show index from employee\G;
    *************************** 1. row ***************************
            Table: employee
       Non_unique: 0
         Key_name: PRIMARY
     Seq_in_index: 1
      Column_name: id
        Collation: A
      Cardinality: 11
         Sub_part: NULL
           Packed: NULL
             Null:
       Index_type: BTREE
          Comment:
    Index_comment:
    *************************** 2. row ***************************
            Table: employee
       Non_unique: 1
         Key_name: idx_name
     Seq_in_index: 1
      Column_name: name
        Collation: A
      Cardinality: 11
         Sub_part: NULL
           Packed: NULL
             Null: YES
       Index_type: BTREE
          Comment:
    Index_comment:
    2 rows in set (0.00 sec)
    
  • 在进行一次字段的查询

    mysql> explain select * from employee where name = '张三'\G;
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: employee
             type: ref
    possible_keys: idx_name
              key: idx_name
          key_len: 93
              ref: const
             rows: 1
            Extra: 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: 1
  select_type: SIMPLE
        table: employee
         type: ref
possible_keys: idx_name_salary_dept
          key: idx_name_salary_dept
      key_len: 93
          ref: const
         rows: 1
        Extra: 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: 1
  select_type: SIMPLE
        table: employee
         type: ref
possible_keys: idx_name_salary_dept
          key: idx_name_salary_dept
      key_len: 93
          ref: const
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

# 查询dept(可见没有索引)
mysql> explain select * from employee where dept = 'A'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employee
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 11
        Extra: 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: employee
       Non_unique: 0
         Key_name: PRIMARY
     Seq_in_index: 1
      Column_name: id
        Collation: A
      Cardinality: 11
         Sub_part: NULL
           Packed: NULL
             Null:
       Index_type: BTREE
          Comment:
    Index_comment:
    *************************** 2. row ***************************
            Table: employee
       Non_unique: 1
         Key_name: idx_name_salary_dept
     Seq_in_index: 1
      Column_name: name
        Collation: A
      Cardinality: 11
         Sub_part: NULL
           Packed: NULL
             Null: YES
       Index_type: BTREE
          Comment:
    Index_comment:
    *************************** 3. row ***************************
            Table: employee
       Non_unique: 1
         Key_name: idx_name_salary_dept
     Seq_in_index: 2
      Column_name: salary
        Collation: A
      Cardinality: 11
         Sub_part: NULL
           Packed: NULL
             Null: YES
       Index_type: BTREE
          Comment:
    Index_comment:
    *************************** 4. row ***************************
            Table: employee
       Non_unique: 1
         Key_name: idx_name_salary_dept
     Seq_in_index: 3
      Column_name: dept
        Collation: A
      Cardinality: 11
         Sub_part: NULL
           Packed: NULL
             Null: YES
       Index_type: BTREE
          Comment:
    Index_comment:
    4 rows in set (0.00 sec)
    
    • 由于复合索引的前导列特性,多建立了三个索引
  • 使用覆盖索引(注意查询的列信息必须能从索引中获得)

    # 使用了覆盖索引
    mysql> explain select id from employee\G;
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: employee
             type: index
    possible_keys: NULL
              key: idx_name_salary_dept
          key_len: 191
              ref: NULL
             rows: 11
            Extra: Using index
    1 row in set (0.34 sec)
    
    # 使用了覆盖索引
    mysql> explain select id from employee\G;
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: employee
             type: index
    possible_keys: NULL
              key: idx_name_salary_dept
          key_len: 191
              ref: NULL
             rows: 11
            Extra: Using index
    1 row in set (0.34 sec)
    
    # 多了一个sex字段,索引没有覆盖,这个只能全表扫描了
    mysql> explain select id from employee\G;
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: employee
             type: index
    possible_keys: NULL
              key: idx_name_salary_dept
          key_len: 191
              ref: NULL
             rows: 11
            Extra: Using index
    1 row in set (0.34 sec)
    
©️2020 CSDN 皮肤主题: 技术黑板 设计师:CSDN官方博客 返回首页