MySQL - 存储引擎与索引

存储引擎 ​

基本介绍 ​

对比其他数据库，MySQL 的架构可以在不同场景应用并发挥良好作用，主要体现在存储引擎，插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取分离，可以针对不同的存储需求可以选择最优的存储引擎

存储引擎的介绍：

• MySQL 数据库使用不同的机制存取表文件 , 机制的差别在于不同的存储方式、索引技巧、锁定水平等不同的功能和能力，在 MySQL 中，将这些不同的技术及配套的功能称为存储引擎
• Oracle、SqlServer 等数据库只有一种存储引擎，MySQL 提供了插件式的存储引擎架构，所以 MySQL 存在多种存储引擎 , 就会让数据库采取了不同的处理数据的方式和扩展功能
• 在关系型数据库中数据的存储是以表的形式存进行，所以存储引擎也称为表类型（存储和操作此表的类型）
• 通过选择不同的引擎，能够获取最佳的方案, 也能够获得额外的速度或者功能，提高程序的整体效果。

MySQL 支持的存储引擎：

• MySQL 支持的引擎包括：InnoDB、MyISAM、MEMORY、Archive、Federate、CSV、BLACKHOLE 等
• MySQL5.5 之前的默认存储引擎是 MyISAM，5.5 之后就改为了 InnoDB

引擎对比 ​

MyISAM 存储引擎：

• 特点：不支持事务和外键，读取速度快，节约资源
• 应用场景：适用于读多写少的场景，对事务的完整性要求不高，比如一些数仓、离线数据、支付宝的年度总结之类的场景，业务进行只读操作，查询起来会更快
• 存储方式：
  • 每个 MyISAM 在磁盘上存储成 3 个文件，其文件名都和表名相同，拓展名不同
  • 表的定义保存在 .frm 文件，表数据保存在 .MYD (MYData) 文件中，索引保存在 .MYI (MYIndex) 文件中

InnoDB 存储引擎：(MySQL5.5 版本后默认的存储引擎)

• 特点：支持事务和外键操作，支持并发控制。对比 MyISAM 的存储引擎，InnoDB 写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引
• 应用场景：对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，读写频繁的操作
• 存储方式：
  • 使用共享表空间存储， 这种方式创建的表的表结构保存在 .frm 文件中， 数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path 定义的表空间中，可以是多个文件
  • 使用多表空间存储，创建的表的表结构存在 .frm 文件中，每个表的数据和索引单独保存在 .ibd 中

MEMORY 存储引擎：

• 特点：每个 MEMORY 表实际对应一个磁盘文件 ，该文件中只存储表的结构，表数据保存在内存中，且默认使用 HASH 索引，所以数据默认就是无序的，但是在需要快速定位记录可以提供更快的访问，服务一旦关闭，表中的数据就会丢失，存储不安全
• 应用场景：缓存型存储引擎，通常用于更新不太频繁的小表，用以快速得到访问结果
• 存储方式：表结构保存在 .frm 中

MERGE 存储引擎：

• 特点：

  • 是一组 MyISAM 表的组合，这些 MyISAM 表必须结构完全相同，通过将不同的表分布在多个磁盘上
  • MERGE 表本身并没有存储数据，对 MERGE 类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的 MyISAM 表进行的

• 应用场景：将一系列等同的 MyISAM 表以逻辑方式组合在一起，并作为一个对象引用他们，适合做数据仓库

• 操作方式：

  • 插入操作是通过 INSERT_METHOD 子句定义插入的表，使用 FIRST 或 LAST 值使得插入操作被相应地作用在第一或者最后一个表上；不定义这个子句或者定义为 NO，表示不能对 MERGE 表执行插入操作
  • 对 MERGE 表进行 DROP 操作，但是这个操作只是删除 MERGE 表的定义，对内部的表是没有任何影响的
  sql

  CREATE TABLE order_1(
  )ENGINE = MyISAM DEFAULT CHARSET=utf8;
  
  CREATE TABLE order_2(
  )ENGINE = MyISAM DEFAULT CHARSET=utf8;
  
  CREATE TABLE order_all(
  	-- 结构与MyISAM表相同
  )ENGINE = MERGE UNION = (order_1,order_2) INSERT_METHOD=LAST DEFAULT CHARSET=utf8;

  

