1、Scheme設(shè)計(jì)與數(shù)據(jù)類型優(yōu)化

選擇數(shù)據(jù)類型只要遵循小而簡(jiǎn)單的原則就好，越小的數(shù)據(jù)類型通常會(huì)更快，占用更少的磁盤、內(nèi)存。

比如，整型就比字符操作代價(jià)低，因而會(huì)使用整型來(lái)存儲(chǔ)ip地址，使用DATETIME來(lái)存儲(chǔ)時(shí)間，而不是使用字符串。

這里總結(jié)幾個(gè)可能容易理解錯(cuò)誤的技巧：

1、通常來(lái)說(shuō)把可為NULL的列改為NOT NULL不會(huì)對(duì)性能提升有多少幫助，只是如果計(jì)劃在列上創(chuàng)建索引，就應(yīng)該將該列設(shè)置為NOT NULL。

2、對(duì)整數(shù)類型指定寬度，比如INT(11)，沒(méi)有任何卵用。INT使用32位（4個(gè)字節(jié)）存儲(chǔ)空間，那么它的表示范圍已經(jīng)確定，所以INT(1)和INT(20)對(duì)于存儲(chǔ)和計(jì)算是相同的。

3、UNSIGNED表示不允許負(fù)值，大致可以使正數(shù)的上限提高一倍。比如TINYINT存儲(chǔ)范圍是-128 ~ 127，而UNSIGNED TINYINT存儲(chǔ)的范圍卻是0 - 255。

4、通常來(lái)講，沒(méi)有太大的必要使用DECIMAL數(shù)據(jù)類型。即使是在需要存儲(chǔ)財(cái)務(wù)數(shù)據(jù)時(shí)，仍然可以使用BIGINT。比如需要精確到萬(wàn)分之一，

那么可以將數(shù)據(jù)乘以一百萬(wàn)然后使用BIGINT存儲(chǔ)。這樣可以避免浮點(diǎn)數(shù)計(jì)算不準(zhǔn)確和DECIMAL精確計(jì)算代價(jià)高的問(wèn)題。

5、TIMESTAMP使用4個(gè)字節(jié)存儲(chǔ)空間，DATETIME使用8個(gè)字節(jié)存儲(chǔ)空間。因而，TIMESTAMP只能表示1970 - 2038年，比DATETIME表示的范圍小得多，而且TIMESTAMP的值因時(shí)區(qū)不同而不同。

6、大多數(shù)情況下沒(méi)有使用枚舉類型的必要，其中一個(gè)缺點(diǎn)是枚舉的字符串列表是固定的，添加和刪除字符串（枚舉選項(xiàng)）必須使用ALTER TABLE（如果只只是在列表末尾追加元素，不需要重建表）。

7、schema的列不要太多。如果列太多而實(shí)際使用的列又很少的話，有可能會(huì)導(dǎo)致CPU占用過(guò)高。

8、大表ALTER TABLE非常耗時(shí)，MySQL執(zhí)行大部分修改表結(jié)果操作的方法是用新的結(jié)構(gòu)創(chuàng)建一個(gè)張空表，從舊表中查出所有的數(shù)據(jù)插入新表，然后再刪除舊表。尤其當(dāng)內(nèi)存不足而表又很大，

而且還有很大索引的情況下，耗時(shí)更久。當(dāng)然有一些奇技淫巧可以解決這個(gè)問(wèn)題，有興趣可自行查閱。

2、創(chuàng)建高性能索引

索引是提高M(jìn)ySQL查詢性能的一個(gè)重要途徑，但過(guò)多的索引可能會(huì)導(dǎo)致過(guò)高的磁盤使用率以及過(guò)高的內(nèi)存占用，從而影響應(yīng)用程序的整體性能。應(yīng)當(dāng)盡量避免事后才想起添加索引，

因?yàn)槭潞罂赡苄枰O(jiān)控大量的SQL才能定位到問(wèn)題所在，而且添加索引的時(shí)間肯定是遠(yuǎn)大于初始添加索引所需要的時(shí)間，可見(jiàn)索引的添加也是非常有技術(shù)含量的。

