数据定义语言DDL（Data Ddefinition Language）CREATE，DROP，ALTER

数据查询语言DQL（Data Query Language）SELECT

数据操纵语言DML（Data Manipulation Language）INSERT，UPDATE，DELETE

数据控制功能DCL（Data Control Language）GRANT，REVOKE，COMMIT，ROLLBACK

1.1 三大范式

第一范式：字段具有原子性，不可再分（字段单一职责）

第二范式：满足第一范式，每行应该被唯一区分，加一列存放每行的唯一标识符，称为主键（非主属性都要依赖主键）

第三范式：满足一二范式，且一个表不能包含其他表已存在的非主键信息（不间接依赖即不存在其他表的非主键信息）

范式优点与缺点：

优点：范式化，重复冗余数据少，更新快，修改少，查询时更少的 distinct

缺点：因为一个表不存在冗余重复数据，查询可能造成很多关联，效率变低，可能使一些索引策略无效，范式化将列存在不同表中，这些列若在同一个表中可以是一个索引。

1.2InnoDB 与 MyISAM 区别

MySQL 5.5 之前，MyISAM 引擎是默认存储引擎，5.5 版本之后，InnoDB 是默认存储引擎。

（1）InnoDB 支持事务（实现了 SQL 标准定义的四个隔离级别，具有提交和回滚事务的能力。默认使用的 REPEATABLE-READ（可重读）隔离级别是可以解决幻读问题发生的（基于 MVCC 和 Next-Key Lock））、外键、行锁和表锁（默认为行级锁），被用来处理大量短期事务，MyISAM 不支持事务、外键、只支持表锁。

（2）索引实现

MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是两者的实现方式不太一样。

• InnoDB 引擎中，其数据文件本身就是索引文件。其表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶子节点 data 域保存了完整的数据记录。
• MyISAM 索引文件和数据文件是分离的，索引保存的是数据文件的指针。
• InnoDB 数据与索引一起保存.ibd，MyISAM 表结构.frm 索引.myi 数据.myd。

InnoDB 聚簇索引，MyISAM 非聚簇索引（数据和索引分开存储）。

（3）MyISAM 支持全文索引，InnoDB5.6 后支持。

（4）MyISAM 查询更优，InnoDB 更新更优。

使用场景：

