資料庫 之 Mysql最佳化的問題

標籤：調整   ida   忽略   run   free   安全性   nes   基於   buffer   

1  概述

資料庫最佳化的方法有四個：

索引策略，通過explain來判斷索引的有效性，移除多餘索引，保留有效索引

單機緩衝，mysql內鍵的緩衝

旁掛緩衝，如使用redis或者memcached，由程式自行決定是否將結果進行緩衝

修改相關參數，提升mysql伺服器的效能

本文主要介紹mysql的單機緩衝以及相關參數的介紹

2  單機緩衝

mysql的內金鑰快取機制，query  cache(查詢快取),mysql緩衝功能是內建的，但是需要啟用，為了足夠高效命中，mysql在記憶體中開闢緩衝空間，緩衝中的資料是kv值，kv值也是緩衝提高效率的原因之一。

這裡的問題是要把什麼當做鍵,鍵是hash值，所以需要將查詢結果做hash運算。Mysql的緩衝是非預先分配的，可能會造成片段，降低效率

為了提高快取命中率，要使得程式員使用同一種分隔來寫語句，如都是用大寫，或者某些關鍵字都是小寫，原因是mysql會將查詢語句做hash運算，大小寫不一樣的話，hash值就不同。如果hash值不同，將不能被命中。通常是把關鍵詞大小。

mysql的查詢快取是單機的，緩衝是放在本地主機上。考慮到快取命中率，在前端調度的時候，可以基於語句做路由，同一語句調度到同一主機上。但是proxysql沒有這種調度能力，haproxy可以實現七層協議調度，但是mysql不支援七層協議，mysql是列表語句，因此不能通過haproxy來做這種調度

查詢快取：

緩衝：k/v 

key：查詢語句的hash值

value：查詢語句的執行結果

如何判斷緩衝是否命中：

通過查詢語句的雜湊值判斷：雜湊值考慮的因素包括

查詢本身、要查詢資料庫、用戶端使用的協議版本、...

SELECT Name FROM students WHERE StuID=3;

Select Name From students where StuID=3;

哪些查詢可能不會被緩衝？如下添加不變緩衝

1 查詢語句中包含UDF(User-Defined Functions)

2 儲存函數

3 使用者自訂變數

4 暫存資料表

5 mysql系統資料表或者是包含列層級許可權的查詢

6 有著不確定結果值的函數(now())；

7 select 語句，如果已經啟用按需緩衝，那麼在select 語句中，[SQL_CACHE | SQL_NO_CACHE]這兩個選項則明確表明該語句需要緩衝或者不緩衝，如明確使用sql_no_cache則不緩衝

查看緩衝相關的選項

MariaDB [(none)]> show global variables like '%cache%';

查詢快取相關的伺服器變數介紹：

query_cache_limit：能夠緩衝的最大查詢結果；（單語句結果集大小上限）

有著較大結果集的語句，顯式使用SQL_NO_CACHE，以避免先緩衝再移出；預設是1M 

query_cache_min_res_unit：記憶體塊的最小分配單位；緩衝過小的查詢結果集會浪費記憶體空間；

較小的值會減少空間浪費，但會導致更頻繁地記憶體配置及回收操作； 

較大值的會帶來空間浪費；

query_cache_size：查詢快取空間的總共可用的大小；單位是位元組，必須是1024的整數倍；0表示沒有啟用 。這個數字建議不要隨便調整，因為每次調整這個值，之前的快取項目都會被刪除，緩衝需要重建

如設定為64M,命令如下

MariaDB [(none)]> set @@global.query_cache_size=67108864;

query_cache_strip_comments

query_cache_type：緩衝功能啟用與否；

ON：啟用；能緩衝的資料就緩衝，處理以上提到的7類不能緩衝的查詢

OFF：禁用；

DEMAND：按需緩衝，僅緩衝SELECT語句中帶SQL_CACHE的查詢結果；如果確定結果大小比緩衝空間大，明確不緩衝，即no_cache。

query_cache_wlock_invalidate：如果某表被其它串連鎖定，是否仍然可以從查詢快取中返回查詢結果；預設為OFF，表示可以；ON則表示不可以；預設是OFF(允許的,雙重否定invalidate OFF所以是啟用的，讀多寫少的環境，緩衝基本是有效)

查看狀態變數：計數器，查詢名字或者插入的次數。

mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';

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

| Variable_name           | Value    |

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

| Qcache_free_blocks      | 1 閒置塊數       |

| Qcache_free_memory      | 16759688 |查詢快取尚且剩餘的空間

