MySQL进阶教程（四）—— SQL调优（上）

@

目录

• 前言
• 一、主键进行查询与非主键进行查询
  • （一）数据准备
  • （二）主键查询与非主键查询
• 二、执行计划EXPLAIN
  • （一）执行计划字段整体说明
    • 1、ID
    • 2、select_type
    • 3、tabel列
    • 4、partitions列
    • 5、type列
    • 6、possible_keys列
    • 7、key列
    • 8、key_len列
    • 9、ref列
    • 12、rows列
    • 11、filtered列
    • 12、 Extra列
  • （二）type 列详讲
    • 1、system
    • 2、const
    • 3、eq_ref
    • 4、ref
    • 5、fulltext
    • 6、ref_or_null
    • 7、index_merge
    • 8、unique_subquery
    • 9、index_subquery
    • 10、range
    • 11、index
    • 12、ALL
  • （三）Extra 列详讲
    • 1、Using temporary
    • 2、Using filesort
    • 3、Using where
    • 4、Using index
• 三、索引覆盖
• 四、回表查询
• 结语

前言

本期将要介绍MySQL的SQL调优部分，该部分主要介绍使用主键查询与不使用主键查询在性能上的区别、不同SQL语句的性能差异、MySQL对查询语句的优化，以及索引失效等问题。现在我们来开始学习关于使用主键查询与不使用主键查询的索引优化的部分。

一、主键进行查询与非主键进行查询

（一）数据准备

在正式开始前，我们用以下的存储过程进行构建一个100万条数据的表来进行演示

-- 修改SQL结束符
delimiter //
-- 创建存储过程
CREATE PROCEDURE p_init_index_data ()
BEGIN
  -- ⽣成学号和主键
  DECLARE
  id BIGINT DEFAULT 100000;
  -- 年龄
  DECLARE
  age TINYINT DEFAULT 18;
  -- 性别
  DECLARE
  gender BIGINT DEFAULT 1;
  -- 班级编号
  DECLARE
  class_id BIGINT DEFAULT 1;
  -- 循环计算
  DECLARE
  count INT DEFAULT 0;
  -- 创建表
  DROP TABLE
  IF EXISTS index_demo;
    CREATE TABLE index_demo (
      id BIGINT auto_increment,
      sn VARCHAR (10) NOT NULL,
      NAME VARCHAR (20) NOT NULL,
      mail VARCHAR (20),
      age TINYINT (1),
      gender TINYINT (1),
      PASSWORD VARCHAR (36) NOT NULL,
      class_id BIGINT NOT NULL,
      create_time DATETIME NOT NULL,
      update_time DATETIME NOT NULL,
      PRIMARY KEY (id),
      INDEX (class_id)
    );
    -- 插⼊⼀条测试数据
    INSERT INTO index_demo
    VALUES
    (100000, '100000', 'testUser', '100000@qq.com', 18, 1, UUID(), 1, NOW(), NOW());
    -- 循环构建数据
    WHILE count < 1000000 DO
      -- ID和学号
      SET id := id + 1;
      -- 年龄
      IF count % 10 = 0 THEN
        SET age := age + 1;
      END IF;
      IF age > 50 THEN
        SET age := 16;
      END IF;
      -- 性别
      IF count % 3 = 0 THEN
        SET gender := 0;
      ELSE
        SET gender := 1;
      END IF;
      -- 班级编号
      SET class_id := class_id + 1;
      IF class_id > 10 THEN
        SET class_id := 1;
      END IF;
      -- 写⼊数据
      INSERT INTO index_demo
      VALUES
      (
        id,
        id,
        CONCAT('user_', id),
        CONCAT(id, '@qq.com'),
        age,
        gender,
        UUID(),
        class_id,
        NOW(),
        NOW()
      );
      -- 更新count
      SET count := count + 1;
    END WHILE;
  END //
  -- 还原SQL结束符
  delimiter;
  -- 调⽤存储过程，开始构建数据，耗时⽐较⻓，20 ~ 100分钟
  CALL p_init_index_data ();

（二）主键查询与非主键查询

在有了数据后，我们适用适用主键查询来查看具体耗时。

