Mysql-索引

[TOC]

索引

索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有:B+树和 Hash。
Hash 索引不支持顺序和范围查询。

主键索引 vs 唯一索引

主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。

主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。

唯一性索引列允许空值，而主键列不允许为空值。

主键列在创建时，已经默认为空值 + 唯一索引了。

主键可以被其他表引用为外键，而唯一索引不能。

一个表最多只能创建一个主键，但可以创建多个唯一索引。

主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。

B 树& B+树两者有何异同呢？

B 树的所有节点既存放键(key) 也存放 数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。

B 树的叶子节点都是独立的;

B+树的叶子节点有一条引用链指向与它相邻的叶子节点。

B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。

而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

MyISAM 引擎中，B+Tree 叶节点的 data 域存放的是数据记录的地址。在索引检索的时候，首先按照 B+Tree 搜索算法搜索索引，如果指定的 Key 存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。

InnoDB 引擎中，其数据文件本身就是索引文件。
相比 MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶节点 data 域保存了完整的数据记录。这个索引的 key 是数据表的主键，因此 InnoDB 表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”，而其余的索引都作为辅助索引。

辅助索引的 data 域存储相应记录主键的值而不是地址，这也是和 MyISAM 不同的地方。

在根据主索引搜索时，直接找到 key 所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，在走一遍主索引。

因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

B+树磁盘

所以一次访盘请求（读/写）完成过程由三个动作组成：

寻道（时间）：磁头移动定位到指定磁道
旋转延迟（时间）：等待指定扇区从磁头下旋转经过
数据传输（时间）：数据在磁盘与内存之间的实际传输
而总时间就是 寻到时间+旋转延迟+n×数据传输时间。

但是这个节点到底应该多大?虽然磁盘是以扇区为大小进行读写的，但是磁盘和操作系统却是用块为单位进行交互的。
块的大小必须是扇区大小的 2 的 n 次方。比如4k，一个页的大小也 4k。

所以 b 树基本上可以说是为机械硬盘量身定制的，b 树的层数决定了在磁盘上查找的次数，而越小的次数，带来的就是更高的效率。

然而在固态硬盘中，虽然也是以整数倍读写，但是它并不用移动磁头之类的机械操作，固态硬盘使用闪存进行存储，所以它的寻址效率和内存是一个数量级的，使用电位进行寻址，类似内存本身每一个存储空间都有一个地址，直接通过电门运算就找到那个地址了 。

但是从数据结构上来说，索引毕竟是索引，肯定要比遍历快很多，所以虽然 ssd 的随机读写很快，但是毕竟没有内存快。所以使用 b 树还是很有意义，再加上很多优化的算法都是基于磁盘的，这种历史包袱也不是说丢就能丢的。

因为旋转的受限，所以就算是顺序读写，固态硬盘也要比机械硬盘快，但是快的不是特别明显，毕竟如果是顺序读写，都可以有预判，可以提前做优化。这也就是为什么即使是固态硬盘本身，顺序读也要比随机读快。

为什么 MySQL 数据库要用 B+树存储索引？而不用红黑树、Hash、B 树？

红黑树：如果在内存中，红黑树的查找效率比 B 树更高，但是涉及到磁盘操作，B 树就更优了。因为红黑树是二叉树，数据量大时树的层数很高，从树的根结点向下寻找的过程，每读 1 个节点，都相当于一次 IO 操作，因此红黑树的 I/O 操作会比 B 树多的多。

B 树索引：B 树索相比于 B+树，在进行范围查询时，需要做局部的中序遍历，可能要跨层访问，跨层访问代表着要进行额外的磁盘 I/O 操作；另外，B 树的非叶子节点存放了数据记录的地址，会导致存放的节点更少，树的层数变高。

概念

主键索引(Primary Key)

数据表的主键列使用的就是主键索引。

一张数据表有只能有一个主键，并且主键不能为 null，不能重复。

在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。

二级索引(辅助索引)

二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。
唯一索引，普通索引，前缀索引等索引属于二级索引。

聚集索引

MyISAM: B+Tree 叶节点的data 域存放的是数据记录的地址。在索引检索的时候，⾸先按照 B+Tree 搜索算法搜索索引，如果指定的 Key 存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“⾮聚簇索引”。