MyISAM 适合读多，更新少的场景，MyISAM 使用非聚簇索引，数据和索引分开存的，因此有读取更快的说法。而 InnoDB 数据和索引存一起的，数据量大时，一个内存页大小固定，读进内存的数据就多一点(数据量小看不出差距，数据量大时差距就明显）。因为 MyISAM 只把索引指针读进内存，可以存更多，查询速度也就更快，而且 InnoDB 还需要维护其他数据，比如其他隐藏字段 row_id、tx_id 等。

InnoDB 适合插入更新频繁的：索引与数据一起放，建立索引更复杂，使用行锁，更新频繁效率更高。需要事务，高并发场景用 Innodb：Innodb 支持事务，采用行锁。

MVCC

锁的粒度过大会导致性能的下降， MySQL 的 InnoDB 引擎下存在一种性能更优越的 MVCC 方法。

MVCC 是 Multi-Version Concurremt Control 的简称，意思是基于多版本的并发控制协议，通过版本号避免同一数据在不同事务间的竞争。它主要是为了提高数据库的并发读写性能，不用加锁就能让多个事务并发读写。

MVCC 的实现依赖于隐藏列、Undo log、 Read View 。标准的 SQL 隔离级别定义里，REPEATABLE-READ(可重复读)是不可以防止幻读的。但是 InnoDB 实现的 REPEATABLE-READ 隔离级别其实是可以解决幻读问题发生的，主要有下面两种情况：

• 快照读 ：由 MVCC 机制来保证不出现幻读。
• 当前读 ：使用 Next-Key Lock（临键锁） 进行加锁来保证不出现幻读，Next-Key Lock 是行锁（Record Lock）和间隙锁（Gap Lock）的结合，行锁只能锁住已经存在的行，为了避免插入新行，需要依赖间隙锁。

InnoDB 存储引擎在分布式事务的情况下一般会用到 SERIALIZABLE 隔离级别。

1.3 自增主键

MySQL 5.7 及之前的 InnoDB 引擎的自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值 max(id)，然后将 max(id)+1 作为这个表当前的自增值。MySQL 8.0 版本后，自增值的变更记录在了 redo log 中，重启的时候依靠 redo log 恢复重启之前的值，这才有了“自增值持久化”的能力。也就是实现了“如果发生重启，表的自增值可以恢复为 MySQL 重启前的值”。

推荐自增id作为主键问题

• 普通索引的 B+ 树上存放的是主键索引的值，如果该值较大，会「导致普通索引的存储空间较大」
• 使用自增 id 做主键索引新插入数据只要放在该页的最尾端就可以，直接「按照顺序插入」，不用刻意维护
• 页分裂容易维护，当插入数据的当前页快满时，会发生页分裂的现象，如果主键索引不为自增 id，那么数据就可能从页的中间插入，页的数据会频繁的变动，「导致页分裂维护成本较高」

MySQL 8.0 版本：

自增值修改机制：如果插入数据时 id 字段指定为 0、null 或未指定值，那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段；如果插入数据时 id 字段指定了具体的值，就直接使用语句里指定的值。

自增值新增机制：如果准备插入的值>=当前自增值，新的自增值就是“准备插入的值+1”；否则，自增值不变。

为什么自增主键不连续

1.在 MySQL 5.7 及之前的版本，自增值保存在内存里，并没有持久化。

2.事务回滚（自增值不能回退，因为并发插入数据时，回退自增 ID 可能造成主键冲突）。

3.唯一键冲突（由于表的自增值已变，但是主键发生冲突没插进去，下一次插入主键=现在变了的子增值+1，所以不连续）

eg：假设，表 t 里面已经有了(1,1,1)这条记录，这时我再执行一条插入数据命令：

insert into t values(null, 1, 1); (自增 id,唯一键 c,普通字段 d)

这个语句的执行流程就是：

1.执行器调用 InnoDB 引擎接口写入一行，传入的这一行的值是(0,1,1);

2.InnoDB 发现用户没有指定自增 id 的值，获取表 t 当前的自增值 2；

3.将传入的行的值改成(2,1,1)；

4.将表的自增值改成 3；

5.继续执行插入数据操作，由于已经存在 c=1 的记录，所以报 Duplicate key error，语句返回。

这个表的自增值改成 3，是在真正执行插入数据的操作之前。这个语句真正执行的时候，因为碰到唯一键 c 冲突，所以 id=2 这一行并没有插入成功，但也没有将自增值再改回去。所以，在这之后，再插入新的数据行时，拿到的自增 id 就是 3。也就是说，出现了自增主键不连续的情况。

1.4 索引

索引可以提高查询速度，查询使用优化隐藏器提高性能，但是也会占据物理空间，降低增删改的速度，因为还要操作索引文件。索引实现本质就是一个查找算法。

二叉树：当数据量庞大时，二叉树的深度会变得非常大，索引树会变成参天大树，每次查询会导致很多磁盘 IO。

多叉树：多叉树解决了了树的深度大的问题。B+树的叶子节点存放了所有的索引值。

创建索引方法：

• ALTER TABLE table_name ADD INDEX index_name (column_list)；
• CREATE INDEX index_name ON table_name (column_list)；
• 在 CREATE TABLE 时创建

1.4.1 常见索引类型

• 数据结构角度（实现层面）

B-Tree 索引（采用 B+树的数据结构）；哈希索引（基于哈希表实现）；R-Tree 索引；全文索引

B-tree 索引能够加快访问数据的速度，不需要进行全表扫描，而是从索引树的根节点层层往下搜索，在根节点存放了索引值和指向下一个节点的指针。其特点：

1.在叶子节点存放所有的索引值，非叶子节点值是为了更快定位包含目标值的叶子节点

2.叶子节点的值是有序的

3.叶子节点之间以链表形式关联

对于 InnoDb 存储引擎的 B-tree 索引，会按以下步骤通过索引找到行数据

• 如果使用了聚簇索引(主键)，则叶子节点上就包含行数据，可直接返回
• 如果使用了非聚簇索引(普通索引)，则在叶子节点存了主键，再根据主键查询一次上面 的聚簇索引，最后返回数据

对于 MyISAM 存储引擎的 B-tree 索引，会按以下步骤通过索引找到行数据

• 在 MyISAM 的索引树的叶子节点上除了索引值之外即没存储主键，也没存储行数据，而是存了指向行数据的指针，根据这个指针在从表文件查询数据。

哈希索引是基于哈希表（键值对的集合）来实现的，只有精确匹配所有列才能生效。

既然哈希表这么快，为什么 MySQL 没有使用其作为索引的数据结构呢？ 主要是因为 Hash 索引不支持顺序和范围查询。假如我们要对表中的数据进行排序或者进行范围查询，那 Hash 索引可就不行了，并且每次 IO 只能取一个。

• 物理存储角度（应用层面）

普通索引：可以重复。普通索引的唯一作用就是为了快速查询数据。

自增主键：一般会建立与业务无关的自增主键，保证空间利用率，不会触发叶子节点分裂。主键自增是无法保证完全自增的，遇到唯一键冲突、事务回滚等都可能导致不连续。

主键索引：唯一不为空，叶子结点存了行记录数据，InnoDB中也称聚簇索引（clustered index）。

非主键索引：非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引（secondary index），用二级索引查需要回表操作（根据二级索引查到主键，再根据主键去主键索引查）。二级索引又称为辅助索引。

唯一索引：唯一，可为空，表中只有一个主键索引，可多个唯一索引。建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。

单列索引：以某一个字段为索引。

联合索引：两个及以上字段联合组成的索引。使用时需要注意满足最左匹配原则！一次性最多联合16个。

覆盖索引：指一个索引包含或覆盖了所有需要查询字段的值，无需回表查询，即索引本身存了对应的值。

覆盖索引好处：1.避免了对主键索引(聚簇)的二次查询 2.由于不需要回表查询(从表数据文件)所以大大提升了 Mysql 缓存的负载。

回表：通过索引找到主键，再根据主键 id 去主键索引查。（覆盖索引就不走回表）[当所要查找的字段不在非主键索引树上时，需要通过叶子节点的主键值去主键索引上获取对应的行数据，这个过程称为回表操作。]

索引下推：5.6引入的，可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少最后的回表操作。[索引下推主要是减少了不必要的回表操作。对于查找出来的数据，先过滤掉不符合条件的，其余的再去主键索引树上查找。]

延迟关联：通过使用覆盖索引查询返回需要的主键，再根据主键关联原表获得需要的数据。

InnoDB存储: *.frm文件是一份定义文件，也就是定义数据库表是一张怎么样的表。*.ibd文件则是该表的索引，数据存储文件，既该表的所有索引树，所有行记录数据都存储在该文件中。

MyISAM存储：*.frm同上。*.MYD文件是MyISAM存储引擎表的所有行数据的文件。*.MYI文件存放的是MyISAM存储引擎表的索引相关数据的文件。MyISAM引擎下，表数据和表索引数据是分开存储的。

MyISAM查询：MyISAM下，主键索引和辅助键索引都属于非聚簇索引。查询不管是走主键索引还是非主键索引，在叶子结点得到的都是目的数据的地址，还需要通过该地址，才能在数据文件中找到目的数据。

普通索引与唯一索引

• 查询比较

查询会以页为单位将数据页加载进内存，不需要一条记录一条记录读取磁盘。然后唯一索引根据条件查询到记录时就返回结果，普通索引查到第一条记录往后遍历直到不满足条件，由于都在内存中，不需要磁盘读取那么大开销，带来的额外查询开销忽略不计，所以查询性能几乎一致。

• 更新比较

唯一索引由于更新时要检查唯一性，所以需要将数据页先加载进内存才能判断，此时直接操作内存，不需要操作 change buffer。

补充：普通索引若数据在内存中则直接更新，否则会将更新操作先记录到 channge buffer 中，等下一次查询将数据读到内存中再进行 change buffer 里相关更新操作后将数据返回，这样在写多读少场景就减少了磁盘 IO，若写完马上查询，就大可不必用 change buffer，不但没提高多少效率还造成维护 change buffer 额外消耗。

将 change buffer 的操作对应到原始数据页的操作称为 merge（在查询来时读到内存再修改数据，后台线程也会 merge，数据库正常关闭也会 merge）。

• 适合场景

写多读少，选用普通索引更好，可以利用 change buffer 进行性能优化减少磁盘 IO，将更新操作记录到 change bufer，等查询来了将数据读到内存再进行修改。唯一索引更加适合查询的场景。

1.4.2 聚簇索引与非聚簇索引

聚簇索引指的是索引和行数据在一起存储。也就是在一颗 B+树的叶子结点上存储的不仅是他的索引值，还有对应的某一行的数据。聚簇索引不是一种索引，而是一种数据存储组织方式 ！！！

crreate table test( col1 int not null, col2 int not null, PRIMARY KEY(col1), KEY(col2) )

如上所示，表 test 由两个索引，分别是主键 col1 和普通索引 col2。这俩索引跟聚簇非聚簇有啥关系呢？

会生成一个聚簇索引和一个非聚簇索引(二级索引)，也就是说会组织两个索引树。主键索引会生成聚簇索引的树 以及以 col2 为索引的非聚簇索引的树。

InnoDb 将通过主键来实现聚簇索引 ，如果没有主键则会选选一个唯一非空索引来实现。如果没有唯一非空索引则会隐式生成一个主键。

下图是聚簇索引的数据组织方式。col1 为主键索引的聚簇索引树，索引列是主键 col1

可以看出叶子结点除了存储索引值 列 col1 (3994700)值 之外还存储了其他列的值，如列 col2 (92813),如果还有别的列的话也会存储，或者换句话说聚簇索引树在叶子节点上存储某个索引值对应的一行数据。

下图是非聚簇索引(二级索引)的数据组织方式。索引列是 col2

与聚簇索引不同的是非聚簇索引在索引树叶子节点上除了索引值之外只存了主键值。而聚簇索引则存了一行数据。

假如有一条 sql 语句 :

select * from test where col2=93;

上面这条语句会经历两次从索引树查找过程:

1.第一步从非聚簇索引的索引树上找到包含 col2=93 的叶子节点，并定位到行的主键 3

2.第二步 根据主键 3 在从聚簇索引定位包含 主键=3 的叶子节点并返回全部行数据。

以上说的都是基于 InnoDb 存储引擎的,MyISAM 是不支持聚簇索引的，因为他的数据文件和索引文件是相互独立存储的 MyISAM 存储引擎的索引树的叶子节点不会寸主键值，而存一个指向对应行的地址或者说是指针，然后再从表数据文件里去找，如下面图所示。

结论：

• 聚簇索引:

通常由主键或者非空唯一索引实现的，叶子节点存储了一整行数据

• 非聚簇索引：

又称二级索引，就是我们常用的普通索引，叶子节点存了索引值和主键值，在根据主键从聚簇索引查

InnoDB支持聚簇索引，MyISAM不支持聚簇索引。

1.4.3 索引使用策略

索引设置原则：

• 出现在 where 子句或则连接子句中的列
• 基数小的表没必要
• 使用短索引，如果索引长字符串列，应该指定前缀长度（index(filed(10))）
• 定义有外键的数据列一定索引
• 不要过度索引
• 更新频繁的不适合
• 区分度不高的不适合，如性别
• 尽量扩展索引，别新建索引，如(a)->(a,b)

字符串字段建立索引方法

1、直接创建完整索引，这样可能比较占用空间；

2、创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；

3、倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；

4、额外用一个字段进行索引，额外计算开销。

总结：索引设计原则要求查询快，占用空间少；一般建在 where 条件，匹配度高的；要求基数大，区分度高，不要过大索引，尽量扩展，用联合索引，更新频繁不适合、使用短索引。

索引失效场景：

• 以“%”开头的 like 语句，索引无效，后缀“%”不影响
• or 语句前后没有同时使用索引
• 列类型是字符串，一定要在条件中将数据用引号引用，否则失效（隐式转换）
• 如果 mysql 估计使用全表扫描比索引快，则不用索引（键值少，重复数据多）
• 组合索引要遵守最左前缀原则——不使用第一列索引 失效
• 在索引字段上使用 not，<>，！= （对它处理是全表扫描）
• 对索引字段进行计算操作，字段使用函数也会失效
• is null

1.4.4 Explain

type：表示 MySQL 在表中找到所需行的方式，或者叫访问类型

• type=ALL，全表扫描，MySQL 遍历全表来找到匹配行
• type=index，索引全扫描
• type=range，索引范围扫描
• type=eq_ref，唯一索引
• type=NULL，MySQL 不用访问表或者索引，直接就能够得到结果（性能最好）

possible_keys: 表示查询可能使用的索引

key: 实际使用的索引

key_len: 使用索引字段的长度

rows: 扫描行的数量

Extra：

• using index：覆盖索引，不回表
• using where：回表查询
• using filesort：需要额外的排序，不能通过索引得到排序结果

1.4.5 其它

B+树、hash、红黑树：

hash：底层是哈希表实现，等值查询，可以快速定位，一般情况效率很高，但不稳定，当出现大量键重复哈希冲突，效率下降，不支持范围查询，无法用于排序分组，无法模糊查询，不支持多列索引的最左前缀匹配，任何时候都避免不了回表操作等。

B+树：非叶子结点不存 data，只存 key，查询更稳定，增大了广度(B+树出度更大，树高矮，节点小，磁盘 IO 次数少)；叶子结点下一级指针（范围查询）；索引冗余。B+树的页节点都是由双向列表连接的，而页里面的记录则是用单向链表连接的，所以获取区间数据也会更高效。

与红黑树相比：

更少查询次数：B+树出度更大，树高更低，查询次数更少

磁盘预读原理：为了减少 IO 操作，往往不严格按需读取，而是预读。B+树叶子结点存储相临，读取会快一些。

存储更多索引结点：B+树只在叶子结点储存数据，非叶子结点存索引，而一个结点就是磁盘一个内存页，内存页大小固定，那么相比 B 树这些可以·存更多的索引结点，出度更大，树高矮，查询次数少，磁盘 IO 少。

B+树比B树优势在于：

B+ 树非叶子节点存储的只是索引，可以存储的更多。B+树比B树更加矮胖，IO次数更少。

B+ 树叶子节点前后管理，更加方便范围查询。同时结果都在叶子节点，查询效率稳定。

B+树中更有利于对数据扫描，可以避免B树的回溯扫描。

B树和B+ 树

• B 树的所有节点既存放键(key) 也存放数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
• B 树的叶子节点都是独立的；B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
• B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

1.5 事务

事务：一系列操作组成，要么全部成功，要么全部失败。事务 ACID 特性：

原子性（Atomicity）：一些列操作要么全部成功，要么全部失败。undolog（记录事务开始前的老版本数据，可以保证原子操作，回滚，实现 MVCC 版本链）。

一致性（Consistency）：数据库总时从一个一致状态变到另一个一致状态（事务修改前后的数据总体保证一致转账）

隔离性（Isolation）：事务的结果只有提交了其他事务才可见。MVCC--多版本并发控制

持久性（Durability）：事务提交后，对数据修改永久的。redo log（记录事务开启后对数据的修改，可用于 crash-safe）

事务的并发问题：

脏读：读到未提交的数据

不可重复读：一个事务下，两次读取数据不一致（侧重内容数据的修改）

幻读：事务 A 按照一定条件进行数据读取， 期间事务 B 插入了相同搜索条件的新数据，事务 A 再次按照原先条件进行读取时，发现了事务 B 新插入的数据 称为幻读（侧重新增或删除，插入数据读到多了一行）

隔离级别原理及解决问题分析：

MySQL 中 RR 级别的隔离是已经实现解决了脏读，不可重复读和幻读的。

读未提交：原理：直接读取数据，不能解决任何并发问题

读已提交：读操作不加锁，写操作加排他锁，解决了脏读。原理：利用 MVCC 实现，每一句语句执行前都会生成 Read View（一致性视图）

可重复读：MVCC 实现，只有事务开始时会创建 Read View，之后事务里的其他查询都用这个 Read View。解决了脏读、不可重复读，快照读（普通查询，读取历史数据）使用 MVCC 解决了幻读，当前读（读取最新提交数据）通过间隙锁解决幻读（lock in share mode、for update、update、detete、insert），间隙锁在可重复读下才生效。（默认隔离级别）

可串行化：原理：使用锁，读加共享锁，写加排他锁，串行执行

总结：读已提交和可重复读实现原理就是 MVCC Read View 不同的生成时机。可重复读只在事务开始时生成一个 Read View，之后都用的这个；读已提交每次执行前都会生成 Read View。

脏页（内存数据页和磁盘数据页不一致）

刷脏页情景：

• redo log 写满了，停止所有更新操作，将 checkpoint 向前推进，推进那部分日志的脏页更新到磁盘。
• 系统内存不够，需要将一部分数据页淘汰，如果是干净页，直接淘汰就行了，脏页的话，需要全部同步到磁盘。
• mysql 自认为空闲时
• mysql 正常关闭之前

1.6日志

WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。事务在提交写入磁盘前，会先写到 redo log 里面去。如果直接写入磁盘涉及磁盘的随机 I/O 访问，涉及磁盘随机 I/O 访问是非常消耗时间的一个过程，相比之下先写入 redo log，后面再找合适的时机批量刷盘能提升性能。

为了保证事务 ACID 中的一致性与原子性，mysql 采用 WAL，预写日志，先写日志，合适时再写磁盘。

innodb 引擎有 undo log（回滚日志） 与 redo log（重做日志），mysql server 级别有 bin log（归档日志）。

binlog 是MySQL server层的日志，而redo log 和undo log都是引擎层（InnoDB）的日志，要换其他数据引擎那么就未必有redo log和undo log了。

1.6.1UndoLog

作用：undolog 记录事务开始前老版本数据，用于实现回滚，保证原子性，实现 MVCC，会将数据修改前的旧版本保存在 undolog，然后行记录有个隐藏字段回滚指针指向老版本。一般是逻辑日志。redo log 是属于引擎层(innodb)的日志，redo log 和undo log的核心是为了保证innodb事务机制中的持久性和原子性，事务提交成功由redo log保证数据持久性，而事务可以进行回滚从而保证事务操作原子性则是通过undo log 来保证的。

undo log 的主要应用场景分别：

事务回滚 ：后台线程会不定时的去刷新buffer pool中的数据到磁盘，但是如果该事务执行期间出现各种错误(宕机)或者执行rollback语句，那么前面刷进去的操作都是需要回滚的，保证原子性，undo log就是提供事务回滚的。

MVCC：当读取的某一行被其他事务锁定时，可以从undo log中分析出该行记录以前的数据版本是怎样的，从而让用户能够读取到当前事务操作之前的数据——快照读。

MVCC 的实现依赖于：隐藏字段、Read View、undo log。在内部实现中，InnoDB 通过数据行的 DB_TRX_ID 和 Read View 来判断数据的可见性，如不可见，则通过数据行的 DB_ROLL_PTR 找到 undo log 中的历史版本。每个事务读到的数据版本可能是不一样的，在同一个事务中，用户只能看到该事务创建 Read View 之前已经提交的修改和该事务本身做的修改。

undo log 数据主要分两类：

insert undo log

insert 操作的记录，只对事务本身可见，对其他事务不可见(这是事务隔离性的要求)，故该undo log可以在事务提交后直接删除，不需要进行purge操作。

update undo log

update undo log记录的是对delete和update操作产生的undo log。该undo log可能需要提供MVCC机制，因此不能在事务提交时就进行删除。提交时放入undo log链表，等待purge线程进行最后的删除。

在InnoDB存储引擎中，undo log使用rollback segment回滚段进行存储，每隔回滚段包含了1024个undo log segment。MySQL5.5之后，一共有128个回滚段。即总共可以记录128 * 1024个undo操作。

每个事务只会使用一个回滚段，一个回滚段在同一时刻可能会服务于多个事务。

1.6.2RedoLog

redo log 它是物理日志，记录内容是“在某个数据页上做了什么修改”，属于 InnoDB 存储引擎，称为重做日志。每条 redo 记录由“表空间号+数据页号+偏移量+修改数据长度+具体修改的数据”组成。

作用：会记录事务开启后对数据做的修改，crash-safe

特性：空间一定，写完后会循环写，有两个指针 write pos 指向当前记录位置，checkpoint 指向将擦除的位置，redolog 相当于是个取货小车，货物太多时来不及一件一件入库太慢了这样。就先将货物放入小车，等到货物不多或则小车满了或则店里空闲时再将小车货物送到库房。用于 crash-safe，数据库异常断电等情况可用 redo log 恢复。

刷盘时机：

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

• 0 （延迟写）：设置为 0 的时候，表示每次事务提交时都只把 redo log 留在redo log buffer中，开启一个后台线程，每1s刷新一次到磁盘中。

为0时，如果MySQL挂了或宕机可能会有1秒数据的丢失。

• 1（实时写，实时刷） ：设置为 1 的时候，表示每次事务提交时都将进行刷盘操作（默认值）

为1时， 只要事务提交成功，redo log记录就一定在硬盘里，不会有任何数据丢失。如果事务执行期间MySQL挂了或宕机，这部分日志丢了，但是事务并没有提交，所以日志丢了也不会有损失。

• 2（实时写，延迟刷）：设置为 2 的时候，表示每次事务提交时都只把 redo log buffer 内容写入 page cache，具体刷盘时机不确定。

为2时， 只要事务提交成功，redo log buffer中的内容只写入文件系统缓存（page cache）。如果仅仅只是MySQL挂了不会有任何数据丢失，但是宕机可能会有1秒数据的丢失。

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

除上面几种机制外，还有其它两种情况会把redo log buffer中的日志刷到磁盘：

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

2、当 redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动刷盘。

也就是说，一个没有提交事务的 redo log 记录，也可能会刷盘。原因：因为在事务执行过程 redo log 记录是会写入redo log buffer 中，这些 redo log 记录会被后台线程刷盘。

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

所以，如果数据写满了但是还没有来得及将数据真正的刷入磁盘当中，那么就会发生「内存抖动」现象，从肉眼的角度来观察会发现 mysql 会宕机一会儿，此时就是正在刷盘了。

日志文件组中还有两个重要的属性，分别是 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 推进一下。

两阶段提交：

redo log（重做日志）让InnoDB存储引擎拥有了崩溃恢复能力。binlog（归档日志）保证了MySQL集群架构的数据一致性。虽然它们都属于持久化的保证，但是则重点不同。 在执行更新语句过程，会记录redo log与binlog两块日志，以基本的事务为单位，redo log在事务执行过程中可以不断写入，而binlog只有在提交事务时才写入，所以redo log与binlog的写入时机不一样。

何为两阶段提交？（2PC）mysql 中在 server 层级别有个 binlog 日志，归档日志，用于备份，主从同步复制，如果采用一主多从架构，主备切换，那就必须用到 binlog 进行主从同步。此时事务提交就必须保证 redolog 与 binlog 的一致性，一般情况没有开启 binlog 日志，事务提交不会两阶段提交，若需要主从同步就必须开启 binlog 使用两阶段提交保证数据一致性。

为什么要两阶段提交？为了保证 binlog 和 redo log 两份日志的逻辑一致，最终保证恢复到主备数据库的数据是一致的，采用两阶段提交的机制。保证事务在多个引擎的原子性。

两阶段提交过程？

Prepare 阶段：InnoDB 将回滚段 undolog 设置为 prepare 状态；将 redolog 写文件并刷盘；

（1、先写 redolog，事务进入 prepare 状态）

Commit 阶段：Binlog 写入文件；binlog 刷盘；InnoDB commit；

（2、prepare 成功，binlog 写盘，然后事务进入 commit 状态，同时会在 redolog 记录 commite 标识，代表事务提交成功）

redolog 与 binlog 怎样联系起来的？（XID）

• 崩溃恢复的时候，会按顺序扫描 redo log，若 redolog 既有 prepare 又有 commit，直接提交
• 如果碰到只有 prepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务。

2pc 不同时刻的崩溃恢复？

1、redolog 有 commite 标识，事务完整，直接提交事务

2、若 redolog 里面的事务只有完整的 prepare，则判断对应事务的 binlog 是否存在并完整 （是-提交事务 | 否-回滚事务）

为什么不直接修改磁盘中的数据？

因为直接修改磁盘数据的话，它是随机IO，修改的数据分布在磁盘中不同的位置，需要来回的查找，所以命中率低，消耗大，而且一个小小的修改就不得不将整个页刷新到磁盘，利用率低；

与之相对的是顺序IO，磁盘的数据分布在磁盘的一块，所以省去了查找的过程，节省寻道时间。

使用后台线程以一定的频率去刷新磁盘可以降低随机IO的频率，增加吞吐量，这是使用buffer pool的根本原因。

如果是写 redo log，一行记录可能就占几十 Byte，只包含表空间号、数据页号、磁盘文件偏移 量、更新值，再加上是顺序写，所以刷盘速度很快。所以用 redo log 形式记录修改内容，性能会远远超过刷数据页的方式，这也让数据库的并发能力更强。

同为操作数据变更的日志，有了binlog为什么还要redo log？

两者记录的数据变更粒度是不一样的。以修改数据为例，binlog 是以表为记录主体，在ROW模式下，binlog保存的表的每行变更记录。

MySQL 是以页为单位进行刷盘的，每一页的数据单位为16K，所以在刷盘的过程中需要把数据刷新到磁盘的多个扇区中去。而把16K数据刷到磁盘的每个扇区里这个过程是无法保证原子性的，如果数据库宕机，那么就可能会造成一部分数据成功，而一部分数据失败的情况。而通过 binlog 这种级别的日志是无法恢复的，因为一个update可能更改了多个磁盘区域的数据，所以这个时候得需要通过redo log这种记录到磁盘数据级别的日志进行数据恢复。

由以上两者的对比可知：binlog 日志只用于归档，只依靠 binlog 是没有 crash-safe 能力的。

同样只有 redo log 也不行，因为 redo log 是 InnoDB特有的，且日志上的记录落盘后会被覆盖掉。因此需要 binlog和 redo log二者同时记录，才能保证当数据库发生宕机重启时，数据不会丢失。

1.6.3BinLog

binlog 是逻辑日志，用于主备同步，记录内容是语句的原始逻辑，属于MySQL Server 层。会记录所有涉及更新数据的逻辑操作（记录所有数据库表结构变更（例如create、alter table）以及表数据修改(insert、update、delete)的二进制日志），这其中不包括select、show，因为对数据没有修改，并且是顺序写。有 3 种格式，5.7.7之前默认statement，之后默认row，可以通过binlog_format参数指定：

row：基于行的复制，记录对数据库做出修改的语句所影响到的数据行以及对这些行的修改。优点是能够完全的还原或者复制日志被记录时的操作。缺点：记录每行数据，占空间，IO 压力大，性能消耗大

statement：基于SQL语句的复制，记录对数据库进行修改的语句本身，有可能会记录一些额外的相关信息。优点是 binlog 日志量少，IO 压力小，性能较高。缺点是由于记录的信息相对较少，在不同库执行时由于上下文的环境不同可能导致主备不一致。

mysql> delete from t where a>=4 and b<=5 limit 1;

主库是索引 a,那么删除 a=4；备库是索引 b,那么删除 b=5

mixed：混合使用上述两种模式，一般的语句使用 statment 方式进行保存，如果遇到一些特殊的函数，则使用 row 模式进行记录。MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则就用 statement 格式。但是在生产环境中，一般会使用 row 模式。

写入机制：

1、事务执行过程中将日志记录到 binlog cache（系统为 binlog 分配了一块内存，每个线程一份）

2、事务提交时，执行器把 binlog cache 里的完整事务写入到 binlog 中，并清空 binlog cache。

通过binlog_cache_size参数控制单个线程 binlog cache 大小，如果存储内容超过了这个参数，就要暂存到磁盘（Swap）。

write：把日志写到文件系统的 page cache，没有写磁盘，速度快；fsync：将数据持久化到磁盘的操作，这时才占磁盘 IOPS。write和fsync的时机是根据 sync_binlog 参数控制：

0——>只write，不fsync，默认值。机器宕机，page cache里面的 binglog 会丢失。

为0的时候，表示每次提交事务都只write，由系统自行判断什么时候执行fsync。

1——>每次fsync。

>1——>每次事务都write，等累积到N后才fsync。机器宕机，会丢失最近N个事务的binlog日志。

在出现IO瓶颈的场景里，将sync_binlog设置成一个比较大的值，可以提升性能。（可以提高 IO 性能，但是若发生异常，日志会丢失）

怎样判断 binlog 是否完整？

• statement 格式的 binlog，最后会有 COMMIT
• row 格式末尾有 XID event

redo log 与 binlog 的区别

• redo log 是 InnoDB 引擎特有的，只记录该引擎中表的修改记录。binlog 是 MySQL 的 Server 层实现的，会记录所有引擎对数据库的修改。
• redo log 是物理日志，记录的是在具体某个数据页上做了什么修改；binlog 是逻辑日志，记录的是这个语句的原始逻辑。
• redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的，binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
• binlog 日志没有 crash-safe 的能力，只能用于归档，而 redo log 有 crash-safe 能力；
• redo log 在事务执行过程中可以不断写入（刷盘设置为1，后台线程1s执行一次或者 redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候），而 binlog 只有在提交事务时才写入文件缓存系统；

MySQL 的架构

• 应用层：负责和客户端，响应客户端请求，建立连接，返回数据。
• 逻辑层：包括 SQK 接口，解析器，优化器，Cache 与 buffer。
• 数据库引擎层：有常见的 MyISAM,InnoDB 等等。
• 物理层：负责文件存储，日志等等

接下来以一条 sql 查询语句执行过程介绍各个部分功能。客户端执行一条 sql：

1、首先由连接器进行身份验证，权限管理

2、若开启了缓存，会检查缓存是否有该 sql 对应结果（缓存存储形式 key-vlaue，key 是执行的 sql，value 是对应的值）若开启缓存又有该 sql 的映射，将结果直接返回；（查询缓存：查询后的结果存储位置，MySQL8.0版本以后已经取消，因为查询缓存失效太频繁，得不偿失。）

3、分析器进行词法语法分析

4、优化器会生成执行计划、选择索引等操作，选取最优执行方案

5、然后来到执行器，打开表调用存储引擎接口，逐行判断是否满足查询条件，满足放到结果集，最终返回给客户端；若用到索引，筛选行也会根据索引筛选。

1.6.4RelayLog

relaylog 是中继日志，在主从同步的时候使用到，它是一个中介临时的日志文件，用于存储从 master 节点同步过来的 binlog 日志内容。

master 主节点的 binlog 传到 slave 从节点后，被写入 relay log 里，从节点的 slave sql 线程从 relaylog 里读取日志然后应用到 slave 从节点本地。从服务器 I/O 线程将主服务器的二进制日志读取过来记录到从服务器本地文件，然后 SQL 线程会读取 relay-log 日志的内容并应用到从服务器，从而使从服务器和主服务器的数据保持一致。

1.7优化

1、先设置慢查询（my.ini 或数据库命令）

方式一：修改配置文件 在 my.ini 增加几行: 主要是慢查询的定义时间（超过 2 秒就是慢查询），以及慢查询 log 日志记录（ slow_query_log）

[mysqlld]//定义查过多少秒的查询算是慢查询，我这里定义的是 2 秒long_query_time=2#5.8、5.1 等版本配置如下选项log-slow-queries="mysql_slow_query.log"#5.5 及以上版本配置如下选项slow-query-log=Onslow_query_log_file="mysql_slow_query. log"1/记录下没有使用索引的 querylog-query-not-using-indexestpspb16glos dndnorte/t

方式二：通过 MySQL 数据库开启慢查询:

mysql>set global slow_query_log=ONmysql>set global long_query_time = 3600;mysql>set global log_querise_not_using_indexes=ON;

2、分析慢查询日志

3、定位低效率 sql（show processlist）

4、explain 分析执行计划（是否索引失效，用到索引没，用了哪些）

5、优化（索引+sql 语句+数据库结构优化+优化器优化+架构优化）

索引

1、尽量覆盖索引，5.6 支持索引下推

2、组合索引符合最左匹配原则

3、避免索引失效

4、再写多读少的场景下，可以选择普通索引而不要唯一索引

更新时，普通索引可以使用 change buffer 进行优化，减少磁盘 IO,将更新操作记录到 change bufer，等查询来了将数据读到内存再进行修改.

5、索引建立原则（一般建在 where 和 order by，基数要大，区分度要高，不要过度索引，外键建索引）

sql 语句

1、分页查询优化

该方案适用于主键自增的表，可以把 Limit 查询转换成某个位置的查询。

select * from tb_sku where id>20000 limit 10;

2、优化 insert 语句

多条插入语句写成一条

在事务中插数据

数据有序插入（主键索引）

数据库结构优化

1、将字段多的表分解成多个表

有些字段使用频率高，有些低，数据量大时，会由于使用频率低的存在而变慢，可以考虑分开。

2、对于经常联合查询的表，可以考虑建立中间表

优化器优化

1、优化器使用 MRR

原理：MRR【Multi-Range Read】将 ID 或键值读到 buffer 排序，通过把「随机磁盘读」，转化为「顺序磁盘读」，减少磁盘 IO，从而提高了索引查询的性能。

mysql >set optimizer_switch='mrr=on';explain 查看 Extra 多了一个 MRRexplainselect*from stu where age between 10 and 20;

对于 Myisam，在去磁盘获取完整数据之前，会先按照 rowid 排好序，再去顺序的读取磁盘。

对于 Innodb，则会按照聚簇索引键值排好序，再顺序的读取聚簇索引。

磁盘预读：请求一页的数据时，可以把后面几页的数据也一起返回，放到数据缓冲池中，这样如果下次刚好需要下一页的数据，就不再需要到磁盘读取（局部性原理）

索引本身就是为了减少磁盘 IO，加快查询，而 MRR，则是把索引减少磁盘 IO 的作用，进一步放大

https://zhuanlan.zhihu.com/p/148680235

架构优化

读/写分离（主库写，从库读）

慢查询参数：long_query_time 默认值为 10s。long_query_time 属于 dynamic 类型的参数。set global long_query_time=xx；show variables like '%slow%';

基数：指 MySql 表中某一列的不同值的数量。如果这一列是唯一索引，则基数==行数；如果这一列是性别，枚举值只有男女，那它的基数就是 2。Cardinality 越高，列就越有成为索引的价值。MySQL 执行计划也会基于 Cardinality 选择索引。

InnoDB 更新基数的时机：通过 innodb_stats_auto_recalc 参数控制 MySQL 是否主动重新计算这些持久性的信息。默认为 1 表示 true、0 表示 false。默认情况下当表中的行变化超过 10%时，重新计算基数信息。

基数的估算：基数并不会实时更新！它是通过随机采样数据页的方式统计出来的一个估算值。而且随机采样的页数可以通过参数：
innodb_stats_persistent_sample_pages 设置，默认值是 20。

基数的持久化机制：通过参数 innodb_stats_persistent 控制是否持久化基数，默认为 off。当然你可以为一个单独的表设置 STATS_PERSISTENT=1 那么它的 innodb_stats_persistent 将自动被启用。开启它的好处是：重启 MySQL 不会再重复计算这个值，加快重启速度。

主动更新基数的方式：执行 analyze table tableName;会触发 InnoDB 更新基数

1.8 常见问题

1.8.1char 和 varchar(MySql)

1.区别：

• char 存储不可变长度的字符串，当字符集编码不同时，能存储的字节数是会变化的；而 varchar 可以存储可变长度的字符串。例如：

插入时：char(5)表示可以存储 5 个字符而不是 5 个字节，它占用的存储空间一直是 5 个字符大小。可以往 a char(5)的列中插入"abc"3 个字符，在 MySql 底层会将"abc"后面追加两个空格字符"abc "。

检索时：MySql 会自动做一次 trim()，返回"abc"。

char(5)表示

2.create table t(a char(5)) charset=utf8 engine=innodb;insert into t select "12345";insert into t select "青天白日梦";是否成功？

utf8 中，每个数字占用一个 byte，每个中文占用 3 个 byte。因此，当 charset 为 utf8 时，char(5)这一列可以存储的字节范围是[5*1,5*3]。上述插入语句执行成功。

3.create table t1(a varchar(2)) charset=utf8 engine=innodb;insert into t1 select "abcd";是否成功？

varchar 的期望长度是 2，但是插入的字符串长度为 4，这时插入语句能否执行成功取决于 sql mode。通过 select @@sql_mode\G 查询，当值中包含 STRICT_TRANS_TABLES 时表示开启了严格模式。以上插入语句执行会报错。当值中包含 ONLY_FULL_GROUP_BY 时表示关闭了严格模式。以上插入语句执行会成功，而且 MySql 会将超出的字符砍掉，数据库中保存了"ab"。

1.8.2MySql 各种锁

• MDL(Metadata Lock 元数据锁)：通常不需要显示的使用，当我们对表进行 CRUD 操作时 MySql 会自动给表加元数据锁，并且这把锁会和所有企图改变表结构的 SQL 互斥。元数据锁的作用：当有用户对表执行 DML 相关操作时，其它线程不能改变表结构（想改也是可以的，的等排在它前面的 DML 全部执行完）。反之，当有线程在更改表结构时，其它线程需要执行的 DML 也会被阻塞。
• 表级别的锁：

a.表级共享锁 lock tables t read;

b.表级独占锁 lock tables t write;

c.释放表锁 unlock table;

d.其它锁全表的方式

begin;（#首先手动开启事务）

select * from t lock in share mode;（#检索时加上共享读锁）

这时其它线程如果执行 insert 会被阻塞。

• Record Lock（行锁也叫记录锁）：每次都会去锁住具体的索引记录。如果表没有索引，连主键索引也不存在，MySql 会为表生成一个隐式的主键索引。行锁添加操作：

begin;

update t set name=xxx where id=1;

commit;

此时会对 id=1 的行加锁。

Gap lock（间隙锁）：也是行锁的一种。它会锁定的是一个间隙范围，而不会锁住某条记录。其目的是为了防止同一事物的两次当前读出现幻读的情况。

Next-key-lock（）：行锁+gap 锁。

参考：
https://www.cnblogs.com/ZhuChangwu/p/15079210.html

select...for update;加锁情况

1.如果查询条件用了索引/主键，则会加行锁；2.如果是普通自动无索引/主键，则会加表锁。

1.8.3B+树存储数据

InnoDB的页结构

• 在InnoDB中，索引默认使用的数据结构为B+树，而B+树里的每个节点都是一个页，默认的页大小为16KB。
• 非叶子节点存的是索引值以及页的偏移量，而叶子节点上存放的则是完整的每行记录

非叶子节点能存多少数据

• 页默认16KB
• File Header、Page Header等一共占102个字节
• Infimum + Supremum分别占13个字节
• 记录头占5个字节
• id占为int，占4个字节
• 页目录的偏移量占4个字节

非叶子节点能存放的索引记录=  (页大小 - File Header - Page Header - ...) / ( 主键 + 页偏移量 + 下一条记录的偏移量)= （16KB - 128B) / (5B + 4B + 4B) =  16256 / 13=  1250 条

