1. 環(huán)境準(zhǔn)備

cpu:4 , mem:8G , mysql:5.7.26全新環(huán)境

1.1. 壓力測試準(zhǔn)備

mysqladmin -uroot -p password 123
mysql < t100w.sql 
mysqladmin -uroot -p password 123

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

T_)%J6(2N)WYIMQ9`GZ8F6Y.png

2. 優(yōu)化哲學(xué)

2.1 為什么

為了獲得成就感?
為了證實比系統(tǒng)設(shè)計者更懂?dāng)?shù)據(jù)庫?
為了從優(yōu)化成果來證實優(yōu)化者更有價值?

QQ圖片20190823091321.png

但通常事實證實的結(jié)果往往會和您期待相反徽龟！
優(yōu)化有風(fēng)險羔杨，涉足需謹(jǐn)慎立镶！

2.2 優(yōu)化風(fēng)險

優(yōu)化不總是對一個單純的環(huán)境進(jìn)行镰烧！還很可能是一個復(fù)雜的已投產(chǎn)的系統(tǒng)。
優(yōu)化手段本來就有很大的風(fēng)險，只不過你沒能力意識到和預(yù)見到！
任何的技術(shù)可以解決一個問題脯厨，但必然存在帶來一個問題的風(fēng)險！
對于優(yōu)化來說解決問題而帶來的問題控制在可接受的范圍內(nèi)才是有成果坑质。
保持現(xiàn)狀或出現(xiàn)更差的情況都是失敽衔洹个少！

穩(wěn)定性和業(yè)務(wù)可持續(xù)性通常比性能更重要！
優(yōu)化不可避免涉及到變更眯杏，變更就有風(fēng)險夜焦！
優(yōu)化使性能變好，維持和變差是等概率事件岂贩！
優(yōu)化不能只是數(shù)據(jù)庫管理員擔(dān)當(dāng)風(fēng)險茫经，但會所有的人分享優(yōu)化成果！
所以優(yōu)化工作是由業(yè)務(wù)需要驅(qū)使的Ｎ颉Ｐ渡　！

2.3 誰參與

DBA(系統(tǒng)锉屈，硬件荤傲，網(wǎng)絡(luò)，存儲颈渊，代碼遂黍，架構(gòu)思維)

業(yè)務(wù)部門代表
應(yīng)用程序架構(gòu)師
應(yīng)用程序設(shè)計人員
應(yīng)用程序開發(fā)人員

硬件及系統(tǒng)管理員
存儲管理員

2.4 優(yōu)化的方向

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

2.5 優(yōu)化范圍及思路

優(yōu)化范圍：
存儲、主機(jī)和操作系統(tǒng):
    主機(jī)架構(gòu)穩(wěn)定性
    I/O規(guī)劃及配置
    Swap
    OS內(nèi)核參數(shù)
        網(wǎng)絡(luò)問題
應(yīng)用程序:（Index俊嗽，lock雾家，session）
        應(yīng)用程序穩(wěn)定性和性能
        SQL語句性能
    串行訪問資源
    性能欠佳會話管理
數(shù)據(jù)庫優(yōu)化:（內(nèi)存、數(shù)據(jù)庫設(shè)計绍豁、參數(shù)）
    內(nèi)存
    數(shù)據(jù)庫結(jié)構(gòu)(物理&邏輯)
    實例配置
架構(gòu)
    性能：讀寫分離芯咧，分布式
    安全：主備，多活
    安全&性能：分布式竹揍，NewSQL

優(yōu)化效果成本評估圖

image.png

2.6 優(yōu)化工具和命令

2.6.1 系統(tǒng)層面

CPU
MEM
IO
top命令查看:
按1展開
%Cpu0  :  0.0 us,  0.0 sy,  0.0 ni,100.0 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
%Cpu1  :  0.0 us,  0.0 sy,  0.0 ni,100.0 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
%Cpu2  :  0.0 us,  0.0 sy,  0.0 ni,100.0 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
%Cpu3  :  0.0 us,  0.0 sy,  0.0 ni,100.0 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st

%Cpu(s):  0.0 us,  0.1 sy,  0.0 ni, 99.9 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
100.0 id:cpu空閑百分百
        5%以上,還是有正常的
0.0 us:用戶程序占用的CPU百分百
        對于mysql程序,處理數(shù)據(jù).
0.0 sy:內(nèi)核程序占用的CPU百分百
        資源管理,資源調(diào)度等,計算.
            SYS過高：鎖,并發(fā)連接數(shù)較高
0.0 wa:花在等待的CPU時間
        IO方面出現(xiàn)了問題
            鎖,大事務(wù)
            IO本身:IOPS達(dá)到峰值
top -H,查看線程

ps -ef | grep mysql                               查看mysql的進(jìn)程號
[root@db01 ~]$ ps -H -T -p 9594                   查看mysql線程
[root@db01 ~]$ top -H -p 9594

I/O負(fù)載情況
[root@db01 ~]$ iostat -dk 1          查看磁盤使用率敬飒，每秒顯示一次
[root@wangxin ~]# dd if=/dev/zero of=/root/123 bs=1M count=5120        測試I/O速度
loadrunner評估工具(收費(fèi))

查看轉(zhuǎn)載文章內(nèi)容

2.6.2 數(shù)據(jù)庫層面

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

3. 優(yōu)化思路分解

3.1 硬件優(yōu)化

主機(jī)

真實的硬件（PC Server）: DELL  R系列 ，華為芬位，浪潮无拗，HP，聯(lián)想
云產(chǎn)品：ECS晶衷、數(shù)據(jù)庫RDS蓝纲、DRDS
IBM 小型機(jī) P6  570  595   P7 720  750 780     P8 

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

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

內(nèi)存

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

磁盤選擇

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

存儲

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

網(wǎng)絡(luò)

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

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

Swap調(diào)整

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

這個參數(shù)決定了Linux是傾向于使用swap，還是傾向于釋放文件系統(tǒng)cache吹艇。在內(nèi)存緊張的情況下灶体，數(shù)值越低越傾向于釋放文件系統(tǒng)cache。
當(dāng)然掐暮，這個參數(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灰羽，也不會占用太多

IO調(diào)度策略

centos 7 默認(rèn)是deadline
cat   /sys/block/sda/queue/scheduler

#臨時修改為deadline(centos6)
echo deadline >/sys/block/sda/queue/scheduler 
vi /boot/grub/grub.conf
更改到如下內(nèi)容:
kernel /boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ elevator=deadline rhgb quiet

IO ：
    raid
    no lvm
    ext4或xfs
    ssd
    IO調(diào)度策略
提前規(guī)劃好以上所有問題驮履，減輕MySQL優(yōu)化的難度。

3.3 應(yīng)用端

1\. 開發(fā)過程規(guī)范,標(biāo)準(zhǔn)
2\. 減少爛SQL:不走索引,復(fù)雜邏輯,切割大事務(wù).
3\. 避免業(yè)務(wù)邏輯錯誤,避免鎖爭用.
這個階段,需要我們DBA深入業(yè)務(wù),或者要和開發(fā)人員\業(yè)務(wù)人員配合實現(xiàn)

優(yōu)化,最根本的是"優(yōu)化"人.
                    ----oldguo      

4. MySQL參數(shù)優(yōu)化測試

虛擬機(jī)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.t1000w where k2='FGCD'" engine=innodb \
--number-of-queries=200 -uroot -p123 -verbose

5. 優(yōu)化細(xì)節(jié)：

5.1 參數(shù)優(yōu)化

Max_connections *****

（1）簡介
Mysql的最大連接數(shù)，如果服務(wù)器的并發(fā)請求量比較大怠噪，可以調(diào)高這個值恐似，當(dāng)然這是要建立在機(jī)器能夠支撐的情況下锣夹，因為如果連接數(shù)越來越多擦剑，mysql會為每個連接提供緩沖區(qū)吉捶，就會開銷的越多的內(nèi)存霹陡，所以需要適當(dāng)?shù)恼{(diào)整該值，不能隨便去提高設(shè)值双藕。
（2）判斷依據(jù)
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

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

back_log ***

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

wait_timeout和interactive_timeout ****

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

5.4 key_buffer_size *****

（1）簡介
key_buffer_size指定索引緩沖區(qū)的大小，它決定索引處理的速度鬼雀，尤其是索引讀的速度
《1》此參數(shù)與myisam表的索引有關(guān)
《2》臨時表的創(chuàng)建有關(guān)（多表鏈接顷窒、子查詢中、union）
     在有以上查詢語句出現(xiàn)的時候源哩，需要創(chuàng)建臨時表鞋吉，用完之后會被丟棄
     臨時表有兩種創(chuàng)建方式：
                        內(nèi)存中------->key_buffer_size
                        磁盤上------->ibdata1(5.6)
                                      ibtmp1 (5.7）
（2）設(shè)置依據(jù)
通過key_read_requests和key_reads可以直到key_baffer_size設(shè)置是否合理。
mysql> show variables like "key_buffer_size%";
+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| key_buffer_size | 8388608 |
+-----------------+---------+
1 row in set (0.00 sec)

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

mysql> 
一共有10個索引讀取請求励烦，有2個請求在內(nèi)存中沒有找到直接從硬盤中讀取索引
控制在 5%以內(nèi) 谓着。
注：key_buffer_size只對myisam表起作用，即使不使用myisam表坛掠，但是內(nèi)部的臨時磁盤表是myisam表赊锚，也要使用該值。
可以使用檢查狀態(tài)值created_tmp_disk_tables得知：

mysql> show status like "created_tmp%";
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |        磁盤臨時表的個數(shù)
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 1     |        內(nèi)存臨時表的個數(shù)
+-------------------------+-------+
3 rows in set (0.00 sec)
mysql> 
通常地屉栓，我們習(xí)慣以 Created_tmp_tables/(Created_tmp_disk_tables + Created_tmp_tables) 
Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables) 

或者已各自的一個時段內(nèi)的差額計算舷蒲，來判斷基于內(nèi)存的臨時表利用率。所以友多，我們會比較關(guān)注 Created_tmp_disk_tables 是否過多牲平，從而認(rèn)定當(dāng)前服務(wù)器運(yùn)行狀況的優(yōu)劣。
Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables) 
控制在5%-10%以內(nèi)
看以下例子：
在調(diào)用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 我們完全不用擔(dān)心渊跋，因此沒必要過分關(guān)注 Created_tmp_disk_tables，但如果它的值大的離譜的話着倾，那就好好查一下拾酝，你的服務(wù)器到底都在執(zhí)行什么查詢了。 
（3）配置方法
key_buffer_size=64M

query_cache_size ***

（1）簡介：
查詢緩存簡稱QC屈呕，使用查詢緩沖微宝，mysql將查詢結(jié)果存放在緩沖區(qū)中，今后對于同樣的select語句（區(qū)分大小寫）,將直接從緩沖區(qū)中讀取結(jié)果虎眨。

SQL層：
select * from t1 where name=:NAME;
select * from t1 where name=:NAME;

1蟋软、查詢完結(jié)果之后，會對SQL語句進(jìn)行hash運(yùn)算嗽桩，得出hash值,我們把他稱之為SQL_ID
2岳守、會將存儲引擎返回的結(jié)果+SQL_ID存儲到緩存中。

存儲方式：
例子：select * from t1  where id=10;      100次

1碌冶、將select * from t1  where id=10; 進(jìn)行hash運(yùn)算計算出一串hash值湿痢，我們把它稱之為“SQL_ID"
2、將存儲引擎返回上來的表的內(nèi)容+SQLID存儲到查詢緩存中

使用方式：
1、一條SQL執(zhí)行時譬重，進(jìn)行hash運(yùn)算拒逮，得出SQLID，去找query cache
2臀规、如果cache中有滩援，則直接返回數(shù)據(jù)行，如果沒有塔嬉，就走原有的SQL執(zhí)行流程

一個sql查詢?nèi)绻詓elect開頭玩徊，那么mysql服務(wù)器將嘗試對其使用查詢緩存。
注：兩個sql語句谨究，只要想差哪怕是一個字符（列如大小寫不一樣恩袱；多一個空格等）,那么這兩個sql將使用不同的一個cache。

（2）判斷依據(jù)
mysql> show status like "%Qcache%";
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1031360 |
| Qcache_hits             | 0       |
| Qcache_inserts          | 0       |
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 2002    |
| Qcache_queries_in_cache | 0       |
| Qcache_total_blocks     | 1       |
+-------------------------+---------+
8 rows in set (0.00 sec)

---------------------狀態(tài)說明--------------------
Qcache_free_blocks：緩存中相鄰內(nèi)存塊的個數(shù)胶哲。
如果該值顯示較大畔塔，則說明Query Cache 中的內(nèi)存碎片較多了，F(xiàn)LUSH QUERY CACHE會對緩存中的碎片進(jìn)行整理纪吮，從而得到一個空閑塊俩檬。
注：當(dāng)一個表被更新之后，和它相關(guān)的cache 
blocks將被free碾盟。但是這個block依然可能存在隊列中棚辽，除非是在隊列的尾部”龋可以用FLUSH QUERY CACHE語句來清空free blocks

Qcache_free_memory：Query Cache 中目前剩余的內(nèi)存大小屈藐。通過這個參數(shù)我們可以較為準(zhǔn)確的觀察出當(dāng)前系統(tǒng)中的Query Cache 內(nèi)存大小是否足夠，是需要增加還是過多了熙尉。

Qcache_hits：表示有多少次命中緩存联逻。我們主要可以通過該值來驗證我們的查詢緩存的效果。數(shù)字越大检痰，緩存效果越理想包归。

Qcache_inserts：表示多少次未命中然后插入，意思是新來的SQL請求在緩存中未找到铅歼，不得不執(zhí)行查詢處理公壤，執(zhí)行查詢處理后把結(jié)果insert到查詢緩存中。這樣的情況的次數(shù)越多椎椰，表示查詢緩存應(yīng)用到的比較少厦幅，效果也就不理想。當(dāng)然系統(tǒng)剛啟動后慨飘，查詢緩存是空的确憨，這很正常。

Qcache_lowmem_prunes：
多少條Query因為內(nèi)存不足而被清除出QueryCache。通過“Qcache_lowmem_prunes”和“Qcache_free_memory”相互結(jié)合休弃，能夠更清楚的了解到我們系統(tǒng)中Query Cache 的內(nèi)存大小是否真的足夠吞歼，是否非常頻繁的出現(xiàn)因為內(nèi)存不足而有Query 被換出。這個數(shù)字最好長時間來看玫芦；如果這個數(shù)字在不斷增長浆熔，就表示可能碎片非常嚴(yán)重本辐，或者內(nèi)存很少桥帆。（上面的free_blocks和free_memory可以告訴您屬于哪種情況）

Qcache_not_cached：不適合進(jìn)行緩存的查詢的數(shù)量，通常是由于這些查詢不是 SELECT 語句或者用了now()之類的函數(shù)慎皱。

Qcache_queries_in_cache：當(dāng)前Query Cache 中cache 的Query 數(shù)量老虫；
Qcache_total_blocks：當(dāng)前Query Cache 中的block 數(shù)量；茫多。
Qcache_hits / (Qcache_inserts+Qcache_not_cached+Qcache_hits) 
    90/         10000             0             90

如果出現(xiàn)hits比例過低祈匙，其實就可以關(guān)閉查詢緩存了。使用redis專門緩存數(shù)據(jù)庫

Qcache_free_blocks    來判斷碎片
Qcache_free_memory   +   Qcache_lowmem_prunes  來判斷內(nèi)存夠不夠
Qcache_hits 多少次命中  Qcache_hits / (Qcache_inserts+Qcache_not_cached+Qcache_hits)  

（3）配置示例
mysql> show variables like '%query_cache%' ;
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 1048576 |
| query_cache_type             | OFF     |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
6 rows in set (0.00 sec)

mysql> 
-------------------配置說明-------------------------------
以上信息可以看出query_cache_type為off表示不緩存任何查詢

各字段的解釋：
query_cache_limit：超過此大小的查詢將不緩存
query_cache_min_res_unit：緩存塊的最小大小天揖，query_cache_min_res_unit的配置是一柄”雙刃劍”夺欲，默認(rèn)是4KB，設(shè)置值大對大數(shù)據(jù)查詢有好處今膊，但如果你的查詢都是小數(shù)據(jù)查詢些阅，就容易造成內(nèi)存碎片和浪費(fèi)。
query_cache_size：查詢緩存大小 (注：QC存儲的最小單位是1024byte斑唬，所以如果你設(shè)定了一個不是1024的倍數(shù)的值市埋，這個值會被四舍五入到最接近當(dāng)前值的等于1024的倍數(shù)的值。)

query_cache_type：緩存類型恕刘，決定緩存什么樣的查詢缤谎，注意這個值不能隨便設(shè)置，必須設(shè)置為數(shù)字褐着，可選項目以及說明如下：
如果設(shè)置為0坷澡，那么可以說，你的緩存根本就沒有用含蓉，相當(dāng)于禁用了频敛。
如果設(shè)置為1，將會緩存所有的結(jié)果谴餐，除非你的select語句使用SQL_NO_CACHE禁用了查詢緩存姻政。
如果設(shè)置為2，則只緩存在select語句中通過SQL_CACHE指定需要緩存的查詢岂嗓。

修改/etc/my.cnf,配置完后的部分文件如下：
query_cache_size=128M
query_cache_type=1

max_connect_errors ***

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

sort_buffer_size ***

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

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

max_allowed_packet *****

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

join_buffer_size ***

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

thread_cache_size *****

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

db01 [(none)]>show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 9     |
+-------------------+-------+
1 row in set (0.00 sec)

試圖連接到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 :代表當(dāng)前此時此刻線程緩存中有多少空閑線程日月。
Threads_connected:代表當(dāng)前已建立連接的數(shù)量袱瓮，因為一個連接就需要一個線程，所以也可以看成當(dāng)前被使用的線程數(shù)爱咬。
Threads_created:代表從最近一次服務(wù)啟動尺借，已創(chuàng)建線程的數(shù)量，如果發(fā)現(xiàn)Threads_created值過大的話精拟，表明MySQL服務(wù)器一直在創(chuàng)建線程燎斩，這也是比較耗cpu SYS資源虱歪，可以適當(dāng)增加配置文件中thread_cache_size值。
Threads_running :代表當(dāng)前激活的（非睡眠狀態(tài)）線程數(shù)栅表。并不是代表正在使用的線程數(shù)笋鄙，有時候連接已建立，但是連接處于sleep狀態(tài)怪瓶。
(3)配置方法：
thread_cache_size=32

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

innodb_buffer_pool_size *****

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

innodb_flush_log_at_trx_commit ******

（1）簡介
主要控制了innodb將log buffer中的數(shù)據(jù)寫入日志文件并flush磁盤的時間點(diǎn)货抄，取值分別為0述召、1、2三個蟹地。
0积暖，表示當(dāng)事務(wù)提交時，不做日志寫入操作怪与，而是每秒鐘將log buffer中的數(shù)據(jù)寫入日志文件并flush磁盤一次夺刑；
1，
每次事務(wù)的提交都會引起redo日志文件寫入分别、flush磁盤的操作遍愿，確保了事務(wù)的ACID；
2耘斩，每次事務(wù)提交引起寫入日志文件的動作,但每秒鐘完成一次flush磁盤操作沼填。

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

innodb_thread_concurrency ***

（1）簡介
此參數(shù)用來設(shè)置innodb線程的并發(fā)數(shù)量腊徙，默認(rèn)值為0表示不限制简十。
（2）配置依據(jù)
在官方doc上，對于innodb_thread_concurrency的使用撬腾，也給出了一些建議螟蝙，如下：
如果一個工作負(fù)載中，并發(fā)用戶線程的數(shù)量小于64民傻，建議設(shè)置innodb_thread_concurrency=0胰默；
如果工作負(fù)載一直較為嚴(yán)重甚至偶爾達(dá)到頂峰，建議先設(shè)置innodb_thread_concurrency=128漓踢，
并通過不斷的降低這個參數(shù)牵署，96, 80, 64等等，直到發(fā)現(xiàn)能夠提供最佳性能的線程數(shù)喧半，
例如奴迅，假設(shè)系統(tǒng)通常有40到50個用戶，但定期的數(shù)量增加至60挺据，70取具，甚至200。你會發(fā)現(xiàn)扁耐，
性能在80個并發(fā)用戶設(shè)置時表現(xiàn)穩(wěn)定暇检，如果高于這個數(shù)，性能反而下降做葵。在這種情況下占哟，
建議設(shè)置innodb_thread_concurrency參數(shù)為80，以避免影響性能酿矢。
如果你不希望InnoDB使用的虛擬CPU數(shù)量比用戶線程使用的虛擬CPU更多（比如20個虛擬CPU）榨乎，
建議通過設(shè)置innodb_thread_concurrency 參數(shù)為這個值（也可能更低，這取決于性能體現(xiàn)）瘫筐，
如果你的目標(biāo)是將MySQL與其他應(yīng)用隔離蜜暑，你可以l考慮綁定mysqld進(jìn)程到專有的虛擬CPU。
但是需 要注意的是策肝，這種綁定肛捍，在myslqd進(jìn)程一直不是很忙的情況下隐绵，可能會導(dǎo)致非最優(yōu)的硬件使用率。在這種情況下拙毫，
你可能會設(shè)置mysqld進(jìn)程綁定的虛擬 CPU依许，允許其他應(yīng)用程序使用虛擬CPU的一部分或全部。
在某些情況下缀蹄，最佳的innodb_thread_concurrency參數(shù)設(shè)置可以比虛擬CPU的數(shù)量小峭跳。
定期檢測和分析系統(tǒng)，負(fù)載量缺前、用戶數(shù)或者工作環(huán)境的改變可能都需要對innodb_thread_concurrency參數(shù)的設(shè)置進(jìn)行調(diào)整蛀醉。

128   -----> top  cpu  
設(shè)置標(biāo)準(zhǔn)：
1、當(dāng)前系統(tǒng)cpu使用情況衅码，均不均勻
top

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

innodb_log_buffer_size

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

設(shè)定依據(jù)：
1、大事務(wù)： 存儲過程調(diào)用 CALL
2巫糙、多事務(wù)

innodb_log_file_size = 100M *****

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

innodb_log_files_in_group = 3 *****

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

read_buffer_size = 1M **

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

read_rnd_buffer_size = 1M **

MySql的隨機(jī)讀（查詢操作）緩沖區(qū)大小筐付。當(dāng)按任意順序讀取行時(例如，按照排序順序)阻肿，將分配一個隨機(jī)讀緩存區(qū)瓦戚。進(jìn)行排序查詢時，MySql會首先掃描一遍該緩沖丛塌，以避免磁盤搜索较解，提高查詢速度畜疾，如果需要排序大量數(shù)據(jù)，可適當(dāng)調(diào)高該值印衔。但MySql會為每個客戶連接發(fā)放該緩沖空間啡捶，所以應(yīng)盡量適當(dāng)設(shè)置該值，以避免內(nèi)存開銷過大奸焙。
注：順序讀是指根據(jù)索引的葉節(jié)點(diǎn)數(shù)據(jù)就能順序地讀取所需要的行數(shù)據(jù)瞎暑。隨機(jī)讀是指一般需要根據(jù)輔助索引葉節(jié)點(diǎn)中的主鍵尋找實際行數(shù)據(jù)，而輔助索引和主鍵所在的數(shù)據(jù)段不同忿偷，因此訪問方式是隨機(jī)的金顿。

bulk_insert_buffer_size = 8M **

批量插入數(shù)據(jù)緩存大小，可以有效提高插入效率鲤桥，默認(rèn)為8M
tokuDB    percona
myrocks   
RocksDB
TiDB
MongoDB

binary log *****

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

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

Innodb_flush_method=(O_DIRECT, fsync) 
1翘贮、fsync    ：
（1）在數(shù)據(jù)頁需要持久化時，首先將數(shù)據(jù)寫入OS buffer中征冷，然后由os決定什么時候?qū)懭氪疟P
（2）在redo buffuer需要持久化時择膝，首先將數(shù)據(jù)寫入OS buffer中，然后由os決定什么時候?qū)懭氪疟P
但检激，如果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決定什么時候?qū)懭氪疟P
但，如果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

一般情況下跺株，我們更偏向于安全。 
“雙一標(biāo)準(zhǔn)”
innodb_flush_log_at_trx_commit=1        ***************
sync_binlog=1                                   ***************
innodb_flush_method=O_DIRECT

6. 參數(shù)優(yōu)化結(jié)果

[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
back_log=128
wait_timeout=60
interactive_timeout=7200
key_buffer_size=16M
query_cache_size=64M
query_cache_type=1
query_cache_limit=50M
max_connect_errors=20
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
thread_cache_size=200
innodb_buffer_pool_size=1024M
innodb_flush_log_at_trx_commit=1
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

7. 鎖的監(jiān)控及處理

7.1 鎖等待模擬

概念：
Record Lock 
Next Lock 
GAP Lock
X 
IX
S 
IS

tx1:

USE oldboy
UPDATE t_100w SET k1='av' WHERE id=10;
## tx2:
USE oldboy 
UPDATE  t_100w SET k1='az' WHERE id=10;

監(jiān)控鎖狀態(tài)

image

## 1\. 看有沒有鎖等待
SHOW  STATUS LIKE 'innodb_row_lock%';

## 2\. 查看哪個事務(wù)在等待(被阻塞了)
USE information_schema
SELECT * FROM information_schema.INNODB_TRX WHERE trx_state='LOCK WAIT';
trx_id : 事務(wù)ID號
trx_state : 當(dāng)前事務(wù)的狀態(tài)
trx_mysql_thread_id:連接層的,連接線程ID(SHOW PROCESSLIST ===>Id或trx_id )
trx_query : 當(dāng)前被阻塞的操作(一般是要丟給開發(fā)的)

7.3.查看鎖源,誰鎖的我!

SELECT * FROM sys.innodb_lock_waits;     ## ====>被鎖的和鎖定它的之間關(guān)系

locked_table : 哪張表出現(xiàn)的等待 
waiting_trx_id: 等待的事務(wù)(與上個視圖trx_id 對應(yīng))
waiting_pid   : 等待的線程號(與上個視圖trx_mysql_thread_id)
blocking_trx_id : 鎖源的事務(wù)ID 
blocking_pid    : 鎖源的線程號

7.4. 找到鎖源的thread_id

SELECT * FROM performance_schema.threads WHERE processlist_id=15;
====> 41

7.5. 找到鎖源的SQL語句

-- 當(dāng)前在執(zhí)行的語句
SELECT * FROM performance_schema.`events_statements_current` WHERE thread_id=41;
-- 執(zhí)行語句的歷史
SELECT * FROM performance_schema.`events_statements_history` WHERE thread_id=41;

得出結(jié)果,丟給開發(fā)
表信息 
被阻塞的
鎖源SQL

練習(xí):
一鍵獲得以上信息,請寫出具體的SQL語句

7.6 優(yōu)化項目:鎖的監(jiān)控及處理

1. 背景: 
硬件環(huán)境: DELL R720,E系列16核,48G MEM,SAS*900G*6,RAID10
在例行巡檢時,發(fā)現(xiàn)9-11點(diǎn)時間段的CPU壓力非常高(80-90%)

2. 項目的職責(zé)
    2.1 通過top詳細(xì)排查,發(fā)現(xiàn)mysqld進(jìn)程占比達(dá)到了700-800%
    2.2 其中有量的CPU是被用作的SYS和WAIT,us處于正常
    2.3 懷疑是MySQL 鎖 或者SQL語句出了問題
    2.4 經(jīng)過排查slowlog及鎖等待情況,發(fā)現(xiàn)有大量鎖等待及少量慢語句    
    (1) pt-query-diagest 查看慢日志  
    (2) 鎖等待有沒有?
    db03 [(none)]>show status like 'innodb_row_lock%';
    +-------------------------------+-------+
    | Variable_name                 | Value |
    +-------------------------------+-------+
    | Innodb_row_lock_current_waits | 0     |
    | Innodb_row_lock_time          | 0     |
    | Innodb_row_lock_time_avg      | 0     |
    | Innodb_row_lock_time_max      | 0     |
    | Innodb_row_lock_waits         | 0     |
    +-------------------------------+-------+
    情況一:
            有100多個current_waits,說明當(dāng)前很多鎖等待情況
    情況二:
            1000多個lock_waits,說明歷史上發(fā)生過的鎖等待很多
    2.5 查看那個事務(wù)在等待(被阻塞了)
    2.6 查看鎖源事務(wù)信息(誰鎖的我)
    2.7 找到鎖源的thread_id 
    2.8 找到鎖源的SQL語句
3. 找到語句之后,和應(yīng)用開發(fā)人員進(jìn)行協(xié)商   
    (1)
    開發(fā)人員描述,此語句是事務(wù)掛起導(dǎo)致
    我們提出建議是臨時kill 會話,最終解決問題
    (2) 
    開發(fā)人員查看后,發(fā)現(xiàn)是業(yè)務(wù)邏輯問題導(dǎo)致的死鎖,產(chǎn)生了大量鎖等待
    臨時解決方案,將阻塞事務(wù)的會話kill掉.
    最終解決方案,修改代碼中的業(yè)務(wù)邏輯
項目結(jié)果:
    經(jīng)過排查處理,鎖等待的個數(shù)減少80%.解決了CPU持續(xù)峰值的問題.

鎖監(jiān)控設(shè)計到的命令:
show status like 'innodb_rows_lock%'
select * from information_schema.innodb_trx;
select * from sys.innodb_lock_waits;
select * from performance_schema.threads;
select * from performance_schema.events_statements_current;
select * from performance_schema.events_statements_history;

解決：

模擬:
## 開啟第一個端口執(zhí)行:
BEGIN;
UPDATE t100w SET k1='aa' WHERE id=10;
## 開啟第二個端口執(zhí)行:
BEGIN
UPDATE t100w SET k1='aa' WHERE id=10;
解決：
SHOW DATABASES;
USE test;
第一步:先查看有沒有鎖等待
SHOW STATUS LIKE 'innodb_row_lock%';

第二步:查看哪個事務(wù)在等待了(被堵塞了)
USE information_schema
SELECT * FROM information_schema.INNODB_TRX WHERE trx_state='LOCK WAIT';
## trx_query:  被阻塞的語句是誰
## trx_mysql_thread_id:  被阻塞的會話ID

第三步:查看鎖源,誰鎖的我脖卖！
SELECT * FROM sys.innodb_lock_waits;
## waiting_pid:  等待的會話線程號
## waiting_query:等待的語句
## blocking_pid: 鎖源的線程號:2
## sql_kill_blocking_query: 處理建議
## sql_kill_blocking_connection: 處理建議

第四步:找到鎖源的thread_id
SELECT * FROM performance_schema.threads WHERE processlist_id=2;
## thread_id=27

第五步:找到鎖源的SQL語句
-- 當(dāng)前在執(zhí)行的語句
SELECT * FROM performance_schema.`events_statements_current` WHERE thread_id=27;
##sql_text:鎖源的SQL

7.7 死鎖監(jiān)控

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

8. 主從優(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
slave-parallel-workers=16
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ù)作為標(biāo)準(zhǔn)

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

總結(jié)：

max_connections                   最大連接數(shù)
key_buffer_size                   臨時表         
innodb_flush_log_at_trx_commit    雙一:redo log的刷寫策略,每次事務(wù)提交時,首先刷寫os buffer,立即刷寫到磁盤.
innodb_buffer_pool_size           數(shù)據(jù)緩沖區(qū)大小,建議不要超過物理內(nèi)存80%
max_allowed_packet                允許最大數(shù)據(jù)包的大小
sync_binlog                       雙一:二進(jìn)制日志的刷寫策略,每次事務(wù)提交理解刷寫二進(jìn)制日志
innodb_flush_method               
刷寫策略:
O_DIRECT : 
        刷寫buffer pool 繞過OS buffer ,直接刷磁盤
        刷寫redo buffer 先到OS buffer ,再到磁盤
=======================================
簡單優(yōu)化:
索引
簡單SQL語句