前言

本文的內(nèi)容是總結(jié)一些MySQL的常見使用技巧，以供沒(méi)有DBA的團(tuán)隊(duì)參考。如無(wú)特殊說(shuō)明，存儲(chǔ)引擎以InnoDB為準(zhǔn)。

MySQL的特點(diǎn)

了解MySQL的特點(diǎn)有助于更好的使用MySQL，MySQL和其它常見數(shù)據(jù)庫(kù)最大的不同在于存在存儲(chǔ)引擎這個(gè)概念，存儲(chǔ)引擎負(fù)責(zé)存儲(chǔ)和讀取數(shù)據(jù)。不同的存儲(chǔ)引擎具有不同的特點(diǎn)，用戶可以根據(jù)業(yè)務(wù)的特點(diǎn)選擇適合的存儲(chǔ)引擎，甚至是開發(fā)一個(gè)新的引擎。MySQL的邏輯架構(gòu)大致如下：

MySQL默認(rèn)的存儲(chǔ)引擎是InnoDB，該存儲(chǔ)引擎的主要特點(diǎn)是：

• 支持事務(wù)處理
• 支持行級(jí)鎖
• 數(shù)據(jù)存儲(chǔ)在表空間中，表空間由一些列數(shù)據(jù)文件組成
• 采用MVVC(多版本并發(fā)控制)機(jī)制實(shí)現(xiàn)高并發(fā)
• 表基于主鍵的聚簇索引建立
• 支持熱備份
  

其它常見存儲(chǔ)引擎特點(diǎn)概述：

• MyISAM：老版本MySQL的默認(rèn)引擎，不支持事務(wù)和行級(jí)鎖，開發(fā)者可以手動(dòng)控制表鎖；支持全文索引；崩潰后無(wú)法安全恢復(fù)；支持壓縮表，壓縮表數(shù)據(jù)不可修改，但占用空間較少，可以提高查詢性能
• Archive：只支持Insert和Select，批量插入很快，通過(guò)全表掃描查詢數(shù)據(jù)
• SCV：把一個(gè)SCV文件當(dāng)做一個(gè)表處理
• Memory：數(shù)據(jù)存儲(chǔ)在內(nèi)存中
  

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

數(shù)據(jù)類型優(yōu)化

選擇數(shù)據(jù)類型的原則：

• 選擇占用空間小的數(shù)據(jù)類型
• 選擇簡(jiǎn)單的類型
• 避免不必要的可空列
  

占用空間小的類型更節(jié)省硬件資源，如磁盤、內(nèi)存和CPU。盡量使用簡(jiǎn)單的類型，如能用 int 就不用 char ，因?yàn)楹笳叩呐判蛏婕暗阶址倪x擇，比使用 int 復(fù)雜?？煽樟惺褂酶嗟拇鎯?chǔ)空間，如果在可空列上創(chuàng)建索引，MySQL需要額外的字節(jié)做記錄。創(chuàng)建表時(shí)，默認(rèn)都是可空，容易被開發(fā)者忽視，最好是手動(dòng)改為不可空，如果要存儲(chǔ)的數(shù)據(jù)確實(shí)不會(huì)有空值的話。

整型類型

整型類型包括 ：

• tinyint
• smallint
• mediumint
• int
• bigint
  

它們分別使用8、16、24、32和64位存儲(chǔ)數(shù)字，它們可以表示

范圍的數(shù)字，前面可以加unsigned修飾，這樣可以讓正數(shù)的可表示范圍提高1倍，但是無(wú)法表示負(fù)數(shù)。另外，為整型指定長(zhǎng)度沒(méi)什么卵用，數(shù)據(jù)類型定下來(lái)，長(zhǎng)度也就相應(yīng)定下來(lái)了。

小數(shù)類型

• float
• double
• decimal
  

float 和 double 就是通常意義上的 float 和 double ，前者使用32位存儲(chǔ)數(shù)據(jù)，后者使用64位存儲(chǔ)數(shù)據(jù)，和整型一樣，為它們指定長(zhǎng)度沒(méi)什么卵用。

