当我们想要对 SQL 进行优化的时候，很大程度上都是围绕着 索引 展开优化的，所以索引在我们对数据库进行优化的过程中至关重要，值得我们重点关注！！！！

本篇文章我们主要围绕以下几个方面展开对 索引 的介绍：

  • 概述（介绍、优缺点）
  • 结构（B-Tree、B+Tree、hash）
  • 分类（主键、唯一、常规、全文索引，聚集、二级索引）
  • 语法（创建、查看、删除索引）
  • SQL 性能分析（SQL 执行频率、慢查询日志、profile、explain）
  • 索引使用
  • 设计原则

索引 是 MySQL 体系结构中存储引擎层的内容，关于 MySQL 存储引擎可以去看这篇文章：MySQL存储引擎（InnoDB引擎）

一、索引概述

什么是索引？

高效获取数据 的 数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

        下面通过一个示例为大家演示一下 索引 的使用：
假如我们要执行的SQL语句为 select * from user where age = 45 对表进行查询时：
        ① 在无索引情况下，需要从第一行开始扫描，一直扫描到最后一行，我们称之为 全表扫描，性能很低。
        ② 如果我们针对于这张表建立了索引，假设索引结构就是二叉树，那么也就意味着，会对age这个字段建立一个二叉树的索引结构，此时我们在进行查询时，只需要扫描三次就可以找到数据了，极大的提高的查询的效率。

sql 的索引 range sql索引使用规则_mysql

备注： 这里我们只是假设索引的结构是二叉树，介绍一下索引的大概原理，只是一个示意图，并不是索引的真实结构，索引的真实结构，后面会详细介绍。

使用索引有什么优点？缺点呢？

                优点

                                缺点

提高数据检索的效率，降低数据库的 IO  成本

索引列也是要占用空间的。

通过索引列对数据进行排序，降低数据排序的成本降低CPU 的消耗。

索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT 、 UPDATE、 DELETE 时，效率降低。

二、索引的结构

MySQL 的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构，主要包含以下几种：

sql 的索引 range sql索引使用规则_mysql_02

上述是 MySQL 中所支持的所有的索引结构，接下来，我们再来看看不同的存储引擎对于索引结构的支持情况。 

sql 的索引 range sql索引使用规则_sql_03

注意： 我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引。

B 树


        如果选择 二叉树 作为索引结构，会存在： 顺序插入时，会形成一个链表，查询性能大大降低；大数据量情况下，层级较深，检索速度慢等缺点。如果选择具有自平衡的 红黑树 ，虽然可以在顺序插入时最终也会形成一颗平衡的二叉树，但是 由于红黑树也是一颗二叉树，所以也会存在 大数据量情况下，层级较深，检索速度慢的缺点。



        所以，在 MySQL 的索引结构中，并没有选择二叉树或者红黑树，而选择的是 B+Tree，那么什么是 B+Tree 呢？在介绍 B+Tree 之前，先来介绍一个 B-Tree。

 B-Tree，B 树是一种多叉路衡查找树，相对于二叉树，B 树每个节点可以有多个分支，即多叉。以一颗最大度数（max-degree）为 5 (5 阶)的 b-tree 为例，那这个 B 树每个节点最多存储 4 个 key、5 个指针，一旦节点存储的 key 数量到达 5，就会裂变，中间元素向上分裂。：        

sql 的索引 range sql索引使用规则_database_04

注意：B-tree 的非叶子节点和叶子节点都会存放数据

我们可以通过数据结构可视化网站来简单演示一下 b 树的插入过程，这里就不一一演示了：B-Tree Visualization (usfca.edu)


B+ 树

        B+Tree 是 B-Tree 的变种，我们以一颗最大度数（max-degree）为 4（4阶）的 b+tree 为例，来看一下其结构示意图：

sql 的索引 range sql索引使用规则_database_05

我们可以看到，两部分：

  • 绿色框框起来的部分，是索引部分，仅仅起到索引数据的作用，不存储数据。
  • 红色框框起来的部分，是数据存储部分，在其叶子节点中要存储具体的数据。

B+Tree 与 B-Tree 的最大区别就是：在 B+ 树中，所有数据都会出现在叶子节点，且叶子结点形成一个单向链表，非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。

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

sql 的索引 range sql索引使用规则_sql 的索引 range_06

注意：这里的 页 / 块 和存储引擎的逻辑结构中的页相对应。

hash

        哈希索引就是采用一定的 hash 算法，将键值换算成新的 hash 值，映射到对应的槽位上，然后存储在 hash 表中。 

        如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了 hash 冲突（也称为 hash 碰撞），可以通过链表来解决。 

sql 的索引 range sql索引使用规则_database_07

hash 索引的特点：

  • Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，...）
  • 无法利用索引完成排序操作
  • 查询效率高，通常(不存在 hash 冲突的情况)只需要一次检索就可以了，效率通常要高于B+tree 索引

        在 MySQL 中，支持 hash 索引的是 Memory 存储引擎。 而 InnoDB 中具有自适应 hash 功能，hash 索引是 InnoDB 存储引擎根据 B+Tree 索引在指定条件下自动构建的。