接下來(lái)將向你展示一系列創(chuàng)建高性能索引的策略，以及每條策略其背后的工作原理。但在此之前，先了解與索引相關(guān)的一些算法和數(shù)據(jù)結(jié)構(gòu)，將有助于更好的理解后文的內(nèi)容。

1、mysql提供四種索引

B-Tree索引:最常見(jiàn)的的索引，大部分引擎支持B樹(shù)索引

HASH索引:只有Memory引擎支持，使用場(chǎng)景簡(jiǎn)單

R-Tree索引:空間索引是MyISAM的一個(gè)特殊索引類型，主要用于地理空間數(shù)據(jù)類型，通常使用較少

Full-text：全文索引也是MyISAM的一個(gè)特殊索引，主要用于全文索引，InnoDb從MySql5.6開(kāi)始提供支持全文索引。

2、索引分類

1.普通索引index :加速查找

2.唯一索引

主鍵索引：primary key ：加速查找+約束（不為空且唯一）

唯一索引：unique：加速查找+約束 （唯一）

3.聯(lián)合索引

-primary key(id,name):聯(lián)合主鍵索引

-unique(id,name):聯(lián)合唯一索引

-index(id,name):聯(lián)合普通索引

4.全文索引fulltext :用于搜索很長(zhǎng)一篇文章的時(shí)候，效果最好。

3、B-Tree索引、hash索引區(qū)別

B-Tree索引的特點(diǎn)

1、B-tree索引可以加快數(shù)據(jù)的查詢速度

存儲(chǔ)引擎不需要進(jìn)行全表掃描來(lái)獲得需要的數(shù)據(jù)，取而代之的是從索引的根節(jié)點(diǎn)開(kāi)始進(jìn)行搜索。然后根據(jù)指針逐層向下查找，通過(guò)比較節(jié)點(diǎn)頁(yè)的值和有目標(biāo)值就可以找到合適的指針進(jìn)入下層節(jié)點(diǎn)，而這些指針實(shí)際上定義了子節(jié)點(diǎn)頁(yè)中值的上限和下限。

2、B-tree索引更適合進(jìn)行范圍查詢

因?yàn)榍懊嬲f(shuō)過(guò)，B-tree對(duì)索引是順序組織存儲(chǔ)的，所以就很適合進(jìn)行查找范圍數(shù)據(jù)。

B-tree索引的使用場(chǎng)景

1、全值匹配的查詢

指的是和索引中的所有列進(jìn)行匹配，比如查詢字段 name = ‘tom’;

2、匹配最左前綴的查詢

比如為ａ列和ｂ列設(shè)置聯(lián)合索引，只要聯(lián)合索引的第一列（ａ列）符合查詢條件，索引就會(huì)被用到，若只是第二列（ｂ列）符合條件則不會(huì)被用到該索引。

３、匹配列前綴的查詢

只匹配某一列的值的開(kāi)頭部分

4、匹配范圍值

5、精準(zhǔn)匹配某一列并范圍匹配另外一列

6、只訪問(wèn)索引的查詢

在這里指的就是覆蓋索引，即只需要訪問(wèn)索引，而無(wú)需訪問(wèn)數(shù)據(jù)行

7、用于查詢中的order by 操作

索引樹(shù)中的節(jié)點(diǎn)是有序的。一般來(lái)說(shuō)，若B-Tree可以按照某種方式查找到該值，那么也可以用這種方式用于排序。所以，如果 order by 子句中滿足前面列出的幾種查詢類型，則這個(gè)索引也可以滿足對(duì)應(yīng)的排序需求。

B-Tree索引的限制

1、若不是按照索引的最左列開(kāi)始查找，則無(wú)法使用該索引

比如建立聯(lián)合索引（name 、phone_num），若搜索phone_num則無(wú)法使用該索引

2、使用索引時(shí)，不能跳過(guò)索引中的列

比如建立聯(lián)合索引（name 、phone_num 、addr），若搜索name和addr 則無(wú)法使用該索引只能使用那么過(guò)濾

3、not in 和 <> 操作無(wú)法使用該索引

