MySQL系列---索引详解

1. 背景

索引是一种数据结构：帮助数据库高效查询数据库表中数据的一种排序的数据结构。

这个数据结构中存储着索引的值和包含这个值的数据所在行的磁盘地址。

在数据十分庞大的时候，索引可以大大加快查询的速度，因为通过索引可以精准的查询到所需的数据，而不用全表扫描，mysql是基与磁盘，全表扫描并不快，这就是索引存在的意义

使用索引时先通过索引表直接找到该行数据对应的磁盘地址，然后获取相应的数据。

索引作为一种数据结构，到底采用哪一种数据结构实现的呢？常见数据结构如下：

• 二叉查找树(简称：BST -Binary Search Tree )

• 平衡二叉树(简称：AVL Tree -Balanced Binary Search Trees )

• 多路平衡查找树(简称：B Tree -Balanced Tree )

• 加强版多路平衡查找树(简称：B+ Tree)

• HASH

目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构。

MySQL索引的底层数据结构是B+ Tree
在MySQL中，索引属于存储引擎级别的概念。因为不同存储引擎对索引的实现方式是不同的。

虽然MyISAM和InnoDB两个存储引擎都是使用B+ Tree作为索引结构，但具体实现方式却截然不同
了解 B+ Tree之前必须先了解二叉查找树、平衡二叉树和多路平衡查找树，B+ Tree便是由这些树逐步演化而来的。

2. 索引概念

2.1. 索引分类

2.1.1. 从数据结构角度区分

查看：客户端–>数据库表–>索引–>索引方法

• B+ Tree索引(时间复杂度：O(log(n)))
• HASH索引

1. 二者各有千秋，tree擅长范围查询和排序（between and），hash擅长精准查询（=，in ，<=>），前者需要从根节点到枝节点，最后才能访问到页节点，需要经历多次IO，IO是查询中最耗费性能的操作，后者可以一次定位到数据所在地。
   <=>的意思和=相同，区别就是 判断null时可以使用前者，后者不能用来判断null值。因此在进行 … WHERE col_a <=> ? …查询中，这里的占位符有可能是常量也有可能是NULL，当使用<=>运算符时，即可对查询语句不做任何修改。

2. 只有Memory存储引擎显式支持(人为创建)HASH索引。
   InnoDB存储引擎是支持HASH索引的，只不过支持的是自适应的哈希索引。InnoDB会根据表的使用情况，自动为表创建HASH索引，不能人为创建。
   示例：
   当我们在一个InnoDB引擎的表中创建HASH索引的时候，只要一保存，就会自动变为创建B-Tree类型的索引。
   虽然这里显示的使用了USING HASH，但在show index查询索引类型，会发现仍然还是B-Tree索引，其底层不支持人为显式的创建HASH索引。

   CREATE TABLE `user_info` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `name` varchar(16) DEFAULT NULL,
     `phone` char(11) DEFAULT NULL,
     PRIMARY KEY (`id`),
     KEY `idx_name` (`name`) USING HASH
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
   

储存引擎支持索引情况：

其实有此设计并不是hash索引过时，它在定值查询中仍然是统治地位，只是综合考虑，B+tree作为tree的进阶，性能更加全面，后续一看便知。

2.1.2. 从物理存储角度区分

• 聚集索引(clustered index)
  索引项的排序方式和表记录数据排序方式一致。
  也就是说聚集索引的顺序就是数据的物理存储顺序。它会根据聚集索引键的顺序来存储表中的数据，即对表的数据按索引键的顺序进行排序，然后重新存储到磁盘上。
  因为数据在物理存放时只能有一种排列方式，所以显而易见：一个表只能有一个聚集索引。

• 非聚集索引(non-clustered index)
  索引顺序与物理存储顺序不同。

2.1.3. 从逻辑角度区分

• 主键索引
  一种特殊的唯一索引，字段不允许有空值。和唯一索引的唯一区别就是其字段是否为主键，也就说主键索引其实就是唯一索引。

• 普通索引或者单列索引
  非唯一的索引，没有任何限制。

• 唯一索引
  字段的值不能重复，允许空值。

• 全文索引
  用大文本对象的列构建的索引，只能作用在CHAR、VARCHAR、TEXT类型的字段上。

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

• 联合索引
  多个字段组成一个索引，这多个列中的值不允许有空值，遵循最左匹配原则。
  最左匹配原则：把最常用的作为检索或排序的列放在最左，以最左边的为起点任何连续(不能中断)的索引都可匹配上(必须按照从左到右的顺序匹配)。因此创建分区表时应该将分区字段当做联合主键的第一个，因为分区字段必然是高频查询条件。

创建索引时，需要确保该索引是应用在 SQL 查询语句的条件上(作为WHERE子句的条件)。

2.1.4. 从主键角度区分

• 主键索引
• 辅助索引

二者的区别就是索引的键值是否为主键。主键索引只能有一个，辅助索引可以有很多个。
主键索引中存储的是索引字段（主键）和全部数据，辅助索引中存储的索引字段（非主键）和主键索引字段。这就是查询时同样使用索引是否回表查询的根源。

2.1.5. 从查询角度区分

• 覆盖索引
  select 查询的字段包含在用到的索引中，即只需要在辅助索引中就可以查找到值，而不用去访问主键索引拿到所有的数据。
  这种索引包含（亦称覆盖）所有需要查询的字段的值，我们称这种索引为覆盖索引。
  当发起一个被索引覆盖的查询时，在explain的Extra列可以看到Using index的标识。

示例：当你对a字段建立索引，a字段不是主键。
select a from table where a = num ，此时就是覆盖索引
select * from table where a = num ,此时便不是覆盖索引。
因此，是否是覆盖索引取决于查询字段，而非索引

2.2. 创建索引

1、在创建表时添加索引
CREATE TABLE 表名 (
        字段名1  数据类型 [完整性约束条件…],
        字段名2  数据类型 [完整性约束条件…],
        [UNIQUE | FULLTEXT | SPATIAL ]  INDEX | KEY
        [索引名]  (字段名[(长度)]  [ASC |DESC]) 
        );

例子：
CREATE TABLE `user_info` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增Id',
  `name` varchar(64) NOT NULL DEFAULT 'default' COMMENT '用户登录账户',
  `password` varchar(512) NOT NULL DEFAULT 'default' COMMENT '密码',
  `email` varchar(64) NOT NULL DEFAULT 'default' COMMENT '邮箱地址',
  `enabled` tinyint(4) DEFAULT NULL COMMENT '是否有效',
  `desc` text NOT NULL COMMENT '描述',
   PRIMARY KEY (`Id`),//主键索引
   KEY `index_name` (`name`),//普通索引
   UNIQUE KEY `unique_index_email` (`email`) USING BTREE， //唯一索引
   FULLTEXT KEY `text_index_decs` (`desc`) //全文索引 
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

