Mysql相关知识点

一、基础知识

来源于

JavaGuide和【对线面试官】MySQL 事务&&锁机制&&MVCC (qq.com)

部分自己整理学习笔记

1.关系型数据库

建立在关系模型基础之上的数据库。

关系模型表明了数据库所存储的数据之间的联系（一对一，一对多，多对多）。

常见的关系型数据库：Mysql，oracle，SQL sercer。

2.mysql默认端口3306

3.MyISAM和InnoDB区别

• 行级锁
• 事务 (commit)，(rollback)
• 外键
• MVCC
• 安全恢复（redo log）

4.查询缓存

Mysql8.0移除。

查询缓存虽然能够提升数据库查询性能，但是缓存本身就带来一定的性能开销。每次查询后都要做一次缓存操作，失效后还要销毁。

5.事务

事务是逻辑上的一组操作，要么都执行，要么都不执行。

6.事务都有ACID特性

• 原子性（Atomicity): 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用。
• 一致性（Consistency)：执行事务前后，数据保持一致。
• 隔离性 (Isolaton): 并发访问数据库时，一个用户的事务不被其他事务所干扰，各个事务之间时相互独立的。
• 持久性(Durability): 一个事务被提交之后，它对数据库中的数据的改变时持久的，即使数据库发生故障也不应该对其有任何影响。

事务的原子性(undo log)和隔离性(锁，MVCC) 保证了事务的一致性。

事务的持久性则是依靠redo log实现。

7. 并发事务带来的问题

• 脏读

• 不可重复读

• 幻读

8.事务的隔离级别

9.Mysql默认的隔离级别

REPEATABLE-READ（可重读）

在Oracle，SqlServer中都是选择读已提交(Read Commited)作为默认的隔离级别，为什么Mysql不选择读已提交(Read Commited)作为默认隔离级别，而选择可重复读(Repeatable Read)作为默认的隔离级别呢？

历史原因,早阶段Mysql(5.1版本之前)的Binlog类型Statement是默认格式，即依次记录系统接受的SQL请求；5.1及以后，MySQL提供了Row,Mixed,statement 3种Binlog格式, 当binlog为statement格式,使用RC隔离级别时,会出现bug因此Mysql将可重复读(Repeatable Read)作为默认的隔离级别！

10.封锁协议（Locking Protocol）

• 一级封锁协议 在一级封锁协议中，如果仅仅是读数据不对其进行修改，是不需要加锁的，它不能保证可重复读和不读“脏”数据。
• 二级封锁协议 除防止了丢失修改，还可以进一步防止读“脏”数据。但在二级封锁协议中，由于读完数据后释放S锁，所以它不能保证可重复读。
• 三级封锁协议 一级封锁协议加上事务T在读取数据R之前必须先对其加S锁(共享锁)，直到事务结束才释放。在一级封锁协议（一级封锁协议：修改之前先加X锁，事务完成释放）的基础上加上S锁，事务结束后释放S锁，三级封锁协议除防止了丢失修改和不读“脏”数据外，还进一步防止了不可重复读。

11.一条 SQL 语句在 MySQL 中如何被执行的?

• 查询语句的执行流程如下：权限校验（如果命中缓存）--->查询缓存--->分析器--->优化器--->权限校验--->执行器--->引擎
• 更新语句执行流程如下：分析器---->权限校验---->执行器--->引擎---redo log(prepare 状态)--->binlog--->redo log(commit状态) 

12.mysql中的聚合函数

count( )、avg( )、max（），min（）

13.表和表是怎么关联的？

可以通过内连接和外连接。

内连接：INNER JOIN

外联：LEFT JOIN 和 RIGHT JOIN

14.关于笛卡尔积的问题

我们在进行表连接查询的时候一般都会使用JOIN xxx ON xxx的语法，ON语句的执行是在JOIN语句之前的，也就是说两张表数据行之间进行匹配的时候，会先判断数据行是否符合ON语句后面的条件，再决定是否JOIN。因此，有一个显而易见的SQL优化的方案是，当两张表的数据量比较大，又需要连接查询时，应该使用 FROM table1 JOIN table2 ON xxx的语法，避免使用 FROM table1,table2 WHERE xxx 的语法，因为后者会在内存中先生成一张数据量比较大的笛卡尔积表，增加了内存的开销。        

15.关于SQL注入的问题

原理:s将ql语句伪装到输入参数中。

解决方法：参数校验，sql预编译。

16.where和having有什么区别？

where是在结果返回之前起作用。

