MySQL數據庫查詢和索引的優化方式

本篇內容主要講解“MySQL數據庫查詢和索引的優化方式”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“MySQL數據庫查詢和索引的優化方式”吧!

一、數據庫查詢性能的優化涉及到的技術面非常廣，一般建議用以下幾個手段實行：

1、減少數據訪問

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

2、減少返回的數據

在網絡中傳輸數據，帶寬是有限的，如果能按需提取最少量的數據，會起到不錯的作用。這里需要注意的是，在SQL中，不要出現select *，而是需要什么字段，就提取什么字段。

3、減少與數據庫交互次數

網絡資源有限，顯然，頻繁與數據庫交互，也是制約性能的一個因素。一個良好的建議就是，使用存儲過程，或者批處理語句，這樣能減少與數據庫的交互，提升一部分性能。

4、減少CPU的負荷

這里，主要是使用緩存計劃。在查詢中，盡量使用參數化的查詢。這樣的話，數據庫會對查詢參數進行緩存，從而復用查詢計劃。

5、提升硬件性能

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

以上5個層次的優化帶來的性能改善，是依次下降的，是一個倒置的金字塔。

二、下面說一下索引以及優化建議

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

索引優化，是一個很考究的事情，它需要找到一個平衡點。

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

索引優化建議

1、前綴索引

前綴索引就是用列的前綴代替整個列作為索引key，當前綴長度合適時，可以做到既使得前綴索引的選擇性接近全列索引，同時因為索引key變短而減少了索引文件的大小和維護開銷。

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

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

字符串本身可能比較長，而且前幾個字符就開始不相同。比如我們對中國人的姓名使用前綴索引就沒啥意義，因為中國人名字都很短，另外對收件地址使用前綴索引也不是很實用，因為一方面收件地址一般都是以XX省開頭，也就是說前幾個字符都是差不多的，而且收件地址進行檢索一般都是like ’%xxx%’，不會用到前匹配。相反對外國人的姓名可以使用前綴索引，因為其字符較長，而且前幾個字符的選擇性比較高。同樣電子郵件也是一個可以使用前綴索引的字段。

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

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

3、對 where,on,group by,order by 中出現的列使用索引。

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

5、對較小的數據列使用索引,這樣會使索引文件更小,同時內存中也可以裝載更多的索引鍵。

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

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

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

9、不要過多創建索引, 權衡索引個數與DML之間關系，DML也就是插入、刪除數據操作。這里需要權衡一個問題，建立索引的目的是為了提高查詢效率的，但建立的索引過多，會影響插入、刪除數據的速度，因為我們修改的表數據，索引也需要進行調整重建。

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

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

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

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

字符串與數字比較不使用索引;

CREATE TABLEa(achar(10))。

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

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

正則表達式不使用索引,這應該很好理解,所以為什么在SQL中很難看到regexp關鍵字的原因。

到此，相信大家對“MySQL數據庫查詢和索引的優化方式”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！