1、優(yōu)化風險

優(yōu)化不總是對一個單純的環(huán)境進行！還很可能是一個復雜的已投產(chǎn)的系統(tǒng)。
優(yōu)化手段本來就有很大的風險，只不過你沒能力意識到和預見到！
任何的技術可以解決一個問題，但必然存在帶來一個問題的風險！
對于優(yōu)化來說解決問題而帶來的問題控制在可接受的范圍內才是有成果。
保持現(xiàn)狀或出現(xiàn)更差的情況都是失?。?
穩(wěn)定性和業(yè)務可持續(xù)性通常比性能更重要！
優(yōu)化不可避免涉及到變更，變更就有風險！
優(yōu)化使性能變好，維持和變差是等概率事件！
優(yōu)化不能只是數(shù)據(jù)庫管理員擔當風險，但會所有的人分享優(yōu)化成果！
所以優(yōu)化工作是由業(yè)務需要驅使的?。?！

2、優(yōu)化方向

安全優(yōu)化（業(yè)務持續(xù)性）
性能優(yōu)化（業(yè)務高效性）

3、優(yōu)化的范圍及思路

優(yōu)化范圍：
存儲、主機和操作系統(tǒng):
    主機架構穩(wěn)定性
    I/O規(guī)劃及配置
    Swap
    OS內核參數(shù)
        網(wǎng)絡問題
應用程序:（Index，lock，session）
        應用程序穩(wěn)定性和性能
        SQL語句性能
    串行訪問資源
    性能欠佳會話管理
數(shù)據(jù)庫優(yōu)化:（內存、數(shù)據(jù)庫設計、參數(shù)）
    內存
    數(shù)據(jù)庫結構(物理&邏輯)
    實例配置

4、優(yōu)化工具的使用

4.1、系統(tǒng)層面的

4.2、CPU

4.3、top

4.4、CPU每個核心的分別使用的情況（按1）：

4.5、CPU有效工作時間

計算: 程序運行,數(shù)據(jù)處理
控制: 少量的關于申請資源和釋放資源等

4.6、CPU無效工作時間

等待 IO

4.7、CPU各項指標說明

0.0 us
用戶程序，在運行過程中，使用的CPU時間的占比。
我們希望的是越高越好，盡量控制在90%
0.0 sy
控制: 資源管理，內核的工作(系統(tǒng)調用)
sys高的原因: 
      1.  bug ,中病毒了
      2.  鎖的問題
99.9 id 
CPU空間的時間占比      

0.0 wa
CPU花在等待上的時間
wa高的原因：
          1. 鎖
          2. IO （raid，過度條帶化）
          3. 索引
多cpu使用情況監(jiān)控：
主要判斷我們cpu多核心有沒有被充分利用。
現(xiàn)象：單顆很忙，其他很閑，對于MySQL來講，有可能是并發(fā)參數(shù)設定不合理導致的。

4.8、MEM

KiB Mem :  4028432 total,  3774464 free,   116240 used,   137728 buff/cache 
名稱介紹
total :總內存大小
free  ：空閑的
used  ：在使用的
buff/cache ：緩沖區(qū) 和 緩存

4.9、內存管理子系統(tǒng)

slab Allocator
buddy system 
程序=指令+數(shù)據(jù)
對于page cache來講(OS buffer)
1. 內存的可用空間的計算   free +buffer cache 
2. 內存回收(buffer)的方式:
        (1) 寫入磁盤
        (2) swap  
對于數(shù)據(jù)庫來講：需要將swap屏蔽掉

4.10、swap

KiB Swap:  2097148 total,  2097148 free,        0 used.  3701464 avail Mem 
Linux 6操作系統(tǒng)，默認回收策略（buffer cache），不立即回收策略
內存使用達到10%-60%時候，40% 會使用swap
Linux 7操作系統(tǒng)
內存使用達到10%-30%（70%）時候，才會使用swap
cat /proc/sys/vm/swappiness 
30  
echo 0 >/proc/sys/vm/swappiness    的內容改成0（臨時）
vim /etc/sysctl.conf
添加:
vm.swappiness=0
sysctl -p 

5、iostat 命令

dd if=/dev/zero of=/tmp/bigfile bs=1M count=4096
iostat -dm 1   //每秒鐘顯示磁盤信息
現(xiàn)象說明
1. IO 高 cpu us 也高,屬于正?，F(xiàn)象
2. CPU  us高  IO很低   ,MySQL 不在做增刪改查,有可能是存儲過程,函數(shù),排序,分組,多表連接
3. Wait,SYS 高  , IO低:IO出問題了,鎖等待過多的幾率比較大. 
IOPS：每秒磁盤最多能夠發(fā)生的IO次數(shù)，這是個定值 
頻繁小事務,IOPS很高,達到閾值,可能IO吞吐量沒超過IO最大吞吐量.無法新的IO了
存儲規(guī)劃有問題.

6、數(shù)據(jù)庫優(yōu)化工具

show status  
show variables 
show index  
show processlist 
show slave status
show engine innodb status 
desc /explain 
slowlog
擴展類深度優(yōu)化:
pt系列
mysqlslap 
sysbench 
information_schema 
performance_schema
sys

7、優(yōu)化思路分解

7.1、硬件優(yōu)化

7.2、主機

7.3、CPU根據(jù)數(shù)據(jù)庫類型

OLTP 
OLAP  
IO密集型：線上系統(tǒng)，OLTP主要是IO密集型的業(yè)務，高并發(fā)
CPU密集型：數(shù)據(jù)分析數(shù)據(jù)處理，OLAP，cpu密集型的，需要CPU高計算能力（i系列，IBM power系列）
CPU密集型： I 系列的，主頻很高，核心少 
IO密集型：  E系列（至強），主頻相對低，核心數(shù)量多

7.4、內存

建議2-3倍cpu核心數(shù)量 （ECC）

7.5、磁盤選擇

SATA-III   SAS    Fc    SSD（sata） pci-e  ssd  Flash
主機 RAID卡的BBU(Battery Backup Unit)關閉

7.6、存儲

根據(jù)存儲數(shù)據(jù)種類的不同，選擇不同的存儲設備
配置合理的RAID級別(raid5、raid10、熱備盤)   
r0 :條帶化 ,性能高
r1 :鏡像，安全
r5 :校驗+條帶化，安全較高+性能較高（讀），寫性能較低 （適合于讀多寫少）
r10：安全+性能都很高，最少四塊盤，浪費一半的空間（高IO要求）

7.7、網(wǎng)絡

1、硬件買好的（單卡單口）
2、網(wǎng)卡綁定（bonding），交換機堆疊
以上問題，提前規(guī)避掉。

7.8、操作系統(tǒng)優(yōu)化

7.9、Swap調整

echo 0 >/proc/sys/vm/swappiness的內容改成0（臨時），
/etc/sysctl.conf
上添加vm.swappiness=0（永久）
sysctl -p

這個參數(shù)決定了Linux是傾向于使用swap，還是傾向于釋放文件系統(tǒng)cache。在內存緊張的情況下，數(shù)值越低越傾向于釋放文件系統(tǒng)cache。
當然，這個參數(shù)只能減少使用swap的概率，并不能避免Linux使用swap。

修改MySQL的配置參數(shù)innodb_flush_method，開啟O_DIRECT模式
這種情況下，InnoDB的buffer pool會直接繞過文件系統(tǒng)cache來訪問磁盤，但是redo log依舊會使用文件系統(tǒng)cache。值得注意的是，Redo log是覆寫模式的，即使使用了文件系統(tǒng)的cache，也不會占用太多

8、MySQL參數(shù)優(yōu)化測試示列

虛擬機vm12.5，OS centos 6.9（系統(tǒng)已優(yōu)化），cpu*4（I5 4440 3.1GHZ）,MEM*4GB ,HardDisk:SSD

模擬數(shù)據(jù)庫數(shù)據(jù)
drop database if exists oldboy;
create database oldboy charset utf8mb4 collate utf8mb4_bin;
use oldboy;
create table t_100w (id int,num int,k1 char(2),k2 char(4),dt timestamp);
delimiter //
create  procedure rand_data(in num int)
begin
declare str char(62) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
declare str2 char(2);
declare str4 char(4);
declare i int default 0;
while i<num do
set str2=concat(substring(str,1+floor(rand()*61),1),substring(str,1+floor(rand()*61),1));
set str4=concat(substring(str,1+floor(rand()*61),2),substring(str,1+floor(rand()*61),2));
set i=i+1;
insert into t_100w values (i,floor(rand()*num),str2,str4,now());
end while;
end;
//
delimiter ;
插入100w條數(shù)據(jù)：
call rand_data(10000000);
commit;

mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 --iterations=1 --create-schema='oldboy' \
--query="select * from oldboy.t_100w where k2='FGCD'" engine=innodb \
--number-of-queries=200 -uroot -p123 -verbose

9、參數(shù)優(yōu)化

9.1、Max_connections *****

（1）簡介
Mysql的最大連接數(shù)，如果服務器的并發(fā)請求量比較大，可以調高這個值，當然這是要建立在機器能夠支撐的情況下，因為如果連接數(shù)越來越多，mysql會為每個連接提供緩沖區(qū)，就會開銷的越多的內存，所以需要適當?shù)恼{整該值，不能隨便去提高設值。
（2）判斷依據(jù)
mysql> select @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
|               151 |
+-------------------+

show variables like 'max_connections';
    +-----------------+-------+
    | Variable_name   | Value |
    +-----------------+-------+
    | max_connections | 151   |
    +-----------------+-------+
show status like 'Max_used_connections';
    +----------------------+-------+
    | Variable_name        | Value |
    +----------------------+-------+
    | Max_used_connections | 101   |
    +----------------------+-------+

（3）修改方式舉例
vim /etc/my.cnf 
Max_connections=1024

補充:
    1.開啟數(shù)據(jù)庫時,我們可以臨時設置一個比較大的測試值
    2.觀察show status like 'Max_used_connections';變化
    3.如果max_used_connections跟max_connections相同,
    那么就是max_connections設置過低或者超過服務器的負載上限了，
    低于10%則設置過大. 

9.2、back_log ***

（1）簡介
mysql能暫存的連接數(shù)量，當主要mysql線程在一個很短時間內得到非常多的連接請求時候它就會起作用，如果mysql的連接數(shù)據(jù)達到max_connections時候，新來的請求將會被存在堆棧中，等待某一連接釋放資源，該推棧的數(shù)量及back_log,如果等待連接的數(shù)量超過back_log，將不被授予連接資源。
back_log值指出在mysql暫時停止回答新請求之前的短時間內有多少個請求可以被存在推棧中，只有如果期望在一個短時間內有很多連接的時候需要增加它
（2）判斷依據(jù)
show full processlist
發(fā)現(xiàn)大量的待連接進程時，就需要加大back_log或者加大max_connections的值
（3）修改方式舉例
vim /etc/my.cnf 
back_log=1024

9.3、wait_timeout和interactive_timeout ****

（1）簡介
wait_timeout：指的是mysql在關閉一個非交互的連接之前所要等待的秒數(shù)
interactive_timeout：指的是mysql在關閉一個交互的連接之前所需要等待的秒數(shù)，比如我們在終端上進行mysql管理，使用的即使交互的連接，這時候，如果沒有操作的時間超過了interactive_time設置的時間就會自動的斷開，默認的是28800，可調優(yōu)為7200。
wait_timeout:如果設置太小，那么連接關閉的就很快，從而使一些持久的連接不起作用
（2）設置建議
如果設置太大，容易造成連接打開時間過長，在show processlist時候，能看到很多的連接 ，一般希望wait_timeout盡可能低
（3）修改方式舉例
wait_timeout=60
interactive_timeout=1200
長連接的應用，為了不去反復的回收和分配資源，降低額外的開銷。
一般我們會將wait_timeout設定比較小，interactive_timeout要和應用開發(fā)人員溝通長鏈接的應用是否很多。如果他需要長鏈接，那么這個值可以不需要調整。
另外還可以使用類外的參數(shù)彌補。

9.4、key_buffer_size *****

1、簡介
key_buffer_size指定索引緩沖區(qū)的大小，它決定索引處理的速度，尤其是索引讀的速度
2、此參數(shù)與myisam表的索引有關
3、臨時表的創(chuàng)建有關
4、設置依據(jù)
通過key_read_requests和key_reads可以直到key_baffer_size設置是否合理。
mysql> show variables like "key_buffer_size%";
+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| key_buffer_size | 8388608 |
+-----------------+---------+
1 row in set (0.00 sec)

mysql> show status like "key_read%";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Key_read_requests | 6     |
| Key_reads         | 3     |
+-------------------+-------+
2 rows in set (0.00 sec)

一共有10個索引讀取請求，有3個請求在內存中沒有找到直接從硬盤中讀取索引
控制在 5%以內 。
注：key_buffer_size只對myisam表起作用，即使不使用myisam表，但是內部的臨時磁盤表是myisam表，也要使用該值。
可以使用檢查狀態(tài)值created_tmp_disk_tables得知：
mysql>  show status like "created_tmp%";
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 1     |
+-------------------------+-------+
3 rows in set (0.00 sec)

通常地，我們習慣以 Created_tmp_tables/(Created_tmp_disk_tables + Created_tmp_tables) 
Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables) 

或者已各自的一個時段內的差額計算，來判斷基于內存的臨時表利用率。所以，我們會比較關注 Created_tmp_disk_tables 是否過多，從而認定當前服務器運行狀況的優(yōu)劣。
Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables) 
控制在5%-10%以內

看以下例子：
在調用mysqldump備份數(shù)據(jù)時，大概執(zhí)行步驟如下：
180322 17:39:33       7 Connect     root@localhost on
7 Query       /*!40100 SET @@SQL_MODE='' */
7 Init DB     guo
7 Query       SHOW TABLES LIKE 'guo'
7 Query       LOCK TABLES `guo` READ /*!32311 LOCAL */
7 Query       SET OPTION SQL_QUOTE_SHOW_CREATE=1
7 Query       show create table `guo`
7 Query       show fields from `guo`
7 Query       show table status like 'guo'
7 Query       SELECT /*!40001 SQL_NO_CACHE */ * FROM `guo`
7 Query       UNLOCK TABLES
7 Quit

其中，有一步是：show fields from `guo`。從slow query記錄的執(zhí)行計劃中，可以知道它也產(chǎn)生了 Tmp_table_on_disk。

所以說，以上公式并不能真正反映到mysql里臨時表的利用率，有些情況下產(chǎn)生的 Tmp_table_on_disk 我們完全不用擔心，因此沒必要過分關注 Created_tmp_disk_tables，但如果它的值大的離譜的話，那就好好查一下，你的服務器到底都在執(zhí)行什么查詢了。 
（3）配置方法
key_buffer_size=64M

9.5、max_connect_errors ***

max_connect_errors是一個mysql中與安全有關的計數(shù)器值，它負責阻止過多嘗試失敗的客戶端以防止暴力破解密碼等情況，當超過指定次數(shù)，m，直到mysysql服務器將禁止host的連接請求服務器重啟或通過flush hosts命令清空此host的相關信息 max_connect_errors的值與性能并無太大關系。
修改/etc/my.cnf文件，在[mysqld]下面添加如下內容
max_connect_errors=2000

9.6、sort_buffer_size ***

（1）簡介：
每個需要進行排序的線程分配該大小的一個緩沖區(qū)。增加這值加速
ORDER BY 
GROUP BY
distinct
union 

（2）配置依據(jù)
Sort_Buffer_Size并不是越大越好，由于是connection級的參數(shù)，過大的設置+高并發(fā)可能會耗盡系統(tǒng)內存資源。
列如：500個連接將會消耗500*sort_buffer_size（2M）=1G內存
（3）配置方法
 修改/etc/my.cnf文件，在[mysqld]下面添加如下：
sort_buffer_size=1M

9.7、max_allowed_packet *****

（1）簡介：
mysql根據(jù)配置文件會限制，server接受的數(shù)據(jù)包大小。
（2）配置依據(jù)：
有時候大的插入和更新會受max_allowed_packet參數(shù)限制，導致寫入或者更新失敗，更大值是1GB，必須設置1024的倍數(shù)
（3）配置方法：
max_allowed_packet=32M

9.8、join_buffer_size ***

select a.name,b.name from a join b on a.id=b.id where xxxx
用于表間關聯(lián)緩存的大小，和sort_buffer_size一樣，該參數(shù)對應的分配內存也是每個連接獨享。
盡量在SQL與方面進行優(yōu)化，效果較為明顯。
優(yōu)化的方法：在on條件列加索引，至少應當是有MUL索引

9.9、thread_cache_size *****

(1)簡介
服務器線程緩存，這個值表示可以重新利用保存在緩存中線程的數(shù)量,當斷開連接時,那么客戶端的線程將被放到緩存中以響應下一個客戶而不是銷毀(前提是緩存數(shù)未達上限),如果線程重新被請求，那么請求將從緩存中讀取,如果緩存中是空的或者是新的請求，那么這個線程將被重新創(chuàng)建,如果有很多新的線程，增加這個值可以改善系統(tǒng)性能.
（2）配置依據(jù)
通過比較 Connections 和 Threads_created 狀態(tài)的變量，可以看到這個變量的作用。
設置規(guī)則如下：1GB 內存配置為8，2GB配置為16，3GB配置為32，4GB或更高內存，可配置更大。
服務器處理此客戶的線程將會緩存起來以響應下一個客戶而不是銷毀(前提是緩存數(shù)未達上限)

試圖連接到MySQL(不管是否連接成功)的連接數(shù)
mysql>  show status like 'threads_%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 8     |
| Threads_connected | 2     |
| Threads_created   | 4783  |
| Threads_running   | 1     |
+-------------------+-------+
4 rows in set (0.00 sec)

Threads_cached :代表當前此時此刻線程緩存中有多少空閑線程。
Threads_connected:代表當前已建立連接的數(shù)量，因為一個連接就需要一個線程，所以也可以看成當前被使用的線程數(shù)。
Threads_created:代表從最近一次服務啟動，已創(chuàng)建線程的數(shù)量，如果發(fā)現(xiàn)Threads_created值過大的話，表明MySQL服務器一直在創(chuàng)建線程，這也是比較耗cpu SYS資源，可以適當增加配置文件中thread_cache_size值。
Threads_running :代表當前激活的（非睡眠狀態(tài)）線程數(shù)。并不是代表正在使用的線程數(shù)，有時候連接已建立，但是連接處于sleep狀態(tài)。
(3)配置方法：
thread_cache_size=32

整理：
Threads_created  ：一般在架構設計階段，會設置一個測試值，做壓力測試。
結合zabbix監(jiān)控，看一段時間內此狀態(tài)的變化。
如果在一段時間內，Threads_created趨于平穩(wěn)，說明對應參數(shù)設定是OK。
如果一直陡峭的增長，或者出現(xiàn)大量峰值，那么繼續(xù)增加此值的大小，在系統(tǒng)資源夠用的情況下（內存）

9.10、innodb_buffer_pool_size *****