只读场景 MyISAM 比 InnoDB 更快：

• 底层存储结构有差别，MyISAM 是非聚簇索引，叶子节点保存的是数据的具体地址，不用回表查询
• InnoDB 每次查询需要维护 MVCC 版本状态，保证并发状态下的读写冲突问题

引擎操作 ​

• 查询数据库支持的存储引擎

  sql

  SHOW ENGINES;
  SHOW VARIABLES LIKE '%storage_engine%'; -- 查看Mysql数据库默认的存储引擎

• 查询某个数据库中所有数据表的存储引擎

  sql

  SHOW TABLE STATUS FROM 数据库名称;

• 查询某个数据库中某个数据表的存储引擎

  sql

  SHOW TABLE STATUS FROM 数据库名称 WHERE NAME = '数据表名称';

• 创建数据表，指定存储引擎

  sql

  CREATE TABLE 表名(
  	列名,数据类型,
      ...
  )ENGINE = 引擎名称;

• 修改数据表的存储引擎

  sql

  ALTER TABLE 表名 ENGINE = 引擎名称;

索引机制 ​

索引介绍 ​

基本介绍 ​

MySQL 官方对索引的定义为：索引（index）是帮助 MySQL 高效获取数据的一种数据结构，**本质是排好序的快速查找数据结构。**在表数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引

索引是在存储引擎层实现的，所以并没有统一的索引标准，即不同存储引擎的索引的工作方式并不一样

索引使用：一张数据表，用于保存数据；一个索引配置文件，用于保存索引；每个索引都指向了某一个数据

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快 Col2 的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据的物理地址的指针，这样就可以运用二叉查找快速获取到相应数据

索引的优点：

• 类似于书籍的目录索引，提高数据检索的效率，降低数据库的 IO 成本
• 通过索引列对数据进行排序，降低数据排序的成本，降低 CPU 的消耗

索引的缺点：

• 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上
• 虽然索引大大提高了查询效率，同时却也降低更新表的速度。对表进行 INSERT、UPDATE、DELETE 操作，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，还会调整因为更新所带来的键值变化后的索引信息，但是更新数据也需要先从数据库中获取，索引加快了获取速度，所以可以相互抵消一下。
• 索引会影响到 WHERE 的查询条件和排序 ORDER BY 两大功能

索引分类 ​

索引一般的分类如下：

• 功能分类

  • 主键索引：一种特殊的唯一索引，不允许有空值，一般在建表时同时创建主键索引
  • 单列索引：一个索引只包含单个列，一个表可以有多个单列索引（普通索引）
  • 联合索引：顾名思义，就是将单列索引进行组合
  • 唯一索引：索引列的值必须唯一，允许有空值，如果是联合索引，则列值组合必须唯一
    • NULL 值可以出现多次，因为两个 NULL 比较的结果既不相等，也不不等，结果仍然是未知
    • 可以声明不允许存储 NULL 值的非空唯一索引
  • 外键索引：只有 InnoDB 引擎支持外键索引，用来保证数据的一致性、完整性和实现级联操作

• 结构分类

  • BTree 索引：MySQL 使用最频繁的一个索引数据结构，是 InnoDB 和 MyISAM 存储引擎默认的索引类型，底层基于 B+Tree
  • Hash 索引：MySQL中 Memory 存储引擎默认支持的索引类型
  • R-tree 索引（空间索引）：空间索引是 MyISAM 引擎的一个特殊索引类型，主要用于地理空间数据类型
  • Full-text 索引（全文索引）：快速匹配全部文档的方式。MyISAM 支持， InnoDB 不支持 FULLTEXT 类型的索引，但是 InnoDB 可以使用 sphinx 插件支持全文索引，MEMORY 引擎不支持

  索引	InnoDB	MyISAM	Memory
  BTREE	支持	支持	支持
  HASH	不支持	不支持	支持
  R-tree	不支持	支持	不支持
  Full-text	5.6 版本之后支持	支持	不支持

