时间戳

首页 > 解决方案 > 如何在我的索引列的查询中使用限制而不扫描所有行？

mysql - 如何在我的索引列的查询中使用限制而不扫描所有行？

问题描述

这是我的桌子：

在此处输入图像描述

在我的桌子上

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

Select * from mytable where ID=5 Limit 3;

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

这里解释查询：

在此处输入图像描述

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

请给我一些解决方案

提前致谢

标签： mysqldatabaseinnodb

解决方案

我模拟了这个场景。

  • 使用创建表
   创建表 mytable (
        Clustering_key INT NOT NULL AUTO_INCREMENT,
        ID INT NOT NULL，
        数据文本不为空，
        位置 INT NOT NULL，
        主键（Clustering_key），
        密钥（ID），
        KEY(位置)
    )
  • 插入数据
    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 限制 3
    +----+-------------+---------+------------+------+ ---------------+------+---------+--------+------+-- --------+--------+
    | 编号 | 选择类型 | 表| 隔断 | 类型 | 可能的键 | 关键 | key_len | 参考 | 行 | 过滤 | 额外 |
    +----+-------------+---------+------------+------+ ---------------+------+---------+--------+------+-- --------+--------+
    | 1 | 简单 | 表 | 空 | 参考 | 身份证 | 身份证 | 4 | 常量 | 5 | 100.00 | 空 |
    +----+-------------+---------+------------+------+ ---------------+------+---------+--------+------+-- --------+--------+
    1 行，1 个警告（0.00 秒）

是的，explain检查的节目行是 5，但不是 3。但这似乎只是一个误导性信息。可以通过以下步骤为所有查询启用慢日志（设置 long_query_time=0）来验证运行时 rows_examined 的确切数量。

注意：您必须仅在您自己的测试数据库中设置 long_query_time=0。并且您必须在测试后将参数重置回之前的值。

     - 设置全球慢查询日志=1；
     - 设置全局 long_query_time=0；
     - 设置会话 long_query_time=0；
     mysql> 显示变量，如 '%slow%';
    +----------------------------+--------------------- --------------------------------------------+
    | 变量名 | 价值 |
    +----------------------------+--------------------- --------------------------------------------+
    | log_slow_admin_statements | 关闭 |
    | log_slow_slave_statements | 关闭 |
    | 慢启动时间 | 2 |
    | 慢查询日志 | 开 |
    | 慢查询日志文件 | /usr/local/mysql/data/slow.log |
    +----------------------------+--------------------- --------------------------------------------+
    5 行一组（0.10 秒）
    mysql> 选择@@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

您可以找到以下信息。

    # 时间：2019-04-26T01:48:19.890846Z
    # 用户@主机：root[root]@localhost []ID：5124
    # Query_time: 0.000575 Lock_time: 0.000146 Rows_sent: 3 Rows_examined: 3
    设置时间戳=1556243299；
    Select * from mytable where ID=5 Limit 3;
    # 时间：2019-04-26T01:48:34.672888Z
    # 用户@主机：root[root]@localhost []ID：5124
    # Query_time: 0.000182 Lock_time: 0.000074 Rows_sent: 1 Rows_examined: 1
    设置时间戳=1556243314；
    Select * from mytable where ID=5 Limit 1;

运行Rows_exmained时值等于limit参数的值。测试在 MySQL 5.7.18 上完成。

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


    mysql> 显示类似 '%Innodb_rows_read%' 的状态；
    +------------------+------+
    | 变量名 | 价值 |
    +------------------+------+
    | Innodb_rows_read | 13 |
    +------------------+------+
    一组中的 1 行（0.00 秒）

    mysql> Select * from mytable where ID=5 Limit 1;
    +----------------+----+--------+----------+
    | 聚类键 | 身份证 | 数据 | 职位 |
    +----------------+----+--------+----------+
    | 5 | 5 | 数据-5 | 7 |
    +----------------+----+--------+----------+
    一组中的 1 行（0.00 秒）

    mysql> 显示类似 '%Innodb_rows_read%' 的状态；
    +------------------+------+
    | 变量名 | 价值 |
    +------------------+------+
    | Innodb_rows_read | 14 |
    +------------------+------+
    一组中的 1 行（0.00 秒）

可以看到Innodb_rows_readlimit 1 只是增加了 1。如果做全表扫描查询，可以看到 value 会增加 table 的计数。

    mysql> 从 mytable 中选择 count(*)；
    +----------+
    | 计数(*) |
    +----------+
    | 126296 |
    +----------+
    一组中的 1 行（0.05 秒）

    mysql> 显示类似 '%Innodb_rows_read%' 的状态；
    +-----------------+--------+
    | 变量名 | 价值 |
    +-----------------+--------+
    | Innodb_rows_read | 505204 |
    +-----------------+--------+
    一组中的 1 行（0.00 秒）

    mysql> Select * from mytable where Data="Data-5";
    +----------------+----+--------+----------+
    | 聚类键 | 身份证 | 数据 | 职位 |
    +----------------+----+--------+----------+
    | 5 | 5 | 数据-5 | 7 |
    | 26293 | 5 | 数据-5 | 26 |
    | 26301 | 5 | 数据-5 | 7 |
    +----------------+----+--------+----------+
    3 行一组（0.09 秒）

    mysql> 显示类似 '%Innodb_rows_read%' 的状态；
    +-----------------+--------+
    | 变量名 | 价值 |
    +-----------------+--------+
    | Innodb_rows_read | 631500 |
    +-----------------+--------+
    一组中的 1 行（0.00 秒）

两种方式都证实了explainfor 限制似乎提供了有关检查行的误导性信息。

推荐阅读