sql語句優化小技巧

優化group by語句

group by分組查詢時，預設分組後，還會進行排序，可能會降低速度。

explain select * from table_name group by column;

extra :use filesort //預設使用了檔案排序

• 解決方法

  在group by後面添加order by null防止預設排序

盡量使用join

• 子查詢會産生臨時表
• 使用join，MySQL不需要在記憶體中建立臨時表

資料庫存儲引擎

MYISAM

• select ,insert速度比較快，鎖機制采用表級鎖，支援全文索引。由于是表級鎖，對高并發性的update效率較低
• 對MyISAM表的讀操作，不會阻塞其他使用者對同一表的讀請求，但會阻塞對同一表的寫請求；對 MyISAM表的寫操作，則會阻塞其他使用者對同一表的讀和寫操作；MyISAM表的讀操作與寫操作之間，以及寫操作之間是串行的！當一個線程獲得對一個表的寫鎖後，隻有持有鎖的線程可以對表進行更新操作。其他線程的讀、寫操作都會等待，直到鎖被釋放為止。

InnoDB

• 鎖機制采用行級鎖，支援事務，查詢和寫入速度低于myisam，但是安全性較好，不支援全文索引。由于是行級鎖，對高并發性的update效率相對較高

兩者差別

1. 事務安全
2. 查詢和添加速度
3. 全文索引的支援
4. 鎖機制
5. 外鍵MyISAM不支援外鍵，InnoDB支援外鍵(通常不設定外鍵，基本都是在程式中保持資料的一緻)

Mysql資料庫檔案擴充名

• frm、MYD、MYI是myisam引擎表的結構檔案，資料檔案，索引檔案
• CSM、CSV是和csv引擎表相關的檔案， .csv是可以直接檢視的

備注

表鎖狀态

可以通過檢查table_locks_waited和table_locks_immediate狀态變量來分析系統上的表鎖定争奪

碎片整理

如果你的資料的存儲引擎是MYISAM, 一定要定時進行碎片整理

指令：optimize table table_name

假設一個項目中的某張MyISAM表有100萬條資料，其中有1/3重複，delete後，/data/table_name.MYD 并沒有減少，導緻項目越來越慢，是以應該對Myisam進行碎片整理