联合索引图示：根据身高年龄建立的组合索引（height、age）

索引操作 ​

索引在创建表的时候可以同时创建， 也可以随时增加新的索引

• 创建索引：如果一个表中有一列是主键，那么会默认为其创建主键索引（主键列不需要单独创建索引）

  sql

  CREATE [UNIQUE|FULLTEXT] INDEX 索引名称 [USING 索引类型] ON 表名(列名...);
  -- 索引类型默认是 B+TREE

• 查看索引

  sql

  SHOW INDEX FROM 表名;

• 添加索引

  sql

  -- 单列索引
  ALTER TABLE 表名 ADD INDEX 索引名称(列名);
  
  -- 组合索引
  ALTER TABLE 表名 ADD INDEX 索引名称(列名1,列名2,...);
  
  -- 主键索引
  ALTER TABLE 表名 ADD PRIMARY KEY(主键列名); 
  
  -- 外键索引(添加外键约束，就是外键索引)
  ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);
  
  -- 唯一索引
  ALTER TABLE 表名 ADD UNIQUE 索引名称(列名);
  
  -- 全文索引(mysql只支持文本类型)
  ALTER TABLE 表名 ADD FULLTEXT 索引名称(列名);

• 删除索引

  sql

  DROP INDEX 索引名称 ON 表名;

• 案例练习

  数据准备：student

  sql

  id	NAME	 age	score
  1	张三		23		99
  2	李四		24		95
  3	王五		25		98
  4	赵六		26		97

  索引操作：

  sql

  -- 为student表中姓名列创建一个普通索引
  CREATE INDEX idx_name ON student(NAME);
  
  -- 为student表中年龄列创建一个唯一索引
  CREATE UNIQUE INDEX idx_age ON student(age);

聚簇索引 ​

索引对比 ​

聚簇索引是一种数据存储方式，并不是一种单独的索引类型

• 聚簇索引的叶子节点存放的是主键值和数据行，支持覆盖索引

• 非聚簇索引的叶子节点存放的是主键值或指向数据行的指针（由存储引擎决定）

在 Innodb 下主键索引是聚簇索引，在 MyISAM 下主键索引是非聚簇索引

Innodb ​

聚簇索引 ​

在 Innodb 存储引擎，B+ 树索引可以分为聚簇索引（也称聚集索引、clustered index）和辅助索引（也称非聚簇索引或二级索引、secondary index、non-clustered index）

InnoDB 中，聚簇索引是按照每张表的主键构造一颗 B+ 树，叶子节点中存放的就是整张表的数据，将聚簇索引的叶子节点称为数据页

• 这个特性决定了数据也是索引的一部分，所以一张表只能有一个聚簇索引
• 辅助索引的存在不影响聚簇索引中数据的组织，所以一张表可以有多个辅助索引

聚簇索引的优点：

• 数据访问更快，聚簇索引将索引和数据保存在同一个 B+ 树中，因此从聚簇索引中获取数据比非聚簇索引更快
• 聚簇索引对于主键的排序查找和范围查找速度非常快

聚簇索引的缺点：

• 插入速度严重依赖于插入顺序，按照主键的顺序（递增）插入是最快的方式，否则将会出现页分裂，严重影响性能，所以对于 InnoDB 表，一般都会定义一个自增的 ID 列为主键

• 更新主键的代价很高，将会导致被更新的行移动，所以对于 InnoDB 表，一般定义主键为不可更新

• 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据

辅助索引 ​

在聚簇索引之上创建的索引称之为辅助索引，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引等

辅助索引叶子节点存储的是主键值，而不是数据的物理地址，所以访问数据需要二次查找，推荐使用覆盖索引，可以减少回表查询

检索过程：辅助索引找到主键值，再通过聚簇索引（二分）找到数据页，最后通过数据页中的 Page Directory（二分）找到对应的数据分组，遍历组内所所有的数据找到数据行

补充：无索引走全表查询，查到数据页后和上述步骤一致

