MySQL数据库必知，满满干货！

1、关于数据库

1.1 数据库技术特点

1. 数据结构化
2. 数据共享
3. 数据独立性高
4. 数据统一管理

1.2 区分数据库、数据库管理系统、数据库系统

• 数据库：一个存在于计算机存储设备上的数据集合，该集合中的数据按照一定的数据模型进行组织、描述和存储。
• 数据库管理系统：一种介于用户和操作系统之间的数据库管理软件，它可以对数据库的建立、维护和运行进行管理。例如MySQL、MongoDB等。
• 数据库系统：由数据库及其管理软件组成的系统，适应数据处理的需要而发展的一种较为理想的数据处理系统。通常包含硬件、数据库、软件、用户这4部分。

1.3 数据模型

1. 定义：数据模型是数据库系统的核心和基础，它是对现实数据特征的抽象。
2. 组成要素

1. 数据结构：描述数据库系统的静态特征，主要研究数据本身的类型、内容、性质以及数据之间的联系等。
2. 数据操作：描述数据库系统的动态行为，是对数据库中的对象实例允许执行的操作集合，包含检索和更新。
3. 数据约束：数据与数据之间所具有的制约和存储规则，保证数据的正确性、有效性和相容性。

3. 数据模型的分类
4. 层次模型：用树形结构表示数据之间的联系，它的数据结构类似一棵倒置的树，有且仅有一个根节点，其余节点都是非根节点。
5. 网状模型：用网络结构表示数据之间的关系，允许有一个以上的节点双亲和至少有一个节点可以有多于一个的双亲。
6. 关系模型：以数据表的形式组织数据，实体之间的关系通过数据表的公共属性表示，结构简单明了，使用较为广泛。其完整性约束包括域完整性、参照完整性、实体完整性、用户自定义完整性。
7. 面向对象模型：用面向对象的思维方式来描述客观实体，继承了关系数据库已有的优势。

1.4 E-R图应用

• 元素

• 实体：用矩形表示，将实体名写在矩形框内表示实体。
• 属性：用椭圆表示，属性名标注在椭圆内，实体与属性之间用直的实线连接。
• 联系：用菱形表示，联系名标注在菱形框内，与实体使用实线连接，实线上标注联系类型(一对一、一对多、多对多)。

1.5 SQL的功能类别

1. 数据定义语言(DDL)：用于定义数据库、表等数据库对象，包括CREATE(创建)、ALTER(修改)、DROP(删除)语句。
2. 数据操纵语言(DML)：用于对数据库的数据操作，包括INSERT、DELETE、UPDATE语句。
3. 数据查询语言(DQL)：用于查询数据，SELECT语句。
4. 数据控制语言(DCL)：控制用户的访问权限，包括GRANT(加权限)、REVOKE(收回权限)、COMMIT(提交事务)、ROLLBACK(回滚事务)语句。

1.6 数据库的三大范式

1. 第一范式(1NF)：确保数据库表字段的原子性。
2. 第二范式(2NF)：首先要满足第一范式，另外包含两部分内容：

1. 表必须有一个主键
2. 非主键列必须完全依赖于主键，而不能只依赖于主键的一部分

3. 第三范式(3NF)：首先要满足第二范式，其次非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列A依赖于非主键列B，非主键列B依赖于主键的情况。
4. 2NF和3NF的区别

1. 2NF依据是非主键列是否完全依赖于主键，还是依赖于主键的一部分。
2. 3NF依据是非主键列是直接依赖于主键，还是直接依赖于非主键。

2、事务

2.1 事务的定义

在MySQL中，事务就是针对数据库的操作，它可以由一条或多条SQL语句组成。在执行过程中，只要有一条SQL执行失败或发生错误，其他语句都不会执行。

2.2 事务的特性

1. 原子性：一个事务必须被视为一个不可分割的最小工作单元，只有事务中所有的数据库操作都执行成功了，整个事务才算执行成功。
2. 一致性：事务将数据库从一个一致状态转变为下一个一致状态。在事务完成之前和完成之后，都要保证数据库内数据处于一致状态。
3. 隔离性：一个事务在执行时，不会受到其他事务的影响。
4. 持久性：事务一旦提交，对数据库中的数据的修改就是永久性的。

