欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL order by實現(xiàn)原理分析和Filesort優(yōu)化方式

 更新時間:2023年12月01日 16:57:04   作者:lijingkuan  
這篇文章主要介紹了MySQL order by實現(xiàn)原理分析和Filesort優(yōu)化方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教

在MySQL中的ORDER BY有兩種排序?qū)崿F(xiàn)方式:

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

2. 文件排序

在使用explain分析查詢的時候,利用有序索引獲取有序數(shù)據(jù)顯示Using index。而文件排序顯示Using filesort。

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

取出滿足過濾條件、作為排序條件的字段,以及可以直接定位到行數(shù)據(jù)的行指針信息,在 Sort Buffer 中進行實際的排序操作,然后利用排好序的數(shù)據(jù)根據(jù)行指針信息返回表中取得客戶端請求的其他字段的數(shù)據(jù),再返回給客戶端。

這種方式,在使用explain分析查詢的時候,顯示Using index。而文件排序顯示Using filesort。

注意:

MySQL在查詢時最多只能使用一個索引。

因此,如果WHERE條件已經(jīng)占用了索引,那么在排序中就不使用索引了。(現(xiàn)在已經(jīng)可以使用多個索引了)

1.1 按照索引對結果進行排序:order by 使用索引時有條件

1) 返回選擇的字段,即只包括在有選擇的此列上(select后面的字段),不一定適應*的情況):

CREATE TABLE `test` (  
  `id` int(11) NOT NULL AUTO_INCREMENT,  
  `rdate` datetime NOT NULL,  
  `inventid` int(11) NOT NULL,  
  `customerid` int(11) NOT NULL,  
  `staffid` int(11) NOT NULL,  
  `data` varchar(20) NOT NULL,  
  PRIMARY KEY (`id`),  
  UNIQUE KEY `rdate`(`rdate`,`inventid`,`customerid`),  
  KEY `inventid` (`inventid`),  
  KEY `customerid` (`customerid`),  
  KEY `staffid` (`staffid`)  
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=latin1  

如:

root@mysql5601 14:58:  [lijk]> explain select inventid from test where rdate='2011-12-1400:00:00' order by  inventid , customerid;
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | test  | ref  | rdate         | rdate | 5       | const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+
1 row in set, 5 warnings (0.00 sec)

Select選擇的列使用索引,而下面不使用索引:

root@mysql5601 15:00:  [lijk]> explain select * from test where rdate='2011-12-14 00:00:00'order by  inventid , customerid ;
+----+-------------+-------+------+---------------+-------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-------------+
|  1 | SIMPLE      | test  | ref  | rdate         | rdate | 5       | const |    1 | Using where |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-------------+
1 row in set (0.00 sec)

2) 只有當ORDER BY中所有的列必須包含在相同的索引,并且索引的順序和order by子句中的順序完全一致,并且所有列的排序方向(升序或者降序)一樣才有,(混合使用ASC模式和DESC模式則不使用索引)

root@mysql5601 15:00:  [lijk]> explain select inventid from test order by rdate, inventid ; 
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | index | NULL          | rdate | 13      | NULL |    1 | Using index |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
1 row in set (0.00 sec)

root@mysql5601 15:01:  [lijk]> explain select inventid from test where rdate="2011-12-16" order by  inventid ,staffid;  
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows | Extra                       |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | test  | ref  | rdate         | rdate | 5       | const |    1 | Using where; Using filesort |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)

由于rdate, inventid使用了同一個索引。排序使用到了索引。這個也是滿足了前綴索引。但是order by inventid ,staffid;就不是使用了索引,因為staffid和inventid不是同一個索引

3) where 語句與ORDER BY語句組合滿足最左前綴:

root@mysql5601 15:02:  [lijk]> explain select inventid from test where rdate="2011-12-16" order by  inventid ;
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | test  | ref  | rdate         | rdate | 5       | const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

4) 如果查詢聯(lián)接了多個表,只有在order by子句的所有列引用的是第一個表的列才可以。

5) 在其他的情況下,mysql使用文件排序 例如:  

  • 1) where語句與order by語句,使用了不同的索引
  • 2) 檢查的行數(shù)過多,且沒有使用覆蓋索引
  • 3) ORDER BY中的列不包含在相同的索引,也就是使用了不同的索引
  • 4) 對索引列同時使用了ASC和DESC
  • 5) where語句或者ORDER BY語句中索引列使用了表達式,包括函數(shù)表達式
  • 6) where 語句與ORDER BY語句組合滿足最左前綴,但where語句中使用了條件查詢。查見第10句,雖然where與order by構成了索引最左有綴的條件,但是where子句中使用的是條件查詢。
root@mysql5601 15:02:  [lijk]> explain select inventid from test where  rdate>"2011-12-16" order by  inventid;
+----+-------------+-------+-------+---------------+-------+---------+------+------+------------------------------------------+
| id | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | Extra                                    |
+----+-------------+-------+-------+---------------+-------+---------+------+------+------------------------------------------+
|  1 | SIMPLE      | test  | index | rdate         | rdate | 13      | NULL |    1 | Using where; Using index; Using filesort |
+----+-------------+-------+-------+---------------+-------+---------+------+------+------------------------------------------+
1 row in set (0.00 sec)
  • 7) 當使用left join,使用右邊的表字段排序

