資料庫的效能調優：如何正確的使用索引？

WanWuJieKeLian發表於2024-07-21
資料庫索引

在當今的資料驅動時代，資料庫的效能最佳化成為每個開發者和資料庫管理員必須掌握的技能之一。而在眾多最佳化手段中，索引的使用無疑是最為重要和有效的。
然而，索引的濫用或誤用不僅不會提升效能，反而可能帶來額外的開銷。那麼，如何正確地使用索引，才能真正提升資料庫效能呢？

為什麼有時我們精心建立的索引卻沒有帶來預期的效能提升？究竟該如何正確地使用索引，才能確保資料庫在查詢時迅速高效？

Mysql是一款廣泛應用於各種規模和型別的應用程式的關係型資料庫管理系統。在實際資料庫應用中，我們常常面臨這各種效能瓶頸和問題，據不完全統計，
效能測試過程中發現的效能瓶頸有70%以上的都是來自於資料庫；而且當資料庫的效能遭遇瓶頸的時候，應用程式的響應時間會延長，TPS會降低，甚至嚴重的
時候會導致系統崩潰和當機。

所以，對資料庫進行效能調優就非常的重要，也是確保應用程式高效執行的關鍵一環。

Mysql是一款廣泛應用於各種規模和型別的應用程式的關係型資料庫管理系統。在實際資料庫應用中，我們常常面臨這各種效能瓶頸和問題，據不完全統計，
效能測試過程中發現的效能瓶頸有70%以上的都是來自於資料庫；而且當資料庫的效能遭遇瓶頸的時候，應用程式的響應時間會延長，TPS會降低，甚至嚴重
的時候會導致系統崩潰和當機。

所以，對資料庫進行效能調優就非常的重要，也是確保應用程式高效執行的關鍵一環。

那麼，資料庫的效能瓶頸可能表現在哪些方面呢？主要總結如下：

1、資料庫查詢效能低下：某些查詢語句執行速度緩慢，導致應用程式的響應時間變慢，使用者體驗下降；

2、併發訪問問題：當多個使用者同時訪問資料庫時，可能會出現鎖競爭、死鎖等問題，從而導致系統效能下降；

3、資料庫配置不當：Mysql的預設配置可能無法滿足特定應用程式的需求，需要對引數進行釋放的調整，比如資料庫最大連線數等配置，可以獲得更好的效能；

4、資料庫儲存引擎選擇：不同的引擎具有不同的效能特點，選擇合適的引擎也至關重要。

5、資料庫的設計不合理：比如業務表資料過於龐大，沒有進行分表分庫，導致資料查詢更新都很慢。

面對這些問題，我們需要對資料庫採取一系列的調優措施，以提升Mysql資料庫的效能和使用者體驗。我們今天主要給大家講一下平時用的最多的索引調優。

索引概念


索引是一種資料結構，用於幫助我們在大量資料中快速定位我們要查詢的資料，主要作用是加快我們查詢資料的速度，類似於漢語字典和書籍目錄。

大家有環境可以拿自己的專案試一下，加了索引和沒有加索引的sql語句的執行速度。

這條語句的執行速度是0.02s，因為id是主鍵索引；

這條SQL的執行速度是0.118s，同一個庫和表，慢出一個數量級，因為mobile欄位不是索引。

SELECT * FROM cb_account where id = 182037;

