前言
我最近由于換工作，博客更新暫緩，后面爭取一周兩篇。
MySQL 系列到這里就差不多了，Mysql集群、分庫分表及分布式事務由于我還是停留在理論上，沒在生產環境上玩過，又怕寫不好，這部分內容我會在有底氣的一天補上來。
下一個系列，想寫 JAVA 相關的，java 虛擬機及問題定位，java 并發，java 源碼等等。
本文內容
- explain 查看執行計劃
- show profile 定位問題
- 硬件的選擇及mysql 使用內存估計
Mysql 單機扛不住的時候，考慮讀寫分離，主庫用于寫，從庫用于查。主要還是為了減小 insert/update/delete 鎖開銷降低了數據庫的并發。
當業務量級真的達到需要分庫分表的時候，數據庫上云吧。上云的花費對于業務盈利來說估計也就是九牛一毛了。
數據庫上云之后，運維也比較方便了。
Cpu/內存/硬盤選擇
內存
你如果給 Mysql 配置的內存較高，將其當成一個內存數據庫使用（索引數據和業務數據都在內存中），那么其性能一定不會差。
內存較大的服務器價格不菲，我們要選擇合適的內存大小。
一般我們傾向于將索引數據和一部分訪問頻率比較頻繁的熱數據放入到內存中就可以了，但是還是要預留出來一部分內存，防止發生 swap 降低性能。當下圖中的 swap 中 si 和 so 為 0 就行了。代表系統沒有發生 swap。當你內存較小的時候發生 swap 對性能影響是不小的。
vmstat -t 1 1000
再不發生 swap 的前提下，一般推薦將系統內存的 80% 的內存分配給 mysql 使用。
圖片來自 《MySQL數據庫頻繁出現OOM問題該如何化解》 https://www.huaweicloud.com/zhishi/19122601.html
// 計算 mysql 內存數值大小
https://www.mysqlcalculator.com/
1、innodb_buffer_pool_size
實際中主要關心的還是 innodb_buffer_pool_size （主要用于緩存業務數據和索引數據）配置，以下是一些參考設置。
典型值為5-6GB（8GB RAM），20-25GB（32GB RAM），100-120GB（128GB RAM）。
2、key_buffer_size 默認 8M
show global status like 'key_read%';
Key_read_requests: 0
Key_reads: 0
key_cache_miss_rate = Key_reads / Key_read_requests * 100%;
key_cache_miss_rate 在0.1%以下都很好(每1000個請求有一個直接讀硬盤)
3、max_connections 最大連接數默認是 151 。
一般我們都是使用線程池，這個值也不太需要調多大，當你 mysql 實例上有很多個數據庫供多個項目使用的時候需要調整這個值。
4、read_buffer_size 默認 128 KB
內存足夠大的時候，推薦設置為 1M，這樣讀取掃描表數據的時候會更快。但也不是越大越好。
read_rnd_buffer_size 默認 256 KB
sort_buffer_size 默認 256 KB
join_buffer_size 默認 256 KB
硬盤
數據庫的瓶頸主要還是磁盤 io 這一塊，SSD 性能相對來說會更好一些。
Mysql 數據的文件還是需要放到 SSD 上的。
當你定時備份數據庫數據的時候，可以將備份的數據壓縮發送到另一個存儲型服務器。
Cpu
當 cpu 總是 100% 的時候你就需要考慮增加 cpu 的核數了。
一般我們選擇 4 核 8g內存，8 核 16g，16 核 64g ，32 核 128 g。
查看數據庫中數據和索引大小
information_schema.TABLES 保存數據了數據表中的數據大小和索引大小。
SELECT
ts.TABLE_SCHEMA AS '數據庫',
CONCAT( ROUND( SUM( ts.DATA_LENGTH / 1024 / 1024 ), 2 ), 'MB' ) AS '總數據大小',
CONCAT( ROUND( SUM( ts.index_length / 1024 / 1024 ), 2 ), 'MB' ) AS '索引數據大小',
CONCAT( ROUND( SUM( ( ts.index_length + ts.DATA_LENGTH ) / 1024 / 1024 ), 2 ), 'MB' ) AS '索引數據大小'
FROM
information_schema.TABLES AS ts
WHERE
ts.TABLE_SCHEMA NOT IN ( 'mysql', 'information_schema', 'performance_schema' )
GROUP BY
ts.TABLE_SCHEMA;
內存的容量小于索引數據的時候，需要考慮增加內存容量。
定位慢 sql
1、druid 連接池也是可以打印慢 sql。一般執行時間長于 1s 的都要優化。
spring:
datasource:
druid:
filter:
stat:
enabled: true
# 執行時間小于 1 秒記錄為慢 sql
slow-sql-millis: 1000
log-slow-sql: true
db-type: mysql
merge-sql: true
logback 配置
<!-- druid sql 日志追蹤器 -->
<Appender name="druidSqlRollingFile" class="ch.qos.logback.core.rolling.RollingFileAppender">
<file>${logPath:-${defaultLogPath}}/druid/druid-sql.log</file>
<rollingPolicy class="ch.qos.logback.core.rolling.SizeAndTimeBasedRollingPolicy">
<fileNamePattern>${logPath:-${defaultLogPath}}/druid/druid-sql.%d{yyyy-MM-dd}.%i.log
</fileNamePattern>
<maxFileSize>${LOG_FILE_MAX_SIZE:-10MB}</maxFileSize>
<maxHistory>${LOG_FILE_MAX_HISTORY:-20}</maxHistory>
</rollingPolicy>
<encoder>
<pattern>${FILE_LOG_PATTERN}</pattern>
<charset>UTF-8</charset>
</encoder>
</appender>
<logger name="druid.sql.Statement" level="warn" additivity="false">
<appender-ref ref="CONSOLE"/>
<appender-ref ref="druidSqlRollingFile"/>
</logger>
2、mysql 的慢 sql 日志，從這個慢 sql 日志文件中分析出執行慢的 sql
默認是不開啟慢 sql 日志記錄的
-- 查看開啟慢 sql
show variables like 'slow_query_log%';
-- 查看執行時間大于多少為慢 sql
show variables like 'long_query_time%';
開啟慢 sql 日志記錄，這是動態修改，沒有持久化，數據庫重啟就失效了。
-- 這個值單位是秒，不要設置的太小。不然打印日志太多，我們要先優化哪些執行時間較長的 sql 比如大于 5 秒，大于 10 秒
-- 一步一步優化
set global long_query_time=3;
-- 開啟慢 sql 記錄
set global slow_query_log=1;
我們可以使用 select sleep(5); 來產生慢 sql 。
# Time: 2021-03-07T07:24:10.757715Z
# User@Host: root[root] @ localhost [] Id: 17
# Query_time: 5.070525 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1615101845;
select sleep(5);
也可以在 mysql 配置文件中修改,這樣數據庫重啟也是開啟的。
[mysqld]
slow_query_log=1
slow_query_log_file=/var/lib/mysql/slow-log.log
long_query_time=3
按 -s 指定按查詢時間排序，-t 指定返回多少條記錄數，也可以 -g 篩選，類似于 grep 操作。
mysqldumpslow -s t -t 10 /usr/local/var/mysql/wanguyunxiao-slow.log | more
mysqldumpslow -s t -t 10 -g "left join" /usr/local/var/mysql/wanguyunxiao-slow.log | more
慢 sql 產生的原因
1、可能沒有用到索引，建立合適的索引
2、有的時候索引也建立了，但是你聯合查詢，關聯 n 多個表查詢速度可能慢。阿里規范推薦最多關聯 3 個表，這個時候我們就需要簡化 sql 了，用多個 sql 完成你的業務邏輯，而不是一條 sql 查詢出你需要的數據。
3、sql 一定要寫規范，索引的使用要符合最左匹配原則，這和索引的數據結構有關
4、隱式數據類型轉換，條件做函數計算等等，這些都要避免
5、還有一種比較特殊，有索引，但是沒有做索引，這個時候可以強制走索引，你也要去優化你的索引統計數據。或者優化你的表空間文件了
-- 實際就是更新索引的統計數據，讓索引更有效利用，一般在空閑的時候做。
ANALYZE TABLE table_name;
6、表空間文件優化。當我們真刪除數據過多，但是數據庫的表空間文件可能并沒有縮小，這時候我們需要在業務不忙的時候去優化表空間文件。
-- 會鎖表，優化了表空間文件及索引相關的數據。定期執行命令即可。
OPTIMIZE TABLE tbl_name [, tbl_name] ...
Explain 查看執行計劃
o 表為組織機構表，字段 id,name
oc 記錄的是某個某個組織機構下某個倉庫的庫存數量，oid,cid,oc_num
EXPLAIN SELECT o.`name`,t.`庫存總量` FROM (SELECT oc.oid,sum(oc.oc_num) AS '庫存總量' FROM oc GROUP BY oc.oid HAVING SUM(oc.oc_num)>5000 ) AS t INNER JOIN o ON t.oid=o.id;
id
sql 執行的順序標識，序號越大越先執行，相同序號，自上而下執行。
partitions
當前查詢所用的分區，一般分區表會使用。
type，重要關注
訪問類型。性能這塊
system > const > eq_ref > ref > range > index > ALL
- ALL
ALL 標識全表掃描，我們要避免全表掃描。
- index
掃描全部索引數據。
- range
掃描一部分索引數據。使用索引進行范圍查詢。一般是 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中
SELECT * FROM tbl_name
WHERE key_column = 10;
SELECT * FROM tbl_name
WHERE key_column BETWEEN 10 and 20;
SELECT * FROM tbl_name
WHERE key_column IN (10,20,30);
SELECT * FROM tbl_name
WHERE key_part1 = 10 AND key_part2 IN (10,20,30);
- ref
查詢的時候，條件是普通索引等值查詢
SELECT * FROM ref_table WHERE key_column=expr;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;
- eq_ref
關聯查詢的時候，關聯的條件使用的是主鍵或者唯一索引
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;
- const
使用主鍵或唯一索引等值查詢。
SELECT * FROM index_test WHERE id =1
- system
表只有一行數據，一般是系統表。
possible_keys
當前查詢中可能用到的索引。
key
當前查詢用到的真實索引。當可能走索引插敘，但實際沒有用到索引查詢，你可能需要去分析表，更新索引統計數據，讓索引更有效利用。
-- 實際就是更新索引的統計數據，讓索引更有效利用，一般在空閑的時候做。
ANALYZE TABLE table_name;
key_len
不損失精確性的前提下，越小越好。
ref
哪個字段或常數與 key 一起被使用
rows，重點關注
顯示此查詢一共掃描了多少行，這個是一個估計值。此值越少性能越好。
filtered
表示此查詢條件所過濾的數據的百分比
extra，需要關注
extra 包含 Using filesort 和 Using temporary 考慮優化。
- Using where
列數據是從僅僅使用了索引中的信息而沒有讀取實際的行動的表返回的，這發生在對表的全部的請求列都是同一個索引的部分的時候，表示mysql服務器將在存儲引擎檢索行后再進行過濾
- Useing index
覆蓋索引掃描，只掃描了索引數據就拿到了結果。往往性能不錯。
- Using temporary
表示 MySQL 需要使用臨時表來存儲結果集，常見于排序和分組查詢或者多表查詢，需要考慮優化
- Using filesort
MySQL中無法利用索引完成的排序操作稱為“文件排序”，必須優化
show profile
show profile 可以看到 sql執行在哪塊比較耗時，cpu/內存/io 等等
-- 查看 profile 是否開啟,默認是關閉的。
show variables like '%profil%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES |
| profiling | OFF |
| profiling_history_size | 15 |
+------------------------+-------+
-- 開啟 profile
set profiling=1;
-- 查看已經執行的 sql
SHOW PROFILES;
-- show profile cpu, block io, memory,swaps,context switches,source for query [Query_ID];
-- 查看具體某個執行 sql
show profile cpu, block io, memory,swaps,context switches,source for query [Query_ID];
-- 先執行 SHOW PROFILES;拿到 query_id 在執行下面的 sql
show profile cpu, block io, memory,swaps,context switches,source for query 173;
SHOW PROCESSLIST
查看數據庫線程中的狀況。
SHOW PROCESSLIST;
SELECT * FROM information_schema.`PROCESSLIST`;
結合 top/vmstat/IOStat 可以定位mysql 中 cpu,io,內存相關問題。
# 查看 mysqld 的進程
ps -ef | grep mysqld | grep -v grep
# 查看 mysqld 中的線程
top -Hp 5762
# 或者一步到位，查看 mysqld 下的線程
top -Hp `ps -ef | grep mysqld | grep -v grep | awk '{print $2}'`
