10分鐘掌握資料型別、索引、查詢的MySQL優化技巧

Java_蘇先生發表於2019-05-09
資料型別索引MySql優化

10分鐘掌握資料型別、索引、查詢的MySQL優化技巧

10分鐘掌握資料型別、索引、查詢的MySQL優化技巧?

不存在的！

本文的內容是總結一些MySQL的常見使用技巧，以供沒有DBA的團隊參考。以下內容以MySQL5.5為準，如無特殊說明，儲存引擎以InnoDB為準。

本文大綱：

  • MySQL的特點
  • 資料型別優化
  • 索引優化
  • 查詢優化

一、MySQL的特點

瞭解MySQL的特點有助於更好的使用MySQL，MySQL和其它常見資料庫最大的不同在於存在儲存引擎這個概念，儲存引擎負責儲存和讀取資料。不同的儲存引擎具有不同的特點，使用者可以根據業務的特點選擇適合的儲存引擎，甚至是開發一個新的引擎。

MySQL的邏輯架構大致如下：

10分鐘掌握資料型別、索引、查詢的MySQL優化技巧

MySQL預設的儲存引擎是InnoDB，該儲存引擎的主要特點是：

  • 支援事務處理
  • 支援行級鎖
  • 資料儲存在表空間中，表空間由一些列資料檔案組成
  • 採用MVVC(多版本併發控制)機制實現高併發
  • 表基於主鍵的聚簇索引建立
  • 支援熱備份

其它常見儲存引擎特點概述：

  • MyISAM：老版本MySQL的預設引擎，不支援事務和行級鎖，開發者可以手動控制表鎖；支援全文索引；崩潰後無法安全恢復；支援壓縮表，壓縮表資料不可修改，但佔用空間較少，可以提高查詢效能

  • Archive：只支援Insert和Select，批量插入很快，通過全表掃描查詢資料

  • SCV：把一個SCV檔案當做一個表處理

  • Memory：資料儲存在記憶體中

還有很多，不再一一列舉。

二、資料型別優化

選擇資料型別的原則：

  • 選擇佔用空間小的資料型別
  • 選擇簡單的型別
  • 避免不必要的可空列

佔用空間小的型別更節省硬體資源，如磁碟、記憶體和CPU。

儘量使用簡單的型別，如能用int就不用char，因為後者的排序涉及到字符集的選擇，比使用int複雜。

可空列使用更多的儲存空間，如果在可空列上建立索引，MySQL需要額外的位元組做記錄。

建立表時，預設都是可空，容易被開發者忽視，最好是手動改為不可空，如果要儲存的資料確實不會有空值的話。

1、整型型別

整型型別包括：

  • tinyint
  • smallint
  • mediumint
  • int
  • bigint

它們分別使用8、16、24、32和64位儲存數字，它們可以表示−2n−1−2n−1到2n−1−12n−1−1範圍的數字，前面可以加unsigned修飾，這樣可以讓正數的可表示範圍提高1倍，但是無法表示負數。

另外，為整型指定長度沒什麼卵用，資料型別定下來，長度也就相應定下來了。

2、小數型別

  • float
  • double
  • decimal

float和double就是通常意義上的float和double，前者使用32位儲存資料，後者使用64位儲存資料，和整型一樣，為它們指定長度沒什麼卵用。

decimal型別比較複雜，支援精確計算，佔用的空間也大，decimal使用每4個位元組表示9個數字，如decimal(18,9)表示數字長度是18，其中小數位9個數字，整數部分9個數字，加上小數點本身，共佔用9個位元組。

考慮到decimal佔用空間較多，以及精度計算很複雜，資料量大的時候可以考慮用bigint代替之，可以在持久化和讀取前對真實資料進行一些縮放操作。

3、字串型別

  • varchar
  • char
  • varbinary
  • binary
  • blob
  • text
  • 列舉

varchar型別資料實際佔用空間等於字串的長度加上1個或2個用來記錄字串長度的位元組（當row-format沒有被設定為fixed時），varchar很節省空間。當表中某列字串型別的資料長度差別較大時適合使用varchar。

char的實際佔用空間是固定的，當表中字串資料的長度相差無幾或很短時適合使用chart型別。

與varchar和char對應的有varbinary和binary，後者儲存的是二進位制字串，和前者相比，後者大小寫敏感，不用考慮編碼方式，執行比較操作時更快。

需要注意的是：雖然varchar(5)和varchar(200)在儲存“hello”這個字串時使用相同的儲存空間，但並不意味著將varchar的長度設定太大不會影響效能，實際上，MySQL的某些內部計算，比如建立記憶體臨時表時（某些查詢會導致MySQL自動建立臨時表），會分配固定大小的空間存放資料。

blob使用二進位制字串儲存大文字，text使用字元儲存大文字，InnoDB會使用專門的外部儲存區來存放此類資料，資料行內僅存放指向他們的指標，此類資料不宜建立索引（要建立也只能正對字串字首建立），不過也不會有人這麼幹。

如果某列字串大量重複且內容有限，可使用列舉代替，MySQL處理列舉時維護了一個“數字-字串”表，使用列舉可以減少很多儲存空間。

4、時間型別

  • year
  • date
  • time
  • datetime
  • timestamp

datetime儲存範圍是1001到9999，精確到秒。

timestamp儲存1970年1月1日午夜以來的秒數，可以表示到2038年。佔用4個位元組，是datetime佔用空間的一半。timestamp表示的時間和時區有關，另外timestamp列還有個特性，執行insert或update語句時，MySQL會自動更新第一個型別為timestamp的列的資料為當前時間。

很多表中都有設計有一列叫做UpdateTime，這個列使用timestamp倒是挺合適的，會自動更新，前提是系統不會使用到2038年。

5、主鍵型別的選擇

儘可能使用整型，整型佔用空間少，還可以設定為自動增長。尤其別使用GUID，MD5等雜湊值字串作為主鍵，這類字串隨機性很大，由於InnoDB主鍵預設是聚簇索引列，所以導致資料儲存太分散。

另外，InnoDB的二級索引列中預設包含主鍵列，如果主鍵太長，也會使得二級索引很佔空間。

6、特殊型別的資料

儲存IP最好使用32位無符號整型，MySQL提供了函式inet_aton()和inet_ntoa()進行IP地址的數字表示和字串表示之間的轉換。

三、索引優化

InnoDB使用B+樹實現索引，舉個例子，假設有個People，建表語句如下

