mysql優(yōu)化

  1. 定位慢查詢
show variables like 'slow_query%'
  1. 選擇合適的數(shù)據(jù)類型
  • 使用可存下數(shù)據(jù)的最小的數(shù)據(jù)類型,int < date, time < char, varchar < blob
  • 使用簡單的數(shù)據(jù)類型誉裆,整型比字符串處理開銷更小
  • 使用合理的字段屬性長度掸冤,固定長度的表會更快
  • 盡可能使用not null 定義字段
  • 盡量少用text
  1. 選擇合適的索引列
  • 查詢頻繁的列栗柒,在where逛裤, group by狮含, order by, join on從句中出現(xiàn)的列
  • where條件中<, <=, =, >=, between, in, 以及l(fā)ike字符串+通配符(%)出現(xiàn)的列
  • 長度小的列懒闷,索引字段越小越好
  • (離散度大)不同的值多的列,放在聯(lián)合索引前面
// 查看離散度
select count(distinct col_name) from table;

索引類型:
普通索引index:適用于name。email等一般屬性
唯一索引unique:與普通索引類似,但唯一
主鍵索引primary:主鍵索引洽糟,一般建表的時候添加
全文索引:只適用于varchar和text字段,且只支持英文纫普,中文需使用Sphinx等技術(shù)
組合索引:就是建立索引時指定多個字段屬性
生效原則:從前往后依次使用生效峭弟,如果中間某個索引沒有使用,那么斷點(diǎn)前面的索引部分起作用屯断,斷點(diǎn)后面的索引沒有起作用文虏;
例:(abc)索引 select * from mytable where a=3 and b=5 and c=4;
查看索引:show index/keys from table_name;

  1. sql語句優(yōu)化
  • 避免全表掃描
  • 避免在where字句中使用!=或<>操作符,否則將放棄使用索引而進(jìn)行全表掃描
  • 避免在where字句中進(jìn)行null判斷殖演,select colname from table where col is null, 可以把null設(shè)為默認(rèn)值0
  • 避免在where字句中使用or連接氧秘,否則將進(jìn)行全表掃描,可以使用union連接2個select查詢
  • 避免在where字句中使用like剃氧,否則將進(jìn)行全表掃描
  • in和not in也會進(jìn)行全表掃描敏储,對于連續(xù)的數(shù)字可以使用between,也可以使用exists代替in
  • 避免在where字句的字段進(jìn)行函數(shù)操作朋鞍,如where num/2=100, 應(yīng)該改為num=100*2
  • 避免使用select * 要用具體字段代替
  • 只查詢一條數(shù)據(jù)時已添,使用limit 1
  • 使用join來代替子查詢
  1. 使用命令行分析
  • show查看狀態(tài)
// 1. 顯示狀態(tài)信息
> show [session|global] status like '%status_name%'
//a 查看查詢次數(shù)【插入,修改滥酥,刪除】
com_select
b 查看連接數(shù)(登陸次數(shù))
connections
c 數(shù)據(jù)庫運(yùn)行時間
uptime
d 慢查詢次數(shù)
slow_queries
e 查看索引使用的情況
handler_read
> handler_read_key 越高越好更舞,表示使用索引查詢到的次數(shù)
handler_read_rnd_next 越高說明查詢越低效

// 2 顯示系統(tǒng)變量
> show variables like '%variables_name%'

// 3 顯示InnoDB存儲引擎的狀態(tài)
> show engine innodb status;

// 4 expain 分析查詢
> explain select ...
explain查詢sql執(zhí)行計(jì)劃,各列含義:
table:表名坎吻;
type:連接的類型
    -const:主鍵缆蝉、索引;
    -eq_reg:主鍵、索引的范圍查找刊头;
    -ref:連接的查找(join)
    -range:索引的范圍查找黍瞧;
    -index:索引的掃描;
    -all:全表掃描原杂;
possible_keys:可能用到的索引印颤;
key:實(shí)際使用的索引;
key_len:索引的長度穿肄,越短越好年局;
ref:索引的哪一列被使用了,常數(shù)較好咸产;
rows:mysql認(rèn)為必須檢查的用來返回請求數(shù)據(jù)的行數(shù)矢否;
extra:using filesort、using temporary(常出現(xiàn)在使用order by時)時需要優(yōu)化脑溢。
    -Using filesort  額外排序僵朗。看到這個的時候屑彻,查詢就需要優(yōu)化了
    -Using temporary 使用了臨時表衣迷。看到這個的時候酱酬,也需要優(yōu)化

