Innodb是索引組織表,其結構是B+Tree。其表索引結構是聚簇表分布。
其葉子節點保存了Key+Row Data(聚集索引,按主鍵排序),其Key是主鍵或非空的唯一索引。而其二級索引是非主鍵或者非空的唯一索引。即Innodb表會把主鍵作為聚集索引,如果沒有主鍵,會選擇非空的唯一索引作為聚集索引。如果非空的唯一索引也沒有的情況下,Innodb表會默認生成一個6bytes的id,默認作為主鍵成為聚集索引。
在創建表的時候要自己指定一個沒有業務含義的主鍵,便於快捷插入
默認6字節的數據,到達數據上限時,不會報錯,而是會直接覆蓋數據
Myisam索引(非聚集索引)(非聚簇表分布)
屏幕快照_2016-10-15_上午11.26.18 Myiasm不管是主鍵索引還是二級索引,永遠存儲的是Key,數據則單獨在表中。即非聚集索引,查詢的時候要先查詢索引,然後查詢數據。
Innodb優化方向和注意事項
創建索引
需要注意的是創建索引不是越多越好,要注意基數和選擇性。
基數:
一列數據,只有True和False兩個結果。則這一列的基數是2
選擇性:
列唯一鍵與行數的比值,在0-1之間。他的取值越小越好。選擇性的值是根據表中值的數據而不一樣的。基數為2的情況下,就有2個選擇性的結果。
計算方法:相同值的數據總量➗表中數據的總量
如果Mysql中經常查詢的方法是使用此列中選擇性小的數據進行篩選和取出,那麼可以對此建立索引提高效率。反之,如果經常使用選擇性比較高的數據進行篩選,則不適合對此列建立索引,而應先考慮業務上的查詢優化。
優化
禁止使用select *
使用select * 會查詢大量數據並讀取,不利於使用索引覆蓋技術。
字符串查詢
字符串查詢的時候最好不要在開頭進行模糊查詢,從開頭進行模糊查詢難以使用索引快捷查找。
創建表的時候要有業務無關的自增主鍵
如果不使用自增主鍵的時候,系統默認的主鍵會有覆蓋數據而不警告的風險。
大批量導入數據
如果大批量插入數據的時候,最好先將數據導入,然後再創建索引,同時進行批量提交而非單條提交。
對排序字段創建索引
order by高頻調用的字段,對齊創建索引有助於快速排序。
避免使用limit a,b
Limit a,b 會取出a+b條記錄。所以應該盡量避免,而使用limit a,並排序後限制>id。
執行計劃
explain SQL Command
Example:
mysql> explain select ID,post_author,post_date,post_title,post_status,post_name from wp_posts;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | wp_posts | NULL | ALL | NULL | NULL | NULL | NULL | 56 | 100.00 | NULL |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
數據的查看方法
注意幾點:
1. ID越大,優先級越高,越先被執行
2. ID相同,從上至下,按順序執行
字段解釋
ID
SQL語句的執行順序
possible_keys
指出Mysql可以利用哪些索引在表中找到Row,如果查詢的字段中存在索引,則會被列出在這個地方,如果沒有的話就不會列出,如果有的話也並不代表會被使用。
key
Mysql實際在查詢中使用到的索引,如果沒有使用到索引,則不會被列出。
type
Mysql找到所需數據(Row)所使用的方式,稱之為訪問類型,按照查詢的性能由差到好如下所示:
Type Desc
ALL Full Table Scan,Mysql會遍歷整張表找到所需數據
index Full Index Scan,Mysql只遍歷索引樹
range 索引范圍掃描,Mysql對索引的掃描開始於某一點而非全部,常見於匹配值域(between,in, >, etc...)
ref 非唯一索引掃描,返回匹配某個單獨值的所有數據。常見於使用非唯一索引即唯一索引的非唯一前綴進行的查找(=)
eq_ref 唯一索引掃描,即每個索引鍵只有一條記錄與之匹配,常見於主鍵和唯一鍵
const,system
null
key_len
表示索引中使用的字節數,可通過該列計算查詢中使用的索引的長度
keylen顯示的值為索引字段的最大可能長度,並非實際使用長度。即keylen是根據表定義計算而得,不是通過表內檢索出的
rows
Mysql根據表統計信息和索引狀況,估算的影響到的數據的行數
ref
表示表的連接匹配條件
Extra
顯示比較重要的額外信息
Using Index
表示Mysql在操作中使用了覆蓋索引(Covering Index)
覆蓋索引
MySQL可以利用索引返回select列表中的字段,而不必根據索引再次讀取數據文件。包含所有滿足查詢需要的數據的索引稱為覆蓋索引。
覆蓋索引切不可使用select *,否則會造成索引文件過於龐大,導致查詢性能下降。
Using Where
表示Mysql使用Where字句過濾結果
Using Temporary
表示Mysql需要使用臨時表來存儲結果,一般見於排序和分組中
Using Filesort
表示Mysql無法利用索引完成排序,需要使用文件排序。