1 索引
- 按「数据结构」分类:B+tree索引、Hash索引、Full-text索引。
- 按「物理存储」分类:聚簇索引(主键索引)、二级索引(辅助索引)
- 按「字段特性」分类:主键索引、唯一索引、普通索引、前缀索引。
- 按「字段个数」分类:单列索引、联合索引。
2 索引失效
- 对索引使用做或者左右模糊匹配
- 对索引使用函数
- 对索引进行表达式计算
- 对索引隐式类型转换:MySQL在遇到字符串和数字比较的时候,会自动把字符串转为数字,然后再进行比较。如果字符串是索引列,而条件语句中的输入参数是数字的话,那么索引列会发生隐式类型转换
- 联合索引非最左匹配
- 在 OR 前的条件列是索引列,而在 OR 后的条件列不是索引列
3 事务四个特性及保证措施
- 原子性:undolog
- 隔离性:锁 + MVCC
- 持久性:redolog
- 一致性:由前三者保证
4 事务隔离级别:
- 读未提交:指一个事务还没提交时,它做的变更就能被其他事务看到
- 读提交:指一个事务提交之后,它做的变更才能被其他事务看到
- 可重复读:指一个事务执行过程中看到的数据,一直跟这个事务启动时看到的数据是一致的
- 串行化
5 脏读、不可重复读、幻读
- 脏读:如果一个事务「读到」了另一个「未提交事务修改过的数据」,就意味着发生了「脏读」现象
- 不可重复度:在一个事务内多次读取同一个数据,如果出现前后两次读到的数据不一样的情况,就意味着发生了「不可重复读」现象。
- 幻读:在一个事务内多次查询某个符合查询条件的「记录数量」,如果出现前后两次查询到的记录数量不一样的情况,就意味着发生了「幻读」现象。
6 什么是快照读、什么是当前读
- 快照读:普通 select 语句,读取的MVCC快照链路中的某个版本
- 当前读:update、insert、delete、select … for update 等语句,读取的是数据记录的最新版本
7 幻读怎么解决
- 快照读:MVCC
- 当前读:使用了next-key lock(next-key lock 是间隙锁+记录锁的组合)
8 Read View 在 MVCC 里如何工作的?
- Read View 有四个重要的字段
- m_ids:创建RW时数据库中活跃的事务列表
- min_trx_id:活跃事务列表中最小id
- max_trx_id :创建RW时当前数据库应该给下一个事务的id值
- creator_trx_id:创建RW的事务的事务id
- 2、数据库表中每行都有两个隐藏列:
- trx_id,当一个事务对某条聚簇索引记录进行改动时,就会把该事务的事务 id 记录在 trx_id 隐藏列里
- 就会把该事务的事务 id 记录在 trx_id 隐藏列里,这个隐藏列是个指针,指向每一个旧版本记录,于是就可以通过它找到修改前的记录
- 3、根据上面两个要素进行可见性分析:
- 如果行隐藏列trx_id小于read view的min_trx_id,代表这个版本记录是在创建read view前生成的,所以可见
- 如果行隐藏列trx_id大于read view的max_trx_id,代表这个版本记录是在创建read view后生成的,所以不可见
- 如果行隐藏列trx_id处于read view的m_ids中,则代表该事务还在活跃中,所以不可见
- 如果行隐藏列trx_id不在read view的m_ids中,则代表该事务已被提交,所以可见。
9 Mysql锁分类
- 全局锁
- 表级锁
- 表锁
- 元数据锁
- 意向锁:意向锁的目的是为了快速判断表里是否有记录被加锁
- AUTO-INC 锁:插入数据时,可以不指定主键的值,数据库会自动给主键赋值递增的值
- 行级锁
- 记录锁:有 S 锁和 X 锁之分
- 间隙锁,只存在于可重复读隔离级别
- 临键锁:前面两者的结合体
加锁的对象是索引,加锁的基本单位是 next-key lock,next-key lock 是前开后闭区间,而间隙锁是前开后开区间。
- 唯一索引等值查询:
- 当查询的记录是「存在」的,在索引树上定位到这一条记录后,将该记录的索引中的 next-key lock 会退化成「记录锁」。
- 当查询的记录是「不存在」的,在索引树找到第一条大于该查询记录的记录后,将该记录的索引中的next-key lock 会退化成「间隙锁」
- 非唯一索引等值查询:
- 当查询的记录「存在」时,由于不是唯一索引,所以肯定存在索引值相同的记录,于是非唯一索引等值查询的过程是一个扫描的过程,直到扫描到第一个不符合条件的二级索引记录就停止扫描,然后在扫描的过程中,对扫描到的二级索引记录加的是 next-key 锁,而对于第一个不符合条件的二级索引记录该二级索引的 next-key 锁会退化成间隙锁。同时,在符合查询条件的记录的主键索引上加记录锁。
- 当查询的记录「不存在」时,扫描到第一条不符合条件的二级索引记录,该二级索引的 next-key 锁会退化成间隙锁。因为不存在满足查询条件的记录,所以不会对主键索引加锁。
- 非唯一索引和主键索引的范围查询的加锁规则不同之处在于:
- 唯一索引在满足一些条件的时候,索引的 next-key lock 退化为间隙锁或者记录锁。
- 非唯一索引范围查询,索引的 next-key lock 不会退化为间隙锁和记录锁。
10 undo log、redo log、binlog 有什么用?
- undo log(回滚日志):是 Innodb 存储引擎层生成的日志,实现了事务中的原子性,主要用于事务回滚和 MVCC
- redo log(重做日志):是 Innodb 存储引擎层生成的日志,实现了事务中的持久性,主要用于掉电等故障恢复
- binlog (归档日志):是 Server 层生成的日志,主要用于数据备份和主从复制;
11 redo log刷盘机制
- 刷盘时机
- MySQL 正常关闭时
- redo log buffer中记录的写入量大于 redo log buffer 内存空间的一半时
- InnoDB 的后台线程每隔 1 秒,将 redo log buffer 持久化到磁盘
- 每次事务提交时都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘
- innodb_flush_log_at_trx_commit=0时,每次提交事务,redo log留在redo log buffer中,不主动触发刷盘
- innodb_flush_log_at_trx_commit=1时,每次提交事务,redo log buffer中redo log都直接持久化到磁盘
- innodb_flush_log_at_trx_commit=2时,每次提交事务,缓存在 redo log buffer 里的 redo log 写到 redo log 文件
- 对于0和2,mysql后台线程每隔一秒:
- 0时,会把缓存在 redo log buffer 中的 redo log通过调用write()写到操作系统的 Page Cache,然后调用fsync()持久化到磁盘(MySQL 进程的崩溃会导致上一秒钟所有事务数据的丢失)
- 2时,调用 fsync,将缓存在操作系统中 Page Cache 里的 redo log 持久化到磁盘。(除非OS崩溃,Mysql崩溃没有影响)
- 文件写满了怎么办
- 「重做日志文件组」由有 2 个 redo log 文件组成,这两个 redo 日志的文件名叫 ib_logfile0和 ib_logfile1。
- 循环写,即0满了写1,1满了写0
12 redo log 和 binlog 有什么区别?
- 1、适用对象不同:
- binlog 是 MySQL的 Server 层实现的日志,所有存储引擎都可以使用,
- redo loq 是 Innodb 存储引擎实现的日志;
- 2、文件格式不同.
- STATEMENT:每一条修改数据的 SQL 都会被记录到 binlog 中(相当于记录了逻辑操作,所以针对这种格式, binlog 可以称为逻辑日志),主从复制中 slave 端再根据 SQL 语句重现。但STATEMENT 有动态函数的问题,比如你用了 uuid 或者 now 这些函数,你在主库上执行的结果并不是你在从库执行的结果,这种随时在变的函数会导致复制的数据不一致;
- ROW:记录行数据最终被修改成什么样了(这种格式的日志,就不能称为逻辑日志了),不会出现STATEMENT 下动态函数的问题。但 ROW 的缺点是每行数据的变化结果都会被记录,比如执行批量update 语句,更新多少行数据就会产生多少条记录,使 binlog 文件过大,而在 STATEMENT 格式下只会记录一个 update 语句而已;
- MIXED:包含了 STATEMENT 和 ROW 模式,它会根据不同的情况自动使用 ROW 模式和STATEMENT 模式:
- 3、写入方式不同.
- binlog 是追加写,写满一个文件,就创建一个新的文件继续写,不会覆盖以前的日志,保存的是全量的日志。
- redo log 是循环写,日志空间大小是固定,全部写满就从头开始,保存未被刷入磁盘的脏页日志。
- 4、用途不同:
- binlog 用于备份恢复、主从复制;
- redo log 用于掉电等故障恢复。、
13 为什么要有 Buffer Pool?
- 当读取数据时,如果数据存在于 Buffer Pool 中,客户端就会直接读取 Buffer Pool 中的数据,否则再去磁盘中读取
- 当修改数据时,首先是修改 Buffer Pool 中数据所在的页,然后将其页设置为脏页,最后由后台线程将脏页写入到磁盘