五 正確使用索引
一 索引未命中
并不是說我們創(chuàng)建了索引就一定會加快查詢速度等缀，若想利用索引達(dá)到預(yù)想的提高查詢速度的效果莱预，我們在添加索引時，必須遵循以下問題
1 范圍問題项滑，或者說條件不明確依沮，條件中出現(xiàn)這些符號或關(guān)鍵字：>、>=枪狂、<危喉、<=、!= 州疾、between...and...辜限、like、
大于號严蓖、小于號
Paste_Image.png
不等于薄嫡！=
Paste_Image.png
between ...and...
Paste_Image.png
like
Paste_Image.png
Paste_Image.png
2 盡量選擇區(qū)分度高的列作為索引,區(qū)分度的公式是count(distinct col)/count(*)氧急，表示字段不重復(fù)的比例，比例越大我們掃描的記錄數(shù)越少毫深，唯一鍵的區(qū)分度是1吩坝，而一些狀態(tài)、性別字段可能在大數(shù)據(jù)面前區(qū)分度就是0哑蔫，那可能有人會問钉寝，這個比例有什么經(jīng)驗值嗎？使用場景不同闸迷，這個值也很難確定嵌纲，一般需要join的字段我們都要求是0.1以上，即平均1條掃描10條記錄
#先把表中的索引都刪除腥沽，讓我們專心研究區(qū)分度的問題
mysql> desc s1;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | MUL | NULL | |
| name | varchar(20) | YES | | NULL | |
| gender | char(5) | YES | | NULL | |
| email | varchar(50) | YES | MUL | NULL | |
+--------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)
mysql> drop index a on s1;
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> drop index d on s1;
Query OK, 0 rows affected (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc s1;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| gender | char(5) | YES | | NULL | |
| email | varchar(50) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)
#先把表中的索引都刪除逮走，讓我們專心研究區(qū)分度的問題
Paste_Image.png
我們編寫存儲過程為表s1批量添加記錄，name字段的值均為egon今阳，也就是說name這個字段的區(qū)分度很低（gender字段也是一樣的师溅，我們稍后再搭理它）
回憶b+樹的結(jié)構(gòu)，查詢的速度與樹的高度成反比酣栈，要想將樹的高低控制的很低，需要保證：在某一層內(nèi)數(shù)據(jù)項均是按照從左到右汹押，從小到大的順序依次排開矿筝，即左1<左2<左3<...
而對于區(qū)分度低的字段，無法找到大小關(guān)系棚贾，因為值都是相等的窖维，毫無疑問，還想要用b+樹存放這些等值的數(shù)據(jù)妙痹，只能增加樹的高度铸史，字段的區(qū)分度越低，則樹的高度越高怯伊。極端的情況琳轿，索引字段的值都一樣，那么b+樹幾乎成了一根棍耿芹。本例中就是這種極端的情況崭篡，name字段所有的值均為'egon'
#現(xiàn)在我們得出一個結(jié)論：為區(qū)分度低的字段建立索引，索引樹的高度會很高吧秕，然而這具體會帶來什么影響呢琉闪？？砸彬？
#1：如果條件是name='xxxx',那么肯定是可以第一時間判斷出'xxxx'是不在索引樹中的（因為樹中所有的值均為'egon’）颠毙，所以查詢速度很快
#2：如果條件正好是name='egon',查詢時斯入，我們永遠(yuǎn)無法從樹的某個位置得到一個明確的范圍，只能往下找蛀蜜，往下找刻两，往下找。涵防。闹伪。這與全表掃描的IO次數(shù)沒有多大區(qū)別，所以速度很慢
分析原因
3 =和in可以亂序壮池，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序偏瓤，mysql的查詢優(yōu)化器會幫你優(yōu)化成索引可以識別的形式
4 索引列不能參與計算，保持列“干凈”椰憋，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引厅克，原因很簡單，b+樹中存的都是數(shù)據(jù)表中的字段值橙依，但進(jìn)行檢索時证舟，需要把所有元素都應(yīng)用函數(shù)才能比較，顯然成本太大窗骑。所以語句應(yīng)該寫成create_time = unix_timestamp(’2014-05-29’)
Paste_Image.png
5 and
條件1 and 條件2:在條件1不成立的情況下女责，不會再去判斷條件2，此時若條件1的字段有索引创译，而條件2沒有抵知，那么查詢速度依然很快
Paste_Image.png
在左邊條件成立但是索引字段的區(qū)分度低的情況下（name與gender均屬于這種情況），會依次往右找到一個區(qū)分度高的索引字段软族，加速查詢
Paste_Image.png
Paste_Image.png
經(jīng)過分析刷喜，在條件為name='egon' and gender='male' and id>333 and email='xxx'的情況下，我們完全沒必要為前三個條件的字段加索引立砸，因為只能用上email字段的索引掖疮，前三個字段的索引反而會降低我們的查詢效率
Paste_Image.png
6 最左前綴匹配原則，非常重要的原則颗祝，對于組合索引mysql會一直向右匹配直到遇到范圍查詢(>浊闪、<、between螺戳、like)就停止匹配规揪，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的温峭，如果建立(a,b,d,c)的索引則都可以用到猛铅，a,b,d的順序可以任意調(diào)整。
Paste_Image.png
7 其他情況
- 使用函數(shù)
select * from tb1 where reverse(email) = 'wupeiqi';
- or
select * from tb1 where nid = 1 or name = 'seven@live.com';
特別的：當(dāng)or條件中有未建立索引的列才失效凤藏，以下會走索引
select * from tb1 where nid = 1 or name = 'seven';
select * from tb1 where nid = 1 or name = 'seven@live.com' and email = 'alex'
- 類型不一致
如果列是字符串類型奸忽，傳入條件是必須用引號引起來堕伪，不然...
select * from tb1 where email = 999;
普通索引的不等于不會走索引
- !=
select * from tb1 where email != 'alex'
特別的：如果是主鍵，則還是會走索引
select * from tb1 where nid != 123
- >
select * from tb1 where email > 'alex'
特別的：如果是主鍵或索引是整數(shù)類型栗菜，則還是會走索引
select * from tb1 where nid > 123
select * from tb1 where num > 123
#排序條件為索引欠雌，則select字段必須也是索引字段，否則無法命中
- order by
select name from s1 order by email desc;
當(dāng)根據(jù)索引排序時候疙筹，select查詢的字段如果不是索引富俄，則不走索引
select email from s1 order by email desc;
特別的：如果對主鍵排序，則還是走索引：
select * from tb1 order by nid desc;
- 組合索引最左前綴
如果組合索引為：(name,email)
name and email -- 使用索引
name -- 使用索引
email -- 不使用索引
- count(1)或count(列)代替count(*)在mysql中沒有差別了
- create index xxxx on tb(title(19)) #text類型而咆，必須制定長度
其他注意事項
- 避免使用select *
- count(1)或count(列) 代替 count(*)
- 創(chuàng)建表時盡量時 char 代替 varchar
- 表的字段順序固定長度的字段優(yōu)先
- 組合索引代替多個單列索引（經(jīng)常使用多個條件查詢時）
- 盡量使用短索引
- 使用連接（JOIN）來代替子查詢(Sub-Queries)
- 連表時注意條件類型需一致
- 索引散列值（重復(fù)少）不適合建索引霍比，例：性別不適合
三 覆蓋索引與索引合并
#覆蓋索引：
- 在索引文件中直接獲取數(shù)據(jù)
http://blog.itpub.net/22664653/viewspace-774667/
#分析
select * from s1 where id=123;
該sql命中了索引，但未覆蓋索引暴备。
利用id=123到索引的數(shù)據(jù)結(jié)構(gòu)中定位到該id在硬盤中的位置悠瞬，或者說再數(shù)據(jù)表中的位置。
但是我們select的字段為*涯捻，除了id以外還需要其他字段浅妆，這就意味著，我們通過索引結(jié)構(gòu)取到id還不夠障癌，還需要利用該id再去找到該id所在行的其他字段值凌外，這是需要時間的，很明顯涛浙，如果我們只select id康辑，就減去了這份苦惱，如下
select id from s1 where id=123;
這條就是覆蓋索引了蝗拿，命中索引晾捏，且從索引的數(shù)據(jù)結(jié)構(gòu)直接就取到了id在硬盤的地址蒿涎，速度很快
#索引合并：把多個單列索引合并使用
#分析：
組合索引能做到的事情哀托，我們都可以用索引合并去解決，比如
create index ne on s1(name,email);#組合索引
我們完全可以單獨為name和email創(chuàng)建索引
組合索引可以命中：
select * from s1 where name='egon' ;
select * from s1 where name='egon' and email='adf';
索引合并可以命中：
select * from s1 where name='egon' ;
select * from s1 where email='adf';
select * from s1 where name='egon' and email='adf';
乍一看好像索引合并更好了：可以命中更多的情況劳秋，但其實要分情況去看仓手，如果是name='egon' and email='adf',那么組合索引的效率要高于索引合并，如果是單條件查玻淑，那么還是用索引合并比較合理
六 查詢優(yōu)化神器-explain
關(guān)于explain命令相信大家并不陌生嗽冒，具體用法和字段含義可以參考官網(wǎng)explain-output，這里需要強(qiáng)調(diào)rows是核心指標(biāo)补履，絕大部分rows小的語句執(zhí)行一定很快（有例外添坊，下面會講到）。所以優(yōu)化語句基本上都是在優(yōu)化rows箫锤。
執(zhí)行計劃：讓mysql預(yù)估執(zhí)行操作(一般正確)
all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
id,email
慢：
select * from userinfo3 where name='alex'
explain select * from userinfo3 where name='alex'
type: ALL(全表掃描)
select * from userinfo3 limit 1;
快：
select * from userinfo3 where email='alex'
type: const(走索引)
七 慢查詢優(yōu)化的基本步驟
0.先運行看看是否真的很慢贬蛙，注意設(shè)置SQL_NO_CACHE
1.where條件單表查雨女，鎖定最小返回記錄表。這句話的意思是把查詢語句的where都應(yīng)用到表中返回的記錄數(shù)最小的表開始查起阳准，單表每個字段分別查詢氛堕，看哪個字段的區(qū)分度最高
2.explain查看執(zhí)行計劃，是否與1預(yù)期一致（從鎖定記錄較少的表開始查詢）
3.order by limit 形式的sql語句讓排序的表優(yōu)先查
4.了解業(yè)務(wù)方使用場景
5.加索引時參照建索引的幾大原則
6.觀察結(jié)果野蝇，不符合預(yù)期繼續(xù)從0分析
八 慢日志管理
慢日志
- 執(zhí)行時間 > 10
- 未命中索引
- 日志文件路徑
配置：
- 內(nèi)存
show variables like '%query%';
show variables like '%queries%';
set global 變量名 = 值
- 配置文件
mysqld --defaults-file='E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\my-default.ini'
my.conf內(nèi)容：
slow_query_log = ON
slow_query_log_file = D:/....
注意：修改配置文件之后讼稚，需要重啟服務(wù)
MySQL日志管理
========================================================
錯誤日志: 記錄 MySQL 服務(wù)器啟動、關(guān)閉及運行錯誤等信息
二進(jìn)制日志: 又稱binlog日志绕沈，以二進(jìn)制文件的方式記錄數(shù)據(jù)庫中除 SELECT 以外的操作
查詢?nèi)罩? 記錄查詢的信息
慢查詢?nèi)罩? 記錄執(zhí)行時間超過指定時間的操作
中繼日志： 備庫將主庫的二進(jìn)制日志復(fù)制到自己的中繼日志中锐想，從而在本地進(jìn)行重放
通用日志： 審計哪個賬號、在哪個時段七冲、做了哪些事件
事務(wù)日志或稱redo日志： 記錄Innodb事務(wù)相關(guān)的如事務(wù)執(zhí)行時間痛倚、檢查點等
========================================================
一、bin-log
1. 啟用
# vim /etc/my.cnf
[mysqld]
log-bin[=dir\[filename]]
# service mysqld restart
2. 暫停
//僅當(dāng)前會話
SET SQL_LOG_BIN=0;
SET SQL_LOG_BIN=1;
3. 查看
查看全部：
# mysqlbinlog mysql.000002
按時間：
# mysqlbinlog mysql.000002 --start-datetime="2012-12-05 10:02:56"
# mysqlbinlog mysql.000002 --stop-datetime="2012-12-05 11:02:54"
# mysqlbinlog mysql.000002 --start-datetime="2012-12-05 10:02:56" --stop-datetime="2012-12-05 11:02:54"
按字節(jié)數(shù)：
# mysqlbinlog mysql.000002 --start-position=260
# mysqlbinlog mysql.000002 --stop-position=260
# mysqlbinlog mysql.000002 --start-position=260 --stop-position=930
4. 截斷bin-log（產(chǎn)生新的bin-log文件）
a. 重啟mysql服務(wù)器
b. # mysql -uroot -p123 -e 'flush logs'
5. 刪除bin-log文件
# mysql -uroot -p123 -e 'reset master'
二澜躺、查詢?nèi)罩?啟用通用查詢?nèi)罩?# vim /etc/my.cnf
[mysqld]
log[=dir\[filename]]
# service mysqld restart
三蝉稳、慢查詢?nèi)罩?啟用慢查詢?nèi)罩?# vim /etc/my.cnf
[mysqld]
log-slow-queries[=dir\[filename]]
long_query_time=n
# service mysqld restart
MySQL 5.6:
slow-query-log=1
slow-query-log-file=slow.log
long_query_time=3
查看慢查詢?nèi)罩?測試:BENCHMARK(count,expr)
SELECT BENCHMARK(50000000,2*3);
日志管理
