1. 優(yōu)化手段

基本上分成四個(gè)手段，數(shù)據(jù)庫(kù)表結(jié)構(gòu)優(yōu)化，SQL語(yǔ)句優(yōu)化，數(shù)據(jù)庫(kù)參數(shù)配置的優(yōu)化，硬件和系統(tǒng)級(jí)別的優(yōu)化。其中最后一個(gè)是要花錢的，這里先不討論。

2. 數(shù)據(jù)庫(kù)表結(jié)構(gòu)和設(shè)計(jì)的優(yōu)化

這里首先要知道一下，表的設(shè)計(jì)的基本原則是表越窄越好，越小越好。
常見的數(shù)據(jù)庫(kù)表一級(jí)的優(yōu)化有如下一些方法：

2.1 讀寫分離，分庫(kù)分表

我們經(jīng)常會(huì)聽到針對(duì)大的數(shù)據(jù)，要分庫(kù)分表，讀寫分離
分庫(kù)分表常用的框架有Sharding Sphere和MyCat，其中Sharding Sphere里面又分為Sharding JDBC，Sharding Proxy，Sharding SideCar三個(gè)子集，Sharding JDBC直接作用于Dao層，相當(dāng)于對(duì)原JDBC操作的封裝改造，Sharding Proxy相當(dāng)于在數(shù)據(jù)庫(kù)和Dao層中間加了一個(gè)代理層，和MyCat作用的位置相似，Sharding SideCar現(xiàn)在還沒(méi)實(shí)現(xiàn)，先不用關(guān)注。下個(gè)主題會(huì)介紹一個(gè)基于Sharding JDBC做的分庫(kù)分表的一個(gè)實(shí)驗(yàn)。

2.2 大表的水平拆分

這里的大表主要指的是表的行數(shù)，我們對(duì)于數(shù)據(jù)量的大小要有一個(gè)基本預(yù)期，一般而言單表的超過(guò)千萬(wàn)行記錄就需要考慮分庫(kù)分表的方案，建議單表數(shù)據(jù)量盡量不要超過(guò)5000萬(wàn)，單表的物理空間大小不要超過(guò)20G。
當(dāng)然這里所謂大表的其實(shí)也依賴于表的寬度，當(dāng)表的寬度足夠小的時(shí)候，上億行在一張表里面性能也可以接受，而當(dāng)你的表很寬的時(shí)候，幾十萬(wàn)行的表也可能慢的讓你沒(méi)法忍受。

2.3 表數(shù)據(jù)的冷熱分離

我們?cè)诒碓O(shè)計(jì)的時(shí)候還要考慮表被訪問(wèn)修改的頻率，也就是我們常說(shuō)的“冷熱表”，當(dāng)然冷熱分離的方式除了在表一級(jí)去做分離，其實(shí)也可以在庫(kù)一級(jí)做分離，具體選擇主要依賴我們的業(yè)務(wù)場(chǎng)景。

2.4 選取合適的數(shù)據(jù)類型

數(shù)據(jù)類型的選擇原則是保留數(shù)據(jù)擴(kuò)展能力情況下，優(yōu)先選擇小的數(shù)據(jù)結(jié)構(gòu)。通常的情況是夠用就好，沒(méi)有必要預(yù)留更多的存儲(chǔ)空間，這里也符合上面說(shuō)的表越窄越好的道理。
這里通常的技巧有：
1）存儲(chǔ)時(shí)間戳或者存儲(chǔ)IPV4地址的時(shí)候可以使用無(wú)符號(hào)整形代替存儲(chǔ)，來(lái)回之間的轉(zhuǎn)換可以用INET_ATON和INET_NTOA或者其他辦法，具體方法看實(shí)現(xiàn)語(yǔ)言，總之是他們的轉(zhuǎn)換很舒適，沒(méi)有啥代價(jià)也能帶來(lái)一定程度查詢效率的提升，比較劃算。
關(guān)于表的寬窄可以使用show table status命令，通過(guò)里面的avg_row_length來(lái)查看，這個(gè)值說(shuō)明了表的行平均長(zhǎng)度，通常情況下這個(gè)值超過(guò)100就可以考慮一些辦法去減小行平均長(zhǎng)度。