having是在结果返回之后的过滤条件。

17.SQL执行加载顺序

18.为什么不用红黑树，二叉树，B树？

红黑树的查找效率很高，但是放在磁盘中，红黑树的高度很高， 不适合IO操作。

二叉树同理。B+树的层数更低，减少了在磁盘上的IO次数。 

理论上，B+数在三层，已经有千万级别的数据量。

19.那 MySQL 中B+树的一个节点大小为多大呢？

一页，16kb

Innodb中，B+树中的一个节点存储的内容是：

- 非叶子节点：key + 指针
- 叶子节点：数据行（key 通常是数据的主键）

对于叶子节点：我们假设1行数据大小为1k（对于普通业务绝对够了），那么1页能存16条数据。

对于非叶子节点：key 使用 bigint 则为8字节，指针在 MySQL 中为6字节，一共是14字节，则16k能存放 16 * 1024 / 14 = 1170个。那么**一颗高度为3的B+树能存储的数据为：1170 * 1170 * 16 = 21902400（千万级）。

以在 InnoDB 中B+树高度一般为3层时，就能满足千万级的数据存储。在查找数据时一次页的查找代表一次IO，所以通过主键索引查询通常只需要1-3次 IO 操作即可查找到数据。千万级别对于一般的业务来说已经足够了，所以一个节点为1页，也就是16k是比较合理的。

20.什么是Buffer POOL？

Buffer Pool 是 InnoDB 维护的一个缓存区域，用来缓存数据和索引在内存中，主要用来加速数据的读写，如果 Buffer Pool 越大，那么 MySQL 就越像一个内存数据库，默认大小为 128M。

InnoDB 会将那些热点数据和一些 InnoDB 认为即将访问到的数据存在 Buffer Pool 中，以提升数据的读取性能。

InnoDB 在修改数据时，如果数据的页在 Buffer Pool 中，则会直接修改 Buffer Pool，此时我们称这个页为脏页，InnoDB 会以一定的频率将脏页刷新到磁盘，这样可以尽量减少磁盘I/O，提升性能。

21.union 和 union all 的区别

union all：对两个结果集直接进行并集操作，记录可能有重复，不会进行排序。

union：对两个结果集进行并集操作，会进行去重，记录不会重复，按字段的默认规则排序。

因此，从效率上说，UNION ALL 要比 UNION 更快。

22.Mysql的锁算法

• Record Lock  单个行记录的锁
• Gap lock  间隙锁
• Next-key Lock ：锁定一个范围，并且锁定本身。（Mysql就是通过这个算法实现的可重复读）

23.Mysql 的死锁

两个事务在执行过程中，因互相争夺资源造成的互相等待的现象。

解决方法：超时回滚，等待图

等待图：要求数据库保存两种信息，锁的信息链表，事务等待链表；

若存在死锁，回滚事务量小的事务。

24.设计数据库的三大范式

1、确保每列都保证原子性

2、保证每列都和主键相关

3、确保每列都和主键直接相关而不是间接相关。

25.Mysql是如何实现主从复制的？

主线程将数据更改记录写在bin log 中

从机有两个线程，一个IO线程负责将binlog日志复制到 中继日志中，

另一个sql线程将中继日志，完成复制。

26.如何优化数据库？

针对查询，可以建立索引，用连接查询替代子查询。

针对慢查询，可以通过分析慢查询日志（slow_log）分析原因。

可以将字段很多的表进行拆分，或者进行读写分离。

28.char和varchar有什么区别？

char是定长的，varchar是可变的。

29.索引重构

什么时候需要索引重构？

表上频繁的发生增删改操作。

如何重建索引？

drop原来索引，再创建或者直接创建。

30.如何插入数据可更高效。

禁用唯一性检查，禁用外键检查，禁用自动提交。

二、索引

1.何为索引？有什么作用？

索引本身就是一种数据结构。常见的有B+树和Hash。

索引的作用就是快速查询的检索数据的。相当于字典的目录。

2.索引的优缺点

优点

• 加快数据检索速度
• 唯一索引，可以保证数据库表中每一行数据的唯一性

缺点

• 本身占用一定内存
• 创建索引和维护索引需要耗费许多时间。当数据进行增删改时候。索引也需要动态修改。

3.索引的底层数据结构

Hash表

哈希表是键值对的集合，可以通过key快速得到value，因此哈希表可以快速检索数据（O(1)）;

散列算法，通过散列算法，快速找到value对应的index，找到index也就找到对应的value。

