mysql 參數(shù)調(diào)優(yōu)（11）之innodb_buffer_pool_instances設(shè)置多個(gè)緩沖池實(shí)例

MySQL 5.5引入了緩沖實(shí)例作為減小內(nèi)部鎖爭(zhēng)用來(lái)提高M(jìn)ySQL吞吐量的手段。在5.5版本這個(gè)對(duì)提升吞吐量幫助很小，然后在MySQL 5.6版本這個(gè)提升就非常大了，所以在MySQL5.5中你可能會(huì)保守地設(shè)置innodb_buffer_pool_instances=4，在MySQL 5.6和5.7中你可以設(shè)置為8-16個(gè)緩沖池實(shí)例。設(shè)置后觀察會(huì)覺(jué)得性能提高不大，但在大多數(shù)高負(fù)載情況下，它應(yīng)該會(huì)有不錯(cuò)的表現(xiàn)。對(duì)了，不要指望這個(gè)設(shè)置能減少你單個(gè)查詢(xún)的響應(yīng)時(shí)間。這個(gè)是在高并發(fā)負(fù)載的服務(wù)器上才看得出區(qū)別。比如多個(gè)線(xiàn)程同時(shí)做許多事情。

創(chuàng)新互聯(lián)建站服務(wù)項(xiàng)目包括彭澤網(wǎng)站建設(shè)、彭澤網(wǎng)站制作、彭澤網(wǎng)頁(yè)制作以及彭澤網(wǎng)絡(luò)營(yíng)銷(xiāo)策劃等。多年來(lái)，我們專(zhuān)注于互聯(lián)網(wǎng)行業(yè)，利用自身積累的技術(shù)優(yōu)勢(shì)、行業(yè)經(jīng)驗(yàn)、深度合作伙伴關(guān)系等，向廣大中小型企業(yè)、政府機(jī)構(gòu)等提供互聯(lián)網(wǎng)行業(yè)的解決方案，彭澤網(wǎng)站推廣取得了明顯的社會(huì)效益與經(jīng)濟(jì)效益。目前，我們服務(wù)的客戶(hù)以成都為中心已經(jīng)輻射到彭澤省份的部分城市，未來(lái)相信會(huì)繼續(xù)擴(kuò)大服務(wù)區(qū)域并繼續(xù)獲得客戶(hù)的支持與信任！

5.7、8.0 下INNODB_BUFFER_POOL_INSTANCES默認(rèn)為1，若mysql存在高并發(fā)和高負(fù)載訪問(wèn)，設(shè)置為1則會(huì)造成大量線(xiàn)程對(duì)BUFFER_POOL的單實(shí)例互斥鎖競(jìng)爭(zhēng)，這樣會(huì)消耗一定量的性能的。

pool_instances 可以設(shè)置為cpu核心數(shù)，它的作用是：

1）對(duì)于緩沖池在數(shù)千兆字節(jié)范圍內(nèi)的系統(tǒng)，通過(guò)減少爭(zhēng)用不同線(xiàn)程對(duì)緩存頁(yè)面進(jìn)行讀寫(xiě)的爭(zhēng)用，將緩沖池劃分為多個(gè)單獨(dú)的實(shí)例可以提高并發(fā)性?？梢灶?lèi)比為 java中的 ThreadLocal 線(xiàn)程本地變量 就是為每個(gè)線(xiàn)程維護(hù)一個(gè)buffer pool實(shí)例，這樣就不用去爭(zhēng)用同一個(gè)實(shí)例了。相當(dāng)于減少高并發(fā)下mysql對(duì)INNODB_BUFFER緩沖池的爭(zhēng)用。

2）使用散列函數(shù)將存儲(chǔ)在緩沖池中或從緩沖池讀取的每個(gè)頁(yè)面隨機(jī)分配給其中一個(gè)緩沖池實(shí)例。每個(gè)緩沖池管理自己的空閑列表， 刷新列表， LRU和連接到緩沖池的所有其他數(shù)據(jù)結(jié)構(gòu)，并受其自己的緩沖池互斥量保護(hù)。

超詳細(xì)MySQL數(shù)據(jù)庫(kù)優(yōu)化

數(shù)據(jù)庫(kù)優(yōu)化一方面是找出系統(tǒng)的瓶頸,提高M(jìn)ySQL數(shù)據(jù)庫(kù)的整體性能,而另一方面需要合理的結(jié)構(gòu)設(shè)計(jì)和參數(shù)調(diào)整,以提高用戶(hù)的相應(yīng)速度,同時(shí)還要盡可能的節(jié)約系統(tǒng)資源,以便讓系統(tǒng)提供更大的負(fù)荷.

1. 優(yōu)化一覽圖

2. 優(yōu)化

筆者將優(yōu)化分為了兩大類(lèi),軟優(yōu)化和硬優(yōu)化,軟優(yōu)化一般是操作數(shù)據(jù)庫(kù)即可,而硬優(yōu)化則是操作服務(wù)器硬件及參數(shù)設(shè)置.

2.1 軟優(yōu)化

2.1.1 查詢(xún)語(yǔ)句優(yōu)化

