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、索引的优缺点
优点:提高查询速度
缺点:更新数据时效率低(需要同时更新索引)