decimal 類型比較復(fù)雜，支持精確計(jì)算，占用的空間也大， decimal 使用每4個(gè)字節(jié)表示9個(gè)數(shù)字，如 decimal(18,9) 表示數(shù)字長(zhǎng)度是18，其中小數(shù)位9個(gè)數(shù)字，整數(shù)部分9個(gè)數(shù)字，加上小數(shù)點(diǎn)本身，共占用9個(gè)字節(jié)?？紤]到 decimal 占用空間較多，以及精度計(jì)算很復(fù)雜，數(shù)據(jù)量大的時(shí)候可以考慮用 bigint 代替之，可以在持久化和讀取前對(duì)真實(shí)數(shù)據(jù)進(jìn)行一些縮放操作。

字符串類型

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

varchar類型數(shù)據(jù)實(shí)際占用空間等于字符串的長(zhǎng)度加上1個(gè)或2個(gè)用來(lái)記錄字符串長(zhǎng)度的字節(jié)（當(dāng)row-format沒(méi)有被設(shè)置為fixed時(shí)），varchar很節(jié)省空間。當(dāng)表中某列字符串類型的數(shù)據(jù)長(zhǎng)度差別較大時(shí)適合使用varchar。

char的實(shí)際占用空間是固定的，當(dāng)表中字符串?dāng)?shù)據(jù)的長(zhǎng)度相差無(wú)幾或很短時(shí)適合使用chart類型。

與varchar和char對(duì)應(yīng)的有varbinary和binary，后者存儲(chǔ)的是二進(jìn)制字符串，和前者相比，后者大小寫敏感，不用考慮編碼方式，執(zhí)行比較操作時(shí)更快。

需要注意的是：雖然varchar(5)和varchar(200)在存儲(chǔ)“hello”這個(gè)字符串時(shí)使用相同的存儲(chǔ)空間，但并不意味著將varchar的長(zhǎng)度設(shè)置太大不會(huì)影響性能，實(shí)際上，MySQL的某些內(nèi)部計(jì)算，比如創(chuàng)建內(nèi)存臨時(shí)表時(shí)（某些查詢會(huì)導(dǎo)致MySQL自動(dòng)創(chuàng)建臨時(shí)表），會(huì)分配固定大小的空間存放數(shù)據(jù)。

blob使用二進(jìn)制字符串保存大文本，text使用字符保存大文本，InnoDB會(huì)使用專門的外部存儲(chǔ)區(qū)來(lái)存放此類數(shù)據(jù)，數(shù)據(jù)行內(nèi)僅存放指向他們的指針，此類數(shù)據(jù)不宜創(chuàng)建索引（要?jiǎng)?chuàng)建也只能正對(duì)字符串前綴創(chuàng)建），不過(guò)也不會(huì)有人這么干。

如果某列字符串大量重復(fù)且內(nèi)容有限，可使用枚舉代替，MySQL處理枚舉時(shí)維護(hù)了一個(gè)“數(shù)字-字符串”表，使用枚舉可以減少很多存儲(chǔ)空間。

時(shí)間類型

• year
• date
• time
• datetime
• timestamp
  

datetime存儲(chǔ)范圍是1001到9999，精確到秒。timestamp存儲(chǔ)1970年1月1日午夜以來(lái)的秒數(shù)，可以表示到2038年。占用4個(gè)字節(jié)，是datetime占用空間的一半。timestamp表示的時(shí)間和時(shí)區(qū)有關(guān)，另外timestamp列還有個(gè)特性，執(zhí)行insert或update語(yǔ)句時(shí)，MySQL會(huì)自動(dòng)更新第一個(gè)類型為timestamp的列的數(shù)據(jù)為當(dāng)前時(shí)間。很多表中都有設(shè)計(jì)有一列叫做UpdateTime，這個(gè)列使用timestamp倒是挺合適的，會(huì)自動(dòng)更新，前提是系統(tǒng)不會(huì)使用到2038年。

主鍵類型的選擇

盡可能使用整型，整型占用空間少，還可以設(shè)置為自動(dòng)增長(zhǎng)。尤其別使用GUID，MD5等哈希值字符串作為主鍵，這類字符串隨機(jī)性很大，由于InnoDB主鍵默認(rèn)是聚簇索引列，所以導(dǎo)致數(shù)據(jù)存儲(chǔ)太分散。另外，InnoDB的二級(jí)索引列中默認(rèn)包含主鍵列，如果主鍵太長(zhǎng)，也會(huì)使得二級(jí)索引很占空間。

