MySQL进阶篇之二：索引

索引

一、索引概述

• 介绍

索引（index）是帮助MySQL高效获取数据的数据结构（有序）。

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

• 演示查找语句：select * from user where age=45;

• 无索引：在无索引的情况下，我们需要对整张表从第一个指针位开始，依次查找表中的所有字段，就算有一个字段的age=45，我们仍然需要查下去，因为后面的地址中可能还会存在age=45的字段。
  -

• 有索引：我们构思一个二叉排序树，建表的时候将每个字段的age值抽象到二叉排序树中，然后再次执行select操作的时候可以直接在二叉排序树进行高效的查找。

备注：上述二叉树索引结构只是一个示意图，并不是真的索引结构。

• 优缺点

二、索引结构

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

• 不同索引支持的存储引擎

在后续的操作中，如果我们没有特意指定是哪种存储引擎、哪种索引结构，就默认是InnoDB下的B+tree索引。

2.1二叉树结构

在讲解B+tree之前，先介绍一下二叉排序树。

二叉排序树：在插入结点的时候，如果当前节点比根结点小，则插入到根结点的左孩子位置。

从上图左侧我们可以看到，如果数据的组成是杂乱无章，且复杂的，使用二叉排序树检索17这个数字，我们只需要查找四次即可。

但是如果数据的组成是有规律的，且不复杂的，如右侧组成的二叉排序树，数据依次排开形成链表结构，那么此时我们再去检索17这个数字速度就会非常慢。

所以二叉树的缺点就是，顺序插入时，会形成一个链表，查询性能大大降低。在大数据量的情况下，层级较深，检索速度慢。

2.2B-Tree结构

B树其实就是一种多路平衡查找树。

多路：指一个结点下面可以包含多个子节点。

举例：以一颗最大度数（max-degree）为5（5阶）的b-tree为例（每个节点最多存储4个key，5个指针）

度：即树的度数，指一个节点的子节点个数

例如下面这个示意图，第一个节点位置存储了四个数据，五个指针，五个指针指向的子节点分别为：小于20、大于20小于30，大于30小于62，大于62小于89。

然后子节点再实现这种结构，这就是B-Tree。

这里我直接给出了插入一段数据形成的B-Tree结构

具体动态变化的过程可以参考网站：https://www.cs.usfca.edu/~galles/visualization/BTree.html

2.3B+Tree结构

以一颗最大度数为4（4阶）的b+tree为例：

• 1、B+Tree中所有元素都会出现在叶子结点
• 2、上半部分绿框所示的分叶子结点只是起到了索引作用
• 3、最终的叶子结点才是存放数据的
• 4、叶子结点形成了一个单向链表

• MySQL对B+树的优化

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

通俗的说，就是将叶子结点从单链表结构优化成了双向循环链表结构。

2.4Hash结构

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

举例：给出一张表，表中有三个字段，id，name，age，id为主键，现在我们想为name字段创建一个hash索引的数据结构。

• 1、先算出这张表当中每一行数据的哈希值。
• 2、拿到name字段的所有值。
• 3、针对name字段的所有值，通过内部的hash函数去计算每一个name值应该落在哪一个哈希表的槽位上。
• 4、例如算出’金庸’该name字段的槽位值是005，此时在这个槽位中存储’金庸’这个key，以及’金庸’这个key对应的该行的哈希值。

在大数据量的情况下，如果两个（或多个）键值，映射到一个相同的槽位上，他们就产生了hash冲突，可以通过链表来解决。

• Hash索引特点

  • 1、hash索引只能用于对等比较（=，int），不支持范围查询（between，>，<，…）
  • 2、无法利用索引完成排序操作
  • 3、查询效率高，通常只需要一次检索就可以了（不出现哈希冲突的情况下），效率通常要高于B+tree索引

• 存储引擎支持

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

2.5思考

• 为什么InnoDB存储引擎选择使用B+Tree索引结构？
  • 1、相对于二叉树：B+Tree层级更少，搜索效率高。
  • 2、相对于B-Tree：B-Tree结构无论是叶子结点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低。
  • 3、相对于Hash索引：B+Tree支持范围匹配以及排序操作。

三、索引分类

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

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

举例：

• 回表查询

回表查询指的是：先根据二级索引拿到主键值，再根据主键值走聚集索引拿到这一行的数据。

举例：select * from user where name = ‘Arm’;

先根据name=‘Arm’，到二级索引中查找‘Arm’字段，找到‘Arm’字段后，根据该字段存储的主键值，到聚集索引查找id为10的row数据。

• 思考

1、以下SQL语句，哪个执行效率高？为什么？

select * from user where id = 10;
select * from user where name = 'Arm';
#id为主键，name字段创建的有索引。

第一条sql语句执行的效率高，因为第一条sql可以直接到聚合索引中查数据，然后拿到row结果；

但是第二条数据需要先到二级索引中查‘Arm’字段，根据查到的字段，拿到对应的主键值，再回到聚合索引中查row。也就是需要执行回表查询，效率不如直接查主键高。