（1）簡介
對于InnoDB表來說，innodb_buffer_pool_size的作用就相當于key_buffer_size對于MyISAM表的作用一樣。
（2）配置依據(jù)：
InnoDB使用該參數(shù)指定大小的內存來緩沖數(shù)據(jù)和索引。
對于單獨的MySQL數(shù)據(jù)庫服務器，最大可以把該值設置成物理內存的80%,一般我們建議不要超過物理內存的70%。
（3）配置方法
innodb_buffer_pool_size=2048M

9.11、innodb_flush_log_at_trx_commit ******

（1）簡介
主要控制了innodb將log buffer中的數(shù)據(jù)寫入日志文件并flush磁盤的時間點，取值分別為0、1、2三個。
0，表示當事務提交時，不做日志寫入操作，而是每秒鐘將log buffer中的數(shù)據(jù)寫入日志文件并flush磁盤一次；
1，
每次事務的提交都會引起redo日志文件寫入、flush磁盤的操作，確保了事務的ACID；
2，每次事務提交引起寫入日志文件的動作,但每秒鐘完成一次flush磁盤操作。

（2）配置依據(jù)
實際測試發(fā)現(xiàn)，該值對插入數(shù)據(jù)的速度影響非常大，設置為2時插入10000條記錄只需要2秒，設置為0時只需要1秒，而設置為1時則需要229秒。因此，MySQL手冊也建議盡量將插入操作合并成一個事務，這樣可以大幅提高速度。
根據(jù)MySQL官方文檔，在允許丟失最近部分事務的危險的前提下，可以把該值設為0或2。
（3）配置方法
innodb_flush_log_at_trx_commit=1
雙1標準中的一個1

9.12、innodb_thread_concurrency ***

（1）簡介
此參數(shù)用來設置innodb線程的并發(fā)數(shù)量，默認值為0表示不限制。
（2）配置依據(jù)
在官方doc上，對于innodb_thread_concurrency的使用，也給出了一些建議，如下：
如果一個工作負載中，并發(fā)用戶線程的數(shù)量小于64，建議設置innodb_thread_concurrency=0；
如果工作負載一直較為嚴重甚至偶爾達到頂峰，建議先設置innodb_thread_concurrency=128，
并通過不斷的降低這個參數(shù)，96, 80, 64等等，直到發(fā)現(xiàn)能夠提供最佳性能的線程數(shù)，
例如，假設系統(tǒng)通常有40到50個用戶，但定期的數(shù)量增加至60，70，甚至200。你會發(fā)現(xiàn)，
性能在80個并發(fā)用戶設置時表現(xiàn)穩(wěn)定，如果高于這個數(shù)，性能反而下降。在這種情況下，
建議設置innodb_thread_concurrency參數(shù)為80，以避免影響性能。
如果你不希望InnoDB使用的虛擬CPU數(shù)量比用戶線程使用的虛擬CPU更多（比如20個虛擬CPU），
建議通過設置innodb_thread_concurrency 參數(shù)為這個值（也可能更低，這取決于性能體現(xiàn)），
如果你的目標是將MySQL與其他應用隔離，你可以l考慮綁定mysqld進程到專有的虛擬CPU。
但是需 要注意的是，這種綁定，在myslqd進程一直不是很忙的情況下，可能會導致非最優(yōu)的硬件使用率。在這種情況下，
你可能會設置mysqld進程綁定的虛擬 CPU，允許其他應用程序使用虛擬CPU的一部分或全部。
在某些情況下，最佳的innodb_thread_concurrency參數(shù)設置可以比虛擬CPU的數(shù)量小。
定期檢測和分析系統(tǒng)，負載量、用戶數(shù)或者工作環(huán)境的改變可能都需要對innodb_thread_concurrency參數(shù)的設置進行調整。

128   -----> top  cpu  
設置標準：
1、當前系統(tǒng)cpu使用情況，均不均勻
top