特殊類型的數(shù)據(jù)

存儲(chǔ)IP最好使用32位無(wú)符號(hào)整型，MySQL提供了函數(shù)inet_aton()和inet_ntoa()進(jìn)行IP地址的數(shù)字表示和字符串表示之間的轉(zhuǎn)換。

索引優(yōu)化

InnoDB使用B+樹實(shí)現(xiàn)索引，舉個(gè)例子，假設(shè)有個(gè)People，建表語(yǔ)句如下

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 '編號(hào)',
 PRIMARY KEY (`Id`),
 KEY `i_name_age_number` (`Name`,`Age`,`Number`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

插入數(shù)據(jù)：

它的索引結(jié)構(gòu)大致是這樣的：

也就是說(shuō)，索引列的順序很重要，如果兩行數(shù)據(jù)的Name列相同，則用Age列比較大小，如果Age列相同，則用Number列比較大小。先用第一列排序，然后是第二列，最后是第三列。

查詢的使用應(yīng)該盡量從左往右匹配，另外，如果左邊列范圍查找，右邊列無(wú)法使用索引；還有就是不能隔列查詢，否則后面的索引也無(wú)法使用到。如以下幾個(gè)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
  

以下幾個(gè)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
  

一個(gè)使用Hash值創(chuàng)建索引的技巧

如果表中有一列存儲(chǔ)較長(zhǎng)字符串，假設(shè)名字為URL，在此列上創(chuàng)建的索引比較大，有個(gè)辦法可以緩解：創(chuàng)建URL字符串的數(shù)字哈希值的索引。再新建一個(gè)字段，比如叫做URL_CRC，專門放置URL的哈希值，然后給這個(gè)字段創(chuàng)建索引，查詢時(shí)這樣寫：

select * from t where URL_CRC = 387695885 and URL = 'www.baidu.com'

如果數(shù)據(jù)量比較多，為防止哈希沖突，可自定義哈希函數(shù)，或用MD5函數(shù)返回值的一部分作為哈希值：

SELECT CONV(RIGHT(MD5('www.baidu.com'),16), 16, 10)

前綴索引

如果字符串列存儲(chǔ)的數(shù)據(jù)較長(zhǎng)，創(chuàng)建的索引也很大，這時(shí)可以使用前綴索引，即：只針對(duì)字符串前幾個(gè)字符做索引，這樣可以縮短索引的大小，不過(guò)，顯然，此類索引在執(zhí)行 order by 和 group by 時(shí)不起作用。

創(chuàng)建前綴索引時(shí)選擇前綴長(zhǎng)度很重要，在不破壞原來(lái)數(shù)據(jù)分布的情況下盡可能選擇較短的前綴。舉個(gè)例子，如果如果大部分字符串是以”abc”開頭，那么如果限定前綴索引長(zhǎng)度為4，索引值會(huì)包含太多的重復(fù)的”abcX”。

多列索引

上面提到的“People”上創(chuàng)建的索引即為多列索引，多列索引往往比多個(gè)單列索引更好。

• 對(duì)多個(gè)索引進(jìn)行and查詢時(shí)，應(yīng)該創(chuàng)建多列索引，而不是多個(gè)單列索引
• 可以試試這樣寫的效果：
  

select * from t where f1 = 'v1' and f2 <> 'v2' union all select * from t where f2 = 'v2' and f1 <> 'v1'

多列索引的順序很重要，通常，不考慮排序和分組查詢時(shí)，應(yīng)該把選擇性（選擇性是指某表索引列不同數(shù)據(jù)的個(gè)數(shù)/總行數(shù)。選擇性高意味著重復(fù)數(shù)據(jù)少）大的列放到前面。但也有例外，如果能確認(rèn)某些查詢是頻繁執(zhí)行的，則應(yīng)該優(yōu)先照顧這些查詢的選擇性，比如，如果上面的People表中Name的選擇性大于Age，查詢語(yǔ)句應(yīng)該這樣寫:

select * from people where name = 'xxx' and age = xx

Name列放了索引中的左側(cè)比較合適，但是如果某個(gè)SQL執(zhí)行的評(píng)率最高，比如

select * from people where name = 'xxx' and age = 20

當(dāng)age=20的記錄在數(shù)據(jù)庫(kù)中非常少時(shí)，反而把a(bǔ)ge放到索引列的左端效率更高。把a(bǔ)ge放了索引左端可能對(duì)其它age不等于20的查詢來(lái)說(shuō)不公平，如果不能確定age=20是最非常頻繁的查詢條件，還是要綜合考慮，把name放了左側(cè)合適。

聚簇索引

聚簇索引是一種數(shù)據(jù)存儲(chǔ)結(jié)構(gòu)，InnoDB在主鍵的索引的葉子節(jié)點(diǎn)中直接保存了數(shù)據(jù)行，而不是像二級(jí)索引那樣只是保存了索引列的值和所指向行的主鍵值。由于這個(gè)特性，一個(gè)表只能有一個(gè)聚簇索引。如果一個(gè)表沒(méi)有定義主鍵也沒(méi)有定義具有唯一索引的列，那么InnoDB會(huì)生成一個(gè)隱藏列，并且在此列設(shè)為聚簇索引列。

覆蓋索引

簡(jiǎn)單地說(shuō)，某些查詢只需要查詢索引列，那么就不用再根據(jù)索引B樹節(jié)點(diǎn)記錄的主鍵ID進(jìn)行二次查詢了。

重復(fù)索引和冗余索引

如果重復(fù)在某列創(chuàng)建索引，并不會(huì)帶來(lái)任何好處，只有壞處，應(yīng)該盡量避免。比如給主鍵創(chuàng)建唯一索引和普通索引就是多于的，因?yàn)镮nnoDB的主鍵默認(rèn)就是聚簇索引了。

冗余索引和重復(fù)索引不同，比如某個(gè)索引是(A,B)，另一個(gè)索引是（A），這叫冗余索引，前者可以代替后者，后者不可以代替前者的作用。但是(A,B)和（B）以及（A,B）和（B,A）不算冗余索引，起作用誰(shuí)也代替不了誰(shuí)。

如果一個(gè)表中已經(jīng)存在索引(A)，現(xiàn)在又想創(chuàng)建索引(A,B),那么只需擴(kuò)展就的索引就可以，沒(méi)有必要?jiǎng)?chuàng)建新的索引。需要注意的是如果已經(jīng)存在索引(A)，那么也沒(méi)有必要在創(chuàng)建索引(A,ID)，其中ID指主鍵，因?yàn)樗饕鼳默認(rèn)已經(jīng)包含了主鍵了，也算是冗余主鍵。

但是，有時(shí)候，冗余索引也是可取的，假設(shè)已經(jīng)存在索引(A)，將其擴(kuò)展為(A,B)后，因?yàn)锽列是一個(gè)很長(zhǎng)的類型，導(dǎo)致用A單獨(dú)查詢時(shí)沒(méi)有以前快了，這時(shí)可以考慮新創(chuàng)建索引(A,B)。

不使用的索引

不使用的索引徒然增加insert、update和delete的效率，應(yīng)該及時(shí)刪除

索引使用總結(jié)

索引的三星原則：

• 索引將查詢相關(guān)的記錄按順序放在一起則得一星
• 索引中的數(shù)據(jù)順序和查詢結(jié)果的排序一致則得一星
• 索引中包含了查詢所需要的全部列則得一星
  

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

索引不是萬(wàn)能的，當(dāng)數(shù)據(jù)量巨大時(shí)，維護(hù)索引本身也是耗費(fèi)性能的，應(yīng)該考慮分區(qū)分表存儲(chǔ)。

查詢優(yōu)化

查詢慢的原因

是否向數(shù)據(jù)庫(kù)請(qǐng)求了多余的行

比如應(yīng)用程序只需要10條數(shù)據(jù)，但是卻向數(shù)據(jù)庫(kù)請(qǐng)求了所有的數(shù)據(jù)，在顯示在UI上之前拋棄了大部分?jǐn)?shù)據(jù)。