2.3 事务执行

• 每一条SQL语句默认情况下都会当成单独的事务自动提交，若将一组SQL作为一个事务，需要先显式的开启事务，执行START TRANSACTION语句开启事务，后续的每一条SQL将不再提交，若需要提交执行COMMIT语句，若需要回滚事务执行ROLLBACK语句。
• ROLLBACK回滚只能针对未提交的事务，已提交的事务是不能回滚的，当执行COMMIT或ROLLBACK后，当前事务就会自动结束。
• MySQL中事务不允许嵌套，如果执行START TRANSACTION语句之前，上一个事务还未提交，则此时执行开启事务语句会隐式执行上一个事务的提交操作。

2.4 事务的保存点

1. 作用：对事务内的部分操作进行回滚，借助保存点实现。
2. 使用

1. 创建保存点：SAVEPOINT 保存点名
2. 回滚到指定保存点：ROLLBACK TO SAVEPOINT 保存点名
3. 删除指定保存点：RELEASE SAVEPOINT 保存点名

3. 注：一个事务中可以创建多个保存点，一旦提交事务，所有的保存点都会删除。如果事务回滚到某个保存点后，该保存点之后创建的其他保存点也会被删除。

2.5 事务的隔离级别

1. READ UNCOMMITTED(读未提交)：事务的最低级别，该级别下的事务读取了其他事务中为提交的数据，也成为脏读。
2. READ COMMITTED(读已提交)：事务只能读取其他事务已经提交的内容，该级别可有效避免脏读，但是会有不可重复读和幻读的问题。
3. REPEATABLE READ(可重复读)：MySQL的默认隔离级别，该级别确保同一事务中的多个实例在并发读取数据时，会看到同样的数据行，解决了不可重复读和脏读问题，但会出现幻读。
4. SERIALIZABLE(串行化)：事务的最高隔离级别，它会在每个读的数据行加上锁，解决了脏读、不可重复读、幻读的问题。由于使用了锁(默认时间是50s)，可能会导致大量的超时和锁竞争的现象，性能较低，一般不使用。
5. 查看当前事务的隔离级别

SELECT @@session.transaction_isolation

6. 设置当前事务的隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED

2.6 关于脏读、不可重复读、幻读

1. 脏读：某一事务读取了另一事务的未提交数据。
2. 不可重复读：多线程下，某一事务内重复读取别的线程已经提交的数据，由于多次查询期间其他线程对事务做了更新数据处理，因此会出现多次读取的结果不一致的现象。
3. 幻读：一个事务内两次查询中的数据条数不一致。可能由于其他事务做了插入记录的操作，导致记录条数有所增加。
4. 脏读与不可重复读的区别

1. 脏读是某一事务读取了另一事务未提交的数据；不可重复读是读取了前一事务提交的数据。

2.7 为什么生产环境的数据库一般使用READ COMMITTED而不使用REPEATABLE READ

1. 在RR隔离级别下，存在间隙锁，导致出现死锁的概率比RC隔离级别下的大。
2. 在RR隔离级别下，条件列未命中索引会锁表，在RC隔离级别下，条件未命中索引只锁行，并且RC的并发高于RR。

3、索引

3.1 关于索引

1. 概念：索引是存储引擎用于提高数据库表的访问速度的一种数据结构。

1. 机制：索引在数据表中一列或多列的值与记录行之间按照一定的顺序建立关系，以此来提高数据的查询速度。

2. 作用 or 优势

1. 提高查询速度：索引能够显著提高查询速度，特别是对于大型数据表。没有索引，数据库必须执行全表扫描来检索数据，效率较低。
2. 优化查询：当查询涉及多张表时，数据库优化器会使用索引来决定最有效的查询路径，有助于提高复杂查询的性能。
3. 提高数据检索的速度：索引可以加速数据的检索速度，特别是在进行范围查询、排序和分组操作时。
4. 实现唯一性约束：索引可以确保某列或某几列的组合值是唯一的，有助于满足唯一性约束的要求。
5. 降低数据库的I/O操作：通过分散数据到多个磁盘块中，索引可以降低数据库的I/O操作，从而提高查询速度和整体性能。
6. 优化连接查询：通过在连接列上创建索引，可以加快表的连接速度。

