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

server/2024/12/16 22:31:20/

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 |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-------------+

http://www.ppmy.cn/server/150737.html

相关文章

腾讯云海外服务器Window切换为linux系统（从Window DD 到 Linux）

腾讯云海外服务器Window切换为linux系统（从Window DD 到 Linux）

腾讯云提示&#xff1a;不支持重装为该镜像&#xff0c;非中国大陆地域不支持Linux系统和Windows系统之间互转 买了腾讯云的海外服务器&#xff0c;重装系统的时候发现无法切换&#xff0c;直接dd到linux系统&#xff0c;以下是全过程。记录一下。 主要是用到一个开源项目&…
阅读更多...
vue3 setup语法，子组件点击一个元素打印了这个元素的下标id，怎么传递给父组件，让父组件去使用

vue3 setup语法，子组件点击一个元素打印了这个元素的下标id，怎么传递给父组件，让父组件去使用

问&#xff1a; vue3 setup语法&#xff0c;子组件点击一个元素打印了这个元素的下标id&#xff0c;怎么传递给父组件&#xff0c;让父组件去使用 回答&#xff1a; 在 Vue 3 中&#xff0c;你可以使用 setup 语法糖和组合式 API 来实现子组件向父组件传递数据。具体来说&am…
阅读更多...
电脑win11家庭版升级专业版和企业版相关事项

电脑win11家庭版升级专业版和企业版相关事项

我的是零刻ser9&#xff0c;自带win11家庭版&#xff0c;但是我有远程操控需求&#xff0c;想用windows系统自带的远程连接功能&#xff0c;所以需要升级为专业版。然后在系统激活页面通过更改序列号方式&#xff0c;淘宝几块钱买了个序列号升级成功专业版了。但是&#xff0c;…
阅读更多...
Edge SCDN 边缘安全加速有什么用？

Edge SCDN 边缘安全加速有什么用？

Edge SCDN是最新推出的边缘安全加速服务&#xff0c;它是一种融合了安全防护和内容分发加速功能的网络服务技术&#xff0c;通过在网络边缘部署服务器节点&#xff0c;来优化内容的传输和用户的访问体验&#xff0c;同时保障网络安全。 抵御 DDoS 攻击&#xff1a; Edge SCDN …
阅读更多...
scala的多维数组

scala的多维数组

创建多维数组 创建多维数组可以使用Array.ofDim方法&#xff0c;该方法接受一个或多个整数参数&#xff0c;分别代表每个维度的大小。 // 创建一个3x3的二维数组&#xff0c;类型为Int val matrix Array.ofDim[Int](3, 3)// 创建一个3x3x3的三维数组&#xff0c;类型为Doubl…
阅读更多...
蓝桥杯刷题——day5

蓝桥杯刷题——day5

蓝桥杯刷题——day5 题目一题干解题思路一代码解题思路二代码 题目二题干解题思路代码 题目一 题干 给定n个整数 a1,a2,⋯ ,an&#xff0c;求它们两两相乘再相加的和&#xff0c;即&#xff1a; 示例一&#xff1a; 输入&#xff1a; 4 1 3 6 9 输出&#xff1a; 117 题目链…
阅读更多...
Edge SCDN的独特优势有哪些？

Edge SCDN的独特优势有哪些？

强大的边缘计算能力 Edge SCDN&#xff08;边缘安全加速&#xff09;是酷盾安全推出的边缘集分布式 DDoS 防护、CC 防护、WAF 防护、BOT 行为分析为一体的安全加速解决方案。通过边缘缓存技术&#xff0c;智能调度使用户就近获取所需内容&#xff0c;为用户提供稳定快速的访问…
阅读更多...
Mysql笔记

Mysql笔记

windows安装记录Windows中Mysql安装-CSDN博客 用到的库 通过网盘分享的文件&#xff1a;atguigudb.sql 链接: https://pan.baidu.com/s/1YfC20c2vK9odn-XRJJwUJw 提取码: utk7 --来自百度网盘超级会员v5的分享 Mysql4中表关联关系 1.1对1,比较少用,因为完全可以一张表,当有…
阅读更多...
最新文章

Copyright @ 2022~2023