Mysql優(yōu)化

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

最后編輯于
?著作權歸作者所有,轉載或內容合作請聯(lián)系作者
  • 序言:七十年代末,一起剝皮案震驚了整個濱河市,隨后出現的幾起案子,更是在濱河造成了極大的恐慌皮假,老刑警劉巖鞋拟,帶你破解...
    沈念sama閱讀 222,378評論 6 516
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件,死亡現場離奇詭異钞翔,居然都是意外死亡严卖,警方通過查閱死者的電腦和手機,發(fā)現死者居然都...
    沈念sama閱讀 94,970評論 3 399
  • 文/潘曉璐 我一進店門布轿,熙熙樓的掌柜王于貴愁眉苦臉地迎上來哮笆,“玉大人,你說我怎么就攤上這事汰扭〕碇猓” “怎么了?”我有些...
    開封第一講書人閱讀 168,983評論 0 362
  • 文/不壞的土叔 我叫張陵萝毛,是天一觀的道長项阴。 經常有香客問我,道長笆包,這世上最難降的妖魔是什么环揽? 我笑而不...
    開封第一講書人閱讀 59,938評論 1 299
  • 正文 為了忘掉前任,我火速辦了婚禮庵佣,結果婚禮上歉胶,老公的妹妹穿的比我還像新娘。我一直安慰自己巴粪,他們只是感情好通今,可當我...
    茶點故事閱讀 68,955評論 6 398
  • 文/花漫 我一把揭開白布粥谬。 她就那樣靜靜地躺著,像睡著了一般辫塌。 火紅的嫁衣襯著肌膚如雪漏策。 梳的紋絲不亂的頭發(fā)上,一...
    開封第一講書人閱讀 52,549評論 1 312
  • 那天臼氨,我揣著相機與錄音掺喻,去河邊找鬼。 笑死一也,一個胖子當著我的面吹牛巢寡,可吹牛的內容都是我干的喉脖。 我是一名探鬼主播椰苟,決...
    沈念sama閱讀 41,063評論 3 422
  • 文/蒼蘭香墨 我猛地睜開眼,長吁一口氣:“原來是場噩夢啊……” “哼树叽!你這毒婦竟也來了舆蝴?” 一聲冷哼從身側響起,我...
    開封第一講書人閱讀 39,991評論 0 277
  • 序言:老撾萬榮一對情侶失蹤题诵,失蹤者是張志新(化名)和其女友劉穎洁仗,沒想到半個月后,有當地人在樹林里發(fā)現了一具尸體性锭,經...
    沈念sama閱讀 46,522評論 1 319
  • 正文 獨居荒郊野嶺守林人離奇死亡赠潦,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內容為張勛視角 年9月15日...
    茶點故事閱讀 38,604評論 3 342
  • 正文 我和宋清朗相戀三年,在試婚紗的時候發(fā)現自己被綠了草冈。 大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片她奥。...
    茶點故事閱讀 40,742評論 1 353
  • 序言:一個原本活蹦亂跳的男人離奇死亡,死狀恐怖怎棱,靈堂內的尸體忽然破棺而出哩俭,到底是詐尸還是另有隱情,我是刑警寧澤拳恋,帶...
    沈念sama閱讀 36,413評論 5 351
  • 正文 年R本政府宣布凡资,位于F島的核電站,受9級特大地震影響谬运,放射性物質發(fā)生泄漏隙赁。R本人自食惡果不足惜,卻給世界環(huán)境...
    茶點故事閱讀 42,094評論 3 335
  • 文/蒙蒙 一梆暖、第九天 我趴在偏房一處隱蔽的房頂上張望伞访。 院中可真熱鬧,春花似錦式廷、人聲如沸咐扭。這莊子的主人今日做“春日...
    開封第一講書人閱讀 32,572評論 0 25
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽蝗肪。三九已至袜爪,卻和暖如春,著一層夾襖步出監(jiān)牢的瞬間薛闪,已是汗流浹背辛馆。 一陣腳步聲響...
    開封第一講書人閱讀 33,671評論 1 274
  • 我被黑心中介騙來泰國打工, 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留豁延,地道東北人昙篙。 一個月前我還...
    沈念sama閱讀 49,159評論 3 378
  • 正文 我出身青樓,卻偏偏與公主長得像诱咏,于是被迫代替她去往敵國和親苔可。 傳聞我的和親對象是個殘疾皇子,可洞房花燭夜當晚...
    茶點故事閱讀 45,747評論 2 361

推薦閱讀更多精彩內容