CREATE TABLE `people` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(5) NOT NULL,
  `Age` tinyint(4) NOT NULL,
  `Number` char(5) NOT NULL COMMENT '編號',
  PRIMARY KEY (`Id`),
  KEY `i_name_age_number` (`Name`,`Age`,`Number`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;
複製程式碼

插入資料：

10分鐘掌握資料型別、索引、查詢的MySQL優化技巧

它的索引結構大致是這樣的：

10分鐘掌握資料型別、索引、查詢的MySQL優化技巧

也就是說，索引列的順序很重要，如果兩行資料的Name列相同，則用Age列比較大小，如果Age列相同，則用Number列比較大小。先用第一列排序，然後是第二列，最後是第三列。

查詢的使用應該儘量從左往右匹配，另外，如果左邊列範圍查詢，右邊列無法使用索引；還有就是不能隔列查詢，否則後面的索引也無法使用到。如以下幾個SQL是正面範例：

SELECT * from people where Name ='Abel' and Age = 2 AND Number = 12312

SELECT * from people where Name ='Abel'

SELECT * from people where Name like 'Abel%'

SELECT * from people where Name = 'Andy' and Age BETWEEN 11 and 20

SELECT * from people ORDER BY NAME

SELECT * from people ORDER BY NAME, Age

SELECT * from people GROUP BY Name
複製程式碼

以下幾個SQL是反面範例：

SELECT * from people where Age = 2

SELECT * from people where NAME like '%B'

SELECT * from people where age = 2

SELECT * from people where NAME = 'ABC' AND number = 3

SELECT * from people where NAME like 'B%' and age = 22
複製程式碼

1、一個使用Hash值建立索引的技巧

如果表中有一列儲存較長字串，假設名字為URL，在此列上建立的索引比較大，有個辦法可以緩解：建立URL字串的數字雜湊值的索引。再新建一個欄位，比如叫做URL_CRC，專門放置URL的雜湊值，然後給這個欄位建立索引，查詢時這樣寫：

select * from t where URL_CRC = 387695885 and URL = 'www.baidu.com'
複製程式碼

如果資料量比較多，為防止雜湊衝突，可自定義雜湊函式，或用MD5函式返回值的一部分作為雜湊值：

SELECT CONV(RIGHT(MD5('www.baidu.com'),16), 16, 10)
複製程式碼

2、字首索引

如果字串列儲存的資料較長，建立的索引也很大，這時可以使用字首索引，即：只針對字串前幾個字元做索引，這樣可以縮短索引的大小，不過，顯然，此類索引在執行order by和group by時不起作用。

建立字首索引時選擇字首長度很重要，在不破壞原來資料分佈的情況下儘可能選擇較短的字首。舉個例子，如果如果大部分字串是以"abc"開頭，那麼如果限定字首索引長度為4，索引值會包含太多的重複的"abcX"。

3、多列索引

上面提到的“People”上建立的索引即為多列索引，多列索引往往比多個單列索引更好。

對多個索引進行and查詢時，應該建立多列索引，而不是多個單列索引。

可以試試這樣寫的效果：

select * from t where f1 = 'v1' and f2 <> 'v2' union all select * from t where f2 = 'v2' and f1 <> 'v1'
複製程式碼

多列索引的順序很重要，通常，不考慮排序和分組查詢時，應該把選擇性（選擇性是指某表索引列不同資料的個數/總行數。選擇性高意味著重複資料少）大的列放到前面。但也有例外，如果能確認某些查詢是頻繁執行的，則應該優先照顧這些查詢的選擇性，比如，如果上面的People表中Name的選擇性大於Age，查詢語句應該這樣寫:

select * from people where name = 'xxx' and age = xx
複製程式碼

Name列放了索引中的左側比較合適，但是如果某個SQL執行的評率最高，比如：

select * from people where name = 'xxx' and age = 20
複製程式碼

當age=20的記錄在資料庫中非常少時，反而把age放到索引列的左端效率更高。把age放了索引左端可能對其它age不等於20的查詢來說不公平，如果不能確定age=20是最非常頻繁的查詢條件，還是要綜合考慮，把name放了左側合適。

4、聚簇索引

聚簇索引是一種資料儲存結構，InnoDB在主鍵的索引的葉子節點中直接儲存了資料行，而不是像二級索引那樣只是儲存了索引列的值和所指向行的主鍵值。由於這個特性，一個表只能有一個聚簇索引。如果一個表沒有定義主鍵也沒有定義具有唯一索引的列，那麼InnoDB會生成一個隱藏列，並且在此列設為聚簇索引列。

5、覆蓋索引

簡單地說，某些查詢只需要查詢索引列，那麼就不用再根據索引B樹節點記錄的主鍵ID進行二次查詢了。

6、重複索引和冗餘索引

如果重複在某列建立索引，並不會帶來任何好處，只有壞處，應該儘量避免。比如給主鍵建立唯一索引和普通索引就是多於的，因為InnoDB的主鍵預設就是聚簇索引了。

冗餘索引和重複索引不同，比如某個索引是(A,B)，另一個索引是（A），這叫冗餘索引，前者可以代替後者，後者不可以代替前者的作用。但是(A,B)和（B）以及（A,B）和（B,A）不算冗餘索引，起作用誰也代替不了誰。

如果一個表中已經存在索引(A)，現在又想建立索引(A,B),那麼只需擴充套件就的索引就可以，沒有必要建立新的索引。需要注意的是如果已經存在索引(A)，那麼也沒有必要在建立索引(A,ID)，其中ID指主鍵，因為索引A預設已經包含了主鍵了，也算是冗餘主鍵。

但是，有時候，冗餘索引也是可取的，假設已經存在索引(A)，將其擴充套件為(A,B)後，因為B列是一個很長的型別，導致用A單獨查詢時沒有以前快了，這時可以考慮新建立索引(A,B)。

7、不使用的索引

不使用的索引徒然增加insert、update和delete的效率，應該及時刪除。

8、索引使用總結

索引的三星原則：

  • 索引將查詢相關的記錄按順序放在一起則得一星
  • 索引中的資料順序和查詢結果的排序一致則得一星
  • 索引中包含了查詢所需要的全部列則得一星

第一個條原則的意思是where條件中查詢的順序和索引是一致的，就是前面說的從左到右使用索引。

索引不是萬能的，當資料量巨大時，維護索引本身也是耗費效能的，應該考慮分割槽分表儲存。

四、查詢優化

1、查詢慢的原因

是否向資料庫請求了多餘的行

比如應用程式只需要10條資料，但是卻向資料庫請求了所有的資料，在顯示在UI上之前拋棄了大部分資料。

是否向資料庫請求了多餘的列

比如應用程式只需要展現5列，但卻通過select * from 把全部的列都查了出來。

是否重複多次執行了相同的查詢

應用程式是否可以考慮一次查詢然後快取，後面的用到時可以使用第一次查詢出來的記錄。

MySQL是否在掃描額外的記錄

通過檢視執行計劃可以大概瞭解需要掃描的記錄數，如果這個數字超出了預期，儘可能通過新增索引、優化SQL（就是本節的重點），或者改變表結構（如新增一個單獨的彙總表，專門供某個語句查詢用）來解決。

2、重構查詢的方式

  • 將一個複雜的查詢分解成多個簡單的查詢
  • 將大的查詢切分成小的查詢，每次查詢功能一樣，只完成一小部分
  • 分解關聯查詢。可以將一個大的關聯查詢改成分別查詢若干個表，然後在應用程式程式碼中處理

3、雜七雜八

優化count()

Count有兩個作用，一是統計指定的列或表示式，二是統計行數。如果引數傳入一列名或者是一個表示式，那麼count會統計所有結果不為NULL的行數，如果引數是*，那麼count會統計所有行數。這裡有一個傳表示式的例子：

SELECT count(name like 'B%') from people
複製程式碼
  • 可以使用近似值優化來代替count()，如執行計劃中的行數。
  • 索引覆蓋掃描
  • 增加彙總表
  • 增加記憶體快取系統記錄資料條數

關聯查詢的優化

  • MySQL優化器關聯表查詢是這樣進行的，比如有兩個表A和B通過c列關聯，MySQL會遍歷A表，然後根據遍歷到的c列的值去B表中查詢資料。綜上所述，通常，如無只需要給B表的c列加上索引即可

  • 確保order by和group by涉及到的列只屬於一個表，這樣才有可能發揮索引的作用

優化子查詢

對於MySQL5.5及以下版本，儘量用連線代替子查詢。

優化group by、distinct

如果可能，儘量對主鍵施加這兩種操作。

優化limit

比如有SQL：

SELECT * from sa_stockinfo ORDER BY StockAcc LIMIT 400, 5
複製程式碼

MySQL優化器會查詢405行所有列資料然後丟棄400。如果能利用覆蓋索引查詢則不必查詢出這麼多列，先修改為：

SELECT * FROM sa_stockinfo i JOIN (SELECT StockInfoID FROM sa_stockinfo ORDER BY StockAcc LIMIT 400,5)t ON i.StockInfoID = t.StockInfoID
複製程式碼

StockAcc上建有索引，該查詢會利用索引覆蓋，較快找出符合條件的主鍵，然後在做聯合查詢，在資料量大的時候效果明顯。

優化union

如無必要，一定要用關鍵字 union all，這樣MySQL把資料放到臨時表時不會再做唯一性驗證。

判斷某條記錄是否存在

通常的做法是：

select count(*) from t where condition
複製程式碼

最好這樣寫：

SELECT IFNULL((SELECT 1 from tableName where condition LIMIT 1),0)
複製程式碼

相關文章