Mysql最佳化方案（整理）

標籤：explain   更新   rand   user   提交   根據   tar   buffer   記憶體   

1.對於複雜查詢，例如報表等多join的查詢，盡量使用緩衝，使用緩衝的時候注意，now（），Randy（），currDate（）等變化的函數是無法使用緩衝，例如：SELECT username FROM user WHERE signup_date >= CURDATE()，可以使用變數代替函數（PHP）；

2.對於複雜查詢可以使用EXPLAIN解釋最佳化sql，從資料量、索引等方面對sql進行最佳化處理。

3.對於確定返回一條資料的sql請使用limit 1；

4.關於索引，使用like ‘%username%’ 是無法應用索引的，可以將username建立fulltext索引以提高效率；兩個表關聯盡量使用索引關聯，注意關聯欄位資料類型必須一致，否則是無法應用索引；

針對索引：列出一些常用技巧

　　負向條件查詢不能使用索引；

　　前置模糊查詢不能使用索引，fulltext類型索引除外，非前置模糊查詢例如like ‘username%’則可以應用索引；

　　列計算不能應用索引，例如sum（column）；

　　複合式索引的順序不影響索引觸發，比如複合式索引a b c，使用b a c ，b a，a都會命中索引，在建立多列索引時，要根據業務需求，where子句中使用最頻繁的一列放在最左邊；

　　頻繁更新的列上不能建立索引，因為索引修改會比普通列產生更大的開銷。

5.關於隨機條數，不能使用select from table order by rand() limit 來實現，效率極低；不能把rand應用在order中！關於隨機條數可以使用自定方案來擷取，例如：

select username from table t1 join(select round(Rand()*(select max(id) from table)) as id) as t2 where t1.id>=t2.id limit 20;

6.絕對不能直接select *！可以使用select count(*) 來擷取資料量，或者返回具體欄位例如:select username from ...;

7.推薦使用PROCEDURE ANALYSE() 來最佳化你的表結構，尤其是你的表欄位很多的時候。

8.針對大資料的insert 和delete操作，盡量拆分成多個資料操作進行，否者。後果。。。。嘖嘖嘖。

9.針對mysql的INNODB引擎，支援事物方面，主要是採用redo log和undo log來實現事物的復原和提交；

任何針對資料庫的修改都會先寫入一個叫buffer pool的緩衝當中，由專門的重新整理線程將修改資料階段性重新整理到磁碟從而持久化資料，但當重新整理的過程中，資料庫非正常中斷，會造成資料丟失，為避免這樣的資料丟失，mysql引入re-do log來記錄資料修改，在非正常中斷髮生後能夠恢複資料到一個正確的狀態，但是會增加寫入re-do log的開銷。

摘一段例子：

假設有2個數值，分別為A和B,值為1，2

1. start transaction;

2. 記錄 A=1 到undo log;

3. update A = 3；

4. 記錄 A=3 到redo log；

5. 記錄 B=2 到undo log；

6. update B = 4；

7. 記錄B = 4 到redo log；

8. 將redo log重新整理到磁碟

9. commit

在1-8的任意一步系統宕機，事務未提交，該事務就不會對磁碟上的資料做任何影響。如果在8-9之間宕機，恢複之後可以選擇復原，也可以選擇繼續完成事務提交，因為此時redo log已經持久化。若在9之後系統宕機，記憶體映射中變更的資料還來不及刷回磁碟，那麼系統復原之後，可以根據redo log把資料刷回磁碟。

所以，redo log其實保障的是事務的持久性和一致性，而undo log則保障了事務的原子性。

10.基礎資料集應盡量少，避免全表資料查詢。

未完待續。。。。。嘎嘎嘎。。。

 

Mysql最佳化方案（整理）