溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章將為大家詳細講解有關MySQL多列索引怎么用，小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。
針對此問題進行測試：
假設某個表有一個聯合索引（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
***************************
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、選項A執行計劃
點擊(此處)折疊或打開
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、選項B執行計劃
點擊(此處)折疊或打開
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、選項C執行計劃
點擊(此處)折疊或打開
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、選項D執行計劃
點擊(此處)折疊或打開
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、選項E執行計劃
點擊(此處)折疊或打開
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
關于“MySQL多列索引怎么用”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，使各位可以學到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
