這篇文章將為大家詳細(xì)講解有關(guān)MySQL多列索引怎么用，小編覺(jué)得挺實(shí)用的，因此分享給大家做個(gè)參考，希望大家閱讀完這篇文章后可以有所收獲。
網(wǎng)站建設(shè)哪家好，找成都創(chuàng)新互聯(lián)！專注于網(wǎng)頁(yè)設(shè)計(jì)、網(wǎng)站建設(shè)、微信開(kāi)發(fā)、成都微信小程序、集團(tuán)企業(yè)網(wǎng)站建設(shè)等服務(wù)項(xiàng)目。為回饋新老客戶創(chuàng)新互聯(lián)還提供了電白免費(fèi)建站歡迎大家使用！
針對(duì)此問(wèn)題進(jìn)行測(cè)試：
假設(shè)某個(gè)表有一個(gè)聯(lián)合索引（c1,c2,c3,c4）一下___只能使用該聯(lián)合索引的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、創(chuàng)建測(cè)試表
點(diǎn)擊(此處)折疊或打開(kāi)
mysql> show create table t1 \G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`c1` mediumint(8) unsigned NOT NULL DEFAULT '0',
`c2` smallint(5) unsigned NOT NULL DEFAULT '0',
`c3` int(10) unsigned NOT NULL DEFAULT '0',
`c4` int(10) unsigned NOT NULL DEFAULT '0',
`c5` mediumint(8) unsigned NOT NULL,
`c6` varchar(2) DEFAULT NULL,
KEY `idx_t1_c1_c2_c3_c4` (`c1`,`c2`,`c3`,`c4`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
2、選項(xiàng)A執(zhí)行計(jì)劃
點(diǎn)擊(此處)折疊或打開(kāi)
mysql> explain select * from t1 where c1=100 and c2=2 and c4>1000 and c3=1419401948 \G
*************************** 1. 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、選項(xiàng)B執(zhí)行計(jì)劃
點(diǎn)擊(此處)折疊或打開(kāi)
mysql> explain select * from t1 where c1=100 and c2=2 and c4=1419317673 order by c3 \G
*************************** 1. 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、選項(xiàng)C執(zhí)行計(jì)劃
點(diǎn)擊(此處)折疊或打開(kāi)
mysql> explain select * from t1 where c1=100 and c4=1419317673 group by c3,c2 \G
*************************** 1. 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、選項(xiàng)D執(zhí)行計(jì)劃
點(diǎn)擊(此處)折疊或打開(kāi)
mysql> explain select * from t1 where c1=100 and c5=2 order by c2,c3 \G
*************************** 1. 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、選項(xiàng)E執(zhí)行計(jì)劃
點(diǎn)擊(此處)折疊或打開(kāi)
mysql> explain select * from t1 where c1=1000 and c2=200 and c5=2 order by c2,c3 \G
*************************** 1. 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
關(guān)于“MySQL多列索引怎么用”這篇文章就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，使各位可以學(xué)到更多知識(shí)，如果覺(jué)得文章不錯(cuò)，請(qǐng)把它分享出去讓更多的人看到。
重慶分公司
重慶分公司
