偷偷摘套内射激情视频,久久精品99国产国产精,中文字幕无线乱码人妻,中文在线中文a,性爽19p

全面透徹,MySQL 正確的慢查詢處理姿勢

數(shù)據(jù)庫 MySQL
全文總結(jié)一下,其實(shí)就是我們要學(xué)會(huì)用數(shù)據(jù)庫的要求方式來執(zhí)行SQL。即要寫好應(yīng)用查詢SQL,必須要結(jié)合良好的數(shù)據(jù)結(jié)構(gòu)和合理的索引設(shè)計(jì)才可以。

發(fā)現(xiàn)的一些問題

  • 問題1

在過去的半年時(shí)間里,研發(fā)團(tuán)隊(duì)內(nèi)部嘗試抓了一波兒慢查詢SQL跟進(jìn)處理率。發(fā)現(xiàn)有些同學(xué)對于慢查詢處理的思路就是看看有沒有用到索引,沒有用到就試圖加一個(gè),實(shí)在不行就甩鍋給這種情況是歷史設(shè)計(jì)問題或者自行判定為用戶特殊操作下觸發(fā)的小概率事件,隨即便申請豁免掉...   其實(shí)問題沒有根本上解決。

  • 問題2

還有就是網(wǎng)絡(luò)上經(jīng)??梢钥吹揭恍╊愃七@樣的文章:

“慢SQL性能優(yōu)化大全”

“慢SQL性能優(yōu)化看這篇就夠了”...  

其實(shí)內(nèi)容大同小異,要么建議加索引,要么建議重寫SQL....

怎么說呢?知識點(diǎn)是對的,但不全面,這個(gè)很容易誤導(dǎo)新同學(xué),哈哈哈。

本文初衷

在業(yè)務(wù)項(xiàng)目發(fā)展過程中,我們常常會(huì)面對要處理 MySQL 慢查詢問題,那我們應(yīng)該如何分析解決問題呢?

部分同學(xué)在處理MySQL慢查詢時(shí)候主要思路是加索引來解決,確實(shí)加索引是一個(gè)很好的解決問題的手段,但不是全部。既然慢查詢作為問題,那就需要明確問題發(fā)生原因,和解決問題路徑分析, 授人以魚不如授人以漁,讓我們一起來解鎖 ?? 下MySQL處理慢查詢的正確姿勢。

本文計(jì)劃主要讓大家搞明白查詢SQL為什么會(huì)變慢,廢話不多說,直接開干~

寫在前面

在業(yè)務(wù)項(xiàng)目發(fā)展過程中,我們常常會(huì)面對要處理 MySQL 慢查詢問題,那我們應(yīng)該如何分析解決問題呢?

部分同學(xué)在處理MySQL慢查詢時(shí)候主要思路是加索引來解決,確實(shí)加索引是一個(gè)很好的解決問題的手段,但不是全部。既然慢查詢是問題,那就需要明確問題發(fā)生原因,和解決問題路徑分析。我們一起來get下MySQL慢查詢的正確姿勢。

一、查詢SQL執(zhí)行到底經(jīng)歷了什么?

首先需要明確:一個(gè)查詢SQL的執(zhí)行到底經(jīng)歷了什么?

圖片圖片

數(shù)據(jù)庫執(zhí)行SQL的大致流程如下:

  • 建立與MySQL服務(wù)器連接(基礎(chǔ))
  • 客戶端發(fā)送查詢SQL到數(shù)據(jù)庫,數(shù)據(jù)庫驗(yàn)證是否有執(zhí)行的權(quán)限
  • MySQL服務(wù)器先檢查查詢緩存,如果命中了緩存,則立即返回存儲(chǔ)在緩存中的結(jié)果,否則繼續(xù)流轉(zhuǎn);
  • MySQL服務(wù)器語法解析器,進(jìn)行詞法與語法分析,預(yù)處理
  • 流轉(zhuǎn)至查詢優(yōu)化器生成執(zhí)行計(jì)劃
  • 根據(jù)生成的執(zhí)行計(jì)劃,調(diào)用存儲(chǔ)引擎暴露的API來執(zhí)行查詢
  • 將查詢執(zhí)行結(jié)果返回給客戶端
  • 關(guān)閉MySQL連接

