Mysql 第二讲：索引

索引的常见模型

索引的三种可以提高搜索效率的结构：

1. 哈希表，适用于只有等值查询的场景
2. 有序数组，在等值查询和范围查询中的性能非常优秀（二分法），但有序数组索引只适用于静态存储引擎，（不适合添加和删除操作）
3. 搜索树（多叉树）

哈希表这种结构适用于只有等值查询：

有序数组在等值查询和范围查询场景中的性能非常优秀：

如果仅仅看查询效率，有序数组就是最好的数据结构了。但是，在需要更新数据的时候就麻烦了，你往中间插入一个记录就必须得挪动后面所有的记录，成本太高。

有序数组索引只适用于静态存储引擎

二叉树：

因为二叉树树高过高，每次查询都需要访问过多节点，即访问数据块过多，而从磁盘随机读取数据块过于耗时。

为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，我们就不应该使用二叉树，而是要使用“N 叉”树。这里，“N 叉”树中的“N”取决于数据块的大小。

以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200。（MySql默认一个节点的长度为16K，一个整数（bigint）字段索引的长度为 8B,另外每个索引还跟着6B的指向其子树的指针；所以16K/14B ≈ 1170）
这棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经 17 亿了。考虑到树根的数据块总是在内存中的，一个 10 亿行的表上一个整数字段的索引，查找一个值最多只需要访问 3 次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。

InnoDB 的索引模型

每一个索引在innodb里都对应一颗b+树，这颗树的数据就是整张表

一个主键列为 ID 的表，表中有字段 k，并且在 k 上有索引。

mysql> create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;

索引类型分为主键索引和非主键索引。

• 主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。
• 非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

基于主键索引和普通索引的查询有什么区别？

• 如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；
• 如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。

索引维护

普通索引的叶子节点就是主键索引，所有主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

一个数据页满了，按照B+Tree算法，新增加一个数据页，叫做页分裂，会导致性能下降。空间利用率降低大概50%。当相邻的两个数据页利用率很低的时候会做数据页合并，合并的过程是分裂过程的逆过程。

重建普通索引影响不是很大, 但重建主键索引影响大, 因为对普通索引和主键索引都有影响, 消耗较大.

关于重建索引的作用, 删除(并不是真正删除,只是标记为删除) 与 页分裂 等原因会导致数据页有空洞, 重建索引的过程会创建一个新的索引, 把数据按顺序插入, 调高了页面的利用率, 也就是索引更加紧凑, 更省空间.

覆盖索引

select * from T where k between 3 and 5

select ID from T where k between 3 and 5

上面两个sql语句是不一样的，第一个因为要查全部信息，所有会在获得ID 后回表，去查索引为ID 的B+树，更费时间
第二个sql语句，ID已经在k索引树上了，所有不需要回表，更快一些

像这种索引树覆盖了我们查询需求的情况，我们称之为 覆盖索引。

最左前缀索引

B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。

比如联合索引（name，age）

SQL 语句的条件是"where name like ‘张 %’"。这时，我们也能够用上这个索引，查找到第一个符合条件的记录是 ID3，然后向后遍历，知道不满足条件，
所有，只需要满足最左前缀，就可以利用索引加速检索
这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

思考：在建立联合索引的时候，如何安排索引内的字段顺序。

如果我们遇到通过身份证号，查询姓名的情况，就可以用（身份证号，姓名）联合索引。

如果一个查询条件里只有b的语句，是无法使用（a，b）联合索引的，需要单独维护（b）索引或（b，a）

---

索引下推

执行下面的语句：

mysql> select * from tuser where name like '张%' and age=10 and ismale=1;

根据最左前缀索引可以直接定位到ID3
然后：
mysql5.6之前：从ID3开始一个个回表。到主键索引上去比较其它字段 比如age
mysql5.6+：索引下推优化：直接在联合索引 ID3之后对索引中包含的字段先进行判断，过滤掉不满足条件的，减少了回表次数。

有无索引下推对比：

无索引下推：回表4次

