数据库索引

写在前面

一直有想要了解数据库索引相关问题的想法，但是以前用不着，也就只是了解了个大概（懒）。前几天在工作中遇到了重复索引的情况，经过导师的谆谆教诲，这才下定心要搞清楚。

本文内容大部分来自网络搜集，原文出处太多不便一一列出，请见谅，在此感谢各位网络先驱！

0x00 什么是数据库索引

数据库这个东西就不必多言，数据库索引就是对数据库表单当中的一列或者多列的值进行排序的一种结构，通过数据库索引我们能加快对表中数据的检索。当我们建立了一个索引之后，查找数据时就能够从索引中直接定位到相应的数据。举个栗子：当我们没有对表中的字段建立索引或者我们的查寻语句的条件没有有效地命中索引时，想要找到一个数据最坏的情况需要遍历全表。但是如果我们建立了合适的索引，并且编写 sql 语句时有效地命中了索引，这样就可以查找索引的方式查询到想要的数据，因为索引的查找算法是经过优化与实验的，比我们直接去遍历数据库要快得多。

01 索引的分类

唯一索引： 唯一索引类似于数据库字段中的主键，它是互斥的，决不允许一个表中出现唯一索引的字段值相同的情况。例如：在一张姓名表中的“姓”一列加上唯一索引，那么整张表中就不允许出现同姓的情况。

主键索引： 主键索引是唯一索引的特殊情况，我们知道一张数据库表的主键本来就是不允许重复的，它用于特定的标识数据库中的一行。主键索引在创建数据库表指定主键时就已经自动的创建好了，不需要程序员去手动创建。

常规索引： 这是最常用的数据库索引类型，它适用于除主键之外的所有字段，在字段属性中不存在唯一性约束时，记录中的值允许重复。

索引组： 索引的创建并没有要求必须是单个字段，我们同样可以使用多个字段来创建索引组，比如：INDEX name ("姓", "名")

0x01 怎么创建一个合适的索引

上面了解了，数据库的索引是什么，那么要怎么才能创建一个实用的索引就是接下来要考虑的事情了。

01 重复索引与冗余索引

这两者类似但不完全相同，索引跟数据库表的字段一样，同一张表中不允许出现两个相同名称的索引，但不对索引中的字段进行约束，所以在程序员没注意检查的情况下，就可能会出现建立了重复索引或者冗余索引的情况。

重复索引： 当一张表中出现 index1 (x) , index2 (x) 的索引时，index1 和 index2 就被称为重复索引，这种情况除了会增加数据库存储负担外，并不能提高数据库的查询效率。

冗余索引： 冗余索引比较起重复索引来讲更加的复杂，它涉及到一个概念——左前缀索引，即一个索引组当中位于第一位的索引列的字段名，如：index (a, b, c) 中 “a” 就是该索引组当中的左前缀索引。只有当两个索引当中的左前缀索引重复时，才是冗余的，如：已经存在 index (a)，此时 index (a,b) ，那么第二个索引就叫做冗余索引。在创建索引时，尽量在原有索引的基础上进行拓展，而不是去新增，新增索引能够使我们的索引能够覆盖到更多的查询情况，但是过多的索引也必然会带来内存的消耗和增删改性能下降。但这并非绝对，当原有索引已经过于庞大时，新建一个索引更有利于性能。是增还是拓展，这个要交给程序员自己来衡量。

02 如何判断是否应该新建索引

1. 频繁作为查询条件的字段应该为其创建索引

   创建索引能够极大地提高数据库在进行 select 时的 IO 速度，当一个字段在项目中被频繁的使用到 select 当中时，理应为其创建索引。

2. 唯一性太差的字段不适合单独为其创建索引

   一个字段的唯一性指的是它存储的数据是否区别于同字段的其他记录，如：一个表单中存在 “性别” 这一字段，它存储的数据就只能有 “男性” 和 “女性” 两种，这时我们称 “性别” 这个字段的唯一性很差。如果我们为这些唯一性很差的字段创建索引，当数据库查找索引时，同一个索引下面可能对应非常多的记录，这时存储引擎在访问数据时会带来大量的随机 IO ，甚至有时会出现大量的重复 IO 。

   随机 IO 很好理解，数据库根据索引查找数据产生的是随机 IO ，走全表扫描时产生的是顺序 IO ，从性能上比较起来，顺序 IO 的性能要远胜于随机 IO 。 重复 IO 的产生源于数据库中存储的数据是以数据页的方式存储，同一个表中的数据存在在不同的页当中，当我们查询唯一性很差的字段（A,B）时，首先会去查找 A 字段对应的数据，这些数据可能存在不同的数据页当中，数据库在一张页当中寻找完成 A 对应的数据后会丢弃当前页并加载下一页继续查找，直到 A 的数据查询完成。在这之后会去查询 B 的结果，如果 B 的数据存在于 A 已经丢弃的数据页当中，那么存储引擎又会把它加载回来，这时就发生了重复 IO。简单来说就是：同一次查询当中，任意两个字段的查询加载了对方已经丢弃的数据页，从而产生的 IO ，就是重复 IO。

3. 更新太过于频繁的字段不适合为其创建索引

   这个命题是相对的，怎么才叫过于频繁呢？（查询:更新 = 1:1）算频繁还是（查询:更新 = 200:1）才算呢，这个需要自行取舍。

4. 不出现在 WHERE 中的字段不应该创建索引

0x02 如何有效地使用索引

在数据库中建立的索引并不是一旦建立就一定会被查询语句所使用的，合理的去构造争取且有效地查询语句才能更好的发挥索引带来的性能提升。

上面说到，索引并不是越多越好，过多的索引会增加数据库的体积，增加数据库维护索引的开销。那么如何使用更少的索引发挥更大的作用，就是查询语句需要考虑事情了。

• 常规 sql

index (col1, col2, col3)
-- 如果构造的查询语句能够命中到索引组的左前缀索引，那么数据库在查询数据时就会使用到这条索引，反之则不会
SELECT * FROM tb WHERE  col1 = val1   -- can_use
SELECT * FROM tb WHERE  col1 = val1 and col2 = val2  -- can_use

SELECT * FROM  tb WHERE  col3 = val3   -- can_not_use
SELECT * FROM  tb  WHERE  col2 = val2   -- can_not_use
SELECT * FROM  tb  WHERE  col2 = val2  and  col3=val3  -- can_not_use

• 除了上述的正常编写查询语句的情况外，还有使用通配符和不等于操作符的情况

-- 使用通配符
SELECT * FROM tb WHERE  col1 = %num	-- can_not_use
SELECT * FROM tb WHERE  col1 = %num%	-- can_not_use

SELECT * FROM tb WHERE  col1 = num%	-- can_use

-- 使用不等于操作符(<>, !=)，当查询语句中使用到了不等于操作符时，不管是否已经为相应字段建立索引，数据库都会走一次全表扫描，但可以通过 or 来避免这种情况
SELECT * FROM tb where col1 <> 1000 -> SELECT * FROM tb where col1 < 1000 or col1 > 1000

• 使用函数

-- 当函数处理的是表单字段时，无法使用索引
select * from staff where trunc(birthdate) = '01-MAY-82'  -- can_not_use

-- 当函数处理的是条件时，可以使用索引
select * from staff where birthdate < (to_date('01-MAY-82') + 0.9999)  -- can_use

• 数据类型不匹配的情况，这个不必多说，不报错就不错了，查询时一定要注意保持查询条件与字段的数据类型一致。