具體執(zhí)行過程可能會(huì)因MySQL服務(wù)器具體配置和執(zhí)行場景有一些差異。

1)如未開啟應(yīng)用查詢緩存,則直接忽略查詢緩存的檢查;

2)執(zhí)行過程中,如同時(shí)對于被掃描的行可能加鎖,同時(shí)也可能會(huì)被其他sql阻塞

二、查詢SQL為什么會(huì)慢?

我們可以把查詢SQL執(zhí)行看做是一個(gè)任務(wù)的話,那它是由一些列子任務(wù)組成的,每個(gè)子任務(wù)都存在一定的時(shí)間消耗。通常情況下,導(dǎo)致慢查詢最根本的問題就是需要訪問的數(shù)據(jù)太多,導(dǎo)致查詢不可避免的需要篩選大量的數(shù)據(jù)。

面對慢查詢,我們需要注意以下兩點(diǎn):

1)查詢了過多不需要的數(shù)據(jù)

2)掃描了額外的記錄

2.1 查詢了過多不需要的數(shù)據(jù)

MySQL并不是只返回需要的數(shù)據(jù),實(shí)際上會(huì)返回全部結(jié)果集再進(jìn)行計(jì)算。

尤其是多表關(guān)聯(lián)查詢 select * 的情況,我們是不是真的需要全部的列呢?如果不是,那我們直接指定對應(yīng)字段就好了。

例如我們要查詢用戶關(guān)聯(lián)訂單下的商品信息,如下所示:

SELECT *
FROM users
  LEFT JOIN orders ON orders.user_id = users.user_id
  LEFT JOIN goods ON goods.good_id = orders.good_id
WHERE users.name = 'zhangsan';

這將返回三個(gè)表的全部數(shù)據(jù)列,可以調(diào)整為僅取需要的列:

SELECT goods.title, goods.description
FROM users
  LEFT JOIN orders ON orders.user_id = users.user_id
  LEFT JOIN goods ON goods.good_id = orders.good_id
WHERE users.name = 'zhangsan';

取出全部列,會(huì)讓優(yōu)化器無法完成索引覆蓋掃描這類優(yōu)化,還會(huì)為服務(wù)器帶來額外的I/O、內(nèi)存和CPU的消耗。

2.2 掃描了額外的記錄

此種情況大部分屬于索引應(yīng)用不當(dāng)造成的(包括:該建的索引沒有建,或者未應(yīng)用到最佳索引)。

示例表結(jié)構(gòu)如下:

