當(dāng)前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

mysql语句orderby_mysql中的orderby_MySQL

發(fā)布時間：2025/3/20 数据库 17 豆豆

生活随笔收集整理的這篇文章主要介紹了 mysql语句orderby_mysql中的orderby_MySQL 小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.

一、order by的原理

1、利用索引的有序性獲取有序數(shù)據(jù)

當(dāng)查詢語句的 order BY 條件和查詢的執(zhí)行計劃中所利用的 Index 的索引鍵(或前面幾個索引鍵)完全一致，且索引訪問方式為 range,ref 或者 index 的時候，MySQL 可以利用索引順序而直接取得已經(jīng)排好序的數(shù)據(jù)。這種方式的 order BY 基本上可以說是最優(yōu)的排序方式了,因為 MySQL 不需要進(jìn)行實際的排序操作，需要注意的是使用索引排序也有很多限制。

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

注意：MySQL在查詢時最多只能使用一個索引。因此，如果WHERE條件已經(jīng)占用了索引，那么在排序中就不使用索引了

mysql> show create table test \G

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

Table: test

Create Table: CREATE TABLE `test` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`addtime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

`a` int(11) unsigned NOT NULL,

`b` int(11) unsigned NOT NULL,

`c` int(11) unsigned NOT NULL,

`data` varchar(20) NOT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `u` (`addtime`,`a`,`b`),

KEY `a` (`a`),

KEY `b` (`b`),

KEY `c` (`c`)

) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8

執(zhí)行查詢1

mysql> explain select * from test where addtime='2015-10-13 15:38:32' order by a , b ;

+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+

+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+

| 1 | SIMPLE | test | ref | u | u | 8 | const | 4 | Using where |

+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+

執(zhí)行查詢2

mysql> explain select * from test where addtime='2015-10-13 15:38:32' order by a , b ,c;

+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------------+

+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------------+

| 1 | SIMPLE | test | ref | u | u | 8 | const | 4 | Using where; Using filesort |

+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------------+

使用filesort的情況

a、where語句與order by語句，使用了不同的索引

b、檢查的行數(shù)過多，且沒有使用覆蓋索引

c、對索引列同時使用了ASC和DESC

d、where語句或者ORDER BY語句中索引列使用了表達(dá)式，包括函數(shù)表達(dá)式

e、where 語句與ORDER BY語句組合滿足最左前綴，但where語句中使用了條件查詢

f、當(dāng)使用left join，使用右邊的表字段排序

2 利用內(nèi)存/磁盤文件排序獲取結(jié)果

由于沒有可以利用的有序索引取得有序的數(shù)據(jù)，MySQL需要通過相應(yīng)的排序算法，將取得的數(shù)據(jù)在sort_buffer_size系統(tǒng)變量所設(shè)置大小的排序區(qū)進(jìn)行排序，這個排序區(qū)是每個Thread 獨享的，所以說可能在同一時刻在 MySQL 中可能存在多個 sort buffer 內(nèi)存區(qū)域

MySQL中filesort 的實現(xiàn)算法有兩種:

1) 雙路排序:是首先根據(jù)相應(yīng)的條件取出相應(yīng)的排序字段和可以直接定位行數(shù)據(jù)的行指針信息，然后在sort buffer 中進(jìn)行排序(but this will be essentially hit the table in random order and is not very fast)。

2) 單路排序:是一次性取出滿足條件行的所有字段，然后在sort buffer中進(jìn)行排序。

在 MySQL4.1 版本之前只有第一種排序算法,第二種算法是從MySQL4.1開始的改進(jìn)算法,主要目的是為了減少第一次算法中需要兩次訪問表數(shù)據(jù)的IO操作,將兩次變成了一次,但相應(yīng)也會耗用更多的 sort buffer 空間。典型的以空間換時間的優(yōu)化方式。當(dāng)然,MySQL4.1開始的以后所有版本同時也支持第一種算法

MySQL主要通過比較系統(tǒng)參數(shù) max_length_for_sort_data的大小和Query語句所取出的字段類型大小總和來判定需要使用哪一種排序算法。如果max_length_for_sort_data更大,則使用第二種優(yōu)化后的算法,反之使用第一種算法。所以如果希望 order BY 操作的效率盡可能的高,需要注意max_length_for_sort_data參數(shù)的設(shè)置。

3、使用Using temporary 臨時表來filesort

如果order by的子句只引用了聯(lián)接中的第一個表，MySQL會先對第一個表進(jìn)行排序，然后進(jìn)行聯(lián)接，expain中的Extra會出現(xiàn)Using Filesort

否則MySQL先把結(jié)果保存到臨時表(Temporary Table)，然后再對臨時表的數(shù)據(jù)進(jìn)行排序，此時expain中的Extra的顯示Using temporary Using Filesort

4、Join排序

mysql中有三種方式

1)、Use index-based access method that produces ordered output　 -》　null

2)、Use filesort() on 1st non-constant table　－》　“Using filesort” in the first row

3)、Put join result into a temporary table and use filesort() on it -》 “Using temporary; Using filesort” in the first row

5、說一下filesort，可以參考 What does Using filesort mean in MySQL?

摘自原文

The truth is, filesort is badly named. Anytime a sort can’t be performed from an index, it’s a filesort. It has nothing to do with files. Filesort should be called “sort.” It is quicksort at heart.

If the sort is bigger than the sort buffer, it is performed a bit at a time, and then the chunks are merge-sorted to produce the final sorted output. There is a lot more to it than this. I refer you to Sergey Petrunia’s article on How MySQL executes ORDER BY. You can also read about it in our book, but if you read Sergey’s article you won’t need to.

這里有一個誤區(qū)，容易望文生義，Explain 命令輸出信息中的 filesort 到底是什么意思呢？其實很簡單，就是告訴你 MySQL 需要進(jìn)行實際的排序操作而不能通過索引獲得已排序數(shù)據(jù)

1)、filesort(其實就是排序) 可不一定會產(chǎn)生臨時表

2)、filesort 與臨時表數(shù)據(jù)寫入磁盤是沒有任何直接聯(lián)系的

二、優(yōu)化order by

當(dāng)無法避免排序操作時,又該如何來優(yōu)化呢？很顯然,優(yōu)先選擇第一種using index 的排序方式，在第一種方式無法滿足的情況下，盡可能讓 MySQL 選擇使用第二種單路算法來進(jìn)行排序。這樣可以減少大量的隨機(jī)IO操作,很大幅度地提高排序工作的效率。

1、加大 max_length_for_sort_data 參數(shù)的設(shè)置

在 MySQL 中,決定使用老式排序算法還是改進(jìn)版排序算法是通過參數(shù) max_length_for_ sort_data 來決定的。當(dāng)所有返回字段的最大長度小于這個參數(shù)值時,MySQL 就會選擇改進(jìn)后的排序算法,反之,則選擇老式的算法。所以,如果有充足的內(nèi)存讓MySQL 存放須要返回的非排序字段,就可以加大這個參數(shù)的值來讓 MySQL 選擇使用改進(jìn)版的排序算法。

2、去掉不必要的返回字段

當(dāng)內(nèi)存不是很充裕時,不能簡單地通過強(qiáng)行加大上面的參數(shù)來強(qiáng)迫 MySQL 去使用改進(jìn)版的排序算法,否則可能會造成 MySQL 不得不將數(shù)據(jù)分成很多段,然后進(jìn)行排序,這樣可能會得不償失。此時就須要去掉不必要的返回字段,讓返回結(jié)果長度適應(yīng) max_length_for_sort_data 參數(shù)的限制。

3 增大 sort_buffer_size 參數(shù)設(shè)置

這個值如果過小的話,再加上你一次返回的條數(shù)過多,那么很可能就會分很多次進(jìn)行排序,然后最后將每次的排序結(jié)果再串聯(lián)起來,這樣就會更慢,增大 sort_buffer_size 并不是為了讓 MySQL選擇改進(jìn)版的排序算法,而是為了讓MySQL盡量減少在排序過程中對須要排序的數(shù)據(jù)進(jìn)行分段,因為分段會造成 MySQL 不得不使用臨時表來進(jìn)行交換排序。

但是這個值不是越大越好：

1 Sort_Buffer_Size 是一個connection級參數(shù),在每個connection第一次需要使用這個buffer的時候,一次性分配設(shè)置的內(nèi)存。

2 Sort_Buffer_Size 并不是越大越好,由于是connection級的參數(shù),過大的設(shè)置+高并發(fā)可能會耗盡系統(tǒng)內(nèi)存資源。

3 據(jù)說Sort_Buffer_Size 超過2M的時候,就會使用mmap() 而不是 malloc() 來進(jìn)行內(nèi)存分配,導(dǎo)致效率降低。

本條技術(shù)文章來源于互聯(lián)網(wǎng)，如果無意侵犯您的權(quán)益請點擊此處反饋版權(quán)投訴

本文系統(tǒng)來源：php中文網(wǎng)

總結(jié)

以上是生活随笔為你收集整理的mysql语句orderby_mysql中的orderby_MySQL的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。

上一篇： mysql删除默认密码_修改mysql默
下一篇： mysql order by 日期_my