Mysql知识点

1 B树和B+树的区别 ，为什么Mysql的索引使用B树？为什么不用其他数据结构做呢？

B树特点：

1. 节点排序
2. 一个节点可以存多个元素，多个元素也排序

B+树特点

1. 拥有B树特点
2. 叶子节点之间有指针
3. 非叶子节点的元素再叶子节点冗余，也就是叶子节点存储了所有的元素，并且排好序

Mysql索引使用的是B+树，因为索引是用来加快查询，而B+树通过对数据进行排序可以提高查询速度，然后通过一个节点中可以存储多个元素，从而可以使得B+树的高度不会太高，在mysql中一个Innodb页就是一个B+树节点，一个Innodb页默认16k，所以一般情况下个一颗三层的B+树可以存储2000w数据，然后通过利用B+树叶子节点存储所有数据并进行排序，并且叶子节点之间有指针，可以很好的支持全表搜索，范围查找等SQL语句。

hash：hash冲突，范围查询时需要逐个遍历，对于内存空间要求高
树：
二叉树： 无序
BST：二叉查找树，插入时有序，左子树小于子树根节点，右子树大于子树根节点。插入连续值时，可能退化成链表，时间复杂度高
AVL：平衡二叉查找树，有序，为了解决连续值退化的问题，通过左旋或右旋让树保持平衡，保持最短子树和最长子树高度不能超过1。
通过插入性能的损失弥补查询性能的提升，但是数据查再插入过程中需要经过多次左旋或者右旋才能保持树的平衡
红黑树：在旋转平衡的基础上，添加了变色的行为，保持最长子树不超过最短子树的2倍。使得查询性能和插入性能近乎一致
二叉树的不足：随着数据的插入，树的深度会越来越深，索引的体积越来越大，内存放不下的需要从磁盘读取，树的层次太高的话，读取 磁盘的次数就多了，影响数据读取效率
B树：非叶子节点存储key和data值，B+树只存储key，B+树可以读取更多的key值
B+树叶子节点之间用指针相连，只需呀遍历所有的叶子节点就可以获取相关数据，b树需要中序遍历那样的遍历。
B+非叶子节点只存储key，导致B+树层级较少，查询效率更高。
B+树所有关键词地址都存储在叶子节点上，所以每次查询次数相同，比B树稳定

2 索引的基本原理

索引就是快速的寻找那些具有特定值的记录，如果没有索引，一般来说执行查询遍历整张表
原理： 就是把无序的数据变成有序的查询

1. 把创建的索引列的内容进行排序
2. 对排序的结果生成倒排表
3. 在倒排表内容上拼上数据地址链
4. 在查询的时候先拿到倒排表的内容，在取出数据地址链，拿到具体数据

3 索引设计原则

查询速度更快，占用空间更少

1. 适合索引的列出现在where子句中的列，或者连接自居中指定的列
2. 基数比较小的表，索引效果差，没有必要在此列建立索引
3. 使用短索引，如果对长字符串列建立索引，应该指定一个前缀长度，这样可以节省大量的索引空间，如果搜素词超过索引前缀长度，使用索排除不匹配行，然后检查其余行是否匹配
4. 不要过度索引，索引需要额外的磁盘空间，并降低性能，修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就越多
5. 定义有外键的数据列一定要建立索引
6. 更新频繁的不适合建立索引
7. 若是不能有效区分数据列不适合做索引（如性别，区分度低）
8. 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引
9. 对于定义为 text、image 和 bt 的数据类型的列不要建立索引

4 高度为3的B+树能存储多少条数据？

mysql使用innodb引擎，默认页文件大小为16k
假设一行数据为1K 那么一页存储16条数据，也就是一个叶子节点存储16条数据

非叶子节点 假设主键id 为bigint类型，那么长度为8B，指针大小在innodb引擎中大小为6B,一共14B，那么一页可以存放
16K/14B=1170.
高度为2的B+树可以存储的数据为 117016=18720
高度为3的B+树可以存储的数据为 11701170*16=21902400

5 B+树索引结构示意图

聚集索引叶子节点中存放表的行记录数据
辅助索引的叶子节点存放只是对应数据的聚集索引键（主键）

辅助索引

6 回表是什么？