CREATE TABLE `test_table` (
  `name` varchar(32) DEFAULT NULL,
  `desc` varchar(32) DEFAULT NULL,
  `age` int(16) DEFAULT NULL,
  `id` bigint(11) DEFAULT NULL,
  KEY `idx_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

存在索引 `idx_age` 的情況下,查詢執(zhí)行計(jì)劃如下:

EXPLAIN SELECT * FROM test_table WHERE age = 10;

圖片圖片

預(yù)估訪問1行數(shù)據(jù)即可命中數(shù)據(jù),如刪除有效索引 `idx_age` 后則會(huì)變成全表掃描(ALL),預(yù)估需要掃描121524條記錄才能完成這個(gè)查詢,如下圖所示:

圖片圖片

三、如何定位問題呢?

通過梳理 MySQL中的 SQL執(zhí)行過程我們發(fā)現(xiàn),任何流程的執(zhí)行都存在其執(zhí)行環(huán)境和規(guī)則,主要導(dǎo)致慢查詢最根本的問題就是需要訪問的數(shù)據(jù)太多,導(dǎo)致查詢不可避免的需要篩選大量的數(shù)據(jù)。

如果將MySQL慢查詢作為一個(gè)問題來拆解分析的話,以上內(nèi)容算是問題分析,那接下來開始問題定位和問題解決。

圖片圖片

發(fā)現(xiàn)了慢查詢之后,關(guān)于如何定位問題發(fā)生原因,最常用的方法就是利用EXPLAIN關(guān)鍵字模擬查詢優(yōu)化器執(zhí)行查詢SQL,從而知道MySQL是如何處理你的查詢SQL,通過執(zhí)行計(jì)劃來分析性能瓶頸。

通常我們使用EXPLAIN,會(huì)得到如下下的執(zhí)行計(jì)劃信息:

圖片圖片

關(guān)于各字段含義,大家可以通過檢索自行了解,在此就不再過多贅述。

關(guān)于定位分析問題,關(guān)鍵看如下幾點(diǎn):

1)select_type

表示查詢類型,用于區(qū)別普通查詢、聯(lián)合查詢、子查詢等復(fù)雜查詢。

2)type

顯示查詢使用類型,從好到差依次為:system > const > eq_ref > ref > range > index > all

3)possible_keys 和 key

分別指可能應(yīng)用的索引和實(shí)際應(yīng)用的索引。

注意:查詢中若使用了覆蓋索引(select 后要查詢的字段剛好和創(chuàng)建的索引字段完全相同),則該索引僅出現(xiàn)在key列表中。

4)rows

大致估算出找到所需記錄所需要讀取的行數(shù)(從效率上來講,數(shù)值越小越好)

5)Extra

重要的額外信息。包含MySQL解決查詢的詳細(xì)信息,也是關(guān)鍵參考項(xiàng)之一。

四、幾種實(shí)用解決方案

我們通過EXPLAIN關(guān)鍵字模擬查詢優(yōu)化器執(zhí)行查詢SQL,發(fā)現(xiàn)了慢查詢問題原因,那看看如何才能有效解決呢?

推進(jìn)幾種較為實(shí)用的解決方案給大家。

4.1 優(yōu)化數(shù)據(jù)結(jié)構(gòu)

4.1.1 選擇索引的數(shù)據(jù)類型

MySQL支持很多數(shù)據(jù)類型,選擇合適的數(shù)據(jù)類型存儲(chǔ)數(shù)據(jù)對性能有很大的影響。

通常來說,可以遵循以下一些指導(dǎo)原則:

(1)越小的數(shù)據(jù)類型通常更好:越小的數(shù)據(jù)類型通常在磁盤、內(nèi)存和CPU緩存中都需要更少的空間,處理起來更快。

(2)簡單的數(shù)據(jù)類型更好:整型數(shù)據(jù)比起字符,處理開銷更小,因?yàn)樽址谋容^更復(fù)雜。在MySQL中,應(yīng)該用內(nèi)置的日期和時(shí)間數(shù)據(jù)類型,而不是用字符串來存儲(chǔ)時(shí)間;以及用整型數(shù)據(jù)類型存儲(chǔ)IP地址。

(3)盡量避免NULL:應(yīng)該指定列為NOT NULL,除非你想存儲(chǔ)NULL。在MySQL中,含有空值的列很難進(jìn)行查詢優(yōu)化,因?yàn)樗鼈兪沟盟饕?、索引的統(tǒng)計(jì)信息以及比較運(yùn)算更加復(fù)雜。你應(yīng)該用0、一個(gè)特殊的值或者一個(gè)空串代替空值。

4.1.2 范式與反范式

  • 范式化

范式化模型要求滿足下面三大范式:

1)數(shù)據(jù)庫表中每個(gè)字段只包含最小的信息屬性,不能再進(jìn)行細(xì)化分解;

2)(在滿足1的基礎(chǔ)上)模型含有主鍵,非主鍵字段依賴主鍵;

比如用戶這個(gè)模型,它的主鍵是用戶ID,那么用戶模型其它字段都應(yīng)該依賴于用戶ID。;

如商品ID和用戶沒有直接關(guān)系,則這個(gè)屬性不應(yīng)該放到用戶模型而應(yīng)該放到“用戶-商品”中間表。

3)(在滿足2的基礎(chǔ)上)模型非主鍵字段不能相互依賴。

訂單表(訂單編號,訂購日期,顧客編號,顧客姓名,……);

初看該表沒有問題,滿足第二范式,每列都和主鍵列”訂單編號”相關(guān)。

再細(xì)看你會(huì)發(fā)現(xiàn)“顧客姓名”和“顧客編號”相關(guān),“顧客編號”和“訂單編號”又相關(guān),最后經(jīng)過傳遞依賴,“顧客姓名”也和“訂單編號”相關(guān)。

為了滿足第三范式,應(yīng)去掉“顧客姓名”列,放入客戶表中。

  • 反范式化

反范式化模型即不滿足范式化的模型。主要是為了性能和效率的考慮適當(dāng)?shù)倪`反范式化設(shè)計(jì)要求,允許存在少量的數(shù)據(jù)冗余,即以空間換時(shí)間。

4.1.3 小結(jié)

可見一個(gè)良好而實(shí)用的數(shù)據(jù)模型往往是依賴于具體的需求場景的,在設(shè)計(jì)數(shù)據(jù)模型之前,仔細(xì)分析需求場景,不僅能提高效率,也能有效規(guī)避后期可能遇到的一些意外麻煩。

范式化設(shè)計(jì)和反范式化設(shè)計(jì)的優(yōu)劣對比如下:

1、范式化可以盡量的減少數(shù)據(jù)冗余;

2、范式化的更新操作比反范式化更快;

3、范式化的表通常比反范式化的表要??;

4、反范式化減少表的關(guān)聯(lián);

5、反范式化相比范式化可以更好的對索引進(jìn)行優(yōu)化,例如使用覆蓋索引。

關(guān)于數(shù)據(jù)庫范式與反范式設(shè)計(jì),詳情可參考我之前的一篇文章:數(shù)據(jù)庫范式與反范式設(shè)計(jì),是一門藝術(shù)。

4.2 應(yīng)用索引策略

索引(MySQL中也被稱為“鍵Key”),是存儲(chǔ)引擎用于快速找到記錄的一種數(shù)據(jù)結(jié)構(gòu)。索引對于良好的性能非常關(guān)鍵,尤其當(dāng)表中的數(shù)據(jù)量越來越大時(shí),索引對性能的影響愈發(fā)重要(不恰當(dāng)?shù)乃饕龑?huì)隨數(shù)據(jù)量增大時(shí),性能急劇下降)。