1.首先我們可以用EXPLAIN或DESCRIBE(簡(jiǎn)寫(xiě):DESC)命令分析一條查詢(xún)語(yǔ)句的執(zhí)行信息.

2.例:

顯示:

其中會(huì)顯示索引和查詢(xún)數(shù)據(jù)讀取數(shù)據(jù)條數(shù)等信息.

2.1.2 優(yōu)化子查詢(xún)

在MySQL中,盡量使用JOIN來(lái)代替子查詢(xún).因?yàn)樽硬樵?xún)需要嵌套查詢(xún),嵌套查詢(xún)時(shí)會(huì)建立一張臨時(shí)表,臨時(shí)表的建立和刪除都會(huì)有較大的系統(tǒng)開(kāi)銷(xiāo),而連接查詢(xún)不會(huì)創(chuàng)建臨時(shí)表,因此效率比嵌套子查詢(xún)高.

2.1.3 使用索引

索引是提高數(shù)據(jù)庫(kù)查詢(xún)速度最重要的方法之一,關(guān)于索引可以參高筆者M(jìn)ySQL數(shù)據(jù)庫(kù)索引一文,介紹比較詳細(xì),此處記錄使用索引的三大注意事項(xiàng):

2.1.4 分解表

對(duì)于字段較多的表,如果某些字段使用頻率較低,此時(shí)應(yīng)當(dāng),將其分離出來(lái)從而形成新的表,

2.1.5 中間表

對(duì)于將大量連接查詢(xún)的表可以創(chuàng)建中間表,從而減少在查詢(xún)時(shí)造成的連接耗時(shí).

2.1.6 增加冗余字段

類(lèi)似于創(chuàng)建中間表,增加冗余也是為了減少連接查詢(xún).

2.1.7 分析表,,檢查表,優(yōu)化表

分析表主要是分析表中關(guān)鍵字的分布,檢查表主要是檢查表中是否存在錯(cuò)誤,優(yōu)化表主要是消除刪除或更新造成的表空間浪費(fèi).

1. 分析表: 使用 ANALYZE 關(guān)鍵字,如ANALYZE TABLE user;

2. 檢查表: 使用 CHECK關(guān)鍵字,如CHECK TABLE user [option]

option 只對(duì)MyISAM有效,共五個(gè)參數(shù)值:

3. 優(yōu)化表:使用OPTIMIZE關(guān)鍵字,如OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE user;

LOCAL|NO_WRITE_TO_BINLOG都是表示不寫(xiě)入日志.,優(yōu)化表只對(duì)VARCHAR,BLOB和TEXT有效,通過(guò)OPTIMIZE TABLE語(yǔ)句可以消除文件碎片,在執(zhí)行過(guò)程中會(huì)加上只讀鎖.

2.2 硬優(yōu)化

2.2.1 硬件三件套

1.配置多核心和頻率高的cpu,多核心可以執(zhí)行多個(gè)線(xiàn)程.

2.配置大內(nèi)存,提高內(nèi)存,即可提高緩存區(qū)容量,因此能減少磁盤(pán)I/O時(shí)間,從而提高響應(yīng)速度.

3.配置高速磁盤(pán)或合理分布磁盤(pán):高速磁盤(pán)提高I/O,分布磁盤(pán)能提高并行操作的能力.

2.2.2 優(yōu)化數(shù)據(jù)庫(kù)參數(shù)

優(yōu)化數(shù)據(jù)庫(kù)參數(shù)可以提高資源利用率,從而提高M(jìn)ySQL服務(wù)器性能.MySQL服務(wù)的配置參數(shù)都在my.cnf或my.ini,下面列出性能影響較大的幾個(gè)參數(shù).

2.2.3 分庫(kù)分表

因?yàn)閿?shù)據(jù)庫(kù)壓力過(guò)大，首先一個(gè)問(wèn)題就是高峰期系統(tǒng)性能可能會(huì)降低，因?yàn)閿?shù)據(jù)庫(kù)負(fù)載過(guò)高對(duì)性能會(huì)有影響。另外一個(gè)，壓力過(guò)大把你的數(shù)據(jù)庫(kù)給搞掛了怎么辦？所以此時(shí)你必須得對(duì)系統(tǒng)做分庫(kù)分表 + 讀寫(xiě)分離，也就是把一個(gè)庫(kù)拆分為多個(gè)庫(kù)，部署在多個(gè)數(shù)據(jù)庫(kù)服務(wù)上，這時(shí)作為主庫(kù)承載寫(xiě)入請(qǐng)求。然后每個(gè)主庫(kù)都掛載至少一個(gè)從庫(kù)，由從庫(kù)來(lái)承載讀請(qǐng)求。

2.2.4 緩存集群

