mysql索引的註意事項

索引的優點

• 大大加快數據的查詢速度
• 使用分組和排序進行數據查詢時，可以顯著減少查詢時分組和排序的時間
• 創建唯一索引，能夠保證資料庫表中每一行數據的唯一性
• 在實現數據的參考完整性方面，可以加速表和表之間的連接

索引的缺點

• 創建索引和維護索引需要消耗時間，並且隨著數據量的增加，時間也會增加
• 索引需要占據磁碟空間
• 對數據表中的數據進行增加，修改，刪除時，索引也要動態的維護，降低了維護的速度

創建索引的原則

• 更新頻繁的列不應設置索引
• 數據量小的表不要使用索引（畢竟總共2頁的文檔，還要目錄嗎？）
• 重覆數據多的欄位不應設為索引（比如性別，只有男和女，一般來說：重覆的數據超過百分之15就不該建索引）
• 首先應該考慮對where 和 order by 涉及的列上建立索引

補充：確認索引是否已使用：explain select username from user where id=1

結果為：

欄位說明：

• select_type  simple表示簡單查詢 還有其他如primary，union，subquery等
• table  表名
• partitions  匹配的分區
• type  引擎在表中找到所需行的方式 由差到好為：all（全表掃描），index（只遍歷索引樹），range（索引範圍掃描，常見於between，>，< 等查詢中），ref（非唯一性索引掃描），eq_ref（唯一性索引掃描），const / system（當MySQL對查詢某部分進行優化，並轉換為一個常量時，使用這些類型訪問），null（MySQL在優化過程中分解語句，執行時甚至不用訪問表或索引）
• possible_keys  可供選擇的索引
• key  使用的索引
• key_len  索引位元組數的長度，數值越小，運行速度越快
• ref  連接匹配條件，即哪些列或常量被用於查找索引列上的值
• rows  返回的數據行數
• filtered  被表條件過濾的行數的百分比
• extra  額外信息  類型： using index（表示select操作中使用了覆蓋索引），using where（mysql伺服器在存儲引擎受到記錄後進行“後過濾“），using temporary（表示mysql需要使用臨時表來存儲結果集，常見於排序和分組查詢）， using filesort（mysql中無法使用索引完成的排序操作，成為“文件排序”）

註意： 通過key就能判斷索引是否執行

優化mysql查詢語句

• 不要在where條件語句 '=' 的左邊進行函數，運算符或表達式的計算，如 select name from tb_user where age/2=20，因為索引不會生效（引擎會放棄使用索引，進行全表掃描）
• 不要使用 <>，！=，not in ，因為索引不會生效
• 避免對欄位進行null的判斷，因為索引不會生效（可以用一個值代替null，如-999）
• 使用like模糊查詢時，like '%xx%'會導致索引不生效，like 'xx%' 索引能夠被使用，所以避免使用第一種
• 避免使用or，可以用union替代（要想使用or，又讓索引生效，or條件中的每個列都必須加上索引）
• 使用exist代替in（表中數據越多，exist的效率就比in要越大）
• 數據類型隱形轉換，索引不會生效：如 select name from user where phone=13155667788;（phone欄位在資料庫中為varchar類型，應改成 phone='13155667788'）
• 聯合索引必須要按照順序才會生效：如創建的索引順序為a,b，where a="xx" and b="xx" 生效，但 b="xx" and a="xx" 則不會生效，補充：a="xx" 沒有後面的，索引也會生效
• 儘量避免使用游標（游標效率低）
• 不要使用 select * 
• 還有很多...

在已經存在的表上創建索引

1. create index index_name  on table_name (column_name[length], ...) [asc|desc] 
2. alter table table_name add [unique|fulltext|spatial] [index|key] index_name(column_name[length, ...]) [asc|desc]

刪除索引

1. alter table table_name drop index index_name
2. drop index index_name on table_name