mysql查詢優化-有解無憂

1.count優化

#a陳述句：
SELECT COUNT(*) FROM users WHERE id>5;
#b陳述句：
SELECT (SELECT COUNT(*)FROM users ) - COUNT(*) FROM users WHERE id<=5；

　　a陳述句當行數超過11行的時候需要掃描的行數比b陳述句要多， b陳述句掃描了6行，此種情況下，b陳述句比a陳述句更有效率，

　　當沒有where陳述句的時候直接select count(*) from world.city這樣會更快，因為mysql總是知道表的行數，

2.資料不兼容會拉低效率

　　例如float和int、char和varchar、binary和varbinary是不兼容的，資料型別的不兼容可能使優化器無法執行一些本來可以進行的優化操作，

　　在程式中，保證在實作功能的基礎上，盡量減少對資料庫的訪問次數；

　　通過搜索引數，盡量減少對表的訪問行數,最小化結果集，從而減輕網路負擔；　　　　　　　

　　能夠分開的操作盡量分開處理，提高每次的回應速度；

　　在資料視窗使用SQL時，盡量把使用的索引放在選擇的首列；演算法的結構盡量簡單；

　　在查詢時，不要過多地使用通配符如 SELECT * FROM T1陳述句，要用到幾列就選擇幾列如：SELECT COL1,COL2 FROM T1；

　　在可能的情況下盡量限制盡量結果集行數如：SELECT TOP 300 COL1,COL2,COL3 FROM T1,因為某些情況下用戶是不需要那么多的資料的，

　　不要在應用中使用資料庫游標，游標是非常有用的工具，但比使用常規的、面向集的SQL陳述句需要更大的開銷；按照特定順序提取資料的查找，

3.索引列進行運算會讓索引失效

　　如下：b陳述句進行了運算，會使索引失效，從而降低查詢效率

#創建索引
CREATE INDEX index_age ON users(age);
#a陳述句
SELECT * FROM WHERE age<12;
#b陳述句
SELECT * FROM WHERE age*2<24;

4.避免使用 !＝／＜＞/is null/is not null/in/not in等這樣的運算子
　　使用這類運算子會使系統無法使用索引,而只能直接搜索表中的資料

#a陳述句
SELECT COUNT(*) FROM users WHERE age IN (SELECT age FROM users WHERE id>100);
#b陳述句
SELECT COUNT(*) FROM users WHERE EXISTS (SELECT age FROM users WHERE id>100);

　　a陳述句使用了in關鍵字，會降低效率

5.盡量使用數字型別的欄位
　　數字型別占用記憶體小
　對于字串型別，數字型別易于作比較
　　比較長的資料使用字串（例如身份證號、電話號碼等...）

6.合理使用where < 0 和 exists

CREATE TABLE t1(c1 INT,c2 INT);
CREATE TABLE t2(c1 INT,c2 INT);
INSERT INTO t1(c1,c2) VALUES(11,22),(22,33),(33,44);
INSERT INTO t2(c1,c2) VALUES(44,33),(33,22),(22,11);
#a陳述句：
SELECT t1.c1 FROM t1 WHERE (SELECT COUNT(*) FROM t2 WHERE t2.c2=t1.c2 > 0);
#b陳述句：
SELECT t1.c1 FROM t1 WHERE EXISTS (SELECT COUNT(*) FROM t2 WHERE t2.c2=t1.c2);

　　b陳述句查詢速度高于a陳述句

7.能使用between,就不使用in，（連續的范圍，可以使用索引，between...and...）

#查詢年齡100到200之間的資料
SELECT * FROM users WHERE age BETWEEN 100 AND 200;

8.能使用distinct(去重)，就不使用group by(分組)

SELECT DISTINCT NAME FROM users;
SELECT NAME FROM users GROUP BY NAME;

9.盡量不要使用select into陳述句，該陳述句會導致‘表鎖定’，組織其他用戶訪問

10. 強制使用索引

#a陳述句：不使用索引
SELECT * FROM users WHERE tid=3 AND uid IN(10,8,5);
#b陳述句：強制使用索引--force index(索引名稱)
SELECT * FROM users FORCE INDEX(id_uid) WHERE tid=3 AND uid IN(10,8,5);

11. 消除順序讀取，使用索引

#a陳述句：不使用索引
SELECT * FROM users WHERE (tid=5 AND uid>20) OR uid =15;
#b陳述句：拆分，使用索引
SELECT * FROM users WHERE tid=8 AND uid>20
UNION
SELECT * FROM users WHERE uid =15;

12. 模糊查詢(where like)時，字母打頭'a%'會使用索引，非字母打頭'%a%'不會使用索引

#a陳述句不會使用索引
SELECT * FROM aaa WHERE NAME LIKE '%F%';
SELECT * FROM aaa WHERE SUBSTRING(NAME,3,2)='F';
#b陳述句會使用索引
SELECT * FROM aaa WHERE NAME LIKE 'F%';

13.雖然UPDATE、DELETE陳述句的寫法基本固定，但是還是對UPDATE陳述句給點建議：

　　a) 盡量不要修改主鍵欄位，

　　b) 當修改VARCHAR型欄位時，盡量使用相同長度內容的值代替，

　　c) 盡量最小化對于含有UPDATE觸發器的表的UPDATE操作，

　　d) 避免UPDATE將要復制到其他資料庫的列，

　　e) 避免UPDATE建有很多索引的列，

　　f) 避免UPDATE在WHERE子句條件中的列，

14.能使用union all，就不要使用union
　　union會默認進行去重、排序操作，會降低效率

　　UNION ALL不執行SELECT DISTINCT函式，這樣就會減少很多不必要的資源在跨多個不同的資料庫時使用UNION是一個有趣的優化方法，UNION從兩個互不關聯的表中回傳資料，這就意味著不會出現重復的行，同時也必須對資料進行排序，我們知道排序是非常耗費資源的，特別是對大表的排序，

　　UNION ALL可以大大加快速度，如果你已經知道你的資料不會包括重復行，或者你不在乎是否會出現重復的行，在這兩種情況下使用UNION ALL更適合，此外，還可以在應用程式邏輯中采用某些方法避免出現重復的行，這樣UNION ALL和UNION回傳的結果都是一樣的，但UNION ALL不會進行排序，

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/463547.html

標籤：MySQL

上一篇：Mysql查詢優化

下一篇：MySQL備份遷移之mydumper