MySQL查询性能优化——索引的使用(三)

目录

1、检索效率

1.1 主键检索效率  

1.2 无索引检索效率

1.3 二级索引查询效率  

2、回表

3、回表解决方法

3.1 覆盖索引

4、覆盖索引的验证

4.1 单列索引

4.2 联合索引

5、索引的使用

5.1 最左前缀原则

 5.1.1 条件字段为从左至右

 5.1.2 条件为从左至右，跳过中间字段

 5.1.3 条件为从左至右，但只有最左边的字段

 5.1.4 条件跳过最左边字段

5.2 索引失效情况

5.2.1 范围查询时：>,<,between

 5.2.2 字符串不加单引号时

5.2.3 尾部模糊查询和覆盖模糊查询

5.2.4 在索引列上运算

5.2.5. or连接

6、索引设计原则

---

上一篇说到了索引的分类及索引的存储结构，今天来聊一下索引的使用。

下面通过一个案例来验证使用索引的检索效率。

 先准备一张有500w条记录的表：(一键向MySQL生成百万级数据会单独出一期)

select count(*) from tb_user;

  来看下表结构：

1、检索效率

1.1 主键检索效率  

  表中id为主键，作为主键索引，其他字段都没添加索引。

  在上一期我们说到过，通过主键索引去检索，能最快地查出数据，通过主键检索，如下：

select * from tb_user where id='80002';

  可以看到即使表里有500w条数据，通过主键去检索依旧能快速查出数据，花费的时间几乎可以忽略不计。

1.2 无索引检索效率

  通过username查询：

 select * from tb_user where username='zeiren';

  查询耗时10.18秒，字段username没有索引导致耗时久。

1.3 二级索引查询效率  

下面给username添加索引后再查询：

  可以看到，添加索引后查询时间从10.18秒降到0.13秒。

2、回表

  在上期说到，回表就是通过二级索引先找到对应的主键，再通过主键找到对应的行数据。

  那么如何判断是否回表呢？

  explain分析执行计划可以看出是否回表，下面执行如下语句：

explain select * from tb_user where username='zeiren';

  key：idx_uname,说明查询用到了索引。

  Extra：NULL，查询没有覆盖索引，回表了。

  查询产生回表会导致效率变低，那么如何解决回表的问题呢？

3、回表解决方法

3.1 覆盖索引

  覆盖索引可以解决回表的问题，覆盖索引不是一种索引，而是索引的一种查询方式。覆盖索引是指查询使用了索引，并且查询需要返回的列都能在索引中找到。

  简单来说，就是查询使用到了索引，并且select后跟着的字段都添加了索引。举例：

  在tb_user表中，id作为主键索引，username添加了二级索引，执行如下sql：

explain select id,username from tb_user where username='zeiren';

  key：idx_uname,查询使用到了索引。

  Extra：Using index，查询覆盖了索引，没有回表。

  上面执行的sql中，需要返回的列(id,username)都添加了索引，所以查询覆盖了索引，解决了回表的问题。

4、覆盖索引的验证

4.1 单列索引

​从这里开始，删掉之前tb_user的表结构，重新创建新的tb_user表结构:

CREATE TABLE `tb_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `username` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT '用户名',
  `phone` varchar(11) COLLATE utf8_unicode_ci NOT NULL COMMENT '手机号',
  `email` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '邮箱',
  `profession` varchar(11) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '专业',
  `age` tinyint(3) unsigned DEFAULT NULL COMMENT '年龄',
  `gender` char(1) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '性别 , 1: 男, 2: 女',
  `status` char(1) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '状态',
  `createtime` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_pro_age_sta` (`profession`,`age`,`status`),
  KEY `idx_phone_name` (`phone`,`username`),
  KEY `idx_phone` (`phone`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='系统用户表'

先查看当前表tb_user的表结构和索引建立情况：

表tb_user的索引建立情况：

 show index from tb_user;

  可以看到除了username外，还给phone添加了单列索引，也是唯一索引

  下面执行一条语句：

explain select id,username,phone from tb_user where username='吕布' and phone='17799990000';

  思考一下，这条语句会用到覆盖索引吗？看执行结果：

  可以看到查询只用到了一个索引，没有覆盖索引。

  虽然查询条件的两个字段都添加了单列索引，但实际上只走了一个索引，所以还是会产生回表。

4.2 联合索引

  针对上面的问题，可以通过给username和phone两个字段添加联合索引来解决。

create  index idx_phone_name on tb_user(phone,username);

  执行语句：

explain select id,phone,username from tb_user where  phone='17799990000' and username='吕布' ;

  执行结果：

  可以看到只使用了idx_phone这个单列索引，这是为什么呢？明明查询的列都在索引上了，且条件字段用到了联合索引。

  这里出现了一个误导，那就是在之前分别给phone和username两个字段分别都添加了单列索引，所以查询只用到了phone的索引。

  把phone和username的索引分别删掉：

  drop index idx_phone on tb_user;

  drop index idx_uname on tb_user;

  删除后再执行上面的语句：

explain select id,phone,username from tb_user where  phone='17799990000' and username='吕布' ;

  执行结果：

  可以看到使用到了联合索引，且覆盖了索引。

  联合索引使用业务场景：

  需要查询多个条件，可以对查询字段添加联合索引，而非单列索引。

5、索引的使用

5.1 最左前缀原则

  使用了联合索引就要遵从最左前缀原则，最左前缀原则是指从联合索引的最左列开始，不跳过中间的某列索引，若跳过了中间的某列索引，则联合索引失效。

  给profession、age、status添加联合索引：

  下面举例验证联合索引的使用情况：

 5.1.1 条件字段为从左至右

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

 5.1.2 条件为从左至右，跳过中间字段

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

 5.1.3 条件为从左至右，但只有最左边的字段

 explain select * from tb_user where profession='软件工程' ；

  可以看出上面三条sql都走了联合索引，因为联合索引的第一个字段总是出现在条件字段的第一位。

 5.1.4 条件跳过最左边字段

  explain select * from tb_user where age='31'；

  第4条sql没走索引，因为查询的条件字段为age，直接跳过了最左边的字段，不符合最左前缀原则，索引失效。

5.2 索引失效情况

  说到索引失效，下面来聊一下导致索引失效的几种情况：

5.2.1 范围查询时：>,<,between

explain select * from tb_user  where age> '30';

 5.2.2 字符串不加单引号时

  加单引号之后：

5.2.3 尾部模糊查询和覆盖模糊查询

  尾部模糊查询是指查询以某个字符或某个字符串结尾的，例如查profession以工程结尾的：

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

  覆盖模糊查询是指全匹配查询某个字符或字符串，例如：

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

  头部模糊查询不会导致索引失效：

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

5.2.4 在索引列上运算

  使用字符串函数substring截取phone：

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

5.2.5. or连接

  or连接的前后字段都需要有索引，否则索引不会生效。

  phone有索引但是age没有，索引失效。

  给age添加索引后：

  可以看到两个索引都用到了。

6、索引设计原则

1. 查询次数较多、数据量大的表。

2. 常出现在where子句后的字段，例如经常需要查phone，可以给phone添加单列索引。

3. 经常需要排序的字段：order by ，group by。

4. 尽量使用联合索引，避免使用单列索引。

5. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。区分度高通常指字段值是唯一的。

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

7. 要控制索引的数量，索引并不是越多越好，索引越多，有可能适得其反，会影响增删改的效率。

总结：

1.   使用索引的效率。

2.   索引的使用(单列索引、联合索引)。

3.   回表问题及解决方法​

4.   索引失效的情况。

5.   索引的设计原则。

  索引的使用分享到这里，下一期来聊一下sql优化。