索引实现 ​

InnoDB 使用 B+Tree 作为索引结构，并且 InnoDB 一定有索引

主键索引：

• 在 InnoDB 中，表数据文件本身就是按 B+Tree 组织的一个索引结构，这个索引的 key 是数据表的主键，叶子节点 data 域保存了完整的数据记录

• InnoDB 的表数据文件通过主键聚集数据，如果没有定义主键，会选择非空唯一索引代替，如果也没有这样的列，MySQL 会自动为 InnoDB 表生成一个隐含字段 row_id 作为主键，这个字段长度为 6 个字节，类型为长整形

辅助索引：

• InnoDB 的所有辅助索引（二级索引）都引用主键作为 data 域

• InnoDB 表是基于聚簇索引建立的，因此 InnoDB 的索引能提供一种非常快速的主键查找性能。不过辅助索引也会包含主键列，所以不建议使用过长的字段作为主键，过长的主索引会令辅助索引变得过大

MyISAM ​

非聚簇 ​

MyISAM 的主键索引使用的是非聚簇索引，索引文件和数据文件是分离的，索引文件仅保存数据的地址

• 主键索引 B+ 树的节点存储了主键，辅助键索引 B+ 树存储了辅助键，表数据存储在独立的地方，这两颗 B+ 树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别
• 由于索引树是独立的，通过辅助索引检索无需回表查询访问主键的索引树

索引实现 ​

MyISAM 的索引方式也叫做非聚集的，之所以这么称呼是为了与 InnoDB 的聚集索引区分

主键索引：MyISAM 引擎使用 B+Tree 作为索引结构，叶节点的 data 域存放的是数据记录的地址

辅助索引：MyISAM 中主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求 key 是唯一的，而辅助索引的 key 可以重复

参考文章：https://blog.csdn.net/lm1060891265/article/details/81482136

索引结构 ​

数据页 ​

文件系统的最小单元是块（block），一个块的大小是 4K，系统从磁盘读取数据到内存时是以磁盘块为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么

InnoDB 存储引擎中有页（Page）的概念，页是 MySQL 磁盘管理的最小单位

• InnoDB 存储引擎中默认每个页的大小为 16KB，索引中一个节点就是一个数据页，所以会一次性读取 16KB 的数据到内存
• InnoDB 引擎将若干个地址连接磁盘块，以此来达到页的大小 16KB
• 在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘 I/O 次数，提高查询效率

超过 16KB 的一条记录，主键索引页只会存储部分数据和指向溢出页的指针，剩余数据都会分散存储在溢出页中

数据页物理结构，从上到下：

• File Header：上一页和下一页的指针、该页的类型（索引页、数据页、日志页等）、校验和、LSN（最近一次修改当前页面时的系统 lsn 值，事务持久性部分详解）等信息
• Page Header：记录状态信息
• Infimum + Supremum：当前页的最小记录和最大记录（头尾指针），Infimum 所在分组只有一条记录，Supremum 所在分组可以有 1 ~ 8 条记录，剩余的分组可以有 4 ~ 8 条记录
• User Records：存储数据的记录
• Free Space：尚未使用的存储空间
• Page Directory：分组的目录，可以通过目录快速定位（二分法）数据的分组
• File Trailer：检验和字段，在刷脏过程中，页首和页尾的校验和一致才能说明页面刷新成功，二者不同说明刷新期间发生了错误；LSN 字段，也是用来校验页面的完整性

数据页中包含数据行，数据的存储是基于数据行的，数据行有 next_record 属性指向下一个行数据，所以是可以遍历的，但是一组数据至多 8 个行，通过 Page Directory 先定位到组，然后遍历获取所需的数据行即可

数据行中有三个隐藏字段：trx_id、roll_pointer、row_id（在事务章节会详细介绍它们的作用）

BTree ​

BTree 的索引类型是基于 B+Tree 树型数据结构的，B+Tree 又是 BTree 数据结构的变种，用在数据库和操作系统中的文件系统，特点是能够保持数据稳定有序

BTree 又叫多路平衡搜索树，一颗 m 叉的 BTree 特性如下：

