mysql分析查询语句 mysql查询分析工具

转载

mob6454cc670f76 2023-08-21 17:35:57

文章标签 mysql分析查询语句 MySQL mysql 子查询 文章分类 MySQL 数据库

5 EXPLAIN 工具

可以通过EXPLAIN来分析索引的有效性,获取查询执行计划信息，用来查看查询优化器如何执行查询

语法

EXPLAIN SELECT clause

EXPLAIN输出信息说明

列名	说明
id	执行编号，标识select所属的行。如果在语句中没子查询或关联查询，只有唯一的select，每行都将显示1。否则，内层的select语句一般会顺序编号，对应于其在原始语句中的位置
select_type	简单查询: SIMPLE 复杂查询: PRIMARY(最外面的SELECT) DERIVED(用于FROM中的子查询) UNION(UNION语句的第一个之后的SELECT语句) UNION RESUlT(匿名临时表) SUBQUERY(简单子查询)
table	访问引用哪个表(引用某个查询，如“derived3”)
type	关联类型或访问类型，即MySQL决定的如何去查询表中的行的方式
possible_keys	查询可能会用到的索引
key	显示mysql决定采用哪个索引来优化查询
key_len	显示mysql在索引里使用的字节数
ref	当使用索引列等值查询时,与索引列进行等值匹配的对象信息
rows	为了找到所需的行而需要读取的行数，估算值，不精确。通过把所有rows列值相乘，可粗略估算整个查询会检查的行数
Extra	额外信息 Using index:MySQL将会使用覆盖索引，以避免访问表 Using where:MySQL服务器将在存储引擎检索后，再进行一次过滤 Using temporary:MySQL对结果排序时会使用临时表 Using filesort:对结果使用一个外部索引排序

说明: type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是:

NULL> system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说，得保证查询至少达到range级别，最好能达到ref

NULL>system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>in dex_subquery>range>index>ALL //最好到最差
备注:掌握以下10种常见的即可
NULL>system>const>eq_ref>ref>ref_or_null>index_merge>range>index>ALL

类型	说明
All	最坏的情况,全表扫描
index	和全表扫描一样。只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。如在Extra列看到Using index，说明正在使用覆盖索引，只扫描索引的数据，它比按索引次序全表扫描的开销要小很多
range	范围扫描，一个有限制的索引扫描。key 列显示使用了哪个索引。当使用 `=、 <>、>、 >=、<、<=、IS NULL、<=>、BETWEEN` 或者 `IN` 操作符,用常量比较关键字列时,可以使用 range
ref	一种索引访问，它返回所有匹配某个单个值的行。此类索引访问只有当使用非唯一性索引或唯一性索引非唯一性前缀时才会发生。这个类型跟eq_ref不同的是，它用在关联操作只使用了索引的最左前缀，或者索引不是UNIQUE和PRIMARY KEY。ref可以用于使用=或<=>操作符的带索引的列。
eq_ref	最多只返回一条符合条件的记录。使用唯一性索引或主键查找时会发生 (高效)
const	当确定最多只会有一行匹配的时候，MySQL优化器会在查询前读取它而且只读取一次，因此非常快。当主键放入where子句时，mysql把这个查询转为一个常量(高效)
system	这是const连接类型的一种特例，表仅有一行满足条件。
Null	意味着mysql能在优化阶段分解查询语句，在执行阶段甚至用不到访问表或索引(高效)

mysql分析查询语句 mysql查询分析工具_mysql

示例

MariaDB [wuxia]> explain select * from wudang where id not in (5,10,20);
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | wudang | ALL  | PRIMARY       | NULL | NULL    | NULL |    7 | Using where |
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.000 sec)

MariaDB [wuxia]> explain select * from wudang where id <> 10 ;
+------+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id   | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+------+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|    1 | SIMPLE      | wudang | range | PRIMARY       | PRIMARY | 4       | NULL |    7 | Using where |
+------+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.000 sec)