如果用戶(hù)量越來(lái)越大，此時(shí)你可以不停的加機(jī)器，比如說(shuō)系統(tǒng)層面不停加機(jī)器，就可以承載更高的并發(fā)請(qǐng)求。然后數(shù)據(jù)庫(kù)層面如果寫(xiě)入并發(fā)越來(lái)越高，就擴(kuò)容加數(shù)據(jù)庫(kù)服務(wù)器，通過(guò)分庫(kù)分表是可以支持?jǐn)U容機(jī)器的，如果數(shù)據(jù)庫(kù)層面的讀并發(fā)越來(lái)越高，就擴(kuò)容加更多的從庫(kù)。但是這里有一個(gè)很大的問(wèn)題：數(shù)據(jù)庫(kù)其實(shí)本身不是用來(lái)承載高并發(fā)請(qǐng)求的，所以通常來(lái)說(shuō)，數(shù)據(jù)庫(kù)單機(jī)每秒承載的并發(fā)就在幾千的數(shù)量級(jí)，而且數(shù)據(jù)庫(kù)使用的機(jī)器都是比較高配置，比較昂貴的機(jī)器，成本很高。如果你就是簡(jiǎn)單的不停的加機(jī)器，其實(shí)是不對(duì)的。所以在高并發(fā)架構(gòu)里通常都有緩存這個(gè)環(huán)節(jié)，緩存系統(tǒng)的設(shè)計(jì)就是為了承載高并發(fā)而生。所以單機(jī)承載的并發(fā)量都在每秒幾萬(wàn)，甚至每秒數(shù)十萬(wàn)，對(duì)高并發(fā)的承載能力比數(shù)據(jù)庫(kù)系統(tǒng)要高出一到兩個(gè)數(shù)量級(jí)。所以你完全可以根據(jù)系統(tǒng)的業(yè)務(wù)特性，對(duì)那種寫(xiě)少讀多的請(qǐng)求，引入緩存集群。具體來(lái)說(shuō)，就是在寫(xiě)數(shù)據(jù)庫(kù)的時(shí)候同時(shí)寫(xiě)一份數(shù)據(jù)到緩存集群里，然后用緩存集群來(lái)承載大部分的讀請(qǐng)求。這樣的話(huà)，通過(guò)緩存集群，就可以用更少的機(jī)器資源承載更高的并發(fā)。

一個(gè)完整而復(fù)雜的高并發(fā)系統(tǒng)架構(gòu)中，一定會(huì)包含：各種復(fù)雜的自研基礎(chǔ)架構(gòu)系統(tǒng)。各種精妙的架構(gòu)設(shè)計(jì).因此一篇小文頂多具有拋磚引玉的效果,但是數(shù)據(jù)庫(kù)優(yōu)化的思想差不多就這些了.

mysql 服務(wù)器CPU占用過(guò)高，如何調(diào)優(yōu)，求助

通過(guò)以前對(duì)mysql的操作經(jīng)驗(yàn)，先將mysql的配置問(wèn)題排除了，查看msyql是否運(yùn)行正常，通過(guò)查看mysql data目錄里面的*.err文件(將擴(kuò)展名改為.txt)記事本查看即可。如果過(guò)大不建議用記事本了，容易死掉，可以用editplus等工具。

簡(jiǎn)單的分為下面幾個(gè)步驟來(lái)解決這個(gè)問(wèn)題：

1、mysql運(yùn)行正常，也有可能是同步設(shè)置問(wèn)題導(dǎo)致

2、如果mysql運(yùn)行正常，那就是php的一些sql語(yǔ)句導(dǎo)致問(wèn)題發(fā)現(xiàn)，用root用戶(hù)進(jìn)入mysql管理

mysql -u root -p

輸入密碼

mysql:show processlist 語(yǔ)句，查找負(fù)荷最重的 SQL 語(yǔ)句，優(yōu)化該SQL，比如適當(dāng)建立某字段的索引。

通過(guò)這個(gè)命令我看到原來(lái)是有人惡意刷搜索，因?yàn)閐edecms搜索后面調(diào)用搜索最高的詞，導(dǎo)致很多人用工具刷這個(gè)，而且是定時(shí)有間隔的，所以將這個(gè)php程序改名跳轉(zhuǎn)都方法解決了。

當(dāng)然如果你的確實(shí)是sql語(yǔ)句用了大量的group by等語(yǔ)句，union聯(lián)合查詢(xún)等肯定會(huì)將mysql的占用率提高。所以就需要優(yōu)化sql語(yǔ)句，網(wǎng)站盡量生成靜態(tài)的，一般4W ip的靜態(tài)網(wǎng)站，mysql占用率幾乎為0的。所以這對(duì)于程序員的經(jīng)驗(yàn)是個(gè)考慮。盡量提高mysql性能 (MySQL 性能優(yōu)化的最佳20多條經(jīng)驗(yàn)分享)

下面是豆芽收集的文章，大家都可以參考下

MYSQL CPU 占用 100% 的現(xiàn)象描述

早上幫朋友一臺(tái)服務(wù)器解決了 Mysql cpu 占用 100% 的問(wèn)題。稍整理了一下，將經(jīng)驗(yàn)記錄在這篇文章里

朋友主機(jī)(Windows 2003 + IIS + PHP + MYSQL )近來(lái) MySQL 服務(wù)進(jìn)程 (mysqld-nt.exe) CPU 占用率總為 100% 高居不下。此主機(jī)有10個(gè)左右的 database, 分別給十個(gè)網(wǎng)站調(diào)用。據(jù)朋友測(cè)試，導(dǎo)致 mysqld-nt.exe cpu 占用奇高的是網(wǎng)站A，一旦在 IIS 中將此網(wǎng)站停止服務(wù)，CPU 占用就降下來(lái)了。一啟用，則馬上上升。

