- 定位慢查詢
show variables like 'slow_query%'
- 選擇合適的數(shù)據(jù)類型
- 使用可存下數(shù)據(jù)的最小的數(shù)據(jù)類型,int < date, time < char, varchar < blob
- 使用簡單的數(shù)據(jù)類型誉裆,整型比字符串處理開銷更小
- 使用合理的字段屬性長度掸冤,固定長度的表會更快
- 盡可能使用not null 定義字段
- 盡量少用text
- 選擇合適的索引列
- 查詢頻繁的列栗柒,在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;
- 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來代替子查詢
- 使用命令行分析
- 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;