3. 缺陷

1. 建立索引需要占用一定的物理空间，用空间换时间。
2. 索引会导致对数据库的写入操作(增删改)性能降低，因为在进行写入操作时，数据库需要更新相应的索引信息。

3.2 索引的使用情况

1. 需要建立索引的情况

1. 查询频率高的列：某个列经常作为查询条件，那么在该列上创建索引可以提高查询速度。
2. 唯一性约束：如果某个列需要满足唯一性约束，可以在该列上创建唯一索引。
3. 连接操作中的列：在进行连表操作时，在连接列上创建索引，可以加快连接速度。
4. 排序和分组操作中的列：在经常进行排序和分组操作的列上创建索引，可以提高查询速度。
5. 复合查询中的列：对于复合查询，在查询条件中使用的列上创建索引可以提高查询速度。

2. 不需要建立索引的情况

1. 数据量小的表：如果表中的数据量很小，例如只有近百条数据，此时创建索引不会有太大的性能提升，甚至可能导致性能下降。
2. 更新频繁的列：如果某个列经常被更新，在该列上创建索引会影响写入性能，每次更新都需要更新索引。
3. 选择率低的列：如果某个列的选择率很低，也就是该列中的大部分值都是相同的，那么在该列上创建索引不会有大的性能提升。
4. 不涉及WHERE、JOIN、GROUP BY、ORDER BY子句中的列：如果某个列不涉及这些操作，那么在该列上创建索引不会有大的性能提升。

3.3 MySQL索引的数据结构

1. B-Tree索引：MySQL中最常用的索引类型。

1. 数据结构：B-Tree(B树)是一种自平衡的树结构，能够保持数据有序。InnoDB存储引擎使用B+Tree(B+树)作为其索引数据结构，而MyISAM存储引擎使用B-Tree。
2. 内部机制：

1. B-Tree：一个有序的树形结构，每个节点包含一定数量的键值和指向子节点的指针。
2. B+Tree：基于B-Tree的非叶子节点仅保存键值，而叶子节点保存键值和指向数据的指针，它具有B树的平衡性，这使得范围查询、排序和分组操作更高效。

2. Hash索引

1. 数据结构：基于哈希表实现，通过哈希函数将键值映射到存储位置。
2. 内部机制：Hash索引通过计算哈希值来快速定位数据。它适用于等值查询，但对于范围查询和排序操作可能不太高效，MySQL的MEMORY存储引擎支持Hash索引。

3. 空间索引

1. 数据结构：用于地理空间数据类型，MySQL的MyISAM和InnoDB存储引擎都支持空间索引。
2. 内部机制：空间索引使用R-Tree或Quadtree等数据结构来存储空间数据，并根据空间对象的位置进行快速检索。MySQL的MyISAM存储引擎使用R-Tree作为其空间索引的数据结构。

4. R-Tree索引

1. 数据结构：R-Tree是一种用于存储空间数据的树形结构，能够高效地处理大量空间数据。
2. 内部机制：R-Tree索引通过将空间对象组织成树形结构来加速检索操作。每个节点包含一组子节点，每个子节点代表一个空间范围或一个子树。MySQL的MyISAM存储引擎使用R-Tree作为其空间索引的数据结构。

3.4 MySQL的Hash索引和B+树索引的区别

1. 哈希索引不支持排序，因为哈希表是无序的。
2. 哈希索引不支持范围查找。
3. 哈希索引不支持模糊查询及多列索引的最左前缀匹配。
4. 由于哈希表中存在哈希冲突，导致哈希索引的性能不稳定，而B+树的性能相对稳定，每次查询都是从根节点到叶子节点。

3.5 B+树比B树更适合实现数据库索引的原因

1. 由于B+树的数据都存储在叶子结点中，叶子结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，而在数据库中基于范围的查询是非常频繁的，所以通常B+树用于数据库索引。
2. B+树的节点只存储索引key值，具体信息的地址存在于叶子节点的地址中。这就使以页为单位的索引中可以存放更多的节点，减少更多的I/O支出。
3. B+树的查询效率更加稳定，任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

3.6 索引的分类

