MySQL是一個(gè)開放源代碼數(shù)據(jù)庫(kù)應(yīng)用程序撞反,可為大量數(shù)據(jù)創(chuàng)建有意義的結(jié)構(gòu)和可訪問(wèn)性逛尚。但是,大數(shù)據(jù)會(huì)帶來(lái)性能問(wèn)題搓谆。本文將為您提供MySQL技巧中的性能調(diào)優(yōu)炒辉,以提高其[性能]。
確保您正在使用最新的MySQL版本
如果您是舊數(shù)據(jù)庫(kù)或較舊的數(shù)據(jù)庫(kù)泉手,則可能不可行黔寇。但是,如果是這樣斩萌,請(qǐng)檢查[最新版本的MySQL]并升級(jí)到最新([最新的NoSQL數(shù)據(jù)庫(kù)]
較新版本默認(rèn)情況下具有性能改進(jìn)缝裤,從而使您無(wú)需再為MySQL問(wèn)題中的常見性能調(diào)整找到更多的解決方案。通常最好使用默認(rèn)或本機(jī)MySQL性能改進(jìn)颊郎,而不是腳本或配置文件憋飞。
選擇基于MyISAM的InnoDB
MyISAM的高級(jí)功能少于InnoDB∧房裕總體而言榛做,MyISAM的效率較低,而本機(jī)優(yōu)化增強(qiáng)功能較少内狸。例如瘤睹,InnoDB有一個(gè)聚簇索引,其中數(shù)據(jù)包含在頁(yè)面和連續(xù)的物理塊中答倡。如果某個(gè)值太大而無(wú)法容納在頁(yè)面上轰传,則InnoDB會(huì)將其遷移到其他位置,為其值編制索引-相關(guān)數(shù)據(jù)將保留在硬盤驅(qū)動(dòng)器上的同一位置瘪撇,從而縮短了檢索時(shí)間获茬。
硬件資源:MySQL中的系統(tǒng)級(jí)性能調(diào)整
處理器
處理器的速度顯示了計(jì)算機(jī)的速度港庄。該頂部命令會(huì)給你洞察每個(gè)進(jìn)程的CPU和內(nèi)存使用情況-換句話說(shuō),是如何被使用的資源恕曲。使用MySQL時(shí)鹏氧,請(qǐng)注意該特定進(jìn)程的使用百分比。太高了佩谣,瓶頸很可能是您的機(jī)器把还,這意味著需要對(duì)其進(jìn)行升級(jí)。
記憶
調(diào)整或改善內(nèi)存將增加MySQL服務(wù)器中的總RAM并提高性能茸俭。轉(zhuǎn)到服務(wù)器配置部分吊履,您將在其中看到用于優(yōu)化內(nèi)存的緩沖池大小命令。
硬盤
如果您使用HDD進(jìn)行存儲(chǔ)调鬓,則升級(jí)到固態(tài)驅(qū)動(dòng)器可以提高性能艇炎。注意與其他資源相比,MySQL正在使用多少磁盤腾窝。如果基本上不成比例缀踪,請(qǐng)?zhí)砑痈啻鎯?chǔ)空間。諸如sar或iotop(sysstat軟件包)之類的工具可用于監(jiān)視磁盤輸入與輸出速率虹脯。
網(wǎng)絡(luò)
網(wǎng)絡(luò)帶寬的瓶頸會(huì)導(dǎo)致數(shù)據(jù)包丟失驴娃,延遲甚至完全停機(jī)。您應(yīng)該能夠提供正常的數(shù)據(jù)庫(kù)流量循集。
MySQL中的軟件級(jí)性能調(diào)優(yōu)
調(diào)整腳本可以實(shí)現(xiàn)更高效的數(shù)據(jù)庫(kù)查詢托慨,MySQL配置文件和最佳的數(shù)據(jù)庫(kù)設(shè)計(jì)。
專家提示:調(diào)整軟件配置可能會(huì)導(dǎo)致更多問(wèn)題暇榴,而不是解決的問(wèn)題厚棵。我建議一次只進(jìn)行少量更改,每次都進(jìn)行測(cè)試蔼紧,因?yàn)檫@樣可以更輕松地[識(shí)別問(wèn)題]并總體評(píng)估對(duì)性能的影響婆硬。
代碼剖析
使用諸如[Stackify Prefix之]類的代碼分析工具,可以在編寫代碼時(shí)對(duì)代碼進(jìn)行概要分析和測(cè)試奸例。通過(guò)在編寫代碼時(shí)驗(yàn)證代碼的性能彬犯,Prefix用戶可以將更好的代碼推送到測(cè)試中,從生產(chǎn)中獲得更少的支持票查吊,并擁有更快樂(lè)的開發(fā)經(jīng)理谐区。前綴的概要分析和跟蹤功能甚至可以幫助最有經(jīng)驗(yàn)的開發(fā)人員找到慢速的SQL查詢,隱藏的異常等等逻卖。
發(fā)現(xiàn)性能不佳的SQL查詢宋列,ORM生成的查詢以及以前未知的瓶頸。跟蹤每個(gè)SQL調(diào)用參數(shù)评也,受影響的記錄和下載時(shí)間炼杖。前綴還可以輕松發(fā)現(xiàn)可怕的N + 1模式灭返。前綴涵蓋了您的整個(gè)技術(shù)堆棧,并且在Windows和Mac操作系統(tǒng)上均支持.NET坤邪,Java熙含,PHP,Node.js艇纺,Python和Ruby怎静。
查詢
查詢數(shù)據(jù)庫(kù)時(shí),要求其檢索與您輸入的值匹配的特定數(shù)據(jù)黔衡。警惕可能在后臺(tái)耗盡表格的自動(dòng)查詢蚓聘。使用show processlist函數(shù)密切關(guān)注它們,并殺死不需要的函數(shù)员帮。一些種植者比其他種植者花費(fèi)更長(zhǎng)的時(shí)間或粮。如果允許不必要的MySQL進(jìn)程運(yùn)行导饲,則這些進(jìn)程將編譯并阻止其他用戶訪問(wèn)和修改數(shù)據(jù)捞高。
自動(dòng)性能改善
我們對(duì)前三項(xiàng)自動(dòng)性能改進(jìn)工具的建議是MySQLTuner,tuning-primer和PHPMyAdmin Adviser渣锦。第一個(gè)工具M(jìn)ySQLTuner最適用于現(xiàn)代MySQL數(shù)據(jù)庫(kù)硝岗。它找到可以優(yōu)化的部分,建議進(jìn)行調(diào)整袋毙,并且與MySQL 8.x兼容型檀。盡管Tuning-primer較舊,是MySQL 5.5至5.7版听盖,但其他兩個(gè)功能也具有類似的功能胀溺。
服務(wù)器調(diào)整
同樣,請(qǐng)確保僅在此處進(jìn)行增量更改皆看。我們將調(diào)整/etc/mysql/my.cnf文件仓坞。
**innodb_buffer_pool_size **—
該命令配置將系統(tǒng)內(nèi)存劃分為數(shù)據(jù)庫(kù)的數(shù)據(jù)緩存。數(shù)據(jù)塊越大腰吟,值應(yīng)越大无埃。進(jìn)行更改時(shí),請(qǐng)注意有多少RAM用于其他系統(tǒng)資源毛雇。
**innodb_io_capacity **—
這顯示了存儲(chǔ)設(shè)備的輸入/輸出速率嫉称。硬限制是您使用的任何類型的存儲(chǔ)驅(qū)動(dòng)器×榇可以進(jìn)行調(diào)整以更好地將數(shù)據(jù)庫(kù)與硬件對(duì)齊
**query_cache_size **—
這設(shè)置了未決MySQL查詢的緩存大小织阅。我們建議從小開始,大約10MB震捣,然后再增加到100-200MB蒲稳,但不要再增加氮趋。如果查詢太多,并且收到“等待緩存鎖等待”消息江耀,請(qǐng)嘗試使用EXPLAIN函數(shù)來(lái)評(píng)估單個(gè)查詢以及優(yōu)化每個(gè)查詢的方法剩胁。
**max_connection **—
這顯示了數(shù)據(jù)庫(kù)中啟用的連接數(shù)。為了克服“連接過(guò)多”的錯(cuò)誤祥国,請(qǐng)?jiān)黾哟藬?shù)字昵观。
解釋
現(xiàn)代的MySQL數(shù)據(jù)庫(kù)包括解釋功能。如果在查詢開始時(shí)使用此表達(dá)式舌稀,則查詢將被讀取和評(píng)估啊犬。如果表達(dá)式效率低下或結(jié)構(gòu)出現(xiàn)異常,EXPLAIN可以幫助您查明它們壁查。然后觉至,您可以調(diào)整查詢短語(yǔ),以消除性能消耗和不必要的表掃描睡腿。
JOIN语御,UNION,DISTINCT
雖然“內(nèi)部聯(lián)接”是要使用的首選類型席怪,但在適用時(shí)也應(yīng)使用聯(lián)接应闯,并集和不重復(fù)。外部聯(lián)接在相關(guān)列之外搜索無(wú)關(guān)的數(shù)據(jù)挂捻。在某些情況下碉纺,可能需要數(shù)據(jù),但是這對(duì)于搜索不相關(guān)的數(shù)據(jù)會(huì)造成不必要的性能消耗刻撒。
查詢有時(shí)涉及DISTINCT和UNION命令骨田。再一次,如果需要声怔,請(qǐng)使用它們态贤,但請(qǐng)注意,它們需要從數(shù)據(jù)庫(kù)中進(jìn)行額外的讀取和排序捧搞。如果不需要它們抵卫,最好選擇一個(gè)更有效的命令。
索引可能有用
該SELECT ... WHERE結(jié)構(gòu)是常用的數(shù)據(jù)庫(kù)中使用胎撇。他們處理過(guò)濾介粘,檢索和評(píng)估結(jié)果。為連接的表創(chuàng)建一個(gè)小的索引集是一種有用的結(jié)構(gòu)晚树,它允許將查詢指向索引姻采,以便在運(yùn)行時(shí)加快索引的速度。
避免在查詢謂詞中使用函數(shù)和通配符
避免在查詢謂詞中使用函數(shù)爵憎。例如慨亲,如果要?jiǎng)?chuàng)建一個(gè)UPPER表示法(創(chuàng)建一個(gè)函數(shù))婚瓜,這將迫使您在SELECT操作中進(jìn)行操作。按此順序工作會(huì)使查詢的工作量加倍刑棵。
使用通配符也是如此—通配符% 表示零個(gè)或多個(gè)字符巴刻。例如,表達(dá)式' %345 '包含所有以345結(jié)尾的值蛉签。%代表該字符串之前出現(xiàn)的任何字符胡陪,包括不存在任何后續(xù)字符。這會(huì)強(qiáng)制搜索對(duì)所有這些可能性進(jìn)行全表掃描碍舍。
使用**SELECT ***操作時(shí)柠座,請(qǐng)記住,在掃描特定列時(shí)片橡,您可能需要縮短搜索查詢時(shí)間妈经,因?yàn)椴⒎撬袛?shù)據(jù)庫(kù)列都在被掃描。
的ORDER BY由所選擇的列表達(dá)式排列的結(jié)果捧书。這也適用于同時(shí)排序兩列吹泡。但是,兩列均應(yīng)按升序或降序均勻排序鳄厌。如果排序不均勻荞胡,將會(huì)降低性能妈踊。為此添加索引以加快排序了嚎。