一、mysql锁的类型有哪些?
可以从锁的属性、粒度和状态来进行分类。
属性:共享锁和排他锁。
粒度:记录锁、行锁、页锁、表锁、间隙锁和临建锁。
状态:意向共享锁和意向排他锁。
二、事务的基本特性和隔离级别?
基本特性:ACID。
A是原子性。指一个事务中的所有操作要么同时成功,要么同时失败。
C是一致性。指数据库总是从一个一致性的状态到另一个一致性的状态。
I是隔离性。指的是一个事务的修改在最终提交前,对其他的事务是不可见的。
D是持久性。指的是事务一旦提交,其修改会永久保存到数据库中。
隔离级别:读未提交、读已提交、可重复读、串行。
读未提交:可以读到其他事务未提交的数据,也叫做脏读。
读已提交:一个事务多次读取的结果可能不一致,也叫不可重复读。oracle默认的隔离级别。
可重复读:一个事务多次读取的结果一致,但是可能导致幻读。mysql默认的隔离级别。
串行:给每一行读取的数据都加锁,可能会导致大量超时和锁竞争的问题。
三、索引的设计原则?
查询更快,占用更少空间。
四、mysql的索引数据结构及特点?
两种索引数据结构:B+树索引和哈希索引。
B+树索引支持范围查询、每次查询所花费的时间基本一致、支持模糊查询。
哈希索引适用于等值查询,查询速度快。
五、怎么处理慢查询?
从三个方面分析原因:是否加载了多余的数据、查询条件没有命中索引、数据量太大。
如果是加载了多余的数据,那么需要重写sql,把多余的数据去掉。
如果是查询条件没有命中索引,那么需要查看语句的执行计划,分析使用索引的情况,修改语句或者索引,使查询尽可能命中索引。
如果是数据量过大,那么需要进行分库分表。单个表超过500w条数据或者超过2G大小就要分。
六、如何保证ACID?
mysql级别有个binlog日志。innoDB级别有undo log和redo log日志。
A由undo log保证。undo log记录了需要回滚的日志信息,事务回滚时撤销已经执行的sql。
C由其他三大特性以及业务逻辑代码保证。
I由MVCC保证。
D由内存和redo log保证。mysql修改数据的时候同时在内存和redo log中记录本次操作,宕机的时候可以从redo log中恢复。
简单提下 redo log 和 undo log。在修改数据的时候,会向 redo log 中记录修改的页内容(为了在数据库宕机重启后恢复对数据库的操作),也会向 undo log 记录数据原来的快照(用于回滚事务)。undo log有两个作用,除了用于回滚事务,还用于实现MVCC。
七、什么是MVCC?
数据库的并发有读读、读写和写写三种,读读并发无冲突,写写并发只能通过加锁解决数据竞争冲突问题,读写是最多的场景,所以使用MVCC(多版本并发控制)解决读写并发冲突,提高并发性能。
- 并发事务对数据的读操作不会产生并发问题,所以不用解决;
- 并发事务对数据的读+写,常规操作一般会对要操作的数据加锁来解决并发读+写可能产生的问题,MySQL的InnoDB实现了MVCC来更好地处理读写冲突,可以做到即使存在并发读写,也可以不用加锁,实现"非阻塞并发读"。
- 并发事务对数据的写操作,只能通过加锁(乐观锁/悲观锁)来解决。
MySQL实现的MVCC,主要是用于在并发读写的情况下,保证 “读” 数据时无需加锁也可以读取到数据的某一个版本的快照,好处是可以避免加锁,降低开销,解决了读写冲突,增大了数据库的并发性能。
- 当前读:读取的数据是最新版本,读取数据时还要保证其他并发事务不会修改当前的数据,当前读会对读取的记录加锁。比如:select …… lock in share mode(共享锁)、select …… for update | update | insert | delete(排他锁)
- 快照读:每一次修改数据,都会在 undo log 中存有快照记录,这里的快照,就是读取undo log中的某一版本的快照。这种方式的优点是可以不用加锁就可以读取到数据,缺点是读取到的数据可能不是最新的版本。一般的查询都是快照读,比如:select * from t_user where id=1; 在MVCC中的查询都是快照度。
MVCC是存储了同一条数据的历史版本链,不同的事务可以访问不同的历史版本。MVCC存储在undo log(回滚日志)中。MySQL中MVCC主要是通过行记录中的隐藏字段(隐藏主键 row_id、事务ID trx_id、回滚指针 roll_pointer)、undo log(版本链)、ReadView(一致性读视图)来实现的。
1. 隐藏字段
- DB_TRX_ID:修改或创建该数据的事务ID;
- DB_ROLL_PTR:回滚指针,指向数据的上一个版本。
2. undo log回滚日志
undo log的主要作用是实现回滚和MVCC。MVCC所需要的版本链就存储在undo log中。
3. Read View
Read View 就是事务进行快照读时,会产生的一个“读视图”,记录并存储了当前活跃事务的id。其主要解决的问题是,事务在读取数据的时候,应该读哪个版本的数据(最新数据还是某个版本的数据)。Read View维护了几个变量:
- rw_trx_ids:生成Read View时,当前活跃的事务id数组,从小到大排序。
- min_trx_id:rw_trx_ids中最小事务的id。
- max_trx_id:生成Read View时,要分配给下一个事务的id。
- curr_trx_id:创建Read View的当前事务id。
原理就是将当前事务的id与最新数据的事务id和活跃事务id进行比较,确定读的版本。
MVCC主要是用来解决RU隔离级别下的脏读和RC隔离级别下的不可重复读的问题,所以MVCC只在RC(解决脏读)和RR(解决不可重复读)隔离级别下生效,也就是MySQL只会在RC和RR隔离级别下的快照读时才会生成ReadView。区别就是,在RC隔离级别下,每一次快照读都会生成一个最新的ReadView;在RR隔离级别下,只有事务中第一次快照读会生成ReadView,之后的快照读都使用第一次生成的ReadView。
八、mysql主从同步的原理?
主节点:dump 线程。
从节点:I/O 线程和 sql 线程。
主节点:每当 binlog 日志文件发生改变,dump 线程都会将变更的内容发送给从节点。
从节点:I/O 线程接受主节点发来的binlog日志并将其写到 relay log 中,sql 线程从 relay log 中读取内容修改数据。
这里有个问题,如果主库写入后,没来得及同步给从库,突然宕机了,会导致数据的丢失。所以就有了以下两种机制解决这个问题。
全同步复制:主节点写入binlog日志后,将binlog文件发送给从库,等全部的从库都回复确认后才返回给客户端。(性能较差)
半同步复制:主节点写入binlog日志后,将binlog文件发送给从库,只要有一个从库回复确认就返回给客户端。
十、InnoDB和MyISAM的区别?
事务:InnoDB支持事务,支持事务的四种隔离级别;MyISAM不支持事务,但是每次查询操作都是原子的。
锁:InnoDB支持行锁; MyISAM支持表锁。
行数统计:InnoDB不存储数据的总行数,所以每次统计总行数都需要全表扫描; MyISAM存储了数据的总行数。
存储方式:InnoDB为聚簇索引; MyISAM为非聚簇索引(索引文件的数据域存储了指向数据文件的指针)。
文件系统:InnoDB表可以存储为一个文件空间(共享表空间,表大小不受操作系统限制,一个表可能有多个表文件)或多个文件文件空间(受操作系统限制,每个文件最大2G); MyISAM表有索引文件,表结构文件和数据文件3个文件。
十一、Mysql索引类型及对数据库性能的影响?
- 普通索引:允许被索引的数据列有重复值。
- 唯一索引:不允许被索引的数据列有重复值,保证唯一性。
- 主键索引:一个表只能有一个主键索引,如果是聚簇索引,那么数据和主键存放在一起。
- 联合索引:在多个列上建立索引,如INDEX(colA, colB)索引。
- 全文索引:通过倒排索引判断字段是否包含的问题。(ES做得更好)。
优点:索引提高查询数据的速度;索引可以使用优化隐藏器,提高性能。
缺点:索引会占用额外的空间;在增删改的时候,需要操作索引文件,会降低速度。
十二、Mysql数据库中,什么情况下设置了索引,但无法使用?
- 没有符合最左前缀原则;
- 字段进行了隐式数据类型转换;
- 走索引没有全表扫描效率高。
十三、InnoDB是怎么实现事务的?
InnoDB通过Buffer pool、LogBuffer、undo log和redo log实现事务,先内存再磁盘。以一个update语句为例:
- InnoDB会找到要修改的数据页,然后将其放到Buffer pool(内存)中,并修改Buffer pool中的值;
- 针对update语句生成一个redo log对象,并将其放入LogBuffer中;
- 针对update语句生成undo log日志,用于回滚;
- 如果事务提交,将LogBuffer中的redo log对象进行持久化;后续会有其他机制将Buffer pool中的数据持久化;如果回滚,则利用undo log日志进行回滚。
十四、B树和B+树的特点?mysql为什么使用B+树?
B树:B树的每个节点是有序的,每个节点内的多条数据也是排序。
B+树:在B树的基础上,叶子节点之间有双向指针,此外非叶子节点上的元素在叶子节点上都冗余了。
B+树中的叶子节点存储了全部的数据且有序,节点中的数据是有序的,查询数据速度快,此外叶子节点之间有双向指针,全表扫描和范围查询快。
十五、数据库设计的三范式?
第一范式:数据库的每个字段都是不可再分的原子数据。如家庭住址应该将省、市、地区等分成省、市和地区三个列。
第二范式(非主键字段和主键字段的关系):首先满足第一范式,然后数据库的非主键字段必须依赖于主键字段,不能只依赖主键一部分。
第三范式(非主键字段和非主键字段的关系):首先满足第二范式,然后非主键字段不能依赖于其他非主键字段。
十六、怎么解决幻读?
幻读是指在同一个事务中,存在前后两次查询同一个范围的数据,但是第二次查询却看到了第一次查询没看到的行,一般情况下特指事务执行中新增的其他行。
幻读出现在可重复读(RR)隔离级别下,普通的SELECT查询就是快照读,是不会看到别的事务插入的数据的。因此,幻读在“当前读”下才会出现(当前读会生成行锁,但行锁只能锁定存在的行,针对新插入的操作没有限定,所以才会出现幻读)。所以要解决幻读,就必须得解决新增行的问题。
产生幻读的原因是:行锁只能锁住行,但是新插入记录这个动作,要更新的是记录之间的“间隙”。因此,为了解决幻读问题,InnoDB 只好引入新的锁,也就是间隙锁 (Gap Lock)。顾名思义,间隙锁,锁的就是两个值之间的空隙。
解决方法是行锁 + 间隙锁。MySQL将行锁 + 间隙锁组合统称为 next-key lock,通过 next-key lock 解决了幻读问题。间隙锁的引入,可能会导致同样的语句锁住更大的范围,这其实是影响了 并发度的