舉例如下情況:

假設(shè)數(shù)據(jù)庫中一個(gè)表有10^6條記錄,DBMS的頁面大小為4K(約可存儲(chǔ)100條記錄)。

如果沒有索引,查詢將對整個(gè)表進(jìn)行掃描,最壞的情況下,如果所有數(shù)據(jù)頁都不在內(nèi)存,需要讀取10^4個(gè)頁面,如果這10^4個(gè)頁面在磁盤上隨機(jī)分布,需要進(jìn)行10^4次I/O,假設(shè)磁盤每次I/O時(shí)間為10ms(忽略數(shù)據(jù)傳輸時(shí)間),則總共需要100s(但實(shí)際上要好很多很多)。

如果對之建立B-Tree索引,則只需要進(jìn)行l(wèi)og100(10^6)=3次頁面讀取,最壞情況下耗時(shí)30ms。這就是索引帶來的效果。

了解了索引的優(yōu)點(diǎn)之后,其實(shí)正確的創(chuàng)建和使用索引是實(shí)現(xiàn)高性能查詢的基礎(chǔ)。

可以利用B-Tree索引進(jìn)行全關(guān)鍵字、關(guān)鍵字范圍和關(guān)鍵字前綴查詢,當(dāng)然,如果想使用索引,必須保證按索引的最左邊前綴(leftmost prefix of the index)來進(jìn)行查詢。

4.2.1 最左邊前綴主要規(guī)則

  • 匹配全值(Match the full value):對索引中的所有列都指定具體的值。例如,上圖中索引可以幫助你查找出生于1960-01-01的Cuba Allen。
  • 匹配最左前綴(Match a leftmost prefix):你可以利用索引查找last name為Allen的人,僅僅使用索引中的第1列。
  • 匹配列前綴(Match a column prefix):例如,你可以利用索引查找last name以J開始的人,這僅僅使用索引中的第1列。
  • 匹配值的范圍查詢(Match a range of values):可以利用索引查找last name在Allen和Barrymore之間的人,僅僅使用索引中第1列。
  • 匹配部分精確而其它部分進(jìn)行范圍匹配(Match one part exactly and match a range on another part):可以利用索引查找last name為Allen,而first name以字母K開始的人。
  • 僅對索引進(jìn)行查詢(Index-only queries):如果查詢的列都位于索引中,則不需要讀取元組的值。

