1.優(yōu)化哲學
1.1 為什么優(yōu)化拇厢？
為了獲得成就感?
為了證實比系統(tǒng)設計者更懂數(shù)據(jù)庫?
為了從優(yōu)化成果來證實優(yōu)化者更有價值?
但通常事實證實的結果往往會和您期待相反伦籍！
優(yōu)化有風險闸氮，涉足需謹慎！
1.2 優(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è)務需要驅使的Ｉ辍Ｎ鄹选劈猪！
1.3 誰參與優(yōu)化
數(shù)據(jù)庫管理員
業(yè)務部門代表
應用程序架構師
應用程序設計人員
應用程序開發(fā)人員
硬件及系統(tǒng)管理員
存儲管理員
1.4 優(yōu)化方向
安全優(yōu)化（業(yè)務持續(xù)性）
性能優(yōu)化（業(yè)務高效性）
1.5 優(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ù)庫結構(物理&邏輯)
實例配置
優(yōu)化效果和成本的評估：
1. 優(yōu)化工具的使用
1.1 系統(tǒng)層面的
1.1.1 CPU
top
cpu使用情況的平均值：
CPU每個核心的分別使用的情況（按1）：
- 程序是如何使用CPU的常侦？
系統(tǒng)給每個程序分配CPU的時候，以時間來劃分表的贬媒。
- CPU有效工作時間?
計算: 程序運行,數(shù)據(jù)處理
控制: 少量的關于申請資源和釋放資源等
3.CPU無效工作時間
等待 IO
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ù)設定不合理導致的。
1.1.2 MEM
KiB Mem : 4028432 total, 3774464 free, 116240 used, 137728 buff/cache
1.2.1 名稱介紹
total :總內存大小
free ：空閑的
used ：在使用的
buff/cache ：緩沖區(qū) 和 緩存
1.2.2 內存管理子系統(tǒng)：
slab Allocator
buddy system
程序=指令+數(shù)據(jù)
對于page cache來講(OS buffer)
1. 內存的可用空間的計算 free +buffer cache
2. 內存回收(buffer)的方式:
(1) 寫入磁盤
(2) swap
對于數(shù)據(jù)庫來講：需要將swap屏蔽掉
1.1.3 swap
KiB Swap: 2097148 total, 2097148 free, 0 used. 3701464 avail Mem
Linux 6操作系統(tǒng)港柜，默認回收策略（buffer cache）请契，不立即回收策略
內存使用達到100%-60%時候，40% 會使用swap
Linux 7操作系統(tǒng)夏醉，內存使用達到100%-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
1.1.4.iostat 命令
dd if=/dev/zero of=/tmp/bigfile bs=1M count=4096
iostat -dm 1
現(xiàn)象說明
1. IO 高 cpu us 也高,屬于正常現(xiàn)象
2. CPU 高 IO很低 MySQL 不在做增刪改查,有可能是存儲過程,函數(shù),排序,分組,多表連接
3. Wait 高 , IO低:IO出問題了,鎖等待的幾率比較大.
4. IOPS：每秒磁盤最多能夠發(fā)生的IO次數(shù)畔柔，這是個定值 頻繁小事務,IOPS很高,達到閾值,可能IO吞吐量沒超過IO最大吞吐量.無法新的IO了
存儲規(guī)劃有問題.
1.2 數(shù)據(jù)庫基礎優(yōu)化工具
show status
show variables
show index
show processlist
show slave status
show engine innodb status
desc /explain
擴展類深度優(yōu)化:
pt系列
mysqlslap
sysbench
information_schema
performance_schema
sys
==========================================
3. 優(yōu)化思路分解
3.1 硬件優(yōu)化：
主機（真實的硬件+云產(chǎn)品ECS,數(shù)據(jù)庫（RDS氯夷、DRDS））
3.1.1根據(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ù)量多
內存容量選擇：建議2-3倍cpu核心數(shù)量 （ECC）
磁盤選擇：SATA-III SAS Fc SSD（sata） pci-e ssd Flash
主機 RAID卡的BBU(Battery Backup Unit)關閉
存儲：
根據(jù)存儲數(shù)據(jù)種類的不同，選擇不同的存儲設備
配置合理的RAID級別(raid5福也、raid10局骤、熱備盤)
r0 :條帶化 ,性能高
r1 :鏡像，安全
r5 :校驗+條帶化暴凑，安全較高+性能較高（讀）峦甩，寫性能較低 （適合于讀多寫少）
r10：安全+性能都很高，最少四塊盤搬设，浪費一半的空間（高IO要求）
網(wǎng)絡：
1穴店、硬件買好的（單卡單口）
2、網(wǎng)卡綁定（bonding）拿穴，交換機堆疊
以上問題泣洞，提前規(guī)避掉。
2默色、操作系統(tǒng)優(yōu)化
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汁尺，也不會占用太多
IO調度策略:
centos 7 默認是deadline
cat /sys/block/sda/queue/scheduler
臨時修改為deadline(centos6)
echo deadline >/sys/block/sda/queue/scheduler
vi /boot/grub/grub.conf
更改到如下內容:
kernel /boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ elevator=deadline rhgb quiet
IO ：
raid
no lvm
ext4或xfs
ssd
IO調度策略
提前規(guī)劃好以上所有問題法精，減輕MySQL優(yōu)化的難度。
應用端
開發(fā)過程規(guī)范,標準:
1. 減少爛SQL:不走索引,復雜邏輯,切割大事務
2. 避免業(yè)務邏輯錯誤,避免鎖征用
這個階段,需要我們DBA深入業(yè)務,或者要和開發(fā)人員\業(yè)務人員配合實現(xiàn)
優(yōu)化,最根本的是"優(yōu)化"人.
----oldguo
=====================================================
4. MySQL參數(shù)優(yōu)化測試建議
4.1 參數(shù)優(yōu)化前壓力測試
4.4.0 優(yōu)化測試前提
虛擬機vm12.5痴突，OS centos 6.9（系統(tǒng)已優(yōu)化）搂蜓，cpu*4（I5 4440 3.1GHZ）,MEM*4GB ,HardDisk:SSD
1、模擬數(shù)據(jù)庫數(shù)據(jù)
為了測試我們創(chuàng)建一個test1的庫創(chuàng)建一個tb1的表辽装，然后導入20萬行數(shù)據(jù)洛勉，腳本如下：
vim slap.sh
#!/bin/bash
HOSTNAME="localhost"
PORT="3306"
USERNAME="root"
PASSWORD="oldboy123"
DBNAME="oldboy"
TABLENAME="lufei"
#create database
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e "drop database if exists ${DBNAME}"
create_db_sql="create database if not exists ${DBNAME}"
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e "${create_db_sql}"
#create table
create_table_sql="create table if not exists ${TABLENAME}(stuid int not null primary key,stuname varchar(20) not null,stusex char(1)
not null,cardid varchar(20) not null,birthday datetime,entertime datetime,address varchar(100)default null)"
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${create_table_sql}"
#insert data to table
i="1"
while [ $i -le 200000 ]
do
insert_sql="insert into ${TABLENAME} values($i,'guojialei_$i','1','110011198809163418','1988-09-16','2017-09-13','oldboyedu')"
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${insert_sql}"
let i++
done
#select data
select_sql="select count(*) from ${TABLENAME}"
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${select_sql}"
執(zhí)行腳本：
sh slap.sh
2、檢查數(shù)據(jù)可用性
mysql -uroot -p123
select count(*) from oldboy.lufei;
3如迟、在沒有優(yōu)化之前我們使用mysqlslap來進行壓力測試
mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 --iterations=1 --create-schema='oldboy' \
--query="select * from oldboy.t_100w where num='505037'" engine=innodb \
--number-of-queries=20000 -uroot -p123 -verbose
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 31.463 seconds
Minimum number of seconds to run all queries: 31.463 seconds
Maximum number of seconds to run all queries: 31.463 seconds
Number of clients running queries: 100
Average number of queries per client: 20
--------------------------------mysqlslap使用說明----------------------------
mysqlslap工具介紹
? mysqlslap來自于mariadb包，測試的過程默認生成一個mysqlslap的schema,生成測試表t1，查詢和插入測試數(shù)據(jù)殷勘，mysqlslap庫自動生成此再，如果已經(jīng)存在則先刪除。用--only-print來打印實際的測試過程玲销，整個測試完成后不會在數(shù)據(jù)庫中留下痕跡输拇。
常用選項：
--auto-generate-sql, -a 自動生成測試表和數(shù)據(jù)，表示用mysqlslap工具自己生成的SQL腳本來測試并發(fā)壓力
--auto-generate-sql-load-type=type 測試語句的類型贤斜。代表要測試的環(huán)境是讀操作還是寫操作還是兩者混合的策吠。取值包括：read，key瘩绒，write猴抹，update和mixed(默認)
--auto-generate-sql-add-auto-increment 代表對生成的表自動添加auto_increment列，從5.1.18版本開始支持
--number-char-cols=N, -x N 自動生成的測試表中包含多少個字符類型的列锁荔，默認1
--number-int-cols=N, -y N 自動生成的測試表中包含多少個數(shù)字類型的列蟀给，默認1
--number-of-queries=N 總的測試查詢次數(shù)(并發(fā)客戶數(shù)×每客戶查詢次數(shù))
--query=name,-q 使用自定義腳本執(zhí)行測試，例如可以調用自定義的存儲過程或者sql語句來執(zhí)行測試
--create-schema 代表自定義的測試庫名稱阳堕，測試的schema跋理，MySQL中schema也就是database
--commint=N 多少條DML后提交一次
--compress, -C 如服務器和客戶端都支持壓縮，則壓縮信息
--concurrency=N, -c N 表示并發(fā)量恬总，即模擬多少個客戶端同時執(zhí)行select前普；可指定多個值，以逗號或者--delimiter參數(shù)指定值做為分隔符
--engine=engine_name, -e engine_name 代表要測試的引擎壹堰，可以有多個拭卿，用分隔符隔開
--iterations=N, -i N 測試執(zhí)行的迭代次數(shù)，代表要在不同并發(fā)環(huán)境下缀旁，各自運行測試多少次
--only-print 只打印測試語句而不實際執(zhí)行
--detach=N 執(zhí)行N條語句后斷開重連
--debug-info, -T 打印內存和CPU的相關信息
測試示例：
1）單線程測試
[root@centos7 ~]# mysqlslap -a -uroot -p
Enter password:
Benchmark
Average number of seconds to run all queries: 0.004 seconds
Minimum number of seconds to run all queries: 0.004 seconds
Maximum number of seconds to run all queries: 0.004 seconds
Number of clients running queries: 1
Average number of queries per client: 0
2）多線程測試记劈，使用--concurrency來模擬并發(fā)連接
[root@centos7 ~]# mysqlslap -uroot -p -a -c 500
Enter password:
Benchmark
Average number of seconds to run all queries: 3.384 seconds
Minimum number of seconds to run all queries: 3.384 seconds
Maximum number of seconds to run all queries: 3.384 seconds
Number of clients running queries: 500
Average number of queries per client: 0
3）同時測試不同的存儲引擎的性能進行對比
[root@centos7 ~]# mysqlslap -uroot -p -a --concurrency=500 --number-of-queries 1000 --iterations=5 --engine=myisam,innodb --debug-info
Enter password:
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.192 seconds
Minimum number of seconds to run all queries: 0.187 seconds
Maximum number of seconds to run all queries: 0.202 seconds
Number of clients running queries: 500
Average number of queries per client: 2
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.355 seconds
Minimum number of seconds to run all queries: 0.350 seconds
Maximum number of seconds to run all queries: 0.364 seconds
Number of clients running queries: 500
Average number of queries per client: 2
User time 0.33, System time 0.58
Maximum resident set size 22892, Integral resident set size 0
Non-physical pagefaults 46012, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 31896, Involuntary context switches 0
4）執(zhí)行一次測試，分別500和1000個并發(fā)并巍，執(zhí)行5000次總查詢
[root@centos7 ~]# mysqlslap -uroot -p -a --concurrency=500,1000 --number-of-queries 5000 --debug-info
Enter password:
Benchmark
Average number of seconds to run all queries: 3.378 seconds
Minimum number of seconds to run all queries: 3.378 seconds
Maximum number of seconds to run all queries: 3.378 seconds
Number of clients running queries: 500
Average number of queries per client: 10
Benchmark
Average number of seconds to run all queries: 3.101 seconds
Minimum number of seconds to run all queries: 3.101 seconds
Maximum number of seconds to run all queries: 3.101 seconds
Number of clients running queries: 1000
Average number of queries per client: 5
User time 0.84, System time 0.64
Maximum resident set size 83068, Integral resident set size 0
Non-physical pagefaults 139977, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 31524, Involuntary context switches 3
5）迭代測試
[root@centos7 ~]# mysqlslap -uroot -p -a --concurrency=500 --number-of-queries 5000 --iterations=5 --debug-info
Enter password:
Benchmark
Average number of seconds to run all queries: 3.307 seconds
Minimum number of seconds to run all queries: 3.184 seconds
Maximum number of seconds to run all queries: 3.421 seconds
Number of clients running queries: 500
Average number of queries per client: 10
User time 2.18, System time 1.58
Maximum resident set size 74872, Integral resident set size 0
Non-physical pagefaults 327732, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 73904, Involuntary context switches 3
mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 --iterations=1 --create-schema='oldboy' \
--query="select * from oldboy.t1 where stuname='alexsb_100'" engine=innodb \
--number-of-queries=2000 -uroot -poldboy123 -verbose
=========================================================================
二目木、優(yōu)化細節(jié)：
1、參數(shù)優(yōu)化
1.1 Max_connections *****
（1）簡介
Mysql的最大連接數(shù)懊渡，如果服務器的并發(fā)請求量比較大刽射，可以調高這個值，當然這是要建立在機器能夠支撐的情況下剃执，因為如果連接數(shù)越來越多誓禁，mysql會為每個連接提供緩沖區(qū)，就會開銷的越多的內存肾档，所以需要適當?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
補充:
1.開啟數(shù)據(jù)庫時,我們可以臨時設置一個比較大的測試值
2.觀察show status like 'Max_used_connections';變化
3.如果max_used_connections跟max_connections相同,
那么就是max_connections設置過低或者超過服務器的負載上限了，
低于10%則設置過大.
IOPS
connections
TPS
QPS
1.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
1.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ù)彌補。
1.4 key_buffer_size *****
（1）簡介
key_buffer_size指定索引緩沖區(qū)的大小架曹，它決定索引處理的速度隘冲，尤其是索引讀的速度
《1》此參數(shù)與myisam表的索引有關
《2》臨時表的創(chuàng)建有關（多表鏈接、子查詢中绑雄、union）
在有以上查詢語句出現(xiàn)的時候展辞，需要創(chuàng)建臨時表，用完之后會被丟棄
臨時表有兩種創(chuàng)建方式：
內存中------->key_buffer_size
磁盤上------->ibdata1(5.6)
ibtmp1 (5.7）
（2）設置依據(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>
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個請求在內存中沒有找到直接從硬盤中讀取索引
控制在 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)
mysql>
通常地通殃，我們習慣以 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%以內