• 根据索引的实现语法

• 普通索引：MySQL中的基本索引类型，使用KEY或INDEX定义，不需要添加任何限制条件,允许有NULL值。
• 唯一索引：创建唯一性索引的字段允许有NULL值，但需要保证索引对应字段中的值是唯一的。
• 主键索引：主键索引是一种特殊的唯一性索引，根据主键自身的唯一性标识每一条记录，不允许有NULL值。
• 全文索引：用于提高数据量较大的字段中的查询效率，只能在CHAR、VARCHAR、TEXT类型的字段上创建该索引。
• 空间索引：只能创建在空间数据类型的字段上，空间类型存储的空间数据是指含有位置、大小、形状以及自身分布特征等多方面信息的数据。

• 根据创建索引的字段个数

• 单列索引：在表中单个字段上创建索引，它可以是普通索引、唯一索引或全文索引，只要保证该索引只对应表中的一个字段即可。
• 复合索引：在表中多个字段上创建一个索引，并且只有在查询条件中使用了这些字段中的第一个字段时，该索引才会被使用。

3.7 索引的使用

1. 创建索引

CREATE INDEX user_index ON user (id);

2. 查看索引

-- 方式一 --
SHOW CREATE TABLE 表名;
例：SHOW CREATE TABLE book;

-- 方式二 --
SHOW KEYS|INDEX|INDEXES FROM 表名;
例：SHOW KEYS FROM book;

3. 删除索引

-- 方式一 --
DROP INDEX 索引名 ON 表名;
例：DROP INDEX book_fk ON book;

-- 方式二 --
ALTER TABLE 表名 DROP KEY|INDEX 索引名;
例：ALTER TABLE book DROP KEY book_fk;

-- 删除主键索引
DROP INDEX `PRIMARY` ON 表名;
ALTER TABLE 表名 DROP PRIMARY KEY;

3.8 聚集索引

1. 数据结构：InnoDB使用表的主键构造主键索引树，同时叶子节点中存放的即为整张表的记录数据。聚集索引叶子节点的存储是逻辑上连续的，使用双向链表连接，叶子节点按照主键的顺序排序，因此对于主键的排序查找和范围查找速度比较快。
2. 内部机制：聚集索引的叶子节点就是整张表的行记录。InnoDB主键使用的是聚集索引。聚集索引要比非聚集索引查询效率高很多。对于InnoDB来说，聚集索引一般是表中的主键索引，如果表中没有显示指定主键，则会选择表中的第一个不允许为NULL的唯一索引。如果没有主键也没有合适的唯一索引，那么InnoDB内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键长度为6个字节，它的值会随着数据的插入自增。

3.9 索引失效的情况

1. 对于组合索引，若没有使用组合索引的最左侧字段，则索引不会生效。
2. LIKE模糊查询中，使用%开头的查询(%abc)，无法使用索引，非%开头的查询(abc%)相当于范围查询，会使用索引。
3. 查询条件中列类型是字符串，没有使用引号，可能会因为类型不同发生隐式转换，使索引失效。
4. 判断索引列是否不等于某个值时，索引会失效。
5. 对索引列进行计算操作时，索引会失效。
6. 查询条件使用OR连接，导致索引失效。

4、存储引擎和锁

4.1 MySQL常用的存储引擎

1. InnoDB存储引擎：MySQL默认的事务型存储引擎，使用最广泛，基于聚集索引建立的，InnoDB内部做了很多优化，如能够自动在内存中创建自适应hash索引，以加速读操作。

1. 优点：支持事务和崩溃修复能力，引入了行级锁和外键约束。
2. 缺点：占用的数据空间相对较大。
3. 实用场景：需要支持事务并且有较高的读写并发频率。

2. MyISAM存储引擎：数据以紧密格式存储，对于只读数据或者表比较小，可以容忍修复操作，可以使用MyISAM引擎，会将表存储在两个文件中，数据文件.MYD和索引文件.MYI。

1. 优点：访问速度快
2. 缺点：不支持事务和行级锁，不支持崩溃后的安全恢复，也不支持外键。
3. 适用场景：对事务完整性没有要求，表的数据都会只读的。

