MySQL多列索引怎么用

這篇文章將為大家詳細(xì)講解有關(guān)MySQL多列索引怎么用，小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。

    針對此問題進行測試：
假設(shè)某個表有一個聯(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)建測試表

點擊(此處)折疊或打開

1. mysql> show create table t1 \G
   

2. *************************** 1. row ***************************
   

3.        Table: t1
   

4. Create Table: CREATE TABLE `t1` (
   

5.   `c1` mediumint(8) unsigned NOT NULL DEFAULT '0',
   

6.   `c2` smallint(5) unsigned NOT NULL DEFAULT '0',
   

7.   `c3` int(10) unsigned NOT NULL DEFAULT '0',
   

8.   `c4` int(10) unsigned NOT NULL DEFAULT '0',
   

9.   `c5` mediumint(8) unsigned NOT NULL,
   

10.   `c6` varchar(2) DEFAULT NULL,
    

11.   KEY `idx_t1_c1_c2_c3_c4` (`c1`,`c2`,`c3`,`c4`)
    

12. ) ENGINE=InnoDB DEFAULT CHARSET=utf8

2、選項A執(zhí)行計劃

點擊(此處)折疊或打開

1. mysql> explain select * from t1 where c1=100 and c2=2 and c4>1000 and c3=1419401948 \G
   

2. *************************** 1. row ***************************
   

3.            id: 1
   

4.   select_type: SIMPLE
   

5.         table: t1
   

6.    partitions: NULL
   

7.          type: range
   

8. possible_keys: idx_t1_c1_c2_c3_c4
   

9.           key: idx_t1_c1_c2_c3_c4
   

10.       key_len: 13
    

11.           ref: NULL
    

12.          rows: 1
    

13.      filtered: 100.00
    

14.         Extra: Using index condition

3、選項B執(zhí)行計劃

點擊(此處)折疊或打開

1. mysql> explain select * from t1 where c1=100 and c2=2 and c4=1419317673 order by c3 \G
   

2. *************************** 1. row ***************************
   

3.            id: 1
   

4.   select_type: SIMPLE
   

5.         table: t1
   

6.    partitions: NULL
   

7.          type: ref
   

8. possible_keys: idx_t1_c1_c2_c3_c4
   

9.           key: idx_t1_c1_c2_c3_c4
   

10.       key_len: 5
    

11.           ref: const,const
    

12.          rows: 1
    

13.      filtered: 10.00
    

14.         Extra: Using index condition

4、選項C執(zhí)行計劃

點擊(此處)折疊或打開

1. mysql> explain select * from t1 where c1=100 and c4=1419317673 group by c3,c2 \G
   

2. *************************** 1. row ***************************
   

3.            id: 1
   

4.   select_type: SIMPLE
   

5.         table: t1
   

6.    partitions: NULL
   

7.          type: ref
   

8. possible_keys: idx_t1_c1_c2_c3_c4
   

9.           key: idx_t1_c1_c2_c3_c4
   

10.       key_len: 3
    

11.           ref: const
    

12.          rows: 1
    

13.      filtered: 10.00
    

14.         Extra: Using index condition; Using temporary; Using filesort

5、選項D執(zhí)行計劃

點擊(此處)折疊或打開

1. mysql> explain select * from t1 where c1=100 and c5=2 order by c2,c3 \G
   

2. *************************** 1. row ***************************
   

3.            id: 1
   

4.   select_type: SIMPLE
   

5.         table: t1
   

6.    partitions: NULL
   

7.          type: ref
   

8. possible_keys: idx_t1_c1_c2_c3_c4
   

9.           key: idx_t1_c1_c2_c3_c4
   

10.       key_len: 3
    

11.           ref: const
    

12.          rows: 1
    

13.      filtered: 10.00
    

14.         Extra: Using index condition; Using where

6、選項E執(zhí)行計劃

點擊(此處)折疊或打開

1. mysql> explain select * from t1 where c1=1000 and c2=200 and c5=2 order by c2,c3 \G
   

2. *************************** 1. row ***************************
   

3.            id: 1
   

4.   select_type: SIMPLE
   

5.         table: t1
   

6.    partitions: NULL
   

7.          type: ref
   

8. possible_keys: idx_t1_c1_c2_c3_c4
   

9.           key: idx_t1_c1_c2_c3_c4
   

10.       key_len: 5
    

11.           ref: const,const
    

12.          rows: 1
    

13.      filtered: 10.00
    

14.         Extra: Using index condition; Using where

關(guān)于“MySQL多列索引怎么用”這篇文章就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，使各位可以學(xué)到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。