MySQL查询性能优化七种方式索引潜水

目录

前言：

有读者可能会一脸懵？

啥是索引潜水？

你给起的名字的吗？有没有索引蛙泳？

这个名字还真不是我起的，今天要讲的知识点就叫索引潜水（Index dive） 。

先要从一件怪事说起：

我先造点数据复现一下问题，创建一张用户表：

CREATE TABLE `user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(100) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT 0 COMMENT '年龄',
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

通过一批用户年龄，查询该年龄的用户信息，并查看一下SQL执行计划：

explain select * from user
where age in (1,2,3,4,5,6,7,8,9);

where条件中有9个参数，重点关注一下执行计划中的预估扫描行数为279行。

到这里没什么问题，预估的非常准，实际就是279行。

但是，问题来了，当我们在where条件中，再加一个参数，变成了10个参数，预估扫描行数本应该增加，结果却大大减少了。

explain select * from user
where age in (1,2,3,4,5,6,7,8,9,10);

一下子减少到了30行，可是实际行数是多少呢？

实际是310行，预估扫描行数是30行，真是错到姥姥家了。

MySQL咋回事啊，到底还能不能预估？

不能预估的话，换其他人！

大家肯定也是满脸疑惑，直到我去官网上看到了一个词语，索引潜水（Index dive） 。

跟这个词语相关的，还有一个配置参数 eq_range_index_dive_limit。

MySQL5.7.3之前的版本，这个值默认是10，之后的版本，这个值默认是200。

可以使用命令查看一下这个值的大小：

show variables like '%eq_range_index_dive_limit%';

当然，我们也可以手动修改这个值的大小：

set eq_range_index_dive_limit=200;

这个 eq_range_index_dive_limit 配置的作用就是：

当where语句in条件中参数个数小于这个值的时候，MySQL就采用索引潜水（Index dive） 的方式预估扫描行数，非常准确。

当where语句in条件中参数个数大于等于这个值的时候，MySQL就采用另一种方式索引统计（Index statistics） 预估扫描行数，误差较大。

MySQL为什么要这么做呢？

都用索引潜水（Index dive） 的方式预估扫描行数，不好吗？

其实这是基于成本的考虑，索引潜水估算成本较高，适合小数据量。索引统计估算成本较低，适合大数据量。

一般情况下，我们的where语句的in条件的参数不会太多，适合使用索引潜水预估扫描行数。

建议还在使用MySQL5.7.3之前版本的同学们，手动修改一下索引潜水的配置参数，改成合适的数值。

如果你们项目中in条件最多有500个参数，就把配置参数改成501。

这样MySQL预估扫描行数更准确，可以选择更合适的索引。

到此这篇关于MySQL查询性能优化七种方式索引潜水的文章就介绍到这了,更多相关MySQL查询优化内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • Mysql数据库性能优化之子查询

    Mysql数据库性能优化之子查询

    记得在做项目的时候, 听到过一句话, 尽量不要使用子查询, 那么这一篇就来看一下, 这句话是否是正确的. 那在这之前, 需要介绍一些概念性东西和mysql对语句的大致处理. 当Mysql Server的连接线程接收到Client发送过来的SQL请求后, 会经过一系列的分解Parse, 进行相应的分析, 然后Mysql会通过查询优化器模块, 根据该Sql所涉及到的数据表的相关统计信息进行计算分析. 然后在得出一个Mysql自认为最合理最优化的数据访问方式, 也就是我们常说的"执行计划",

  • mysql千万级数据分页查询性能优化

    mysql数据量大时使用limit分页,随着页码的增大,查询效率越低下. 实验 1.直接使用用limit start, count分页语句: select * from order limit start, count 当起始页较小时,查询没有性能问题,我们分别看下从10, 100, 1000, 10000开始分页的执行时间(每页取20条), 如下: select * from order limit 10, 20 0.016秒 select * from order limit 100, 20

  • 使用MySQL的Explain执行计划的方法(SQL性能调优)

    使用MySQL的Explain执行计划的方法(SQL性能调优)

    目录 前言 1. explain的使用 2. explain字段详解 id列 select_type列 table列 partitions列 type列 system const eq_ref ref ref_or_null index_merge range index ALL possible_keys列 key列 key_len列 ref列 rows列 filtered列 Extra列 Using where Using index Using filesort Using tempora

  • MySQL查询性能优化七种方式索引潜水

    MySQL查询性能优化七种方式索引潜水

    目录 前言: 有读者可能会一脸懵? 啥是索引潜水? 你给起的名字的吗?有没有索引蛙泳? 这个名字还真不是我起的,今天要讲的知识点就叫索引潜水(Index dive) . 先要从一件怪事说起: 我先造点数据复现一下问题,创建一张用户表: CREATE TABLE `user` (  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',  `name` varchar(100) NOT NULL DEFAULT '' COM

  • MySQL数据库查询性能优化策略

    优化查询 使用Explain语句分析查询语句 Explain 用来分析 SELECT 查询语句,开发人员可以通过分析 Explain 结果来优化查询语句. 通过对查询语句的分析,可以了解查询语句的执行情况,找出查询语句执行的瓶颈,从而优化查询语句. 使用索引查询 MySql中提高性能的一个最有效的方式就是对数据表设计合理的索引. 索引提供了高效访问数据的方法,并且加快查询速度. 如果查询时没有使用索引,那么查询语句将扫描表中所有的记录.在数据量大的时候,这样查询速度会很慢. 使用索引进行查询,查

  • mysql查询时offset过大影响性能的原因和优化详解

    mysql查询时offset过大影响性能的原因和优化详解

    前言 mysql查询使用select命令,配合limit,offset参数可以读取指定范围的记录.本文将介绍mysql查询时,offset过大影响性能的原因及优化方法. 准备测试数据表及数据 1.创建表 CREATE TABLE `member` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(10) NOT NULL COMMENT '姓名', `gender` tinyint(3) unsigned NOT NU

  • 大幅优化MySQL查询性能的奇技淫巧

    大幅优化MySQL查询性能的奇技淫巧

    回顾 MySQL / InnoDB 的改善历史.你能很容易发现.在MySQL 5.6稳定版本中从来没有在read-only 这么快的提速,它很容易搞懂,以及在read-only(RO)有着良好的扩张性.也很期待它在read+write(RW)上达到一个较高水平.(特别是在读取数据是数据库主要工作的时候) 然而.我们对于RO在 MySQL 5.6的表现也十分的高兴,在5.7这个版本中,主要工作集中在 read+write (RW)上, 因为在大数据的处理上还没能达到我们的期望.但是RW依赖RO下.

  • MySQL数据库查询性能优化的4个技巧干货

    MySQL数据库查询性能优化的4个技巧干货

    目录 前言 SQL的执行频率 慢查询日志 show profiles详情分析 explain执行计划 1.ID参数 2.select_type参数 3.type参数 前言 MySQL性能优化是一个老生常谈的问题,无论是在实际工作中还是面试中,都不可避免遇到相应的场景,下面博主就总结一些能够帮助大家解决这个问题的小技巧. SQL优化之前需要确认哪些SQL需要优化,这时就需要引起SQL性能分析工具,主要优化的是查询语句. SQL的执行频率 SQL性能优化一般是针对查询语句,所以在定位是否需要优化之前

  • 通过MySQL慢查询优化MySQL性能的方法讲解

    通过MySQL慢查询优化MySQL性能的方法讲解

    随着访问量的上升,MySQL数据库的压力就越大,几乎大部分使用MySQL架构的web应用在数据库上都会出现性能问题,通过mysql慢查询日志跟踪有问题的查询非常有用,可以分析出当前程序里有很耗费资源的sql语句. 慢查询日志我们可以通过my.cnf文件设置开启,下面先来看一下相关参数的意义 log-slow-queries <slow_query_log_file> 存放slow query日志的文件.你必须保证mysql server进程mysqld_safe进程用户对该文件有w权限. lo

  • MySQL查询性能优化索引下推

    MySQL查询性能优化索引下推

    目录 前言 1. 索引下推的作用 2. 案例实践 3. 索引下推配置 4. 索引下推原理剖析 5. 索引下推应用范围 前言 前面已经讲了MySQL的其他查询性能优化方式,没看过可以去了解一下: MySQL查询性能优化七种方式索引潜水 MySQL查询性能优化武器之链路追踪 今天要讲的是MySQL的另一种查询性能优化方式 — 索引下推(Index Condition Pushdown,简称ICP),是MySQL5.6版本增加的特性. 1. 索引下推的作用 主要作用有两个: 减少回表查询的次数 减少存

  • MySQL查询性能优化武器之链路追踪

    MySQL查询性能优化武器之链路追踪

    目录 前言 1. 查看optimizer trace配置 2. 开启optimizer trace 3. 线上问题复现 3. 使用optimizer trace 前言 MySQL优化器可以生成Explain执行计划,我们可以通过执行计划查看是否使用了索引,使用了哪种索引? 但是到底为什么会使用这个索引,我们却无从得知. 好在MySQL提供了一个好用的分析工具 — optimizer trace(优化器追踪),可以帮助我们查看优化器生成执行计划的整个过程,以及做出的各种决策,包括访问表的方法.各种

  • Sql Server 查询性能优化之走出索引的误区分析

    Sql Server 查询性能优化之走出索引的误区分析

    据了解绝大多数开发人员对于索引的理解都是一知半解,局限于大多数日常工作没有机会.也什么没有必要去关心.了解索引,实在哪天某个查询太慢了找到查询条件建个索引就ok,哪天又有个查询慢了,再建立个索引就是,或者干脆把整个查询SQL直接发给DBA,让DBA直接帮忙优化了,所以造成的状况就是开发人员对于索引的理解.认识很局限,以下就把我个人对于索引的理解及浅薄认识和大家分享下,希望能解除一些大家的疑惑,一起走出索引的误区 误区1.在表上建立了索引,在查询时用到了索引的列,索引就一定会生效 首先明确下这样的

  • Mysql数据库性能优化一

    Mysql数据库性能优化一

    今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显.关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我们程序员需要去关注的事情.当我们去设计数据库表结构,对操作数据库时(尤其是查表时的SQL语句),我们都需要注意数据操作的性能.这里,我们不会讲过多的SQL语句的优化,而只是针对MySQL这一Web应用最多的数据库. mysql的性能优化无法一蹴而就,必须一步一步慢慢来,从各个方面进行优化,最终性能就会有大的提升. Mysql数据库的优化技术 对mysql优化是

  • MySQL数据库性能优化介绍

    MySQL数据库性能优化介绍

    目录 为什么做优化?? 从哪些方面入手?? 解决方案是什么???  要怎样选择??? SQL优化 总结 为什么做优化?? 因为数据量太多了,项目部署上线再到用户使用,每天数据增长几十万条,给服务器带来非常大的负担,互联网一直追求高性能,可是随着业务规模变大,用户数量变多,服务器的性能越来越差,因此我们不得不对数据库有更高要求. 从哪些方面入手?? 第一,是查询的速度,我们期望数据量到达TB级别仍然能够实现百万级别查询速度. 第二.是并发量,我们对它的要求能够同时处理几千甚至上万的并发访问,还要配

  • Oracle分页查询性能优化代码详解

    对于数据库中表的数据的 Web 显示,如果没有展示顺序的需要,而且因为满足条件的记录如此之多,就不得不对数据进行分页处理.常常用户并不是对所有数据都感兴趣的,或者大部分情况下,他们只看前几页. 通常有以下两种分页技术可供选择. Select * from ( Select rownum rn,t.* from table t) Where rn>&minnum and rn<=&maxnum 或者 Select * from ( Select rownum rn,t.* fro

随机推荐