MySQL索引学习思维导图

1. 什么是索引？

数据库索引，是数据库管理系统（DBMS）中一个排序的数据结构，以协助快速查询、更新数据库表中数据。比如：书本的目录，可以快速定位某一页的内容。索引必须是有序的，索引的字段的值可能是无序的。

1.1 索引类型

1.1.1 普通索引(Normal)

也叫非唯一索引，是最普通的索引，没有任何的限制。

1.1.2 唯一索引(Unique)：

唯一索引要求键值不能重复。另外需要注意的是，主键索引是一种特殊的唯一索引，它还多了一个限制条件，要求键值不能为空。主键索引用 primay key 创建。

1.1.3 全文索引(Fulltext):

针对比较大的数据，比如我们存放的是消息内容，有几 KB 的数据的这种情况，如果要解决 like 查询效率低的问题，可以创建全文索引。只有文本类型的字段才可以创建全文索引，比如 char、varchar、text。一般用专业的搜索工具比如ES。

create table m3 (
name varchar(50),
fulltext index(name)
);

全文索引的使用：

select * from fulltext_test where match(content) against('阿斯蒂芬' IN NATURAL LANGUAGE MODE);

1.1.4 MYISAM专用索引，空间索引(SPATIAL)：

空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建。

2. 索引存储模型推演

二分查找
二叉查找树（BST）
平衡二叉树（AVL）
多路平衡二叉树（B Tree）
加强多路平衡二叉树（B+Tree）

2.1 二分查找（有序数组）

优点：查询效率非常高。
缺点：更新数据，要挪动大量数据（改变index）。
结论：只适合存储静态数据，不适合做索引。需要采用链表，而单链表更新效率高，但是查询效率低也不适合。
总结：查询效率低。

2.2 二叉查找树（BST）

网站图例：www.cs.usfca.edu/~galles/vis…

优点：快速查询、快速插入。
缺点：查找耗时和树的深度有关，最坏情况下时间复杂度会退化成O(n)。比如：插入数据刚好是有序的变成斜树，深度太大，不平衡。
结论：一次IO，加载一个节点，在内存计算（走右节点还是左节点），继续下一次IO，直到找到对应节点。每次加载节点但是只存一个键值对，浪费空间，并且增加IO次数。如果在一个树的节点，尽量存储多个单元（键值、数据磁盘地址、子节点引用），一次加载满16kb的节点，效率会高的多。降低树的深度，减少IO次数。
总结：不平衡，IO次数多。

2.3 平衡二叉树（AVL）

当我们用树的结构来存储索引的时候，访问一个节点就要跟磁盘之间发生一次 IO。InnoDB 操作磁盘的最小的单位是一页（或者叫一个磁盘块），大小是 16K(16384 字节)。那么，一个树的节点就是 16K 的大小。如果我们一个节点只存一个键值+数据+引用，例如整形的字段，可能只用了十几个或者几十个字节，它远远达不到 16K 的容量，所以访问一个树节点，进行一次 IO 的时候，浪费了大量的空间。查看InnoDB页容量：SHOW VARIABLES LIKE 'innodb_page_size';

优点：左右子树深度差的绝对值不能超过1，在二叉查找树的基础上，节点通过左旋、右旋达到平衡，不会产生斜树。
缺点：只存储键值+数据磁盘地址+子节点引用，数据量远远达不到16K，访问一个树节点，进行一次IO，浪费大量的空间。分叉,数只有2，节点存储数据太少，树的深度更高，需要访问更多节点，磁盘交互次数过多。
结论：节点没有存满数据浪费空间，导致树的深度过大，IO次数过多
总结：InnoDB每页容量16K，树节点只存储一个键值+数据+两个子节点引用，浪费空间，树的深度得不到控制。

网站图例：www.cs.usfca.edu/~galles/vis…

2.4 多路平衡查找树（B Tree）（合并、分裂）