如果select 所需获得列中有大量非索引列，那么就需要到表中找到相应的列的信息。
特点： 先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。
使用聚集索引（主键或者第一个唯一索引）就不会回表，普通索引就会回表。

7 什么是覆盖索引？

如果一个索引包含或者覆盖所有需要查询的字段的值，称为覆盖索引，即只需要扫描索引无序回表。

覆盖索引的优点：

• 索引条目通常小于数据行大小，只需要读取索引，极大的减少数据访问量
• 因为索引是按照列值顺序存储的，对于IO密集的范围查找要比随机从磁盘读取的每一行IO少很多
• innodb 的聚簇索引，覆盖索引对innodb表特别有用
• 一些存储引擎如mysam在内存种只缓存索引，数据则依赖操作系统来缓存，因此访问数据需要系统一次调用

覆盖索引必须存储索引列的值，所以mysql只能使用B-tree索引做覆盖索引

8 联合索引

定义： 对表上的多个列进行索引。
联合索引也是一颗B+树，不同的是联合索引的键值数量不是1 ，而是大于等于2

9 最左前缀匹配原则是什么？

在mysql建立联合索引时会遵循最左前缀匹配原则：即最左优先，在检索数据的时候从联合索引的最左边开始匹配。

当我们建立联合索引（a，b，c），索引文件中对应的B+树每个节点都存储了a,b,c的值，而B+树上查询到对应叶子节点时，是按照我们建立联合索引的字段顺序来依次查询。

即在节点中，选择下一个节点时，先判断a，再判断b，最后判断c，因为我们建立索引的顺序就是abc的顺序。

走索引的条件语句 a,ab,abc,ba(mysql查询优化器)，ac等
不走索引的条件语句 bc ,cb

原因：
在联合索引所形成的B+树内，只有a（联合索引的第一个）是真正意义上的按序排列了，b的排序仅仅在a值相同的节点间进行，而c的按序排序又仅仅在a,b都相同的节点间进行排序。也就是说只有进行了a的查询，才能进行下一步b的查询，而后才能进行下一步c的查询！！！

注意：
1，mysql会一直向右匹配，直到遇到范围查询 （<,>,between,like）就停止匹配。范围列可以用到索引，但是范围列后面的列是无法用到索引的。即索引最多用与一个范围列。
2，如果通配符%出现在开头，也是不走索引的
3，如果查询条件中含有函数或表达式，将导致索引失效而进行全表查询。
4，只要列中包含有NULL值都将不会包含在索引中。联合索引中只要有一列含有NULL值，那么这一列对于此联合索引是无效的。所以数据库设计时不要将字段默认值为NULL
5，尽量选择区分度高的列作为索引（区分度公式：count(distinct col)/count(*)表示字段的不重复比例，比例越大我们扫描的记录越少）

10 索引下推

索引下推Index Condition Pushdown (ICP) ）：数据库检索数据过程中为了减少回表次数而做的优化。
索引下推是MySQL5.6添加的

配置，默认时开启的

SET optimizer_switch = 'index_condition_pushdown=off'; 
SET optimizer_switch = 'index_condition_pushdown=on';

原理：
不使用索引条件下推优化：
存储引擎通过索引检索到数据，然后返回Mysql服务器，服务器然后判断数据是否符合条件。
使用索引条件下推优化：
如果where条件中包含联合索引中一个以上的字段，Mysql服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合Msyql服务器传递条件，只有当索引符合条件时才会将数据检索出来返回给Mysql服务器。

ICP可以减少存储引擎查询基础表的次数，也可以减少mysql服务器从存储引擎接受数据的次数。

适用条件： 需要整表扫描的情况，适用innodb和myisam引擎查询；对于innodb只适合二级索引。

工作过程
不使用索引条件下推优化时的查询过程

• 获取下一行，首先读取索引信息，然后根据索引将整行数据读取出来。
• 然后通过where条件判断当前数据是否符合条件，符合返回数据。

使用索引条件下推优化时的查询过程

• 获取下一行的索引信息。
• 检查索引中存储的列信息是否符合索引条件，如果符合将整行数据读取出来，如果不符合跳过读取下一行。
• 用剩余的判断条件，判断此行数据是否符合要求，符合要求返回数据。

11 RedoLog是什么？

