2021-11-07大数据学习日志——MySQL进阶——事务和索引

01_事务

学习目标

• 能够知道事务的四大特性

---

1.1 事务的介绍

事务就是用户定义的一系列执行SQL语句的操作, 这些操作要么完全地执行，要么完全地都不执行， 它是一个不可分割的工作执行单元。

事务的使用场景:

在日常生活中，有时我们需要进行银行转账，这个银行转账操作背后就是需要执行多个SQL语句，假如这些SQL执行到一半突然停电了，那么就会导致这个功能只完成了一半，这种情况是不允许出现，要想解决这个问题就需要通过事务来完成。

转账的基本流程：

• 第一步：开启一个事务
• 第二步：减少转出账户的余额：转出 200 元
• 第三步：增加转入账户的余额：增加 200 元
• 第四步：提交事务

1.2 事务的四大特性

• 原子性(Atomicity)
• 一致性(Consistency)
• 隔离性(Isolation)
• 持久性(Durability)

原子性:

一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性

一致性:

数据库总是从一个一致性的状态转换到另一个一致性的状态。（在前面的例子中，一致性确保了，即使在转账过程中系统崩溃，支票账户中也不会损失200美元，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。）

隔离性:

通常来说，一个事务所做的修改操作在提交事务之前，对于其他事务来说是不可见的。（在前面的例子中，当执行完第三步、第四步还未开始时，此时有另外的一个账户汇总程序开始运行，则其看到转出帐户的余额并没有减少 200 元。）

持久性:

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

说明:

事务能够保证数据的完整性和一致性，让用户的操作更加安全。

1.3 事务的使用

1.3.1 数据库的存储引擎

在使用事务之前，先要确保表的存储引擎是 InnoDB 类型, 只有 InnoDB 引擎才可以使用事务，MySQL数据库中表的存储引擎默认是 InnoDB 类型。

表的存储引擎说明:

表的存储引擎就是提供存储数据一种机制，不同表的存储引擎提供不同的存储机制。

说明:

• 不同的汽车引擎，提供的汽车动力也是不同的。

查看MySQL数据库支持的表的存储引擎:

-- 查看MySQL数据库支持的表的存储引擎
show engines;

说明:

• 常用的表的存储引擎是 InnoDB 和 MyISAM
• InnoDB 是支持事务的
• MyISAM 不支持事务，优势是访问速度快，对事务没有要求或者以select、insert为主的都可以使用该存储引擎来创建表

查看 students 表的创表语句:

-- 选择数据库
use winfunc;
-- 查看students表
show create table students;

执行结果：

