[大数据]MySQL 索引及使用规则（优化sql的关键）

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

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

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

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

一、索引概述

什么是索引？

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

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

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

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

二、索引的结构

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

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

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

B 树

B+ 树

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

我们可以看到，两部分：

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

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

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

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

hash

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

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

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 支持范围匹配及排序操作；

三、索引的分类

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

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

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

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

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

---

四、索引的语法

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_______';

????????通过上述指令，我们可以查看到当前数据库到底是以查询为主，还是以增删改为主，从而为数据库优化提供参考依据。 如果是以增删改为主，我们可以考虑不对其进行索引的优化。 如果是以查询为主，那么就要考虑对数据库的索引进行优化了。

慢查询日志

????????慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有 SQL 语句的日志。在 MySQL 中默认是没有开启慢查询日志的，若想要开启，则需要在 MySQL 的配置文件（?/etc/my.cnf?）中配置如下信息：

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

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

show variables like 'slow_query_log';

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

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 条件 ;

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

六、索引的使用及原则

最左前缀法则

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

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

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 既存在于联合索引中，也存在属于自己的连列索引，我们就可以通过相应的命令人为地决定我们想要使用或禁止的索引：

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 = '软件工程';

覆盖索引

????????尽量使用覆盖索引，减少 select *。 那么什么是覆盖索引呢？ 覆盖索引是指：查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到 。

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

前缀索引

????????当字段类型为字符串（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 ;

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

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

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

创作打卡挑战赛

赢取流量/现金/CSDN周边激励大奖