来一道面试吧：为什么 InnoDB 存储引擎选择使用 B+tree 索引结构？换句话说，B+tree 索引结构相对于其他几个结构有什么优点？

        1. 相对于二叉树，层级更少，搜索效率高；

        2. 对于 B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；

        3. 相对Hash索引，B+tree 支持范围匹配及排序操作；

三、索引的分类

sql 的索引 range sql索引使用规则_数据库_08

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

sql 的索引 range sql索引使用规则_数据库_09

聚集索引必须要有一个，而且也只能有一个，那么聚集索引的选取规则是怎样的呢？

  • 如果存在主键，主键索引就是聚集索引。
  • 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
  • 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

聚集索引和二级索引的具体结构如下：


sql 的索引 range sql索引使用规则_sql_10


回表查询： 这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。

四、索引的语法

1. 创建索引

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name ( index_col_name,... ) ;

2. 查看索引

SHOW INDEX FROM table_name ;

3. 删除索引

DROP INDEX index_name ON table_name ;

五、SQL 性能分析

SQL 执行频率


MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的 INSERT、UPDATE、DELETE、SELECT 的访问频次： 

-- session 是查看当前会话 ; 
-- global 是查询全局数据 ; 
SHOW GLOBAL STATUS LIKE 'Com_______';

sql 的索引 range sql索引使用规则_database_11

如果是以查询为主，那么就要考虑对数据库的索引进行优化了。

慢查询日志

/etc/my.cnf ）中配置如下信息：

# 开启MySQL慢日志查询开关 
slow_query_log=1 
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志 
long_query_time=2

        配置完毕之后，需要在重新启动 MySQL 后使用以下命令查看是否开启了慢日志查询，并测试查看慢日志文件中记录的信息

show variables like 'slow_query_log';

sql 的索引 range sql索引使用规则_数据库_12

检查慢查询日志（ /var/lib/mysql/localhost-slow.log ） ：

        最终我们发现，在慢查询日志中，只会记录执行时间超多我们预设时间（2s）的SQL，执行较快的 SQL 是不会记录的。那这样，通过慢查询日志，就可以定位出执行效率比较低的SQL，从而有针对性的进行优化。

sql 的索引 range sql索引使用规则_mysql_13

profile 详情

        show profiles 能够在做 SQL 优化时帮助我们了解时间都耗费到哪里去了。

查看当前 MySQL 是否支持 profile 操作：

SELECT @@have_profiling;

查看当前 MySQL 中 profile 的状态（默认关闭）：

select @@profiling;

开启 MySQL 中的 profile：

SET profiling = 1;

        经过以上指令后，profile 开关就已经打开了，接下来，我们所执行的 SQL 语句，都会被 MySQL 记录，并记录每条 SQL 语句执行的时间，通过以下指令可以查看：

-- 查看每一条 SQL 的耗时基本情况 
show profiles; 
-- 查看指定 query_id 的 SQL 语句各个阶段的耗时情况 
show profile for query query_id; 
-- 查看指定 query_id 的 SQL 语句 CPU 的使用情况 
show profile cpu for query query_id;

explain 执行计划

        EXPLAIN 或者 DESC 命令可以获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。使用格式如下：

-- 直接在select语句之前加上关键字 explain / desc 
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;

sql 的索引 range sql索引使用规则_database_14

Explain 执行计划中各个字段的含义：

        字段

                                             含义

id

select 查询的序列号，表示查询中执行 select 子句或者是操作表的顺序( id 相同，执行顺序从上到下；id 不同，值越大，越先被执行)。

select_type

表示 SELECT 的类型，常见的取值有 SIMPLE （简单表，即不使用表连接或者子查询）、PRIMARY （主查询，即外层的查询）、UNION（ UNION 中的第二个或者后面的查询语句）、SUBQUERY（ SELECT/WHERE 之后包含了子查询）等

type

表示连接类型，性能由好到差的连接类型为NULL、system、const(主键/唯一索引)、eq_ref、ref(非唯一索引)、range、 index(遍历索引)、all(全表扫描)

possible_key

显示可能应用在这张表上的索引，一个或多个。

key

实际使用的索引，如果为NULL，则没有使用索引。

key_len

表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好 

rows

MySQL 认为必须要执行查询的行数，在 innodb 引擎的表中，是一个估计值，可能并不总是准确的

filtered

表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好。

六、索引的使用及原则

最左前缀法则

联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(即跳跃之后的字段索引失效)。 接下来我们通过一个示例来更清楚地理解最左前缀法则：

        例如：我们可以创建一个联合索引：CREATE INDEX index_name ON table_name(index1, index2, index3)，这个联合索引中涉及到的三个字段，按顺序分别为：index1，index2，index3，其中 index1 和 index3 是字符串类型，index2 是 int 类型。


        对于最左前缀法则指的是，查询时，最左变的列，也就是 index1 必须存在，否则索引全部失效。 而且中间不能跳过某一列，否则该列后面的字段索引将失效（而前面连续的几个字段索引依然生效）。下面我们通过几个 SQL 语句的示例看一下具体实现：