叶子节点能存多少数据

• 变长列表占1个字节
• null标志位忽略
• 记录头占5个字节
• id占为int，占4个字节
• name为VARCHAR，编码为UTF8，为了好算，所有行记录我都只用两个中文，那就是 2 * 3B = 6个字节
• 事务ID列占6个字节
• 回滚指针列占7个字节

叶子节点能存放的数据记录=  (页大小 - File Header - Page Header - ...) / ( 主键 + 字段 + 下一条记录的偏移量)= （16KB - 128B) / (1B + 5B + 4B + 6B + 6B + 7B) =  16256 / 29=  560 条

高为3的B+树能存多少行数据记录

• 根节点能放1250条索引记录
• 第二层能放1250 * 1250 = 1,562,500条索引记录
• 叶子节点 1250 * 1250 * 560 = 875,000,000条数据记录，八亿多条数据

B树和B+ 树

• B 树的所有节点既存放键(key) 也存放数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
• B 树的叶子节点都是独立的；B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
• B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

1.8.4drop、delete、truncate区别

（1）用法不同

• drop(丢弃数据): drop table 表名 ，直接将表结构都删除掉，在删除表的时候使用。
• truncate (清空数据) : truncate table 表名 ，只删除表中的数据，再插入数据的时候自增长 id 又从 1 开始，在清空表中数据的时候使用。
• delete（删除数据） : delete from 表名 where 列名=值，删除某一行的数据，如果不加 where 子句和truncate table 表名作用类似。

