mysql 索引篇

复制代码
-- 8.0.16
select version()
;
drop table if exists test1
-- 建表test1
CREATE TABLE `test1` (
  `id` bigint NOT NULL,
  `code` varchar(30) NOT NULL,
  `age` int NOT NULL,
  `name` varchar(30) NOT NULL,
  `height` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_code_age_name` (`code`,`name`, `age`) USING BTREE,
  KEY `idx_height` (`height`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
-- 插入数据
INSERT INTO `test1`(`id`, `code`, `name`, `age`,`height`) VALUES (1, '001', '张飞', 18,'7');
INSERT INTO `test1`(`id`, `code`, `name`, `age`,`height`) VALUES (2, '002', '关羽', 19,'8');
INSERT INTO `test1`(`id`, `code`, `name`, `age`,`height`) VALUES (3, '002', '关羽', 19,null);

-- system：这种类型要求数据库表中只有一条数据，是const类型的一个特例，一般情况下是不会出现的。
-- const：通过一次索引就能找到数据，一般用于主键或索引作为条件，这类扫描效率极高，，速度非常快。
-- eq_ref：常用于主键或索引扫描，一般指使用主键的关联查询
-- ref : 常用于非主键和索引扫描。
-- ref_or_null：这种连接类型类似于ref，区别在于MySQL会额外搜索包含NULL值的行
-- index_merge：使用了索引合并优化方法，查询使用了两个以上的索引。
-- unique_subquery：类似于eq_ref，条件用了in子查询
-- index_subquery：区别于unique_subquery，用于非索引，可以返回重复值。
-- range：常用于范围查询，比如：between ... and 或 In 等操作
-- index：全索引扫描
-- ALL：全表扫描
-- 索引长度
-- 1.所有的索引字段，如果没有设置not null，则需要加一个字节。
-- 2.定长字段，int占四个字节、date占三个字节、char(n)占n个字符。
-- 3.对于变成字段varchar(n)，则有n个字符+两个字节。
-- 4.不同的字符集，一个字符占用的字节数不同。latin1编码的，一个字符占用一个字节，gbk编码的，一个字符占用两个字节，utf8编码的，一个字符占用三个字节。
-- 5.索引长度 char()、varchar()索引长度的计算公式：
-- (Character Set：utf8mb4=4,utf8=3,gbk=2,latin1=1) * 列长度 + 1(允许null) + 2(变长列)
--
-- show index
SHOW INDEX from test1
-- 1    SIMPLE    test1        ref    idx_code_age_name    idx_code_age_name    92 = 30*3 + 2     const    1    100.0    
explain  select * from test1 
    where code='001111';
   
--
-- 不满足最左侧前缀原则
-- 将组合索引想成书的一级目录、二级目录、三级目录，如index(a,b,c)，相当于a是一级目录，b是一级目录下的二级目录，
-- c是二级目录下的三级目录。要使用某一目录，必须先使用其上级目录，除了一级目录除外。
--
-- code age name 有效
explain  select * from test1 
    where  code='001' and age=18  and  name='张飞' ;
-- 1    SIMPLE    test1        ref    idx_code_age_name    idx_code_age_name    188 = 30*3 + 2 + 30*3 + 2 + 4    const,const,const    1    100.0    
-- age code name 有效  在查询之前mysql会自动优化顺序
explain  select * from test1 
    where  age=18 and code='001'  and  name='张飞' ;
;
-- 1    SIMPLE    test1        ALL                    2    50.0    Using where
-- 无效 
explain  select * from test1 
    where age=18;
   
-- 范围索引没有放在最后
-- 1    SIMPLE    test1        range    idx_code_age_name    idx_code_age_name    96        1    50.0    Using index condition
-- 从上图中看出索引长度变成： 96 ， 96 = 30*3 + 2 + 4 ，只用到了两个索引字段 code 和age ，而 name 字段的索引失效了。
-- name 失效
EXPLAIN  select * from test1 
where  code='001' and age>18  and  name='张飞' ;
-- 创建索引的时候 age 也要放最后
-- 1    SIMPLE    test1        range    idx_code_age_name    idx_code_age_name    188        1    100.0    Using index condition
-- 有效
EXPLAIN  select * from test1 
where code='001' and  name='张飞' and age>18 ;

--
-- 使用了select *
-- 其实这里用到了： 覆盖索引 。
-- 如果 select 的列都是索引列，则被称为 覆盖索引 。
-- 如果 select 的列不只包含索引列，则需要 回表 ，即回到表中再查询出其他列，效率相当更低一些。 select * 大概率需要查询非索引列，需要 回表 ，因此要少用。
-- 当然，本文中很多示例都使用了 select * ，主要是我表中只有两条数据，为了方便演示，正常业务代码中是要杜绝这种写法的。
-- 
-- 1    SIMPLE    test1        ALL                    2    100.0    
EXPLAIN  select * from test1
-- 1    SIMPLE    test1        index        idx_code_age_name    188        2    100.0    Using index
EXPLAIN  select code,age,name  from test1
-- 1    SIMPLE    test1        index        idx_code_age_name    188        2    100.0    Using index
EXPLAIN  select id,code,age,name  from test1
-- 1    SIMPLE    test1        ALL                    2    100.0    
EXPLAIN  select id,code,age,name,height  from test1

--
-- 4.索引列上有计算
-- 
-- 1    SIMPLE    test1        index        idx_height    4        2    100.0    Using where; Using index
explain  select * from test1 
where  height+1 =7;
explain  select * from test1 
where  height =7;
-- 5.索引列上使用了函数
-- 1    SIMPLE    test1        index        idx_height    4        2    100.0    Using where; Using index
-- 
explain  select * from test1 
where   SUBSTR(height,1,1)=8;

--
-- 6.字符类型没加引号
-- 1    SIMPLE    test1        ALL    idx_code_age_name                2    50.0    Using where
explain  select * from test1 
where  code = 12;

-- 1    SIMPLE    test1        ref    idx_code_age_name    idx_code_age_name    92    const    1    100.0    
explain  select * from test1 
where  code = '12';

-- 7.用is null和is not null没注意字段是否允许为空
-- 前提允许字段为null
-- 1    SIMPLE    test1        ref    idx_height    idx_height    5    const    1    100.0    Using index condition
explain  select * from test1 
where  height is  null;
-- 1    SIMPLE    test1        range    idx_height    idx_height    5        2    100.0    Using index condition
explain  select * from test1 
where  height is  not  null;
-- 1    SIMPLE                                        no matching row in const table
-- -- 失效
explain  select * from test1 
where  height <> null;
-- 1    SIMPLE    test1        ref    idx_height    idx_height    5    const    1    100.0    Using index condition
explain  select * from test1 
where  height <=> null;

-- 如果字段不允许为空，则is null 和 is not null这两种情况索引都会失效。
-- 如果字段允许为空，则is null走 ref 类型的索引，而is not null走 range 类型的索引。

--
-- 8.like查询左边有%
--
-- 1    SIMPLE    test1        ALL                    3    33.33    Using where
-- 失效
explain  select * from test1 
where  code like '%001';
-- 1    SIMPLE    test1        range    idx_code_age_name    idx_code_age_name    92        1    100.0    Using index condition
explain  select * from test1 
where  code like  '001%';
-- 1    SIMPLE    test1        ALL                    3    33.33    Using where
-- 失效
explain  select * from test1 
where  code like  '%001%';
-- 如果有这种业务场景可以使用es 代替 mysql 存储数据

--
-- 9.使用or关键字时没有注意 字段都有索引的话 好像没有失效
-- 
-- 1    SIMPLE    test1        range    idx_height    idx_height    5        2    100.0    Using index condition
explain  select * from test1 
where  height = 8  or height = 9;
-- 1    SIMPLE    test1        index_merge    idx_code_age_name,idx_height    idx_code_age_name,idx_height    92,5        2    100.0    Using sort_union(idx_code_age_name,idx_height); Using where
explain  select * from test1 
where  code = '001' or height = 8;
--  
explain (select * from test1 where   code = '001') 
union (select * from test1 where  height = 8);


--
-- 索引失效的常见误区
-- 

--
-- 1.使用not in会导致索引失效
-- 1    SIMPLE    test1        range    idx_height    idx_height    5        3    100.0    Using index condition
-- 有效
explain  select * from test1 
where  height not  in (7,8);

-- 
-- 2.使用不等于号会导致索引失效
-- 1    SIMPLE    test1        range    idx_height    idx_height    5        2    100.0    Using index condition
-- 有效
-- 需要特别说明的是mysql5.7和5.8不同的版本效果不一样，5.7中这种情况sql执行结果是全表扫描，5.78也是有range类型索引
-- 而5.8中使用了 range 类型索引。5.7中如果想使用索引该怎么办呢？答案：使用大于和小于代替不等于。
-- 在这里温馨的提醒一声，不等于号不只是 != ，还包括 <> 。
explain  select * from test1 
where height!=8;
-- 1    SIMPLE    test1        range    idx_height    idx_height    5        2    100.0    Using index condition
explain  select * from test1 
where height<>8;

-- 
-- 3.order by索引字段顺序不当导致索引失效
-- 1    SIMPLE    test1        ref    idx_code_age_name    idx_code_age_name    92    const    1    100.0    
EXPLAIN  select * from test1 
where  code='001' order  by age,name;

-- 1    SIMPLE    test1        ref    idx_code_age_name    idx_code_age_name    92    const    1    100.0    
EXPLAIN  select * from test1 
    where  code='001' order  by  name;
-- 1    SIMPLE    test1        ref    idx_code_age_name    idx_code_age_name    92    const    1    100.0    Using filesort
EXPLAIN  select * from test1 
    where  code='001' order  by  age,name; -- 和联合索引顺序不一致
-- 1    SIMPLE    test1        ref    idx_code_age_name    idx_code_age_name    92    const    1    100.0    
EXPLAIN  select * from test1 
    where  code='001' order  by  name, age; 
--
从上图中看出还是走了 ref 类型的索引，索引长度是 92 ，但是额外信息中提示： Using filesort ，即按文件重排序。

上面两个例子能够看出有没有使用索引跟 where 后面的条件有关，而跟 order by 后面的字段没关系。

而需不需要按文件重排序，则跟 order by 后面的字段有直接关系。

问题来了，额外信息中提示： Using filesort 这种该如何优化？

答：这种情况一般是联合索引中索引字段的顺序，跟 sql 中 where 条件及 order by 不一致导致的，只要顺序调整一致就不会出现这个问题。


三. 索引设计的几个建议：
优先使用唯一索引，能够快速定位
为常用查询字段建索引
为排序、分组和联合查询字段建索引
一张表的索引数量不超过5个
表数据量少，可以不用建索引
尽量使用占用空间小的字段建索引
用idx_或unx_等前缀命名索引，方面查找
删除没用的索引，因为它会占一定空间
四. 彩蛋：
特别说明：索引失效除了上述的常见问题之外， mysql 通过索引扫描的行记录数超过全表的10%~30% 左右,优化器也可能不会走索引，自动变成全表扫描。

送给大家一个避坑口诀：

全职匹配我最爱，最左前缀要遵守
带头大哥不能死，中间兄弟不能断
索引列上少计算，范围列后全失效
like百分写最右，覆盖索引不写*
不等空值还有or ， 索引影响要注意；
字符字段引号不能丢，sql优化有诀窍。
复制代码

 

posted @   linzm14  阅读(37)  评论(0)  编辑  收藏  举报
相关博文：
·  mysql 5.7 和 8.0 json 类型 json 数组类型 普通字符串类型 10w数据 查询速度差异
·  mysql 两百万数据 深分页性能优化
·  mysql explain的使用和索引失效的情况
·  sql优化-1-查询执行计划:explain
·  sql优化-2-索引失效
阅读排行：
· 周边上新：园子的第一款马克杯温暖上架
· 分享 3 个 .NET 开源的文件压缩处理库，助力快速实现文件压缩解压功能！
· Ollama——大语言模型本地部署的极速利器
· DeepSeek如何颠覆传统软件测试？测试工程师会被淘汰吗？
· 使用C#创建一个MCP客户端
点击右上角即可分享
微信分享提示
AI IDE Trae