03.MySQL——索引和事务

索引

索引的概念

• 索引可以提高数据库的性能。
• 不用加内存，不用改程序，不用调sql，只要执行正确的 create index ，查询速度就可能提高成百上千倍。
• 但是查询速度的提高以插入、更新、删除的速度为代价。
• 索引的价值在于提高一个海量数据的检索速度。

---

索引分类

• 主键索引(primary key)
• 唯一索引(unique)
• 普通索引(index)
• 全文索引(fulltext)

---

索引的应用场景

当存在一个10000000条记录的数据库表时，查询员工编号为998877的员工select * from EMP where empno=998877; 耗时大约在5S，在实际公网服务器项目中，面对高并发访问就很可能死机。

当创建索引后，alter table EMP add index(empno); 查询效率就会大幅度提高，减少上述场景发生的可能性。

---

索引的理解

前言

1. mysqld本质是一个运行在OS上的进程。
2. 对MySQL数据进行操作本质上是对文件进行操作。
3. 任何的磁盘数据在进程中操作本质都必须在内存中进行。
4. MySQL在启动的时候，会预先申请一部分内存空间，通过文件系统把文件load到缓冲区中。
5. MySQL进行保存和IO的基本单位(Page)是16KB，OS管理内存的基本单位是4KB，磁盘设备的基本单位是512字节（扇区大小）。
6. 根据局部性原理，有很大概率能访问到预加载的数据，所以MySQL的IO交互是 Page相较于用多少，加载多少的方案而言可以提高效率。
7. MySQL内部会存在大量的page，管理好这些page，就需要先描述再组织。
8. 索引的基础就是采用B+树的数据结构来组织这些page。

---

单页Page

• MySQL 中的Page都是 16KB ，使用 prev 和 next 构成双向链表。链表的特点是增删快，查询修改慢，所以优化查询的效率是必须的。

• 如果有主键的存在， MySQL 会默认按照主键给我们的数据进行排序，数据是有序且彼此关联的，插入数据时排序的目的就是为了方便引入页目录从而优化查询的效率 。

• 目录是一种“空间换时间的做法” ，提高了查找的效率。

  ---

  多页Page

  

  • MySQL 中每一页的大小只有 16KB ，单个Page大小固定，但是随着数据量不断增大，16KB 不可能存下所有的数据，那么必定会有多个页来存储数据。

  • 给Page页也带上目录，使用一个目录项来指向某一页，而这个目录项存放的就是将要**指向的页中存放的最小数据的键值。**通过比较找到要访问的那个Page，进而通过指针找到下一个Page 。

  • 目录页的本质也是页，普通页中存的数据是用户数据，而目录页中存的数据是下级Page的最小键值和普通页的地址映射。

  • 查找的时候，自顶向下找，只需要加载部分目录页到内存，即可完成算法的整个查找过程，大大减少了IO次数。

  • 多个page构成B+树，全部的数据都在叶子节点，非叶子节点只保存键值和Page指针。B+树中的需要全部load到内存，mysql会按需load。

  • 索引的本质就是通过B+树的数据结构将表中的数据根据用户指定的关键字组织起来，提高查找的效率。

  ---

  为何选择B+，不选择B树

  • B树节点既有数据又有Page指针，而B+只有叶子节点有数据，其他目录页只有键值和Page指针，B+叶子节点全部相连，而B树不会。

  • 目录页不存储数据可以让一个目录页管理更多的page，使得树更矮，减少IO次数。并且叶子节点相连，更便于进行范围查找。

  ---

  聚簇索引和非聚簇索引

  聚簇索引：用户数据与索引数据在一起索引方案，叫做聚簇索引，例如：InnoDB 存储引擎。

  非聚簇索引： 索引Page和数据Page分离，也就是叶子节点没有数据，只有对应数据的地址，叫做非聚簇索引，例如： MyISAM 存储引擎。

---

索引操作

创建主键索引

-- 在创建表的时候，直接在字段名后指定 primary key
create table user1(id int primary key, name varchar(30));

-- 在创建表的最后，指定某列或某几列为主键索引
create table user2(id int, name varchar(30), primary key(id));

create table user3(id int, name varchar(30));
-- 创建表以后再添加主键
alter table user3 add primary key(id);

主键索引的特点：

• 一个表中最多有一个主键索引
• 主键索引的效率高
• 创建主键索引的列，它的值不能为null
• 主键索引的列基本上是int

---

创建唯一键索引

-- 在表定义时，在某列后直接指定unique唯一属性。
create table user4(id int primary key, name varchar(30) unique);

-- 创建表时，在表的后面指定某列或某几列为unique
create table user5(id int primary key, name varchar(30), unique(name));

