MySQL进阶 (3)：索引的结构优化

索引使用策略及优化

MySQL的优化主要分为结构优化 和 查询优化。

查询优化就是对就是对查询的 sql语句进行优化，运用查询技术实现数据操纵功能的过程，是确定给定查询的高效执行计划的过程。本文主要讲述结构优化。

☁ 为什么要优化？

虽然提高查询性能最直接有效的方法就是建立索引，但是不必要的索引会浪费空间，同时也增加了额外的时间成本去判断应该走哪个索引。

此外，索引还会增加插入、更新、删除数据的成本，因为做这些操作的同时还要去维护(更新)索引树。因此，应该学会使用最佳索引集来优化查询

先前条件

演示索引结构优化前，先准备示例数据库和知晓mysql提供的分析SQL执行效率的执行计划

☛ 准备：示例数据库

这里我用的是mysql官方提供的数据库：employees，相关下载和导入方式我之前的博文有发表，请参考：MySQL官方示例数据库的使用/导入

总共6张表，其关系图如下：

 

索引优化一：最左前缀原理

复合索引有最左匹配原则，假设数据库中建立了复合索引： (a, b, c)

那么 where a, b, c、where a, b、where a 都会走索引。但是 where b, c 就不走索引了，因为按照左前缀原则，必须要出现 a 才行。

行数	查询条件	实际索引使用
①	a, b, c	a, b, c
②	a, c	a
③	a, b	a, b
④	a	a
⑤	b, c	无

✦ 解析

• 第②行：虽然 a, c 是联合索引( a, b, c) 中有两个字段，但未出现 b，按照左前缀原则就断开了，所以只能使用到 a
• where/order 后的顺序可随意组装，比如 a, b, c 可以写成 a,c,b 或者 c,a,b 等，不影响结果，跟出现的字段有关

► 语句示例

 我们拿官方提供的employees数据库中的titles表做演示效果，查看其表结构，如下：

 

也可以使用 show index 命令先查看其上都有哪些索引：

由图可知，titles表中有一个复合主键索引，分别由字段emp_no、title、 from_date组成一个主键。

复合主键：指一张表的主键含有一个以上的字段组成，不使用无业务含义的自增id作为主键

☁ 疑惑一：主键是唯一的索引，那么为何一个表可以创建多个主键呢？

答：“主键是唯一的索引”  仅仅体现在当表中只有一个主键时，才是唯一的索引，当表中有多个主键时，称为复合主键，复合主键联合保证唯一索引。

☁ 疑惑二：自增长ID已经可以作为唯一标识的主键，为什么还需要复合主键呢？

答：比如学生表，没有唯一能标识学生的ID，学生的名字、年龄、班级都可能重复，无法使用id单个字段来唯一标识。所以就要将多个字段设置为主键，形成复合主键，来标识其唯一性。例如某几个主键的值出现重复没问题，只要不是多条记录的所有主键值完全一样，就不算重复。

☁ 疑惑三：复合主键 ≠ 联合主键

答：复合主键 和联合主键 是两个东西，两者不等。

复合主键：按理来说一张表只能存在一个主键，但根据需要，我们可以设置多个字段同时为主键，称为复合主键。  例如：

-- 创建student表
create table student(
    name VARCHAR (30 ),
    age INT(30),
    primary key(Name,Age)  -- name,age都是主键
);

联合主键：其实就是中间表。在多对多模型里，需要两个表中的主键组成联合主键，这样就可以查到两个表中的每个数据，例如：

-- 人员表
create table user( 
   uid int(4)  auto_increment COMMENT '用户id',
   name varchar(30) comment '姓名',
   pwd varchar(30) comment '密码',
   primary key (uid) 
)


-- 角色表
create table role( 
   rid int(4)  auto_increment comment '角色id',
   roleName varchar(30) comment '角色名称',
   primary key (rid) 
)


-- 联合主键
-- 中间表，绑定人员和角色的关系（一个人可以绑定多个角色）
create table role( 
   id int(4)  auto_increment COMMENT '角色id',
   uid int(4) comment '用户id',
   rid int(4) comment '角色id',
   primary key (id) 
)

-- 注意：通过中间表进行联表查询，例如查询当前用户绑定了多少角色

由上述得出，titles表存在一个复合索引，即：<emp_no,title,from_date>

☛ 场景一：最左前缀匹配（全列匹配 ）

解析：根据上图可知，使用到了primary主键索引，key_len索引长度为209，由此可见，整个复合索引 <emp_no,title,from_date>的字段列都匹配到了。