3. MEMORY存储引擎：将数据全部放在内存中，访问速度快，但是系统一旦崩溃，数据都会丢失。MEMORY存储引擎默认使用哈希索引，将键的哈希值指向数据行指针保存在哈希索引中。

1. 优点：访问速度较快
2. 缺点：

1. 哈希索引数据不是按照索引值顺序存储，无法用于排序。
2. 不支持部分索引匹配查找，因为哈希索引是使用索引列的全部内容来计算哈希值的。
3. 只支持等值比较，不支持范围查询。
4. 当出现哈希冲突时，存储引擎需要遍历链表中所有的行指针，逐行进行比较，直到找到符合条件的行。

4. ARCHIVE存储引擎：适合存储大量独立的、作为历史记录的数据。

1. 优点：提供了压缩功能，拥有高效的插入速度。
2. 缺点：不支持索引，查询性能较差。

4.2 InnoDB和MyISAM的区别

1. 存储结构区别

1. MyISAM在磁盘上存储成三个文件。文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD(MYData)，索引文件的扩展名是.MYI(MYIndex)。
2. InnoDB所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。

2. 存储空间的区别

1. MyISAM支持支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。当表在创建之后并导入数据之后，不会再进行修改操作，可以使用压缩表，极大的减少磁盘的空间占用。
2. InnoDB需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

3. 可移植性、备份及恢复

1. MyISAM数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。
2. InnoDB可行的方案是拷贝数据文件、备份 binlog，或者用mysqldump，在数据量达到几十G的时候就相对麻烦了。

4. 是否支持行级锁

1. MyISAM只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。
2. InnoDB支持行级锁和表级锁，默认为行级锁。行锁大幅度提高了多用户并发操作的性能。

5. 是否支持事务和崩溃后的安全恢复

1. MyISAM不提供事务支持。
2. InnoDB提供事务支持，具有事务、回滚和崩溃修复能力。

6. 是否支持外键

1. MyISAM不支持
2. InnoDB支持

7. 是否支持MVCC

1. MyISAM不支持
2. InnoDB支持。应对高并发事务，MVCC比单纯的加锁更高效。

8. 是否支持聚集索引

1. MyISAM不支持聚集索引
2. InnoDB支持聚集索引

9. 全文索引

1. MyISAM支持FULLTEXT类型的全文索引。
2. InnoDB不支持FULLTEXT类型的全文索引，但是InnoDB可以使用sphinx插件支持全文索引，并且效果更好。

10. 表主键

1. MyISAM允许没有任何索引和主键的表存在，索引都是保存行的地址。
2. InnoDB如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)。

11. 表的行数

1. MyISAM保存有表的总行数，如果select count(1) from table会直接取出该值。
2. InnoDB没有保存表的总行数，如果使用select count(1) from table就会遍历整个表，消耗相当大，但是在加了where条件后，MyISAM和InnoDB处理的方式都一样。

4.3 MySQL中的锁

• 按锁的粒度分类

• 行级锁：MySQL中粒度最细的一种锁。表示只针对当前操作的行进行加锁。行级锁能大大减少数据库的操作冲突，其加锁粒度最小，但加锁的开销也最大。
• 表级锁：MySQL中粒度最大的一种锁。表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持，最常用的MyISAM与InnoDB引擎都支持表级锁。
• 页级锁：粒度介于行级锁和表级锁之间，表级锁速度快，但冲突多，行级冲突少，但速度慢。因此，采取了折衷的页级锁，一次锁定相邻的一组记录。

• 按锁的级别分类

• 共享锁：又称读锁，读取操作创建的锁，其他用户可以并发读取数据，但任何事务都不能对数据进行修改，直到已释放所有的共享锁。

-- 读取共享锁 --
SELECT * FROM table LOCK IN SHARE MODE;

• 排他锁：又称写锁、独占锁，如果事务对数据加上排他锁后，其他事务不能再对该数据加任何类型的封锁，获准排他锁的事务既能读数据，又能修改数据。

-- 读取排他锁 --
SELECT * FROM table FOR UPDATE;

• 意向锁：意向锁是表级锁，其设计的主要目的是为了在一个事务中揭示下一行将要被请求锁的类型，意向锁是InnoDB自动加的，不需要用户干预。InnoDB 中的两个表锁：

