共計 3238 個字符，預(yù)計需要花費 9 分鐘才能閱讀完成。
這篇文章主要講解了“MySQL 的調(diào)優(yōu)思路和實際操作”，文中的講解內(nèi)容簡單清晰，易于學(xué)習(xí)與理解，下面請大家跟著丸趣 TV 小編的思路慢慢深入，一起來研究和學(xué)習(xí)“MySQL 的調(diào)優(yōu)思路和實際操作”吧！
調(diào)優(yōu)思路：
1. 設(shè)計與規(guī)劃 – 以后再修該很麻煩，估計數(shù)據(jù)量，使用什么存儲引擎
2. 數(shù)據(jù)的應(yīng)用 – 怎樣取數(shù)據(jù)，sql 語句的優(yōu)化
3. 服務(wù)優(yōu)化 – 內(nèi)存的使用，磁盤的使用
4. 操作系統(tǒng)的優(yōu)化 – 內(nèi)核、tcp 連接數(shù)量
5. 升級硬件設(shè)備
磁盤 io 規(guī)劃
raid 技術(shù)：raid0[xfs]
swap 分區(qū)：最好使用 raid0
磁盤分區(qū)：一個庫放到一個分區(qū)上或一個磁盤上
物理分區(qū)
create table t1(id int,name char(20)) data directory= /data/ index directory = /data
mkdir /data
chown mysql.mysql /data
mysql show variables like %part%
4. 操作系統(tǒng)的優(yōu)化
網(wǎng)卡 bonding 技術(shù)，
tcp 連接數(shù)量限制
優(yōu)化系統(tǒng)打開文件的最大限制
關(guān)閉操作系統(tǒng)不必要的服務(wù)
5.mysql 服務(wù)優(yōu)化
show status 看系統(tǒng)的資源
show variables 看變量，在 my.cnf 配置文件里定義的
show warnings 查看最近一個 sql 語句產(chǎn)生的錯誤警告，看其他的需要看.err 日志
show processlist 顯示系統(tǒng)中正在運行的所有進程。
show errors
啟用 mysql 慢查詢：— 分析 sql 語句，找到影響效率的 SQL
log-slow-queries=/var/lib/mysql/slow.log 這個路徑對 mysql 用戶具有可寫權(quán)限
long_query_time=2 查詢超過 2 秒鐘的語句記錄下來
上面的 2 是查詢的時間，即當(dāng)一條 SQL 執(zhí)行時間超過 5 秒的時候才記錄，/var/lib/mysql/slow.log 是日志記錄的位置。
然后重新啟動 MySQL 服務(wù)
對查詢進行緩存
query_cache_size 使用多大內(nèi)存來緩存查詢語句 [+8M]
mysql show variables like %query%
query_cache_size=8M
[root@st mysql]# vim /etc/my.cnf
mysql show status like %Qcache%
Qcache_free_blocks：說明緩存太大了。緩存中相鄰內(nèi)存的個數(shù)。數(shù)目大說明可能有碎片。FLUSH QUERY CACHE 會對緩存中的碎片進行整理，從而得到一個空閑塊。[+8M]
Qcache_free_memory 緩存中的空閑內(nèi)存
Qcache_hits 每次查詢在緩存中命中時就增大
Qcache_inserts 每插入一個查詢時就增大。命中次數(shù)除以插入次數(shù)就是命中率。
Qcache_lowmen_prunes 緩存出現(xiàn)內(nèi)存不足并且必須要進行清理以便為更多查詢提供空間的次數(shù)。這個數(shù)字最好長時間看；如果這個數(shù)字在不斷增長就表示可能碎片非常嚴(yán)重，或者內(nèi)存很少
Qcache_hits/Qcache_inserts 命中率
關(guān)鍵字緩沖區(qū)
mysql show status like %key%
mysql show variables like key_buffer_size
key_buffer_size 指定索引緩沖區(qū)的大小，它決定索引處理的速度，尤其是索引讀的速度。[+8M]
key_read_requests 請求總數(shù)
key_reads 代表命中磁盤的請求個數(shù)
(key_read_requests-key_read)/key_read_requests：命中率
key_buffer_size 只對 MyISAM 表起作用。即使你不使用 MyISAM 表，但是內(nèi)部的臨時磁盤表是 MyISAM 表，也要使用該值?？梢允褂脵z查狀態(tài)值 created_tmp_disk_tables 得知詳情。
對于 1G 內(nèi)存的機器，如果不使用 MyISAM 表，推薦值是 16M（8-64M）。
臨時表空間大?。簅rder by 和 group by 時把數(shù)據(jù)放到臨時表里。
tmp_table_size 占的是內(nèi)存的大小，如果太小在排序時會出錯
created_tmp_tables 創(chuàng)建臨時表的數(shù)量
max_tmp_tables=32
tmpdir=/tmp 硬盤上臨時表所在的位置
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
innodb 表：
創(chuàng)建表空間文件
[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend
這個設(shè)置配置一個可擴展大小的尺寸為 10MB 的單獨文件，名為 ibdata1。沒有給出文件的位置，所以默認(rèn)的是在 MySQL 的數(shù)據(jù)目錄內(nèi)。
如果你對最后的數(shù)據(jù)文件指定 autoextend 選項。如果數(shù)據(jù)文件耗盡了表空間中的自由空間，InnoDB 就擴展數(shù)據(jù)文件。擴展的幅度是每次 8MB。
要為一個自動擴展數(shù)據(jù)文件指定最大尺寸，請使用 max 屬性。下列配置允許 ibdata1 漲到極限的 500MB：
[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend:max:500M
InnoDB 默認(rèn)地在 MySQL 數(shù)據(jù)目錄創(chuàng)建表空間文件。要明確指定一個位置，請使用 innodb_data_home_dir 選項。比如，要使用兩個名為 ibdata1 和 ibdata2 的文件，但是要把他們創(chuàng)建到 /ibdata，像如下一樣配置 InnoDB：
[mysqld]
innodb_data_home_dir = /ibdata
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
mysql show variables like innodb_buffer_pool_size
innodb_buffer_pool_size
對于 InnoDB 表來說，innodb_buffer_pool_size 的作用就相當(dāng)于 key_buffer_size 對于 MyISAM 表的作用一樣。InnoDB 使用該參數(shù)指定大小的內(nèi)存來緩沖數(shù)據(jù)和索引。對于單獨的 MySQL 數(shù)據(jù)庫服務(wù)器，最大可以把該值設(shè)置成物理內(nèi)存的 80%。
根據(jù) MySQL 手冊，對于 2G 內(nèi)存的機器，推薦值是 1G（50%）。
mysql show variables like innodb_%per% [建議打開]
innodb_file_per_table =1 為每一個表單獨創(chuàng)建一個表空間文件。
其他參數(shù)
skip-locking
取消文件系統(tǒng)的外部鎖，減少出錯幾率增強穩(wěn)定性
skip-name-resolve
關(guān)閉 mysql 的 dns 反查功能。這樣速度就快了!
選項就能禁用 DNS 解析，連接速度會快很多。不過，這樣的話就不能在 MySQL 的授權(quán)表中使用主機名了而只能用 ip 格式。
wait_timeout=10 終止空閑時間超過 10 秒的鏈接，避免長連接[默認(rèn) 8 個小時]
max_connect_errors=10 //10 次連接失敗就鎖定，使用 flush hosts 解鎖，
或 mysqladmin flush-hosts 解鎖
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL 語句調(diào)優(yōu)：
explain 命令：查詢 select。
.type
這列很重要, 顯示了連接使用了哪種類別, 有無使用索引
從最好到最差的連接類型為 const、eq_reg、ref、range、indexhe 和 ALL
感謝各位的閱讀，以上就是“MySQL 的調(diào)優(yōu)思路和實際操作”的內(nèi)容了，經(jīng)過本文的學(xué)習(xí)后，相信大家對 MySQL 的調(diào)優(yōu)思路和實際操作這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是丸趣 TV，丸趣 TV 小編將為大家推送更多相關(guān)知識點的文章，歡迎關(guān)注！
