MySQL的調優思路和實際操作

這篇文章主要講解了“MySQL的調優思路和實際操作”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“MySQL的調優思路和實際操作”吧！

調優思路：

1.設計與規劃--以后再修該很麻煩，估計數據量，使用什么存儲引擎

2.數據的應用--怎樣取數據，sql語句的優化

3.服務優化--內存的使用，磁盤的使用

4.操作系統的優化--內核、tcp連接數量

5.升級硬件設備

磁盤io規劃

raid技術：raid0[xfs]

swap分區：最好使用raid0

磁盤分區：一個庫放到一個分區上或一個磁盤上

物理分區

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.操作系統的優化

網卡bonding技術，

tcp連接數量限制

優化系統打開文件的最大限制

關閉操作系統不必要的服務

5.mysql服務優化

show status  看系統的資源

show variables  看變量，在my.cnf配置文件里定義的

show warnings   查看最近一個sql語句產生的錯誤警告，看其他的需要看.err日志

show processlist顯示系統中正在運行的所有進程。

show errors

啟用mysql慢查詢：---分析sql語句，找到影響效率的SQL

log-slow-queries=/var/lib/mysql/slow.log 這個路徑對mysql用戶具有可寫權限

long_query_time=2   查詢超過2秒鐘的語句記錄下來

上面的2 是查詢的時間，即當一條SQL 執行時間超過5秒的時候才記錄，/var/lib/mysql/slow.log 是日志記錄的位置。

然后重新啟動MySQL服務

對查詢進行緩存

query_cache_size 使用多大內存來緩存查詢語句[+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：說明緩存太大了。緩存中相鄰內存的個數。數目大說明可能有碎片。FLUSH QUERY CACHE會對緩存中的碎片進行整理，從而得到一個空閑塊。[+8M]

Qcache_free_memory緩存中的空閑內存

Qcache_hits每次查詢在緩存中命中時就增大

Qcache_inserts每插入一個查詢時就增大。命中次數除以插入次數就是命中率。

Qcache_lowmen_prunes緩存出現內存不足并且必須要進行清理以便為更多查詢提供空間的次數。這個數字最好長時間看；如果這個數字在不斷增長就表示可能碎片非常嚴重，或者內存很少

Qcache_hits/Qcache_inserts 命中率

關鍵字緩沖區

mysql> show status like '%key%';

mysql> show variables like'key_buffer_size';

key_buffer_size  指定索引緩沖區的大小，它決定索引處理的速度，尤其是索引讀的速度。[+8M]

key_read_requests  請求總數

key_reads   代表命中磁盤的請求個數

(key_read_requests-key_read)/key_read_requests：命中率

key_buffer_size只對MyISAM表起作用。即使你不使用MyISAM表，但是內部的臨時磁盤表是MyISAM表，也要使用該值。可以使用檢查狀態值created_tmp_disk_tables得知詳情。

對于1G內存的機器，如果不使用MyISAM表，推薦值是16M（8-64M）。

臨時表空間大小：order by和group by時把數據放到臨時表里。

tmp_table_size   占的是內存的大小，如果太小在排序時會出錯

created_tmp_tables  創建臨時表的數量

max_tmp_tables=32

tmpdir=/tmp  硬盤上臨時表所在的位置

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

innodb表：

創建表空間文件

[mysqld]

innodb_data_file_path=ibdata1:10M:autoextend

這個設置配置一個可擴展大小的尺寸為10MB的單獨文件，名為ibdata1。沒有給出文件的位置，所以默認的是在MySQL的數據目錄內。

如果你對最后的數據文件指定autoextend選項。如果數據文件耗盡了表空間中的自由空間，InnoDB就擴展數據文件。擴展的幅度是每次8MB。

要為一個自動擴展數據文件指定最大尺寸，請使用max屬性。下列配置允許ibdata1漲到極限的500MB：

[mysqld]

innodb_data_file_path=ibdata1:10M:autoextend:max:500M

InnoDB默認地在MySQL數據目錄創建表空間文件。要明確指定一個位置，請使用innodb_data_home_dir選項。比如，要使用兩個名為ibdata1和ibdata2的文件，但是要把他們創建到/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的作用就相當于key_buffer_size對于MyISAM表的作用一樣。InnoDB使用該參數指定大小的內存來緩沖數據和索引。對于單獨的MySQL數據庫服務器，最大可以把該值設置成物理內存的80%。

根據MySQL手冊，對于2G內存的機器，推薦值是1G（50%）。

mysql> show variables like 'innodb_%per%';［建議打開］

innodb_file_per_table =1 為每一個表單獨創建一個表空間文件。

其他參數

skip-locking

取消文件系統的外部鎖，減少出錯幾率增強穩定性

skip-name-resolve

關閉mysql的dns反查功能。這樣速度就快了!

選項就能禁用DNS解析，連接速度會快很多。不過，這樣的話就不能在MySQL的授權表中使用主機名了而只能用ip格式。

wait_timeout=10 終止空閑時間超過10秒的鏈接，避免長連接［默認8個小時］

max_connect_errors=10 //10次連接失敗就鎖定，使用flush hosts 解鎖，

或mysqladmin flush-hosts解鎖

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL語句調優：

explain命令：查詢select。

.type

這列很重要,顯示了連接使用了哪種類別,有無使用索引

從最好到最差的連接類型為const、eq_reg、ref、range、indexhe和ALL

感謝各位的閱讀，以上就是“MySQL的調優思路和實際操作”的內容了，經過本文的學習后，相信大家對MySQL的調優思路和實際操作這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！