個人原創，允許轉載，請註明出處，作者，否則追究法律責任。MySQL最佳化

標籤：緩衝   roo   大連   mit   parser   不同   mysq   通過   sql最佳化   

 

mysql最佳化

個人原創，允許轉載，請註明出處，作者，否則追究法律責任。
mysql串連數
QPS 每秒查詢數

PV page view

峰值QPS=峰值pv數 * 串連資料庫的pv佔總pv的百分比 * 每個pv的平均請求數
185 * 10% * 20 = 370

1 thread reuse線程數量
show variables like "%thread%";

thread_cache_size thread cache 中存放的最大連接線程數。
1G --- 8
2G --- 16
3G --- 32
>3G --- 64

修改值的方法：
臨時生效：
set global thread_cache_size=16;

永久生效的方法：
thread_cache_size=16寫入my.cnf

2 connection limit串連數限制
set global max_connections=300;
補：32G記憶體的機器可以設定成2000

3 check memory-cache查詢快取，最佳化查詢速度。
MySQL> show variable like "%cache_size%"
query_cache_size 0 ----- 預設為0

臨時設定：
MySQL> set global query_cache_siz=10000000 (10m)

永久生效：
vim my.cnf
在[mysqld]下面添加
query_cache_size=128M （不一定 越大越好，一旦開啟，就會每次查詢都走緩衝，太大反而慢）

thread_cache_size=16
max_connections=1000
query_cache_size=128M

SQL Interface sql介面，用來接受sql請求命令。
PARSER：mysql的解譯器，解釋sql命令，然後將命令交給最佳化器。
Optimizer：mysql的最佳化器，負責最佳化sql的執行，選擇最優的方式進行資料檢索。
caches and buffers: mysql自己的緩衝緩衝，不同的資料庫引擎使用不同的緩衝。

innodb 緩衝
innodb_buffer_pool_size innodb緩衝池大小。 這個值越大越好。
如果mysql專用伺服器，作業系統記憶體的70%-80%。
set global innodb_buffer_pool_size=800000000;
ERROR 1238 (HY000): Variable ‘innodb_buffer_pool_size‘ is a read only variable
報錯，唯讀變數，只能在設定檔中修改。

myisam 緩衝
key_buffer_size myisam的緩衝池大小。可以通過動態方式進行修改。
set global key_buffer_size=134217728;

慢速日誌：
slow log

開啟方法：
vim my.cnf (在mysqld下面添加兩行)
log_slow_queries=/tmp/slow.log （定義慢日誌的位置）
long_query_time=4 （慢速日誌的記錄的時間，當查詢時間超過4秒時記錄到這裡）

table_open_cache 用於限制開啟的最大的緩衝表的數量。（緩衝多少張表）
mysql> show variables like "%table%";
裡面有個: table_open_cache 64 (預設緩衝64張表)
設定：set global table_open_cache=128

清空緩衝的表的數量：
flush tables;

暫存資料表：只有使用者自己才能看到。作用：提高速度。放在記憶體中。
tmp_table_size 16777216 預設16M,不超過這個值，暫存資料表放在記憶體中，如果超過，會放入硬碟。

mysql>show variables like "%timeout%" （單位都是秒）

connect_timeout 10 (預設逾時時間，超過10秒連不上就自動斷開)
wait_timeout預設28800秒,8小時，超過則中斷連線。
net_read_timeout網路讀逾時時間
net_write_timeout網路寫逾時時間

 

innodb的記錄層級：
innodb_flush_log_at_trx_commit ： innodb的日誌 中的快取資料是如何寫入硬碟的。
0最不安全，每隔一秒將資料寫入硬碟，斷電或者資料庫損壞資料都會丟失。效率最高。
1最安全，資料即時寫入硬碟。
2先寫入系統的緩衝，在寫入磁碟，伺服器斷電資料丟失。一般使用這種。

mysqlreport

./mysqlreport --user root --password 123

 

個人原創，允許轉載，請註明出處，作者，否則追究法律責任。MySQL最佳化