Mysql 数据库结构及索引类型

前言

数据库索引的数据结构

聚集索引

辅助索引

索引管理

联合索引

覆盖索引

总结

前言

数据库索引是mysql数据库中重要的组成部分，是数据库查询数据速度提升的关键，本文将介绍数据库索引的一些内容。

数据库索引的数据结构

在数据库中的索引方法中，有TREE和HASH两种方法，HASH是经常使用的，本文中主要介绍TREE的数据结构。B+Tree 的高度一般是2-4层，也就是说查找一条数据记录，最多使用 2-4 次 IO，当前一般的机械磁盘每秒至少可以做 100 次 IO， 2-4 词的 IO 操作时间大概耗时 20-40ms。

数据库中的B+Tree索引分为聚集索引（clustered index）和辅助索引（secondary index），聚集索引和还是非聚集索引其内部的数据结构为平衡二叉树，所有的数据都存放在叶子节点，聚集索引存放的是一整行的数据，而辅助索引的叶子节点存放的是主键 id。需要提一下的是B+Tree是一个自底向上生长的树，在数据库表中的数据进行增长时，B+Tree也会进行增长和分裂。

聚集索引

聚集索引就是按照每张表的主键 id 构造出的一个B+Tree，同时叶子节点存放的是整行表的数据记录，所以聚集索引的叶子节点就是数据页，每个叶子节点所在的数据页通过双向链表来进行链接。

数据页智能按照一个B+Tree进行排序，因此每张表智能由一个聚集索引。大多数情况下，查询优化器首先采用聚集索引，因为聚集索引上的叶子结点能够直接找到数据记录。由于数据是按照顺序排列的，索引聚集索引对于主键的排序查找和范围值的查询速度非常快。

辅助索引

辅助索引也是非聚集索引，叶子节点并不包含行记录的全部数据。叶子节点除了包含的主键值外，每个叶子结点中的索引行还包含一个书签（bookmark）。借助这个书签InnoDB能够很快找到索引对应的行记录。InnoDB 存储引擎是索引组织表，因此辅助索引的书签就是相应行数据的聚集索引。

数据库表中的聚集索引只有一个，辅助索引可以有多个，当通过辅助索引来查找数据时，InnoDB 存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后通过主键索引来找到完整的行记录。如果辅助索引树的高度为 3，聚集索引树的高度同样为 3，那么如果需要找到一个完整的行记录数据，一共需要 6 次逻辑 IO 访问才能拿到数据页。

索引管理

索引的创建和删除方法如下：

# 创建和删除方法如下
alter table table_name  index_name
create table table_name index_name
drop table table_name index_name
# 查询数据库表的索引
show index from table_name

如下图所示，查询数据库表中的索引情况：

表中的实际索引如下：

PRIMARY KEY (`id`),
UNIQUE KEY `uk_tb_user_2` (`username`,`id_card`) USING BTREE,
UNIQUE KEY `uk_tb_user_1` (`username`,`cellphone`) USING BTREE

在索引的展示项中，索引的值cardinality是非常重要的选项，表示的是数据基数即是数据中不同元素的个数，如果数据内容为枚举，则再该列上建立索引就没有多大意义，因为去分部不大。但是这个值不是实时更新的，只能作为参考。

如果需要更新，则执行以下命令：

analyze table table_name;

上面我们讲述了数据库的索引类型，包括聚集索引和辅助索引，一般情况下这两种索引的索引列都是单列，接下来我们来介绍另外两种索引：联合索引和覆盖索引。

联合索引

联合索引是指对表上的多个列建立索引，联合索引的创建方法与单个索引的创建方法一样，不同之处仅在于有多个索引列。联合索引的数据结构也是一个B+Tree。对于联合索引，其键值对的数量必定是大于 1，多个键值对的排序和单个键值对的排序都是一样的，通过叶子节点可以逻辑上顺序读取所有的数据。

多个键值的B+树：

如有有数据库表的索引建立为（a，b），那么对于数据库的查询：

# 这两种情况下都可以查询到数据，而且可以用得到这个联合索引
select * from table_name where a = XX and b = XX;
select * from table_name where a = XX;
# 如果查询条件使用 b 列，那么就不会使用索引，因为 b 列位于索引的第二位
select * from table_name where b = XX;
# 这个查询条件的顺序和索引的顺序不同，但是依然可以使用索引，
# 这是因为在数据库查询时会进行sql 的优化
select * from table_name where b = XX and a = XX;

在使用联合索引时，选用性能最高也是区分度最高的列放在索引的前面，这样就可以最大限度的提高查询的新能。

覆盖索引

InnoDB 存储引擎支持覆盖索引（covering index，或称索引覆盖），即从辅助索引中就可以查询到数据记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有记录，故其大小要远小于聚集索引，因此可以减少大量的 IO 操作。

使用覆盖索引，就是查询的条件和查询的内容是一致的，通过覆盖索引则可以避免回表的情况发生，减少了一次 IO 操作。