mysql的性能如何實現優化

下文給大家帶來關于mysql的性能如何實現優化，感興趣的話就一起來看看這篇文章吧，相信看完mysql的性能如何實現優化對大家多少有點幫助吧。

MYSQL性能優化 慢查詢分析

1）性能瓶頸定位

Show 命令 慢查詢日志 explain 分析查詢  profiling 分析查詢

2）索引及查詢優化 3）配置優化

MySQL 數據庫是常見的兩個瓶頸是 CPU 和 I/O 的瓶頸，CPU 在飽和的時候一般發生在數據裝入內存或從磁盤上讀取數據時候。

可以用mpstat, iostat, sar 和 vmstat 來查看系統的性能狀態。

iostat,

優化數據庫的性

能，通常有三種：使用索引，使用 EXPLAIN 分析查詢以及調整 MySQL 的內部配置。

1 、 查詢與索引優化分析

在優化 MySQL 時，通常需要對數據庫進行分析EXPLAIN 分析查詢，profiling 分析以及 show 命令查詢系統狀態及系統變量，通過定位分析性能的瓶頸，才能更好的優化數據庫系統的性能。

查看 MySQL云服務器配置信息 mysql> show variables;

查看 MySQL云服務器運行的各種狀態值 mysql> show global status;

# mysqladmin variables -u username -ppassword——顯示系統變量

# mysqladmin extended-status -u username -ppassword——顯示狀態信息

比較全的 show 命令的使用可參考：mysql>help show

或 http://dev.mysql.com/doc/refman/5.7/en/show.html

慢查詢日志開啟：

在配置文件 my.cnf 中在 [mysqld] 一行下面加入 3 個配置參數，并重啟 mysql 服務

slow_query_log = 1 //0 關閉 1 開啟

slow_query_log_file = /usr/local/mysql/data/slow-query.log //慢查詢日志存放地點

long_query_time = 1 //表示查詢超過 1 秒才記錄

在 my.cnf 中添加 log-queries-not-using-indexes 參數，表示向慢查詢日志中記錄下沒有使用索引的查詢。

二 查看慢查詢的設置信息

mysql> show variables like '%slow_query_log%';

mysql> show variables like '%long_query_time%';

化這個 sql 查詢語句

mysql> select * from test1.tb1 where entertime < '2016-9-3' or entertime > '2016-9-3';

explain 分析查詢

使用 EXPLAIN 關鍵字可以模擬優化器執行 SQL 查詢語句，從而知道 MySQL 是如何處理你的

SQL 語句的。

explain 模擬優化器執行 sql 語句來看是沒有使用索引查詢的，而是全表掃描

優化方法：在 stuname 列上創建索引

mysql> create index index_stuname on test1.tb1(stuname);

再次執行 explain

mysql> explain select * from test1.tb1 where stuname='admin'\G;

profiling 分析查詢

通過慢日志查詢可以知道哪些 SQL 語句執行效率低下，通過 explain 我們可以得知 SQL 語句具體執行情況，索引使用等，還可以結合 show 命令查看執行狀態。

select @@profiling; //0 表示未開啟

執行要測試的 sql 語句

mysql> select * from test1.tb1 where stuname='admin' and entertime='2016-9-1';

mysql> show profiles\G; //可以得到被執行的 SQL 語句的時間和 ID

status:是 profile 里的狀態，duration：是 status 狀態下的耗時。

   Mysql配置優化

Mysql 參數優化對于不同的網站，及其在線量，訪問量，帖子數量，網絡情況，以及機器硬件配置都有關系，優化不可能一次性完成，需要不斷的觀察以及調試才有可能得到最佳效果。

1） 連接請求的變量：

1. max_connections

數值過小會經常出現 ERROR 1040: Too many connections 錯誤，可以過 mysql> show status like

'connections';通配符查看當前狀態的連接數量(試圖連接到MySQL(不管是否連接成功)的連接數)，以定奪該值的大小。

最大連接數

max_used_connections / max_connections * 100% （理想值≈ 85%）

如果 max_used_connections 跟 max_connections 相同那么就是 max_connections 設置過低或者超過云服務器負載上限了，低于 10%則設置過大。

2.back_log

MySQL 能暫存的連接數量。當主要 MySQL 線程在一個很短時間內得到非常多的連接請求，它就會起作用。

如何設置 back_log?

修改/etc/my.cnf 文件，在[mysqld]下面添加如下內容，如設置最大連接數為 1024back_log = 數值  重啟

3.wait_timeout 和 interactive_timeout

wait_timeout -- 指的是 MySQL在關閉一個非交互的連接之前所要等待的秒數interactive_time -- 指的是 mysql 在關閉一個交互的連接之前所要等待的秒數。

wait_timeout：

（1）如果設置大小，那么連接關閉的很快，從而使一些持久的連接不起作用

（2）如果設置太大，容易造成連接打開時間過長，在 show processlist 時，能看到太多的 sleep

狀態的連接，從而造成 too many connections 錯誤

3）一般希望 wait_timeout 盡可能地低

interactive_timeout 的設置將要對你的 web application 沒有多大的影響

查看 wait_timeout 和 interactive_timeout

mysql> show variables like '%wait_tmeout%';

mysql> show variables like '%interactive_timeout%';

如何設置 wait_timeout 和 interactive_timeout ?

修改/etc/my.cnf 文件，在[mysqld]下面添加如下內容

 wait_timeout=100

interactive_timeout=100

重啟 MySQL Server 進入后，查看設置已經生效。

 全局緩沖：

4.key_buffer_size

key_buffer_size 指定索引緩沖區的大小，它決定索引處理的速度，尤其是索引讀的速度。通過檢查狀態值 Key_read_requests 和 Key_reads，可以知道 key_buffer_size 設置是否合理。

一共有 6 個索引讀取請求，有 3 個請求在內存中沒有找到直接從硬盤讀取索引，計算索引未命中緩存的概率：

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

調整 key_buffer_size

默認配置數值是 8388608(8M)，主機有 4GB 內存，可以調優值為 268435456(256MB)修改/etc/my.cnf 文件，在[mysqld]下面添加如下內容

5. query_cache_size(查詢緩存簡稱 QC)

使用查詢緩沖，MySQL 將查詢結果存放在緩沖區中，今后對于同樣的 SELECT 語句（區分大小寫），將直接從緩沖區中讀取結果。

如果該值顯示較大，則說明 Query Cache 中的內存碎片較多了，FLUSH QUERY CACHE 會對緩存中的碎片進行整理

Qcache_queries_in_cache：當前 Query Cache 中 cache 的 Query 數量；Qcache_total_blocks：當前 Query Cache 中的 block 數量；。查詢云服務器關于 query_cache 的配置：

query_cache_limit：超過此大小的查詢將不緩存

query_cache_min_res_unit：緩存塊的最小大小 ，query_cache_min_res_unit 的配置是一柄”雙刃劍”，默認是 4KB，設置值大對大數據查詢有好處，但如果你的查詢都是小數據查詢，就容易造成內存碎片和浪費。

在優化之前執行 mysqlslap 工具進行測試

優化之后執行 mysqlslap 工具進行測試

看了以上關于mysql的性能如何實現優化詳細內容，是否有所收獲。如果想要了解更多相關，可以繼續關注我們的行業資訊板塊。