带你从入门到精通——MySQL（七. 事务、索引和视图）

原创已于 2024-11-25 09:34:48 修改 · 1.2k 阅读

28 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

收录于

MySQL

于 2024-11-24 19:39:20 首次发布

建议先阅读我之前的博客，掌握一定的MySQL前置知识后再阅读本文，链接如下

带你从入门到精通——MySQL（一. 基础知识）-CSDN博客

带你从入门到精通——MySQL（二. 单表查询）-CSDN博客

带你从入门到精通——MySQL（三. 多表查询）-CSDN博客

带你从入门到精通——MySQL（四. 常用函数一）-CSDN博客

带你从入门到精通——MySQL（五. 常用函数二）-CSDN博客

带你从入门到精通——MySQL（六. 窗口函数）-CSDN博客

七. MySQL事务、索引和视图

7.1 事务

7.1.1 存储引擎

MySQL提供了多种不同的存储引擎供我们使用，并且不同的表可以使用不同的存储引擎，对于MySQL来说事务是只有某些特定的存储引擎才支持的功能。

常见的存储引擎有: InnoDB、MyISAM、Memory、Archive、CSV等，可以使用以下MySQL语句查看数据库支持的存储引擎：

SHOW engines;

查询结果如下：

可以看到MySQL的初始默认存储引擎是MyISAM。

在MySQL中最常用存储引擎的是InnoDB和MyISAM，它们的区别如下：

1. InnoDB支持事务，而MyISAM不支持事务。

2. InnoDB支持外键，而MyISAM不支持外键。

3. InnoDB默认行锁，而MyISAM是表锁，注意InnoDB中也可以设置为表锁，表锁的含义是每次增加删除更新都会锁住整张表，意味着同一时间只能操作表中的一组数据，而行锁只锁住每次操作的指定行，意味着可以同时操作多组数据。

4. InnoDB是聚集索引（索引和数据不分离，存储在一起），而MyISAM是非聚集索引（索引和数据是分离的，不存储在一起）

