MySQL索引_InnoDB_事务（锁）

一、什么是索引？

The best way to improve the performance of SELECT operations is to create indexes on one or more of the columns that are tested in the query. The index entries act like pointers to the table rows, allowing the query to quickly determine which rows match a condition in the WHERE clause, and retrieve the other column values for those rows. All MySQL data types can be indexed.Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows.
索引能大幅提升Select操作的查询速度，因为索引就像书的目录一样，能快速定位到符合Where子句筛选条件的行。如果没有索引的话，MySQL将不得不遍历所有的行

Although it can be tempting to create an indexes for every possible column used in a query, unnecessary indexes waste space and waste time for MySQL to determine which indexes to use. Indexes also add to the cost of inserts, updates, and deletes because each index must be updated. You must find the right balance to achieve fast queries using the optimal set of indexes.
尽管能给所有的字段添加索引，但是不必要的索引会浪费空间和时间，因为Insert、Update、Delete的每次操作都会更新索引。

 

二、什么是InnoDB索引?

1 InnoDB是MySQL的默认存储引擎，创建表时默认会创建
In MySQL 5.7, InnoDB is the default MySQL storage engine. Unless you have configured a different default storage engine, issuing a CREATE TABLE statement without an ENGINE= clause creates an InnoDB table.

2 聚簇索引和非聚簇索引

聚簇索引：数据与索引存储在一起，找到索引也就找到了数据
非聚簇索引：数据与索引分开存储，索引结构的叶子节点指向数据的对应行

在InnoDB中，聚簇索引具有唯一性。因为其是根据主键创建，所以也称主键索引。
如果没有显式指定主键，MySQL会自动选择一个不包含有NULL值的唯一索引（所谓唯一索引就是索引列的值必须唯一）作为主键索引。如果不存在这样的索引，MySQL 会自动创建长整形的自增字段作为主键索引（为什么要用自增主键呢，稍后解释）

聚簇索引之上创建的索引称之为辅助索引，辅助索引均是非聚簇索引

如下所示，pid是主键，绿色代表聚簇索引的查询过程（直接在主键索引中检索可获得整行数据），红色代表非聚簇索引的查询过程（首先在辅助索引中检索到叶子节点对应的主键，在使用主键在主键索引中检索以获得整行数据）

pid	name	birthday
5	zhangsan	2016-10-02
8	lisi	2015-10-04
11	wangwu	2016-09-02
13	zhaoliu	2016-10-07

另外需要注意的是，如果在非聚簇索引树上找到了想要的值，则不需要继续查询聚簇索引，以上图为例
当执行 select name from table where name = ? 时，因为name有索引，所以查询速度比执行 select * from table where name = ? 好几多

3 InnoDB 数据页结构

页（Page）是 Innodb 存储引擎用于管理数据的最小磁盘单位

4  InnoDB 使用 B+Tree 作为索引结构

InnoDB将所有数据记录存储在一棵B+Tree（主键索引）的叶子节点的链表中，且链表中的主键是有序的

非叶子节点存放键值和对应数据页的指针

【解释为啥会默认创建自增主键】
如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。如果使用非自增主键，由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据，这会增加了很多I/O开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构。

A B-tree index can be used for column comparisons in expressions that use the =, >, >=, <, <=, or BETWEEN operators. The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard(通配符，也就是%) character. 

5 联合索引

联合索引，又称复合索引，即一个覆盖表中两列或者以上的索引

创建测试表

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `a` varchar(32) NOT NULL,
  `b` varchar(32) NOT NULL,
  `c` varchar(64) NOT NULL,
  `d` varchar(128) NOT NULL,
  `e` varchar(256) NOT NULL,
  `create_time` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),  
  `update_time` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), 
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `test` (`a`, `b`, `c`, `d`, `e`) VALUES('a', 'b', 'c', 'd', 'e');
INSERT INTO `test` (`a`, `b`, `c`, `d`, `e`) VALUES('a', 'b', 'c', 'd', 'e');
INSERT INTO `test` (`a`, `b`, `c`, `d`, `e`) VALUES('a', 'b', 'c', 'd', 'e');
INSERT INTO `test` (`a`, `b`, `c`, `d`, `e`) VALUES('a', 'b', 'c', 'd', 'e');
INSERT INTO `test` (`a`, `b`, `c`, `d`, `e`) VALUES('a', 'b', 'c', 'd', 'e');
INSERT INTO `test` (`a`, `b`, `c`, `d`, `e`) VALUES('a', 'b', 'c', 'd', 'e');
INSERT INTO `test` (`a`, `b`, `c`, `d`, `e`) VALUES('a', 'b', 'c', 'd', 'e');
INSERT INTO `test` (`a`, `b`, `c`, `d`, `e`) VALUES('a', 'b', 'c', 'd', 'e');
INSERT INTO `test` (`a`, `b`, `c`, `d`, `e`) VALUES('a', 'b', 'c', 'd', 'e');
INSERT INTO `test` (`a`, `b`, `c`, `d`, `e`) VALUES('a', 'b', 'c', 'd', 'e');

创建联合索引

create index idx_a_b_c on test(a, b, c);

测试是否使用了索引

