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

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

news 文章来源：https://blog.csdn.net/qq_73797346/article/details/144387820 2025/4/29 1:28:27

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

Scala的正则表达式二

验证用户名是否合法 规则 1.长度在6-12之间 2.不能数字开头 3.只能包含数字&#xff0c;大小写字母&#xff0c;下划线def main(args: Array[String]): Unit {val name1 "1admin"//不合法&#xff0c;是数字开头val name2 "admin123"//合法val name3 &quo…...

编程日记 2024/12/14 3:28:57

软考系分：今日成绩已出

前言 今年报考了11月份的软考高级&#xff1a;系统分析师。 考试时间&#xff1a;11月9日。 总体感觉偏简单&#xff0c;但是知识点记得不牢&#xff0c;估计机会不大。 今日 12.11 &#xff0c;成绩已出&#xff0c;每科总分 75分&#xff0c;全部45分以上为通过。 成绩总…...

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

DevExpress WPF中文教程：Grid - 如何移动和调整列大小？（一）

DevExpress WPF拥有120个控件和库&#xff0c;将帮助您交付满足甚至超出企业需求的高性能业务应用程序。通过DevExpress WPF能创建有着强大互动功能的XAML基础应用程序&#xff0c;这些应用程序专注于当代客户的需求和构建未来新一代支持触摸的解决方案。 无论是Office办公软件…...

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

Docker 安装 sentinel

Docker 安装系列 1、拉取 [rootTseng ~]# docker pull bladex/sentinel-dashboard Using default tag: latest latest: Pulling from bladex/sentinel-dashboard 4abcf2066143: Pull complete 1ec1e81da383: Pull complete 56bccb36a894: Pull complete 7cc80011dc6f: Pull…...

编程日记 2024/12/14 3:21:43

PyCharm 2024.1 解锁版 (Python集成开发IDE)详细安装步骤

分享文件&#xff1a;PyCharm 2024.1 解锁版 (Python集成开发IDE) 链接&#xff1a;https://pan.xunlei.com/s/VOAa_CiVVvZnyQgLfpmCIOABA1 提取码&#xff1a;cx4h 安装步骤 1、下载解压后点击如下进行安装 2、选择安装路径 3、默认勾选将PyCharm创建桌面快捷方式 4、默认…...

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

SQL中的函数介绍

大多数SQL实现支持以下类型 文本函数&#xff1a;用于处理文本字符串&#xff08;如删除或填充值&#xff0c;转换值为大写或小写&#xff09;。数值函数&#xff1a;用于在数值数据上进行算术操作&#xff08;如返回绝对值&#xff0c;进行代数运算&#xff09;。日期和时间函…...

编程日记 2024/12/14 3:16:37

【工业机器视觉】基于深度学习的水表盘读数识别（2-数据采集与增强）

【工业机器视觉】基于深度学习的仪表盘识读&#xff08;1&#xff09;-CSDN博客 数据采集与增强 为了训练出适应多种表型和环境条件的模型&#xff0c;确保数据集的质量与多样性对于模型的成功至关重要。高质量的数据不仅需要准确无误、具有代表性&#xff0c;还需要涵盖尽可能…...

编程日记 2024/12/14 3:13:33

爬虫基础知识点

最近看了看爬虫相关知识点&#xff0c;做了记录&#xff0c;具体代码放到了仓库&#xff0c;本文仅学习使用&#xff0c;如有违规请联系博主删除。 这个流程图是我使用在线AI工具infography生成的&#xff0c;这个网站可以根据url或者文本等数据自动生成流程图&#xff0c;挺…...

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

高效利用资源：分布式有状态服务的高可靠性设计

在分布式系统设计中&#xff0c;实现有状态服务的高可靠性通常采用主备切换的方式。当主服务停止工作时&#xff0c;备服务接管任务&#xff0c;例如通过Keepalive实现VIP的切换以保证可用性。然而&#xff0c;这种方式存在资源浪费的问题&#xff0c;因为备服务始终处于空转状…...

编程日记 2024/12/14 3:04:20

aws(学习笔记第十六课) 使用负载均衡器(ELB)解耦webserver以及输出ELB的日志到S3

aws(学习笔记第十六课) 使用负载均衡器(ELB)以及输出ELB的日志到S3 学习内容&#xff1a; 使用负载均衡器(ELB)解耦web server输出ELB的日志到S3 1. 使用负载均衡器(ELB) 全体架构 使用ELB(Elastic Load Balancer)能够解耦外部internet访问和web server之间的耦合&#xff0c…...

编程日记 2024/12/14 3:00:16