Redo是Innodb引擎特有的，记录着事务对数据的修改。
是一个物理日志，每条redo记录=表空间号+数据页号+偏移量+修改数据长度+具体修改的数据

作用：
确保事务的持久性，RedoLog记录事务执行后的状态，用来恢复未写入datafile的已成功事务更新的数据。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redolog进行重做，从而达到事务的持久性这一特性。

为什么需要RedoLog?
Innodb是页为单位来管理存储空间的，任何增删改查的操做都会操做完整的一页，会将整个页加载到buffer pool中，然后对需要修改的记录进行修改，修改完毕之后不会立即刷新到磁盘，因为此时刷新是一个随机的io，仅仅修改一条记录，刷新磁盘有的浪费。但是如果不立即刷新的话，数据还在内存中，如果mysql断电的话，数据就会丢失。因此引入redo log，日志记录那个页面，多少偏移量，什么数据发生了声明变化，这样即使系统崩溃了，还可以恢复。另外redolog是循环写入固定文件的，是顺序写入磁盘。

Redo Log Buffer: 重做日志缓存，提升性能
默认大小16M. buffer分为很多block 每个block大小为512kb，每个事务产生的所有的redo log称为group，一组redo log会写入到redo log buffer的block中去。

Redo Log buffer的缓冲日志什么时候写入磁盘？

1） 如果redo log buffer 中的日志已经占据redo log buffer总容量的一半
2）一个事务提交的时候，必须把他的那些redo log block都刷入到磁盘文件，只有这样，当事务提交的之后，它修改的数据绝对不会丢失，因为redo log里有重做日志，随时可以恢复事务做的修改。
3） 后台线程定时刷新，有一个后台线程每隔1s 就会把redo log buffer 里的redo log block刷写到磁盘中
4）mysql关闭的时候

mysql通过innodb_flush_log_at_trx_commit配置来控制redo log的写盘时机。
0：延迟写。提交事务时不会将redo log写入os buffer，而是每隔1秒将redo log写入os buffer并调用fsync()刷入磁盘。系统崩溃会丢失一秒钟的数据。
1：实时写，实时刷。每次提交事务都将redo log写入os buffer并调用fsync()刷入磁盘。这种方式系统奔溃不会丢失数据，因每次提交事务都写入磁盘，性能比较差（推荐）
2：实时写，延时刷。每次提交事务都将redo log写入os buffer，但并不会马上调用fsync()刷如磁盘，而是间隔1秒调fsync()刷盘。相对于每次提交都写盘和每隔1秒写盘，实施写os buffer延时刷盘是一个数据一致性与性能的之间的这种方案。

redo log 日志文件
硬盘存储的redo log日志文件不止一个，而是以日志文件组的形式出现的，每个日志文件大小都是一样的，采用的是环形数组的形式，从头开始写，写到末尾又回到头循环写

在个日志文件组中还有两个重要的属性，分别是write pos、checkpoint
write pos是当前记录的位置，一边写一边后移
checkpoint是当前要擦除的位置，也是往后推移
每次刷盘redo log记录到日志文件组中，write pos位置就会后移更新。
每次MySQL加载日志文件组恢复数据时，会清空加载过的redo log记录，并把checkpoint后移更新。
write pos和checkpoint之间的还空着的部分可以用来写入新的redo log记录
如果write pos追上checkpoint，表示日志文件组满了，这时候不能再写入新的redo log记录，MySQL得停下来，清空一些记录，把checkpoint推进一下。

通过过innodb_log_file_size可以指定每个redo log文件的大小，默认48MB 通过innodb_log_files_in_group可以指定日志文件的数量，默认2个

12 Binary log是什么？

MySQL二进制日志（Binary Log，简称binlog）记录着数据库中所有更新数据的SQL语句，也就是数据库的增，删，改操作语句，将其写入到二进制日志中，以时间的形式保存。

作用：
1）主从同步
2）数据恢复

什么时候会新建binlog文件
当遇到以下3种情况时会重新生成一个新的日志文件，文件序号递增：

• MySQL重启后，会生成一个新的日志文件
• 使用flush logs命令
• 当binlog文件大小超过max_binlog_size上限时

1、查看binlog文件列表
 show binary logs;

 2、查看当前正在写入的binlog文件
 show master status；
 
3、查看指定binlog文件的内容
show binlog events [in 'log_name'] [FROM pos] [limit [offset,] row_count]

4、查看二进制日志是否开启
show variables like 'log_bin%';

5、开启二进制日志
MySQL二进制日志默认是关闭的，可以通过编辑/etc/my.cnf配置文件
server-id=1
log-bin=/data/mysql/log/mysql-bin  //binlog日志存放路径
expire_logs_days = 30    //binlog日志的保存时间，超过将会自动删除
max_binlog_size = 200M  //binlog日志文件大小，根据环境而定

 

13 Undo log是什么？

undo log是一种用于撤销回退的日志，在事务没提交之前，MySQL会先记录更新前的数据到 undo log日志文件里面，当事务回滚时或者数据库崩溃时，可以利用 undo log来进行回退。

redo log 和undo log的核心是为了保证innodb事务机制中的持久性和原子性，事务提交成功由redo log保证数据持久性，而事务可以进行回滚从而保证事务操作原子性则是通过undo log 来保证的。

作用：

• 实现了事务的原子性
  Undo log 是为了实现事务的原子性的产物。 事务处理过程中， 如果出现了错误或用户执行了RoLLBack 语句，mysql可以利用undolog中的备份将数据恢复到事务开始之前的状态。

• 实现多版本并发控制(MVCC)
  Undolog 在mysql innodb存储引擎中用来实现多版本并发控制。 事务未提交之前，undolog保存了未提交之前的版本数据，undolog中的数据可作为数据旧版本快照供其他并发事务进行快照度。

Undo Log的产生和销毁： Undo Log在事务开始前产生; 事务在提交时，并不会立刻删除undo log，innodb会将该事务对应的undo log放到删除列表中，后面会通过一个后台线程purge thread进行回收处理。 Undo Log属于逻辑日志，记录一个变化过程。 例如执行一个delete，undo log会记录一个insert; 执行一个update， undolog会记录一个相反的update

14 MVCC

MVCC 全称 Muti-Version Concurrency Control 多版本并发控制，主要是为了提高数据库的并发性能，用更好的方式去处理读写冲突，做到即使读写有冲突的时候，也能做到不加锁，非阻塞并发读

什么是当前读和快照读？
当前读： 读取的数据记录是数据库目前最新的版本，读取的时候还要保证其他并发事务不能修改当前记录，所以会对读取的数据加锁。
（select lock in share mode ,select for update, update insert ,delete(排他锁)），这些操作都是当前读。

快照读：像不加锁的select的操作就是快照读，即不加锁的非阻塞读，快照读的前提是隔离级别不是串行级别，串行级别的快照读会退化成当前读。快照读的实现是基于MVCC.

数据的并发场景

• 读-读：不存在任何问题，也不需要并发控制
• 读-写：有线程安全的问题，可能会导致事务隔离性问题，也就是遇到的脏读，不可重复读，幻读等
• 写-写： 有线程安全问题，可能导致丢失更新的问题。

MVCC能解决什么问题？ 好处是什么？
MVCC 是用来解决读写冲突的无锁并发控制，就是为事务分配单向增长和时间戳，为每个数据的修改保存一个版本，版本与事务时间戳相关联，也就是每个事务都一个对应版本的快照，快照版本按照单向增长的时间戳决定先后顺序。

可以解决脏读，不可重复读，幻读等事务隔离级别问题，但不能解决丢失更新的问题。

数据库事务隔离级别解决方案
MVCC+悲观锁： 读写，写写
MVCC+乐观锁： 读写，写写

MVCC实现原理

依赖记录中的三个隐含字段，undolog,read view来实现

隐含字段：
每行记录除了我们自定义的字段 ，还有数据库隐含定义的DB_TRX_ID，DB_ROLL，DB_ROW_ID等字段。

DB_ROW_ID： 6byte,隐含的自增id，如果数据库表中没有主键，innodb会自动以DB_ROW_ID生成一个聚簇索引
DB_TRX_ID：6byte，最近修改的事务id(修改，插入)，记录创建这条记录及最后一次该记录的事务的Id,是一个指针。
DB_ROLL_PTR：7byte，一个回滚指针，用于配合undo日志，指向上一个旧版本
DELETE_BIT:：1byte，记录被更新或者删除，并不代表真的删除，相当于记录一次逻辑删除

