如何在不扫描所有行的情况下在索引列上的查询中使用限制? [英] How to use Limit in query on my index column without scan all rows?

问题描述

这是我的桌子:

在我的桌子上

• Clustering_key(主键和自动增量键)
• ID(索引列)
• Data(文本数据类型"列)
• Position(索引列)保持Data
的顺序

• Clustering_key (Primary key and auto incremental)
• ID (Index Column)
• Data (Text datatype column)
• Position(Index column) maintain the order of Data

我的表有90,000行，相同的ID等于5.我想在前3行中的ID等于5，我的查询像这样

My table have 90,000 rows with same ID equal to 5. I want to first 3 rows with ID equal to 5 and my query like this

Select * from mytable where ID=5 Limit 3;

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

ID column is index column So I think mysql scan only first 3 rows but mysql scan around 42000 rows.

在这里解释查询:

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

Any possibility to avoid all rows scan.

请给我一些解决方法

预先感谢

推荐答案

我模拟了这种情况.

• 使用
创建表

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

• 解释

    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的确切数量.

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.

注意:您只能在自己的测试数据库中设置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;

通过检查打印在/usr/local/mysql/data/slow.log上方的slow_query_log_file

Checking the slow log by inspecting the slow_query_log_file printed above /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上完成的.

The runtime Rows_exmained value is equal to the value of limit parameter. The test is done on MySQL 5.7.18.

----------------------------------另一种验证方式---------- -------------------------

----------------------------------Another way to verify----------------------------------

    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. 如果您执行全表扫描查询，则可以看到该值将随着表的计数而增加.

You can see the Innodb_rows_read just be increased 1 for limit 1. If you do a full table scan query, you can see the value will be increased by the count of the table.

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

Both ways confirmed the explain for limit seems providing misleading info about rows examined.

这篇关于如何在不扫描所有行的情况下在索引列上的查询中使用限制?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！