MySQL 篇

MySQL 篇

整体内容

image-20241027100816131

索引结构剖析

1、索引是什么?

索引是一种特殊的文件,它们包含着对数据表里所有记录的引用指针。
通俗的说,索引就相当于目录。为了方便查找书中的内容,通过对内容建立索引形成目录。
由于索引也是一个文件,故它也要占内存。

2、索引的优缺点

索引的优点:
	1.大大加快数据的检索速度
	2.通过使用索引,在查询的过程中,优化隐藏器,提高系统的性能。
索引的缺点:
	1、时间方面:创建索引和维护索引要耗费时间,具体地,当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,会降低增/改/删的执行效率;
	2、空间方面:索引需要占物理空间

3、MySQL有哪几种索引类型?

1、存储结构上划分:
	B-Tree或B+Tree索引,Hash索引等
2、应用层次上划分:
	2.1、普通索引
		即一个索引只包含单个列,一个表可以有多个单列索引
	2.2、唯一索引
		索引列的值必须唯一,但允许有空值
	2.3、复合索引
		多列值组成一个索引,专门用于组合搜索,其效率大于索引合并
	2.4、聚簇索引
		并不是一种单独的索引类型,而是一种数据存储方式。具体细节取决于不同的实现,InnoDB的聚簇索引其实就是在同一个结构中保存了B-Tree索引(技术上来说是B+Tree)和数据行。
	2.5、非聚簇索引
		 数据和索引分开存储,B+树叶子节点保存对应主键,可以有多个;一般我们自己定义的索引都是非聚簇索引

4、索引的底层实现

4.1、Hash索引
	基于哈希表实现,只有精确匹配索引所有列的查询才有效,对于每一行数据,存储引擎都会对所有的索引列计算一个哈希码(hash code),并且Hash索引将所有的哈希码存储在索引中,同时在索引表中保存指向每个数据行的指针。

image-20241220225120164

4.2B-Tree索引
	B-Tree能加快数据的访问速度,因为存储引擎不再需要进行全表扫描来获取数据,数据分布在各个节点之中。

image-20241220225229224

4.3B+Tree索引
	是B-Tree的改进版本,同时也是数据库索引所采用的存储结构。数据都在叶子节点上,并且增加了顺序访问指针,每个叶子节点都指向相邻的叶子节点的地址。相比B-Tree来说,进行范围查找时只需要查找两个节点,进行遍历即可。而B-Tree需要获取所有节点,相比之下B+Tree效率更高。
	非叶子节点存储的是指针,数据全部存储在叶子节点上。并且叶子节点是通过双向链表进行连接。

image-20241220225639482

5、为什么索引结构默认使用B+Tree,而不是B-Tree,Hash,二叉树,红黑树?

5.1B-tree: 从两个方面来回答
	B+树的磁盘读写代价更低:B+树的内部节点并没有指向关键字具体信息的指针,因此其内部节点相对B(B-)树更小,如果把所有同一内部节点的关键字存放在同一盘块中,那么盘块所能容纳的关键字数量也越多,一次性读入内存的需要查找的关键字也就越多,相对IO读写次数就降低了。
	由于B+树的数据都存储在叶子结点中,分支结点均为索引,方便扫库,只需要扫一遍叶子结点即可,但是B树因为其分支结点同样存储着数据,我们要找到具体的数据,需要进行一次中序遍历按序来扫,所以B+树更加适合在区间查询的情况,所以通常B+树用于数据库索引。
	
5.2、Hash:
	虽然可以快速定位,但是没有顺序,IO复杂度高;
	基于Hash表实现,只有Memory存储引擎显式支持哈希索引 ;
	适合等值查询,如=、in()、<=>,不支持范围查询 ;
	因为不是按照索引值顺序存储的,就不能像B+Tree索引一样利用索引完成排序 ;
	Hash索引在查询等值时非常快 ;
	因为Hash索引始终索引的所有列的全部内容,所以不支持部分索引列的匹配查找 ;
	如果有大量重复键值得情况下,哈希索引的效率会很低,因为存在哈希碰撞问题 。
	(总结下来:不支持范围查询、在等值查询方面比较快、不支持部分索引列的匹配)
	
