免费在线a视频-免费在线观看a视频-免费在线观看大片影视大全-免费在线观看的视频-色播丁香-色播基地

MySQL 排序優(yōu)化

:2021年12月04日 博客園- 我們都是小白鼠
分享到:

文章涉及到的 customer 表來源于案例庫 sakila,下載地址為 http://downloads.mysql.com/docs/sakila-db.zip,另外文章演示的 Demo 基于 MySQL Community Server 8.0.19 版本。

1|0寫在前面

文章涉及到的 customer 表來源于案例庫 sakila,下載地址為 http://downloads.mysql.com/docs/sakila-db.zip,另外文章演示的 Demo 基于 MySQL Community Server 8.0.19 版本。

2|0MySQL 排序方式基本可以分為兩種

  • 運用索引天然排序的特征直接返回排好序的數(shù)據(jù)

  • 通過對返回數(shù)據(jù)進行排序,即 FileSort 排序

所有不是通過索引直接返回排序結(jié)果的排序都叫 FileSort 排序。FileSort 并不代表通過磁盤文件進行排序,而只是說進行了一個排序操作,至于 排序操作是否使用了磁盤文件取決于 MySQL 服務(wù)器對排序參數(shù)的設(shè)置和需要排序數(shù)據(jù)的大小。

3|0EXPLAIN 排序分析

