MySQL 篇
MySQL 篇
整体内容
索引结构剖析
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索引将所有的哈希码存储在索引中,同时在索引表中保存指向每个数据行的指针。
4.2、B-Tree索引
B-Tree能加快数据的访问速度,因为存储引擎不再需要进行全表扫描来获取数据,数据分布在各个节点之中。
4.3、B+Tree索引
是B-Tree的改进版本,同时也是数据库索引所采用的存储结构。数据都在叶子节点上,并且增加了顺序访问指针,每个叶子节点都指向相邻的叶子节点的地址。相比B-Tree来说,进行范围查找时只需要查找两个节点,进行遍历即可。而B-Tree需要获取所有节点,相比之下B+Tree效率更高。
非叶子节点存储的是指针,数据全部存储在叶子节点上。并且叶子节点是通过双向链表进行连接。
5、为什么索引结构默认使用B+Tree,而不是B-Tree,Hash,二叉树,红黑树?
5.1、B-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的查询优化器会帮你优化成索引可以识别的形式。
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 字段,在一定程度上有更好的区分度。我们可能还会继续尝试其他前缀长度,比如长度为 8、10 等(通过修改 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=’95054′ AND 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';
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 。
3、table:每个查询对应的表名 。
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:查询器预测满足下一次查询条件的百分比 。
8、rows 也是一个重要的字段. MySQL 查询优化器根据统计信息, 估算 SQL 要查找到结果集需要扫描读取的数据行数.
这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好。
9、extra:表示额外信息,如Using where,Start temporary,End temporary,Using temporary等。
13、为什么官方建议使用自增长主键作为索引?
结合B+Tree的特点,自增主键是连续的,在插入过程中尽量减少页分裂,即使要进行页分裂,也只会分裂很少一部分。并且能减少数据的移动,每次插入都是插入到最后。总之就是减少分裂和移动的频率。
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,DELETE,UPDATE将为此多付出4,5 次的磁盘I/O。 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能,索引范围查询(INDEX RANGE SCAN)适用于两种情况:
1、基于一个范围的检索,一般查询返回结果集小于表中记录数的30%。
2、基于非唯一性索引的检索。
18、什么情况下不走索引(索引失效)?
1、使用!= 或者 <> 导致索引失效
2、类型不一致导致的索引失效
3、函数导致的索引失效
SELECT * FROM `user` WHERE DATE(create_time) = '2020-09-03';
如果你的索引字段使用了函数,对不起,他是真的不走索引的。
4、运算符导致的索引失效
SELECT * FROM `user` WHERE age - 1 = 20;
如果你对列进行了(+,-,*,/,!), 那么都将不会走索引。
5、OR引起的索引失效
SELECT * FROM `user` WHERE `name` = '张三' OR height = '175';
OR导致索引是在特定情况下的,并不是所有的OR都是使索引失效,如果OR连接的是同一个字段,那么索引不会失效,反之索引失效。
6、模糊搜索导致的索引失效
SELECT * FROM `user` WHERE `name` LIKE '%冰';
当%放在匹配字段前是不走索引的,放在后面才会走索引。
7、NOT IN、NOT EXISTS导致索引失效
优化-如何定位慢查询
如何定位慢查询
面试总结
MySQL中如何定位慢查询?
1、可以部署像Skywalking这样的监控系统,它能够在展示的报表中看到是哪一个接口比较慢,并且可以看到是接口的哪一部分比较慢,也可以看到SQL的具体执行时间,方便定位是哪个SQL出了问题。
2、也可以使用MySQL提供的慢日志查询功能,可以在MySQL的系统配置文件中开启这个慢日志的功能以及设置SQL执行超过多少时间来记录到一个日志文件中,一般设置的可能是2秒,通过这种方式就可以在日志文件中找到执行比较慢的SQL了。
优化-SQL语句执行的很慢,如何分析
解决思路
面试总结
SQL语句执行很慢,如何分析?
如果一条SQL执行很慢的话,可以采用MySQL自带的分析工具explain来查看这条语句的执行情况:
1、可以通过key和key_len检查是否命中了索引,如果本身添加了索引,也可以判断索引是否有失效的情况。
2、可以通过type字段查看SQL是否有进一步的优化空间,是否存在全索引扫描或全盘扫描。
3、通过extra建议判断,是否出现了回表的情况,如果出现了,可以尝试添加索引或修改返回字段来修护。
优化-索引概念及索引底层数据结构
数据结构对比
面试总结
Q1:什么是索引?
索引是帮助MySQL高效获取数据的数据结构,主要用来提高数据检索的效率,降低数据库的IO成本(不需要全表扫描),同时通过索引列对数据进行排序,降低数据排序的成本以及CPU的消耗(例如,对于一个整数列建立的索引,这些整数在索引树的叶子节点中是按照从小到大的顺序排列的。当我们创建索引时,数据库会将索引列的值进行排序后存储在索引结构中。)(更通俗的说,索引就相当于目录。为了方便查找书中的内容,通过对内容建立索引形成目录。)
Q2:索引的底层数据结构
Mysql默认的存储引擎InnoDB采用的B+树的数据结构来存储索引,选择B+树的主要原因:
1、阶数更多,路径更短
2、磁盘读写代价B+树更低,非叶子节点只存储指针,叶子节点存储数据
3、B+树便于扫库和区间查询,叶子节点是一个双向链表
Q3:B树和B+树的区别是什么?
1、在B树中,非叶子节点和叶子节点都会存储数据,而B+树所有的数据都会出现在叶子节点;在查询的时候,B+树查找效率更加稳定
2、在范围查询的时候,B+树效率更高,因为B+树都在叶子节点存储,并且叶子节点是一个双向链表。
优化-聚簇索引、非聚簇索引、回表查询
回表查询
面试总结
Q1:聚簇索引和非聚簇索引
聚簇索引:数据和索引放到一起,B+树叶子节点保存整行数据,有且只有一个;一般情况下主键作为聚簇索引
非聚簇索引(二级索引):数据和索引分开存储,B+树叶子节点保存对应主键,可以有多个;一般我们自己定义的索引都是非聚簇索引
Q2:回表查询
通过二级索引找到对应的主键值,到聚集索引中查询整行数据,这个过程就是回表
优化-覆盖索引、超大分页优化
覆盖索引
超大分页
面试总结
Q1:覆盖索引
覆盖索引是指select查询语句使用了索引,返回的列必须在索引中全部能够找到
使用id查询,直接走聚簇索引查询,一次索引扫描,直接返回数据,性能高
如果按照二级索引查询数据的时候,返回的列中没有创建索引,有可能会触发回表查询,尽量避免使用select *,尽量在返回的列中包含添加索引的字段。
Q2:Mysql超大分页怎么处理?
超大分页一般都是在数据量比较大时,我们使用了limit分页查询,并且需要对数据进行排序,这个时候效率就很低,我们可以采用覆盖索引和子查询来解决。
先分页查询数据的id字段,确定了id之后,再用子查询来过滤,只查询这个id列表中的数据就可以了。因为查询id的时候,走的覆盖索引,所以效率可以提升很多。
下面给出解释:
优化-索引创建原则
面试总结
Q:索引创建原则有哪些?
一般的大前提都是表中的数据要超过10万以上,我们才会创建索引;并且添加索引的字段都是查询比较频繁的字段,一般像查询条件、排序字段或分组字段这些。
创建索引的时候都是使用组合索引来创建,一条SQL的返回值,尽量使用覆盖索引,如果字段的区分度不高的话,也会把其放在组合索引的后面字段。
如果某一个字段内容较长,可以考虑使用前缀索引,当然并不是所有的字段都要添加索引,索引的数量需要控制,因为添加索引会导致增改的速度变慢。
优化-什么情况下索引会失效
索引没有问题的情况
索引失效的情况
面试总结
什么情况下索引会失效?
1、违反最左前缀法则
2、范围查询右边的列,不能使用索引
3、不要在索引列上进行运算操作,否则索引将失效
4、字符串不加单引号,照成索引失效(类型转换)
5、以%开头的Like模糊查询,索引失效
优化-谈谈你对SQL优化的经验
面试总结
Q1:SQL优化经验
SQL优化的话,一般会从这几方面考虑,比如建表的时候、使用索引、SQL语句的编写,主从复制,读写分离,还有一个如果量比较大的话,可以考虑分库分表。
Q2:创建表的时候如何优化?
定义字段的时候需要结合字段的内容来选择合适的类型,如果是数值的话,像tinyint、int、bigint这些类型,要根据实际情况选择。如果是字符串类型,也是结合存储的内容选择char和varchar或者text类型。
Q3:使用索引的时候,如何优化?
参考索引创建原则!
Q4:平时对SQL语句做了哪些优化?
比如,select语句务必指明字段名称,不要直接使用select *;
注意SQL语句避免索引失效的写法;
如果是聚合查询,尽量用union all 代替 union,union会多一次过滤,效率比较低;
如果是表关联的话,尽量使用innerjoin,不要使用left join、right join,如必须使用一定要以小表为驱动。
事务-事务的特性
ACID
面试总结
事务的特性?
原子性(Atomicity):原子操作,要么全部成功,要么全部失败
一致性(Consistency):以转账为例,转账的过程中,数据要保持一致性,A减少多少,B增加多少。
隔离性(Isolation):当前事务的执行,不受其他事务的干扰
持久性(Durability):事务提交后,要把数据进行持久化(可以说落盘操作)
事务-并发事务问题、隔离级别
并发事务问题
隔离级别
面试总结
Q1:并发事务带来哪些问题?
1、脏读:一个事务读到另一个事务还没有提交的数据
2、不可重复读:一个事务先后读取同一条记录,但两次读取的数据不同
3、幻读:一个事务按照条件查询数据时,没有对应的数据行;但是在插入数据时,又发现这行数据已经存在,好像出现了“幻影”
Q2:怎么解决这些问题?Mysql的默认隔离级别是什么?
解决方案是对这些事务进行隔离
1、未提交读(一个事务可以读取到另一个事务未提交的数据):解决不了刚才提出的所有问题,一般项目中也不用这个
2、读已提交(一个事务只能读取到另一个事务已经提交的数据):能解决脏读的问题,解决不了不可重复读和幻读
3、可重复读(在一个事务内,多次读取同一数据会得到相同的结果,不管其他事务是否对该数据进行了修改并提交):能解决脏读和不可重复读,解决不了幻读(Mysql默认隔离级别)
4、串行化(事务是串行执行的,一个事务必须等待另一个事务完成后才能开始):能解决提出的所有问题,但由于事务串行执行,性能比较低
一般选取 可重复读 作为隔离级别
注意:事务隔离级别越高,数据越安全,性能越低
事务-undo log 和 redo log的区别
redo log
undo log
面试总结
undo log 和 redo 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
MVCC-实现原理
记录中的隐藏字段
undo log
undo log版本链
readview
说明:这个地方好像目前不需要掌握
RC(READ COMMITTED)情况下
RR情况下
面试总结
事务的隔离性如何保证?(解释一下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-主从同步原理
主从同步原理
面试问答
说一下主从同步的原理?
Mysql主从复制的核心就是二进制日志,二进制日志记录了所有的DDL(数据定义语言)语句和DML(数据操纵语言)语句。
具体的主从同步流程:
1、Master主库在事务提交时,会把数据变更记录在二进制日志文件Binlog中
2、从库读取主库的二进制日志文件Binlog,写入到从库的中继日志Relay Log
3、slave重新执行中继日志中的事件,将从库中的数据和主库保持一致
MySQL-分库分表
拆分策略
垂直拆分
垂直分库
垂直分表
水平拆分
水平分库
水平分表
分库分表的策略有哪些
面试总结
Q1:为什么要分库分表?
读写分离分散了数据库读写操作的压力,但没有分散存储压力。当数据量达到千万或上亿条的时候,单台数据库服务器的存储能力会成为系统的瓶颈,主要体现在几个方面:
1、数据量太大,读写的能力会下降,即使有索引,索引也会变得很大,性能同样会下降。
2、数据文件会变得很大,数据库备份和恢复需要耗费很长时间。
基于上述原因,单个数据库服务器存储的数据量不能太大,需要控制在一定的范围内。为了满足业务存储的需求,就需要将存储分散到多台数据库服务器上。常见的分散存储的方法“分库分表”,包括“分库”和“分表”两大类。
Q2:分库分表的四种策略?
业务分库: 按照业务模块将数据分散到不同的数据库服务器
水平分表和垂直分表:
Q3:分库分表的使用方案
ShardingSphere 绝对可以说是当前分库分表的首选!ShardingSphere 的功能完善,除了支持读写分离和分库分表,还提供分布式事务、数据库治理、影子库、数据加密和脱敏等功能。
Q4:分库分表后,数据怎么迁移?
比较简单同时也是非常常用的方案就是停机迁移,写个脚本老库的数据写到新库中。比如你在凌晨 2 点,系统使用的人数非常少的时候,挂一个公告说系统要维护升级预计 1 小时。然后,你写一个脚本将老库的数据都同步到新库中。
如果你不想停机迁移数据的话,也可以考虑双写方案。双写方案是针对那种不能停机迁移的场景,实现起来要稍微麻烦一些。具体原理是这样的:
1、我们对老库的更新操作(增删改),同时也要写入新库(双写)。如果操作的数据不存在于新库的话,需要插入到新库中。 这样就能保证,咱们新库里的数据是最新的。
2、在迁移过程,双写只会让被更新操作过的老库中的数据同步到新库,我们还需要自己写脚本将老库中的数据和新库的数据做比对。如果新库中没有,那咱们就把数据插入到新库。如果新库有,旧库没有,就把新库对应的数据删除(冗余数据清理)。
3、重复上一步的操作,直到老库和新库的数据一致为止。
想要在项目中实施双写还是比较麻烦的,很容易会出现问题。我们可以借助上面提到的数据库同步工具 Canal 做增量数据迁移(还是依赖 binlog,开发和维护成本较低)。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