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

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

比如，整型就比字符操作代價低，因而會使用整型來存儲ip地址，使用DATETIME來存儲時間，而不是使用字符串。

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

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

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

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

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

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

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

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

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

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

而且還有很大索引的情況下，耗時更久。當然有一些奇技淫巧可以解決這個問題，有興趣可自行查閱。

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

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

因為事后可能需要監(jiān)控大量的SQL才能定位到問題所在，而且添加索引的時間肯定是遠大于初始添加索引所需要的時間，可見索引的添加也是非常有技術(shù)含量的。

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

1、mysql提供四種索引

B-Tree索引:最常見的的索引，大部分引擎支持B樹索引

HASH索引:只有Memory引擎支持，使用場景簡單

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

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

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 :用于搜索很長一篇文章的時候，效果最好。

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

B-Tree索引的特點

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

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

2、B-tree索引更適合進行范圍查詢

因為前面說過，B-tree對索引是順序組織存儲的，所以就很適合進行查找范圍數(shù)據(jù)。

B-tree索引的使用場景

1、全值匹配的查詢

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

2、匹配最左前綴的查詢

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

３、匹配列前綴的查詢

只匹配某一列的值的開頭部分

4、匹配范圍值

5、精準匹配某一列并范圍匹配另外一列

6、只訪問索引的查詢

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

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

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

B-Tree索引的限制

1、若不是按照索引的最左列開始查找，則無法使用該索引

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

2、使用索引時，不能跳過索引中的列

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

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

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

注意：

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

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

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

HASH索引

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

hash索引的特點

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

2、對于hash索引中的所有列，存儲引擎都會為每一行計算一個hash碼，hash索引中存儲的就是hash碼。

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

因為hash索引本身只需要存儲對應(yīng)的hash值，所以索引的結(jié)構(gòu)十分緊湊，這也讓hash索引查找的速度非常快。然而，hash索引也是存在其限制的：

hash索引的限制

1、Hash索引必須進行二次查找

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

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

hash索引存儲的是hash碼而不是鍵值，所以無法用于外排序

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

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

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

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

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

缺點

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

避免select *，將需要查找的字段列出來

使用連接（join）來代替子查詢

拆分大的delete或insert語句

可通過開啟慢查詢?nèi)罩緛碚页鲚^慢的SQL

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

sql語句盡可能簡單：一條sql只能在一個cpu運算；大語句拆小語句，減少鎖時間；一條大sql可以堵死整個庫

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

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

避免%xxx式查詢

少用JOIN

使用同類型進行比較，比如用’123’和’123’比，123和123比

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

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

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

5、分區(qū)

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

對用戶來說，分區(qū)表是一個獨立的邏輯表，但是底層由多個物理子表組成，實現(xiàn)分區(qū)的代碼實際上是通過對一組底層表的對象封裝，但對SQL層來說是一個完全封裝底層的黑盒子。MySQL實現(xiàn)分區(qū)的方式也意味著索引也是按照分區(qū)的子表定義，沒有全局索引

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

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

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

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

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

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

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

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

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

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

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

NULL值會使分區(qū)過濾無效

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

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

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

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

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

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