知识点—数据库

SQL数据库

基本概念

• 元组 ： 关系是一张表，表中的每行（即数据库中的每条记录）就是一个元组，每列就是一个属性。 在二维表里，元组也称为行。
• 码 ：码就是能唯一标识实体的属性，对应表中的列。
• 候选码 ： 若关系中的某一属性或属性组的值能唯一的标识一个元组，而它的任何子集都不能再标识，则称该属性组为候选码。例如：在学生实体中，“学号”是能唯一的区分学生实体的，同时又假设“姓名”、“班级”的属性组合足以区分学生实体，那么{学号}和{姓名，班级}都是候选码。
• 主码 : 主码也叫主键。主码是从候选码中选出来的。 一个实体集中只能有一个主码，但可以有多个候选码。
• 外码 : 外码也叫外键。如果一个关系中的一个属性是另外一个关系中的主码则这个属性为外码。
• 主属性 ： 候选码中出现过的属性称为主属性。比如关系 工人（工号，身份证号，姓名，性别，部门）. 显然工号和身份证号都能够唯一标示这个关系，所以都是候选码。工号、身份证号这两个属性就是主属性。如果主码是一个属性组，那么属性组中的属性都是主属性。
• 非主属性： 不包含在任何一个候选码中的属性称为非主属性。比如在关系——学生（学号，姓名，年龄，性别，班级）中，主码是“学号”，那么其他的“姓名”、“年龄”、“性别”、“班级”就都可以称为非主属性。

关于外键： 阿里巴巴开发手册是不推荐使用外键和级联的，要求一切外键概念要在应用层解决。

原因：

1. 增加复杂性： 每次DELETE和UPDATE都必须考虑外键约束。且级联更新（外键表跟着更新）是强阻塞的，可能引发数据库更新风暴的风险。
2. 在分库分表情况下，外键是无法生效的。
3. 数据库增加额外工作：涉及外键的字段的操作，数据库会进行一些操作保证数据一致性和正确性，会额外消耗数据库资源。

事实上，外键能保证数据库数据的一致性和完整性，级联操作也方便，能减轻程序代码量。 所以系统不涉及分库分表，并发量不高时，能考虑使用外键。

三大范式

第一范式： 属性不可再分。

第二范式： 1NF的基础上，消除了非主属性对于码的部分函数依赖。

第三范式： 2NF的基础上，消除了非主属性对于码的传递函数依赖。

NF（normal form），范式，标准形式

函数依赖： 在一张表中，如果属性X的值能确定，则属性Y的值也必定能确定，那么就说 Y函数依赖于X，写作 X → Y。

部分函数依赖：如果 X→Y，且存在X的一个真子集X0，有X0→Y，则称Y对X是部分函数依赖。 例如：学生表中有（学号，身份证号，姓名），可知（学号，身份证号）->（姓名），（学号）->（姓名），（身份证号）->（姓名）；所以（姓名）部分函数依赖于（学号，身份证号）；

完全函数依赖：如果 X→Y，且不存在X的一个真子集X0，能有X0→Y，则称Y对X是完全函数依赖。 例如：学生表有（学号，班级，姓名），假设不同班级的学号能相同，则有（学号，班级）->（姓名），但是（学号）->(姓名)不成立，（班级）->(姓名)不成立，所以（姓名）完全函数依赖与（学号，班级）。

传递函数依赖：在一个表中，某非主键字段A依赖于另一个非主键字段B，B又依赖于字段C，就称 A传递函数依赖于C。传递关系会导致数据冗余和异常。如关系R（学号 , 姓名, 系名，系主任）中，学号→系名，系名→系主任，存在非主属性（系主任）对于（学号）的传递函数依赖。

具体解释：

• 第一范式： 属性，即表中的字段不可再分， 这个字段只能是一个值，不能再分为多个其他的字段。1NF是所有关系型数据库的最基本要求。
• 第二范式： 在1NF的基础上，消除非主属性对于码的部分函数依赖。 也就是尽量让表中的非主属性只依赖主键。
• 第三范式： 就是要消除传递依赖，减少重复冗余。

ER图

• 实体 ：通常是现实世界的业务对象，逻辑对象也可以。比如对于一个校园管理系统，会涉及学生、教师、课程、班级等等实体。在 ER 图中，实体使用矩形框表示。
• 属性 ：就是某个实体拥有的属性，属性用来描述组成实体的要素，对于产品设计来说可以理解为字段。在 ER 图中，属性使用椭圆形表示。
• 联系 ：即实体与实体之间的关系，这个关系不仅有业务关联关系，还能通过数字表示实体之间的数量对照关系，有1:1，1:N，M:N。例如，一个班级会有多个学生就是一种实体间的联系。

MySQL

SQL分类

• 数据定义语言 DDL，Data Definition Language

  主要功能：定义数据库对象。

  指令：CREATE，ALTER，DROP

• 数据操作语言 DML，Data Manipulation Language

  主要功能：访问数据，读写数据库。

  指令：SELECT，INSERT，UPDATE，DELETE （合称增删改查CRUD，create，read，update，delete）

• 事务控制语言 TCL，Transaction Control Language

  主要功能：管理数据库中的事务。

  指令：COMMIT，ROLLBACK

• 数据控制语言 DCL，Data Control Language

  主要功能：对不同用户的 数据访问权 进行控制

  指令：GRANT，REVOKE

---

删除操作 注意点：

drop，delete，truncate的区别：

• drop（丢弃数据）：drop table 表名，直接将整个表，包括表结构都删除掉。

• truncate（清空数据）：truncate table 表名，只删除表中所有数据，自增长id初始化为1。

• delete（删除数据）：delete from 表名 where 列明=值，删除某一行数据，不带where就和truncate一样，删除所有表中数据。

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

• 速度：delete会产生binlog日志，truncate不产生日志，drop是直接把表占用的空间释放掉，所以 drop(最快)>truncate>delete。

基础架构

MySQL主要分为：

• Server层： 所有跨存储引擎的功能都在这一层实现，有连接器，缓存查询，分析器，优化器，执行器等； 还有其他的比如：存储过程，触发器，视图，函数，通用日志模块binlog。
• 存储引擎： 主要负责数据的存储和读取。MySQL5.5开始默认为InnoDB。

组件详情：

• 连接器：

  身份认证和权限相关（登录MySQL时）；连接器会查询登录用户的权限，之后这个连接的权限判断都依据此时读到的数据，即使用户权限被修改，只要连接不断开，用户就不受影响。

