1 mysql連線數
一、取得mysql最大連線數
show status like ‘Max_used_connections’
二、取得mysql目前開啟的連線數
show status like ‘Threads_connected’
2 mysql 暫存
show status like ‘Innodb_buffer_pool_reads’
show status like ‘Innodb_buffer_pool_read_requests’
show status like ‘Innodb_buffer_pool_pages_total’
show status like ‘Innodb_buffer_pool_pages_free’
（1-Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests）*100%
((Innodb_buffer_pool_pages_total-Innodb_buffer_pool_pages_free）/Innodb_buffer_pool_pages_total）*100%
3 鎖
show status like ‘Innodb_row_lock_waits’
show status like ‘Innodb_row_lock_time_avg’
show open TABLES where in_use>0；
4 sql語法
一、慢查詢mysql 語法開關是否打開
show variables like ‘slow_query_log’；
二、慢查詢mysql語法打開
set global slow_query_log=1
三、設定慢sql的時間(秒)
set long_query_time=1
三、慢sql路徑
show variables like ‘slow_query_log_file’；
四、透過慢sql分析工具格式化記錄
mysqldumpslow -s at -t 10 /export/data/mysql/log/slow.log
四.1、取出使用最多的10條慢sql
./mysqldumpslow -s c -t 10 /export/data/mysql/log/slow.log
四.2、取出查詢時間最慢的3條sql
./mysqldumpslow -s t -t 3 /export/data/mysql/log/slow.log
幾個會導致sql變慢的原因
1 子查詢
SELECT FROM t1 WHERE id (SELECT id FROM t2 WHERE name=’hechunyang’);
改用 join ，但update/delete沒作用
2 避免用函數
SELECT FROM t WHERE YEAR(d) >= 2016;
改用 SELECT FROM t WHERE d >= ‘2016-01-01’;
3 用in 取代 or
SELECT FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;
改用 SELECT FROM t WHERE LOC_IN IN (10,20,30);
4 雙%號無法用到索引
SELECT FROM t WHERE name LIKE ‘%de%’;
改為 SELECT FROM t WHERE name LIKE ‘de%’;
5 分組統計可以禁止排序
SELECT goods_id,count() FROM t GROUP BY goods_id;
改為 SELECT goods_id,count () FROM t GROUP BY goods_id ORDER BY NULL;
6不要用無意義的order by
SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id WHERE 1 = 1 ORDER BY u.create_time DESC;
改為
SELECT count (1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id;