// 5 開啟profile壶谒,查看當(dāng)前sql的執(zhí)行時間
> set profiling=on;
> show profiles;
// 查看所有用戶當(dāng)前連接,包括執(zhí)行狀態(tài)等
> show processlist;
// produce analyse()通過扥洗select結(jié)果對現(xiàn)有的表的每一列給出優(yōu)化的建議
> select column_name from table produce analyse();

//6 optimize table 回收閑置的數(shù)據(jù)庫空間
> optmize table table_name;
//7 repair table 修復(fù)被破壞的表
> repair table table_name;
//8 check table 檢查表是否有錯誤
> check table table_name;
最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末膳沽,一起剝皮案震驚了整個濱河市汗菜,隨后出現(xiàn)的幾起案子,更是在濱河造成了極大的恐慌挑社,老刑警劉巖陨界,帶你破解...
    沈念sama閱讀 222,464評論 6 517
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件,死亡現(xiàn)場離奇詭異痛阻,居然都是意外死亡菌瘪,警方通過查閱死者的電腦和手機(jī),發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 95,033評論 3 399
  • 文/潘曉璐 我一進(jìn)店門阱当,熙熙樓的掌柜王于貴愁眉苦臉地迎上來俏扩,“玉大人,你說我怎么就攤上這事弊添÷嫉” “怎么了?”我有些...
    開封第一講書人閱讀 169,078評論 0 362
  • 文/不壞的土叔 我叫張陵油坝,是天一觀的道長嫉戚。 經(jīng)常有香客問我刨裆,道長,這世上最難降的妖魔是什么彬檀? 我笑而不...
    開封第一講書人閱讀 59,979評論 1 299
  • 正文 為了忘掉前任帆啃,我火速辦了婚禮,結(jié)果婚禮上窍帝,老公的妹妹穿的比我還像新娘链瓦。我一直安慰自己,他們只是感情好盯桦,可當(dāng)我...
    茶點(diǎn)故事閱讀 69,001評論 6 398
  • 文/花漫 我一把揭開白布。 她就那樣靜靜地躺著渤刃,像睡著了一般拥峦。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上卖子,一...
    開封第一講書人閱讀 52,584評論 1 312
  • 那天略号,我揣著相機(jī)與錄音,去河邊找鬼洋闽。 笑死玄柠,一個胖子當(dāng)著我的面吹牛,可吹牛的內(nèi)容都是我干的诫舅。 我是一名探鬼主播羽利,決...
    沈念sama閱讀 41,085評論 3 422
  • 文/蒼蘭香墨 我猛地睜開眼,長吁一口氣:“原來是場噩夢啊……” “哼刊懈!你這毒婦竟也來了这弧?” 一聲冷哼從身側(cè)響起,我...
    開封第一講書人閱讀 40,023評論 0 277
  • 序言:老撾萬榮一對情侶失蹤虚汛,失蹤者是張志新(化名)和其女友劉穎匾浪,沒想到半個月后,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體卷哩,經(jīng)...
    沈念sama閱讀 46,555評論 1 319
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡蛋辈,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 38,626評論 3 342
  • 正文 我和宋清朗相戀三年,在試婚紗的時候發(fā)現(xiàn)自己被綠了将谊。 大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片冷溶。...
    茶點(diǎn)故事閱讀 40,769評論 1 353
  • 序言:一個原本活蹦亂跳的男人離奇死亡,死狀恐怖尊浓,靈堂內(nèi)的尸體忽然破棺而出挂洛,到底是詐尸還是另有隱情,我是刑警寧澤眠砾,帶...
    沈念sama閱讀 36,439評論 5 351
  • 正文 年R本政府宣布虏劲,位于F島的核電站托酸,受9級特大地震影響,放射性物質(zhì)發(fā)生泄漏柒巫。R本人自食惡果不足惜励堡,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 42,115評論 3 335
  • 文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望堡掏。 院中可真熱鬧应结,春花似錦、人聲如沸泉唁。這莊子的主人今日做“春日...
    開封第一講書人閱讀 32,601評論 0 25
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽亭畜。三九已至扮休,卻和暖如春,著一層夾襖步出監(jiān)牢的瞬間拴鸵,已是汗流浹背玷坠。 一陣腳步聲響...
    開封第一講書人閱讀 33,702評論 1 274
  • 我被黑心中介騙來泰國打工, 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留劲藐,地道東北人八堡。 一個月前我還...
    沈念sama閱讀 49,191評論 3 378
  • 正文 我出身青樓,卻偏偏與公主長得像聘芜,于是被迫代替她去往敵國和親兄渺。 傳聞我的和親對象是個殘疾皇子,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 45,781評論 2 361

推薦閱讀更多精彩內(nèi)容