关系型数据库索引初步使用（搞不好性能提升百倍呦）

发表于 更新于

0X00 前言

不论多初级的后端程序员，只要认可自己是“后端程序员”那最起码也是听过“数据库索引”这个东西的，应该也直到这东西能让数据库变快。但是具体“数据库索引”是个什么东西，怎么用，为什么能让数据库变快可能就不一定清楚了。

这篇博客仅仅是简述了“数据库索引”的最基础内容，不涉及内部原理（其实我也还没搞懂）。阅读了本篇博客可以（也许）搞清楚怎么给一张表添加索引，索引加在哪儿，以及为什么不能给所有字段加索引。

0X01 一个业务场景

假设我们搞了一个电商平台“并夕夕”，现在有超多用户大概几百万，很多人都在我们系统里买了东西，大概下了1000W单。那么我们用来存储订单的表就有大概1000W条数据了，那么我们这个没有进行过任何优化的数据库就已经顶不住了，我们每次在后台查看订单列表的时候一个查询就要快20s，显然是一个接受不了的速度了。

那么我们来看一下这个列表都干了什么：“根据 订单状态（下单待支付/已支付/配送中/已收货/退货中/已完成），订单类型（直接购买/拼单购买/分享白拿）等状态筛选，并取出前20条展示出来”。后台SQL是这个样子的SELECT * FROM bxx_order WHERE order_status = xxx AND order_type = xxx ORDER BY -id LIMIT 20，我们可以看到真正会影响性能的主要就在WHERE子句里的查询条件。接下来我们给这两个字段分别加上索引，查询效率就会高非常非常多。

我在公司的一张400W的表中为“状态”字段添加索引后对比未添加索引的时候快了不止100倍。

下面我们来看一下索引是怎么使用的

0X02 什么是索引&为什么用索引会加快查询

网上常见的解释是将“书的目录”与“数据库索引”关联理解，其实是没有问题的，但是第一次理解这个概念光直到这个也没什么用啊，还是不太能明白呢。我们来把一本书作为一张数据库表好了，其中有如下字段一级标题，二级标题，小节，正文。比如一级标题就是“1. 引论”，二级标题是“1.1 什么是操作系统”，小节是“1.1.1 作为扩展机器的操作系统”，正文就是“balabalabalabala”，那么一般的书籍都会对一二级标题建立索引（目录）。

我们想想这本500多页的《现代操作系统》如果没有索引，那我想看“多核处理器上的虚拟机”这一部分要怎么找呢，就只有从第一页开始翻书一直到找到为止。这就是数据库不添加索引时候的查询方式，逐条查看。

现在我们给标题添加了索引，也就多出了一部分名为“目录”的内容。但是这个目录只有7页，我可以在这7页目录里找到我需要的内容在500页中的位置然后直接翻到对应的那一页。数据库也是这样的，假设我们给订单状态添加了索引，数据库就会知道状态为“待支付”的订单在数据库的什么位置，状态为“已完成”的在什么位置。也就不用每次都一条条的查看这1000W条订单数据了。

0X03 如何添加索引

那说了这么多，怎么才能给数据库表添加上索引呢？如果是在建表阶段就考虑到了索引，本来的建表SQL是这样的

1
2
3
4
5
6
7
CREATE TABLE bxx_order(
id INT NOT NULL,
order_status VARCHAR(11) NOT NULL,
order_type VARCHAR(11) NOT NULL,

PRIMARY KEY (id)
);

那么只需要在最后加上索引指定就行了

1
2
3
4
5
6
7
8
9
CREATE TABLE bxx_order(
id INT NOT NULL,
order_status VARCHAR(11) NOT NULL,
order_type VARCHAR(11) NOT NULL,

PRIMARY KEY (id),
-- 上面没变，只是加了最后的逗号
INDEX (order_status)
);

那如果表已经存在了呢？也只需要ALERT TABLE bxx_order ADD INDEX order_status就可以将已经存在的表添加上索引。

