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 | 解决 | 解决 | 解决 |
大多数数据库如Oracle、sqlServer默认级别都是RC读已提交。MySQL默认的隔离级别是RR可重复读。
原文地址：https://blog.csdn.net/LycLhsYYY/article/details/143972429
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！