• 查询缓存：

  执行查询语句时，会先查询缓存（MySQL8.0移除，不实用）。 缓存以 Key-Value 形式保存，Key为查询预计，Value是结果集。 （对于结果集的来源，一旦有更新变动，这个缓存就立即失效）

• 分析器：

  缓存未命中的话，SQL语句就会经过分析器， 主要分析SQL语句的功能，分2步：词法分析（提取关键词，表名等），语法分析（是否符合规范）。

• 优化器：

  选择它认为最优的执行方案。如多个索引如何选择，多表查询的关联顺序如何。

• 执行器：

  在选择了执行方案后，就可以开始执行了。不过在执行还会校验用户有没有对应的权限，如果没有，就返回错误信息，有权限的话就调用引擎接口。

• 插件式存储引擎：

  支持 InnoDB、MyISAM、Memory 等多种存储引擎。

存储引擎

存储引擎是基于表的，而不是数据库。 所以可以根据不同场景为不同表设置存储引擎。

InnoDB比MyISAM优秀的地方有：

• 支持行级锁

• 支持事务

  InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别，具有提交(commit) 和回滚(rollback) 事务的能力。并且，InnoDB 默认使用的 REPEATABLE-READ（可重读）隔离级别是可以解决幻读问题发生的（基于 MVCC 和 Next-Key Lock）。

• 支持外键

• 支持数据异常崩溃后的安全恢复 ，依赖于redo log

• 支持MVCC

• 性能更好，InnoDB的读写支持并发，性能随CPU核数增加呈线性增长。

索引

概述

索引是一种用于快速检索数据的数据结构， 本质上可看成是一种排序好的数据结构。（类似于书的目录）

常见的索引结构有：B树，B+树，Hash，红黑树。 MyISAM和InnoDB都是使用B+树作为索引结构。

优点：

• 大大加快检索数据的速度（减少检索的数据量），这也是使用索引的最主要原因。
• 使用唯一性索引，可以保证数据库表中每一行数据的唯一性。

缺点：

• 索引的创建和维护都需要耗费许多时间。当对数据数据增删改的时候，对应的索引也要动态修改。
• 索引需要使用物理文件存储，会耗费一定空间。

适合创建索引的字段：

• 被频繁查询的字段
• 被作为条件查询的字段
• 频繁需要排序的字段
• 被频繁用于连接的字段（联系表与表之间的键列）

不适合创建索引的字段：

• 可以为null的字段： 数据库难以优化值为null的字段，如果字段应当创建索引又避免不了为null，建议使用0，1，true，false这些语义清晰的值代替。
• 被频繁更新的字段： 维护索引是不小的开销。

建议：

• 限制每张表上的索引数量： 最好不超5个。 因为优化器会对每一个可以用到的索引进行评估，过多的索引会增加优化器生成执行计划的时间。
• 尽量创建联合索引，而不是单列索引： 每个索引都对应一颗B+树，少创建索引不仅节约空间，也提高数据修改效率。
• 避免冗余索引： 索引（a，b）肯定能命中索引（a），则（a）就是冗余的索引。
• 字符串类型的字段使用前缀索引，而不是普通索引： 占用空间更小。

索引失效的情况：

• 使用 select * 查询。
• 创建联合索引，当查询条件未遵守最左匹配原则。
• 在索引列上进行计算，函数，类型转换等操作。
• like的前缀匹配，如：like “%abc”。
• 查询条件使用or， or的前后条件有一个列没有索引，则另一个涉及的索引不生效。
• 发生隐式转换。

索引下推： 是一项索引优化，可以在非聚簇索引遍历中，对索引中的字段想做判断，以过滤掉不符合条件的记录，减少回表次数。

类型

按数据结构划分：

• B+树索引：最常用的结构。只有节点存储数据，非叶子节点只有指针和key。
• 哈希索引：键值对形式，一次定位。
• R树索引：仅支持geometry数据类型，优势在于范围查找，效率低，一般用搜索引擎如ElasticSearch代替。
• 全文索引：对文本内容分词，进行搜索。效率低，一般用搜索引擎如ElasticSearch代替。。

按底层存储方式划分：

• 聚簇索引：索引结构和数据一起存放的索引。InnoDB的主键索引就属于聚簇索引。
• 非聚簇索引：索引结构和数据分开存放的索引。二级索引(辅助索引)就属于非聚簇索引，MyISAM引擎的主键和非主键索引都是非聚簇索引。

按应用划分：

• 主键索引：加速查询 + 列值唯一（不可以有NULL）+ 一张表只能有一个主键。
• 唯一索引：加速查询 + 列值唯一（可以为NULL）
• 普通索引：仅加速查询
• 前缀索引：仅适用字符串类型的数据。只取文本的前几个字符创建索引，比普通索引小
• 覆盖索引：此索引包含所有需要查询的字段。（在查询时仅通过索引就能获取需要的数据，而无需访问表的实际行记录，减少了IO操作，提升查询性能）
• 联合索引：多字段组成一个索引，专门用于组合搜索，效率是大于索引合并的。
• 全文索引：文本分词+搜索。效率低，一般用ES代替。

---

主键索引：

一张表只能有一个，且不能为null，不能重复。 主键的叶子节点存储 具体数据。

当没有指定主键时，InnoDB会检查表中是否有唯一索引且不允许null的字段，有的话就将此字段设为主键，没有的话就创建一个6Byte的自增主键。

二级索引：

又称辅助索引，因为二级索引的叶子节点存储的数据是主键， 即二级索引是用来定位主键的位置的，根据主键再回表查数据。

唯一索引，普通索引，前缀索引都是二级索引。

聚簇索引：

是一种将索引结构和数据放在一起的索引。InnoDB的主键索引就属于聚簇索引。在InnoDB中表的 .ibd文件 就包含了该表的索引和数据。

对InnoDB来说，表的索引(B+树)的 非叶子节点存储 索引，叶子节点存储 索引和索引对应的数据。

优点：查询速度非常快（因为B+树是多叉平衡树，叶子节点有序）。相比于非聚簇索引，也少了一次读取数据的IO操作。 对于排序和范围查找也非常快（叶子节点指向下一个叶子节点）。

缺点：依赖于有序数据，更新代价大（叶子节点存放数据）。

非聚簇索引：

是一种将索引结构和数据分开放的索引。 二级索引属于非聚簇索引。

优点：更新代价比聚簇索引小（叶子节点只放主键值）。

缺点：依赖于有序数据，可能要二次查询（回表）。

如果查找的是主键，或者是覆盖索引的字段，则不用回表，因为叶子节点的key或value就是要找的。

最左前缀匹配原则：

使用联合索引时，会根据联合索引中的字段顺序，从左到右依次到查询条件中匹配。