4、若查詢中有某個(gè)列的范圍查詢，則其右邊的所有列都無(wú)法使用索引

注意：

存儲(chǔ)引擎用不同的方式使用B-Tree索引，性能也各有不同，各有優(yōu)劣。例如，MyISAM使用前綴壓縮的技術(shù)使得索引更小，但I(xiàn)nnoDB則按照原數(shù)據(jù)格式進(jìn)行存儲(chǔ)。

MyISAM索引通過(guò)數(shù)據(jù)的物理位置引用被索引的行，而InnoDB則根據(jù)逐漸引用被索引的行

至此，我們基本已經(jīng)將B-Tree索引介紹完了，下面我們來(lái)了解另外的一種MySQL的索引類型：HASH索引。

HASH索引

在MySQL的存儲(chǔ)引擎中，MyISAM不支持哈希索引，而InnoDB中的hash索引是存儲(chǔ)引擎根據(jù)B-Tree索引自建的，后面會(huì)對(duì)其做具體說(shuō)明。

hash索引的特點(diǎn)

1、hash索引是基于hash表實(shí)現(xiàn)的，只有查詢條件精確匹配hash索引中的所有列的時(shí)候，才能用到hash索引。

2、對(duì)于hash索引中的所有列，存儲(chǔ)引擎都會(huì)為每一行計(jì)算一個(gè)hash碼，hash索引中存儲(chǔ)的就是hash碼。

3、hash索引包括鍵值、hash碼和指針 。

因?yàn)閔ash索引本身只需要存儲(chǔ)對(duì)應(yīng)的hash值，所以索引的結(jié)構(gòu)十分緊湊，這也讓hash索引查找的速度非?？?。然而，hash索引也是存在其限制的：

hash索引的限制

1、Hash索引必須進(jìn)行二次查找

使用哈市索引兩次查找，第一次找到相應(yīng)的行，第二次讀取數(shù)據(jù)，但是被頻繁訪問(wèn)到的行一般會(huì)緩存在內(nèi)存中，這點(diǎn)對(duì)數(shù)據(jù)庫(kù)性能的影響不大。

2、hash索引不能用于外排序

hash索引存儲(chǔ)的是hash碼而不是鍵值，所以無(wú)法用于外排序

3、hash索引不支持部分索引查找也不支持范圍查找

只能用到等值查詢，不能范圍和模糊查詢

4、hash索引中的hash碼的計(jì)算可能存在hash沖突

當(dāng)出現(xiàn)hash沖突的時(shí)候，存儲(chǔ)引擎必須遍歷整個(gè)鏈表中的所有行指針，逐行比較，直到找到所有的符合條件的行，若hash沖突很多的話，一些索引的維護(hù)代價(jià)機(jī)會(huì)很高，所以說(shuō)hash索引不適用于選擇性很差的列上（重復(fù)值很多）。姓名、性別、身份證（合適）

上面說(shuō)到InnoDB的“自適應(yīng)hash索引”。就是當(dāng)InnoDB注意到某些索引值被使用的非常頻繁時(shí)，它會(huì)在內(nèi)存中基于B-Tree索引上在創(chuàng)建一個(gè)hash索引，這樣就讓B-tree索引也具有hash索引的一些優(yōu)點(diǎn)。這是一個(gè)完全自動(dòng)的內(nèi)部的行為，用戶無(wú)法控制或配置，不過(guò)，如果有需要，完全可以關(guān)閉該功能。

缺點(diǎn)

1、需要維護(hù)hash值，可以手動(dòng)維護(hù)，也可以使用觸發(fā)器實(shí)現(xiàn)。

2、若數(shù)據(jù)表非常大的話，CRC32（）會(huì)出現(xiàn)大量hash沖突，則可以自己實(shí)現(xiàn)一個(gè)64位的hash函數(shù)，這個(gè)自定義的hash函數(shù)要返回整數(shù)而不是字符串，因?yàn)榉秶麛?shù)，對(duì)此效率更高。一個(gè)簡(jiǎn)單的辦法就是使用MD5（）函數(shù)返回值的一部分來(lái)作為自定義的hash函數(shù)。但是這可能比自己寫一個(gè)hash算法性能要差一些。

