Java性能调优实战
刘超
金山软件西山居技术经理
立即订阅
7535 人已学习
课程目录
已完结 48 讲
0/4登录后,你可以任选4讲全文学习。
开篇词 (1讲)
开篇词 | 怎样才能做好性能调优?
免费
模块一 · 概述 (2讲)
01 | 如何制定性能调优标准?
02 | 如何制定性能调优策略?
模块二 · Java编程性能调优 (10讲)
03 | 字符串性能优化不容小觑,百M内存轻松存储几十G数据
04 | 慎重使用正则表达式
05 | ArrayList还是LinkedList?使用不当性能差千倍
加餐 | 推荐几款常用的性能测试工具
06 | Stream如何提高遍历集合效率?
07 | 深入浅出HashMap的设计与优化
08 | 网络通信优化之I/O模型:如何解决高并发下I/O瓶颈?
09 | 网络通信优化之序列化:避免使用Java序列化
10 | 网络通信优化之通信协议:如何优化RPC网络通信?
11 | 答疑课堂:深入了解NIO的优化实现原理
模块三 · 多线程性能调优 (10讲)
12 | 多线程之锁优化(上):深入了解Synchronized同步锁的优化方法
13 | 多线程之锁优化(中):深入了解Lock同步锁的优化方法
14 | 多线程之锁优化(下):使用乐观锁优化并行操作
15 | 多线程调优(上):哪些操作导致了上下文切换?
16 | 多线程调优(下):如何优化多线程上下文切换?
17 | 并发容器的使用:识别不同场景下最优容器
18 | 如何设置线程池大小?
19 | 如何用协程来优化多线程业务?
20 | 答疑课堂:模块三热点问题解答
加餐 | 什么是数据的强、弱一致性?
模块四 · JVM性能监测及调优 (6讲)
21 | 磨刀不误砍柴工:欲知JVM调优先了解JVM内存模型
22 | 深入JVM即时编译器JIT,优化Java编译
23 | 如何优化垃圾回收机制?
24 | 如何优化JVM内存分配?
25 | 内存持续上升,我该如何排查问题?
26 | 答疑课堂:模块四热点问题解答
模块五 · 设计模式调优 (6讲)
27 | 单例模式:如何创建单一对象优化系统性能?
28 | 原型模式与享元模式:提升系统性能的利器
29 | 如何使用设计模式优化并发编程?
30 | 生产者消费者模式:电商库存设计优化
31 | 装饰器模式:如何优化电商系统中复杂的商品价格策略?
32 | 答疑课堂:模块五思考题集锦
模块六 · 数据库性能调优 (8讲)
33 | MySQL调优之SQL语句:如何写出高性能SQL语句?
34 | MySQL调优之事务:高并发场景下的数据库事务调优
35 | MySQL调优之索引:索引的失效与优化
36 | 记一次线上SQL死锁事故:如何避免死锁?
37 | 什么时候需要分表分库?
38 | 电商系统表设计优化案例分析
39 | 数据库参数设置优化,失之毫厘差之千里
40 | 答疑课堂:MySQL中InnoDB的知识点串讲
模块七 · 实战演练场 (4讲)
41 | 如何设计更优的分布式锁?
42 | 电商系统的分布式事务调优
43 | 如何使用缓存优化系统性能?
44 | 记一次双十一抢购性能瓶颈调优
结束语 (1讲)
结束语 | 栉风沐雨,砥砺前行!
Java性能调优实战
登录|注册

35 | MySQL调优之索引:索引的失效与优化

刘超 2019-08-10
你好,我是刘超。
不知道你是否跟我有过同样的经历,那就是作为一个开发工程师,经常被 DBA 叫过去“批评”,而最常见的就是申请创建新的索引或发现慢 SQL 日志了。
记得之前有一次迭代一个业务模块的开发,涉及到了一个新的查询业务,需要根据商品类型、订单状态筛选出需要的订单,并以订单时间进行排序。由于 sku 的索引已经存在了,我在完成业务开发之后,提交了一个创建 status 的索引的需求,理由是 SQL 查询需要使用到这两个索引:
select * from order where status =1 and sku=10001 order by create_time asc
然而,DBA 很快就将这个需求驳回了,并给出了重建一个 sku、status 以及 create_time 组合索引的建议,查询顺序也改成了 sku=10001 and status=1。当时我是知道为什么要重建组合索引,但却无法理解为什么要添加 create_time 这列进行组合。
从执行计划中,我们可以发现使用到了索引,那为什么 DBA 还要求将 create_time 这一列加入到组合索引中呢?这个问题我们在第 32 讲中提到过,相信你也已经知道答案了。通过故事我们可以发现索引知识在平时开发时的重要性,然而它又很容易被我们忽略,所以今天我们就来详细聊一聊索引。
取消
完成
0/1000字
划线
笔记
复制
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。
该试读文章来自付费专栏《Java性能调优实战》,如需阅读全部文章,
请订阅文章所属专栏。
立即订阅
登录 后留言

