MySQL 数据库高级特性

在 MySQL 数据库中，视图、索引、函数、存储过程等通常属于数据库的结构化定义或高级功能部分，用于提升性能、增强功能以及支持复杂业务逻辑。

索引

索引是关系型数据库用来提升查询性能最为重要的手段。关系型数据库中的索引就像书本中目录一样。创建索引虽然会带来存储空间上的开销，但在牺牲空间换来查询效率的提升是显著的。

MySQL 数据库中所有数据类型的列都可以被索引。对于 MySQL 的 InnoDB 存储引擎来说，它支持三种类型的索引，分别是 B+ 树索引、全文索引和 R 树索引。这里介绍最广泛的 B+ 树索引。

B+ 树是一棵平衡树，树的高度通常为 3 或 4，但是却可以保存百万级别到十亿级别的数据，而从这些数据中查询一条数据，只需要 3 次或 4 次 I/O 操作。

B+ 树由根节点、中间节点和叶子节点构成。其中，叶子节点用来保存排序后的数据。由于记录在索引上是排序过的，因此在一个叶子节点内查找数据时可以使用二分查找，这种查找方法效率很高。当数据很少时，B+ 树只有一个根节点，数据也就保存在根节点上。随着记录越来越多，B+ 树会发生分裂，根节点不再保存数据，而是提供访问下一层节点的指针，帮助快速确定数据在哪个叶子节点上。

聚集索引

在创建二维表时，我们通常都会为表指定主键列，主键列上默认会创建主键索引。对于 MySQL InnoDB 存储引擎来说，主键索引被用来组织和存储整张表的数据，这种组织方式被称为索引组织表。表的数据行按照主键的顺序存储在聚集索引的叶节点中。这种索引与数据直接相关联，因此被称为聚集索引（clustered index）

很显然，每张表只能有一个聚集索引，否则表的数据就要保存多次。所以我们自己创建的索引通常都是二级索引，更常见的叫法是非聚集索引。

非聚集索引

非聚集索引（Non-clustered Index） 是一种数据库索引类型，与聚合索引相对应。它在索引中保存的是索引列的值和指向实际数据行的主键指针，而不是直接保存数据本身。这使得非聚集索引在查询中能够快速定位到所需的数据行，然后再根据指针访问实际的数据。

非聚合索引通常适用于频繁进行查询而不是频繁更新的列上，因为在更新数据时，需要更新索引和数据行之间的指针，可能会带来额外的开销。

相比之下，聚合索引通常更适合频繁更新的列，因为数据行和索引在同一位置维护的，更新操作会更加高效。

当非聚合索引中不包含查询所需字段时，数据库通过非聚合索引获取主键，再通过主键去聚合索引中查询数据，这个过程被称为“回表”。它会增加 I/O 操作，降低查询性能。

为了优化性能，可以通过创建一个覆盖索引（包含所有需要查询的列）来避免回表操作。

接下来通过一个简单的例子说明索引。

我们使用 MySQL 的 explain 关键字来查看 SQL 的执行计划，以了解数据库是如何执行你的查询的，以及它是否有效利用了索引或其他优化技术。

EXPLAIN 关键字的一般语法：

EXPLAIN SELECT column1, column2, ... FROM table_name WHERE condition;

说明：这将返回一个关于查询执行计划的结果集，包括以下信息：

• id：查询操作的标识符。
• select_type：查询类型，例如：
  • SIMPLE：简单的 SELECT，不需要使用 UNION 操作或子查询。
  • PRIMARY：如果查询包含子查询，最外层的 SELECT 被标记为 PRIMARY。
  • UNION：UNION 操作中的第二个或后面的 SELECT 语句。
  • SUBQUERY：子查询中的第一个 SELECT。
  • DERIVED：派生表的 SELECT 子查询。
• table：涉及到的表。
• type：查找满足条件的行数据的方式，即访问类型，例如：
  • ALL：全表扫描。
  • index：索引全扫描，只遍历索引树。
  • range：索引范围扫描。
  • ref：非唯一索引扫描。
  • eq_ref：唯一索引扫描。
  • const/system：常量级查询。
  • NULL：不需要访问表或索引。
  • 在所有访问类型中，显然 ALL 是性能最差的，扫描全表中的每一行寻找匹配的行。