有索引下推：回表两次

Mysql 三大锁

全局锁

全局锁 是对整个数据库实例加锁，加全局读锁的命令是：Flush tables with read lock (FTWRL)，使用这个命令后，整个数据库处于只读状态，会阻塞其它线程的：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句

全局锁的典型使用场景是，做全库逻辑备份

全局锁的缺点：（读写分离的情况，就是主表用来 增删改，从表用到查询）

1. 在主库备份时，不能执行更新，业务基本停止
2. 在从库备份时，从库不能执行主库发过来的binlog，造成 主从延迟

既然全局锁有这些缺点，那备份的时候可以不加吗？
我们看看不加锁会发生什么事：

下面这个逻辑（读写分离）是我们要买课，先把课加到 M.course，再扣M.account 的钱。

如果没有锁，先在从表备份账户的钱 为 200，此时还没有来得及备份课程 就来了购买业务，钱扣了99，课程表加上了购买的课程，然后备份课程表。

上面的逻辑在主表和从表中没有问题，买了课也付了钱还有101元，，但是备份的数据确有问题：课购买成功，但钱还是200. 如果后面用这个备份来恢复数据的话，就会出问题

---

既然全局锁不合适，不加锁又不行，那么应该怎么办呢？

我们需要通过事务隔离的可重复读
官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。

但有些事务的引擎不支持事务比如 MyISAM，这种情况我们就只能使用FTWRL命令了

一个小问题:
备份一般都会在备库上执行，你在用–single-transaction 方法做逻辑备份的过程中，如果主库上的一个小表做了一个 DDL，比如给一个表上加了一列。这时候，从备库上会看到什么现象呢？

---

既然要全库只读，为什么不使用 set global readonly=true 的方式呢？
建议你用 FTWRL 方式，主要有两个原因：

1. 在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更大，不建议你使用。
2. 在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。

表级锁

表级别的锁有两种：一种是表锁，一种是元数据锁

表锁

表锁语法：lock tables … read / write

释放锁的两种方式：

1. unlock tables 主动释放锁
2. 客服端断开的时候自动释放

举例：线程 A 中执行 lock tables t1 read, t2 write;

则其它线程写 t1，读写 t2的语句会被阻塞，同时A线程也只能执行读 t1 和 读写 t2，但无法写 t1

---

对于InnoDB 这种支持行锁的引擎，一般不使用lock tables，因为它的影响面还是太大，我们需要用更细粒度的锁。

元数据锁（MDL）

meta data lock

MDL 元数据锁
DML 数据操纵语言
DDL 数据定义语言

MDL 不需要显示操作，访问表的时候会自动加上。
增删改查 的时候 自动加读锁
改变数据库结构 时 自动加写锁
读锁之间不互斥，读写之间和写写之间互斥
所谓互斥就是不能同时发生，只有一个等另一个执行完才能开始执行

上面看着没有问题，但我们需要知道一点：MDL 读锁和写锁在事务提交后才释放，在语句开始时，
所有下面这种情况就会出问题：

sessionA 事务开始，然后会对这个表加一个MDL读表，select查询，然后sessionB的select查询，到这都没有问题，但是之后的sessionC会被阻塞，因为现在的读锁还没有释放，而sessionC需要写锁，但读写互斥，加不进来，所有只能被阻塞。
现在这个线程阻塞，其它线程不能读写，如果后面来的select语句频繁，还有session查询进来，这个库可能就很快爆满了。

---

如何安全地给小表加字段（sessionC）？

首先我们要解决长事务，事务不提交，就会一直占着 MDL 锁。在 MySQL 的 information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。

但考虑一下这个场景。如果你要变更的表是一个热点表，虽然数据量不大，但是上面的请求很频繁，而你不得不加个字段，你该怎么做呢？这时候 kill 可能未必管用，因为新的请求马上就来了。比较理想的机制是，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。MariaDB 已经合并了 AliSQL 的这个功能，所以这两个开源分支目前都支持 DDL NOWAIT/WAIT n 这个语法。

ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ... 