MYSQL CPU 占用 100% 的解決過(guò)程

今天早上仔細(xì)檢查了一下。目前此網(wǎng)站的七日平均日 IP 為2000，PageView 為 3萬(wàn)左右。網(wǎng)站A 用的 database 目前有39個(gè)表，記錄數(shù) 60.1萬(wàn)條，占空間 45MB。按這個(gè)數(shù)據(jù)，MySQL 不可能占用這么高的資源。

于是在服務(wù)器上運(yùn)行命令，將 mysql 當(dāng)前的環(huán)境變量輸出到文件 output.txt：

d:\web\mysql mysqld.exe --help output.txt

發(fā)現(xiàn) tmp_table_size 的值是默認(rèn)的 32M，于是修改 My.ini, 將 tmp_table_size 賦值到 200M:

d:\web\mysql notepad c:\windows\my.ini

[mysqld]

tmp_table_size=200M

然后重啟 MySQL 服務(wù)。CPU 占用有輕微下降，以前的CPU 占用波形圖是 100% 一根直線(xiàn)，現(xiàn)在則在 97%~100%之間起伏。這表明調(diào)整 tmp_table_size 參數(shù)對(duì) MYSQL 性能提升有改善作用。但問(wèn)題還沒(méi)有完全解決。

于是進(jìn)入 mysql 的 shell 命令行，調(diào)用 show processlist, 查看當(dāng)前 mysql 使用頻繁的 sql 語(yǔ)句：

mysql show processlist;

反復(fù)調(diào)用此命令，發(fā)現(xiàn)網(wǎng)站 A 的兩個(gè) SQL 語(yǔ)句經(jīng)常在 process list 中出現(xiàn)，其語(yǔ)法如下：

SELECT t1.pid, t2.userid, t3.count, t1.date

FROM _mydata AS t1

LEFT JOIN _myuser AS t3 ON t1.userid=t3.userid

LEFT JOIN _mydata_body AS t2 ON t1.pid=t3.pid

ORDER BY t1.pid

LIMIT 0,15

調(diào)用 show columns 檢查這三個(gè)表的結(jié)構(gòu) :

mysql show columns from _myuser;

mysql show columns from _mydata;

mysql show columns from _mydata_body;

終于發(fā)現(xiàn)了問(wèn)題所在：_mydata 表，只根據(jù) pid 建立了一個(gè) primary key，但并沒(méi)有為 userid 建立索引。而在這個(gè) SQL 語(yǔ)句的第一個(gè) LEFT JOIN ON 子句中：

LEFT JOIN _myuser AS t3 ON t1.userid=t3.userid

_mydata 的 userid 被參與了條件比較運(yùn)算。于是我為給 _mydata 表根據(jù)字段 userid 建立了一個(gè)索引：

mysql ALTER TABLE `_mydata` ADD INDEX ( `userid` )

建立此索引之后，CPU 馬上降到了 80% 左右?？吹秸业搅藛?wèn)題所在，于是檢查另一個(gè)反復(fù)出現(xiàn)在 show processlist 中的 sql 語(yǔ)句：

SELECT COUNT(*)

FROM _mydata AS t1, _mydata_key AS t2

WHERE t1.pid=t2.pid and t2.keywords = '孔雀'

經(jīng)檢查 _mydata_key 表的結(jié)構(gòu)，發(fā)現(xiàn)它只為 pid 建了了 primary key, 沒(méi)有為 keywords 建立 index。_mydata_key 目前有 33 萬(wàn)條記錄，在沒(méi)有索引的情況下對(duì)33萬(wàn)條記錄進(jìn)行文本檢索匹配，不耗費(fèi)大量的 cpu 時(shí)間才怪。看來(lái)就是針對(duì)這個(gè)表的檢索出問(wèn)題了。于是同樣為 _mydata_key 表根據(jù)字段 keywords 加上索引:

mysql ALTER TABLE `_mydata_key` ADD INDEX ( `keywords` )

建立此索引之后，CPU立刻降了下來(lái)，在 50%~70%之間震蕩。

再次調(diào)用 show prosslist，網(wǎng)站A 的sql 調(diào)用就很少出現(xiàn)在結(jié)果列表中了。但發(fā)現(xiàn)此主機(jī)運(yùn)行了幾個(gè) Discuz 的論壇程序， Discuz 論壇的好幾個(gè)表也存在著這個(gè)問(wèn)題。于是順手一并解決，cpu占用再次降下來(lái)了。(2007.07.09 附注：關(guān)于 discuz 論壇的具體優(yōu)化過(guò)程，我后來(lái)另寫(xiě)了一篇文章，詳見(jiàn)：千萬(wàn)級(jí)記錄的 Discuz! 論壇導(dǎo)致 MySQL CPU 100% 的 優(yōu)化筆記 )

解決 MYSQL CPU 占用 100% 的經(jīng)驗(yàn)總結(jié)