（2）属于不同的数据库语言

• truncate 和 drop 属于 DDL(数据定义语言)语句，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger。
• delete 语句是 DML (数据库操作语言)语句，这个操作会放到 rollback segment 中，事务提交之后才生效。

（3）执行速度不同

• delete命令执行的时候会产生数据库的binlog日志，而日志记录是需要消耗时间的，但是也有个好处方便数据回滚恢复。
• truncate命令执行的时候不会产生数据库日志，因此比delete要快。除此之外，还会把表的自增值重置和索引恢复到初始大小等。
• drop命令会把表占用的空间全部释放掉。

一般来说：drop > truncate > delete

1.9与oracle的区别

1、本质

• Oracle数据库是一个对象关系数据库管理系统（收费）重量级数据库。
• mysql是一个开源的关系数据库管理系统（免费）轻量级数据库。瑞典公司开发后被sun公司收购，sun又被oracle公司收购。

2、事务

• mysql在innodb存储引擎的行级锁的情况下才可支持事务，而Oracle则完全支持事务；
• mysql默认是自动提交，而Oracle默认不自动提交，需要用户手动提交，需要在写commit;指令或者点击commit按钮；
• mysql是read commited的隔离级别，而Oracle是repeatable read的隔离级别，同时二者都支持serializable串行化事务隔离级别，可以实现最高级别的读一致性。每个session提交后其他session才能看到提交的更改。Oracle通过在undo表空间中构造多版本数据块来实现读一致性，每个session查询时，如果对应的数据块发生变化，Oracle会在undo表空间中为这个session构造它查询时的旧的数据块。MySQL没有类似Oracle的构造多版本数据块的机制，只支持read commited的隔离级别。一个session读取数据时，其他session不能更改数据，但可以在表最后插入数据。session更新数据时，要加上排它锁，其他session无法访问数据。

