MySQL索引设计原则

索引设计原则2024/7/17

建议索引

情况		索引设计
WHERE		对于经常用于 WHERE 子句中的列，考虑创建普通索引。例如：如果经常使用 WHERE username = 'some_value'，则应在 username 列上创建索引。
GROUP BY		如果 GROUP BY 子句中的列没有索引，MySQL 可能会创建临时表来执行分组操作。为 GROUP BY 中的列创建普通索引可以提升查询效率。例如：GROUP BY category_id，则应在 category_id 列上创建索引。
ORDER BY		如果经常使用 ORDER BY 子句中的列进行排序，可以考虑在相关列上创建索引。MySQL 可以使用这些索引来避免对整个结果集进行排序操作。例如：ORDER BY date_created DESC，则应在 date_created 列上创建降序索引。
唯一字段 或组合字段		对于需要唯一性约束或者多列组合约束的字段，使用唯一索引或者复合唯一索引。例如：如果需要确保 username 列的唯一性，则在 username 列上创建唯一索引。如果需要确保 (username, email) 组合的唯一性，则创建复合唯一索引。
WHERE + GROUP BY		如果 WHERE 子句和 GROUP BY 子句中的列不同，分别创建普通索引。例如：WHERE status = ‘active’ AND GROUP BY category_id，则在 status 和 category_id 列上分别创建索引。
WHERE + ORDER BY		如果 WHERE 子句和 ORDER BY 子句中的列不同，分别创建普通索引。例如：WHERE date_created > ‘2023-01-01’ ORDER BY priority DESC，则在 date_created 和 priority 列上分别创建索引。
GROUP BY + ORDER BY		如果 GROUP BY 和 ORDER BY 子句中的列不同，分别创建普通索引。例如：GROUP BY category_id ORDER BY avg_rating DESC，则在 category_id 和 avg_rating 列上分别创建索引或者创建联合索引。
DISTINCT		对于使用DISTINCT的查询，索引的设计应侧重于提高查询效率，尤其是当查询涉及大量数据且需要快速去重时。建议为查询中涉及的列（尤其是那些用于过滤或排序的列）创建索引。如果DISTINCT查询经常与某个或某些列一起使用，考虑在这些列上创建单列或多列索引。然而，需要注意的是，索引虽然可以加速查询，但也会增加写入操作的开销和存储空间的消耗。因此，在设计索引时，需要权衡查询性能与写入性能、存储空间之间的关系。

对数据按照某个条件进行查询后再进行UPDATE 或DELETE的操作，如果对WHERE 字段创建了索引，就能大幅提升效率。原理是因为我们需要先根据WHERE 条件列检索出来这条记录，然后再对它进行更新或删除。如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。

操作	没有索引时的运行情况	创建索引后的运行情况
UPDATE	执行 SQL 语句: UPDATE student_info SET student_id = 10002 WHERE name = '462eed7ac6e791292a79' 运行结果为 Affected rows: 1，运行时间为 0.578s。	执行 SQL 语句: UPDATE student_info SET student_id = 10001 WHERE name = '462eed7ac6e791292a79' 运行结果为 Affected rows: 1，运行时间仅为 0.001s。
DELETE	执行 SQL 语句: DELETE FROM student_info WHERE name = '462eed7ac6e791292a79' 运行结果为 Affected rows: 1，运行时间为 0.627s。	执行 SQL 语句: DELETE FROM student_info WHERE name = '462eed7ac6e791292a79' 运行时间为 0.03s。

联表情况

注意事项	描述	示例
连接表的数量	尽量不要超过3张，每增加一张表都会增加嵌套循环，严重影响查询效率	-
对WHERE条件创建索引	WHERE条件用于数据过滤，对查询性能至关重要	-
对连接字段创建索引	连接字段必须在多张表中类型一致，以提高连接操作的效率	course_id在student_info表和course表中都为int(11)类型
索引效果示例	仅对student_id创建索引时，查询耗时长	SQL语句: SELECT course_id, name, student_info.student_id, course_name FROM student_info JOIN course ON student_info.course_id = course.course_id WHERE name = '462eed7ac6e791292a79' 运行结果: 1条数据，运行时间0.189s
索引优化后效果	对name字段也创建索引后，查询速度显著提升	对name创建索引后，再次执行上述SQL语句，运行时间缩短至0.002s