2.文件排序

這個 filesort 并不是說通過磁盤文件進行排序,而只是告訴我們進行了一個排序操作。

即在MySQL Query Optimizer 所給出的執(zhí)行計劃(通過 EXPLAIN 命令查看)中被稱為文件排序(filesort)

文件排序是通過相應的排序算法,將取得的數(shù)據(jù)在內(nèi)存中進行排序: MySQL需要將數(shù)據(jù)在內(nèi)存中進行排序,所使用的內(nèi)存區(qū)域也就是我們通過sort_buffer_size 系統(tǒng)變量所設置的排序區(qū)。這個排序區(qū)是每個Thread 獨享的,所以說可能在同一時刻在MySQL 中可能存在多個 sort buffer 內(nèi)存區(qū)域。

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

雙路排序:是首先根據(jù)相應的條件取出相應的排序字段和可以直接定位行數(shù)據(jù)的行指針信息,然后在sort buffer 中進行排序。

單路排序:是一次性取出滿足條件行的所有字段,然后在sort buffer中進行排序。

在MySQL4.1版本之前只有第一種排序算法雙路排序,第二種算法是從MySQL4.1開始的改進算法,主要目的是為了減少第一次算法中需要兩次訪問表數(shù)據(jù)的 IO 操作,將兩次變成了一次,但相應也會耗用更多的sortbuffer 空間。

當然,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ù)的設置。

曾經(jīng)就有同事的數(shù)據(jù)庫出現(xiàn)大量的排序等待,造成系統(tǒng)負載很高,而且響應時間變得很長,最后查出正是因為MySQL 使用了傳統(tǒng)的第一種排序算法而導致,在加大了max_length_for_sort_data 參數(shù)值之后,系統(tǒng)負載馬上得到了大的緩解,響應也快了很多。

2.1 MySQL 需要使用filesort 實現(xiàn)排序的實例

假設有 Table A 和 B 兩個表結構分別如下:

# mysql

>show create table A\G
   *************************** 1. row ***************************
   Table: A
   Create Table: CREATE TABLE `A` (
   `id` int(11) NOT NULL default '0',
    `c2` char(2) default NULL,
   `c3` varchar(16) default NULL, 
   `c4` datetime default NULL, 
   PRIMARY KEY (`id`) 
   ) ENGINE=MyISAM DEFAULT CHARSET=utf8

#:mysql

> show create table B\G
   *************************** 1. row ***************************    Table: B
   Create Table: CREATE TABLE `B` ( 
   `id` int(11) NOT NULL default '0', 
   `c2` char(2) default NULL,
   `c3` varchar(16) default NULL, 
   PRIMARY KEY (`id`),
   KEY `B_c2_ind` (`c2`)
   ) ENGINE=MyISAM DEFAULT CHARSET=utf8

A.c2不是索引將使用:

root@mysql5601 15:02:  [lijk]> EXPLAIN SELECT A.* FROM A,B WHERE A.id >2 AND A.c2 <5 AND A.c2 = B.c2 ORDER BY A.c2\G

  *************************** 1. row ***************************
  id: 1
  select_type: SIMPLE
  table: A
  type: range
  possible_keys: PRIMARY
  key: PRIMARY
  key_len: 4
  ref: NULL
  rows: 3
  Extra: Using where; Using filesort



*************************** 2. row ***************************
  id: 1
  select_type: SIMPLE
  table: B
  type: ref
  possible_keys: B_c2_ind
  key: B_c2_ind
  key_len: 7
  ref: example.A.c2
  rows: 2
  Extra: Using where; Using index

2.2 MySQL 需要使用Using temporary 臨時表來filesort

如果order by的子句只引用了聯(lián)接中的第一個表,MySQL會先對第一個表進行排序,然后進行聯(lián)接。

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

例如如果我們的排序數(shù)據(jù)如果是兩個(或者更多個) Table 通過 Join所得出的,如下例所示:

root@mysql5601 15:02:  [lijk]> explain select A.* from A,B
where A.id > 2 and A.c2 < 5 and A.c2 = B.c2 order by B.c3\G

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

  id: 1
   select_type: SIMPLE
  table: A
  type: range
  possible_keys: PRIMARY
  key: PRIMARY
  key_len: 4
  ref: NULL
   rows: 3

Extra: Using where; Using temporary; Using filesort

  *************************** 2. row ***************************
  id: 1
  select_type: SIMPLE
  table: B

  type: ref
  possible_keys: B_c2_ind
  key: B_c2_ind
  key_len: 7
  ref: example.A.c2
  rows: 2
  Extra: Using where

實際執(zhí)行過程應該是如下圖所示:

3.優(yōu)化Filesort

當無法避免排序操作時,又該如何來優(yōu)化呢?很顯然,應該盡可能讓 MySQL 選擇使用第二種單路算法來進行排序。這樣可以減少大量的隨機IO操作,很大幅度地提高排序工作的效率。

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

