mysql - 如何在不扫描所有行的情况下在我的索引列上使用查询限制？

Question

这是我的 table :

在我的 table 上

• Clustering_key(主键自增)
• ID(索引列)
• Data(文本数据类型列)
• Position(索引列)维护Data
的顺序

我的表有 90,000 行相同的 ID 等于 5。我想前 3 行 ID 等于 5 我的查询是这样的

Select * from mytable where ID=5 Limit 3;

ID 列是索引列所以我认为 mysql 只扫描前 3 行，但 mysql 扫描大约 42000 行。

这里解释查询:

避免所有行扫描的任何可能性。

请给我一些解决方案

提前致谢

Answer 1

我模拟了这个场景。

• 使用
创建表

   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)    )

• Inserted data with

    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);

• Explain

    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)

Yes, the explain shows rows examined is 5, but not 3.But seems it is just a misleading info.The exact number of run-time rows_examined can be verified by enabling slow log for all queries(Setting long_query_time=0) by following steps.

Note: You MUST set long_query_time=0 only in your own testing database. And you MUST reset the parameter back to the previous value after the testing.

     - 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;

通过检查 /usr/local/mysql/data/slow.log 上面打印的 slow_query_log_file 检查慢日志

您可以找到以下信息。

    # 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似乎提供了有关所检查行的误导性信息。