4.MySQL索引相关知识

索引

索引和事务未来面试必考的内容
数据库常考的
①给定场景写sql
②数据库的索引(索引是啥,索引的的底层结构)
③数据库的事务(事务是干啥的,事务的特点,事务的隔离级别,不同隔离级别中产生的问题)
④其他内容
索引在面试过程中经常会提问的问题
①索引是什么
②索引要解决的问题
③索引的应用场景
④索引的数据结构
1)为啥不用哈希
2)为啥不用二叉搜索树
3) B树的概念,优势(现场画)
4)B+树,优势(现场画)
⑤其他注意事项

1.1概念

索引是一种特殊的文件,包含着对数据表里所有记录的引用指针.可以对表中的一列或多列创建索引,并指定索引的类型,各类索引有各自的数据结构实现

1.2作用

①数据库中的表,数据,索引之间的关系,类似于书架上的图书,书籍内容和书籍目录的关系.
②索引所起的作用类似书籍目录,可用于快速定位,检索数据.
③数据对于提高数据库的性能有很大的帮助.
如果要在学生表里找到id为8的学生信息,没有索引,查找过程就相当于”顺序表查找”,如果是针对顺序表查找,顺序表在内存中,内存访问速度较块,并且数据并不多,查找速度还行;如果是针对数据库顺序查找,数据库的数据在磁盘上,磁盘访问速度较慢,并且数据量可能非常大,此时访问速度就会很慢,索引就是为了避免数据库进行顺序查找

1.3索引的数据结构

1.3.1哈希

(1)哈希查找的过程,把key带入哈希函数,计算得到下标,再根据小标取到对应的链表,再去遍历比较key是否相等,
(2)!!!哈希只能处理相等的情况,不能处理其他逻辑>,≥,＜,≤,between and
例如:查找id<6并且>3的学生信息
Sel3ect * from student where id <6 and id >3;利用哈希函数就处理不了
()哈希表查找效率:O(1)

1.3.2二叉树(二叉树搜索树)

例如还是查找id<6并且>3 的学生信息
先按照二叉搜索树典型的方式找到id 为6 的元素,再按照二叉搜索树典型的方式找到id为3 的元素,中序遍历3和6之间的结果就是想要的结果,效率是O(n)
(2)!!!相比于哈希表,二叉搜索树虽然能处理范围查找,但是处理效率不高,
①二叉搜索树每个节点最多两个叉,当数据量比较大的时候,树的高度就会比较高,最终的操作效率就会越低②二叉搜索树直接获取到中序遍历的效率也不是很高效(O(N))
二叉树(二叉搜索树)如果比较平衡,查找效率:O(logN)

1.3.3 B树

真正的索引结构是一种N叉搜索树=>B+树
(1)B树(也称作B-树),-相当于一个连接号,不是减号,B树,B+树,但是没有B-树
(2)B树和普通的的二叉树的差异①每个节点不是2叉,而是N叉,②每个节点不是存一个数据而是可能存在多个数据③每个节点存的数据的个数和该节点的度相关,度=存的数据个数+1

(3)!!!B树二叉树相比优点:①在B树上查找,就是一个N分查找效率比二分查找快②由于每个节点存了多个数据,每个节点又有多个度,和二叉树相比.在保存相同个数元素时,B树的高度比二叉树的高度低很多③处理范围查找比较容易
(4)B树构造过程和二叉树构造过程类似,先找到合适位置,再进行插入,需要考虑当前位置的值是放到节点中还是节点下面增加子树

1.3.4 B+树

(1)B+树与B树的不同:①每一层的元素之间都链接在一起②数据只是再叶子节点上保存,非叶子节点只保存一些辅助查找的边界信息
(2)B+树与B树相比优点:①查询每一条的记录速度都是比较平均的,不会出现效率差异较大的情况②不需要进行额外的中序遍历,遍历链表就可以得到中序结果③处理范围查找更高效,当定位min和max之后,中间叶子节点就是结果集④叶子节点放到磁盘上,非叶子节点放到内存中,查找效率更高(减少了读磁盘的次数)索引在内存中占用的实际开销不高

1.4索引起到的效果

加快查找效率减慢插入和删除,修改效率(需要调整索引效果)
索引本质上也会占用额外的空间(实际上是使用空间换取时间)
给表中某列加索引是,加在索引上和加在其他列是不同的,加在主键上可以直接查到,加到其他列上先找到主键在利用主键查取
数据里存的是一条记录(比如存了14,张三,1234@qq.com)
主键索引的叶子节点存的是数据的完整记录,其他索引的叶子节点存的是主键的id
如果是按照name查找,先通过索引查找找到”王五”对应的主键id,再根据主键id 去主键索引里查找到具体的记录

1.5使用场景

对某列或某几列创建索引时,考虑以下几点:
① 数据量量大且经常对这些列进行条件查找
② 该数据库的插入操作,以及对这些列的修改操作频率较低
③ 索引会占用额外空间
主要应用于查找频繁,插入,修改不频繁的操作
如果非条件查询列,或经常做插入,修改操作,或磁盘不足时,不考虑创建索引

1.6索引

创建主键约束(primary key),唯一约束(unique),外键约束(foreign key)时,会自动创建对应列的索引
(1)查看索引
show index from 表名;

show index from student;  -- 查看学生表已有的索引

(2)创建索引
对于非主键,非唯一约束,非外键的字段,可以创建普通索引
create index 索引名 on 表名(字段名);

create index idx_classes_name on classes(name);

(3)删除索引
drop index 索引名 on 表名;

drop index id_classes_name on classes; -- 删除班级表中的name字段索引

主键索引是不能删除的
创建,删除索引都比较耗时

为什么不用哈希:因为不能处理范围查找
为什么不用二叉树:因为效率太低