3、特定類型查詢優(yōu)化

1、優(yōu)化COUNT()查詢

COUNT()可能是被大家誤解最多的函數(shù)了，它有兩種不同的作用，其一是統(tǒng)計(jì)某個(gè)列值的數(shù)量，其二是統(tǒng)計(jì)行數(shù)。統(tǒng)計(jì)列值時(shí)，要求列值是非空的，它不會(huì)統(tǒng)計(jì)NULL。如果確認(rèn)括號(hào)中的表達(dá)式不可能為空時(shí)，

實(shí)際上就是在統(tǒng)計(jì)行數(shù)。最簡(jiǎn)單的就是當(dāng)使用COUNT(*)時(shí)，并不是我們所想象的那樣擴(kuò)展成所有的列，實(shí)際上，它會(huì)忽略所有的列而直接統(tǒng)計(jì)所有的行數(shù)。

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

在大數(shù)據(jù)場(chǎng)景下，表與表之間通過(guò)一個(gè)冗余字段來(lái)關(guān)聯(lián)，要比直接使用JOIN有更好的性能。如果確實(shí)需要使用關(guān)聯(lián)查詢的情況下，需要特別注意的是：

1）確保ON和USING字句中的列上有索引。在創(chuàng)建索引的時(shí)候就要考慮到關(guān)聯(lián)的順序。當(dāng)表A和表B用列c關(guān)聯(lián)的時(shí)候，如果優(yōu)化器關(guān)聯(lián)的順序是A、B，那么就不需要在A表的對(duì)應(yīng)列上創(chuàng)建索引。沒(méi)

有用到的索引會(huì)帶來(lái)額外的負(fù)擔(dān)，一般來(lái)說(shuō)，除非有其他理由，只需要在關(guān)聯(lián)順序中的第二張表的相應(yīng)列上創(chuàng)建索引（具體原因下文分析）

2）確保任何的GROUP BY和ORDER BY中的表達(dá)式只涉及到一個(gè)表中的列，這樣MySQL才有可能使用索引來(lái)優(yōu)化。

要理解優(yōu)化關(guān)聯(lián)查詢的第一個(gè)技巧，就需要理解MySQL是如何執(zhí)行關(guān)聯(lián)查詢的。當(dāng)前MySQL關(guān)聯(lián)執(zhí)行的策略非常簡(jiǎn)單，它對(duì)任何的關(guān)聯(lián)都執(zhí)行嵌套循環(huán)關(guān)聯(lián)操作，即先在一個(gè)表中循環(huán)取出單條數(shù)據(jù)，

然后在嵌套循環(huán)到下一個(gè)表中尋找匹配的行，依次下去，直到找到所有表中匹配的行為為止。然后根據(jù)各個(gè)表匹配的行，返回查詢中需要的各個(gè)列。

3、優(yōu)化LIMIT分頁(yè)

當(dāng)需要分頁(yè)操作時(shí)，通常會(huì)使用LIMIT加上偏移量的辦法實(shí)現(xiàn)，同時(shí)加上合適的ORDER BY字句。如果有對(duì)應(yīng)的索引，通常效率會(huì)不錯(cuò)，否則，MySQL需要做大量的文件排序操作。

4、sql的編寫需要注意優(yōu)化

使用limit對(duì)查詢結(jié)果的記錄進(jìn)行限定

避免select *，將需要查找的字段列出來(lái)

使用連接（join）來(lái)代替子查詢

拆分大的delete或insert語(yǔ)句

可通過(guò)開(kāi)啟慢查詢?nèi)罩緛?lái)找出較慢的SQL

不做列運(yùn)算：SELECT id WHERE age + 1 = 10，任何對(duì)列的操作都將導(dǎo)致表掃描，它包括數(shù)據(jù)庫(kù)教程函數(shù)、計(jì)算表達(dá)式等等，查詢時(shí)要盡可能將操作移至等號(hào)右邊