前缀索引

序号	内容描述	示例/说明
1	B+树索引中记录需存储完整字符串，费时且占用空间大	字符串越长，索引占用的存储空间越大，比较时间也越长
2	前缀索引简介	截取字段前面一部分内容建立索引，以节约空间和减少比较时间
3	前缀索引的优势	1. 节约索引存储空间 2. 减少字符串比较时间 3. 大体解决排序问题
4	前缀索引应用场景	适用于TEXT、BLOB等类型字段的全文检索优化
5	创建商户表及前缀索引示例	create table shop(address varchar(120) not null);<br>alter table shop add index(address(10));
6	截取长度的选择性问题	截取得多则节省空间效果不明显，截取得少则选择性降低
7	计算字段选择性的方法	select count(distinct address) / count(*) from shop; 获取全表选择性
8	通过不同长度计算选择性的公式	count(distinct left(列名，索引长度)) / count(*) from 表名;
9	注意事项	1. 评估索引长度对选择性的影响 2. 平衡索引存储空间与查询效率 3. 根据实际数据分布调整索引策略

索引选择

• 合适创建

内容描述	示例/说明
使用列的类型小的创建索引	选择占用空间小的数据类型作为索引列，如INT比VARCHAR更节省空间
使用字符串前缀创建索引	对于较长的字符串字段，可以只索引其前缀部分以减少索引大小和比较时间
区分度高（散列性高）的列适合作为索引	选择值分布均匀、区分度高的列作为索引，可以提高查询效率
使用最频繁的列放到联合索引的左侧	在创建联合索引时，将查询条件中最常用的列放在索引的最前面，以优化查询性能
在多个字段都要创建索引的情况下，联合索引优于单值索引（在适用时）	当多个字段经常一起出现在查询条件中时，考虑创建联合索引以减少索引数量和提高查询效率

• 不合适

情况描述	扩展说明
在WHERE子句中使用不到的字段，不要设置索引	如果某个字段在查询条件中从未被使用，那么为它创建索引是浪费的，因为索引的主要目的是加速查询条件中的字段查找。
数据量小的表最好不要使用索引	对于数据量非常小的表（例如，只有几十行或几百行数据），索引带来的性能提升可能非常有限，甚至可能因为索引的维护开销（如插入、更新、删除操作时需要更新索引）而降低性能。
有大量重复数据的列上不要建立索引	如果某个列上的数据重复率很高，那么该列的索引效果会大打折扣。因为索引的目的是为了快速定位到不同的数据行，如果大部分数据都是相同的，那么索引的区分度就很低，查询时仍需扫描大量数据。
避免对经常更新的表创建过多的索引	对于经常进行插入、更新、删除操作的表，过多的索引会显著影响这些操作的性能。因为每次数据变动时，所有相关的索引都需要被更新，这会增加额外的I/O开销和CPU消耗。
不建议用无序的值（如UUID）作为索引	无序的值（如UUID）作为索引时，会导致索引页面频繁分裂，影响索引的紧凑性和查询效率。此外，UUID的随机性也会使得索引在物理存储上分布不均，增加查询时的磁盘I/O。
删除不再使用或者很少使用的索引	定期审查数据库中的索引，删除那些不再被查询使用或很少被使用的索引。这些“僵尸”索引不仅占用存储空间，还会在数据变动时增加不必要的维护开销。
不要定义冗余或重复的索引	确保索引的唯一性和必要性。避免创建重复覆盖已有索引功能的索引，或者索引中包含的列顺序与已有索引相同但包含额外列的情况。这样的索引不仅浪费资源，还可能影响数据库的性能。
对频繁进行范围查询的列，考虑索引的选择性和效率	对于频繁进行范围查询（如BETWEEN、>、<等）的列，索引的选择性和效率尤为重要。如果列中的数据分布不均，或者范围查询的条件过于宽泛，可能导致索引效果不佳，此时需要谨慎考虑是否创建索引以及如何设计索引。
索引并不是万能的，需要根据实际查询需求和数据分布来设计	最后，需要强调的是，索引并不是解决所有性能问题的万能药。在设计索引时，需要充分了解查询需求、数据分布和数据库的工作负载，综合考虑后再做出决策。