万字详解Mysql底层索引，事务等面试热点

最新推荐文章于 2024-08-23 09:39:07 发布

猿华

最新推荐文章于 2024-08-23 09:39:07 发布

阅读量293

点赞数

分类专栏：数据库文章标签： mysql 数据库面试

本文链接：https://blog.csdn.net/m0_52255061/article/details/125733402

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

深入理解mysql

mysql作为一个非常重要的数据库，在面试中也会扮演非常重要的角色，恰逢秋招来临，总结了mysql从索引到事务全方位的知识点；希望能帮到各位读者！

索引数据结构

二叉搜索树：单边存储查询次数大
红黑树：二叉平衡树：数据量大时高度太高！查询叶子节点变慢
B树：第一层划分空间，横向存储多个节点，索引元素

B + 树：优化B树；非叶子节点存储冗余节点！只是为了构建B + 树，叶子节点是排好序的，用指针连接,只有3层，每一页有16KB；

查找过程：把第一层节点加载到内存，二分进行数据比对，判断所属区间，找到区间地址，用该地址去磁盘加载对应页数据，继续进行二分比对，拿到地址，加载最后一层叶子节点对应页，继续比对！拿到节点对应的地址data，查出完整数据：

data:可以存储整行数据，或者存储主键索引位置！把data移动到叶子节点能存储更多数据！这就是为什么选择B+树的原因，

双向指针的意义：范围查找！排好序的，一步到位！这就是B 和B+ 的重要区别

hash

innodb存储引擎

表级别！

底层实现，两个文件frm、ibd;

聚集索引

叶子节点包含了所有数据！也就是索引对应的其他列数据、非聚集索引相反！只是存储了数据所在的内存地址！需要跨文件查询！

索引的创建方式建议！

一定要有主键并且推荐自增！如果没有主键，MySQL会自动比较每一列中数据！选取一列来建立索引！如果都没有，会建立一个隐藏列来作为主键！

为什么推荐整形索引！比对速度快！占用空间小！

为什么自增主键？

减少树的分裂和平衡次数

二级索引

与主键索引的差别就是叶子节点不存放整张表的数据，而是放主键id；进行回表操作！找到真正的数据

解决一致性，节约存储空间！

联合索引

多个字段组合
最左前列：按照索引先后建立：先用左边排，相等就换元素继续排

索引优化、Explain

优先选用二级索引！

主键索引数据占用空间大扫描慢！

优化：

KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE

尽量让三个字段都用上，全职匹配！第一个一定要用name不然不会走索引！

但是在name上计算之后就不会走索引了！会导致索引失效！破坏索引树了！

索引树只放原生的索引值

一条Sql的执行过程

输入连接信息！校验IP，连接器用用户去mysql系统库查询是否有用户！如果有就输入密码！连接成功，默认长连接！8小时！

把用户信息存放在临时会话中，包括用户权限等等！防止之火重复校验！

如果再连接期间root用户更改了用户权限临时会话是否会刷新？-------不会！类似JVM的SWT功能，突然刷新会导致线程阻塞，造成不可设想的后果！

show processlist查看所有连接，kill id杀死连接！

select * from table----> 先到缓存查询，如果查到就直接返回结果！有点鸡肋，8以后的版本删除！不过要使用得开启它！

如何存放再缓存空间？把sql语句当KEY，结果当Value，

引擎层innodb做了buffer——pool做了LRU缓存淘汰策略！

分析器

分析sql语句检测语法是否正确！

找关键字拆分成树

优化器

优化where后面条件的字段选择索引！

执行器

调用引擎

归档

sql执行逻辑记录在bin-log,

删库是不需要跑路的，因为我们的SQL执行时，会将sql语句的执行逻辑记录在我们的bin-log当中，什么是bin-log呢？ binlog是Server层实现的二进制日志,他会记录我们的crud操作。

Binlog有以下几个特点：

1、Binlog在MySQL的Server层实现（引擎共用）

2、Binlog为逻辑日志,记录的是一条语句的原始逻辑