增加 tmp_table_size 值。mysql 的配置文件中，tmp_table_size 的默認(rèn)大小是 32M。如果一張臨時(shí)表超出該大小，MySQL產(chǎn)生一個(gè) The table tbl_name is full 形式的錯(cuò)誤，如果你做很多高級(jí) GROUP BY 查詢(xún)，增加 tmp_table_size 值。

對(duì) WHERE, JOIN, MAX(), MIN(), ORDER BY 等子句中的條件判斷中用到的字段,應(yīng)該根據(jù)其建立索引 INDEX。索引被用來(lái)快速找出在一個(gè)列上用一特定值的行。沒(méi)有索引，MySQL不得不首先以第一條記錄開(kāi)始并然后讀完整個(gè)表直到它找出相關(guān)的行。表越大，花費(fèi)時(shí)間越多。如果表對(duì)于查詢(xún)的列有一個(gè)索引，MySQL能快速到達(dá)一個(gè)位置去搜尋到數(shù)據(jù)文件的中間，沒(méi)有必要考慮所有數(shù)據(jù)。如果一個(gè)表有1000行，這比順序讀取至少快100倍。所有的MySQL索引(PRIMARY、UNIQUE和INDEX)在B樹(shù)中存儲(chǔ)。

根據(jù) mysql 的開(kāi)發(fā)文檔:

索引 index 用于：

快速找出匹配一個(gè)WHERE子句的行

當(dāng)執(zhí)行聯(lián)結(jié)(JOIN)時(shí)，從其他表檢索行。

對(duì)特定的索引列找出MAX()或MIN()值

如果排序或分組在一個(gè)可用鍵的最左面前綴上進(jìn)行(例如，ORDER BY key_part_1,key_part_2)，排序或分組一個(gè)表。如果所有鍵值部分跟隨DESC，鍵以倒序被讀取。

在一些情況中，一個(gè)查詢(xún)能被優(yōu)化來(lái)檢索值，不用咨詢(xún)數(shù)據(jù)文件。如果對(duì)某些表的所有使用的列是數(shù)字型的并且構(gòu)成某些鍵的最左面前綴，為了更快，值可以從索引樹(shù)被檢索出來(lái)。

假定你發(fā)出下列SELECT語(yǔ)句：

mysql SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2；

如果一個(gè)多列索引存在于col1和col2上，適當(dāng)?shù)男锌梢灾苯颖蝗〕?。如果分開(kāi)的單行列索引存在于col1和col2上，優(yōu)化器試圖通過(guò)決定哪個(gè)索引將找到更少的行并來(lái)找出更具限制性的索引并且使用該索引取行。

mysql如何優(yōu)化以下語(yǔ)句，查詢(xún)耗時(shí)太久了？

根據(jù)所描述的問(wèn)題，可嘗試在mms_profitcenter 的FOrderID ，F(xiàn)Suffix列上建立索引，再查詢(xún)?cè)囋嚒?下面提供30種mysql常用優(yōu)化方法供參考：

1.對(duì)查詢(xún)進(jìn)行優(yōu)化，應(yīng)盡量避免全表掃描，首先應(yīng)考慮在 where 及 order by 涉及的列上建立索引。

2.應(yīng)盡量避免在 where 子句中使用!=或操作符，否則將引擎放棄使用索引而進(jìn)行全表掃描。

3.應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行 null 值判斷，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如：

select id from t where num is null

可以在num上設(shè)置默認(rèn)值0，確保表中num列沒(méi)有null值，然后這樣查詢(xún)：

select id from t where num=0

4.應(yīng)盡量避免在 where 子句中使用 or 來(lái)連接條件，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如：

select id from t where num=10 or num=20

可以這樣查詢(xún)：

select id from t where num=10

union all

select id from t where num=20

5.下面的查詢(xún)也將導(dǎo)致全表掃描：

select id from t where name like '%abc%'

若要提高效率，可以考慮全文檢索。

6.in 和 not in 也要慎用，否則會(huì)導(dǎo)致全表掃描，如：

select id from t where num in(1,2,3)

對(duì)于連續(xù)的數(shù)值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

7.如果在 where 子句中使用參數(shù)，也會(huì)導(dǎo)致全表掃描。因?yàn)镾QL只有在運(yùn)行時(shí)才會(huì)解析局部變量，但優(yōu)化程序不能將訪問(wèn)計(jì)劃的選擇推遲到運(yùn)行時(shí)；它必須在編譯時(shí)進(jìn)行選擇。然而，如果在編譯時(shí)建立訪問(wèn)計(jì)劃，變量的值還是未知的，因而無(wú)法作為索引選擇的輸入項(xiàng)。如下面語(yǔ)句將進(jìn)行全表掃描：

select id from t where num=@num

可以改為強(qiáng)制查詢(xún)使用索引：

select id from t with(index(索引名)) where num=@num

8.應(yīng)盡量避免在 where 子句中對(duì)字段進(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子句中對(duì)字段進(jìn)行函數(shù)操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如：

select id from t where substring(name,1,3)='abc'--name以abc開(kāi)頭的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ù)運(yùn)算或其他表達(dá)式運(yùn)算，否則系統(tǒng)將可能無(wú)法正確使用索引。