undolog分为三种：

insert undo log:：插入一条记录，至少把这条记录的主键记录下来，之后回滚的时候只需要把主键对应的记录删除即可
update undo log： 修改一条记录的时候，至少把修改这条记录前旧值记录下来，回滚的时候更新为旧值即可
delete undo log：删除一条记录的时候，会把这条记录的全部内容都记录下来，删除操作都只是设置老记录的delete_bit并不是整整将其删除

purge线程
InnoDB有专门的purge线程来清理deleted_bit为true的记录。为了不影响MVCC的正常工作，purge线程自己也维护了一个read view（这个read view相当于系统中最老活跃事务的read view）;如果某个记录的deleted_bit为true，并且DB_TRX_ID相对于purge线程的read view可见，那么这条记录一定是可以被安全清除的。

undo log 实际上就是存在于 rollback segment 中的旧纪录链

Read View
Read View就是事务进行快照读操作的时候生产的读视图(Read View)，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的ID(当每个事务开启时，都会被分配一个ID, 这个ID是递增的，所以最新的事务，ID值越大)
Read View主要是用来做可见性判断的, 即当我们某个事务执行快照读的时候，对该记录创建一个Read View读视图，把它比作条件用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo log里面的某个版本的数据。

Read View遵循一个可见性算法，主要是将要被修改的数据的最新记录中的DB_TRX_ID（即当前事务ID）取出来，与系统当前其他活跃事务的ID去对比（由Read View维护），如果DB_TRX_ID跟Read View的属性做了某些比较，不符合可见性，那就通过DB_ROLL_PTR回滚指针去取出Undo Log中的DB_TRX_ID再比较，即遍历链表的DB_TRX_ID（从链首到链尾，即从最近的一次修改查起），直到找到满足特定条件的DB_TRX_ID, 那么这个DB_TRX_ID所在的旧记录就是当前事务能看见的最新老版本

RC,RR级别下的InnoDB快照读有什么不同？
正是Read View生成时机的不同，从而造成RC,RR级别下快照读的结果的不同
1）在RR级别下的某个事务的对某条记录的第一次快照读会创建一个快照及Read View, 将当前系统活跃的其他事务记录起来，此后在调用快照读的时候，还是使用的是同一个Read View，所以只要当前事务在其他事务提交更新之前使用过快照读，那么之后的快照读使用的都是同一个Read View，所以对之后的修改不可见；
2）即RR级别下，快照读生成Read View时，Read View会记录此时所有其他活动事务的快照，这些事务的修改对于当前事务都是不可见的。而早于Read View创建的事务所做的修改均是可见
3）而在RC级别下的，事务中，每次快照读都会新生成一个快照和Read View, 这就是我们在RC级别下的事务中可以看到别的事务提交的更新的原因

15 Innodb是如何实现事务的

innodb通过buffer pool，log buffer，redo log，undo log来实现事务，以一个update举例：

• innodb接到一个update语句的时候，会先根据条件找到 数据所在的页，并将该页放到buffer pool中
• 执行update语句，修改buffer pool的数据，
• 针对update语句生成一个redo log记录，存入redo log buffer 中的block
• 针对update语句生成一个undo log，用于事务回滚
• 如果事务提交，那么则把redo log持久化到日志文件中，后续还有其他的机制将buffer pool所修改的数据持久化到data file中
• 如果事务回滚则用undolog进行回滚。

16 事务的并发问题

1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。
3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

17 MySQL事务隔离级别

读未提交（read uncommitted）：一个事务还没有提交时，它做的变更就能被别的事务看到。会产生脏读问题，在项目中不用， 安全性太差；
读提交（read committed）：一个事物提交之后，它做的变更才会被其他事务看到。由于一个事务可以看到别的事务已经提交的数据，于是随之而来产生了不可重复读和幻读等 问题；
可重复读（repeatable read）：这是 MySQL 的默认隔离级别，它确保了一个事务中多个实例在并发读取数据的时候会读取 到一样的数据.
不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当 用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB 和 Falcon 存储引擎通 过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。
串行化（serializable）：事务的最高级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之， 它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争，一 般为了提升程序的吞吐量不会采用这个；