一.表結構優(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(文件排序苞俘,但文件可能在磁盤上做裙,也可能在內存中)