精选留言(23)

  • QQ怪
    回答老师问题:
    按照老师的操作了一遍,实验小部分区间是会走order_id索引的,但是查询范围继续增大 ,反而不走索引而是全表扫描,大概我估摸着的是小于全表5分之一区间能够走索引,超过5分之一会全表扫描,可以使用force index(索引名)强制使用该索引,这就是有些sql表开始跑的挺快的,后面越来越慢的原因吧。。但不清楚mysql优化器为啥要这样选择,希望老师解惑?

    作者回复: 因为order_id索引不能覆盖我们要查询的信息,所以在对order_id查询之后还需要一次回表来查找到整行数据,虽然order_id索引是顺序存放的,但是相对于主键id存放的数据顺序是不一致的,所以存在每次回表都是随机获取整行数据,如果在获取大量数据时,通过这种方式获取数据性能肯定是不理想的。

    所以mysql一般判断在查询超过整个表20%的数据时,就会考虑使用聚族索引来查找数据,这种方式顺序读取数据的可能性要大于使用辅助索引的随机读。

    在查询少量数据的情况下,使用辅助索引性能更加,而查询大量数据时,就未必了。

    如果我们发现在查询一定量数据使用辅助索引要比主键索引快,而数据库又没有按照我们期望的去使用辅助索引,则我们可以通过子查询或force index来强制使用辅助索引。

    2019-08-10
    2
    16
  • CCC
    对索引进行函数操作或者表达式计算也会导致索引的失效

    作者回复: 对的,点赞补充

    2019-08-10
    4
  • Charles
    想问下老师为什么回表查询的速度会慢于直接用主键查询,因为回表也是使用主键ID去查询的,就算查询的数据量大,用不用子查询都是使用主键ID去回表或是查询,速度应该一样吧

    作者回复: 回表就相当于两次索引树扫描操作了,而主键查询只有一次。

    2019-08-14
    1
    3
  • 张三丰
    ”如果不存在辅助索引,此时会通过查询聚簇索引来统计行数,如果此时正好存在一个辅助索引,则会通过查询辅助索引来统计行数,减少 I/O 操作。“
    这有什么区别吗?都是通过索引统计行数

    作者回复: 区别在于聚簇索引存储了其他数据,而辅助索引只保存了索引列和主键,所以通过查询辅助索引统计行检索的数据量会更少,I/O操作会更少

    2019-10-04
    2
  • Geek__ad4af7fe01f4
    请问老师,既然使用辅助索引效率低,mysql默认超出20%又使用主键索引优化,而优化的效果又变低,为何还要强制使用辅助索引?

    这里强制使用辅助索引的优化 和下面您的描述不是冲突吗?

    因为order_id索引不能覆盖我们要查询的信息,所以在对order_id查询之后还需要一次回表来查找到整行数据,虽然order_id索引是顺序存放的,但是相对于主键id存放的数据顺序是不一致的,所以存在每次回表都是随机获取整行数据,如果在获取大量数据时,通过这种方式获取数据性能肯定是不理想

    作者回复: 这个跟具体场景有关系,在数据量非常大的情况下,可能使用辅助索引会效率更高些。

    2019-08-27
    2
  • 张学磊
    由于是select *操作,所以每条记录都需进行回表,当server层分析器发现between的范围太大时,使用辅助索引存在大量回表操作,所以觉得得不偿失,故而直接使用主键索引。如果想使用我们期望的索引,需要给server层分析器一个hint,force index(idx_order_id)

    作者回复: 分析到位,答案正确。

    2019-08-10
    1
  • 某、 
    使用某个字段中字符串的前几个字符建立索引?这句不是很明白,能否细讲一下,能否以身份证这个字段作个栗子?
    2019-12-05
  • 传传传传传传奇
    来晚了
    有一事不明,通过创建覆盖索引(如文中将商品编码、名称、价格作为一个组合索引)在某些情况下可以避免回表。但是非主键索引存储的却是主键的值,并以此来作为指向行的指针。在查询商品编码的时候,怎么理解避免回表吗?
    2019-11-05
  • 风轻扬
    老师,我试了一下最左匹配原则。按照您的例子试了一下,发现有的时候,and两端的表达式交换顺序,依然可以使用到复合索引。查了查,是因为mysql的优化器会自己优化。这是怎么回事呢?网上的解释没有看懂。。。。。。
    2019-09-23
    1
  • man1s
    走主键索引,优化器认为5000数据+回表5000次性能消耗要大于全表扫描
    force index

    作者回复: 👍

    2019-09-16
  • Demon.Lee
    Key Point:

    如果觉得这些规则太多,难以记住,我们就要养成经常检查 SQL 执行计划的习惯。
    2019-09-16
  • godtrue
    老师,请问存储引擎具体判断使用什么索引的原则是啥?大体的原则肯定是怎么快怎么走?不过也存在一定的误判,请问老师清楚误判的原因和具体都有那些场景嘛?

    作者回复: mysql查询优化是基于检索成本考虑,而不是基于时间成本考虑,假设我们读取的是随机行的数据,在磁盘中存储是无序的,有可能在扫描少数行的情况下,所需时间更长,这种情况下会出现误选择索引。

    通常在一些in操作时,在数据量比较小的情况下,会使用我们建立的索引,当数据量超过一定量时,会改走主键索引。我们一般是通过慢日志来排查这些问题,一旦发现不是走的我们想要的索引,可以使用force index来强制走期望的索引。

    2019-09-14
  • DY
    老师,你好。 select * from order_detail where id in (select id from order_detail where order_id between 5000 and 10000); 这种优化方式我试了试, 没起到什么优化作用。问了下DBA,说都回表了, 先查询主键ID也回表, 感觉和自己理解的不一样,但是又没法反驳。看了下执行计划这条sql也确实回表了

    作者回复: 我们这里讨论的是索引失效

    2019-09-10
    1
  • 疯狂咸鱼
    老师,你这个表里的order id和id不是一起递增的么?如果orderid也是递增的,那情况又是怎呢

    作者回复: 如果与id索引的排序是一致的,会走索引,可以动手实践一下

    2019-09-07
  • 我行我素
    老师,想请问下InnoDB引擎下使用HASH索引也可以啊,但是文中的图InnoDB索引Hash是no

    作者回复: 官网给出的是不支持自创建hash数据结构的索引,但是它是自适应的,也就是我们不能人为的干预使用hash索引。具体的可以参考官网:https://dev.mysql.com/doc/refman/8.0/en/create-index.html

    2019-08-13
  • 胡峣
    好几个都同学都提到 select * from order_detail where id in (select id from order_detail where order_id between 5000 and 10000),至少mysql 57里这样写
    2019-08-13
    1
  • 密码123456
    我这一直用的都是oracle,看到mysql就想,快快跳过。后来发现,和数据库关系不大,很多都是通用的。

    作者回复: 多熟悉一门数据库也是好的,知己知彼

    2019-08-12
  • Loubobooo
    我的想法是,可以利用子查询去减少回表操作,既然有主键自增id,便可以利用聚簇索引的优势来强制走索引。代码方法如下:select * from order_detail where id in (select id from order_detail where order_id between 5000 and 10000)

    作者回复: 思路是对的,这种方式可以解决。

    2019-08-11
  • 新世界
    查询用order_id索引,然后进行回表查询

    作者回复: 对的

    2019-08-10
  • 许童童
    请问该查询选择的索引是什么?有什么方式可以强制使用我们期望的索引呢?
    会直接查询主键索引进行全表扫描,因为数据库优化器在判断SQL语句执行使用哪个索引时会计算代价,如果使用主订单 id 索引回表太多,代价太大,还不如用主键索引进行全表扫描。
    我觉得SQL改为以下方式可以使用主订单 id 索引,并提高查询效率。
    select * from order_detail where id in (select id from order_detail where order_id between 5000 and 10000)

    作者回复: 正解!

    2019-08-10
    1
收起评论
23
返回
顶部