1. Mysql優(yōu)化介紹

1.1 sql優(yōu)化

a. sql優(yōu)化分析
b. 索引優(yōu)化
c. 常用sql優(yōu)化
d. 常用優(yōu)化技巧

1.2 優(yōu)化數(shù)據(jù)庫(kù)對(duì)象

a. 優(yōu)化表的數(shù)據(jù)類型
b. 表拆分
c. 逆規(guī)范式（不遵循三范式）
d. 使用中間表（一般用于大數(shù)據(jù)或者統(tǒng)計(jì)分析時(shí)）

1.3 優(yōu)化Mysql Server

a. Mysql 內(nèi)在管理優(yōu)化
b. log機(jī)制及優(yōu)化
c. 調(diào)整Mysql并發(fā)相關(guān)參數(shù)

1.4 應(yīng)用優(yōu)化

a. 數(shù)據(jù)庫(kù)連接池
b. 使用緩存減少壓力
c. 負(fù)載均衡建立集群
d. 主主同步 中從復(fù)制（讀寫分離）

2 Mysql優(yōu)化問(wèn)題分析定位

2.1分析SQL執(zhí)行頻率

mysql命令行：show status "Com_select";
查看Com_select,Com_select,Com_update,Com_delete,Innodb_rows-read,Innodb_rows_inserted,Innodb_rows_updated,Innodb_deleted狀態(tài)
通過(guò)查看狀態(tài)檢測(cè)出該服務(wù)器是以讀寫哪個(gè)為主，就可以從不同方面
進(jìn)行優(yōu)化

2.2 定位執(zhí)行效率低SQL

2.2.1 慢查詢?nèi)罩径ㄎ?查詢結(jié)束記錄：

慢查詢?nèi)罩疚募窂?/p>

og-slow-queries

超過(guò)多少秒的查詢就寫入日志

long_query_time

打開my.cnf配置文件掠河，加入以下代碼：

        log-slow-queries = /tmp/mysql-slow.log  //log文件路徑
        long_query_time = 2 //代表語(yǔ)句超過(guò)兩秒的就記錄

保存退出唠摹，重啟MySQL即可勾拉。
[root@lizhong tmp]# tail -f /tmp/mysql_slow.log
Time: 120815 23:22:11
User@Host: root[root] @ localhost []
Query_time: 9.869362 Lock_time: 0.000035 Rows_sent: 1 Rows_examined: 6261774
SET timestamp=1294388531;
select count(*) from blog;

第一行：執(zhí)行時(shí)間

第二行：執(zhí)行用戶

第三行（重要）：

Query_time SQL執(zhí)行的時(shí)間,越長(zhǎng)則越慢

Lock_time 在MySQL服務(wù)器階段(不是在存儲(chǔ)引擎階段)等待表鎖時(shí)間

Rows_sent 查詢返回的行數(shù)

Rows_examined 查詢檢查的行數(shù)

2.2.2 show processlist

查看當(dāng)前正在進(jìn)行的線程藕赞，包括線程狀態(tài)，是否鎖表（status為L(zhǎng)ocked ）更多狀態(tài)解析

3 索引優(yōu)化

3.1 索引存儲(chǔ)分類

B-TREE索引：最常見的索引類型双霍，大部分都支持
HASH索引：只有Memory引擎支持店煞，使用場(chǎng)景簡(jiǎn)單
R-TREE索引：空間索引是MykSAM的一個(gè)特殊索引類型，主要用于地理空間數(shù)據(jù)類型
Full-text索引：全文索引酒请，MylSAM的一個(gè)特殊索引羞反，Innodb從5.6開始支持

3.2 索引的創(chuàng)建與刪除

1. 添加PRIMARY KEY(主鍵索引)
   mysql>alter table 'table_name' add primary key ('columm');
2. 添加UNIQUE(唯一索引)
   mysql>alter table 'table_name' add unique('column');
3. 添加index(普通索引)
   mysql>alter table 'table_name' add index index_name ('column');
4. 添加FULLTEXT(全文索引)
   mysql>alter table 'table_name' add fulltext ('column');
5. 添加多列索引

mysql>alter table 'table_name' add index index_name ('column1'昼窗，'column2','column3');

3.3 索引查看

1. show index from table_name;
2. show keys from table_name;

3.4 Mysql中使用索引的情況

