gpt4 book ai didi

正在考虑 Mysql 索引,但仍有大表扫描

转载 作者:行者123 更新时间:2023-11-29 03:11:36 26 4
gpt4 key购买 nike

我有一个带有主键和几个日期相关字段的 Job 表 ...

 +------------------+---------------+------+-----+-------------------+----------------+| Field            | Type          | Null | Key | Default           | Extra          |+------------------+---------------+------+-----+-------------------+----------------+| jobId            | bigint(20)    | NO   | PRI | NULL              | auto_increment || creationDateTime | datetime      | NO   | MUL | NULL              |                || lastModified     | timestamp     | NO   | MUL | CURRENT_TIMESTAMP |                |+------------------+---------------+------+-----+-------------------+----------------+

此表有 426,579 行和以下索引

+---------+------------+--------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+| Table   | Non_unique | Key_name                 | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |+---------+------------+--------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+| Job     |          0 | PRIMARY                  |            1 | jobId            | A         |      439957 |     NULL | NULL   |      | BTREE      |         || Job     |          1 | Job_lastModified_idx     |            1 | lastModified     | A         |      439957 |     NULL | NULL   |      | BTREE      |         || Job     |          1 | Job_creationDateTime_idx |            1 | creationDateTime | A         |      439957 |     NULL | NULL   |      | BTREE      |         |+---------+------------+--------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+

现在查询如...

select * from Job where jobId > 1000 and creationDateTime between '2011-09-07 18:29:24' and '2011-09-07 20:00:33';

按预期运行得非常快(0s)

解释...

+----+-------------+-------+-------+----------------------------------+--------------------------+---------+------+------+-------------+| id | select_type | table | type  | possible_keys                    | key                      | key_len | ref  | rows | Extra       |+----+-------------+-------+-------+----------------------------------+--------------------------+---------+------+------+-------------+|  1 | SIMPLE      | Job   | range | PRIMARY,Job_creationDateTime_idx | Job_creationDateTime_idx | 8       | NULL |   39 | Using where |+----+-------------+-------+-------+----------------------------------+--------------------------+---------+------+------+-------------+

我看到针对 lastModified 而不是 creationDateTime 的等效查询的速度相同


但是稍微复杂一点的查询...

select * from Job where jobId > 1000 and (lastModified between '2011-09-07 18:29:24' and '2011-09-07 20:00:33'       or creationDateTime between '2011-09-07 18:29:24' and '2011-09-07 20:00:33');

运行缓慢(9 秒)并且出于某种原因最终不得不进行更大的扫描,即使正如预期的那样,它返回相同的行数 (40)

+----+-------------+-------+-------+-------------------------------------------------------+---------+---------+------+--------+-------------+| id | select_type | table | type  | possible_keys                                         | key     | key_len | ref  | rows   | Extra       |+----+-------------+-------+-------+-------------------------------------------------------+---------+---------+------+--------+-------------+|  1 | SIMPLE      | Job   | range | PRIMARY,Job_lastModified_idx,Job_creationDateTime_idx | PRIMARY | 8       | NULL | 204581 | Using where |+----+-------------+-------+-------+-------------------------------------------------------+---------+---------+------+--------+-------------+

(注意:这个表有426579行,所以不确定204581是从哪里来的)


我原以为这会同样快?

为什么MySQL在这样组合时无法使用这些索引?

为 lastModified 和 creationDateTime 创建一些额外的复合索引根本没有帮助。

 create index test_idx1 on Job (lastModified,creationDateTime); create index test_idx2 on Job (jobId,lastModified,creationDateTime);

我一定是漏掉了一些简单的东西?

最佳答案

从@bill 的引用来看,我们的 mysql 版本 (5.0.67) 似乎不支持索引合并,因此在这种情况下没有索引会有所帮助。

我会选择一个快速的工会,谢谢大家!

关于正在考虑 Mysql 索引,但仍有大表扫描,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/7366998/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com