在 Linux 上成功搭建好 MySQL 环境后，我们即将正式踏入进阶篇中最为核心且重要的章节 —— 索引。在 MySQL 优化过程中，索引优化占据着举足轻重的地位，因此，深入理解索引相关知识尤为关键。本次关于索引的讲解将从七个方面展开，下面首先进入第一个方面：索引概述。

一、索引概述

索引，英文称作 “index”，是一种能助力 MySQL 高效获取数据的有序数据结构。在数据库系统里，除了存储原始数据，还需维护索引这种数据结构，它可指向原始数据，以便运用高效查找算法快速定位到原始数据。常见的数据结构如二叉树、红黑树、B 树、B + 树等，那 MySQL 到底采用哪种来实现索引结构呢，下面会详细说明。

1.1 无索引与有索引的查询对比

为为更直观理解索引的作用，来看个实例。假设有张user表初始未创建索引，执行查询语句 “SELECT * FROM user WHERE age = 45” 时，查询过程是从表的第一条记录开始逐一匹配，判断 age 是否为 45，不是就继续查下一条，如此循环直至整张表数据匹配完，这就是全表扫描，性能极低。

而当为 age 字段创建索引后（这里以二叉树为例演示，并非 MySQL 真实索引结构），每插入一条数据，都会在二叉树中维护相应节点，该节点指向数据行地址。比如插入 22、33、48、53 等数据后形成特定二叉树结构。再次执行上述查询语句时，只需将 45 与二叉树节点数据比对，如先与 36 比对，因 45 大于 36 走向右边分支，再与 48 比对，因 45 小于 48 走向左侧，最终定位到 45 对应的记录。有索引时，仅需三次匹配就能定位到数据，搜索效率显著提高。

1.2 索引的优缺点

1）优点

  • 提升查询效率：索引能极大加快数据查询速度，减少查询所需时间，降低数据库的 I/O 成本。因为数据库数据存于磁盘，查询涉及磁盘 I/O，索引可有效减少磁盘读取次数。

  • 优化排序操作：若通过索引列对数据进行排序，可降低排序成本，减少 CPU 的消耗。

2）缺点

  • 占用磁盘空间：索引作为数据结构，其索引列需存储在磁盘中，虽磁盘空间相对廉价，但仍会占用一定空间。

  • 影响增删改效率：执行插入（INSERT）、更新（UPDATE）、更新（DELETE）操作时，因需维护索引结构，比如插入数据时要在索引中增加节点，删除数据时要删除相应节点，这会对增删改操作的效率产生一定影响。不过在通常业务系统中，考虑到磁盘成本较低且增删改操作占比相对较小（主要以查询操作为主），这两点弊端往往可被忽略。

二、索引结构

2.1 不同存储引擎下的索引结构种类及支持情况

MySQL 的索引是在存储引擎层实现的，不同存储引擎有着不同的索引结构，主要可分为以下四种：

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

2.2 为什么InnoDB存储引擎选择使用B+tree索引结构

对比二叉树：

  • 二叉树每个节点只有两个子节点，在存储大量数据时，二叉树的高度会很高。

  • 二叉树在插入或删除数据时，可能会导致树的结构发生较大变化，特别是在顺序插入时，可能会退化成链表，导致查询性能急剧下降。

对比红黑树：

  • 红黑树本质上也是二叉树，虽然它是自平衡的，但同样受到每个节点只有两个子节点的限制。在数据量较大时，红黑树的高度仍然可能较高，导致较多的磁盘 I/O 操作。

  • 红黑树在插入和删除数据时需要进行复杂的平衡调整操作，如变色和旋转。这些操作在频繁的数据更新场景下可能会消耗较多的 CPU 时间。

对比 B 树：

  • B 树和 B + 树都属于多路平衡查找树，在磁盘 I/O 方面都有较好的性能。但是 B + 树的非叶子节点只存储索引信息，而 B 树的节点既存储索引信息也存储数据。这使得 B + 树的非叶子节点可以存储更多的索引，在相同的数据量下，B + 树的高度可能会比 B 树更矮，进一步减少磁盘 I/O 次数。

对比Hash：

  • Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，...）

  • 无法利用索引完成排序操作

  • 查询效率高，通常只需要一次检索就可以了，效率通常要高于B+tree索引

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

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

三、索引分类

3.1 常见索引类型

  1. 主键索引：主键索引是基于表中的主键所创建的索引。在创建表并指定主键时，系统会自动针对该主键生成一个索引，其对应的关键字为 “PRIMARY”。主键索引具有唯一性，在一张表中仅能存在一个。它的存在不仅确保了数据记录的唯一性标识，更为数据的快速检索提供了高效的路径。

  2. 唯一索引：唯一索引的作用在于防止同一张表中某一个字段的值出现重复。与主键索引不同的是，一张表中可以创建多个唯一索引，对应的关键字为 “UNIQUE”。值得一提的是，当在某个字段上施加唯一约束时，数据库会自动为该字段创建一个唯一索引，这一特性在保障数据完整性的同时，也提升了特定查询场景下的效率。

  3. 常规索引：常规索引的核心目的是加速数据的定位与检索。在实际的数据库表设计中，往往会根据业务需求创建多个常规索引，以优化不同条件下的查询操作，它为数据的快速查找提供了有力支持。

  4. 全文索引：全文索引主要用于在文本数据中查找特定的关键字，而非针对索引中的普通值进行检索，其对应的关键字为 “FULLTEXT”。不过，在实际应用场景中，全文索引的使用相对较为少见。

