数据库索引（MySQL）

1、什么是索引

索引（index）是帮助MySQL高效获取数据的排好序的数据结构，存储在磁盘文件里

2、建立索引的目的

提高数据库性能，提高海量数据的检索速度

3、在MySQL数据库管理系统中，对表中记录进行检索的时候，通常包括两种检索方式：

第一种：全表扫描

第二种：通过索引进行检索（提高查询效率）

4、索引结构

主键自动添加索引，所以能够通过主键查询尽量通过主键查询，数据较高

• 二叉查找树

若对递增列建索引，查询速度就跟没有建索引一样

• 红黑树

树的高度太大时，磁盘I/O次数增大

增加、删除节点时，红黑树会进行频繁的调整，来保证其性质，浪费时间

• HASH

通过哈希算法将查询的数据与索引进行映射

只能用于范围查找较少的场景

缺点：不能排序，不能用于范围查找（>/<）

• B-Tree

度-节点的数据存储个数（节点大小有限制）

叶节点具有相同的深度

叶节点的指针为空

节点中的数据key从左到右递增排列

查询性能不稳定，查询结果高度不一致，每个节点保存指向真实数据的指针，相比B+树每一层存储的元素更多，显得更高一点

• B+Tree

非叶子节点不存储data，只存储key，可以增大度

所有叶子节点包含全部元素的信息，即指向这些元素记录的指针，且叶子节点本身依关键字的大小自小而大顺序连接

顺序访问指针，提高区间访问的性能（可用于范围查找）

相较于其他树，显得更矮更宽，查询层次更少

性能分析

一般使用磁盘I/O次数评价索引结构的优劣

预读：磁盘一般会向后读取一定长度的数据（页的整数倍）放入内存

局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用

B+Tree节点的大小设为等于一个页，每次新建节点直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，就实现了一个节点的载入只需一次I/O（将横向节点上的全部数据通过一次磁盘I/O读入内存，再从内存查找，从内存中查找性能很高）

B+Tree的度一般会超过100，因此高度非常小（一般在1-3之间）

一般操作系统的最小存储单元为页，1页大小为4K

查看MySQL文件页大小（16K）：show global status like 'Innodb_page_size';

为什么MySQL页文件默认16K

假设一行数据大小为1K，那么一页就能存16条数据，也就是一个叶子节点能存16条数据；对于非叶子节点，假设主键ID为bigint类型，那么长度为8B，指针大小在Innodb源码中为6B，一共为14B，那么一页就能存储16K/14B=1170个（主键+指针）；即一颗高度为2的B+树能存储的数据为：1170*16 = 18720条，一颗高度为3的B+树能存储的数据为：1170*1170*16 = 21902400条

5、常见存储引擎

存储引擎是形容表的

• InnoDB索引实现（聚集索引）

表数据文件本身就是按B+Tree组织的一个索引结构文件

聚集索引-叶节点包含了完整的数据记录（表索引和表索引存储在一个文件）

注：包含两个文件（frm文件-存储表结构、ibd文件-存储表数据和表索引）

为什么InnoDB表必须有主键？并且推荐使用整型的自增主键？

如果没有设置主键，数据库会选择一列（可以唯一标识）作为主键

建立索引是为了方便查询，查询的时候需要判断大小，如果是其他类型，比较大小的时候比较麻烦

如果是自增主键，那么增加数据时，会在之前的叶子节点之后依次添加索引和数据；避免已建立好的B+树节点产生分裂，影响平衡

指针高效解决范围查找

• MyISAM索引实现（非聚集索引）

非聚集索引-索引与数据存储在不同文件中

MyISAM索引文件和数据文件是分离的

注：包含三个文件（frm文件-存储表结构、MYD文件-存储表数据、MYI文件-存储表索引）

6、适合添加索引的场景

该字段数据量庞大

该字段很少有DML操作（DML操作很多的话，索引也是需要不断的维护，效率反而降低）

该字段经常出现在where条件当中

7、索引相关SQL语句

（1）创建索引

create index 索引名 on 表名(字段名);

（2）查询索引

show keys from 表名;

show index from 表名;

desc 表名;

（3）删除索引

删除主键索引：alter table 表名 drop primary key;

删除其他索引：alter table 表名 drop index 索引名;    或    drop index 索引名 on 表名;

8、索引的优缺点

优点：提高查询速度

缺点：更新数据时效率低（需要同时更新索引）