Mysql怎么運行環(huán)境優(yōu)化_Mysql運行環(huán)境有哪些優(yōu)化
你知道Mysql是怎么運行的嗎?知道運行環(huán)境怎么樣嗎?下面由學(xué)習(xí)啦小編為大家整理的Mysql運行環(huán)境優(yōu)化,希望大家喜歡!
Mysql運行環(huán)境優(yōu)化
一、修改Linux默認(rèn)的IO調(diào)度算法.
linux默認(rèn)的IO調(diào)度算法為cfq,需要修改為dealine,如果是SSD或者PCIe-SSD設(shè)備,需要修改為noop,可以使用下面兩種修改方式。
1、在線動態(tài)修改,重啟失效。
代碼如下:
echo “deadline” > /sys/block/sda/queue/scheduler
tips:這里的sda代表你需要修改的硬盤,根據(jù)你實際情況修改。
2、修改/etc/grub.conf,永久生效。
修改/etc/grub.conf配置文件,在kernel那行增加一個配置,例如:
代碼如下:
kernel /vmlinuz-2.6.32-279.el6.x86_64 ro root=UUID=e01d6bb4-bd74-404f-855a-0f700fad4de0 rd_NO_LUKS rd_NO_LVM LANG=en_US.UTF-8 rd_NO_MD SYSFONT=latarcyrheb-sun1
6 crashkernel=auto KEYBOARDTYPE=pc KEYTABLE=us rd_NO_DM elevator=deadline rhgb quiet
主要關(guān)注elevator這個參數(shù),設(shè)置內(nèi)核的話需要重啟系統(tǒng)才能生效。
最后可以通過 cat /sys/block/sda/queue/scheduler 觀察一下,修改前和修改后的區(qū)別。
二、擴(kuò)大文件描述符
這個是經(jīng)常修改的參數(shù),高并發(fā)的程序都會修改。
1、動態(tài)修改,重啟失效,只能使用root,并且當(dāng)前session有效。
代碼如下:
ulimit -n 51200
2、修改配置文件,永久生效。
在/etc/security/limits.conf配置文件中增加一行
代碼如下:
* hard nofile 51200
擴(kuò)大可開啟進(jìn)程數(shù) nprocess /etc/security/limits.conf
在/etc/security/limits.conf配置文件中增加一行
代碼如下:
* hard nproc 51200
最后修改/etc/pam.d/login文件添加
代碼如下:
session required /lib64/security/pam_limits.so
重啟系統(tǒng)以后使用 ulimit -a 命令查看是否生效。
三、禁用numa特性
新一代架構(gòu)的NUMA不適合跑數(shù)據(jù)庫,它本意是為了提高內(nèi)存利用率,但是實際效果不好,反而可能導(dǎo)致一CPU的內(nèi)存尚有剩余,但是另外一個不夠用,發(fā)生swap的問題,因此建議關(guān)閉或者修改NUMA的調(diào)度機(jī)制。
1、修改/etc/grub.conf關(guān)閉NUMA,重啟后生效。
代碼如下:
kernel /vmlinuz-2.6.32-279.el6.x86_64 ro root=UUID=e01d6bb4-bd74-404f-855a-0f700fad4de0 rd_NO_LUKS rd_NO_LVM LANG=en_US.UTF-8 rd_NO_MD SYSFONT=latarcyrheb-sun1
6 crashkernel=auto KEYBOARDTYPE=pc KEYTABLE=us rd_NO_DM elevator=deadline numa=off rhgb quiet
2、修改/etc/init.d/mysql或者mysqld_safe腳本,設(shè)置啟動mysqld進(jìn)程時的NUMA調(diào)度機(jī)制,
MySQL調(diào)試和優(yōu)化技巧
1. 擁有足夠的物理內(nèi)存來把整個InnoDB文件加載到內(nèi)存中——在內(nèi)存中訪問文件時的速度要比在硬盤中訪問時快的多。
2. 不惜一切代價避免使用Swap交換分區(qū) – 交換時是從硬盤讀取的,它的速度很慢。
3. 使用電池供電的RAM(注:RAM即隨機(jī)存儲器)。
4. 使用高級的RAID(注:Redundant Arrays of Inexpensive Disks,即磁盤陣列) – 最好是RAID10或更高。
5. 避免RAID5(注:一種存儲性能、數(shù)據(jù)安全和存儲成本兼顧的存儲解決方案) – 確保數(shù)據(jù)庫完整性的校驗是要付出代價的。
6. 將操作系統(tǒng)和數(shù)據(jù)分區(qū)分開,不僅僅是邏輯上,還包括物理上 – 操作系統(tǒng)的讀寫操作會影響數(shù)據(jù)庫的性能。
7. 把MySQL臨時空間和復(fù)制日志與數(shù)據(jù)放到不同的分區(qū) – 當(dāng)數(shù)據(jù)庫后臺從磁盤進(jìn)行讀寫操作時會影響數(shù)據(jù)庫的性能。
8. 更多的磁盤空間等于更快的速度。
9. 更好更快的磁盤。
10. 使用SAS(注: Serial Attached SCSI,即串行連接SCSI)代替SATA(注:SATA,即串口硬盤)。
11. 較小的硬盤 比 較大的硬盤快,尤其是在RAID配置的情況下。
12. 使用電池支持的高速緩存RAID控制器。
13. 避免使用軟件磁盤陣列。
14. 考慮為數(shù)據(jù)分區(qū)使用固態(tài)IO卡 (不是磁盤驅(qū)動器) – 這些卡能夠為幾乎任何數(shù)量的數(shù)據(jù)支持2GB/s的寫入速度。
15. 在Linux中設(shè)置swappiness的值為0 – 在數(shù)據(jù)庫服務(wù)器中沒有理由緩存文件,這是一個服務(wù)器或臺式機(jī)的優(yōu)勢。
16. 如果可以的話,使用 noatime 和 nodirtime 掛載文件系統(tǒng) – 沒有理由更新訪問數(shù)據(jù)庫文件的修改時間。
17. 使用 XFS 文件系統(tǒng) – 一種比ext3更快、更小的文件系統(tǒng),并且有許多日志選項, 而且ext3 已被證實與MySQL有雙緩沖問題。
18. 調(diào)整 XFS 文件系統(tǒng)日志和緩沖變量 – 為了最高性能標(biāo)準(zhǔn)。
19. 在 Linux 系統(tǒng)中, 使用 NOOP 或者 DEADLINE IO 定時調(diào)度程序 – 同 NOOP 和 DEADLINE定時調(diào)度程序相比,這個 CFQ 和 ANTICIPATORY 定時調(diào)度程序 顯得非常慢。
20. 使用64位的操作系統(tǒng) – 對于MySQL,會有更大的內(nèi)存支持和使用。
mysql優(yōu)化sql語句查詢的方法
1.對查詢進(jìn)行優(yōu)化,應(yīng)盡量避免全表掃描,首先應(yīng)考慮在 where 及 order by 涉及的列上建立索引。
2.應(yīng)盡量避免在 where 子句中使用!=或<>操作符,否則將引擎放棄使用索引而進(jìn)行全表掃描。
3.應(yīng)盡量避免在 where 子句中對字段進(jìn)行 null 值判斷,否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描,如:
select id from t where num is null
可以在num上設(shè)置默認(rèn)值0,確保表中num列沒有null值,然后這樣查詢:
select id from t where num=0
4.應(yīng)盡量避免在 where 子句中使用 or 來連接條件,否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描,如:
select id from t where num=10 or num=20
可以這樣查詢:
select id from t where num=10
union all
select id from t where num=20
5.下面的查詢也將導(dǎo)致全表掃描:
select id from t where name like '%abc%'
若要提高效率,可以考慮全文檢索。
6.in 和 not in 也要慎用,否則會導(dǎo)致全表掃描,如:
select id from t where num in(1,2,3)
對于連續(xù)的數(shù)值,能用 between 就不要用 in 了:
select id from t where num between 1 and 3
7.如果在 where 子句中使用參數(shù),也會導(dǎo)致全表掃描。因為SQL只有在運行時才會解析局部變量,但優(yōu)化程序不能將訪問計劃的選擇推遲到運行時;它必須在編譯時進(jìn)行選擇。然而,如果在編譯時建立訪問計劃,變量的值還是未知的,因而無法作為索引選擇的輸入項。如下面語句將進(jìn)行全表掃描:
select id from t where num=@num
可以改為強(qiáng)制查詢使用索引:
select id from t with(index(索引名)) where num=@num
8.應(yīng)盡量避免在 where 子句中對字段進(jìn)行表達(dá)式操作,這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如:
select id from t where num/2=100
應(yīng)改為:
select id from t where num=100*2
9.應(yīng)盡量避免在where子句中對字段進(jìn)行函數(shù)操作,這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如:
select id from t where substring(name,1,3)='abc'--name以abc開頭的id
select id from t where datediff(day,createdate,'2005-11-30')=0--'2005-11-30'生成的id
應(yīng)改為:
select id from t where name like 'abc%'
select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'
10.不要在 where 子句中的“=”左邊進(jìn)行函數(shù)、算術(shù)運算或其他表達(dá)式運算,否則系統(tǒng)將可能無法正確使用索引。
11.在使用索引字段作為條件時,如果該索引是復(fù)合索引,那么必須使用到該索引中的第一個字段作為條件時才能保證系統(tǒng)使用該索引,否則該索引將不會被使用,并且應(yīng)盡可能的讓字段順序與索引順序相一致。
12.不要寫一些沒有意義的查詢,如需要生成一個空表結(jié)構(gòu):
select col1,col2 into #t from t where 1=0
這類代碼不會返回任何結(jié)果集,但是會消耗系統(tǒng)資源的,應(yīng)改成這樣:
create table #t(...)
13.很多時候用 exists 代替 in 是一個好的選擇:
select num from a where num in(select num from b)
用下面的語句替換:
select num from a where exists(select 1 from b where num=a.num)
14.并不是所有索引對查詢都有效,SQL是根據(jù)表中數(shù)據(jù)來進(jìn)行查詢優(yōu)化的,當(dāng)索引列有大量數(shù)據(jù)重復(fù)時,SQL查詢可能不會去利用索引,如一表中有字段sex,male、female幾乎各一半,那么即使在sex上建了索引也對查詢效率起不了作用。
15.索引并不是越多越好,索引固然可以提高相應(yīng)的 select 的效率,但同時也降低了 insert 及 update 的效率,因為 insert 或 update 時有可能會重建索引,所以怎樣建索引需要慎重考慮,視具體情況而定。一個表的索引數(shù)最好不要超過6個,若太多則應(yīng)考慮一些不常使用到的列上建的索引是否有必要。
16.應(yīng)盡可能的避免更新 clustered 索引數(shù)據(jù)列,因為 clustered 索引數(shù)據(jù)列的順序就是表記錄的物理存儲順序,一旦該列值改變將導(dǎo)致整個表記錄的順序的調(diào)整,會耗費相當(dāng)大的資源。若應(yīng)用系統(tǒng)需要頻繁更新 clustered 索引數(shù)據(jù)列,那么需要考慮是否應(yīng)將該索引建為 clustered 索引。
17.盡量使用數(shù)字型字段,若只含數(shù)值信息的字段盡量不要設(shè)計為字符型,這會降低查詢和連接的性能,并會增加存儲開銷。這是因為引擎在處理查詢和連接時會逐個比較字符串中每一個字符,而對于數(shù)字型而言只需要比較一次就夠了。
18.盡可能的使用 varchar/nvarchar 代替 char/nchar ,因為首先變長字段存儲空間小,可以節(jié)省存儲空間,其次對于查詢來說,在一個相對較小的字段內(nèi)搜索效率顯然要高些。
19.任何地方都不要使用 select * from t ,用具體的字段列表代替“*”,不要返回用不到的任何字段。
20.盡量使用表變量來代替臨時表。如果表變量包含大量數(shù)據(jù),請注意索引非常有限(只有主鍵索引)。