全表扫描、全索引扫描、覆盖索引

准备

版本：Mysql 8

本片文章参考自https://www.cnblogs.com/jpfss/p/9176147.html

本篇原文：《全表扫描、全索引扫描、覆盖索引》

创建语句

CREATE TABLE IF NOT EXISTS `sanguo` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `age` int NOT NULL,
  `sex` int DEFAULT NULL,
  `zw` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name_age` (`name`,`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

生成测试数据 

SET GLOBAL log_bin_trust_function_creators=TRUE; 
DELIMITER $$   
 
CREATE FUNCTION mock_data()		
RETURNS INT 					
BEGIN								
	DECLARE num INT DEFAULT 1000000; 	
	DECLARE i INT DEFAULT 0; 
	
	WHILE i < num DO 				
		 INSERT INTO sanguo(`name`,`age`,`sex`,`zw`) 
		 VALUES( CONCAT('用户',i),  FLOOR(RAND()  *  100), 1, FLOOR(RAND() * ((999999999 - 100000000) + 1000000000)));
		SET i =  i + 1;	
	END WHILE;	
	RETURN i;
END; 							
-- 执行函数
SELECT mock_data(); 

参考：MySQL生成百万条数据超详细步骤_Eric-x的博客-CSDN博客_mysql百万数据

开启性能分析

set profiling=1;

全表扫描

Type列为 ALL。顺序读磁盘文件。

mysql> explain SELECT zw FROM sanguo ;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | sanguo | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    8 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+

全索引扫描

Type列为 index。遍历索引树获取数据。非密集数据会产生随机IO。

mysql> explain SELECT name FROM sanguo ;
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | sanguo | NULL       | index | NULL          | idx_name_age | 206     | NULL |    8 |   100.00 | Using index |
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+

覆盖索引

Extra列为 Using index。无需回表。

mysql> explain SELECT id FROM sanguo ;
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | sanguo | NULL       | index | NULL          | idx_name_age | 206     | NULL |    8 |   100.00 | Using index |
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+

全表扫描VS全索引扫描

那个性能好，取决于数据存储的位置。

• 如果在内存，速度应该差不多。
• 如果数据在磁盘，那么全表扫描更好。原因是顺序读。

全索引扫描+覆盖索引

从性能分析的结果来看，全索引扫描+覆盖索引的组合性能相对较优。

 SELECT id FROM sanguo;
 SELECT `name` FROM sanguo ;
 SELECT zw FROM sanguo ;
 SELECT * FROM sanguo ;
 show profiles;

关于profiles：MySQL :: MySQL 8.0 Reference Manual :: 13.7.7.30 SHOW PROFILE Statement