• 树中每个节点最多包含 m 个孩子
• 除根节点与叶子节点外，每个节点至少有 [ceil(m/2)] 个孩子
• 若根节点不是叶子节点，则至少有两个孩子
• 所有的叶子节点都在同一层
• 每个非叶子节点由 n 个 key 与 n+1 个指针组成，其中 [ceil(m/2)-1] <= n <= m-1

5 叉，key 的数量 [ceil(m/2)-1] <= n <= m-1 为 2 <= n <=4 ，当 n>4 时中间节点分裂到父节点，两边节点分裂

插入 C N G A H E K Q M F W L T Z D P R X Y S 数据的工作流程：

• 插入前 4 个字母 C N G A

  

• 插入 H，n>4，中间元素 G 字母向上分裂到新的节点

  

• 插入 E、K、Q 不需要分裂

  

• 插入 M，中间元素 M 字母向上分裂到父节点 G

  

• 插入 F，W，L，T 不需要分裂

  

• 插入 Z，中间元素 T 向上分裂到父节点中

  

• 插入 D，中间元素 D 向上分裂到父节点中，然后插入 P，R，X，Y 不需要分裂

  

• 最后插入 S，NPQR 节点 n>5，中间节点 Q 向上分裂，但分裂后父节点 DGMT 的 n>5，中间节点 M 向上分裂

  

BTree 树就已经构建完成了，BTree 树和二叉树相比， 查询数据的效率更高， 因为对于相同的数据量来说，BTree 的层级结构比二叉树少，所以搜索速度快

BTree 结构的数据可以让系统高效的找到数据所在的磁盘块，定义一条记录为一个二元组 [key, data] ，key 为记录的键值，对应表中的主键值，data 为一行记录中除主键外的数据。对于不同的记录，key 值互不相同，BTree 中的每个节点根据实际情况可以包含大量的关键字信息和分支

缺点：当进行范围查找时会出现回旋查找

B+Tree ​

数据结构 ​

BTree 数据结构中每个节点中不仅包含数据的 key 值，还有 data 值。磁盘中每一页的存储空间是有限的，如果 data 数据较大时将会导致每个节点（即一个页）能存储的 key 的数量很小，当存储的数据量很大时同样会导致 B-Tree 的深度较大，增大查询时的磁盘 I/O 次数，进而影响查询效率，所以引入 B+Tree

B+Tree 为 BTree 的变种，B+Tree 与 BTree 的区别为：

• n 叉 B+Tree 最多含有 n 个 key（哈希值），而 BTree 最多含有 n-1 个 key

• 所有非叶子节点只存储键值 key 信息，只进行数据索引，使每个非叶子节点所能保存的关键字大大增加
• 所有数据都存储在叶子节点，所以每次数据查询的次数都一样
• 叶子节点按照 key 大小顺序排列，左边结尾数据都会保存右边节点开始数据的指针，形成一个链表
• 所有节点中的 key 在叶子节点中也存在（比如 5)，key 允许重复，B 树不同节点不存在重复的 key

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

优化结构 ​

MySQL 索引数据结构对经典的 B+Tree 进行了优化，在原 B+Tree 的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的 B+Tree，提高区间访问的性能，防止回旋查找

区间访问的意思是访问索引为 5 - 15 的数据，可以直接根据相邻节点的指针遍历

B+ 树的叶子节点是数据页（page），一个页里面可以存多个数据行

通常在 B+Tree 上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。可以对 B+Tree 进行两种查找运算：

• 有范围：对于主键的范围查找和分页查找
• 有顺序：从根节点开始，进行随机查找，顺序查找

InnoDB 中每个数据页的大小默认是 16KB

• 索引行：一般表的主键类型为 INT（4 字节）或 BIGINT（8 字节），指针大小在 InnoDB 中设置为 6 字节节，也就是说一个页大概存储 16KB/(8B+6B)=1K 个键值（估值）。则一个深度为 3 的 B+Tree 索引可以维护 10^3 * 10^3 * 10^3 = 10亿 条记录
• 数据行：一行数据的大小可能是 1k，一个数据页可以存储 16 行

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree 的高度一般都在 2-4 层。MySQL 的 InnoDB 存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要 1~3 次磁盘 I/O 操作

