高级篇(day08)-性能分析工具的使用

花粥之间
336 阅读18分钟

性能分析工具的使用

参考链接：www.bilibili.com/video/BV1iq…

在数据库调优中，我们的目标就是响应时间更快，吞吐最更大。利用宏观的监控工具和微观的日志分析可以帮我们快速找到调优的思路和方式。

1、数据库服务器的优化步骤

当我们遇到数据库调优问题的时候，该如何思考呢？这里把思考的流程整理成下面这张图。 整个流程划分成了 观察（Show status） 和 行动（Action） 两个部分。字母 S 的部分代表观察（会使用相应的分析工具），字母 A 代表的部分是行动（对应分析可以采取的行动）。

image.png

小结

image.png

2、查看系统性能参数

在MySQL中，可以使用 SHOW STATUS 语句查询一些MySQL数据库服务器的性能参数、执行频率 。 SHOW STATUS语句语法如下：

SHOW [GLOBAL|SESSION] STATUS LIKE '参数';

一些常用的性能参数如下：

  • Connections：连接MySQL服务器的次数。
  • Uptime：MySQL服务器的上线时间。
  • Slow_queries：慢查询的次数。
  • Innodb_rows_read：Select查询返回的行数
  • Innodb_rows_inserted：执行INSERT操作插入的行数
  • Innodb_rows_updated：执行UPDATE操作更新的 行数
  • Innodb_rows_deleted：执行DELETE操作删除的行数
  • Com_select：查询操作的次数。
  • Com_insert：插入操作的次数。对于批量插入的 INSERT 操作，只累加一次。
  • Com_update：更新操作的次数。
  • Com_delete：删除操作的次数。

3、统计SQL的查询成本：last_query_cost

