mysql - 具有性能问题的简单 MySQL 查询

Question

我有以下简单的 MySQL 查询:

SELECT SQL_NO_CACHE mainID
FROM tableName 
WHERE otherID3=19
AND dateStartCol >= '2012-08-01' 
AND dateStartCol <= '2012-08-31';

当我运行它时，需要 0.29 秒才能返回 36074 个结果。当我增加我的日期周期以带回更多结果 (65703) 时，它以 0.56 运行。当我在同一台服务器上但在不同的表(有些表更大)上运行其他类似的 SQL 查询时，结果会在大约 0.01 秒内返回。

虽然 0.29 并不慢 - 这是复杂查询的基本部分，这个时间意味着它不可扩展。

请参阅下面的表定义和索引。

我知道这不是服务器负载问题，因为我在使用很少的开发服务器上遇到了同样的问题。

+---------------------------+--------------+------+-----+---------+----------------+
| Field                     | Type         | Null | Key | Default | Extra          |
+---------------------------+--------------+------+-----+---------+----------------+
| mainID                    | int(11)      | NO   | PRI | NULL    | auto_increment |
| otherID1                  | int(11)      | NO   | MUL | NULL    |                |
| otherID2                  | int(11)      | NO   | MUL | NULL    |                |
| otherID3                  | int(11)      | NO   | MUL | NULL    |                |
| keyword                   | varchar(200) | NO   | MUL | NULL    |                |
| dateStartCol              | date         | NO   | MUL | NULL    |                |
| timeStartCol              | time         | NO   | MUL | NULL    |                |
| dateEndCol                | date         | NO   | MUL | NULL    |                |
| timeEndCol                | time         | NO   | MUL | NULL    |                |
| statusCode                | int(1)       | NO   | MUL | NULL    |                |
| uRL                       | text         | NO   |     | NULL    |                |
| hostname                  | varchar(200) | YES  | MUL | NULL    |                |
| IPAddress                 | varchar(25)  | YES  |     | NULL    |                |
| cookieVal                 | varchar(100) | NO   |     | NULL    |                |
| keywordVal                | varchar(60)  | NO   |     | NULL    |                |
| dateTimeCol               | datetime     | NO   | MUL | NULL    |                |
+---------------------------+--------------+------+-----+---------+----------------+


+--------------------+------------+-------------------------------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table              | Non_unique | Key_name                      | Seq_in_index | Column_name               | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------------+------------+-------------------------------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+
| tableName          |          0 | PRIMARY                       |            1 | mainID                    | A         |      661990 |     NULL | NULL   |      | BTREE      |         |
| tableName          |          1 | idx_otherID1                  |            1 | otherID1                   | A         |      330995 |     NULL | NULL   |      | BTREE      |         |
| tableName          |          1 | idx_otherID2                  |            1 | otherID2                   | A         |          25 |     NULL | NULL   |      | BTREE      |         |
| tableName          |          1 | idx_otherID3                  |            1 | otherID3                   | A         |          48 |     NULL | NULL   |      | BTREE      |         |
| tableName          |          1 | idx_dateStartCol              |            1 | dateStartCol               | A         |         187 |     NULL | NULL   |      | BTREE      |         |
| tableName          |          1 | idx_timeStartCol              |            1 | timeStartCol               | A         |       73554 |     NULL | NULL   |      | BTREE      |         |
|tableName          |          1 | idx_dateEndCol                 |            1 | dateEndCol                 | A         |         188 |     NULL | NULL   |      | BTREE      |         |
|tableName          |          1 | idx_timeEndCol                 |            1 | timeEndCol                 | A         |       73554 |     NULL | NULL   |      | BTREE      |         |
| tableName          |          1 | idx_keyword                   |            1 | keyword                    | A         |       82748 |     NULL | NULL   |      | BTREE      |         |
| tableName           |          1 | idx_hostname                 |            1 | hostname                   | A         |        2955 |     NULL | NULL   | YES  | BTREE      |         |
| tableName           |          1 | idx_dateTimeCol              |            1 | dateTimeCol                | A         |      220663 |     NULL | NULL   |      | BTREE      |         |
| tableName           |          1 | idx_statusCode               |            1 | statusCode                 | A         |           2 |     NULL | NULL   |      | BTREE      |         |
+--------------------+------------+-------------------------------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+

解释输出:

+----+-------------+-----------+-------+----------------------------------+-------------------+---------+------+-------+----------+-------------+
| id | select_type | table     | type  | possible_keys                    | key               | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-----------+-------+----------------------------------+-------------------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | tableName | range | idx_otherID3,idx_dateStartCol | idx_dateStartCol | 3       | NULL | 66875 |    75.00 | Using where |
+----+-------------+-----------+-------+----------------------------------+-------------------+---------+------+-------+----------+-------------+

Answer 1

如果这确实是您的查询(而不是相同的简化版本)，那么这应该会获得最佳结果:

 CREATE INDEX table_ndx on tableName( otherID3, dateStartCol, mainID);

第一个索引条目意味着WHERE中的第一个匹配非常快；这同样适用于 dateStartCol。第三个字段非常小，不会明显减慢索引速度，但可以让您立即在索引中找到所需的数据，而无需访问任何表。

键位于相同 索引中很重要。在您发布的 EXPLAIN 中，每个键都在自己的索引中，因此即使 MySQL 选择最佳索引，性能也不会是最佳的。我会尝试使用更少的索引，因为它们也有成本(无耻的插件:Can Indices actually decrease SELECT performance?)。