行锁

InnoDB支持行锁，但MyISAM不支持。

两阶段锁

上面两条语句是怎么执行的呢？
首先事务A在执行完两条update语句之后，还会持有行锁，在commit之后才释放。

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。这样可以最大程度的减少事务之间的锁等待，提高并发度

死锁和死锁检测

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都会进入无限等待的状态，称为死锁。

事务A和事务B都在互相等待对方的资源释放，就进入到死锁状态。
此时有两个策略：

1. 进入等待，直到超时，超时时间参数为 innodb_lock_wait_timeout
2. 发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其它事务得以继续执行，将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

第一种情况，超时时间设置过长，等待时间就会太长，漫长的无法接受，如果设置过短，对于锁等待就可能误伤

所以一般使用第二种策略，主动死锁检测，在有死锁的情况下，可以很快检测出
每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。

死锁检测也有缺点：因为每加入一个新来的被堵住的线程，都会判断会不会是这个线程导致的死锁，这一操作的时间复杂度是O(n)。假如有1000个并发线程同时更新同一行，死锁检测就是100万量级。这期间会消耗大量CPU资源，这时就会看到，CPU利用率很高，但每秒执行不了几个事务。

那么怎么解决死锁检测消耗大量CPU资源这件事呢？

第一种方法，就是在确认一定不会发生死锁的地方，关闭死锁检测。但这有一定风险，如果有死锁就会出现大量的超时。

第二种方法就是控制并发度，如果同一时间的并发数量少，那么死锁检测的成本就减少了。并发控制要做在数据库服务端，让对于同一行的更新在进入引擎前排队，这样InnoDB内部就不会有大量的死锁检测了。

还有一种方案是，把多条记录变为一条，比如更新账户的余额，我们可以把100条记录的值相加，求出总和，再去和账户的余额相加 求出余额，这样冲突就变成了原来的 1 / 100。但这个方案需要根据具体业务做调整，比如有退票的可能，余额不能是负数

普通索引和唯一索引怎么选择

• 唯一索引
  由于索引定义了唯一性，查找到第一个满足条件的记录后，直接停止继续检索。

• 普通索引
  查找到第一个满足条件的记录后，继续向后遍历，直到第一个不满足条件的记录。

说什么是change buffer？

当我们需要更新某个数据的时候,如果这个数据所在的数据页在内存中时,可以直接更新,如果数据页不在内存中,在不影响数据一致性的前提下,InnoDB会缓存更新操作在 change buffer中, 这样就不用从磁盘中读取数据页了.

change buffer机制减少了读磁盘避免了占用内存,提高了内存利用率,提升了语句的执行速度.

change buffer 只能对普通索引有效，对唯一索引没有用

因为唯一索引需要确定索引是否唯一(和其它的值不一样),需要把数据页读到内存,一一比较.

change buffer 对所有普通索引都有加速作用吗

真正改变数据的是merge，change buffer 更像是将数据变更的动作缓存下来。所有在merge之前，change buffer的内容越多，则在数据页merge的时候，收益越大。（如 写多读少的情况）
如果是写少读多，change buffer可能才缓存了一个变动，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。

关于merge
将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。

redo log 和 change buffer

1. Page 1 在内存中，直接更新内存；
2. Page 2 没有在内存中，就在内存的 change buffer 区域，记录下“我要往 Page 2 插入一行”这个信息
3. 将上述两个动作记入 redo log 中（图中 3 和 4）。

redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。

讨论一下Mysql为什么会选错索引

场景:
表:

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB;

插入10万行数据(1,1,1)…(a,a,a)

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=100000)do
    insert into t values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

---

mysql> select * from t where a between 10000 and 20000;

通过explain命令查看语句执行情况

索引是a, 扫描1万行数据, 符合预期

下面我们这样做:

通过慢查询日志,查看执行情况
我们发现,sessionB 语句执行不在使用索引a了.并且它扫描了10万行数据.

