我模拟了这个场景。
CREATE TABLE mytable (
Clustering_key INT NOT NULL AUTO_INCREMENT,
ID INT NOT NULL,
Data text NOT NULL,
Position INT NOT NULL,
PRIMARY KEY (Clustering_key),
KEY(ID),
KEY(Position)
)
INSERT INTO mytable (ID,Data,Position) VALUES (5,CONCAT("Data-",5), 7);
INSERT INTO mytable (ID,Data,Position) VALUES (5,CONCAT("Data-",5), 26);
INSERT INTO mytable (ID,Data,Position) VALUES (5,CONCAT("Data-",51), 27);
INSERT INTO mytable (ID,Data,Position) VALUES (5,CONCAT("Data-",56), 28);
INSERT INTO mytable (ID,Data,Position) VALUES (5,CONCAT("Data-",57), 31);
mysql> explain Select * from mytable where ID=5 Limit 3
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | mytable | NULL | ref | ID | ID | 4 | const | 5 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
是的explain
显示检查的行是 5,而不是 3。
但似乎这只是一个误导性的信息。
可以通过以下步骤为所有查询启用慢速日志(设置 long_query_time=0)来验证运行时 rows_examined 的确切数量。
注意:您必须仅在您自己的测试数据库中设置 long_query_time=0。并且测试后必须将参数重置回之前的值。
- set GLOBAL slow_query_log=1;
- set global long_query_time=0;
- set session long_query_time=0;
mysql> show variables like '%slow%';
+---------------------------+-------------------------------------------------+
| Variable_name | Value |
+---------------------------+-------------------------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /usr/local/mysql/data/slow.log |
+---------------------------+-------------------------------------------------+
5 rows in set (0.10 sec)
mysql> select @@long_query_time;
+-------------------+
| @@long_query_time |
+-------------------+
| 0.000000 |
+-------------------+
And then in the terminal, executing the query
<pre>
mysql> Select * from mytable where ID=5 Limit 3;
+----------------+----+---------+----------+
| Clustering_key | ID | Data | Position |
+----------------+----+---------+----------+
| 5 | 5 | Data-5 | 7 |
| 26293 | 5 | Data-5 | 26 |
| 26294 | 5 | Data-51 | 27 |
+----------------+----+---------+----------+
3 rows in set (0.00 sec)
mysql> Select * from mytable where ID=5 Limit 1;
通过检查来检查慢速日志slow_query_log_file
上面印有/usr/local/mysql/data/slow.log
您可以通过以下方式了解信息。
# Time: 2019-04-26T01:48:19.890846Z
# User@Host: root[root] @ localhost [] Id: 5124
# Query_time: 0.000575 Lock_time: 0.000146 Rows_sent: 3 Rows_examined: 3
SET timestamp=1556243299;
Select * from mytable where ID=5 Limit 3;
# Time: 2019-04-26T01:48:34.672888Z
# User@Host: root[root] @ localhost [] Id: 5124
# Query_time: 0.000182 Lock_time: 0.000074 Rows_sent: 1 Rows_examined: 1
SET timestamp=1556243314;
Select * from mytable where ID=5 Limit 1;
运行时Rows_exmained
值等于limit
范围。
测试在MySQL 5.7.18上完成。
----------------------------------另一种验证方式------------------------ ----------------------
mysql> show status like '%Innodb_rows_read%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Innodb_rows_read | 13 |
+------------------+-------+
1 row in set (0.00 sec)
mysql> Select * from mytable where ID=5 Limit 1;
+----------------+----+--------+----------+
| Clustering_key | ID | Data | Position |
+----------------+----+--------+----------+
| 5 | 5 | Data-5 | 7 |
+----------------+----+--------+----------+
1 row in set (0.00 sec)
mysql> show status like '%Innodb_rows_read%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Innodb_rows_read | 14 |
+------------------+-------+
1 row in set (0.00 sec)
您可以看到Innodb_rows_read
只需将限制 1 增加 1 即可。
如果执行全表扫描查询,您可以看到该值将增加表的计数。
mysql> select count(*) from mytable;
+----------+
| count(*) |
+----------+
| 126296 |
+----------+
1 row in set (0.05 sec)
mysql> show status like '%Innodb_rows_read%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| Innodb_rows_read | 505204 |
+------------------+--------+
1 row in set (0.00 sec)
mysql> Select * from mytable where Data="Data-5";
+----------------+----+--------+----------+
| Clustering_key | ID | Data | Position |
+----------------+----+--------+----------+
| 5 | 5 | Data-5 | 7 |
| 26293 | 5 | Data-5 | 26 |
| 26301 | 5 | Data-5 | 7 |
+----------------+----+--------+----------+
3 rows in set (0.09 sec)
mysql> show status like '%Innodb_rows_read%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| Innodb_rows_read | 631500 |
+------------------+--------+
1 row in set (0.00 sec)
两种方式都证实了explain
for limit 似乎提供了有关所检查行的误导性信息。