乱爱性全过程免费视频/午夜毛片视频/中文字幕av三区/一级视频网址

當(dāng)前位置： 維易PHP培訓(xùn)學(xué)院 > MYSQL教程 > 內(nèi)容正文

Mysql入門[MySQL FAQ]系列 — MySQL聯(lián)合索引是否支持不同排序規(guī)則

作者：VEPHP   時(shí)間 2017-09-27

《Mysql入門[MySQL FAQ]系列 — MySQL聯(lián)合索引是否支持不同排序規(guī)則》要點(diǎn)：
本文介紹了Mysql入門[MySQL FAQ]系列 — MySQL聯(lián)合索引是否支持不同排序規(guī)則，希望對您有用。如果有疑問，可以聯(lián)系我們。

導(dǎo)讀：截止到目前的5.7.4版本為止,MySQL的聯(lián)合索引仍無法支持聯(lián)合索引使用不同排序規(guī)則,例如：ALTER TABLE t ADD INDEX idx(col1, col2 ...
截止到目前的5.7.4版本為止,MySQL的聯(lián)合索引仍無法支持聯(lián)合索引使用不同排序規(guī)則,例如：ALTER TABLE t ADD INDEX idx(col1, col2 DESC).

先來了解下MySQL關(guān)于索引的一些基礎(chǔ)知識要點(diǎn)：MYSQL必讀

? a、EXPLAIN結(jié)果中的key_len只顯示了條件檢索子句需要的索引長度,但 ORDER BY、GROUP BY 子句用到的索引則不計(jì)入 key_len 統(tǒng)計(jì)值；
? b、聯(lián)合索引(composite index)：多個(gè)字段組成的索引,稱為聯(lián)合索引；
例如：ALTER TABLE t ADD INDEX `idx` (col1, col2, col3)
? c、覆蓋索引(covering index)：如果查詢需要讀取到索引中的一個(gè)或多個(gè)字段,則可以從索引樹中直接取得結(jié)果集,稱為覆蓋索引；
例如：SELECT col1, col2 FROM t;
? d、最左原則(prefix index)：如果查詢條件檢索時(shí),只需要匹配聯(lián)合索引中的最左順序一個(gè)或多個(gè)字段,稱為最左索引原則,或者叫最左前綴；
例如：SELECT * FROM t WHERE col1 = ? AND col2 = ?;
? e、在老版本(大概是5.5以前,具體版本號未確認(rèn)核實(shí))中,查詢使用聯(lián)合索引時(shí),可以不區(qū)分條件中的字段順序,在這以前是需要按照聯(lián)合索引的創(chuàng)建順序書寫SQL條件子句的；
例如：SELECT * FROM t WHERE col3 = ? AND col1 = ? AND col2 = ?;
? f、MySQL截止目前還只支持多個(gè)字段都是正序索引,不支個(gè)別字段持倒序索引；
例如：ALTER TABLE t ADD INDEX `idx` (col1, col2, col3 DESC),這里的DESC只是個(gè)預(yù)留的關(guān)鍵字,目前還不能真正有作用
? g、聯(lián)合索引中,如果查詢條件中最左邊某個(gè)索引列使用范圍查找,則只能使用前綴索引,無法使用到整個(gè)索引；
例如：SELECT * FROM t WHERE col1 = ? AND col2 >= ? AND col3 = ?; 這時(shí)候,只能用到 idx 索引的最左2列進(jìn)行檢索,而col3條件則無法利用索引進(jìn)行檢索
? h、InnoDB引擎中,二級索引實(shí)際上包含了主鍵索引值；

關(guān)于 key_len 的計(jì)算規(guī)則：MYSQL必讀

? 當(dāng)索引字段為定長數(shù)據(jù)類型,比如：char,int,datetime,需要有是否為空的標(biāo)記,這個(gè)標(biāo)記需要占用1個(gè)字節(jié)；
? 當(dāng)索引字段為變長數(shù)據(jù)類型,比如：varchar,除了是否為空的標(biāo)記外,還需要有長度信息,需要占用2個(gè)字節(jié)；
? 當(dāng)字段定義為非空的時(shí)候,是否為空的標(biāo)記將不占用字節(jié)；
? 同時(shí)還需要考慮表所使用字符集的差異,latin1編碼一個(gè)字符1個(gè)字節(jié),gbk編碼一個(gè)字符2個(gè)字節(jié),utf8編碼一個(gè)字符3個(gè)字節(jié)；

因此,key_len長度的計(jì)算公式MYSQL必讀

? varchr(10)變長字段且允許NULL : 10*(Character Set：utf8=3,gbk=2,latin1=1)+1(NULL標(biāo)記位)+2(變長字段)
? varchr(10)變長字段且不允許NULL : 10*(Character Set：utf8=3,gbk=2,latin1=1)+2(變長字段)
? char(10)固定字段且允許NULL : 10*(Character Set：utf8=3,gbk=2,latin1=1)+1(NULL標(biāo)記位)
? char(10)固定字段且不允許NULL : 10*(Character Set：utf8=3,gbk=2,latin1=1)

附,關(guān)于?filesort?排序算法：
光看 filesort 字面意思,可能以為是要利用磁盤文件進(jìn)行排序,實(shí)則不全然.
當(dāng)MySQL不能使用索引進(jìn)行排序時(shí),就會(huì)利用自己的排序算法(快速排序算法)在內(nèi)存(sort buffer)中對數(shù)據(jù)進(jìn)行排序,如果內(nèi)存裝載不下,它會(huì)將磁盤上的數(shù)據(jù)進(jìn)行分塊,再對各個(gè)數(shù)據(jù)塊進(jìn)行排序,然后將各個(gè)塊合并成有序的結(jié)果集(實(shí)際上就是外排序).MYSQL必讀

對于filesort,MySQL有兩種排序算法：
1、兩遍掃描算法(Two passes)
實(shí)現(xiàn)方式是先將須要排序的字段和可以直接定位到相關(guān)行數(shù)據(jù)的指針信息取出,然后在設(shè)定的內(nèi)存(通過參數(shù)?sort_buffer_size?設(shè)定)中進(jìn)行排序,完成排序之后再次通過行指針信息取出所需的列.
注：該算法是4.1之前只有這種算法,它需要兩次訪問數(shù)據(jù),尤其是第二次讀取操作會(huì)導(dǎo)致大量的隨機(jī)I/O操作.不過,這種方法內(nèi)存開銷較小.MYSQL必讀

2、一次掃描算法(single pass)
該算法一次性將所需的列全部取出,在內(nèi)存中排序后直接將結(jié)果輸出.MYSQL必讀

注：從 MySQL 4.1 版本開始支持該算法.它減少了I/O的次數(shù),效率較高,但是內(nèi)存開銷也較大.如果我們將并不需要的列也取出來,就會(huì)極大地浪費(fèi)排序過程所需要的內(nèi)存.在 MySQL 4.1 之后的版本中,可以通過設(shè)置?max_length_for_sort_data?參數(shù)來控制 MySQL 選擇第一種排序算法還是第二種.當(dāng)取出的所有大字段總大小大于?max_length_for_sort_data?的設(shè)置時(shí),MySQL 就會(huì)選擇使用第一種排序算法,反之,則會(huì)選擇第二種.為了盡可能地提高排序性能,我們自然更希望使用第二種排序算法,所以在SQL中僅僅取出需要的列是非常有必要的.MYSQL必讀

當(dāng)對連接操作進(jìn)行排序時(shí),如果ORDER BY僅僅引用第一個(gè)表的列,MySQL對該表進(jìn)行filesort操作,然后進(jìn)行連接處理,此時(shí),EXPLAIN輸出“Using filesort”；否則,MySQL必須將查詢的結(jié)果集生成一個(gè)臨時(shí)表,在連接完成之后進(jìn)行filesort操作,此時(shí),EXPLAIN輸出“Using temporary;Using filesort”.MYSQL必讀

后面是幾個(gè)幾個(gè)測試結(jié)果,有興趣不怕累的可以看看,哈哈.MYSQL必讀

測試MySQL版本：5.5.37-log MySQL Community Server (GPL)MYSQL必讀

#創(chuàng)建一個(gè)測試表,id是主鍵字段,(a1, a2) 組成聯(lián)合索引MYSQL必讀

(yejr@imysql.com)> show create table t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`a1` int(10) unsigned NOT NULL DEFAULT '0',
`a2` int(10) unsigned NOT NULL DEFAULT '0',
`aa` varchar(20) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `idx` (`a1`,`a2`)
) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8

