MySQL基础篇（四）

李子捌 2021-06-28

290

此文简单记录下MySQL数据的事务和索引相关知识点

事务

事务简介

数据库事务( transaction)是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作组成。
                                                                    ——《百度百科》

事务的ACID特性

1、原子性(Atomic)

一个事务被视为一个不可分割的最小工作单元，这个事务里的所有操作要么全部成功执行，要么全都不执行，不能只执行其中的一部分操作。实现事务的原子性，要支持回滚操作，在某个操作失败后，回滚到事务执行之前的状态。   

回滚是一个抽象概念，大多数数据库在实现事务时是在事务操作的数据快照上进行，并不修改实际的数据，发生错误时并不提交。

2 、一致性(Consistency)

一致性是指事务使得系统从一个一致性的状态转换到另一个一致性的状态。在实际的工程项目中事务可以有不同程度的一致性：
强一致性：读操作可以立即读到提交的更新数据。
弱一致性：提交的更新操作，不一定立即会被读操作读到，这种情况存在不一致窗口，指的是读操作要延迟一定时间才能读到最新值。
最终一致性：弱一致性的特例。事务更新一份数据，最终一致性保证在没有其他事务更新同样的值的话，最终所有的事务都会读到之前事务更新的最新值。如果没有错误发生，不一致窗口的大小依赖于通信延迟、系统负载等。

其他一致性变体还有：
单调一致性：如果一个进程已经读到一个值，那么后续不会读到更早的值。
会话一致性：保证客户端和服务器交互的会话过程中，读操作可以读到更新操作后的最新值。

3、隔离性(Isolation)

一个事务所做的修改在最终提交以前对其他事务是不可见的。

4、持久性(Durability)

一旦事务提交，则所做的修改会永久保存在数据库中。

四种隔离级别

1、Read Uncommitted(未提交读)

最低隔离级别。事务可以读取到未提交的数据，也即脏读(Dirty read)。存在脏读、不可重复读、幻读的问题。实际应用中一般很少使用这个隔离级别。

2、Read Committed(提交读)

大多数数据库系统默认的隔离级别(mysql不是)。只有在事务提交后，其更新结果才会被其他事务看见。该级别也叫 不可重复读(nonrepeatable read)，两次执行同样的查询语句可能得到不同的结果。可以解决脏读问题，存在不可重复读、幻读的问题。

不可重复读发生的一个场景：事务A需要多次读取同一个数据，当再次读取该数据的时候 另一个事务B修改了该数据，导致事务A读到的该数据 与 上一次读到的数据不一致。(侧重在数据被修改了update)

3、Repeated Read(可重复读)

mysql默认的事务隔离级别。在同一个事务中多次读取同样记录的结果总是一致的。可以解决脏读、不可重复读，存在幻读(Phantom read)问题。幻读指的是当某个事务在读取某个范围内的记录时，会产生幻行(Phantom Rows)。

幻读发生的一个场景：select检测某数据是否存在，当不存在时插入数据，但在执行insert 语句插入数据时发现此记录已经存在了，不能再插入，此时即发生了幻读。

另一个场景：事务A执行 select语句1 返回 5条记录，再次执行 select语句1 时返回了 6条 记录，也即与上次返回的结果集不一致。(侧重在插入了新数据insert)

InnoDB引擎通过使用 NK锁(Next-Key Locks)解决幻读问题。

4、Serialization(可串行化)

事务串行化执行，隔离级别最高，牺牲了系统的并发性。解决脏读、不可重复读、幻读，可保证事务安全。通过强制事务串行执行避免了幻读问题，它在读取的每一行数据上都加锁，会导致大量的超时和锁争用问题。实际应用中很少使用这个隔离级别。

注意：

不可重复读的重点于在修改 -- 同样的检索条件读取数据,再次读取出来时发现值不一致

幻读的重点在于新增或者删除 -- 同样的条件,第1次和第2次读出来的记录数量不一样

事务的基本语法

-- 由于MySQL默认是开启自动提交所以在使用事务的时候我们要先关闭自动提交 SET AUTOCOMMIT = 0;
-- 关闭和开启自动提交模式
SET AUTOCOMMIT = 0; -- 关闭
SET AUTOCOMMIT = 1; -- 开启（MySQL默认是开启自动提交）

-- 开始一个事务
START TRANSACTION;

-- 事务提交
COMMIT;

-- 事务回滚
ROLLBACK;

