事务和索引

每日分享：

欲望就像是暴风雨，而自律就是指南针。

你不是不想自律，而是做不到。

目录

一、事务

1. 事务的介绍

2. 事务的四大特性

3. 事务的使用

二、索引

1. 索引的介绍

2. 索引的使用

3. 验证索引查询的性能

4. 联合索引

5. 联合索引的最左原则

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

一、事务 1. 事务的介绍

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

事务的使用场景：

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

2. 事务的四大特性

• 原子性
• 一致性
• 隔离性
• 持久性

原子性：

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

一致性：

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

隔离性：

通常来说，一个事务所做的修改操作在提交事务之前，对于其他事务来说是不可见的

持久性：

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

说明：

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

3. 事务的使用

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

表的存储引擎说明：

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

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

show engines;

说明：

• InnoDB：支持事务
• MyISAM：不支持事务，但访问速度快，对事务没有要求或者以select、insert为主的都可以使用该引擎创表

查看classes表的创表语句：

show create table classes;

可以看到它的存储引擎是InnoDB

修改表的存储引擎：

alter table 表名 engine = 引擎类型;

开启事务：

begin;

或者

start transaction;

说明：

• 开启事务后执行修改命令，变更数据会保存到MySQL服务端的缓存文件中，而不保存到物理表中
• MySQL数据库默认采用自动提交（autocommit）模式，如果没有显示的开启一个事务，那么每条SQL语句都会被当作一个事务执行提交的操作
• 设置autocommit=0，就是取消了自动提交事务模式，直到显示的执行commit或者rollback表示该事务结束（set autocommit = 0表示取消自动提交事务模式，需要手动执行commit完成事务的提交）

取消自动提交事务，插入数据后可以看到，新数据已经保存到缓存中，但我们新开一个终端，查看classes表发现没有新插入的数据：

而当我们输入commit提交数据的时候，在新终端就可以看到数据了：

提交事务:

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

commit;

回滚事务：

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

rollback;

二、索引 1. 索引的介绍

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

应用场景：

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

2. 索引的使用

查看表中已有的索引：

show index from 表名;

 查看创表语句：

说明：

• 主键会自动创建索引
• 外键约束也会自动创建索引

索引的创建：

创建索引的语法格式

alter table 表名 add index 索引名 (列名);

给表classes中的name字段添加索引

alter table classes add index (name);

说明：

• 索引名不指定，默认使用字段名作为索引名

索引的删除：

删除索引的语法格式

alter table 表名 drop index 索引名;

不知道索引名可以查看创表SQL语句（show create table classes;）

alter table classes drop index name;

3. 验证索引查询的性能

创建测试表test_index：

create table test_index(name varchar(20));

向表中插入一万条数据：

import pymysql

conn = pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='xingchen',
    database='hello',
    charset='utf8'
)
cursor = conn.cursor()
sql = "insert into test_index values(%s);"
try:
    for i in range(10000):
        cursor.execute(sql, ['test' + str(i)])
    conn.commit()
except Exception as e:
    conn.rollback()
finally:
    cursor.close()
    conn.close()

验证性能操作：

开启时间检测：

set profiling=1;

查找第6666条数据：

select * from test_index where name='test6666';

查看执行时间：

show profiles;

给name字段创建索引：

alter table test_index add index (name);

再次查找第6666条数据：

select * from test_index where name='test6666';

再次查看执行时间：

show profiles;

4. 联合索引

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

创建test1表

create table test1(

id int unsigned not null primary key auto_increment,

name varchar(10),

age int

);

创建联合索引

alter table test1 add index (name, age);

联合索引的好处：

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

删除联合索引（删除第一个即可）：

5. 联合索引的最左原则

在使用联合索引的时候，要遵循一个最左原则，即index(name, age)支持name、name和age组合查询，而不支持单独age查询，因为没有用到创建的联合索引（必须用到第一个（最左侧字段），否则不会提升性能）

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

优点：

1. 加快数据的查询速度

缺点：

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

使用原则：

1. 通过优缺点对比，不是索引越多越好，而是需要自己合理的使用
2. 对于经常更新的表就避免对其进行过多的索引创建，对于经常用于查询的字段应该创建索引
3. 数据量小的表最好不要使用索引，索引的优化效果不明显
4. 在一字段上相同值比较多的不要创建索引，比如：性别（男，女），相反在一个字段上不同值较多的可以建立索引