5.3、二叉树:
	树的高度不均匀,不能自平衡,查找效率跟数据有关(树的高度),并且IO代价高。
	
5.4、红黑树:
	树的高度随着数据量增加而增加,IO代价高。

6、讲一讲聚簇索引与非聚簇索引?

建议看下文关于这部分的描述:
也即:
	聚簇索引:数据和索引存放到了一起,找到索引就找到了行数据。
	非聚簇索引:叶子节点不存储表中的数据,而是存储该列对应的主键(行号)
	
通常情况下, 主键索引(聚簇索引)查询只会查一次,而非主键索引(非聚簇索引)需要回表查询多次。当然,如果是覆盖索引的话,查一次即可

7、非聚簇索引一定会回表查询吗?

	不一定,这涉及到查询语句所要求的字段是否全部命中了索引,如果全部命中了索引,那么就不必再进行回表查询。一个索引包含(覆盖)所有需要查询字段的值,被称之为"覆盖索引"。
	举个简单的例子,假设我们在学生表的成绩上建立了索引,那么当进行select score from student where score > 90的查询时,在索引的叶子节点上,已经包含了score 信息,不会再次进行回表查询。

8、联合索引是什么?为什么需要注意联合索引中的顺序?

	MySQL可以使用多个字段同时建立一个索引,叫做联合索引。在联合索引中,如果想要命中索引,需要按照建立索引时的字段顺序挨个使用,否则无法命中索引。
具体原因为:
	MySQL使用索引时需要索引有序,假设现在建立了"name,age,school"的联合索引,那么索引的排序为: 先按照name排序,如果name相同,则按照age排序,如果age的值也相等,则按照school进行排序。
	当进行查询时,此时索引仅仅按照name严格有序,因此必须首先使用name字段进行等值查询,之后对于匹配到的列而言,其按照age字段严格有序,此时可以使用age字段用做索引查找,以此类推。因此在建立联合索引的时候应该注意索引列的顺序,一般情况下,将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整。

9、讲一讲MySQL的最左前缀原则?

	最左前缀原则就是最左优先,在创建多列索引时,要根据业务需求,where子句中使用最频繁的一列放在最左边。mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。
	=和in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式。

image-20241220232433592

10、讲一讲前缀索引?

	因为可能我们索引的字段非常长,这既占内存空间,也不利于维护。所以我们就想,如果只把很长字段的前面的公共部分作为一个索引,就会产生超级加倍的效果。但是,我们需要注意,order by不支持前缀索引 。
流程是:
1、先计算完整列的选择性 :select count(distinct col_1)/count(1) from table_1
	(选择性数值越低,意味着这个字段中不同值的重复度越高,也就意味着通过这个字段去区分不同行的能力相对较弱,但它可以帮助我们后续对比不同前缀长度的选择性情况。)
2、再计算不同前缀长度的选择性 :select count(distinct left(col_1,4))/count(1) from table_1(假设这次得到的选择性数值是 0.05(同样是示例值),这说明取前 4 个字符作为前缀时,能区分出更多不同的情况,相比完整的 customer_address 字段,在一定程度上有更好的区分度。我们可能还会继续尝试其他前缀长度,比如长度为 810 等(通过修改 left 函数里的第二个参数),并对比它们的选择性数值。)
3、找到最优长度之后,创建前缀索引 :create index idx_front on table_1 (col_1(4))(假设经过前面的对比,发现取 customer_address 字段前 10 个字符作为前缀时,选择性达到了一个相对比较理想的值(能较好地区分不同记录,同时又不会使索引太长)。这时,我们就可以创建前缀索引了)

11、了解索引下推吗?

