数据库索引-结构分析、创建使用以及性能分析

Mysql索引

B+Tree 索引是大多数 MySQL 存储引擎的默认索引类型。因为不再需要进行全表扫描，只需要对树进行搜索即可，所以查找速度快很多。

1.索引所需的数据结构B树和B+树

因为创建索引会根据关键字构造B+树，所以会造成以下结果：

• 在表中有大量数据的前提下，创建索引速度会很慢
• 在索引创建完毕后，对表的查询性能会发幅度提升，但是写性能会降低（因为写入的时候会对B+树进行维护）

1.1 B树

B树就是一种平衡查找多叉树，因为数据库在查找中很多情况都是根据关键字查找的，例如找出所有年龄大于 20 岁的用户名字：

select user_name from user where age > 20; 

所以树上的节点会存放关键字，关键字会关联到一个数据项。

为了方便理解，可以看成关键字就是我们要得到数据，因为我们通过拿到的这个关键字就可以得到想要的对应的数据。

因此，B树的节点可以看成是一个数组 [ p1, 15, p2, 22, p3 ] ，在这个节点中，15和22是两个关键字并有自己对应的数据，p1、p2 和 p3 分别指向三个不同的节点。

B树：

                 根节点 [ p1, 15, p2, 22, p3 ]

p1 -> [ p4, 9, p5, 12 ]   p2 -> [ p7, 19, p8 ]    p3 -> [ 29, 32 ]

因为 B 树是一个搜索树，所以可以知道关键字 15 左侧的 p1 指向的节点中的若干关键字都小于等于 15。同理，p2 指向的节点中，那些关键字的值都在 [15,22] 区间，p3 指向的节点中，那些关键字的值都大于22。

这样一来，查找的效率就很高了，查找的时候，通过这个查找树，小于当前节点关键字时向左边的节点继续查找，大于当前节点关键字时向右边的节点继续查找，就可以比较快的得到想要的关键字，从而能拿到对应的数据了。

1.2 B+树

B+树是基于B树的优化，在非叶子节点上只存放关键字，所有的 data 都放在叶子节点，而且每个叶子节点都会有一个 next 指针指向下一个叶子节点（所有叶子节点会连成一个双向链表）。

B+树：

                 根节点 [ p1, 3, p2, 5, p3 ]

p1 -> [ 1, 2, next1 ]  p2 -> [ 3, 4, next2 ]  p3 -> [ 5, 6, next3 ] 

其中 next1 指向 p2 所指向的节点，next2 指向 p3 所指向的节点。
进行查找操作时，首先在根节点进行二分查找，找到一个关键字所在的指针，然后递归地在指针所指向的节点进行查
找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找出关键字以及所对应的数据。

tips

通过前面可以知道，B+树一个节点（非叶子节点）上存放了许多关键字。如果关键字过长则会导致一个节点上只能存放更少的关键字，也就导致了查找的时候需要搜索更多的磁盘块，所以我们在建立索引的时候，键不宜过长。同理，因为数据库会给主键自动建立一个索引，所以主键不宜过长。

2.索引类型

2.1 数据结构上划分

可以分为 B+树索引和哈希索引

B+Tree 索引

1.结构上，因为B+树是多叉树，所以在同样的数据量下，B+树会比二叉查找树的树高小很多，所需要的查找次数也就更少。
2.磁盘上，B+树因为节点是一个数组，这一个节点的地址是连续的（一个节点就代表一个磁盘的块，该块存放了某个区间的若干关键字），也就是说当搜索到叶子节点的时候，是查到了一个磁盘块上（也就是关键字所在的区间），接下来在这一块一个磁盘块内查找到关键字就行，省去了磁盘重新寻道的时间，提升了许多效率。

哈希索引

哈希索引能以 O(1) 时间进行查找，但是失去了有序性：
无法用于排序与分组；
只支持精确查找，无法用于部分查找和范围查找。
InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之
上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

2.2 重要程度上划分

数据库中的B+树索引可以分为聚集索引（clustered index）和辅助索引（secondary index）

聚集索引与辅助索引相同的是：不管是聚集索引还是辅助索引，其内部都是B+树的形式，即高度是平衡的，叶子结点存放着所有的数据。
聚集索引与辅助索引不同的是：叶子结点存放的是否是一整行的信息

聚集索引

每张表只能拥有一个聚集索引，聚集索引的叶子节点存放的是对应关键字的行记录

• 如果一个主键被定义了，那么这个主键就是作为聚集索引。
• 如果没有主键被定义，那么该表的第一个唯一非空索引被作为聚集索引。
• 如果没有主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个6个字节的列，该列的值会随着数据的插入自增。

辅助索引

