MySQL数据库的索引与事务

目录

1.索引

1）索引的数据结构

2）索引的使用

2.事务

1）脏读 

2）不可重复读 

3）幻读 

 3.MySQL的隔离级别

---

1.索引

索引：是一种特殊的文件，包含着对数据表里所有记录的引用指针，可以对表中的一列或多列创建索引，并指定索引的类型，各类索引有各自的数据结构实现。简单来说，数据库索引好比是一本书的目录，能够加快数据库的查询速度。

作用：索引就是为了避免数据库进行顺序查找，提高查找的效率。

索引付出的代价：空间的代价，需要浪费一定的空间；

时间的代价，只针对增删改来说

1）索引的数据结构

不用哈希表的原因：因为如果使用哈希表，只能处理相等的情况，无法处理“ < , > , >= ”等情况，因为哈希表存储是没有顺序的，不支持范围查找。

不用二叉搜索树的原因：因为，如果使用二叉搜索树，二叉树每个节点最多有2个叉，当数据量大时，树的高度就会较高，最终操作的效率也会非常低，而且二叉搜索树获取到中序遍历的效率也不是很高效O(N),这样效率很低，就跟普通查找效率差不多了。

真正的索引是用B+树：

1）仍然是N叉树，层级小，非叶子节点不再存储数据，数据只存储在同一层的叶子结点上，B+树从根到每个节点的路径长度一样，而B树不是这样（表示查询任何一条记录速度是比较平均的，不会出现效率差异大的情况）

2）叶子结点，增加了链表，获取所有节点，不再需要中序遍历，使用链表的next节点就可以快速访问到

3）范围查找方面，当定位min与max之后，中间叶子结点，就是结果集，不用中序回溯（范围查询在SQL中用的最多，这是B+树比B树最大的优势）

4）叶子结点存储实际记录，记录行相对比较紧密的存储，适合大数据量磁盘存储；非叶子节点存储记录的PK，用于查询加速，适合内存存储

5）非叶子节点，不存储实际记录，而只存储记录的key的话，那么在相同内存的情况下，B+树能够存储更多索引

索引的使用场景：

1）数据量较大，且经常对这些列进行条件查询

2）该数据库的插入操作，及对这些列的修改操作频率较低

3）索引会占用额外的磁盘空间

2）索引的使用

创建主键约束（primary  key），唯一约束（unique），外键约束（forei key）时，会自动创建对应的索引

查看索引：show index from 表名；

 创建索引：create index 索引名  on 表名（字段名）；

 删除索引：drop index 索引名 on 表名；

 MySQL中的索引类型：

普通索引：允许被索引的数据列包含重复的值

唯一索引：可以保证数据记录的唯一性

主键索引：是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯一标识一条记录，使用关键字primary key来创建

联合索引：索引可以覆盖多个数据列，如像index（columnA，columnB）索引

全文索引：通过建立倒叙索引，可以极大的提升索引检索效率，解决判断字段是否包含的问题，是目前搜索引擎使用的一种关键的技术，可以通过alter table table_name  add fulltext（column）；来创建全文索引

2.事务

概念：事务是指逻辑上的一组操作，组成这组操作的各个单元，要么全部成功，要么全部失败，在不同的环境中，都可以有事务，对应的数据库中，就是数据库事务。

事务诞生的目的：就是为了把若干个独立的操作给打包成一个整体 

 事务的基本特性（ACID）：

原子性：事务中的若干操作，要么全部执行成功，要么就都不执行（此处的不执行并不是真的不执行，而是一旦中间的某个步骤执行出错，就把前面已经执行完毕的步骤回滚回去(rollback)）

一致性：在事务开始之前和事务结束之后，数据库的完整性没有被破坏，执行事务前后，数据始终处于一种合法的状态（例如装转操作，减账户余额的时候，不能把账户减成负数）

隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致

持久性：事务一旦执行完毕，此时对于数据的修改就是持久生效的

 事务的使用：

1）开启事务：start transaction；

2）执行多条SQL语句；

3）回滚或提交：rollback/commit；（rollback是全部失败，commit是全部成功）

1）脏读 

 脏读就是一个事务A在执行修改数据的操作，此时事务A还没有完成修改操作，这时候有另外一个事务B读取了此时的内容，此时，事务B的读操作就是脏读

解决办法：给写操作加锁，当一个事务在执行写操作的时候，其他事务没办法执行读操作，当事务执行完写操作，释放了锁，其他事务才能读

2）不可重复读 

 不可重读读就是，事务A在执行过程中，多次读取的数据不相同，就叫不可重复读

解决办法：给读操作也加锁，当事务A在读的时候，事务B没办法修改操作，只有等事务A执行完释放锁之后，事务B才能进行操作

3）幻读 

 幻读就是，事务A在执行过程中，多次读取的结果集不一样（数据总量不一样）

虽然读操作和写操作都加锁了，但是可以新增

解决办法：彻底让事务之间彻底的串行化，就是当事务A在执行的过程中，事务B什么也不能干，只能挂机等待

 3.MySQL的隔离级别

 1）read uncommitted ：允许读取未提交的数据

（隔离性最低，并发性最高，存在脏读问题）

2）read committed ：只允许读取已提交的数据，相当于对写操作加锁

（隔离性提高了，并发性降低了，解决了脏读，但存在不可重复读）

3）repeatable read ：给读也加锁了，这是MySQL默认的隔离级别

（隔离性又提高了，并发性更低了，解决了不可重复读，但存在幻读）

4）serializable ：严格的串行化执行

（隔离性最高，并发性最低，解决了幻读问题）