在数据库性能优化中，索引是提升查询效率最直接有效的工具之一。然而，不恰当的索引使用反而会成为性能瓶颈。掌握索引创建的核心原则，是从根本上优化慢SQL查询、保障系统高效稳定运行的关键。
本文将系统性地解析创建与使用索引时必须遵循的八大核心原则，并结合实际场景帮助你深入理解。
1. 全值匹配原则
这是对数据库索引利用率最高、性能最好的查询方式。当WHERE条件中包含了联合索引的所有字段并进行等值匹配时，数据库可以直接、精确地定位到所需数据行，避免了不必要的扫描，查询效率最高。
2. 最左前缀原则
这是联合索引使用的黄金法则。可以形象地理解为：“带头大哥不能丢，中间兄弟不能断”。
- 带头大哥不能丢：查询条件中必须包含联合索引最左边的列，否则索引将无法被使用。
- 中间兄弟不能断：如果跳过了联合索引中的某个中间列，那么其后续列的索引也将失效，因为索引中的数据是按照前列的值进行排序的。
3. 不在索引列上计算或使用函数
在索引列上进行计算（如 id + 1 = 10）或使用函数（如 YEAR(create_time)=2023）会改变列的原始值，从而破坏索引的有序性，导致查询优化器无法有效利用索引，转而进行全表扫描。
4. 范围条件右侧索引列失效
当查询中使用范围操作符（如 >, <, BETWEEN）时，该范围条件之后的索引列将失效。这是因为范围查询会使该列之后字段的数据排列变得无序，索引无法继续生效。
5. 尽量使用覆盖索引
使用 SELECT * 是引发“回表”问题的常见原因。普通二级索引只存储了索引列的值和主键ID，如果SELECT的字段不在索引中，数据库就必须根据主键ID回到主键索引树（聚簇索引）中取出完整行数据。如果匹配1000行，就需要回表1000次！因此，尽量让查询的字段被索引覆盖，可以显著减少I/O，性能提升可达50%以上。
6. Like查询，百分号放最右边
数据在索引中是按首字母顺序存储的。LIKE ‘张%’可以利用索引进行前缀匹配，而LIKE ‘%张’或LIKE ‘%张%’则会导致前面的数据无序，索引失效。
7. 警惕不等、空值与OR
不等（!= 或 <>）、IS NULL/IS NOT NULL以及部分OR条件，很容易导致查询需要检查的数据范围过大。此时查询优化器通过成本估算，可能会认为全表扫描比使用索引更高效，从而放弃使用索引。
8. 字符串类型务必加引号
这是一个容易被忽略的细节。当对VARCHAR等字符串类型的索引列进行查询时，如果不加单引号，例如 where name = 100，MySQL会尝试进行隐式的类型转换，这等同于在列上使用了函数，最终导致索引失效。
为了方便记忆，可以将以上原则总结为以下口诀：
全值匹配我最爱，最左前缀要遵守；
索引列上少计算，范围之后全失效；
Like百分写最右，覆盖索引不写星；
不等空值还有OR，字符串型加引号。
这些原则看似基础，但在高并发、大数据量的实际生产环境中，正是对这些细节的严格把控，支撑起了系统高效稳定的查询性能。深入理解并应用它们，是每一位开发者进行有效的SQL优化的必备技能。 | 
发表于 5 天前
|
查看: 16|
回复: 0
