MySQL是目前使用最為廣泛的關(guān)系型數據庫之一，如果使用得當，可支撐企業(yè)級高并發(fā)、高可靠服務(wù)，使用不當甚至連并發(fā)量略高的個(gè)人網(wǎng)站都難以支撐；
就算使用了緩存，大量的數據庫訪(fǎng)問(wèn)依舊在所難免，即使設置了較長(cháng)的緩存有效期，而且緩存命中率較理想，但緩存的創(chuàng )建和過(guò)期后的重建都是需要訪(fǎng)問(wèn)數據庫的；
本文主要從MySQL表結構設計規范和MySQL自身性能優(yōu)化兩方面來(lái)討論該如何對MySQL數據庫進(jìn)行優(yōu)化；
（1）數據庫,數據表一律使用前綴，前綴名稱(chēng)一般不超過(guò)5字；
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | # 正式數據庫名使用小寫(xiě)英文以及下劃線(xiàn)組成,盡量說(shuō)明是哪個(gè)應用或者系統在使用的；mcs_webimops_portal # 備份數據庫名使用正式庫名加上備份時(shí)間組成；ops_portal_20150621mcs_webim_20150622 # 相關(guān)應用的數據表使用同一前綴，前綴名稱(chēng)一般不超過(guò)5字；webim_userwebim_group # 備份數據表名使用正式表名加上備份時(shí)間組成；webim_user_20150620webim_group_20150620 |
（2）字段名稱(chēng)使用單詞組合完成，首字母小寫(xiě)，后面單詞的首字母大寫(xiě)（駝峰式），最好是帶表名前綴；
1 2 3 4 5 6 | # 如表webim_user的字段userIduserNameuserPassword# 表與表之間的相關(guān)聯(lián)字段要用統一名稱(chēng)；# 用盡量少的存儲空間來(lái)存儲一個(gè)字段的數據； |
實(shí)際關(guān)系模式設計中，一般遵循第三范式——在一個(gè)數據表中，非主鍵字段之間不能存在依賴(lài)關(guān)系；
具體可參考：規范化—數據庫設計原則
舉例：在頁(yè)面顯示我的好友列表；
1.遵循第三范式
1 2 | (用戶(hù)ID, 好友ID)(用戶(hù)ID, 用戶(hù)昵稱(chēng), 用戶(hù)郵箱, 注冊時(shí)間, 聯(lián)系電話(huà)) |
2.反范式化設計
1 2 | (用戶(hù)ID, 好友ID, 好友昵稱(chēng))(用戶(hù)ID, 用戶(hù)昵稱(chēng), 用戶(hù)郵箱, 注冊時(shí)間, 聯(lián)系電話(huà)) |
反范式化問(wèn)題：
1. 數據冗余；
2. 更新導致數據不一致問(wèn)題，可通過(guò)定期同步的手段來(lái)修改不一致數據；
反范式化優(yōu)勢：
減少讀取數據的開(kāi)銷(xiāo)，這點(diǎn)非常重要，需要根據不同場(chǎng)景來(lái)適當使用反范式化設計；
MySQL自身優(yōu)化主要從如下幾方面來(lái)介紹：
1. Query語(yǔ)句優(yōu)化；
2. 慢查詢(xún)優(yōu)化；
3. MySQL鎖機制分析優(yōu)化；
4. 參數配置優(yōu)化；
1 2 | show status;show engine innodb status; |
一些更加友好的第三方工具：mysqlreport（下載地址），mysqltunner，mytop等，可展示更加友好的狀態(tài)報告；
如果索引使用不當，其他任何優(yōu)化將毫無(wú)意義；
索引目的
索引類(lèi)似于書(shū)的目錄，通過(guò)不斷的縮小想要獲得數據的范圍來(lái)篩選出最終想要的結果，加快查詢(xún)速度；
索引類(lèi)型
索引分單列索引和組合索引，組合索引的一個(gè)索引包含多列；
MySQL索引類(lèi)型包括：
（1）普通索引——沒(méi)有任何限制
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | # 直接創(chuàng )建索引create index indexName on tableName(columnName(length));# 如果是char，varchar類(lèi)型，length長(cháng)度可以小于字段實(shí)際長(cháng)度；# 如果是blob和text類(lèi)型，必須指定length；# 修改表結構alter tableName add index indexName on (columnName(length));# 創(chuàng )建表時(shí)直接指定create table tableName( id int not null, username varchar(16) not null, index indexName (columnName(length)));# 刪除索引drop index indexName on tableName;# 查看索引show index from tableName; |
（2）唯一索引——索引列的值必須唯一，允許有空值，如果是組合索引，則列值的組合必須唯一；
1 2 3 4 5 6 7 8 9 10 11 12 | # 直接創(chuàng )建索引create unique index indexName on tableName(columnName(length));# 修改表結構alter tableName add unique indexName on (columnName(length));# 創(chuàng )建表時(shí)直接指定create table tableName( id int not null, username varchar(16) not null, unique indexName columnName(length)); |
（3）主鍵索引——是唯一索引的一種，但不允許有空值，一般是建表的時(shí)候直接創(chuàng )建主鍵索引；
1 2 3 4 5 | create table tableName( id int not null, username varchar(16) not null, primary key(id)); |
（4）組合索引——為多列添加索引；
1 2 3 4 | # 假設數據表中name, age, address, zip等多個(gè)字段，需要為name, age, zip建立組合索引；create index indexName on tableName(name(10), age, zip);# 或alter table tableName add index indexName (name(10), age, zip); |
對于varchar類(lèi)型字段，如果長(cháng)度過(guò)長(cháng)，最好限制一下索引的長(cháng)度，可以加快索引查詢(xún)速度，減少索引文件的大??；
最左前綴匹配
如上面的name，age，zip的組合索引，如下的組合都會(huì )用到該索引，可使用explain來(lái)進(jìn)行分析：
1 2 3 4 5 6 7 8 9 10 11 | select * from tableName where name="lee" and age=20 and zip="050000";select * from tableName where name="lee" and age=20;select * from tableName where name="lee";# 組合索引對于包含order by和group by的查詢(xún)也可發(fā)揮作用，同樣遵循最左前綴原則（對于Hash索引，對order by無(wú)效）；select * from tableName order by name, age, zip;select * from tableName where name="lee" order by age, zip;# 對于group by，一般需要先將記錄分組后放在新的臨時(shí)表中，然后分別進(jìn)行函數運算，如count()，sum()，max()等；# 如有恰當的索引，可使用索引來(lái)取代創(chuàng )建臨時(shí)表；select count(id) from tableName where sex='m' group by age, zip; |
查詢(xún)優(yōu)化器會(huì )自動(dòng)調整條件表達式的順序，以匹配組合索引；
建立索引時(shí)一定要注意順序，(key1, key2)和(key2, key1)完全不同；
建立索引的時(shí)機
一般來(lái)說(shuō)，在where和join中出現的列需要建立索引，mysql只對<，<=，=，>，>=，between，in以及某些時(shí)候（不以通配符%和_開(kāi)頭的查詢(xún)）的like才會(huì )使用索引；
1 2 | select a.name from table1 as a left join table2 b on a.name=b.username where b.age=20 and b.zip='053000';# 此時(shí)，需要對username，age和zip建立索引； |
索引的不足之處
索引有很大優(yōu)勢，但是不能濫用，需要根據實(shí)際情況來(lái)決定到底使不使用索引，該為哪些字段建索引，一般在查詢(xún)量占比較多的表才會(huì )建立索引；
使用索引注意事項
select * from users where YEAR(datetime)<2015;，會(huì )導致索引失效，進(jìn)行全表掃描；索引的原理
主要參考：MySQL索引原理及慢查詢(xún)優(yōu)化
join語(yǔ)句分為內連接和外連接；
內連接：
1 2 3 | select * from a inner join b on a.id = b.id;# 等價(jià)于select * from a,b where a.id = b.id; |
內連接就是檢索出與連接條件完全匹配的數據行；
外連接：
1 | select id, name, action from user as u left join user_action a on u.id = a.user_id; |
外連接保留了所有驅動(dòng)表的數據，匹配表中無(wú)法匹配的數據則以NULL輸出；
外連接工作原理
從左表讀取一條記錄，選出所有與on中條件匹配的右表記錄的（n條）數據，進(jìn)行連接，形成n條記錄（包括重復的行），如果右邊沒(méi)有與on條件匹配的記錄，那連接的字段都是null，繼續讀下一條；
找出所有在左表而不在右表中的記錄：
1 2 3 4 5 6 7 | # 注意：a.user_id必須聲明為NOT NULL，如果a，u兩表連接條件中的兩個(gè)列具有相同的名字，可使用using(col);select id, name, action from user as u left join user_action a on u.id = a.user_id where a.user_id is NULL;# 查詢(xún)時(shí)手動(dòng)指定索引select * from table1 use index (key1, key2) where key1=1 and key2=2 and key3=3;select * from table1 ignore index (key3) where key1=1 and key2=2 and key3=3; |
開(kāi)啟慢查詢(xún)日志：
1 2 3 | # 在my.cnf中增加如下配置：long_query_time = 1log-slow-queries = /var/log/mysql/mysql_slow.log |
# 將所有沒(méi)有使用索引的查詢(xún)記錄也記錄下來(lái)（根據需要決定是否開(kāi)啟）：log-queries-not-using-indexes慢查詢(xún)工具mysqlsla，可使用此工具對慢查詢(xún)日志進(jìn)行分析；
1 | # mysqlsla -lt slow /var/log/mysql/mysql_slow.log |
大多數慢查詢(xún)都是因為索引使用不當造成的，使用索引時(shí)一定要謹慎，其他原因還有查詢(xún)語(yǔ)句本身太過(guò)復雜（多表聯(lián)合查詢(xún)），數據表記錄數太多等；
鎖機制是影響查詢(xún)性能的另一個(gè)重要因素；
查詢(xún)的時(shí)間開(kāi)銷(xiāo)主要包括兩部分：
1. 查詢(xún)本身的計算時(shí)間；（主要受索引影響）
2. 查詢(xún)開(kāi)始前的等待時(shí)間；（受鎖機制影響）
MyISAM類(lèi)型表提供了表級別鎖定，可使用mysqlreport來(lái)查看等待表鎖定查詢(xún)所占的比例；
MyISAM的表鎖定允許多線(xiàn)程同時(shí)讀取數據，如select查詢(xún)，無(wú)需鎖等待；
對于更新操作，如update、insert、delete操作，會(huì )排斥對當前表的所有查詢(xún)，并且更新操作有著(zhù)默認的更高優(yōu)先級，即當表鎖釋放后，更新操作將先獲得鎖定，全部執行完畢后，才輪到讀取操作，應盡量避免在有大量查詢(xún)請求時(shí)，批量更改數據表，否則非常容易造成慢查詢(xún)；
可使用如下命令監視所有線(xiàn)程的狀態(tài)：
1 | show processlist\G; |
結論：
1 2 | 對于以查詢(xún)操作為主，并且更新操作耗時(shí)較低的應用，將不會(huì )存在太多的鎖等待，可以使用MyISAM存儲引擎；對于有頻繁數據更新并且查詢(xún)請求量也不低的站點(diǎn)，必須使用提供行鎖定功能的Innodb存儲引擎； |
Innodb存儲引擎提供了行鎖定的支持；
行鎖定優(yōu)勢：在select和update混合的情況下，行鎖定可以解決讀和寫(xiě)互斥的問(wèn)題，由于update操作和select操作來(lái)自不同的線(xiàn)程，并且針對的是不同行的記錄，可以并發(fā)進(jìn)行；
行鎖定并不一定總是好的：
1. 行鎖定的開(kāi)銷(xiāo)并不比表鎖定??；
2. 在全部都是更新操作的場(chǎng)景下，行鎖定耗時(shí)可能會(huì )更長(cháng)，雖然表鎖定每次只有一個(gè)線(xiàn)程處于Updating狀態(tài)，而行鎖定所有線(xiàn)程都是Updating狀態(tài)，但鎖定只是一種邏輯層面的約束，即使全部線(xiàn)程都是Updating狀態(tài)，但是磁盤(pán)的物理寫(xiě)操作還是串行執行的；
3. 對于全部查詢(xún)的場(chǎng)景，行鎖定也需要更多額外的開(kāi)銷(xiāo)，速度相對表鎖定略慢；
1 2 | show table status from DataBaseName where name='TableName';alter table tableName type=myisam; |
Innodb存儲引擎除了支持行鎖定，外鍵以及其易于修復的特性，另一個(gè)優(yōu)勢就是其支持事務(wù)（ACID），當然，事實(shí)上大多數站點(diǎn)都不需要事務(wù)級別的保障；
Innodb是通過(guò)預寫(xiě)日志（WAL）方式來(lái)實(shí)現事務(wù)的，即當有事務(wù)提交時(shí)，首先寫(xiě)入內存中的事務(wù)日志緩沖區，隨后當事務(wù)日志寫(xiě)入磁盤(pán)時(shí)，Innodb才更新實(shí)際的數據和索引；
如果選擇使用事務(wù)，那事務(wù)日志何時(shí)寫(xiě)入磁盤(pán)，就是一個(gè)優(yōu)化點(diǎn)了；
1 2 3 4 5 6 7 8 9 10 | # 事務(wù)提交時(shí)立即將事務(wù)日志寫(xiě)入磁盤(pán)，數據和索引也立即更新，符合持久性原則；innodb_flush_log_at_trx_commit = 1# 事務(wù)提交時(shí)不立即寫(xiě)入磁盤(pán)，每隔1S寫(xiě)入磁盤(pán)文件一次，并刷新到磁盤(pán)，同時(shí)更新數據和索引；# 如果mysql崩潰，事務(wù)日志緩沖區中最近1秒內的數據永久性丟失；innodb_flush_log_at_trx_commit = 0# 事務(wù)提交時(shí)立即寫(xiě)入磁盤(pán)文件，但間隔1S才會(huì )刷新磁盤(pán)，同時(shí)更新數據和索引；# 操作系統崩潰才會(huì )造成數據損失；innodb_flush_log_at_trx_commit = 2 |
注意：
“寫(xiě)入磁盤(pán)文件”只是將數據寫(xiě)入位于物理內存中的內核緩沖區，“刷新到磁盤(pán)“是將內核緩沖區中的數據真正寫(xiě)入到磁盤(pán)；
將innodb_flush_log_at_trx_commit設置為0，可以獲得最好的性能，同時(shí)數據丟失的可能性也最大；如果希望盡量避免數據丟失，可設置為2；
1 2 | # 設置Innodb數據和索引的內存緩沖池大小，一般可設置為服務(wù)器物理內存的80%；innodb_buffer_pool_size = 12G |
目的：將select的查詢(xún)結果緩存在內存中，以供下次直接獲??；
1 2 3 | query_cache_type = 1query_cache_size = 64Mquery_cache_limit = 1M |
對于緩存過(guò)期策略，MySQL采用的機制是：當一個(gè)表有更新操作后，涉及這個(gè)表的所有查詢(xún)緩存都會(huì )失效；
這個(gè)看場(chǎng)景，對于密集select操作且很少更新的表，比較適合使用查詢(xún)緩存；對于select和update混合的應用，不適合使用查詢(xún)緩存；
目的：在磁盤(pán)上創(chuàng )建臨時(shí)表非常耗時(shí)，開(kāi)銷(xiāo)大，需要降低在磁盤(pán)上創(chuàng )建臨時(shí)表的次數；
1 2 | # 盡量給臨時(shí)表設置較大的內存空間，當內存空間不夠時(shí)，MySQL將會(huì )啟用磁盤(pán)來(lái)保存臨時(shí)表；tmp_table_size = 512M |
MySQL采用多線(xiàn)程來(lái)處理并發(fā)連接，如果每次都新建連接，都要創(chuàng )建新的線(xiàn)程，在系統繁忙的時(shí)候，也會(huì )增加MySQL的開(kāi)銷(xiāo)；
1 2 3 | # 盡量使用持久連接，減少線(xiàn)程的重復創(chuàng )建；thread_cache_size = 100# 可以使MySQL緩存100個(gè)線(xiàn)程； |
聯(lián)系客服