| Qcache_hits             | 0  表示命中緩衝的次數    |

| Qcache_inserts          | 0  表示查詢插入的次數    |

| Qcache_lowmem_prunes    | 0  這個值很大，說明緩衝空間太小      |

| Qcache_not_cached       | 0   本來要放入緩衝空間但是實際沒有被緩衝的數量     |

| Qcache_queries_in_cache | 0   在緩衝系統中查詢的個數     |

| Qcache_total_blocks     | 1    查詢總的塊數    |

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

評估select快取命中率公式如下：

Qcache_hits/Com_select 

當讀寫操作差不多的時候，建議關閉該功能，但是如果是大量查詢,即讀多寫少的環境中，建議啟用緩衝

注意，第一次查詢時產生緩衝，接下去的查詢才會命中緩衝

查看Com_select的總數

MariaDB [sunny]> show global status like 'Com%select%';

3  修改相關參數

Innodb儲存引擎相關的參數查看命令如下

MariaDB [sunny]> show global variables like 'innodb%';

一般不建議使用預設參數，

最佳化相關參數，一般以下的參數不使用預設數值運行，而是要根據實際情況調整後才能使用服務，相關參數介紹如下

innodb_buffer_pool_size這個參數的相關解釋如下：

innodb的緩衝池的大小，通常用來緩衝索引，資料，插入資料時的緩衝區。innodb_buffer_pool_size這個參數一般要根據實際情況調大，不支援動態修改，需要修改設定檔，預設是128M,這個值一般要調大，提高效能的最佳化，這個是佔用記憶體空間，這個根據伺服器是專用或者組合使用的，來確定這個數值調整的大小。

如果innodb引擎為主的主機，建議innodb_buffer_pool_size設定40%~60%的記憶體空間。

如果msyql是專用主機，記憶體空間為32G以上，innodb_buffer_pool_size一般建議設定為70--80%的記憶體空間。這個值對mysql效能提升很有協助。但是不能設定太大，如剩餘的記憶體空間不夠系統正常運轉，可能會帶來更多的問題，所以這個值要適當設定。

如果資料集本身較小，可以根據資料變化幅度及規劃的線上時間長度來設定合理的innodb_buffer_pool_size值，如比預估的目標值略大。

5.7版本後innodb_buffer_pool_size可以動態調整，但是，建議將innodb_buffer_pool_size寫入設定檔中，而且建議不要在系統繁忙時調整該值

修改配置如下

[[email protected] ~]#vim /etc/my.cnf.d/server.cnf 

[server]

innodb_buffer_pool_size = 512M

然後重啟mysql服務後生效

innodb_buffer_pool_instances：buffer_pool的區段（執行個體）數量， 表示把記憶體空間大小切割成幾個空間欄位,鎖定的單位會變小，可以提高mysql運行效率，但是數量建議不要太多

innodb_log_files_in_group : 一組的記錄檔數量，至少兩個

innodb_log_file_size：記錄檔大小，不能太大，預設是5M;根據實際情況來調整。建議調大，一般可以設定為50--100M

innodb_flush_logs_at_trx_commit:事務提交刷寫磁碟的設定參數，值為0 1 2

0:log_buffer(記憶體中)每秒1次同步到log_file中，且同時會進行log file 到data file的同步操作，0最多丟失1s的事務

1：每次提交時，log buffer同步到log file,,同時進行log file 到data file的同步操作，1最多丟失1次事務

2:  每次提交時，log buffer同步到log file,,但是不會同時進行log file 到data file的同步操作，由mysql的線程自行決定什麼時候進行重新整理資料。2的效能最高，如果事務的安全性不是很大，建議設定為2

建議：關閉autocommit,而後將此值innodb_flush_logs_at_trx_commit設定為1或2；

innodb_file_per_table:innodb的諸多進階特性都依賴此參數，建議開啟

innodb_file_io_threads:檔案讀寫的io線程數，如果CPU核心夠多，建議調大，預設是4。這個值是要根據並發量和CPU核心數適量調整

innodb_open_files:innodb可開啟的檔案數量上限，按需調整此值

innodb_flush_method：innodb刷寫磁碟的方式

innodb_thread_concurrency:innodb的線程並發數，即核心級可以同時啟動並執行線程數，一般是CPU核心數量*2

skip_name_resolve:忽略主機名稱解析，網路最佳化相關，禁止調用外部dns進行名稱解析

max_connections:最大並發串連數

資料庫 之 Mysql最佳化的問題