MySQL 5.6引入了索引下推优化。默认开启,使用SET optimizer_switch = ‘index_condition_pushdown=off’;可以将其关闭。
	有了索引下推优化,可以减少回表次数
	在InnoDB中只针对二级索引有效
官方文档中给的例子和解释如下:
	在 people_table中有一个二级索引(zipcode,lastname,address),查询是SELECT * FROM people WHERE zipcode=’95054AND lastname LIKE ‘%etrunia%’ AND address LIKE ‘%Main Street%’;
	如果没有使用索引下推技术,则MySQL会通过zipcode=’95054’从存储引擎中查询对应的数据,返回到MySQL服务端,然后MySQL服务端基于lastname LIKE ‘%etrunia%’ and address LIKE ‘%Main Street%’来判断数据是否符合条件
	如果使用了索引下推技术,则MYSQL首先会返回符合zipcode=’95054’的索引,然后根据lastname LIKE ‘%etrunia%’ and address LIKE ‘%Main Street%’来判断索引是否符合条件。如果符合条件,则根据该索引来定位对应的数据,如果不符合,则直接reject掉。

12、怎么查看MySQL语句有没有用到索引?

通过explain,如以下例子:

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title='Senior Engineer' AND from_date='1986-06-26';

image-20241220233734952

	1、id:在⼀个⼤的查询语句中每个SELECT关键字都对应⼀个唯⼀的id ,如explain select * from s1 where id = (select id from s1 where name = 'egon1');第一个select的id是1,第二个select的id是2。有时候会出现两个select,但是id却都是1,这是因为优化器把子查询变成了连接查询 。
	2、select_type:select关键字对应的那个查询的类型,如SIMPLE,PRIMARY,SUBQUERY,DEPENDENT,SNION 。
	3table:每个查询对应的表名 。
	4、type:type 字段比较重要, 它提供了判断查询是否高效的重要依据依据. 通过 type 字段, 我们判断此次查询是 全表扫描 还是 索引扫描 等。如const(主键索引或者唯一二级索引进行等值匹配的情况下),ref(普通的⼆级索引列与常量进⾏等值匹配),index(扫描全表索引的覆盖索引) 。
	通常来说, 不同的 type 类型的性能关系如下:
ALL < index < range ~ index_merge < ref < eq_ref < const < system
ALL 类型因为是全表扫描, 因此在相同的查询条件下, 它是速度最慢的.
而 index 类型的查询虽然不是全表扫描, 但是它扫描了所有的索引, 因此比 ALL 类型的稍快.
	5、possible_key:查询中可能用到的索引(可以把用不到的删掉,降低优化器的优化时间) 。
	6、key:此字段是 MySQL 在当前查询时所真正使用到的索引。
	7、filtered:查询器预测满足下一次查询条件的百分比 。
	8rows 也是一个重要的字段. MySQL 查询优化器根据统计信息, 估算 SQL 要查找到结果集需要扫描读取的数据行数.
这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好。
	9、extra:表示额外信息,如Using where,Start temporary,End temporary,Using temporary等。

13、为什么官方建议使用自增长主键作为索引?

结合B+Tree的特点,自增主键是连续的,在插入过程中尽量减少页分裂,即使要进行页分裂,也只会分裂很少一部分。并且能减少数据的移动,每次插入都是插入到最后。总之就是减少分裂和移动的频率。

image-20241220234456268

image-20241220234519436

14、如何创建索引?

创建索引有三种方式。
1、在执行CREATE TABLE时创建索引
CREATE TABLE user_index2 (
    id INT auto_increment PRIMARY KEY,
    first_name VARCHAR (16),
    last_name VARCHAR (16),
    id_card VARCHAR (18),
    information text,
    KEY name (first_name, last_name),
    FULLTEXT KEY (information),
    UNIQUE KEY (id_card)
);

