【SQL】之索引

索引是帮助mysql高效获取数据的数据结构

简单的索引设计方式

新建一个表：

mysql> CREATE TABLE index_demo(
-> c1 INT,
-> c2 INT,
-> c3 CHAR(1),
-> PRIMARY KEY(c1)
-> ) ROW_FORMAT = Compact;
1
2
3
4
5
6

存储一条记录的示意图：

放到页中：

若有多个数据，则建立目录项：

innodb中的索引设计方式

迭代一次

迭代两次

迭代三次

将目录项再生成目录,可减少io次数

b+tree

b+树一般不超过四层（树的层次越低,io次数越少）

聚簇索引

聚簇索引是一种数据存储方式，innodb中，数据即索引，索引即数据。

页内：按主键大小排序形成单链表
页间：双向链表

使用innodb，在添加数据的过程中，底层的b+树就已经创建起来了，不需要在添加完数据后再创建index。

优点

• 数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
• 聚簇索引对于主键的排序查找和范围查找速度非常快
• 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的io操作。

缺点

• 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
• 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新
• 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据

二级索引（非聚簇）

使用主键进行查找，可以使用聚簇索引，若使用非主键查找，需要借助二级索引。
每个表中只能有一个聚簇索引，但可以有多个二级索引。
由c2字段构建的索引：

该索引中只有c2字段和c1（主键）字段，要想查找到该字段对应的所有数据（如select *），需要通过查找到的c1（主键）再到聚簇索引中查找完整的数据项目，这个过程叫回表。总共需要用到2棵b+树。

为什么需要有回表操作？不能将完整记录存在叶子节点吗？
正如前面说的，一个表中只能有一个聚簇索引，但可以有多个二级索引。如果给二级索引放上了所有记录，数据量会十分庞大，假设有三个二级索引，每个索引都完整存储了一遍表记录，整个表记录存储了四份，冗余非常大。。

联合索引（非聚簇）

同时为多个列建立索引，如c2和c3，先按c2排序，再在基础上为c3排序，最后保留主键

InnoDB的B+树索引的注意事项

1.根页面位置万年不动
2. 内节点中目录项记录的唯一性
3. 一个页面最少存储2条记录

myISAM中索引设计方案

myisam中索引和数据分开。文件拓展：.MYD 表数据；.MYI 表索引
innodb中，文件拓展：.mdb

添加的字段不进行排序。
myisam的索引文件仅保存数据记录的地址。

myisam一定会进行回表操作。