3、数据持久性

• mysql默认提交sql语句，但如果更新过程中出现db或主机重启的问题，也许会丢失数据；
• oracle把提交的sql操作先写入了在线联机日志文件中，保持到了硬盘上，可以随时恢复。

4、逻辑备份

• oracle逻辑备份时不锁定数据，且备份的数据是一致的。
• mysql逻辑备份时要锁定数据，才能保证备份的数据是一致的，影响业务正常的dml使用。

5、性能诊断及管理工具

• mysql的诊断调优方法较少，主要有慢查询日志。管理工具较少。
• Oracle有各种成熟的性能诊断调优工具，能实现很多自动分析、诊断功能。比如awr、addm、sqltrace、tkproof等。管理工具较多。

6、锁级别

• mysql以表级锁为主，对资源锁定的粒度很大，如果一个session对一个表加锁时间过长，会让其他session无法更新此表中的数据。虽然InnoDB引擎的表可以用行级锁，但这个行级锁的机制依赖于表的索引，如果表没有索引，或者sql语句没有使用索引，那么仍然使用表级锁。
• oracle使用行级锁，对资源锁定的粒度要小很多，只是锁定sql需要的资源，并且加锁是在数据库中的数据行上，不依赖与索引。所以oracle对并发性的支持要好很多。

