MySQL索引和事务

一、索引

1、概念

索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引，并指定索引的类型，各类索引有各自的数据结构实现。

2、作用

索引可以提高查询效率，所以每个表中都有索引。

3、使用

使用索引可以避免顺序查询，可以直接将查询的访问定位出来；使用索引可以将数据库中的关键信息存储到内存中，而内存的操作速度远比磁盘快。

4、缺点

（1）维护成本高，因为索引使用的是B+ 树，每次新增、删除数据时需要整理树的结构，带来了新的开销；
（2）使用索引会增加存储成本（磁盘和内存空间成本的提升）；
（3）若索引过多，会对mysql的优化器造成一定的负担。

5、创建索引要考虑的因素

（1）数据量是否足够大，查询速度是否比较慢；
（2）创建索引的列是否是经常使用的查询条件。

6、索引的实现原理

第一阶段：二叉树：缺点：层级较高，查询和维护不方便；
第二阶段：B-树：缺点：将所有数据都存在叶子节点和非叶子节点中，当数据量特别大的时候，刚把索引加载起来就需要很长很长的时间。
第三阶段：B+树：优化：非叶子节点不再存储表的数据，叶子结点存储的不是数据本身，而是数据地址。

7、操作

1、查看索引
（创建主键约束、唯一约束、外键约束时，会自动创建对应的索引。）
show index from 表名;
例：查询学生表已有的索引
show index from student;

2、创建索引
（1）创建普通索引
语法：create index idx_字段名 on 表名(字段名);
例：创建班级表中classname字段的索引
create index id_classname on class(classname);

（2）创建唯一索引（在创建的时候要确保原来的数据具有唯一性）
语法：create unique index idx_unique_字段名 on 表名(字段名)；
（3）添加主键索引
语法：alter table 表名 add primary key(字段名);
3、删除索引
语法：drop index idx_字段名 on 表名;
例：删除班级表中classname字段的索引
drop index id_classname on class;

8、聚簇索引

1、生成规则：

（1）正常情况下，所有表中都会有一个主键索引，大部分时候聚簇索引就是主键，默认情况下为主键索引；
（2）非正常情况下（没主键索引），那么InnoDB会使用一个唯一且非空的索引作为主键索引；
（3）如果没有主键索引且没有唯一不为null的索引，那么InnoDB就会自动生成一个隐藏的主键索引。

2、聚簇索引和非聚簇索引（二级索引）的区别：

（1）数量上聚簇索引一个表中只能有一个，而二级索引可以有多个；
（2）聚簇索引：只要找到对应的id，就可以得到一行数据，宏观上叶子节点存储的数据；
非聚簇索引：非叶子节点存储的是二级索引的值，叶子节点存储的是主键id；拿到主键id，然后根据主键信息去聚簇索引里找到叶子节点所对应的行数据，这样才能完成二级索引的数据查询，把二级索引进行数据查询的过程叫做回表查询。
所以：聚簇索引的查询效率更快，而二级索引需进行“回表查询”，因此它的查询效率更低。

9、判断索引是否生效（借助mysql中的explain）

索引的校验：

1、索引失效场景1：联合索引不满足最左匹配原则（以最左边的为起点字段查询可以使用联合索引，否则将不能使用联合索引 ）

（联合索引：key idx_字段1_字段2_字段3）
例如：联合索引的顺序是A+B+C，则A+B、A+C、A+B+C、A+C+B 都可以，但是B+C就不行（B、C单独查询也不行）
explain select * from 表名 where A=’ ‘and B = ’ ’ and C=’ ';
补充：mysql的优化器B+A、C+A也可以，相当于把B和C 省略，满足最左匹配原则。
总结：只要联合索引由A，就会生效。

2、索引失效场景2：使用错误的模糊查询

like模糊查询的常用方法：
（1）前面确定：字段名 like “张%”;
（2）后面确定：字段名 like “%张”;
（3）中间确定：字段名 like “%张%”;
只有第一种才不会索引失效，能正确触发索引查询。

3、索引失效场景3：索引查询列使用运算操作（加减乘除）

eg：explain select * from 表名 where id+1=2;

4、索引失效场景4：查询列使用了mysql的任何函数

eg：explain select * from 表名 where ifnull（id,0）>5;

5、索引失效场景5：查询列进行隐式的类型转换（int 变 char）

eg：explain select * from 表名 where adress=111;

6、索引失效场景6：使用 is not null；

eg：explain select * from 表名 where adress is not null;

二、事务

InnoB才支持事务，MyISAM不支持事务

1、定义

事务就是将一组操作封装成一组执行单元，要么一块执行成功，要么执行失败，不会出现执行一半的情况。

2、事务的使用

（1）开启事务：mysql 8 之前使用start transaction ，mysql 8 之后使用begin
（2）提交事务：commit
（3）回滚事务：rollback

3、测试

（1）准备测试表
drop table if exists accout;
create table accout ( id int primary key auto_increment, name varchar(250) comment ‘账户名称’,money decimal(11,2) comment ‘金额’);
insert into accout (name ,money) values (‘张三’,5000),(‘李四’,1000) ;

（2）进行转账事务（张三给李四转账1000元）
第一步：
开启事务：start transaction;
第二步：
张三开始转：update accout set money = money-1000 where name = ‘张三’;
第三步：
回滚，保留张三的数据（原数据）；
第四步：
开启事务：start transaction;
第五步：
转账操作：update accout set money = money-1000 where name = ‘张三’;
update accout set money = money+1000 where name = ‘李四’;
第六步：
提交：commit；

4、事务的特性

（1）原子性（A）：事务中所有的操作要么全部成功，要么全部失败。
（2）一致性（C）：事务在执行前后数据必须保持一种合法状态，总是从一个一致状态到另一个一致状态。
（3）隔离性（I）：多个事务并发访问时，事物之间是相互隔离的，一个事务在执行时不影响另一个事物。
（4）持久性（D）：事务在执行完成后，它所有的修改都是永久的。

5、MySQL的事务隔离级别（四种）

1、读未提交（read uncommitted）

1、定义：在此隔离级别的事务中，可以读取到其他事务未提交的数据，而未提交的数据发生了回滚操作，那么此事务读到的数据就是幽灵数据，也叫做脏数据，这种情况就叫做脏读。
2、步骤：
（1）查询事务的隔离级别
注意：
@@global.tx_isolation：全局事务隔离级别；
@@tx_isolation：当前连接事务级别；
select @@global.tx_isolation,@@tx_isolation;
（2）设置事务隔离级别
set session transaction isolation level read uncommitted;

（3）剩余的步骤：

2、读已提交（read committed）

1、定义：可以解决賍读问题，但是它存在不可重复读问题（使用相同的SQL执行两次，得到不同的结果）。
2、步骤 ：

3、可重复读（repeatale read）——mysql默认的隔离级别

1、定义：可以解决“不可重复读问题”，但是还存在幻读的问题（针对于一个表的添加、删除操作）。
2、步骤：

注意 不可重复读和幻读的区别：
答：描述的侧重点不同，不可重复度描述的是修改操作，比如t1时间读取了一行数据，t2时间时读取到了这个数据，内容已经发生改变了；而幻读描述的使幻象行的问题，是指t1时间使用一个SQL得到了n条数据，而在t2时间使用相同的SQL却得到了n+1条数据，那么多出来的这一行就叫做幻象行。综上，不可重复度侧重点是修改操作，而幻读描述的侧重点添加或删除操作。

4、序列化（效率低）

1、定义：解决脏读、不可重复度、幻读问题；事务最高隔离级别，会强制事务排序，使之不会发生冲突。
2、步骤：