MySQL相关面试必备知识
大多数内容参考小林coding和B站上黑马讲的视频,下面都是基于自己理解写的,如有问题,可留言,第一次在CSDN写博客,格式不太好。
1.事务
事务是一条或多条sql语句,需要同时失败或同时成功
原子性: 所有指令要么全部成功,要么全部失败(失败通过undo log日志回滚到之前状态)。
一致性:事务执行前后整个数据是完整的。(如字段唯一、金额总量不变等)
隔离性:多个事务不能发生干扰,要相互隔离(通过undo log日志保证)。
持久性:事务提交后,对数据的改变是永久的(操作记录到磁盘的redo log日志中)。
2.Redo log 和 Undo log
Redo (重做日志): 数据库发生意外时,通过redo日志文件恢复。 事务提交时都会将数据刷新到磁盘。是一种物理日志。保证持久性。
Undo (撤销回滚): 是一种逻辑日志,作为事务回滚的快照读参考,和用于MVCC(多版本并发控制)。
undo log是逻辑日志,对事务回滚时,只是将数据库逻辑地恢复到原来的样子,而redo log是物理日志,记录的是数据页的物理变化,所以undo log不是redo log的逆过程。SELECT操作并不会修改任何记录,所以并不需要记录相应的undo日志。
3.Innodb行记录格式
rowId: 行唯一标识,用于B+树索引。
trx_id: 事务对该行记录修改时,就会把该事务的事务id记录在该隐藏列中。
roll_pointer: 对每条记录改动时,都会把旧版本内容写入undo日志,roll_pointer就指向这个旧版本记录。
如下图所示,形成一个版本链就是MVCC:
4.ReadView机制
ReadView是一个保存事务ID的list列表,记录当前事务执行时,还有哪些事务在执行,包含四个字段。
在访问某条记录时,只需要按照下边的步骤判断该记录在版本链中的某个版本(trx_id)是否可见:
1、trx_id < m_ids列表中最小的事务id
表明生成该版本的事务在生成ReadView前已经提交,所以该版本可以被当前事务访问。
2、trx_id > m_ids列表中最大的事务id
表明生成该版本的事务在生成ReadView 后才生成,所以该版本不可以被当前事务访问。
3、m_ids列表中最小的事务id < trx_id < m_ids列表中最大的事务id
此处比如m_ids为[5,6,7,9,10]
①、若trx_id在m_ids中,比如是6,说明创建 ReadView 时生成该版本的事务还是活跃的,该版本不可以被访问。
②、若trx_id不在m_ids中,比如是8:说明创建 ReadView 时生成该版本的事务已经被提交,该版本可以被访问。
一句话说:当trx_id在m_ids中,或者大于m_ids列表中最大的事务id的时候,这个版本就不能被访问。
参考:https://blog.csdn.net/thesprit/article/details/112970122
5.事务并发执行时数据一致性问题
脏读:读取到其它事务未提交的数据;(共享排他锁解决)
不可重复读:前后读取到的数据不一致;(undo日志)
幻读:前后读取的记录数量不一致。(之前存在突然不存在,或者之前不存在突然存在了)
脏读主要是发生于update操作,解决办法:修改时加排他锁,事务提交后才释放锁,读取数据时加共享锁,防止其它事务修改该数据,读取完释放(在可重复读隔离级别以下是,否则是事务执行完释放)。
事务A和事务B同时修改数据,事务A先查询了数据(释放了S锁),事务B修改了数据,并提交了,那么A再次查询数据就会不同,导致不可重复读。
不可重复读 和 幻读 解决见下面隔离级别。
6.事务隔离级别
括号内表示解决办法
读未提交: 一个事务还没提交时,它做的变更就能被其他事务看到;
读提交: 一个事务提交之后,它做的变更才能被其他事务看到;(Read View + MVCC)
可重复读: 一个事务执行过程中看到的数据,一直跟这个事务启动时看到的数据是一致的,MySQL InnoDB 引擎的默认隔离级别;(Read view + MVCC)
串行化: 会对读到所有记录加上读写锁,在多个事务对这条记录进行读写操作时,如果发生了读写冲突的时候,后访问的事务必须等前一个事务执行完成,才能继续执行;(加读写锁避免并行访问)
注:读已提交 和 可重复读 的 Read View 的创建时机不同。
读已提交 会在每次读取数据前生成一个Read View,可重复 会在事务第一次读的时生成一个Read View之后一直使用这个Read View。
Read View记录当前活跃的ids,顺着undo版本链进行找,找到已提交或者小于等于当前事务id的日志就可以读了,大于当前事务id且活跃的那就顺着版本链继续找。
因为读提交是在每个select时都会生成Read View,所以两次数据可能会不同。
可重复读也是通过间隙锁来锁住间隙和记录本身,可以一定程度上避免幻读。
串行化会在读取的每条记录上都加锁,严格串行,从而避免幻读。
7. 可重复读 + 临建锁 可以完全解决幻读吗(小林coding)
针对快照读(select),通过MVCC解决幻读。(ReadView + undo log 不会加锁)
针对当前读(select … for update 等语句) 通过next-key解决幻读。 – 当前读每次都要查数据库最新记录,并且对数据加锁
可重复读可能会出现幻读的场景:
1.如下表
-- 事务 A
begin; -- 开启事务
select * from t_stu where id = 5; -- 生成read view 并且后续都用这个read view
-- 事务 B
begin;
insert into t_stu values(5, '小美', 18); -- 插入数据5,此时A看不到
commit; -- 提交事务
-- 事务 A
update t_stu set name = '小林coding' where id = 5; -- 更新成功,同时修改该行记录的事务id是A
select * from t_stu where id = 5; -- 5 | 小林coding | 18 看到了数据
-- A
select * from t_test where id > 100 -- 假设3条记录
-- B
insert (200) into t_test -- 插入了一条200的记录 并提交
-- A
select * from t_test where id > 100 for update -- 当前读,得到四条记录
尽量在开启事务之后,马上执行 select … for update 这类当前读的语句,因为它会对记录加 next-key lock,从而避免其他事务插入一条新记录。
8.索引 和 存储引擎
8.1 B+树索引的优点
1.相对于二叉树、红黑树,层级更低,搜索效率高
2.相对于B-树索引,层级更低,因为其非叶子节点只存key,所以每个叶子节点可以存更多的key
3.相对于Hash索引,B+树支持范围匹配和排序等操作。
8.2 MySQL常用存储引擎及其优缺点
存储引擎是存储数据、建立索引、更新/查询等技术的实现方式,针对每张表(一个库中不同表有不同索引引擎),有能力可以自定义。
-- 在创建表时,指定存储引擎
create table 表名 (
字段1 字段1类型 [comment 字段1注释],...
) ENGINE = INNODB [COMMENT 表注释];
常见存储引擎:
InnoDB:三大特性:行锁、外键、事务, 适合于对事务完整性要求高的场景 (Mysql默认的)
MyISAM:不支持事务、只有表锁、访问速度快,适用于少量插入、事务完整性要求不高的场景
Memory: 表结构存储在内存中,是临时表,hash索引,访问快。
8.3 MySQL常使用的索引
- B+Tree索引 最常见的索引类型,大部分引擎都支持B+树索引 (默认)
- Hash索引 底层数据结构是用哈希表实现的,只有精确匹配索引列的查询才有效,不支持范围查询
- R-tree(空间索引) 空间索引是MylSAM引擎的一个特殊索引类型,主要用于地理空间数据类型,通常使用较少
- Full-text(全文索引) 是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES
索引分类
主键索引 针对表中主键创建的索引 默认自动创建,只能由一个 PRIMARY
唯一索引 表中某个列中的值不能重复 可以有多个 UNIQUE
常规索引 快速定位特定数据 可以有多个
全文索引 查找文本中的关键词 可以有多个 FULLTEXT
在InnoDB中,索引也可以分为 聚集 和 二级索引(非聚集索引)
聚集索引:叶子节点存储具体的行数据
二级索引:叶子节点存储的是聚集索引的key,查找行数据,需要在通过key进行聚集索引查找(回表)
聚集索引的key选取规则: 1.主键 2.没有主键就唯一索引 3.都没有自动生成一个rowid作为key
以下SQL语句,哪个执行效率更高?
select * from user where id = 10;
select * from user where name = 'Arm'; -- name是唯一索引
第1条快,直接进行聚集索引查询, 第2条 先进行name的索引查找,再进行回表查询 (select *)
如果第2条语句改成 select id,name from user where name = 'Arm'; 就不用回表查询了, 覆盖索引
联合索引:多个字段联合起来创建索引 如:
create index idx_user_pro_age_sta on tb_user(profession,age,statue);
8.4 SQL性能分析
- 1.show [session|global] status 查看 会话/全局的 增删改查sql语句
- 2.慢查询日志: 在配置文件中配置超过ns的sql语句记录在日志中,便于后续分析
- 3.prefile详情 具体查看每条sql的耗时
- 4.explain执行计划 在select语句前直接加explain
explain中关键字段:
type 连接类型 主键/唯一索引值 就是 const 非唯一索引 ref
possible_key: 可能使用的索引(一个或多个)
key: 实际使用的索引,null表示没有使用索引
key_len: 索引的最大可能长度
rows: 大概可能要查询的行数
filtered: 返回结果的行数 占 读取行数的百分比 (越大越好)
extra: 额外信息 Using index conditon 需要回表查询 Using where; Using index 不需要回表查询
8.5 索引可能失效的场景
- 1.最左前缀匹配
联合索引需要从最左端匹配,且不能跳过某个中间列,否则后面所有失效
create index idx_user_pro_age_sta on tb_user(profession,age,statue);
-- and连接,只要都存在即可,与先后顺序无关
explain select * from tb_user where age = 31 and statue = '0' and profession='软件工程';
-- 丢失profession,最左匹配失效
explain select * from tb_user where age=32 and statue='3';
-- 跳过age,索引只走profession
explain select * from tb_user where profession='软件工程' and statue='3';
- 2.范围查询,索引失效,实际业务中可以使用 >=
-- age失效,索引只走profession
explain select * from tb_user where age > 31 and statue = '0' and profession='软件工程';
-
- 索引列运算导致索引失效
-- phone 索引失效,不走索引
explain select * from tb_user where substring(phone, 10, 1) = '5';
- 4.字符串不加引号 索引失效
explain select * from tb_user where stat = '0'; -- 索引不失效
explain select * from tb_user where stat = 0; -- 索引失效
- 5.模糊查询 只要头模糊就失效
explain select * from tb_user where profession = '软件%'; -- 不失效
explain select * from tb_user where profession = '%软件'; -- 失效
explain select * from tb_user where profession = '%软件%'; -- 不失效
- 6.or连接 有一个字段没有索引就不走索引,必须所有字段都有索引
explain select * from tb_user where id = 10 or age = 23; -- age没有索引, 都不走索引
- 7.数据分布 mysql认为不走索引更快,则不走索引
-- profession只有一小部分数据是null
explain select * from tb_user where profession is null; -- 走索引
explain select * from tb_user where profession is not null; -- 不走索引
-
8.sql提示 使用select告诉mysql使用哪个索引、不用哪个索引
use index: 告诉数据库用哪个索引 (这只是一个建议)
ignore index: 告诉数据库不要用哪个索引
force index: 告诉数据库必须用这个索引 -
9.覆盖索引 select返回的字段都能查询到,不用回表查询 查询计划explain extra中查看
-- 下面一条sql如何创建索引
select id,username,password from tb_user where username = ' itcast';
-- 针对username、password 创建联合索引
- 10.前缀索引
-- 当字段类型为字符串(varchar, text等)时,有时候需要索引很长的字符串,这会让索引变得很大
-- 可以只将字符串的一部分前缀,建立索引
create index idx_xox on table_name(column(n));
8.6 索引设计原则
- 针对于数据量较大,且查询比较频繁的表建立索引。
- 针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引。
- 尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高。
- 如果是字符串类型的字段,字段的长度较长,可以针对于字段的特点,建立前缀索引。
- 尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表,提高查询效率。
- 要控制索引的数量,索引越多,维护索引结构的代价也就越大,会影响增删改的效率。
- 如果索引列不能存储NULL值,请在创建表时使用NOTNULL约束它。当优化器知道每列是否包含NULL值时,它可以更好地确定哪个索引最有效地用于查询。
8.7 索引优化
- 插入数据
-- 1.批量插入
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry'); -- 只需要提交一次事务
-- 2.手动提交事务
-- 3.主键顺序插入 (减少页分裂)
- 主键优化
-- 1.满足业务的前提下,越短越好 (二级索引会占用空间)
-- 2.主键尽量使用 Auto_Increment 顺序插入
-- 3.不用使用身份证、UUID等,它是无序的,防止出现页分裂
-- 4.业务修改尽量不要改主键 (因为需要同时改二级索引)
- order by,group by
-- 字段上建立索引,支持最左前缀匹配
- limit优化
-- limit 2000000,10
从 0-2000010 查询,丢弃前面 0-199999 个数据,太浪费
-- 使用覆盖索引 + 子查询
select s.* from tb_user s,(select id from tb_user order by id limit 10000000,10) a where s.id = a.id;
- count优化
count(字段)<count(主键id)< count(1) := count(*) -- 尽量使用count(*)
- update优化
-- 更新数据时,尽量对索引字段,并且索引不失效,这样加的是行数 否则是表锁
update tb_user set name = 'Kaf' where id = 1; -- 行锁
update tb_user set name = 'Kaf' where name = "sql"; -- name不是索引 加表锁
9 锁
9.1 所有锁概述
这个我用语雀的思维导图画的,图片太大,不好截图
9.2 什么时候添加行级锁
- 行锁(记录锁 Record Lock) 锁定的是该条记录
-- 对读取的记录加共享锁(S型锁)
select ... lock in share mode;
-- 对读取的记录加独占锁(X型锁)
select ... for update;
-- update、delet会添加 x型锁 注: id是索引字段
delete from table where id = 1;
update table .... where id = 1;
- 间隙锁 (Gap Lock) 只存在于可重复读级别下,锁定一个范围,防止幻读
例如:锁定(3,5) 之间的间隙,那么id为4的记录就不能被其他事务访问 (3,5不受影响)
注: 间隙锁是兼容的,可能会导致两个事务出现死锁。 - 临键锁 (Next-key Lock) X型X型临键锁不能共存
record+gap [3,5] 锁定 3,4,5 这几条记录
9.3 加锁默认是临键锁 (前开后闭) ,什么时候会退化成行锁或者间隙锁?
(数据库表图如下)
- 唯一索引等值查询
-- 给id等于1的记录添加上X型行锁 (索引值存在)
select * from user where id = 1 for update;
-- 给(1-5) 范围内添加间隙锁 -- 记录不存在,查找2的左右边界,添加临键锁
select * from user where id = 2 for update;
- 唯一索引范围查询: 默认临键锁,可能会退化为行锁/间隙锁
-- 1.大于且存在情况下: 对 (15,20] 和 (20,+∞] 添加 临键锁
select * from user where id > 15 for update;
-- 2.大于等于情况下: 15等值查询退化为记录锁
-- 即: 15是行锁 (15,20] 和 (20,+∞] 添加 临键锁
select * from user where id >= 15 for update;
-- 3.小于且不存在情况下 (5, 10) 退化为间隙锁
-- (-∞, 1] (1, 5]临键锁 (5, 10) 退化为间隙锁
select * from user where id < 6 for update;
-- 4.小于等于情况下: (-∞, 1] (1, 5] 临键锁
select * from user where id <= 5 for update;
-- 5.小于且存在情况下: (-∞, 1] 临键锁 (1, 5) 间隙锁
select * from user where id < 5 for update;
- 非唯一索引等值查询
非唯一索引会对主键索引和非唯一索引都加锁,会对满足条件的主键索引加锁
-- 1.记录存在的情况
-- 会对 (21,22]添加临键锁(非唯一索引有多个age=22,临键锁是到第一个age=22)
-- 同时 age=22 符合条件查询,对其添加记录锁 同时对 id=10 这条记录添加记录锁
-- 并且对 (22,39) 添加记录锁 防止其他事务再次添加 age=22 锁住了之间的间隙
-- (注: 由于二级索引下id是自增的, 这里对 (id=20, age=39) 前加间隙锁,
-- 那么 插入 (id=3, age=39) 会被阻塞, 而 (id=21, age=39) 插入不会被阻塞)
select * from user where age = 22 for update;
-- 2.记录不存在的情况
-- 会对 (22, 39) 之间添加间隙锁
select * from user where age = 25 for update;
- 非唯一索引范围查询 – 不会退化为间隙锁或行锁
-- 对(21, 22]之间添加临键锁, 22 和 id=10 添加 记录锁 (22,39] (39,+∞) 添加 临键锁
-- 22的插入有可能阻塞,也有可能不会阻塞,和上面 非唯一索引等值查询 情况类似
select * from user where age >= 22 for update;
没有索引的查询对所有记录都加临键锁,等同于给表加了一个锁
总结:索引字段存在会出现记录锁, 不存在就会出现间隙锁
10.面试遇到的问题
4.1 MySQL有哪些存储引擎,区别
4.2 事务的特点
4.3 事务的原子性怎么实现的
4.4 事务的隔离性是什么
4.5 Innodb的索引是怎么实现的
4.6 为什么不用B树 搜索二叉树 红黑树
4.7 为什么树的高度高查询就会更慢?
4.8 索引失效的场景
4.9 执行一个查询语句的执行过程
4.10 知道MySQL执行器优化器吗
4.9 答
1.客户端(运行程序)先通过连接器连接到MySQL服务器
2.连接器通过数据库权限身份验证后,先查询数据库缓存是否存在(之前执行过相同条件的SQL查询),如果有会直接返回缓存中的数据。如果没有则会进入分析器
3.进入分析器后会对查询语句进行语法的分析,判断该查询语句SQL是否存在语法错误,如果存在查询语法错误,会直接返回给客户端错误,如果正确会进入优化器
4.优化器会对查询语句进行优化处理:如:如果一条语句用到了多个索引会判断哪个索引性能更好
5.最终会进入执行器,开始执行查询语句直到查询出满足条件的所有数据,然后进行返回
4.10 答
优化器:1)有多个索引决定使用那个索引 2)多表join的是否,决定各个表的连接顺序
select * from test1 join test2 using(ID) where test1.name=yangguo and test2.name=xiao;
既可以先从表 test1 里面取出 name=yangguo的记录的 ID 值,再根据 ID 值关联到表 test2,再判断 test2 里面 name 的值是否等于 xiao。
也可以先从表 test2 里面取出 name=xiao 的记录的 ID 值,再根据 ID 值关联到 test1,再判断 test1 里面 name的值是否等于 yangguo。
执行效率可能不同,优化器决定使用哪个方案
执行器: 1)先判断用户是否有对该表的执行查询权限,没有返回错误
2)是否命中缓存,返回结果前做权限验证
3)执行器调用InnoDB引擎一行一行判断id值,直到最后一行或找到目标id (没有索引)
11.Mysql实战中用到的技术
11.1 主从复制、读写分离
思路:从库读取主库的日志,再解析日志并应用到自身。 – mysql自身就可以实现
读写分离:主库负责增删改,从库负责查,可以避免由数据更新导致的行锁。
可以基于sharding-jdbc实现。
在两台服务器上安装mysql。
主服务器上创建一个账户,其可以允许从库读取日志。
-- mysql主数据库
-- 登录到mysql数据库
mysql -uroot -proot
-- 创建一个小明账户 并 授予 REPLICATION SLAVE
GRANT REPLICATION SLAVE ON *.* to 'xiaoming'@'%' identified by 'Root@123456';
-- 执行下面语句获取日志位置(File和Position的值),不要执行其它语句,会修改其位置
show master status;
-- mysql从数据库
-- 1.修改 /etc/my.cnf
[mysqld]
server-id=101 #[必须]服务器唯一ID
-- 2.重启mysql
systemctl restart mysqld
-- 3.登录mysql,执行下面sql
change master to master_host='192.168.56.101',master_user='xiaoming',master_password='Root@123456',master_log_file='mysql-bin.000001',master_log_pos=154;
start slave;
-- 4.查看从数据库状态
show slave status;
show slave status\G; # 竖排查看
-- 显示为yes,表示没啥问题
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
使用Sharding-JDBC实现读写分离(1、导依赖 2、配置文件配置读写分离 3、允许Bean覆盖定义)
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC1</version>
</dependency>
spring:
shardingsphere:
datasource:
names:
master,slave
# 主数据源
master:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://192.168.56.101:3306/rw?characterEncoding=utf-8
username: root
password: root
# 从数据源
slave:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://192.168.56.102:3306/rw?characterEncoding=utf-8
username: root
password: root
masterslave:
# 读写分离配置
load-balance-algorithm-type: round_robin #轮询 从库负载均衡策略
# 最终的数据源名称 bean的名称
name: dataSource
# 主库数据源名称
master-data-source-name: master
# 从库数据源名称列表,多个逗号分隔
slave-data-source-names: slave
props:
sql:
show: true #开启SQL显示,默认false 控制可以输出sql
main:
allow-bean-definition-overriding: true
主从复制是有延迟的,对于用户访问量很大,先更新后查询不出最新结果的情况越来越多,需要保证一致性。
使用Sharding-JDBC可以自己增加一条语句,进行强制路由到主库,但是会导致主库压力过大。
● 同个调用线程,执行多条语句,其中一旦发现有非读操作,后续所有读操作均从主库读取。
11.2 分库分表
1.一个服务器网络IO频繁,并且内存有限,其缓冲区有限,频繁换入换出会导致磁盘IO频繁。
2.一个数据库的压力太大。
思路:
- 垂直分库分表:垂直分库:以表为依据,不同业务的表拆分到不同库中。每个库的表结构不同、数据不同、所有库的并集是全量数据。垂直分表:以字段为依据,将不同字段拆分到不同表中。【(id,name,category)、(id, description)】每个表结构不同、数据也不一样,通过(主键/外键)关联,所有表的并集是全量数据。
- 水平分库分表:水平分库:以字段为依据,将一个库的数据拆分到多个库中。每个库的表结构一样、每个库的数据都不一样,所有库的并集是全量数据。水平分表:以字段为依据,将一个表的数据拆分到多个表中。每个表的表结构一样、每个表的数据都不一样,所有表的并集是全量数据。
(例如:有三个分片数据库,可以按照 id%3 的值分别将数据存储在不同数据库上)
实现技术:
ShardingJDBC:基于AOP原理,在应用程序中对本地执行的SQL进行拦截、解析、改写、路由处理、需要自行编码实现,只支持java,性能较高。
MyCat:数据库分库分表中间件,不用调整代码即可实现分库分表,支持多种语言,性能不及前者。–可以像使用mysql一样来使用mycat。
-- 一台服务器上配置 JDK、MyCat、Mysql
-- 其他分片服务器 只需要创建 mysql 即可
-- 分片配置(schema.xml)
-- 需要配置逻辑库、逻辑表、分片规则、数据节点、节点主机