由于B-樹中的節(jié)點(diǎn)都是順序存儲(chǔ)的,所以可以利用索引進(jìn)行查找(找某些值),也可以對查詢結(jié)果進(jìn)行ORDER BY。

當(dāng)然,使用B-tree索引有以下一些限制:

  • 查詢必須從索引的最左邊的列開始。關(guān)于這點(diǎn)已經(jīng)提了很多遍了。例如你不能利用索引查找在某一天出生的人。
  • 不能跳過某一索引列。例如,你不能利用索引查找last name為Smith且出生于某一天的人。
  • 存儲(chǔ)引擎不能使用索引中范圍條件右邊的列。例如,如果你的查詢語句為WHERE lastname="Smith" AND firstname LIKE 'J%' AND dob='1976-12-23',則該查詢只會(huì)使用索引中的前兩列,因?yàn)長IKE是范圍查詢。

4.2.2 聚簇索引

聚簇索引保證關(guān)鍵字的值相近的元組存儲(chǔ)的物理位置也相同(所以字符串類型不宜建立聚簇索引,特別是隨機(jī)字符串,會(huì)使得系統(tǒng)進(jìn)行大量的移動(dòng)操作),且一個(gè)表只能有一個(gè)聚簇索引。因?yàn)橛纱鎯?chǔ)引擎實(shí)現(xiàn)索引,所以,并不是所有的引擎都支持聚簇索引。目前,只有solidDB和InnoDB支持。

InnoDB對主鍵建立聚簇索引。如果你不指定主鍵,InnoDB會(huì)用一個(gè)具有唯一且非空值的索引來代替。如果不存在這樣的索引,InnoDB會(huì)定義一個(gè)隱藏的主鍵,然后對其建立聚簇索引。

4.3 查詢緩存

MySQL查詢緩存會(huì)保存查詢返回的完整結(jié)果。當(dāng)查詢命中緩存,MySQL會(huì)立刻返回結(jié)果,而跳過了后續(xù)解析、優(yōu)化以及執(zhí)行階段,會(huì)有效提升查詢性能。

但是查詢緩存不是銀彈,它也會(huì)存在一些問題。

4.3.1 查詢緩存注意事項(xiàng)

1)緩存情況嚴(yán)格

存在一些不確定函數(shù)情況無法使用查詢緩存,如:NOW()、CURRENT_DATE() 等類似的函數(shù);

超過 query_cache_size (設(shè)置查詢緩存空間大小)的查詢結(jié)果無法被緩存;

同時(shí)大小寫敏感,只有字符串相等情況下查詢SQL才使用相同緩存。

-- 不會(huì)使用同一個(gè)緩存
select name from users where id = 1;
SELECT name FROM users WHERE id = 1;

2)緩存易失效

假如緩存過查詢結(jié)果,但是由于查詢緩存設(shè)置內(nèi)存不足,新緩存加入時(shí)MySQL會(huì)將某些緩存逐出,導(dǎo)致后續(xù)查詢未命中。同時(shí)數(shù)據(jù)結(jié)構(gòu)及數(shù)據(jù)修改,內(nèi)存不足,緩存碎片都會(huì)導(dǎo)致緩存失效。

4.3.2 小結(jié)

查詢緩存對應(yīng)用程序完全透明,應(yīng)用程序無需關(guān)心MySQL是通過查詢緩存返回的還是實(shí)際執(zhí)行返回的結(jié)果。但隨著目前服務(wù)器性能越來越強(qiáng),查詢緩存被發(fā)現(xiàn)是一個(gè)影響服務(wù)器擴(kuò)展性的因素,它很可能成為整個(gè)服務(wù)器的資源競爭點(diǎn),大家采用生產(chǎn)環(huán)境開啟應(yīng)用時(shí)候一定要慎重考量。

4.4 重構(gòu)查詢方式

優(yōu)化慢查詢時(shí)候,我們可以轉(zhuǎn)換下思路,我們的目標(biāo)是找到一個(gè)更優(yōu)的方法獲取時(shí)間需要的結(jié)果,而不是一定從MySQL獲取一模一樣的結(jié)果集。重構(gòu)查詢的技巧很有必要。

