一.表結構優(yōu)化
二.列類型優(yōu)化
三.索引優(yōu)化策略
四.聚簇索引和非聚簇索引
五.理想的索引
怎么查看一個sql語句的執(zhí)行效果
在sql語句前面加上 “explain”夺英，如果加上"\\\\G",則能看到豎列效果 explain sql \\\\G
結果中的 key_len 就是在查詢上所用到的索引的個數痛悯； using filesort就是用到了排序的意思;using index是用到了索引覆蓋
排序非常耗資源载萌，想分組巡扇，必須排序
測試運行需要的時間 time file.php
如果A\\\\B兩張表結構是一樣的，把A表里面的數據插入B表乖坠，
可以這樣執(zhí)行：insert into B select * from A;
如果要插入某個字段的數據
insert into B select id,name,age from A;
在數據庫終端指令熊泵， 如果要查看前幾個 sql語句運行時間，show profiles;這個時候只能看到小數點后兩位顽分，
如果要看到精確的運行時間怯邪。set profiling = on;
如果某個表查詢的時候出現了亂碼花墩，在終端 set names utf8;
delimiter $ //在終端輸入$才會結束一條語句
優(yōu)化總則
1.少查(能不查就不查)
2.查少(少查點數據)
3.快點查(走索引)
4.少排序
一.表結構優(yōu)化
1.定長和變長分離
如id int,占4個字節(jié)冰蘑，char(4)占4個字段長度，也是定長祠肥，time 即每一個單元值占的字節(jié)是固定的
而varchar,text,blob,這種變長字段，適合單放一張表东羹，用主鍵與核心表關聯(lián)起來
2.常用字段和不常用字段分表忠烛，通過uuid將兩張表關聯(lián)。這個需要結合網站的具體業(yè)務來分析冤议，分析字段的查詢場景恕酸，查詢頻率低的字段蕊温，單拆出來(用空間來換取時間)
比如兩張關聯(lián)表，一個是板塊表(cat),另一個是板塊下回復的帖子义矛。查詢某個板塊下有多少個回復
方法一：先查出有幾個板塊症革，再在for循環(huán)里面找每個板塊下有多少回復
select * from cat; //比如10個板塊
for($i=1; $i<=10; $i++){
select count(*) from post where cat_id = $i;
}
方法二鸯旁，使用左右連接查詢量蕊，比較消耗資源残炮，不推薦
select cat.* from cat left join post on cat.cat_id = post.cat_id group by cat_id;
如果我們需要實時計算帖子的數量，可以在cat表里面再加一個num字段泉瞻，每次有人發(fā)帖袖牙，讓這個字段用update更新加1舅锄，這是最好的方法。
二.列類型優(yōu)化
字段類型選擇原則
整型>date,time,>enum,char>varchar>blob,text
列的特點分析：
整型：定長坦仍，沒有國家/地區(qū)之分叨襟，沒有字符集的差異
比如，tinyint 1,2,3,4,5 <--> char(1) a,b,c,d
從空間上锻离，都是占1個字節(jié)汽纠，但是order by排序，前者快
原因：char需要考慮字符集與校對級(就是排序規(guī)則)
time 定長虱朵，運算快钓账，節(jié)省空間梆暮，考慮時區(qū)時，寫sql時不方便 where > '2016-10-11';
enum:定長偿荷，能起約束的目的唠椭，內部用整型來存儲贪嫂，但與char聯(lián)查時，內部要經歷串與值得轉化
Char 定長斗塘，考慮字符集的轉換與排序時的校對集
varchar,不定長馍盟，要考慮字符集的轉換與排序時的校對集台猴，速度慢
text/blob,無法使用內存臨時表(排序等操作只能在磁盤上進行)
三.索引優(yōu)化策略
1.索引類型
1.1Btree索引(二叉樹就是索引)--適用于范圍的查詢
名叫btree索引俱两，大的方面看宪彩，都是用的平衡樹尿孔，但是具體的實現上活合，各引擎稍有不同
比如物赶，嚴格說NDB引擎酵紫，使用的是T-tree
Mysiam,innodb中默認使用B-tree
但抽象一下奖地，B-tree系統(tǒng)可以理解為"排好序的快速查找結構"
1.2hash索引（只在內存表里面使用hash索引）
在memory表里默認是hash索引，hash里的理論查詢時間復雜度為0(1)(一次就能找到)
hash的特點：
1.隨機性：hash函數計算后的結果仰楚，是隨機的犬庇，如果是在磁盤上放置數據械筛，
比如主鍵為id埋哟，那么隨著id的增長，id對應的行赤赊，在磁盤上隨機放置
2.無法對范圍查詢進行優(yōu)化
3.（離散性）無法利用前綴索引抛计，比如在btree中，field列的值"helloworld",加索引吹截， hash(helloworld)和hash(hello),兩者沒有關系。
4.排序也法優(yōu)化(它是隨機分配數據的)
5.必須回行波俄，也就是說，通過索引拿到數據位置捉貌，必須回到表中取數據
2.btree索引常見的誤區(qū)
要查詢的字段都建立了索引趁窃，并不是都可以使用得到醒陆。查詢的時候遵循左前綴規(guī)則
2.索引的左前綴規(guī)則--比如index(a,b,c)
| 語句 | 索引是否發(fā)揮作用 |
|---|---|
| where a=3 | 是，只使用了a列 |
| where a=3 and b=5 | 是裆针，只用了a,b列 |
| where a=3 and b=5 and c=4 | 是统求，使用了abc列 |
| where b=3 / where c=4 | 否 |
| where a=3 and c=4 | a列能發(fā)揮索引，c不能 |
| where a=3 and b>10 and c=7 | A能利用据块，b能利用码邻，c不能利用 |
| 同上，where a=3 and b like"xxx%" and c=7 | A能利用另假，b能利用像屋，c不能利用 |
四.聚簇索引和非聚簇索引
Myisam用的是非聚簇索引：主鍵和次索引都指向物理行磁盤的位置
數據是數據 獨立文件 xx.myd
索引是索引 獨立文件 xx.myi
兩者不摻和 這就是非聚簇
它的查找是先根據索引查找到主鍵的值，值旁邊有一個地址边篮，在地址上去查找你要的信息己莺，這個過程叫“回行”。
但是innodb聚簇戈轿，值得旁邊直接就是行信息。這就是他們的本質信息
innodb用的是聚簇索引：innodb的主索引文件上胜蛉，直接存放該行數據，稱為聚簇索引，次索引指向對主鍵的作用
注意：對innodb來說
1.主鍵索引 既存儲索引金砍，又在葉子中存儲行的數據
2.如果沒有主鍵(primary key),則會unique key做主鍵
3.如果沒有unique,則系統(tǒng)生成一個內部的 rowid 做主鍵
4.像innodb中，主鍵的索引結構中，既存儲了主鍵的值昆著，又存儲了行數據，這種結構稱為"聚簇索引"
聚簇索引
優(yōu)勢：根據主鍵查詢條目比較少時，不用回行(數據就在主節(jié)點下)
劣勢：如果碰到不規(guī)則數據插入時脓豪，造成頻繁的頁分裂
索引覆蓋
索引文件中笤闯，myi有你需要的字段，就不需要回行。這就是索引覆蓋
explain sql \\\\G 如果在extra里面出現了"using index",就是用了索引覆蓋捐韩，查詢效率會很高
比如：如果表A中c1建立了索引
explain select c1 from A where c1>5 \\\\G; 這個時候就用到了索引覆蓋
explain select c1,c2 from A where c1>5 \\\\G;如果c2沒有索引 這個時候沒用索引覆蓋仅政。因為c2在索引里面找不到，需要回行
五.理想的索引
1. sql查詢中也可以直接使用函數,例如查詢詞典表中最長的兩個單詞:
select * from dict order by length(word) desc limit 2;
2.取10個，單詞的左邊只有一個值的：
select left(word,1) from dict limit 10;
3.取重復的
select distinct left(word,1) from dict limit 10;
select count( distinct left(word,1) ) from dict;
2.偽哈希索引技巧
用于存儲url,有兩種方法倦微。1，列內容倒過來存儲并建立索引拓劝。這樣左前綴區(qū)分度大。
2.偽hash索引效果同時存url hash列
create table A( id int primary key,
url char(60) not null default '' );
insert into A values(1,"www.baidu.com"),
(2,"www.sina.com"),(3,"www.itxdl.com");
alert table A add urlcrc int unsigned not null;
在sql存儲時犀变，crccurl == crc32(url)
因為crc的結果是32位，int 無符號數，因此當數據超過40億也會有重復懦傍，但這是是的的寸认。(索引長度為int4個字節(jié))
select crc32('str'); //可以把任何字符串轉換成數字
商品建立索引，一般兩種方式
以，欄目、價格 index(cat_id, price); 或者欄目蚁堤、品牌呈队、價格 index(cat_id,brand_id,shop_price);
索引與排序有兩種情況
1.對于覆蓋索引宪摧，直接在索引上查詢時，就是有序的，using,index
在innodb引擎中，沿著索引字段排序，也是自然有序的，對于myisam引擎肺素，如果按某種索引字段排序，如id筝尾，但去除的字段中未有索引字段，如goods_name,myisam的做法呢撞，不是 索引->回行，而是先去除所有的行，再進行排序
2.先去除數據棒卷，形成臨時表做filesort（文件排序苞俘，但文件可能在磁盤上做裙，也可能在內存中）