在条件匹配中，如果遇到 > 和 < 的范围查询就会停止联合索引匹配（而 >=, <=, BETWEEN, like前缀匹配 这样的范围查询，并不会停止来拟合索引匹配）。所以在适用联合索引时，可以将区分度高的字段放左边，可以先过滤更多数据。

执行计划

执行计划是一条SQL语句在经过MySQL优化器后，产生的具体的执行方式。 执行计划通常用于SQL性能分析、优化等场景。

MySQL提供了 EXPLAIN命令 来获取执行计划的相关信息。（explain命令不会真的执行相关语句） 通过这些信息，可以了解到 表的查询顺序、数据查询的操作类型、哪些索引可以被命中、哪些索引实际会命中、每个数据表有多少行记录被查询等信息。

格式： EXPLAIN + SELECT语句

mysql> explain SELECT * FROM dept_emp WHERE emp_no IN (SELECT emp_no FROM dept_emp GROUP BY emp_no HAVING COUNT(emp_no)>1);

+----+-------------+----------+------------+-------+-----------------+---------+---------+-
| id | select_type | table    | partitions | type  | possible_keys   | key     | key_len | 
+----+-------------+----------+------------+-------+-----------------+---------+---------+-
|  1 | PRIMARY     | dept_emp | NULL       | ALL   | NULL            | NULL    | NULL    | 
|  2 | SUBQUERY    | dept_emp | NULL       | index | PRIMARY,dept_no | PRIMARY | 16      | 
+----+-------------+----------+------------+-------+-----------------+---------+---------+-

-----+--------+----------+-------------+
ref  | rows   | filtered | Extra       |
----+--------+----------+-------------+
NULL | 331143 |   100.00 | Using where |
NULL | 331143 |   100.00 | Using index |
-----+--------+----------+-------------+

可以看到，执行计划结果共有12列：

列名	含义
id	查询中SELECT的序号，id越大执行优先级越高
select_type	SELECT关键字对应的查询类型
table	用到的表名
partitions	匹配的分区，对于未分区的表，值为 NULL
type	表的访问方法
possible_keys	可能用到的索引
key	实际用到的索引
key_len	所选索引的长度
ref	当使用索引等值查询时，与索引作比较的列或常量
rows	预计要读取的行数
filtered	按表条件过滤后，留存的记录数的百分比
Extra	附加信息

---

select_type： 查询的类型，主要用于区分普通查询、联合查询、子查询等复杂的查询，常见的值有：

• SIMPLE：简单查询，不包含 UNION 或者子查询。
• PRIMARY：查询中如果包含子查询或其他部分，外层的 SELECT 将被标记为 PRIMARY。
• SUBQUERY：子查询中的第一个 SELECT。
• UNION：在 UNION 语句中，UNION 之后出现的 SELECT。
• DERIVED：在 FROM 中出现的子查询将被标记为 DERIVED。
• UNION RESULT：UNION 查询的结果。

---

table： 查询用到的表名，每行都有对应的表名，表名除了正常的表之外，也可能是以下列出的值：

• <unionM,N> : 本行引用了 id 为 M 和 N 的行的 UNION 结果；
• <derivedN> : 本行引用了 id 为 N 的表所产生的的派生表结果。派生表有可能产生自 FROM 语句中的子查询。 -<subqueryN> : 本行引用了 id 为 N 的表所产生的的物化子查询结果。

---

type（重要）：

查询执行的类型，描述了查询是如何执行的。所有值的顺序从最优到最差排序为：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

（索引type级别最好达到 ref | ref_eq 级别）

常见的几种类型具体含义如下：

• system：如果表使用的引擎对于表行数统计是精确的（如：MyISAM），且表中只有一行记录的情况下，访问方法是 system ，是 const 的一种特例。
• const：表中最多只有一行匹配的记录，一次查询就可以找到，常用于使用主键或唯一索引的所有字段作为查询条件。
• eq_ref：当连表查询时，前一张表的行在当前这张表中只有一行与之对应。是除了 system 与 const 之外最好的 join 方式，常用于使用主键或唯一索引的所有字段作为连表条件。
• ref：使用普通索引作为查询条件，查询结果可能找到多个符合条件的行。
• index_merge：当查询条件使用了多个索引时，表示开启了 Index Merge 优化，此时执行计划中的 key 列列出了使用到的索引。
• range：对索引列进行范围查询，执行计划中的 key 列表示哪个索引被使用了。
• index：查询遍历了整棵索引树，与 ALL 类似，只不过扫描的是索引，而索引一般在内存中，速度更快。
• ALL：全表扫描。

---

key（重要）： 表示 MySQL 实际使用到的索引。如果为 NULL，则表示未用到索引。

---

Extra（重要）：

这列包含了 MySQL 解析查询的额外信息，通过这些信息，可以更准确的理解 MySQL 到底是如何执行查询的。常见的值如下：

• Using filesort：在排序时使用了外部的索引排序，没有用到表内索引进行排序。
• Using temporary：MySQL 需要创建临时表来存储查询的结果，常见于 ORDER BY 和 GROUP BY。
• Using index：表明查询使用了覆盖索引，不用回表，查询效率非常高。
• Using index condition：表示查询优化器选择使用了索引条件下推这个特性。
• Using where：表明查询使用了 WHERE 子句进行条件过滤。一般在没有使用到索引的时候会出现。
• **Using join buffer (Block Nested Loop)**：连表查询的方式，表示当被驱动表的没有使用索引的时候，MySQL 会先将驱动表读出来放到 join buffer 中，再遍历被驱动表与驱动表进行查询。

注：当 Extra 列包含 Using filesort 或 Using temporary 时，MySQL 的性能可能会存在问题，需要尽可能避免。

---

底层数据结构

虽然Hash单个检索速度很快，但MySQL没有使用Hash作为索引结构，因为Hash索引不支持 顺序和范围查询，且每次IO只能取一个。

目前大部分数据库都使用 B树 或 B+树 作为索引结构。

B树，全称 多路平衡查找树（B是Balanced），B+树是B树的一种变体，区别有：

• B树的所有节点即存放key 也存放data，而B+树只有叶子节点存放 key 和 data，其余节点只放 key。
• B树的叶子节点是独立的，B+树的叶子节点有一条引用链指向相邻的下一个叶子节点。
• B树和B+树检索过程都是做二分查找， B树可能没到叶子节点就检索完成，B+树的任何检索都是从根节点到叶子节点。

B+树的优势：

1. 所有数据都在叶子节点，且叶子节点有指向下一个节点的指针，这使得查询速度稳定，也适用 范围查询。
2. B+树的非叶子节点只存索引信息，这意味着每个节点能存储更多索引，从而降低树的高度，提高查询效率。