-- 事务结束后设置MySQL数据的自动提交
SET AUTOCOMMIT = 1;-- 开启（MySQL默认是开启自动提交）

-- 保存点
-- 使用保存点，可以在回滚操作时只回滚到该保存点即可，而非事务的开头。
SAVEPOINT `保存点名称` --设置一个事务的保存点
ROLLBACK TO SAVEPOINT `保存点名称` -- 回滚到保存点
RELEASE SAVEPOINT `保存点名称` -- 删除保存点


-- 测试题
/*
李子捌在线买一款价格为500元商品,网上银行转账.
李子捌的银行卡余额为2000,然后给商家李子柒支付500.
商家李子柒一开始的银行卡余额为10000

创建数据库shop和创建表account并插入2条数据
*/
-- 创建数据库shop
CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `shop`;
-- 创建表account
CREATE TABLE `account` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
`cash` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
-- 插入2条数据
INSERT INTO account (`name`,`cash`)
VALUES('李子捌',2000.00),('李子柒',10000.00)

-- 通过事务控制转账的实现
SET AUTOCOMMIT=0;  -- 关闭自动提交
START TRANSACTION; -- 开启事务
UPDATE account SET cash=cash-500 WHERE `name` = '李子捌'; 
UPDATE account SET cash=cash+500 WHERE `name` = '李子柒';
COMMIT; -- 事务提交
# ROLLBACK; -- 事务会滚（出现异常）
SET AUTOCOMMIT=1; -- 开启自动提交

索引

索引的概念

索引是一种特殊的文件，包含着对数据表中所有的记录的引用指针，数据库索引好比是一本书前面的目录，能加快数据库的查询速度，数据库索引就是为了提高表的搜索效率而对某些字段中的值建立的目录。

索引的作用

建立索引的目的是加快对表中记录的查找或排序，为表设置索引要付出代价；
一是增加了数据库的存储空间
二是在插入和修改数据时要花费更多的时间（因为索引也会随之改变）。

（1）设置合适的索引之后，数据库利用各种快速的定位技术，可以大大加快数据的查询速度，这也是创建索引的最主要的原因。
（2）当表很大的时候，或者查询涉及到多个表时，使用索引可以使查询速度快上成千倍。
（3）可以降低数据库的IO成本，并且索引还可以降低数据库的排序成本。
（4）通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
（5）在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间。

索引的分类

1.普通索引：列值可以取空值或重复值。创建使用关键字INDEX或KEY；

2.唯一索引：列值不能重复；即索引列值必须是唯一的，但可以是空值；创建使用关键字UNIQUE；

3.主键索引：主键索引是系统自动创建的主键索引，并且是唯一的。与唯一索引区别是；列值不能为空；

4.聚簇索引：就是数据存储的物理存储顺序，非聚簇索引就是索引顺序与数据的物理顺序无关。一个表只能有一个聚簇索引。目前只有InoDB和solidDB支持。

5.全文索引：只能创建在varchar或text的列上；建立全文索引能够在全文索引的列上进行查找。

（1）单列索引：就是一个索引只包含表中的一个列；比创建一个学号ID的索引；以name再创建一个姓名的单列索引。即每个索引包含一个列。

（2）组合索引（复合索引或多列索引）：就是表中的两个列或多个列来创建成一个索引；比如；以用户ID、用户名Name、用户年龄Age来创建的索引就是联合索引。

创建索引的原则

（1）表的主键、外建必须有索引，主键具有唯一性，索引值也是唯一性，查询时可以快速定位到数据行，外键一般关联的是另一个表的主键，所有在多表查询时也可以快速定位。
（2）数据量过300行的表应该有索引，数据量较大的时候，如果没有索引，需要把表遍历一遍，严重影响数据库的性能。
（3）经常与其他表进行表连接的表，在连接字段上应该建立索引。
（4）唯一性太差的字段不适合建立索引，如果索引字段的数据唯一性太差，是不适合创建索引。
（5）更新太频繁的字段不合适创建索引，在表中进行增加、删除、修改操作时，索引也有相应操作产生、字段更新得过于频繁，对于系统资源占用也会更多。
（6）经常出现在where（条件判断）字句中的字段，特别是大表的字段，应该建立索引。
（7）索引应该建立在选择性高的字段上，如果很少的字段拥有相同值，即有很多独特值，则选择性很高。
（8）索引应该建在小字段上，对于大的文本段甚至超长字段，不要建索引。

索引创建基本语法

1、使用 CREATE INDEX 语句

