快捷導(dǎo)航

淺談MySQL的性能優(yōu)化

更新時(shí)間：2023年08月25日 09:40:49 作者：摳腳的大灰狼

這篇文章主要介紹了淺談MySQL的性能優(yōu)化,MySQL性能優(yōu)化是通過對(duì)數(shù)據(jù)庫的配置、查詢優(yōu)化以及索引優(yōu)化等手段提高數(shù)據(jù)庫的響應(yīng)速度和處理能力,本文從多個(gè)層面對(duì)mysql性能優(yōu)化進(jìn)行了小結(jié),需要的朋友可以參考下

服務(wù)器層面

innodb_buffer_pool_size

將緩沖池的大小設(shè)置的盡可能大，比如設(shè)為總內(nèi)存的3/4。這樣可以減少mysql的磁盤IO次數(shù)，使得盡可能地從緩沖池里讀數(shù)據(jù)

innodb_log_file_size

在生產(chǎn)環(huán)境下，可以盡可能地把一些日志開關(guān)給關(guān)掉。比如通用查詢?nèi)罩荆樵內(nèi)罩?，錯(cuò)誤日志。并且，將redo log的大小設(shè)置的足夠大，避免由于redo log過小，導(dǎo)致頻繁的臟頁刷磁盤

innodb_flush_log_at_trx_commit

當(dāng)對(duì)數(shù)據(jù)的安全性要求不是那么高的時(shí)候，可以考慮將此參數(shù)設(shè)為0或者2，減少刷磁盤頻率

表設(shè)計(jì)層面

對(duì)于統(tǒng)計(jì)和分析類等對(duì)實(shí)時(shí)性要求不高的需求（OLAP），設(shè)計(jì)中間表，避免直接查大量的raw data
創(chuàng)建合理的冗余字段，以減少連表查詢
表中不經(jīng)常使用的字段，或者存儲(chǔ)了過多字段，考慮拆表
每張表都要有個(gè)主鍵，且主鍵最好是自增的int類型

SQL語句層面

索引優(yōu)化

為經(jīng)常出現(xiàn)在where條件中的字段，需要排序的字段創(chuàng)建合適的索引（當(dāng)讀多寫少時(shí)，可以考慮創(chuàng)建索引）
當(dāng)需要對(duì)多個(gè)列建索引時(shí)，優(yōu)先考慮組合索引，而不是多個(gè)單列索引，并且合理的組織組合索引的順序，將篩選粒度大的列，放到組合索引的最左
盡量使用覆蓋索引，而不要使用SELECT * ，可避免回表查詢

LIMIT優(yōu)化

若預(yù)計(jì)查詢結(jié)果只有1條，使用LIMIT 1可以提前終止全表掃描
子查詢優(yōu)化

當(dāng)使用LIMIT進(jìn)行分頁時(shí)，頁碼過大時(shí)，LIMIT的偏移量會(huì)很大，此時(shí)會(huì)導(dǎo)致MySQL掃描大量不需要的行，然后再丟棄，性能很差。

比如 LIMIT 10000,20 ，會(huì)先掃描前10000行，然后丟棄，最后取10000后的20行。

此時(shí)可以使用子查詢來做優(yōu)化

-- 原SQL
select * from product limit 10000,20;
-- 優(yōu)化后的SQL
select * from product where id >(select id from product order by id limit 10000,1) limit 20;
-- 由于子查詢使用了id主鍵索引，且查詢是覆蓋索引，可以很快的定位到10000的位置
-- 當(dāng)單表查詢，且主鍵已經(jīng)是排序好的，可以直接簡寫如下
select * from product where id > 10000 limit 20;