#填充了64條測試數(shù)據(jù)MYSQL必讀

(yejr@imysql.com)> show table status like 't'\G
*************************** 1. row ***************************
Name: t
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 64
Avg_row_length: 256
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 0
Auto_increment: 122
Create_time: 2014-09-15 17:17:09
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:

#對 a1、a2 正序排序,同時(shí)取a1、a2兩個(gè)字段,可以直接使用該聯(lián)合索引取回結(jié)果,并且排序完成
#符合規(guī)則cMYSQL必讀

(yejr@imysql.com)> explain select a1, a2 from t order by a1, a2\G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

#對 a1、a2 倒序排序,同時(shí)取a1、a2兩個(gè)字段,可以直接使用該聯(lián)合索引取回結(jié)果,并且排序完成
#由于同時(shí)對a1、a2都是倒序排序,因此完全可以用到索引的順序,只是反向掃描而已
#符合規(guī)則cMYSQL必讀

(yejr@imysql.com)> explain select a1, a2 from t order by a1 desc, a2 desc\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

#對 a1、a2正序排序,只取a1字段,可以直接使用該聯(lián)合索引取回結(jié)果,并且排序完成
#匹配規(guī)則cMYSQL必讀

(yejr@imysql.com)> explain select a1 from t order by a1, a2\G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

#對 a1、a2 正序排序,只取a2字段,可以直接使用該聯(lián)合索引取回結(jié)果,并且排序完成
#符合規(guī)則cMYSQL必讀

