1、MySQL执行计划

MySQL提供了Explain工具,用于查看SQL语句的执行计划。

  • 使用语法
explain SQL语句;
  • 数据类型 图片说明
    • id:查询的序列号,标识执行的顺序
      • id 相同,执行顺序由上至下
      • id 不同,如果是子查询,id会递增,id值越大优先级越高,越先被执行
    • select_type:查询类型
    • type:连接类型。system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
    • possible_keys:可能使用到的索引
    • key:实际使用的索引
    • ref:与key中索引进行匹配的值或列
    • rows:将要遍历的记录行数的估计值
    • Extra:额外信息
      • Using index:查询的数据包含在了索引中,不用再扫描表中的记录。即:索引覆盖
      • Using where:检索数据后进行过滤
      • Using filesort:对检索结果进行文件排序
      • Using index condition:索引下推。存储引擎使用该索引条件对索引进行过滤

2、索引覆盖

如果索引的叶子节点中包含所要查询的数据,那么就不需要回表查询。换句话说,如果一个索引包含了所有需要查询的数据,就称为索引覆盖MySQL官方认为使用explain查看SQL语句执行计划时,如果【Extra】的值为【Using index】则表示索引覆盖了所需查询的数据。对于事务中被修改的表,无法使用索引覆盖。索引覆盖的优点如下:

  • 索引条目通常远远小于数据行大小,只扫描索引能够减少数据访问量。
  • 索引是按照列值顺序存储的(单个页内),范围查询时扫描索引能够减少排序耗时。

InnoDB引擎使用了聚簇索引和二级索引。聚簇索引根据索引列的值对数据进行排序,并将叶子节点指向对应的行记录。每张表有且只有一个聚簇索引;二级索引的叶子节点保存了主键值,查询数据时仍需根据主键值扫描聚簇索引查询数据。如果聚簇索引和二级索引可以包含所有需要查询的数据,即可实现索引覆盖。

3、延迟关联

3.1、测试数据准备

  • 假设有一张users表
CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `age` int DEFAULT NULL,
  `username` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB
  • 添加一些数据:创建如下存储过程并执行
DELIMITER $$
USE `数据库名`$$
DROP PROCEDURE IF EXISTS `init_data_2`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `init_data_2`()
BEGIN
	DECLARE age INT;
	DECLARE username INT;
	SET age = 21;
	WHILE age <= 100 DO
		START TRANSACTION; 
		SET username = 1;
		WHILE username <= 10000 DO
			INSERT INTO users(age,username) VALUES (age,CONCAT('username',username));
			SET username = username + 1;
		END WHILE;
		SET age = age + 1;
		COMMIT; 
	END WHILE;
	END$$
DELIMITER ;

3.2、分页查询及其优化

  • 对users表进行分页查询:
SELECT * FROM users WHERE age >90 LIMIT 50000,10;

MySQL的limit操作,先查询50010条记录,丢弃前50000条,返回最后10条。 - 执行计划: 执行计划中type=ALL,表示对表进行了全扫描。找出所有age>90的记录,然后选择前50010条数据,最后返回后10条。 - 执行结果: 图片说明

  • 给age添加索引
ALTER TABLE users ADD INDEX index_age(age);
- 执行计划:

图片说明 Using index condition表示对索引进行了条件查询、Using MRR表示将随机磁盘读转换为顺序磁盘读。添加索引后,在index_age索引树中检索满足age>90的所有数据,然后选择其中50010条主键,将这些主键暂存后排序(Using MRR),并按照顺序扫描主键索引树获取对应的数据50010条,然后返回后10条。 因为索引是以索引文件的形式存储在磁盘中的,当文件很大时只能有一部分索引页存在于内存当中。当通过主键随机访问主键索引时,可能发生多次索引文件磁盘读操作;通过将主键排序后再顺序访问主键索引,可以减少磁盘读操作,提高性能。 - 执行结果: 图片说明

  • 延迟关联 所谓延迟关联,是指先通过子查询从二级索引中获取目标数据的主键构成临时表,并对临时表中的数据进行筛选,再通过原表与临时表进行关联操作获取对应的数据。即:先对二级索引的结果进行筛选,然后再扫描主键索引。通过延迟二级索引与主键索引之间的关联操作,减少关联数据量,从而提高性能。
select s.* from users s inner join (select id from users where age>90 limit 50000,10)as temp on (temp.id=s.id);
- 执行计划:

根据执行计划的id得知,上图中的执行顺序为(2、3、1)3、1、2。第3行:首先从index_age索引树中检索出所有age>90的数据,并选择前50010条数据构成一张临时表;第1行:从临时表中选择最后10条数据。第2行:将这10条数据与s表(users表的别名)进行关联操作,通过主键值扫描主键索引树获取数据。 - 执行结果: