mysql - 使用 "where"和 "order by"子句为我的 sql 表创建索引以进行查询

Question

我在 mysql 数据库中有一个表。我的查询看起来像

SELECT device_id,date,timestamp,power,floor 
from tableData 
where date=%s 
and floor=%s 
order by timestamp desc 
limit 1

我已经在我的表上创建了索引以便更快地得到结果。我的索引:

+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| minionData |          1 | idx1     |            1 | date        | A         |        9447 |     NULL | NULL   | YES  | BTREE      |         |               |
| minionData |          1 | idx1     |            2 | floor       | A         |       12072 |     NULL | NULL   | YES  | BTREE      |         |               |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

所以我的问题是针对我正在使用的特定查询，data 和 floor 列上的索引已经足够好了，还是我也应该在 timestamp 列上建立索引，以提高查询性能？

Answer 1

为每一列添加索引不允许数据库正确优化查询(一次只会使用一个索引)。

首先:要优化 where 子句，您需要在两列上使用复合索引，例如:

tableData(date, floor)

但为了使这更有效，您还可以在ìindex 中添加用于排序的列，因此:

tableData(date, floor, timestamp)

如果您运行的是 MySQL 8.0，您可以使索引按 timestamp 列降序排列:

tableData(date, floor, timestamp desc)

最后:一个选择是添加您在索引中选择的其他列，使其成为覆盖索引；这可以允许仅使用索引执行整个查询(即不查看表数据):

tableData(date, floor, timestamp desc, device_id, power) 

有关如何在 MySQL 中优化索引使用的更多信息，我建议阅读很棒的 MySQL index cookbook由 Rick James 撰写(这也是一个活跃的 SO 用户)。