Mysql索引使用实践

多情刺客
1,066 阅读10分钟

前言

犹记得在大学时期学习数据库原理的时候，主要学的是DDL和DML，做作业安装的是oracle数据库。后来从事java开发的工作没用上oracle，一直用的是Mysql，发现DDL和DML也基本都能普遍使用。于是乎我就产生了这么一个问题，学Mysql应该学什么？我个人认为，在掌握了DDL和DML的语法和使用之后，Mysql当中重点要学习的就是索引、事务和存储引擎。今天我们先来聊聊索引吧。

Mysql索引及数据结构（以下仅针对InnoDB存储引擎来讨论）

  • 索引是什么？ 索引是帮助Mysql高效获取数据的排好序的数据结构。
  • 索引的数据结构是B+树，优点如下：
    • 非叶子节点不存储数据，只存储索引（冗余存储），好处是可以存放更多的索引
    • 叶子节点包含了所有的索引字段
    • 叶子节点之间用双向链接进行连接，提升范围查询的性能
  • 主键索引结构图：叶子节点存整行记录的数据

image.png

  • 非主键索引结构图：叶子节点存对应行的主键id

image.png

  • 联合索引结构图：遵循最左前缀原则

image.png

  • 最左前缀原则：指的是查询从最左的列开始且不可跳过索引中间的列

EXPLAIN工具

在介绍索引的使用之前，先来看看Explain工具。在sql前面加上EXPLAIN关键字可以模拟Mysql的优化器执行对应的SQL语句，可以用来分析sql语句的性能瓶颈。在Select语句之前加上EXPLAIN关键字，Mysql会在该sql上进行标志，返回该sql执行计划的信息，但不会真正执行这条sql。 有一种情况是例外，如果from后面有子查询，还是会执行该查询。之所以要先介绍explain，是因为在优化sql之前首先得分析sql，explain就是分析sql用的工具。

Explain使用示例：

  • 以下基于以下几个表进行
-- Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `update_time` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', 'Chinese', null);
INSERT INTO `course` VALUES ('2', 'Math', null);
INSERT INTO `course` VALUES ('3', 'English', null);

-- ----------------------------
-- Table structure for `teacher`
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `update_time` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', 'Hardy', null);
INSERT INTO `teacher` VALUES ('2', 'Jac', null);
INSERT INTO `teacher` VALUES ('3', 'Ray', null);

-- ----------------------------
-- Table structure for `course_teacher`
-- ----------------------------
DROP TABLE IF EXISTS `course_teacher`;
CREATE TABLE `course_teacher` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `course_id` int(20) DEFAULT NULL,
  `teacher_id` int(20) DEFAULT NULL,
  `update_time` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of course_teacher
-- ----------------------------
INSERT INTO `course_teacher` VALUES ('1', '1', '1', null);
INSERT INTO `course_teacher` VALUES ('2', '2', '2', null);
INSERT INTO `course_teacher` VALUES ('3', '3', '3', null);

Expalin中的列

  1. id列：id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。
  2. select_type列：表示对应行的查询是简单查询还是复杂查询

  • SIMPLE： 简单查询，不包含子查询或者union

    explain select * from course where id = 2 image.png

  • PRIMARY：复杂查询中最外层的select

  • SUBQUERY: 包含在select中的子查询，不包含在from中的

  • DERIVED：包含在from里的子查询，Mysql会将该子查询的结果存在一个临时表（也叫派生表）

    set session optimizer_switch='derived_merge=off'; #关闭Mysql5.7对派生表的合并
explain select (select id from course where id = 1) from (select * from teacher where id = 2) t;

image.png 以上例子是对primary、subquery以及derived三种查询类型的一个展示，验证完之后记得恢复mysql的对派生表合并的原配置。（set session optimizer_switch='derived_merge=on';）

  • UNION: 在union关键字后面的所有select查询

    explain select 1 union all select 1

image.png

  • DEPENDENT SUBQUERY：子查询的查询方式依赖于外面的查询结果

    • 这里说明一下，依赖子查询这种查询类型，是肯定要优化了的，因为效率会极其慢。关于这类型的情况的例子可看我的另一篇笔记juejin.cn/post/708894…

  • MATERIALIZED: 被物化的子查询，主要是在where里面的子查询 explain select * from course c where c.id in (select ct.course_id from course_teacher ct)