4.4.1 復(fù)雜查詢拆分

將一個(gè)復(fù)雜查詢拆分多個(gè)簡單查詢,考慮是否需要將一個(gè)復(fù)雜查詢拆分為多個(gè)簡單查詢。

實(shí)際開發(fā)過程中,大家往往會(huì)強(qiáng)調(diào)數(shù)據(jù)庫層完成盡可能多的工作,這樣做的初衷是認(rèn)為網(wǎng)絡(luò)通信、查詢解析和優(yōu)化是一件代價(jià)很高的事情,其實(shí)MySQL從設(shè)計(jì)上讓連接和斷開都很輕量級,同時(shí)在返回一個(gè)小查詢結(jié)果方面很高效。況且目前網(wǎng)絡(luò)速度也比之前快很多,無論是帶寬還是延遲。

對于大查詢我們要“分而治之”,將大查詢切分成多個(gè)小查詢。不過在一次查詢能夠勝任的情況下還拆成多個(gè)獨(dú)立查詢就不明智了。

例如:做數(shù)據(jù)庫做10次查詢,每次返回一行記錄。

4.4.2 分解關(guān)聯(lián)查詢

將關(guān)聯(lián)查詢進(jìn)行分解,對每一個(gè)表進(jìn)行一次單表查詢,然后將結(jié)果在應(yīng)用程序中進(jìn)行關(guān)聯(lián)。

例如:

SELECT *
FROM users
LEFT JOIN orders ON orders.user_id = users.user_id
LEFT JOIN goods ON goods.good_id = orders.good_id
WHERE users.name = 'zhangsan';

以上查詢可以分解成下面的查詢來代替:

SELECT * FROM users WHERE users.name = 'zhangsan';
SELECT * FROM orders WHERE orders.user_id = 103;
SELECT * FROM goods WHERE goods.good_id IN  (123, 456, 789);

為什么要這樣做呢?看起來好像沒有什么好處,而且返回?cái)?shù)據(jù)結(jié)果也是一致的。實(shí)際上利用分解查詢的方式來重構(gòu)查詢有很大的優(yōu)勢,主要表現(xiàn)為:

  • 將查詢分解后,執(zhí)行單個(gè)查詢可減少鎖的競爭;
  • 應(yīng)用層做關(guān)聯(lián),更容易對數(shù)據(jù)庫進(jìn)行拆分,更易于做到高性能和可擴(kuò)展;
  • 減少冗余記錄的查詢(在應(yīng)用層做關(guān)聯(lián),表示對某條記錄應(yīng)用只需要查詢一次,而在數(shù)據(jù)庫中做關(guān)聯(lián)查詢,則可能需要重復(fù)訪問一部分?jǐn)?shù)據(jù)。)

五、高性能查詢難題優(yōu)化總結(jié)

如果將MySQL慢查詢作為一個(gè)問題來拆解分析的話,之前內(nèi)容算是問題分析、問題定位和解決,那現(xiàn)在來收下尾,聊聊MySQL慢查詢問題解決經(jīng)驗(yàn)總結(jié)。

圖片圖片

廢話不多說,直接開干~

我們來總結(jié)一下,應(yīng)該如何處理高性能查詢難題?

假如把高性能查詢比作一個(gè)“難題”,它其實(shí)是包括多個(gè)子難題在內(nèi),共同作用的結(jié)果。

今天我們來歸納總結(jié)下,主要包括以下幾類:

5.1 數(shù)據(jù)結(jié)構(gòu)優(yōu)化

良好的schema設(shè)計(jì)原則是普遍適用的,但是MySQL有他自己的實(shí)現(xiàn)細(xì)節(jié)要注意,概況來講,盡可能保持任何東西小而簡單總是好的。

主要有以下簡單的原則值得你去考慮使用:

  • 盡量避免過度設(shè)計(jì)
  • 使用小而簡單的合適數(shù)據(jù)類型,盡可能避免使用null
  • 盡量使用相同的數(shù)據(jù)類型存儲(chǔ)相似或者相關(guān)的值
  • 注意可變長字符串,其在臨時(shí)表和排序時(shí)可能按最大長度分配內(nèi)存
  • 盡量使用整形定義標(biāo)識符

