https://juejin.cn/post/6997578033179656229
– 2021-08 黄壮壮 mysql索引总结与实践; (纸上得来终觉浅 绝知此事要躬行) 复制到 navicat 更清晰哦
先来张B+TREE 图养养眼(借用美团技术网站的) https://tech.meituan.com/2014/06/30/mysql-index.html
------------------------------------初始化-------------------------------------------
-- 初始化表以及表中数据 使用存储过程 (注意:表名后的数字即是该表的数据量造数据时，变量i取表名后的数字即可)
-- DDL:
CREATE TABLE `xzll_student100` (
`id` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`hobby` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`intention` varchar(255) NOT NULL,
`edu` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`channel` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`level` int DEFAULT NULL,
`status` int NOT NULL,
`sex` int NOT NULL,
`project` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`ctime` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP,
`utime` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `xzll_student10000` (
`id` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`hobby` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`intention` varchar(255) NOT NULL,
`edu` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`channel` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`level` int DEFAULT NULL,
`status` int NOT NULL,
`sex` int NOT NULL,
`project` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`ctime` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP,
`utime` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `xzll_student100000` (
`id` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`hobby` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`intention` varchar(255) NOT NULL,
`edu` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`channel` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`level` int DEFAULT NULL,
`status` int NOT NULL,
`sex` int NOT NULL,
`project` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`ctime` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP,
`utime` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `xzll_student1000000` (
`id` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`hobby` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`intention` varchar(255) NOT NULL,
`edu` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`channel` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`level` int DEFAULT NULL,
`status` int NOT NULL,
`sex` int NOT NULL,
`project` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`ctime` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP,
`utime` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `xzll_student2000000` (
`id` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`hobby` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`intention` varchar(255) NOT NULL,
`edu` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`channel` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`level` int DEFAULT NULL,
`status` int NOT NULL,
`sex` int NOT NULL,
`project` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`ctime` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP,
`utime` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- 存储过程
drop procedure test_insert;
CREATE PROCEDURE test_insert()
#开始
BEGIN
#定义变量
DECLARE i INT DEFAULT 1;
#条件判断
WHILE i<=2000000
#执行
DO
#SQL
INSERT into xzll_student2000000 (name,address,hobby,intention,edu,channel,level,status,sex,project,ctime,utime)
values(
substring('王李张刘陈杨黄赵吴周徐孙马朱胡郭何高林罗郑梁谢宋唐位许韩冯邓曹彭曾萧田董潘袁于蒋蔡余杜叶程苏魏吕丁任沈姚卢姜崔钟谭陆汪范金石廖贾夏韦傅方白邹孟熊秦邱江尹薛阎段雷侯龙史陶黎贺顾毛郝龚邵万钱严覃武戴莫孔向汤', substring(rand()*100000, 1, 1),substring(rand()*100000, 1, 1)),
substring('张家口北京唐山石家庄邯郸衡水大同呼和浩特包头哈尔滨齐齐哈尔', substring(rand()*100000, 1, 1),substring(rand()*100000, 1, 1)),
substring('篮球排球足球乒乓球网球高尔夫羽毛球保龄球曲棍球踢踏舞名族舞娃哈哈矿泉水鸭子鸽子鸡恐龙麻雀火烈鸟黄壮壮哈哈哈', substring(rand()*100000, 1, 1),substring(rand()*100000, 1, 2)),
substring('本科专科硕士博士', substring(rand()*100000, 1, 1),substring(rand()*100000, 1, 1)),
substring('消防工程师，java研发，大数据，前端，运维', substring(rand()*100000, 1, 1),substring(rand()*100000, 1, 1)),
substring('广点通，百度，头条，360，uc，抖音，趣头条，神马', substring(rand()*100000, 1, 1),substring(rand()*100000, 1, 1)),
substring(rand()*100000, 1, 1),
substring(rand()*100000, 1, 1),
substring(rand()*100000, 1, 1),
'工程师',
NOW(),
NOW()
);
#变量增加
SET i=i+1;
#结束循环
END WHILE ;
#提交
commit;
#结束
END;
#执行
CALL test_insert();
#删除存储过程
drop procedure test_insert ;
select count(*) from xzll_student100;
select count(*) from xzll_student10000;
select count(*) from xzll_student100000;
select count(*) from xzll_student1000000;
select count(*) from xzll_student2000000;
----------------添加初始索引 注意:（后边也会有索引添加，是在具体的演示前添加）-------------
-- 添加索引
ALTER TABLE `xzll_student100`
ADD INDEX `idx_name`(`name`) USING BTREE,
ADD INDEX `idx_project`(`project`) USING BTREE;
----------------------------基数计算公式以及explain字段释义--------------------------------
-- 小技巧: 索引基数计算公式 select (count(DISTINCT 索引列) / count(*) ) * 100 from table 值越接近100 说明该列区分度越大 更适合做索引 注意: 一般需要join的字段我们都要求是 10 以上，即平均1条扫描10条记录。
-- 示例如下:
select (count(DISTINCT ctime)/count(*) ) * 100 from xzll_student2000000
一: EXPLAIN 列解释
-- A: type 说明:
-- const 访问类型 ---> 聚簇索引或者唯一索引去等值匹配的
-- ref 访问类型 ---> 二级索引去等值匹配的
-- ref_or_null 访问类型 ---> 二级索引去等值匹配的 + 二级索引的null值 如 select * from xzll_student2000000 where name='黄' or name is null;
-- range 访问类型 ---> 对索引列进行范围查找 如 in > < BETWEEN AND
-- index 访问类型 ---> 搜索条件中使用到索引 并且在返回时候 所查询字段都包含在二级索引中
-- all 访问类型 ---> 全表扫描
-- B: rows说明: 如果是全表扫描的情况下 代表预计需要扫描的行数 ，如果使用索引的话 代表预计扫描的索引行数
-- C: filtered说明: 根据某条件/索引条件查询到数据后 ，剩余其他条件的过滤比例 单表情况下 该属性参考意义不大 连接查询时候 该属性比较关键
-- rows * filtered 即代表 真实查到的数据(单表情况下)，需要和某表连接的次数(也叫扇出值 ) 注意 可能不准 因为rows是估算值
-- D: key_len说明: 表示使用到索引列的长度 固定长度的就是列长 变长的话 比如varchar(255) utf8mb4编码(最大字符长度为4字节) utf8为三字节
-- 其ken_len = 255 * 4 + 2(变长列的实际长度)+ 1 (如果字段可为 null的话) , 如果某列是int类型的话 key_len应该就是 4+2+1 (如果可以为null的情况下)
-- 为了显示和idx_level索引(可以为null)的区别我们给sex字段(不允许为null)添加个索引
ALTER TABLE `xzll_student100`
ADD INDEX `idx_sex`(`sex`) USING BTREE;
explain select * from xzll_student100 where sex = 1;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE xzll_student100 ref idx_sex idx_sex 4 const 7 100.00
-- 结论: 可以看到key_len为 4字节 因为sex列属性 为 not null
ALTER TABLE `xzll_student100`
ADD INDEX `idx_lecel`(`level`) USING BTREE;
explain select * from xzll_student100 where LEVEL = 1;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE xzll_student100 ref idx_lecel idx_lecel 5 const 7 100.00
-- 结论: 可以看到 key_len 为 5 字节 原因就是level列属性是 可以为null
-- E: ref 表示使用到索引时候 对索引的匹配方式 比如单表时候的等值匹配 或者 join 时候会展示出具体的
explain select a.name,a.address,a.sex from xzll_student100 a left join xzll_student2000000 b on a.level =b.level
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE a ALL 100 100.00
1 SIMPLE b ref idx_level idx_level 5 xzll-dev.a.level 213906 100.00 Using index
-- 可以看到 ref 把数据库名称也显示出来了
-- F: possible_keys 可能会使用到的索引
-- G: key 真正使用到的索引名称
-- H: Extra 很多值 具体的在之前总结的笔记中找即可这里不做总结了。
----------------------索引之 单列索引 (类型为二级索引) 失效与生效演示-------------------------
-- :::::::重要::::: 下边证明的索引情况 注意:表名后边的数字 即代表这个表的数据条数
-- 其他表有索引 idx_name 而这张xzll_student2000000表有两个索引 即 下边show index所示
show index from xzll_student2000000; idx_name,idx_project -- 可以看到目前该表有两个索引哈
-- (1).直接匹配某个单列索引
explain select * from xzll_student2000000 where name = '黄';
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE xzll_student2000000 ref idx_name idx_name 1022 const 46696 100.00
-- 结论: 使用到索引 没啥好说的 会进行回表操作 此时为随机io没达到量呢 我估计达到量后 也会走全表扫描
-- (1.1).匹配某个单列索引并和其他非索引列组合 and 方式 （交集）
explain select * from xzll_student2000000 where name = '黄' and address ='张家口' and channel like '%百度%';
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE xzll_student2000000 ref idx_name idx_name 1022 const 46696 1.11 Using WHERE
-- 结论: 使用到了索引 因为mysql应该是先回表后再直接在 聚簇索引中找的
-- (1.2).匹配某个单列索引并和其他非索引列组合 or 方式（并集）
explain select * from xzll_student2000000 where name = '黄' and address ='张家口' or channel='百度';
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE xzll_student2000000 ALL idx_name 1817211 10.11 Using where
-- 结论: 不使用索引 因为mysql根据索引条件查找到数据后 还需要取or右边查到的并集 而 or右边的条件会进行全表扫描 so就全表扫描了
-- 由 (1.1) 和 (1.2) 我们初步得出结论 交集情况下 只要有一个条件使用到索引 那么就会使用到 如果并集情况下 只要有一个or条件没使用到索引列 那么就会索引失效 进行全表扫描
-- 问题来了 or两边都是索引列会不会使用到索引呢？？？？我们来试试
-- (1.3).匹配某个单列索引并和其他索引列组合 or 方式（并集）
explain select * from xzll_student2000000 where name='黄' or project = '工程' ;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE xzll_student2000000 index_merge idx_name,idx_project idx_name,idx_project 1022,1022 46697 100.00 Using union(idx_name,idx_project); Using where
-- 结论: 我去 居然使用了索引合并？ 666 想必是把两边的or条件都从二级索引查到然后合并后回表吧 所以叫索引合并喽
-- 那又有个疑问
explain select * from xzll_student2000000 where name='黄' or project like '工程%' ;
-- 这样做会使用到吗 答案是不会的，因为 project like '工程%' 不会使用到索引 (原因是啥?还不是因为符合条件的数据对应的主键id太分散会形成随机io造成的嘛)
-- 当然如果你使用
explain select * from xzll_student2000000 where name='黄' or project like '%工程' ;
-- 那跟不会使用到索引了 原因？ 不用说了吧 。 由此证明了我们在(1.2)处的猜想 哈哈 哪个猜想叫啥来着？巴赫猜想？哥德巴赫猜想？
-- (1.4).在索引列上使用函数
explain select * from xzll_student2000000 where UPPER(name)='黄';
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE xzll_student2000000 ALL 1817211 100.00 Using where
-- 结论: 不使用索引 具体为啥呢？ 因为索引在建立时会和计算后可能不同，无法定位到索引。但如果查询条件不是对索引列进行计算(前提是其他条件中使用到了索引哈)，那么依然可以走索引
-- 注意 这里是索引列 如果是索引值的话那么依然后走索引 我们看下
explain select * from xzll_student2000000 where name = CONCAT('黄','赵');
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE xzll_student2000000 ref idx_name idx_name 1022 const 1 100.00
-- 晓得了吧
-- (2).范围匹配某个单列索引 数据量比较小
explain select * from xzll_student100 where name like '黄%';
explain select * from xzll_student10000 where name like '黄%';
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE xzll_student10000 range idx_name idx_name 1022 1086 100.00 Using index condition
-- 结论: 上边两个 (2) 将会使用到索引 type 为range extra 为 Using index condition
-- (2.1).范围匹配某个单列索引 数据量比较大
explain select * from xzll_student100000 where name like '黄%';
explain select * from xzll_student1000000 where name like '黄%';
explain select name from xzll_student2000000 where name like '黄%';
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE xzll_student2000000 ALL idx_name 1817211 39.77 Using where
-- 结论: 上边三个 (2.1) 不会使用到索引 因为 数据量较多 会产生随机IO 所以mysql觉得直接走全表得了 但是: 如果把* 换成 name的话(换成非name字段也不会使用到索引哦)
-- 计划长这样 即用到索引了 因为不需要回表啦 也就不存在随机io所以使用到了索引
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE xzll_student2000000 range idx_name idx_name 1022 722658 100.00 Using where; Using INDEX
-- (3).like 左模糊
explain select * from xzll_student2000000 where name like '%黄';
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE xzll_student2000000 ALL 1817211 11.11 Using WHERE
-- 结论: 肯定走全表 和数据量没关系， 因为 mysql是从左到右进行遍历的(待论证)，左边没有等值匹配，也就意味着需要全表扫描
-- (4).like 左右都模糊
explain select * from xzll_student2000000 where name like '%黄%';
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE xzll_student2000000 ALL 1817211 11.11 Using where
-- 结论: 这个更没的说，肯定走全表 ， 原因同: (3)
-- (5).in
explain select * from xzll_student2000000 where name in ('黄','赵');
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE xzll_student2000000 range idx_name idx_name 1022 85994 100.00 Using index condition
-- 结论： 可以看到也会使用到索引，原因 和(1)一样 因为我in的值太少了 多了的话 可能就用不到了 用于地方比较小 这个暂时不做演示了 LOCALTIMESTAMP()
-- (6).between and 大于 小于这类的范围操作 是否会使用索引呢
-- :::::::::::::此时我们给level加个索引哈:::::::::::::
ALTER TABLE `xzll_student2000000`
ADD INDEX `idx_level`(`level`) USING BTREE;
ALTER TABLE `xzll_student10000`
ADD INDEX `idx_level`(`level`) USING BTREE;
show index from xzll_student2000000
explain select * from xzll_student2000000 where level >3 and level < 5;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE xzll_student2000000 ALL idx_level 1817211 25.05 Using WHERE
-- 结论: 不使用索引 应该和上边like '黄%' 一样 需要回表的太多了 所以mysql觉得还是全表扫描吧
explain select * from xzll_student10000 where level >3 and level < 5;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE xzll_student10000 range idx_level idx_level 5 1116 100.00 Using index CONDITION
-- 结论: 使用到了索引 回表的不多 随机io可以接接受 那就用上呗
-- 上边是大于小于 那between and呢 其实和大于等于差不多 索引失效有效也就差不多了 in 应该也是 这里不做示例了 都属于范围匹配
explain select * from xzll_student2000000 where level BETWEEN 4 and 5;
explain select * from xzll_student10000 where level BETWEEN 4 and 5;
-- (7). 反向操作 != <> not in not exists is not null <br>
-- ***`我看很多文章上都说is null is notnull != 这种反向操作等等都一定用不上索引 但是事实情况其实并没有这么肯定，这种情况要具体问题具体分析 有可能能用上索引 有可能用不上 不能那么肯定的说一定用不上 那样会误导人的。 不推荐这么写只是这种写法大部分情况 用索引的几率会很小(具体在于mysql优化器决定的)。`***<br>
explain select * from xzll_student2000000 where name not in ('黄');
explain select * from xzll_student2000000 where name != '黄';
explain select * from xzll_student2000000 where name <> '黄';
explain select name from xzll_student2000000 where not EXISTS (select name from xzll_student2000000 ) ;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE xzll_student2000000 ALL idx_name 1925159 75.71 Using where
-- 结论: 结果无一例外 没有一个能使用上索引
-- (8). is not null is null 相关的null操作 ; ---> 由于目前dba规范所有字段必须设置为 not null so null对索引的影响暂时不做探究 平时开发时，最好都设置位not null类型
----------------------索引之 联合索引 (类型为二级索引) 失效与生效演示-------------------------
-- (9). 联合索引常见失效、生效场景
-- 为了不影响联合索引的执行计划 我们暂时把name project的单列索引删除掉 等需要时候在加上
DROP INDEX idx_name ON xzll_student2000000;
DROP INDEX idx_project ON xzll_student2000000;
show index from xzll_student2000000;
-- 新建联合索引 注意顺序
ALTER TABLE `xzll_student2000000`
ADD INDEX `idx_name_address_project`(`name`,address,project) USING BTREE;
explain select * from xzll_student2000000 where name ='黄';
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE xzll_student2000000 ref idx_name_address_project idx_name_address_project 1022 const 57902 100.00
explain select * from xzll_student2000000 where name ='黄' and address = '张家口';
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE xzll_student2000000 ref idx_name_address_project idx_name_address_project 2044 const,const 82 100.00
explain select * from xzll_student2000000 where name ='黄' and address = '张家口' and project ='消防';
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE xzll_student2000000 ref idx_name_address_project idx_name_address_project 3066 const,const,const 1 100.00
-- 结论: 以上三个sql都可以使用到联合索引 因为符合最左前缀原则 且计划中的 ref为const
-- 另外注意以上三个sql的key_len 是不同的 。说明第一个只使用到了name列 第二个使用到了name和address列 第三个都使用到了
explain select * from xzll_student2000000 where name = '黄' and address like '张%';
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE xzll_student2000000 range idx_name_address_project idx_name_address_project 2044 659 100.00 Using index condition
-- 结论: 该sql将会使用到索引 但是只会使用到name和address 从key_len就可以看出来
explain select * from xzll_student2000000 where name = '黄' and project = '消防';
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE xzll_student2000000 ref idx_name_address_project idx_name_address_project 1022 const 57902 10.00 Using index CONDITION
-- 结论: 可以看到 由于缺少address列 所以只能使用到name列
explain select * from xzll_student2000000 where address = '张家口';
explain select * from xzll_student2000000 where project = '消防';
-- 结论: 像这种更使用不上啦 总而言之 言而总之 都是不符合最左前缀原则
explain select * from xzll_student2000000 where name like '黄%' and address like '张%';
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE xzll_student2000000 ALL idx_name_address_project 1925159 2.89 Using WHERE
-- 结论: 由于name匹配的数据较多 mysql觉得回标不如全表扫呢 所以type变为all了
-- 思考: 联合索引为什么会有最左前缀原则呢？(此处只是针对二级索引)
-- 结论: 1.因为mysql在插入数据时候 , 会为该联合索引新建一个b+tree，后续的新增修改 都会使得这个b+tree进行变化 (主要是维护其有序性)。
-- 2.拿联合索引 idx_name_address_project来说其数据组织方式是有序的，排序规则是根据 name , address ,project来进行的 name相同按照address排 address相同按照project排 project还相同 那么根据主键id排
-- 3.他这么做得原因在于 mysql查询数据时候 内部使用二分法，所以数据必须保证有序，而无序的数据不能使用二分法莱查找 所以只能挨个遍历了。这就是最左前缀原则产生的原因。(个人理解哈)
--------------------where group by order by 使用联合索引的失效生效示例-------------------
explain select * from xzll_student2000000 order by project limit 10;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE xzll_student2000000 index idx_name_address_project 3066 10 100.00
-- 结论 只使用联合索引中的左前列 如 order by name 或者 order by name , address 也会使用到索引
explain select * from xzll_student2000000 order by name ,address ,project limit 10;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE xzll_student2000000 index idx_name_address_project 3066 10 100.00
-- 结论 不带任何条件的查询 且order by后使用联合索引排序 也是可以使用到索引的 但是注意 我加了个limit10 因为不limit得话 mysql会觉得不如全表扫描快呢
-- 注意 排序规则必须统一 asc或者desc 否则将会filesort
-- 我们来看下如果不按照索引顺序进行排序呢？
explain select * from xzll_student2000000 order by name , project,address limit 10;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE xzll_student2000000 ALL 1925159 100.00 Using filesort
-- 结论: order by时候 必须符合最左前缀原则
explain select * from xzll_student2000000 where name = '黄' order by address desc,project desc;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE xzll_student2000000 ref idx_name_address_project idx_name_address_project 1022 const 57902 100.00 Backward index scan
-- 结论: mysql8开始 添加了降序索引 Backward index scan 避免了filesort 可以看到 必须是order后边跟着 索引列才会使用降序索引排序 且where条件中 要使用到最左列
explain select * from xzll_student2000000 order by address desc,project desc;
-- 结论: 此时不能使用到索引啦 还是最左前缀原则嘛
explain select address from xzll_student2000000 where name like '黄%' group by address,project;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE xzll_student2000000 index idx_name_address_project idx_name_address_project 3066 1925159 33.25 Using where; Using index; Using temporary
-- 结论: 由于group by后只返回分组列 且在查询条件中没有非索引列 所以这里使用到的type是index 这个没啥好说的
explain select address from xzll_student2000000 where name like '黄%' group by sex,address,project;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE xzll_student2000000 ALL idx_name_address_project 1925159 33.25 Using where; Using temporary
-- 结论: 当group by后有非索引列时候 也会全表扫描
-- 总结: 联合索引下 group by order by 操作基本也是遵守最左前列原则
-------------------多个单列索引组合使用 where group order 时候 的索引失效有效演示-------------
-- (11).
-- 添加两个索引
ALTER TABLE xzll_student2000000
ADD INDEX `idx_channel`(`channel`) USING BTREE;
ADD INDEX `idx_hobby`(`hobby`) USING BTREE;
explain select hobby from xzll_student2000000 where channel = '百度' group by hobby;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE xzll_student2000000 ref idx_hobby,idx_channel idx_channel 1022 const 46430 100.00 Using temporary
-- 结论: 可以使用到idx_channel索引 但是 分组时候hobby索引不会用到，可以看到 Extra列是 Using temporary ，表示分组时候使用的是临时表 而不是利用索引来分组，ps:临时表其实效率比较低的
explain select * from xzll_student2000000 where hobby = '123' order by channel desc;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE xzll_student2000000 ref idx_hobby idx_hobby 1022 const 1 100.00 Using filesort
-- 结论: 可以使用上idx_hobby索引 但是排序时候就用不上channel了 因为Extra输出为 Using filesort 文件排序也是效率比较低的
-- 至此索引部分总结完毕 我知道一定会有漏了的case 也可能会有不对的地方，如发现请指正不胜感激
-- <<<<<<<<<<<<<<<<<<<<<<<<<< 索引优化 实际案例 ****** 1 ***** >>>>>>>>>>>>>>>>>>>>>>
-- 准备工作
-- 首先加两个联合索引
ALTER TABLE xzll_student2000000
ADD INDEX `idx_ctime`(`ctime`) USING BTREE,
ADD INDEX `idx_ctime_status`(`ctime`,`status`) USING BTREE,
ADD INDEX `idx_ctime`(`ctime`) USING BTREE,
ADD INDEX `idx_status_ctime`(`status`,`ctime`) USING BTREE;
show index from xzll_student2000000
-- 为了模拟真实场景 我们为ctime 随机生成过去十天的时间
update xzll_student2000000 set ctime=DATE_ADD(ctime , INTERVAL FLOOR(1 - (RAND() * 864000)) SECOND);
explain select * from xzll_student2000000 where ctime > '2021-08-10 00:00:00' and ctime <'2021-08-15 23:59:59' limit 1500000,10 -- 12.376s 啧啧~ 速度感人
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE xzll_student2000000 range idx_ctime_status idx_ctime_status 4 953471 100.00 Using index condition; Using MRR
-- 耗时 12.376秒 哇咔咔 现实情况的话 你可不知道使用系统的人 会把时间筛选条件拉到多长呀 假如他选择一个月呢？那不是gg了
-- Using index condition (在搜索条件中使用了索引 但是却无法利用索引 就会出现这玩意 也叫索引下推)，Using MRR 表示mysql读取数据时候是顺序读取 8.0默认开启
-- 为甚这么慢？ 因为 limit 1500000,10 这个坑货 ，mysql会从第一条记录找 一直找到第 1500000这个位置，需要扫描的数据太多了 ，导致IO开销非常大 如果你换成limit 100,10 在执行sql你会发现 哇好快 哈哈
-- 如何优化呢？？？ 如下
explain
select a.* from xzll_student2000000 a
INNER join (
select id from xzll_student2000000 where ctime > '2021-08-01 00:00:00' and ctime <'2021-08-15 23:59:59' limit 1500000,10
) b on a.id =b.id
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> ALL 211840 100.00
1 PRIMARY a eq_ref PRIMARY PRIMARY 8 b.id 1 100.00
2 DERIVED xzll_student2000000 range idx_ctime_status,idx_status_ctime,idx_ctime idx_status_ctime 8 211840 100.00 Using where; Using index for skip scan
-- 0.862秒 嗯还可以接受。(如不能接受 那就使用更高级的优化 如同步es或者分库分表了)， 该思想mysql高性能中称为 延迟关联
-- (其本质是利用二级索引的特性 ，根据索引查询出数据后 先不回表，而是只返回id，我们知道这样就可以使用上索引覆盖了)，然后使用id集 去和主表关联 注意是inner join 查出想要的数据
-- 注意有的也把他写为in的方式 但是我个人不推荐 因为in里边的数据我们不知道有多大，当然如果加了limit无所谓 不加limit的话 in太多了会有问题
-- 疑问 为什么在第三个计划中会使用 idx_status_ctime 而不是idx_ctime_status呢 明明ctime区分度更高呀 第三个计划的Extra中 有个 Using index for skip scan 他是个什么东东???
-- 而且我的where条件是使用 ctime的范围查询 now我们看下更详细的执行计划 如下: 找出具体执行计划的json串 黏贴到sublime中 因为 输出的json真的是太长了 哈哈
set session optimizer_trace="enabled=on",end_markers_in_json=on;
select id from xzll_student2000000 where ctime > '2021-08-01 00:00:00' and ctime <'2021-08-15 23:59:59' limit 1500000,10;
SELECT trace FROM information_schema.OPTIMIZER_TRACE;
-- 通过查看trace 我们可以了解到 使用 idx_status_ctime的原因并不是和范围查询有关系 而是 索引跳跃扫描 什么是索引跳跃扫描？google一下你就知道
-- 简单解释出现索引跳跃扫描的情况：当复合索引前导列唯一值少，后导列唯一值多的情况下 一般优化器倾向于索引跳跃扫描，如果前导列唯一值变多了，则M优化器不会选择索引跳跃扫描，取决于索引列的数据分表情况
-- 同时 不使用idx_ctime_status的原因也很简单 cost即成本太高了 在 range_scan_alternatives 属性中可以看到原因
-- <<<<<<<<<<<<<<<<<<<<<<<<<< 索引优化 实际案例 ****** 2 ***** >>>>>>>>>>>>>>>>>>>>>>
-- 未完，待续。。。
注意 此文章最好复制到navicat查看更清晰明了
– 如有错误，恳请指正，不胜感激。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