网站图例：www.cs.usfca.edu/~galles/vis… AVL树升级版，跟 AVL 树一样，B 树在枝节点和叶子节点存储键值、数据地址、节点引用。它有一个特点：分叉数（路数）永远比关键值数多1。比如我们画的这棵树，每个节点存储两个关键字，那么就会有三个指针指向三个子节点，以此类推。

特点：分叉数（路数）永远比关键值数多1。
优点：AVL树升级版，不再是二叉，而是多叉（多路），可以根据页容量（16KB）和索引的容量计算，可以放满键+ 值，和路数。可以容量得到最大利用率。大大降低树的深度。
缺点：性能不稳定，查询根节点数据快，叶子节点可能会很慢。
结论：可以容量得到最大利用率，大大降低树的深度。
总结：MySQL使用的是加强版本B+Tree。

节点的分裂和合并，其实就是 InnoDB 页的分裂和合并

2.5 B+Tree

特点：关键字与路数相等；只在叶子节点存储数据；叶子节点有一个双向指针（有序链表）。
优点：B Tree能解决的问题，B+Tree都能解决；扫库、扫表能力更强；磁盘读写能力更强；排序能力更强；效率更加稳定。
缺点：必须要走到叶子节点才能拿到数据（不同于B Tree根节点也可以拿到数据），B+Tree是最稳定的，但不是每种情况都是最快的。
总结：MySQL索引数据结构用的是B+Tree，稳定，每次IO时间都一样。

2.5.1 B+Tree优点说明

B Tree能解决的问题，B+Tree都能解决
扫库、扫表的能力更强：（不需要遍历树左序、中序、右序遍历每一次都得从根节点开始），只需要遍历叶子节点顺序访问。
磁盘读写能力更强：IO次数更少。它把所有数据放到了叶子节点，意味着内节点可以存更多的关键字，更多的指针，形成更多的分叉，深度更低。
排序能力更强：大于、小于、between and更快，基于叶子节点的双向指针来实现的。比如：between 30 and 60，只需在叶子节点找到30，顺序往后一直读取到60，就可以了，不需要回到跟节点遍历树。
效率更加稳定：（B+Tree 永远是在叶子节点拿到数据，所以 IO 次数是稳定的）加稳定。

它的关键字的数量是跟路数相等的； B+Tree 的根节点和枝节点中都不会存储数据，只有叶子节点才存储数据。搜索到关键字不会直接返回，会到最后一层的叶子节点。

举个例子：假设一条记录是 1K，一个叶子节点（一页）可以存储 16 条记录。非叶子节点可以存储多少个指针？假设索引字段是 bigint 类型，长度为 8 字节。指针大小在 InnoDB 源码中设置为6 字节，这样一共 14 字节。非叶子节点（一页）可以存储 16384/14=1170 个这样的单元（键值+指针），代表有 1170 个指针。树深度为 2 的时候，有 1170^2 个叶子节点，可以存储的数据为1170117016=21902400。

在查找数据时一次页的查找代表一次 IO，也就是说，一张 2000 万左右的表，查询数据最多需要访问 3 次磁盘。所以在 InnoDB 中 B+ 树深度一般为 1-3 层，它就能满足千万级的数据存储。

B+Tree 的每个叶子节点增加了一个指向相邻叶子节点的指针，它的最后一个数据会指向下一个叶子节点的第一个数据，形成了一个有序链表的结构。 它是根据左闭右开的区间 [ )来检索数据

2.6 为什么不用红黑树

2.6.1 为什么不用红黑树？1、只有两路；2、不够平衡。

红黑树也是二叉查找树（BST）树，但是不是严格平衡的。必须满足5个约束：

节点为红色或者黑色。
根节点必须是黑色。
叶子节点都是黑色的null节点。
红色节点的两个子节点都是黑色（不允许两个相邻的红色节点）。
从任意节点触发，到其每个叶子节点的路径中包含相同数量的黑色节点。插入：60、56、68、45、64、58、72、43、49