InnoDB: 其数据⽂件本身就是索引⽂件。相⽐MyISAM，索引⽂件和数据⽂件是分离的，其表数据⽂件本身就是按 B+Tree 组织的⼀个索引结构，树的叶节点 data 域保存了完整的数据记录。这个索引的 key 是数据表的主键，因此 InnoDB 表数据⽂件本身就是主索引。这被称为“聚簇索引（或聚集索引）”。

⽽其余的索引都作为辅助索引，辅助索引的 data 域存储相应记录主键的值⽽不是地址，这也是和 MyISAM 不同的地⽅。在根据主索引搜索时，直接找到 key 所在的节点即可取出数据；再根据辅助索引查找时，则需要先取出主键的值，再⾛⼀遍主索引。因此，在设计表的时候，不建议使⽤过⻓的字段作为主键，也不建议使⽤⾮单调的字段作为主键，这样会造成主索引频繁分裂。

聚集索引的缺点

依赖于有序的数据 ：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。

更新代价大 ： 如果对索引列的数据被修改时，那么对应的索引也将会被修改， 而且况聚集索引的叶子节点还存放着数据，修改代价肯定是较大的， 所以对于主键索引来说，主键一般都是不可被修改的。

非聚集索引的叶子节点并不一定存放数据的指针， 因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。

什么是回表查询？

InnoDB 中，对于主键索引，只需要走一遍主键索引的查询就能在叶子节点拿到数据。
而对于普通索引，叶子节点存储的是 key + 主键值，因此需要再走一次主键索引，通过主键索引找到行记录，这就是所谓的回表查询，先定位主键值，再定位行记录。

走普通索引，一定会出现回表查询吗？

不一定，如果查询语句所要求的字段全部命中了索引，那么就不必再进行回表查询。
有一个 user 表，主键为 id，name 为普通索引，则再执行：
select id, name from user where name =’joonwhee’ 时，通过 name 的索引就能拿到 id 和 name。

覆盖索引

当索引上包含了查询语句中的所有列时，我们无需进行回表查询就能拿到所有的请求数据，因此速度会很快。当 explain 的输出结果 Extra 字段为 Using index 时，则代表触发覆盖索引。

索引下推

索引下推是 MySQL 5.6引入了一种优化技术，默认开启，使用SET optimizer_switch = ‘index_condition_pushdown=off’;可以将其关闭。

官方文档中给的例子和解释如下： people表中（zipcode，lastname，firstname）构成一个索引
SELECT * FROM people WHERE zipcode=’95054′ AND lastname LIKE ‘%etrunia%’ AND address LIKE ‘%Main Street%’;

如果没有使用索引下推技术，则MySQL会通过zipcode=’95054’从存储引擎中查询对应的数据，返回到MySQL服务端，然后MySQL服务端基于lastname LIKE ‘%etrunia%’和address LIKE ‘%Main Street%’来判断数据是否符合条件。

如果使用了索引下推技术，则MYSQL首先会返回符合zipcode=’95054’的索引，然后根据lastname LIKE ‘%etrunia%’来判断索引是否符合条件。

如果符合条件，则根据该索引来定位对应的数据，如果不符合，则直接reject掉。 有了索引下推优化，可以在有like条件查询的情况下，减少回表次数。

当一条SQL使用到索引下推时，explain的执行计划中的extra字段中内容为：Using index condition

上面的例子中，提到了like，包括MySQL官网中也只提到了like，但是其实不止有Like。因为我认为索引下推其实是解决索引失效带来的效率低的问题的一种手段。

所以当联合索引中，某个非前导列因为索引失效而要进行扫表并回表时，就可以进行索引下推优化了。

联合索引（复合索引）的底层实现？最佳左前缀原则？

联合索引底层还是使用 B+树索引，并且还是只有一棵树，只是此时的排序会：首先按照第一个索引排序，在第一个索引相同的情况下，再按第二个索引排序，依次类推。

这也是为什么有“最佳左前缀原则”的原因，因为右边（后面）的索引都是在左边（前面）的索引排序的基础上进行排序的，如果没有左边的索引，单独看右边的索引，其实是无序的

B+树中一个节点到底多大合适？

1 页或页的倍数最为合适。因为如果一个节点的大小小于 1 页，那么读取这个节点的时候其实也会读出 1 页，造成资源的浪费。所以为了不造成浪费，所以最后把一个节点的大小控制在 1 页、2 页、3 页等倍数页大小最为合适。

这里说的“页”是 MySQL 自定义的单位（和操作系统类似），MySQL 的 Innodb 引擎中 1 页的默认大小是 16k。

为什么一个节点为 1 页就够了？