• 意向共享锁（IS）：表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁。
• 意向排他锁（IX）：表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。

4.4 关于MVCC

1. 概念：MVCC全称Multi-Version Concurrency Control(多版本并发控制)，是一种并发控制方法，通常用于实现对数据库的并发访问。
2. 实现原理：通过保存数据在某个时间点的快照来实现。每修改一次记录，就会插入一条undo日志，形成一个版本链，当读取数据时，会根据一致性视图(ReadView)选择一个合适的版本进行读取，这样就可以保证每个事务看到的数据都是一致的。
3. 优势

1. 提高数据库并发性能，并处理读写冲突。在传统的数据库系统中，读写操作会相互阻塞，降低了并发性能。而引入MVCC后，只有写写之间会相互阻塞，其他操作（读、写、读、写）都可以并行进行，大大提高了数据库的并发度。
2. 减少锁的竞争，提高系统的并发性能。在MVCC机制下，读操作不需要加锁，可以非阻塞地并发进行。当有读写冲突时，可以通过一定的算法来选择合适的版本进行读取，而不是等待另一个事务释放锁。这样既提高了数据库的并发性能，也保证了事务的一致性和隔离性。

4.5 表记录读取方式快照读和当前读

• 快照读：读取的是快照版本，普通的查询语句就是快照读，通过MVCC进行并发控制，不用加锁。
• 当前读：读取的是最新版本，UPDATE、DELETE、INSERT、SELECT … LOCK IN SHARE MODE、SELECT … FOR UPDATE是当前读。
• 快照读情况下，InnoDB通过MVCC机制避免了幻读现象，而MVCC机制无法避免当前读情况下出现的幻读现象，因为当前读每次读取的都是最新数据，如果两次查询中间有其他事务插入数据，就会产生幻读。
• MySQL避免幻读机制

• 在快照读情况下，MySQL通过MVCC来避免幻读。
• 在当前读情况下，MySQL通过NEXT-KEY来避免幻读(使用行锁和间隙锁实现)。

• NEXT-KEY包括两部分：行锁和间隙锁。行级锁是加在索引上的锁，间隙锁是加在索引之间的。

• Serializable(串行化)隔离级别也可以避免幻读，会锁住整张表，并发性极低，一般不会使用。

4.6 乐观锁和悲观锁

• 数据库中的并发控制是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观锁和悲观锁是并发控制主要采用的技术手段。
• 悲观锁：假定会发生并发冲突，会对操作的数据进行加锁，直到提交事务，才会释放锁，其他事务才能进行修改。

• 实现方式：使用数据库中的锁机制。

• 乐观锁：假设不会发生并发冲突，只在提交操作时检查是否数据是否被修改过。给表增加version字段，在修改提交之前检查version与原来取到的version值是否相等，若相等，表示数据没有被修改，可以更新，否则，数据为脏数据，不能更新。

• 实现方式：乐观锁一般使用版本号机制或CAS算法实现。

5、MySQL日志

5.1 binlog/redolog/undolog

1. binlog(二进制日志)：MySQL数据库级别的文件，记录对MySQL数据库执行修改的修改操作，不会记录select和show语句，主要用于恢复数据库和同步数据。
2. redlog(重做日志)：InnoDB级别，用来记录InnoDB存储引擎的事务日志，不管事务是否提交都会记录下来，用于数据恢复，当数据库发生故障，InnoDB存储引擎会使用redolog恢复到发生故障前的时刻，以此来保证数据的完整性。
3. undolog(回滚日志)：当进行数据修改时还会记录undolog，undolog用于数据的撤回操作，它保留了记录修改前的内容。通过undolog可以实现事务回滚，并且可以根据undolog回溯到某个特定的版本的数据，实现MVCC。

5.2 binlog和redolog的区别

1. binlog会记录所有日志记录，包括InnoDB、MyISAM等存储引擎的日志，redolog只记录InnoDB自身的事务日志。
2. binlog只在事务提交前写入到磁盘，一个事务只写一次，redolog在事务进行过程中会不断的写入日志到磁盘。
3. binlog是逻辑日志，记录的是SQL语句的原始逻辑，redolog是物理日志，记录的是在某个数据页上做了什么修改。