-- 创建表以后再添加唯一键
create table user6(id int primary key, name varchar(30)）；
alter table user6 add unique(name);

唯一索引的特点：

• 一个表可以有多个唯一索引
• 查询效率高
• 唯一索引列数据不能重复
• 如果一个唯一索引上指定not null = 主键索引

---

创建普通索引

create table user8(id int primary key,
    name varchar(20),
    email varchar(30),
    index(name) --在表的定义最后，指定某列为索引
);

create table user9(id int primary key, name varchar(20), email varchar(30));
alter table user9 add index(name); --创建完表以后指定某列为普通索引

create table user10(id int primary key, name varchar(20), email varchar(30));
-- 创建一个索引名为 idx_name 的索引
create index idx_name on user10(name);

普通索引的特点：

• 一个表中可以有多个普通索引，普通索引在实际开发中用的比较多
• 如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引

---

索引创建原则

• 频繁作为查询条件的字段应该创建索引
• 唯一性太差的字段不适合单独创建索引
• 更新非常频繁的字段不适合作创建索引
• 不会出现在where子句中的字段不该创建索引

---

创建全文索引

当对文章字段或有大量文字的字段进行检索时，会使用到全文索引。MySQL全文索引的存储引擎必须是MyISAM，而且默认的全文索引支持英文，不支持中文。如果对中文进行全文检索，可以使用sphinx。

---

查询索引

• show keys from 表名;
• show index from 表名;
• desc 表名;

---

删除索引

• 删除主键索引：alter table 表名 drop primary key;
• 删除其他索引： alter table 表名 drop index 索引名;
• drop index 索引名 on 表名

---

事务

事务的概念

• 事务就是一组在逻辑上存在相关性的sql语句集合，作为一个整体，要么全部成功，要么全部失败。
• 事务不是数据库软件天然存在的，而是为了简化工作，数据库提供的机制，不需要我们去考虑各种各样的潜在错误和并发问题 。
• 事务主要用于处理操作量大，复杂度高的数据，事务规定不同的客户端看到的数据是不相同的。
• mysqld提供了事务机制，同时就会对多个事务采用先描述，再组织的方式进行管理。

---

事务的属性

• MySQL 数据库不止你一个事务在运行，同一时刻，甚至有大量的请求被包装成事务，在向 MySQL 服务器发起事务处理请求。而每条事务至少一条 SQL ，这样如果大家都访问同样的表数据，在不加保护的情况就绝对会出现问题。所以，一个完整的事务绝对不是简单的 sql 集合，还需要满足如下四个属性 ：
• 原子性：一个事务 中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样。
• 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
• 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交、读提交、可重复读和串行化。
• 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
• 前三种属性是手段，而一致性是目的。

---

事务的版本支持

• MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务， MyISAM 不支持。

---

事务提交方式

事务的提交方式常见的有两种：

• 自动提交
• 手动提交

查看事务提交方式

show variables like 'autocommit';

用 SET 来改变 MySQL 的自动提交模式

SET AUTOCOMMIT=0;  #SET AUTOCOMMIT=0 禁止自动提交 #SET AUTOCOMMIT=1 开启自动提交

---

事务的操作

start transaction/begin;——开始一个事务

rollback; ——回滚事务，默认回滚到最开始

savepoint XXX;——再任意位置创建一个回滚点

rollback to XXX; ——回滚到回滚点所在的位置

start transaction/begin;——开始一个事务

commit——提交事务。

• 只要输入begin/start transaction，事务必须通过commit提交才会持久化，与是否设置set autocommit无关。
• 事务可以手动回滚，当操作异常，MySQL也会自动回滚。
• 对于 InnoDB 每一条 SQL 语言都默认封装成事务，按照autocommit的值决定是否自动提交。
• 如果一个事务被提交了，则不可以回退

---

事务隔离级别

MySQL服务可能会同时被多个客户端进程(线程)访问，访问的方式以事务方式进行。在数据库中，为了保证事务执行过程中尽量不受干扰，就需要一个重要特征：隔离性。在保证数据安全的情况下，允许事务受不同程度的干扰，就有了一种重要特征：隔离级别。

• 读未提交【Read Uncommitted】： 所有的事务都可以看到其他事务没有提交的执行结果。但是相当于没有任何隔离性，也会有很多并发问题，如：脏读，幻读，不可重复读。
• 读提交【Read Committed】 ：一个事务只能看到其他的已经提交的事务。这种隔离级别会引起不可重复读，即一个事务执行时，如果多次 select 可能得到不同的结果。
• 可重复读【Repeatable Read】： 这是 MySQL 默认的隔离级别，它确保一个事务内部，在执行
  中，多次读取操作数据时会看到同样的数据，但是会有幻读问题(MySQL解决了幻读的问题)。
• 串行化【Serializable】: 这是事务的最高隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决了幻读的问题。它在每个读的数据行上面加上共享锁，但是可能会导致超时和锁竞争。

---

脏读

• 一个事务在执行中，读到另一个执行中事务的更新但是未commit的数据，这种现象叫做脏读。

不可重复读

• 同一个事务内，同样的读取，在不同的时间段，读取到了不同的值，这种现象叫做不可重复读。

幻读

• 一般的数据库在可重复读情况的时候，无法屏蔽其他事务insert的数据，导致多次查找时，会多查找出来新的insert记录，这种现象，叫做幻读。

总结：

• 其中隔离级别越严格，安全性越高，但数据库的并发性能也就越低，往往需要在两者之间找一个平衡点。
• 不可重复读的重点是修改和删除：同样的条件，你读取过的数据，再次读取出来发现值不一样。
• 幻读的重点在于新增：同样的条件, 第1次和第2次读出来的记录数不一样。
• 事务也有长短事务这样的概念，事务间互相影响，指的是事务在并行执行的时候，即都没有commit的时候，互相影响。