hash = hashfunc(key)
index = hash % array_size

但是！哈希算法有个 Hash 冲突 问题，也就是说多个不同的 key 最后得到的 index 相同。通常情况下，我们常用的解决办法是 链地址法。链地址法就是将哈希冲突数据存放在链表中。就比如 JDK1.8 之前 HashMap 就是通过链地址法来解决哈希冲突的。不过，JDK1.8 以后HashMap为了减少链表过长的时候搜索时间过长引入了红黑树。

 为什么MySQL 没有使用其作为索引的数据结构呢？

1.Hash 冲突问题 ：我们上面也提到过Hash 冲突了，不过对于数据库来说这还不算最大的缺点。

2.Hash 索引不支持顺序和范围查询(Hash 索引不支持顺序和范围查询是它最大的缺点： 假如我们要对表中的数据进行排序或者进行范围查询，那 Hash 索引可就不行了。

B 树& B+树

区别

• B树所有节点及存放key，也存放data。B+树只有叶子节点存放key和data，其他节点只存放key。
• B树的叶子节点都是独立的，B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
• B树检索离树越近越快，B+树任何查找都是从根节点到叶子节点。

4.为什么说 B+树比 B-树更适合实际应用中操作系统的文件索引和数据库索引？

• B+树的磁盘读写代价更低
• B+树的查询效率更加稳定

5.索引类型

• 主键索引和二级索引（辅助索引）二级索引包括唯一索引（可以null），普通索引，前缀索引（字符串类型数据，相对于普通索引建立的数据更小，只取前几个字符串）等
• 聚集索引和非聚集索引

主键索引和二级索引

• 数据表的主键列使用的就是主键索引。主键索引不能为null,不能重复。InooDB的表中，没有显示的执行表的主键，InnoDB会自动检查是否有唯一索引的字段。如果有，则选择该字段为默认的主键，否则InnoDB会自动创建一个6Byte的自增主键。
• 二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。

聚集索引和非聚集索引

• 聚集索引即索引结构和数据一起存放的索引，主键索引属于聚集索引。在 Mysql 中，InnoDB 引擎的表的 .ibd文件就包含了该表的索引和数据，其表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶节点 data 域保存了完整的数据记录。这个索引的 key 是数据表的主键，因此 InnoDB 表数据文件本身就是主索引。
• 非聚集索引即索引结构和数据分开存放的索引，二级索引属于非聚集索引。MyISAM 引擎中，B+Tree 叶节点的 data 域存放的是数据记录的地址。InnoDB引擎中，索引的 data 域存储相应记录主键的值而不是地址。

总结聚集索引查询速度快，非聚集索引查询需要先查询主键，再查询数据。相对而言要慢。

但是非聚集索引维护开销小，因为存储的数据只有主键，但是聚集索引维护开销大，因为叶子节点存放的数据多。

6.创建索引的注意事项

1.选择合适的字段创建索引

• 不为null的字段（Mysql难以优化引用可空列查询，它会使索引、索引统计和值更加复杂。）key_len 的计算规则和三个因素有关：数据类型、字符编码、是否为 NULL
• 被频繁查询的字段
• 被作为条件查询的字段
• 频繁需要排序的字段
• 被经常用于连接的字段（建立索引，提高多表连接查询的效率。)

2.被频繁更新的字段应该慎重建立索引。

3.尽可能的考虑建立联合索引而不是单列索引。

4.避免冗余索引

5.考虑在字符串类型的字段上使用前缀索引代替普通索引

三、MySQL三大日志

1.redo log(持久性)

InnoDB特有，具有崩溃恢复的能力。Mysql实例挂了或者宕机了，InnoDB会使用redo log恢复数据。

redo log记录物理日志。记录"在某个数据页上做了什么修改"记录到重做日志缓存（redo log buffer）中，接着刷盘到redo log文件中。

刷盘时机

InnoDB 存储引擎为 redo log 的刷盘策略提供了 innodb_flush_log_at_trx_commit 参数，它支持三种策略：

• 0 ：设置为 0 的时候，表示每次事务提交时不进行刷盘操作
• 1 ：设置为 1 的时候，表示每次事务提交时都将进行刷盘操作（默认值）
• 2 ：设置为 2 的时候，表示每次事务提交时都只把 redo log buffer 内容写入 page cache

innodb_flush_log_at_trx_commit 参数默认为 1 ，也就是说当事务提交时会调用 fsync 对 redo log 进行刷盘