7、对象名称

• Oracle对所有对象名称都不区分大小写；
• mysql某些对象名称（如数据库和表）区分大小写（取决于底层操作系统）。

8、临时表

• mysql中，临时表是仅对当前用户会话可见的数据库对象，并且一旦会话结束，这些表将自动删除。
• Oracle中临时表一旦创建就会存在，直到它们被显式删除，并且对具有适当权限的所有会话都可见。但是，临时表中的数据仅对将数据插入表中的用户会话可见，并且数据可能在事务或用户会话期间持续存在。

9、语法区别

①、表字段类型

mysql的字符类型，如CHAR和VARCHAR的长度小于65535字节。Oracle支持4种字体类型：CHAR、NCHAR、NVARCHAR2和VARCHAR2。CHAR和NCHAR的最大长度为2000字节，NVARCHAR2和VARCHAR2最大长度为4000字节。

②、列默认值

mysql的非空字段也有空的内容，Oracle里定义了非空字段就不容许有空的内容。按mysql的NOT NULL来定义Oracle表结构，导数据的时候会产生错误。因此导数据时要对空字符进行判断，如果为NULL或空字符，需要把它改成一个空格的字符串。

③、引号处理

mysql里可以用双引号包起字符串，Oracle里只可以用单引号包起字符串。在插入和修改字符串前必须做单引号的替换：把所有出现的一个单引号替换成两个单引号。