2、使用ALTER TABLE命令去增加索引。
ALTER TABLE table_name ADD INDEX index_name (column_list);
	ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。
	其中table_name是要增加索引的表名,column_list指出对哪些列进行索引,多列时各列之间用逗号分隔。
	索引名index_name可自己命名,缺省时,MySQL将根据第一个索引列赋一个名称。另外,ALTER TABLE允许在单个语句中更改多个表,因此可以在同时创建多个索引。

3、使用CREATE INDEX命令创建。
CREATE INDEX index_name ON table_name (column_list);

15、创建索引时需要注意什么?

	非空字段:应该指定列为NOT NULL,除非你想存储NULL。在mysql中,含有空值的列很难进行查询优化,因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值;
	取值离散大的字段:(变量各个取值之间的差异程度)的列放到联合索引的前面,可以通过count()函数查看字段的差异值,返回值越大说明字段的唯一值越多字段的离散程度高;
	索引字段越小越好:数据库的数据存储以页为单位,一页存储的数据越多,一次IO操作获取的数据越大效率越高。

16、建索引的原则有哪些?

	1、最左前缀匹配原则,非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。
	2、=和in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式。
	3、尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录。
	4、索引列不能参与计算,保持列“干净”,比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。
	5、尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。

17、使用索引查询一定能提高查询的性能吗?

	通常通过索引查询数据比全表扫描要快。但是我们也必须注意到它的代价。
	索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时,索引本身也会被修改。 这意味着每条记录的I* NSERT,DELETEUPDATE将为此多付出45 次的磁盘I/O。 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能,索引范围查询(INDEX RANGE SCAN)适用于两种情况:
	1、基于一个范围的检索,一般查询返回结果集小于表中记录数的30%2、基于非唯一性索引的检索。

image-20241221000003755

18、什么情况下不走索引(索引失效)?

1、使用!= 或者 <> 导致索引失效
2、类型不一致导致的索引失效
3、函数导致的索引失效
	SELECT * FROM `user` WHERE DATE(create_time) = '2020-09-03';
	如果你的索引字段使用了函数,对不起,他是真的不走索引的。
4、运算符导致的索引失效
	SELECT * FROM `user` WHERE age - 1 = 20;
	如果你对列进行了(+-*/!), 那么都将不会走索引。
5OR引起的索引失效
	SELECT * FROM `user` WHERE `name` = '张三' OR height = '175';
	OR导致索引是在特定情况下的,并不是所有的OR都是使索引失效,如果OR连接的是同一个字段,那么索引不会失效,反之索引失效。
6、模糊搜索导致的索引失效
	SELECT * FROM `user` WHERE `name` LIKE '%冰';
	当%放在匹配字段前是不走索引的,放在后面才会走索引。
7NOT INNOT EXISTS导致索引失效

优化-如何定位慢查询

image-20241027101029324

如何定位慢查询

image-20241027101402997

image-20241027101525100

面试总结

MySQL中如何定位慢查询?
	1、可以部署像Skywalking这样的监控系统,它能够在展示的报表中看到是哪一个接口比较慢,并且可以看到是接口的哪一部分比较慢,也可以看到SQL的具体执行时间,方便定位是哪个SQL出了问题。
	2、也可以使用MySQL提供的慢日志查询功能,可以在MySQL的系统配置文件中开启这个慢日志的功能以及设置SQL执行超过多少时间来记录到一个日志文件中,一般设置的可能是2秒,通过这种方式就可以在日志文件中找到执行比较慢的SQL了。

优化-SQL语句执行的很慢,如何分析

image-20241027102130014

解决思路

image-20241027102317201

image-20241027102521603

image-20241027102751699

面试总结

SQL语句执行很慢,如何分析?
如果一条SQL执行很慢的话,可以采用MySQL自带的分析工具explain来查看这条语句的执行情况:
	1、可以通过key和key_len检查是否命中了索引,如果本身添加了索引,也可以判断索引是否有失效的情况。
	2、可以通过type字段查看SQL是否有进一步的优化空间,是否存在全索引扫描或全盘扫描。
	3、通过extra建议判断,是否出现了回表的情况,如果出现了,可以尝试添加索引或修改返回字段来修护。