2、當前的連接數(shù)，有沒有達到頂峰
show status like 'threads_%';
show processlist;
（3）配置方法：
innodb_thread_concurrency=8
方法:
    1. 看top ,觀察每個cpu的各自的負載情況
    2. 發(fā)現(xiàn)不平均,先設置參數(shù)為cpu個數(shù),然后不斷增加(一倍)這個數(shù)值
    3. 一直觀察top狀態(tài),直到達到比較均勻時,說明已經(jīng)到位了.

9.13、innodb_log_buffer_size

此參數(shù)確定些日志文件所用的內存大小，以M為單位。緩沖區(qū)更大能提高性能，對于較大的事務，可以增大緩存大小。
innodb_log_buffer_size=128M

設定依據(jù)：
1、大事務： 存儲過程調用 CALL
2、多事務

9.14、innodb_log_file_size = 100M *****

設置 ib_logfile0  ib_logfile1 
此參數(shù)確定數(shù)據(jù)日志文件的大小，以M為單位，更大的設置可以提高性能.
innodb_log_file_size = 100M

9.15、innodb_log_files_in_group = 3 *****

為提高性能，MySQL可以以循環(huán)方式將日志文件寫到多個文件。推薦設置為3

9.16、read_buffer_size = 1M **

MySql讀入緩沖區(qū)大小。對表進行順序掃描的請求將分配一個讀入緩沖區(qū)，MySql會為它分配一段內存緩沖區(qū)。如果對表的順序掃描請求非常頻繁，并且你認為頻繁掃描進行得太慢，可以通過增加該變量值以及內存緩沖區(qū)大小提高其性能。和 sort_buffer_size一樣，該參數(shù)對應的分配內存也是每個連接獨享

9.17、binary log *****

log-bin=/data/mysql-bin
binlog_cache_size = 2M //為每個session 分配的內存，在事務過程中用來存儲二進制日志的緩存, 提高記錄bin-log的效率。沒有什么大事務，dml也不是很頻繁的情況下可以設置小一點，如果事務大而且多，dml操作也頻繁，則可以適當?shù)恼{大一點。前者建議是--1M，后者建議是：即 2--4M
max_binlog_cache_size = 8M //表示的是binlog 能夠使用的最大cache 內存大小
max_binlog_size= 512M //指定binlog日志文件的大小，如果當前的日志大小達到max_binlog_size，還會自動創(chuàng)建新的二進制日志。你不能將該變量設置為大于1GB或小于4096字節(jié)。默認值是1GB。在導入大容量的sql文件時，建議關閉sql_log_bin，否則硬盤扛不住，而且建議定期做刪除。
expire_logs_days = 7 //定義了mysql清除過期日志的時間。
二進制日志自動刪除的天數(shù)。默認值為0,表示“沒有自動刪除”。
log-bin=/data/mysql-bin
binlog_format=row 
sync_binlog=1
雙1標準(基于安全的控制)：
sync_binlog=1   什么時候刷新binlog到磁盤，每次事務commit
innodb_flush_log_at_trx_commit=1
set sql_log_bin=0;
show status like 'com_%';

10、安全參數(shù) *****

Innodb_flush_method=(O_DIRECT, fsync) 
1、fsync    ：
（1）在數(shù)據(jù)頁需要持久化時，首先將數(shù)據(jù)寫入OS buffer中，然后由os決定什么時候寫入磁盤
（2）在redo buffuer需要持久化時，首先將數(shù)據(jù)寫入OS buffer中，然后由os決定什么時候寫入磁盤
但，如果innodb_flush_log_at_trx_commit=1的話，日志還是直接每次commit直接寫入磁盤
2、 Innodb_flush_method=O_DIRECT
（1）在數(shù)據(jù)頁需要持久化時，直接寫入磁盤
（2）在redo buffuer需要持久化時，首先將數(shù)據(jù)寫入OS buffer中，然后由os決定什么時候寫入磁盤
但，如果innodb_flush_log_at_trx_commit=1的話，日志還是直接每次commit直接寫入磁盤

最安全模式：
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
最高性能模式：
innodb_flush_log_at_trx_commit=0
innodb_flush_method=fsync
        