3.2 InnoDB 存储引擎中的索引形式

在 InnoDB 存储引擎的语境下，索引依据其形式可进一步细分为以下两种：

  1. 聚集索引（Clustered Index）：聚集索引的显著特征是将数据存储与索引存储整合为一体。在这种索引结构中，B + 树的叶子节点直接保存了完整的行数据。由于数据的存储依赖于聚集索引的组织方式，所以在一张表中，聚集索引是必不可少的，并且只能有一个。其重要性不言而喻，它决定了数据在磁盘上的物理存储顺序，直接影响数据的读写效率。

  2. 二级索引（Secondary Index）：二级索引，也常被称为辅助索引或非聚集索引，其数据存储与索引存储是相互分离的。二级索引的 B + 树叶子节点并不存储行数据本身，而是关联对应的主键值。在一张表中，可以根据实际需求创建多个二级索引，它们为数据检索提供了多样化的途径。

在创建 InnoDB 引擎的表结构时，如果未明确指定哪个索引为聚集索引，哪个为二级索引，数据库会遵循特定的规则来确定聚集索引。聚集索引的选取规则如下：

  • 若表中存在主键，则主键索引自动成为聚集索引。

  • 若表中不存在主键，InnoDB 存储引擎会尝试使用第一个唯一索引作为聚集索引。

  • 倘若既无主键又无唯一索引，InnoDB 存储引擎将自动生成一个隐藏的 ROWID 作为聚集索引，从而确保聚集索引的必然存在性。

clustered_secondary_index.png

四、索引语法

4.1 创建索引

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

4.2 查看索引

SHOW  INDEX  FROM  table_name ;

4.3 删除索引

DROP  INDEX  index_name  ON  table_name ;

五、SQL性能分析

5.1 SQL执行频率

SQL 性能分析工具首先要了解 SQL 的执行频率。为什么要知道 SQL 的执行频率呢？大家思考一个问题，假如有一个业务系统，这个业务系统有一个数据库，其中一些表的数据量特别大。但是，如果这张表只进行插入操作，不进行查询操作。那么，此时对其进行索引优化和 SQL 优化有必要吗？没有太大必要，因为只插入不查询。所以，我们要通过 SQL 的执行频率来判定当前数据库是以插入为主、更新为主、删除为主还是查询为主。我们要查看增删改查在当前数据库中所占的比例。对于以查询为主的数据库，我们就需要考虑进行优化；对于以增删改为主的数据库，我们可以适当减轻优化的比重。

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

SHOW  GLOBAL STATUS LIKE  'Com_______';

在这个结果中，我们主要关注以下几项信息：insert（插入）、select（查询）、update（更新）和 delete（删除）。这四项指标分别代表当前数据库的插入、查询、更新和删除次数。

通过这条指令，我们就能知道当前数据库到底是以插入为主、查询为主、更新为主还是删除为主。如果我们发现一个数据库的查询操作占用了绝大部分，那么此时我们就需要针对这一类数据库中的 SQL 进行优化。

这是我们介绍的第一种性能分析工具，通过这个指令查看 SQL 的执行频率，为我们的 SQL 优化提供支撑。

5.2 慢查询日志

若已确认当前数据库中 SQL 语句的执行频次里 select（查询）占用绝大部分，此时需针对当前数据库进行优化。那么应该针对当前数据库的哪些 SQL 进行优化呢？因为我们仅知晓查询 SQL 的权重较高，但具体哪些 select 语句需要优化并不明确。此时，我们就需要借助数据库的慢查询日志来定位 SQL 语句，以确定哪些 SQL 语句执行效率较低，从而对这类 SQL 进行优化。

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。

MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

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

配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息 /var/lib/mysql/localhost-slow.log。

5.3 profile详情

我们通过 MySQL 提供的慢查询日志，可以定位出哪些 SQL 语句执行耗时较长，从而对这类 SQL 语句进行优化。但是慢查询日志中所记录的 SQL 语句是执行耗时超过预设指定时间后才会记录。例如，预设指定时间为2秒，那么超过2秒才会记录在慢查询日志中。假如有一些 SQL 语句，其执行耗时比如为 1.8 秒、1.9 秒、1.95秒等，这类 SQL 不会记录在慢查询日志中。假如业务系统中有一些 SQL 语句，其业务很简单，但执行时间达到了1.9几秒，这类 SQL 实际上性能也相对较低，我们也需要对其进行优化，那如何定位到这类 SQL 呢？慢查询日志满足不了需求时，我们可以借助另一种手段，叫做 profile 详情。

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作：

SELECT  @@have_profiling;

默认profiling是关闭的，可以通过set语句在session/global级别开启profiling：

SET profiling = 1;

执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时：