5.2 索引設(shè)計(jì)優(yōu)化

常見的B-Tree索引,按照順序存儲(chǔ)數(shù)據(jù),所以MySQL可以用來做ORDER BY 和 GROUP BY操作。因?yàn)閿?shù)據(jù)是有序的,所以便于將相關(guān)的列值都存儲(chǔ)在一起。由于索引中存儲(chǔ)了實(shí)際的列值,所以一些查詢只通過索引就能夠完成查詢(如:聚簇索引)。

根據(jù)索引的特性,總結(jié)索引的優(yōu)點(diǎn)有如下幾點(diǎn):

  • 減少服務(wù)器需要掃描的數(shù)據(jù)量;
  • 幫助服務(wù)器避免排序和臨時(shí)表;
  • 將隨機(jī)I/O變?yōu)轫樞騃/O。

編寫查詢語句時(shí)候應(yīng)該注意盡可能選擇合適的索引,以避免單行查找,盡可能使用索引覆蓋。

根據(jù)執(zhí)行計(jì)劃依次掃描相關(guān)表中的行,不在數(shù)據(jù)緩沖區(qū)的走IO存儲(chǔ)引擎掃描表的性能消耗參考下面的list,消耗從大到?。?/p>

全表掃描>全索引掃描>部分索引掃描>索引查找>唯一索引/主鍵查找>常量/null

5.3 應(yīng)用查詢優(yōu)化

應(yīng)用查詢優(yōu)化是建立在良好的數(shù)據(jù)結(jié)構(gòu)和合理的索引設(shè)計(jì)之上的。

它主要包括以下幾種情況:

5.3.1 重構(gòu)查詢方式

優(yōu)化慢查詢時(shí),目標(biāo)應(yīng)該是找到一個(gè)更優(yōu)的方案來達(dá)到我們獲取結(jié)果數(shù)據(jù)的目的。其中可以存在多樣的權(quán)衡方案:

1)從數(shù)據(jù)庫中查詢計(jì)算直接獲取到結(jié)果數(shù)據(jù);

2)拆分多條子查詢來逐步得到結(jié)果數(shù)據(jù);

3)從數(shù)據(jù)庫獲取到基礎(chǔ)數(shù)據(jù),然后應(yīng)用代碼邏輯加工后獲得結(jié)果數(shù)據(jù)。

5.3.2 讓SQL盡量符合查詢優(yōu)化器的執(zhí)行要求

MySQL 查詢優(yōu)化器并不是對所有查詢都適用的,我們可以通過改寫查詢 SQL 來讓數(shù)據(jù)庫更高效的完成工作。

常見查詢應(yīng)用優(yōu)化建議匯總?cè)缦拢?/p>

1)對于任何查詢,應(yīng)盡量避免全表掃描

    首先應(yīng)考慮在 where 及 order by 涉及的列上建立并應(yīng)用索引;

2)盡量避免在 where 子句中進(jìn)行操作

    使用 or 來連接條件、對字段進(jìn)行 null 值判斷、匹配查詢 '%abc%'、!= 或 <> 操作符,否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描;

    對字段進(jìn)行表達(dá)式、函數(shù)操作,這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描;

3)盡量應(yīng)用索引

    使用索引字段作為條件時(shí),如果是復(fù)合索引,那么必須使用到該索引中的第一個(gè)字段作為條件時(shí)才能保證系統(tǒng)使用該索引,否則該索引將不會(huì)被使用,并且應(yīng)盡可能的讓字段順序與索引順序相一致;

4)索引字段要注意慎重選取

    索引盡量避開區(qū)分度不大的字段,如:sex、male、female

    這種五五開的索引列有大量數(shù)據(jù)重復(fù)時(shí),那么即使在 sex 上建了索引也對查詢效率起不了作用。

5)一個(gè)表的索引數(shù)最好不要超過 6 個(gè)

    索引并不是越多越好,索引固然可以提高相應(yīng)的 select 的效率,但同時(shí)也降低了 insert 及 update 的效率, 因?yàn)?insert 或 update 時(shí)有可能會(huì)重建索引,所以怎樣建索引需要慎重考慮,視具體情況而定。

