数据库索引的前世今生

• 自增主键作为表的主键
• 索引类型
• 主键索引比普通索引查询性能高
• 联合索引
• 前缀索引
• B+树存储

自增主键作为表的主键的好处

因为自增主键是连续的，在插入过程中尽量减少页分裂，即使要进行页分裂，也只会分裂很少一部分；并且自增主键也能减少数据的移动，每次插入都是插入到最后，所以自增主键作为表的主键，对于表的操作来说性能是最高的。

自增主键有哪些优缺点

1. 优点

• 数据存储空间很小；
• 性能最好；
• 减少页分裂。

2. 缺点：

• 数据量过大，可能会超出自增长取值范围；
• 无法满足分布式存储，分库分表的情况下无法合并表；
• 主键有自增规律，容易被破解

综上所述：是否需要使用自增主键，需要根据自己的业务场景来设计。如果是单表单库，则优先考虑自增主键，如果是分布式存储，分库分表，则需要考虑数据合并的业务场景来做数据库设计方案。

索引类型

MySQL 的索引有两种分类方式：逻辑分类和物理分类。

逻辑分类

1. 主键索引：一张表只能有一个主键索引，不允许重复、不允许为 NULL；
2. 唯一索引：数据列不允许重复，允许为 NULL 值，一张表可有多个唯一索引，比如身份证号码、卡号等都可以作为唯一索引；
3. 普通索引：一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入；
4. 一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入；

物理分类

1. 聚簇索引

   • 一般是表中的主键索引，如果表中没有显示指定主键，则会选择表中的第一个不允许为 NULL 的唯一索引，如果还是没有的话，就采用 Innodb 存储引擎为每行数据内置的 6 字节 ROWID 作为聚集索引。
   • 每张表只有一个聚集索引，因为聚集索引的键值的逻辑顺序决定了表中相应行的物理顺序。
   • 聚集索引在精确查找和范围查找方面有良好的性能表现（相比于普通索引和全表扫描），聚集索引就显得弥足珍贵，聚集索引选择还是要慎重的（一般不会让没有语义的自增 id 充当聚集索引）；

2. 非聚簇索引

• 该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同（非主键的那一列），一个表中可以拥有多个非聚集索引。

主键索引为什么比普通索引的查询性能高

因为普通索引的查询会多执行一次检索操作。

比如主键查询 select * from t where id=10 只需要搜索 id 的这棵 B+ 树，而普通索引查询 select * from t where f=3 会先查询 f 索引树，得到 id 的值之后再去搜索 id 的 B+ 树，因为多执行了一次检索，所以执行效率就比主键索引要低。

联合索引

优点&作用

1. 用于多字段查询，比如，建了一个 key(a,b,c) 的联合索引，那么实际等于建了 key(a)、key(a,b)、key(a,b,c) 等三个索引(最左原则)，我们知道，每多一个索引，就会多一些写操作和占用磁盘空间的开销，尤其是对大数据量的表来说，这可以减少一部分不必要的开销；
2. 覆盖索引，比如，对于联合索引 key(a,b,c) 来说，如果使用 SQL：select a,b,c from table where a=1 and b = 1 ，就可以直接通过遍历索引取得数据，而无需回表查询，这就减少了随机的 IO 操作，减少随机的 IO 操作，可以有效的提升数据库查询的性能，是非常重要的数据库优化手段之一；
3. 索引列越多，通过索引筛选出的数据越少。

前缀索引

前缀索引也叫局部索引，比如给身份证的前 10 位添加索引，类似这种给某列部分信息添加索引的方式叫做前缀索引。

eg：手机号前6位创建索引：

alter table t add index index_phone(phone(6));
create index index_phone on t(phone(6));

优缺点：

1. 优点：前缀索引能有效减小索引文件的大小，让每个索引页可以保存更多的索引值，从而提高了索引查询的速度
2. 不能在 order by 或者 group by 中触发前缀索引，也不能把它们用于覆盖索引(需要回表查询)。

InnoDB 为什么要使用 B+ 树，而不是 B 树、Hash、红黑树或二叉树？

因为 B 树、Hash、红黑树或二叉树存在以下问题：

1. B 树：不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致IO操作变多，查询性能变低；
2. Hash：虽然可以快速定位，但是没有顺序，IO 复杂度高；
3. 二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且 IO 代价高；
4. 红黑树：树的高度随着数据量增加而增加，IO 代价高。

B+Tree 中的 B 是 Balance，是平衡的意思，它在经典 B Tree 的基础上进行了优化，增加了顺序访问指针，在B+Tree 的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的 B+Tree，这样就提高了区间访问性能：如果要查询 key 为从 18 到 49 的所有数据记录，当找到 18 后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率（无需返回上层父节点重复遍历查找减少 IO 操作）。

索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上，这样的话，索引查找过程中就要产生磁盘 IO 消耗，相对于内存存取，IO 存取的消耗要高几个数量级，所以索引的结构组织要尽量减少查找过程中磁盘 IO 的存取次数，从而提升索引效率。 综合所述，InnDB 只有采取 B+ 树的数据结构存储索引，才能提供数据库整体的操作性能。