B+树的主键索引 和 二级索引 图示：

日志

MySQL日志主要包括 错误日志、查询日志、慢查询日志、事务日志、二进制日志等。

其中，较重要的有 二进制日志binlog（归档日志），事务日志redo log（重做日志），undo log（回滚日志）。

binlog在server层，redo log在引擎层

redo log保证事务持久性，undo log保证事务原子性。binlog用于服务器间的数据同步，保证数据一致性。

redo log

rode log（重做日志）是InnoDB独有的，主要作用： mysql崩溃后的数据恢复，保证数据的完整性。

• redo log的每条记录组成： 表空间号 + 数据页号 + 偏移量 + 修改数据长度 + 具体修改的数据

MySQL 中数据是以页为单位，当查询一条记录，就会从硬盘把一页的数据加载出来，加载出来的数据叫数据页，会放入到 Buffer Pool 中。

后续的查询都是先从 Buffer Pool 中找，没有命中再去硬盘加载，减少硬盘 IO 开销，提升性能。更新表数据的时候，也是如此，发现 Buffer Pool 里存在要更新的数据，就直接在 Buffer Pool 里更新。

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

• 顺序： 磁盘加载数据放入缓冲池——InnoDB直接更新缓冲数据——记录更新信息到redo log buffer——择机清空redo log buffer，写到page cache（文件系统缓存），再调用fsync函数 刷盘到硬盘的redo日志中。

redo log 刷盘时机：

InnoDB有一个后台线程，每隔1秒就会将redo log buffer的内容写到page cache，并调用fsync刷盘。

还看innodb_flush_log_at_trx_commit 参数，它支持三种策略：

• 0 ：设置为 0 的时候，表示每次事务提交时不进行刷盘操作（mysql挂了会丢失1s数据）
• 1 ：设置为 1 的时候，表示每次事务提交时都将进行刷盘操作（默认值）（不会丢失数据，就算宕机了，由于事务没提交，日志丢了也没事）
• 2 ：设置为 2 的时候，表示每次事务提交时都只把 redo log buffer 内容写入 page cache。（mysql挂了不会丢失数据，当服务器宕机会丢失1s数据）

• 日志文件组： 磁盘上的redo log以一个文件组形式存在，文件组有多个redo log文件。

  它采用的是环形数组形式，从头到尾循环写。 属性write pos表示当前写入记录的位置，checkpoint表示有效数据的末尾，也是要擦除的位置。 当write pos追上checkpoint，就说明日志文件组满了，需要先清空一些记录才能再写入。

问题：

• 为什么修改后的数据不直接刷入硬盘？

  一数据页是16KB，可能就修改了几Byte数据，就要刷入整个数据页。而且一个数据页对应的位置可能在硬盘中的随机位置，所以数据页刷盘性能很差。

  而写 redo log，一行记录就几十Byte，而且是顺序写，刷盘速度很快。

binlog

redo log是物理文件，记录了“在某个物理数据页做了什么修改”，属于InnoDB。

binlog是逻辑文件，记录内容是语句的原始逻辑，属于Server层。 只要表发生更新，就会产生binlog日志。 在数据库的数据备份，主备，主主，主从都需要binlog来同步数据。

binlog日志的三种格式：statement，row，mixed

• statement： 记录的内容是SQL原文。但对于now()等实时性的函数会出问题。
• row：记录的sql中的变量或函数会转化为具体数值。row格式的内容看不到详细信息，要使用mysqlbinlog工具解析。row格式需要较大容量来记录，恢复和同步更消耗IO资源。
• mixed： 这是个折中的方案， Mysql会判断sql语句是否可能引起数据不一致，如果是，就用row格式，否则就用statement格式。

写入时机： 事务中，先把日志写到 binlog cache，事务提交时再把 binlog cache 写到binlog 文件中（提交事务时先写到page cache，再择机调用fsync刷盘）。

两阶段提交

redo log 和 binlog 都是持久化的保证，但侧重点不同。

在执行更新语句的过程，redo log可以不断写入，而binlog只在提交事务时才写入。如果出现意外，可能会导致两份日志的逻辑不一样。 所以就有两阶段提交来保证两种日志一致。

• 简单说就是： redo log分两个步骤，事务中处于prepare阶段，事务提交时，先写入binlog，在将redo log改成commit状态。

• 流程： 先查询目标数据，拿到数据后进行修改，调用InnoDB引擎API写入数据，InnoDB会把数据保存在内存中，同时记录 redo log（prepare状态），然后告知执行器，操作完成可以提交。 执行器收到通知后记录 binlog， 再调用引擎接口，InnoDB就提交redo log为提交状态， 至此更新完成。

  查询语句的执行流程如下：权限校验（如果命中缓存）—>查询缓存—>分析器—>优化器—>权限校验—>执行器—>引擎

  更新语句执行流程如下：分析器—->权限校验—->执行器—> 引擎—> redo log (prepare 状态) —> binlog —> redo log (commit状态)

• 异常判断：

  • 如果binlog异常，那redo log肯定也处于prepare状态，会回滚日志。
  • 如果redo log设置commit时异常，而binlog正常，此时redo log处于prepare状态，那么不会回滚，因为binlog和redo log一致，只是最后的确定出问题，并没有影响。

undo log

在 MySQL 中，恢复机制是通过 回滚日志（undo log） 实现的，undo-log记录了事务发生之前的数据的一个版本/旧值， 所有事务进行的修改都会先记录到这个回滚日志中，然后再执行相关的操作。

如果执行过程中遇到异常的话，我们直接利用 回滚日志 中的信息将数据回滚到修改之前的样子即可。

并且，回滚日志会先于数据持久化到磁盘上。这样就保证了即使遇到数据库突然宕机等情况，当用户再次启动数据库的时候，数据库还能够通过查询回滚日志来回滚将之前未完成的事务。

MVCC的实现也需要undo log，对于不可见的行记录，可以利用undo log查看该行记录的历史版本。

事务

现实中有许多意外会导致数据的不一致，如数据库中途宕机，客户端突然断连，并发写入导致彼此覆盖。。 所以需要事务来保证数据的一致性。

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

数据库事务： 保证多个SQL语句是一个逻辑上的整体，要么全部执行成功，要么都不执行。

# 开启事务
START TRANSACTION;
SQL1;SQL2;
# 提交事务
COMMIT;

事务的ACID特性：

• 原子性（Atomicity） ： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
• 一致性（Consistency）： 执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；
• 隔离性（Isolation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
• 持久性（Durability）： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