CREATE TABLE `customer` (
    `customer_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `store_id` TINYINT UNSIGNED NOT NULL,
    `first_name` VARCHAR ( 45 ) NOT NULL,
    `last_name` VARCHAR ( 45 ) NOT NULL,
    `email` VARCHAR ( 50 ) DEFAULT NULL,
    `address_id` SMALLINT UNSIGNED NOT NULL,
    `active` TINYINT ( 1 ) NOT NULL DEFAULT '1',
    `create_date` datetime NOT NULL,
    `last_update` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY ( `customer_id` ),
    KEY `idx_fk_store_id` ( `store_id` ),
    KEY `idx_fk_address_id` ( `address_id` ),
    KEY `idx_last_name` ( `last_name` ),
    KEY `idx_storeid_email` ( `store_id`, `email` ),
     CONSTRAINT `fk_customer_address` FOREIGN KEY ( `address_id` ) REFERENCES `address`  ( `address_id` ) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT `fk_customer_store`  FOREIGN KEY ( `store_id` ) REFERENCES `store` ( `store_id` ) ON DELETE RESTRICT ON UPDATE CASCADE 
) ENGINE = INNODB AUTO_INCREMENT = 600 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;
CREATE DEFINER = `root` @`%` TRIGGER `customer_create_date` BEFORE INSERT ON `customer` FOR EACH ROW
SET NEW.create_date = NOW();

3|1主鍵索引

根據(jù) customer_id 進行排序。因為 customer_id 是主鍵,記錄都是按照主鍵排好序的,所以無需進行額外的排序操作,直接返回所有數(shù)據(jù)即可。

EXPLAIN SELECT * FROM customer ORDER BY customer_id;

3|2普通索引

由于整張表的所有記錄默認是根據(jù) customer_id 排好序的,當然 store_id 索引樹也是排好序的,但是僅僅是對 store_id 這個字段來說是排好序的,這里的需求是整張表按 store_id 進行排序,所以涉及到了 FileSort 排序。

EXPLAIN SELECT * FROM customer ORDER BY store_id;

EXPLAIN SELECT store_id FROM customer ORDER BY store_id;

3|3聯(lián)合索引

store_id , email 這兩個字段是有聯(lián)合索引 idx_storeid_email 的,只查詢 store_id 和 email 兩個字段,直接通過聯(lián)合索引所在的 B+ 樹返回查詢數(shù)據(jù)(該索引樹先根據(jù) store_id 字段先進行排序,然后再根據(jù) email 字段排序好的),所以這里的查詢結(jié)果就是排序好的。

EXPLAIN SELECT store_id, email FROM customer ORDER BY store_id;

和上面不同的是,排序的字段是 email,導(dǎo)致 FileSort 排序的原因是聯(lián)合索引 idx_storeid_email 的是先根據(jù) store_id 字段先進行排序,然后再根據(jù) email 字段排序好的,如果直接使用 email 排序,則無法使用 idx_storeid_email 索引樹排好的順序,需要先從 idx_storeid_email 索引樹上將 store_id 和 email 這兩個字段查出來然后再根據(jù) email 字段進行排序。

EXPLAIN SELECT store_id, email FROM customer ORDER BY email;

按照聯(lián)合索引順序多字段排序

EXPLAIN SELECT store_id, email FROM customer ORDER BY store_id, email;

不按照聯(lián)合索引多字段順序進行排序

EXPLAIN SELECT store_id, email FROM customer ORDER BY email, store_id;

這里注意,和 SELECT 的字段順序沒有關(guān)系

EXPLAIN SELECT email, store_id FROM customer ORDER BY store_id, email;

idx_storeid_email 索引是按照 store_id 和 email 升序進行排序的,這里如果 email 按照降序來排序,那前面 store_id 升序排序可以繼續(xù)使用索引排好序,后面 email 降序是要進行再排序的。

EXPLAIN SELECT store_id, email FROM customer ORDER BY store_id ASC, email DESC;

固定 store_id = 1 情況下對 email 字段進行排序,使用 idx_storeid_email 索引即可

EXPLAIN SELECT store_id, email FROM customer WHERE store_id  = 1 ORDER BY email;

where 條件先進行 store_id 范圍查詢導(dǎo)致 ORDER BY email 字段無法使用 idx_storeid_email 索引進行排序。

EXPLAIN SELECT store_id, email FROM customer WHERE store_id  >= 1 AND store_id <= 3 ORDER BY email;

4|0ORDER BY 可能出現(xiàn) FileSort 的幾種情況

無法直接利用索引樹排好序的,基本都會出現(xiàn) Using FileSort。常見的情況如下:

1、一般全表數(shù)據(jù)默認只會按照主鍵進行排好序,所以,如果需要 SELECT * 時,或者 SELECT 的字段沒有建立索引時如果不是按照主鍵進行排序,那么是要再排序的。

2、對于聯(lián)合索引來說,索引樹是按照多個字段的升序排列,如果你 order by 的時候涉及到多字段升序和降序混用,會導(dǎo)致無法利用索引的天然排序,或者是只能利用到一部分,另一部分需要再排序。

3、聯(lián)合索引情況下,order by 多字段排序的字段左右順序和聯(lián)合索引的字段左右順序不一致導(dǎo)致 FileSort。

4、聯(lián)合索引情況下,where 字段和 order by 字段的左右順序和聯(lián)合索引字段左右順序或者 where 字段出現(xiàn)范圍查詢都可能導(dǎo)致 FileSort。

5|0排序的優(yōu)化

從上面案例我們大致能了解到,要想優(yōu)化排序,其實就是盡量使用索引排好的序,減少再排序,也就是盡量減少 Using FileSort 的出現(xiàn)。下面我們來了解一下排序的基本原理。

5|1全字段排序和 rowid 排序

1|0全字段排序

如果 MySQL 認為內(nèi)存足夠大,會優(yōu)先選擇全字段排序,把需要的字段都放到 sort_buffer 中,這樣排序后就會直接從內(nèi)存里面返回查詢結(jié)果了,不用再回到原表去取數(shù)據(jù)。

這也就體現(xiàn)了 MySQL 的一個設(shè)計思想:如果內(nèi)存夠,就要多利用內(nèi)存,盡量減少磁盤訪問。

1|0rowid 排序

上面的全字段排序涉及到一個問題,就是如果查詢要返回的字段很多的話,那么 sort_buffer 里面要放的字段數(shù)太多,這樣內(nèi)存里能夠同時放下的行數(shù)很少,比如:符合條件的記錄一共 1000 行,內(nèi)存只能放 500 行,那么還有 500 行就要水平拆分放入多個臨時文件進行排序(究竟拆分成多少個臨時文件排序,也涉及到相關(guān)算法,這里就不深入研究了),所以如果單行很大,這個方法效率不夠好。那么,如果 MySQL 認為排序的單行長度太大會怎么做呢?接下來,我來修改一個參數(shù),讓 MySQL 采用另外一種算法。

SET max_length_for_sort_data = 16;

max_length_for_sort_data,是 MySQL 中專門控制用于排序的行數(shù)據(jù)的長度的一個參數(shù)。它的意思是,如果單行的長度超過這個值,MySQL 就認為單行太大,要換一個排序算法。

假設(shè)現(xiàn)在單行超過了 max_length_for_sort_data,那么就會采用 rowid 進行排序,首先會將需要排序的字段和主鍵取出來到內(nèi)存中進行排序,排好序之后,再根據(jù)主鍵進行回表查出其他字段直接返回給客戶端。注意,這里回表查詢的數(shù)據(jù)不會存放到內(nèi)存中,而是直接返回給客戶端。這樣就緩解了全字段排序可能導(dǎo)致很多記錄磁盤排序的問題。但是 rowid 排序同樣也引入了另一個問題,那就是回表,如果回表過多也會導(dǎo)致性能下降。

MySQL 8.0.19 版本默認 max_length_for_sort_data 大小為 4096 字節(jié),即 4kb;默認 sort_buffer_size 大小為 262144 字節(jié),即 256kb。

6|0總結(jié)

MySQL 通過 max_length_for_sort_data 的大小單行所有列的總大小來判斷使用哪種排序算法。如果 max_length_for_sort_data 設(shè)置足夠大,那么會使用一次掃描算法;但是,一次性性將很多行數(shù)據(jù)都加載到
sort_buffer 中,如果 sort_buffer 放不下,就會導(dǎo)致大量記錄會使用到磁盤文件排序。此時磁盤負載就會過高,內(nèi)存中排序的記錄就會變少,相應(yīng)的 CPU 利用率就會過低。如果設(shè)置的很小,則會使用 rowid 排序,也會導(dǎo)致回表過多,性能過差。

比較權(quán)衡的做法是,適當加大 sort_buffer_size 排序區(qū),盡量讓排序在內(nèi)存中完成,而不是在磁盤文件中進行排序;當然也不能無限加大 sort_buffer_size 排序區(qū),因為 sort_buffer_size 參數(shù)是每個線程獨占的,設(shè)置過大可能會導(dǎo)致服務(wù)器 SWAP 嚴重,要考慮數(shù)據(jù)庫活躍連接數(shù)和服務(wù)器內(nèi)存的大小來適當設(shè)置排序區(qū)

盡量只使用必要的字段,SELECT 具體的字段名稱,而不是 SELECT * 選擇所有字段,這樣可以減少排序區(qū)的使用,提高 SQL 性能。

1|0參考

《深入淺出 MySQL 數(shù)據(jù)庫開發(fā)、優(yōu)化與管理維護第 2 版》

《極客時間· MySQL 實戰(zhàn) 45 講》

[我要糾錯]
文:王振袢&發(fā)表于江蘇
關(guān)鍵詞: 在前面 文章 及到 customer 來源于

來源:本文內(nèi)容搜集或轉(zhuǎn)自各大網(wǎng)絡(luò)平臺,并已注明來源、出處,如果轉(zhuǎn)載侵犯您的版權(quán)或非授權(quán)發(fā)布,請聯(lián)系小編,我們會及時審核處理。
聲明:江蘇教育黃頁對文中觀點保持中立,對所包含內(nèi)容的準確性、可靠性或者完整性不提供任何明示或暗示的保證,不對文章觀點負責(zé),僅作分享之用,文章版權(quán)及插圖屬于原作者。

點個贊
0
踩一腳
0

您在閱讀:MySQL 排序優(yōu)化

Copyright?2013-2024 JSedu114 All Rights Reserved. 江蘇教育信息綜合發(fā)布查詢平臺保留所有權(quán)利

蘇公網(wǎng)安備32010402000125 蘇ICP備14051488號-3技術(shù)支持:南京博盛藍睿網(wǎng)絡(luò)科技有限公司

南京思必達教育科技有限公司版權(quán)所有   百度統(tǒng)計

主站蜘蛛池模板: 亚洲w码| 成在线人永久免费播放视频 | 我要看一级黄色毛片 | 色综合综合 | 久草视频在线资源 | 视频一区二区中文字幕 | 黄色aqq | 看看免费a一片欧 | xxxx性bbbb| 日韩在线第三页 | 久久亚洲人成国产精品 | 一级美国乱色毛片 | 人人草人人澡 | 亚洲欧美日韩高清一区二区三区 | 欧美特级视频 | 在线播放成人高清免费视频 | 成人一级黄色片 | 成人网18免费软件 | 免费人成视网站在线不卡 | 一个人免费观看在线视频www | 成人国产免费 | 高清国产精品入口麻豆 | 欧美freesex呦交6_10 | 中文国产成人精品久久无广告 | 国产无内制服肉丝精品视频 | 欧美顶级黄色大片免费 | 91免费精品国偷自产在线在线 | 国产日韩欧美911在线观看 | 日韩欧美中文在线 | 免费一级e一片在线播放 | 日本一区二区三区不卡在线视频 | 99成人国产精品视频 | 久久精品国产2020观看福利色 | 欧美大黄 | 一本大道道无香蕉综合在线 | 亚洲欧美丝袜综合精品第一页 | 国产色噜噜 | 免费视频精品 | 日本黄色视屏 | 国产一级特黄高清免费大片 | 一97日本道伊人久久综合影院 |
最熱文章
最新文章
  • 阿里云上云鉅惠,云產(chǎn)品享最低成本,有需要聯(lián)系,
  • 卡爾蔡司鏡片優(yōu)惠店,鏡片價格低
  • 蘋果原裝手機殼