可以看到结尾处的0.00 sec，耗时在10ms以内。现在再来看一下不使用索引来查询的情况。

可以看到查询同样一条记录的耗时要比使用主键列的更多。

上面的过程我们也可以用MySQL自带的压力测试工具mysqlslap进行并发测试来进一步体会两种查询方式的耗时不同。

二、执行计划EXPLAIN

我们可以使用执行计划explan在执行具体SQL语句前分析SQL语句的执行情况，以便优化SQL语句。

• 使用主键查询的执行计划
  
• 使用非主键查询的执行计划
  

（一）执行计划字段整体说明

对于上面两个表的结果里面的字段，我们在这给出整体的说明再逐一进行说明。

列名	描述
id	SELECT标识符
select_type	SELECT类型
table	查询的表
partitions	查询的分区
type	JOIN类型
possible_keys	可能选择的索引
key	实际选择的索引
key_len	索引长度
ref	与索引比较的列 rows 估算要检查的行数
filtered	按条件筛选行的百分比
Extra	附加信息

1、ID

SELECT标识符，查询语句中SELECT的序号。如果整条查询语句中包含子查询或合并查询，则每个查询的编号依次递增。

子查询的例子如下：

2、select_type

SELECT类型的具体内容如下表。

select_type 值	描述
SIMPLE	简单SELECT(不使用UNION或子查询)
PRIMARY	外层查询
UNION	UNION中的第二个及之后的SELECT语句
UNION RESULT	UNION的结果。
SUBQUERY	子查询中
INSERT	INSERT 语句
UPDATE	UPDATE 语句
DELETE	DELETE 语句

3、tabel列

查询时数据行所指在表的名称。

• <unionM,N> : 表示合并查询用到的表，M 和 N是id列的值
• <subquery,N> : 表示子查询用到的表，N是id列的值

4、partitions列

查询时数据行所在的分区，对于非分区表，值为 NULL。

5、type列

type列 ，其内容较多，后面细讲。

6、possible_keys列

possible_keys 列显示SQL语句可能会用到的索引，如果该列为 NULL 则SQL语句没有可用的索引。这时可以检查 WHERE 子句中引用的列，并考虑是否需要在这些列上建立索引来提高查询的性能。

7、key列

key 列表示SQL语句实际使用的索引，如果该列为NULL, MySQL则SQL语句没有可用的索引。

8、key_len列

key_len 列表示SQL语句使用索引的字节长度，如果 key 列为 NULL ，则 key_len 列也为NULL 。

9、ref列

ref 列显示在查询过程中哪些列或常量与key列中指定的索引进行比较。如果值是 func ，则使用的值是某个函数的结果，如果要查看具体用了哪个函数，可以使用 EXPLAIN 后面的 SHOW WARNINGS 来查看扩展的 EXPLAIN 输出。

12、rows列

rows 列表示SQL语句执行查询需要检查的行数，对于InnoDB表这是⼀个估计值。数据越小效率越高。

11、filtered列

filtered 表示按表条件筛选的表中数据行的百分比。最大值是100，这意味着没有对数据行进行过滤。从100开始递减的值表示过滤量在增加。这个值越大表示过滤的效率越高。

如果Rows列显示的预估行数为1000，filtered为50.00(50%)，Rows × filtered的结果是根据条件过滤后的行数，也就是符合查询条件的记录数为1000 × 50% = 500行。

12、 Extra列

Extra 列包含MySQL如何解析查询语句附加信息。同type列一样稍后讨论。

（二）type 列详讲

EXPLAIN输出的type列描述了表是如何连接的，性能从最好到最差的排序如下：system、const、eq_ref、ref、fulltext、ref_or_null、index_merge、unique_subquery、index_subquery、range、index、ALL

1、system

表中只有一行数据，不用任何扫描，性能极高，是const类型的特殊情况 。该字段需要在MyISAM的存储引擎下可见。

2、const

当查询中的条件通过主键索引或唯一索引与常量进行比较时，结果最多有一个匹配的行，类型显示为 const ，这种类型查询性能极⾼，且只会返回一行数据。此处在上面使用主键查询的执行计划的结果中可见。