④、主键

MySQL一般使用自动增长类型，在创建表的时候只要指定表的主键为auto increment。Oracle没有自动增长，主键一般使用序列，序列号的名称.NEXTVAL。

⑤、分页处理

mysql是直接在SQL语句中使用limit就可以实现分页。oracle则是需要用到伪劣ROWNUM和嵌套查询。

⑥、分组函数

Mysql中group by可以使用别名，Oracle 中不可以。

Oracle中出现在select列表中的字段或者出现在order by后面的字段，如果不是包含在分组函数中，那么该字段必须同时在group by子句中出现。Mysql没有此限制。

⑦、模糊查询

MYSQL里用字段名 like '%字符串%',ORACLE里也可以用字段名 like '%字符串%' 但这种方法不能使用索引，速度不快，用字符串比较函数 instr(字段名,'字符串')>0 会得到更精确的查找结果。

⑧、其它函数

1、时间格式化及时间计算函数

2、空值返0

说明：

nvl：基本语法为nvl(E1,E2)，意思是E1为null就返回E2，不为null就返回E1。

nvl2：nvl2函数的是nvl函数的拓展，基本语法为nvl2(E1,E2,E3)，意思是E1为null，就返回E3，不为null就返回E2。

IFNULL：基本语法为IFNULL(E1,E2)，假如E1不为NULL，则返回值为E1，否则为E2。