优化-索引概念及索引底层数据结构

image-20241027103748698

数据结构对比

image-20241027104102477

image-20241027104300777

image-20241027104636980

面试总结

Q1:什么是索引?
	索引是帮助MySQL高效获取数据的数据结构,主要用来提高数据检索的效率,降低数据库的IO成本(不需要全表扫描),同时通过索引列对数据进行排序,降低数据排序的成本以及CPU的消耗(例如,对于一个整数列建立的索引,这些整数在索引树的叶子节点中是按照从小到大的顺序排列的。当我们创建索引时,数据库会将索引列的值进行排序后存储在索引结构中。)(更通俗的说,索引就相当于目录。为了方便查找书中的内容,通过对内容建立索引形成目录。)


Q2:索引的底层数据结构
	Mysql默认的存储引擎InnoDB采用的B+树的数据结构来存储索引,选择B+树的主要原因:
		1、阶数更多,路径更短
		2、磁盘读写代价B+树更低,非叶子节点只存储指针,叶子节点存储数据
		3B+树便于扫库和区间查询,叶子节点是一个双向链表
		
		
Q3:B树和B+树的区别是什么?
	1、在B树中,非叶子节点和叶子节点都会存储数据,而B+树所有的数据都会出现在叶子节点;在查询的时候,B+树查找效率更加稳定
	2、在范围查询的时候,B+树效率更高,因为B+树都在叶子节点存储,并且叶子节点是一个双向链表。

优化-聚簇索引、非聚簇索引、回表查询

image-20241027113615917

image-20241027113752134

回表查询

image-20241027114004875

面试总结

Q1:聚簇索引和非聚簇索引
	聚簇索引:数据和索引放到一起,B+树叶子节点保存整行数据,有且只有一个;一般情况下主键作为聚簇索引
	非聚簇索引(二级索引):数据和索引分开存储,B+树叶子节点保存对应主键,可以有多个;一般我们自己定义的索引都是非聚簇索引
	
	
Q2:回表查询
	通过二级索引找到对应的主键值,到聚集索引中查询整行数据,这个过程就是回表

优化-覆盖索引、超大分页优化

覆盖索引

image-20241027114829171

image-20241027115128183

超大分页

image-20241027115706613

image-20241027120053264

面试总结

Q1:覆盖索引
	覆盖索引是指select查询语句使用了索引,返回的列必须在索引中全部能够找到
		使用id查询,直接走聚簇索引查询,一次索引扫描,直接返回数据,性能高
		如果按照二级索引查询数据的时候,返回的列中没有创建索引,有可能会触发回表查询,尽量避免使用select *,尽量在返回的列中包含添加索引的字段。
		
		
Q2:Mysql超大分页怎么处理?
	超大分页一般都是在数据量比较大时,我们使用了limit分页查询,并且需要对数据进行排序,这个时候效率就很低,我们可以采用覆盖索引和子查询来解决。
	先分页查询数据的id字段,确定了id之后,再用子查询来过滤,只查询这个id列表中的数据就可以了。因为查询id的时候,走的覆盖索引,所以效率可以提升很多。
下面给出解释:

优化-索引创建原则

面试总结

Q:索引创建原则有哪些?
	一般的大前提都是表中的数据要超过10万以上,我们才会创建索引;并且添加索引的字段都是查询比较频繁的字段,一般像查询条件、排序字段或分组字段这些。
	创建索引的时候都是使用组合索引来创建,一条SQL的返回值,尽量使用覆盖索引,如果字段的区分度不高的话,也会把其放在组合索引的后面字段。
	如果某一个字段内容较长,可以考虑使用前缀索引,当然并不是所有的字段都要添加索引,索引的数量需要控制,因为添加索引会导致增改的速度变慢。