2、InnoDB主键索引的B+Tree高度为多高？

四、索引语法

4.1创建索引

create [unique|fulltext] index 索引名 on 表名(字段名1,...);

在INDEX之前我们可以加上关键字UNIQUE或FULLTEXT，UNIQUE表示创建的是一个唯一索引，FULLTEXT表示创建的是一个全文索引。这两个关键字是一个可选项，如果不加这两个指定索引关键字的话，表示创建的是一个常规索引。

INDEX后是索引名称。

ON指定表名，以及表中的字段名；

一个索引可以关联多个字段，如果一个索引只关联一个字段，这种索引称之为单列索引，如果关联多个字段的话，称为联合索引或组合索引。

4.2查看索引

show index from 表名;

4.3删除索引

drop index 索引名 on 表名;

4.4案例

按照下面的需求，完成索引的创建。

• 涉及的表结构

• 1、name字段为姓名字段，该字段的值可能会重复，为该字段创建索引。

create index idx_user_name on tb_user(name);

• 2、phone手机号字段的值，是非空且唯一的，为该字段创建唯一索引。

create unique index idx_user_phone on tb_user(phone);

• 3、为profession、age、status创建联合索引。

create index idx_user_pro_age_sta on tb_user(profession,age,status);

• 4、为email建立合适的索引来提升查询效率

create index idx_user_email on tb_user(email);

五、SQL性能分析

5.1查看执行频次

• SQL执行频率

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

show global status like 'Com____';

'__'表示一个模糊查询字符占位。

举例：

5.2慢查询日志

如果我们现在想对当前数据库进行优化，我们应该对哪些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。

当我此时查询一个超过千万级别的数据表的count数据时，tail命令实时跟踪的slow.log文件输出的内容：

5.3show profiles

我们通过MySQL中提供的慢查询日志可以定位出哪些SQL语句的执行耗时比较长，从而对这一类SQL语句进行优化；

但是慢查询日志中记录的SQL语句是执行耗时超过了我们预设的指定时间之后才会记录；

比如我们设定的预设时间为2s，但是系统中有一些SQL语句的执行时间达到了1.99s或1.9999s，这些SQL语句并不会被记录在慢查询日志中，但是这些SQL语句性能同样很低，我们找到这些语句并对其进行优化。

• profile详情

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

通过have_profiling参数，能够看到当前MySQL是否支持profile操作：

select @@have_profiling;

我们默认情况下profiling是关闭的（0表示当前profiling是关闭的），可以通过set语句在session/global级别开启profiling：

set profiling = 1;

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

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

执行show profiles;

执行show profile for query query_id;

5.3explain执行计划

explain或者desc命令获取MySQL如何执行select语句的信息，包括在select语句执行过程中表如何连接和连接的顺序。

语法：

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

• EXPLAIN执行计划各字段含义

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

  

id值相同的情况：

这是给出的三张表，student和course表通过student_course表进行关联查询

可以看到我们的sql查询执行的顺序为，s、sc、c。

explain select s.*,c.* from student s,course c,student_course sc
where s.id = sc.studentid and c.id = sc.courseid;

id值不同的情况：

查询选修了MySQL课程的学生（用子查询）

首先查询MySQL课程的id号:

select id from course c where c.name = 'MySQL';

从关联表查询选修了该课程同学的id：

select studentid from student_course sc
where sc.courseid=(select id from course c where c.name = 'MySQL');

最后根据拿到的studentid再去查学生的信息：

select * from student s
where s.id in(select studentid from student_course sc where sc.courseid=(select id from course c where c.name = 'MySQL'));

• select_type：表示select的类型，常见的取值有simple（简单表，即不使用表连接或子查询）、primary（主查询，即外层的查询）、union（union中第二个或后面的查询语句）、subquery（select/where之后包含了子查询）等
• type：表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、range、index、all
  • 对唯一索引的查询是const
  • 对非唯一索引的查询是ref
• possible_key：显示可能应用在这张表上的索引，一个或多个
• key：显示实际用到的索引，如果为NULL，即没有使用索引
• key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。
• rows：MySQL认为必须要执行查询的行数，在innoBD引擎的表中，是一个估计值，可能并不总是准确的。
• filtered：表示返回的结果行数占需读取行数的百分比，filtered的值越大越好。

六、索引使用

6.1验证索引效率

在未建立索引之前，执行如下SQL语句，查看SQL的耗时。

select * from tb_sku where sn = '10000003145001';

耗时长达21s！！性能极低。

还是查询这条数据，但是我们通过id查询：

select * from st_sku where id = 1;

我们可以发现，同样是查询一条数据，用主键id为条件查询，效率极高。

这是因为，我们的id是主键，主键默认建立了主键索引，但是sn字段没有索引。

那么现在我们对sn字段创建索引，与刚刚的查询时间进行对比：

create index idx_sku_sn on tb_sku(sn);