-- 11 ref 只匹配少数行
explain select * from test where a = "1";
-- 12 range 常数值的范围
explain select * from test where a like "1%";
-- 13 ref 只匹配少数行
explain select * from test where a like "b%";
-- 14 all 扫全表
explain select * from test where a like "%1";
-- 2 all 扫全表
explain select * from test where b = "1";
-- 3 all 扫全表
explain select * from test where c = "1";
-- 41 ref 只匹配少数行
explain select * from test where a = "1" and b = "1" and c = "1";
-- 42 ref 只匹配少数行
explain select * from test where a = "1" and c = "1" and b = "1";
-- 51 ref 只匹配少数行
explain select * from test where a = "1" and b = "1";
-- 52 ref 只匹配少数行
explain select * from test where b = "1" and a = "1";
-- 61 ref 只匹配少数行
explain select * from test where a = "1" and c = "1";
-- 62 ref 只匹配少数行
explain select * from test where c = "1" and a = "1";
-- 8 all 扫全表
explain select * from test where b = "1" and c = "1";

归纳起来就是

最左匹配原则：以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配

注1：41和42、51和52、61和62调换了位置，但不影响查询对索引的使用，因为Mysql中有查询优化器，会自动优化查询顺序 

注2：如果查询子句为 where a = "1" and c like "%1" and b = "1"，则该查询只会使用索引中的前两列，因为LIKE是范围查询

注3：如果某列是字符型，它的比较规则是先比较字符串的第一个字符，第一个字符小的哪个字符串就比较小，如果两个字符串第一个字符相通，那就再比较第二个字符，第二个字符比较小的那个字符串就比较小，依次类推

注4：类似于 like "a%" 会使用相应的索引，因为符合最左匹配原则； like "%a" 则不会使用索引，因为不符合最左匹配原则

 

三 事务（Transaction）

1 为什么要有事务？

为了解决同一个表在多人同时使用时，保持数据的一致性，提出了事务的概念

2 什么是事务？

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行（commit），要么全部不执行（回滚rollback到事务的最初状态）。
事务用来管理 insert,update,delete 语句

3 事务有哪些特性？

事务是必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

• 原子性：事务中的操作要么全部执行，要么全都不执行，不会结束在中间某个环节。为保证原子性，事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样
• 一致性：事务开始之前，数据库处于正确的状态；事务结束后，数据库必须也处于正确的状态；事务开始和结束之间的中间状态不会被其他事务看到。什么叫正确的状态？就是当前的状态满足预定的约束就叫做正确的状态。预定的约束是由用户负责的。例如，在银行转账中，用户可以定义转账前后两个账户金额之和保持不变。更多可参考：https://www.zhihu.com/question/31346392/answer/362597203
• 隔离性：系统必须保证事务不受其他并发执行事务的影响，即当多个事务同时运行时，各事务之间相互隔离，不可互相干扰
• 持久性：事务处理结束后，对数据的修改就是永久的。持久性通过恢复机制实现，发生故障时，可以通过日志等手段恢复数据库信息

3 事务隔离级别

事务隔离分为以下级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	可能	可能	可能
不可重复读（read-committed）	不可能	可能	可能
可重复读（repeatable-read），InnoDB 默认的级别	不可能	不可能	可能/不可能
串行化（serializable）	不可能	不可能	不可能

• 脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
• 不可重复读：事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。
• 幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样

注1：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

注2：InnoDB 通过间隙锁，在可重复读（repeatable-read）级别防止了幻读

注3：从上到下隔离性增强，并发能力降

-- 查看系统隔离级别：
select @@global.tx_isolation;
-- 查看当前会话隔离级别
select @@tx_isolation;
-- 设置当前会话隔离级别
SET session TRANSACTION ISOLATION LEVEL serializable;
-- 设置全局系统隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

4 数据库使用锁的方式保证隔离性

4.1 行锁的两种状态

共享锁（Share Locks，记为S锁，也称读锁）：其他事务可以读，但不能写。
排它锁（eXclusive Locks，记为X锁，也称写锁） ：其他事务不能读取，也不能写。

-- 当前seesion加入共享锁（next-key类型）
select …… lock in share mode

-- 当前seesion加入排它锁（next-key类型）
select …… for update

4.2 行锁的三种算法

Record锁：只锁住特定行的数据，并发能力强，MySQL一般都是用行锁来处理并发事务
GAP锁（间隙锁）：MySQL使用索引对行锁两边的区间进行加锁，避免其他事务在这两个区间insert的一种锁

Next-Key锁：行锁和GAP锁的合并（MySQL使用它来避免幻读）

4.3 UPDATE、INSERT、DELETE InnoDB会自动给涉及的数据集加排他锁，一般的 SELECT 一般是不加任何锁的

4.4 表锁的两种状态

InnoDB允许行级锁和表级锁的共存
意向共享锁(IS Lock)：当一个事务要给一条数据加S锁的时候，会先对数据所在的表先加上IS锁，成功后才能加上S锁
意向排他锁(IX Lock)：当一个事务要给一条数据加X锁的时候，会先对数据所在的表先加上IX锁，成功后才能加上X锁
意向锁之间兼容，不会阻塞

-- 给表添加读锁
lock table 表名 read;

-- 给表添加写锁
lock table 表名 write;

-- 释放锁
unlock tables;

4.5 兼容性

	表意向共享锁	表意向排它锁	行共享锁	行排它锁
表意向共享锁	兼容	兼容	兼容	不兼容
表意向排它锁	兼容	兼容	不兼容	不兼容
行共享锁	兼容	不兼容	兼容	不兼容
行排它锁	不兼容	不兼容	不兼容	不兼容

更复杂的原理，等实际用到的时候再理吧

5 事务控制语句

-- 显式地开启一个事务
begin
-- 或者
start transaction

-- 提交事务
commit
-- 或者
commit work

-- 回滚：结束用户的事务，并撤销正在进行的所有未提交的修改
rollback
-- 或
rollback work

-- 在事务中创建一个保存点
savepoint identifier(保存点名)

-- 删除一个事务的保存点
release savepoint identifier(保存点名)

--  把事务回滚到标记点
rollback to identifier(保存点名)