• possible_keys：MySQL 可以使用的索引，但有可能不会使用。
• key：MySQL 实际使用的索引，如果为 NULL 表示没有使用索引。
• key_len：使用的索引字段的长度，在不影响查询的情况下长度越短越好。
• ref：索引与表之间的引用，表示查询的参考对象。
  • const：表示使用了一个常量值来进行索引查找，通常发生在查询条件是一个确切的常量值，使得优化器能够直接定位到索引中相应的值。
  • eq_ref：表示在连接查询时使用了索引，并且查询使用的索引是唯一索引或主键索引。这意味着每个索引键值在索引中只有一行对应，通常发生在使用连接条件和主键或唯一键进行连接查询时。
  • ref：表示在查询过程中使用了普通索引或覆盖索引，但查询的结果不是唯一的。这种情况下，MySQL 必须进行索引查找，并检查匹配索引键值的行。
  • range：表示查询使用了范围查找，通常发生在查询条件中使用了不等号（如 <, >, BETWEEN 等）或者 IN 等条件。
  • fulltext：表示在全文索引查询中使用了全文索引。
  • const_rowid：表示使用了一个常量值来进行行 ID 的查找。这种情况通常发生在查询条件是一个确切的常量值，并且表使用了隐式的隐藏列作为行 ID 进行索引查找。
• rows：根据查询条件的统计信息估算扫描的行数。
• Extra：关于查询额外的信息，包括：
  • Using filesort：MySQL 无法利用索引完成排序操作。
  • Using index：只使用索引的信息而不需要进一步查表来获取更多的信息。
  • Using temporary：MySQL 需要使用临时表来存储结果集，常用于分组和排序。
  • Impossible where：where子句会导致没有符合条件的行。
  • Distinct：MySQL 发现第一个匹配行后，停止为当前的行组合搜索更多的行。
  • Using where：查询的列未被索引覆盖，筛选条件并不是索引的前导列。

示例：

MariaDB [school]> explain select * from tb_student where stu_name='xxx'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_student
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 8
        Extra: Using where
1 row in set (0.00 sec)

说明：

• \G 将输出信息显示成一个单独的块。

在上面的执行计划中，可以看出，当我们通过学生姓名查询学生时，实际上进行的全表扫描。这样查询性能明显很糟糕，尤其在有很多行数据的表中。

如果我们需要经常通过学生名称查询学生，可以在学生名称对应的列上创建索引，通过索引加速查询。

创建索引

创建索引将使数据库系统在执行查询时能够更快地定位到匹配条件的行。

创建索引的语法：

CREATE INDEX index_name ON table_name(column_name);

说明：

• INDEX：创建索引的关键字。
• index_name：创建的索引的名称。
• column_name：要创建索引的列名。

示例：

添加学生记录：

MariaDB [school]> insert into `tb_student` 
    ->     (`stu_id`, `stu_name`, `stu_sex`, `stu_birth`, `stu_addr`, `col_id`) 
    -> values
    ->     (3011, '林震南', 1, '1985-12-12', '福建莆田', 3),
    ->     (3755, '项少龙', 1, '1993-1-25', '四川成都', 3),
    ->     (3923, '杨不悔', 0, '1985-4-17', '四川成都', 3);
Query OK, 3 rows affected (0.11 sec)
Records: 3  Duplicates: 0  Warnings: 0

创建索引：