11.在使用索引字段作為條件時(shí)，如果該索引是復(fù)合索引，那么必須使用到該索引中的第一個(gè)字段作為條件時(shí)才能保證系統(tǒng)使用該索引，否則該索引將不會(huì)被使用，并且應(yīng)盡可能的讓字段順序與索引順序相一致。

12.不要寫(xiě)一些沒(méi)有意義的查詢(xún)，如需要生成一個(gè)空表結(jié)構(gòu)：

select col1,col2 into #t from t where 1=0

這類(lèi)代碼不會(huì)返回任何結(jié)果集，但是會(huì)消耗系統(tǒng)資源的，應(yīng)改成這樣：

create table #t(...)

13.很多時(shí)候用 exists 代替 in 是一個(gè)好的選擇：

select num from a where num in(select num from b)

用下面的語(yǔ)句替換：

select num from a where exists(select 1 from b where num=a.num)

14.并不是所有索引對(duì)查詢(xún)都有效，SQL是根據(jù)表中數(shù)據(jù)來(lái)進(jìn)行查詢(xún)優(yōu)化的，當(dāng)索引列有大量數(shù)據(jù)重復(fù)時(shí)，SQL查詢(xún)可能不會(huì)去利用索引，如一表中有字段sex，male、female幾乎各一半，那么即使在sex上建了索引也對(duì)查詢(xún)效率起不了作用。

15.索引并不是越多越好，索引固然可以提高相應(yīng)的 select 的效率，但同時(shí)也降低了 insert 及 update 的效率，因?yàn)?insert 或 update 時(shí)有可能會(huì)重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。一個(gè)表的索引數(shù)最好不要超過(guò)6個(gè)，若太多則應(yīng)考慮一些不常使用到的列上建的索引是否有必要。

16.應(yīng)盡可能的避免更新 clustered 索引數(shù)據(jù)列，因?yàn)?clustered 索引數(shù)據(jù)列的順序就是表記錄的物理存儲(chǔ)順序，一旦該列值改變將導(dǎo)致整個(gè)表記錄的順序的調(diào)整，會(huì)耗費(fèi)相當(dāng)大的資源。若應(yīng)用系統(tǒng)需要頻繁更新 clustered 索引數(shù)據(jù)列，那么需要考慮是否應(yīng)將該索引建為 clustered 索引。

17.盡量使用數(shù)字型字段，若只含數(shù)值信息的字段盡量不要設(shè)計(jì)為字符型，這會(huì)降低查詢(xún)和連接的性能，并會(huì)增加存儲(chǔ)開(kāi)銷(xiāo)。這是因?yàn)橐嬖谔幚聿樵?xún)和連接時(shí)會(huì)逐個(gè)比較字符串中每一個(gè)字符，而對(duì)于數(shù)字型而言只需要比較一次就夠了。

18.盡可能的使用 varchar/nvarchar 代替 char/nchar ，因?yàn)槭紫茸冮L(zhǎng)字段存儲(chǔ)空間小，可以節(jié)省存儲(chǔ)空間，其次對(duì)于查詢(xún)來(lái)說(shuō)，在一個(gè)相對(duì)較小的字段內(nèi)搜索效率顯然要高些。

19.任何地方都不要使用 select * from t ，用具體的字段列表代替“*”，不要返回用不到的任何字段。

20.盡量使用表變量來(lái)代替臨時(shí)表。如果表變量包含大量數(shù)據(jù)，請(qǐng)注意索引非常有限（只有主鍵索引）。

21.避免頻繁創(chuàng)建和刪除臨時(shí)表，以減少系統(tǒng)表資源的消耗。

22.臨時(shí)表并不是不可使用，適當(dāng)?shù)厥褂盟鼈兛梢允鼓承├谈行?，例如，?dāng)需要重復(fù)引用大型表或常用表中的某個(gè)數(shù)據(jù)集時(shí)。但是，對(duì)于一次性事件，最好使用導(dǎo)出表。

23.在新建臨時(shí)表時(shí)，如果一次性插入數(shù)據(jù)量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果數(shù)據(jù)量不大，為了緩和系統(tǒng)表的資源，應(yīng)先create table，然后insert。

24.如果使用到了臨時(shí)表，在存儲(chǔ)過(guò)程的最后務(wù)必將所有的臨時(shí)表顯式刪除，先 truncate table ，然后 drop table ，這樣可以避免系統(tǒng)表的較長(zhǎng)時(shí)間鎖定。

25.盡量避免使用游標(biāo)，因?yàn)橛螛?biāo)的效率較差，如果游標(biāo)操作的數(shù)據(jù)超過(guò)1萬(wàn)行，那么就應(yīng)該考慮改寫(xiě)。

26.使用基于游標(biāo)的方法或臨時(shí)表方法之前，應(yīng)先尋找基于集的解決方案來(lái)解決問(wèn)題，基于集的方法通常更有效。

27.與臨時(shí)表一樣，游標(biāo)并不是不可使用。對(duì)小型數(shù)據(jù)集使用 FAST_FORWARD 游標(biāo)通常要優(yōu)于其他逐行處理方法，尤其是在必須引用幾個(gè)表才能獲得所需的數(shù)據(jù)時(shí)。在結(jié)果集中包括“合計(jì)”的例程通常要比使用游標(biāo)執(zhí)行的速度快。如果開(kāi)發(fā)時(shí)間允許，基于游標(biāo)的方法和基于集的方法都可以嘗試一下，看哪一種方法的效果更好。