1. 匹配全值（select name,age from test where age=15）
2. 匹配值范圍查詢（select name,age from test where age>15）
3. 匹配最左前綴
4. 僅僅對(duì)索引進(jìn)行查詢（字段而不用*）
5. 匹配列前綴(select name from test where name like "d%"
   )
6. 部分精確+部分范圍(select name,age from test where age>15 and id=1)

3.5 不能使用索引的情況

1. 以‘%’開頭的like查詢
2. 數(shù)據(jù)類型出現(xiàn)隱式轉(zhuǎn)換（int型數(shù)據(jù)卻加上引號(hào)轉(zhuǎn)成字符串）
3. 復(fù)合索引查詢條件不包含最左側(cè)部分
4. 即使使用索引但比全表掃面還慢（數(shù)據(jù)中都是已A開頭澄惊，卻selec>t name from test like 'A%';）
5. 用or分割開的條件
6. 高秒殺的時(shí)候

3.5 查詢索引的使用情況

show status like 'Handler_reader%';
其中Handler_read_rnd_next越大掸驱，說(shuō)明查詢中大部分都沒(méi)使用索引

4 常用sql語(yǔ)句優(yōu)化

4.1 定期優(yōu)化表

使用Optimize table table_name（不宜經(jīng)常使用没佑，看操作次數(shù)蛤奢，一周左右就可以）來(lái)合并表空間碎片（對(duì)MYISAM,DBD,INNODB有效）
默認(rèn)情況下直接對(duì)innodb引擎的數(shù)據(jù)表使用，可能會(huì)顯示[Table does not support optimize,doing recreate + annalyze install]的提示信息待秃，這時(shí)候我們需要用mysql --skip-new或者mysql --safe-mode命令來(lái)重啟MySQL,以便于其他引擎支持OPTMIZE TABLE

4.2 常用優(yōu)化

1. 對(duì)查詢進(jìn)行優(yōu)化锥余，應(yīng)盡量避免全表掃描，首先應(yīng)考慮在where及order by涉及的列上簡(jiǎn)歷索引
2. 應(yīng)該盡量避免在where子句中使用!=,<>不等于操作符，否則將引擎放棄使用索引而進(jìn)行掃面足画。
3. 盡量在where語(yǔ)句中別使用or連接佃牛。
4. 亂用%導(dǎo)致全表掃描，如果想提高效率象缀，可以換成全文索引
5. 應(yīng)盡量避免在where字句中對(duì)字段進(jìn)行表達(dá)式操作，這將導(dǎo)致引擎將放棄使用索引而進(jìn)行全盤掃描
6. 應(yīng)盡量避免在where子句中使用對(duì)字段的函數(shù)操作央星，這將導(dǎo)致引擎放棄索引莉给。

5. 優(yōu)化數(shù)據(jù)庫(kù)對(duì)象

5.1 優(yōu)化數(shù)據(jù)類型

使用 PROCEDURE ANALYSE()對(duì)當(dāng)前應(yīng)用的表進(jìn)行分析，它會(huì)給出優(yōu)化建議徐矩，用戶可根據(jù)實(shí)際情況考慮是否優(yōu)化,輸入如下命令之后叁幢，看最后一列，不一定正確鳞骤，可參考弟孟。
PROCEDURE ANALYSE(16,256)排出多余16個(gè)，大于256字節(jié)的ENUM建議拂募。
例：select * from user procedure analyse();

5.2 表拆分

1. 垂直拆分：字段比較多陈症，而針對(duì)某些不常用字段
2. 水平拆分：a震糖。表很大；b论咏。表中的數(shù)據(jù)本來(lái)就有獨(dú)立性颁井，能簡(jiǎn)單分類;c.需要把表存放在多中介質(zhì)

5.3 逆規(guī)范式

逆規(guī)范式可以降低鏈接操作雅宾，加快查詢速度，但會(huì)降低修改速度贯吓，影響數(shù)據(jù)完整性。所以在考慮使用逆規(guī)范式時(shí)一定要權(quán)衡利弊
常用的逆規(guī)范式有：增加冗余列介评，增加派生列威沫，重新組表和分割表洼专、

5.4 使用中間表

1. 數(shù)據(jù)查詢量大
2. 數(shù)據(jù)統(tǒng)計(jì)，數(shù)據(jù)分析
   像查找財(cái)務(wù)報(bào)表烟很，需要很多join蜡镶，這時(shí)只需要建一兩個(gè)中間表官还，先整合，在用一到兩個(gè)join完成

6. Mysql引擎比較

6.1 存儲(chǔ)引擎

10.png