MySQL执行计划extra中的using index 和 using where using index 的区别

本文出处:http://www.cnblogs.com/wy123/p/7366486.html
(保留来源并不是原著的权利,我的烂作品也远未达到,只是为了链接原文,因为以后可能出现的一些错误,没有其他方法可以更正或补充。)[en](the reservation of the source is not the right of the original work, and my bad work is far from being reached, just to link to the original text, because there is no other way to correct or supplement some possible errors later.)

mysql执行计划中的extra列中表明了执行计划的每一步中的实现细节,其中包含了与索引相关的一些细节信息
其中,与索引相关的使用索引在不同情况下会出现使用索引、使用位置使用索引、使用索引条件等。[en]Among them, the using index related to the index will appear Using index, Using where Using index, Using index condition and so on under different circumstances.
那么使用index和使用where;使用index有什么不同呢?我在网上搜索了很多文章,但老实说,我不太懂,所以我自己试了一下。[en]So what’s the difference between Using index and Using where;Using index? I searched a lot of articles on the Internet, but to be honest, I didn’t understand much, so I tried it myself.

本文仅测试使用INDEX和使用WHERE从最简单的单个表中使用索引,以及使用INDEX条件进行简单测试的时机。[en]This article only tests using index and using where using index from the simplest single table, as well as the timing of simply testing using index condition.
执行计划的生成涉及到多种环境,如表结构、表数据、索引结构、统计信息等。[en]The generation of execution plan is related to a variety of environments, such as table structure, table data, index structure, statistical information and so on.

测试环境建设[en]Test environment building

构建测试表和测试数据,类似于Order表和Order List,暂时使用Order表进行测试[en]Build the test table and test data, similar to the order table and order list, use the order table for testing for the time being

测试台结构[en]Test table structure

create table test_order
(
    id int auto_increment primary key,
    user_id int,
    order_id int,
    order_status tinyint,
    create_date datetime
);create table test_orderdetail
(
    id int auto_increment primary key,
    order_id int,
    product_name varchar(100),
    cnt int,
    create_date datetime
);
create index idx_userid_order_id_createdate on test_order(user_id,order_id,create_date);
create index idx_orderid_productname on test_orderdetail(order_id,product_name);

测试数据(50W)[en]Test data (50W)

CREATE DEFINER=root@% PROCEDURE test_insertdata(IN loopcount INT)
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
    declare v_uuid  varchar(50);
    while loopcount>0 do
        set v_uuid = uuid();
        insert into test_order (user_id,order_id,order_status,create_date) values (rand()*1000,id,rand()*10,DATE_ADD(NOW(), INTERVAL - RAND()*20000 HOUR));
        insert into test_orderdetail(order_id,product_name,cnt,create_date) values (rand()*100000,v_uuid,rand()*10,DATE_ADD(NOW(), INTERVAL - RAND()*20000 HOUR));
        set loopcount = loopcount -1;
    end while;
END

Using index VS Using where Using index

首先,在Order表上,下面是一个多列综合索引[en]First of all, on the order Table, here is a multi-column composite index
create index idx_userid_order_id_createdate on test_order(user_id,order_id,create_date);

Using index

1,查询的列被索引覆盖,并且where筛选条件是索引的是前导列,Extra中为Using index

MySQL执行计划extra中的using index 和 using where using index 的区别

Using where Using index

1,查询的列被索引覆盖,并且where筛选条件是索引列之一但是不是索引的不是前导列,Extra中为Using where; Using index,
这意味着不可能直接通过索引查找来查询符合条件的数据。[en]It means that it is impossible to query qualified data directly through index lookup.

MySQL执行计划extra中的using index 和 using where using index 的区别

2,查询的列被索引覆盖,并且where筛选条件是索引列前导列的一个范围,同样意味着无法直接通过索引查找查询到符合条件的数据

MySQL执行计划extra中的using index 和 using where using index 的区别

NULL( 既没有Using index,也没有Using where Using index,也没有using where )

1,查询的列未被索引覆盖,并且where筛选条件是索引的前导列,
这意味着使用了索引,但有些字段未被索引覆盖,因此必须通过“回表”来实现。它不是纯粹使用索引,或者根本不使用索引。在Extra中,它为空(无信息)。[en]It means that the index is used, but some fields are not covered by the index, so it must be realized by “returning to the table”. It is not purely using the index or not using the index at all. In Extra, it is NULL (no information).

MySQL执行计划extra中的using index 和 using where using index 的区别

Using where

1,查询的列未被索引覆盖,where筛选条件非索引的前导列,Extra中为Using where