2、在创建表后添加索引
CREATE  [UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名 
                     ON 表名 (字段名[(长度)]  [ASC |DESC]) ;                
例子：                     
CREATE INDEX index_name ON user_info(name);

注意：

1. 不支持在创建表后使用CREATE INDEX 语句创建主键索引，必须用ALTER TABLE 创建。

2. INDEX | KEY 为必选参数，用于指定字段为索引，只需二选一即可。
   使用key和index创建索引是一样的，只不过在SQL体现上都是KEY，
   通过SHOW INDEX FROM table可知。
   也就是说key和index通常是一样的，两种方式是为了实现和其他数据库的兼容。
   如果创建的是约束，比如PRIMARY KEY，FOREIGN KEY这种，只能用key。

2.3. 修改(添加)索引

--修改
ALTER TABLE 表名 ADD  [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                              索引名 (字段名[(长度)]  [ASC |DESC]) ;
例子：
ALTER TABLE table_name ADD INDEX index_name(column);

-- 添加主键，意味着索引值必须是唯一的，且不能为null
ALTER TABLE 表名 ADD PRIMARY KEY (主键名)

2.4. 删除索引

1、方式一
DROP INDEX 索引名 ON 表名;

2、方式二
alter table 表名 drop index 索引名 ;
alter table 表名 drop primary key;

注意：

删除主键时只需指定PRIMARY KEY(一个表只有一个主键)，但在删除索引时，你必须指定索引名。

2.5. 查看索引

SHOW INDEX FROM 表名;

2.6. 常用关键字

修改类型	增删改查关键字
结构级	create、drop、alter、show
数据级	insert、delete、update、select

3. 索引结构演化及实现原理

3.1. 二叉查找树（Binary Search Tree，简称BST）

3.1.1.特点

左子节点的值都要小于跟节点，右子节点的值都要大于等于跟节点。

3.1.2. 缺点

如果是有序的插入，那么BST树就会变成一个线性的链表(斜树，不平衡树)，深度会很大，导致查询会很慢。
所以不适合作为MySQL的索引结构。

如图所示：如查找6，需要经过6次磁盘IO。

3.2. 平衡二叉树（Balanced Binary Search Trees，简称AVL Tree）

AVL是发明者的名字简写。

3.2.1. 特点

基于二叉树，但任何节点的左右子树的深度差绝对值不能超过1。

如下图：

• 顺序插入，形成AVL Tree：
  
• 非AVL Tree：
  

3.2.2. 维持平衡

如果在AVL树中进行插入或删除节点，可能导致AVL树失去平衡。

3.2.2.1. 失去平衡类型

失去平衡的二叉树可以概括为四种类型：LL型、RR型、LR型、RL型。

• LL型：LeftLeft，也称“左左”。插入或删除一个节点后，根节点的左子节点的左子节点还有非空节点，导致根节点的左子树高度比右子树高度高2，AVL树失去平衡。
  
• LR型：LeftRight，也称“左右”。插入或删除一个节点后，根节点的左子节点的右子节点还有非空节点，导致根节点的左子树高度比右子树高度高2，AVL树失去平衡。
  
• RR型：RightRight，也称“右右”。插入或删除一个节点后，根节点的右子节点的右子节点还有非空节点，导致根节点的右子树高度比左子树高度高2，AVL树失去平衡。
  
• RL型：RightLeft，也称“右左”。插入或删除一个节点后，根节点的右子节点的左子节点还有非空节点，导致根节点的右子树高度比左子树高度高2，AVL树失去平衡。
  
  AVL树失去平衡之后，可以通过左、右旋转使其恢复平衡。

3.2.2.2. 旋转方式

平衡二叉树保持平衡的方式就是旋转，左旋、右旋、多次旋转，最优旋转方式

• LL型的旋转：LL失去平衡的情况下，可通过一次右旋让AVL树恢复平衡。

1. 将根节点的左子节点作为新根节点；

2. 将新根节点的右子节点作为原根节点的左子节点；

3. 将原根节点作为新根节点的右子节点。

• RR型的旋转：RR失去平衡的情况下，可通过一次左旋让AVL树恢复平衡

1. 根节点的右子节点作为新根节点；

2. 将新根节点的左子节点作为原根节点的右子节点；

3. 将原根节点作为新根节点的左子节点。

• LR型的旋转：LR失去平衡的情况下，需要进行两次旋转。

1. 围绕根节点的左子节点进行左旋；

2. 围绕根节点进行右旋。

• RL型的旋转：RL失去平衡的情况下，也需要进行两次旋转。

1. 围绕根节点的右子节点进行右旋；

2. 围绕根节点进行左旋。

3.2.3. 关于平衡

其实归根结底只有左左和右右。所谓的左右右左，不过是左左和右右的组合，
从其维持平衡的旋转方式也可以看出来，左左右右只需要一次旋转就可以重新平衡，而左右和右左则需要两次旋转才可以重新达到平衡。

3.2.4. 缺点

假设我们采用AVL Tree方式存储索引，如下图：

一个节点(磁盘块)由三部分组成：

• 键值

• 数据磁盘地址

• 子节点引用

索引检索数据流程：

先将磁盘块中的数据加载到内存，然后在内存中比较数值的大小。

我们看下查找索引值为3这条数据的过程：

1. 首先从索引结构中拿到磁盘块1，加载到内存中比较，发现比查找的数据大；

2. 然后找到左子节点的磁盘块2，加载到内存中比较，发现比查找的数据小；

3. 最后找到左子节点的右子节点磁盘块5，加载到内存中比较，最终获取到数据。

这里检索经历3次IO，如果有上千万条数据，那么，IO时间是无法估量的。

系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来。

我们读取一次节点就发生一次磁盘IO，将磁盘数据加载进内存。

在InnoDB中，IO操作最小单位是页（page），默认是16kb，也就是16384byte。

一个节点加载到内存，会分配一个16kb大小的内存容量。

可通过参数innodb_page_size修改页的容量大小，在MySQL中可通过如下命令查看页的大小：

show variables like 'innodb_page_size';

由上图可知，一个节点大概只会存储几十byte，是远远不够分配的16kb的。因此，会产生很大的内存浪费。

因此，使用AVL Tree每次IO都会浪费大量的内存空间，而且放的节点越多，IO次数也越多，浪费的内存空间也就越大，所需的时间也就越多。

那么如何解决该问题？究其根本，是IO次数太多和IO获取的数据量太小导致的问题，树的深度决定IO的次数， 数的宽度决定IO获取的数据量；所以解决问题的根本在于减少IO次数增加IO获取数据量，即降低树的深度增加树的宽度。因此，解决方案就是多路平衡查找树

3.3. 多路平衡查找树（Balanced Tree，简称B Tree或B-Tree）

B Tree是为磁盘等外存储设备设计的一种平衡查找树，可以让系统高效的找到数据所在的磁盘块。

InnoDB以页为基本单位把磁盘数据读入到内存中，在查询数据时，一个页中有更多数据能有助于定位数据记录的位置，将会减少磁盘I/O次数，提高查询效率。

3.3.1. 特点

一个磁盘块由三部分组成：

• 键值(key)：表记录中的主键；

• 数据(data)：主键所在表记录行中的所有数据(InnoDB中data存储的是该字段值对应的行值，MyISAM存储的是磁盘地址)；

• 指针( p)：子节点的磁盘地址信息；
  一个节点中拥有的关键字（包含key，data两部分）个数称为关键字数。

一个节点拥有的子树数量称为度。

关键字数：N。

度(Degree)：D = N+1。

一棵m阶B树满足如下条件：

• 每个节点最多有m-1个关键字，至少有m/2个关键字；

• 每个节点(非叶子结点)最多有m个子节点，至少有m/2 +1 个子节点；

• 每个节点中的关键字都按照从小到大的顺序排列，每个关键字的左子树中的所有关键字都小于它，而右子树中的所有关键字都大于它；

• 所有叶子节点都位于同一层，具有相同的深度，且指针为空；

• 索引键值可重复。
  

每个节点占用一个磁盘块，一个节点中有两个升序排序的关键字数和三个指向子树根节点的指针。

以查找关键字14为例：

1. 首先找到磁盘块1，加载数据到内存中，发生第一次磁盘I/O操作；

2. 比较关键字14小于20，找到磁盘块1最左边的指针；

3. 根据指针找到磁盘块2，加载数据到内存中，发生第二次磁盘I/O操作；

4. 比较关键字14在区间（10，15）之间，找到磁盘块2中间指针；

5. 根据指针找到磁盘块6，加载数据到内存中，发生第三次磁盘I/O操作；

6. 在磁盘块6中的关键字列表中找到关键字。

由上面可知，检索到目标值需要经过3次磁盘IO操作，和3次内存查找操作。

由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。

而3次磁盘IO操作是影响整个B Tree查找效率的决定因素(内存操作速度非常快，相对IO操作可忽略不计)。

对于B-Tree来说，N越大，m就会越小，IO就会越少，查询就会越快，但是插入也会越复杂。

3.3.2. 维持平衡

如果在B 树中进行插入或删除节点，可能导致失去平衡。

那么，B树是如何实现一个节点存储多个数据并且还保持平衡的呢？

3阶的B树，也就是一个节点只能放两个关键字，如果要插入第三个数据，将会产生分裂，反之合并。

结论：通过分裂和合并来保证B树的平衡。

3.3.3. 缺点

那么，B Tree是否就是最合适的呢？

前面我们说过，一个节点固定分配内存为16KB，非叶子节点既存key也存data。

在整个节点内存空间固定的情况下，如果data越大，那么所能容纳的关键字个数就越少。当数据量大的时候，树依然会比较高，也就会增加磁盘IO操作。

基于以上缺点，在B Tree基础上进行优化，衍生出B+Tree

3.4. 加强版多路平衡查找树（B+ Tree）

在B+ Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以加大每个节点存储的key值数量，降低B+Tree的高度。B+ Tree就是为了拆分索引数据与业务数据的平衡多叉树

3.4.1. 特点

• 非叶子节点的关键字数(不存在data)等于度，即节点上有N个键值，就有N个子节点；

• 非叶子节点只存储键值、指针信息；

• 只有叶子节点才存储表记录数据，整个叶子节点包含树中所有的记录数据；

• 叶子节点用指针连接的(MySQL是双向指针，形成一个有序的环状链表）；
  

我们可以推算一下B+ Tree相关数据，窥视其中的优势：

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4字节）或BIGINT（占用8字节），指针类型也一般为4或8个字节。

假设存储BIGINT类型，InnoDB中的指针大小为6字节，这样一个索引就是14个字节。

每个节点16kb（16384字节），那么一个节点就可以存放16384/14=1170个索引，也就有1170个子节点。

那么，子节点就可以存放1170*1170=1368900个索引，也就有1368900个子节点。（页是最小IO大小，不是最大，因此目的就是增加第一次获取的数据量，所以要堆满16KB，后续可以根据叶节点拉取很多的数据量，不局限于16KB）

假设一条记录1KB，一个叶子节点16k可以存放16条记录，那么1368900个叶子节点共可以存放1368900*16=21902400条记录，也就是两千多万条记录，树的深度只有2，查找数据只需3次IO。

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2 - 4层，再大再高就要分库分表了。

MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1 - 3次磁盘I/O操作。

不仅如此，B+ Tree叶子节点还是一个有序的链表，如果我们进行范围的查找(如where id between 10 and 60)，可以沿着叶子节点的链表指针一次查找到所需数据。如果是B Tree，它每次都会从根节点开始遍历查找。

索引是怎么支撑千万级表的快速查找？

B+ Tree结构的索引，3次磁盘IO就可以查找千万级的数据。

内存中查找定位索引元素，相对磁盘IO，可忽略不计。

3.4.2. 总结

• mysql数据存储在磁盘，而磁盘处理数据很慢；

• 提高磁盘性能主要通过减少IO次数，以及单次IO有效数据量；

• 索引针对的是单个字段或部分字段，字段的数据量本身比一条记录的数据量要少的多，这样即使通过扫描的方式查询索引也比扫描数据库表本身快的多；

• 索引通过树数据的有序和二分查找，大大缩小查询范围；

• 索引通过多阶使树的结构更矮胖，从而减少IO次数；

• 索引通过B+ Tree，把业务数据与索引数据分离，提高单次IO有效数据量，从而减少IO次数；

• 非叶子节点只存key及指针，不存data数据，节点可以存储更多的索引元素，使得查询的IO次数更少；

• 所有的关键字数据(key,value)都存在叶子节点上，所以每次查找的次数都相同，查询速度要比B Tree更稳定；

• 所有的数据都存在叶子节点，数据是按顺序存储的。叶子节点之间用双向链表连接，节点内数据间用单向链表连接，使得范围查找、排序查找更加高效；

• 遍历整棵树只需遍历所有的叶子节点即可，而不是像B Tree一样，需要对每一层进行遍历，全节点遍历更快。

3.4.3. 缺点

• 主键不是有序递增的，导致每次插入数据产生大量的数据迁移和空间碎片；

• 即使主键是有序递增的，如果写入的数据比较离散，那么寻找写入位置时，子节点有很大可能性不会在内存中，最终产生大量随机写，导致性能下降；

• 由于B Tree的每一个节点都包含key和data，因此，离根节点更近的节点访问会更迅速，而B+ Tree需要查找到叶子节点

3.5. 扩展-B*树：

B*树是B+树的变体，在B+树的非根和非叶子结点再增加指向兄弟的指针。

B*树分配新结点的概率比B+树要低，空间使用率更高。

4. 存储引擎索引实现

在MySQL中，索引属于存储引擎级别的概念。

不同存储引擎对索引的实现方式是不同的，MySQL中最常见的两种存储引擎分别是MyISAM和InnoDB，分别实现了非聚集索引和聚集索引。所以只对比这两种

4.1. 聚集索引与非聚集索引

前面已经解释过两个索引，这里再次加深一下。

• 聚集索引(clustered index)：

  索引列的排序方式和表数据排序方式一致。

  也就是说索引的顺序就是表数据的物理存储顺序。

  它会根据索引键值的顺序来存储表中的数据，即对表中数据按索引键值的顺序进行排序，然后重新存储到磁盘上。

  因数据在物理存放时只能有一种排列方式，所以一个表只能有一个聚集索引。

• 非聚集索引(non-clustered index)：

  索引列的逻辑顺序与表数据的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

• 两者区别：

  • 聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个；

  • 聚集索引存储表记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续；

  • 聚集索引物理存储按照索引排序，索引的键值逻辑顺序决定了表数据行的物理存储顺序。

  • 非聚集索引物理存储不按照索引排序，仅仅只是对数据列创建相应的索引，不影响整个表的物理存储顺序。

  • 索引是通过二叉树的数据结构来描述的，可以这么理解聚簇索引：索引的叶子节点就是数据节点。而非聚簇索引的叶子节点仍是索引节点，只不过有一个指针指向对应的表数据。

• 优缺点：

  • 聚集索引插入数据时速度要慢，因为时间花费在物理存储的排序上，也就是首先要找到位置，然后插入；

  • 聚集索引查询数据比非聚集索引的速度更快；

  • 聚集索引order by时比非聚集索引速度更快，特别是在小数据量情况下。而数据量如果很大的话(如10万以上)，则二者的速度差别不明显。

4.2. 主键索引与辅助索引

在索引的分类中，按照索引的键值是否为主键来分为“主键索引”和“辅助索引”。使用主键键值建立的索引称为“主键索引”，其它的称为“辅助索引”。

因此，主键索引只能有一个，辅助索引可以有很多个。

4.3. MySQL数据文件存储

MySQL数据库会在本地磁盘data目录下面建立一个以数据库为名的文件夹，用来存储数据库中的表文件数据。不同的数据库引擎，每个表的扩展名也不一样。

通过客户端命令，定位数据文件的位置：

show global variables like "%datadir%";

可知，本地磁盘目录和数据库客户端中展示的库表是一一对应的。

.frm：主要存储表结构的定义信息。当数据库崩溃时，用户可以通过frm文件来恢复数据表结构。
.ibd：存储的是索引数据和表记录数据。

.frm：主要存储表结构的定义信息。当数据库崩溃时，用户可以通过frm文件来恢复数据表结构。
.MYD：存储表数据。
.MYI：存储索引数据。

4.4. MySQL级存储结构

MySQL 的存储结构分为5级:表空间、段、区、页、行。从左往右，依次细分。

为了高效管理物理空间，对簇(区)进一步细分，就得到了页。

簇是由连续的页(Page)组成的空间，一个簇中有64个连续的页。(1MB/16KB=64)。这些页面在物理上和逻辑上都是连续的。

跟大多数数据库一样，InnoDB也有页的概念(也可以称为块)，每个页默认 16KB。

页是InnoDB存储引擎磁盘管理的最小单位，通过 innodb_page_size 设置。

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

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

4.5. MyISAM 索引实现

MyISAM引擎使用B+ Tree作为索引结构，叶子节点的data存放的是表记录数据的物理地址，采用的是非聚簇索引。

• MyISAM中索引文件和表数据文件是分离的。

• 即检索数据先从索引文件(MYI)中获取到表数据行的磁盘地址，然后根据磁盘地址从表数据文件(MYD)中获取到表数据行。

MyISAM索引原理图如下 :

id作为主键索引，name 作为辅助索引
总结：

• MyISAM存储引擎采用的是非聚簇索引，非聚簇索引的主键索引和辅助索引是一样的。只是主键索引不允许重复，不允许空值， key 是唯一的，而辅助索引的 key 可以重复。叶子结点的data只存储表数据的物理地址；

• 非聚簇索引的数据表和索引表是分开存储的；表数据存储在独立的地方，对于表数据来说，这两个键值没有任何区别。

• 即在MyISAM中，主键索引和辅助索引在结构上没有任何区别，都可以直接获取表数据而不需经过另外的索引检索。

• 非聚簇索引中的数据是根据数据的插入顺序来保存的。因此，非聚簇索引更适合单个数据的查询，插入顺序不受键值影响；

• 只有在MyISAM中才能使用FULLTEXT索引(mysql5.6以后InnoDB也支持全文索引)。

4.6. InnoDB 索引实现

InnoDB的表数据文件本身就是索引文件，叶子节点data保存了完整的表数据记录，采用的是聚集索引。

4.6.1. 主键索引

InnoDB使用的是聚集索引，将主键组织到一棵B+树中；主键索引中，叶子节点的data中存储了完整的表数据。

为什么InnoDB表必须有主键？

InnoDB的表数据文件本身要按主键聚集，选择主键作为聚集索引，通过主键组织一个索引结构，所以InnoDB要求表必须有主键(MyISAM可以没有)。

InnoDB对聚簇索引的处理？

1. 第一种情况：用户定义了主键，那么InnoDB会使用主键作为聚簇索引；
2. 第二种情况：用户没有定义主键，那么InnoDB会使用第一个非空的唯一索引作为聚簇索引；
3. 第三种情况：用户既没有定义主键，也没有定义唯一索引，那么InnoDB会自动生成一个不可见的ROW_ID的列名为GEN_CLUST_INDEX的聚簇索引，该列是一个6字节的自增数值，随着插入而自增。

为什么推荐 InnoDB 采用整型自增字段做表的主键？

• 聚集索引的顺序和磁盘中数据的存储顺序是一致的，即只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。

• 非自增主键在插入新记录时，数据文件为了维持B+Tree的特性(如顺序结构)而频繁的分裂调整(达成顺序)，这样效率就十分低效。

• 如果使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。这样会形成一个紧凑的索引结构，磁盘碎片少。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加额外开销维护索引。

• 整型可以直接进行大小比较，如字符串做主键，在比较大小的时候，会先将每个字符转为ASCII码，然后码表顺序比较，效率低下，而且比整型占用空间更大。

4.6.2. 辅助索引

InnoDB辅助索引的叶子节点中data只保存了主键值，不是全部的表数据。

辅助索引查找需要检索两遍索引：首先检索辅助索引获得主键，然后到主键索引中检索主键，获取对应表数据。

为什么不建议使用过长的字段作为主键？

• 辅助索引也包含主键列，如果主键定义的比较大，辅助索引也将变大。

• InnoDB不会压缩索引。

为什么辅助索引的叶子节点存储的是主键值？

1. 减少数据修改
   当数据库表进行DML操作时，同一行记录的页地址会发生改变。如果每个索引都存储date的磁盘地址，则修改量很大，且可能出现数据不一致的情况。
2. 节省存储空间
   Innodb表数据已经存储到主键索引所在的B+树上了， 如果辅助索引再保存一份数据，虽然提升了查询性能，但是需要更多的空间来存储冗余的数据。

总结：

• 聚集索引的主键索引的叶子结点的data存储的是表数据，辅助索引的叶子结点的data存储的只是主键键值。因此主键的值长度越小越好，类型越简单越好；

• 聚集索引的表数据和索引数据存储在一起的；

• 聚集索引不是一定是主键，但默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空列代替。如果没有这样的索引，InnoDB会隐式定义一个主键row-id(6字节)来作为聚集索引；

• 聚集索引的表数据是根据主键的顺序来保存的，推荐采用整型自增字段做表的主键；

• 键值排序的时候，会先排联合索引的第一个，如果相等，就排序后面字段，以此类推。如果不相等，后面的字段就不会排序了。全局来看，第一个字段是有序的，后面的字段都是无序的，是相对有序的；

• 使用主键索引的时候，更适合使用聚集索引，因为聚集索引只需要查找一次，而非聚集索引在查到数据的地址后，还要进行一次IO查找数据。

• 因此，innodb存储引擎中最好使用主键进行查询，性能极佳。

5. 最左匹配原则

多个字段组成一个索引叫联合索引，这多个列中的值不允许有空值，遵循最左匹配原则

最左匹配原则：

把最常用的作为检索或排序的列放在最左，以最左边的为起点任何连续(不能中断)的索引都可匹配上(必须按照从左到右的顺序匹配)。

能不能用到联合索引跟查询语句后面的条件有关，遇到范围查询(>、<、between、like)就会停止匹配。

联合索引的健值数量有多个，构建B+ Tree只能根据一个值来构建。

因此，数据库依据联合索引最左的字段来构建。

ALTER TABLE ‘table_name’ ADD INDEX idx_union(‘a’,‘b’,‘c’)；

索引树结构如下：

可以看到a从左往右的值是有顺序的，而b的值从左往右是没有顺序的。

在a值相等的情况下，b值又是按顺序排列的，当然，这种顺序是相对的。

这里相当于建立了a，a、b，a、b、c三个索引。b或者c开头是不能使用到索引的。

例如：

where a = 11 and b = 12，a，b字段都可以使用索引，因为在a值确定的情况下，b是相对有序的。

而where a>11 and b = 12，a字段可以匹配上索引，但b值不可以，因为a的值是一个范围，在这个范围中b是无序的。

所以，最左匹配原则遇到范围查询就会停止匹配，剩下的字段都无法使用索引。

5.1. 联合索引应用

5.1.1. 全值匹配查询（精准索引查询）

select * from table_name where a = 23 and b = 18 and c = 'F' 
select * from table_name where b = 36 and a = 11 and c = 'M' 
select * from table_name where c = 'L' and b = 99 and a = 30 

select * from table_name where a = 23 and b = 18
select * from table_name where b = 18 and a = 23

这里SQL没有严格按照定义联合索引的顺序来书写，依然全部用到了索引，这是为什么？

因为Mysql中有查询优化器，会自动优化查询顺序。

SQL语句中字段的顺序不需要和联合索引中定义的字段顺序一致，查询优化器会调整顺序，它会判断sql语句该以什么样的顺序执行效率最高，生成真正的执行计划。

所以只要查询的条件中只要包含最左匹配原则的元素，无论书写顺序怎样，都可以走索引。

5.1.2. 查询条件中未使用索引a（不走索引）

select * from table_name where b = 18 and c = 'F' 
select * from table_name where b = 18
select * from table_name where c = 'F'

查询都没有用到索引，而是全表扫描 。

没有以a作为条件来检索，B+ Tree不知道第一步该查哪个节点，从而需要去全表扫描。通过上面的索引树结构图可知，a是第一个比较因子，必须要先根据a来搜索，进而才能往后继续查询b和c。

5.1.3. 查询条件中未使用索引b（回表查询+范围索引）

select * from table_name where a = 23 and c = 'F' 

如果不连续时，只能用到a列的索引。

B+ Tree第一步用a查找到所有数据，由于下一个字段b的缺失，所以只能把a的主键ID都找到，通过查到的主键ID回表查询相关行，再去匹配c的数据。

回表：

查询字段不能通过辅助索引直接获取到。

需通过辅助索引获取到主键，再从主键索引查询获取所需表数据。

5.1.4. 索引中范围条件右边的列（回表查询+范围索引）

select * from table_name where a = 23 and b > 16 and c='F';

MySQL会从左到右匹配，直到遇到范围查询(>、<、between、like(包括后模糊匹配))就停止匹配。a匹配完之后已经是无序了，所以会回表查询。

5.2. 联合索引优点

• 减少内存及磁盘开销；

  一个联合索引，相当于多个索引。

  每多创建一个索引，都会增加写操作的开销和磁盘空间的开销。

  对于大量的表数据，使用联合索引可以极大的减少开销。

• 覆盖索引；

  MySQL可以直接通过遍历辅助索引取得数据，而无需回表，这减少了很多的随机IO操作。
  效率高。

• 索引列越多，通过条件筛选出数据的速度越快。

5.3. 联合索引缺点

联合索引越多，则索引列越多，创建的索引就越多。

索引都是存储在磁盘里的，通过索引算法来查找数据，的确可以极大的提高查询效率。

但与此同时增删改的操作，是需要更新索引的，同样需要花时间，并且索引所占的磁盘空间也不会小。

5.4. 建议

单表尽可能不超过一个联合索引，单个联合索引不超过3个字段。

5.5. 总结

6. 执行计划

执行计划即使用EXPLAIN关键字模拟优化器执行SQL语句，查看查询语句或结构的执行情况，分析性能瓶颈。

在select语句之前增加explain关键字，且只有select语句可以添加explain关键字，MySQL会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行这条SQL。

以下为explain结果中每个列的信息一一详细说明

6.1. id列（表示select语句的序列号）

特点：

• 有多少个select就有多少个id；

• id的顺序按select出现的顺序增长；

• id值越大，执行优先级越高；

• id相同，则从上到下顺序(执行计划结果表的视角)执行；

• id为NULL最后执行。

explain select * from (select * from user where id = 1) u left join user_skill us on u.id = us.user_id;

6.2. select_type列（表示对应SQL是简单还是复杂的查询）

6.2.1. simple/简单的（简单查询，查询不包含子查询和union操作）

6.2.2. primary/初始的（union操作或包含子查询的外层）

union操作或包含子查询的复杂查询中，最外层的select语句会标记为primary。

explain select * from user where id = 1 union select * from user where id = 2;

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

6.2.3. union/联合（union操作的后面的语句）

出现在union之后的select语句，会被标记为union。

explain select * from user where id = 1 union select * from user where id = 2;

6.2.4. subquery/子查询（where子句中包含子查询）

在select或where子句中(不在from子句中)包含子查询的情况。

6.2.5. derived/派生（from子句中的子查询）

包含在from子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表。

6.3. table列（表示正在访问哪个表）

当from子句中有子查询时，table列是格式，表示当前查询依赖id=N的查询，会先执行id=N的查询。

当有union时，UNION RESULT的table列的值为<union1,2>，1和2表示参与union的select行id。

explain select * from user where id = 1 union select * from user where id = 2;

6.4. type列（访问类型）

即MySQL决定如何查找表中的行数据，查找数据行记录的大概范围。

从左往右，执行效率从高到低分别为:
NULL > system > const > eq_ref > ref > range > index > ALL

一般来说，需保证查询达到range级别，最好达到ref。

6.4.1. NULL（不用访问表便可得到结果）

不用访问表就可以直接得到结果，不需要在执行阶段访问表。

6.4.2. system/const（查询条件是主键或者非空的唯一索引，且结果只有一条）

查询条件是主键或者非空的唯一索引，且结果最多只有一条，读取1次，速度比较快。同时优化过程中查询列值会转成常量(通过show warnings语句查看结果)。

system是const的特例，表里只有一条数据时标识为system（多出现在衍生表中）。

如子查询通过主键查询得出一条数据(该条数据构成衍生表，表中只有一条数据)，通过衍生表的数据进行主查询，其衍生表的访问类型即为system。

6.4.3. eq_ref（多表查询中使用主键或者唯一索引）

多表关联查询中，使用主键或者唯一索引作为关联条件，最多只会返回一条符合 条件的记录

explain select * from user u left join user_skill us on u.id = us.user_id left join skill s on us.skill_id = s.id;

6.4.4. ref（查询或关联条件使用普通索引）

查询或关联条件不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀(如联合索引的左边索引)。

单表或者关联查询中，可能会找到多个符合条件的行。

--关联查询
explain select * from skill s left join user_skill us on us.skill_id = s.id where s.name = 'Java';

6.4.5. range（范围查询）

范围扫描通常出现在查询条件中 in，between ，> ，<，>= 等操作中。

使用一个索引来检索给定范围的行。

6.4.6. index（索引全扫描）

遍历整个索引来查找匹配的行数据。

要查询的列是索引中包含的列，所以只要全表扫描索引即可，直接使用索引树查找数据(不带where查询条件)。

6.4.7. ALL（全表扫描，必须优化）

从头到尾去查找所需要的行。通常情况下，需要增加索引来进行优化。这种查询是无法忍受的，除非是数据量固定的小表

6.5. possible_keys列（可能使用的索引）

表示查询可能使用哪些索引来查找。

特点：

• possible_keys列有值，而key列显示NULL的情况；
  这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。

• 如果possible_keys列是NULL，则没有相关的索引。
  在这种情况下，可以通过检查where子句看是否可以创造适当的索引来提高查询性能，再用explain查看效果。

也就是说如果possible中存在而key为null，是mysql自己的决定！

6.6. key列（实际使用的索引）

实际采用哪个索引来优化对该表的访问。

如果没有使用索引，则该列为NULL。

如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用force index、ignore index。

6.7. key_len列（索引的字节数）

key_len计算规则如下:

• 字符串
  char(n)：n字节；
  varchar(n)：2字节存储字符串，如果是utf-8，则长度 3n + 2；

• 数值类型
  tinyint：1字节；
  smallint：2字节；
  int：4字节；
  bigint：8字节；

• 时间类型
  date：3字节；
  timestamp：4字节；
  datetime：8字节；

• 如果字段允许为 NULL，需要1字节记录是否为NULL；

索引最大长度是768字节，当字符串过长时，mysql会将前半部分的字符提取出来做索引。

explain select * from user_skill us left join user u  on us.user_id = u.id  where us.user_id = '2';

idx_user_skill_id联合索引中，两个字段都为int类型，这里长度为4。

可知，只用到了user_id索引。

6.8. ref列（对应key的字段名）

展示在key列记录的索引中，表查找值所用到的列或常量。也就说key不为空则它不为空，key为空它也为空。

常见的有:

• const(常量)；
• 字段名(user.id)；

6.9. rows列（预估需要读取的行数）

mysql估测要读取的行数，这个并不是结果集里的行数。

6.10. Extra列（查询语句的额外信息，不固定）

展示额外信息，数据不是绝对的。常见的重要值如下

6.10.1. Using index（覆盖索引查询）

使用覆盖索引。

6.10.2. Using where（回表索引查询）

在where语句使用索引查找的情况下，查询的字段未被索引覆盖(当前使用的索引)，需要回表去查询所需的数据。

或者说，从存储引擎取回的数据不全部满足条件，需要在Server层过滤。

6.10.3. Using index condition（有条件使用索引，通俗讲就是部分条件用索引，剩下的用不了索引）

先用索引条件过滤索引得到数据，然后拿着过滤后的结果回表用WHERE子句中的其他条件去过滤这些数据行。

相当于部分过滤采用了索引，所以叫Using index condition。

索引下推：

Index Condition Pushdown，简称ICP，在MySQL5.6的版本上推出。

索引下推在非主键索引上(普通索引)的优化，可以有效减少回表的次数，大大提升了查询的效率。

-- 联合索引 （name,age）
explain SELECT * from user where  name like 'yoka%' and age > 20

• 没有使用ICP的情况下，查询过程如下：

1. 存储引擎读取索引记录；
2. 根据键值从辅助索引中找到主键，然后去主键索引定位并读取完整的行记录；
3. 存储引擎把记录交给Server层去检测该记录是否满足WHERE剩下的其他条件。
   
   根据联合索引查出所有name匹配的辅助索引数据(忽略了age字段)，然后回表，到主键索引上查询全部符合条件的数据。然后返回给Server层，在Server层过滤出age的条件符合的数据，这个过程需要回表两次。

• 使用ICP的情况下，查询过程如下：

1. 存储引擎读取索引记录；

2. 根据键值从辅助索引中找到主键，判断WHERE其他条件能否用索引中的列来做判断，条件不满足，则处理下一行索引记录；

3. 条件满足，则where尽可能的利用索引列作为查询条件过滤了结果表数据，然后去主键索引使用主键去定位并读取完整的行记录（就是所谓的回表）；

4. 存储引擎把记录交给Server层，Server层检测该记录是否满足WHERE剩下的其他条件。
   
   根据联合索引查出所有name匹配的辅助索引数据，然后再从当前筛选出的辅助索引数据中筛选符合age条件的数据，然后再回表，到主键索引上查询全部符合条件的数据，返回给Server层。这个过程需要回表一次。

注意：索引的比较是在存储引擎中进行的，数据的比较是在Server层进行的。

explain的Extra的值为Using index condition，表示使用了索引下推。

使用条件：

• 只能用于range、 ref、 eq_ref、ref_or_null访问类型；

• 只能用于InnoDB和 MyISAM存储引擎及其分区表；

• 对InnoDB存储引擎来说，索引下推只适用于非主键索引。InnoDB的聚集索引保存了完整的表记录数据，索引下推也就没什么意义了(不需要回表)；

• 引用了子查询的条件不能下推；

• 引用了存储函数的条件不能下推，因为存储引擎无法调用存储函数。

索引下推默认是开启的，可以使用系统参数optimizer_switch来控制器是否开启。

-- 查看优化器选项
select @@optimizer_switch;
-- 切换
set optimizer_switch="index_condition_pushdown=off";
set optimizer_switch="index_condition_pushdown=on";

6.10.4. Using temporary（创建了临时表）

需要创建一张临时表来处理查询的情况。

出现这种情况一般用索引来优化。

6.10.5. Using filesort（使用了外部排序）

用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。

6.10.6. Select tables optimized away（使用了聚合函数）

使用某些聚合函数(比如 max、min)来访问存在索引的某个字段。

7. 索引实践

7.1. 联合索引遵循最左匹配原则

创建联合索引时一定要注意字段的顺序，且最好不要超过3个字段。

7.2. 不在索引列上做任何计算、函数、(自动或手动)类型转换操作

使用函数后会导致索引失效而转向全表扫描。

7.3. 尽量使用覆盖索引（也就是主键索引）

如果用name索引需要遍历name字段联合索引树，然后还需要根据遍历出来的主键值去主键索引树里再去查出最终数据，成本比全表扫描还高，可以用覆盖索引优化，这样只需要遍历name字段的联合索引树就能拿到所有结果。

只查询使用到的索引中的字段，减少select * 语句。

如果不能使用覆盖索引则可能需要借助搜索引擎(如es)。

使用非覆盖索引有时会比全表查询更慢！！

7.4. 负向查询的时候，可能无法使用索引，导致全表扫描（可能！！！）

不等于(!=或者<>)、not like、not in。

但是如果主键索引使用不等于操作，一般不会使索引失效；也就是说如果是非主键索引，尽量不要用负向查询。

其他的索引使用不等于操作，一般都会使索引失效，导致全表扫描。

7.5. is null，is not null可能无法使用索引，导致全表扫描（可能！！！）

7.6. like以通配符开头，使索引失效，导致全表扫描

7.7. or或in操作，可能无法使用索引，导致全表扫描（可能！！！）

有时是不会失效的

7.8. 索引范围查询操作，可能无法使用索引，导致全表扫描（可能！！！）

一般不会失效

由于单次数据量查询过大，可能导致优化器最终不走索引 。
优化方案：可以将大的范围拆分成多个小范围。

由上面可知，为什么有些索引有时候是失效的，有时候又有效呢？

MySQL内部优化器会根据检索比例、 表大小等多个因素整体评估是否使用索引。一次查询中回表次数太多，MySQL优化器计算认为使用索引的时间成本高于全表扫描，就会放弃使用索引。
mysql最终是否选择走索引或者一张表涉及多个索引，mysql最终如何选择索引，我们可以用trace工具查看分析相关指标及成本，开启trace工具会影响mysql性能，所以只能临时分析sql使用，用完之后需立即关闭。

7.9. Order by与Group by优化（mysql实际操作中group其实是包含order的）

创建联合索引：KEY idx_name_phone_age (name,phone,age)

7.9.1. order by遵循最左匹配原则（只要利用联合索引都是最左原则）

where语句与order by语句使用同一个联合索引，顺序遵循最左连续。

where查询条件是否使用了索引从type及key列可知。

order by 是否使用了索引排序从Extra列可知。如值为Using filesort，就表示当前为文件排序，效率比较低下，需要进行优化。

• explain select * from user where name = ‘fussen’ and age = 18 order by phone;
  
  通过key及ken_len可知，只使用到了联合索引中的name索引，因为中间字段phone缺失。
  phone索引列用在了排序中，Extra字段里没有using filesort。

• explain select * from user where name = ‘fussen’ and phone = ‘138’ order by age;
  
  通过key及ken_len可知，使用到了联合索引中的name、phone索引。
  age索引列用在了排序中，Extra字段里没有using filesort。

• explain select * from user where name = ‘fussen’ order by age;
  
  通过key及ken_len可知，使用到了联合索引中的name索引。
  phone字段缺失，无法利用age索引排序，Extra字段里出现using filesort。

• explain select * from user where name = ‘fussen’ order by phone,age;
  

• explain select * from user where name = ‘fussen’ order by age,phone;
  

• explain select * from user where name = ‘fussen’ and age = 18 order by phone,age;
  explain select * from user where name = ‘fussen’ and age = 18 order by age,phone;
  
  通过key及ken_len可知，只使用到了联合索引中的name索引。
  因为age为常量，排序一个常量等同于不需排序，在排序中会被优化，相当于只使用phone索引排序，Extra字段里没有using filesort。

• explain select * from user where name = ‘fussen’ order by phone desc,age asc;
  

• explain select * from user where name in (‘fussen’,‘wakaka’) order by phone,age;
  explain select * from user where name > ‘fussen’ order by phone,age;
  

7.9.2. 总结

• MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低；

• order by满足两种情况会使用Using index；

  1、order by语句使用索引最左匹配原则。

  2、使用where子句与order by子句条件列组合满足索引最左匹配原则。

• 尽量在索引列上完成排序，遵循索引的最左匹配原则；

• 如果order by的条件不在索引列上或不遵循最左匹配原则，就会产生Using filesort；

• 能用覆盖索引尽量用覆盖索引；

• group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左匹配原则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中的限定条件就不要去having限定了。

7.9.3. Using filesort文件排序原理详解

文件排序方式

1. 单路排序
   一次性取出满足条件行的所有字段，然后在sort buffer中进行排序，这样排序后就会直接从内存里返回查询结果了;
   用trace工具可以看到sort_mode信息里显示< sort_key, additional_fields >或者< sort_key, packed_additional_fields >
2. 双路排序(又叫回表排序模式)
   首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行ID，然后在sort buffer中进行排序，排序完后只是需要再根据主键回到原表取数据;
   用trace工具 可以看到sort_mode信息里显示< sort_key, rowid >。

MySQL通过比较系统变量max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式。

• 如果max_length_for_sort_data比查询字段的总长度大，则使用单路排序模式；

• 如果max_length_for_sort_data比查询字段的总长度小，则使用双路排序模式。

注意：如果全部使用sort_buffer内存排序一般情况下效率会高于磁盘文件排序，但不能因为这个就随便增大sort_buffer(默认1M)，mysql很多参数设置都是做过优化的，不要轻易调整。

7.10. 分页查询优化

explain select * from user limit 10000,20;

该sql表示从表user中取出从10001 行开始的20行记录。看似只查询了20条记录，实际这条 SQL是先读取10020条记录，然后抛弃前10000条记录，然后读到后面20条想要的数据。因此要查询一张大表比较靠后的数据，执行效率是非常低的。

• 根据主键排序的分页查询优化
  主键分页优化前提是满足以下两个条件:

  • 主键自增且连续；

  • 结果是按照主键排序的。

  否则，会导致两条SQL的结果不一致。

  未优化SQL：
  
  优化SQL：
  
  首先是尽量使用覆盖索引，其次是尽量减少返回值，返回值不等于展示值。

• 根据非主键字段排序的分页查询优化
  根据非主键字段排序的分页查询SQL：
  
  可知，并没有使用name字段的索引(优化器决定)，使用了文件排序。

  那么，怎么优化呢?
  关键是让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录。

  优化SQL如下：

  EXPLAIN select * from user u inner join (select id from user order by name limit 10000,20) uu on u.id = uu.id;
  

  
  使用了索引查找大部分数据再过滤，执行时间减少了一半以上。

  虽然最后是全表扫描，但此时，整个表(查找出来的数据)只有20条数据，效率并不会低。

7.11. Join关联查询优化

MySQL的表关联常见有两种算法：

• Nested-Loop Join 算法（有索引，磁盘比较）

• Block Nested-Loop Join 算法（无索引，内存比较）

1. 嵌套循环连接 Nested-Loop Join(NLJ) 算法
   一次一行循环地从第一张表(称为驱动表)中读取行，在这行数据中取到关联字段，根据关联字段在另一张表(被驱动
   表)里取出满足条件的行，然后取出两张表的结果合集。

   一般join语句中，如果执行计划Extra中未出现Using join buffer，则表示使用的join算法是NLJ。

   先执行的就是驱动表(执行计划结果的id如果一样，则按从上到下顺序执行sql)。优
   化器一般会优先选择小表做驱动表。所以使用inner join时，排在前面的表并不一定就是驱动表。

   a1、a2两张表关联查询流程如下：

   先读取a2表(驱动表)的所有数据(100条)，然后遍历这每行数据中字段x的值，根据a2表(100条)中x的值索引扫描a1表（10000条）中的对应行(扫描100次a1表的索引，1次扫描可以认为最终只扫描a1表一行完整数据，也就是总共a1表也扫描了100行)。因此整个过程扫描了200 行。
   如果被驱动表的关联字段没索引，使用NLJ算法性能会比较低，MySQL会选择BNL算法。

2. 基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法

   把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟join_buffer中的数据做对比

   EXPLAIN select * from user u inner join user_skill us on u.id = us.user_id;
   

   
   Extra中的Using join buffer(Block Nested Loop)说明该关联查询使用的是BNL算法。

   a1、a2两张表关联查询流程如下：

   整个过程对表a1和a2都做了一次全表扫描，因此扫描的总行数为10100(表a1的数据总量 + 表a2的数据总量)。并且join_buffer里的数据是无序的，因此对表a1中的每一行，都要做100次比对判断，所以内存中的判断次数是100 * 10000 = 100 万次。

   被驱动表的关联字段没索引为什么要选择使用BNL算法而不使用NLJ呢?

   如果使用NLJ，那么扫描行数的100万次，就是磁盘扫描。

   很显然，用BNL磁盘扫描次数少很多，相比于磁盘扫描，BNL的内存计算会快得多。因此MySQL对于被驱动表的关联字段没索引的关联查询，一般都会使用BNL 算法。如果有索引一般选择NLJ 算法，有索引的情况下NLJ算法比BNL算法性能更高。

关联查询优化：

• 关联字段加索引，让MySQL做join操作时尽量选择NLJ算法；

• 小表驱动大表，如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去MySQL优化器自己判断的时间。

  straight_join功能同join类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执
  行顺序。

  straight_join只适用于inner join，并不适用于left join，right join(因为left join，right join已经代表指定了表的执行顺序)。

  使用straight_join一定要慎重，尽可能让优化器去判断，因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。

7.12. count(*)查询

EXPLAIN select count(1) from user; 
EXPLAIN select count(id) from user;
EXPLAIN select count(name) from user;
EXPLAIN select count(*) from user;

四个sql的执行计划一样，说明这四个sql执行效率差不多，区别在于根据某个字段count不会统计字段为null值的数据行。

为什么count(name) 选择辅助索引而不是主键聚集索引?
因为辅助索引相对主键索引存储数据更少，检索性能更高。

8. 总结

什么时候需要建索引？

• 主键自动建唯一索引；

• 经常作为查询条件在WHERE或者ORDER BY 语句中出现的列要建索引；

• 作为排序的列要建索引；

• 查询中与其他表关联的字段要建索引；

• 建立组合索引，而不是修改单列索引，把区分度高的字段放在前面；

• 查询结果列，使用聚合函数可以建索引，如使用了max(age)。

什么时候不需要建索引？

• 经常增删改的列不要建索引，更新会变更B+树，会大大降低数据库性能；

• 有大量重复的列不建索引；

• 区分度低的字段，例如性别，不要建索引，离散度太低，导致扫描行数过多；

• 表记录太少不要建索引。

索引优点：

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；

• 可以大大加快数据的检索速度 ，这也是创建索引的最主要的原因；

• 以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义；

• 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间；

• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

索引缺点：

• 创建索引和维护索引要耗费时间 ，这种时间随着数据量的增加而增加；

• 索引需要占据物理空间 ，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚集索引，那么需要的空间就会更大；

• 当对表中的数据进行增加、删除和修改的时候， 索引也要动态的维护 ，这样就降低了数据的维护速度。

写出好sql：
核心是减少返回值，其次是走索引，当二者不可得兼时，要根据数据量来抉择，数据量大则必须走索引，数据量小时走全表也无所谓。