MySQL索引设计原则

MySQL索引设计原则 1. 选择合适的列 频繁作为查询条件的列建立索引 参与ORDER BY、GROUP BY或者DISTINCT的列建立索引 在多表连接的情况下，对作为连接条件的列建立索引 2. 优化多列索引 复合索引：对于多个列组合查询的情况，考虑使用复合索引而非单个列索引，减少表的扫描次数 最左前缀匹配： 举例说明： 假设我们有一个 users 表，包含以下列：country state city age 我们经常会执行以下几种查询： 按国家查询：SELECT * FROM users WHERE country = 'USA'; 按国家和州查询：SELECT * FROM users WHERE country = 'USA' AND state = 'California'; 按国家、州和城市查询：SELECT * FROM users WHERE country = 'USA' AND state = 'California' AND city = 'Los Angeles'; 仅按州查询：SELECT * FROM users WHERE state = 'California'; 创建复合索引：根据最左前缀匹配原则，如果我们频繁按照 country、state 和 city 来查询，那么我们可以创建一个复合索引，列的顺序为 (country, state, city)：CREATE INDEX idx_location ON users (country, state, city); 复合索引的利用情况： 按国家查询 使用索引：country 是索引的第一个列，符合最左前缀匹配。 效果：索引被有效利用，查询速度加快。 按国家和州查询 使用索引：country 和 state 都在索引的最左边，依次匹配。 效果：索引被有效利用，查询速度加快。 按国家、州和城市查询** 使用索引：country、state 和 city 完全匹配索引顺序。 效果：索引被充分利用，查询速度最快。 仅按州查询 使用索引：state 不是索引的最左边列，跳过了 country。 效果：MySQL 可能无法有效利用这个复合索引，查询性能提升有限，可能需要进行全表扫描或使用其他可用的索引。 3. 控制索引的数量和类型 避免过多索引：索引过多会影响写操作的效率 适用索引的类型：例如B-tree索引适用于大多数情况，Full-text索引用于全文搜索，Hash索引用于等值查询(如内存表) 4. 考虑数据分布和选择性 高选择性的列(值大多数不同) 5. 使用覆盖索引 当索引包含查询所需的所有列时，称为覆盖索引，能够有效减少读取数据也的次数