计算机教程

当前位置:3522.com > 计算机教程 > 3522.com:MySQL之Handler_read_*

3522.com:MySQL之Handler_read_*

来源:http://www.4sports-uk.com 作者:3522.com 时间:2019-12-12 08:44

在MySQL里,我们一般使用SHOW STATUS查询服务器状态,语法一般来说如下:

MySQL EXPLAIN命令详解学习

SHOW [GLOBAL | SESSION] STATUS [LIKE 'pattern' | WHERE expr]

 

执行命令后会看到很多内容,其中有一部分是Handler_read_*,它们显示了数据库处理SELECT查询语句的状态,对于调试SQL语句有很大意义,可惜实际很多人并不理解它们的实际意义,本文简单介绍一下:

MySQL的EXPLAIN命令用于SQL语句的查询执行计划(QEP)。这条命令的输出结果能够让我们了解MySQL 优化器是如何执行

为了让介绍更易懂,先建立一个测试用的表:

SQL 语句的。这条命令并没有提供任何调整建议,但它能够提供重要的信息帮助你做出调优决策。

CREATE TABLE IF NOT EXISTS `foo` (
`id` int(10) unsigned NOT NULL auto_increment,
`col1` varchar(10) NOT NULL,
`col2` text NOT NULL,
PRIMARY KEY (`id`),
KEY `col1` (`col1`)
);

1 语法

INSERT INTO `foo` (`id`, `col1`, `col2`) VALUES
(1, 'a', 'a'),
(2, 'b', 'b'),
(3, 'c', 'c'),
(4, 'd', 'd'),
(5, 'e', 'e'),
(6, 'f', 'f'),
(7, 'g', 'g'),
(8, 'h', 'h'),
(9, 'i', 'i');

MySQL 的EXPLAIN 语法可以运行在SELECT 语句或者特定表上。如果作用在表上,那么此命令等同于DESC 表命令。UPDATE

在下面的测试里,每次执行SQL时按照如下过程执行:

和DELETE 命令也需要进行性能改进,当这些命令不是直接在表的主码上运行时,为了确保最优化的索引使用率,需要把它们改

FLUSH STATUS;
SELECT ...;
SHOW SESSION STATUS LIKE 'Handler_read%';
EXPLAIN SELECT ...;

写成SELECT 语句(以便对它们执行EXPLAIN 命令)。请看下面的示例:

Handler_read_first

UPDATE table1

The number of times the first entry was read from an index. If this value is high, it suggests that the server is doing a lot of full index scans; for example, SELECT col1 FROM foo, assuming that col1 is indexed.

SET col1 = X, col2 = Y

此选项表明SQL是在做一个全索引扫描,注意是全部,而不是部分,所以说如果存在WHERE语句,这个选项是不会变的。如果这个选项的数值很大,既是好事也是坏事。说它好是因为毕竟查询是在索引里完成的,而不是数据文件里,说它坏是因为大数据量时,简便是索引文件,做一次完整的扫描也是很费时的。

WHERE id1 = 9

FLUSH STATUS;

AND dt >= '2010-01-01';

SELECT col1 FROM foo;

这个UPDATE语句可以被重写成为下面这样的SELECT语句:

mysql> SHOW SESSION STATUS LIKE 'Handler_read%';
----------------------- -------
| Variable_name         | Value |
----------------------- -------
| Handler_read_first    | 1     |
| Handler_read_key      | 0     |
| Handler_read_next     | 9     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
----------------------- -------
6 rows in set (0.00 sec)

SELECT col1, col2

mysql> EXPLAIN SELECT col1 FROM fooG
         type: index
        Extra: Using index

FROM table1

Handler_read_key

WHERE id1 = 9

The number of requests to read a row based on a key. If this value is high, it is a good indication that your tables are properly indexed for your queries.

AND dt >= '2010-01-01';

此选项数值如果很高,那么恭喜你,你的系统高效的使用了索引,一切运转良好。

在5.6.10版本里面,是可以直接对dml语句进行explain分析操作的.

FLUSH STATUS;

MySQL 优化器是基于开销来工作的,它并不提供任何的QEP的位置。这意味着QEP 是在每条SQL 语句执行的时候动态地计