只有保证 持久性，原子性，隔离性，才有 一致性。

并发带来的问题：

• 脏读：事务A修改了数据，但还未提交，此时事务B读取这个被修改的数据，然后事务A又回滚了。 所以事务B读到的就是脏数据，即发生脏读。
• 丢失修改：事务A修改一个数据，还未提交时，事务B也修改这个数据。这样事务A的修改就丢失了。
• 不可重复读：事务A中多次读取一个数据，中途事务B修改了该数据，导致事务A读取同一个数据但读出来不同的结果。
• 幻读： 事务A中多次读取确定的某几行数据，事务B中途插入了一些符合事务A要查的数据，使事务A后查的数据中多了一些记录。

幻读可以看作不可重复读的一种情况，只是它们的解决方案不同，所以将两者分开，比如：

执行delete和update时，可以对记录直接加锁，就能保证事务安全。

但执行insert时，由于记录锁（Record Lock）只能锁已存在的记录，为了避免插入新纪录，需要额外依赖间隙锁（Gap Lock）。 即执行 insert 需要使用 临键锁 Next-Key Lock（Record Lock+Gap Lock） 进行加锁来保证不出现幻读。

SQL标准定义了四个隔离级别：

• READ-UNCOMMITTED(读取未提交) ： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
• READ-COMMITTED(读取已提交) ： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
• REPEATABLE-READ(可重复读) ： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
• SERIALIZABLE(可串行化) ： 最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰。

隔离级别	脏读	不可重复读	幻读
read-uncommitted	√	√	√
read-committed	×	√	√
repeatable-read	×	×	√
serializable	×	×	×

MySQL 的隔离级别 基于 锁和 MVCC 机制 共同实现的。

serializable 隔离级别是通过锁来实现的，read-committed 和 repeatable-read 隔离级别是基于 MVCC 实现的。不过，serializable 之外的其他隔离级别可能也需要用到锁机制，就比如 repeatable-read 在当前读情况下需要使用加锁读来保证不会出现幻读。

锁和MVCC

并发事务中的控制方式有两种：锁和MVCC， 锁相当于悲观控制，MVCC相当于乐观控制。

锁

MySQL 中主要是通过 读写锁 来实现并发控制。

• 共享锁（S 锁） ：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。
• 排他锁（X 锁） ：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条记录加任何类型的锁（锁不兼容）。

	S锁	X锁
S锁	不冲突	冲突
X锁	冲突	冲突

由于MVCC的存在，一般的SELECT语句，InnoDB不会加任何锁，但可以自己显示加锁：

# 共享锁
SELECT ... LOCK IN SHARE MODE;
# 排他锁
SELECT ... FOR UPDATE;

根据锁的粒度，还可分为 表级锁 和 行级锁。

• 表级锁： 锁粒度仅次于全局锁，是针对非索引字段加的锁，对当前的整张表加锁。 实现简单，加锁快，不会死锁；但触发锁冲突概率高，高并发下效率低。
• 行级锁： 锁粒度最小的一种锁，是针对索引字段加的锁，只对操作的行记录加锁。加锁开销大，加锁慢，会出现死锁。锁冲突概率低，并发度高。

表级锁和存储引擎无关，MyISAM 和 InnoDB 引擎都支持表级锁。

行级锁和存储引擎有关，是在存储引擎层面实现的。

当执行UPDATE、DELETE时，如果where条件没有命中唯一索引 或 索引失效，也会导致扫描全表，对表中所有行记录加锁；而有些时候，因为优化器的原因，即使使用索引也可能会全表扫描。

InnoDB支持的三种行锁 ：

• 记录锁（Record Lock）：单个行记录上的锁。（只锁已存在的记录）
• 间隙锁（Gap Lock）：锁定一个范围，不包括记录本身。
• 临键锁（Next-Key Lock）：是Record Lock + Gap Lock，锁定一个范围，包含记录本身，主要目的是解决幻读问题。

在 InnoDB 默认的隔离级别 repeatable-read 下，行锁默认使用的是 Next-Key Lock。但是，如果操作的索引是唯一索引或主键，InnoDB 会对 Next-Key Lock 进行优化，降级为 Record Lock，即仅锁住索引本身，而不是范围。

意向锁：

因为表锁和行锁不能同时加， 所以就需要 意向锁 来快速判断是否可以对一张表加表锁。 意向锁有两种：

• 意向共享锁（Intention Shared Lock，IS锁）：当事务要对表中的某些记录加共享锁（S锁），必须先获取该表的IS锁。
• 意向排他锁（Intention Exclusive Lock，IX锁）：当事务要对表中的某些记录加排他锁（X锁），必须先获取该表的IX锁。

意向锁是引擎维护的，用户不能操作意向锁。

意向锁之间相互兼容：

	IS锁	IX锁
IS锁	兼容	兼容
IX锁	兼容	兼容

意向锁和表级的共享锁和排它锁：

	IS锁	IX锁
S锁	兼容	冲突
X锁	冲突	冲突

快照读和当前读：

• 快照读（一致性非锁定读）：就是单纯的select语句。快照指记录的历史版本，每行记录可能存在多个历史版本。

  使用快照读，如读取的记录正在updata/delete操作，读取操作不会因此等待记录上的X锁释放，而是直接读取该记录的快照

  • 在 read-committed 下，快照读总是读取锁定行最新一份快照，所以其他事务的修改提交后，可以读到新数据。
  • 在 repeatable-read 下，快照读总是读取本事务开始时的快照，所以其他事务的修改提交后，读到的仍是旧数据。

• 当前读（一致性锁定读）：就是读取时给记录加X锁或S锁。常见的当前读的SQL语句有：

  # 对读的记录加一个X锁
  SELECT...FOR UPDATE
  # 对读的记录加一个S锁
  SELECT...LOCK IN SHARE MODE
  # 对修改的记录加一个X锁
  INSERT...
  UPDATE...
  DELETE...

MVCC

多版本并发控制 （Multiversion concurrency control）

在InnoDB中，MVCC 是对 非锁定读的实现。

实现

MVCC实现依赖于 隐藏字段，Read View（快照），undo log。

• 在内部，InnoDB为每行数据添加三个隐藏字段：

  • DB_TRX_ID（6字节）：表示最后一次增删改该行的事务id。
  • DB_ROLL_PTR（7字节）：回滚指针，指向该行的 undo log。
  • DB_ROW_ID（6字节）：如果没有设置主键，也没有唯一非空索引时，InnoDB会使用该id来生成聚簇索引。