MariaDB [school]> create index idx_student_name on tb_student(stu_name);
Query OK, 0 rows affected (1.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

再次查看之前 SQL 查询语句的执行计划：

MariaDB [school]> explain select * from tb_student where stu_name='林震南'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_student
         type: ref
possible_keys: idx_student_name
          key: idx_student_name
      key_len: 82
          ref: const
         rows: 1
        Extra: Using index condition
1 row in set (0.00 sec)

说明：

• rows：根据查询条件的统计信息估算扫描的行数。即使查询条件中的值在表中不存在（如 stu_name='xxx'），MySQL 也会假定有一个匹配的行，并且返回的行数为 1。

可以注意到，在对学生姓名创建索引后，查询由之前的全表扫描变成了基于索引的查询，而且扫描只有唯一的一行，这显示提升了查询的性能。

MySQL 中还支持创建前缀索引，即对索引字段的前 N 个字符创建索引，这样可以减少索引占用的空间，但节约了空间很可能会浪费时间（时间和空间是需要调和平衡矛盾）。

创建前缀索引

创建前缀索引（Prefix Index）是指在索引中只存储列值的前缀，而不是整个列值。这种索引可以节省存储空间并提高查询性能，特别是在需要索引长文本或大字符串列时。

可以通过在创建索引时指定索引长度来创建前缀索引。一般语法：

CREATE INDEX prefix_index_name ON table_name(column_name(n));

说明：n 指定创建前缀索引的长度。

示例：

若在表 tb_student 的 stu_name 列上创建一个最大长度为 1 的前缀索引：

create index prefix_indx_student_name on tb_student(stu_name(1));

再次查询 SQL 执行计划：

MariaDB [school]> explain select * from tb_student where stu_name='林震南'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_student
         type: ref
possible_keys: idx_student_name,prefix_index_student_name
          key: idx_student_name
      key_len: 82
          ref: const
         rows: 1
        Extra: Using index condition
1 row in set (0.00 sec)

说明：

• 索引的选择是由查询优化器根据查询条件、索引的统计信息以及数据库统计信息等因素进行决策的。即使存在多个索引，优化器也可能选择一个最适合当前查询条件的索引来执行查询。
• 当前使用索引 idx_student_name，可能用到的索引是 idx_student_name,prefix_index_student_name。

某些情况下，我们想使用特定索引执行 SQL，使用 FORCE INDEX 子句来强制执行。如使用前缀索引 prefix_index_student_name 检查 SQL 执行情况：

MariaDB [school]>  explain select * from tb_student force index (prefix_index_student_name) where stu_name=' 林震南'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_student
         type: ref
possible_keys: prefix_index_student_name
          key: prefix_index_student_name
      key_len: 6
          ref: const
         rows: 2
        Extra: Using where
1 row in set (0.00 sec)

可以注意到，使用指定的前缀索引 prefix_index_student_name 扫描有 2 行，这是因为学生表里有两个姓林的同学，我们使用前缀索引以林索引，在查询时就会找到这两行数据。

删除索引

若删除索引，如删除 idx_student_name，使用以下 SQL 语句：

alter table tb_student drop index idx_student_name;

或

drop index idx_student_name on tb_student;

在创建索引时，我们还可以使用复合索引、函数索引（MySQL 5.7 开始支持），用好复合索引实现索引覆盖能减少不必要的排序和回表操作，使查询的性能得到成倍的提升。

索引的设计原则：

1. 最适合索引的列是出现在 WHERE 子句和连接子句中的列。
2. 索引列的基数越大（取值多、重复值少），索引的效果越好。
3. 使用前缀索引可以减少索引占用的空间，内存中可以缓存更多的索引。
4. 索引并不是越多越好，虽然索引提高了读操作（查询）的效率，但是写操作（增、删、改）都会变慢。因为数据的变化会导致索引的更新，就如图书籍章节的增删需要同步更新目录索引一样。
5. 使用 InnoDB 存储引擎时，表的非聚集索引在存储时会包含主键值作为指针，以便能够通过主键访问实际数据行。因此，主键应尽可能选择短的数据类型，这样可以有效地减少索引占用的空间，提升索引的缓存效果。

InnoDB 存储引擎使用 B-tree 索引时，在数据列上的表现：

• 等值查询：索引会生效，能够快速定位匹配的记录。
• 范围查询（如 >、<、>=、<=、BETWEEN...AND...）：索引依然生效，可以高效地找到满足条件范围内的数据。
• 不等查询（<>）：尽管索引仍然可以使用，但效果可能不如等值查询和范围查询高效，因为数据库可能需要扫描多个范围的数据。
• 对于字符串类型的列的 LIKE 查询：
  • 当查询模式不以通配符开头的模糊查询（如 LIKE 'abc%'），索引可以生效，因为 B-tree 索引可以顺序定位到以 abc 开头的记录。
  • 如果查询模式以通配符开头（如 LIKE '%abc' 或 LIKE 'abc'），索引将失效。因为 B-tree 索引无法从后往前或在字符串中间定位匹配数据，因此需要对整个表做全表扫描。

视图

MySQL中的视图是基于 SQL 查询结果的虚拟表。视图本身不包含实际存储的数据，而是根据查询定义的规则动态生成数据。

视图可以简化复杂查询、隐藏数据结构、提高安全性等。

• 将实体数据表隐藏起来，让外部程序无法得知实际的数据结构。让访问者使用表的组成部分而不是整个表，降低数据库被攻击的风险。
• 大多数情况下，视图是只读的（更新视图有诸多限制），外部程序无法直接透过视图修改数据。
• 重用 SQL 语句，将高度复杂的查询包装在视图表中，直接访问视图即可取出所需数据；也可以将视图视为数据表进行连接查询。
• 视图可以返回与实体数据表不同格式的数据，在创建视图时对数据进行格式化处理。

创建视图

基本语法：

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

其中，view_name 是视图的名称，column1, column2, ... 是视图中包含的列，table_name 是视图基于的表，condition 是视图的筛选条件。

示例：

-- 创建视图
create view `vw_avg_score`
as
	select `stu_id`, round(avg(`score`), 1) as `avg_score`
	from `tb_record` group by `stu_id`;

返回结果：

MariaDB [school]> select * from `vw_avg_score`;
+--------+-----------+
| stu_id | avg_score |
+--------+-----------+
|   1001 |      95.6 |
|   1002 |      53.5 |
|   1033 |      84.3 |
|   5566 |      75.0 |
+--------+-----------+
4 rows in set (0.00 sec)

-- 基于已有的视图创建视图
create view `vw_student_score`
as
	select `stu_name`, `avg_score`
	from `tb_student` natural join `vw_avg_score`;

使用视图

像查询表一样使用视图，基本语法：

SELECT * FROM view_name;

查询视图 vw_student_score：

select * from `vw_student_score` order by `avg_score` desc;

返回结果：

+-----------+-----------+
| stu_name  | avg_score |
+-----------+-----------+
| 杨过      |      95.6 |
| 王语嫣    |      84.3 |
| 任我行    |      53.5 |
+-----------+-----------+
3 rows in set (0.02 sec)

注意：因为视图本身不存储数据，所以，每次使用视图时，都必须执行查询以获取数据。如果你使用了连接查询、嵌套查询创建较为复杂的视图，可能导致查询性能下降。因此，使用复杂视图之前，应测试以确保其性能满足应用的需求。

视图的规则和限制：

• 视图可以嵌套，可以利用从其他视图中检索的数据来构造一个新的视图。视图也可以和表一起使用。
• 创建视图时可以使用 order by 子句，但如果从视图中检索数据时也使用了 order by，那么该视图中原先的 order by 会被覆盖。
• 视图无法使用索引，也不会激发触发器（实际开发中因为性能等考虑，通常也不建议使用触发器）的执行。

视图中的数据是否可以更新，取决于视图的定义以及基础表的数据结构。一般来说，可以更新视图中的数据，但存在一些限制：

可以更新的情况：

• 如果视图是基于单个基础表创建的，且没有复杂的 SQL 元素（如聚合函数、DISTINCT、 GROUP BY、 HAVING等），通常可以直接对视图中的数据进行更新、插入或删除，修改会反应到基础表中。
• 视图中的列必须能够映射到基础表的列，这样才能正确传递更新操作。

不可更新的情况：

• 聚合视图：使用聚合函数（SUM、MIN、MAX、AVG、COUNT等 ）
• DISTINCT、GROUP BY、HAVING 等会对数据进行分组或去重，导致数据在视图中的映射变得复杂，无法直接更新。
• UNION 和 UNION ALL：这类操作涉及将多个表的数据合并到一个结果集，数据库无法确定应该更新哪个基础表中的数据。
• SELECT 中包含子查询的视图。
• FROM 子句中包含有不能更新的视图的视图。（如包含了聚合、JOIN 或其他复杂操作）。
• WHERE 子句的子查询引用了 FROM 子句中的表的视图。MySQL 将无法确定如何将 WHERE 子句中的子查询更新回原始的基础表。

删除视图

删除视图基本语法：

DROP VIEW view_name;

说明：如果是更新视图，可以先使用该命令删除视图，再创建视图；也可以通过 create or replace view 来更新视图。

删除视图示例：

MariaDB [school]> drop view vw_student_score;
Query OK, 0 rows affected (0.00 sec)

函数

函数是用来封装功能上相对独立且会被重复使用的代码。MySQL 中的函数可以执行 SQL 语句。

自定义函数

通过自定义函数实现截断超长字符串的功能，示例：

delimiter $$

create function truncate_string(
	content varchar(10000),
	max_length int unsigned
) returns varchar(10000) no sql
begin
	declare result varchar(10000) default content;
	if char_length(content) > max_length then
       set result = left(content, max_length);
	   set result = concat(result, '......');
	end if;
	return result;
end $$

delimiter ;

说明：

• delimiter $$：设置 SQL 语句分隔符为 $$，这样在函数体中使用 ; 而不会被误认为语句结束符。end $$：标记整段代码的结束位置。
• delimiter ;：将 SQL 语句分隔符恢复为默认的 ;。
• create function truncate_string(...)：定义一个名为 truncate_string 的函数：
  • 接受两个参数：content （待截断的字符串）和 max_length （截断的最大长度）。
  • returns varchar(10000)：表示该函数返回一个最大长度为 10000 的字符串。
  • no sql：声明函数体没有使用 SQL 语句，不会修改数据库的数据。如果函数体中需要通过 SQL 读取数据，需要声明为 reads sql data。
• begin：开始函数体。end $$：结束函数体，使用 $$ 作为语句结束符。
• declare result varchar(10000) default content;：声明一个名为 result 的变量，用来存储处理后的结果，默认值为传入的 content。
• if... end if;：if 条件语句块。
  • if char_length(content) > max_length：判断输入的字符串长度是否超过了指定的最大长度。
  • left(content, max_length)：使用 LEFT 函数截取输入字符串的前 max_length 个字符。
  • concat(result, '......')：在截取后的字符串末尾添加省略号。

调用函数

在查询中调用自定义函数：

select truncate_string('是强烈而不可抗拒的宿命感，一明一暗两条彻底不同的轨迹在肮脏泥泞阴暗的小希斯里一刹那间碰撞，激烈，压抑又汹涌。', 12) as short_string;

返回结果：

MariaDB [school]> select truncate_string('是强烈而不可抗拒的宿命感，一明一暗两条彻底不同的轨迹在肮脏泥泞阴暗的小希斯里一刹那间碰撞，激烈，压抑又汹涌。', 12) as short_string;
+--------------------------------------------+
| short_string                               |
+--------------------------------------------+
| 是强烈而不可抗拒的宿命感......             |
+--------------------------------------------+
1 row in set (0.00 sec)

说明：as short_string：指定输出的列名。

删除函数

若要覆盖已存在的函数，可以使用 DROP FUNCTION 命令删除已存在的函数，然后再重新创建新的函数。

例如：

DROP FUNCTION IF EXISTS truncate_string;

过程

过程（即存储过程，Stored Procedure）是事先编译好存储在数据库中的一组 SQL 的集合。它是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

调用过程可以简化应用程序开发人员的工作，减少与数据库服务器之间的通信，对于提升数据操作的性能有帮助。

学习到目前为止，使用的 SQL 语句都是针对一个或多个表的单条语句，但实际开发中经常会遇到需要多条 SQL 语句才能完成的情况。

例如，电商网站在受理用户订单时，需要进行如下一系列处理。

1. 通过查询核实库存是否有对应商品以及库存是否充足。
2. 如果库存有商品，需要锁定库存以确保这些商品不再卖给别人，同时要减少可用的物品数量以反映正确的库存量。
3. 如果库存不足，可能需要进一步与供应商进行交互或至少产生一条系统提示消息。
4. 不论受理订单是否成功，都应该产生流水记录，而且需要给对应的用户产生一条通知消息。

因此，我们可以通过过程将复杂的操作封装起来。这样不仅有助于保证数据的一致性，而且将来如果业务发生变动，只需要调整和修改过程即可。

对于调用过程的用户来说，过程并不会暴露数据表的细节，而且执行过程比一条条的执行 SQL 语句要快的多。

定义存储过程

下面的过程实现查询某门课程的最高分、最低分和平均分。

drop procedure if exists sp_score_stat;

delimiter $$

create procedure sp_score_stat(
	courseId int,
	out maxScore decimal(4, 1),
	out minScore decimal(4, 1),
	out avgScore decimal(4, 1)
)
begin
	select max(score) into maxScore from tb_record where cou_id=courseId;
	select min(score) into minScore from tb_record where cou_id=courseId;
	select avg(score) into avgScore from tb_record where cou_id=courseId;

end $$
delimiter ;

说明：

• DROP PROCEDURE IF EXISTS：如果存在某存储过程，则删除。
• create procedure sp_score_stat(...)：创建一个存储过程。
  • courseId：接收一个输入参数。此例表示课程的编号。
  • out：定义输出参数的关键字。
  • decimal(4, 1)：是 MySQL 中的一种数据类型表示一个固定精度的十进制数。其中，4 表示总共的数字位数，1 表示小数位数。
• SELECT INTO 语句用于将查询的数据结果存储到对应的输出参数中。
  • max()、min() 和 avg()：聚合函数，计算最值和平均值。

注意：默认情况下的参数都是输入参数。因为存储过程主要用于执行一系列的 SQL 语句或逻辑操作，而不是返回单个值。因此，存储过程通常没有返回值。但可以通过输出参数返回需要的结果。使用 out 关键字定义输出参数。

在存储过程中，我们可以定义变量、条件，可以使用分支和循环语句，可以通过游标操作查询结果，还可以使用时间调度器。

虽然存储过程有很多好处，但在实际开发中，如果频繁的使用存储过程并将大量复杂的运算放到存储过程中，会对数据库服务器造成巨大的压力。而数据库往往是有性能瓶颈的。

所以，对于互联网产品开发，一般建议数据库只做好存储，复杂的运算和处理交给应用服务器上的程序来完成。如果应用服务器不堪重负，可以通过部署多台应用服务器来分摊压力。

调用存储过程

call sp_score_stat(1111, @a, @b, @c);

获取输出参数

select @a as 最高分, @b as 最低分, @c as 平均分;

返回结果：

MariaDB [school]> create procedure sp_score_stat(
    -> courseId int,
    -> out maxScore decimal(4, 1),
    -> out minScore decimal(4, 1),
    -> out avgScore decimal(4, 1)
    -> )
    -> begin
    -> select max(score) into maxScore from tb_record where cou_id=courseId;
    -> select min(score) into minScore from tb_record where cou_id=courseId;
    -> select avg(score) into avgScore from tb_record where cou_id=courseId;
    -> 
    -> end $$
Query OK, 0 rows affected (0.00 sec)

MariaDB [school]> delimiter ;
MariaDB [school]> call sp_score_stat(1111, @a, @b, @c);
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [school]> select @a as 最高分, @b as 最低分, @c as 平均分;
+-----------+-----------+-----------+
| 最高分    | 最低分    | 平均分    |
+-----------+-----------+-----------+
|      95.0 |      65.0 |      83.1 |
+-----------+-----------+-----------+
1 row in set (0.00 sec)

删除过程

基本语法：

DROP PROCEDURE IF EXISTS procedure_name;

视图、函数、存储过程及触发器可以参考《MySQL必知必会》阅读了解。

其他内容

范式理论

范式理论是数据库设计的基础概念，旨在通过结构化的表设计来减少数据冗余和提高数据完整性。它将数据库表规范化为不同级别的范式（如第一范式、第二范式、第三范式等），以保证数据结构的逻辑性和高效性。

范式级别及其主要内容：

1. 第一范式（1NF）：要求表的每个列都是原子性的，不可再分。
2. 第二范式（2NF）：在满足 1NF 的基础上，消除非主属性对部分主键的依赖。
3. 第三范式（3NF）：在满足 2NF 的基础上，消除非主属性之间的传递依赖。

说明：范式理论的目标是减少数据冗余、提高数据的一致性和完整性，并简化数据库的维护和更新。但是，过度规范化可能会导致性能下降。因此，我们设计表时很有可能需要做反范式设计，增加冗余数据来获取更好的操作性能。

数据完整性

数据完整性是指数据库中的数据是准确和可靠的，能够反映真实的状态。数据完整性主要通过各种约束来保证，包括如下内容：

实体完整性，每个实体都是独一无二的。

• 主键（primary key）
• 唯一约束（unique）

引用完整性，即参照完性，关系中不允许引用不存在的实体。

域完整性，确保数据是有效的。

• 数据类型及长度
• 非空约束（not null）
• 默认值约束（default）
• 检查约束（check）

说明：在 MySQL 8.x 之前，检查约束不起作用。

数据一致性

数据一致性指数据库在事务处理后，数据仍然保持一致的状态。所有的数据库操作都应遵循一定的规则，使得数据在任意时刻都符合预期的完整性和业务逻辑约束。

事务是数据库管理系统执行的基本单位，它是由一组操作组成的逻辑工作单元，这些操作要么全部成功，要么全部失败回滚，保持数据库的一致性和完整性。

在 MySQL 中，事务是一组 SQL 语句，它们被视为一个单独的工作单元。

• 在 MySQL 中只有使用了 InnoDB 数据库引擎的数据库或表才支持事务。
• 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
• 事务用来管理 insert、update、delete 语句。

事务具有以下四个关键特性（ACID 特性）：

• 原子性：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么全部撤销，不会出现部分执行的情况。
• 一致性：事务应确保数据库状态从一个一致状态转变为另一个一致状态。
• 隔离性：事务的执行时相互隔离的，多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
• 持久性：已被提交的事务对数据库的修改应该永久保存在数据库中。即使发生系统崩溃或故障，数据也能够恢复到事务提交后的状态。

注意：在 MySQL 命令行的默认设置下，事务是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此，要显式开启一个事务必须使用命令 BEGIN 或 START TRANSCTION，或执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

MySQL 事务处理主要方式

1. 用 BEGIN、ROLLBACK、COMMIT 实现

   • BEGIN 或 START TRANSACTION：显示开启一个事务。
   • ROLLBACK 事务回滚，取消自上次提交所做的更改。
   • COMMIT：事务确认提交，使更改永久生效。

2. 直接使用 SET 更改 MySQL 的自动提交模式

   • SET AUTOCOMMIT=0 禁止自动提交事务
   • SET AUTOCOMMIT=1 开启自动提交事务

MySQL 中的事务操作

• 开启事务环境

begin;

或

start transaction;

• 提交事务

commit;

或

commit work;

• 回滚事务

rollback;

或

rollback work;

• 设置事务保存点

savepoint savepoint_name;

• 回滚到设置的事务保存点

rollback to savepoint savepoint_name;

• 查看事务隔离级别

SELECT @@tx_isolation;

返回：

+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

说明：InnoDB 存储引擎提供事务的隔离级别包括读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

• 设置事务的隔离级别

SET TRANSACTION ISOLATION LEVEL isolation_level; 

示例：

MariaDB [school]> set transaction isolation level READ COMMITTED;
ERROR 1568 (25001): Transaction isolation level can't be changed while a transaction is in progress
MariaDB [school]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

MariaDB [school]> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

MariaDB [school]> SELECT @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)

