前提環(huán)境：
- 創(chuàng)庫：slow_test
create database slow_test; - 選定庫：
use slow_test; - 創(chuàng)表comic：
create table comic(comic_id int primary key auto_increment,name char(20),pen_name char(20),cover char(40));
image.png
delimiter //
create procedure insert_many()
begin
declare i int;
set i =0;
while i<10 do
insert into comic (name,pen_name,cover) select name,pen_name,cover from comic;
set i=i+1;
end while;
end;
//
delimiter ;
一鸽凶、生成實驗數(shù)據(jù)
原理：sql 蠕蟲復(fù)制（這種生成數(shù)據(jù)方式同樣適用于數(shù)據(jù)表中有主鍵的情況）。
insert into comic (name,pen_name,cover) select name,pen_name,cover from comic
二原探、慢查詢?nèi)罩驹O(shè)置
當(dāng)語句執(zhí)行時間較長時耘纱，通過日志的方式進行記錄震贵，這種方式就是慢查詢的日志擎宝。
- 1、臨時開啟慢查詢?nèi)罩荆ㄈ绻枰L時間開啟涡上，則需要更改mysql配置文件趾断，第6點有介紹）
set global slow_query_log = on;
注：如果想關(guān)閉慢查詢?nèi)罩荆恍枰獔?zhí)行 set global slow_query_log = off; 即可
- 2吩愧、臨時設(shè)置慢查詢時間臨界點 查詢時間高于這個臨界點的都會被記錄到慢查詢?nèi)罩局校ㄈ绻枰L時間開啟芋酌，則需要更改mysql配置文件，第6點有介紹）雁佳。
set long_query_time = 1;
現(xiàn)在起脐帝，所有執(zhí)行時間超過1秒的sql都將被記錄到慢查詢文件中
（我這里就是 /var/lib/mysql/oyzx-slow.log）同云。
set long_query_time = 1;
show variables like 'slow_query%'; #查看慢日志文件所在目錄
- 3、設(shè)置慢查詢存儲的方式
set global log_output='FILE';
說明:可以看到,我這里設(shè)置為了file類型,就是說我的慢查詢?nèi)罩臼峭ㄟ^file體現(xiàn)的,默認是none,我們可以設(shè)置為table或者file,如果是table則慢查詢信息會保存到mysql庫下的slow_log表中set global log_output='TABLE';
- 4堵腹、查詢慢查詢?nèi)罩镜拈_啟狀態(tài)和慢查詢?nèi)罩緝Υ娴奈恢?/li>
show variables like '%quer%';
參數(shù)說明:
slow_query_log : 是否已經(jīng)開啟慢查詢
slow_query_log_file : 慢查詢?nèi)罩疚募窂?br> long_query_time : 超過多少秒的查詢就寫入日志
log_queries_not_using_indexes 如果值設(shè)置為ON炸站，則會記錄所有沒有利用索引的查詢(性能優(yōu)化時開啟此項,平時不要開啟)
- 5、使用慢查詢?nèi)罩臼纠?/li>
cat -n /var/lib/mysql/oyzx-slow.log
從慢查詢?nèi)罩局芯吻辏覀兛梢钥吹矫恳粭l查詢時間高于1s鐘的sql語句旱易，并可以看到執(zhí)行的時間是多少。
比如上面荡含，就表示 sql語句 select * from comic; 執(zhí)行時間為3.30秒咒唆，超出了我們設(shè)置的慢查詢時間臨界點1s，所以被記錄下來了释液。
- 6全释、永久設(shè)置慢查詢?nèi)罩鹃_啟，以及設(shè)置慢查詢?nèi)罩緯r間臨界點
linux中误债，mysql配置文件一般默認在/etc/my.cnf
更改對應(yīng)參數(shù)即可浸船。
image.png
systemctl restart mysqld重啟mysql服務(wù)
image.png
三、對慢查詢?nèi)罩具M行分析
我們通過查看慢查詢?nèi)罩究梢园l(fā)現(xiàn)寝蹈，很亂李命，數(shù)據(jù)量大的時候，可能一天會產(chǎn)生幾個G的日志箫老，根本沒有辦法去清晰明了的分析封字。所以，這里耍鬓，我們采用工具進行分析阔籽。
- 1、使用
mysqldumpslow進行分析【第一種方式】
mysqldumpslow -t 10 /var/lib/mysql/oyzx-slow.log #顯示出慢查詢?nèi)罩局凶盥?0條sql
注：mysqldumpslow工具還有其他參數(shù)牲蜀，以提供其他功能笆制，這里，只以最基本的-t做了介紹涣达。
- 2在辆、使用
pt-query-digest工具進行分析
mysqldumpslow是mysql安裝后就自帶的工具，用于分析慢查詢?nèi)罩径忍Γ莗t-query-digest卻不是mysql自帶的匆篓，如果想使用pt-query-digest進行慢查詢?nèi)罩镜姆治觯瑒t需要自己安裝pt-query-digest寇窑。pt-query-digest工具相較于mysqldumpslow功能多一點奕删。
（1）安裝及部署
yum install perl-DBI
yum install perl-DBD-MySQL
yum install perl-Time-HiRes
yum install perl-IO-Socket-SSL
yum -y install perl-Digest-MD5
wget percona.com/get/pt-query-digest
chmod u+x pt-query-digest
mv pt-query-digest /usr/bin/
（2）查看具體參數(shù)作用
pt-query-digest --help
（3）使用
pt-query-digest /var/lib/mysql/oyzx-slow.log
查詢出來的結(jié)果分為三部分
-
第一部分：
顯示出了日志的時間范圍，以及總的sql數(shù)量和不同的sql數(shù)量疗认。
image.png -
第二部分：
顯示出統(tǒng)計信息完残。
image.png
第三部分：
每一個sql具體的分析
pct是percent的簡寫伏钠，表示占的百分比
cout是占總sql個數(shù)的百分比，
exec time 是占總執(zhí)行時間的百分比谨设，
lock time表示占總的鎖表時間的百分比熟掂。
- （4）如何通過pt-query-digest 慢查詢?nèi)罩景l(fā)現(xiàn)有問題的sql
1）查詢次數(shù)多且每次查詢占用時間長的sql
通常為pt-query-digest分析的前幾個查詢
2）IO消耗大的sql
注意pt-query-digest分析中的Rows examine項
3）為命中索引的sql
注意pt-query-digest分析中Rows examine（掃描行數(shù)） 和 Rows sent （發(fā)送行數(shù)）的對比 ，如果掃描行數(shù)遠遠大于發(fā)送行數(shù)扎拣，則說明索引命中率并不高赴肚。
四、對sql進行優(yōu)化
- 1二蓝、使用explain查詢sql的執(zhí)行計劃
explain select comic_id,name,pen_name,cover,last_verify_time from comic;
參數(shù)分析：
table：表示屬于哪張數(shù)據(jù)表
type：最重要的參數(shù)誉券，表示連接使用了何種類型。從最好到最差的連接類型為const刊愚，eq_reg踊跟，ref，range鸥诽，index和ALL商玫。
possible_keys：顯示可能應(yīng)用在這張表中的索引。如果為null牡借，則表示沒有可能的索引拳昌。
key：實際使用的索引。如果為null钠龙，則表示沒有使用索引炬藤。
key_len：使用的索引的長度，在不損失精確性的情況下碴里，長度越短越好沈矿。
ref：表示索引的哪一列被使用了，如果可能的話并闲，是一個常數(shù)。
rows：Mysql認為必須檢查的用來返回請求數(shù)據(jù)的行數(shù)谷羞。
- 2帝火、count() 和 Max() 的優(yōu)化方法
（1）優(yōu)化前，是沒有為last_update_time字段建立索引的情況湃缎，查詢最大的時間戳
（2）優(yōu)化后犀填，是為last_update_time字段建立索引的情況，查詢最大的時間戳
create index update_time on comic(last_update_time);
對比嗓违，可以看到九巡，在沒有為字段建立索引的情況下，查詢時間是11秒多蹂季，建立索引之后冕广，查詢時間變成0秒了疏日。
所以總結(jié)就是，如果經(jīng)常用于count和max操作的字段撒汉，可以為其添加索引沟优。
還有，值得注意的地方是：count() 計算時睬辐，count（*）會將這一列中的null值但也算進去挠阁，而count（comic_id）則不會將null算進去。
- 3溯饵、子查詢的優(yōu)化
通常情況下侵俗，需要把子查詢優(yōu)化為join查詢，但在優(yōu)化時要注意關(guān)聯(lián)鍵是否有一對多的關(guān)系丰刊，如果有隘谣，是可能會出現(xiàn)重復(fù)數(shù)據(jù)的。所以如果存在一對多關(guān)系藻三，則應(yīng)該使用distinct進行限制洪橘。
例如：
select t.id from t where t.id in (select k.kid from k);
優(yōu)化成：
select distinct t.id from t join k on t.id = k.kid;
- 4、group by 的優(yōu)化
待補
5棵帽、limit的優(yōu)化
五熄求、對索引進行優(yōu)化
1、選擇合適的列建立索引
2逗概、索引優(yōu)化sql的方法
3弟晚、索引維護的方法
六、數(shù)據(jù)庫結(jié)構(gòu)優(yōu)化
1逾苫、選擇合適的數(shù)據(jù)類型
2卿城、數(shù)據(jù)庫表的范式化優(yōu)化
3、數(shù)據(jù)庫表的反范式優(yōu)化
4铅搓、數(shù)據(jù)庫表的垂直拆分
5瑟押、數(shù)據(jù)庫表的水平拆分
七、系統(tǒng)配置優(yōu)化
1星掰、數(shù)據(jù)庫系統(tǒng)配置優(yōu)化
2多望、Mysql配置文件優(yōu)化
3、第三方配置工具使用
八氢烘、服務(wù)器硬件優(yōu)化