MariaDB [wuxia]> explain select * from wudang where age > (select avg(age)from emei);
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+
|    1 | PRIMARY     | wudang | ALL  | NULL          | NULL | NULL    | NULL |    7 | Using where |
|    2 | SUBQUERY    | emei   | ALL  | NULL          | NULL | NULL    | NULL |    3 |             |
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.002 sec)

MariaDB [wuxia]>

示例：创建索引和使用索引

MariaDB [wuxia]> create index idx_name on wudang(name(10));
Query OK, 0 rows affected (0.008 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [wuxia]> show indexes from wudang\G
*************************** 1. row ***************************
        Table: wudang
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 7
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: wudang
   Non_unique: 1
     Key_name: idx_name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 7
     Sub_part: 10
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.000 sec)

MariaDB [wuxia]> explain select * from wudang where name like 'zhang%';
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | wudang | ALL  | idx_name      | NULL | NULL    | NULL |    7 | Using where |
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.002 sec)

MariaDB [wuxia]> explain select * from wudang where name like 'song%';
+------+-------------+--------+-------+---------------+----------+---------+------+------+-------------+
| id   | select_type | table  | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+------+-------------+--------+-------+---------------+----------+---------+------+------+-------------+
|    1 | SIMPLE      | wudang | range | idx_name      | idx_name | 42      | NULL |    2 | Using where |
+------+-------------+--------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.001 sec)

MariaDB [wuxia]> select * from wudang where name like 'zhang%';
+----+--------------+------+--------+
| id | name         | age  | gender |
+----+--------------+------+--------+
| 16 | zhangsanfeng |  100 | M      |
| 19 | zhangcuishan |   30 | M      |
| 20 | zhangwuji    |   10 | M      |
+----+--------------+------+--------+
3 rows in set (0.001 sec)

MariaDB [wuxia]> select * from wudang where name like 'song%';
+----+--------------+------+--------+
| id | name         | age  | gender |
+----+--------------+------+--------+
| 18 | songqingshu  |   20 | M      |
| 17 | songyuanqiao |   50 | M      |
+----+--------------+------+--------+
2 rows in set (0.000 sec)

MariaDB [wuxia]>

示例：复合索引

MariaDB [wuxia]> desc emei;
+--------+---------------------+------+-----+---------+----------------+
| Field  | Type                | Null | Key | Default | Extra          |
+--------+---------------------+------+-----+---------+----------------+
| id     | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)         | NO   |     | NULL    |                |
| age    | tinyint(3) unsigned | YES  |     | NULL    |                |
| gender | enum('M','F')       | YES  |     | M       |                |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.002 sec)

# 创建符合索引
MariaDB [wuxia]> create index idx_name_age on emei(name,age);
Query OK, 0 rows affected (0.008 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [wuxia]> desc emei;
+--------+---------------------+------+-----+---------+----------------+
| Field  | Type                | Null | Key | Default | Extra          |
+--------+---------------------+------+-----+---------+----------------+
| id     | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)         | NO   | MUL | NULL    |                |
| age    | tinyint(3) unsigned | YES  |     | NULL    |                |
| gender | enum('M','F')       | YES  |     | M       |                |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.001 sec)

MariaDB [wuxia]> show indexes from emei\G
*************************** 1. row ***************************
        Table: emei
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 3
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: emei
   Non_unique: 1
     Key_name: idx_name_age
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 3
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 3. row ***************************
        Table: emei
   Non_unique: 1
     Key_name: idx_name_age
 Seq_in_index: 2
  Column_name: age
    Collation: A
  Cardinality: 3
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
3 rows in set (0.000 sec)

# 跳过查询复合索引的前面字段,后续字段的条件查询无法利用复合索引
MariaDB [wuxia]> explain select * from emei where age=20;
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | emei  | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.000 sec)

MariaDB [wuxia]>

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。