基于以上规则，可以推导出：

从根节点到叶子节点的最长路径（红黑相间的路径）不大于最短路径（全部是黑色节点）的 2 倍。

为什么不用红黑树？1、只有两路；2、不够平衡。

红黑树一般只放在内存里面用。例如 Java 的 TreeMap。

2.7 索引方式

在 Navicat 的工具中，创建索引，索引方式有两种，Hash 和 B Tree。

2.7.1 Hash索引

Innodb不能显示创建Hash索引。
以 KV 的形式检索数据，也就是说，它会根据索引字段生成哈希码和指针，指针指向数据。InnoDB 内部使用哈希索引。
Memory引擎可以使用Hash索引。

2.7.1.1 Hash索引的特点

时间复杂度是O(1)，查询速度比较快。因为哈希索引里面的数据不是按顺序存储的，所以不能用于排序。
查询数据时要根据键值计算哈希码，它只能支持等值查询（= IN），不支持（> < >= <= between and）。
字段重复值很多的时候，会出现大量的哈希冲突（采用拉链法解决），效率会降低。

2.7.1.1 InnoDB 内部使用哈希索引

InnoDB 内部使用哈希索引来实现自适应哈希索引特性。这句话的意思是 InnoDB 只支持显式创建 B+Tree 索引，对于一些热点数据页，InnoDB 会自动建立自适应 Hash 索引，也就是在 B+Tree 索引基础上建立 Hash 索引，这个过程对于客户端是不可控制的，隐式的。我们在 Navicat 工具里面选择索引方法是哈希，但是它创建的还是 B+Tree 索引，这个不是我们可以手动控制的。

buffer pool 里面有一块区域是 Adaptive Hash Index 自适应哈希索引，就是这个。

这个开关默认是 ON：show variables like 'innodb_adaptive_hash_index';

从存储引擎的运行信息中可以看到：show engine innodb status\G

----------------------
BUFFER POOL AND MEMORY
----------------------
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------

因为B Tree 和B+Tree 的特性，它们广泛地用在文件系统和数据库中，例如Windows的 HPFS 文件系统，Oracel、MySQL、SQLServer 数据库。

3. B+Tree落地形式

MySQL是一个支持插件式存储引擎的数据库。每个表创建的时候都可以知道它所使用的存储引擎。最常用的两个存储引擎：MyISAM和InnoDB的索引实现。

3.1 MySQL架构

3.2 MySQL数据存储文件

3.2.1 创建各种存储引擎的表

3.2.1.1 InnoDB

