数据库索引那些事儿，怎么折腾才能让查询快起来，性能提升真不是吹的

说到数据库索引,咱们可以把它想象成一本超级厚的字典，这本字典就是你的数据库表，里面存了海量的数据，如果你想在这本厚字典里找一个字，数据库”的“库”字，你会怎么做？你肯定不会从第一页开始，一页一页地翻吧？那得翻到猴年马月去，聪明人的做法是直接去查字典的部首目录或者拼音索引，先找到“库”字大概在哪一页，然后直接翻到那附近，很快就能找到，这个“部首目录”或者“拼音索引”，就是数据库里的索引。

索引的本质就是一个为了加快数据查询速度而创建的辅助数据结构，它就像一本书的目录，通过建立一个“关键字-位置”的映射，让数据库系统能够不扫描整个表，而是像查字典一样，快速地定位到你需要的数据所在的位置，这个“扫描整个表”的操作，在数据库里有个专业名词叫“全表扫描”，这是性能杀手，尤其是在表里有几百万、几千万甚至上亿条数据的时候，全表扫描一次可能会慢得让你怀疑人生。

索引具体是怎么让查询快起来的呢？咱们举个例子，假设你有一个用户表，里面有1000万条用户记录，其中一个字段是用户的身份证号，现在老板让你查身份证号是“110101199001015432”的用户信息，如果没有给身份证号这个字段建立索引，数据库就只能老老实实地从第一条记录开始，逐条比对身份证号，直到找到匹配的那一条，最坏的情况是，它需要扫描完1000万条记录才能找到，或者确认找不到，这个过程就像在乱糟糟的、没排序的仓库里找一个特定的螺丝钉。

但如果你提前给身份证号这个字段创建了索引,情况就完全不同了，数据库的索引，最常见的一种叫B+树索引，它会把这个身份证号字段的值，按照一定的顺序（比如从小到大）排列好，并记录下每条数据对应的物理存储位置，当你要查询时，数据库就会先去这个排好序的索引结构里进行查找，因为索引是有序的，所以它可以用一种非常高效的算法（比如二分查找）快速定位到“110101199001015432”这个值，然后根据索引里记录的位置信息，直接去硬盘上把那条数据捞出来，这个过程可能只需要读取很少的几次磁盘（比如3到4次），相比于1000万次的全表扫描，速度的提升是天壤之别，这就像你先查了一个按照身份证号排序的目录，然后直接走到了仓库里第A区第B架第C层，一把就拿到了那个螺丝钉。

索引好处这么多,是不是给每个字段都建上索引就高枕无忧了呢？绝对不是！索引可不是免费的午餐，它是有代价的，主要体现在两个方面：

第一,占用存储空间，索引本身也是一种数据，它需要占用额外的磁盘空间来存储，你建的索引越多，占用的空间就越大，如果一张表有十个字段，你给每个字段都单独建一个索引，那索引占用的空间可能比原始数据表本身还要大。

第二,降低数据写入和修改的速度，这是一个非常关键的副作用，因为索引需要保持和原始数据的一致性，当你往表里插入一条新记录、或者删除、修改一条已有记录时，数据库不仅要处理原始数据，还得同时去更新所有相关的索引，以保证索引里的信息是正确的，想象一下，你每在字典里加一个新字，不仅要把它塞进正文里，还得在部首目录和拼音索引里都加上这个字的条目，如果索引很多，这个维护索引的成本就会变得很高，会显著拖慢插入、更新和删除操作的速度，对于需要频繁进行写操作（增删改）的表，创建索引需要特别谨慎。

到底该怎么折腾,才能让索引真正帮上忙，而不是帮倒忙呢？这里有几个非常实用的原则：

为查询条件中的字段建索引，而不是输出结果的字段。 这是最核心的一点，比如你的SQL语句是 SELECT name, age FROM users WHERE id_card = 'xxx'，这里查询条件是 WHERE id_card = 'xxx'，所以你应该给 id_card 字段建索引，而 SELECT 后面的 name 和 age 是你要查出来的结果，给它们建索引通常没用。

考虑创建复合索引（也叫组合索引）。 如果经常有查询同时用到多个字段作为条件，WHERE city='北京' AND age > 30，那么单独为 city 建一个索引，再单独为 age 建一个索引，效果可能不如直接创建一个 (city, age) 的复合索引好，复合索引的排序规则是“先左后右”，它首先按照第一个字段（city）排序，在city相同的情况下，再按第二个字段（age）排序，对于上面的查询，数据库可以高效地利用这个复合索引先定位到所有“北京”的用户，然后再在这些用户里快速找到年龄大于30的，但是要注意最左前缀原则，如果你的查询条件里没有包含复合索引最左边的字段（比如只查 WHERE age > 30），那么这个复合索引很可能就用不上了。

选择性高的字段更适合建索引。 什么是选择性高？就是这个字段的值重复度很低，几乎每条记录都不同，比如身份证号、手机号、用户名，这些字段的值唯一性很强，建索引的效果就非常好，反之，像“性别”这种字段，只有“男”、“女”等少数几个值，选择性就很低，如果你给性别建索引，查询时可能依然会返回大量的数据（比如一半的表记录），数据库优化器可能觉得走索引还不如直接全表扫描快，这样索引就白建了。

避免在频繁更新的字段上建过多索引。 正如前面所说，索引会影响写性能，如果一个字段经常被修改，那么维护它的索引成本就会很高，需要权衡查询需求和写入性能。

定期维护索引。 数据库里的数据经过大量的增删改之后，索引可能会产生碎片，就像硬盘碎片一样，导致索引的效率下降，大多数数据库系统都提供了优化或者重建索引的命令，定期执行一下可以保持索引的最佳性能。

索引是数据库性能优化中最强大、最立竿见影的工具之一，但它是一把双刃剑，用对了，查询速度飞起，性能提升真不是吹的；用错了，反而会拖慢整个系统，关键在于理解其工作原理，并根据你实际的应用场景（是读多写少还是写多读少？常用的查询条件是什么？）来做出合理的规划和设计，最好的办法就是，在重要的查询上，通过数据库提供的查询执行计划分析工具，看看你的索引是否真的被用上了，效果如何，然后不断地进行调整和优化。