说明：

• 在事务进行中无法更改事务隔离级别。首先开始一个新的事务，设置所需要的事务隔离级别。
• 若设置当前会话的事务隔离级别：set session transaction isolation level read committed;。

一个简单的 MySQL 事务例子：

-- 开始事务
START TRANSACTION;

-- 可以设置事务隔离级别

-- 执行一些SQL语句

-- 判断是否要提交还是回滚
IF (condition) THEN
	commit;
ELSE
	ROLLBACK;
END IF;

关系型数据库，当存在多个并发事务访问数据时，可能会出现三类读数据的问题：脏读、不可重复读、幻读和两类更新数据的问题（第一类丢失更新、第二轮丢失更新）。了解参考《Java面试题全集（上）》一文的第80题。

为避免这些额外难题，关系型数据库底层有对应的锁机制，按照锁对象可分为表级锁和行级锁，按并发事务锁定关系可分为共享锁和独占锁。

然而使用锁是非常麻烦的，数据库有为用户提供自动锁机制。只要用户指定适当的事务隔离级别，数据库就会通过分析 SQL 语句，为事务访问的资源加上合适的锁。

了解 MySQL 事务和锁的细节支持，参考进阶读物《高性能MySQL》。

ANSI/ISO SQL 92标准定义了4个等级的事务隔离级别，如下表所示。需要说明的是，事务隔离级别和数据访问的并发性是对立的，事务隔离级别越高并发性就越差。所以要根据具体的应用来确定到底使用哪种事务隔离级别。

MySQL 数据库还有很多的内容可以探索学习，比如：MySQL 性能调优、MySQL运维相关工具、MySQL 数据备份和恢复、监控 MySQL 服务、部署高可用架构等等。