• ReadView（快照）： 主要用于做可见性判断，里面保存了“当前对本事务不可见的其他还未提交的活跃事务”

  class ReadView {
     ... 
  private:
    trx_id_t m_low_limit_id;  /* 大于等于这个 ID 的事务均不可见 */
  
    trx_id_t m_up_limit_id;   /* 小于这个 ID 的事务均可见 */
  
    trx_id_t m_creator_trx_id;  /* 创建该 Read View 的事务ID */
  
    trx_id_t m_low_limit_no;   /* 事务 Number, 小于该 Number 的 Undo Logs 均可以被 Purge */
  
    ids_t m_ids;             /* 创建 Read View 时的活跃事务列表 */
  
    m_closed;                /* 标记 Read View 是否 close */
  }

  事务可见性：

  • 大于 m_low_limit_id 均不可见；
  • 小于 m_low_limit_id 且大于 m_up_limit_id 的： 如果存在于活跃列表则不可见，否则可见；
  • 小于 m_up_limit_id 均可见。

• undo log（回滚日志）：

  undo log主要有两个作用：

  • 当事务回滚时，将数据恢复到修改前的样子。
  • 用于MVCC，当读取记录时，若该记录被占用或不可见，则可以通过 undo log 读取之前的版本数据，以实现非锁定读。

  undo log 分两种： insert undo log 和 update undo log。

  • insert undo log：因为 insert 操作的记录只对事务本身可见，所以这个undo log在事务提交后直接删除。
  • update undo log： update 或 delete 操作中产生的 undo log， 这个undo log 可能会被MVCC查看，所以事务提交时会放入该记录行的 undo log 链表（链首为最新记录），等待 purge线程 删除。（purge用来清除过期或无用的undo日志）

数据可见性算法

MVCC具体的数据可见性算法：

在InnoDB中的事务中，执行每个 select 语句前，都会创建一个快照（Read View），快照中保存了当前数据库系统中正处于活跃（没有 commit）的事务的 ID 号，即不应该被本事务看到的事务ID列表。

在这个事务中要读取某记录行时，会进行判断：

1. 如果记录行的 DB_TRX_ID 小于 快照的 m_up_limit_id， 那么表明最新修改该行的事务（DB_TRX_ID）在当前事务创建快照之前就提交了，所以该记录行的值对当前事务是可见的。
2. 如果 DB_TRX_ID >= m_low_limit_id，那么表明最新修改该行的事务（DB_TRX_ID）在当前事务创建快照之后才修改该行，所以该记录行的值对当前事务不可见。跳到步骤 5
3. 若 m_ids 为空，则表明在当前事务创建快照之前，修改该行的事务就已经提交了，所以该记录行的值对当前事务是可见的
4. 如果 m_up_limit_id <= DB_TRX_ID < m_low_limit_id，表明最新修改该行的事务（DB_TRX_ID）在当前事务创建快照的时候可能处于“活动状态”或者“已提交状态”；所以就要对活跃事务列表 m_ids 进行查找（源码中是用的二分查找，因为是有序的）
   • 如果在活跃事务列表 m_ids 中能找到 DB_TRX_ID，表明：① 在当前事务创建快照前，该记录行的值被事务 ID 为 DB_TRX_ID 的事务修改了，但没有提交；或者 ② 在当前事务创建快照后，该记录行的值被事务 ID 为 DB_TRX_ID 的事务修改了。这些情况下，这个记录行的值对当前事务都是不可见的。跳到步骤 5
   • 在活跃事务列表中找不到，则表明“id 为 trx_id 的事务”在修改“该记录行的值”后，在“当前事务”创建快照前就已经提交了，所以记录行对当前事务可见
5. 在该记录行的 DB_ROLL_PTR 指针所指向的 undo log 取出快照记录，用快照记录的 DB_TRX_ID 跳到步骤 1 重新开始判断，直到找到满足的快照版本或返回空。

InnoDB的事务隔离级别 read-committed 和 repeatable-read 都是使用MVCC，但生成 ReadView 的时机不同：

• read-committed：每次select前都会生成快照；（不可重复读！因为每次生成的快照中，原本还未完成对该记录行的修改可能已经完成，所以在本事务后面生成的快照中，对修改后的记录行可见了，也就是读同一行出现修改前修改后不同的结果。）
• repeatable-read：只在事务的第一次select前生成快照。

防止幻读

repeatable-read级别 通过 MVCC + Next-key Lock 来解决幻读。

对于不同的查询，有：

• 执行普通的 select，仍以 MVCC快照读 的方式读取数据。

  因为快照只生成一次，在快照之后其他事务所做的更新插入都对本事务不可见，避免了幻读。

• 执行 select…for update/lock in share mode、insert、update、delete 等当前读 ：

  在当前读下，读取的都是最新的数据，如果其它事务有插入新的记录，并且刚好在当前事务查询范围内，就会产生幻读！InnoDB 使用 Next-key Lock 来防止这种情况。当执行当前读时，会锁定读取到的记录的同时，锁定它们的间隙，防止其它事务在查询范围内插入数据。只要不让别的事务插入，就不会发生幻读

零碎知识

自增值

自增值的保存位置：

• 在mysql5.x版本，InnoDB的自增值保存在内存里，并没有持久化。在第一次打开表时，都会找当前自增值的最大值+1 来作为这个表当前的自增值。
• 在mysql8.0版本之后，自增值的变更记录放在redo log中，使自增值持久化。

自增值不一定是连续的，但一定是递增。不连续的例子有：

• 自增步长 auto_increment_increment 不为1。
• 插入时指定自增字段的值。该值若大于自增值，则当前自增值修改为改值+1。
• 插入时发生唯一键冲突，自增值+1后是不会回退的。
• 事务回滚，自增值+1后也不会回退。
• 批量插入数据，MySQL有一个批量申请自增id的策略： 语句执行中，第n次申请会直接分配 2^n-1^个。（如 insert…select语句 ）

时间类型存储建议

首先，不要用字符串存储日期，因为字符串占用空间大，且识别日期效率低，无法使用日期相关的API。

有三种数据类型可供选择：DateTime，Timestamp，时间戳

日期类型	存储空间	日期格式	日期范围	时区问题
DateTime	8字节	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 到 9999-12-31 23:59:59	有
Timestamp	4字节	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:00 到 2037-12-31 23:59:59	无
时间戳	4字节	全数字如1578903421	1970-01-01 00:00:01 之后的时间	无

DateTime没有时区信息，它保存的是当前会话的时区对应的时间，如果服务器更换时区后，数据库中的这个时间就是错误的了。

Timestamp和时区有关，它的值会随服务器时区的变化而变化。

时间戳是指从基准时间（1970-01-01 00:00:00）开始经过的时间。

