mysql的执行计划分析和索引下推以及索引长度计算
1 执行计划介绍
执行计划（Execution Plan）是数据库查询优化的重要工具，用于展示数据库如何执行 SQL 查询的详细过程。它包含了查询操作的步骤、各个步骤的执行顺序、使用的索引、访问的表、连接方式、预计的成本等信息
可以显示SQL语句最终执行方案，可以用于判断是否使用的最优索引方案
2 执行计划的字段说明
- 命令：
desc 或 explain select ...
1.在之前对 t100w 表的 k2 列做了辅助索引的基础上查看执行计划
mysql> explain select * from t100w where k2='noUV';
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-----------------------+
| 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值越小的步骤通常在查询中先执行。id=1表示这是唯一的查询步骤，即简单查询（没有子查询 -
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子句中使用了常量值（k2='Vwlm'）。这意味着查询只需要匹配一个常量值，而不是从表中查找多个行 -
rows：MySQL 预计要扫描的行数，尽量越少越好 -
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应用类型详细说明：
| 序号 | 类型 | 解释说明 | 示例 |
|---|---|---|---|
| 01 | ALL - ok | 表示全表扫描方式，没用利用索引扫描类型； | |
| 02 | index | 表示全索引扫描方式，需要将索引树全部遍历，才能获取查询的信息（主键index=全表扫描） | |
| 03 | range | 表示范围索引方式，按照索引的区域范围扫描数据，获取查询的数据信息； | |
| 04 | ref | 表示辅助索引等值（常量）查询，精准定义辅助索引的查询条件 | where name=xx |
| 05 | eq_ref | 表示多表连接查询时，被驱动表的连接条件是主键或者唯一键时，获取的数据信息过程； | |
| 06 | const/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列为辅助索引的情况下，以k1，k2再增加一条联合索引
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 k1='nD' and k2='VWAB';，在 EXPLAIN 输出中出现了 Using where; Using index，意味着索引帮助进行了过滤，select k1, k2 的列已经包含在联合索引中，查询结果不需要回表，是索引完全覆盖。
而第二个为索引不完全覆盖
mysql> desc select k1,k2 from t100w where k1='nD' and k2='VWAB';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+--------------------------+
| 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 k1='nD' and k2='VWAB';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-----------------------+
| 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.全索引扫描方式，type=index，需要遍历整个索引树（不推荐）
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.范围查询情况也不推荐，type=range
随着筛选条件的增加，范围也会随之扩大
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 |
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
再次查看新的 type：const
mysql> desc select * from t100w where id=100;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 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.countrycode=country.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.countrycode=country.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.countrycode=country.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.countrycode=country.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.countrycode=country.code
wherecity.countrycode='CHN';mysql> desc select city.name,country.name,city.population from city left join country on city.countrycode=country.code where city.countrycode='CHN';
+----+-------------+---------+------------+-------+---------------+-------------+---------+-------+------+----------+-------+
| 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.countrycode=country.code where city.population>'50000';
+----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------------+
| 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 索引覆盖长度的计算
| 字段 | 数据类型 | 字符集 | 计算结果 |
|---|---|---|---|
| name | char(10) | utf8mb4 | 最大预留长度=4*10=40 10 |
| utf8 | 最大预留长度=3*10=30 | ||
| varcher(10) | utf8mb4 | 最大预留长度=4*10=40 + 2字节 =42 (1-2字节存储字符长度信息) | |
| utf8 | 最大预留长度=3*10=30 + 2字节 =32 (1-2字节存储字符长度信息) | ||
| tinyint | N/A | 最大预留长度=1（大约3位数） 2的8次方=256 | |
| int | N/A | 最大预留长度=4（大约10位数） 2的32次方=4294967296 | |
| bigint | N/A | 最大预留长度=8（大约20位数) 2的64次方=18446744073709551616 | |
| not null | N/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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_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 k2='wenzy';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
| 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 num=913759 and k1='ej' and k2='EFfg';
+----+-------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+-----------------------+
| 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 countrycode='CHN' 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 countrycode='CHN' 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 num>9999 and num<999999;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 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 num>9999 and num<999999;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
| 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 num>9999 and num<999999;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 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_pushdown=on;
4️⃣.开关该功能的命令 set global optimizer_switch='index_condition_pushdown=off';
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 k1='2m' 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 k1='2m' 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 |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-------------+
相关文章：
mysql的执行计划分析和索引下推以及索引长度计算
1 执行计划介绍 执行计划（Execution Plan）是数据库查询优化的重要工具，用于展示数据库如何执行 SQL 查询的详细过程。它包含了查询操作的步骤、各个步骤的执行顺序、使用的索引、访问的表、连接方式、预计的成本等信息 可以显示SQL语句最终…...
C#中的string操作详解-截取、分割、连接、替换等
在C#中，string 类提供了许多用于操作字符串的方法，包括截取、分隔和连接等。以下是一些常用字符串操作的介绍和实例： 1. 截取字符串 Substring 方法 用于从字符串中截取子字符串。 语法： //从startIndex开始截取，…...
Redis Cluster 分片机制
Redis 集群是 Redis 提供的一种分布式实现，用于水平扩展数据存储能力。通过 Redis 集群，可以将数据分片存储在多个 Redis 节点上，同时提供高可用性和故障转移功能。 分片（Sharding）： Redis 集群将数据划分…...
论文结论：GPTs and Hallucination Why do large language models hallucinate
GPTs and Hallucination 当一个主题有普遍共识，并且有大量语言可用于训练模型时，大模型的输出可以反映出该共识观点在没有足够关于主题的语言示例【晦涩/数据有限】，或者主题有争议，或是对主题没有明确共识的情况下，就…...
CSS在线格式化 - 加菲工具
CSS在线格式化 打开网站 加菲工具 选择“CSS在线格式化” 或者直接访问 https://www.orcc.online/tools/css 输入CSS代码，点击左上角的“格式化”按钮 得到格式化后的结果...
组件通信（父传子，子传父，跨组件通信）
组件（component）是vue.js最核心的功能，是可扩展的HTML元素。每个页面都是一个HTML。以.vue结尾的文件，都可以叫组件。 场景：将一个完整的项目，拆分成不同的功能模块。 注意：组件首字母要大写。 …...
JWT 令牌：原理、应用与安全考量
深入理解 JWT 令牌：原理、应用与安全考量 文章目录 深入理解 JWT 令牌：原理、应用与安全考量一、引言二、JWT 令牌与传统方式的区别（一）传统身份验证方式的特点与局限（二）JWT 令牌的优势 三、JWT 令牌的字段…...
YOLOv5+pyqt5+摄像头在特定条件下进行目标检测并采集原始数据
项目介绍 项目地址 GitHub - biabu0/Yolov5_D435i: 通过YOLOV5与pyqt5实现一个使用D435i深度摄像头采集特定需求与场景下的深度数据的小程序 通过YOLOV5对指定的区域进行检测，当检测到目标进入特定区域时，开始保存数据，摄像头采用D435i深度…...
12.6深度学习_模型优化和迁移_整体流程梳理
七、整体流程梳理 1. 引入使用的包 用到什么包，临时引入就可以，不用太担心。 import time import osimport numpy as np import pandas as pd import torch import torch.nn as nn import torch.optim as optim import torchvision import torchvisio…...
TCP 和 UDP 可以使用同一个端口吗
TCP 和 UDP 可以使用同一个端口吗 简单来说 可以使用同一个端口，关键在于它们属于不同的传输层协议，在内核中是两个完全独立的软件模块，各自维护独立的端口空间，虽然端口号相同，但通过协议类型可以确定是哪种协议。 …...
信而泰网络测试仪校准解决方案
一、影响仪表精度的因素 网络测试仪是用于对数据网络及其相关设备性能参数进行测试的仪表，可以模拟网络终端产生流量，进行网络性能测试，对网络状态进行实时监测，分析和统计。数字计量对于精准数据的网络测试仪来说是一剂强心针&a…...
Java 实现给pdf文件指定位置盖章功能
Java 实现给pdf文件指定位置盖章功能 开发中遇到一个需求, 需要给用户上传的的pdf文件, 指定位置上盖公章的功能, 经过调研和对比, 最终确定实现思路. 这里是使用pdf文件中的关键字进行章子的定位, 之所以这样考虑是因为如果直接写死坐标的话, 可能会出现因pdf大小, 缩放, 盖章…...
机器学习支持向量机（SVM）算法
一、引言 在当今数据驱动的时代，机器学习算法在各个领域发挥着至关重要的作用。支持向量机（Support Vector Machine，SVM）作为一种强大的监督学习算法，以其在分类和回归任务中的卓越性能而备受瞩目。SVM 具有良好的泛化…...
解决 MySQL 启动失败与大小写问题，重置数据库
技术文档：解决 MySQL 启动失败与大小写问题，重置数据库 1. 问题背景 在使用 MySQL 时，可能遇到以下问题： MySQL 启动失败，日志显示 “permission denied” 或 “Can’t create directory” 错误。MySQL 在修改配置文…...
计算生成报价单小程序系统开发方案
计算生成报价单小程序报价系统，是根据商品品牌、类型、型号、规格、芯数、特性、颜色、分类进行选择不同的参数进行生成报价单，要求报价单支持生成图片、pdf、excel表格。 计算生成报价单小程序系统的主要功能模块有： 1、在线生成报价单&…...
若依集成Uflo2工作流引擎
文章目录 1. 创建子模块并添加依赖1.1 新建子模块 ruoyi-uflo1.2 引入 Uflo2 相关依赖 2. 配置相关 config2.1 配置 ServletConfig2.2 配置 UfloConfig2.3 配置 TestEnvironmentProvider 3. 引入Uflo配置文件4. 启动并访问 Uflo2 是由 BSTEK 自主研发的一款基于 Java 的轻量级工…...
STM32模拟I2C通讯的驱动程序
目录 STM32模拟I2C通讯的驱动程序 开发环境 引脚连接 驱动程序 STM32模拟I2C通讯的驱动程序 开发环境 立创天空星开发板、主控芯片为STM32F407VxT6 引脚连接 使用stm32的PB9引脚模拟I2C时钟线SCL、PB8引脚模拟I2C数据线SDA 驱动程序 i2c.h文件如下：#ifndef…...
Unity简单操作及使用教程
Unity 是一款强大的跨平台游戏引擎，它不仅支持 2D 和 3D 游戏的开发，还可以用于虚拟现实 (VR)、增强现实 (AR)、动画、建筑可视化等多个领域。Unity 提供了完整的开发环境，具有丰富的功能、工具和资源，可以帮助开发者快速实现创意…...
网络安全法-监测预警与应急处置
第五章 监测预警与应急处置 第五十一条 国家建立网络安全监测预警和信息通报制度。国家网信部门应当统筹协调有关部门加强网络安全信息收集、分析和通报工作，按照规定统一发布网络安全监测预警信息。 第五十二条 负责关键信息基础设施安全保护工作的部门…...
qt 设置系统缩放为150%，导致的文字和界面的问题
1 当我们设置好布局后，在100%的设置里面都是正常的，但是当我们修改缩放为150%后，字体图标，界面大小就出现问题了，这就需要我们设置一些参数。 QCoreApplication::setAttribute(Qt::AA_EnableHighDpiScaling);QCoreAppl…...
eNSP-Cloud(实现本地电脑与eNSP内设备之间通信)
说明： 想象一下，你正在用eNSP搭建一个虚拟的网络世界，里面有虚拟的路由器、交换机、电脑（PC）等等。这些设备都在你的电脑里面“运行”，它们之间可以互相通信，就像一个封闭的小王国。 但是&#…...
利用ngx_stream_return_module构建简易 TCP/UDP 响应网关
一、模块概述 ngx_stream_return_module 提供了一个极简的指令： return <value>;在收到客户端连接后，立即将 <value> 写回并关闭连接。<value> 支持内嵌文本和内置变量（如 $time_iso8601、$remote_addr 等）&a…...
Zustand 状态管理库：极简而强大的解决方案
Zustand 是一个轻量级、快速和可扩展的状态管理库，特别适合 React 应用。它以简洁的 API 和高效的性能解决了 Redux 等状态管理方案中的繁琐问题。 核心优势对比 基本使用指南 1. 创建 Store // store.js import create from zustandconst useStore create((set)…...
基于ASP.NET+ SQL Server实现（Web）医院信息管理系统
医院信息管理系统 1. 课程设计内容 在 visual studio 2017 平台上，开发一个“医院信息管理系统”Web 程序。 2. 课程设计目的 综合运用 c#.net 知识，在 vs 2017 平台上，进行 ASP.NET 应用程序和简易网站的开发；初步熟悉开发一…...
在rocky linux 9.5上在线安装 docker
前面是指南，后面是日志 sudo dnf config-manager --add-repo https://download.docker.com/linux/centos/docker-ce.repo sudo dnf install docker-ce docker-ce-cli containerd.io -y docker version sudo systemctl start docker sudo systemctl status docker …...
【第二十一章 SDIO接口(SDIO)】
第二十一章 SDIO接口 目录 第二十一章 SDIO接口(SDIO) 1 SDIO 主要功能 2 SDIO 总线拓扑 3 SDIO 功能描述 3.1 SDIO 适配器 3.2 SDIOAHB 接口 4 卡功能描述 4.1 卡识别模式 4.2 卡复位 4.3 操作电压范围确认 4.4 卡识别过程 4.5 写数据块 4.6 读数据块 4.7 数据流…...
SpringBoot+uniapp 的 Champion 俱乐部微信小程序设计与实现，论文初版实现
摘要 本论文旨在设计并实现基于 SpringBoot 和 uniapp 的 Champion 俱乐部微信小程序，以满足俱乐部线上活动推广、会员管理、社交互动等需求。通过 SpringBoot 搭建后端服务，提供稳定高效的数据处理与业务逻辑支持；利用 uniapp 实现跨平台前…...
图表类系列各种样式PPT模版分享
图标图表系列PPT模版，柱状图PPT模版，线状图PPT模版，折线图PPT模版，饼状图PPT模版，雷达图PPT模版，树状图PPT模版 图表类系列各种样式PPT模版分享：图表系列PPT模板https://pan.quark.cn/s/20d40aa…...
处理vxe-table 表尾数据是单独一个接口，表格tableData数据更新后，需要点击两下，表尾才是正确的
修改bug思路： 分别把 tabledata 和 表尾相关数据 console.log() 发现 更新数据先后顺序不对 settimeout延迟查询表格接口 ——测试可行 升级↑：async await 等接口返回后再开始下一个接口查询 ________________________________________________________…...
【Redis】笔记｜第8节｜大厂高并发缓存架构实战与优化
缓存架构 代码结构 代码详情 功能点： 多级缓存，先查本地缓存，再查Redis，最后才查数据库热点数据重建逻辑使用分布式锁，二次查询更新缓存采用读写锁提升性能采用Redis的发布订阅机制通知所有实例更新本地缓存适用读多…...