+----------+----------------------------------------------------------------------------+
| Table    | Create Table                                                               |
+----------+----------------------------------------------------------------------------+
| students | CREATE TABLE `students` (                                                  |
|          |  `ID` int(11) NOT NULL,                                                    |
|          |  `Name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,   |
|          |  `Age` int(11) NOT NULL,                                                   |
|          |  `Gender` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, |
|          |  `GPA` decimal(11,2) NOT NULL,                                             |
|          |  PRIMARY KEY (`ID`) USING BTREE                                            |
|          | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC                    |
+----------+----------------------------------------------------------------------------+

说明:

• 通过创表语句可以得知，students表的存储引擎是InnoDB。
• 修改表的存储引擎使用: alter table 表名 engine = 引擎类型;
  • 比如：alter table students engine = 'MyISAM';

1.3.2 事务的基本使用

事务使用步骤：

1）开启事务：begin 或 start transaction

• 开启事务后执行修改命令，变更数据会保存到 MySQL 服务端的缓存文件中，而不维护到物理表中
• MySQL数据库默认采用自动提交(autocommit)模式，如果没有显示的开启一个事务，那么每条sql语句都会被当作一个事务执行提交的操作

2）事务中的 SQL 操作

3）结束事务：提交事务(commit)或回滚事务(rollback)

• 提交事务：将本地缓存文件中的数据提交到物理表中，完成数据的更新

• 回滚事务：放弃本地缓存文件中的缓存数据，表示回到开始事务前的状态

事务演练的SQL语句：

1）首先打开一个 cmd 终端1，连接 mysql 数据库，执行如下 SQL 操作

-- 使用 winfunc 数据库
use winfunc;

-- 开启事务
begin;
-- 查看 students 表的数据
select * from students;
-- 在事务中新增一个学生的数据
insert into students values(9, 'Tom', 20, 'Male', 3.80);
-- 查看 students 表的数据
-- 注意: 在当前事务中可以看到新增的数据，但是数据此时并没有更新到物理表中
-- 如果这里后续没有执行提交事务操作，那么数据是没有真正的更新到物理表中
select * from students;

2）再打开一个 cmd 终端2，连接 mysql 数据库，执行如下 SQL 操作

-- 使用 winfunc 数据库
use winfunc;

-- 查看 students 表的数据
-- 这时没有显示新增的数据，说明之前的事务没有提交，这就是事务的隔离性
-- 一个事务所做的修改操作在提交事务之前，对于其他事务来说是不可见的
select * from students;

3）再次在 cmd 终端1中进行操作，提交事务

-- 提交事务，事务中新增的学生数据会真正插入到物理表中
commit;

4）再次在 cmd 终端2中进行操作，查看学生的数据

-- 因为新增学生数据的事务已经提交了，数据已经真正插入到物理表中，此时会看到新增的学生数据
select * from students;

02_索引

学习目标

• 能够写出创建索引的SQL语句

---

2.1 索引的介绍

索引在MySQL中也叫做“键”，它是一个特殊的文件，它保存着数据表里所有记录的位置信息，更通俗的来说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

应用场景:

当数据库中数据量很大时，查找数据会变得很慢，我们就可以通过索引来提高数据库的查询效率。

2.2 索引的使用

查看表中已有索引：

• 格式：show index from 表名;
• 举例：show index from students;
  • 查看 students 表的索引，主键列会自动创建索引

索引的创建：

• 格式：alter table 表名 add index 索引名 (列名, ...)
  • 索引名不指定时，默认使用列名
• 举例：alter table students add index stu_name (Name);
  • 给 students 表的 Name 列创建索引

索引的删除：

• 格式： alter table 表名 drop index 索引名
  • 如果不知道索引名，可以查看创表sql语句：show create table students;
• 举例：alter table students drop index stu_name;
  • 删除 students 表中的 stu_name 索引

2.3 案例-验证索引查询性能

创建测试表并添加测试数据：

1）创建测试表 test_index

# 创建 python 数据库
create database python charset=utf8;
# 创建 test_index 数据表
use python;
create table test_index(title varchar(10));

2）向 test_index 表中添加 10 万条数据

from pymysql import connect


def main():
    # 创建数据库连接对象
    conn = connect(host='localhost', port=3306,
                   database='python', user='root', password='mysql')
    # 创建游标对象
    cursor = conn.cursor()
    # 循环向 test_index 表中添加 10 万条测试数据
    for i in range(100000):
        cursor.execute("insert into test_index values('py-%d')" % i)
    # 提交数据
    conn.commit()


if __name__ == "__main__":
    main()

验证索引性能操作：

1）未添加索引之前的查询

# 开启 sql 执行时间监测
set profiling=1;
# 查找第1万条数据'py-99999'
select * from test_index where title='py-99999';
# 查看 sql 执行的时间
show profiles;

2）添加索引之后的查询

# 给 title 字段创建索引
alter table test_index add index (title);
# 再次执行 sql 查询语句
select * from test_index where title='py-99999';
# 再次查看 sql 执行的时间
show profiles;

结果：给 title 字段添加索引之后，通过 title 字段筛选数据时，查询效率明显提升。

2.4 联合索引

联合索引又叫复合索引，即一个索引覆盖表中两个或者多个字段，一般用在多个字段一起查询的时候。

# 创建teacher表
create table teacher
(
    id int not null primary key auto_increment,
    name varchar(10),
    age int
);

# 创建联合索引
alter table teacher add index (name, age);

联合索引的好处:

• 减少磁盘空间开销，因为每创建一个索引，其实就是创建了一个索引文件，那么会增加磁盘空间的开销。

2.5 联合索引的最左原则

在使用联合索引的时候，我们要遵守一个最左原则，即index(name, age)支持 name 、name 和 age 组合查询，而不支持单独 age 查询，因为没有用到创建的联合索引。

最左原则示例:

# 下面的查询使用到了联合索引
# 示例1：这里使用了联合索引的name部分
select * from stu where name='张三'
# 示例2：这里完整的使用联合索引，包括 name 和 age 部分
select * from stu where name='李四' and age=10 

# 下面的查询没有使用到联合索引
# 示例3： 因为联合索引里面没有这个组合，只有【name】和【name age】这两种组合
select * from stu where age=10

说明:

• 在使用联合索引的查询数据时候一定要保证联合索引的最左侧字段出现在查询条件里面，否则联合索引失效

2.6 MySQL中索引的优点和缺点和使用原则

优点：

• 加快数据的查询速度

缺点：

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

使用原则：

• 通过优缺点对比，不是索引越多越好，而是需要自己合理的使用。
• 对经常更新的表就避免对其进行过多索引的创建，对经常用于查询的字段应该创建索引，
• 数据量小的表最好不要使用索引，因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果。
• 在一字段上相同值比较多不要建立索引，比如在学生表的"性别"字段上只有男，女两个不同值。相反的，在一个字段上不同值较多可以建立索引。