• 一般的，我们尽量选择使用Timestamp，因为它比DateTime空间效率高，而时间戳的表示形式不够直观。

隐式转换

当操作符与不同类型的操作数一起使用时，会发生类型转换以使操作数兼容。某些转换是隐式发生的。隐私转换 可能会导致 索引失效。

例如，MySQL 会根据需要自动将字符串转换为数字，反之亦然。以下规则描述了比较操作的转换方式：

1. 两个参数至少有一个是NULL时，比较的结果也是NULL，特殊的情况是使用<=>对两个NULL做比较时会返回1，这两种情况都不需要做类型转换
2. 两个参数都是字符串，会按照字符串来比较，不做类型转换
3. 两个参数都是整数，按照整数来比较，不做类型转换
4. 十六进制的值和非数字做比较时，会被当做二进制串
5. 有一个参数是TIMESTAMP或DATETIME，并且另外一个参数是常量，常量会被转换为timestamp
6. 有一个参数是decimal类型，如果另外一个参数是decimal或者整数，会将整数转换为decimal后进行比较，如果另外一个参数是浮点数，则会把decimal转换为浮点数进行比较
7. 所有其他情况下，两个参数都会被转换为浮点数再进行比较

像是 字符串类型的字段 num， 当遇到 where num = 100； 是第7种情况，两边都转化为浮点数100， 但由于其他字符串也能转化为100（如‘100a’，‘0100’），这样是不能使用索引的，索引会导致索引失效。

总结：

• 当操作符左右两边的数据类型不一致时，会发生隐式转换。
• 当 where 查询操作符左边为数值类型时发生了隐式转换，那么对效率影响不大，但还是不推荐这么做。
• 当 where 查询操作符左边为字符类型时发生了隐式转换，那么会导致索引失效，造成全表扫描效率极低。
• 字符串转换为数值类型时，非数字开头的字符串会转化为0，以数字开头的字符串会截取从第一个字符到第一个非数字内容为止的值为转化结果。

MySQL高性能规范

数据库命名规范：

*

MySQL建表时注意：

1. 注意选择存储引擎，如果要支持事务需要选择nnoDB。

2. 注意字段类型的选择：

   对于日期类型如果要记录时分秒建议使用datetime，只记录年月日使用date类型；

   对于字符类型的选择，固定长度字段选择char，不固定长度的字段选择varchar，varcharb比char节省空间但速度没有char快； 对于内容介绍类的长广文本字段使用 text 或 longtext类型；

   如果存储图片等二进制数据使用blob或 longblob类型；

   对金额字段建议使用DECIMAL；

   对于数值类型的字段在确保取值范围足够的前提下尽量使用占用空间较小的类型

3. 主键字段建议使用自然主键，不要有业务意义，建议使用int unsigned类型，特殊场景使用bigint类型。

4. 如果要存储text、blob字段建议单独建一张表，使用外键关联。

5. 尽量不要定义外键，保证表的独立性，可以存在外键意义的字段。

6. 设置字段默认值，比如：状态、创建时间等。

7. 每个字段写清楚注释。

8. 注意字段的约束，比如：非空、唯一、主键等。

优化

数据库优化

读写分离

读写分离主要是为了将对数据库的读写操作分散到不同的数据库节点上。 这样的话，就能够小幅提升写性能，大幅提升读性能。

读写分离会产生的影响：

• 主库和从库的数据存在延迟，比如你写完主库之后，主库的数据同步到从库是需要时间的，这个时间差就导致了主库和从库的数据不一致性问题。这也就是我们经常说的主从同步延迟。
• 主从同步延迟问题的解决方案： 1. 将那些必须获取最新数据的读请求都交给主数据库处理； 2. 延迟读取（如支付成功后，跳到一个字符成功页面，点击返回才能回到自己的账户，避免立即进行请求操作）

读写分离的实现：

1. 部署多台数据库，一台作为主数据库，其他的作为从数据库。
2. 保证主数据库和从数据库之间的数据是实时同步的，这个过程也就是主从复制。
3. 系统将写请求交给主数据库处理，读请求交给从数据库处理。

具体的落地操作，可以使用代理方式或组件方式

• 代理方式： 在应用和数据中间加了一个代理层。代理层负责分离读写请求，将它们路由到对应的数据库中。 提供类似功能的中间件有 MySQL Router（官方）、Atlas（基于 MySQL Proxy）、MaxScale、MyCat。
• 组件方式： 使用 sharding-jdbc

主从复制的原理是：MySQL的 binlog记录了数据的所有变化，所以从库可以拿到主库的binlog来进行数据同步。

分库分表

读写分离主要应对的是数据库读并发，没有解决数据库存储问题。 如果一张表的数据量过大，为了缓解存储压力，就需要进行分库分表。

• 分库 就是将数据库中的数据分散到不同的数据库上，可以垂直分库，也可以水平分库。

• 分表 就是对单表的数据进行拆分，可以是垂直拆分，也可以是水平拆分。

  • 垂直拆分：就是按列拆，将列比较多的表拆开；
  • 水平拆分：按行拆，列不变，可以解决单表数据量过大的问题；

常见的分片算法：

• 哈希分片：适合随机读写的场景，不适合范围查询；
• 范围分片：适合范围查询，不适合随机读写（数据未分散，容易出现局部热点数据问题）
• 地理位置分片：很多 NewSQL 数据库都支持地理位置分片算法，也就是根据地理位置（如城市、地域）来分配数据。
• 融合算法：灵活组合多种分片算法，比如将哈希分片和范围分片组合。

分库分表带来的问题：

• 无法使用join操作
• 需要使用分布式事务
• 需要使用分布式id
• 跨库聚合查询需要编写复杂的代码

分库分表推荐实现： 推荐使用ShardingSphere，ShardingSphere 的功能完善，除了支持读写分离和分库分表，还提供分布式事务、数据库治理、影子库、数据加密和脱敏等功能。

SQL优化

1. **避免使用 select *** ，因为：

   • SELECT * 会消耗更多的 CPU。
   • SELECT * 无用字段增加网络带宽资源消耗，增加数据传输时间，尤其是大字段（如 varchar、blob、text）。
   • SELECT * 无法使用 MySQL 优化器覆盖索引的优化（基于 MySQL 优化器的“覆盖索引”策略 速度极快，效率极高，业界极为推荐的查询优化方式）
   • SELECT <字段列表> 可减少表结构变更带来的影响。

