Tim

一枚野生程序员~

  • 主页
  • 分类
  • 标签
  • 归档
  • 关于
所有文章 工具

Tim

一枚野生程序员~

  • 主页
  • 分类
  • 标签
  • 归档
  • 关于

【面试】MySQL索引失效分析

阅读数:次 2019-09-22
字数统计: 974字   |   阅读时长≈ 4分

本文主要向大家介绍了MySQL数据库之索引会失效的原因分析及解决索引失效的方法 ,通过具体的内容向大家展现,希望对大家学习MySQL数据库有所帮助。

先看下面的一个示例:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
create table test_03(
id int primary key not null auto_increment,
c1 char(10),
c2 char(10),
c3 char(10),
c4 char(10),
c5 char(10)
);

insert into test_03(c1, c2, c3, c4, c5) values ('a1', 'a2', 'a3', 'a4', 'a5');
insert into test_03(c1, c2, c3, c4, c5) values ('b1', 'b2', 'b3', 'b4', 'b5');
insert into test_03(c1, c2, c3, c4, c5) values ('c1', 'c2', 'c3', 'c4', 'c5');
insert into test_03(c1, c2, c3, c4, c5) values ('d1', 'd2', 'd3', 'd4', 'd5');
insert into test_03(c1, c2, c3, c4, c5) values ('e1', 'e2', 'e3', 'e4', 'e5');

create index idx_test03_c1234 on test_03(c1, c2, c3, c4);

show index from test_03;

explain select * from test_03 where c1='a1' and c2='a2' and c3='a3' and c4='a4';
explain select * from test_03 where c4='a1' and c3='a2' and c1='a3' and c2='a4';

mark

很显然最下面的SQL语句并没有按照索引的建立顺序来查询,但是由于MySQL优化器对这条SQL做了优化,使得优化后的语句和上面的是一样的顺序,这样就能顺利用到索引了!但是由于这样写SQL语句还是会让底层做一次SQL优化,所以还不如一开始就按照建立索引的顺序来写SQL。

mark

上面的SQL会导致c4这个索引失效,那么如果是c4的范围条件写前面呢?同样的道理,SQL优化器会对SQL语句做优化,优化后的语句成了explain select * from test_03 where c1='a1' and c2='a2' and c3='a3' and c4>'c4'; 所以自然会用到四个索引了!

再看看下面的order by的例子,很显然用于查找的索引有两个就是c1、c2。c3用于排序了而不是查找,c4这个索引没有用到:

mark

所以上面的SQL和explain select * from test_03 where c1='a1' and c2='a2' order by c3; 一样的,和c4根本没有关系了

mark

上面这个例子呢主要是说中间兄弟没了,也就是你都没有给我第三层梯子,我怎么通过第四层排序,所以只能硬着头皮完成任务,那就Using filesort 呗!

mark

用到了一个c1索引,但是c2、c3用于排序,无filesort

mark

用了c1、c2两个字段索引,但是c2、c3用于排序,无filesort。看看下面这一种情况,为什么explain select * from test_03 where c1='a1' and c2='a2' and c5='a5' order by c3, c2; 并没有产生文件内排序呢?

mark

因为c2已经是常量了,在已知字段是常量的情况下需要排序吗?肯定不需要。所以即使你写成了order by c3,c2 但是实际上只是对c3排序!

mark

由上面的例子可以看出,用到了c1来查找,c4由于中间兄弟的消失是失效的索引,c2、c3用于分组,但是c2、c3如果不按照顺序分组的话就基本是是死掉了,出现了using temporary,这个是什么意思呢?看这个Explain中的Using temporary

group 表面上是分组,分组之前必排序,所以说和order by排序的法则和索引优化的原则是一致的!

定值、范围还是排序,一般order by是给个范围。group by 基本上都需要进行排序,会有临时表产生

一般性建议:

对于单键索引,尽量选择针对当前query过滤性更好的索引

在选择组合索引的时候,当前Query中过滤性最好的字段在索引字段顺序中,位置越靠前越好。

在选择组合索引的时候,尽量选择可以能包含当前query中的where子句中更多字段的索引

尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

赏

谢谢你请我喝咖啡

支付宝
微信
  • 本文作者: Tim
  • 本文链接: https://zouchanglin.cn/1474252714.html
  • 版权声明: 本博客所有文章除特别声明外,均采用 CC BY-SA 4.0 许可协议。转载请注明出处!
  • MySQL
  • 数据库

扫一扫,分享到微信

谈谈两次比赛的感想
如何避免MySQL索引失效与三个优化实例
目录,不存在的…
© 2017-2021 Tim
本站总访问量次 | 本站访客数人
  • 所有文章
  • 工具

tag:

  • 生活
  • Android
  • 索引
  • MySQL
  • 组件通信
  • Nginx
  • JavaSE
  • JUC
  • JavaWeb
  • 模板引擎
  • 前端
  • Linux
  • 计算机网络
  • Docker
  • C/C++
  • JVM
  • 上传下载
  • JavaEE
  • SpringCloud
  • Golang
  • Gradle
  • 网络安全
  • 非对称加密
  • IDEA
  • SpringBoot
  • Jenkins
  • 字符串
  • vim
  • 存储
  • 文件下载
  • Mac
  • Windows
  • NIO
  • RPC
  • 集群
  • 微服务
  • SSH
  • 配置中心
  • XML
  • Chrome
  • 压力测试
  • Git
  • 博客
  • 概率论
  • 排序算法
  • 分布式
  • 异常处理
  • 文件系统
  • 哈希
  • openCV
  • 栈
  • 回溯
  • SpringCore
  • 流媒体
  • rtmp
  • 面向对象
  • Vue
  • ElementUI
  • 软件工程
  • 异步
  • 自定义UI
  • ORM框架
  • 模块化
  • 交互式
  • Jsoup
  • Http Client
  • LRUCache
  • RabbitMQ
  • 消息通信
  • 服务解耦
  • 负载均衡
  • 权限
  • 多线程
  • 单例模式
  • Protobuf
  • 序列化
  • Python
  • m3u8
  • 堆
  • 二叉树
  • 自定义View
  • 观察者模式
  • 设计模式
  • 线程池
  • 动态扩容
  • 高可用
  • GC
  • ffmpeg
  • SpringMVC
  • REST
  • Redis
  • 缓存中间件
  • UML
  • Maven
  • Netty
  • 高性能网络
  • IPC通信
  • IO
  • Stream
  • 发布订阅
  • SQLite
  • Hash
  • 集合框架
  • 链表
  • Lambda
  • 汇编语言
  • 组件化
  • Router
  • 开发工具

    缺失模块。
    1、请确保node版本大于6.2
    2、在博客根目录(注意不是yilia-plus根目录)执行以下命令:
    npm i hexo-generator-json-content --save

    3、在根目录_config.yml里添加配置:

      jsonContent:
        meta: false
        pages: false
        posts:
          title: true
          date: true
          path: true
          text: false
          raw: false
          content: false
          slug: false
          updated: false
          comments: false
          link: false
          permalink: false
          excerpt: false
          categories: false
          tags: true
    

  • 思维导图
  • PDF工具
  • 无损放大
  • 代码转图
  • HTTPS证书