數(shù)據(jù)庫優(yōu)化主要步驟:
先通過mysql的命令定位到慢查詢
使用explain分析慢查詢
然后根據(jù)具體的情況考慮使用具體的優(yōu)化方案進行優(yōu)化茸塞,單機優(yōu)化方案常用的是使用索引和優(yōu)化sql語句捐腿,多機方案常用的是集群和分布式滞详,其他方案是使用redis緩存和ES全文檢索迅腔。
1.定位慢查詢
mysql默認是10s是慢查詢, 可以修改配置文件或者通過sql語句修改敞咧。
可以通過命令查看到有多少條慢查詢(show status like 'uptime')孽江。
開啟全局慢查詢?nèi)罩荆ゲ榭磎ysql的日志文件或者將慢查詢的結(jié)果輸出到表中來找到是哪條sql引起的問題宣鄙。
2.分析慢查詢
使用explain關(guān)鍵字查看sql運行時是否用創(chuàng)建索引袍镀,主要關(guān)注的是type和key,判斷是否設(shè)置了索引框冀,如果設(shè)置了索引,看索引是否生效敏簿。
如果有存在查詢被鎖住的情況明也,使用showprocesslist可以查看sql執(zhí)行情況,判斷是否被鎖
3.優(yōu)化
單機優(yōu)化
3nf(三范式) 是什么惯裕?
①數(shù)據(jù)庫的列不能再進行拆分成其他幾列
②數(shù)據(jù)庫一行數(shù)據(jù)通常由一個主鍵來維護
③如果一個表中的數(shù)據(jù)能夠從其他表中推導(dǎo)出來温数,不需要使用冗余字段
表設(shè)計的時候:
1.根據(jù)情況使用冗余字段來減少表的JOIN,提高sql執(zhí)行效率蜻势。 比如我們空間的相冊功能撑刺。相冊一個表,相冊中的照片一個表握玛。相冊表里面有生活照和風(fēng)景照字段够傍,再冗余一個數(shù)量字段來表示該類照片的總數(shù)。
上面當我們存入照片時冗余字段的總數(shù)也要改變挠铲,引發(fā)的數(shù)據(jù)更新問題解決辦法:
①多條sql (每次存入相片的時候同時也使用sql語句去增加冗余字段的數(shù)量)
②觸發(fā)器(使用數(shù)據(jù)庫的觸發(fā)器冕屯,把觸發(fā)條件設(shè)置為每當照片發(fā)生改變時去執(zhí)行相應(yīng)的操作就可以了)
2.選擇合適的存儲引擎myisam 或者innodb,如果表對事務(wù)要求不高拂苹,同時是以查詢和添加為主的安聘,我們考慮使用myisam存儲引擎. 比如貼吧的發(fā)帖表,回復(fù)表。對事務(wù)要求高浴韭,保存的數(shù)據(jù)都是重要數(shù)據(jù)丘喻,我們建議使用INNODB,比如訂單表,賬號表.
myisam 和innodb的區(qū)別念颈?
myisam 和innodb都是存儲引擎泉粉。
myisam不支持事務(wù),innodb支持事務(wù)
myisam是表鎖舍肠,innodb是行鎖
myisam支持全文索引搀继,innodb不支持
MyISAM查詢和添加的速度快,INNODB速度慢
mysql的索引
索引分類
主鍵索引 :當我們設(shè)置某個列為主鍵的時候后翠语,數(shù)據(jù)庫就會給該列創(chuàng)建索引叽躯。非空且唯一
普通索引 :設(shè)置任意一個字段為索引,允許有重復(fù)的值出現(xiàn)肌括。
唯一索引 :跟普通索引一樣点骑,只是不允許有重復(fù)的值出現(xiàn),比如字段里面的電話 身份證號等
全文索引 :用來對表中的文本域進行索引谍夭,全文索引只能對myisam支持黑滴,innodb不支持全文索引
其他分類
聚集索引 :數(shù)據(jù)的索引和索引的結(jié)構(gòu)在一起,比如innodb的主鍵索引
非聚集索引 :數(shù)據(jù)的索引和索引的結(jié)構(gòu)是分開的紧索,葉子節(jié)點儲存的是數(shù)據(jù)在磁盤上的地址袁辈。比如myisam的主鍵索引。
主鍵索引 :innodb的主鍵索引和輔助索引
輔助索引 :mysiam的主鍵索引和輔助索引
普通索引(單列索引) :該索引只在一個列上面創(chuàng)建
聯(lián)合索引(多列索引): 該索引在多個列上面創(chuàng)建 (注意最左匹配原則)
innodb索引結(jié)構(gòu)的特點:
B+tree的特點
它是多叉樹珠漂,每次查詢都要走到子節(jié)點晚缩,效率比較穩(wěn)定。
它葉子節(jié)點存儲的是key和數(shù)據(jù)媳危,非葉子節(jié)點存儲的是key 荞彼,可以充分利用每個子節(jié)點的儲存空間為16KB,可以儲存更多的key,這樣可以降低B+樹的層高待笑,查詢的時候效率比較高
葉子節(jié)點儲存完整的數(shù)據(jù)鸣皂,葉子節(jié)點是有序的,適合范圍查詢暮蹂。
myisam索引結(jié)構(gòu)的特點
myisam的數(shù)據(jù)和索引結(jié)構(gòu)是分開的寞缝,葉子節(jié)點儲存的是數(shù)據(jù)在磁盤上的地址
sql優(yōu)化技巧:
導(dǎo)致索引失效的一些寫法
創(chuàng)建索引的原則
分頁數(shù)據(jù)偏移量較大時的解決方案
小表驅(qū)動大表
多機優(yōu)化
主從同步 主庫從庫讀寫分離 binlog relaylog