我有一个 MySQL 表，其中包含大约 2000 万行数据。

+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| b_id        | int(11)     | YES  | MUL | NULL    |                |
| order       | bigint(20)  | YES  | MUL | NULL    |                |
| date        | date        | YES  |     | NULL    |                |
| time        | time        | YES  |     | NULL    |                |
| channel     | varchar(8)  | YES  | MUL | NULL    |                |
| data        | varchar(60) | YES  |     | NULL    |                |
| date_system | date        | YES  | MUL | NULL    |                |
| time_system | time        | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+

我在 (b_id, channel, date) 上有一个非唯一索引来加速查询，例如:

select date, left(time,2) as hour, round(data,1) as data
from data_lines
where channel='1'
  and b_id='300'
  and date >='2013-04-19'
  and date <='2013-04-26' 
group by date,hour

问题是我的插入有时会重叠，所以我想使用“ON DUPLICATE KEY UPDATE”，但这需要一个唯一索引。所以我在 (b_id, channel, date, time) 上创建了一个唯一索引，因为这是确定是否存在 double 值的四个主要特征。插入现在工作正常，但是我的选择查询速度慢得令人无法接受。

我不太清楚为什么自从添加新索引后我的选择变慢了:

• 时间是否如此独特以至于索引变得非常大 --> 并且很慢？
• 我应该删除非唯一索引以加快处理速度吗？
• 是我查询不好吗？
• 欢迎提出其他想法!

郑重声明(order、date_system 和 time_system)根本不在索引或选择中使用，但确实包含数据。插入从 C 和 Python 运行，选择从 PHP 运行。

根据请求解释查询:

mysql> explain select date, left(time,2) as hour, round(data,1) as data 
from data_lines 
where channel='1'
  and b_id='300'
  and date >='2013-04-19'
  and date <='2013-04-26'
group by date,hour;

+----+-------------+-----------+------+--------------------------------+------------+---------+-------------+------+----------------------------------------------+
| id | select_type | table     | type | possible_keys                  | key        | key_len | ref         | rows | Extra                                        |
+----+-------------+-----------+------+--------------------------------+------------+---------+-------------+------+----------------------------------------------+
|  1 | SIMPLE      | data_lines| ref  | update_index,b_id,comp_index   | comp_index | 16      | const,const | 3548 | Using where; Using temporary; Using filesort |
+----+-------------+-----------+------+--------------------------------+------------+---------+-------------+------+----------------------------------------------+

update_index 是我的(b_id、 channel 、日期、时间)的唯一索引，comp_index 是我的(b_id、 channel 、日期)的非唯一索引。

索引是:

+-----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| data_lines|          0 | PRIMARY      |            1 | id          | A         |    17918898 |     NULL | NULL   |      | BTREE      |         |               |
| data_lines|          0 | id_UNIQUE    |            1 | id          | A         |    17918898 |     NULL | NULL   |      | BTREE      |         |               |
| data_lines|          0 | update_index |            1 | channel     | A         |          17 |     NULL | NULL   | YES  | BTREE      |         |               |
| data_lines|          0 | update_index |            2 | b_id        | A         |          17 |     NULL | NULL   | YES  | BTREE      |         |               |
| data_lines|          0 | update_index |            3 | date        | A         |       44244 |     NULL | NULL   | YES  | BTREE      |         |               |
| data_lines|          0 | update_index |            4 | time        | A         |    17918898 |     NULL | NULL   | YES  | BTREE      |         |               |
| data_lines|          1 | box_id       |            1 | b_id        | A         |          17 |     NULL | NULL   | YES  | BTREE      |         |               |
| data_lines|          1 | idx          |            1 | order       | A         |    17918898 |     NULL | NULL   | YES  | BTREE      |         |               |
| data_lines|          1 | comp_index   |            1 | b_id        | A         |          17 |     NULL | NULL   | YES  | BTREE      |         |               |
| data_lines|          1 | comp_index   |            2 | channel     | A         |        6624 |     NULL | NULL   | YES  | BTREE      |         |               |
| data_lines|          1 | comp_index   |            3 | date        | A         |      165915 |     NULL | NULL   | YES  | BTREE      |         |               |
| data_lines|          1 | date_system  |            1 | date_system | A         |          17 |     NULL | NULL   | YES  | BTREE      |         |               |
| data_lines|          1 | mac          |            1 | mac         | A         |          17 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

最佳答案

尝试在查询中显式指定 USE INDEX(update_index)。

优化器在选择索引时做出了错误的选择，导致查询变慢。

希望这能解决您的问题..:)

关于MySQL索引导致查询变慢，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/16230698/