Innodb 中，B+树中的一个节点存储的内容是：

• 非叶子节点：key + 指针
• 叶子节点：数据行（key 通常是数据的主键）
  对于叶子节点：我们假设 1 行数据大小为 1k（对于普通业务绝对够了），那么 1 页能存 16 条数据。对于非叶子节点：key 使用 bigint 则为 8 字节，指针在 MySQL 中为 6 字节，一共是 14 字节，则 16k 能存放 16 * 1024 / 14 = 1170 个。那么一颗高度为 3 的 B+树能存储的数据为：1170 * 1170 * 16 = 21902400（千万级）。

什么是 Buffer Pool？

Buffer Pool 是 InnoDB 维护的一个缓存区域，用来缓存数据和索引在内存中，主要用来加速数据的读写，如果 BufferPool 越大，那么 MySQL 就越像一个内存数据库，默认大小为 128M。
InnoDB 会将那些热点数据和一些 InnoDB 认为即将访问到的数据存在 Buffer Pool 中，以提升数据的读取性能。
InnoDB 在修改数据时，如果数据的页在 Buffer Pool 中，则会直接修改 Buffer Pool，此时我们称这个页为脏页，InnoDB 会以一定的频率将脏页刷新到磁盘，这样可以尽量减少磁盘 I/O，提升性能。

非聚集索引不一定回表查询

用户准备使用 SQL 查询用户名，而用户名字段正好建立了索引。
SELECT name FROM table WHERE name=’guang19’;
那么这个索引的 key 本身就是 name，查到对应的 name 直接返回就行了，无需回表查询。

联合索引命中规则

1. MySQL 联合索引遵循最左前缀匹配规则，即从联合索引的最左列开始向右匹配，直到遇到匹配终止条件。
2. 例如联合索引(col1, col2, col3), where 条件为 col1=a AND col2=b 可命中该联合索引的(col1,col2)前缀部分, where 条件为 col2=b AND col3=c 不符合最左前缀匹配，不能命中该联合索引。
3. **匹配终止条件为范围操作符(如>, <, between, like 等)或函数等不能应用索引的情况**。例如联合索引(col1, col2, col3), where 条件为 col1=a AND col2>1 AND col3=c, 在 col2 列上为范围查询，匹配即终止，只会匹配到 col1，不能匹配到(col1, col2, col3)。
4. where 条件中的顺序不影响索引命中。例如联合索引(col1, col2, col3), where 条件为 col3=c AND col2=b AND col1=a, MySQL 优化器会自行进行优化，可命中联合索引(col1, col2, col3)。

锁

共享锁和排他锁？

共享锁又称为读锁，简称 S 锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

排他锁又称为写锁，简称 X 锁，顾名思义，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务可以对数据就行读取和修改。
常见的几种 SQL 语句的加锁情况如下：
select * from table：不加锁
update/insert/delete：排他锁
select * from table where id = 1 for update：id 为索引，加排他锁
select * from table where id = 1 lock in share mode：id 为索引，加共享锁

MySQL 如何实现悲观锁和乐观锁？

乐观锁：更新时带上版本号（cas 更新）
悲观锁：使用共享锁和排它锁，select…lock in share mode，select……for update。

避免死锁？

事务之间对资源访问顺序的交替

一个用户 A 访问表 A（锁住了表 A），然后又访问表 B；另一个用户 B 访问表 B（锁住了表 B），然后企图访问表 A；这时用户 A 由于用户 B 已经锁住表 B，它必须等待用户 B 释放表 B 才能继续，同样用户 B 要等用户 A 释放表 A 才能继续，这就死锁就产生了。

并发修改同一记录

主要是由于没有一次性申请够权限的锁导致的。

用户 A 查询一条纪录，然后修改该条纪录；这时用户 B 修改该条纪录，这时用户 A 的事务里锁的性质由查询的共享锁企图上升到独占锁，而用户 B 里的独占锁由于 A 有共享锁存在所以必须等 A 释放掉共享锁，而 A 由于 B 的独占锁而无法上升的独占锁也就不可能释放共享锁，于是出现了死锁。

乐观锁，实现写-写并发

何尽可能的避免死锁呢？

1）以固定的顺序访问表和行。即按顺序申请锁，这样就不会造成互相等待的场面。

2）大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。

3）在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。

4）降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从 RR 调整为 RC，可以避免掉很多因为 gap 锁造成的死锁。

5）为表添加合理的索引。如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大。