Alibaba开发规范_MySQL索引规约：最佳实践与常见陷阱

引言

索引是数据库性能优化的关键，合理的索引设计可以显著提高查询效率，而不当的索引使用则可能导致性能下降甚至数据不一致。接下来我们将深入探讨MySQL索引规约的最佳实践，并通过反例和正例代码来更好地理解和应用这些规则。

1. 唯一索引

规则

• 业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。

解释

唯一索引可以防止脏数据的产生，并提高查询效率。即使在高并发环境下，唯一索引也能确保数据的唯一性。

代码示例

正例

CREATE TABLE user (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL COMMENT '用户名',
    UNIQUE KEY uk_username (username)
);

反例

CREATE TABLE user (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL COMMENT '用户名'
);

在反例中，未对username字段建立唯一索引，可能导致脏数据。

2. 表关联与索引

规则

• 超过三个表禁止join。需要join的字段，数据类型必须绝对一致；多表关联查询时，保证被关联的字段需要有索引。

解释

过多的表关联会影响查询性能，而数据类型不一致可能导致查询错误。

代码示例

正例

SELECT a.*, b.*
FROM table_a a
JOIN table_b b ON a.id = b.id
WHERE a.id = 1;

反例

SELECT a.*, b.*, c.*, d.*
FROM table_a a
JOIN table_b b ON a.id = b.id
JOIN table_c c ON b.id = c.id
JOIN table_d d ON c.id = d.id
WHERE a.id = 1;

在反例中，超过三个表进行join，影响查询性能。

3. 索引长度

规则

• 在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引。

解释

指定索引长度可以提高索引的区分度和查询效率。

代码示例

正例

CREATE TABLE user (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL COMMENT '用户名',
    KEY idx_username (username(20))
);

反例

CREATE TABLE user (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL COMMENT '用户名',
    KEY idx_username (username)
);

在反例中，未指定索引长度，可能导致索引效率低下。

4. 模糊查询

规则

• 页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

解释

左模糊和全模糊查询会导致索引失效，影响查询性能。

代码示例

正例

SELECT * FROM user WHERE username LIKE 'john%';

反例

SELECT * FROM user WHERE username LIKE '%john%';

在反例中，使用全模糊查询，导致索引失效。

5. 覆盖索引

规则

• 利用覆盖索引来进行查询操作，避免回表。

解释

覆盖索引可以减少回表操作，提高查询效率。

代码示例

正例

SELECT id, username FROM user WHERE username = 'john';

反例

SELECT * FROM user WHERE username = 'john';

在反例中，未使用覆盖索引，导致回表操作。

6. 延迟关联

规则

• 利用延迟关联或者子查询优化超多分页场景。

解释

延迟关联可以减少分页查询的数据量，提高查询效率。

代码示例

正例

SELECT a.*
FROM table_a a
JOIN (SELECT id FROM table_a WHERE condition LIMIT 100000, 20) b
ON a.id = b.id;

反例

SELECT * FROM table_a WHERE condition LIMIT 100000, 20;

在反例中，直接使用LIMIT进行分页查询，效率低下。

7. SQL性能优化目标

规则

• SQL性能优化的目标：至少要达到range级别，要求是ref级别，如果可以是consts最好。

解释

consts表示单表中最多只有一个匹配行，ref表示使用普通索引，range表示对索引进行范围检索。

代码示例

正例

EXPLAIN SELECT * FROM user WHERE id = 1;
-- type: const

反例

EXPLAIN SELECT * FROM user WHERE username LIKE '%john%';
-- type: ALL

在反例中，查询类型为ALL，表示全表扫描，性能较差。

8. 组合索引的顺序

规则

• 建组合索引的时候，区分度最高的在最左边。

解释

区分度高的字段放在组合索引的最左边，可以提高索引的查询效率。

代码示例

正例

CREATE INDEX idx_age_gender ON user (age, gender);

反例

CREATE INDEX idx_gender_age ON user (gender, age);

在反例中，区分度低的gender字段放在组合索引的最左边，影响查询效率。

9. 防止隐式转换

规则

• 防止因字段类型不同造成的隐式转换，导致索引失效。

解释

字段类型不一致可能导致隐式转换，使索引失效。

代码示例

正例

SELECT * FROM user WHERE username = 'john';

反例

SELECT * FROM user WHERE username = 123;

在反例中，username字段为字符串类型，而查询条件为数字类型，导致隐式转换，索引失效。

10. 创建索引的误解

规则

• 创建索引时避免有如下极端误解：
  1） 宁滥勿缺。认为一个查询就需要建一个索引。
  2） 宁缺勿滥。认为索引会消耗空间、严重拖慢记录的更新以及行的新增速度。
  3） 抵制惟一索引。认为业务的惟一性一律需要在应用层通过“先查后插”方式解决。

解释

合理的索引设计可以提高查询效率，但过多的索引会增加存储和维护成本。

代码示例

正例

CREATE INDEX idx_username ON user (username);

反例

CREATE INDEX idx_username ON user (username);
CREATE INDEX idx_email ON user (email);
CREATE INDEX idx_phone ON user (phone);

在反例中，创建了过多的索引，增加了存储和维护成本。

小结

通过遵循索引规约的最佳实践，开发者可以编写出高效、易维护的数据库代码。