“如何判断一条 SQL 是否走索引？”的版本间的差异
跳到导航
跳到搜索
Jihongchang（讨论 | 贡献） |
Jihongchang（讨论 | 贡献） |
||
| 第1行： | 第1行： | ||
https://www.bilibili.com/video/BV1ya41157oE?spm_id_from=333.1007.top_right_bar_window_history.content.click&vd_source=66ca2a18f4b76100f6e90bdb5950f9f6 | https://www.bilibili.com/video/BV1ya41157oE?spm_id_from=333.1007.top_right_bar_window_history.content.click&vd_source=66ca2a18f4b76100f6e90bdb5950f9f6 | ||
| − | + | ===表结构和数据=== | |
| − | 表结构：id, a, b, c, d 5个字段<syntaxhighlight lang="powershell"> | + | 表结构：id, a, b, c, d 5个字段 |
| + | |||
| + | 索引：id，idx_a_b_c<syntaxhighlight lang="powershell"> | ||
mysql> select * from t_demo; | mysql> select * from t_demo; | ||
+------+------+------+------+------+ | +------+------+------+------+------+ | ||
| 第14行： | 第16行： | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| − | + | ||
| − | + | === 等值匹配原则 === | |
| + | <syntaxhighlight lang="sql"> | ||
select * from t_demo | select * from t_demo | ||
where a = 1 | where a = 1 | ||
| 第31行： | 第34行： | ||
| − | + | === 最左侧匹配 === | |
| + | <syntaxhighlight lang="sql"> | ||
select * from t_demo where a = 1 and b = 1; /*可以命中索引*/ | select * from t_demo where a = 1 and b = 1; /*可以命中索引*/ | ||
select * from t_demo where b = 1; /*不能命中索引*/ | select * from t_demo where b = 1; /*不能命中索引*/ | ||
| 第52行： | 第56行： | ||
</syntaxhighlight>因为索引在B+树中是按照字段顺序创建的，索引 idx_a_b_c 顺序是 a、b、c，所以条件 "where a = 1 and b = 1" 可以命中索引，条件 "where b = 1" 不能命中索引 | </syntaxhighlight>因为索引在B+树中是按照字段顺序创建的，索引 idx_a_b_c 顺序是 a、b、c，所以条件 "where a = 1 and b = 1" 可以命中索引，条件 "where b = 1" 不能命中索引 | ||
| − | + | ||
| + | === 最左前缀匹配规则 === | ||
| + | <syntaxhighlight lang="sql"> | ||
select * from t_demo where a like '1%'; /*可以命中索引*/ | select * from t_demo where a like '1%'; /*可以命中索引*/ | ||
select * from t_demo where a like '%1%'; /*不能命中索引*/ | select * from t_demo where a like '%1%'; /*不能命中索引*/ | ||
2022年8月25日 (四) 23:56的版本
表结构和数据
表结构：id, a, b, c, d 5个字段
索引：id，idx_a_b_c
mysql> select * from t_demo;
+------+------+------+------+------+
| id | a | b | c | d |
+------+------+------+------+------+
| 1 | 1 | 1 | 1 | NULL |
| 5 | 5 | 5 | 5 | NULL |
| 10 | 10 | 10 | 10 | NULL |
+------+------+------+------+------+
3 rows in set (0.00 sec)
等值匹配原则
select * from t_demo
where a = 1
and b = 1
and c = 1;
mysql> explain select * from t_demo where a = 1 and b = 1 and c = 1;
+----+-------------+--------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------+
| 1 | SIMPLE | t_demo | NULL | ref | idx_a_b_c | idx_a_b_c | 15 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
a、b、c 同时存在的情况下，只要是等值判断，即便调换 where 后面的先后顺序，也仍然会命中索引。
最左侧匹配
select * from t_demo where a = 1 and b = 1; /*可以命中索引*/
select * from t_demo where b = 1; /*不能命中索引*/
mysql> explain select * from t_demo where a = 1 and b = 1;
+----+-------------+--------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | t_demo | NULL | ref | idx_a_b_c | idx_a_b_c | 10 | const,const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from t_demo where b = 1;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t_demo | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
因为索引在B+树中是按照字段顺序创建的，索引 idx_a_b_c 顺序是 a、b、c，所以条件 "where a = 1 and b = 1" 可以命中索引，条件 "where b = 1" 不能命中索引
最左前缀匹配规则
select * from t_demo where a like '1%'; /*可以命中索引*/
select * from t_demo where a like '%1%'; /*不能命中索引*/
mysql> explain select * from t_demo where a like '1%';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t_demo | NULL | ALL | idx_a_b_c | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from t_demo where a like '%1%';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t_demo | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)