MySQL之索引与事务

索引

索引的分类

从定义的分类来看，索引分为：

主键索引：必须唯一且不能有null值

唯一索引：必须唯一，但是允许有null值

普通索引：即对一个列添加索引，也称单列索引

联合索引：对多个列合并添加索引，也称多列索引

索引的底层数据结构

索引是一种数据结构，可以用来快速查找和排序数据，MySQL的innerDB引擎，用的是B+数据结构来存储索引，特点是非叶子节点值存储索引值和指针，叶子节点用双向链表的形式存储数据。使用黑马于洪波老师的图：

B+树是从搜索二叉树->红黑树->B树，进化来的。它的好处有：

节点采用多阶的方式，相对二叉树、红黑树，树更矮，查询效率更高

叶子节点才存储数据且采用双向链表的方式，相对B树，范围查询的效率更高

聚簇索引和非聚簇索引

从MySQL将索引存储的物理形式来看，可以分为聚簇索引和非聚簇索引（也叫二级索引），这2个都是B+树，区别如下：

聚簇索引：叶子节点保存的是整行数据。只能由一个聚簇索引。通常是主键（没有主键则使用唯一索引或MySQL隐藏的rowid列）

非聚簇索引：叶子节点保存的是主键值。除了聚簇索引外，其他的索引都是以非聚簇索引的形式保存的

为什么要了解聚簇索引和非聚簇索引呢？因为这里涉及到MySQL查询非常重要的一个概念：回表

回表查询

假设现在有一张表tableA，表里面有3个字段列，id主键，name建立了普通索引，age年龄就是一个普通列

-- 创建表A，id为主键，默认用的是innoDB引擎
create table `tableA`(id int primary key,name varchar(200),age int
);
-- 给name添加普通索引
create index name_index on tableA(name);

此时MySQL底层会存储2个索引结构，一个是以ID为主键的聚簇索引，一个是以name为普通索引的非聚簇索引。

先看会触发回表查询的情况：

select id,name,age from tableA where name = '张三';

查询条件name = '张三'，那么走的是name的非聚簇索引；select要返回的字段有3个：id,name,age；当非聚簇索引定位到'张三'这个数据的时候，只能获取到id,name这2个字段数据（因为非聚簇索引只存储了主键值）。age这个列是拿不到的，必须根据拿到的id值，再去聚簇索引中定位到这条数据，然后获取age字段值返回；这个根据id重新去聚簇索引中查找数据，就叫回表查询。在大数据量的情况下，回表是比较影响性能的。

在看一个不触发回表的SQL。

-- 不会触发回表，因为通过name索引，可以直接到要返回的id,name字段
select id,name from tableA where name = '张三';

上面的sql是不会触发回表的，查询条件name = '张三'，走的是name的非聚簇索引，因为select id,name；表示查询要返回的列为id和name，通过非聚簇索引定位到'张三'这条数据时，非聚簇索引叶子节点保存了主键值id，那么要查询的列都齐了，可以直接返回，就不需要回表。

索引失效的情况

以下情况下索引会失效：

-- 1. 对索引列name做运算导致索引失效
select * from table where upper(name) = 'ABC';-- 2. 类型转换，这里name是varchar类型，但是却跟123数值类型做比较，虽然MySQL执行这种，但是会导致索引失效
select * from table where name = 123;
-- 正确的方法，'123'
select * from table where name = '123';-- 3. 使用模糊查询，且开头为%
select * from table where name = '%123';
-- 正确的方法，开头不能为%
select * from table where name = '123%';-- 4. 聚合索引，没有满足最左前缀法制，也就是最左表的索引列要出现
-- 假设有一个聚合索引name,age
-- 失效的写法 age放在了name的前面，不满足最左前缀法制
select * from table where age = 18 and name = '123';
-- 正确的写法，按聚合索引顺序编写
select * from table where name = '123' and age = 18;

事务

事务的特性

事务的特性可以总结为ACID原则：

原子性（Atomicity）：事务里的操作，要么全部执行成功，要么全部执行失败

一致性（Consistency）：事务操作前后，数据的状态保存一致

隔离性（Isolation）：事务需在不受外部并发的环境下运行

持久性（Durability）：事务一旦提交，那么对数据的修改就是永久性的

undo log日志，保证原子性和一致性，redo log日志保证持久性，MySQL事务隔离级别保证隔离性。

undo log日志

逻辑日志，当insert新增一条数据时，会对应记录一条delete的恢复语句，update修改一条数据时，会记录一条update相反的恢复语句。

一旦发生错误需要事务回滚，那么执行undo log日志里的内容恢复数据即可。

redo log日志

物理日志，对数据库的修改，会先持久化到redo log日志里，也就是Write-Ahead Logging日志优先的思想，当事务提交后发生服务器宕机时，恢复redo log日志里的内容即可。

事务的隔离级别

在高并发的时候，怎么保证事务的隔离性呢。先看下并发事务会带来的一些问题：

脏读：一个事务读到了另一个事务还没有提交的数据

不可重复读：一个事务两次读取同一条数据，得到的结果不一样

幻读：一个事务读取不到此条数据，但是在insert这条数据时，却提示此条数据已存在，像是出现了幻影

MySQL中有4种隔离级别，对应解决上面3种问题：

隔离级别	脏读	不可重复读	幻读
读未提交 READ UNCOMMITTED 简称RU	不解决	不解决	不解决
读已提交 READ COMMITTED 简称RC	解决	不解决	不解决
可重复读 REPEATABLE READ 简称RR	解决	解决
串行化 SERIALIZABLE	解决	解决	解决