一、生成實驗數據

原理：sql 蠕蟲復制（這種生成數據方式同樣適用于數據表中有主鍵的情況）。

insert into comic (name,pen_name,cover) select name,pen_name,cover from comic

?

二、慢查詢日志設置

當語句執行時間較長時，通過日志的方式進行記錄，這種方式就是慢查詢的日志。

1、臨時開啟慢查詢日志（如果需要長時間開啟，則需要更改mysql配置文件，第6點有介紹）

set global slow_query_log = on;?

注：如果想關閉慢查詢日志，只需要執行 set global slow_query_log = off; 即可

?

2、臨時設置慢查詢時間臨界點??查詢時間高于這個臨界點的都會被記錄到慢查詢日志中（如果需要長時間開啟，則需要更改mysql配置文件，第6點有介紹）。

set long_query_time = 1;

現在起，所有執行時間超過1秒的sql都將被記錄到慢查詢文件中（我這里就是 /data/mysql/mysql-slow.log）。

?

3、設置慢查詢存儲的方式

set globle log_output = file;

說明: 可以看到,我這里設置為了file,就是說我的慢查詢日志是通過file體現的,默認是none,我們可以設置為table或者file,如果是table則慢查詢信息會保存到mysql庫下的slow_log表中

?

4、查詢慢查詢日志的開啟狀態和慢查詢日志儲存的位置

show variables like '%quer%';

參數說明:

slow_query_log : 是否已經開啟慢查詢

slow_query_log_file :?慢查詢日志文件路徑

long_query_time : ?超過多少秒的查詢就寫入日志?

log_queries_not_using_indexes 如果值設置為ON，則會記錄所有沒有利用索引的查詢(性能優化時開啟此項,平時不要開啟)

?

5、使用慢查詢日志示例

cat -n? /data/mysql/mysql-slow.log

從慢查詢日志中，我們可以看到每一條查詢時間高于1s鐘的sql語句，并可以看到執行的時間是多少。

比如上面，就表示 sql語句??select * from comic where comic_id < 1952000;? 執行時間為3.902864秒，超出了我們設置的慢查詢時間臨界點1s，所以被記錄下來了。

?

6、永久設置慢查詢日志開啟，以及設置慢查詢日志時間臨界點

linux中，mysql配置文件一般默認在 /etc/my.cnf

更改對應參數即可。

?

?

三、對慢查詢日志進行分析

我們通過查看慢查詢日志可以發現，很亂，數據量大的時候，可能一天會產生幾個G的日志，根本沒有辦法去清晰明了的分析。所以，這里，我們采用工具進行分析。

1、使用mysqldumpslow進行分析【第一種方式】

mysqldumpslow -t 10 ?/data/mysql/mysql-slow.log? #顯示出慢查詢日志中最慢的10條sql

注：mysqldumpslow工具還有其他參數，以提供其他功能，這里，只以最基本的-t做了介紹。

?

2、使用pt-query-digest工具進行分析

mysqldumpslow是mysql安裝后就自帶的工具，用于分析慢查詢日志，但是pt-query-digest卻不是mysql自帶的，如果想使用pt-query-digest進行慢查詢日志的分析，則需要自己安裝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

wget percona.com/get/pt-query-digest

chmod u+x pt-query-digest?

mv pt-query-digest ?/usr/bin/??

?

（2）查看具體參數作用

pt-query-digest --help

?

（3）使用

pt-query-digest ?/data/mysql/mysql-slow.log

查詢出來的結果分為三部分

?第一部分：

顯示出了日志的時間范圍，以及總的sql數量和不同的sql數量。

第二部分：

顯示出統計信息。

第三部分：

每一個sql具體的分析

pct是percent的簡寫，表示占的百分比

cout是占總sql個數的百分比，exec time 是占總執行時間的百分比，lock time 表示占總的鎖表時間的百分比。

?

（4）如何通過pt-query-digest 慢查詢日志發現有問題的sql

1）查詢次數多且每次查詢占用時間長的sql

通常為pt-query-digest分析的前幾個查詢

2）IO消耗大的sql

注意pt-query-digest分析中的Rows examine項

3）為命中索引的sql

注意pt-query-digest分析中Rows examine（掃描行數） 和?Rows sent （發送行數）的對比?，如果掃描行數遠遠大于發送行數，則說明索引命中率并不高。

?

四、對sql進行優化

1、使用explain查詢sql的執行計劃

explain select comic_id,name,pen_name,cover,last_verify_time from comic;

參數分析：

table：表示屬于哪張數據表

type：最重要的參數，表示連接使用了何種類型。從最好到最差的連接類型為const，eq_reg，ref，range，index和ALL。

possible_keys：顯示可能應用在這張表中的索引。如果為null，則表示沒有可能的索引。

key：實際使用的索引。如果為null，則表示沒有使用索引。

key_len：使用的索引的長度，在不損失精確性的情況下，長度越短越好。

ref：表示索引的哪一列被使用了，如果可能的話，是一個常數。

rows：Mysql認為必須檢查的用來返回請求數據的行數。?

?

2、count() 和 Max() 的優化方法

（1）優化前，是沒有為last_update_time字段建立索引的情況，查詢最大的時間戳

?

（2）優化后，是為last_update_time字段建立索引的情況，查詢最大的時間戳

create index update_time on comic(last_update_time);

對比，可以看到，在沒有為字段建立索引的情況下，查詢時間是11秒多，建立索引之后，查詢時間變成0秒了。

所以總結就是，如果經常用于count和max操作的字段，可以為其添加索引。

還有，值得注意的地方是：count() 計算時，count（*）會將這一列中的null值但也算進去，而count（comic_id）則不會將null算進去。

?

3、子查詢的優化

通常情況下，需要把子查詢優化為join查詢，但在優化時要注意關聯鍵是否有一對多的關系，如果有，是可能會出現重復數據的。所以如果存在一對多關系，則應該使用distinct進行限制。

例如：

select t.id?from t where t.id in (select k.kid from k);

優化成：

select distinct t.id from t join k on t.id = k.kid;

?

4、group by?的優化

#待補

5、limit的優化

?

五、對索引進行優化

1、選擇合適的列建立索引

2、索引優化sql的方法

3、索引維護的方法

?

六、數據庫結構優化

1、選擇合適的數據類型

2、數據庫表的范式化優化

3、數據庫表的反范式優化

4、數據庫表的垂直拆分

5、數據庫表的水平拆分

?

七、系統配置優化

1、數據庫系統配置優化

2、Mysql配置文件優化

3、第三方配置工具使用

?

八、服務器硬件優化

?

?

?

?

?