image.png

除了avg_row_length，還有一些值需要關(guān)注，比如data_free，這個(gè)值標(biāo)示了表的碎片，值越大碎片率越高。

2.5 寬表的垂直拆分

對(duì)于字段過(guò)多的表可以做拆分，具體多少字段需要看實(shí)際的執(zhí)行效率，必要時(shí)可以增加中間表。字段過(guò)多還會(huì)有本身維護(hù)復(fù)雜度的問(wèn)題，比如三四十個(gè)或者更多的字段的表通常也會(huì)認(rèn)為過(guò)于復(fù)雜不便于維護(hù)。

2.6 適度使用反范式

這種方式慎重使用，反范式也就是某些場(chǎng)景可以不嚴(yán)格遵守三范式的要求，這樣做可以提高查詢效率，如果對(duì)查詢效率提示不大就不用考慮了。

2.7 正確的使用索引

可以為經(jīng)常作為查詢條件的字段加索引，創(chuàng)建聯(lián)合索引要考慮最左原則提供的索引復(fù)用能力，避免重復(fù)建索引，對(duì)唯一性可以保證的字段創(chuàng)建唯一索引等等。
而且索引不宜過(guò)多，建議索引的數(shù)量不要超過(guò)5個(gè)。

2.8 字段盡量設(shè)置為NOT NULL

因?yàn)镸ySQL需要為空字段做很多特殊的處理，MySQL的優(yōu)化器也很難對(duì)空字段做優(yōu)化，空字段需要的存儲(chǔ)空間也更多。

2.9 字符集和庫(kù)表的設(shè)計(jì)要一致

字符集不一致可能導(dǎo)致報(bào)錯(cuò)，還有一個(gè)潛在的問(wèn)題是join類型轉(zhuǎn)換無(wú)法走索引。

3. SQL語(yǔ)句優(yōu)化

常見的優(yōu)化有如下一些方法：
1）首先需要分析慢查詢?nèi)罩?，找到需要?yōu)化的語(yǔ)句，或者是執(zhí)行頻率非常高的語(yǔ)句。
2）利用分析工具：explain、profile，其中explain用來(lái)分析執(zhí)行計(jì)劃的，主要看是否使用了索引，使用哪個(gè)索引，掃描了多少記錄（這里主要看掃描的記錄是否符合預(yù)期）。profile命令分析某個(gè)語(yǔ)句執(zhí)行過(guò)程中分步的耗時(shí)。
3）避免使用SELECT *，只取需要的列，這樣做一個(gè)是避免拿到多余的字段，對(duì)于大而無(wú)用的字段，會(huì)增加io操作，二是可以避免查詢列字段的元信息，最后他會(huì)失去MySQL優(yōu)化器“覆蓋索引”策略優(yōu)化的可能性。
4）使用preparedstatement，一個(gè)是它性能更好，一個(gè)是可以防止SQL注入。
5）盡量在有索引的字段上進(jìn)行排序操作，這樣排序的時(shí)候會(huì)用上索引掃描。
6）對(duì)于寫操作要盡快的完成，事務(wù)要盡快的提交或者回滾，因?yàn)檫@些操作都會(huì)加鎖（不管是表鎖還是行鎖或者其他細(xì)粒度的鎖），如果表鎖不釋放，其他的SQL或者事務(wù)就會(huì)被阻塞。這里建議監(jiān)控MySQL的線程狀態(tài)以及InnoDB的事務(wù)狀態(tài)，對(duì)于超過(guò)5秒的線程執(zhí)行和事務(wù)操作要監(jiān)控起來(lái)。

4. 數(shù)據(jù)庫(kù)配置文件的優(yōu)化