(yejr@imysql.com)> explain select a2 from t order by a1, a2 \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

#只對 a1 正序排序,同時(shí)取a1、a2兩個(gè)字段,可以直接使用該聯(lián)合索引取回結(jié)果,并且排序完成
#符合規(guī)則cMYSQL必讀

(yejr@imysql.com)> explain select a1, a2 from t order by a1\G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

#對 a1 正序排序,對 a2 倒序排序,只取a1字段,可以直接使用該聯(lián)合索引取回結(jié)果,但排序時(shí)需要進(jìn)行filesort排序,不能利用索引直接得到排序結(jié)果
#這時(shí)雖然只讀取一個(gè)字段,但實(shí)際還是掃描了整個(gè)索引,并非使用前綴索引
#符合規(guī)則c、fMYSQL必讀

(yejr@imysql.com)> explain select a1 from t order by a1, a2 desc \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index; Using filesort

#只取a1字段,同時(shí)只對 a1 字段正序排序,這時(shí)可用聯(lián)合索引取得結(jié)果,同時(shí)也可以利用前綴索引的原則進(jìn)行排序
#符合規(guī)則cMYSQL必讀

(yejr@imysql.com)> explain select a1 from t order by a1\G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

#只取a1字段,同時(shí)只對 a2 字段正序排序,這時(shí)雖然可用聯(lián)合索引取得結(jié)果,但排序時(shí)需要進(jìn)行filesort排序,不能利用索引直接得到排序結(jié)果
#符合規(guī)則c、fMYSQL必讀

(yejr@imysql.com)> explain select a1 from t order by a2\G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index; Using filesort

#對 a1 正序排序,對a2 倒序排序,只取a1字段,可以直接使用該聯(lián)合索引取回結(jié)果,但排序時(shí)需要進(jìn)行filesort排序,不能利用索引直接得到排序結(jié)果
#這時(shí)雖然只讀取一個(gè)字段,但實(shí)際還是掃描了整個(gè)索引,并非使用前綴索引
#符合規(guī)則c、fMYSQL必讀

(yejr@imysql.com)> explain select a1 from t order by a1, a2 desc \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index; Using filesort

#對 a1 正序排序,對a2 倒序排序,只取a2字段,可以直接使用該聯(lián)合索引取回結(jié)果,但排序時(shí)需要進(jìn)行filesort排序,不能利用索引直接得到排序結(jié)果
#這時(shí)雖然只讀取一個(gè)字段,但實(shí)際還是掃描了整個(gè)索引,并非使用前綴索引
#符合規(guī)則c、fMYSQL必讀

(yejr@imysql.com)> explain select a2 from t order by a1, a2 desc \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index; Using filesort

#對 a1 正序排序,對a2 倒序排序,只取a2字段,可以直接使用該聯(lián)合索引取回結(jié)果,但排序時(shí)需要進(jìn)行filesort排序,不能利用索引直接得到排序結(jié)果
#這時(shí)雖然只讀取一個(gè)字段,但實(shí)際還是掃描了整個(gè)索引,并非使用前綴索引
#符合規(guī)則c、fMYSQL必讀

(yejr@imysql.com)> explain select a1 from t order by a1, a2 \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

#對 a1 、a2順序排序,取得主鍵id字段,可以直接使用該聯(lián)合索引取回結(jié)果并完成排序.
#這里需要注意下,二級索引其實(shí)是包括主鍵索引的,因此用idx索引即可取到全部結(jié)果.
#下面這個(gè)SQL也是一樣的效果：select a1,a2,id from t order by a1, a2;
#符合規(guī)則c、hMYSQL必讀

(yejr@imysql.com)> explain select id from t order by a1, a2 \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

#對 a1 正序排序,對a2 倒序排序,取得主鍵id字段,可以直接使用該聯(lián)合索引取回結(jié)果,但需要進(jìn)行filesort排序.
#符合規(guī)則c、f、hMYSQL必讀

(yejr@imysql.com)> explain select id from t order by a1, a2 desc \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index; Using filesort

#對 a1 倒序排序,對a2 正序排序,取得主鍵id字段,可以直接使用該聯(lián)合索引取回結(jié)果,但需要進(jìn)行filesort排序.
#符合規(guī)則c、f、hMYSQL必讀

(yejr@imysql.com)> explain select id from t order by a1 desc, a2 \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index; Using filesort

#過濾條件a1字段(使用前綴索引掃描,key_len為4),對a2字段進(jìn)行正序排序,取得主鍵id字段,可以直接使用聯(lián)合索引取回結(jié)果
#符合規(guī)則a、c、d、hMYSQL必讀

(yejr@imysql.com)> explain select id from t where a1 = 219 order by a2\G
id: 1
select_type: SIMPLE
table: t
type: ref
possible_keys: idx
key: idx
key_len: 4
ref: const
rows: 2
Extra: Using where; Using index

轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/5804.html


    標(biāo)簽：