mysql - 如何决定哪些字段必须在数据库表中建立索引

Question

解释

我有一个没有主键(甚至没有复合键)的表。

该表用于存储食品店的时段(营业时间和可送餐时间)。我们将该表命名为“business_hours”，主要字段如下。

• 店铺编号
• 天(0 - 6，表示周日 - 周六)
• 类型(打开，交付)
• 开始时间
• 结束时间

例如，如果商店 A 在周一上午 9 点至中午 1 点和下午 5 点至晚上 10 点营业，则此场景的 business_hours 表中将有两条记录。

-----------------------------------------------
| shop_id | day | type | start_time | end_time
-----------------------------------------------
| 1000    | 1   | open | 09:00:00   | 13:00:00
-----------------------------------------------
| 1000    | 1   | open | 17:00:00   | 22:00:00
-----------------------------------------------

当我查询此表时，我将始终使用 shop_id 作为 where 子句中的第一个条件。

例如:

SELECT COUNT(*) FROM business_hours WHERE shop_id = 1000 AND day = 1 AND type = 'open' AND start_time <= '13.29.00' AND end_time > '13.29.00';

问题

为“shop_id”应用索引就足够了，还是“day”和“type”字段也应该被索引？

如果您能解释索引的真正工作原理，那就更好了。

Answer 1

这取决于您应该指定的几个因素:

1. 数据增长的速度有多快
2. 表格的估计行数是多少
3. 将针对该表运行哪些查询
4. 您希望查询运行多快

更多的是这样想:一些服务每小时插入数千条新记录，旧记录每晚归档，每晚从该表创建报告。在这种情况下，您可能不想创建太多索引，因为它们会减慢插入速度。

另一方面，如果您的表增长和变化缓慢并且许多用户将对其运行查询，您需要有适当的索引来加速查询。

如果可以，请尝试创建大多数查询都可以从中受益的集群唯一主键。如果您有形成某个时间线的数据，并且大多数查询将使用日期时间标准(如从 - 到)获取数据范围，则最好将日期时间包含在聚集索引中 - 您将获得最快的查询性能。

所以像这样的东西会给你提到的选择最好的性能。 (但您不能为一个商店和类型存储重复的营业时间)

CREATE TABLE Business_hours
( shop_id INT NOT NULL 
, day INT NOT NULL
--- other columns
, CONSTRAINT Business_hours_PK
    PRIMARY KEY (shop_id, day, type, start_time, end_time)     -- your clustered index
) 

只需在 SELECT 中使用的字段(所有字段或仅其中一些最常用的字段)上创建索引，也将加快您的查询速度:

CREATE INDEX BusinessHours_IX ON business_hours (shop_id,day,type, start_time, end_time);

聚簇索引和非聚簇索引的区别在于，聚簇索引会影响数据库记录在磁盘上的存储顺序。

您可以使用 EXPLAIN 来查找数据库中缺失的索引，请参阅 this answer .

更多详情 this blog .