另外，InnoDB 存储引擎有一个后台线程，每隔1 秒，就会把 redo log buffer 中的内容写到文件系统缓存（page cache），然后调用 fsync 刷盘。

除了后台线程每秒1次的轮询操作，还有一种情况，当 redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动刷盘。

日志文件组

redo log 日志文件不只一个，而是以一个日志文件组的形式出现的，每个的redo日志文件大小都是一样的。

• write pos 是当前记录的位置，一边写一边后移
• checkpoint 是当前要擦除的位置，也是往后推移

• 

2.binlog(数据备份，主备，主主，主从)

binlog 是逻辑日志，记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”，属于MySQL Server 层。不管用什么存储引擎，只要发生了表数据更新，都会产生 binlog 日志。

记录格式

• statement
• row
• mixed

statement记录原文，update_time=now()这里会获取当前系统时间，直接执行会导致与原库的数据不一致。

row记录具体时间。能保证同步数据的一致性。但是需要更大容量来记录。

mixed混合判断SQL语句会不会引起数据不一致，如果是，用row格式。否则用statement格式。

写入机制

• 事务开始，写入binlog cache中，事务提交，写入到磁盘binlog中。
• 每个线程都有一个块内存作为binlog cache。（因为每个事务binlog不能被拆开，无论事务多大，也要确保一次性写入）。
• 我们可以通过binlog_cache_size参数控制单个线程 binlog cache 大小，如果存储内容超过了这个参数，就要暂存到磁盘（Swap）。

write和fsync的时机，可以由参数sync_binlog控制，默认是0。

• 0:每次事务提交只write，系统自行判断fsync
• 1:每次提交都fsync。
• N(N>1)：每次都只write，N个事务后fsync

两阶段提交

redo log 容灾恢复 

binlog 集群架构

虽然它们都属于持久化的保证，但是侧重点不同。

在执行更新语句过程，会记录redo log与binlog两块日志，以基本的事务为单位，redo log在事务执行过程中可以不断写入，而binlog只有在提交事务时才写入，所以redo log与binlog的写入时机不一样。

• 执行器调用存储引擎更新数据行

• 存储引擎将数据记入内存中，并写redo log，并将redo log状态设置为prepare，并返回

• 执行器生成binlog，并写入磁盘

• 执行器调用存储引擎的提交事务接口

• 存储引擎将刚刚的redo log状态设置为commit

回滚

• binlog发生异常，redo log处于prepare阶段，没有binlog日志
• relog commit异常，binlog日志不完整

提交

• 无异常提交
• relog commit异常，binlog日志完整

3.undo log

undo log(回滚日志) 原子性和MVCC实现

四、锁和MVCC

1.行锁和表锁

行锁作用在索引之上的。SQL命中了索引，锁住的就是命中条件内的索引节点。

若没有命中索引，那我们就只能锁整个表。

2.行锁分类

• 读锁（共享锁、S锁）
• 写锁（排它锁、X锁）

读锁是共享的，多个事务可以同时读取同一个资源，但不允许其他事务修改。写锁是排他的，写锁会阻塞其他的写锁和读锁。

read uncommit隔离级别下，读不会加任何锁，而写会加排他锁。

这样会导致在读取时候其他事务还没提交的数据，成为脏读。脏读是接受不了的，但是如果加读锁的话，更新数据的时候，没办法读取了，这样会极大的降低数据库性能。解决方案就是MVCC。

3.MVCC(多版本并发控制)

MVCC通过生成数据快照（Snapshot）并用这个快照提供一定级别（语句级或事务级）的一致性读取。

read commit隔离级别下，生成的就是语句级快照。

repeatable read隔离级别下，生成的就是事务级的快照。

• read commit隔离级别，读取的时候生成一个版本号，等到其他事务commit了之后，才会读取到最新的已经commit的"版本号"数据。这样就避免了脏读。但是不能避免不可重复读。
• repeatable read隔离级别，每次读取的都是当前事务的版本，即使当前数据被其他事务修改了，也只会读取到当前事务版本的数据。解决了部分幻读问题，但是如果是当前读，则需要配合间隙锁来解决幻读的问题当执行当前读时，会锁定读取到的记录的同时，锁定它们的间隙，防止其它事务在查询范围内插入数据。只要我不让你插入，就不会发生幻读。
• serializeble隔离级别的，不允许事务的并发，事务与事务之间执行是串行的。效率最低，但是最安全。

4.MVCC原理

通过read view和undo log来实现的。

undo log帮我们找到版本的数据。

read view帮我们找到是哪个版本。