【MySQL】索引与事务

索引：

什么是索引？为什么要⽤索引
为了提高查询效率而使用的一种数据结构把数据组织起来；
例如生活中的字典：声母，韵母，声调。
    创建索引：
自动创建的主键约束，唯一约束，外键约束时，会自动创建对应列的索引；

create index 索引名 on 表名(字段名1,…);

一张表里至少有一个索引；主键索引又叫聚簇索引。

    索引分类：

• 按使用场景分：普通索引，主键索引，唯一索引，全文索引；
• 按数据组织方式分：聚簇索引，非聚簇索引；
• 还有一种数据查找过程中出现的现象叫索引覆盖。

主键索引，手动创建索引，默认索引，唯一索引。
索引一般创建在频繁查询的列上，而且这个列的值重复值很少。
    查看索引：

show index from 表名;

    删除索引：

drop index 索引名 on 表名;

    可以使用explain来进行查看SQL的执行

explain select * from 表名 where 条件;

    索引使用的数据结构是什么？

1.HASH

查询的时间复杂度O(1)
MySQL没有使用HASH作为索引的数据结构；
HASH不支持范围查找；

2.二叉搜索树
时间复杂度O(N),O(logN)
红黑树是O(logN)，红黑树可以动态调整树高，不会出现单边树情况；
但是他没有办法控制树的高度，树的高度决定了磁盘的访问次数，每向孩子节点访问一级，就发生磁盘IO，而在一个系统中，对性能影响最大的就是磁盘IO

3.N叉搜索B树 

可以规定每⼀个节点可以存多少个元素，当节点中达到了规定的元素个数时，才去调整，B树就可以解决树⾼的问题，那么N叉搜索树可以做为索引的数据结构吗，看上去好像是可以的，但是Mysql没有选这种数据结构，⽽是在这B树的基础上⼜做了优化。

4.B+树

 

 

通过观察可以发现

1.   ⾮叶⼦节点中的每个数据都存在于叶⼦节点中，并且都是对应所在叶⼦节点中的第⼀条数据
2.   Mysql中的B+树是⼀个循环双向链表，相邻节点是通过双向链表连接的，这样组织数据更有利⽤范围查找
3.   最重要的是，叶⼦节点中的数据是有序的
4.   N叉搜索树，有效的降低了树的⾼度，从⽽减少了磁盘IO次数
5.   对于B+树⽽⾔，在相同树⾼的情况下，查找任⼀元素的时间复杂度都⼀样，中间⽐较次数也差不多，也就是说性能均衡，只要控制树⾼，就可以达到性能可控的效果
6.   只有叶⼦⻚点存储了真实完整的数据，⾮叶⼦⻚点，只保存了主键(索引)的值和⼦节点的引⽤

索引补充：

•   一个索引包含多个字段就叫组合索引；create index 索引名 on 表名(字段名1,字段名2);
•   在查询时一定要在where条件中按索引顺序写过滤条件，使用索引的最左原则；
•   如果索引中包含要查询的所有列，那么直接从索引中返回结果，叫做索引覆盖；
•   当查询列表为*或索引不能满足查询结果，那么会使用id到主键索引中查询完整的结果，主键索引中包含当前数据行中所有列的值，叫做回表查询。

索引失效：

1.   最左原则：类似于字典的⽬录，这就是⼀个典型的复合索引
2.   判断不等：每个都要判断
3.   类型转换：与原类型不符
4.   like '%xxx'：第⼀个字符都不能确定，怎么去索引中⽐较呢？
5.   索引列运算 age + 1：改了原来的值
6.   is null 或 is not null ： 全表扫描了

事务：

什么是事务
把一组SQL语句打包运行，要么全部执行要么全部不执行。

开启事务：

start transaction;

回滚或提交：

rollback/commit;

    事务特性：

  1.原子性(atomicity)

事务中的SQL要么都执⾏要么都不执⾏，通过commit/rollback控制。

  2.⼀致性(Consistency)

官⽹上描述：数据库从⼀个⼀致性状态变换到另外⼀个⼀致性状态。
事务执⾏之前与执⾏之后，要保持的正确的结果，可以⽤转账说明

  3.隔离性(Isolation)

多个事务执⾏的过程中不能互相⼲扰

  4.持久性(Durability)

事务⼀旦提交就会写⼊磁盘永久保留，即使是数据库服务故障也不会影响数据的内容

    事务的隔离级别：
对并发访问的⼀种限制
如果隔离级别越低，那么可以⽀持同时访问的客户端数就越多，性能变⾼，数据安全性变低；
如果隔离级别越⾼，那么可以⽀持同时访问的客户端数就越少，性能变低，数据安全性变⾼；

隔离级别分类：

    不同隔离级别的现象：

  脏读：

事务B读到了另⼀个事务A还没有提交的数据，当事务A回滚后，事务中所有的修改都回滚了，那么事务B读到的数据就没有意义了，把这个称之为脏读；

要解决脏读问题，就要给⼀个写操作的事务加上⼀把锁，在写这个事务从开始时加锁，事务提交或回滚的时候释放锁，被加锁的事务不能与其他事务共存，写锁也叫排他锁。

 

  不可重复读：

当⼀个新的事务A在读⼀条记录时，另⼀个事务B对这条记录做出了修改，当事务A再次读这条记录时，就发现两次读到的结果不⼀致，那么这种情况就是不可重复读；

给读的事务也加上⼀把锁，但是这个锁是⼀把读锁(共享锁)，多个读锁可以共存，但是由于写锁是排他锁，所以读锁不能与写锁共存，也就是说，在加了读锁之后，不能进⾏写操作。

 

  幻读：

当事务A去查询所有记录时，事务B往这个表中添加了条新的记录，当然也可以删除，那么事务A再去查询所有记录时，发现与上⼀次查到的所有记录条数不⼀致，或多或少，这种现象就是幻读。

MySQL数据事务的默认隔离级别虽然是可重复读，但是最⼤限度的解决了幻读问题，但是并没有完全解决。