28.在所有的存儲(chǔ)過(guò)程和觸發(fā)器的開(kāi)始處設(shè)置 SET NOCOUNT ON ，在結(jié)束時(shí)設(shè)置 SET NOCOUNT OFF 。無(wú)需在執(zhí)行存儲(chǔ)過(guò)程和觸發(fā)器的每個(gè)語(yǔ)句后向客戶(hù)端發(fā)送 DONE_IN_PROC 消息。

29.盡量避免向客戶(hù)端返回大數(shù)據(jù)量，若數(shù)據(jù)量過(guò)大，應(yīng)該考慮相應(yīng)需求是否合理。

30.盡量避免大事務(wù)操作，提高系統(tǒng)并發(fā)能力。

MySQL服務(wù)器如何進(jìn)行調(diào)優(yōu)??

第一種方法時(shí)替換有問(wèn)題的硬件。對(duì)MySQL進(jìn)程的設(shè)置進(jìn)行調(diào)優(yōu)。對(duì)查詢(xún)進(jìn)行優(yōu)化。

替換有問(wèn)題的硬件通常是我們的第一考慮，主要原因是數(shù)據(jù)庫(kù)會(huì)占用大量資源。不過(guò)這種解決方案也就僅限于此了。實(shí)際上，您通?？梢宰屩醒胩幚砥鳎–PU）或磁盤(pán)速度加倍，也可以讓內(nèi)存增大4到8倍。

第二種方法是對(duì)MySQL服務(wù)器（也稱(chēng)為mysqld）進(jìn)行調(diào)優(yōu)。

對(duì)這個(gè)進(jìn)程進(jìn)行調(diào)優(yōu)意味著適當(dāng)?shù)胤峙鋬?nèi)存，并讓 mysqld 了解將會(huì)承受何種類(lèi)型的負(fù)載。加快磁盤(pán)運(yùn)行速度不如減少所需的磁盤(pán)訪問(wèn)次數(shù)。類(lèi)似地，確保 MySQL 進(jìn)程正確操作就意味著它花費(fèi)在服務(wù)查詢(xún)上的時(shí)間要多于花費(fèi)在處理后臺(tái)任務(wù)（如處理臨時(shí)磁盤(pán)表或打開(kāi)和關(guān)閉文件）上的時(shí)間。對(duì)mysqld進(jìn)行調(diào)優(yōu)是本文的重點(diǎn)。

最好的方法是確保查詢(xún)已經(jīng)進(jìn)行了優(yōu)化。這意味著對(duì)表應(yīng)用了適當(dāng)?shù)乃饕?，查?xún)是按照可以充分利用MySQL功能的方式來(lái)編寫(xiě)的。盡管本文并沒(méi)有包含查詢(xún)調(diào)優(yōu)方面的內(nèi)容（很多著作中已經(jīng)針對(duì)這個(gè)主題進(jìn)行了探討），不過(guò)它會(huì)配置mysqld來(lái)報(bào)告可能需要進(jìn)行調(diào)優(yōu)的查詢(xún)。

雖然已經(jīng)為這些任務(wù)指派了次序，但是仍然要注意硬件和mysqld的設(shè)置以利于適當(dāng)?shù)卣{(diào)優(yōu)查詢(xún)。海外服務(wù)器租用機(jī)器速度慢也就罷了，我曾經(jīng)見(jiàn)過(guò)速度很快的機(jī)器在運(yùn)行設(shè)計(jì)良好的查詢(xún)時(shí)由于負(fù)載過(guò)重而失敗，因?yàn)閙ysqld被大量繁忙的工作所占用而不能服務(wù)查詢(xún)。

第三種方法是記錄慢速查詢(xún)

在一個(gè)SQL服務(wù)器中，數(shù)據(jù)表都是保存在磁盤(pán)上的。索引為服務(wù)器提供了一種在表中查找特定數(shù)據(jù)行的方法，而不用搜索整個(gè)表。當(dāng)必須要搜索整個(gè)表時(shí)，就稱(chēng)為表掃描。通常來(lái)說(shuō)，您可能只希望獲得表中數(shù)據(jù)的一個(gè)子集，因此全表掃描會(huì)浪費(fèi)大量的磁盤(pán)I/O,因此也就會(huì)浪費(fèi)大量時(shí)間。當(dāng)必須對(duì)數(shù)據(jù)進(jìn)行連接時(shí)，這個(gè)問(wèn)題就更加復(fù)雜了，因?yàn)楸仨氁獙?duì)連接兩端的多行數(shù)據(jù)進(jìn)行比較。

當(dāng)然，表掃描并不總是會(huì)帶來(lái)問(wèn)題；有時(shí)讀取整個(gè)表反而會(huì)比從中挑選出一部分?jǐn)?shù)據(jù)更加有效（服務(wù)器進(jìn)程中查詢(xún)規(guī)劃器用來(lái)作出這些決定）。如果索引的使 用效率很低，或者根本就不能使用索引，則會(huì)減慢查詢(xún)速度，而且隨著服務(wù)器上的負(fù)載和表大小的增加，這個(gè)問(wèn)題會(huì)變得更加顯著。執(zhí)行時(shí)間超過(guò)給定時(shí)間范圍的查詢(xún)就稱(chēng)為慢速查詢(xún)。