B+Tree 优点：提高查询速度，减少磁盘的 IO 次数，树形结构较小

索引维护 ​

B+ 树为了保持索引的有序性，在插入新值的时候需要做相应的维护

每个索引中每个块存储在磁盘页中，可能会出现以下两种情况：

• 如果所在的数据页已经满了，这时候需要申请一个新的数据页，然后挪动部分数据过去，这个过程称为页分裂，原本放在一个页的数据现在分到两个页中，降低了空间利用率
• 当相邻两个页由于删除了数据，利用率很低之后，会将数据页做页合并，合并的过程可以认为是分裂过程的逆过程
• 这两个情况都是由 B+ 树的结构决定的

一般选用数据小的字段做索引，字段长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小

自增主键的插入数据模式，可以让主键索引尽量地保持递增顺序插入，不涉及到挪动其他记录，避免了页分裂，页分裂的目的就是保证后一个数据页中的所有行主键值比前一个数据页中主键值大

参考文章：https://developer.aliyun.com/article/919861

设计原则 ​

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率

创建索引时的原则：

• 对查询频次较高，且数据量比较大的表建立索引
• 使用唯一索引，区分度越高，使用索引的效率越高
• 索引字段的选择，最佳候选列应当从 where 子句的条件中提取，使用覆盖索引
• 使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的 I/O 效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升 MySQL 访问索引的 I/O 效率
• 索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价越高。对于插入、更新、删除等 DML 操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低 DML 操作的效率，增加相应操作的时间消耗；另外索引过多的话，MySQL 也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但提高了选择的代价

• MySQL 建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配

  N 个列组合而成的组合索引，相当于创建了 N 个索引，如果查询时 where 句中使用了组成该索引的前几个字段，那么这条查询 SQL 可以利用组合索引来提升查询效率

  sql

  -- 对name、address、phone列建一个联合索引
  ALTER TABLE user ADD INDEX index_three(name,address,phone);
  -- 查询语句执行时会依照最左前缀匹配原则，检索时分别会使用索引进行数据匹配。
  (name,address,phone)
  (name,address)
  (name,phone)	-- 只有name字段走了索引
  (name)
  
  -- 索引的字段可以是任意顺序的，优化器会帮助我们调整顺序，下面的SQL语句可以命中索引
  SELECT * FROM user WHERE address = '北京' AND phone = '12345' AND name = '张三';

  sql

  -- 如果联合索引中最左边的列不包含在条件查询中，SQL语句就不会命中索引，比如：
  SELECT * FROM user WHERE address = '北京' AND phone = '12345';

哪些情况不要建立索引：

• 记录太少的表
• 经常增删改的表
• 频繁更新的字段不适合创建索引
• where 条件里用不到的字段不创建索引

索引优化 ​

覆盖索引 ​

覆盖索引：包含所有满足查询需要的数据的索引（SELECT 后面的字段刚好是索引字段），可以利用该索引返回 SELECT 列表的字段，而不必根据索引去聚簇索引上读取数据文件

回表查询：要查找的字段不在非主键索引树上时，需要通过叶子节点的主键值去主键索引上获取对应的行数据

使用覆盖索引，防止回表查询：

• 表 user 主键为 id，普通索引为 age，查询语句：

  sql

  SELECT * FROM user WHERE age = 30;

  查询过程：先通过普通索引 age=30 定位到主键值 id=1，再通过聚集索引 id=1 定位到行记录数据，需要两次扫描 B+ 树

• 使用覆盖索引：

  sql

  DROP INDEX idx_age ON user;
  CREATE INDEX idx_age_name ON user(age,name);
  SELECT id,age FROM user WHERE age = 30;

  在一棵索引树上就能获取查询所需的数据，无需回表速度更快

使用覆盖索引，要注意 SELECT 列表中只取出需要的列，不可用 SELECT *，所有字段一起做索引会导致索引文件过大，查询性能下降

