MySQL數(shù)據(jù)庫查詢和索引的優(yōu)化方式

本篇內(nèi)容主要講解“MySQL數(shù)據(jù)庫查詢和索引的優(yōu)化方式”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實(shí)用性強(qiáng)。下面就讓小編來帶大家學(xué)習(xí)“MySQL數(shù)據(jù)庫查詢和索引的優(yōu)化方式”吧!

一、數(shù)據(jù)庫查詢性能的優(yōu)化涉及到的技術(shù)面非常廣，一般建議用以下幾個(gè)手段實(shí)行：

1、減少數(shù)據(jù)訪問

相關(guān)的技術(shù)就是建立合適的索引，將全表掃描、索引掃描(scan)等耗時(shí)的操作轉(zhuǎn)化為索引查找(seek)。建立正確的索引，能讓數(shù)據(jù)庫查詢性能提升100-1000倍甚至更高，就好比一本非常厚的詞典，如果沒有任何索引，你要查一個(gè)東西，那可是相當(dāng)費(fèi)盡，需要整本書查一遍，有索引就可以直接根據(jù)索引定位了。這是最重要的改善性能的途徑。

2、減少返回的數(shù)據(jù)

在網(wǎng)絡(luò)中傳輸數(shù)據(jù)，帶寬是有限的，如果能按需提取最少量的數(shù)據(jù)，會(huì)起到不錯(cuò)的作用。這里需要注意的是，在SQL中，不要出現(xiàn)select *，而是需要什么字段，就提取什么字段。

3、減少與數(shù)據(jù)庫交互次數(shù)

網(wǎng)絡(luò)資源有限，顯然，頻繁與數(shù)據(jù)庫交互，也是制約性能的一個(gè)因素。一個(gè)良好的建議就是，使用存儲(chǔ)過程，或者批處理語句，這樣能減少與數(shù)據(jù)庫的交互，提升一部分性能。

4、減少CPU的負(fù)荷

這里，主要是使用緩存計(jì)劃。在查詢中，盡量使用參數(shù)化的查詢。這樣的話，數(shù)據(jù)庫會(huì)對(duì)查詢參數(shù)進(jìn)行緩存，從而復(fù)用查詢計(jì)劃。

5、提升硬件性能

這是最后一招了，如果其他方面都已經(jīng)做得非常不錯(cuò)了，性能瓶頸在CPU，內(nèi)存和磁盤上，那采取提升硬件性能的方案就會(huì)顯得比較合適了，否則還是先去優(yōu)化其他的地方吧。

以上5個(gè)層次的優(yōu)化帶來的性能改善，是依次下降的，是一個(gè)倒置的金字塔。

二、下面說一下索引以及優(yōu)化建議

索引能大幅度提高查詢和排序性能，但是，在插入、刪除、以及修改了主鍵的操作中，是需要維護(hù)索引順序的。如果一張頻繁變更的表，是不宜建立過多的索引的，索引帶來的負(fù)面性能影響，將會(huì)得不償失。

索引優(yōu)化，是一個(gè)很考究的事情，它需要找到一個(gè)平衡點(diǎn)。

MySQL的優(yōu)化主要分為結(jié)構(gòu)優(yōu)化(Scheme optimization)和查詢優(yōu)化(Query optimization)。本章討論的高性能索引策略主要屬于結(jié)構(gòu)優(yōu)化范疇。本章的內(nèi)容完全基于上文的理論基礎(chǔ)，實(shí)際上一旦理解了索引背后的機(jī)制，那么選擇高性能的策略就變成了純粹的推理，并且可以理解這些策略背后的邏輯。

索引優(yōu)化建議

1、前綴索引

前綴索引就是用列的前綴代替整個(gè)列作為索引key，當(dāng)前綴長度合適時(shí)，可以做到既使得前綴索引的選擇性接近全列索引，同時(shí)因?yàn)樗饕齥ey變短而減少了索引文件的大小和維護(hù)開銷。

一般來說以下情況可以使用前綴索引：

