阅读 4826

探索MySQL是否走索引(一)——范围查询一定走索引吗?

首先,准备4个版本的数据库,5.5/5.6/5.7/8.0

然后,每个库中有一模一样的表,数据量一样,建立的索引一样,InnoDB引擎。

先看一看,各个版本中,索引统计信息(Cardinality代表该列在总数据中有多少个不同的值,该值为估算值)

  1. MySQL5.5

  2. MySQL5.6

  3. MySQL5.7

  4. MySQL8.0


    对比可知,统计的数据量方面,基本都差不多。

本文探索>,<,>=,<=走索引的情况,首先回答标题的问题,>,<,>=,<=不一定走索引,因为优化器会计算,符合条件的值的总和,与总数据量的比值,比值<=0.30,才会走索引。

官网原话如下:
Each table index is queried, and the best index is used unless the optimizer believes that it is more efficient to use a table scan. At one time, a scan was used based on whether the best index spanned more than 30% of the table, but a fixed percentage no longer determines the choice between using an index or a scan. The optimizer now is more complex and bases its estimate on additional factors such as table size, number of rows, and I/O block size.

查询每个表索引,并使用最佳索引,除非优化程序认为使用表扫描更有效。
一次使用扫描是基于**最佳索引**是否**跨越了表的30%以上**,但是固定百分比不再决定使用索引还是扫描。现在,优化器更加复杂,其估计基于**其他因素**,**例如表大小,行数和I / O块大小**。

注意:这段话有多个重点

  1. 假如where条件中,涉及到了多个索引,MySQL会选择一个最佳索引。最佳索引就是选择性最高的索引,因为它可以过滤掉很多的无用数据行。
  2. 0.30比例值。以前可能是>0.30就不走索引,但文档最低版本是5.6已无从考证。现在不仅取决于比例值,也取决于其他因素。所以,用 > 或 >= 或 < 或 <=其中测试以下即可。

接下来,准备开干。

  1. MySQL5.5版本中,通过不断测试,找到了8这个关键数字。

    explain select user_name,user_age from user1 where user_age <= 8;
    explain select user_id,user_name,user_age from user1 where user_age < 8;
    复制代码


    第二张图中Extra虽然没有明确说明Using index ,但是type显示为range范围查询,扫描行数rows为35042行,但第一张图中type:ALL,rows:210463与文章一开始的全表统计信息中,主键id数据量一致,主键id的数据量=全表数据多少行,可知这是全表扫描。
    差距只是在于是否包含8这个值,因此统计一下,<8 和 =8的数据量(特别说明,8这个值是试出来的,不可能一下找到该值)


    计算一下比值:user_age < 8 比值为:35042/210463 = 0.1664(走索引)
    user_age <= 8比值为:(35042 + 2221)/210463 = 0.1770(不走索引)
    以上可知:比例值<=0.1664可以走索引,比例值>=0.1770优化器认为全表扫描更快,所以就不走索引。
    测试大于的情况(89也是不断测试出来的)

    explain select user_name,user_age from user1 where user_age >= 89;
    explain select user_id,user_name,user_age from user1 where user_age > 89;
    复制代码


    一样,统计一下 >89 和 =89 的数据量


    user_age > 89的比例值:35894 / 210463 = 0.1705(走索引)
    user_age >= 89的比例值:(35894 + 2088)/210463 = 0.1804(不走索引)
    结合user_age < 8的比例值,和user_age > 89的比例值,可知,对于5.5版本,user_age这个索引列,比例值在 0.1705~0.1770之间。

    特殊情况1,可以使用FORCE INDEX强制走索引,不管比例值是多少

    explain select user_name,user_age from user1 force index(idx_user_age) where user_age < 50;
    explain select user_name,user_age from user1 force index(idx_user_age) where user_age > 20;
    复制代码


    特殊情况2:只返回索引列时,不管比例值是多少,都会走索引

    explain select user_age from user1 where user_age < 80;
    复制代码

  2. MySQL5.6只测试一下<的情况(通过测试关键值也是8)

    explain select user_name,user_age from user1 where user_age <= 8;
    explain select user_id,user_name,user_age from user1 where user_age < 8;
    复制代码


    同样计算一下比例值:
    user_age < 8 :35042 / 208534 = 0.1680(走索引)
    user_age <= 8:(35042+2221)/208534 = 0.1786(不走索引)
    由此可大致推断:5.6版本下,该比例值在 0.1680 ~ 0.1786之间。
    特别注意:第二张图片中,出现了using index condition,这是MySQL5.6做出的优化,ICP(index condition pushdown)可对比5.5版本,Extra中显示的是Using where,代表数据最终是在MySQL服务层过滤数据的,5.6版本显示的是Using index condition,代表数据在存储引擎层就过滤了无用的数据,ICP可以减少存储引擎必须访问基表的次数以及MySQL服务器必须访问存储引擎的次数。
    官网地址如下:dev.mysql.com/doc/refman/…
    同样,如果使用FORCE INDEX,则肯定会走索引,只返回索引列,不管比例值,照样走索引,此处不再贴图

  3. MySQL5.7只测试一下<的情况(测试到20是个关键值)

    explain select user_name,user_age from user1 where user_age <= 20;
    explain select user_id,user_name,user_age from user1 where user_age < 20;
    复制代码


    计算比例值如下:
    user_age < 20:87966 / 208303 = 0.422 (走索引)
    user_age <= 20:(87966 + 2093)/208303 = 0.4323(不走索引)
    同样,如果使用FORCE INDEX,则肯定会走索引,只返回索引列,不管比例值,照样走索引,此处不再贴图
    特别注意:此处出现MRR,本处不讨论,后边会有详细讨论。

  4. MySQL8.0只测试一下<的情况(测试到关键值为12)

    explain select user_name,user_age from user1 where user_age <= 12;
    explain select user_id,user_name,user_age from user1 where user_age < 12;
    复制代码


    计算一下比例值:
    user_age < 12:54798 / 208611 = 0.2626(走索引)
    user_age <= 12:(54798 + 2051)/208611 = 0.2725(不走索引)
    由此可推测,比例值在 0.2626 ~ 0.2725之间
    同样,如果使用FORCE INDEX,则肯定会走索引,只返回索引列,不管比例值,照样走索引,此处不再贴图

总结:

  1. 并不是给一个列建立了索引,对这个列进行范围查询的时候,就会走索引,他是有一个比例值的。比例值会随着版本、服务器、IO、数据量、数据重复情况而不同。也就是说,同一个版本,同一个库表,此时和下一时刻,比例值就可能不一样。测试中途遇到过该问题。
  2. MySQL5.6版本的时候,进行了优化,ICP 和 MRR,极大提升性能。后边也会有例子。
  3. FORCE INDEX 的作用,特殊情况下,可以只返回索引列。
文章分类
后端
文章标签