3、Binlog不限大小,追加写入,不会覆盖以前的日志

数据归档操作

1 从bin‐log恢复数据 

2 恢复全部数据 

3 /usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults /usr/local/mysql/data/binlog/mysql‐bin.000001 |mysql ‐uroot ‐p tuling(数据库名)

 4 恢复指定位置数据 

5 /usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults ‐‐start‐position="408" ‐‐stop‐position="731" /usr/local/mysql/data/binlog/mysql‐bin.000001 |mysql ‐uroot ‐p tuling(数据库) 

6 恢复指定时间段数据 

7 /usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults /usr/local/mysql/data/binlog/mysql‐bin.000001 ‐‐stop‐date= "2018‐03‐02 12:00:00" ‐‐start‐date= "2019‐03‐02 11:55:00"|mysql ‐uroot ‐p test(数 据库)

索引优化实战

索引下推优化

CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
`position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
PRIMARY KEY (`id`),
KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';
 INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
 INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW());
 INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());
 ‐‐ 插入一些示例数据
 drop procedure if exists insert_emp;
 delimiter ;;
 create procedure insert_emp()
 begin
 declare i int;
 set i=1;
 while(i<=100000)do
 insert into employees(name,age,position) values(CONCAT('zhuge',i),i,'dev');
 set i=i+1;
 end while;
 end;;
 delimiter ;
 call insert_emp();

mysql底层做的优化很智能，它觉得全表扫描比走二级索引更快！因为二级索引有大量回表操作！
or和in不一定走索引，根据表数据量情况，数据量太大走索引必然快，但是数据量少的话全表扫描可能还要快点，并不是走索引就一定会加快速度
like KK%,把他等于常量，无论表数据量大小都会走索引，范围查询不一定会走索引

索引下推优化！

5.6以前不会再使用like字段后的索引，之后就会继续往后推使用索引，这样大大减少了回表次数！为什么使用>的时候不会走索引下推呢？> 的结果集范围比较大，所以内部可能觉得直接走全表扫描要快一点！

优化器索引底层选择

第一阶段：SQL准备，格式化SQL；
第二阶段：做一些简单优化，去除一些无意义的sql

第三阶段：预估扫描表的访问成本！全表扫描的行数，消耗的成本cost;以及可能选取走到的索引；分析各个索引的使用使用成本，根据成本选择索引；一般由cost决定

第四阶段：选择最有访问路径
sql执行！

计算成本很快，优化的很快，有一套预估手段！

Order BY，Group by

order by 是否走索引？看EXTRA是否用到useing index

order by 后面的字段按照联合索引判断顺序决定是否走索引！中间不能跳过，

order by默认升序，索引也是，如果order by指定desc降序呢？还会走索引嘛？----------不会

如果是范围查找==name in (“ddd”,‘hgfa’)==的order by会走索引嘛？------不会！查出来的数据不是有序的！

总结：

文件排序原理

单路排序（additional_fields）：全表扫描加载数据到内存，然后得到结果集排序完成；在sort buffer中进行排序；
双路排序（rowid)，扫描联合索引，得到排序好的id,id已经排序完成，最后回表查出对应的数据即可！
排序字段长度太大数据量多mysql就会选择双路排序！否则就单路；

单路排序数据量大可能会产生临时文件在磁盘，然后再load到内存排序！

索引设计原则

代码先行，索引后上

如何设计索引？建完表就建立索引？还是等主体功能开发出来，把SQL拖出来分析，哪些查询比较热！

联合索引尽量覆盖多字段条件
不要在小基数上建立索引

索引基数是指这个字段在表里总共有多少个不同的值，比如一张表总共100万行记录，其中有个性别字段，其值不是男就是女，那么该字段的基数就是2。如果对这种小基数字段建立索引的话，还不如全表扫描了，因为你的索引树里就包含男和女两种值，根本没法进行快速的二分查找，那用索引就没有太大的意义了。一般建立索引，尽量使用那些基数比较大的字段，就是值比较多的字段，那么才能发挥出B+树快速二分查找的优势来。

长字符串采用前缀索引------order by就不可用了；

尽量对字段类型较小的列设计索引，比如说什么tinyint之类的，因为字段类型较小的话，占用磁盘空间也会比较小，此时你在搜索的时候性能也会比较好一点。当然，这个所谓的字段类型小一点的列，也不是绝对的，很多时候你就是要针对varchar(255)这种字段建立索引，哪怕多占用一些磁盘空间也是有必要的。对于这种varchar(255)的大字段可能会比较占用磁盘空间，可以稍微优化下，

比如针对这个字段的前20个字符建立索引，就是说，对这个字段里的每个值的前20个字符放在索引树里，类似于 KEYindex(name(20),age,position)。此时你在where条件里搜索的时候，如果是根据name字段来搜索，那么此时就会先到索引树里根据name 字段的前20个字符去搜索，

定位到之后前20个字符的前缀匹配的部分数据之后，再回到聚簇索引提取出来完整的name字段值进行比对。但是假如你要是order by name，那么此时你的name因为在索引树里仅仅包含了前20个字符，所以这个排序是没法用上索引的， group by也是同理。所以这里大家要对前缀索引有一个了解。

where与order by冲突时优先where
慢sql查询；

可以根据监控后台的一些慢sql，针对这些慢sql查询做特定的索引优化。关于慢sql查询不清楚的可以参考这篇文章：https://blog.csdn.net/qq_40884473/article/details/89455740

实战

优化二

自增且连续

根据非主键字段排序的分页查询

为什么走联合索引优化不成功呢？因为mysql觉得回表很麻烦会导致很慢，还不如全表扫描！

Join关联优化

EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;

NLJ 算法

上面这条sql会先查询什么数据呢？t1 还是 t2 ? 看t1 t2的有效查询数据量，谁数量小就先查出谁的数据，再用这些数据到另一个表中查需要的数据！小表就叫做驱动表，另一个就叫做被驱动表；但这只是局限于inner join; 对于 Left join 和 right join不会有这种机制！

BNL 算法

上面这条sql走的是NLJ算法，NLJ算法是建立在关联字段是索引的情况下！因为有索引的话很快就能拿到驱动表数据，再去大表上走索引就能很快拿到数据！

但是如果关联字段不是索引呢？那岂不是要全表扫描！这样效率太他妈低了！怎么办呢？

我们可以把小表的所有数据加载到一个缓冲区 join_bufer！然后扫描大表中的每一条数据和小表做对比！这样固然很慢；但这是最优解决方案！另外，如果缓冲区放不下驱动表的数据，只能采用分段手段，但是这样大表只能又多扫了一次！

**count(*)查询优化**

其实count(*) 速度是很快的，快过count（1）；
count(*) ≈ count(1) >count(字段) > count(id) 为什么二级索引比主键id还快？因为二级索引的数据量没有主键id数据量大！count(1) 比索引字段快的原因是字段计数会把字段取出统计，而1不会有这一步，所以理论上count(1) 快一点

事务、隔离级别、锁机制

多个事务同时执行产生的并发问题！

脏写（丢失更新）；两个事务同时更新共享资源，后提交的事务覆盖了前面提交的事务！
脏读；两个事务同时操作一条记录！一个事务在某种隔离级别读取到了另一个事务修改但是并没有提交的数据，另一个事务失败回滚的就造成了第一个事务读取到了一个无效的数据，就叫做脏读！不符合一致性
不可重复读：事务A内部同样的查询语句在不同时刻读取时不一致！不满足隔离性
幻读：事务A读取到了事务B提交的数据！感觉产生了幻觉一样，读取的数据和之前不太一样

读未提交 ：顾名思义！事务A读取到了事务B更新但是没有提交的数据！造成脏读！事务B改一次他就会读取到一次！数据不一致！产生不可重复读！幻读固然就避免不了！

读已提交：只能读事务B事务提交之后的数据！避免了脏读！读的时候事务B没提交事务，再读一次事务B提交了事务，就造成数据不一致，造成不可重复读！

可重复读：事务A内多次读取到的数据都是一样的，不会因为事务B操作数据而变化，但是实际使用数据的时候还是使用其他事务更新之后的数据而不是使用查询出来的数据！所以只能可能产生幻读！验证幻读：事务B新增一条数据，事务A是查不到的，但是如果事务A对这条数据进行修改，然后就可以查到了！还是会产生幻读！

可串行化：select都会枷锁；想要解决这些问题就直接将并行转成串行；只要查询加锁了；这些数据就只有自己能操作；也就是单线程；所以压根就不会有ACID问题！

锁机制

解决多个进程并发访问统一资源的问题，所有并发问题都可以加锁解决！针对不同场景采用不同锁！

锁分类：
乐观锁和悲观锁：前者用版本字段做比较！一致就提交，否则就回滚！后者产生等待队列！
读锁写锁！

读锁：共享锁！针对同一数据，多个读操作不会相互影响！

写锁：排他锁！写操作排挤其他写操作！

表锁和行锁！

行锁：开销大，加锁慢！会出现死锁；锁的粒度小；

表锁：锁整张表；很少用到；对整张表对数据进行迁移的时候可以使用表锁，迁移就不会有啥问题；

间隙锁：

对一个范围进行修改，默认将数据所处的所有区间加锁，其他事务无法对区间内的数据进行操作；间隙锁只有在可重复读隔离级别才会生效！、

无索引的的行锁可能会升级为表锁！所以来说索引是非常重要的！

MVCC机制

多版本并发控制机制！

在可重复读机制下，是如何保证隔离性的？为什么能读取到一致相同的数据？

Mysql在读已提交和可重读隔离级别下实现了MVCC机制！

Undo日志；

修改记录之前会将数据原始版本写入日志；用于回滚；

read-view:当事务开启，执行任何查询sql时会生成当前事务的**一致性视图read-view，**该视图在事务结束之前都不会变化(如果是读已提交隔离级别在每次执行查询sql时都会重新生成)，这个视图由执行查询时所有未提交事务id数组（数组里最小的id为min_id）和已创建的最大事务id（max_id）组成，事务里的任何sql查询结果需要从对应版本链里的最新数据开始逐条跟read-view做比对从而得到最终的快照结果。在RR级别下这个视图在一开始生成就不会发生改变！
版本链比对规则：

如果 row 的 trx_id 落在绿色部分( trx_id);表示这个版本是已提交的事务生成的，这个数据是可见的；
如果 row 的 trx_id 落在红色部分( trx_id>max_id )，表示这个版本是由将来启动的事务生成的，是不可见的(若 row 的 trx_id 就是当前自己的事务是可见的）；
如果 row 的 trx_id 落在黄色部分(min_id <=trx_id<= max_id)，那就包括两种情况

a. 若 row 的 trx_id 在视图数组中，表示这个版本是由还没提交的事务生成的，不可见(若 row 的 trx_id 就是当前自己的事务是可见的)；

b. 若 row 的 trx_id 不在视图数组中，表示这个版本是已经提交了的事务生成的，可见。

对于删除的情况可以认为是update的特殊情况，会将版本链上最新的数据复制一份，然后将trx_id修改成删除操作的trx_id，同时在该条记录的头信息（record header）里的（deleted_flag）标记位写上true，来表示当前记录已经被删除，在查询时按照上面的规则查到对应的记录如果delete_flag标记位为true，意味着记录已被删除，则不返回数据。

**注意：**begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个修改操作InnoDB表的语句，事务才真正启动，才会向mysql申请事务id，mysql内部是严格按照事务的启动顺序来分配事务id的。

Undo版本链一致存在，并且只有一份；所有事务都是在该版本链上进行查询；只是基于不同的readview去查询而已；

读已提交的话流程大致差不多，只是每次都会生成新的readview,这样就能查询出来的数据就是数据库最新的；