另外注意：mysql的查询优化器会自动调整where子句的条件顺序，也就是说 where后面的字段emp_no、title、 from_date可以随意顺序，无需严格按照复合索引中字段的顺序

按照索引中所有列进行精确匹配时，即 “=” 或 “IN” 的匹配，索引会被用到。

☛ 场景二：最左前缀匹配（中间断开）

在复合索引<emp_no,title,from_date>中，只对emp_no、from_date条件查询，丢失中间字段 title

解析：title未提供，故查询只用到索引的第一列<emp_no> ，emp_no='10009' 的数据预估有3行，后面的from_date虽然也在索引中，但由于title不存在而无法和左前缀连接，因此需要对结果进行扫描过滤from_date。

MySQL的联合索引可以用于包含索引中：所有列的查询条件，或者索引中第一列的查询条件, 以及索引中前两列, 索引中的前三列, 以此类推。

☁ 思考：针对这种索引断连情况，如果是数据量大的表，如何提升其检索效率呢？

答：第一种：增加辅助索引 <emp_no, from_date>，需考虑其他因素：索引量大导致的维护成本，第二种：“隔离列”的优化方法，将emp_no与from_date之间的“坑”填上。这种方式只适合针对title列重复值少的情况

首先我们先看下title一共有几种不同的值：

总共7种。重复列值比较少的情况下，可以考虑用“IN”来填补这个“坑”从而形成最左前缀： 

这次key_len为209，说明索引被用全了，预估扫描的数据有7行

  

☛ 场景三：最左前缀匹配（前列断开）

在复合索引<emp_no,title,from_date>中，只对from_date条件查询，丢失前面的列

解析：由于不是最左前缀，索引这样的查询显然用不到索引。扫描的数据高达442545条。。。。

☛ 场景四：最左前缀匹配（匹配某列的前缀字符串）

在复合索引里的title列，使用前缀匹配 like ‘Senior’；而不是完全匹配模式

解析：符合最左前缀，用到了复合索引中的前两列

这里需要注意，符合前缀索引的前提是，like后面的通配符 % 不能出现在开头，如下：

虽然符合最左原则，但不符合前缀索引条件，导致索引没有用到title，而只用到了emp_no

例如比较一个字符串。java 与 javac，肯定是一个个比较过去，第一个相同后，再比较第二个字符，以此类推。所以要从左边开始，并且是不能跳过的。SQL 索引也是如此。

如果% 在前，就代表前面的内容不确定。不确定就无法比较，只能去索引树里一个一个的比较，等同于全匹配，全匹配就不需要索引，还不如直接全表扫描。

☛ 场景五：最左前缀匹配（范围查询）

解析：范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引。（特殊情况除外，例如第一个范围列使用的是between..and..语法，而不是 “<”、“>”）

注意①：索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引，如下：

解析：虽然复合索引中3个列都作为条件查询，由于emp_no是范围查询，故后面列无法使用索引

注意②：仅用explain可能无法区分范围索引和多值匹配，因为在type中这两者都显示为range。同时，用了“between”并不意味着就是范围查询，例如下面的查询：

解析：虽然用了2个范围查询，但 emp_no上的“between” 实际上相当于“in”，等同多值精确匹配。可以看到这个查询用到了索引全部三个列。在MySQL中要谨慎地区分多值匹配和范围匹配。

☛ 场景六：最左前缀匹配（查询条件中含有函数或表达式）

又或者：

解析：由于查询条件是一个表达式，MySQL无法为其使用索引。看来MySQL还没有智能到自动优化常量表达式的程度，因此在写查询语句时尽量避免表达式出现在查询中，而是先手工私下代数运算，转换为无表达式的查询语句。

索引优化二：选择性与前缀索引

索引并不是越多越好，索引文件本身要消耗存储空间，及加重写操作的负担

问题1：如何判断是否需要建立索引？

表记录较少，例如一两千条内的表，如果超过2000条可以根据情况建立合适的索引。

问题2：什么是合适的索引？

尽量选择区分度高的列作为索引，即不重复的值，与表记录总数的比值，区分度的公式是：

count(distinct col)/count(*)

选择性的取值范围为(0, 1)，选择性越高的索引价值越大，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，

☛ 例如我们的titles表，其计算出来的比值是：

解析：title列的选择性不足0.0001，没有必要为其单独建索引。

前缀索引

有一种与索引选择性有关的索引优化策略叫做前缀索引。