索引下推 ​

索引条件下推优化（Index Condition Pushdown，ICP）是 MySQL5.6 添加，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数

索引下推充分利用了索引中的数据，在查询出整行数据之前过滤掉无效的数据，再去主键索引树上查找

• 不使用索引下推优化时存储引擎通过索引检索到数据，然后回表查询记录返回给 Server 层，服务器判断数据是否符合条件

  

• 使用索引下推优化时，如果存在某些被索引的列的判断条件时，由存储引擎在索引遍历的过程中判断数据是否符合传递的条件，将符合条件的数据进行回表，检索出来返回给服务器，由此减少 IO 次数

  

适用条件：

• 需要存储引擎将索引中的数据与条件进行判断（所以条件列必须都在同一个索引中），所以优化是基于存储引擎的，只有特定引擎可以使用，适用于 InnoDB 和 MyISAM
• 存储引擎没有调用跨存储引擎的能力，跨存储引擎的功能有存储过程、触发器、视图，所以调用这些功能的不可以进行索引下推优化
• 对于 InnoDB 引擎只适用于二级索引，InnoDB 的聚簇索引会将整行数据读到缓冲区，不再需要去回表查询了

工作过程：用户表 user，(name, age) 是联合索引

SELECT * FROM user WHERE name LIKE '张%' AND　age = 10;	-- 头部模糊匹配会造成索引失效

• 优化前：在非主键索引树上找到满足第一个条件的行，然后通过叶子节点记录的主键值再回到主键索引树上查找到对应的行数据，再对比 AND 后的条件是否符合，符合返回数据，需要 4 次回表

  

• 优化后：检查索引中存储的列信息是否符合索引条件，然后交由存储引擎用剩余的判断条件判断此行数据是否符合要求，不满足条件的不去读取表中的数据，满足下推条件的就根据主键值进行回表查询，2 次回表

当使用 EXPLAIN 进行分析时，如果使用了索引条件下推，Extra 会显示 Using index condition

参考文章：https://blog.csdn.net/sinat_29774479/article/details/103470244

参考文章：https://time.geekbang.org/column/article/69636

前缀索引 ​

当要索引的列字符很多时，索引会变大变慢，可以只索引列开始的部分字符串，节约索引空间，提高索引效率

注意：使用前缀索引就系统就忽略覆盖索引对查询性能的优化了

优化原则：降低重复的索引值

比如地区表：

area			gdp		code
chinaShanghai	100		aaa
chinaDalian		200		bbb
usaNewYork		300		ccc
chinaFuxin		400		ddd
chinaBeijing	500		eee

发现 area 字段很多都是以 china 开头的，那么如果以前 1-5 位字符做前缀索引就会出现大量索引值重复的情况，索引值重复性越低，查询效率也就越高，所以需要建立前 6 位字符的索引：

CREATE INDEX idx_area ON table_name(area(7));

场景：存储身份证

• 直接创建完整索引，这样可能比较占用空间
• 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引
• 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题（前 6 位相同的很多）
• 创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描

索引合并 ​

使用多个索引来完成一次查询的执行方法叫做索引合并 index merge

• Intersection 索引合并：

  sql

  SELECT * FROM table_test WHERE key1 = 'a' AND key3 = 'b'; # key1 和 key3 列都是单列索引、二级索引

  从不同索引中扫描到的记录的 id 值取交集（相同 id），然后执行回表操作，要求从每个二级索引获取到的记录都是按照主键值排序

• Union 索引合并：

  sql

  SELECT * FROM table_test WHERE key1 = 'a' OR key3 = 'b';

  从不同索引中扫描到的记录的 id 值取并集，然后执行回表操作，要求从每个二级索引获取到的记录都是按照主键值排序

• Sort-Union 索引合并

  sql

  SELECT * FROM table_test WHERE key1 < 'a' OR key3 > 'b';

  先将从不同索引中扫描到的记录的主键值进行排序，再按照 Union 索引合并的方式进行查询

索引合并算法的效率并不好，通过将其中的一个索引改成联合索引会优化效率