5. InnoDB不存储表的总行数，在执行select count(*) from table_name的时候会全表查询，执行相对较慢。而MyISAM会存放表的总行数，所以select count(*）from table_name的时候相对较快。

7.1.2 事务的概念

事务是一组操作的集合，它是一个不可分割的工作单位。事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

事务应该具有4个特性: 原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。

原子性（atomicity）: 一个事务是一个不可分割的工作单位，事务中包括的操作要么同时成功，要么同时失败。

一致性（consistency）: 事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。

隔离性（isolation）: 一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

持久性（durability）: 一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

7.1.3 事务的分类

事务的分类主要是两种：隐式事务和显式事务。

隐式事务：该事务没有明显的开启和结束标记，它们都具有自动提交事务的功能；我们的DML语句就是隐式事务。

显式事务：该事务具有明显的开启和结束标记。使用显式事务的前提是先把自动提交事务的功能给禁用，禁用自动提交功能就是设置autocommit变量值为0，其中0表示禁用，1表示开启。

在MySQL中可以使用以下语句完成上述操作：

# 查看自动提交事务状态
SELECT @@autocommit;

# 禁用自动提交事务的方式
SET autocommit = 0;

在禁用自动提交功能后需要使用一下的命令来完成对事务的操作：

# 开启事务
START TRANSACTION / BEGIN;
# 提交事务
COMMIT;
# 回顾事务
ROLLBACK;

7.1.4 事务的并发问题

事务中存在的并发问题：主要有以下三种：

脏读：对于两个事务T1，T2，T1读取了已经被T2更新但还没有被提交的字段之后，若T2回滚，T1读取的内容就是临时且无效的。

不可重复读：对于两个事务T1，T2，T1读取了一个字段，然后T2更新了该字段之后，T1再读取同一个字段，值就不同了。

幻读：对于两个事务T1,T2，T1在A表中读取了一个字段，然后T2又在A表中插入了一些新的数据时，T1再读取该表时，就会发现此时读取的数据比之前多出几行。

7.1.5 事务的隔离级别

MySQL中的四种事务隔离级别如下：

Read uncommitted（读未提交数据）：允许事务读取未被其他事务提交的变更。（脏读、不可重复读和幻读的问题都会出现）。

Read committed（读已提交数据）：只允许事务读取已经被其他事务提交的变更（可以避免脏读，但不可重复读和幻读的问题仍然可能出现）

Repeatable read（可重复读）：确保事务可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其他事务对这个字段进行更新(update)。（可以避免脏读和不可重复读，但幻读仍然存在）

Serializable（串行化）：确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事务对该表执行插入、更新和删除操作，所有并发问题都可避免，但效率和性能十分低下。

我们可以使用以下语句查看MySQL的默认事务隔离级别:

SELECT @@transaction_isolation;

可以看到MySQL中默认的隔离级别是repeatable read。

7.2 索引

7.2.1 索引的概念

索引（Index）是帮助MySQL高效获取数据的数据结构，MySQL中索引也叫做‘’键（Key）”，它是一个特殊的文件，它保存着数据表里所有记录的位置信息，我们可以使用索引快速定位到某条记录的位置，并去到该位置获取此条记录，这比起按顺序暴力搜索所有记录的方法要更加高效，注意只有在大量数据中查询时索引才显得有意义。

在MySQL中索引是在存储引擎层实现的，所以不同存储引擎具有不同的索引和实现方式

7.2.2 索引的分类

在MySQL中常见的索引分类有以下四种：

按数据结构分类：B+tree索引、Full-text索引、Hash索引。这里的数据结构是指实现索引功能的底层所使用的数据结构，InnoDB和MyISAM的索引都是基于B+tree的。

按物理存储分类：聚集索引、非聚集索引（也叫二级索引、辅助索引）。注意： InnoDB是聚集索引，而MyISAM是非聚集索引。

按字段个数分类：单列索引、联合索引（也叫复合索引、组合索引）。‌注意：联合索引必须满足最左匹配原则：‌该原则是指在查询时，数据库会从联合索引的最左边的字段开始匹配条件。如果查询条件中包含联合索引中的第一个字段，则可以使用该索引进行数据检索；如果第一个字段匹配成功，则会继续匹配第二个字段，以此类推，直到所有字段都匹配完成，或者在匹配过程中遇到范围查询（如>、<等）时停止匹配

按字段特性分类：主键索引（PRIMARY KEY）、唯一索引（UNIQUE INDEX）、普通索引（INDEX）、全文索引（FULLTEXT）。

主键索引：建立在主键上的索引被称为主键索引，一张数据表只能有一个主键索引，索引列值不允许有空值，不允许重复，通常在创建表时一起创建。

唯一索引：建立在有UNIQUE约束的字段上的索引被称为唯一索引，一张表可以有多个唯一索引，索引列值允许为空，不允许重复。注意：列值中出现多个空值不会发生重复冲突。

普通索引：建立在普通字段上的索引被称为普通索引。

全文索引：MyISAM 存储引擎支持Full-text索引，用于查找文本中的关键词，而不是直接比较是否相等。Full-text索引一般使用倒排索引实现，它记录着关键词到其所在文档的映射。MySQL 5.6 以前的版本：只有 MyISAM 存储引擎支持全文索引；MySQL 5.6 及以后的版本：MyISAM 和 InnoDB 存储引擎均支持全文索引。注意: 只有字段的数据类型为char、varchar、text及其系列才可以建全文索引。

7.2.3 索引的增删查操作

具体示例代码如下：

# 查看所有索引
SHOW INDEX FROM table_name;

# 添加主键索引
ALTER TABLE table_name ADD PRIMARY KEY(field_name);
# 添加唯一索引
CREATE UNIQUE INDEX index_name ON table_name(field_name);
# 添加普通索引
CREATE INDEX index_name ON table_name(field_name);
# 添加全文索引(注意:字段类型只能为char、varchar、text及其系列)
CREATE FULLTEXT INDEX index_name ON table_name(field_name);
# 添加组合索引(注意:查询的时候必须遵循最左匹配原则)
CREATE INDEX index_name ON table_name(field_name1, field_name2);


# 删除主键索引
ALTER TABLE index_name DROP PRIMARY KEY;
# 删除其他索引
DROP INDEX index_name ON table_name;

7.2.4 索引的优缺点

索引的优点：索引可以加快数据的查询速度。

索引的缺点：创建维护索引会耗费时间和占用磁盘空间，并且随着数据量的增加所耗费的时间也会增加。

索引的一般使用原则：不要滥用索引，只针对经常查询的字段建立索引，较少或基本不参与查询的字段不要建立索引。数据量小的表最好不要使用索引（一般可以10万行为界限，但这并不是绝对标准）。区分度不大（即一个字段上相同值比较多）的字段最好不要建立索引，比如：性别。

7.3 视图

7.3.1 视图的概念

MySQL中的视图（View）是一个虚拟表，其内容由具体的查询语句定义。与存储在数据库中的实际表（物理表）不同，视图不包含数据，而是在被访问时运行查询来提取数据。你可以认为它是一个预先定义好的MySQL查询语句，本质是根据MySQL查询语句获取动态的数据集，并为其命名，用户使用时只需使用视图名称即可获取结果集，并可以将其当作表来使用。注意：数据库中只存放了视图的定义，而并没有存放视图中的数据，这些数据存放在原来的表中，也就是说原表数据改变会影响视图数据。

使用视图可以简化复杂的SQL操作，如果经常运行某个复杂的MySQL查询语句，那么可以创建一个视图来简化这个查询，每次只需访问视图，而不是重新编写整个查询，此外，使用视图还可以保护数据（隐藏数据）可以让用户访问数据库中的特定数据，而不是整个表。例如：可以创建一个仅显示某些字段（而不是所有字段）的视图，或者仅显示满足特定条件的视图。

7.3.2 视图的基本操作

具体示例代码如下：

# 创建视图
CREATE VIEW view_name AS (SELECT field_name FROM table_name);

# 删除视图
DROP VIEW view_name;

# 修改视图数据
ALTER VIEW view_name AS (SELECT field_name FROM table_name);

# 修改视图名
RENAME TABLE old_view_name TO new_view_name;

# 查询视图
SELECT field_name FROM view_name;

# 查看表视图和对应类型
SHOW FULL TABLES;

# 查看视图字段信息
DESC view_name;

标签

#mysql #sql #数据库