用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。

☛ 举例说明：首先查看下employee表的索引有哪些

employees表只有一个主键索引<emp_no>，如果按名字搜索一个人，就只能走全表扫描，如下

✦ 名字搜索

如果频繁按名字搜索员工，这样显然效率很低，因此我们可以考虑建索引。有两种选择，建<first_name>或<first_name, last_name>，看下两个索引的选择性：

① <first_name>的选择性：

② <first_name, last_name>的选择性：

虽然<first_name, last_name> 选择性很好，但是first_name 和 last_name加起来长度为30。。。

索引长度直接影响索引文件的大小,影响增删改的速度,并间接影响查询速度(占用内存多)，所以, 要在  区分度 + 长度  两者上,取得一个平衡.

☁ 思考：有没有兼顾长度和选择性的办法？

答：针对列中的值,从左往右截取部分,来建索引
1: 截的越短, 重复度越高,区分度越小, 索引效果越不好
2: 截的越长, 重复度越低,区分度越高, 索引效果越好,但带来的影响也越大（长度不宜过长）

例如：用 first_name 和 last_name的前几个字符建立索引，<first_name, left(last_name, 3)>：

解析：选择性一般，比起不截取字符前的值 0.93，还是差很多。

那么我们把last_name前缀加到4看看？

解析：长度只有18（first_name14+4），比<first_name, last_name> 长度为30 将近短了一半！

✦ 建立索引：使用sql语句建立其组合索引

✦ 对比查询：此时再执行一遍按名字查询，比较分析一下与建索引前的结果

解析：性能的提升是显著的。前缀索引兼顾索引大小和查询速度，但其缺点是不能用于order by和group by 操作，也不能用于Covering index（即当索引本身包含查询所需全部数据时，不再访问数据文件本身）。

为什么要使用联合索引？

① 减少开销：建一个联合索引(a,b,c)，等同建了 (a)，(a,b)，(a,b,c) 三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对数据量大的表，用联合索引会大大的减少开销！

② 覆盖索引：减少了很多的随机io操作，对联合索引(a,b,c)，如执行 select a,b,c from test where a=1 and b=2; 可直接通过遍历索引树获得数据；

③ 效率高：索引列越多，通过索引筛选出的数据越少。

如1000W 条数据的表，执行 select * from table where a=1 and b=2 and c=3，假设每个条件可筛选出 10% 的数据，如果只有单值索引，通过该索引能筛选出 1000W10%=100w 条数据，然后再回表从 100w 条数据中找到符合 b=2 and c=3 的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出 1000w * 10% * 10% * 10% =1w，效率提升可想而知！

 

InnoDB表为什么要建议用自增列做主键?

如果没有显式定义主键，InnoDB会选择第一个不包含有null 值的唯一索引作为主键索引，如果没有这样的索引，则InnoDB会选择内置6字节长的 rowId 作为隐含的聚集索引 

注意：rowId会随着行记录的写入而逐渐递增。

✦ 主索引

数据本身被存放在主索引（一颗B+Tree树）的叶子节点（大小为一个内存页或磁盘页）上。MySQL在维护聚簇索引的时候都是按照主键的顺序排序的，即每个数据页中的数据按照主键从小到大排序顺序存放，上一个数据页中最大的主键值一定是小于下一个数据页中的最小的主键的值

因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）

使用自增主键

每次插入新记录，会顺序添加到当前索引节点的后续位置，形成一个紧凑的索引结构，当一页写满，就会自动开辟一个新的页。插入时也无需移动已有数据，因此效率很高。例如：

使用非自增主键

例如身份证号或学号等，因为这些都是无序且是随机的值，可能导致新记录被插到现有索引页的中间某位置，MySQL会因此将新记录插到合适位置而移动数据。例如：

目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，增加很多开销，同时频繁的移动、分页操作造成大量的碎片，得到不够紧凑的索引结构，后续需要optimize table来重建表并优化填充页面。

➳ 结论

• 主键自增：MySQL只需要根据主键目录能很快的定位到新增的记录应该插入到哪里
• 主键不是自增：每次都需从头开始比较，找到合适的位置，再将记录插进去，影响效率

!! 注意：使用身份证等作为主键不能保证顺序，长度相对较长，占用空间较多

1. 可以唯一标识一行数据，在InnoDB构建索引树的时候会使用主键
2. 自增id是顺序的，保证索引树上的数据比较紧凑，有更高的空间利用率以及减少数据页的分裂合并等操作，提高效率