字符串列(varchar,char,text等)，需要進(jìn)行全字段匹配或者前匹配。也就是=‘xxx’ 或者 like ‘xxx%’

字符串本身可能比較長，而且前幾個(gè)字符就開始不相同。比如我們對(duì)中國人的姓名使用前綴索引就沒啥意義，因?yàn)橹袊嗣侄己芏蹋硗鈱?duì)收件地址使用前綴索引也不是很實(shí)用，因?yàn)橐环矫媸占刂芬话愣际且訶X省開頭，也就是說前幾個(gè)字符都是差不多的，而且收件地址進(jìn)行檢索一般都是like ’%xxx%’，不會(huì)用到前匹配。相反對(duì)外國人的姓名可以使用前綴索引，因?yàn)槠渥址^長，而且前幾個(gè)字符的選擇性比較高。同樣電子郵件也是一個(gè)可以使用前綴索引的字段。

前一半字符的索引選擇性就已經(jīng)接近于全字段的索引選擇性。如果整個(gè)字段的長度為20，索引選擇性為0.9，而我們對(duì)前10個(gè)字符建立前綴索引其選擇性也只有0.5，那么我們需要繼續(xù)加大前綴字符的長度，但是這個(gè)時(shí)候前綴索引的優(yōu)勢(shì)已經(jīng)不明顯，沒有太大的建前綴索引的必要了。

2、主鍵外檢一定要建索引。

3、對(duì) where,on,group by,order by 中出現(xiàn)的列使用索引。

4、盡量選擇區(qū)分度高的列作為索引,區(qū)分度的公式是count(distinct col)/count(*)，表示字段不重復(fù)的比例，比例越大我們掃描的記錄數(shù)越少，唯一鍵的區(qū)分度是1，而一些狀態(tài)、性別字段可能在大數(shù)據(jù)面前區(qū)分度就是0。

5、對(duì)較小的數(shù)據(jù)列使用索引,這樣會(huì)使索引文件更小,同時(shí)內(nèi)存中也可以裝載更多的索引鍵。

6、索引列不能參與計(jì)算，保持列“干凈”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很簡單，b+樹中存的都是數(shù)據(jù)表中的字段值，但進(jìn)行檢索時(shí)，需要把所有元素都應(yīng)用函數(shù)才能比較，顯然成本太大。所以語句應(yīng)該寫成create_time = unix_timestamp(’2014-05-29’)。

7、為較長的字符串使用前綴索引。

8、盡量的擴(kuò)展索引，不要新建索引。比如表中已經(jīng)有a的索引，現(xiàn)在要加(a,b)的索引，那么只需要修改原來的索引即可。

9、不要過多創(chuàng)建索引, 權(quán)衡索引個(gè)數(shù)與DML之間關(guān)系，DML也就是插入、刪除數(shù)據(jù)操作。這里需要權(quán)衡一個(gè)問題，建立索引的目的是為了提高查詢效率的，但建立的索引過多，會(huì)影響插入、刪除數(shù)據(jù)的速度，因?yàn)槲覀冃薷牡谋頂?shù)據(jù)，索引也需要進(jìn)行調(diào)整重建。

10、對(duì)于like查詢，”%”不要放在前面。

SELECT * FROMhoudunwangWHEREunameLIKE'后盾%' -- 走索引。

SELECT * FROMhoudunwangWHEREunameLIKE "%后盾%" -- 不走索引。

11、查詢where條件數(shù)據(jù)類型不匹配也無法使用索引。

字符串與數(shù)字比較不使用索引;

CREATE TABLEa(achar(10))。

EXPLAIN SELECT * FROMaWHEREa="1" – 走索引。

EXPLAIN SELECT * FROM a WHERE a=1 – 不走索引。

正則表達(dá)式不使用索引,這應(yīng)該很好理解,所以為什么在SQL中很難看到regexp關(guān)鍵字的原因。

到此，相信大家對(duì)“MySQL數(shù)據(jù)庫查詢和索引的優(yōu)化方式”有了更深的了解，不妨來實(shí)際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！