3、eq_ref

eq_ref 是除了system类型和const类型之外最高效的连接类型。应用于多表连接的场景，表关联条件是主键索引或唯一非空索引时使用等号 ( = ) 进行索引列的比较，每行只匹配一条记录。

在这我们给出具体例子：

-- 创建⽤⼾表，与student表是⼀对⼀关系
drop table if exists account;
create table account (
	id bigint primary key auto_increment,
	username varchar(20) not null,
	password varchar(32) not null,
	student_id bigint not null,
	UNIQUE (student_id)
);
-- 写⼊数据
insert into account (username, password, student_id) values ('user_001','pwd_123456', 1);
insert into account (username, password, student_id) values ('user_002','pwd_123456', 2);
insert into account (username, password, student_id) values ('user_003','pwd_123456', 3);
insert into account (username, password, student_id) values ('user_004','pwd_123456', 4);
insert into account (username, password, student_id) values ('user_005','pwd_123456', 5);
insert into account (username, password, student_id) values ('user_006','pwd_123456', 6);
insert into account (username, password, student_id) values ('user_007','pwd_123456', 7);
insert into account (username, password, student_id) values ('user_008','pwd_123456', 8);

4、ref

ref 表示SQL语句中使用了普通索引，返回的结果可能是多行组成的结果集。
例子如下：

5、fulltext

使用全文索引时显示为 FULLTEXT。

6、ref_or_null

类型与ref类似，但包括对null值的检索，索引列必须是可以为空的列。

7、index_merge

在查询中使用了多个索引，OR 两边必须是单独索引，最终通过不同索引检索数据，然后对结果集进行合并，Key_len显示最长的索引长度。

8、unique_subquery

用eq_ref 替换⼦查询中的IN。

-- 子查询中返回的是外层表的主键索引或唯一索引
value IN (SELECT primary_key FROM single_table WHERE some_expr)

9、index_subquery

与unique_subquery类似，只不过子查询中返回的是普通索引列。

-- 子查询中返回的是外层表的普通索引
value IN (SELECT key_column FROM single_table WHERE some_expr)

10、range

用索引列进行范围查询，当使用<>、>、>=、<、<=、is NULL、<=>、BETWEEN、LIKE或IN()操作符，索引列与常量进行比较时为range。

11、index

扫描整个索引树而不扫描整个表，比如只使用索引排序而不使用条件查询时。

12、ALL

最差的情况，表示MySQL必须对全表进行逐行扫描才可以找到匹配行，遇到这种情况通常建议在查询的列上加索引来避免全表扫描。

（三）Extra 列详讲

Extra 列中如果出现 Using filesort 和 Using temporary ，将会对查询效率有比较严重的影响。

1、Using temporary

当使用非索引列进行分组时，会用临时表进行排序，在优化时可以考虑为分组的列加索引。

2、Using filesort

当使用非索引列进行排序时会用到文件内排序，在优化时可以考虑为排序的列加索引。

3、Using where

当使用非索引列进行检索数据，且进行了全表扫描。
值得注意的是当使用索引列进行检索数据时，进行范围查找，此时扫描的是索引树。

4、Using index

发生索引覆盖时显示using index，表示这是一个高效查询。

三、索引覆盖

要使用的索引中包含要查询列，这时会直接返回索引中列的值，不用回表查询。

接在来我们举一个例子来进行说明。

复合索引的索引树中的叶子结点中包含name, age, create_time列值，以及主键的值，当⼀个查询使用这个复合索引进行检索，并且查询列表中只有name列、age列、create_time列和主键列的全部或其中之一时，可以直接从索引树中返回数据，这个现象叫做索引覆盖。

四、回表查询

当使用索引检索数据时，查询的列不只包含索引列，这时需要通过索引中记录的主键值到主表中进行查询，这个现象叫做回表查询。

结语

以上便是本期关于MySQL中SQL调优之索引优化的内容了。如果喜欢我的内容，请点赞、收藏加关注，多多支持我，以及欢迎各位在评论区讨论交流，并指出我的不足，谢谢大家。