SELECT * FROM cb_account where mobile= '13525329369';`

給這張表的mobile欄位加一個索引，然後再查詢：

同一個sql語句速度立馬提升到0.020s

SELECT * FROM cb_account where mobile= '13525329369';

透過以上的簡單的案例演示，我們發現索引確實可以很大程度的提高查詢的速度，特別是資料量比較大的時候，這種速度提升的效果就尤其明顯。

索引分類


索引主要的型別有如下幾種：
一張表，可以沒有主鍵；如果有主鍵，主鍵這一列的值，一定是唯一，不重複，不存在‘空’

建立主鍵索引的方法：create index 索引名 on 表名(欄位);

建立主鍵索引的語法：create unique index 索引名 on 表名(欄位);

建立主鍵索引的語法：create index 索引名 on 表名(欄位1，欄位2,......);

主鍵索引 primary key ：也叫做單值索引，如果一張表建立了主鍵，就預設會生成一個主鍵索引，不用再額外建立。

唯一索引(unique index)：不可重複，但是可以儲存NULL

複合索引，也叫做組合索引：由表的多列按順序組合成為索引；使用時，按照組合順序使用索引，也可以使用組合索引中部分索引欄位。

建立索引的原則


索引雖然可以大大的提升資料庫的查詢的效能，但是也不能盲目加索引，因為索引有一定大小，會佔磁碟\記憶體空間。

建立索引，其實本質上就是使用空間換時間，以磁碟和記憶體的空間來換取查詢時間更少；如果索引建立很多和很複雜，那麼就會佔用大量的記憶體空間，
效能損耗就會很大；所以我們需要正確加索引，在空間和時間上取最佳平衡點。

我們建立索引的時候要遵循如下原則：

可以建立索引的列：
1、主鍵列可以建立索引，外來鍵列用於表關聯和連結查詢的條件， 也可以用於建立索引

2、頻繁查詢的資料列可以用於建立索引

3、頻繁用在where語句中的列可以建立索引

不應該建立索引的列：
表修改操作遠頻繁於查詢操作時 ：因為修改表是需要修改索引的，所以維護索引成本比用索引的成本高了；

資料很少的列，比如type欄位只有0和1兩個值，提升不明顯；

查詢很少用到的列，或者經常會變化列值的 【型別會變化的列】

如何正確的使用索引
索引的最左字首規則
當索引型別為複合索引的時候，我們要遵循組合索引的“最左字首”規則，否則使用就會失效，無法達到提高查詢效率的目的。

比如建立組合索引（c1,c2,c3）,實際包含三個索引（c1），(c1,c2)，(c1,c2,c3)，索引裡必須有一個最左邊的欄位。必須要按照這個索引來使用
才是會正確使用索引，否則就不會使用索引。

我們建索引的時候，使用的列，只要列的名稱，與表列的順序沒有關係，因為表中的列是沒有順序的，

我們做個小練習：如果在表裡有如下欄位，我們建立一個複合索引：

如上組合索引，就相當於加了3個索引：
mobile

mobile，email

mobile，email，gqid

我們的SQL語句就需要按照如上三個索引去查詢，才會使用索引提高速度，所以要正確的使用索引。我們做個判斷練習，以下SQL語句哪些會使用索引，哪些不會使用索引？

1 SELECT * FROM `cb_account` where gqid= '4004707'; -- 這個就不會使用索引
2 SELECT * FROM `cb_account` where email= '13537007192@test. com'; -- 這個就不會使用索引
3 SELECT * FROM `cb_account` where email= '13537007192@test. com' and gqid= '4004707';-- 這個就不會使用索引
4 
5 SELECT * FROM `cb_account` where mobile= '13525329369'; -- 這個就會使用索引
6 SELECT * FROM `cb_account` where mobile= '13525329369' and gqid= '4004707'; -- 這個就會使用索引
7 SELECT * FROM `cb_account` where mobile= '13525329369' and gqid= '4004707' and email= '13537007192@test. com' ; -- 這個就會使用索引

索引失效其他場景


1、sql向右匹配遇到查詢範圍就會停止匹配，後面的索引就無效了：比如between like 等 ；
2、like %value% 這種 % 出現在開頭，也不會使用索引【索引失效】：

SELECT * FROM **cb_account where mobile like '%135%' ;

3、列上做了函式或者表示式運算，也會導致索引失效：

select * from user where YEAR(date) < 1990；# 函式使用會導致索引失效

select * from student where id -1 =1；# 運算會導致索引失效

SELECT * FROM **cb_account where id = 182037; # 直接這樣寫索引是有效的

4、查詢條件裡有or 的時候，除非所有的查詢條件都有索引，否則索引失效：

SELECT * FROM student where id =1 or birthday = "2021-12-23' # 除非id和 birthday都是索引，否則就會索引失效

5、如果列型別是字串，那麼查詢條件中需要將資料用引號引用起來，否則不走索引：

select * from strudent where name = 222；#name是字串型別的索引，222值沒有用引號括起來，索引失效

索引的利弊總結


索引的弊端


a、索引本身很大，通常存在在磁碟(也可以存在記憶體) ，所以不要隨便見索引，佔用空間；


b、不是所有情況都可以用索引：資料量很少的情況，以及列值頻繁變更 ，還有列很少使用的情況都不推薦使用索引；


c、索引會提升查的效率但是會降低增刪改的效率， 因為增刪改需要修改更新索引本身，所以加了索引反而會降低增刪改的速度。

索引的優勢
a、降低IO、CPU使用率：查詢的時候，一行一行比對 需要CPU大量操作，每一行都讀取IO也會高；索引就會降低這些小消耗


b、索引列，可以保證行的唯一性：想讓某個欄位唯一 可以把這個欄位設定為唯一索引，那麼就在功能上保證它的唯一了。


c、可以有效縮短資料檢索時間


d、加快表與表之間的連線 ：多表關聯查詢，一般會把關聯欄位 【外來鍵】建立索引，大大提升查詢的效率。

所以，索引要設定，但是不能濫用，合理設定索引就很重要。一般，資料庫的表資料量級別在十萬級以內，有無索引，查詢資料的速度
差異不大，沒必要建索引。

在大資料時代，資料量的爆炸式增長給資料庫的效能帶來了巨大挑戰。隨著企業對實時資料分析和快速響應需求的增加，資料庫效能最佳化的
重要性愈加凸顯。正確使用索引是解決這一問題的關鍵之一，但也需要結合實際業務場景和資料特點，避免盲目建立索引。

索引如利劍，用之得當，所向披靡；用之不當，反受其累。

透過詳實的案例和社會現象分析，這篇文章展示了正確使用索引在資料庫效能最佳化中的重要性，並透過引人入勝的開頭和有力的金句收尾，
使讀者對索引最佳化產生了濃厚的興趣和實際操作的動機。


相關文章