优化-什么情况下索引会失效

image-20241028114024798

索引没有问题的情况

image-20241028114144463

索引失效的情况

image-20241028114334492

image-20241028114829992

image-20241028114917488

image-20241028115030565

image-20241028172832263

面试总结

什么情况下索引会失效?
	1、违反最左前缀法则
	2、范围查询右边的列,不能使用索引
	3、不要在索引列上进行运算操作,否则索引将失效
	4、字符串不加单引号,照成索引失效(类型转换)
	5、以%开头的Like模糊查询,索引失效

优化-谈谈你对SQL优化的经验

image-20241028173757503

image-20241028174146713

image-20241028174252408

面试总结

Q1:SQL优化经验
	SQL优化的话,一般会从这几方面考虑,比如建表的时候、使用索引、SQL语句的编写,主从复制,读写分离,还有一个如果量比较大的话,可以考虑分库分表。
	
Q2:创建表的时候如何优化?
	定义字段的时候需要结合字段的内容来选择合适的类型,如果是数值的话,像tinyint、intbigint这些类型,要根据实际情况选择。如果是字符串类型,也是结合存储的内容选择charvarchar或者text类型。
	
Q3:使用索引的时候,如何优化?
	参考索引创建原则!
	
Q4:平时对SQL语句做了哪些优化?
	比如,select语句务必指明字段名称,不要直接使用select *;
	注意SQL语句避免索引失效的写法;
		如果是聚合查询,尽量用union all 代替 unionunion会多一次过滤,效率比较低;
		如果是表关联的话,尽量使用innerjoin,不要使用left joinright join,如必须使用一定要以小表为驱动。

事务-事务的特性

image-20241028174746536

ACID

image-20241028175121725

面试总结

事务的特性?
	原子性(Atomicity):原子操作,要么全部成功,要么全部失败
	一致性(Consistency):以转账为例,转账的过程中,数据要保持一致性,A减少多少,B增加多少。
	隔离性(Isolation):当前事务的执行,不受其他事务的干扰
	持久性(Durability):事务提交后,要把数据进行持久化(可以说落盘操作)

事务-并发事务问题、隔离级别

image-20241028175547924

并发事务问题

image-20241028175646222

image-20241028175805805

image-20241028175903255

image-20241028180047423

隔离级别

image-20241028180246708

面试总结

Q1:并发事务带来哪些问题?
	1、脏读:一个事务读到另一个事务还没有提交的数据
	2、不可重复读:一个事务先后读取同一条记录,但两次读取的数据不同
	3、幻读:一个事务按照条件查询数据时,没有对应的数据行;但是在插入数据时,又发现这行数据已经存在,好像出现了“幻影”
	
	
Q2:怎么解决这些问题?Mysql的默认隔离级别是什么?
	解决方案是对这些事务进行隔离
	1、未提交读(一个事务可以读取到另一个事务未提交的数据):解决不了刚才提出的所有问题,一般项目中也不用这个
	2、读已提交(一个事务只能读取到另一个事务已经提交的数据):能解决脏读的问题,解决不了不可重复读和幻读
	3、可重复读(在一个事务内,多次读取同一数据会得到相同的结果,不管其他事务是否对该数据进行了修改并提交):能解决脏读和不可重复读,解决不了幻读(Mysql默认隔离级别)
	4、串行化(事务是串行执行的,一个事务必须等待另一个事务完成后才能开始):能解决提出的所有问题,但由于事务串行执行,性能比较低
一般选取 可重复读 作为隔离级别
注意:事务隔离级别越高,数据越安全,性能越低

事务-undo log 和 redo log的区别

image-20241029140535582

redo log

image-20241029141324506

undo log

image-20241029141455546

面试总结

