MySQL數(shù)據(jù)庫優(yōu)化漫談

點(diǎn)擊上方藍(lán)色字體，選擇“標(biāo)星公眾號”

優(yōu)質(zhì)文章，第一時間送達(dá)

1.表設(shè)計
    遵循三范式,但必要的時候做數(shù)據(jù)冗余，舉例說明：在權(quán)限模型中可能會用到5張表 用戶表、角色表、權(quán)限表，還有用戶角色關(guān)聯(lián)表和角色權(quán)限關(guān)聯(lián)表。如果此時要通過用戶查詢權(quán)限則必須關(guān)聯(lián)查詢或者使用多條sql查詢，此時可以在用戶表增加一個字段來存儲用戶的權(quán)限（例如將權(quán)限值使用逗號隔開），這樣可以如果查詢某用戶的權(quán)限可以直接在用戶表中查詢，查詢后再用程序來處理。

    設(shè)計表時要選用合適的存儲引擎，對于經(jīng)常查詢的表，并且不需要事務(wù)等應(yīng)用，優(yōu)先選用MyISAM（例如新聞表），如果需要事務(wù)處理對穩(wěn)定性要求高的表優(yōu)先選用InnoDB存儲引擎（例如訂單表，訂單商品表），目前InnoDB已經(jīng)成為新版MySQL的默認(rèn)存儲引擎，在高并發(fā)寫入時的穩(wěn)定性相比MyISAM更高,在讀取速度上速度也比較快，所以推薦使用此引擎。不需要永久保存的數(shù)據(jù)可以選用Memory存儲引擎（例如session，購物車）

    對于不同的字段選用合適的字段類型，例如IP地址應(yīng)該存儲為整型，見將IP以整型的方式存儲到數(shù)據(jù)庫中。固定長度的字符串要使用char，例如md5加密的密碼。

2.分表技術(shù)
    橫向分表（各個表的字段類型與數(shù)量是完全一致的），例如用戶表，可以按用戶首字母橫向切分；交易記錄可以按照年份或者月份切分，每張表的字段一致，至于按什么規(guī)則來來區(qū)分可以看具體的業(yè)務(wù)。
    縱向分表（各個表的字段不一致，但條數(shù)是一致的），例如某商城網(wǎng)站用戶表使用MyISAM引擎，可以應(yīng)對用戶登錄時的查詢操作，但是用戶之間轉(zhuǎn)賬需要事務(wù)來保證安全，這樣就可以將用戶余額字段分離出來組成一張InnoDB引擎的新表，和用戶表關(guān)聯(lián)，既滿足高效查詢，又滿足穩(wěn)定性。
    另外還可以進(jìn)行分區(qū)，或者分庫，都是類似的思路。
3.索引優(yōu)化
    常見的索引有 主鍵索引、唯一索引、普通索引、全文索引（僅MyISAM存儲引擎支持，并且不支持中文，如果需要支持中文需要安裝插件）。通常需要在條件字段、排序字段、分組字段以及關(guān)聯(lián)字段上建立對應(yīng)索引。但重復(fù)率很高的字段不宜建立索引,比如狀態(tài)字段(是否被刪除,是否上架等),emum類型(性別等)，另外建立索引會對增刪改操作的速度有影響,因此頻繁更新的字段不適合建立索引,例如文章的點(diǎn)擊量。
