毕设做桌面端还是网站,做热图的在线网站,百度搜索引擎算法,中国建筑未来走势预测1 执行计划介绍
执行计划#xff08;Execution Plan#xff09;是数据库查询优化的重要工具#xff0c;用于展示数据库如何执行 SQL 查询的详细过程。它包含了查询操作的步骤、各个步骤的执行顺序、使用的索引、访问的表、连接方式、预计的成本等信息
可以显示SQL语句最终…1 执行计划介绍
执行计划Execution Plan是数据库查询优化的重要工具用于展示数据库如何执行 SQL 查询的详细过程。它包含了查询操作的步骤、各个步骤的执行顺序、使用的索引、访问的表、连接方式、预计的成本等信息
可以显示SQL语句最终执行方案可以用于判断是否使用的最优索引方案
2 执行计划的字段说明
命令desc 或 explain select ...
1.在之前对 t100w 表的 k2 列做了辅助索引的基础上查看执行计划
mysql explain select * from t100w where k2noUV;
------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | t100w | NULL | ref | idx_k2 | idx_k2 | 17 | const | 1019 | 100.00 | Using index condition |
------------------------------------------------------------------------------------------------------------------------id表示查询的唯一标识符通常在执行多表查询时表示执行的顺序。id 值越小的步骤通常在查询中先执行。id1 表示这是唯一的查询步骤即简单查询没有子查询 select_type表示查询的类型通常有 SIMPLE简单查询没有子查询PRIMARY主查询用于联合查询或子查询中的主查询UNION表示该查询是 UNION 中的一个查询SUBQUERY表示该查询是一个子查询DERIVED表示派生表例如在 FROM 子句中使用子查询 table查询中正在访问的表名 partitions该列表示查询扫描的表分区。分区用于将表数据分成多个物理段以提高性能。为 NULL 即没有分区 type表示访问表的方式表明 MySQL 在执行查询时选择的连接类型。常见的访问类型有 ALL全表扫描尽量不要出现index使用索引扫描整个索引表尽量不要出现range范围扫描通过索引获取数据。尽量不要出现ref基于索引查找匹配的行此处为 ref表示查询通过索引进行行匹配而不是扫描整个表eq_ref 对每个来自外表的行查找最多一行匹配的行xiaoQ表示多表连接查询时被驱动表的连接条件是主键或者唯一键时获取的数据信息过程 const 常量查询查询会通过索引或常量值直接获取数据速度最快xiaoQ表示主键或者唯一键等值常量查询精准定义索引的查询条件 NULL表示没有使用任何表 possible_keys列出可能用于执行查询的所有索引。如果查询中涉及多个表或多个条件这一列会显示每个表可能使用的索引。idx_k2 表示查询条件中 k2 列上有索引 key实际使用的索引。与 possible_keys 列不同key 列显示的是查询实际使用的索引 key_len表示使用的索引的长度 这个长度指的是 MySQL 用于扫描索引的字节数。较长的索引长度表示 MySQL 需要扫描更多的数据。这里的 17 表示17个字节的数据xiaoQ解释表示索引覆盖长度是否合理的使用了联合索引信息。一般数值越大表示使用的索引越多 ref表示与索引匹配的列或常量。这里 const 表示查询的 WHERE 子句中使用了常量值k2Vwlm。这意味着查询只需要匹配一个常量值而不是从表中查找多个行 rowsMySQL 预计要扫描的行数尽量越少越好 filtered查询在该步骤中过滤掉的行的百分比。该值用于表示 MySQL 在该步骤应用过滤条件后的行数比例 Extra提供额外的信息表示索引应用过程是否进行了优化设置。常见的值有 Using index condition表示查询优化器决定使用索引中的条件进行筛选而不是回表查找数据索引下推Using where表示查询有 WHERE 子句并且在执行过程中应用了该过滤条件Using filesort表示查询需要使用文件排序不是通过索引Using temporary表示查询需要使用临时表
字段解释说明ID表示语句执行顺序单表查询就是一行执行计划多表查询就会多行执行计划select_type表示语句查询类型sipmle表示简单(普通)查询SUBQUERY子查询table表示语句针对的表单表查询就是一张表多表查询显示多张表type表示索引应用类型通过类型可以判断有没有用索引其次判断有没有更好的使用索引possible_keys表示可能使用到的索引信息因为列信息是可以属于多个索引的key表示可能使用到的索引信息key_len***表示索引覆盖长度对联合索引是否都应用做判断rows表示查询扫描的数据行数尽量越少越好尽量和结果集行数匹配从而使查询代价降低fltered表示查询的匹配度Extra***表示索引应用过程是否进行了优化设置
type应用类型详细说明
序号类型解释说明示例01ALL - ok表示全表扫描方式没用利用索引扫描类型02index表示全索引扫描方式需要将索引树全部遍历才能获取查询的信息主键index全表扫描03range表示范围索引方式按照索引的区域范围扫描数据获取查询的数据信息04ref表示辅助索引等值常量查询精准定义辅助索引的查询条件where namexx05eq_ref表示多表连接查询时被驱动表的连接条件是主键或者唯一键时获取的数据信息过程06const/system表示主键或者唯一键等值常量查询精准定义索引的查询条件where id 1
3 单表查询执行计划分析
1.有了辅助索引还是出现全表扫描情况以下根据情况讨论
使用模糊匹配
mysql desc select * from t100w where k2 like %ort;
-------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | t100w | NULL | ALL | NULL | NULL | NULL | NULL | 997335 | 11.11 | Using where |
-------------------------------------------------------------------------------------------------------------使用排除
mysql desc select * from t100w where k2 not in (kisf,abcd);
-------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | t100w | NULL | ALL | k2 | NULL | NULL | NULL | 997335 | 100.00 | Using where |
-------------------------------------------------------------------------------------------------------------使用数值范围查询
mysql desc select * from t100w where num 5000 and num 50000;
-------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | t100w | NULL | ALL | NULL | NULL | NULL | NULL | 997335 | 11.11 | Using where |
-------------------------------------------------------------------------------------------------------------2.在原有k2列为辅助索引的情况下以k1k2再增加一条联合索引
mysql alter table t100w add index idx_k1_k2(k1,k2);mysql show index from t100w;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| t100w | 1 | k2 | 1 | k2 | A | 1369 | NULL | NULL | YES | BTREE | | | YES | NULL |
| t100w | 1 | idx_k1_k2 | 1 | k1 | A | 1071 | NULL | NULL | YES | BTREE | | | YES | NULL |
| t100w | 1 | idx_k1_k2 | 2 | k2 | A | 162611 | NULL | NULL | YES | BTREE | | | YES | NULL |
------------------------------------------------------------------------------------------------------------------------------------------------------------------可以发现 select k1,k2 from t100w where k1nD and k2VWAB;在 EXPLAIN 输出中出现了 Using where; Using index意味着索引帮助进行了过滤select k1, k2 的列已经包含在联合索引中查询结果不需要回表是索引完全覆盖。
而第二个为索引不完全覆盖
mysql desc select k1,k2 from t100w where k1nD and k2VWAB;
------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
------------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | t100w | NULL | ref | k2,idx_k1_k2 | idx_k1_k2 | 26 | const,const | 8 | 100.00 | Using where; Using index |
------------------------------------------------------------------------------------------------------------------------------------mysql desc select * from t100w where k1nD and k2VWAB;
---------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | t100w | NULL | ref | k2,idx_k1_k2 | idx_k1_k2 | 26 | const,const | 8 | 100.00 | Using index condition |
---------------------------------------------------------------------------------------------------------------------------------3.全索引扫描方式typeindex需要遍历整个索引树不推荐
mysql desc select k2 from t100w;
--------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
--------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | t100w | NULL | index | NULL | k2 | 17 | NULL | 997335 | 100.00 | Using index |
--------------------------------------------------------------------------------------------------------------4.范围查询情况也不推荐typerange
随着筛选条件的增加范围也会随之扩大
mysql desc select * from t100w where k2 in (IJ56,bc67);
----------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
----------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | t100w | NULL | range | k2 | k2 | 17 | NULL | 1070 | 100.00 | Using index condition |
----------------------------------------------------------------------------------------------------------------------mysql desc select * from t100w where k2 in (wzy,wenzy,wzy666,文,IJ56,ghLM);
----------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
----------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | t100w | NULL | range | k2 | k2 | 17 | NULL | 1526 | 100.00 | Using index condition |
----------------------------------------------------------------------------------------------------------------------5.增加一条主键索引
mysql alter table t100w add primary key idx_id(id);mysql desc t100w;
-----------------------------------------------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-----------------------------------------------------------------------------------------------
| id | int | NO | PRI | NULL | |
| num | int | YES | | NULL | |
| k1 | char(2) | YES | MUL | NULL | |
| k2 | char(4) | YES | MUL | NULL | |
| dt | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
-----------------------------------------------------------------------------------------------再次查看新的 typeconst
mysql desc select * from t100w where id100;
----------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
----------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | t100w | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
----------------------------------------------------------------------------------------------------------4 多表查询执行计划分析
4.1 多表环境准备
1.查看表结构
mysql desc city;
-----------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-----------------------------------------------------------
| ID | int | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | MUL | | |
| District | char(20) | NO | | | |
| Population | int | NO | | 0 | |
-----------------------------------------------------------
5 rows in set (0.00 sec)mysql desc country;
----------------------------------------------------------------------------------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
----------------------------------------------------------------------------------------------------------------------------------
| Code | char(3) | NO | PRI | | |
| Name | char(52) | NO | | | |
| Continent | enum(Asia,Europe,North America,Africa,Oceania,Antarctica,South America) | NO | | Asia | |
| Region | char(26) | NO | | | |
| SurfaceArea | float(10,2) | NO | | 0.00 | |
| IndepYear | smallint | YES | | NULL | |
| Population | int | NO | | 0 | |
| LifeExpectancy | float(3,1) | YES | | NULL | |
| GNP | float(10,2) | YES | | NULL | |
| GNPOld | float(10,2) | YES | | NULL | |
| LocalName | char(45) | NO | | | |
| GovernmentForm | char(45) | NO | | | |
| HeadOfState | char(60) | YES | | NULL | |
| Capital | int | YES | | NULL | |
| Code2 | char(2) | NO | | | |
----------------------------------------------------------------------------------------------------------------------------------2.查看2张表的索引信息
mysql show index from city;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
| city | 0 | PRIMARY | 1 | ID | A | 4188 | NULL | NULL | | BTREE | | | YES | NULL |
| city | 1 | CountryCode | 1 | CountryCode | A | 232 | NULL | NULL | | BTREE | | | YES | NULL |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------mysql show index from country;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| country | 0 | PRIMARY | 1 | Code | A | 239 | NULL | NULL | | BTREE | | | YES | NULL |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------1.对world库的 city表 和 国家表 进行内连接查询
此处city表是驱动表country表是被驱动表。内连接查询时country表的数据量比city表少这也是导致country表成为被驱动表的原因join city.countrycode 中 countrycode 不是主键
select city.name, country.name, city.population
from city
join country on city.countrycode country.code
limit 3;mysql desc select city.name,country.name,city.population from city join country on city.countrycodecountry.code;
----------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
----------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | country | NULL | ALL | PRIMARY | NULL | NULL | NULL | 239 | 100.00 | NULL |
| 1 | SIMPLE | city | NULL | ref | CountryCode | CountryCode | 3 | world.country.Code | 18 | 100.00 | NULL |
----------------------------------------------------------------------------------------------------------------------------2.尝试改为 country join city发现驱动表还是country表
mysql desc select city.name,country.name,city.population from country join city on city.countrycodecountry.code;
----------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
----------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | country | NULL | ALL | PRIMARY | NULL | NULL | NULL | 239 | 100.00 | NULL |
| 1 | SIMPLE | city | NULL | ref | CountryCode | CountryCode | 3 | world.country.Code | 18 | 100.00 | NULL |
----------------------------------------------------------------------------------------------------------------------------4.2 没有where条件
1.改为以 city为右表的右连接city表是驱动表country表是被驱动表
连接类型RIGHT JOIN 表明是右连接意味着会先扫描右表 city然后根据连接条件在左表 country 中寻找匹配项驱动关系 驱动表 city右表显示在执行计划的上方被驱动表 country左表显示在执行计划的下方 当没有where条件 左连接查询时join关键字前面的表是驱动表join关键字后面的表是被驱动表。简称左前驱后被右连接查询时join关键字前面的表是被驱动表join关键字后面的表是驱动表。简称右前被后驱
mysql desc select city.name,country.name,city.population from country right join city on city.countrycodecountry.code;
------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | city | NULL | ALL | NULL | NULL | NULL | NULL | 4188 | 100.00 | NULL |
| 1 | SIMPLE | country | NULL | eq_ref | PRIMARY | PRIMARY | 3 | world.city.CountryCode | 1 | 100.00 | NULL |
------------------------------------------------------------------------------------------------------------------------------左连接
mysql desc select city.name,country.name,city.population from city left join country on city.countrycodecountry.code;
------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | city | NULL | ALL | NULL | NULL | NULL | NULL | 4188 | 100.00 | NULL |
| 1 | SIMPLE | country | NULL | eq_ref | PRIMARY | PRIMARY | 3 | world.city.CountryCode | 1 | 100.00 | NULL |
------------------------------------------------------------------------------------------------------------------------------4.3 带有where条件
where条件时带where条件的表是驱动表否则是被驱动表
1.下面是一个带有where的左连接。city作为主表和左表
desc select city.name,country.name,city.population
fromcity left join country on city.countrycodecountry.code
wherecity.countrycodeCHN;mysql desc select city.name,country.name,city.population from city left join country on city.countrycodecountry.code where city.countrycodeCHN;
----------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
----------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | city | NULL | ref | CountryCode | CountryCode | 3 | const | 363 | 100.00 | NULL |
| 1 | SIMPLE | country | NULL | const | PRIMARY | PRIMARY | 3 | const | 1 | 100.00 | NULL |
----------------------------------------------------------------------------------------------------------------mysql desc select city.name,country.name,city.population from city join country on city.countrycodecountry.code where city.population50000;
------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
------------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | city | NULL | ALL | CountryCode | NULL | NULL | NULL | 4188 | 33.33 | Using where |
| 1 | SIMPLE | country | NULL | eq_ref | PRIMARY | PRIMARY | 3 | world.city.CountryCode | 1 | 100.00 | NULL |
------------------------------------------------------------------------------------------------------------------------------------5 索引覆盖长度的计算
字段数据类型字符集计算结果namechar(10)utf8mb4最大预留长度4*1040 10utf8最大预留长度3*1030varcher(10)utf8mb4最大预留长度4*1040 2字节 42 (1-2字节存储字符长度信息)utf8最大预留长度3*1030 2字节 32 (1-2字节存储字符长度信息)tinyintN/A最大预留长度1大约3位数 2的8次方256intN/A最大预留长度4大约10位数 2的32次方4294967296bigintN/A最大预留长度8大约20位数) 2的64次方18446744073709551616not nullN/A在没有设置not null时在以上情况计算结果再1
1.查看t100w表为例
CREATE TABLE t100w (id int NOT NULL,num int DEFAULT NULL,k1 char(2) DEFAULT NULL,k2 char(4) DEFAULT NULL,dt timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY (id),KEY k2 (k2),KEY idx_k1_k2 (k1,k2)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci;mysql desc t100w;
-----------------------------------------------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-----------------------------------------------------------------------------------------------
| id | int | NO | PRI | NULL | |
| num | int | YES | | NULL | |
| k1 | char(2) | YES | MUL | NULL | |
| k2 | char(4) | YES | MUL | NULL | |
| dt | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
-----------------------------------------------------------------------------------------------key_len 4*char(4)1 4*4 17
mysql desc select * from t100w where k2wenzy;
----------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
----------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | t100w | NULL | ref | k2 | k2 | 17 | const | 1 | 100.00 | Using index condition |
----------------------------------------------------------------------------------------------------------------------# 1.创建联合索引
alter table t100w add index idx(k1,k2,num);# 2.计算key_len长度
k1: 4*2 为空 1 -- 9
k2: 4*4 为空 1 -- 17
num: 4 为空 1 -- 5
-----
mysql desc t100w;
-------------------------------
| Field | Type | Null | Key | D
-------------------------------
| id | int | YES | | N
| num | int | YES | | N
| k1 | char(2) | YES | MUL | N
| k2 | char(4) | YES | | N
| dt | timestamp | NO | | C
-------------------------------# 3.验证desc select * from t100w where num913759 and k1ej and k2EFfg;
----------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
----------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | t100w | NULL | ref | idx | idx | 31 | const,const,const | 1 | 100.00 | Using index condition |
----------------------------------------------------------------------------------------------------------------------------------6 执行计划拓展
6.1 extra出现排序情况
1.extra最后一列出现 Using filesort表示对查询结果排序比较考验CPU计算
desc select * from city where countrycodeCHN order by population;
-----------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | city | NULL | ref | CountryCode | CountryCode | 12 | const | 363 | 100.00 | Using index condition |
-----------------------------------------------------------------------------------------------------------------------------2.可以对 countrycode和 population 增加一条联合索引因为简历索引的过程中就相当于做好了排序
alter table city add index idx_code_pop(CountryCode,Population);mysql desc select * from city where countrycodeCHN order by population;
-------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | city | NULL | ref | CountryCode,idx_code_pop | idx_code_pop | 3 | const | 363 | 100.00 | NULL |
-------------------------------------------------------------------------------------------------------------------------1️⃣.以 t100w 表为例对num列增加索引
alter table t100w add index idx_num(num);2️⃣.以 num 列查找全部信息时全表扫描了把要查的结果列缩小到 num,id又成了索引查找。当增加一列 k1 时又成了全表扫描
由于查询返回了多个列MySQL 优化器认为执行全表扫描可能会更加高效因为在执行索引查找时仍需要访问其他列的数据可能会导致额外的随机 IO
mysql desc select * from t100w where num9999 and num999999;
-------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | t100w | NULL | ALL | idx_num | NULL | NULL | NULL | 997632 | 50.00 | Using where |
-------------------------------------------------------------------------------------------------------------mysql desc select num,id from t100w where num9999 and num999999;
------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | t100w | NULL | range | idx_num | idx_num | 5 | NULL | 498816 | 100.00 | Using where; Using index |
------------------------------------------------------------------------------------------------------------------------------mysql desc select num,id,k1 from t100w where num9999 and num999999;
-------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | t100w | NULL | ALL | idx_num | NULL | NULL | NULL | 997632 | 50.00 | Using where |
-------------------------------------------------------------------------------------------------------------6.2 索引下推
1️⃣.索引下推
索引下推是 MySQL 在查询优化中的一种技术将 WHERE 子句中的一些条件 “下推” 到索引扫描的阶段。这样MySQL 就可以在索引层面上过滤不符合条件的数据而不是在检索出所有记录后再应用这些条件从而提高查询效率
2️⃣.例如第二列在innodb 扫描阶段就被过滤了因此不会加载到内存中 3️⃣.通过 select optimizer_switch; 可以查看到 index_condition_pushdownon; 4️⃣.开关该功能的命令 set global optimizer_switchindex_condition_pushdownoff;
mysql show index from t100w;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| t100w | 0 | PRIMARY | 1 | id | A | 997632 | NULL | NULL | | BTREE | | | YES | NULL |
| t100w | 1 | k2 | 1 | k2 | A | 1369 | NULL | NULL | YES | BTREE | | | YES | NULL |
| t100w | 1 | idx_k1_k2 | 1 | k1 | A | 1071 | NULL | NULL | YES | BTREE | | | YES | NULL |
| t100w | 1 | idx_k1_k2 | 2 | k2 | A | 162611 | NULL | NULL | YES | BTREE | | | YES | NULL |
------------------------------------------------------------------------------------------------------------------------------------------------------------------mysql desc select * from t100w where k12m and k2 like %tu%;
---------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | t100w | NULL | ref | idx_k1_k2 | idx_k1_k2 | 9 | const | 529 | 11.11 | Using index condition |
---------------------------------------------------------------------------------------------------------------------------5️⃣.关闭该功能后重新打开会话发现索引下推没有了
mysql desc select * from t100w where k12m and k2 like %tu%;
-----------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | t100w | NULL | ref | idx_k1_k2 | idx_k1_k2 | 9 | const | 529 | 11.11 | Using where |
-----------------------------------------------------------------------------------------------------------------6️⃣.关闭下推后可以执行下面的查询进行压测对比关闭前后的性能影响
mysql desc select k1,count(*) from t100w group by k1 order by k1;
-------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | t100w | NULL | index | idx_k1_k2 | idx_k1_k2 | 26 | NULL | 997632 | 100.00 | Using index |
-------------------------------------------------------------------------------------------------------------------
