MySQL資料庫sql語句調優

MySQL資料庫sql語句調優

MySQL索引設計原則：
索引列一般為where子句中的列或串連字句中的列
盡量不對基數小的列做索引，如性別列
儘可能使用短索引：如果對字元列索引盡量指定最小長度。
（short Keys are better,Integer best）
create index cityname on city(city(10));
複合索引首碼特性，索引的順序很重要。
key(a,b,c)聯合索引：
可以走索引的組合：key(a),key(a,b ),key(a,b,c)
下列索引無法走索引key(b),key(b,c),key(a,c)
key(a,b)...where b=5 will not use index

建立複合索引時應將最常用作限制條件列放在最左邊，依次遞減

避免出現無用的索引。（很少使用或從未別調用）

INNODB：盡量指定主鍵，最常用較短資料類型，唯一列做主鍵。
盡量使用定長字元類型char,而不用varchar 
對於索引的最佳化，當使用聯合索引時，將常用的列在索引的左邊，不常用的列，取其前部分字元，只要能精確查到它即可
例如：
create index d_a_p on ad_oldboy_detal(dateline,),ader(ader(20),pos(20));
避免過度使用索引
1.索引的建立對提高檢索能力很有用，但是資料庫維護它也很費資源。
2.對性別列索引，被稱為過度索引。
只有兩個值，建索引不僅沒優勢，還會影響到插入，更新速度，
3，索引會佔用磁碟空間，降低更新操作效能，且執行計畫要考慮各個索引
4.索引不是越多越好。
5行數比較少的表可以不建索引（100行以內）
建立索引的文法 
help create index
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type]
index_col_name:Col_name:[(length)] [ASC|DESC]
Phpadmin:
ALTER TABLE `pw4_group_art` ADD INDEX(`tid`)
create index pw4_group_art_tid on pw4_group_art(tid);
刪除索引文法
drop INDEX ind_sage on student;
複合索引及前N個字元索引例子集合；
create index Sage_Sdet on student(sname,sage(100)
查詢一個語句，看他有沒有走索引；
explain  select * from student where Sno=6
只要key不為空白，
如果資料庫有緩衝，想測試速度，應該
explain SQL_NO_CACHE* from uc_members where email="1234";
查看錶的唯一值多少，用distinct;
select count(distinct Sage) from student;
使用索引的條件
1：索引列不能包含空值
2：複合索引中只要有一列含有null值，那麼這一列將不會使用索引
3：列類型是字串，要在where條件中吧字元值用引號括起來。
4：用or分割開的條件，or前條件有索引，而後面列無索引，那麼設計的索引都不會被用到
5：條件不是索引列的第一部分
5：like語句操作
一般情況下盡量不使用like操作。like "%aaa%" 不會使用索引，而like,"aaa%"可以使用索引。可以建立fulltext或者sphinx(斯芬克司)
6：不要在列上進行運算
select  * from users where YEAR(adddate)<2007;將在每個行上進行運算，這將導致索引失效而進行全表掃描，因此我們可以改成select* from users where addate<'2007-07-01';
7:不使用NOT IN  和<>操作
NOT IN 和<>操作多不會使用索引將進行全表掃描，NOT IN 可以 NOT EXISTS代替，id<>3 則則可使用id>3orid<3來代替
其他；
盡量用串連查詢代替子查詢（巢狀查詢）
order by 的索引問題
show processlist 查看線程
show full processlist
uptime
在生產環境中如果是大表，建立索引會很耗費時間，也許需要幾分鐘，要把索引放在業務的低穀
explian看看有沒有走索引
show processlist;
在看看最佳化之後負載的變化

最佳化的起因：
1 網站出問題，很慢show full processlist--->

2：慢查詢語句(記錄檔)
grep slow my.cnf

只要是
long_query_time=1
log-slow-queries=/data/3306/slow.log
不要強求所有的列進行走索引

讓大表常用的列走索引，一般來說就可以了

本文永久更新連結地址：