image.png

  1. table列：表示exlain的当前行正在访问哪个表
  • 当访问的是from里面子查询的临时表时，是deriveN的格式，表示id=N的查询结果
  • 当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id

  1. patitions列：表示当前分区

  2. type列：表示访问类型，即Mysql决定在表中怎么查找指定的行，表示一个大概范围

  • 性能从优到差的顺序为：System > const > eq_ref > ref > range > index > all

  • 从效率来上说，一般要求查询能达到range级别，最好能达到ref级别，处于index和all级别的查询是要考虑优化的了。

  • const,system: ：mysql能对查询的某部分进行优化并将其转化成一个常量。用于primary key或unique key 的所有列与常数比较时，所以表最多有一个匹配行。system是const的特例，表里只有一条元组匹配时为system。

  • eq_ref： primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。这可能是在const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。

  • ref：相比eq_ref，使用普通索引的或者唯一索引的前缀部分，索引和某个值比较能找到多行匹配的数据。

  • range: 范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。

  • index: 扫描全索引就能拿到结果，一般是扫描某个二级索引。这里的扫描是指遍历索引树，一般来说是使用索引覆盖，由于二级索引比较小，所以会比ALL要快一些。

  • all：全表扫描，即遍历聚集索引的所有叶子节点。这种要考虑加索引优化了。

  1. possible_key列：mysql认为可能会使用哪些索引来查找。如果possible_key有值，但key显示为Null，说明是mysql认为全表扫描速度更快，干脆不走该索引了。

  2. key列: 实际执行过程中Mysql采用的索引。如果为NULL说明没有走索引。

  3. key_len：表示使用到的索引的字符长度

  4. ref: 显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量,例如条件为where id = 1），字段名（例如where条件里含子查询的查询）

  5. row：显示了该查询可能要读取的行数，但不一定是结果集的行数

  6. filtered: 过滤比例，这里是一个百分比数据，rows * filtered/100约为当前结果集的行数

  7. extra列：表示额外信息

  • using index：使用覆盖索引，即在非聚集索引中就能拿到结果，不用再通过主键去聚集索引中查找。
  • using where：使用where条件来处理结果，并且查询的列未被索引覆盖。
  • Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围；
  • Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索 引来优化。
  • Using filesort：采用外部排序而不是内部排序，数据小时在内存排序，数据大时在磁盘排序，这种一般看来加索引优化。
  • Select tables optimized away：使用某些聚合函数来访问某个存在索引的字段时是这种类型。

索引使用实践

下面基于这个表对索引的使用进行说明，其中，idx_name_age_type是个三个列的联合索引

-- ----------------------------
-- Table structure for `user`
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `type` varchar(20) NOT NULL DEFAULT '' COMMENT '用户类型',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_type` (`name`,`age`,`type`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='用户表';

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'Hardy', '12', 'manager', '2022-06-02 16:31:31');
INSERT INTO `user` VALUES ('2', 'Jac', '11', 'zongjian', '2022-06-02 16:32:57');
INSERT INTO `user` VALUES ('3', 'Ray', '14', 'employee', '2022-06-02 16:32:57');
INSERT INTO `user` VALUES ('4', 'Jac66', '16', 'test', '2022-06-02 16:32:57');
  1. 全值匹配
  • explain select * from user u where u.name = 'Hardy'

image.png

  • explain select * from user u where u.name = 'Hardy' and u.age = 12

image.png

  • explain select * from user u where u.name = 'Hardy' and u.age = 12 and type = 'manager'

image.png

  1. 最左前缀原则（重点）：指的是一个索引如果由多列组成，要遵守最左前缀原则才会使索引生效，即条件匹配要从最左前列开始并且不能跳过中间的列，如下，如果跳过了name去匹配，索引将会失效
  • explain select * from user u where u.age = 12 and type = 'manager'

image.png

  1. 不在索引的列上做类型转换或者计算等操作，否则可能会使索引失效，导致全表扫描
  • explain select * from user u where left(u.name,2) = 'Ha'

image.png

  1. 联合索引中，使用了范围查询，则范围查询右边的列索引会失效
  • explain select * from user u where u.name = 'Hardy' and u.age > 12 and type = 'manager'

image.png

  1. 在不需要所有列的数据时，考虑使用索引覆盖，减少select *的使用，在联合索引中能直接取得到某几列的数据的话，可以减少回表次数，提高效率
  • explain select u.name, u.age, u.typefromuser u where u.name = 'Hardy'

image.png

  1. 使用不等于（!=或者<>），not in ，not exists 的时候，索引会失效，导致全表扫描
  • explain select * from user u where u.name <> 'hardy'

image.png

  1. 使用'>','<',''>=','<='这种范围查询时，mysql会根据数据量大小来决定是走索引还是全表扫描

  2. 使用is null或者 is not null时一般情况下索引也会失效

  • explain select * from user u where u.name is not null

image.png

  1. 使用in或者or时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评 估是否使用索引
  • explain select * from user u where u.name = 'hardy' or u.name = 'Jac'

image.png

  1. 字符串类型不加引号会导致索引失效
  • explain select * from user u where u.name = 123

image.png

  • explain select * from user u where u.name = '123'

image.png

  1. like以通配符开头的会导致索引失效，如like '%123',若通配符在后面，则前缀部分能按索引去匹配
  • explain select * from user u where u.name like '%dy'

image.png

  • explain select * from user u where u.name like 'Har%'

image.png

  1. 用in关键字的参数集合如果过大或者使用范围查询时，可能会导致索引失效，可考虑分步查找

  2. 最后，用一张经典的表格来总结联合索引的生失效情况

image.png

总结

本文主要从Mysql索引的数据结构出发，聊到了explain工具的使用以及参数的说明，最后重点总结了在日常开发的sql中索引可能失效的场景。总得来说，就是在使用Mysql的时候，应该尽可能地去考虑让查询走索引，如此可大大提高查询的效率。下一步要去了解的就是索引的设计原则以及怎么基于索引对sql进行优化了。

avatar
文章
阅读
粉丝