MySQL执行计划extra中的using index 和 using where using index 的区别

2,查询的列未被索引覆盖,where筛选条件非索引列,Extra中为Using where

MySQL执行计划extra中的using index 和 using where using index 的区别

using where 意味着通过索引或者表扫描的方式进程where条件的过滤,
另一方面,没有可用的索引查找,当然,这里应该考虑索引扫描+表返回和表扫描的成本。[en]On the other hand, there is no index lookup available, of course, the cost of index scan + table return and table scan should be considered here.
这里的类型是全部,这表明MySQL认为全表扫描是一种相对较低的成本。[en]The type here is all all, indicating that MySQL believes that full table scanning is a relatively low cost.

Using index condition

1,查询的列不全在索引中,where条件中是一个前导列的范围

2,查询列不完全被索引覆盖,查询条件完全可以使用到索引(进行索引查找)

MySQL执行计划extra中的using index 和 using where using index 的区别

参考:MySQL功能分析索引条件下推(ICP)[en]Reference: MySQL feature Analysis Index Condition Pushdown (ICP)
using index conditoin 意味着查询列的某一部分无法直接使用索引
在上面的案例中1[en]In the above case1
如果禁用ICP(set optimizer_switch=’index_condition_pushdown=off’),
执行计划使用WHERE,这意味着全表扫描。如果启用了icp,则执行计划使用的是索引条件,即在过滤过程中进行过滤。[en]The execution plan is using where, which means full table scanning. If ICP is enabled, the execution plan is using index Condition, which means filtering in the filtering process.
在上面的案例中1[en]In the above case1
第二个查询条件不能直接使用索引,这意味着需要进行查找和筛选。[en]The second query condition cannot use the index directly, implying a process of finding and filtering.
这两种情况的共同点是它们不能直接使用索引。[en]What the two case have in common is that they cannot use the index directly.

结论:[en]Conclusion:
1,Extra中的为Using index的情况
where筛选列是索引的前导列 &&查询列被索引覆盖 && where筛选条件是一个基于索引前导列的查询,意味着通过索引超找就能直接找到符合条件的数据,并且无须回表

2,Extra中的为空的情况
QUERY列不会被索引覆盖&&WHERE FILTER列是索引的前导列,这意味着通过索引覆盖搜索和“返回到表”可以找到索引没有覆盖的字段。[en]The query column is not overwritten by the index & & the where filter column is the leading column of the index, which means that the fields that are not covered by the index are found by index oversearch and by “back to the table”.

3,Extra中的为Using where Using index:
使用WHERE USING INDEX的出现意味着SQL语句是通过索引扫描(或表扫描)执行的,即使是索引前导列的索引范围查找也有少量的范围扫描操作,无论是由前一个非索引前导列还是由非索引列查询引起的。[en]The appearance of Using where Using index means that the sql statement is executed through an index scan (or table scan), and even the index range lookup of the index leading column has a little range scanning action, whether caused by the previous non-index leading column or the non-indexed column query.

未解决的问题:[en]Unresolved issues:

查询1[en]Query 1

MySQL执行计划extra中的using index 和 using where using index 的区别

问题2[en]Query 2

MySQL执行计划extra中的using index 和 using where using index 的区别

查询3(逻辑上相当于查询1+查询2),执行计划发生了很大变化。[en]Query 3 (logically equivalent to query 1 + query 2), the execution plan has changed a lot.

MySQL执行计划extra中的using index 和 using where using index 的区别

摘要:[en]Summary:

MySQL执行计划中的Extra中信息非常多,不仅仅包括Using index,Using where Using index,Using index condition,Using where,尤其是在多表连接的时候,这一点在相对MSSQL来说,不够直观或者结构化。
MSSQL中是通过区分索引查找(index seek),索引扫描(index scan),表扫描(table scan)来实现具体的查询的,这图形化的执行计划在不同的场景下是非常直观的,要想完全弄懂MySQL的这个执行计划,可能要更多地在实践中摸索。

Original: https://www.cnblogs.com/jpfss/p/12182723.html
Author: 星朝
Title: MySQL执行计划extra中的using index 和 using where using index 的区别

原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/6162/

转载文章受原作者版权保护。转载请注明原作者出处!

(0)

大家都在看

免费咨询
免费咨询
扫码关注
扫码关注
联系站长

站长Johngo!

大数据和算法重度研究者!

持续产出大数据、算法、LeetCode干货,以及业界好资源!

2022012703491714

微信来撩,免费咨询:xiaozhu_tec

分享本页
返回顶部