CREATE TABLE `user_innodb` (
  `id` int(11) PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3.2.1.1 MyISAM

CREATE TABLE `user_myisam` (
  `id` int(11) PRIMARY KEY  AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

3.2.1.1 MEMORY

CREATE TABLE `user_memory` (
  `id` int(11) PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4;

3.2.1.1 Archive

CREATE TABLE `user_archive` (
  `id` int(11) PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL
) ENGINE=Archive DEFAULT CHARSET=utf8mb4;

3.2.1.1 CSV

CREATE TABLE `user_csv` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `gender` tinyint(1) NOT NULL,
  `phone` varchar(11) NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8mb4;

3.3 InnoDB和MyISAM存储文件解析

查询MySQL存储文件路径：show VARIABLES LIKE 'datadir';在这个路径下，每个库都有一个目录。

3.3.1 .frm

有一个是相同的文件.frm。.frm是MySQL里面表结构定义的文件，不管你建表的时候选用任何一个存储引擎都会生成。主要看一下其他两个文件是怎么实现 MySQL 不同的存储引擎的索引的。

3.3.1 MyISAM

3.3.1.1 .MYD 文件

D 代表 Data，是 MyISAM 的数据文件，存放数据记录，比如我们的 user_myisam 表的所有的表数据。

3.3.1.2 .MYI 文件

I 代表 Index，是 MyISAM 的索引文件，存放索引，比如我们在id 字段上面创建了一个主键索引，那么主键索引就是在这个索引文件里面。在 MyISAM 里面，索引和数据是两个独立的文件。MyISAM 的 B+Tree 里面，叶子节点存储的是数据文件对应的磁盘地址。

3.3.1.3 主键索引

所以从索引文件.MYI中找到键值后，会到数据文件.MYD中获取相应的数据记录。

3.3.1.4 辅助索引

在 MyISAM 里面，辅助索引也在这个.MYI 文件里面。辅助索引跟主键索引存储和检索数据的方式是没有任何区别的，一样是在索引文件里面找到磁盘地址，然后到数据文件里面获取数据。

3.3.2 InnoDB

InnoDB只有一个文件（.ibd文件），那索引放在哪里？

3.3.2.1 .ibd文件

在InnoDB里面，它是以主键索引来组织数据的存储的，索引索引文件和数据文件是同一个文件，都在.ibd文件里面。在InnoDB的主键索引的叶子节点上，它直接存储了我们的数据。

3.3.2.2 聚集索引（聚簇索引）与非聚集索引

聚集索引就是索引键值的逻辑顺序跟表数据行的物理存储顺序是一致的。（比如字典的目录是按拼音排序的，按拼音排序的这种目录就叫聚集索引）。在InnoDB里面，它组织数据的方式叫做聚集索引组织表（clustered index organize table），所以主键索引是聚集索引，非主键都是非聚集索引。

3.3.2.3 聚集索引检索数据方式

3.3.2.4 非聚集索引检索数据方式

如果InnoDB里面主键是这样存储的，那主键之外的索引，比如我们在name字段上面建的普通索引，又是怎么存储和检索数据的呢？

InnoDB中，主键索引和辅助索引是有一个主次之分的。辅助索引存储的是辅助索引和主键值。如果使用辅助索引查询，会根据主键值在主键索引中查询，最终取得数据。

为什么在辅助索引里面存储的是主键值而不是主键的磁盘地址呢？
如果主键的数据类型比较大，是不是比存地址更消耗空间呢？
我们前面说到 B Tree 是怎么实现一个节点存储多个关键字，还保持平衡的呢？答：是因为有分叉和合并的操作，这个时候键值的地址会发生变化，所以在辅助索引里面不能存储地址。另一个问题，如果一张表没有主键怎么办？
如果我们定义了主键(PRIMARY KEY)，那么 InnoDB 会选择主键作为聚集索引。
如果没有显式定义主键，则 InnoDB 会选择第一个不包含有 NULL 值的唯一索引作为主键索引。
如果也没有这样的唯一索引，则 InnoDB 会选择内置 6 字节长的 ROWID 作为隐藏的聚集索引，它会随着行记录的写入而主键递增。查询一张没有索引表的隐藏聚集索引：select _rowid name from t2;

4. 索引使用原则

4.1 列的离散度

离散列公式：count(distinct(id))/count(*)列的全部不同值和所有数据行的比例。数据行数相同的情况下，分子越大，列的离散度就越高。

如果在 B+Tree 里面的重复值太多，MySQL 的优化器发现走索引跟使用全表扫描差不了多少的时候，就算建了索引，也不一定会走索引。查看索引命令：show index from <表名> [from <数据库名>]; 删除索引命令：alter table <表名> drop index <索引名>; 增加索引命令：alter table <表名> add index index_user_gender(gender); 查看索引命令：show index from user_innodb from gupao;

4.1.1 离散度低的字段建立索引，查询速度没有提升

alter table user_innodb drop index idx_user_gender;
alter table user_innodb add index index_user_gender(gender);
explain select * from user_innodb where gender = 0;

4.1.2 离散度高的字段建立索引，查询速度明显提升

alter table user_innodb drop index idxuser_name;
alter table user_innodb add index idx_user_name(name);
explain select * from user_innodb where name = '青山';

4.2 联合索引最左匹配

多条件查询，建立联合索引。单例索引可以看成特殊的联合索引。

alter table user_innodb add index <索引名称>([<字段1>,<字段2>,<字段...>]);
alter table user_innodb add index comidx_name_phone(name,phone);

联合索引在B+Tree中是复合的数据结构，按照从左到右的顺序来建立搜索树。（name在左边，phone在右边），name是有序的，phone是无序的。当那么相等的时候phone才是有序的。

如果使用where name = '黄几' and phone = '135xxxx'去查询数据的时候，B+Tree会有序比较name来确定下一步应该搜索的方向，往左还是往右。如果name相同的时候再比较phone。

但是如果查询条件没有name，就不知道第一版应该查哪个节点，因为建立搜索树的时候name是第一个比较因子，索引用不到索引。

explain select * from user_innodb where name='黄几' and phone='13500000000';
explain select * from user_innodb where phone='13500000000' and name = '黄几';
explain select * from user_innodb where name = '黄几';
explain select * from user_innodb where phone = '13500000000';

4.2.1 最左匹配原则

相当于建立了两个联合索引(name),(name,phone)。
如果我们创建三个字段的索引 index(a,b,c)，相当于创建三个索引：
index(a)
index(a,b)
index(a,b,c)
用 where b=? 和 where b=? and c=? 和 where a=? and c=?是不能使用到索引
的。不能不用第一个字段，不能中断。
这里就是 MySQL 联合索引的最左匹配原则。

4.3 覆盖索引

4.3.1 什么是回表？

非主键索引，我们先通过索引找到主键索引的键值，再通过主键值查出索引里面没有的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表。

4.3.2 什么是覆盖索引？

在辅助索引里面，不管是单列索引还是联合索引，如果 select 的数据列只用从索引中就能够取得，不必从数据区中读取，这时候使用的索引就叫做覆盖索引，这样就避免了回表。

4.3.3 实例

--创建联合索引
alter table user_innodb drop index comixd_name_phone;
alter table user_innodb add index `comixd_name_phone`(`name`,`phone`);

4.3.3.1 使用联合索引（不回表）

以下三个查询语句都用到了覆盖索引：

explain select name,phone from user_innodb where name='黄几' and phone = '13500000000';
explain select name from user_innodb where name = '黄几' and phone = '13500000000';
explain select phone from user_innodb where name = '黄几' and phone = '13500000000';

Extra 里面值为“Using index”代表使用了覆盖索引。

4.3.3.2 不使用覆盖索引的条件（回表）

select * ，用不到覆盖索引。

4.3.3.3 条件不走索引，实际却走索引的条件

explain select phone from user_innodb where phone = '13500000000';

where phone = '135xxxx'，正常是用不到索引的，但是执行这条语句却用到了索引，原因是优化器发现查询的name字段刚好是索引的列，优化器选择把phone字段过滤，选择使用到索引，不走全部扫描，优化器觉得走索引的效率高一些。是优化器的一个选择。注意如下图：当possible_keys为null的时候，key有可能是有值会走索引的。

4.4 索引条件下推

存储引擎层只根据索引过滤数据，索引的的条件是在在服务层过滤的。

create table employees(
emp_no int(11) not null,
birth_date date null,
first_name varchar(14) not null,
last_name varchar(16) not null,
gender enum('M','F') not null,
hire_date date null,
primary key(emp_no)
)engine=innodb default charset=latin1;

alter table employees add index idx_lastname_firstname(last_name,first_name);

INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (1, NULL, '698', 'liu', 'F', NULL); INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (2, NULL, 'd99', 'zheng', 'F', NULL); INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (3, NULL, 'e08', 'huang', 'F', NULL); INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (4, NULL, '59d', 'lu', 'F', NULL); INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (5, NULL, '0dc', 'yu', 'F', NULL); INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (6, NULL, '989', 'wang', 'F', NULL); INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (7, NULL, 'e38', 'wang', 'F', NULL); INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (8, NULL, '0zi', 'wang', 'F', NULL); INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (9, NULL, 'dc9', 'xie', 'F', NULL); INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (10, NULL, '5ba', 'zhou', 'F', NULL);

4.4.1 索引条件下推（ICP）相关命令

查看ICP命令：show variables like 'optimizer_switch';默认是开启状态index_condition_pushdown=on

关闭命令：set optimizer_switch='index_condition_pushdown=off';
开启命令：set optimizer_switch='index_condition_pushdown=on';
关闭ICP后，执行SQL就不会出现索引条件下推了。
执行计划Extra字段包含Using index condition表示使用了索引条件下推，Using Where 代表从存储引擎取回的数据不全部满足条件，需要在 Server 层过滤。

4.4.2 两种执行方式

执行查询语句：select * from employees where last_name='wang' and first_name LIKE '%zi' ;

4.4.2.1 方式一没有使用索引条件下推

根据联合索引查出所有姓wang的二级索引数据，然后回表，到主键索引上查询全部符合条件的数据（3条数据）。然后返回给Server层，在Server层过滤出名字以zi结尾的员工。

4.4.2.2 方式二使用索引条件下推

根据联合索引查出所有姓wang的二级索引数据（3个索引），然后从二级索引中筛选出first_name以zi结尾的所有（1个索引），然后再回表，到主键索引上查询全部符合条件的数据（1条数据），返回给Server层。

4.4.2.3 两种方式比较

方式二使用索引条件下推查询更优。索引条件下推（Index Condition Pushdown），5.6 以后完善的功能。只适用于二级索引。ICP 的目标是减少访问表的完整行的读数量从而减少 I/O 操作。

注意：索引的比较是在存储引擎进行的，数据记录的比较，是在Server层进行的。而当first_name的条件不能用于过滤时，Server层不会把first_name的条件传递给存储引擎，索引读取了两条没有必要的记录。这时候，如果满足last_name='wang'的记录有100000条，就好有9999条没有必要读取的记录。

4.5 复合主键

主键有唯一约束。当多个字段组合成唯一标识的时候，创建复合主键。通过复合主键，减少了表的数量，不是一个group一张表。

4.5.1 复合主键的特点

以为复合主键需要存储多个字段的值，相对于单列主键来说要消耗更多的存储空间。
联合主键包含多个列的时候，不允许所有的字段都相同。因为判断是否重复更复杂（代码中重新hashCode和equals也是）。
表结构修改或者数据迁移会更加困难。
如果目的是唯一，限制唯一性，可以直接拼接两个字段的内容，比如ADB1213DFS,BDB1677DFS。或者用唯一索引Unique key也可以实现。
如果目的不是为了限制唯一性，或者有其他检查唯一性的方法，用自增ID之类的无业务意义的字段作为主键更合适，自增ID在插入数据时，一起的页分裂和合并更少。

举例：在一张表中，有一个group，一个name，group+name才是一条数据的唯一标识。

create table `task`(
    `group` varchar(255) not null,
    `name` varchar(255) not null,
    primary key (`group`,`name`)
);

5. 索引的创建与使用

因为索引对于改善查询性能的作用是巨大的，索引我们的目标是尽量使用索引。

5.1 索引的创建

5.1.1 创建索引准则

在用于where判断order排序和join的(on)字段上创建索引。
索引的个数不要过多（浪费空间，更新变慢）。
区分度低的字段，例如性别，不要建索引（离散度太低，导致扫描行数过多）。
频繁更新的值，不要作为主键或者索引（会引起B+Tree数据结构大量调整浪费很多计算性能，造成页分裂合并）。
组合索引把散列性高（区分度高）的值放在前面（增加检索效率）。
创建复合索引，而不是修改单列索引（及创建联合索引又创建单列索引（冗余索引），那么选择联合索引）。
过长的字段，怎么建立索引？（如果匹配前面一部分内容就可把数据过滤了，那么选择建立前缀索引）。
不建议用无序的值例如身份证、UUID（因为无序，随机插入，造成频繁的页分裂合并）。

5.2.1 前缀索引

计算前六个字符的离散度 count(distinct(substr(content,1,6)))
用content字段前6个字符建立索引

create table pre_test(
	content varchar(200) default null,
	key pre_idx (content(6))
)engine=innodb default charset=utf8mb4;
--前面六个字符的离散度
select count(distinct(substr(content,1,6))),count(*) from pre_test;

5.2 什么时候用不到索引

索引裂伤使用函数（replace、substr、concat、sum、count、avg、表达式）
字符串不加引号，出现隐式转换
like条件中前面带'%'（不符合索引的最左前缀原则。不绝对，如果索引下推还是可以使用到索引）
负向查询<>、!=、not in ...等

5.5.1 最左前缀原则

5.5.1.1 符合最左前缀，使用索引

5.5.1.2 不符合最左前缀，无法使用索引

5.5.1.3 不符合最左前缀，符合索引下推，使用索引（优化器作用）

5.5.2 负向查询（不一定都用不到索引）

跟很多因素有关，比如表结构类型、表数据或者数据库版本、优化器版本、字段散列度，都有关系。负向查询有可能得到不一样的情况。有时候用到索引有时候用不到索引。是优化器决定的。

5.5.2.1基于成本的优化器（Cost Base Optimizer）

基于成本的优化器（Cost Base Optimizer），不是基于规则（Rule-Based Optimizer），也不是基于语义。怎么样开销小就怎么来（比如：I/O、CPU等）。在MySQL里面用不用索引是根据它的成本计算模型来决定的。我们学索引的规则，只能记一下原则，不应该记细节。

比如：从地点A到地点B
基于成本：选择不会拥堵的线路
基于规则：固定选择距离最短的线路

5.5.2.2 not like 不能使用索引

explain select * from employees where last_name not like 'wang';

5.5.2.3 !=、<>和not in在某些情况下可以使用索引

explain select * from employees where emp_no not in (1);
explain select * from employees where emp_no <> 1;

docs.oracle.com/cd/B10501_0…

dev.mysql.com/doc/refman/…

5.5.3 隐式类型转换用不到索引

两个参数只是有一个是null时，比较的结果也是null，特殊情况是使用<、=、>对两个null做比较时会返回1，这两种情况都不需要左类型转换。
两个参数都是字符串，会按照字符串来比较，不做类型转换。
两个参数都是整数，安装整数来比较，不做类型转换。
十六进制的值和非数字做比较时，会被当做二进制串。
有一个参数是TIMESTAMP或DATETIME，并且另外一个参数是常量，常量会被转换为timestamp。
有一个参数是decimal类型，如果另外一个参数是decimal或者整数，会将整数转换为decimal后进行比较。
所有其他情况下，两个参数都会被转换为浮点再进行比较。

把数字和字符相比较的时候，MySQL不能用索引很快地找到对应的值。如果一个字段是创建了索引的字符类型字段，这个SQL是无法用到索引：select * from tbl_name where str_col=1;

按照第7条转换规则

不以数字开头的字符串都将转换为0.如' abc'、'a123bc'、'abc123' 都会转化为0。
以数字开头的字符串转换时会进行截取，从第一个字符截取到第一个非数字内容为止。如'123abc'会转换为123,'0123abc'会转换为012也就是12，'5.3asd454d'会转换为5.3，其他同理这种写法会把str_col='1a'、str_col='01'、str_col='1'的值都查出来。

6. InnoDB逻辑存储结构

MySQL的存储结构分为5级：表空间、端、簇、页、行。

6.1 表空间 Table Space

表空间可以看做InnoDB存储引擎逻辑结构的最高层，索引的数据都存放在表空间中。分为：系统表空间、独占表空间、通用表空间、临时表空间、Undo表空间。

6.2 段 Segment

表空间是各个段组成的，常见的段有数据段、索引段、回滚段等，段是一个逻辑的概念。一个ibd文件（独立表空间文件）里面会由很多个段组成。

创建一个索引会创建两个段，一个是索引段：leaf node segment，一个是数据段：non-leaf node segment。索引段管理非叶子节点的数据。数据段管理叶子节点的数据。也就是说，一个表的段数，就是索引的个数乘以2。

6.3 簇 Extent

一个段（Segment）又由很多簇（也可以叫区）组成，每个区的大小是1MB（64个连续的页，每个页就是16KB）。每一个段至少会由一个簇，一个段所管理的空间大小是无限的，可以一直扩展下去，但是扩展的最小单位就是簇。

6.4 页 Page

为了高效管理物理空间，对簇进一步细分，就得到了页。簇是由连续的页（Page）组成的空间，一个簇有64个连续的页。（1M/16KB=64）。这些页面在物理上和逻辑上都是连续的。

跟大多数数据库一样，InnoDB也有页的概念（页可以称为块），每个页默认16KB。页是InnoDB存储引擎磁盘管理的最小单位，通过innodb_page_size设置。

一个表空间最多拥有2^32个页，默认情况下一个页的大小为16KB，也就是说一个表空间最多存储64TB的数据。

注意，文件系统中，也有页的概念。操作系统和内存打交道，最小的单位是也Page。文件系统的内存页通常是4KB。

6.4.1 查看页的size

show variables like 'innodb_page_size';

假设一行数据大小是1K，那么一个数据页可以放16行这样的数据。举例：一个页放3行数据。

6.4.2 页的增加

往表中插入数据时，如果一个页面已经写完，产生一个新的叶页面。如果一个簇的所有的页面都被用完，会从当前页面所在段分配一个簇。

6.4.3 页的分裂

如果数据不是连续的，往已经写满的页中插入数据，会导致叶页面分裂：

6.5 行 Row

InnoDB存储引擎是面向行（row-oriented），也就是说数据的存放按行进行存放。 dev.mysql.com/doc/refman/…

6.5.1 InnoDB存储引擎支持四种行格式

冗余行格式（REDUNDANT）、紧凑行格式（COMPACT）、动态行格式（DYNAMIC）、压缩行格式（COMPRESSED）

Antelope or Barracuda，是InnoDB内置的文件格式，有两种：
冗余行格式（REDUNDANT row format）
紧凑行格式（COMPACT row format）
Barracuda，是InnoDB plugin 支持的文件格式，新增了两种格式：
动态行格式（DYNAMIC）
压缩行格式（COMPRESSED）
5.6默认行格式：紧凑行格式（COMPACT）
5.7默认行格式：动态行格式（DYNAMIC）

文件格式	行格式	描述
Antelope（Innodb-base）	ROW_FORMAT=COMPACT ROW_FORMAT=REDUNDANT	Compact 和 redumdant 的区别在就是在于首部的存存内容区别。compact 的存储格式为首部为一个非 NULL 的变长字段长度列表redundant 的存储格式为首部是一个字段长度偏移列表（每个字段占用的字节长度及其相应的位移）。在 Antelope 中对于变长字段，低于 768 字节的，不会进行 overflow page 存储，某些情况下会减少结果集 IO
Barracuda (innodb-plugin)	ROW_FORMAT=DYNAMIC ROW_FORMAT=COMPRESSED	这两者主要是功能上的区别功能上的。另外在行里的变长字段和 Antelope 的区别是只存 20 个字节，其它的 overflow page 存储。另外这两都需要开启 innodb_file_per_table=1

innodb_file_format 在配置文件中指定；row_format 则在创建数据表时指定。

show variables like "%innodb_file_format%";
SET GLOBAL innodb_file_format=Barracuda;

在创建表的时候可以指定行格式

create table tf1 (c1 int primary key)
row_format=compressed
key_block_size=8;

6.5.2 查看行格式

show table status like <'表名称'>;