此时再次执行查询操作：

效率提升了2000倍…

6.2最左前缀法则

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

如果在查询过程中跳过了某一列，索引将部分失效（后面的字段索引失效）。

举例：给出一张tb_user表，其中的profession、age、status三个字段用了联合索引，其中profession为第一个字段，age为第二个字段，status为第三个字段。

现在我们依次对以下五条SQL进行执行，并执行explain操作，查看索引的使用情况。

①执行第一条SQL，我们发现key_len为57，且三个字段的索引全部用上了。

②执行第二条SQL，即删去status字段筛选条件，我们发现key_len为49，所以我们可以推测status字段的索引长度为5。

③执行第三条SQL，即删除age和status字段筛选条件，我们发现key_len为47，所以可以推测status字段的索引长度为5，age字段的索引长度为2。

④执行第四条SQL，即删除profession字段筛选条件，根据age和status来查，我们发现并没有走索引，走的是全表扫描。这是因为没有满足最左前缀法则，最左边的列为profession，没有存在。

⑤如果我们只删除age字段筛选条件，即用profession和status来查，我们发现索引长为47，即之前推断的profession字段的索引长度，status索引并没有执行，这是因为我们跳过了age索引，所以导致索引部分失效，即age之后的索引失效。

6.3范围查询

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

举例：我们执行下面这两条SQL语句。

①执行第一条语句，我们发现索引长度为49，即status字段没有走索引，这是因为age字段用了范围查询，导致右侧的列索引失效。

②如果想规避这种情况，我们尽量使用>=或<=，可以解决。

6.4索引失效情况

6.4.1索引列运算

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

举例：

在正常情况下我们对phone字段进行查找，用到了phone索引

此时我们对该SQL语句执行函数运算，即用substring（）函数截取phone字段的数值进行匹配，发现索引失效

6.4.2字符串不加引号

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

举例：

还是对phone字段进行查询操作，我们的phone字段是一个varchar类型的，现在模拟不加引号的情况，我们发现，possible_key可能存在的索引确实为phone索引，但是实际key却为null。

6.4.3模糊查询

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

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

举例：

尾部模糊匹配

头部模糊匹配索引失效

6.4.4or连接的条件

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

举例：执行下面这两条SQL

注意：我们的age字段使用的是联合索引，并没有单独的索引。

执行第一条SQL，我们发现虽然id为主键索引，但是age的没有单独的age索引，所以此时索引不生效。

同样的，执行第二条SQL我们会得到同样的结果。

由于age没有索引，所以即使id、phone有索引，索引也会失效。如果想解决这个问题，我们只需要针对age也建立一个索引即可。

6.4.5数据分布影响

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

6.5 SQL提示

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

例如，我现在给之前的profession字段新增一个索引idx_user_pro，此时该字段拥有两个索引。

那么我现在执行查询语句，该字段会执行哪个索引？

显而易见，执行了联合索引。那么如果我想指定该字段执行我想要执行的索引，需要进行什么操作？这就是我们的SQL提示功能，即“给SQL提示”。

• use index（建议数据库使用该字段的哪个索引，并不是强制，MySQL会自己对速度、效率进行权衡）

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(inx_user_pro) where profession = '软件工程';

6.6覆盖索引

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

举例：where筛选条件都一样，我们分析select查询返回的对象不同的形式。

注意：

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

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

通过执行四条SQL我们发现，①②④三条语句的extra为using where；using index；

但是第③条语句的extar为using index condition；

• 解释

在第④条SQL语句中，我们直接通过聚集索引拿到了整行的数据，不需要进行回表查询。

原理解释：

在第①②条语句中，因为profession、age、status三个字段是联合索引，我们在查询的过程中，直接通过二级索引，既拿到了联合索引三个字段的值，又拿到了对应的主键id值，不需要走回表查询。

原理解释：

在第③条语句中，多出来了一个name字段！name字段在我们的二级索引中是没有的，此时再根据where筛选条件进行查询，我们不仅要在二级索引中拿到联合索引的各个字段的值，id主键的值，并且还要通过回表查询回到聚集索引，根据已经拿到的id的值，再去查询整张表的数据，然后再拿到name字段的值。

原理解释：

• 思考

一张表，有四个字段（id、username、password、status），由于需求量大，需要对以下SQL语句进行优化，该如何进行才是最优方案？

select id,username,password from tb_user where username = 'itcast';

在id为主键索引的前提下，创建usernmae和password的联合索引即可。

6.7前缀索引

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

• 语法

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

和之前创建索引的语法基本一致，我们只需要在对应的字段后面加上一个n，表示提取该字符串前面的n个字符来构建索引。

• 前缀长度

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

求选择性公式：

#该表中不重复的email字段数量/该表的总字段数量
select count(distinct email)/count(*) from tb_user;
#表示截取emial字段的前五个字符，求这五个字符的选择性
select count(distinct substring(email,1,5))/count(*) from tb_user;

七、索引设计原则

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