MySQL索引事务

1. 索引

1.1 概念

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

在关系型数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单（定义真特么拗口）。大白话意思是索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

一个索引是存储的表中一个特定列的值数据结构。索引是在表的列上创建。要记住的关键点是索引包含一个表中列的值，并且这些值存储在一个数据结构中。请牢记这一点：索引是一种数据结构。一个好的数据库表设计,从一开始就应该考虑添加索引,而不是到最后发现慢SQL了,影响业务了才来补救。其实我在工作经历当中，由于新建表或新加字段后,忘记添加索引也造成了多次生产事故,记忆犹新!!!

1.2 作用

• 数据库中的表、数据、索引之间的关系，类似于书架上的图书、书籍内容和书籍目录的关系。
• 索引所起的作用类似书籍目录，可用于快速定位、检索数据。
• 索引对于提高数据库的性能有很大的帮助。

1.3 使用场景

要考虑对数据库表的某列或某几列创建索引，需要考虑以下几点：

• 数据量较大，且经常对这些列进行条件查询。
• 该数据库表的插入操作，及对这些列的修改操作频率较低。
• 索引会占用额外的磁盘空间。

满足以上条件时，考虑对表中的这些字段创建索引，以提高查询效率。
反之，如果非条件查询列，或经常做插入、修改操作，或磁盘空间不足时，不考虑创建索引。

1.4 使用

创建主键约束（PRIMARY KEY）、唯一约束（UNIQUE）、外键约束（FOREIGN KEY）时，会自动创建对应列的索引。

• 查看索引
  

• 创建索引
  

• 删除索引
  

索引可以考虑的数据结构？

哈希？？

哈希表 查找效率是O（1），数据库的索引可以考虑使用哈希，但是也有问题。
例如：查找id <6 并且 >3 的学生信息

select * from student where id<6 and id>3;

哈希就不能处理，哈希他只能处理相等的情况，不能处理其他逻辑 > >= < <= between and…
哈希的查找过程：把key 带入哈希函数，计算得到下标，再根据下标取的相应的链表，再去遍历比较key是否相等。

二叉搜索树？？

二叉搜索树内部元素是有序的（中序遍历结果），二叉树（二叉搜索树）如果比较平衡，查找效率就是O（logN）
例如：查找id <6 并且 >3 的学生信息

select * from student where id<6 and id>3;

二叉搜索树可以解决这个问题，效率是O（n），相比于哈希表，二叉搜索树虽然可以解决范围查找的问题，但是处理效果不高。
1）二叉搜索树每个节点最多有两个叉，当数据量比较大时，树的高度就会较高，最终的处理效率也就越低；
2）二叉搜索树直接获取到的中序结果也不是很高O（N）。

真实的索引结构是一种N叉搜索树=>B+树

BTREE

传统的二叉树：

它并不适合直接拿来做索引存储，算法设计人员在二叉树的基础之上进行了变种，引入了BTREE的概念
BTREE：

如上图可知BTREE有以下特点：

• 不再是二叉搜索，而是N叉搜索，树的高度会降低，查询快
• 叶子节点，非叶子节点，都可以存储数据，且可以存储多个数据
• 通过中序遍历，可以访问树上所有节点

BTREE被作为实现索引的数据结构被创造出来，是因为它能够完美的利用“局部性原理”，其设计逻辑是这样的：

• 内存读写快，磁盘读写慢，而且慢很多
• 磁盘预读：磁盘读写并不是按需读取，而是按页预读，一次会读一页的数据，每次加载一些看起来是冗余的数据，如果未来要读取的数据就在这一页中，可以避免未来的磁盘读写，提高效率（通常，一页数据是4K）
• 局部性原理：软件设计要尽量遵循“数据读取集中”与“使用到一个数据，大概率会使用其附近的数据”，这样磁盘预读能充分提高磁盘IO效能

早先的MySQL就是使用的BTREE做为索引的数据结构，随着时间推移，B树发生了较多的变种，其中最常见的就是B+TREE变种，现在MySQL用的就是这种，示意如下：

B+TREE改进点及优势所在：

• 仍然是N叉树，层级小，非叶子节点不再存储数据，数据只存储在同一层的叶子节点上，B+树从根到每一个节点的路径长度一样，而B树不是这样
• 叶子之间，增加了链表(图中红色箭头指向)，获取所有节点，不再需要中序遍历，使用链表的next节点就可以快速访问到
• 范围查找方面，当定位min与max之后，中间叶子节点，就是结果集，不用中序回溯（范围查询在SQL中用得很多，这是B+树比B树最大的优势）
• 叶子节点存储实际记录行，记录行相对比较紧密的存储，适合大数据量磁盘存储；非叶子节点存储记录的PK，用于查询加速，适合内存存储
• 非叶子节点，不存储实际记录，而只存储记录的KEY的话，那么在相同内存的情况下，B+树能够存储更多索引

2. 事务

2.1 为什么使用事务

准备测试表：

drop table if exists accout;
create table accout(
id int primary key auto_increment,
name varchar(20) comment '账户名称',
money decimal(11,2) comment '金额'
);
insert into accout(name, money) values
('阿里巴巴', 5000),
('四十大盗', 1000);

比如说，四十大盗把从阿里巴巴的账户上偷盗了2000元

-- 阿里巴巴账户减少2000
update accout set money=money-2000 where name = '阿里巴巴';
-- 四十大盗账户增加2000
update accout set money=money+2000 where name = '四十大盗';

假如在执行以上第一句SQL时，出现网络错误，或是数据库挂掉了，阿里巴巴的账户会减少2000，但是四十大盗的账户上就没有了增加的金额。
解决方案：使用事务来控制，保证以上两句SQL要么全部执行成功，要么全部执行失败。

2.2 事务的概念

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

2.3 使用

（1）开启事务：start transaction;
（2）执行多条SQL语句
（3）回滚或提交：rollback/commit;
说明：rollback即是全部失败，commit即是全部成功。

2.4 事务的基本特性

1. 原子性：事务中的若干操作，要摸全部执行成功，要么就全部不执行（并不是不执行，而是如果执行过程中有错误，就把已经执行的步骤回滚回去）；
2. 一致性：执行事务前后，数据始终处于一种合法的状态；
3. 持久性：事务一旦执行完毕，此时对数据的修改是持久性生效的；
4. 隔离性