sql語(yǔ)句盡可能簡(jiǎn)單：一條sql只能在一個(gè)cpu運(yùn)算；大語(yǔ)句拆小語(yǔ)句，減少鎖時(shí)間；一條大sql可以堵死整個(gè)庫(kù)

OR改寫成IN：OR的效率是n級(jí)別，IN的效率是log(n)級(jí)別，in的個(gè)數(shù)建議控制在200以內(nèi)

不用函數(shù)和觸發(fā)器，在應(yīng)用程序?qū)崿F(xiàn)

避免%xxx式查詢

少用JOIN

使用同類型進(jìn)行比較，比如用’123’和’123’比，123和123比

盡量避免在WHERE子句中使用!=或<>操作符，否則將引擎放棄使用索引而進(jìn)行全表掃描

對(duì)于連續(xù)數(shù)值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5

列表數(shù)據(jù)不要拿全表，要使用LIMIT來(lái)分頁(yè)，每頁(yè)數(shù)量也不要太大

5、分區(qū)

MySQL在5.1版引入的分區(qū)是一種簡(jiǎn)單的水平拆分，用戶需要在建表的時(shí)候加上分區(qū)參數(shù)，對(duì)應(yīng)用是透明的無(wú)需修改代碼

對(duì)用戶來(lái)說(shuō)，分區(qū)表是一個(gè)獨(dú)立的邏輯表，但是底層由多個(gè)物理子表組成，實(shí)現(xiàn)分區(qū)的代碼實(shí)際上是通過(guò)對(duì)一組底層表的對(duì)象封裝，但對(duì)SQL層來(lái)說(shuō)是一個(gè)完全封裝底層的黑盒子。MySQL實(shí)現(xiàn)分區(qū)的方式也意味著索引也是按照分區(qū)的子表定義，沒(méi)有全局索引

1、分區(qū)的好處是：

可以讓單表存儲(chǔ)更多的數(shù)據(jù)

分區(qū)表的數(shù)據(jù)更容易維護(hù)，可以通過(guò)清楚整個(gè)分區(qū)批量刪除大量數(shù)據(jù)，也可以增加新的分區(qū)來(lái)支持新插入的數(shù)據(jù)。另外，還可以對(duì)一個(gè)獨(dú)立分區(qū)進(jìn)行優(yōu)化、檢查、修復(fù)等操作

部分查詢能夠從查詢條件確定只落在少數(shù)分區(qū)上，速度會(huì)很快

分區(qū)表的數(shù)據(jù)還可以分布在不同的物理設(shè)備上，從而搞笑利用多個(gè)硬件設(shè)備

可以使用分區(qū)表賴避免某些特殊瓶頸，例如InnoDB單個(gè)索引的互斥訪問(wèn)、ext3文件系統(tǒng)的inode鎖競(jìng)爭(zhēng)

可以備份和恢復(fù)單個(gè)分區(qū)

2、分區(qū)的限制和缺點(diǎn)：

一個(gè)表最多只能有1024個(gè)分區(qū)

如果分區(qū)字段中有主鍵或者唯一索引的列，那么所有主鍵列和唯一索引列都必須包含進(jìn)來(lái)

分區(qū)表無(wú)法使用外鍵約束

NULL值會(huì)使分區(qū)過(guò)濾無(wú)效

所有分區(qū)必須使用相同的存儲(chǔ)引擎

3、分區(qū)的類型：

RANGE分區(qū)：基于屬于一個(gè)給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)

LIST分區(qū)：類似于按RANGE分區(qū)，區(qū)別在于LIST分區(qū)是基于列值匹配一個(gè)離散值集合中的某個(gè)值來(lái)進(jìn)行選擇

HASH分區(qū)：基于用戶定義的表達(dá)式的返回值來(lái)進(jìn)行選擇的分區(qū)，該表達(dá)式使用將要插入到表中的這些行的列值進(jìn)行計(jì)算。這個(gè)函數(shù)可以包含MySQL中有效的、產(chǎn)生非負(fù)整數(shù)值的任何表達(dá)式

KEY分區(qū)：類似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計(jì)算一列或多列，且MySQL服務(wù)器提供其自身的哈希函數(shù)。必須有一列或多列包含整數(shù)值