当前位置： 首页 > news >正文

mysql的执行计划分析和索引下推以及索引长度计算

news 2025/7/7 18:47:16

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应用类型详细说明：

序号类型解释说明示例
01ALL - ok表示全表扫描方式，没用利用索引扫描类型；
02index表示全索引扫描方式，需要将索引树全部遍历，才能获取查询的信息（主键index=全表扫描）
03range表示范围索引方式，按照索引的区域范围扫描数据，获取查询的数据信息；
04ref表示辅助索引等值（常量）查询，精准定义辅助索引的查询条件where name=xx
05eq_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列为辅助索引的情况下，以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 索引覆盖长度的计算

字段数据类型字符集计算结果
namechar(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字节存储字符长度信息)
tinyintN/A最大预留长度=1（大约3位数） 2的8次方=256
intN/A最大预留长度=4（大约10位数） 2的32次方=4294967296
bigintN/A最大预留长度=8（大约20位数) 2的64次方=18446744073709551616
not 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`)
) 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 执行计划介绍 执行计划&#xff08;Execution Plan&#xff09;是数据库查询优化的重要工具&#xff0c;用于展示数据库如何执行 SQL 查询的详细过程。它包含了查询操作的步骤、各个步骤的执行顺序、使用的索引、访问的表、连接方式、预计的成本等信息 可以显示SQL语句最终…...

编程日记 2024/12/14 4:39:22

C#中的string操作详解-截取、分割、连接、替换等

在C#中&#xff0c;string 类提供了许多用于操作字符串的方法&#xff0c;包括截取、分隔和连接等。以下是一些常用字符串操作的介绍和实例&#xff1a; 1. 截取字符串 Substring 方法 用于从字符串中截取子字符串。 语法&#xff1a; //从startIndex开始截取&#xff0c;…...

编程日记 2024/12/14 4:38:19

Redis Cluster 分片机制

Redis 集群是 Redis 提供的一种分布式实现&#xff0c;用于水平扩展数据存储能力。通过 Redis 集群&#xff0c;可以将数据分片存储在多个 Redis 节点上&#xff0c;同时提供高可用性和故障转移功能。 分片&#xff08;Sharding&#xff09;&#xff1a; Redis 集群将数据划分…...

编程日记 2024/12/14 4:34:15

论文结论：GPTs and Hallucination Why do large language models hallucinate

GPTs and Hallucination 当一个主题有普遍共识&#xff0c;并且有大量语言可用于训练模型时&#xff0c;大模型的输出可以反映出该共识观点在没有足够关于主题的语言示例【晦涩/数据有限】&#xff0c;或者主题有争议&#xff0c;或是对主题没有明确共识的情况下&#xff0c;就…...

编程日记 2024/12/14 4:24:03

CSS在线格式化 - 加菲工具

CSS在线格式化 打开网站 加菲工具 选择“CSS在线格式化” 或者直接访问 https://www.orcc.online/tools/css 输入CSS代码&#xff0c;点击左上角的“格式化”按钮 得到格式化后的结果...

编程日记 2024/12/14 4:21:00

组件通信（父传子，子传父，跨组件通信）

组件&#xff08;component&#xff09;是vue.js最核心的功能&#xff0c;是可扩展的HTML元素。每个页面都是一个HTML。以.vue结尾的文件&#xff0c;都可以叫组件。 场景&#xff1a;将一个完整的项目&#xff0c;拆分成不同的功能模块。 注意&#xff1a;组件首字母要大写。 …...

编程日记 2024/12/14 4:18:58

JWT 令牌：原理、应用与安全考量

深入理解 JWT 令牌&#xff1a;原理、应用与安全考量 文章目录 深入理解 JWT 令牌&#xff1a;原理、应用与安全考量一、引言二、JWT 令牌与传统方式的区别&#xff08;一&#xff09;传统身份验证方式的特点与局限&#xff08;二&#xff09;JWT 令牌的优势 三、JWT 令牌的字段…...

编程日记 2024/12/14 4:15:53

YOLOv5+pyqt5+摄像头在特定条件下进行目标检测并采集原始数据

项目介绍 项目地址 GitHub - biabu0/Yolov5_D435i: 通过YOLOV5与pyqt5实现一个使用D435i深度摄像头采集特定需求与场景下的深度数据的小程序 通过YOLOV5对指定的区域进行检测&#xff0c;当检测到目标进入特定区域时&#xff0c;开始保存数据&#xff0c;摄像头采用D435i深度…...

编程日记 2024/12/14 4:03:40

12.6深度学习_模型优化和迁移_整体流程梳理

七、整体流程梳理 1. 引入使用的包 用到什么包&#xff0c;临时引入就可以&#xff0c;不用太担心。 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…...

编程日记 2024/12/14 4:01:38

TCP 和 UDP 可以使用同一个端口吗

TCP 和 UDP 可以使用同一个端口吗 简单来说 可以使用同一个端口&#xff0c;关键在于它们属于不同的传输层协议&#xff0c;在内核中是两个完全独立的软件模块&#xff0c;各自维护独立的端口空间&#xff0c;虽然端口号相同&#xff0c;但通过协议类型可以确定是哪种协议。 …...

编程日记 2024/12/14 4:00:36

信而泰网络测试仪校准解决方案

一、影响仪表精度的因素 网络测试仪是用于对数据网络及其相关设备性能参数进行测试的仪表&#xff0c;可以模拟网络终端产生流量&#xff0c;进行网络性能测试&#xff0c;对网络状态进行实时监测&#xff0c;分析和统计。数字计量对于精准数据的网络测试仪来说是一剂强心针&a…...

编程日记 2024/12/14 3:58:34

Java 实现给pdf文件指定位置盖章功能

Java 实现给pdf文件指定位置盖章功能 开发中遇到一个需求, 需要给用户上传的的pdf文件, 指定位置上盖公章的功能, 经过调研和对比, 最终确定实现思路. 这里是使用pdf文件中的关键字进行章子的定位, 之所以这样考虑是因为如果直接写死坐标的话, 可能会出现因pdf大小, 缩放, 盖章…...

编程日记 2024/12/14 3:56:32

机器学习支持向量机（SVM）算法

一、引言 在当今数据驱动的时代&#xff0c;机器学习算法在各个领域发挥着至关重要的作用。支持向量机&#xff08;Support Vector Machine&#xff0c;SVM&#xff09;作为一种强大的监督学习算法&#xff0c;以其在分类和回归任务中的卓越性能而备受瞩目。SVM 具有良好的泛化…...

编程日记 2024/12/14 3:53:30

解决 MySQL 启动失败与大小写问题，重置数据库

技术文档&#xff1a;解决 MySQL 启动失败与大小写问题&#xff0c;重置数据库 1. 问题背景 在使用 MySQL 时&#xff0c;可能遇到以下问题&#xff1a; MySQL 启动失败&#xff0c;日志显示 “permission denied” 或 “Can’t create directory” 错误。MySQL 在修改配置文…...

编程日记 2024/12/14 3:49:26

计算生成报价单小程序系统开发方案

计算生成报价单小程序报价系统&#xff0c;是根据商品品牌、类型、型号、规格、芯数、特性、颜色、分类进行选择不同的参数进行生成报价单&#xff0c;要求报价单支持生成图片、pdf、excel表格。 计算生成报价单小程序系统的主要功能模块有&#xff1a; 1、在线生成报价单&…...

编程日记 2024/12/14 3:47:19

若依集成Uflo2工作流引擎

文章目录 1. 创建子模块并添加依赖1.1 新建子模块 ruoyi-uflo1.2 引入 Uflo2 相关依赖 2. 配置相关 config2.1 配置 ServletConfig2.2 配置 UfloConfig2.3 配置 TestEnvironmentProvider 3. 引入Uflo配置文件4. 启动并访问 Uflo2 是由 BSTEK 自主研发的一款基于 Java 的轻量级工…...

编程日记 2024/12/14 3:41:11

STM32模拟I2C通讯的驱动程序

目录 STM32模拟I2C通讯的驱动程序 开发环境 引脚连接 驱动程序 STM32模拟I2C通讯的驱动程序 开发环境 立创天空星开发板、主控芯片为STM32F407VxT6 引脚连接 使用stm32的PB9引脚模拟I2C时钟线SCL、PB8引脚模拟I2C数据线SDA 驱动程序 i2c.h文件如下&#xff1a;#ifndef…...

编程日记 2024/12/14 3:39:10

Unity简单操作及使用教程

Unity 是一款强大的跨平台游戏引擎&#xff0c;它不仅支持 2D 和 3D 游戏的开发&#xff0c;还可以用于虚拟现实 (VR)、增强现实 (AR)、动画、建筑可视化等多个领域。Unity 提供了完整的开发环境&#xff0c;具有丰富的功能、工具和资源&#xff0c;可以帮助开发者快速实现创意…...

编程日记 2024/12/14 3:38:08

网络安全法-监测预警与应急处置

第五章 监测预警与应急处置 第五十一条 国家建立网络安全监测预警和信息通报制度。国家网信部门应当统筹协调有关部门加强网络安全信息收集、分析和通报工作&#xff0c;按照规定统一发布网络安全监测预警信息。 第五十二条 负责关键信息基础设施安全保护工作的部门&#xf…...

编程日记 2024/12/14 3:34:02

qt 设置系统缩放为150%，导致的文字和界面的问题

1 当我们设置好布局后&#xff0c;在100%的设置里面都是正常的&#xff0c;但是当我们修改缩放为150%后&#xff0c;字体图标&#xff0c;界面大小就出现问题了&#xff0c;这就需要我们设置一些参数。 QCoreApplication::setAttribute(Qt::AA_EnableHighDpiScaling);QCoreAppl…...

编程日记 2024/12/14 3:29:58

手游刚开服就被攻击怎么办？如何防御DDoS？

开服初期是手游最脆弱的阶段&#xff0c;极易成为DDoS攻击的目标。一旦遭遇攻击&#xff0c;可能导致服务器瘫痪、玩家流失&#xff0c;甚至造成巨大经济损失。本文为开发者提供一套简洁有效的应急与防御方案&#xff0c;帮助快速应对并构建长期防护体系。 一、遭遇攻击的紧急应…...

编程新知 2025/7/7 12:11:39

【OSG学习笔记】Day 18: 碰撞检测与物理交互

物理引擎&#xff08;Physics Engine&#xff09; 物理引擎 是一种通过计算机模拟物理规律&#xff08;如力学、碰撞、重力、流体动力学等&#xff09;的软件工具或库。 它的核心目标是在虚拟环境中逼真地模拟物体的运动和交互&#xff0c;广泛应用于 游戏开发、动画制作、虚…...

编程新知 2025/7/7 3:36:15

FFmpeg 低延迟同屏方案

引言 在实时互动需求激增的当下&#xff0c;无论是在线教育中的师生同屏演示、远程办公的屏幕共享协作&#xff0c;还是游戏直播的画面实时传输&#xff0c;低延迟同屏已成为保障用户体验的核心指标。FFmpeg 作为一款功能强大的多媒体框架&#xff0c;凭借其灵活的编解码、数据…...

编程新知 2025/6/21 15:38:27

《通信之道——从微积分到 5G》读书总结

第1章 绪 论 1.1 这是一本什么样的书 通信技术&#xff0c;说到底就是数学。 那些最基础、最本质的部分。 1.2 什么是通信 通信 发送方 接收方 承载信息的信号 解调出其中承载的信息 信息在发送方那里被加工成信号&#xff08;调制&#xff09; 把信息从信号中抽取出来&am…...

编程新知 2025/7/7 0:36:53

反射获取方法和属性

Java反射获取方法 在Java中&#xff0c;反射&#xff08;Reflection&#xff09;是一种强大的机制&#xff0c;允许程序在运行时访问和操作类的内部属性和方法。通过反射&#xff0c;可以动态地创建对象、调用方法、改变属性值&#xff0c;这在很多Java框架中如Spring和Hiberna…...

编程新知 2025/7/5 23:14:36

微信小程序云开发平台MySQL的连接方式

注&#xff1a;微信小程序云开发平台指的是腾讯云开发 先给结论&#xff1a;微信小程序云开发平台的MySQL&#xff0c;无法通过获取数据库连接信息的方式进行连接&#xff0c;连接只能通过云开发的SDK连接&#xff0c;具体要参考官方文档&#xff1a; 为什么&#xff1f; 因为…...

编程新知 2025/7/5 3:22:34

优选算法第十二讲：队列 + 宽搜 优先级队列

优选算法第十二讲&#xff1a;队列 宽搜 && 优先级队列 1.N叉树的层序遍历2.二叉树的锯齿型层序遍历3.二叉树最大宽度4.在每个树行中找最大值5.优先级队列 -- 最后一块石头的重量6.数据流中的第K大元素7.前K个高频单词8.数据流的中位数 1.N叉树的层序遍历 2.二叉树的锯…...

编程新知 2025/7/6 22:41:36

今日学习：Spring线程池|并发修改异常|链路丢失|登录续期|VIP过期策略|数值类缓存

文章目录 优雅版线程池ThreadPoolTaskExecutor和ThreadPoolTaskExecutor的装饰器并发修改异常并发修改异常简介实现机制设计原因及意义 使用线程池造成的链路丢失问题线程池导致的链路丢失问题发生原因 常见解决方法更好的解决方法设计精妙之处 登录续期登录续期常见实现方式特…...

编程新知 2025/6/22 19:33:11

sipsak：SIP瑞士军刀！全参数详细教程！Kali Linux教程！

简介 sipsak 是一个面向会话初始协议 (SIP) 应用程序开发人员和管理员的小型命令行工具。它可以用于对 SIP 应用程序和设备进行一些简单的测试。 sipsak 是一款 SIP 压力和诊断实用程序。它通过 sip-uri 向服务器发送 SIP 请求&#xff0c;并检查收到的响应。它以以下模式之一…...

编程新知 2025/7/6 19:16:54

HarmonyOS运动开发：如何用mpchart绘制运动配速图表

##鸿蒙核心技术##运动开发##Sensor Service Kit&#xff08;传感器服务&#xff09;# 前言 在运动类应用中&#xff0c;运动数据的可视化是提升用户体验的重要环节。通过直观的图表展示运动过程中的关键数据&#xff0c;如配速、距离、卡路里消耗等&#xff0c;用户可以更清晰…...

编程新知 2025/7/7 10:57:47