#### 不能使用索引 - 負向條件不能使用索引,用in - 前綴模糊匹配不能使用索引,例如'like %name',改為'like name%' - 選擇性小的不宜作為索引 - 索引列不要做運算操作 - 多列索引遵循前綴原則,最左的列必定是頻繁查詢的列 - 避免使用默認值null,索引樹不包含null記錄,也就是說null查詢會導致全表掃描,例如select * from account where name is null導致全表掃描(全表1722608行記錄),select * from account where name = ''只掃描了7行記錄 #### 選擇索引 - 一般是在where和order by涉及的列建立索引 - 不要每個列都建立索引,太多的索引會影響插入,刪除,更新的效率,根據數據的選擇性建立 - 字符串建立索引應考慮索引的長度 #### 確定索引長度 ```sql create table city ( id int(11) not null auto_increment, name varchar(50) character set utf8 collate utf8_general_ci not null comment '城市名', primary key (id) ) engine = InnoDB charset = utf8 collate utf8_general_ci comment '城市表'; select count(distinct left(city,3))/ count(*) as sel3, count(distinct left(city,4)) /count(*) as sel4, count(distinct left(city,5)) /count(*) as sel5, count(distinct left(city,6)) /count(*) as sel6 from city; ``` 比較sel3,sel4,sel5,sel6,值越大也好(也要考慮下索引長度,平衡下選擇合適的長度) #### 優化場景 1. 首先explain查看sql的執行計劃,重點看key和rows 2. 如果rows掃描過多查看是否建立索引,或者是否正確使用了索引(效果最顯著) 3. 查看查詢條件,如果查詢條件是否全表掃描,在業務上調整(時間范圍問題) 4. 復雜的sql語句拆分多條sql,例如一條sql關聯的很多張表會產生臨時表,避免臨時表(查詢大量數據時候) 5. 多條sql遍歷插入改為批量插入(交卷的時候) 6. 遍歷查詢改為批量查詢 7. 統計類數據建立計數表 8. 使用mongo保存關聯數據,簡單的單表查詢,配合aggregate可以做復雜的聚合統計 9. 使用redis保存熱點數據 10. select * 問題(數據量大的時候內存占用很明顯) 11. optimize定期優化表,表含有可變長度的列(如varchar),并且經常性刪除或修改記錄,使用optimize將對空間碎片進行合并 12. 分拆很長的列：一般情況下，TEXT、BLOB，大于512字節的字符串，基本上都是為了顯示信息，而不會用于查詢條件， 因此表設計的時候，應該將這些列獨立到另外一張表 13. 有時候排序或分組交給php處理,避免臨時表 (如獲取每個地區數據之后,再用array_multisort) #### 其他一些優化 ##### 字段類型為字符串，并建立索引，sql查詢的時候需要加上引號,例如outside為varchar(20)，sql如下: ```sql select * from user where outside = '123456' # 會走索引 select * from user where outside = 123456 # 不會走索引 ```