2. 分页优化

   在数据量很大时，分页会很耗时，优化方法有：

   1. 使用子查询，先查询出 limit 第一个参数对应的主键值，再根据这个主键值再去过滤并 limit，这样效率会更快。

      SELECT `score`,`name` FROM `cus_order` 
      WHERE id >= (SELECT id FROM `cus_order` LIMIT 1000000, 1) 
      LIMIT 10

      • 子查询的结果会产生一张新表，会影响性能，应该尽量避免大量使用子查询。

   2. 除了子查询之外，还以采用延迟查询的方式来优化。

      SELECT `score`,`name` 
      FROM `cus_order` a, 
      	(SELECT id from `cus_order` ORDER BY `score` DESC LIMIT 1000000, 10) b 
      where a.id = b.id

3. 避免做多表join

   阿里开发手册要求： 超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时，保证被关联的字段需要有索引。

   其实系统并发量不大的话，用join也没问题，很多业务复杂的查询经常要join3、5张表以上才能查出来。

   优化方法：

   • 单表查询后在内存中自己做关联 （推荐）：对数据库做单表查询，再根据查询结果进行二次查询，以此类推，最后再进行关联。
   • 数据冗余：把一些重要的数据在表中做冗余，尽可能地避免关联查询。很笨的一种做法，表结构比较稳定的情况下才会考虑这种做法。进行冗余设计之前，思考一下自己的表结构设计的是否有问题。

4. 不要用外键和级联

5. 选择合适的字段类型

   存储字节越小，占用也就空间越小，性能也越好。

   • 某些字符串可以转换成数字类型存储，比如可以将 IP 地址转换成整型数据。

     MySQL 提供了两个方法来处理 ip 地址

     • INET_ATON() ： 把 ip 转为无符号整型 (4-8 位)
     • INET_NTOA() ：把整型的 ip 转为地址

   • 对于非负型的数据 (如自增 ID,整型 IP，年龄) 来说，要优先使用无符号整型来存储。

   • 小数值类型（比如年龄、状态表示如 0/1）优先使用 TINYINT 类型。

   • 对于日期类型来说， 一定不要用字符串存储日期。可以考虑 DATETIME、TIMESTAMP 和 数值型时间戳。

   • 金额字段用 decimal，避免精度丢失。

   • 使用自增 id 作为主键。

     • 因为主键索引结果是B+树，会保持节点有序，需要新加入值自增，则可以直接加到B+树尾部，否则需要进行查找再插入，如果页写满了，还需要加悲观锁进行页分裂，性能很低。

   • 不建议用 NULL 作为默认值（NULL 跟 ‘’（空字符串）是两个完全不一样的值）

     • NULL 代表一个不确定的值，就算是两个 NULL，它俩也不一定相等。例如，SELECT NULL=NULL的结果为 false，但是在使用DISTINCT，GROUP BY，ORDER BY时，NULL又被认为是相等的。
     • ‘’ 的长度是 0，是不占用空间的，而NULL 是需要占用空间的。
     • NULL 会影响聚合函数的结果。例如，SUM、AVG、MIN、MAX 等聚合函数会忽略 NULL 值。 COUNT 的处理方式取决于参数的类型。如果是 COUNT(*)，则会统计所有的记录数，包括 NULL 值；如果参数是某个字段名 COUNT(列名)，则会忽略 NULL 值，只统计非空值的个数。
     • 查询 NULL 值时，必须使用 IS NULL 或 IS NOT NULL 来判断，而不能使用 =、!=、 <、> 之类的比较运算符。而 ‘’ 是可以使用这些比较运算符的。

6. 尽量用 UNION ALL 代替 UNION

   因为 union 默认去重，会影响性能； 只有业务要求不允许产生重复值，还是可以用 union all

7. 批量操作

   对于数据库中的数据更新，如果能使用批量操作就要尽量使用，减少请求数据库的次数，提高性能。

   # 反例 
   INSERT INTO `cus_order` (`id`, `score`, `name`) VALUES (1, 426547, 'user1'); 
   INSERT INTO `cus_order` (`id`, `score`, `name`) VALUES (1, 33, 'user2'); 
   INSERT INTO `cus_order` (`id`, `score`, `name`) VALUES (1, 293854, 'user3'); 
   # 正例 
   INSERT into `cus_order` (`id`, `score`, `name`) values(1, 426547, 'user1'),(1, 33, 'user2'),(1, 293854, 'user3');

8. 优化慢查询

   出于性能层面的考虑，慢查询日志功能默认是关闭的，需要先开启：

   # 开启慢查询日志功能 
   SET GLOBAL slow_query_log = 'ON'; 
   # 慢查询日志存放位置 
   SET GLOBAL slow_query_log_file = '/var/lib/mysql/ranking-list-slow.log'; 
   # 无论是否超时，未被索引的记录也会记录下来。 
   SET GLOBAL log_queries_not_using_indexes = 'ON'; 
   # 慢查询阈值（秒），SQL 执行超过这个阈值将被记录在日志中。 
   SET SESSION long_query_time = 1; 
   # 慢查询仅记录扫描行数大于此参数的 
   SQL SET SESSION min_examined_row_limit = 100;

   设置成功之后，使用 show variables like ‘slow%’; 命令进行查看。

   | Variable_name 	  | Value 								 | 
   +---------------------+--------------------------------------+ 
   | slow_launch_time 	  | 2 									 | 
   | slow_query_log      | ON 									 | 
   | slow_query_log_file | /var/lib/mysql/ranking-list-slow.log | 
   +---------------------+--------------------------------------+ 
   3 rows in set (0.01 sec)

   查看慢查询日志，其中的大概格式为，其信息说明如下：

   # Time: 2022-10-09T08:55:37.486797Z 
   # User@Host: root[root] @ [172.17.0.1] Id: 14 
   # Query_time: 0.978054 Lock_time: 0.000164 Rows_sent: 999999 Rows_examined: 1999998 
   SET timestamp=1665305736; 
   SELECT `score`,`name` FROM `cus_order` ORDER BY `score` DESC;

   • Time ：被日志记录的代码在服务器上的运行时间。
   • User@Host：谁执行的这段代码。
   • Query_time：这段代码运行时长。
   • Lock_time：执行这段代码时，锁定了多久。
   • Rows_sent：慢查询返回的记录。
   • Rows_examined：慢查询扫描过的行数。

   实际项目中，慢查询日志通常会比较复杂，我们需要借助一些工具对其进行分析。像 MySQL 内置的 mysqldumpslow 工具就可以把相同的 SQL 归为一类，并统计出归类项的执行次数和每次执行的耗时等一系列对应的情况。

   找到了慢 SQL 之后，我们可以通过 EXPLAIN 命令分析对应的 SELECT 语句。

9. 正确使用索引