undo logredo log 的区别?
	其中redo log日志记录的是数据页的物理变化,服务宕机可用来同步数据。而undo log记录的是逻辑日志,当事务回滚时,通过逆操作恢复原来的数据,比如我们删除一条数据的时候,就会在undo log日志文件中新增一条delete语句,如果发生回滚就执行逆操作。
	redo log保证了事务的持久性;(例如,一个事务将表中某条记录的某个字段值从 1 更新为 2,这个更新操作被记录在 Redo Log 中。如果在更新操作完成后系统崩溃,重启后系统可以根据 Redo Log 中的记录,重新执行这个更新操作,使得数据库中的数据和事务提交后的状态一致。)
	undo log保证了事务的原子性(例如,一个事务包含两个操作:插入一条记录和更新另一条记录。如果在更新操作时发生错误,数据库可以根据 Undo Log 中插入操作之前的数据状态,撤销插入操作,从而保证事务的原子性,即整个事务好像从未执行过一样。)和一致性(例如,在转账事务中,从一个账户扣除金额后,在未将金额添加到另一个账户之前,数据是不一致的。Undo Log 可以在需要时(如事务回滚)将数据恢复到事务开始前的一致状态。同时,Undo Log 还可以帮助维护数据的完整性约束。如果插入的数据违反了主键约束,通过 Undo Log 可以撤销插入操作,从而保持数据库的一致性。)。
	
Redo Log(重做日志)是 InnoDB 存储引擎用于保证事务持久性的一种日志。它记录了事务对数据库所做的修改操作,包括插入、更新和删除等操作的信息。这些信息以日志的形式存储在磁盘上。
Undo Log(回滚日志)主要用于记录事务执行过程中对数据的修改前的旧值,它是为了实现事务的回滚操作而存在的。

事务-解释一下MVCC

解释一下MVCC

image-20241029142235793

MVCC-实现原理

记录中的隐藏字段

image-20241030133857955

undo log

image-20241030134104779

undo log版本链

image-20241030134508102

readview

image-20241030135019032

image-20241030135159524

说明:这个地方好像目前不需要掌握

image-20241030135558589

RC(READ COMMITTED)情况下

image-20241030140208416

RR情况下

image-20241030140353986

面试总结

事务的隔离性如何保证?(解释一下MVCC)
	事务的隔离性是由锁和MVCC实现的
	其中MVCC含义是多版本并发控制。指维护一个数据的多个版本,使得读写操作没有冲突,它的底层实现主要分为三个部分:
	第一个是隐藏字段:在Mysql中给每个表都设置了隐藏字段,有一个是trx_id(事务id),记录每一次操作的事务id,是自增的;另一个字段是roll_pointer(回滚指针),指向上一个版本的事务版本记录地址。
	第二个是undo log日志:记录回滚日志,存储老版本数据,在内部会形成一个版本链,在多个事务并行操作某一个记录,记录不同事务修改数据的版本,通过roll_pointer指针形成一个链表。(不同事务或相同事务对同一条记录进行修改,会导致该记录的undo log生成一条记录版本链表,链表的头部是最新的旧记录,链表的尾部是最早的旧记录)
	
	第三个是readView读视图:解决一个事务查询选择版本的问题,在内部定义了一些匹配规则和当前的一些事务id,判断该访问哪个版本的数据,不同的隔离级别快照读是不一样的,最终的访问结果不一样。
	如果是RC(读已提交)隔离级别,每一次执行快照读时生成ReadView (在这种隔离级别下,每一次执行快照读(一种一致性非锁定读)时都会生成一个新的 ReadView。这是因为在 “读已提交” 隔离级别下,要保证每次读取的数据都是已提交的最新数据。)(例如,事务 A 在时间点 T1 读取数据项 X 的值为 10,在 T2 时刻,另一个事务 B 修改了 X 的值并提交,当事务 A 在 T3 时刻再次读取 X 时,根据新生成的 ReadView,会读取到修改后的值,比如 15。); 
	如果是RR(可重复读)隔离级别,仅在事务中第一次执行快照读时生成ReadView,后续复用。(在这个隔离级别下,仅在事务中第一次执行快照读时生成 ReadView,后续的快照读操作复用这个 ReadView。这样做的目的是为了保证在一个事务内,多次读取同一数据会得到相同的结果。)(例如,事务 A 在第一次快照读时生成了 ReadView,在事务处理过程中,即使其他事务对数据进行了修改并提交,事务 A 后续的快照读操作由于复用了最初的 ReadView,依然会读取到和第一次相同的数据版本,从而保证了数据读取的可重复性。)

