索引后MySQL仍然很慢

Question

我有一张桌子：

+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| id          | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| client_name | varchar(32)         | YES  | MUL | NULL    |                |
| timestamp   | varchar(32)         | YES  |     | NULL    |                |
| pair_name   | varchar(8)          | YES  |     | NULL    |                |
| bid_price   | decimal(20,8)       | YES  |     | NULL    |                |
| ask_price   | decimal(20,8)       | YES  |     | NULL    |                |
+-------------+---------------------+------+-----+---------+----------------+

它有超过70万行。因为当我尝试选择一些东西时速度很慢，所以我决定添加索引：

ALTER TABLE price_trading ADD INDEX price_trading_index (client_name, timestamp, pair_name);

+---------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table         | Non_unique | Key_name            | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| price_trading |          0 | PRIMARY             |            1 | id          | A         |      715216 |     NULL | NULL   |      | BTREE      |         |          |
| price_trading |          1 | price_trading_index |            1 | client_name | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |          |
| price_trading |          1 | price_trading_index |            2 | timestamp   | A         |       41897 |     NULL | NULL   | YES  | BTREE      |         |          |
| price_trading |          1 | price_trading_index |            3 | pair_name   | A         |      740698 |     NULL | NULL   | YES  | BTREE      |         |          |
+---------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

当我运行此语法时：

SELECT * FROM price_trading WHERE timestamp = 'lm83-1524620255198';

结果是这样的：

18 rows in set (23.43 sec)

如何提高查询速度？为什么索引方法不能让它更快呢？

谢谢。

Answer 1

您正在使用复合索引，复合索引从左到右工作，在您的示例中，它将适用于1) client_name和timestamp和pair_name 2) client_name和timestamp 3) client_name

因此，根据您需求，为时间戳列创建单独的索引。