MySQL索引设计原则

1. 选择合适的列

• 频繁作为查询条件的列建立索引
• 参与ORDER BY、GROUP BY或者DISTINCT的列建立索引
• 在多表连接的情况下，对作为连接条件的列建立索引

2. 优化多列索引

• 复合索引：对于多个列组合查询的情况，考虑使用复合索引而非单个列索引，减少表的扫描次数

• 最左前缀匹配：

  • 举例说明：

  • 假设我们有一个 users 表，包含以下列：country state city age

  • 我们经常会执行以下几种查询：

  1. 按国家查询：SELECT * FROM users WHERE country = 'USA';
  2. 按国家和州查询：SELECT * FROM users WHERE country = 'USA' AND state = 'California';
  3. 按国家、州和城市查询：SELECT * FROM users WHERE country = 'USA' AND state = 'California' AND city = 'Los Angeles';
  4. 仅按州查询：SELECT * FROM users WHERE state = 'California';

  • 创建复合索引：根据最左前缀匹配原则，如果我们频繁按照 country、state 和 city 来查询，那么我们可以创建一个复合索引，列的顺序为 (country, state, city)：CREATE INDEX idx_location ON users (country, state, city);

  • 复合索引的利用情况：

  1. 按国家查询

     • 使用索引：country 是索引的第一个列，符合最左前缀匹配。

     • 效果：索引被有效利用，查询速度加快。

  2. 按国家和州查询

     • 使用索引：country 和 state 都在索引的最左边，依次匹配。

     • 效果：索引被有效利用，查询速度加快。

  3. 按国家、州和城市查询**

     • 使用索引：country、state 和 city 完全匹配索引顺序。

     • 效果：索引被充分利用，查询速度最快。

  4. 仅按州查询

     • 使用索引：state 不是索引的最左边列，跳过了 country。

     • 效果：MySQL 可能无法有效利用这个复合索引，查询性能提升有限，可能需要进行全表扫描或使用其他可用的索引。

3. 控制索引的数量和类型

• 避免过多索引：索引过多会影响写操作的效率
• 适用索引的类型：例如B-tree索引适用于大多数情况，Full-text索引用于全文搜索，Hash索引用于等值查询(如内存表)

4. 考虑数据分布和选择性

• 高选择性的列(值大多数不同)

5. 使用覆盖索引

• 当索引包含查询所需的所有列时，称为覆盖索引，能够有效减少读取数据也的次数