什么字段适合创建索引？

八股文_MySQL面试题 0 3

参考回答

适合创建索引的字段通常具有以下特点：
1. 查询频繁：经常在 WHERE、JOIN 或排序（ORDER BY）条件中使用的字段。
2. 唯一性较高：值的区分度高，比如身份证号、邮箱等，不适合在重复率高的字段（如性别）上创建索引。
3. 经常作为排序和分组依据：在 ORDER BY、GROUP BY 中出现的字段。
4. 范围查询的字段：如 BETWEEN 或 > < 操作中的字段。
5. 数据量较大的表：小表通常无需索引，因为全表扫描性能足够高。

详细讲解与拓展

1. 字段适合创建索引的条件

1) 经常用于查询条件

字段如果频繁出现在 WHERE 或 JOIN 子句中，创建索引可以显著提升查询速度。
– 示例：用户表中 email 字段经常被用于查询用户信息：

“`sql
SELECT * FROM users WHERE email = 'user@example.com';
“`
在 `email` 字段上创建索引：
“`sql
CREATE INDEX idx_email ON users(email);
“`

2) 区分度高的字段

字段的值分布如果具有较高的唯一性（如身份证号、订单号等），索引能够有效减少扫描的记录数，从而提升查询效率。
– 区分度公式：区分度 = COUNT(DISTINCT(col)) / COUNT(*)
– 区分度高：如身份证号，区分度接近 1，适合创建索引。
– 区分度低：如性别（“男”或“女”），区分度低，索引效果不明显。

3) 需要排序的字段

如果查询需要对结果进行排序（ORDER BY），创建索引可以优化排序性能。
– 示例：订单表中按时间排序：

“`sql
SELECT * FROM orders ORDER BY created_at DESC;
“`
在 `created_at` 字段上创建索引：
“`sql
CREATE INDEX idx_created_at ON orders(created_at);
“`

4) 需要分组的字段

如果字段经常在 GROUP BY 子句中出现，索引可以加速分组操作。
– 示例：统计每个城市的用户数量：

“`sql
SELECT city, COUNT(*) FROM users GROUP BY city;
“`
在 `city` 字段上创建索引可以优化查询性能。

5) 范围查询的字段

字段经常用于范围查询（如 BETWEEN、>=），创建索引能够加速范围扫描。
– 示例：按年龄范围查询用户：

“`sql
SELECT * FROM users WHERE age BETWEEN 20 AND 30;
“`
在 `age` 字段上创建索引：
“`sql
CREATE INDEX idx_age ON users(age);
“`

6) 大表中的字段

对于小表，全表扫描的效率较高，通常不需要创建索引；而在数据量较大的表中，索引可以显著提升查询效率。

2. 不适合创建索引的字段

值重复率高的字段：如性别、是否激活（is_active），这些字段的区分度低，索引无法显著减少扫描的数据量。
频繁更新的字段：如用户的登录时间、账户余额，频繁更新会导致索引重建，增加维护开销。
很少查询的字段：如果字段不用于查询条件或排序，创建索引会徒增存储成本。
字段长度过长：如包含大文本（TEXT 或 BLOB）的字段，不建议直接创建索引，可使用前缀索引优化。

3. 索引设计的常见策略

1) 单列索引 vs 复合索引

单列索引：在单个字段上创建索引。
复合索引：在多个字段上创建索引，用于多条件查询（WHERE col1 = ? AND col2 = ?）。
- 示例：查询订单时按用户 ID 和创建时间：
```
SELECT * FROM orders WHERE user_id = 123 AND created_at > '2023-01-01';
```
  创建复合索引：
```
CREATE INDEX idx_user_created ON orders(user_id, created_at);
```

2) 覆盖索引

覆盖索引是指查询的所有字段都可以通过索引直接获取，避免访问数据表，提高性能。
– 示例：

“`sql
SELECT user_id, email FROM users WHERE email = 'user@example.com';
“`
如果在 `email` 字段上创建了索引，查询只需扫描索引，性能更高。

3) 前缀索引

对于长字符串字段（如 URL 或电子邮件），可以创建前缀索引，减少存储开销。
– 示例：只索引前 10 个字符：

“`sql
CREATE INDEX idx_email_prefix ON users(email(10));
“`

4. 示例分析

假设有一张用户表 users：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100),
    age INT,
    city VARCHAR(50),
    created_at DATETIME
);

适合索引的字段：
- email：经常用作查询条件，区分度高。
- age：用于范围查询。
- created_at：用于排序。
不适合索引的字段：
- name：如果经常更新，可能导致索引维护成本过高。
- city：值可能重复率较高，效果有限。

总结

适合创建索引的字段通常需要满足查询频繁、区分度高或涉及排序和分组等特点，但索引设计需要根据实际业务场景权衡查询性能与存储和维护成本。合理使用单列索引、复合索引和覆盖索引，可以大幅提升数据库的查询效率。