數(shù)據(jù)庫(kù)配置文件的優(yōu)化一般不需要普通開發(fā)人員掌握，經(jīng)常用到的就是根據(jù)具體業(yè)務(wù)場(chǎng)景調(diào)整各種緩沖區(qū)的大小，屬于DBA的工作范疇。配置文件如果是在windows環(huán)境的話一般在MySQL安裝目錄下就有my.ini文件，linux環(huán)境則是my.conf文件，可以通過(guò)find -name命令查詢文件路徑。
這里指出一下配置文件的路徑在5.7已經(jīng)修改了，我的MySQL環(huán)境8.0.13的版本沒(méi)有在安裝目錄下找到my.ini文件了，默認(rèn)的安裝路徑改成C:\ProgramData\MySQL\MySQL Server 8.0這個(gè)地方了，在這個(gè)目錄下一樣可以看到，5.7之前的版本的安裝路徑現(xiàn)在看都是沒(méi)有變化，和MySQL的安裝是在同一個(gè)目錄下面的。
這里我們簡(jiǎn)單了解一下配置文件里面的內(nèi)容：

port=3306   //默認(rèn)端口
default-character-set=utf8   //默認(rèn)字符集
basedir="D:/MySql/"     //安裝地址
datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"    //數(shù)據(jù)庫(kù)的根地址
character-set-server=utf8   //服務(wù)端默認(rèn)字符集
default-storage-engine=INNODB   //默認(rèn)存儲(chǔ)引擎
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" //數(shù)據(jù)庫(kù)模式
max_connections=100   //服務(wù)端允許的最大連接數(shù)，這個(gè)也是經(jīng)常會(huì)根據(jù)業(yè)務(wù)場(chǎng)景修改提升性能的，不過(guò)也不能無(wú)限制改大還是要根據(jù)機(jī)器性能來(lái)
query_cache_size=0   //查詢緩存大小，建議是讀非常多，很少寫的時(shí)候可以開啟提升性能，其他的情況不建議使用
table_cache=256    //每個(gè)線程能夠處理的表數(shù)上限
tmp_table_size=18M   //臨時(shí)表的大小限制
thread_cache_size=8  //線程池中的線程數(shù)

/*myisam引擎特有的配置*/
myisam_max_sort_file_size=100G  //重建索引時(shí)myisam引擎的臨時(shí)文件允許的大小
myisam_sort_buffer_size=35M     //建索引時(shí)緩沖區(qū)大小
key_buffer_size=25M     //主鍵緩沖區(qū)的大小
read_buffer_size=64K     //全表掃描myisam表的時(shí)候的緩沖池大小
read_rnd_buffer_size=256K
sort_buffer_size=256K              //重建索引的時(shí)候分配的緩沖區(qū)大小

/*innodb特有的配置*/
innodb_additional_mem_pool_size=2M   //innodb用于存放元信息的內(nèi)存池大小
innodb_flush_log_at_trx_commit=1    //設(shè)置1會(huì)讓innodb在每次事務(wù)提交的時(shí)候刷事務(wù)日志
innodb_log_buffer_size=1M      //innodb緩存日志數(shù)據(jù)的大小
innodb_buffer_pool_size=47M   //innodb使用了緩沖池來(lái)緩存索引和行數(shù)據(jù)，這個(gè)值設(shè)的越大磁盤IO就越少，通常用作MySQL的服務(wù)器該值可以設(shè)置到物理內(nèi)存的60%到80%
innodb_log_file_size=24M     //每個(gè)innodb日志文件的大小
innodb_thread_concurrency=10    //innodb引擎允許的線程數(shù)，依賴操作系統(tǒng)硬件資源等
/*binlog相關(guān)配置*/
binlog-format=ROW     //開啟Binlog，并指定模式為ROW
log-bin=mysqlbinlog    //指定log-bin名字為mysqlbinlog