一般情況下，我們更偏向于安全。 
“雙一標準”
innodb_flush_log_at_trx_commit=1        ***************
sync_binlog=1                                   ***************
innodb_flush_method=O_DIRECT

11、參數(shù)優(yōu)化結果

[mysqld]
basedir=/data/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
log-error=/var/log/mysql.log
log_bin=/data/binlog/mysql-bin
binlog_format=row
skip-name-resolve
server-id=52
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
relay_log_purge=0
max_connections=1024   //最大連接數(shù)
back_log=128   //超過最大連接數(shù) 還可以有128個可以連
wait_timeout=60   //非交互式連接超時時間
interactive_timeout=7200   //交互式連接超時時間
key_buffer_size=16M  //受myisam索引和臨時表內存影響
query_cache_size=64M
query_cache_type=1
query_cache_limit=50M
max_connect_errors=20  
sort_buffer_size=2M
max_allowed_packet=32M  //服務端接受數(shù)據(jù)大小
join_buffer_size=2M
thread_cache_size=200  //線上緩存?zhèn)€數(shù)
innodb_buffer_pool_size=1024M  //數(shù)據(jù)緩存區(qū)大小
innodb_flush_log_at_trx_commit=1  //redo刷寫磁盤促發(fā)條件
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=3
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
read_buffer_size=2M
read_rnd_buffer_size=2M
bulk_insert_buffer_size=8M
[client]
socket=/tmp/mysql.sock  
        
再次壓力測試  ：
 mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='oldboy' --query="select * from oldboy.t_100w where k2='FGCD'" engine=innodb --number-of-queries=200000 -uroot -p123 -verbose

第一次查詢很慢 第二次查詢就可以看見效果

12、鎖的監(jiān)控及處理

看有沒有鎖等待
mysql> show status like 'innodb_row_lock%';
查看哪個事務在等待(被阻塞了)
mysql> select * from information_schema.innodb_trx where trx_state='lock wait';

trx_id    事務id號
trx_state   當前事務的狀態(tài)
trx_mysql_thread_id   連接層的，連接線程id（show proceeelist）
trx_query   當前被阻塞的操作

查看鎖源，誰鎖的
mysql> select * from sys.innodb_lock_waits;
locked_table : 哪張表出現(xiàn)的等待 
waiting_trx_id: 等待的事務(與上個視圖trx_id 對應)
waiting_pid   : 等待的線程號(與上個視圖trx_mysql_thread_id)
blocking_trx_id : 鎖源的事務ID 
blocking_pid    : 鎖源的連接線程號

找到鎖源的thread_id
SELECT * FROM performance_schema.threads WHERE processlist_id=15;  （15為鎖源的連接線程號  通過這條命令可以找到sql線程id號）
====> 41


找到鎖源的SQL語句
-- 當前在執(zhí)行的語句
SELECT * FROM performance_schema.`events_statements_current` WHERE thread_id=41;
-- 執(zhí)行語句的歷史
SELECT * FROM performance_schema.`events_statements_history` WHERE thread_id=41;

13、死鎖監(jiān)控

show engine innodb status\G
show variables like '%deadlock%';
vim /etc/my.cnf 
innodb_print_all_deadlocks = 1  

14、主從優(yōu)化

5.7 從庫多線程MTS
基本要求:
5.7以上的版本(忘記小版本)
必須開啟GTID 
binlog必須是row模式  

gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
slave-parallel-type=LOGICAL_CLOCK   //實現(xiàn)事務級別sql線程并發(fā)  
slave-parallel-workers=16  //最多可以有1sql并發(fā)線程
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON

5.7 :
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=8
cpu核心數(shù)作為標準

CHANGE MASTER TO
  MASTER_HOST='192.168.206.128',
  MASTER_USER='repl',
  MASTER_PASSWORD='123',
  MASTER_PORT=3307,
  MASTER_AUTO_POSITION=1;
start slave;