4.SQL優(yōu)化
在開發(fā)時可以使用explain 測試sql語句
在測試結(jié)果會有如下參數(shù)，簡要解釋一下
select_type    
查詢的方式 SIMPLE表示select類型,沒有連接或者子查詢,PRIMARY表示主查詢(注意:不是主鍵,例如子查詢時的外層查詢,UNION查詢時的第一個select),DEPENDENT SUBQUERY表示子查詢語句,UNION表示UNION查詢時除了PRIMARY(第一條語句)之外的語句
table        表名
type        掃描類型（重要）如果是All表示全表掃描，效率低;如果是const表示最多有一行與結(jié)果匹配，效率高;system表示表中僅有一條數(shù)據(jù),肯定高效;eq_ref表示所以用到主鍵或者唯一索引;ref表示用到普通索引;range表示查詢一個區(qū)間(范圍的數(shù)據(jù));index表示都是通過索引查詢性能一般
possible_keys    可能使用到的索引（重要）
key        實(shí)際用到的索引（重要）
key_len        索引長度
ref
rows        MySQL認(rèn)為它執(zhí)行查詢時必須檢查的行數(shù)（重要）越小越好        
Extra        額外信息 Using filesort表示查詢中使用了order by 并且無法利用索引排序，如果確實(shí)不需要排序可以在SQL語句末尾增加order by null.Using temporary某些操作使用了臨時表,不要.Using where 使用索引.

    編寫代碼以及SQL語句需要注意的如下幾項(xiàng)：

    1）如果查詢條件中有or,則所有使用到的字段必須都有索引才有效；

    2）like查詢條件中如果以%開頭則索引失效；

    3）聯(lián)合(多列)索引 只有使用左邊的列索引才能生效；

    4）where后有多個查詢條件應(yīng)優(yōu)先使用能夠過濾數(shù)據(jù)量大的條件（先后順序很重要）；

    5）不要使用子查詢（效率很低，除非數(shù)據(jù)量非常少），盡可能少用關(guān)聯(lián)查詢，寧可份多次查詢，有條件可以封裝成存儲過程。

    6）不要在循環(huán)中使用SQL語句，如果是查詢盡可能一次查詢?nèi)缓笸ㄟ^程序來計算。如果是insert可使用擴(kuò)展插入,即類似insert into tablename values (11,22),(33,33),(45,42)。
    檢測MySQL的索引使用情況可以使用以下命令
show global status where Variable_name like 'Handler_read%'; 
解釋一下主要參數(shù)
Handler_read_first 全索引掃描的次數(shù)，使用到了索引，但是全部索引；
Handler_read_key 越大越好，使用索引的情況；
Handler_read_rnd_next 表示沒有命中，值越大索引利用率越低；

對于已經(jīng)上線的項(xiàng)目可以開啟MySQL的慢查詢來定位低效率的SQL，見使用MySQL的慢查詢?nèi)罩菊业降托У腟QL語句并通過explain分析進(jìn)行優(yōu)化
使用如下命令
show global status like 'Com%';
show global status like 'InnoDB_rows%';
show variables like  'long_query_time';
show variables like  '%slow%';

查看MySQL各種SQL的執(zhí)行頻度 以及慢查詢狀態(tài)

使用optimize table 表名;命令來優(yōu)化表,執(zhí)行時會占用大量資源,所以建議在用戶訪問量少的時刻執(zhí)行。
5.配置優(yōu)化

修改mysql的配置文件，通常在windows下為my.ini，在Linux下為my.cnf

query_cache_size = 64M

該值可以通過使用SHOW STATUS LIKE 'Qcache%';命令來查看MySQL狀態(tài)來進(jìn)行相應(yīng)的更改，如果Qcache_lowmem_prunes的值非常大，則表明經(jīng)常出現(xiàn)緩沖不夠的情況需要增加cache值；如果Qcache_hits的值非常大，則表明查詢緩沖使用非常頻繁，是理想狀態(tài)，如果該值較小反而會影響效率，那么可以考慮不用查詢緩沖。
對于MyISAM設(shè)置
key_buffer_size = 32M
MyISAM表會使用操作系統(tǒng)的緩存來緩存數(shù)據(jù)，設(shè)置的值取決于系統(tǒng)內(nèi)存、索引大小、數(shù)據(jù)量以及負(fù)載。
對于InnoDB設(shè)置
innodb_buffer_pool_size = 2.4G
可以設(shè)置內(nèi)存的70%左右，當(dāng)然要考慮內(nèi)存的整體占用情況。

6.硬件架構(gòu)設(shè)計優(yōu)化

更換硬件 硬盤更換為SSD 可提高幾倍的效率，
延時寫入(更新),對于實(shí)時性較小的數(shù)據(jù)可以先將數(shù)據(jù)寫入到文件,每隔一段時間批量寫入(更新)到數(shù)據(jù)庫中
搭建MySQL主從同步架構(gòu)，使用讀寫分離技術(shù)
使用sphinx 全文檢索服務(wù)器， 減輕MySQL服務(wù)器的壓力

版權(quán)聲明：本文為博主原創(chuàng)文章，遵循 CC 4.0 BY-SA 版權(quán)協(xié)議，轉(zhuǎn)載請附上原文出處鏈接和本聲明。

本文鏈接：

https://blog.csdn.net/xiaowuc/article/details/11988753