大厂面试题:说一下怎么决定建立哪些索引?

摘要:数据库索引的设计是开发中不可忽视的重要环节。索引能够极大地提升查询性能,但错误的索引设计反而可能拖累数据库性能。那么,什么时候该建索引?什么时候又该避免?今天我们来深入聊聊这个问题。

数据库索引的设计是开发中不可忽视的重要环节。索引能够极大地提升查询性能,但错误的索引设计反而可能拖累数据库性能。那么,什么时候该建索引?什么时候又该避免?今天我们来深入聊聊这个问题。

首先,索引的本质是通过额外的数据结构(通常是 B+ 树或哈希表)来加速数据查询。索引能够将全表扫描变成快速的键值查找,节省了大量的时间。但索引并不是越多越好,因为它也带来了存储开销和写操作的性能损耗。

1、字段具有唯一性要求时
比如商品编码(product_code)或用户邮箱(email)等字段,唯一索引可以防止数据重复,同时加速查询。例如:

CREATE UNIQUE INDEX idx_product_code ON products (product_code);

2、经常用于 WHERE 查询的字段
如果某个字段经常出现在查询条件中,创建索引可以显著提高查询速度。例如:

SELECT * FROM orders WHERE order_status = 'SHIPPED';

这里 order_status 应该建立索引。对于多字段查询条件,还可以使用联合索引:

CREATE INDEX idx_order_status_date ON orders (order_status, order_date);

3、用于排序(ORDER BY)或分组(GROUP BY)的字段
如果查询结果需要排序或分组,索引可以减少排序操作的成本:

SELECT * FROM sales ORDER BY sale_date DESC;

在这种情况下,sale_date 字段可以加上索引,避免排序时的额外开销。

1、不参与查询的字段
如果字段从未出现在 WHERE、GROUP BY、ORDER BY 中,那就没有必要建索引。例如用户的个人说明字段 bio,几乎不参与查询逻辑,这种字段的索引只会占用空间。

2、重复值多的字段
比如性别字段 gender,值通常只有“男”和“女”。查询优化器会忽略这种高重复率的索引,转而进行全表扫描。假设有 100 万条用户记录,搜索“男”或“女”几乎查全表,索引价值极低。

3、小表数据
如果一张表的数据量非常小,比如只有几十条记录,全表扫描本身成本就很低,索引带来的收益不明显。

4、频繁更新的字段
比如电商用户余额 account_balance,这个字段在每次交易时都会被修改。频繁变更索引会带来额外的维护成本,导致写操作变慢。

假设我们设计一个订单查询功能,查询近一个月已发货的订单。SQL 查询如下:

SELECT * FROM orders WHERE order_status = 'SHIPPED' AND order_date >= NOW - INTERVAL 1 MONTH ORDER BY order_date DESC;

为提高性能,可以创建复合索引:

CREATE INDEX idx_orders_status_date ON orders (order_status, order_date);

这样查询优化器可以直接利用索引,快速锁定所需的数据范围。

最后,我们来看一道面试题,及怎么回答:

面试问题:什么时候该创建索引?什么情况下不建议使用索引?

回答:

索引的设计应该基于查询场景,遵循“查询频繁,数据区分度高”的原则。适合创建索引的场景包括:

字段具有唯一性要求,如用户邮箱。经常作为查询条件的字段,如订单状态。经常用于排序或分组的字段,如销售日期。

不适合建立索引的情况包括:

不参与查询的字段,如用户头像。高重复值的字段,如性别。数据量小的表,因全表扫描已足够高效。频繁更新的字段,如账户余额,因更新时索引维护成本高。

面试时强调索引设计的权衡原则,结合具体场景阐述,展示出技术深度与实践经验,面试官绝对会对你的回答刮目相看。

来源:麻辣小王子

相关推荐