MySQL-主从同步原理

image-20241030141506001

主从同步原理

image-20241030141808393

面试问答

说一下主从同步的原理?
	Mysql主从复制的核心就是二进制日志,二进制日志记录了所有的DDL(数据定义语言)语句和DML(数据操纵语言)语句。
具体的主从同步流程:
	1、Master主库在事务提交时,会把数据变更记录在二进制日志文件Binlog中
	2、从库读取主库的二进制日志文件Binlog,写入到从库的中继日志Relay Log
	3、slave重新执行中继日志中的事件,将从库中的数据和主库保持一致

MySQL-分库分表

image-20241030142220488

拆分策略

image-20241030142330212

垂直拆分

垂直分库

image-20241030142535566

垂直分表

image-20241030142826102

水平拆分

水平分库

image-20241030143123223

水平分表

image-20241030143301159

分库分表的策略有哪些

image-20241030143633399

面试总结

Q1:为什么要分库分表?
	读写分离分散了数据库读写操作的压力,但没有分散存储压力。当数据量达到千万或上亿条的时候,单台数据库服务器的存储能力会成为系统的瓶颈,主要体现在几个方面:
	1、数据量太大,读写的能力会下降,即使有索引,索引也会变得很大,性能同样会下降。
	2、数据文件会变得很大,数据库备份和恢复需要耗费很长时间。
基于上述原因,单个数据库服务器存储的数据量不能太大,需要控制在一定的范围内。为了满足业务存储的需求,就需要将存储分散到多台数据库服务器上。常见的分散存储的方法“分库分表”,包括“分库”和“分表”两大类。


Q2:分库分表的四种策略?
业务分库: 按照业务模块将数据分散到不同的数据库服务器
水平分表和垂直分表:

image-20241222204928005

Q3:分库分表的使用方案
	ShardingSphere 绝对可以说是当前分库分表的首选!ShardingSphere 的功能完善,除了支持读写分离和分库分表,还提供分布式事务、数据库治理、影子库、数据加密和脱敏等功能。
	
	
Q4:分库分表后,数据怎么迁移?
	比较简单同时也是非常常用的方案就是停机迁移,写个脚本老库的数据写到新库中。比如你在凌晨 2 点,系统使用的人数非常少的时候,挂一个公告说系统要维护升级预计 1 小时。然后,你写一个脚本将老库的数据都同步到新库中。
	如果你不想停机迁移数据的话,也可以考虑双写方案。双写方案是针对那种不能停机迁移的场景,实现起来要稍微麻烦一些。具体原理是这样的:
1、我们对老库的更新操作(增删改),同时也要写入新库(双写)。如果操作的数据不存在于新库的话,需要插入到新库中。 这样就能保证,咱们新库里的数据是最新的。
2、在迁移过程,双写只会让被更新操作过的老库中的数据同步到新库,我们还需要自己写脚本将老库中的数据和新库的数据做比对。如果新库中没有,那咱们就把数据插入到新库。如果新库有,旧库没有,就把新库对应的数据删除(冗余数据清理)。
3、重复上一步的操作,直到老库和新库的数据一致为止。
想要在项目中实施双写还是比较麻烦的,很容易会出现问题。我们可以借助上面提到的数据库同步工具 Canal 做增量数据迁移(还是依赖 binlog,开发和维护成本较低)。
posted @   墨羽寻觅  阅读(7)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤
点击右上角即可分享
微信分享提示