全部生效：


EXPLIAN select * from tb_user where index1 = '****' and index2 = **** and index3 = '****';

index1 和 index2 都生效：

EXPLIAN select * from tb_user where index1 = '****' and index2 = ****;

都不生效（无最左侧字段索引）：

EXPLIAN select * from tb_user where index2 = **** and index3 = '****';

index1 生效，index3 失效（存在最左侧前缀，但中间有跳跃，跳跃之后的索引字段失效）：

EXPLIAN select * from tb_user where index1 = '****' and index3 = '*****';

全部生效（索引是否生效和编写在 SQL 语句中的位置无关，只看是否存在最左侧的索引字段，且 SQL 语句中的索引字段是否按照联合索引的顺序存在）：

EXPLIAN select * from tb_user where index3 = '****' and index2 = **** and index1 = '****';

注意：这里所说的左侧、右侧、中间跳跃等，是按照索引字段在联合索引中的顺序（即创建索引语句中括号里的顺序）为依据的，并不是索引字段在 SQL 语句中的位置为依据的。

范围查询

范围查询右侧的列索引失效。

-- index1 和 index2 生效，index3 失效，因为 index2 使用了范围查询，其右侧的索引字段将会失效
EXPLIAN select * from tb_user where index1 = '****' and index2 > **** and index3 = '****';

注意：当范围查询使用 >= 或 <= 时，并不存在范围查询的规则，其索引字段是可以生效的，所以，在业务允许的情况下，尽可能的使用类似于 >= 或 <= 这类的范围查询，而避免使用 > 或 <

索引失效的情况

1.　不要在索引列上进行运算操作， 否则索引将失效。

--　索引失效，使用了 substring() 运算操作
EXPLAIN　select * from tb_user where substring(phone, 10, 2) = '15';

2.　字符串类型字段使用时，不加引号，索引将失效。

-- index3 的索引失效，其他索引生效，因为 index3 是字符串类型的数据，没有加''会导致该索引失效
EXPLAIN select * from tb_user where index1 = '****' and index2 = **** and index3 = ****;

3.　如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

-- 索引生效，因为模糊查询是尾部模糊
EXPLAIN select * from tb_user where index1 = '软件%';
-- 索引失效，因为模糊查询是首部模糊
EXPLAIN select * from tb_user where index1 = '%工程';

4.　or 连接条件：用 or 分割开的条件， 如果 or 前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

-- 索引全部失效，因为使用了 or 作为连接条件且 col 字段没有索引
EXPLAIN select * from tb_user where index1 = '****' or col = '****';

5.　数据分布影响：如果 MySQL 自动评估使用索引查询比全表扫描更慢，则不使用索引，而使用全表扫描。
        即 MySQL 在查询时，会评估使用索引的效率与走全表扫描的效率，如果走全表扫描更快，则放弃索引，走全表扫描。 因为索引是用来索引少量数据的，如果通过索引查询返回大批量的数据，则还不如走全表扫描来的快，此时索引就会失效。

SQL 提示

        SQL 提示，是优化数据库的一个重要手段，简单来说，就是在 SQL 语句中加入一些人为的提示来达到优化操作的目的。

        假设 index1 既存在于联合索引中，也存在属于自己的连列索引，我们就可以通过相应的命令人为地决定我们想要使用或禁止的索引：


1.　use index ： 建议 MySQL 使用哪一个索引完成此次查询（仅仅是建议，mysql 内部还会再次进行评估） 

EXPLAIN select * from tb_user use index(index_name) where index1 = '软件工程';


2.　ignore index ： 忽略指定的索引。

EXPLAIN select * from tb_user ignore index(index_name) where index1 = '软件工程';

3.　 force index ： 强制使用索引。

EXPLAIN select * from tb_user force index(index_name) where index1 = '软件工程';

覆盖索引

覆盖索引是指：查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到 。

在查询语句的执行计划 explian 子表中的  extral 字段中可能会显示以下两种数据：

sql 的索引 range sql索引使用规则_数据库_15

sql 的索引 range sql索引使用规则_sql_16

前缀索引

        当字段类型为字符串（varchar，text，longtext 等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘 IO， 影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。 

创建前缀索引的语法如下：

create index idx_xxxx on table_name(column(n)) ;

选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高， 唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。查看选择性的公式如下：

-- 按照此索引查询的不重复数据数量 / 总数居数量
select count(distinct index1) / count(*) from tb_user ; 
select count(distinct substring(index1,1,5)) / count(*) from tb_user ;

下面我们来看一看前缀索引的查询流程：

sql 的索引 range sql索引使用规则_sql_17

在具体的业务设计中，我们该遵循怎样的设计原则呢？

  1. 针对于数据量较大，且查询比较频繁的表建立索引。
  2. 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
  3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
  4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
  5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
  6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
  7. 如果索引列不能存储 NULL 值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含 NULL 值时，它可以更好地确定哪个索引最有效地用于查询。