前言
续接上文
本篇只讲解数据表操作中表定义选项的索引部分内容，属于数据表操作包含内容，学习完整数据表操作可跳转相关链接
索引官方文档：
create_definition: {
col_name column_definition
| {INDEX | KEY} [index_name] [index_type] (key_part,...)
[index_option] ...
| {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] PRIMARY KEY
[index_type] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
[index_name] [index_type] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (col_name,...)
reference_definition
| check_constraint_definition
}
check_constraint_definition:
[CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
reference_definition:
REFERENCES tbl_name (key_part,...)
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
index_type:
USING {BTREE | HASH}
index_option: {
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
| {VISIBLE | INVISIBLE}
|ENGINE_ATTRIBUTE [=] 'string'
|SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
}
一、索引概述
（一）什么是索引
- 官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。
- 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。
- 我们通常所说的索引，包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等，没有特别说明，默认都是使用B+树结构组织（多路搜索树，并不一定是二叉的）的索引。
（二）索引优点
- 可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录。
- 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。
- 被索引的列会自动进行排序，包括【单列索引】和【组合索引】，只是组合索引的排序要复杂一些。
- 如果按照索引列的顺序进行排序，对应order by语句来说，效率就会提高很多。
（三）索引缺点
- 索引会占据磁盘空间
- 索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件。
二、索引类型及对应操作
（一）主键索引
create table User(
`name` varchar(50) not null,
`uid` int(4) not null,
`gender` int(2) not null,
primary key(`uid`)
);
主键索引是唯一的，通常以表的ID设置为主键索引,一个表只能有一个主键索引，这是他跟唯一索引的区别。
这里注意：主键索引和主键约束，两者语法上用法是一样的。
（二）普通索引
所谓普通索引，就是在创建索引时，不附加任何限制条件（唯一、非空等限制）。该类型的索引可以创建在任何数据类型的字段上。
1.创建表时定义索引
CREATE TABLE tablename(
propname1 type1,
propname2 type2,
……
propnamen type..n,
INDEX | KEY
[indexname] (propnamen [(length)] [ ASC | DESC ] )
);
其中，参数 INDEX 和 KEY 是用来指定字段为索引的，两者选择其中之一就可以了，作用是一样的；参数indexname 是索引名字，可省略；参数 propnamen 是索引对应的字段的名称，该字段必须为前面定义好的字段；参数 length 是可选参数，其指索引的长度，必须是字符串类型才可以使用；参数 ASC 和 DESC 都是可选参数，ASC 表示升序排列，DESC 表示降序排列，如果不指定，则为升序。
实例：
create database school;#创建数据库school
use school;#选择数据库school
#创建表class,并建立为id字段索引：
create table class(id int, name varchar(128) UNIQUE, teacher varchar(64), INDEX index_no(id DESC));
show create table class;#查看表结构
insert into class values(1, '一班', 'Martin');#插入记录1
insert into class values(1, '二班', 'Rock');#插入记录2
select * from class where id > 0 ;#根据id查询记录，结果将降序排列
2.在已存在的表上创建索引
方法一: 执行 create 语句
CREATE INDEX indexname ON tablename (propname [(length)] [ASC|DESC]);
参数 INDEX 是用来指定字段为索引，此处不能为 KEY；参数 indexname 是新创建的索引的名字；参数tablename 是指需要创建索引的表的名称，该表必须是已经存在的，如果不存在，需要先创建；参数 propname指定索引对应的字段的名称，该字段必须为前面定义好的字段；参数 length 是可选参数，表示索引的长度，必须是字符串类型才可以使用；参数 ASC 和 DESC 都是可选参数，ASC 表示升序排列，DESC 表示降序排列, 默认升序。
实例：
create database school; #创建数据库 school
use school; #选择数据库 school
create table class(id int, name varchar(128) UNIQUE, teacher varchar(64));
#创建表 class, 并建立为 id 字段索引
create index index_id on class(id ASC); #追加升序索引
show create table class; #查看表定义
insert into class values(1, '一班', 'Martin'); # 插入记录 1
insert into class values(1, '二班', 'Rock'); # 插入记录 2
select * from class where id > 0 ; #根据 id 查询记录，结果将降序排列
方法二: 执行 ALTER TABLE 语句
ALTER TABLE tablename ADD INDEX | KEY indexname (propname [(length)] [ASC|DESC]);
在上述语句中，参数 tablename 是需要创建索引的表；关键字 IDNEX 或 KEY 用来指定创建普通索引；参数 indexname 用来指定所创建的索引名；参数 propname 用来指定索引所关联的字段的名称；参数 length用来指定索引的长度；参数 ASC 用来指定升序排序；参数 DESC 用来指定降序排序。
（三）唯一索引
所谓唯一索引，就是在创建索引时，限制索引的字段值必须是唯一的。通过该类型的索引可以比普通索引更快速地查询某条记录。
1.创建表时定义索引
CREATE TABLE tablename(
propname1 type1,
……
propnamen type..n,
UNIQUE INDEX | KEY [indexname] (propnamen [(length)] [ ASC | DESC ] )
);
**注意：**参数 UNIQUE INDEX 和 UNIQUE KEY 是用来指定字段为索引的，两者选择其中之一即可；参数indexname 是索引名字，可省略；参数 propnamen 是索引对应的字段的名称，该字段必须为前面定义好的字段且必须定义为 UNIQUE 约束；参数 length 是可选参数，其指索引的长度，必须是字符串类型才可以使用；参数 ASC 和 DESC 都是可选参数，ASC 表示升序排列，DESC 表示降序排列，如果不指定，则为升序。
2.已存在的表上创建索引
方法一:
CREATE UNIQUE INDEX indexname ON tablename (propname [(length)] [ASC|DESC]);
方法二:
ALTER TABLE tablename ADD UNIQUE INDEX | KEY indexname (propname [(length)] [ASC|DESC]);
（四）全文索引
全文索引主要对字符串类型建立基于分词的索引，主要是基于 CHAR、VARCHAR 和 TEXT 的字段上，以便能够更加快速地查询数据量较大的字符串类型的字段。全文索引以词为基础的，MySQL 默认的分词是所有非字母和数字的特殊符号都是分词符。
MySQL 从 3.23.23 版本开始支持全文索引，MySQL5.6 以前只能在存储引擎为 MyISAM 的数据表上创建全文索引,5.6 之后 InnoDB 开始支持全文索引(5.7 之后支持中文全文索引) 。在默认情况下，全文索引的搜索执行方式为不区分大小写，如果全文索引所关联的字段为二进制数据类型，就以区分大小写的搜索方式执行。
1.创建表时定义索引
CREATE TABLE tablename(
propname1 type1,
propname2 type2,
……
propnamen type..n,
FULLTEXT INDEX | KEY
[indexname] (propnamen [(length)] )
);
实例：
create database school;#创建数据库school
use school;#选择数据库school
create table class(id int,
name varchar(128) UNIQUE,
teacher varchar(64),
comment varchar(1024),
FULLTEXT INDEX index_comm(comment));
#创建表class,并建立为comment字段为全文索引
insert into class values(1,'1班','Martin','我是一个兵，来自老百姓!');#插入记录1
insert into class values(2,'2班','Rock','此班主任毕业自唐僧系');#插入记录2
insert into class values(3,'3班','Janny','I am Miss Zhang.');#插入记录3
select * from class where match(comment) AGAINST('我是一个兵');#利用全文检索索引快速查询记录
2.已存在的表上创建索引
方法一: 执行 create 语句
在 MySQL 中创建全文索引除了通过 SQL 语句 FULLTEXT INDEX 来实现外，还可以通过 SQL 语句 CREATEFULLTEXT INDEX 来实现，其语法形式如下：
CREATE FULLTEXT INDEX indexname ON tablename( propname1 [ ( length ) ] );
在上述语句中，关键字 CREATE FULLTEXT INDEX 表示用来创建全文索引。如下例表已存在，可通过 CREATE 语句创建全文索引：
create database school; #创建数据库 school
use school; #选择数据库 school
create table class(id int, name varchar(128) UNIQUE, teacher varchar(64));#创建表 class, 并建立为 id 字段索引
create FULLTEXT index index_teacher on class(teacher ); #追加全文索引
show create table class; #查看表定义
方法二: 执行 ALTER TABLE 语句
除了上述两种方式来创建全文索引外，在 MySQL 中创建全文索引还可以通过 SQL 语句 ALTER 来实现，
其语法形式如下：
ALTER TABLE tablename ADD FULLTEXT INDEX|KEY indexname(propname [(length)]);
3.检索方法
根据全文索引字段进行全文检索数据：
SELECT * FROM 表名 WHERE MATCH(列名) AGAINST('关键字');
MySQL8 中文分词支持
配置文件 my.ini（Windows 10 默认路径: C:\ProgramData\MySQL\MySQL Server 8.0） 中增加如下配置项，同时重启 MySQL80 服务:
[mysqld] ngram_token_size=2
（五）多列索引（组合、复合、联合索引）
多列索引，是指在创建索引时所关联的字段不是一个字段，而是多个字段，虽然可以通过所关联的字段进行查询，但是只有查询条件中使用了所关联字段中的第一个字段，多列索引才会被使用。
合索引比单值索引所需要的开销是更小的（对于相同的多个列键索引）。当表的行数远大于索引列的数目时可以使用复合索引。
创建联合索引
create index idx_name_sta_address on table(name,status,address);
相当于创建了三个索引：
name;
name + status;
name + status + address;
创建单列索引
create index idx_name on table(name);
create index idx_status on table(status);
create index idx_address on table(address);
数据库只会选择一个最优的索引来使用，并不会使用全部索引。
1.基本用法
（1）创建表时定义索引
CREATE TABLE tablename(
propname1 type1,
……
propnamen type..n,
INDEX | KEY [indexname] (propname1 [(length)] [ ASC | DESC ],
Propname2 [(length)] [ ASC | DESC ],
... ...
Propnamen [(length)] [ ASC | DESC ])
);
**注意：**和普通索引定义基本相同，不同之处就是增加了多个索引列。
实例：
create database school; #创建数据库 school
use school; #选择数据库 school
#创建表 class, 并建立包含 id,teacher 字段的多列索引：
create table class(id int, name varchar(128) UNIQUE, teacher varchar(64), INDEX index_mult_columns(id, teacher));
show create table class; #查看表定义
insert into class values(1, '一班', 'Martin'); # 插入记录 1
insert into class values(1, '二班', 'Rock'); # 插入记录 2
select * from class where id > 0 ; #仅根据 id 查询记录会启用多列索引
（2）已存在的表上创建索引
方法一: 执行 create 语句
CREATE INDEX indexname ON tablename( propname1 [(length)] [ ASC | DESC ],
Propname2 [(length)] [ ASC | DESC ],
... ...
Propnamen [(length)] [ ASC | DESC ] );
在上述语句中，关键字 CREATE INDEX 表示用来创建多列索引。如下例表已存在，可通过 CREATE 语句创建多列索引：
create database school;#创建数据库school
use school;#选择数据库school
create table class(id int, name varchar(128) UNIQUE, teacher varchar(64));#创建表class,并建立为id字段索引
create index index_id on class(id, name );#追加多列索引
show create table class;#查看表定义
方法二: 执行 ALTER TABLE 语句
除了上述两种方式来创建全文索引外，在 MySQL 中创建全文索引还可以通过 SQL 语句 ALTER 来实现，
其语法形式如下：
ALTER TABLE tablename ADD INDEX|KEY indexname(propname1 [(length)] [ ASC | DESC ],
Propname2 [(length)] [ ASC | DESC ],
... ...
Propnamen [(length)] [ ASC | DESC ] );
2.最左前缀原则
例如表中有a，b，c 3列，为a，b两列创建组合索引，那么在使用时需要满足最左侧索引原则。即在使用组合索引的列为条件的时候，是必须要出现最左侧列作为条件，否则索引是不生效的。
例如：
SELECT * FROM 表名 WHERE a = ' ' AND b = ' ' ; #索引生效`
`SELECT * FROM 表名 WHERE b = ' ' AND b = ' ' ; #索引生效`
`SELECT * FROM 表名 WHERE a = ' ' AND b = ' ' ; # 索引生效`
`SELECT * FORM 表名 WHERE b = ' ' AND c = ' ' ; # 索引不生效
3.覆盖索引（数据查询方式）
覆盖索引就是指索引包含了所有需要查询的字段。
1.覆盖索引是一种数据查询方式，不是索引类型
2.在索引数据结构中，通过索引值可以直接找到要查询字段的值，而不需要通过主键值回表查询，不必通过二级索引查到主键之后再去查询数据。，那么就叫覆盖索引
3.查询的字段被使用到的索引树全部覆盖到
覆盖索引好处：
1.避免了对主键索引(聚簇)的二次查询
2.由于不需要回表查询(从表数据文件)所以大大提升了Mysql缓存的负载
总之大大提升了读取数据的性能
假设你定义一个联合索引
CREATE INDEX idx_name_age ON user(name,age);
查询名称为 liudehua 的年龄：
select name, age from user where name = 'liudehua';
上述语句中，查找的字段 name 和 age 都包含在联合索引 idx_name_age 的索引树中，这样的查询就是覆盖索引查询。
4.索引下推
讲解：
- 索引条件下推(Index Condition Pushdown),简称ICP。MySQL5.6新添加，用于优化数据的查询。
- 当你不使用ICP,通过使用非主键索引（普通索引or二级索引）进行查询，存储引擎通过索引检索数据，然后返回给MySQL服务器，服务器再判断是否符合条件。
- 使用ICP，当存在索引的列做为判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。
适用场景
- 当需要整表扫描，e.g.:range,ref,eq_ref…
- 适用InnoDB引擎和MyISAM引擎查询（5.6版本不适用分区查询，5.7版本可以用于分区表查询）。
- InnoDB引擎仅仅适用二级索引。（原因InnoDB聚簇索引将整行数据读到InnoDB缓冲区）。
- 子查询条件不能下推。触发条件不能下推，调用存储过程条件不能下推。
小示例
-
当我们创建一个用户表(userinfo),其中有字段：id,name,age,addr。我们将name,age建立联合索引。
当我们执行：select * from userinfo where name like "ming%" and age=20; -
对于MySQL5.6之前：我们在索引内部首先通过name进行查找，在联合索引name,age树形查询结果可能存在多个，然后再拿着id值去回表查询，整个过程需要回表多次。
-
对于MySQL5.6之后：我们是在索引内部就判断age是否等于20，对于不等于20跳过。因此在联合索引name,age索引树只匹配一个记录，此时拿着这个id去主键索引树种回表查询全部数据，整个过程就回一次表。
-
如下：
当Extra值为：Using index condition.表示使用索引下推。 -
通过索引下推对于非主键索引进行优化，可有效减少回表次数，从而提高效率。
-
关闭索引下推命令
set optimizer_switch='index_condition_pushdown=off';
聚簇索引与非聚簇索引
（六）其它不常见类型
1.空间索引
2.前缀索引
（七）什么是聚簇索引与非聚簇索引
聚簇索引
就是找到了索引就找到了需要的数据，那么这个索引就是聚簇索引，所以主键就是聚簇索引。
非聚簇索引
索引的存储和数据的存储时分离的，也就是意味着找到了索引但没有找到数据，需要根据索引上的值（主键）再次回表查询，非聚簇索引也叫做辅助索引。
举例：
先创建一个表，用来说明说明情况下是聚簇索引和非聚簇索引：
CREATE TABLE student (
`id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '主键id',
`name` VARCHAR (50) COMMENT '学生姓名',
`age` INT NOT NULL DEFAULT 0 COMMENT '学生年龄',
KEY `idx_name` (`name`)
)
123456
创建的数据库表中，主键id是一个索引，给age添加了索引。
① 直接根据主键查询获取所有字段数据，此时主键就是聚簇索引。因为主键对应的索引叶子节点存储的就是id的所有字段值。
SELECT * FROM student WHERE id = 1;
② 根据name查询所有信息，name本身就是一个唯一索引，但查询的数据包括了所有的数据，所以当命中name索引的时候，该索引的节点的数据存储的就是主键的ID，然后需要ID再查询一次。
这就是非聚簇索引。
SELECT * FROM student WHERE name = “张三”;
索引就是再单独的创建一张表，在这个表中查找数据，然后拿到ID在返回所有数据的表中查询所有的数据。
③ 根据name查询的时候，我们只查询name的值，不查询其他的信息。这种查询就是命中name索引，直接返回name的值，因为所需要的数据就是索引，此时不需要会表查询。这种场景就是非聚簇索引。
SELECT name FROM student WHERE name = “张三”；
拓展
在MySQL中InnoDB引擎的索引和文件是存放在一起的，找到索引就可以找到数据，是聚簇式设计。（默认是聚簇设计的，当你加上其他的索引，根据变化就成为了非聚簇式）
而MyISAM引擎采用的是非聚簇式设计，索引文件和数据文件不在同一个文件中。（不管怎么创建，它本质上就是非聚簇的设计）
因为在MyISAM当中，它是在MYD中存储文件的数据；在MYI中存储文件的索引；在sdi中存储表的结构。
（八）隐藏索引操作
MySQL 8 开始支持隐藏索引。隐藏索引提供了更人性化的数据库操作。隐藏索引，顾名思义，让索引暂时不可见，不会被优化器使用。默认情况下索引是可见的。隐藏索引可以用来测试索引的性能。验证索引的必要性时不需要删除索引，可以先将索引隐藏，如果优化器性能无影响就可以真正地删除索引。
ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; #隐藏索引
ALTER TABLE tablename ALTER INDEX index_name VISIBLE; #取消隐藏
（九）删除索引操作
所谓删除索引，就是删除表中已经创建的索引。之所以要删除索引，是因为这些索引会降低表的更新速度，影响数据库的性能。
在MySQL 中删除索引通过 SQL 语句 DROP INDEX 来实现，其语法形式如下：
DROP INDEX indexname ON tablename;
修改索引: 先删除，后增加
三、常见索引底层数据结构
以下内容均来自：MySql之索引
1.Hash索引
哈希索引是一种基于哈希表的索引结构，它是一种需要精确匹配才生效的索引结构。
实现原理：对索引列计算哈希值把记录映射到哈希槽中，然后指向对应记录行的地址。因此，在查询的时候只要正确匹配到索引列，就能在O(1)的时间复杂度内查到记录。
以下是一个哈希索引的示例，左边是哈希槽，右边是对应的数据列：
相比于B-Tree索引而言，哈希索引有不少的局限性：
- 哈希索引不支持排序
- 哈希索引不支持部分列索引查找
- 哈希索引只支持等值查询，无法提供范围查询功能
哈希索引的查找效率是非常高的，大多数时候都能在O(1)的时间内找到记录，除非哈希冲突很高。
2.二叉查找树
二叉树的特点：
- 一个节点只能有两个子节点，也就是一个节点度不能超过2
- 左子节点 小于 本节点，右子节点大于等于 本节点
在二叉树结构，计算比较 3 次就可以检索到 id=7 的数据，相对于直接遍历查询省了一半的时间，从检索效率上能做到高速检索的。此外二叉树的结构还能提供的范围查找功能，上图二叉树的叶子节点都是按序排列的，从左到右依次升序排列，如果我们需要找 id>5 的数据，那我们取出节点为 6 的节点以及其右子树就可以了，范围查找也是比较容易实现。
缺点:
主键自增情况下会退化为线性链表，二分查找也会退化为遍历查找（全盘扫描），检索性能急剧下降。**id主键索引自增情况下二叉树会退化为线性链表，检索速度降低。**此时检索 id=7 的数据的所需要计算的次数已经变为 7 了,因此 不能直接用于实现 Mysql 底层索引
二叉查找树存在不平衡问题，让二叉树始终保持基本平衡的状态，就能保持二叉查找树的最佳查找性能了。基于这种思路的自调整平衡状态的二叉树有 AVL 树和红黑树。
3.平衡二叉树
AVL 树的特点：
- 平衡二叉树又称AVL树，在满足二叉查找树特性的基础上，要求每个节点的左右子树的高度差不能超过1。
- 很好的查找性能，不存在极端的低效查找的情况。
- 可以实现范围查找、数据排序。
顶端的节点我们称为根节点，没有子节点的节点我们称之为叶节点。
平衡二叉树和非平衡二叉树的对比：
平衡二叉树查找过程:
AVL 树顺序插入 1~16 个节点，查找 id=16 需要比较的节点数为 4。从查找效率而言，AVL 树查找的速度要高于红黑树的查找效率（AVL 树是 4 次比较，红黑树是 6 次比较）。
mysql 如果使用的是 AVL 树，我们每一个树节点只存储了一个数据，一次磁盘 IO 只能取出来一个节点上的数据加载到内存里，如查询 id=7 这个数据我们就要进行磁盘 IO 三次，这很消耗时间。所以我们设计数据库索引时需要首先考虑怎么尽可能减少磁盘 IO 的次数。因此 不能直接用于实现 Mysql 底层索引.。
为了保证平衡，在插入数据的时候必须要旋转，通过插入性能的损失来弥补查询性能的提升。
3.红黑树
红黑树的特点：
- 每个节点的最长子树的只要不超过最短子树的两倍即可
上图所示，左图在插入数值为3时，红黑树的算法发现有偏向，就会重新调整树结构；调整到右边保持平衡，如持续递增，之前的数据1～7持续递增的树，会变成如下图所示
递增插入过程红黑树会自动左旋右旋节点以及节点变色，调整树的形态，使其保持基本的平衡状态，也就保证了查找效率不会明显减低。如上图所示。红黑树下查找 id=7 的所要比较的节点数为 4，依然保持二叉树不错的查找效率。
红黑树很好的解决线性链表问题，但红黑树问题也比较大。
每次插入都要检查规则，再把树进行重新平衡，这个是非常消耗时间,数据量大的话，*红黑树的深度会比较深，并且产生“右倾”,树一旦深就代表着我们读取磁盘次数就会增加*，因此 不能直接用于实现 Mysql 底层索引
我们如果把有序二叉树变成有序多叉树，就能降低树的高度，这个就是基于红黑树演变出来的B树的核心思想。
4.B-Tree：B树，改造二叉树
磁盘 IO 特点：从磁盘读取1B 数据和 1KB 数据所消耗的时间是基本一样的(空间局部性与时间局部性决定)，根据这个思路，可以在一个树节点上尽可能多地存储数据，一次磁盘 IO 就尽可能多的加载数据到内存，影响数据查询时间的是树的高度，高度越高，比较的次数越多，尽量把树的高度降低，这就是B树的设计原理了
B-Tree特点:
- 叶节点具有相同的深度。
- 节点中的元素从左向右递增排序
- 所有的元素不重复
B-Tree结构图:
B树简单地说就是多叉树，每个叶子会存储数据，和指向下一个节点的指针。
例如要查找9，步骤如下
- 我们与根节点的关键字 (17，35）进行比较，9 小于 17 那么得到指针 P1；
- 按照指针 P1 找到磁盘块 2，关键字为（8，12），因为 9 在 8 和 12 之间，所以我们得到指针 P2；
- 按照指针 P2 找到磁盘块 6，关键字为（9，10），然后我们找到了关键字 9。
总结来说，B 树用作数据库索引有以下优点：
- 优秀检索速度
- 尽可能少的磁盘 IO，加快了检索速度；
- 可以支持范围查找。
5.B+Tree：改造B树
有了B树知识铺垫，一个树节点我们应该尽可能的包含更多的子节点，但又不能超过一个磁盘页（16kb）的大小。发现B树的节点中还包含了一些关键字信息data，这个data也占据着一定的数据量，如果把data去掉，这样就又能多加很多子节点了。这也就是B+树的核心思想。
对于聚簇索引来说，data存的是数据行，对于非聚簇索引来说，data存的是主键的值
B+Tree特点：
- 非叶子节点不存储data,只存储索引(冗余),可以放更多的索引
- 叶子节点包含所有索引字段
- 叶子节点用双向指针相连，提高区间访问性
B+树是通过二叉树，平衡二叉树，B树和索引顺序访问演化而来,是对B树的进一步优化。
B+Tree结构图:
B+树是B树的改进，简单地说是：只有叶子节点才存数据，非叶子节点是存储的指针；所有叶子节点构成一个有序链表
例如要查找关键字16，步骤如下
- 与根节点的关键字 (1，18，35) 进行比较，16 在 1 和 18 之间，得到指针 P1（指向磁盘块 2）
- 找到磁盘块 2，关键字为（1，8，14），因为 16 大于 14，所以得到指针 P3（指向磁盘块 7）
- 找到磁盘块 7，关键字为（14，16，17），然后我们找到了关键字 16，所以可以找到关键字 16 所对应的数据。
B+树和B树有什么不同:
- B+树非叶子节点不存储数据的，仅存储键值(索引地址)，而B树节点中不仅存储键值，也会存储数据。B+树之所以这么做是因为在数据库中页的大小是固定的，innodb中页的默认大小是16KB。如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的IO次数会再次减少，数据查询的效率也会更快 。
- B+树索引的所有数据均存储在叶子节点，且数据是按照顺序排列的。B+树使得范围查找，排序查找，分组查找以及去重查找变得简单高效
- B+树各个页之间是通过双向链表连接，叶子节点中的数据是通过单向链表连接的。我们通过双向链表和单向链表连接的方式可以找到表中所有的数据。
6.MySql中的B+Tree
B+树由二叉查找树、平衡二叉树（AVLTree）和平衡多落查找树（B-Tree）逐步优化而来的。
在 mysql分别创建 以myisam 和 Innodb 作为存储引擎的数据表。
Innodb 创建表后生成的文件有：
- frm:创建表的语句
- idb:表里面的数据+索引文件
Myisam 创建表后生成的文件有：
- frm:创建表的语句
- MYD:表里面的数据文件（myisam data）
- MYI:表里面的索引文件（myisam index）
Myisam不支持事务原因索引与数据分开存储，两个文件无法做到一致性
- MyISAM 引擎的底层实现（非聚集索引方式）
MyISAM 是非聚集索引方式，即数据和索引落在不同的两个文件上。B+树索引的叶子节点并不存储数据，而是存储数据的文件地址，MyISAM 在建表时以主键作为 KEY 来建立主索引 B+树，树的叶子节点存的是对应数据的物理地址。拿到这个物理地址后，就可以到 MyISAM 数据文件中直接定位到具体的数据记录了。下图所示：
Myisam检索数据过程中有 “回表操作”
- Innodb 引擎的底层实现（聚集索引方式）
InnoDB 是聚集索引方式，因此数据和索引都存储在同一个文件里。首先 InnoDB 会根据主键 ID 作为 KEY 建立索引 B+树，如左下图所示，而 B+树的叶子节点存储的是主键 ID 对应的数据，比如在执行 select * from user_info where id=15 这个语句时，InnoDB 就会查询这颗主键 ID 索引 B+树，找到对应的 user_name=‘Bob’。
这是建表的时候 InnoDB 就会自动建立好主键 ID 索引树，这也是为什么 Mysql 在建表时要求必须指定主键的原因。当我们为表里某个字段加索引时 InnoDB 会怎么建立索引树呢？比如我们要给 user_name 这个字段加索引，那么 InnoDB 就会建立 user_name 索引 B+树，节点里存的是 user_name 这个 KEY，叶子节点存储的数据的是主键 KEY。注意，叶子存储的是主键 KEY！拿到主键 KEY 后，InnoDB 才会去主键索引树里根据刚在 user_name 索引树找到的主键 KEY 查找到对应的数据（回表）。
Inodb存储引擎特点:
- 表本身是按B+Tree组织的一个索引结构文件
- 叶子节点包含了完整的数据记录
- 非主键索引结构叶子节点存储的是主键的值，使其保持一致性和节省空间
Inodb查询等于的查询 引擎会自动使用哈希索引进行查询,存储引擎会监控对表上索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，所以称之为自适应哈希索引。自适应哈希索引通过缓冲池的B+树构造而来，因此建立的速度很快。而且不需要将整个表都建哈希索引，InnoDB存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引。
- 为什么 InnoDB 只在主键索引树的叶子节点存储了具体数据 ?
为节省存储空间，一个表里可能有很多个索引，InnoDB 都会给每个加了索引的字段生成索引树，如果每个字段的索引树都存储了具体数据，那么这个表的索引数据文件就变得非常巨大（数据极度冗余了）。
-
为什么MySQL不推荐使用uuid作为主键?
1）使用自增id的内部结构
自增的主键的值是顺序的，所以Innodb把每一条记录都存储在一条记录的后面。当达到页面的最大填充因子时候(innodb默认的最大填充因子是页大小的15/16，会留出1/16的空间留作以后的修改)：
①下一条记录就会写入新的页中，一旦数据按照这种顺序的方式加载，主键页就会近乎于顺序的记录填满，提升了页面的最大填充率，不会有页的浪费
②新插入的行一定会在原有的最大数据行下一行，mysql定位和寻址很快，不会为计算新行的位置而做出额外的消耗
③减少了页分裂和碎片的产生
2）使用uuid的索引内部结构
因为uuid相对顺序的自增id来说是毫无规律可言的，新行的值不一定要比之前的主键的值要大，所以innodb无法做到总是把新行插入到索引的最后，而是需要为新行寻找新的合适的位置从而来分配新的空间。
这个过程需要做很多额外的操作，数据的毫无顺序会导致数据分布散乱，将会导致以下的问题：
①写入的目标页很可能已经刷新到磁盘上并且从缓存上移除，或者还没有被加载到缓存中，innodb在插入之前不得不先找到并从磁盘读取目标页到内存中，这将导致大量的随机IO
②因为写入是乱序的，innodb不得不频繁的做页分裂操作，以便为新的行分配空间，页分裂导致移动大量的数据，一次插入最少需要修改三个页以上
③由于频繁的页分裂，页会变得稀疏并被不规则的填充，最终会导致数据会有碎片
在把随机值（uuid和雪花id）载入到聚簇索引(innodb默认的索引类型)以后，有时候会需要做一次OPTIMEIZE TABLE来重建表并优化页的填充，这将又需要一定的时间消耗。
结论：使用innodb应该尽可能的按主键的自增顺序插入，并且尽可能使用单调的增加的聚簇键的值来插入新行
3）使用自增id的缺点
那么使用自增的id就完全没有坏处了吗？并不是，自增id也会存在以下几点问题：
①别人一旦爬取你的数据库，就可以根据数据库的自增id获取到你的业务增长信息，很容易分析出你的经营情况
②对于高并发的负载，innodb在按主键进行插入的时候会造成明显的锁争用，主键的上界会成为争抢的热点，因为所有的插入都发生在这里，并发插入会导致间隙锁竞争
③Auto_Increment锁机制会造成自增锁的抢夺，有一定的性能损失
附：Auto_increment的锁争抢问题，如果要改善需要调优innodb_autoinc_lock_mode的配置
数据表的字段加索引原则：
- 较频繁的作为查询条件的字段应该创建索引；
- 唯一性太差的字段不适合单独创建索引，即使该字段频繁作为查询条件；
- 更新非常频繁的字段不适合创建索引。
- 联合索引底层数据结构
联合索引的数据结构 也是字典排序法则，将第一个 第二个进行排序，B+之所以高效是借助 叶子节点从左到右的排序，如果跳过 第一个字段，则第二三字段 在叶子节点中的排序 不是按顺序排序，则整个数据不一定是顺序递增的结构，就是说联合索引使用时遵循"最左前缀原则"
非主键索引存储的是主键索引位置，会扫描两棵树 （主键索引, 非主键索引）
虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。因为InnoDB支持聚簇索引（主键索引），聚簇索引就是表，所以InnoDB不用像MyISAM那样需要独立的行存储。也就是说，InnoDB的数据文件本身就是索引文件。
四、MySQL的索引实现
以下内容均来自，参考文章1：MySQL数据库中的索引（含SQL语句）
（一）MyIsam索引
以一个简单的user表为例。user表存在两个索引，id列为主键索引，age列为普通索引
CREATE TABLE `user`
(
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
KEY `idx_age` (`age`) USING BTREE
) ENGINE = MyISAM
AUTO_INCREMENT = 1
DEFAULT CHARSET = utf8;
12345678910
MyISAM的数据文件和索引文件是分开存储的。MyISAM使用B+树构建索引树时，叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址。
1.主键索引
表user的索引存储在索引文件user.MYI中，数据文件存储在数据文件 user.MYD中。
简单分析下查询时的磁盘IO情况：
根据主键等值查询数据：
select * from user where id = 28;
1
- 先在主键树中从根节点开始检索，将根节点加载到内存，比较28<75，走左路。（1次磁盘IO）
- 将左子树节点加载到内存中，比较16<28<47，向下检索。（1次磁盘IO）
- 检索到叶节点，将节点加载到内存中遍历，比较16<28，18<28，28=28。查找到值等于30的索引项。（1次磁盘IO）
- 从索引项中获取磁盘地址，然后到数据文件user.MYD中获取对应整行记录。（1次磁盘IO）
- 将记录返给客户端。
磁盘IO次数：3次索引检索+记录数据检索。
根据主键范围查询数据：
select * from user where id between 28 and 47;
1
-
先在主键树中从根节点开始检索，将根节点加载到内存，比较28<75，走左路。（1次磁盘IO）
-
将左子树节点加载到内存中，比较16<28<47，向下检索。（1次磁盘IO）
-
检索到叶节点，将节点加载到内存中遍历比较16<28，18<28，28=28<47。查找到值等于28的索引项。
根据磁盘地址从数据文件中获取行记录缓存到结果集中。（1次磁盘IO）
我们的查询语句时范围查找，需要向后遍历底层叶子链表，直至到达最后一个不满足筛选条件。
-
向后遍历底层叶子链表，将下一个节点加载到内存中，遍历比较，28<47=47，根据磁盘地址从数据文件中获取行记录缓存到结果集中。（1次磁盘IO）
-
最后得到两条符合筛选条件，将查询结果集返给客户端。
磁盘IO次数：4次索引检索+记录数据检索。
**备注：**以上分析仅供参考，MyISAM在查询时，会将索引节点缓存在MySQL缓存中，而数据缓存依赖于操作系统自身的缓存，所以并不是每次都是走的磁盘，这里只是为了分析索引的使用过程。
2.辅助索引
在 MyISAM 中,辅助索引和主键索引的结构是一样的，没有任何区别，叶子节点的数据存储的都是行记录的磁盘地址。只是主键索引的键值是唯一的，而辅助索引的键值可以重复。
查询数据时，由于辅助索引的键值不唯一，可能存在多个拥有相同的记录，所以即使是等值查询，也需要按照范围查询的方式在辅助索引树中检索数据。
（二）InnoDB索引
1.主键索引（聚簇索引）
每个InnoDB表都有一个聚簇索引 ，聚簇索引使用B+树构建，叶子节点存储的数据是整行记录。一般情况下，聚簇索引等同于主键索引，当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID字段来构建聚簇索引。InnoDB创建索引的具体规则如下：
- 在表上定义主键PRIMARY KEY，InnoDB将主键索引用作聚簇索引。
- 如果表没有定义主键，InnoDB会选择第一个不为NULL的唯一索引列用作聚簇索引。
- 如果以上两个都没有，InnoDB 会使用一个6 字节长整型的隐式字段 ROWID字段构建聚簇索引。该ROWID字段会在插入新行时自动递增。
除聚簇索引之外的所有索引都称为辅助索引。在中InnoDB，辅助索引中的叶子节点存储的数据是该行的主键值都。 在检索时，InnoDB使用此主键值在聚簇索引中搜索行记录。
这里以user_innodb为例，user_innodb的id列为主键，age列为普通索引。
CREATE TABLE `user_innodb`
(
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
KEY `idx_age` (`age`) USING BTREE
) ENGINE = InnoDB;
12345678
InnoDB的数据和索引存储在一个文件t_user_innodb.ibd中。InnoDB的数据组织方式，是聚簇索引。
主键索引的叶子节点会存储数据行，辅助索引只会存储主键值。
等值查询数据：
select * from user_innodb where id = 28;
1
-
先在主键树中从根节点开始检索，将根节点加载到内存，比较28<75，走左路。（1次磁盘IO）
-
将左子树节点加载到内存中，比较16<28<47，向下检索。（1次磁盘IO）
-
检索到叶节点，将节点加载到内存中遍历，比较16<28，18<28，28=28。查找到值等于28的索引项，直接可以获取整行数据。将改记录返回给客户端。（1次磁盘IO）
磁盘IO数量：3次。
2.辅助索引
除聚簇索引之外的所有索引都称为辅助索引，InnoDB的辅助索引只会存储主键值而非磁盘地址。
以表user_innodb的age列为例，age索引的索引结果如下图。
底层叶子节点的按照（age，id）的顺序排序，先按照age列从小到大排序，age列相同时按照id列从小到大排序。
使用辅助索引需要检索两遍索引：首先检索辅助索引获得主键，然后使用主键到主索引中检索获得记录。
画图分析等值查询的情况：
select * from t_user_innodb where age=19;
1
根据在辅助索引树中获取的主键id，到主键索引树检索数据的过程称为回表查询。
磁盘IO数：辅助索引3次+获取记录回表3次
3.组合索引
还是以自己创建的一个表为例：表 abc_innodb，id为主键索引，创建了一个联合索引idx_abc(a,b,c)。
CREATE TABLE `abc_innodb`
(
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` varchar(10) DEFAULT NULL,
`d` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
KEY `idx_abc` (`a`, `b`, `c`)
) ENGINE = InnoDB;
12345678910
select * from abc_innodb order by a, b, c, id;
组合索引的数据结构：
组合索引的查询过程：
select * from abc_innodb where a = 13 and b = 16 and c = 4;
1
最左匹配原则：
最左前缀匹配原则和联合索引的索引存储结构和检索方式是有关系的。
在组合索引树中，最底层的叶子节点按照第一列a列从左到右递增排列，但是b列和c列是无序的，b列只有在a列值相等的情况下小范围内递增有序，而c列只能在a，b两列相等的情况下小范围内递增有序。
就像上面的查询，B+树会先比较a列来确定下一步应该搜索的方向，往左还是往右。如果a列相同再比较b列。但是如果查询条件没有a列，B+树就不知道第一步应该从哪个节点查起。
可以说创建的idx_abc(a,b,c)索引，相当于创建了(a)、（a,b）（a,b,c）三个索引。、
组合索引的最左前缀匹配原则：使用组合索引查询时，mysql会一直向右匹配直至遇到范围查询(>、<、between、like)就停止匹配。
4.覆盖索引
覆盖索引并不是说是索引结构，**覆盖索引是一种很常用的优化手段。*因为在使用辅助索引的时候，我们只可以拿到主键值，相当于获取数据还需要再根据主键查询主键索引再获取到数据。但是试想下这么一种情况，在上面abc_innodb表中的组合索引查询时，如果我只需要abc字段的，那是不是意味着我们查询到组合索引的叶子节点就可以直接返回了，而不需要回表。这种情况就是*覆盖索引。
可以看一下执行计划：
覆盖索引的情况：
未使用到覆盖索引：
五、索引的设计原则
索引可以提高查询的速度，但是会影响插入记录的速度，因为向有索引的表中插入记录时，数据库系统会按照索引进行排序，这样就降低了插入记录的速度，插入大量记录时的速度影响更加明显。这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后再创建索引。
- 对查询频次较高,且数据量比较大的表建立索引
- 索引字段的选择,最佳候选列应当从 where子句的条件中提取,如果where子句中的组合比较多,那么应当挑选最常用、过滤效果最好的列的组合
- 索引可以有效的提升查询数据的效率,但索引数昰不是多多益善,索引越多,维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说,索引过多,会引入相当高的维护代价,降低DM操作的效率,增加相应操作的时间消耗。另外索引过多的话, MySQL也会犯选择困难病,虽然最终仍然会找到一个可用的索引,但无疑提高了选择的代价
- 独立的列
独立的列不是指单列索引，而是指索引列不能是表达式的一部分或者是函数的一部分。
select * FROM test where col1 + 1 =100; // 不能是表达式一部分
select * FROM test where ABS(col1) =100; // 不能是函数一部分
- 最左匹配原则
假如有个联合索引 key (col1,col2)。那么以下查询是索引无效的
select * from test where col2 = 3;
select * from test where col1 like ‘%3’;
对于最左匹配原则，大家想一下B+树的叶子节点的关联就差不多知道为啥需要最左匹配原则了，因为B+的叶子结点，从左到右以链表的形式关联的，索引我们查询的时候要么范围查询，要么有明确的左边一个开始的索引值，不能跳过或者不明确如 like '%XYZ’这种查询。
这里需要注意的是，查询的时候如果两个条件都用上了，但是顺序不同，如 col2= xx and col1 ＝xx，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的
- 使用聚簇索引和覆盖索引大大提升读取性能
因为聚簇索引和覆盖索引的索引树上就有了需要的字段，所以不需要回表文件查询，所以提升了查询速度
- 使用短索引
如果很长的字符串进行查询，只需匹配一个前缀长度，这样能够节省大量索引空间
- 尽量使用覆盖索引，避免使用select *
427
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