CREATE <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC])

2、使用 CREATE TABLE 语句

CONSTRAINT PRIMARY KEY [索引类型] (<列名>,…)

KEY | INDEX [<索引名>] [<索引类型>] (<列名>,…)

UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…)

FOREIGN KEY <索引名> <列名>

在使用 CREATE TABLE 语句定义列选项的时候，可以通过直接在某个列定义后面添加 PRIMARY KEY 的方式创建主键。而当主键是由多个列组成的多列索引时，则不能使用这种方法，只能用在语句的最后加上一个 PRIMARY KRY(<列名>，…) 子句的方式来实现。

3、使用 ALTER TABLE 语句

ADD INDEX [<索引名>] [<索引类型>] (<列名>,…)

ADD PRIMARY KEY [<索引类型>] (<列名>,…)

ADD UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…)

ADD FOREIGN KEY [<索引名>] (<列名>,…)

索引创建示例

1、创建普通索引

-- 创建一个表 tb_stu_info，在该表的 height 字段创建普通索引。
CREATE TABLE tb_stu_info(
 `id` int(11) NOT NULL,
  `name` char(45) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `height` int(11) DEFAULT NULL,
 INDEX(`height`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

CREATE TABLE `tb_stu_info` (
  `id` int(11) NOT NULL,
  `name` char(45) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `height` int(11) DEFAULT NULL,
  KEY `height` (`height`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

2、创建唯一索引

-- 创建一个表 tb_stu_info2，在该表的 height 字段上使用 UNIQUE 关键字创建唯一索引

CREATE TABLE `tb_stu_info2` (
  `id` int(11) NOT NULL,
  `name` char(45) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `height` int(11) DEFAULT NULL,
  UNIQUE KEY `height` (`height`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

测试索引

1、创建表user

CREATE TABLE `user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT '' COMMENT '用户昵称',
`email` varchar(50) NOT NULL COMMENT '用户邮箱',
`phone` varchar(20) DEFAULT '' COMMENT '手机号',
`gender` tinyint(4) unsigned DEFAULT '0' COMMENT '性别（0:男；1：女）',
`password` varchar(100) NOT NULL COMMENT '密码',
`age` tinyint(4) DEFAULT '0' COMMENT '年龄',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='user用户表'

2、批量插入数据100W

-- This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its de 错误解决办法
/*
这是我们开启了bin-log, 我们就必须指定我们的函数是否是
1 DETERMINISTIC 不确定的
2 NO SQL 没有SQl语句，当然也不会修改数据
3 READS SQL DATA 只是读取数据，当然也不会修改数据
4 MODIFIES SQL DATA 要修改数据
5 CONTAINS SQL 包含了SQL语句

其中在function里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数。
*/
set global log_bin_trust_function_creators=TRUE;


DROP FUNCTION IF EXISTS mock_data;
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i < num DO
  INSERT INTO `user`(`name`, `email`, `phone`, `gender`, `password`, `age`)
   VALUES(CONCAT('用户', i), '1057301174@qq.com', CONCAT('22', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
  SET i = i + 1;
END WHILE;
RETURN i;
END;
SELECT mock_data();

3、无索引测试

-- 耗时0.570S
SELECT * FROM user WHERE name = '用户8888';

-- 耗时0.621S
SELECT * FROM user WHERE name = '用户8888';

-- 耗时0.625S
SELECT * FROM user WHERE name = '用户8888';

4、测试普通索引

-- 创建普通索引
CREATE INDEX idx_user_name ON user(name);

-- 耗时0.022S
SELECT * FROM user WHERE name = '用户8888';

-- 耗时0.022S
SELECT * FROM user WHERE name = '用户8888';

-- 耗时0.022S
SELECT * FROM user WHERE name = '用户8888';

5、测试唯一索引

-- 创建唯一索引
ALTER TABLE user ADD UNIQUE KEY uidx_user_name (`name`);

-- 耗时0.022S
SELECT * FROM user WHERE name = '用户8888';

-- 耗时0.023S
SELECT * FROM user WHERE name = '用户8888';

-- 耗时0.022S
SELECT * FROM user WHERE name = '用户8888';

学习资料：

https://search.bilibili.com/allkeyword=%E7%8B%82%E7%A5%9E%E8%AF%B4JAVA

https://www.runoob.com/mysql

附：推荐MySQL相关入门课程，哔哩哔哩《狂神说JAVA》

mysql

文章转载自李子捌，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL基础篇 （四）​