文章目录
- Q：mysql架构设计
- Q：SQL语句的更新流程
- Q：undo log、redo log、binlog的区别？
- Q：buffer pool是什么？
- Q：buffer pool的内存结构
- Q：Free list 、Flush list、LRU list分别是什么？
- Q：一行数据在磁盘是如何存储的？变长字段怎么存？Null字段怎么存？
- Q：redo log机制，为什么写redo log文件刷盘性能不受影响？
- Q：redo log是怎么写入文件的？
- Q：redo log buffer什么时候刷入硬盘？
- Q：redo log文件满了怎么办？
- Q：undo log回滚日志是什么？
- Q：多事务并发问题中的脏写、脏读、不可重复读、幻读分别是什么？
- Q：4个隔离级别分别是什么？
- Q：undo log版本链是什么？
- Q：ReadView机制是什么？
- Q：mysql的RC和RR的实现有何不同？
- Q：ReadView的可见性判断？
- Q：mvcc是什么？
- Q：数据页是如何存储的？
- Q：物理存储时页分裂是什么？
- Q：什么是聚簇索引？
- Q：二级索引是怎么查找数据的？
- Q：一个表里索引是不是越多越好？
- Q：最左匹配规则、范围查找规则、等值匹配+范围匹配规则？
- Q：排序时如何才能使用索引？
- Q：分组时如何才能使用索引？
- Q：覆盖索引指的是什么？
- Q：有哪些设计索引的原则？
- Q：执行计划有哪些类型？
- Q：mysql是如何对一个查询语句的多个执行计划评估成本的？
- Q：explain获取执行计划会返回哪些信息？
Q：mysql架构设计
有四大部分，解析器、优化器、执行器、存储引擎。
Q：SQL语句的更新流程
更新语句：update users set name=‘xxx’ where id=10;
1.将要修改的数据加载到缓存。
2.写undo log日志文件，用于回滚需要。
3.更新缓存中的数据。
4.redo log buffer（注意是缓存中），避免系统宕机造成数据丢失，用来恢复更新过的数据。
说明：<目前尚未提交事务>
<如果系统崩溃，内存中修改的数据和redo log buffer都会丢失，内存和硬盘都是旧数据>
5.提交事务时将redo log buffer写入硬盘，配置策略由innodb_flush_log_at_trx_commit参数指定。
说明：<0：不刷硬盘、1：实时刷盘、2：异步刷盘。必须设置为1>
<如果系统崩溃，重启后可以根据硬盘redo log文件恢复之前做过的修改>
6.提交事务时同时写入binlog日志文件。
说明：binlog为归档日志；redo log为重做日志，是innodb特有。
7.将修改的binlog的文件名称及位置，写入到redo log日志文件里。同时在redo log日志文件中写入一个commit标记。此时才真正完成了事务的提交。
说明：commit标记的意义，保持redo log日志和binlog日志的一致。
提交事务时共完成5/6/7三个步骤。
假设步骤5时宕机，因为没有commit标记可以判定为事务提交失败，避免了出现有redo log没有binlog的情况。
假设步骤6时宕机，即使有redo log和binlog，但是没有commit标记，同样判定为失败。
commit标记，用来保证redo log和binlog的同步，保证两者完全一致。
8.后台IO线程随机将脏数据刷回磁盘
Q：undo log、redo log、binlog的区别？
undo log：用于回滚
redo log：重做日志，innodb存储引擎特有
binlog：归档日志
Q：buffer pool是什么？
数据库的内存组件，对磁盘真实数据的缓存。
Q：buffer pool的内存结构
buffer pool的内存结构是由缓存页和描述数据组成，默认一页为16kb，每个数据页都包含了描述信息，有表空间、编号、地址等。
Q：Free list 、Flush list、LRU list分别是什么？
Free list：控制空闲缓存页的分配
Flush list：存储脏页
LRU list：控制缓存淘汰
Q：一行数据在磁盘是如何存储的？变长字段怎么存？Null字段怎么存？
存储时有一些头字段对这一行数据进行描述，然后再放上这一行数据每一列的值，这就是行格式。
如果是变长字段，每一行数据前面都补充一个描述，说明变长字段列表。Null字段也是同理。
Q：redo log机制，为什么写redo log文件刷盘性能不受影响？
redo log保证了数据不会丢失。它的文件格式：对表空间xx中的数据页xx中的偏移量为xxx的地方更新了数据xxx。
写redo log文件，和将缓存页刷入硬盘，同样是刷盘，性能差异怎么这么大？
1.redo log文件小，几十个byte，而缓存页是16kb一页
2.redo log是顺序写，而缓存页是随机写。
Q：redo log是怎么写入文件的？
通过redo log block写入，一个512kb
Q：redo log buffer什么时候刷入硬盘？
1.redo log buffer占据总redo log buffer的一半时。（不常见）
2.事务提交时（常见）
3.后台定时刷新
4.mysql关闭时
Q：redo log文件满了怎么办？
默认2个。ib_logfile0和ib_logfile1，分别是48MB，最大96MB，循环写。
Q：undo log回滚日志是什么？
分别存储对应的恢复语句。
insert->delete，update->记录修改前的值，delete->insert。
Q：多事务并发问题中的脏写、脏读、不可重复读、幻读分别是什么？
脏写：A提交a，B提交b，A回滚后b值被脏写。（刚写的数据没了）
脏读：事务B查询了事务A回滚前的数据。
说明：无论脏写还是脏读，都是因为查询了另一个没有提交的事务修改了的数据。->由 读未提交引起的
不可重复读：A事务开启中，B、C事务提交了修改，A事务中不同时段对同一条记录查询的结果不同。
幻读：一个事务中同一个SQL多次查询，结果查询到了之前没出现过的数据。如事务A第一次select * from t where id > 0;结果为10条，事务B插入2条后，A事务中继续查询结果为12条。
Q：4个隔离级别分别是什么？
读未提交：不会出现脏写，可能出现脏读、不可重复读、幻读（不靠谱，很少使用）
读已提交：不会出现脏写、脏读，可能出现不可重复读、幻读
可重复读：不会出现脏写、脏读、不可重复读，可能出现幻读
可串行化：不允许并发出现。（性能差，很少使用）
mysql通过 TRANSACTION ISOLATION LEVEL设置隔离级别。并且mysql的“可重复读”可以通过MVCC机制避免不可重复读和幻读。
Q：undo log版本链是什么？
多个事务串行执行时，修改的每行数据都会更新隐藏字段trx_id和roll_pointer。
trx_id代表更新该行数据的事务ID。
roll_pointer指向undo log的生成链表。
Q：ReadView机制是什么？
执行一个事务的时候，就会生成一个ReadView，里面有4个关键的字段：
1.m_ids，有哪些事务在mysql里执行还没有提交的。
2.min_trx_id，m_ids里的最小值
3.max_trx_id，下一个要生成的事务ID
4.creator_trx_id，当前事务ID
通过undo log多版本链路，加上开启事务时生成的ReadView，就可以知道读取哪个版本的数据。
Q：mysql的RC和RR的实现有何不同？
一个很大的不同就是：生成ReadView 的时机不同
RC：每次查询都生成一个ReadView。
RR：第一次查询时产生一个ReadView，后面直接复用。
Q：ReadView的可见性判断？
版本链比较规则：
1、如果trx-id < min-id(绿色部分),表示这个版本是已提交的事务生成的，这个数据是可见的
2、如果trx-id > max-id(红色部分)，表示这个版本是由将来启动的事务生成的，那肯定是不可见的
3、如果min-id <= trx-id <= max-id(黄色部分)，分如下两种情况：
a：若row的trx-id在数组中，表示这个版本是由还没提交的事务生成的，此时不可见（当前在自己的事务中是可见的）；
b：若row的trx-id不在数组中，表示这个版本是已经提交了的事务生成的，可见
Q：mvcc是什么？
多版本并发控制，主要通过ReadView和undo log版本链来控制。
Q：数据页是如何存储的？
数据页之前组成一个双向链表进行存储，内部的数据行是单向链表存储，根据主键ID排序。
没有索引时只能通过全表扫描进行查找。
Q：物理存储时页分裂是什么？
挪动数据页里的数据，保证数据的顺序从小到大递增。
Q：什么是聚簇索引？
如果索引的B+树同时包含了索引页和数据页，并且叶子节点是存放数据页的，就称之为聚簇索引。默认使用主键来组织。
Q：二级索引是怎么查找数据的？
聚簇索引称为一级索引，普通索引称之为二级索引。
普通索引建立后，会创建一个独立的B+树，叶子节点存放了索引字段和主键ID。查询时分为两步：
1.先在二级索引上查找，找到对应的主键值。
2.根据主键值去聚簇索引中查找，也称为回表。
联合索引与普通索引同理，只是独立的B+树是根据多个字段创建的。
Q：一个表里索引是不是越多越好？
有两个缺点，分别是空间上的和时间上的。
1.空间上，维护B+树索引需要更多的空间。
2.时间上，在执行增删改时维护B+树会有一定的时间消耗。
Q：最左匹配规则、范围查找规则、等值匹配+范围匹配规则？
索引key（class_name、student_name、subject_name）
最左匹配规则：
select * from student_score where class_name = ‘’ and student_name = ‘’ (走索引，class_name+student_name)
select * from student_score where subject_name = ‘’ （不走索引）
select * from student_score where class_name = ‘’ and subject_name = ‘’ （走class_name的索引）
范围查找规则：只对最左侧的列进行范围查找时生效
select * from student_score where class_name > ‘’ and class_name < ‘’ （走索引）
select * from student_score where class_name > ‘’ and class_name < ‘’ and student_name > ‘’ （class_name走索引,student_name不走）
等值匹配+范围匹配规则：
select * from student_score where class_name = ‘’ and student_name > ‘’ and subject_name < ‘’ (student_name走索引，subject_name不走)
Q：排序时如何才能使用索引？
按照联合索引的顺序进行排序，因为索引树里的有序性就是按联合索引顺序排序的。多个字段时同时保持顺序或逆序。
Q：分组时如何才能使用索引？
和排序时一样，字段顺序和联合索引最左侧开始的字段顺序一致。
Q：覆盖索引指的是什么？
需要的字段值直接在索引树里就能提取出来，不需要回表到聚簇索引，这种查询方式称为覆盖索引。
Q：有哪些设计索引的原则？
1.设计表后可以先不着急建索引，等系统编码后再结合查询语句来创建索引。
2.选择基数比较大的字段，基数小发挥不了索引的作用。如果字段值比较大可以考虑使用前缀索引如name(20)作为索引。
3.避免函数计算引起索引失效。主键保持自增，避免使用UUID之类的，避免频繁的页分裂。
Q：执行计划有哪些类型？
const：最快的一种，主键索引和唯一索引时。
ref：普通索引（另有ref_or_null：普通索引+is null的判断。）
range：普通索引+范围搜索
index：只需要扫描二级索引而不需要回表到聚簇索引时。例如key(x1,x2,x3)，select x1,x2,x3 from table where x2 = ‘’
all：全表扫描
Q：mysql是如何对一个查询语句的多个执行计划评估成本的？
主要成本来自两块。
一个是从磁盘读数据到内存的IO成本，可以近似理解为读1页数据的成本为1.0。
一个是CPU计算的成本，可以近似理解查询和检测一条数据的成本为0.2。
show table status like ‘表名’;
rows：表里的记录数，data_length：表的聚簇索引的字节数大小，用data_length除以1024 就是kb为单位的大小，然后再除以16kb(默认一页的大小)，就是有多少页，
Q：explain获取执行计划会返回哪些信息？
id：每个执行计划都有一个唯一的ID
select_type：查询类型，
table：查询哪些表
partition：表分区
type：类型，可选值：const/ref/range/index/all
possible_keys：有哪些索引可供选择
key：实际选择的索引
key_length：索引的长度
ref：等值匹配的一些信息
rows：大概读取了多少条行
filtered：过滤数据的百分比
extra：额外信息
359
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
