mysql索引优化策略练手

最新推荐文章于 2025-09-09 07:39:35 发布

原创最新推荐文章于 2025-09-09 07:39:35 发布 · 81 阅读

0 ·

CC 4.0 BY-SA版权

数据库专栏收录该内容

231 篇文章

订阅专栏

本文通过创建和插入数据，详细分析了MySQL中联合索引的使用情况，包括不同查询条件和排序方式对索引的影响。验证了A到E五种查询场景，展示了索引在不同查询条件下的匹配程度和排序操作对性能的影响，揭示了索引使用策略的重要性。

建表

create table t2(
c1 char(1) not null default 0,
c2 char(1) not null default 0,
c3 char(1) not null default 0,
c4 char(1) not null default 0,
c5 char(1) not null default 0,
index c1234(c1,c2,c3,c4)
)engine innodb charset utf8;

插入数据

insert into t2 values (1,3,5,6,7),(2,3,9,8,3),(4,3,2,7,5);

验证下面几种情况

假设某个表有一个联合索引（c1,c2,c3,c4
A where c1=1 and c2=2 and c4>3 and c3=4 
B where c1=1 and c2=2 and c4=3 order by c3
C where c1=1 and c4=2 group by c3,c2
D where c1=1 and c5=2 order by c2,c3
E where c1=1 and c2=2 and c5=4 order by c2,c3

开始验证

验证A

explain select * from t2 where c1=‘a’ and c2=‘b’ and c4>‘a’ and c3=‘b’\G;

mysql> explain select * from t2 where c1='a' and c2='b' and c4>'a' and c3='b'\G; 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
         type: range
possible_keys: c1234
          key: c1234
      key_len: 12
          ref: NULL
         rows: 1
        Extra: Using index condition
1 row in set (0.00 sec)

ERROR: 
No query specified

结果 4个都用了

验证B

explain select * from t2 where c1=‘a’ and c2=‘b’ and c4=‘a’ order by c3\G;

mysql> explain select * from t2 where c1='a' and c2='b' and c4='a' order by c3\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
         type: ref
possible_keys: c1234
          key: c1234
      key_len: 6
          ref: const,const
         rows: 2
        Extra: Using index condition; Using where
1 row in set (0.00 sec)

ERROR: 
No query specified

结果 c1 c2 用了 c3用在排序上

验证C

explain select * from t2 where c1=‘a’ and c4=‘b’ group by c3,c2\G;

mysql> explain select * from t2 where c1='a' and c4='b' group by c3,c2\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
         type: ref
possible_keys: c1234
          key: c1234
      key_len: 3
          ref: const
         rows: 2
        Extra: Using index condition; Using where; Using temporary; Using filesort
1 row in set (0.00 sec)

ERROR: 
No query specified

结果用了1个c1

验证D

explain select * from t2 where c1=‘a’ and c5=‘b’ order by c2,c3\G;

mysql> explain select * from t2 where c1='a' and c5='b' order by c2,c3\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
         type: ref
possible_keys: c1234
          key: c1234
      key_len: 3
          ref: const
         rows: 2
        Extra: Using index condition; Using where
1 row in set (0.00 sec)

ERROR: 
No query specified

结果用了1个c1，排序用到了c2和c3

D变种

explain select * from t2 where c1=‘a’ and c5=‘b’ order by c3,c2\G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
         type: ref
possible_keys: c1234
          key: c1234
      key_len: 3
          ref: const
         rows: 2
        Extra: Using index condition; Using where; Using filesort
1 row in set (0.00 sec)

ERROR: 
No query specified

结果用了1个c1 c3和c2顺序使用错误导致索引失效产生filesort

验证E

explain select * from t2 where c1=‘a’ and c2=‘c’ and c5=‘b’ order by c2,c3\G;

mysql> explain select * from t2 where c1='a' and c2='c' and c5='b' order by c2,c3\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
         type: ref
possible_keys: c1234
          key: c1234
      key_len: 6
          ref: const,const
         rows: 1
        Extra: Using index condition; Using where
1 row in set (0.00 sec)

ERROR: 
No query specified

结果用了2个 c1 c2 排序用到c2和c3

E变种

explain select * from t2 where c1=‘a’ and c2=‘c’ and c5=‘b’ order by c3,c2\G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
         type: ref
possible_keys: c1234
          key: c1234
      key_len: 6
          ref: const,const
         rows: 1
        Extra: Using index condition; Using where
1 row in set (0.00 sec)

ERROR: 
No query specified

结果用了3个此时c2是确定值相当于常量