5.3 MySQL的架构

MySQL主要分为Server层和存储引擎层

1. Server层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都能在这一层实现，比如存储过程、视图、函数等，还有一个通用的日志模块binlog日志模块。
2. 存储引擎层：主要负责数据的存储和读取，Server层通过API与存储引擎层进行通信。
3. Server层的基本组件

1. 连接器：当客户连接MySQL时，Server层会对其身份进行验证和权限校验。
2. 查询缓存：执行查询语句的时候会先查询缓存，先校验这个sql是否执行过，如果缓存中有这个sql，就会直接返回给客户端，如果没有命中，就会执行后续操作。
3. 分析器：没有命中缓存的话，sql语句就会经过分析器，主要分为两步，词法分析和语法分析，先看sql语句要做什么，再检查sql语句语法是否正确。
4. 优化器：优化器对查询进行优化，包括重写查询、决定表的读写顺序以及选择合适的索引等，生成执行计划。
5. 执行器：首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会根据执行计划去调用引擎的接口，返回结果。

5.4 分库分表

1. 垂直划分

1. 优点：行记录变小，数据页可以存放更多的记录，在查询时较少I/O次数。
2. 缺点

1. 主键出现冗余，需要管理冗余列
2. 会引起表连接JOIN操作，可以通过在业务服务器上进行JOIN来减少数据库的压力
3. 依然存在单表数据量过大的问题

2. 水平划分

1. 优点：单库(表)的数量得以减少，提高性能。切分出的额表结构相同，程序改动较少。
2. 缺点

1. 分片事务一致性难以解决
2. 跨节点JOIN性能差，逻辑复杂
3. 数据分片在扩容时需要迁移

3. 分区的问题

1. 打开和锁住所有底层表的成本可能很高。当查询访问分区表时，MySQL 需要打开并锁住所有的底层表，这个操作在分区过滤之前发生，所以无法通过分区过滤来降低此开销，会影响到查询速度。可以通过批量操作来降低此类开销，比如批量插入、LOAD DATA INFILE 和一次删除多行数据。
2. 维护分区的成本可能很高。例如重组分区，会先创建一个临时分区，然后将数据复制到其中，最后再删除原分区。
3. 所有分区必须使用相同的存储引擎。

5.5 MySQL的主从同步

1. 概念：主从同步使得数据可以从一个数据库服务器复制到其他服务器上，在复制数据时，一个服务器充当主服务器(master)，其余的服务器充当从服务器(slave)。
2. 机制：因为复制是异步进行的，所以从服务器不需要一直连接着主服务器，从服务器甚至可以通过拨号断断续续的连接主服务器，通过配置文件，可以指定复制所有的数据库、某个数据库甚至是某个数据库上的某个表。
3. 使用主从同步的原因

1. 读写分离，使数据库能支撑更大的并发
2. 在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能
3. 数据备份，保证数据的安全

5.6 MySQL不建议使用外键的原因

1. 并发问题：在使用外键的情况下，每次修改数据都需要另外一个表检查数据，需要获取额外的锁，若是在高并发大流量事务场景，使用外键更容易造成死锁。
2. 扩展性问题：数据库的迁移不方便，外键依赖数据库本身的属性。
3. 不利于分库分表：在水平拆分和分库情况下，外键是无法生效的。将数据间关系的维护放入应用程序中，为将来的分库分表省去很多的麻烦。

5.7 MySQL不建议使用存储过程的原因

1. 存储过程难以调试。存储过程的开发一直缺少有效的IDE环境。SQL本身经常很长，调试式要把句子拆开分别独立执行，非常麻烦。
2. 移植性差。存储过程的移植困难，一般业务系统总会不可避免地用到数据库独有的特性和语法，更换数据库时这部分代码就需要重写，成本较高。
3. 管理困难。存储过程的目录是扁平的，而不是文件系统那样的树形结构，脚本少的时候还好办，一旦多起来，目录就会陷入混乱。
4. 存储过程是只优化一次，有的时候随着数据量的增加或者数据结构的变化，原来存储过程选择的执行计划也许并不是最优的了，所以这个时候需要手动干预或者重新编译了。