COALESCE：COALESCE(value,…)是一个可变参函数，可以使用多个参数。作用：接受多个参数，返回第一个不为NULL的参数，如果所有参数都为NULL，此函数返回NULL；当它使用2个参数时，和IFNULL函数作用相同。

3、长度函数

4、条件函数

说明：

IF(expr1,expr2,expr3)：如果expr1不为null，则返回expr2，否则返回expr3。

IF(value=val1, val2, val3)：如果value等于val1，则返回val2，否则返回val3。

case when then else end：如果value等于if1，则返回val1，如果value等于if2，则返回value2…如果value等于ifn，则返回valn，否则返回val。

5、trunc()函数

6、字符串整型转换函数

7、字符串连接符

8、空数据排序

MySQL

select * from USER A order by IF(ISNULL(A.REMARK),1,0),A.REMARK desc

select * from USER A order by IF(ISNULL(A.REMARK),0,1),A.REMARK desc

Oracle

SELECT * FROM USER A ORDER BY A.REMARK DESC NULLS FIRST

SELECT * FROM USER A ORDER BY A.REMARK DESC NULLS LAST

9、表(左/右)关联

Oracle左连接、右连接可以使用(+)来实现，MySQL只能使用left join ,right join等关键字。

10、merge into

MySQL不支持（merge into），但提供的replace into 和on duplicate key update可实现相似的功能。