共计 2427 个字符，预计需要花费 7 分钟才能阅读完成。
这篇文章将为大家详细讲解有关 MySQL 多列索引怎么用，丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。
针对此问题进行测试：
假设某个表有一个联合索引（c1,c2,c3,c4）一下___只能使用该联合索引的 c1,c2,c3 部分
A where c1=x and c2=x and c4 x and c3=x
B where c1=x and c2=x and c4=x order by c3
C where c1=x and c4= x group by c3,c2
D where c1=x and c5=x order by c2,c3
E where c1=x and c2=x and c5=? order by c2,c
1、创建测试表
点击 (此处) 折叠或打开
mysql
show
create
table
t1 \G
***************************
row
***************************
Table:
t1
Create
Table:
CREATE
TABLE
`t1`
(
`c1`
mediumint(8)
unsigned
NULL
DEFAULT
0 ,
`c2`
smallint(5)
unsigned
NULL
DEFAULT
0 ,
`c3`
int(10)
unsigned
NULL
DEFAULT
0 ,
`c4`
int(10)
unsigned
NULL
DEFAULT
0 ,
`c5`
mediumint(8)
unsigned
NULL,
`c6`
varchar(2)
DEFAULT
NULL,
KEY
`idx_t1_c1_c2_c3_c4`
(`c1`,`c2`,`c3`,`c4`)
)
ENGINE=InnoDB
DEFAULT
CHARSET=utf8
2、选项 A 执行计划
点击 (此处) 折叠或打开
mysql
explain
select
from
where
c1=100
c2=2
c4 1000
c3=1419401948 \G
***************************
row
***************************
id: 1
select_type: SIMPLE
table:
t1
partitions:
NULL
type: range
possible_keys: idx_t1_c1_c2_c3_c4
key: idx_t1_c1_c2_c3_c4
key_len: 13
ref:
NULL
rows: 1
filtered: 100.00
Extra: Using index condition
3、选项 B 执行计划
点击 (此处) 折叠或打开
mysql
explain
select
from
where
c1=100
c2=2
c4=1419317673 order by c3 \G
***************************
row
***************************
id: 1
select_type: SIMPLE
table:
t1
partitions:
NULL
type: ref
possible_keys: idx_t1_c1_c2_c3_c4
key: idx_t1_c1_c2_c3_c4
key_len: 5
ref: const,const
rows: 1
filtered: 10.00
Extra: Using index condition
4、选项 C 执行计划
点击 (此处) 折叠或打开
mysql
explain
select
from
where
c1=100
c4=1419317673 group by c3,c2 \G
***************************
row
***************************
id: 1
select_type: SIMPLE
table:
t1
partitions:
NULL
type: ref
possible_keys: idx_t1_c1_c2_c3_c4
key: idx_t1_c1_c2_c3_c4
key_len: 3
ref: const
rows: 1
filtered: 10.00
Extra: Using index condition;
Using temporary;
Using filesort
5、选项 D 执行计划
点击 (此处) 折叠或打开
mysql
explain
select
from
where
c1=100
c5=2 order by c2,c3 \G
***************************
row
***************************
id: 1
select_type: SIMPLE
table:
t1
partitions:
NULL
type: ref
possible_keys: idx_t1_c1_c2_c3_c4
key: idx_t1_c1_c2_c3_c4
key_len: 3
ref: const
rows: 1
filtered: 10.00
Extra: Using index condition;
Using
where
6、选项 E 执行计划
点击 (此处) 折叠或打开
mysql
explain
select
from
where
c1=1000
c2=200
c5=2 order by c2,c3 \G
***************************
row
***************************
id: 1
select_type: SIMPLE
table:
t1
partitions:
NULL
type: ref
possible_keys: idx_t1_c1_c2_c3_c4
key: idx_t1_c1_c2_c3_c4
key_len: 5
ref: const,const
rows: 1
filtered: 10.00
Extra: Using index condition;
Using
where
关于“MySQL 多列索引怎么用”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。