SELECT * FROM foo WHERE col1 = 'e';

算出来的。在MySQL 存储过程中的SQL 语句也是在每次执行时计算QEP 的。存储过程缓存仅仅解析查询树。

mysql> SHOW SESSION STATUS LIKE 'Handler_read%';
----------------------- -------
| Variable_name         | Value |
----------------------- -------
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_next     | 1     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
----------------------- -------

2 各列详解

mysql> EXPLAIN SELECT * FROM foo WHERE col1 = 'e'G
         type: ref
        Extra: Using where

MySQL EXPLAIN命令能够为SQL语句中的每个表生成以下信息:

Handler_read_next

mysql> EXPLAIN SELECT * FROM inventory WHERE item_id = 16102176G;

The number of requests to read the next row in key order. This value is incremented if you are querying an index column with a range constraint or if you are doing an index scan.

  ********************* 1. row ***********************

此选项表明在进行索引扫描时,按照索引从数据文件里取数据的次数。

  id: 1

FLUSH STATUS;

  select_type: SIMPLE

SELECT col1 FROM foo ORDER BY col1 ASC;

  table: inventory

mysql> SHOW SESSION STATUS LIKE 'Handler_read%';
----------------------- -------
| Variable_name         | Value |
----------------------- -------
| Handler_read_first    | 1     |
| Handler_read_key      | 0     |
| Handler_read_next     | 9     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
----------------------- -------

  type: ALL

mysql> EXPLAIN SELECT * FROM foo WHERE col1 = 'e'G
         type: index
        Extra: Using index

  possible_keys: NULL

Handler_read_prev

  key: NULL

The number of requests to read the previous row in key order. This read method is mainly used to optimize ORDER BY ... DESC.

  key_len: NULL

此选项表明在进行索引扫描时,按照索引倒序从数据文件里取数据的次数,一般就是ORDER BY ... DESC。

  ref: NULL

FLUSH STATUS;

  rows: 787338

SELECT col1 FROM foo ORDER BY col1 DESC;

  Extra: Using where

mysql> SHOW SESSION STATUS LIKE 'Handler_read%';
----------------------- -------
| Variable_name         | Value |
----------------------- -------
| Handler_read_first    | 0     |
| Handler_read_key      | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 9     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
----------------------- -------

  

mysql> EXPLAIN SELECT col1 FROM foo ORDER BY col1 DESCG
         type: index
        Extra: Using index

这个QEP 显示没有使用任何索引(也就是全表扫描)并且处理了大量的行来满足查询。对同样一条SELECT 语句,一个优化过的QEP 如下所示:

Handler_read_rnd

  ********************* 1. row ***********************

The number of requests to read a row based on a fixed position. This value is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan entire tables or you have joins that don't use keys properly.

  id: 1

简单的说,就是查询直接操作了数据文件,很多时候表现为没有使用索引或者文件排序。

  select_type: SIMPLE

FLUSH STATUS;

  table: inventory

SELECT * FROM foo ORDER BY col2 DESC;

  type: ref

mysql> SHOW SESSION STATUS LIKE 'Handler_read%';
----------------------- -------
| Variable_name         | Value |
----------------------- -------
| Handler_read_first    | 0     |
| Handler_read_key      | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 9     |
| Handler_read_rnd_next | 10    |
----------------------- -------

  possible_keys: item_id

mysql> EXPLAIN SELECT * FROM foo ORDER BY col2 DESCG
         type: ALL
        Extra: Using filesort

  key: item_id

Handler_read_rnd_next

  key_len: 4

The number of requests to read the next row in the data file. This value is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.

  ref: const

此选项表明在进行数据文件扫描时,从数据文件里取数据的次数。

  rows: 1

FLUSH STATUS;

  Extra:

SELECT * FROM foo;

在这个QEP 中,我们看到使用了一个索引,且估计只有一行数据将被获取。

mysql> SHOW SESSION STATUS LIKE 'Handler_read%';
----------------------- -------
| Variable_name         | Value |
----------------------- -------
| Handler_read_first    | 0     |
| Handler_read_key      | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 10    |
----------------------- -------

QEP 中每个行的所有列表如下所示:

mysql> EXPLAIN SELECT * FROM foo ORDER BY col2 DESCG
         type: ALL
        Extra: Using filesort

 id

后记:不同平台,不同版本的MySQL,在运行上面例子的时候,Handler_read_*的数值可能会有所不同,这并不要紧,关键是你要意识到Handler_read_*可以协助你理解MySQL处理查询的过程,很多时候,为了完成一个查询任务,我们往往可以写出几种查询语句,这时,你不妨挨个按照上面的方式执行,根据结果中的Handler_read_*数值,你就能相对容易的判断各种查询方式的优劣。

 select_type

说到判断查询方式优劣这个问题,就再顺便提提show profile语法,在新版MySQL里提供了这个功能:

 table

mysql> set profiling=on;

 partitions(这一列只有在EXPLAIN PARTITIONS 语法中才会出现)

mysql> use mysql;
mysql> select * from user;

 possible_keys

mysql> show profile;
-------------------- ----------
| Status             | Duration |
-------------------- ----------
| starting           | 0.000078 |
| Opening tables     | 0.000022 |
| System lock        | 0.000010 |
| Table lock         | 0.000014 |
| init               | 0.000054 |
| optimizing         | 0.000008 |
| statistics         | 0.000015 |
| preparing          | 0.000014 |
| executing          | 0.000007 |
| Sending data       | 0.000139 |
| end                | 0.000007 |
| query end          | 0.000007 |
| freeing items      | 0.000044 |
| logging slow query | 0.000004 |
| cleaning up        | 0.000005 |
-------------------- ----------
15 rows in set (0.00 sec)

 key

mysql> show profiles;
---------- ------------ --------------------
| Query_ID | Duration   | Query              |
---------- ------------ --------------------
|        1 | 0.00017725 | SELECT DATABASE(). |
|        2 | 0.00042675 | select * from user |
---------- ------------ --------------------
2 rows in set (0.00 sec)

 key_len

作者 老王

 ref

http://www.bkjia.com/Mysql/489213.htmlwww.bkjia.comtruehttp://www.bkjia.com/Mysql/489213.htmlTechArticle在MySQL里,我们一般使用SHOW STATUS查询服务器状态,语法一般来说如下: SHOW [GLOBAL | SESSION] STATUS [LIKE pattern | WHERE expr] 执行命令后会看到很多...

 rows

 filtered(这一列只有在EXPLAINED EXTENDED 语法中才会出现)

 Extra

这些列展示了SELECT 语句对每一个表的QEP。一个表可能和一个物理模式表或者在SQL 执行时生成的内部临时表(例如从子查询或者合并操作会产生内部临时表)相关联。

 

2.1 key

 key 列指出优化器选择使用的索引。一般来说SQL 查询中的每个表都仅使用一个索引。也存在索引合并的少数例外情况,如给定表上用到了两个或者更多索引。

 下面是QEP 中key 列的示例:

 key: item_id

 key: NULL

 key: first, last

 SHOW CREATE TABLE <table>命令是最简单的查看表和索引列细节的方式。和key 列相关的列还包括possible_keys、rows 以及key_len。

