索引
索引的概念
-
概念:索引(index)是帮助数据库高效获取数据的数据结构,数据库系统维护查找数据的特定数据结构,这些数据结构指向我们的数据,通过高效算法高效查找
-
学习过的数据结构:
- 链表
- hash表
- 二叉树
- 红黑树
-
无索引的查询
select * from emp where age = 19;
- 无索引的情况下,需要全文检索,全表扫描,性能会很低。
-
有索引查询
select * from emp where age = 19;
- 有索引,扫描特别快,时间复杂度极大降低,提高了查询速度
-
优缺点
优点 缺点 提高了查询速度,降低数据库 io 的成本 索引也需要维护,也需要占用空间 通过索引列对数据进行排序,降低排序成本,降低了CPU的消耗 对于表的 INSERT,UPDATE,DELETE 效率降低
索引结构
-
存储引擎层支持索引,也就是说不通的存储引擎可能支持不同的索引。
-
数据引擎和索引结构之间的关系
-
注意:没有特别说明,提问的数据索引都是 B+树索引,基本上面试中,说出B+tree索引和hash索引就可以了
Hash 索引
- 使用Hash算法,将键值换算成hash值,映射到对应槽位
- 特点:查询非常快
- Hash 索引只支持对等比较(=,in),不支持范围查询(between,>,<)
- 索引不支持排序
- 如果没有hash 冲突的时候,查找一次就行了。比B+tree 索引还要快。
- Mysql 支持 Hash 索引的就是 Memory,Innodb 支持hash 索引,是通过指定条件下自动构建。
二叉树
-
注意:加入我们mysql 构建的二叉树数据结构索引,比较理想的就是平衡的二叉树。
-
问题:
-
id的插入是自增的,此时二叉树变成了单向链表
-
此时做查询时,会存在如下问题
- 顺序插入的时候,会形成链表,查询性能很低。
- 如果数据量很大的情况,层级会很深,查询速度会很慢。
-
演示网址
-
红黑树
- 问题:
- 红黑树还是一颗二叉树,会存在层级较深的问题,查询速度也会很慢
B-Tree
- 多叉路平衡查找树,相对于每个二叉树,每个节点有多个分支(多叉)。
- 度数:一个节点子节点的个数,一个最大度数为4(4级)的b-tree为例,每个节点只能存储3key,4个指针。
- 特点:
- 5阶B数,每一个节点最多有4个K,有5个指针。
- 当存储元素达到key 的数量时,中间元素向上分裂。
- 叶子节点和非叶子节点都会存放数据。
B+Tree
-
注意:
- 叶子节点存放数据,非叶子节点存放索引结构。
- 叶子节点通过一个指针进行关联,编程了一个链表的结构
- 当需要裂变的时候,非叶子节点的数据也会出现在叶子节点
- 蓝色框的只存索引结构
注意: Mysql B+Tree 其实进行优化的,在原来的 B+Tree 的基础上,增加了一个指向相邻叶子节点的链表指针,就形成了带有顺序的指针B+Tree,提高区间访问性能。
索引分类
- 主键索引
- 唯一索引
- 常规索引
聚集索引与二级索引
-
需求
- 查询步骤
- 根据 name 去二级索引查询到具体的id=4
- 根据得到的id=4去到聚集索引里面查找,最终拿到这一行的数据
select *from emp where name = 'ez';
- 查询步骤
-
注意:
- 数据关联在聚集索引子节点的
- 二级索引只是关联聚集索引的id
- 非聚集索引的查询都是通过回表查询查询出的数据
-
聚集索引的选取规则:
- 如果存在主键,选择主键作为聚集索引
- 如果不存在主键,会去选择第一个唯一索引(UNIQUE)作为聚集索引。
- 如果都没有,那么 Innodb 会默认生成一个 rowid 作为隐藏的聚集索引。
面试题
-
innodb 为什么选择 B+Tree 做为索引结构
- 对于Hash 索引,Hash 索引不支持范围查询不支持排序
- 对于二叉树索引,层级更多,搜索效率低
- B-Tree 来说,无论是子节点,还是非叶子节点都存放数据,每一页的数据只有16K,如果非叶子节点存放数据了,那么每一页存储的数据就会变少,指针和键值都会变少
-
分析sql 判断查询效率高低
- id 是主键
- name 建立了普通索引
A select * from emp where id = 3; B select * from emp where name ='ez';
- 答案:
- A sql 查询速度高于B sql
- Asql 直接使用聚集索引,可以直接返回 row 信息
- Bsql 先使用二级索引去查找出id=3 然后再去通过聚集索引查找出 row,需要回表查询,所以效率低
-
Innodb 主键索引使用 B+Tree 能够存储多少数据?
- 假设一行数据为1K,一页可以存储16行数据。Innodb 指针固定占用 6个字节,主键bigint 8,int 4
- 高度为2
- 16*1024 = x * 8 + (x+1)*6 x = 1170 指针就是 1171 *16
- 高度为3
- 1171*1171 * 16 =千万级的 (只需要记住MySQL是一个千万级的数据库就行,数据量在大,就要考虑分库分表了)
索引语法
-
查询索引
SHOW INDEX FROM table_name ;
-
创建
CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name ( index_col_name,... ) ;
-
删除索引
DROP INDEX index_name ON table_name ;
-
通过sql 操作索引
-
为用户名创建索引
create index ix_user_name on user(user_name);
-
给 phone 建立索引(唯一索引)
create unique index ix_phone on user(phone);
-
给专业和年龄和状态建立联合索引
create index ix_p_age_s on user(profession,age,status);
-
给专业建索引
create index ix_p on user(profession);
-
查一下所有索引
show index from user;
-
删除 ix_p 索引
drop index ix_p on user;
-