数据库索引到底怎么用才对，别再乱建索引浪费资源了

主要综合了数据库领域的普遍实践原则,常见于如《高性能MySQL》、数据库官方文档（如MySQL、PostgreSQL）、以及像“掘金”、“InfoQ”等技术社区中资深开发者的经验分享文章）

数据库索引就像一本书的目录,想象一下，你想在一本厚厚的百科全书里找“光合作用”这个词的解释，如果没有目录，你只能一页一页地翻，这就是数据库的“全表扫描”，速度极慢，而有了目录，你直接翻到“G”字母开头的部分，很快就能定位到准确页面，索引就是干这个用的。

但问题来了,是不是给书里的每个词都做一个目录条目就好了呢？显然不是，那样目录本身就会比书还厚，而且维护这个巨型目录的代价太高，反而让你查东西变慢了，这就是“乱建索引浪费资源”的根源，下面我们就来详细说说，怎么用索引才算对。

什么时候应该建索引？

索引不是越多越好,它的核心原则是：只为那些频繁作为查询条件的字段创建索引。

1. 高频查询的WHERE条件字段：这是最经典的场景，比如你的用户表，几乎每个操作都要根据用户ID来查信息，那给用户ID建索引是天经地义的，再比如，电商平台经常根据商品分类来筛选商品，那分类ID字段也值得一个索引。
2. 需要排序（ORDER BY）的字段：如果经常需要按某个字段排序，比如按“创建时间”倒序显示文章列表，那么给创建时间字段建索引可以避免数据库在每次查询时都进行耗时的临时排序操作，数据库可以直接按索引的顺序读取数据，又快又省力。
3. 连接表（JOIN）时用的字段：比如订单表需要关联用户表来获取用户名，连接条件通常是用户ID，如果在用户表的用户ID和订单表的用户ID上都建立了索引，这个连接操作的速度会大大提升。
4. 高选择性的字段：“选择性”指的是字段值的唯一程度，像身份证号、手机号这种几乎唯一的字段，选择性非常高，通过索引能快速定位到唯一一条记录，效果拔群，相反，像“性别”这种只有两三种值的字段，选择性极低，建索引意义不大，因为数据库很可能还是需要扫描一半的数据。

什么时候不应该建索引或少建索引？

这才是避免浪费资源的关键,很多人栽在这里。

1. 频繁更新的表，索引要谨慎：索引不是免费的，它需要维护，每次你对表进行增（INSERT）、删（DELETE）、改（UPDATE）操作时，数据库不仅要动数据，还要去更新所有相关的索引结构，如果一个表经常被修改，你建的每一个索引都会拖慢写入速度，写操作非常频繁的表，索引数量要严格控制。
2. 小表不需要索引：如果一张表只有几百条甚至几十条记录，数据库可能直接全表扫描比通过索引去查还要快，因为读索引本身也有开销，对于小表来说，这个开销可能比收益还大。
3. 选择性差的字段不要建索引：再次强调，像“状态”、“类型”这种只有几个固定值的字段，你建了索引，数据库用它查，还是会返回大量数据，效果不明显，反而占了空间，拖慢了写入。
4. 很少被用于查询的字段：有些字段可能只是用来存储，一年也查不了几次，那就完全没必要为它建索引。
5. 过长的文本字段（如TEXT）要小心：给一个非常长的字符串字段建索引，索引文件会变得巨大，查询效率也会受影响，通常的做法是只对字段的前缀（比如前20个字符）建立索引，这在很多数据库中支持为“前缀索引”。

一些高级但非常重要的技巧

1. 联合索引（复合索引）是神器，但顺序是关键：联合索引指的是同时为多个字段建立一个索引，城市, 年龄），这个索引的优势在于它不仅是“目录的目录”（先按城市找，再在城市内按年龄排序），而且有时可以“覆盖查询”。

   • 最左前缀原则：这是联合索引的灵魂，索引（A, B, C）生效的前提是查询条件必须包含A，它可以用于A=？、A=？ AND B=？、A=？ AND B=？ AND C=？这几种查询，但如果你的查询条件是B=？，那么这个联合索引就完全用不上了，建立联合索引时，一定要把最常用、选择性最高的字段放在左边。
   • 覆盖索引：如果一个查询所需要的所有数据，刚好都在索引的字段里，数据库就不需要再回表去查数据行了，直接在索引里就能拿到结果，速度极快，比如你有一个（用户ID, 用户名）的索引，查询语句是SELECT 用户名 FROM 用户表 WHERE 用户ID = 123，这就是一个完美的覆盖索引查询。

2. 学会查看执行计划：不要凭感觉建索引！现代数据库都提供了“执行计划”（EXPLAIN）功能，在你写的SQL语句前加上EXPLAIN关键字（如EXPLAIN SELECT * FROM ...），数据库就会告诉你它打算怎么执行这条查询，会不会用索引，用了哪个索引，通过分析执行计划，你可以验证你的索引是否有效，这是优化索引最直接有效的方法。

别再乱建索引了,把它当成一种珍贵的资源，建之前先问自己几个问题：

• 这个字段真的经常被用来查询吗？
• 这个表是不是更新特别频繁？
• 这个字段的值是不是太重复了？
• 我能不能用联合索引来“一石二鸟”？
• 我有没有用EXPLAIN命令验证过？

正确的索引策略是数据库性能的基石,它需要在查询速度和写入开销之间做一个精细的权衡，花点时间分析你的业务查询模式，有针对性地创建和维护索引，才能让数据库真正高效地为你服务，而不是被不必要的索引拖垮。