在 MySQL 中,決定使用老式排序算法還是改進版排序算法是通過參數(shù) max_length_for_ sort_data 來決定的。

當所有返回字段的最大長度小于這個參數(shù)值時,MySQL 就會選擇改進后的排序算法,反之,則選擇老式的算法。

所以,如果有充足的內(nèi)存讓MySQL 存放須要返回的非排序字段,就可以加大這個參數(shù)的值來讓 MySQL 選擇使用改進版的排序算法。

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

當內(nèi)存不是很充裕時,不能簡單地通過強行加大上面的參數(shù)來強迫 MySQL 去使用改進版的排序算法,否則可能會造成 MySQL 不得不將數(shù)據(jù)分成很多段,然后進行排序,這樣可能會得不償失。

此時就須要去掉不必要的返回字段,讓返回結果長度適應 max_length_for_sort_data 參數(shù)的限制。

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

增大 sort_buffer_size 并不是為了讓 MySQL選擇改進版的排序算法,而是為了讓MySQL盡量減少在排序過程中對須要排序的數(shù)據(jù)進行分段,因為分段會造成 MySQL 不得不使用臨時表來進行交換排序。

總結

以上為個人經(jīng)驗,希望能給大家一個參考,也希望大家多多支持腳本之家。

相關文章

  • mysql datetime查詢異常問題解決

    mysql datetime查詢異常問題解決

    這篇文章主要介紹了mysql datetime查詢異常問題解決的相關資料,這里對異常進行了詳細的介紹和該如何解決,需要的朋友可以參考下
    2016-11-11
  • mysql中insert?into...select語句優(yōu)化方式

    mysql中insert?into...select語句優(yōu)化方式

    這篇文章主要介紹了mysql中insert?into...select語句優(yōu)化方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教<BR>
    2024-04-04
  • MySQL錯誤Forcing close of thread的兩種解決方法

    MySQL錯誤Forcing close of thread的兩種解決方法

    這篇文章主要介紹了MySQL錯誤Forcing close of thread的兩種解決方法,需要的朋友可以參考下
    2014-11-11
  • MySQL筑基篇之增刪改查操作詳解

    MySQL筑基篇之增刪改查操作詳解

    這篇文章主要和大家講解一下MySQL數(shù)據(jù)庫的增刪改查操作,這里的查詢確切的說應該是初級的查詢,不涉及函數(shù)、分組等模塊,需要的可以參考一下
    2022-07-07
  • mysql?WITH?RECURSIVE語法的具體使用

    mysql?WITH?RECURSIVE語法的具體使用

    WITH RECURSIVE是一個用于創(chuàng)建遞歸查詢的語句,本文主要介紹了mysql?WITH?RECURSIVE語法的具體使用,具有一定的參考價值,感興趣的可以了解一下
    2024-07-07
  • mysqld-nt: Out of memory (Needed 1677720 bytes)解決方法

    mysqld-nt: Out of memory (Needed 1677720 bytes)解決方法

    這篇文章主要介紹了mysqld-nt: Out of memory (Needed 1677720 bytes)解決方法,需要的朋友可以參考下
    2014-12-12
  • MySQL中的datediff()方法和timestampdiff()方法的應用示例小結

    MySQL中的datediff()方法和timestampdiff()方法的應用示例小結

    在MySQL中,DATEDIFF()函數(shù)和TIMESTAMPDIFF()函數(shù)用于計算日期和時間之間的差異,TIMESTAMPDIFF()函數(shù)返回的結果是整數(shù),但你可以通過在計算過程中使用適當?shù)某▉慝@得所需的小數(shù)部分,本文介紹MySQL中的datediff()方法和timestampdiff()方法的應用,感興趣的朋友一起看看吧
    2023-12-12
  • mysql server is running with the --skip-grant-tables option

    mysql server is running with the --skip-grant-tables option

    今天在mysql中新建數(shù)據(jù)庫提示The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement,原來是數(shù)據(jù)中配置的--skip-grant-tables,這樣安全就降低了,這個一般當忘記root密碼的時候需要這樣操作
    2017-07-07
  • 一次mysql的.ibd文件過大處理過程記錄

    一次mysql的.ibd文件過大處理過程記錄

    mysql數(shù)據(jù)庫的每個表都有ibd和frm兩種格式的文件,ibd文件是表的數(shù)據(jù)文件,存放表的數(shù)據(jù),修改文件后綴,無法在MySQL數(shù)據(jù)庫中查詢表數(shù)據(jù),這篇文章主要給大家介紹了關于一次mysql的.ibd文件過大處理過程的相關資料,需要的朋友可以參考下
    2022-06-06
  • MySQL 角色(role)功能介紹

    MySQL 角色(role)功能介紹

    這篇文章主要介紹了MySQL 角色(role)功能的相關資料,幫助大家更好的理解和學習使用MySQL數(shù)據(jù)庫,感興趣的朋友可以了解下
    2021-04-04

最新評論