mysql中高阶玩法系列（五）

最新推荐文章于 2024-04-19 14:31:33 发布

你是魔鬼吧

最新推荐文章于 2024-04-19 14:31:33 发布

阅读量243

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/qq_42403866/article/details/112426856

版权

数据库专栏收录该内容

10 篇文章 2 订阅

订阅专栏

本文深入探讨了MySQL中的EXPLAIN关键字，用于分析查询性能和表结构优化。通过示例展示了如何使用EXPLAIN理解数据读取顺序、索引应用以及潜在的性能瓶颈。同时，文章提到了查看表索引详情的方法，以及获取MySQL数据库运行状态的途径，帮助读者提升数据库管理能力。

摘要由CSDN通过智能技术生成

CREATE TABLE `user` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(45) COLLATE UTF8MB4_UNICODE_CI NOT NULL,
    `pwd` VARCHAR(255) COLLATE UTF8MB4_UNICODE_CI NOT NULL,
    `created_at` INT(8) UNSIGNED NOT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_created_at` (`created_at`)
)  ENGINE=INNODB DEFAULT CHARSET=UTF8MB4 COLLATE = UTF8MB4_UNICODE_CI;

插入几条数据，方便之后快乐的玩耍

INSERT INTO `user` (`id`, `name`, `pwd`, `created_at`) VALUES ('1', '牛A', '555', '1559318400');
INSERT INTO `user` (`id`, `name`, `pwd`, `created_at`) VALUES ('2', '牛B', '555', '1559318400');
INSERT INTO `user` (`id`, `name`, `pwd`, `created_at`) VALUES ('3', '牛C', '555', '1573441871');
INSERT INTO `user` (`id`, `name`, `pwd`, `created_at`) VALUES ('4', '牛X', '555', '1558329240');

在这里插入图片描述

MySQL优化—EXPLAIN

使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的。这可以帮助分析查询语句或是表结构的性能瓶颈。

表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询

在执行Explain时MySQL不会执行查询，这是一个误区。事实上查询中包括子查询，那么MySQL实际上会执行子查询，将其结果放在一个临时表中，然后完成外层的查询优化。

EXPLAIN的不足

无法知道触发器，存储过程是如何影响查询
无法得知mysql在查询执行中所做的特定优化
无法显示关于查询的执行计划的所有信息
并不区分具有相同名字的事物。例如，对内存排序和临时文件都使用filesort，并且对于磁盘上和内存中的临时表都显示using temporary
信息可能会误导。例如，会对一个有着很小limit的查询显示全索引扫描

mysql> explain select * from user;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

字段名	解释
id	select查询的序列号，即sql语句执行的顺序，表示查询中执行select子句或操作表的顺序
select_type	1. `SIMPLE`：表示简单的select，没有union和子查询 2. `PRIMARY`：查询中若包含任何复杂的子部分，如果有子查询或者联合查询等则是primary 3. `DERIVED`：用来表示包含在FROM子句的子查询中的SELECT， MySQL会递归执行并将结果放到一个临时表中。服务器内部称其“派生表”，因为该临时表是从子查询中派生来的。 4. `SUBQUERY`：在SELECT或WHERE列表中包含了子查询 5. `DEPENDENT SUBQUERY`：在SELECT或WHERE列表中包含了子查询,子查询基于外层 6. `UNCACHEABLE SUBQUREY`：结果集无法缓存的子查询，必须重新评估外部查询的每一行 7. `UNION`：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED 8. `DEPENDENT UNION`：UNION中第二个或之后的SELECT语句取决于外面的查询 9. `UNION RESULT`：UNION的结果 10. `MATERIALIZED`：物化子查询 11. `UNCACHEABLE UNION`：UNION中第二个或之后的SELECT，属于无法缓存的子查询
table	表示这一行的数据是关于哪张表的
partitions	该列显示的为分区表命中的分区情况
type	表示查询使用了何种类型。从最好到最差的连接类型为system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range(尽量保证) > index > ALL。一般来说，得保证查询至少达到range级别，最好能达到ref。 1. `system`：系统表，表中只有一行数据，这是const类型的特列 2. `const`：表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以速度很快，如将主键置于where列表中，MySQL就能将该查询转换为一个常量，如id=1; id为主键或唯一键 3. `eq_ref`：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描，返回某单一行的数据(通常在联接时出现，查询使用的索引为主键或惟一键) 4. `ref`：非唯一性索引扫描，返回匹配某个单独值的所有行。 5. `fulltext`：进行全文索引检索。要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引 6. `ref_or_null`：对于某个字段既需要关联条件，也需要null值得情况下。查询优化器会选择用ref_or_null连接查询。与ref的唯一区别就是在使用索引引用的查询之外再增加一个空值的查询。 7. `index_merge`：在查询过程中需要多个索引组合使用，然后对索引结果进行合并（merge），再读取表数据，通常出现在有 or 的关键字的sql中。 8. `unique_subquery`：子查询中的返回结果字段组合是主键或唯一约束。用于where中的in形式子查询，子查询返回不重复值唯一值，可以完全替换子查询，效率更高。 9. `index_subquery`：利用索引来关联子查询，不再全表扫描。子查询中的返回结果字段组合是一个索引（或索引组合） 10. `range`：只检索给定范围的行,使用一个索引来选择行，常见于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等运算符的查询中，并且该字段上建有索引时发生的情况(注:不一定好于index) 11. `index`：全索引扫描，以索引的顺序进行全表扫描，优点是不用排序,缺点是还要全表扫描。index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的） 12. `ALL`：全表扫描，应该尽量避免
possible_keys	显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
key	实际使用的索引。如果为NULL，则没有使用索引。MYSQL很少会选择优化不足的索引，此时可以在SELECT语句中使用USE INDEX（index）来强制使用一个索引或者用IGNORE INDEX（index）来强制忽略索引
key_len	使用的索引的长度。在不损失精确性的情况下，长度越短越好（key_len显示的值为索引字段的最大可能长度，并非实际使用长度）
ref	如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func
rows	mysql估算的需要扫描的行数。这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好
filtered	表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例
Extra	关于MYSQL如何解析查询的额外信息，主要有以下几种 1. `using index`：所需数据只需在 Index 即可全部获得，不用扫描表数据文件, 表示查询在索引树中就可查找所需数据, 往往说明性能不错 2. `using where`：使用到where来过滤数据. 不是所有的where clause都要显示using where. 如以=方式访问索引 3. `using tmporary`：使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要查看status变量，used_tmp_table，　used_tmp_disk_table才能看出来。 4. `using filesort`：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大。MySQL中无法利用索引完成的排序操作称为“文件排序”(当使用order by v1,而没用到索引时,就会使用额外的排序) 5. `range checked for eache record(index map:N)`：通过 MySQL 官方手册的描述，当 MySQL Query Optimizer 没有找到理想的索引，如果发现前面表的列值已知，部分索引可以使用。对前面表的每个行组合，MySQL检查是否可以使用range或 index_merge访问方法来索取行。这是使用索引的最慢的连接之一 6. `using join buffer`：使用了join缓存，主要是减少内表的循环数量以及比较顺序地扫描查询。 7. `impossible where noticed after reading const tables`：表明无法通过where获取到任何元组，where子句的值总是false。MySQL Query Optimizer 通过收集到的统计信息判断出不可能存在结果。 8. `select tables optimized away`：当我们使用某些聚合函数来访问存在索引的某个字段时，MySQL Query Optimizer 会通过索引直接一次定位到所需的数据行完成整个查询。当然，前提是在 Query 中不能有 GROUP BY 操作。如使用MIN()或MAX()的时候。 9. `distinct`：在select部分使用了distinc关键字 10. `full scan on null key`：子查询中的一种优化方式，主要在遇到无法通过索引访问null值的使用。 11. `using index for group-by`：数据访问和 Using index 一样，所需数据只须要读取索引，当Query 中使用GROUP BY或DISTINCT 子句时，如果分组字段也在索引中，Extra中的信息就会是 Using index for group-by。 12. `using where with pushed condition`：这是一个仅仅在 NDBCluster存储引擎中才会出现的信息，而且还须要通过打开 Condition Pushdown 优化功能才可能被使用。 13. `no tables`：Query 语句中使用 FROM DUAL或不包含任何 FROM子句。 14. `not exists`：在某些左连接中，MySQL Query Optimizer通过改变原有 Query 的组成而使用的优化方法，可以部分减少数据访问次数。

查看表的索引详情

show index from '表名'

mysql> show index from user \G;
*************************** 1. row ***************************
        Table: user
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 4
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: user
   Non_unique: 1
     Key_name: idx_created_at
 Seq_in_index: 1
  Column_name: created_at
    Collation: A
  Cardinality: 3
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 

2 rows in set (0.00 sec)

字段名	解释
Table	表名
Non_unique	如果索引不能包括重复词，则为0。如果可以，则为1。
Key_name	索引名
Seq_in_index	索引中的列顺序号, 从 1 开始
Column_name	字段名
Collation	字段在索引中是如何被排序的，大概意思就是字符序。在MySQL中，这可以有值A（升序) 或NULL（不排序)。
Cardinality	基数的意思，索引中唯一值的数量的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。我们知道某个字段的重复值越少越适合建索引，所以我们一般都是根据Cardinality来判断索引是否具有高选择性，如果这个值非常小，那就需要重新评估这个字段是否适合建立索引。
Sub_part	前置索引的意思，如果该列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
Packed	指示关键字如何被压缩。如果没有被压缩，则为NULL。压缩一般包括压缩传输协议、压缩列解决方案和压缩表解决方案。
Null	索引的列中含有NULL。含有NULL则为YES。如果没有，则这里显示为空。
Index_type	索引类型（BTREE, FULLTEXT, HASH, RTREE）
Comment	字段的评注
Index_comment	索引的评注

查看mysql数据库的运行状态

可以列出MySQL数据库的基本参数。比如编码信息，MySQL版本，查询次数等等。

status

mysql> status;
--------------
mysql  Ver 14.14 Distrib 5.7.21, for linux-glibc2.12 (x86_64) using  EditLine wrapper

Connection id:		2
Current database:	test
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.7.21-log MySQL Community Server (GPL)
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	latin1
Db     characterset:	utf8mb4
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/tmp/mysql.sock
Uptime:			3 hours 26 min 26 sec

Threads: 1  Questions: 32  Slow queries: 0  Opens: 112  Flush tables: 1  Open tables: 105  Queries per second avg: 0.002
--------------

字段名	解释
Connection id	连接ID。
Current database	当前数据库名
Current user	当前连接的登录用户
SSL	是否使用ssl
Current pager	标准输出到屏幕
Using outfile
Using delimiter	使用的语句结束符
Server version	当前mysql服务器的版本号
Protocol version	协议版本
Connection	使用的连接类型，如本地连接数据库用的是unix sock 方式
Server characterset	mysql server使用字符集类型
Db characterset	数据库使用的字符集类型
Client characterset	客户端使用的字符集类型
Conn.characterset	连接使用的字符集类型
UNIX socket	socket文件路径
Uptime	数据库开机运行时间
Threads	数据库连接线程数
Questions	已经发送给服务器的查询的个数
Slow queries	慢查询数
Opens	在table cache记录中当前被打开的非TEMPORARY表的数量
Flush tables	关闭所有已打开的表对象，同时将查询缓存中的结果清空
Open tables	mysqld已经打开了多少表的数量
Queries per second avg	QPS，计算方式是 Queries 除以 Uptime 时间