MySQL索引事务

一、索引

使用一定的数据结构，来保存索引字段对应的数据，以后根据索引字段来检索，就可以提高检索效率。

一定的数据结构-->需要一定的空间来保存

建立索引：类似于建立书籍目录或者手机电话簿

使用索引：查询条件中的字段能够命中索引

创建索引，更新/删除索引字段，插入数据，都会导致索引更新的耗时操作

• 数据库保存数据的基本单位：page
• 目的：硬盘读取文件到内存的io操作时耗时操作，读取数据最好能最少次读取到需要的结果集

MySQL中，有多种索引类型

（1）从索引存储结构划分：B Tree索引、Hash索引、FULLTEXT全文索引、R Tree索引

（2）从应用层次划分：普通索引、唯一索引、主键索引、复合索引

（3）从索引键值类型划分：主键索引、辅助索引（二级索引）

（4）从数据存储和索引键值逻辑关系划分：聚集索引（聚簇索引）、非聚集索引（非聚簇索引）

（5）从索引列数量划分：单列索引、复合索引

创建的某个索引可以是以上多种类型

• B树：所有节点都保存有索引列及数据；
• B+树：叶子节点保存有索引列和数据，非叶子节点只有索引字段；
• B树，搜索路径是从根节点搜索到叶子节点，比链表查询效率高；
• B+树，非叶子节点可以存储更多的数据，此时树就更矮，搜索路径更短，io次数更少

1.主键索引--->唯一索引

默认B+树，聚簇索引；B+树的叶子节点上存放主键字段（索引字段）及数据

（一张表只能有一个聚簇索引）

主键索引--->叶子节点  id_number+整行数据

• 优点：速度快
• 缺点：主键需要是整型，且字段不要太长；更新（代价大）效率低

2.非聚簇索引

非主键索引都是，可以是有很多种类型的索引，如B+树、Hash索引等；

如：使用B+树，存储结构：叶子节点存放索引字段值+主键的值

age字段建立B+树索引--->叶子节点  age的值+id_number的值

• 优点：更新代价相比于聚簇索引小（叶子节点是索引值和主键值，没有真实数据）
• 缺点：（1）也依赖有序数据（2）回表操作导致效率更低

搜索数据的方式：

（1）先通过索引字段找到叶子节点上的主键值

（2）再通过主键值，赵正条数据（回表操作）（存在回表，说明效率比主键索引慢）

3.覆盖索引：

如果一个索引包含（或者说是覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”

语法：select 覆盖索引字段 from 表 where 覆盖索引字段...

• 特殊的有些操作不会走索引：is null、is not null
• select name from table where name='guang19';
• 原因：检索的时候，是根据name索引字段来检索（name是在叶子节点，排序的），查询字段又没有其他字段，所以就不需要回表

4.复合索引：

使用多列来创建索引（涉及最左匹配原则：索引创建的字段顺序从左到右匹配）

ALTER TABLE table ADD INDEX index_name(num,name,age)

• select...from 表 where num=.. and name=... and age=...--->覆盖索引（效率高）、
• select...from 表 where name=... and num=...--->符合索引最左匹配原则
• select * from 表 where name=... and age=...--->不符合索引最左匹配原则

查询的条件字段顺序无所谓，关键是索引创建的字段顺序

基于B+树的索引：

（1）主键索引（聚簇索引）

（2）非聚簇索引

优化原则：

（1）索引字段尽量不要有null值

查询的时候条件是-->字段 is [not] null，>，< （<=，>=）会走全表扫描，不会走索引

like的操作：匹配a开头（a%）--->可以走索引；匹配包含a或以a结尾--->不可以走索引；

（2）频繁查询的字段建立索引：需要考虑最左匹配原则顺序是左边

（3）频繁更新的字段，慎用索引（索引更新代价大）

5.hash索引

hashmap存储数据的特性：键值对、无序、键唯一（不重复）

底层数据结构：数组+链表+红黑树

原理：存取元素，复杂的都是put流程（往火车放东西）

（1）算数组位置，往上边放（根据key的hashcode值，基于内部hash函数计算出数组索引）-->找车厢

（2）如果该位置没有元素：放这个位置上（车厢是空的）

如果这个位置有元素：---> 往链表上放：是否存在节点equals(key)  --->存在，替换（车厢位置有人，他走放我自己的）；不存在，考虑头插？尾插？-》可能转红黑树

（3）如果放进去了东西，可能需要扩容

• 缺点：不支持顺序和范围查询

二、事务

1.事务的特性：

（1）原子性：事务是最小的执行单位，不允许分割。即多条sql要么都执行要么都不执行；

（2）一致性：执行事务前后，数据保持一致性。比如：转账业务中，无论事务是否成功，转账者和收款人的总额应该不变；

（3）隔离性：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库时独立的；(不同隔离级别下，可能不一定满足某种场景的隔离性）

（4）持久性：一个事务被提交之后，它对数据库中数据的改变时持久的，即使数据库发生故障也不应该对其有任何影响；（事务提交后，数据会持久化到硬盘中）

start transaction;

-- 阿里巴巴账户减少2000

update accout set money =money-2000 where name='阿里巴巴';

-- 四十大盗账户增加2000

update accout set money =money+2000 where name='四十大盗';

开启事务后sql相关操作，都是在内存中执行；

rollback-->回滚：内存中执行的回滚不执行；

commit-->提交：内存中修改的操作执行（写入硬盘）；

事务中不同隔离级别，一个事务修改的数据，其他事务可不可见要根据隔离级别。

2.事务会出现的问题

（1）丢失更新

事务1修改数据的操作，在事务2修改以后，就覆盖掉了（相当于丢失）。

（2）脏读

第一个事务修改数据后还没有提交，第二个事务就读取，此时第一个事务回滚后，第二个事务读取的就是脏数据。

（3）不可重复读

一个事务两次读取数据，中间有另一个事务修改，第一个事务两次读取的数据就不同（不一致）

（4）幻读

一个事务两次读取，中间有另一个事务执行了插入操作，造成第一个事务看到不同的结果。

解决以上问题就使用到了隔离级别

3.隔离级别

从低到高的级别（级别越高越安全但是性能越差）：

（1）未提交读（read uncommitted）--->（一般都不会使用）

事务中的修改，即使没有提交，对其他事务也是可见的。

（2）提交读（read committed）--->（用的比较广泛的）

一个事务只能读取已经提交的事务所作的修改，即一个事务所作的修改在提交之前对其他事务不可见。

（3）可重复读（repeatable read）--->（MySQL默认的隔离级别）

保证在同一个事务中多次读取用一个数据的结果是一样的。

（4）可串行化（serializable）--->（公司几乎也不适用，因为性能太差）

强制事务串行执行，这样多个事务互不干扰，不会出现并发一致性问题。

串行执行--->一个事务中多行sql全部执行完（提交或者回滚），另一个事务才能执行。

	脏读	不可重复读	幻读
未提交读	×	×	×
提交读	√	×	×
可重复读	√	√	×
可串行化	√	√	√