表中除了聚集索引外其他索引都是辅助索引，与聚集索引的区别是：辅助索引的叶子节点不包含行记录的全部数据，辅助索引的叶子节点中存放的是建立辅助索引的对应关键字的值以及一个书签 {‘name字段’，name的值，主键id值} 。
比如一张表主键是 id，我们根据字段 name 创建了一个普通索引（该普通索引是辅助索引），那么我们有以下情况：

select name from table_name where name='xx'
# 只需要查询name的值的时候，这个xx值你直接就在辅助索引的叶子节点就能找到，这种我们也可以称为覆盖索引（在组合索引情况下比较有用）。

select age from tb1 where name='xx'
# 这个时候，索引只能查到name为xx的叶子节点，并通过叶子节点上的书签中主键id去聚集索引中查找到对应的节点，再进一步得到age，这种查询效率比覆盖索引略低。

使用覆盖索引的一个好处是：辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。

3.建立索引并使用

索引种类

普通索引 INDEX

是最基本的索引，它没有任何限制。

唯一索引 UNIQUE INDEX

与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

主键索引 PRIMARY KEY

是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引

组合索引 (包括组合普通索引、组合唯一索引、组合主键索引)

指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合

组合索引的使用需要注意一个左前缀匹配的规则，假设有如下组合索引

(id, name, age)

其实相当于建立了三个索引

(id)
(id, name)
(id, name, age)

所以在查询的时候如果想用到这个索引，搜索的条件就要根据组合索引的顺序来匹配：

# 成功使用索引（id，name和age可以打乱顺序）
select * from table_name where id='x' and name='x' and age='x'
select * from table_name where name='x' and id='x' and age='x'

# 只发挥了id的索引，因为根据索引需要的字段，从左匹配到name缺失，就不匹配了
select * from table_name where id='x' and age='x'

# 没有匹配到最左的id，索引完全没发挥作用
select * from table_name where name='x' and age='x'

# 使用了(id, name)，age的没发挥效果
select * from table_name where id='x' and name='x'

前缀索引

顾名思义，索引只取某列的前n个长度作为关键字，就叫做前缀索引，这样能有效减小索引文件的大小，提高索引的速度，但是因为叶子节点只有前n个长度的值，所以不能用作覆盖索引。
需要通过计算全长度选择性来得到最佳的前缀长度。

# 计算全列选择性的值
SELECT COUNT(DISTINCT column_name)/COUNT(*) FROM table_name;

得到该值后，需要定下前缀的长度：

# 计算长度n选择性的值
SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;

当前缀的选择性越接近全列选择性的时候，索引效果越好。

索引相关 sql 语句

// 创建普通索引和组合普通索引
CREATE INDEX index_name ON table_name (column_list);
CREATE INDEX index_name ON table_name (column_list_1, column_list_2, column_list_3...);
// 查看索引
SHOW INDEXES FROM table_name;
// 删除索引
DROP INDEX index_name ON table_name;

// 创建前缀索引
ALTER TABLE table_name ADD KEY(column_name(prefix_length));

性能查询

创建了索引之后可以在 select 前加一个 Explain 来查看查询操作相关信息：

字段名	简介
id	子查询的时候多个select有各自的id
select_type	查询类型，有简单查询、联合查询和子查询
table	查询的表名
type	这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行。
possible_keys	这一列显示查询可能使用哪些索引来查找。
key	这一列显示mysql实际采用哪个索引来优化对该表的访问。
rows	这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。
Extra	若该值为Using Index，则代表发生了索引覆盖

id

id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。

select_type

• SIMPLE：简单查询。查询不包含子查询和union
• UNION：联合查询
• 子查询
  • primary：复杂查询中最外层的 select
  • subquery：包含在 select 中的子查询（不在 from 子句中）
  • derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）

type

链接类型，常见的type性能从慢到快依次为:

all < index < range < ref < eq_ref < const。

type	简介
all	这便是所谓的“全表扫描”，如果是展示一个数据表中的全部数据项（或者通过一个没有索引的关键字查找）。如果是在一个查找数据项的sql中出现了all类型，那通常意味着你的sql语句处于一种最原生的状态，有很大的优化空间。
index	index 指的是发生了索引树扫描，举个例子，表的索引为 (a, b) 联合索引，此时的 sql 为 select a, b from table where b > {value} ，虽然索引不满足最左匹配，但是因为发生了索引覆盖，因此也能通过扫描索引树直接获取结果，不需要回表做全表扫描
range	range指的是有范围的索引扫描，相对于index的全索引扫描，它有范围限制，因此要优于index。关于range比较容易理解，需要记住的是出现了range，则一定是基于索引的。同时除了显而易见的between，and以及’>‘,’<'外，in和or甚至like也是索引范围扫描。
ref	查找条件列使用了索引而且不为主键和unique。其实，意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。
eq_ref	两表做等值连接的时候，命中主键索引或者唯一索引，就会出现。
const	命中主键(primary key)或者唯一(unique)索引，并且被连接的部分是一个常量(const)值；例如最简单的 where id = 1；