6)盡量使用數(shù)字型字段

    若只含數(shù)值信息的字段盡量不要設(shè)計(jì)為字符型,這會(huì)降低查詢和連接的性能,并會(huì)增加存儲(chǔ)開銷。 這是因?yàn)橐嬖谔幚聿樵兒瓦B接時(shí)會(huì)逐個(gè)比較字符串中每一個(gè)字符,而對于數(shù)字型而言只需要比較一次就夠了。

7)盡量避免使用 *

    select * from table ,用具體的字段列表代替 *,不要返回用不到的任何字段,尤其是多表關(guān)聯(lián)查詢的情況。

MySQL v5.6版本以后,消除了很多MySQL原本的限制,讓更多的查詢能夠以盡可能高的效率完成。

5.4 小結(jié)

根據(jù)梳理 MySQL中的 SQL執(zhí)行過程我們發(fā)現(xiàn),任何流程的執(zhí)行都存在其執(zhí)行環(huán)境和規(guī)則,其實(shí)產(chǎn)生慢SQL的本質(zhì)是:我們沒有按照數(shù)據(jù)庫的要求方式來執(zhí)行SQL。

主要導(dǎo)致慢查詢最根本的問題就是需要訪問的數(shù)據(jù)太多,導(dǎo)致查詢不可避免的需要篩選大量的數(shù)據(jù)。

MySQL慢查詢問題細(xì)數(shù)起來,林林總總太多了,但行之有效的無外乎這幾種:

  • 優(yōu)化數(shù)據(jù)結(jié)構(gòu)
  • 應(yīng)用索引策略
  • 查詢緩存
  • 重構(gòu)查詢方式

良好的表結(jié)構(gòu)設(shè)計(jì)是高性能查詢的基石,恰當(dāng)?shù)乃饕O(shè)計(jì)是高性能查詢的助推器,同時(shí)合理的查詢應(yīng)用也是必不可少的。數(shù)據(jù)結(jié)構(gòu)優(yōu)化、索引設(shè)計(jì)優(yōu)化及應(yīng)用查詢優(yōu)化猶如三叉戟一般,齊頭并進(jìn),在高性能查詢應(yīng)用中缺一不可。

寫在最后

全文總結(jié)一下,其實(shí)就是我們要學(xué)會(huì)用數(shù)據(jù)庫的要求方式來執(zhí)行SQL。

即要寫好應(yīng)用查詢SQL,必須要結(jié)合良好的數(shù)據(jù)結(jié)構(gòu)和合理的索引設(shè)計(jì)才可以。

其實(shí)MySQL查詢優(yōu)化中的每一項(xiàng)拆開講都可以是很大的章節(jié),在此主要是將解決問題的思路分享給大家,希望能對大家今后的工作中能有所幫助。

責(zé)任編輯:武曉燕 來源: 架構(gòu)精進(jìn)之路
相關(guān)推薦

2024-05-21 08:44:43

MySQLB+Tree內(nèi)存

2024-06-24 08:31:42

2021-01-08 08:10:34

MySQL表空間回收

2018-01-11 15:31:39

命令Linux關(guān)機(jī)

2017-02-23 15:37:44

OptionObject容器

2016-05-09 10:41:03

算法分析開發(fā)

2017-07-10 13:09:45

前端Flexbox

2017-03-16 11:39:33

Openstack源碼姿勢

2023-01-30 07:41:43

2017-05-23 16:26:26

MySQL優(yōu)化處理

2021-05-26 05:33:30

5G網(wǎng)絡(luò)運(yùn)營商

2010-10-14 15:07:44

MySQL慢查詢

2017-10-12 11:30:34

Spark代碼PR

2016-12-12 08:48:24

2019-12-27 15:58:57

大數(shù)據(jù)IT互聯(lián)網(wǎng)

2021-09-15 16:20:02

Spring BootFilterJava

2024-09-25 08:22:06

2017-04-01 19:00:25

MySQL慢查詢

2021-04-07 10:38:43

MySQL數(shù)據(jù)庫命令

2019-10-30 17:06:50

AWS物聯(lián)網(wǎng)IoT
點(diǎn)贊
收藏

51CTO技術(shù)棧公眾號