MySQL数据库灵魂拷问-基础篇
1、 什么是事务?
事务是数据库最小逻辑单位,是一个或者一组sql的集合
2、 事务的ACID特性?
A=Atomicity 原子性:就是上面说的,务是数据库最小逻辑单位。
C=Consistency 一致性:系统总是从一个一致性的状态转移到另一个一致性的状态。
I=Isolation 隔离性: 通常来说:一个事务在完全提交之前,对其他事务是不可见的.注意前面的通常来说加了红色,意味着有例外情况。
D=Durability 持久性:事务一旦提交,将永久存在。
3、 事务的隔离级别与对应的问题?
隔离级别与对应问题矩阵
隔离级别 | 脏读 | 不可重复读 | 幻读 |
读未提交 | 是 | 是 | 是 |
不可重复读 | 否 | 是 | 是 |
可重复读 | 否 | 否 | 是(mysql 否) |
串行化 | 否 | 否 | 否 |
4、 mysql是如何解决幻读的?
在快照读读情况下,mysql通过mvcc来避免幻读。
在当前读读情况下,mysql通过next-key来避免幻读。
5、 mysql常见死锁场景?
2个事务交叉。
3个insert事务,一个回滚。
多个事务,间隙锁造成死锁。(一般会回答上面2个就行了)
6、 drop、delete与truncate的区别?
drop:删除表结构与表空间,底层系统文件是会变小的。
truncate:删除表空间,底层系统文件是会变小的。
delete: 只删表数据,删除操作后,底层系统文件是不会变小的。
7、 若客户反馈系统慢,如何查找问题?
第一步:查询应用服务器,数据库服务器 CPU使用率,CPU负载,带宽,内存
第二步:一般是 CPU 过高,且是mysql进程,则进入数据库,首先查询 活跃线程数,查询正在执行的sql,顺便也去慢查询日志文件
第三步:找到问题sql,分析sql,通过explain分析具体问题(一般都是数据库有大量计算操作,大量数据查询返回没有做分页处理)
8、 explain 怎么看?
谁是第一行,谁是驱动表(数据库查询的原则是小表驱动大表 a left join b 则a是驱动表)
key:表示实际使用的索引
key_len:索引字段的长度
rows:查询时实际扫描的行数
Extra:执行情况的说明和描述
9、 谈谈索引的理解?
索引大大减小了服务器需要扫描的数据量
索引可以帮助服务器避免排序和临时表
索引可以将随机IO变成顺序IO
缺点:创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加;索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大;当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。
10、索引数据结构与索引类型
索引数据结构有:结构b+树索引,hash索引,问各自适用什么场景?
索引类型:聚簇索引与非聚簇索引,问谁快谁慢?
11、哪些情况可能无法使用上索引?
类型转换:当存在索引列的数据类型隐形转换,则用不上索引,比如列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引
索引列加函数:加了函数无法使用上索引
字符校对规则不对
12、超大分页怎么处理?
SELECT * FROM suka_agent_test LIMIT 2000000,1000;
改为:SELECT a.* FROM `suka_agent_test` a INNER JOIN ( SELECT id FROM suka_agent_test LIMIT 2000000,1000 ) b ON a.id=b.id;
什么叫延迟关联?延迟两个字的关键在于延迟了对列的访问。延迟关联的原理是借助了覆盖索引,减少了对不符合条件的行的扫描
13 、提问
一张自增表里面总共有 7 条数据,删除了最后 2 条数据,又插入了一条数据 ,id 是多少,重启 MySQL 数据库,又插入了一条数据,此时 id 是几, 为什么
MySQL数据库灵魂拷问-加强版
1、 mysql 是如何实现 ACID 特性的?
通过undolog实现原子性,redolog、binlog实现持久性,锁机制与MVCC实现隔离性。
2、 mysql 如何做大表DDL?
Mysql 5.7 已经支持原生在线DDL语句,但是涉及到一些参数配置,并可能不知道配置多少合适,所以一般大表还是使用percona-tools
3、 mysql 主从复制原理?
三个线程:slave IO线程发起请求binlog,master收到请求,通过master binlogdump 线程将binlog发送给slave,slave io线程收到binlog 写入自己的relaylog中,然后slave SQL 线程回放relaylog中的内容。
4、 mysql 内部有哪些线程,二各自负责什么工作?
主线程:innodb1.2 版本 负责插入缓冲
purge线程:负责undo页回收
page clean 线程:负责脏页刷新
5、 mysql 主从复制延迟原因与表现?
网络延迟:Read_Master_Log_Pos 变化非常慢,Seconds_Behind_Master逐步加大
大表DDL:Read_Master_Log_Pos不变,status 显示正在alter 表,Seconds_Behind_Master逐步加大
大事务:Read_Master_Log_Pos
6、 mysql 备份原理?
首先记录开始的LSN(全备的话就是从0开始,增备的话从指定路径,或者从表中获取)并启动一个xtrabackup_log后台检测的 fork进程,实时检测mysql redo的变化,一旦发现redo有新的日志写入,立刻将日志写入到日志文件xtrabackup_log中
复制innodb的数据文件和系统表空间文件idbdata1到对应的以默认时间戳为备份目录的地方(流式备份就没有这个目录咯)
复制结束后,执行flush table with read lock操作(5.7以及之前) 8.0使用备份锁,所以percona工具xtrabackup8.0只能备份mysql8.0
复制.frm .myd .myi文件
并且在这一时刻获得binary log 的位置
将表进行解锁unlock tables (8.0 使用 UNLOCK INSTANCE)
停止xtrabackup_log进程
7、 mysql 锁到底锁了什么?
同时锁住了主键与辅助索引
8、 mysql 有哪些日志,各个介绍?
undolog,redolog,errorlog,general_log,slow.log,binlog,(若装了审计插件还有审计日志)
9、 mysql 半同步,5.6,5.7 半同步的区别?
5.6在半同步的时候,采用的是After Commit策略。即在主库上commit了之后,等待从库返回确认。
5.7在半同步的时候,采用的是AFTER_SYNC,先等待从库返回确认,然后主库在提交。
10、 redolog,binlog 区别,如何保证一致性?
MySQL通过两阶段提交(内部XA的两阶段提交)很好地解决了这一问题
第一阶段:协调者向所有参与者发送事务内容,询问是否可以执行事务提交操作,并开始等待各参与者响应。
事务操作,资源管理器此时会将undo日志和redo日志计入事务日志中
如果参与者成功执行了事务操作,那么就反馈给协调者Yes响应,表示事务可以执行;如果参与者没有成功执行事务,那么就反馈给协调者No响应,表示事务不可以执行。
第二阶段:提交或者中断事务