mysql explain

测试explain

前置sql：

表结构

CREATE TABLE `t_user` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `age` int NOT NULL,
  `address` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `phone` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='测试索引';

数据

INSERT INTO `t_user` (`id`, `name`, `age`, `address`, `phone`) VALUES (1, 'Peggy Williams', 88, '152 Lodge Ln, Toxteth', '13512341');
INSERT INTO `t_user` (`id`, `name`, `age`, `address`, `phone`) VALUES (2, 'Shimizu Momoe', 100, '773 68 Qinghe Middle St, Haidian District', '13512342');
INSERT INTO `t_user` (`id`, `name`, `age`, `address`, `phone`) VALUES (3, 'Charlotte Gordon', 57, '618 Elms Rd, Botley', '13512343');
INSERT INTO `t_user` (`id`, `name`, `age`, `address`, `phone`) VALUES (4, 'He Lan', 61, '399 Broadway', '13512344');
INSERT INTO `t_user` (`id`, `name`, `age`, `address`, `phone`) VALUES (5, 'Herbert Stevens', 35, '641 Sky Way', '13512345');
INSERT INTO `t_user` (`id`, `name`, `age`, `address`, `phone`) VALUES (6, 'Chang Shihan', 95, '492 Jianxiang Rd, Pudong', '13512346');

测试sql

-- id 字段为主键索引
select * from t_user where id=1;
-- type=const key=PRIMARY
EXPLAIN select * from t_user where id=1;

-- name 字段为二级索引
-- ALTER TABLE `db_test`.`t_user` ADD INDEX `inx_name`(`name`);
select * from t_user where name="Peggy Williams";
-- type=ref key=inx_name
EXPLAIN select * from t_user where name="Peggy Williams";

select id from t_user where name="Peggy Williams";
-- type=ref key=inx_name
EXPLAIN select id from t_user where name="Peggy Williams";

-- name 字段为二级索引
select * from t_user where name like '%Williams';
-- type=ALL Extra=Using where
EXPLAIN select * from t_user where name like '%Williams';

-- name 字段为二级索引
select * from t_user where name like 'Peggy%';
-- type=range key=inx_name Extra=Using index condition
EXPLAIN select * from t_user where name like 'Peggy%';

-- type=ALL
-- 说明  id是PRIMARY, age是普通列
-- 走全表是因为 OR 的含义就是两个只要满足一个即可，因此只有一个条件列是索引列是没有意义的，只要有条件列不是索引列，就会进行全表扫描。 
EXPLAIN select * from t_user where id = 1 or age = 18;

-- ALTER TABLE `db_test`.`t_user` ADD INDEX `inx_age`(`age`);
-- type=ref key=inx_age
-- 说明：字段类型是int，传'100'和100是一样的，会转换成 CAST("100" AS int);
EXPLAIN select * from t_user where age = '100';
EXPLAIN select * from t_user where age = 100;

-- type=index_merge key=PRIMARY,inx_age
EXPLAIN select * from t_user where id = 1 or age = 18;



-- ALTER TABLE `db_test`.`t_user`  ADD COLUMN `phone` varchar(32) NULL AFTER `address`, ADD INDEX `inx_phone`(`phone`);
-- update `db_test`.`t_user` set phone=concat('1351234', id);
-- type=ref key=inx_phone
EXPLAIN select * from t_user where phone = '13512341';
-- 说明：字段类型是varchar，传'13512341'和13512341是不一样的，
-- 会转换成  select * from t_user where CAST(phone AS int) = 13512341;
-- type=ALL
EXPLAIN select * from t_user where phone = 13512341;

测试count(id) count(*) count(1)

CREATE TABLE `t_count` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `age` int NOT NULL,
  `address` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `phone` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='测试count';

-- 当只有PRIMARY没有其它index时
-- 如果表里只有主键索引，没有二级索引时，那么，InnoDB 循环遍历聚簇索引，将读取到的记录返回给 server 层，然后读取记录中的 id 值，就会 id 值判断是否为 NULL，如果不为 NULL，就将 count 变量加 1。
-- type=index key=PRIMARY
-- 如下三条执行结果一样
EXPLAIN select count(*) from t_count ;
EXPLAIN select count(1) from t_count ;
EXPLAIN select count(id) from t_count ;

-- 但是，如果表里有二级索引时，InnoDB 循环遍历的对象就不是聚簇索引，而是二级索引。这是因为相同数量的二级索引记录可以比聚簇索引记录占用更少的存储空间，所以二级索引树比聚簇索引树小，这样遍历二级索引的 I/O 成本比遍历聚簇索引的 I/O 成本小，因此「优化器」优先选择的是二级索引。
-- ALTER TABLE `db_test`.`t_count` ADD INDEX `inx_age`(`age`);
-- type=index key=inx_age
-- 如下三条执行结果一样
EXPLAIN select count(*) from t_count ;
EXPLAIN select count(1) from t_count ;
EXPLAIN select count(id) from t_count ;

-- type=index index=inx_age
EXPLAIN select count(age) from t_count ;
-- type=ALL
EXPLAIN select count(address) from t_count ;

-- 其它
-- 同时执行如下两行

EXPLAIN select count(*) from t_count ;
show WARNINGS;

-- 可以看到 /* select#1 */ select count(0) AS `count(*)` from `db_test`.`t_count`，说明select count(*)会被优化为selct count(0)