對(duì)于配置文件里面的內(nèi)容，有一些選擇不依賴于復(fù)雜場(chǎng)景判斷的其實(shí)我們也可以掌握，比如innodb_buffer_pool_size這種參數(shù)，只需要知道我們機(jī)器總的內(nèi)存大小和機(jī)器的主要用途（是否還有其他必須要占用大量?jī)?nèi)存的操作）就可以準(zhǔn)確的設(shè)置他的值了。

5. 一些重要的設(shè)計(jì)細(xì)節(jié)

MySQL在設(shè)計(jì)優(yōu)化的時(shí)候，還會(huì)有一些重要的設(shè)計(jì)細(xì)節(jié)需要遵循。

基礎(chǔ)原則：

1）盡量小的原則。
2）禁止使用外鍵[增加行鎖]（高并發(fā)不建議）。
3）自增INT/BIGINT主鍵（InnoDB引擎表）。
4）字符集和庫(kù)表的設(shè)計(jì)要一致，否則報(bào)錯(cuò)，join類型轉(zhuǎn)換無(wú)法走索引。

實(shí)例維度：

1）表的總的大小不要超過(guò)500G。
2）總表數(shù)量不超過(guò)5000個(gè)（包括分區(qū)表）。

庫(kù)表字段設(shè)計(jì)規(guī)范:

1）每個(gè)表建議不超過(guò)50個(gè)字段。
2）優(yōu)先選擇utf8mb4字符集（支持移動(dòng)終端的emoji符號(hào)，表情包）。
3）嚴(yán)禁在數(shù)據(jù)庫(kù)中明文存儲(chǔ)用戶的一些核心數(shù)據(jù)（最好要單向加密）。
4）遇到BLOB，TEXT字段，盡量要拆出去，再用主鍵做關(guān)聯(lián)。
5）字符類型盡可能采用varchar的數(shù)據(jù)類型（靈活、高效），最好不要變長(zhǎng)更新。
6）日期時(shí)間數(shù)據(jù)建議采用datetime（0000-9999年）類型，5.6以后多個(gè)datetime數(shù)據(jù)類型自動(dòng)更新為當(dāng)前時(shí)間，5.6之前只有一個(gè)可用更新為當(dāng)前時(shí)間。

SQL開發(fā)建議：

1）多表join時(shí)，join列的數(shù)據(jù)類型要一致（長(zhǎng)度，類型，字符集）。
2）多表join時(shí)，把過(guò)濾后結(jié)果集較小的表作為驅(qū)動(dòng)表，建議統(tǒng)一采用inner join讓優(yōu)化器自動(dòng)優(yōu)化，如果優(yōu)化器優(yōu)化錯(cuò)誤，可用采用straight_join強(qiáng)制執(zhí)行順序。
3）不要執(zhí)行sellect * 操作，會(huì)導(dǎo)致io代價(jià)高。
4）不要執(zhí)行l(wèi)ike '%x%'這種前綴有%的操作
5）盡量不用'!='條件，因?yàn)閽呙璧臄?shù)據(jù)量超過(guò)20%~30%范圍時(shí)，會(huì)把執(zhí)行計(jì)劃變成全表掃描，不管有無(wú)索引。
6）優(yōu)先使用union all代替union，這樣會(huì)減少臨時(shí)表的生成。
7）所有SQL都要通過(guò)SQL審核系統(tǒng)檢查符合標(biāo)準(zhǔn)后才能上線。
8）可以監(jiān)控MySQL的線程狀態(tài)，監(jiān)控InnoDB的事務(wù)狀態(tài)，一般是大于5秒。
9）設(shè)置修改鎖定行數(shù)的閾值，比如大于10行，可以避免潛在長(zhǎng)時(shí)間鎖或者事務(wù)SQL運(yùn)行的風(fēng)險(xiǎn)。
10）檢查或者監(jiān)控SQL注入的風(fēng)險(xiǎn)，比如SLEEP函數(shù)，UNION ALL函數(shù)。