CREATE TABLE `student_info` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `student_id` INT NOT NULL ,
    `name` VARCHAR(20) DEFAULT NULL,
    `course_id` INT NOT NULL ,
    `class_id` INT(11) DEFAULT NULL,
    `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

如果我们想要查询 id=900001 的记录，然后看下查询成本，我们可以直接在聚簇索引上进行查找：

SELECT student_id, class_id, NAME, create_time FROM student_info
WHERE id = 900001;

运行结果（1 条记录，运行时间为 0.042s ），然后再看下查询优化器的成本，实际上我们只需要检索一个页即可：

mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| Last_query_cost | 1.000000 |
+-----------------+----------+

如果我们想要查询 id 在 900001 到 9000100 之间的学生记录呢？

SELECT student_id, class_id, NAME, create_time FROM student_info
WHERE id BETWEEN 900001 AND 900100;

运行结果（100 条记录，运行时间为 0.046s ）： 然后再看下查询优化器的成本，这时我们大概需要进行 20 个页的查询。

mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| Last_query_cost | 21.134453 |
+-----------------+-----------+

你能看到页的数量是刚才的 20 倍，但是查询的效率并没有明显的变化，实际上这两个 SQL 查询的时间 基本上一样，就是因为采用了顺序读取的方式将页面一次性加载到缓冲池中，然后再进行查找。虽然 页数量（last_query_cost）增加了不少 ，但是通过缓冲池的机制，并没有增加多少查询时间。

使用场景：它对于比较开销是非常有用的，特别是我们有好几种查询方式可选的时候。

4、定位执行慢的SQL：慢查询日志

MySQL的慢查询日志，用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值 的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上(不含10秒)的语 句，认为是超出了我们的最大忍耐时间值。

它的主要作用是，帮助我们发现那些执行时间特别长的SQL查询，并且有针对性地进行优化，从而提高系统的整体效率。当我们的数据库服务器发生阻塞、运行变慢的时候，检查一下慢查询日志，找到那些慢查询，对解决问题很有帮助。比如一条sq执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合explain进行全面分析。

默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。如果不是调优需要的话，一般 不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。

慢查询日志支持将日志记录写入文件。

4.1、开启慢查询日志参数

1、开启 slow_query_log

mysql > set global slow_query_log='ON';

然后我们再来查看下慢查询日志是否开启，以及慢查询日志文件的位置：

image.png

你能看到这时慢查询分析已经开启，同时文件保存在 /var/lib/mysql/atguigu02-slow.log 文件 中。

2、开启了慢查询日志后，什么样的SQL才会被记录到慢查询日志里面呢？这个是由参数long_query_time控制的，修改 long_query_time 阈值 接下来我们来看下慢查询的时间阈值设置，默认情况下long_query_time的值为10秒。使用如下命令：

# 查看long_query_time 默认是10秒
# 只有SQL的执行时间>10才会被记录

mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

注：假如sql语句的运行时间正好等于long_query_time时，sql并不会被记录下来，因此判断慢查询是sql的执行时间大于long_query_time，而不是大于等于。

image.png

这里如果我们想把时间缩短，比如设置为 1 秒，可以这样设置：

#测试发现：设置global的方式对当前session的long_query_time失效。对新连接的客户端有效。所以可以一并执行下述语句
mysql > set global long_query_time = 1;
mysql> show global variables like '%long_query_time%';
mysql> set long_query_time=1;
mysql> show variables like '%long_query_time%';

当设置long_query_time值后，再次show查看其值并没有变化，解决方式： 关闭当前连接，重新开一个新的连接，再重新查看即可。

补充：配置文件中一并设置参数

如下的方式相较于前面的命令行方式，命令开启慢查询只对当前数据库生效，MySQL重启失效， 如果要使慢查询日志永久开启，需要修改my.cnf文件，在[mysqld]下增加修改参数,修改完重启即可可以看作是永久设置的方式。

修改my.cnf文件， [mysqld]下增加或修改参数long- query_time、slow_query_1og和 slow_query_log_file后，然后重启MySQL服务器。

[mysqld]
slow_query_log=ON # 开启慢查询日志的开关
slow_query_1og_file=/var/1ib/mysql/atguigu- slow.log #慢查询日志的目录和文件名信息

long_query_time=3 #设置慢查询的阀值为3秒，超出此设定值的SQL即被记录到慢查询日志
log_output=FILE

如果不指定存储路径，慢查询日志将默认存储到MySQL数据库的数据文件夹下。如果不指定文件名，默认为hostname-slow.log

4.2、查看慢查询数目

查询当前系统中有多少条慢查询记录

SHOW GLOBAL STATUS LIKE '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 1     |
+---------------+-------+
1 row in set (0.00 sec)

查看慢查询日志文件地址：SHOW VARIABLES LIKE "%slow%"

4.3、慢查询日志分析工具：mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具 mysqldumpslow 。 查看mysqldumpslow的帮助信息

# 1、mysqldumpslow --help 来查看mysqldumpslow的帮助信息
root@1dcb5644392c:/usr/bin# mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default  # 按照何种方式排序
                al: average lock time # 平均锁定时间
                ar: average rows sent # 平均返回记录数
                at: average query time # 平均查询时间
                 c: count  # 访问次数
                 l: lock time  # 锁定时间
                 r: rows sent  # 返回记录
                 t: query time  # 查询时间 
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries  # 返回前面多少条记录
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string  
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time

mysqldumpslow 命令的具体参数如下：

  • -a: 不将数字抽象成N，字符串抽象成S
  • -s: 是表示按照何种方式排序：
    • c: 访问次数
    • l: 锁定时间
    • r: 返回记录
    • t: 查询时间
    • al:平均锁定时间
    • ar:平均返回记录数
    • at:平均查询时间 （默认方式）
    • ac:平均查询次数
  • -t: 即为返回前面多少条的数据；
  • -g: 后边搭配一个正则匹配模式，大小写不敏感的；

举例：我们想要按照查询时间排序，查看前五条 SQL 语句，这样写即可：

[root@bogon ~]# mysqldumpslow -s t -t 5 /var/lib/mysql/atguigu01-slow.log

Reading mysql slow query log from /var/lib/mysql/atguigu01-slow.log
Count: 1 Time=2.39s (2s) Lock=0.00s (0s) Rows=13.0 (13), root[root]@localhost
SELECT * FROM student WHERE name = 'S'
Count: 1 Time=2.09s (2s) Lock=0.00s (0s) Rows=2.0 (2), root[root]@localhost
SELECT * FROM student WHERE stuno = N
Died at /usr/bin/mysqldumpslow line 162, <> chunk 2.

工作常用参考：

#得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log
#得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log
#得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log
#另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

4.4、关闭慢查询日志

MySQL服务器停止慢查询日志功能有两种方法：

方式1：永久性方式

[mysqld]
slow_query_log=OFF

或者，把slow_query_log一项注释掉 或 删除

[mysqld]
#slow_query_log =OFF

重启MySQL服务，执行如下语句查询慢日志功能。

SHOW VARIABLES LIKE '%slow%'; #查询慢查询日志所在目录
SHOW VARIABLES LIKE '%long_query_time%'; #查询超时时长

方式2：临时性方式

使用SET语句来设置。

（1）停止MySQL慢查询日志功能，具体SQL语句如下。

SET GLOBAL slow_query_log=off;

（2）重启MySQL服务，使用SHOW语句查询慢查询日志功能信息，具体SQL语句如下

SHOW VARIABLES LIKE '%slow%';
#以及
SHOW VARIABLES LIKE '%long_query_time%';

4.5、删除慢查询日志

使用SHOW语句显示慢查询日志信息，具体SQL语句如下。 SHOW VARIABLES LIKE ' slow_query_1og%' :

从执行结果可以看出，慢查询日志的目录默认为MySQL的数据目录，在该目录下手动删除慢查询日志文件（rm 文件名）即可。

使用命令mysqladmin flush- logs来重新生成查询日志文件，具体命令如下，执行完毕会在数据目录下重新生 成慢查询日志文件。

mysqladmin -uroot -P flush-logs slow

提示

必须开启慢查询日志：set global slow_query_log='ON';

慢查询日志都是使用mysqladmin flush-logs命令来删除重建的。使用时一定要注意，一旦执行了这个命令， 慢查询日志都只存在新的日志文件中，如果需要旧的查询日志，就必须事先备份。

5、查看 SQL 执行成本：SHOW PROFILE

Show Profile：MySQL提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量。默认情况下，参数处于关闭状态，并保存最近15次的运行结果。

分析步骤

1、是否支持，看看当前的MySQL版本是否支持。

# 查看Show Profile功能是否开启
mysql> SHOW VARIABLES LIKE 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

2、通过设置 profiling='ON' 来开启 show profile：

# 开启Show Profile功能
mysql> SET profiling=ON;
Query OK, 0 rows affected, 1 warning (0.00 sec)

3、然后执行相关的查询语句

SELECT * FROM `emp` GROUP BY `id`%10 LIMIT 150000;
SELECT * FROM `emp` GROUP BY `id`%20 ORDER BY 5;

4、接着看下当前会话都有哪些 profiles，使用下面这条命令：

mysql > show profiles;

image.png

Duration：持续时间(耗时) 。注：使用navicat会出现一些别的语句。

5、诊断SQL，使用show profile对sql语句进行诊断，你能看到当前会话一共有 2 个查询。如果我们想要查看最近一次查询的开销，可以使用：

mysql > show profile;

image.png

当然，我们也可以查看更加详细的信息

mysql> show profile cpu,block io for query 2

image.png

show profile的常用查询参数：

  • ALL：显示所有的开销信息。
  • BLOCK IO：显示块IO开销。
  • CONTEXT SWITCHES：上下文切换开销。
  • CPU：显示CPU开销信息。
  • IPC：显示发送和接收开销信息
  • MEMORY：显示内存开销信息。
  • PAGE FAULTS：显示页面错误开销信息。
  • SOURCE：显示和Source_function，Source_file， Source_line相关的开销信息。
  • SWAPS：显示交换次数开销信息。

日常开发需要注意的结论：

  • converting HEAP to MyISAM：查询结果太大，内存都不够用了，往磁盘上搬了。
  • Creating tmp table：创建临时表（拷贝数据到临时表，用完再删除），非常耗费数据库性能。
  • Copying to tmp table on disk：把内存中的临时表复制到磁盘，危险！！！
  • locked：死锁。
  • 如果在show profile诊断结果中出现了以上4条结果中的任何一条，则sql语句需要优化。

总结

1.show profile默认是关闭的，并且开启后只存活于当前会话，也就说每次使用前都需要开启。

2.通过show profiles查看sql语句的耗时时间，然后通过show profile命令对耗时时间长的sql语句进行诊断。

3.注意show profile诊断结果中出现相关字段的含义，判断是否需要优化sql语句。

4.可更多的关注MySQL官方文档，获取更多的知识。

7、全局查询日志

全局查询日志用于保存所有的sql执行记录，该功能主要用于测试环境，在生产环境中永远不要开启该功能。

通过命令开启该功能。

开启
set global general_log=1;
日志输出
set global log_output='TABLE';
日志查看
select * from mysql.general_log;

8、分析查询语句：EXPLAIN

8.1、MySQL常见瓶颈

1、CPU：CPU在饱和的适合一般发生在数据装入内存或从磁盘上读取数据时候

2、IO：磁盘的I/O瓶颈发生在装入数据远大于内容容量的时候

3、服务器硬件的性能瓶颈：top、free、iostat和vmstat来查看系统的性能状态

8.2、EXPLAIN概述

EXPLAIN是什么？

EXPLAIN：SQL的查询执行计划，使用EXPLAIN关键字可以模拟优化器【mysql query optimizer查询优化器】执行SQL查询语句，从而知道MySQL是如何处理和执行SQL语句的。分析你的查询语句或表结构的性能瓶颈。

image.png

EXPLAIN怎么使用？

语法：explain + SQL

版本情况

  • MySQL 5.6.3以前只能 EXPLAIN SELECT ；MYSQL 5.6.3以后就可以 EXPLAIN SELECT，UPDATE， DELETE
  • 在5.7以前的版本中，想要显示 partitions 需要使用 explain partitions 命令；想要显示 filtered 需要使用 explain extended 命令。在5.7版本后，默认explain直接显示partitions和 filtered中的信息。

EXPLAIN 语句输出的各个列的作用如下：

image.png

EXPLAIN能干嘛？

可以查看以下信息：

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

8.3、EXPLAIN字段

为了充分说明这几个字段，下面给出一个例子进行演示讲解

CREATE TABLE t1(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));
CREATE TABLE t2(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));
CREATE TABLE t3(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));
CREATE TABLE t4(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));
INSERT INTO t1(content) VALUES(CONCAT('t1_',FLOOR(1+RAND()*1000)));
INSERT INTO t2(content) VALUES(CONCAT('t2_',FLOOR(1+RAND()*1000)));
INSERT INTO t3(content) VALUES(CONCAT('t3_',FLOOR(1+RAND()*1000)));
INSERT INTO t4(content) VALUES(CONCAT('t4_',FLOOR(1+RAND()*1000)));

image.png

id

id：select 查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。

值有以下三种情况：

  • id相同，执行顺序由上至下。由此可见是先操作t1表，然后是t3表，然后是t2表

image.png

  • id不同，如果是子查询【subquery】，id的序号会递增，id值越大优先级越高，越先被执行。

image.png

  • id有相同也有不同，同时存在。永远是id大的优先级最高，id相等的时候顺序执行。 即id 如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id 值越大，优先级越高，越先执行。衍生= DERIVED

    • 从下面可知，这条sql语句时先执行t3表【t3这条子查询】

image.png

关注点：id 号的每个号码，表示一趟独立的查询。一个sql 的查询趟数越少越好。

select_type

select_type：数据查询的类型，主要是用于区别，普通查询、联合查询、子查询等的复杂查询。

image.png

  • SIMPLE：简单的SELECT查询，查询中不包含子查询或者UNION ，其实就是单表查询

image.png

  • PRIMARY：查询中如果包含任何复杂的子部分，最外层查询则被标记为PRIMARY。

image.png

  • SUBQUERY：在SELECT或者WHERE子句中包含了子查询。

image.png

  • DERIVED：在FROM子句中包含的子查询被标记为DERIVED(衍生)，MySQL会递归执行这些子查询，把结果放在临时表中【临时表会增加负担】。

  • DEPENDENT SUBQUERY：在SELECT 或WHERE 列表中包含了子查询,子查询基于外层。与subquery的差别就是他们都是where 后面的条件，但是subquery是单个值，dependent subquery 是一组值。

image.png

  • UNION：如果第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为DERIVED。

image.png

  • UNION RESULT：从UNION表获取结果的SELECT。

table

这个数据是基于哪张表的。

type

type：查询的访问类型排列，是较为重要的一个指标

结果值从最好到最坏依次是：

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

SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts级别。（阿里巴巴 开发手册要求）

  • system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计。

image.png

  • const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转化为一个常量。

image.png

  • eq_ref：唯一性索引扫描，读取本表中和关联表表中的每行组合成的一行，查出来只有一条记录。除了 system 和 const 类型之外， 这是最好的联接类型。

image.png

  • ref：非唯一性索引扫描，返回本表和关联表某个值匹配的所有行，查出来有多条记录。

image.png

  • range：只检索给定范围的行，key 列显示使用了哪个索引一般就是在WHERE语句中出现了BETWEEN、< >、in等的查询。这种范围扫描索引比全表扫描要好，因为它只需要开始于索引树的某一点，而结束于另一点，不用扫描全部索引。

image.png

  • index：Full Index Scan，全索引扫描，index和ALL的区别为index类型只遍历索引树。**也就是说虽然ALL和index都是读全表，但是index是从索引中读的，ALL是从磁盘中读取的。**出现index是sql使用了索引但是没用,通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组。

image.png

  • ALL：Full Table Scan，没有用到索引，全表扫描，将遍历全表以找到匹配的行

image.png

possible_keys 和 key

possible_keys：显示可能应用在这张表中的索引，一个或者多个。若查询语句涉及到的字段上存在索引，则该索引将被列出，但不一定被查询实际使用。

key：实际使用的索引。如果为NULL，则没有使用索引。查询中如果使用了覆盖索引，则该索引仅仅出现在key列表中。

image.png

key_len

key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。在不损失精度的情况下，。ken_len 越长，说明索引使用的越充分。

image.png

key_len计算规则：blog.csdn.net/qq_34930488…

  1. 先看索引上字段的类型+长度，比如int=4 ; varchar(20) =20 ; char(20) =20
  2. 如果是varchar 或者char 这种字符串字段，视字符集要乘不同的值，比如utf-8 要乘3,GBK 要乘2，
  3. varchar 这种动态字符串要加2 个字节
  4. 允许为空的字段要加1 个字节
    1. 第一组：key_len=age 的字节长度+name 的字节长度=4+1 + ( 20*3+2)=5+62=67
    2. 第二组：key_len=age 的字节长度=4+1=5

image.png

ref

ref：显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

image.png

rows

rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录需要读取的行数。rows 列显示MySQL 认为它执行查询时必须检查的行数。越少越好

image.png

Extra

Extra：包含不适合在其他列中显示但十分重要的额外信息。

  • Using filesort：说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作成为"文件内排序"。

    • 优化前：【排序没有使用索引】

image.png

- 优化后【排序使用到了索引，优化后，不再出现filesort 的情况】

image.png

查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。

  • Using temporary：使用了临时表保存中间结果，MySQL在対查询结果排序时使用了临时表。常见于排序order by和分组查询group by。临时表対系统性能损耗很大。

    • 优化前：

image.png

  • 优化后【加索引】

image.png

  • Using index：表示相应的SELECT操作中使用了覆盖索引(Covering Index)，，避免访问了表的数据行，效率不错！如果同时出现Using where，表示索引被用来执行索引键值的查找；如果没有同时出现Using where，表明索引用来读取数据而非执行查找动作。
# 覆盖索引
# 就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。
# 注意：如果要使用覆盖索引，一定不能写SELECT *，要写出具体的字段。
mysql> explain select cat_id from pms_category \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pms_category
   partitions: NULL
         type: index
possible_keys: NULL       
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 1425
     filtered: 100.00
        Extra: Using index   # select的数据列只用从索引中就能够取得，不必从数据表中读取   
1 row in set, 1 warning (0.00 sec)

  • Using where：表明使用了WHERE过滤。

  • Using join buffer：使用了连接缓存。

image.png

  • impossible where：WHERE子句的值总是false，不能用来获取任何元组。
mysql> explain select name from pms_category where name = 'zs' and name = 'ls'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Impossible WHERE   # 不可能字段同时查到两个名字
1 row in set, 1 warning (0.00 sec)

select tables optimized away：在没有GROUPBY 子句的情况下，基于索引优化MIN/MAX 操作或者对于MyISAM 存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

在innodb 中：

image.png

在Myisam 中：

image.png

  • EXPLAIN不考虑各种Cache
  • EXPLAIN不能显示MySQL在执行查询时所作的优化工作
  • EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
  • 部分统计信息是估算的，并非精确值

8.4、练习

image.png

avatar
文章
阅读
粉丝