MySQL數(shù)據(jù)庫(kù)ORDER BY優(yōu)化是怎樣的呢

今天就跟大家聊聊有關(guān)MySQL數(shù)據(jù)庫(kù)ORDER BY優(yōu)化是怎樣的呢，可能很多人都不太了解，為了讓大家更加了解，小編給大家總結(jié)了以下內(nèi)容，希望大家根據(jù)這篇文章可以有所收獲。

在使用order by時(shí)，經(jīng)常出現(xiàn)Using filesort，所以對(duì)于此類(lèi)sql語(yǔ)句我們需要去盡力優(yōu)化，使其盡量使用Using index。

那么，我們對(duì)于這類(lèi)型的語(yǔ)句我們?cè)趺慈プ鰞?yōu)化呢?因?yàn)檫@一塊還是比較容易混淆的，所以我弄了個(gè)實(shí)驗(yàn)，相信大家跟我一起做下實(shí)驗(yàn)就都能理解了~

1. 環(huán)境準(zhǔn)備

drop table if exists test; create table test( id int primary key auto_increment, c1 varchar(10), c2 varchar(10), c3 varchar(10), c4 varchar(10), c5 varchar(10) ) ENGINE=INNODB default CHARSET=utf8;  insert into test(c1,c2,c3,c4,c5) values('a1','a2','a3','a4','a5'); insert into test(c1,c2,c3,c4,c5) values('b1','b2','b3','b4','b5'); insert into test(c1,c2,c3,c4,c5) values('c1','c2','c3','c4','c5'); insert into test(c1,c2,c3,c4,c5) values('d1','d2','d3','d4','d5'); insert into test(c1,c2,c3,c4,c5) values('e1','e2','e3','e4','e5');

2. 創(chuàng)建btree索引

create index idx_c1234 on test(c1,c2,c3,c4); show index from test;

3. 范圍掃導(dǎo)致全表掃描

explain select * from test where c1>'a1' order by c1;

分析：

• 在c1,c2,c3,c4上創(chuàng)建了索引，直接在c1上使用范圍，導(dǎo)致了索引失效，全表掃描：type=ALL，ref=Null。因?yàn)榇藭r(shí)c1主要用于排序，并不是查詢(xún)。

• 使用c1進(jìn)行排序，出現(xiàn)了Using filesort。

• 解決方法：使用覆蓋索引。

4、覆蓋索引--》優(yōu)化

explain select c1 from testwhere c1>'a1' order by c1;

分析：

• 使用了覆蓋索引，不走全掃，走索引范圍掃描

• 排序時(shí)按照索引的順序，所以不會(huì)出現(xiàn)Using filesort。

這里不懂沒(méi)關(guān)系，后面我會(huì)分享索引的八大法則，保證看得懂...

5. 沒(méi)有按最左列索引排序

explain select c1 from testwhere c1>'a1' order by c2;

分析：

這里出現(xiàn)了Using filesort，是因?yàn)榕判蛴玫腸2，與索引的創(chuàng)建順序(c1,c2,c3,c4)不一致。

6. 排序索引列與索引創(chuàng)建的順序相反

explain select c1 from testwhere c1>'a1' order by c2,c1;

分析：

這里出現(xiàn)了Using filesort。因?yàn)榕判蛩饕?c2,c1)與索引創(chuàng)建的順序(c1,c2)相反，從而產(chǎn)生了重排，也就出現(xiàn)了Using  filesort。

7. order by索引列排序不一致

explain select c1 from testwhere c1>'a1' order by c1 asc,c2 desc;

分析：

雖然排序的字段列與索引順序一樣，且order by默認(rèn)升序，這里c2 desc變成了降序，導(dǎo)致與索引的排序方式不同，從而產(chǎn)生Using  filesort。如果是order by c1 asc,c2 asc或者order by c1 desc,c2 desc就會(huì)是using index了。

實(shí)驗(yàn)總結(jié)

