index.md

索引

什么是索引

索引是对数据库表中的一列或多列的值进行排序的一种结构，用于对数据库的列的值进行结构化排序

优点：

加快查询和排序

缺点：

1. 增加存储空间
2. 影响查询和修改性能

相关语句

设置索引

• 主键索引 ALTER TABLE tb_name ADD PRIMARY KEY (col_name)
• 唯一索引 ALTER TABLE tb_name ADD UNIQUE (col_name)
• 普通索引 ALTER TABLE tb_name ADD INDEX (col_name)
• 全文索引 ALTER TABLE tb_name ADD FULLTEXT (col_name)
• 多列索引 ALTER TABLE tb_name ADD INDEX index_name (col1,col2...)

查看索引

SHOW INDEX FROM tb_name

唯一索引

当a、b列联合作为唯一索引

• a和b同时作为查询条件，a、b不区分先后索引都可以生效
• a和b单独作为查询条件，都不会生效

索引分类

存储结构层次

• BTree索引（MySQL使用了B+树）
• Hash索引
• full-index全文索引
• R-Tree索引

应用层次

• 普通索引 ：单列索引
• 唯一索引 ：索引列唯一，可空
• 复合索引 ：多列索引，用于组合搜索，效率高于索引合并

物理存储层次

• 聚簇索引 ：同一个结构保存了数据和索引
• 非聚簇索引

存储引擎	允许的索引类型
InnoDB	BTREE
MyISAM	BTREE
MEMORY/HEAP	HASH,BTREE
NDB	HASH,BTREE

InnoDB支持哈希索引，但是不能人工干预，自适应（如果表被频繁使用，自动建立哈希索引）

时间复杂度

HASH，O(1) TREE，O(lg(n))

MySQL使用B+树实现索引的原因

1. 为什么不使用Hash

对于分组、排序、比较等操作，HASH的时间复杂度会退化为O(n)，TREE还是O(lg(n))

2. 为什么不使用二叉搜索树

1）树高度较高，查询慢 2）每个节点存储一个记录，查询会出现多次IO

3. 为什么使用B+树而不是B树

都是m阶搜索树，B+树支持范围查询

4. B+树优点

1. 适合磁盘存储，充分利用磁盘局部性原理和磁盘预读
2. 树高极低，可以存储大量数据
3. 占用内存少
4. 支持单点、范围、有序查询

聚簇索引

InnoDB中使用，索引和数据保存在一个B+树中，一个表只能有一个聚簇索引，默认主键。因为保存在一起，所以查询索引的同时可以直接获得数据

优点

1. 仅需要一次查询，效率高与非聚簇索引
2. 索引和数据保存在一起，因此是有序的，对于范围查询效果高

缺点

1. 更新代价高，因为可能导致数据移动
2. 插入速度依赖插入顺序，按照主键插入最快
3. 插入和更新可能导致页分裂（页：具有一定大小的连续的存储区域）
4. 可能导致全表扫描变慢，因为可能需要加载物理上相隔较远的页到内存（耗时的磁盘寻道操作）

非聚簇索引

MyISAM中使用，又叫二级索引，索引记录的是行的主键值，因此需要两次查询（第一次查询索引得到主键，第二次通过主键查询聚簇索引），一个表可以建立多个索引

底层实现

MySQL默认使用InnoDB作为存储引擎，显示的支持B+树

Hash索引：将索引列计算hash code，索引表保存每行指针

B+索引：

自增主键好处： 可以减少由于新增索引引起的页分裂和数据移动

不使用原则

索引基数低

基数指数据列所包含的不同值的数量

例如：性别列的基数为2

因为当某个值占比很高时，查询优化器会忽略索引，占比一般是30%

数据量较小

索引多的表

索引较多导致

1. 磁盘消耗。索引需要空间
2. 性能消耗。数据修改需要修改索引

不常用字段

没必要创建索引

大字段

建议使用搜索引擎

使用原则

合理使用复合索引

合理使用聚簇索引

合理使用唯一索引

经常进行范围查询的字段

失效原因

对索引列进行运算

无论是通过表达式还是函数运算都会失效

MySQL5.7通过虚拟列、MySQL8.0通过函数索引都可以解决函数运算时的失效问题

索引列类型使用错误

字段类型是string，但是条件使用number

OR

OR条件必须都加索引，否则失效

NULL

B-tree 索引 is null 不会走，is not null 会走，位图索引 is null，is not null 都会走

官网文档 https://dev.mysql.com/doc/refman/8.0/en/create-index.html