#查看每一条SQL的耗时基本情况
show profiles;

#查看指定query_id的SQL语句各个阶段的耗时情况
show profile  for  query query_id;

#查看指定query_id的SQL语句CPU的使用情况
show profile  cpu for  query query_id;

这就是我们在此介绍的 profile 详情。通过 profile 详情，我们可以知道每一条 SQL 语句的耗时以及耗时耗费在哪个阶段。

5.4 explain执行计划

前面我们已经讲解了三种 SQL 语句性能分析的工具，分别用于查看 SQL 语句的执行频次、查看慢查询日志以及通过“show profiles”查看 SQL 语句的耗时以及时间耗费在哪里。这些都是通过时间层面来评判一条 SQL 语句的性能，执行时间短说明 SQL 语句的性能高，执行时间长则说明 SQL 语句的执行性能低。实际上，这种判定只是粗略的进行判定，并不能真正评判一条 SQL 语句的性能。若要查看一条 SQL 语句的性能，还需要借助第四种手段——通过 explain 查看 SQL 语句的执行计划。

在 SQL 优化中，“explain”占据着非常重要的地位。我们经常通过它来判定 SQL 语句的性能，通过 explain 可以查看到 SQL 语句的执行计划，包括执行过程中是否用到了索引、表的连接情况以及表的连接顺序等。其语法是在任意的 select 语句之前加上关键字“explain”或者“desc”，我们比较常用的是“explain”。打开终端，执行一条 SQL 语句“select * from tb_user where id = 1”，要查看这条 SQL 语句的执行计划，只需要在 select 语句前面加上“explain”即可，这就是当前 select 语句的执行计划。

语法：

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

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 的值越大越好。

通过对 “explain” 执行计划各字段的分析，我们能够全面深入地评估 SQL 语句的性能，从而有针对性地进行优化，提升数据库的整体运行效率。

综上所述，SQL 性能分析需要综合运用多种工具，从执行频率、慢查询日志、profile 详情到 explain 执行计划，全方位、多层次地评估 SQL 语句性能，精准定位问题并实施优化策略，以确保数据库在复杂业务环境下始终保持高效稳定运行。

六、索引使用

6.1 最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。

如果跳跃某一列，索引将部分失效(后面的字段索引失效)。

explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';
explain select * from tb_user where profession = '软件工程' and age = 31;
explain select * from tb_user where profession = '软件工程';
explain select * from tb_user where age = 31 and status = '0';
explain select * from tb_user where status = '0';

6.2 范围查询

联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。

explain  select  *  from  tb_user  where  profession = '软件工程'  and  age > 30  and  status = '0';
explain  select  *  from  tb_user  where  profession = '软件工程'  and  age >= 30  and  status = '0';

6.3 索引列运算

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

explain  select  *  from  tb_user  where  substring(phone,10,2)  =  '15';

6.4 字符串不加引号

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

explain select * from tb_user where profession = '软件工程' and age = 31 and status = 0;
explain select * from tb_user where phone = 17799990015;

6.5 模糊查询

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

explain  select  *  from  tb_user  where  profession like '软件%';
explain  select  *  from  tb_user  where  profession like '%工程';
explain  select  *  from  tb_user  where  profession like '%工%';

6.6 or连接的条件

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

explain select * from tb_user where id = 10 or age = 23;
explain select * from tb_user where phone = '17799990017' or age = 23;

由于age没有索引，所以即使id、phone有索引，索引也会失效。所以需要针对于age也要建立索引。

6.7 数据分布影响

如果MySQL评估使用索引比全表更慢，则不使用索引。

select * from tb_user where phone >= ‘17799990005’;
select * from tb_user where phone >= '17799990015';

6.8 SQL提示

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

  • use index：explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';

  • ignore index：explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';

  • force index：explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';

6.9 覆盖索引

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少select *。

explain  select id, profession from  tb_user  where  profession = '软件工程‘  and  age = 31  and  status = '0‘ ;
explain  select  id,profession,age, status  from  tb_user  where  profession = '软件工程‘  and  age = 31  and  status = '0‘ ;

explain  select  id,profession,age, status, name  from tb_user where profession = '软件工程'  and age = 31  and  status = '0‘ ;
explain  select  *  from  tb_user  where  profession = '软件工程‘  and  age = 31  and  status = '0‘ ;

知识小贴士：

  • using index condition ：查找使用了索引，但是需要回表查询数据

  • using where; using index ：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

6.10 前缀索引

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

  • 语法：

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

  • 前缀长度

    可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高， 唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

    select  count(distinct email) / count(*)   from  tb_user ;
select  count(distinct substring(email,1,5)) / count(*)  from  tb_user ;

6.11 单列索引与联合索引

  • 单列索引：即一个索引只包含单个列。

  • 联合索引：即一个索引包含了多个列。

在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。

  • 单列索引情况：

 explain  select  id, phone, name  from  tb_user  where  phone =  '17799990010'  and  name  =  '韩信' ;

  • 联合索引情况：

七、索引设计原则

针对于数据量较大，且查询比较频繁的表建立索引。

针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。

尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。

如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。

尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。

要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。

如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。