1. MySQL支持兩種方式的排序filesort和index

Using index是指MySQL掃描索引本身完成排序。index效率高，filesort效率低。

2. 為排序使用索引

假設(shè)KEY test(a,b,c)

(1) order by 能使用索引最左前綴

-order by a -order by a,b -order by a,b,c -order by a asc,b asc,c asc -order by a desc,b desc,c desc

(2) 如果where使用索引最左前綴定位為常量，則order by可以使用索引

-where a= const order by b,c -where a= const and b= const order by c -where a= const and b> consst order by b,c

(3) 不能使用索引進(jìn)行排序

-order by a asc,b desc, c desc /*排序不一致*/ -where g=const order by b,c /*丟失a索引*/ -where a=const order by c /*丟失b索引*/ -where a=const order by a,d /*d不是索引一部分*/ -where a in (....) order by b,c /*對(duì)于排序來(lái)說(shuō)，多個(gè)相等條件也是范圍查詢(xún)*/

3. filesort有兩種排序算法：雙路排序和單路排序

雙路排序：在MySQL4.1之前使用雙路排序，就是兩次磁盤(pán)掃描，得到最終數(shù)據(jù)。讀取行指針和order  by列，對(duì)他們進(jìn)行排序，然后掃描已經(jīng)排好序的列表，按照列表中的值重新從列表中讀取對(duì)應(yīng)的數(shù)據(jù)輸出。即從磁盤(pán)讀取排序字段，在buffer進(jìn)行排序，再?gòu)拇疟P(pán)取其他字段。如果使用雙路排序，取一批數(shù)據(jù)要對(duì)磁盤(pán)進(jìn)行兩次掃描，眾所周知，I/O操作是很耗時(shí)的，因此在MySQL4.1以后，出現(xiàn)了改進(jìn)的算法：?jiǎn)温放判颉?/p>

單路排序：從磁盤(pán)中查詢(xún)所需的列，按照order  by列在buffer中對(duì)它們進(jìn)行排序，然后掃描排序后的列表進(jìn)行輸出。它的效率更高一些，避免了第二次讀取數(shù)據(jù)，并且把隨機(jī)I/O變成了順序I/O，但是會(huì)使用更多的空間，因?yàn)樗衙恳恍卸急４嬖趦?nèi)存中了。但當(dāng)讀取數(shù)據(jù)超過(guò)sort_buffer的容量時(shí)，就會(huì)導(dǎo)致多次讀取數(shù)據(jù)，并創(chuàng)建臨時(shí)表，最后多路合并，產(chǎn)生多次I/O，反而增加其I/O運(yùn)算。

解決方式：

• 增加sort_buffer_size參數(shù)的設(shè)置。

• 增大max_length_for_sort_data參數(shù)的設(shè)置。

4. 提升order by速度

• 在使用order by時(shí)，不要用select  *，只查詢(xún)所需的字段。因?yàn)楫?dāng)查詢(xún)字段過(guò)多時(shí)，會(huì)導(dǎo)致sort_buffer不夠，從而使用多路排序或進(jìn)行多次I/O操作。

• 增加sort_buffer_size。

• 增加max_length_for_sort_data。

5. 優(yōu)化group by

group by與order  by很類(lèi)似，其實(shí)質(zhì)是先排序后分組，遵照索引創(chuàng)建順序的最佳左前綴法則。當(dāng)無(wú)法使用索引列的時(shí)候，也要對(duì)sort_buffer_size和max_length_for_sort_data參數(shù)進(jìn)行調(diào)整。注意where高于having，能寫(xiě)在where中的限定條件就不要去having限定了。

看完上述內(nèi)容，你們對(duì)MySQL數(shù)據(jù)庫(kù)ORDER BY優(yōu)化是怎樣的呢有進(jìn)一步的了解嗎？如果還想了解更多知識(shí)或者相關(guān)內(nèi)容，請(qǐng)關(guān)注億速云行業(yè)資訊頻道，感謝大家的支持。