索引小知識

篇幅有限，索引的基本知識我們就不贅述了，在此，我們嘗試說明其中的一個小點-----B+樹與B樹的區別到底是什么。

InnoDB是使用B+樹來實現其索引功能的。在B+樹中，內節點（非葉子節點）存儲了行數據的鍵，而葉子節點存儲了所有的行數據，而B樹的每個節點都存儲了真實的數據。這種數據結構，決定了兩者有以下不同點：

（1）非葉子節點能存放指針的數據量。因為B樹的非葉子節點存放的是整行的數據，占用了較多的空間，所以能存放指針就相對較少，因此整個B樹的層數就變高。當數據量比較大時，插入更新會導致維護代價也是比較大的，而且層數越高，搜索的性能就會越低。而B+樹的內節點存放的是相對短很多的鍵值，就克服了B樹遇到的問題。

（2）從數據結構上來看，B樹的查詢效率與數據所在的位置有關。即如果所要搜索的數據節點，在樹上的位置，越靠近根節點，查詢返回結果越快，最差的就是數據位于葉子節點上，不同的節點位置，其性能不均衡；而B+樹，完整的數據都是在葉子節點上，其查詢效率是固定的。插入、刪除操作同樣的原理，在B樹中，其復雜度明顯增加，而B+樹相對簡單的多。例如，B+樹，在插入過程中，只需要通過在每一層搜索一個節點，依次找到節點之后，在節點處插入即可（節點滿，則分裂）。

（3）B樹中，所有的數據只存儲了一份；而B+樹，除了存儲了所有數據的葉子節點外，還要在內節點存儲了鍵值。所以，在空間占用方面，B+樹會比B樹多些。

（4）在一個表中，聚族索引占用的空間肯定是最大的，因為它存儲了全部數據，而二級索引是建立在某幾個經常查詢的列上（還有用來“回表”的指針），所以，二級索引的占用空間都會比聚族索引小很多。

索引設計原則

（1）MySQL 表主鍵設計

INNODB 以主鍵排序存儲；聚集索引只能是主鍵；存儲所有數據；二級索引包含主鍵鍵值。

如果表沒有定義主鍵，會選擇第一個唯一索引（非空）作為聚集索引主鍵。如果唯一索引也沒有，MySQL后臺會自動生成RowID。

字符類型字段最好不要做主鍵；常見的主鍵有兩種：自增列和UUID。

自增：  順序存儲，索引維護成本低，索引效率高；

UUID：非順序增長，隨機IO嚴重。

（2）索引并不是越多越好，要根據查詢，有針對性的創建，考慮在WHERE和ORDER BY命令上涉及的列建立索引，可根據EXPLAIN來查看是使用了索引還是全表掃描；

（3）應盡量避免在WHERE子句中對字段進行NULL值判斷，否則將導致引擎放棄使用索引而進行全表掃描；

（4）值分布很稀少的字段不適合建索引，例如“性別”這種只有兩三個值的字段；

（5）不用外鍵，由程序保證約束；

（6）盡量不用UNIQUE，由程序保證約束；

（7）使用多列索引時主意順序和查詢條件保持一致，同時刪除不必要的單列索引。

（8）排序時，排序字段需要注意index， 尤其是關聯查詢排序時，盡可能使用小表的字段進行排序

SQL 優化 原則

（1）避免屬性隱試轉換 ， 如定義Moblie varchar  where Moblie =198989888會導致全表掃描；

（2）Where子句中條件字段本身避免使用函數；

（3）使用獲取的必要字段代替SELECT *；

（4）批量插入，使用INSERT INTO table (col1,col2,...) VALUES (value1, value2,...),(value1, value2,...); 插入多條數據只有一次提交；

（5）避免使用長事務；

（6）禁止負向查詢： NOT、！=、<>、！<、！>、NOT IN、NOT LIKE，會導致全表掃描；

（7）大表之間的join，盡量縮小結果集之后再join，否則會消耗較多的內存和CPU；

（8）搜索嚴禁左模糊或者全模糊（like %XX, 或like %XX%），會導致全表掃描。