为什么会选错索引呢？因为行数判断错误
为什么行数判断错误？因为sessionB的删除并不是真正的删除，删除的操作只是将原来那一行的删除版本号设置成了当前事务版本号，所以后面新增完成以后，其实是有两份索引a的数据
原来的索引加上添加的索引，此时== a索引 有20万个==。

我们强制使用a (force index)使用来对比一下:

select * from t where a between 10000 and 20000; /*Q1*/
select * from t force index(a) where a between 10000 and 20000;/*Q2*/

Q1 扫描了 10 万行，显然是走了全表扫描，执行时间是 40 毫秒。Q2 扫描了 10001 行，执行了 21 毫秒。MySQL 用错了索引，导致了更长的执行时间。

优化器逻辑

选择索引是优化器的工作
优化器选择索引会结合一下几点判断：
扫描行数，临时表，是否排序

那既然选择错了索引就说明行数判断的不对。

mysql怎么预估计行数的？

一个索引上不同的值的个数，我们称之为“基数”（cardinality）.
基数是 每页的基数平均数 * 页面数.
InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。

以下是优化器预估计的扫描行数:

但是为什么Q2比Q1的rows小,却没有走a索引呢?
因为使用 a索引(二级索引) 需要回表, 这个过程需要时间.
优化器认为直接扫描主键索引更快。当然，从执行时间看来，这个选择并不是最优的。

所以 是 扫描行数 + 回表时间 两个因素让优化器选择Q1 而不是 Q2

怎么解决选错索引的问题?
analyze table t 命令，可以用来重新统计索引信息。

索引选择异常和处理

采用 force index 强行选择一个索引
第二种方法就是，我们可以考虑修改语句，引导 MySQL 使用我们期望的索引
第三种方法是，在有些场景下，我们可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。

给字符串字段加索引

问题的提出:
如何在邮箱这样的字段上建立合理的索引

使用邮箱时,sql:

mysql> select f1, f2 from SUser where email='xxx';

如果email字段没有索引,就进行全表扫描

---

我们也可以给字符串加上索引, 还可以加上前缀索引.(就是定义字符串的一部分作为索引) 占用空间小

alter table SUser add index index2(email);

指定前六个字符是索引
alter table SUser add index index2(email(6));

select id,name,email from SUser where email='zhangssxyz@xxx.com';

index1 查询顺序:

1. 从 index1 索引树找到满足索引值是’zhangssxyz@xxx.com’的这条记录，取得 ID2 的值；
2. 到主键上查到主键值是 ID2 的行，判断 email 的值是正确的，将这行记录加入结果集；
3. 取 index1 索引树上刚刚查到的位置的下一条记录，发现已经不满足 email='zhangssxyz@xxx.com’的条件了，循环结束。

这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。

index2 查询顺序:

1. 从index2 索引树找到满足索引值 “zhangs” 的记录,找到第一个ID1;
2. 到主键上查到主键值是ID1的行,判断出email的值不是 “zhangssxyz@xxx.com”,丢弃.
3. 去index2上刚查过的的位置的下一条记录, 发现仍然是’zhangs’，取出 ID2，再到 ID 索引上取整行然后判断，这次值对了，将这行记录加入结果集；
4. 重复上一步，直到在 idxe2 上取到的值不是’zhangs’时，循环结束。

这个过程中,主键索引取4次数据,也就是扫描了4行.

所以说,前缀索引可以节省空间,但可能会增加额外的查询成本
另外系统无法判断前缀索引是否截断完整信息, 所以使用前缀索引就无法使用覆盖索引对查询性能的优化了.
(如果只select id, email的话,可以索引覆盖,不需要还回表. 但前缀索引不行)

小结

字符串创建索引的方式:

1. 直接创建完整索引,比较占空间;
2. 创建前缀索引,节省空间, 增加扫描次数, 不能索引覆盖
3. 倒序存储( 比如身份证 ),再创建前缀索引;
4. 创建hash字段索引( 添加一列 hash值 ), 有额外存储和计算消耗.