注意在生产环境上添加索引时可能会锁表从而导致业务收到影响。所以请务必调查清楚自己使用的数据库版本和当前的业务，结合数据库版本特性和业务情况酌情在生产环境操作

注意在生产环境上添加索引时可能会锁表从而导致业务收到影响。所以请务必调查清楚自己使用的数据库版本和当前的业务，结合数据库版本特性和业务情况酌情在生产环境操作

注意在生产环境上添加索引时可能会锁表从而导致业务收到影响。所以请务必调查清楚自己使用的数据库版本和当前的业务，结合数据库版本特性和业务情况酌情在生产环境操作

0X04 索引加在那儿

好了，我们已经知道了索引是什么也知道索引怎么加了，那在哪些字段加索引才会有很好的效果呢？给WHERE后面用的字段加索引，给ORDER BY后面的字段加索引，给“选择的值”加索引。

SELECT create_time, pay_time, username, amount FROM bxx_order WHERE order_status = "done" AND order_type = "free" ORDER BY -pay_time LIMIT 20

第一部分是“给WHERE后面的字段加索引”，也就是上面这个实例中的order_status和order_type字段；第二部分是”给ORDER BY后面的字段加索引”，也就是上面实例中的pay_time字段。因为我们加了索引是方便查询和排序的，你看我们的书上不会给正文加索引，更不会给每一页的字数加索引，因为这些索引加了之后对我们翻看目录并没有任何帮助。

重点是第三点给“选择的值”加索引。什么是“选择的值”呢？就是说某个字段的值就只有这些，这个字段的值值可能是这几种十几种里的一种。

我们看这样一个SQLSELECT * FROM bxx_order WHERE order_status = "done"。因为我们的order_status一共就这么几种，假设这些状态的订单都差不多数量的话，应该是每种状态的订单都是167W左右。那么我们给这个字段加了索引后，数据库查询时候就可以瞬间将查询范围从1000W缩减为167W，效率一下就是原来的6倍，如果状态更多那效果会更明显。（当然不是越多越好，副作用后面会提到）

0X05 联合索引

联合索引可以理解为将索引后的数据再索引一次。比如这么一个SQLSELECT * FROM bxx_order WHERE order_status = "done" AND ORDER_TYPE = "free"，如果建立了order_status和order_type的联合索引，那么就会在对order_status索引完成后的167W条数据的g基础上再来一次对order_type的索引，速度会进一步变快。不过要注意的是联合索引要是AND条件下才会触发，如果用了SELECT * FROM bxx_order WHERE order_status="done" OR order_type="free"这种就不会触发了。

建立联合索引也比较简单，建表的时候将上述的INDEX (order_status)改成INDEX (order_status, order_type)就好了，改表也是同理。

0X06 索引当然有副作用

那是不是给所以字段都加上索引数据库就飞快了？当然不是，如果是的话那索引这东西就应该不需要手动添加而是直接自动实现了是吧。那索引的副作用是什么呢？下面这几种情况都是建立索引的副作用，或是无用功:

  1. 想想把书写完了，生成了一个目录，读者读起来开开心心是吧。突然你说要往书的第三章和第四章中间插入一些新内容，本来是只需要修改内容的，现在还要对应修改目录部分。数据库也是一样，本来一次的写入变成了两次写入，性能自然下降了；
  2. 如果建立了过多的索引，那么索引占用的内存就会很大，对机器造成过多的负载。想想如果我们给书的所有内容都建立了索引，甚至正文页建立了索引，那索引也就没什么太大的意义了；
  3. 如果我们把索引添加在了经常只出现在SELECT后面的字段上，那不仅每次写入数据的时候都要更新索引数据，而且这个索引对我们的系统性能还毫无提升甚至因为这个索引我们系统还更慢了。

0X07 总结

总结起来说的话，索引的建立应该遵循其设计初衷与原理，否则不仅对查询毫无帮助反而会加大消耗的内存并且减慢写入的速度。