_深圳比特捷_

mysql 參數(shù)調(diào)優(yōu)(2)之設(shè)置重做日志文件的大小 innodb_log_file_size

我們知道redo log包括 buffer和log file的部分，這里的innodb_log_file_size是配置log file的大小的。

innodb_log_file_size這個(gè)選項(xiàng)是設(shè)置 redo 日志（重做日志）的大小。這個(gè)值的默認(rèn)為5M，是遠(yuǎn)遠(yuǎn)不夠的，在安裝完mysql時(shí)需要盡快的修改這個(gè)值。如果對(duì) Innodb 數(shù)據(jù)表有大量的寫(xiě)入操作，那么選擇合適的 innodb_log_file_size 值對(duì)提升MySQL性能很重要。然而設(shè)置太大了，就會(huì)增加恢復(fù)的時(shí)間，因此在MySQL崩潰或者突然斷電等情況會(huì)令MySQL服務(wù)器花很長(zhǎng)時(shí)間來(lái)恢復(fù)。

由于事務(wù)日志相當(dāng)于一個(gè)寫(xiě)緩沖，而小日志文件會(huì)很快的被寫(xiě)滿(mǎn)，這時(shí)候就需要頻繁地刷新到硬盤(pán)，速度就慢了。如果產(chǎn)生大量的寫(xiě)操作，MySQL可能就不能足夠快地刷新數(shù)據(jù)，那么寫(xiě)性能將會(huì)降低。

大的日志文件，另一方面，在刷新操作發(fā)生之前給你足夠的空間來(lái)使用。反過(guò)來(lái)允許InnoDB填充更多的頁(yè)面。對(duì)于崩潰恢復(fù) – 大的重做日志意味著在服務(wù)器啟動(dòng)前更多的數(shù)據(jù)需要讀取，更多的更改需要重做，這就是為什么崩潰恢復(fù)慢了。

如果不配的后果：默認(rèn)是5M，這是肯定不夠的。

最后，讓我們來(lái)談?wù)勅绾握页鲋刈鋈罩镜恼_大小。

幸運(yùn)的是，你不需要費(fèi)力算出正確的大小，這里有一個(gè)經(jīng)驗(yàn)法則：在服務(wù)器繁忙期間，檢查重做日志的總大小是否夠?qū)懭?-2小時(shí)。你如何知道InnoDB寫(xiě)入多少，使用下面方法可以統(tǒng)計(jì)60秒內(nèi)地增量數(shù)據(jù)大小：

mysql show engine innodb status\G select sleep(60); show engine innodb status\G

Log sequence number 4631632062

...

Log sequence number 4803805448

mysql select (4803805448-4631632062) 60/1024/1024;

+--------------------------------------+

| (4803805448-4631632062) 60/1024/1024 |

+--------------------------------------+

| 9851.84017181 |

+--------------------------------------+

1 row in set (0.00 sec)

在這個(gè)60s的采樣情況下，InnoDB每小時(shí)寫(xiě)入9.8GB數(shù)據(jù)。所以如果innodb_log_files_in_group沒(méi)有更改(默認(rèn)是2，是InnoDB重復(fù)日志的最小數(shù)字)，然后設(shè)置innodb_log_file_size為10G，那么你實(shí)際上兩個(gè)日志文件加起來(lái)有20GB，夠你寫(xiě)兩小時(shí)數(shù)據(jù)了。

更改innodb_log_file_size的難易程度和能設(shè)置多大取決于你現(xiàn)在使用的MySQL版本。特別地，如果你使用的是5.6之前的版本，你不能僅僅的更改變量，期望服務(wù)器會(huì)自動(dòng)重啟。

好了，下面是步驟：

1、在my.cnf更改innodb_log_file_size

2、停止mysql服務(wù)器

3、刪除舊的日志,通過(guò)執(zhí)行命令rm -f /var/lib/mysql/ib_logfile*

4、啟動(dòng)mysql服務(wù)器 – 應(yīng)該需要比之前長(zhǎng)點(diǎn)的時(shí)間，因?yàn)樾枰獎(jiǎng)?chuàng)建新的事務(wù)日志。最后，需要注意的是，有些mysql版本(比如5.6.2)限制了重做日志大小為4GB。所以在你設(shè)置innodb_log_file_size為2G或者更多時(shí)，請(qǐng)先檢查一下MySQL的版本這方面的限制。

網(wǎng)站標(biāo)題：mysql調(diào)優(yōu)怎么寫(xiě) mysql調(diào)優(yōu)經(jīng)驗(yàn)
標(biāo)題路徑：http://aaarwkj.com/article20/hhpoco.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站維護(hù)、關(guān)鍵詞優(yōu)化、云服務(wù)器、品牌網(wǎng)站建設(shè)、靜態(tài)網(wǎng)站、微信公眾號(hào)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶(hù)投稿、用戶(hù)轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話(huà)：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)