是否向數(shù)據(jù)庫(kù)請(qǐng)求了多余的列

比如應(yīng)用程序只需要展現(xiàn)5列，但卻通過(guò)select * from 把全部的列都查了出來(lái)

是否重復(fù)多次執(zhí)行了相同的查詢

應(yīng)用程序是否可以考慮一次查詢?nèi)缓缶彺?，后面的用到時(shí)可以使用第一次查詢出來(lái)的記錄。

MySQL是否在掃描額外的記錄

通過(guò)查看執(zhí)行計(jì)劃可以大概了解需要掃描的記錄數(shù)，如果這個(gè)數(shù)字超出了預(yù)期，盡可能通過(guò)添加索引、優(yōu)化SQL（就是本節(jié)的重點(diǎn)），或者改變表結(jié)構(gòu)（如新增一個(gè)單獨(dú)的匯總表，專門供某個(gè)語(yǔ)句查詢用）來(lái)解決。

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

• 將一個(gè)復(fù)雜的查詢分解成多個(gè)簡(jiǎn)單的查詢
• 將大的查詢切分成小的查詢，每次查詢功能一樣，只完成一小部分
• 分解關(guān)聯(lián)查詢。可以將一個(gè)大的關(guān)聯(lián)查詢改成分別查詢?nèi)舾蓚€(gè)表，然后在應(yīng)用程序代碼中處理
  

雜七雜八

優(yōu)化count()

Count有兩個(gè)作用，一是統(tǒng)計(jì)指定的列或表達(dá)式，二是統(tǒng)計(jì)行數(shù)。如果參數(shù)傳入一列名或者是一個(gè)表達(dá)式，那么count會(huì)統(tǒng)計(jì)所有結(jié)果不為NULL的行數(shù)，如果參數(shù)是*，那么count會(huì)統(tǒng)計(jì)所有行數(shù)。這里有一個(gè)傳表達(dá)式的例子：

SELECT count(name like 'B%') from people

• 可以使用近似值優(yōu)化來(lái)代替count()，如執(zhí)行計(jì)劃中的行數(shù)。
• 索引覆蓋掃描
• 增加匯總表
• 增加內(nèi)存緩存系統(tǒng)記錄數(shù)據(jù)條數(shù)
  

關(guān)聯(lián)查詢的優(yōu)化

• MySQL優(yōu)化器關(guān)聯(lián)表查詢是這樣進(jìn)行的，比如有兩個(gè)表A和B通過(guò)c列關(guān)聯(lián)，MySQL會(huì)遍歷A表，然后根據(jù)遍歷到的c列的值去B表中查找數(shù)據(jù)。綜上所述，通常，如無(wú)只需要給B表的c列加上索引即可
• 確保order by和group by涉及到的列只屬于一個(gè)表，這樣才有可能發(fā)揮索引的作用
  

優(yōu)化子查詢

對(duì)于MySQL5.5及以下版本，盡量用連接代替子查詢。

優(yōu)化group by、distinct

如果可能，盡量對(duì)主鍵施加這兩種操作。

優(yōu)化limit，比如有SQL

SELECT * from sa_stockinfo ORDER BY StockAcc LIMIT 400, 5

MySQL優(yōu)化器會(huì)查找405行所有列數(shù)據(jù)然后丟棄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上建有索引，該查詢會(huì)利用索引覆蓋，較快找出符合條件的主鍵，然后在做聯(lián)合查詢，在數(shù)據(jù)量大的時(shí)候效果明顯。

優(yōu)化union

如無(wú)必要，一定要用關(guān)鍵字 union all，這樣MySQL把數(shù)據(jù)放到臨時(shí)表時(shí)不會(huì)再做唯一性驗(yàn)證

判斷某條記錄是否存在，通常的做法是

select count(*) from t where condition

最好這樣寫：

SELECT IFNULL((SELECT 1 from tableName where condition LIMIT 1),0)

總結(jié)

到此這篇關(guān)于MySQL索引查詢優(yōu)化技巧的文章就介紹到這了,更多相關(guān)MySQL索引查詢優(yōu)化技巧內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論