2.2 ROWS

 rows 列提供了试图分析所有存在于累计结果集中的行数目的MySQL 优化器估计值。QEP 很容易描述这个很困难的统计量。

 查询中总的读操作数量是基于合并之前行的每一行的rows 值的连续积累而得出的。这是一种嵌套行算法。

 

 以连接两个表的QEP 为例。通过id=1 这个条件找到的第一行的rows 值为1,这等于对第一个表做了一次读操作。第二行是

 通过id=2 找到的,rows 的值为5。这等于有5 次读操作符合当前1 的积累量。参考两个表,读操作的总数目是6。在另一个QEP

 中,第一rows 的值是5,第二rows 的值是1。这等于第一个表有5 次读操作,对5个积累量中每个都有一个读操作。因此两个表

 总的读操作的次数是10(5 5)次。

 

 最好的估计值是1,一般来说这种情况发生在当寻找的行在表中可以通过主键或者唯一键找到的时候。

 在下面的QEP 中,外面的嵌套循环可以通过id=1 来找到,其估计的物理行数是1。第二个循环处理了10行。

 

 

 ********************* 1. row ***********************

 id: 1

 select_type: SIMPLE

 table: p

 type: const

 possible_keys: PRIMARY

 key: PRIMARY

 key_len: 4

 ref: const

 rows: 1

 Extra:

 ********************* 2. row ***********************

 id: 1

 select_type: SIMPLE

 table: c

 type: ref

 possible_keys: parent_id

 key: parent_id

 key_len: 4

 ref: const

 rows: 10

 Extra:

 

 可以使用SHOW STATUS 命令来查看实际的行操作。这个命令可以提供最佳的确认物理行操作的方式。请看下面的示例:

 mysql> SHOW SESSION STATUS LIKE 'Handler_read%';

  ----------------------- -------

  | Variable_name         | Value |

  ----------------------- -------

  | Handler_read_first    | 0     |

  | Handler_read_key      | 0     | 

  | Handler_read_last     | 0     |

  | Handler_read_next     | 0     |

  | Handler_read_prev     | 0     |

  | Handler_read_rnd      | 0     |

  | Handler_read_rnd_next | 11    |

  ----------------------- -------

  7 rows in set (0.00 sec)

  

 在下一个QEP 中,通过id=1 找到的外层嵌套循环估计有160行。第二个循环估计有1 行。

 ********************* 1. row ***********************

  id: 1

  select_type: SIMPLE

  table: p

  type: ALL

  possible_keys: NULL

  key: NULL

  key_len: NULL

  ref: NULL

  rows: 160

  Extra:

 ********************* 2. row ***********************

  id: 1

  select type: SIMPLE

  table: c

  type: ref

  possible_keys: PRIMARY,parent_id

  key: parent_id

  key_len: 4

  ref: test.p.parent_id

  rows: 1

  Extra: Using where

 

 通过SHOW STATUS 命令可以查看实际的行操作,该命令表明物理读操作数量大幅增加。请看下面的示例:

 mysql> SHOW SESSION STATUS LIKE 'Handler_read%';

  -------------------------------------- ---------

 | Variable_name | Value |

  -------------------------------------- ---------

 | Handler_read_first | 1 |

 | Handler_read_key | 164 |

 | Handler_read_last | 0 |

 | Handler_read_next | 107 |

 | Handler_read_prev | 0 |

 | Handler_read_rnd | 0 |

 | Handler_read_rnd_next | 161 |

  -------------------------------------- ---------

 相关的QEP 列还包括key列。

 

 2.3 possible_keys

 possible_keys 列指出优化器为查询选定的索引。

 一个会列出大量可能的索引(例如多于3 个)的QEP 意味着备选索引数量太多了,同时也可能提示存在一个无效的单列索引。

 可以用第2 章详细介绍过的SHOW INDEXES 命令来检查索引是否有效且是否具有合适的基数。

 为查询确定QEP 的速度也会影响到查询的性能。如果发现有大量的可能的索引,则意味着这些索引没有被使用到。

 相关的QEP 列还包括key 列。

 

 2.4 key_len

 key_len 列定义了用于SQL 语句的连接条件的键的长度。此列值对于确认索引的有效性以及多列索引中用到的列的数目很重要。

 此列的一些示例值如下所示:

 

 此列的一些示例值如下所示:

 key_len: 4 // INT NOT NULL

 key_len: 5 // INT NULL

 key_len: 30 // CHAR(30) NOT NULL

 key_len: 32 // VARCHAR(30) NOT NULL

 key_len: 92 // VARCHAR(30) NULL CHARSET=utf8

 

 从这些示例中可以看出,是否可以为空、可变长度的列以及key_len 列的值只和用在连接和WHERE 条件中的索引的列

 有关。索引中的其他列会在ORDER BY 或者GROUP BY 语句中被用到。下面这个来自于著名的开源博客软件WordPress 的表展示了

 如何以最佳方式使用带有定义好的表索引的SQL 语句:

 CREATE TABLE `wp_posts` (

  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

  `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

  `post_status` varchar(20) NOT NULL DEFAULT 'publish' ,

本文由3522.com发布于计算机教程,转载请注明出处:3522.com:MySQL之Handler_read_*

关键词: 3522.com