標籤：完整 b+tree 注意 執行 date 指定 如何 primary com
mysql之高效能索引當db的量達到一定數量級之後，每次進行全表掃描效率就會很低，因此一個常見的方案是建立一些必要的索引作為最佳化手段，那麼問題就來了：
- 那麼什麼是索引呢？
- 索引的實現原理是怎樣的？
- 我們通常說的叢集索引，非叢集索引的區別是什嗎？
- 如何建立和使用索引呢？
I. 索引介紹MySQL官方對索引的定義為：索引是協助MySQL高效擷取資料的資料結構。簡而言之,索引是資料結構1. 幾種樹的結構a. B+樹單來說就是一種為磁碟或者其他存放裝置而設計的一種平衡二叉樹,在B+tree中所有記錄都按照key的大小存放在葉子結點上，各葉子結點直接用指標串連b. 二叉樹二叉樹的規則是父節點大於左孩子節點，小於右孩子節點c. 平衡二叉樹首先是一個二叉樹，但是要求任意一個節點的左右孩子節點的高度差不大於1d. B樹首先是一個平衡二叉樹，但是又要求每個葉子節點到根節點的距離相等那麼B樹和B+樹的區別是什麼呢？
- B+樹的葉子節點可以包含一個指標，指向另一個葉子節點
- B+樹索引值的拷貝存在非葉子節點；索引值+記錄儲存在葉子節點
2. InnoDB引擎之B+樹mysql的InnnoDB引擎採用的B+樹，只有葉子節點儲存對應的資料列，有以下好處
- 葉子結點通常包含較多的記錄，具有較高的扇出性(可理解為每個節點對應的下層節點較多)，因此樹的高度較低(3~4)，而樹的高度也決定了磁碟IO的次數，從而影響了資料庫的效能。一般情況下，IO次數與樹的高度是一致的
- 對於複合式索引，B+tree索引是按照索引列名(從左至右的順序)進行順序排序的，因此可以將隨機IO轉換為順序IO提升IO效率;並且可以支援order by \group等排序需求;適合範圍查詢
3. hash索引hash索引，相比較於B樹而言，不需要從根節點到葉子節點的遍曆，可以一次定位到位置，查詢效率更高，但缺點也很明顯
- 僅能滿足"=","IN"和"<=>"查詢，不能使用範圍查詢
- 因為是通過hash值進行計算，所以只能精確查詢，hash值是沒什麼規律的，不能保證順序和原來一致，所以範圍查詢不行
- 無法進行排序
- 不支援部分索引
- hash值的計算，是根據完整的幾個索引列計算，如果少了其中一個乃至幾個，這個hash值就沒法計算了
- hash碰撞
4. 叢集索引與非叢集索引a. 叢集索引InnoDB的資料檔案本身就是索引檔案，B+Tree的葉子節點上的data就是資料本身，key為主鍵，非葉子節點存放<key,address>，address就是下一層的地址聚簇索引的結構圖:b. 非叢集索引非聚簇索引，葉子節點上的data是主鍵(即聚簇索引的主鍵，所以聚簇索引的key，不能過長)。為什麼存放的主鍵，而不是記錄所在地址呢，理由相當簡單，因為記錄所在地址並不能保證一定不會變，但主鍵可以保證非聚簇索引結構圖：從非叢集索引的結構上，可以看出這種情境下的定位流程：
- 先通過非叢集索引，定位到對應的葉子節點，找到對應的主鍵
- 根據上面找到的主鍵，在叢集索引中，定位到對應的葉子節點（擷取資料）
5. 索引的優點
- 避免全表掃描（當走不到索引時，就只能一個一個的去匹配；如果走索引，則可以根據B樹來定位）
- 使用索引可以協助伺服器避免排序或者暫存資料表 （葉子節點上的指標，可以有效支援範圍查詢；此外葉子節點本身就是根據key進行排序的）
- 索引將隨機IO變成順序IO
6. 適用範圍索引並不是適用於任何情況。對於中型、大型表適用。對於小型表全表掃描更高效。而對於特大型表，考慮”分區”技術II. 索引的使用原則一般我們在建立表的時候，需要指定primary key, 這樣就可以確定叢集索引了，那麼如何添加非叢集索引呢？1. 索引的幾個文法建立索引-- 建立索引create index `idx_img` on newuser(`img`); -- 查看show create table newuser\G;輸出show create table newuser\G*************************** 1. row ***************************Table: newuserCreate Table: CREATE TABLE `newuser` (`userId` bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘使用者id‘,`username` varchar(30) DEFAULT ‘‘ COMMENT ‘使用者登入名稱‘,`nickname` varchar(30) NOT NULL DEFAULT ‘‘ COMMENT ‘使用者暱稱‘,`password` varchar(50) DEFAULT ‘‘ COMMENT ‘使用者登入密碼 & 密文根式‘,`address` text COMMENT ‘使用者地址‘,`email` varchar(50) NOT NULL DEFAULT ‘‘ COMMENT ‘使用者郵箱‘,`phone` bigint(20) NOT NULL DEFAULT ‘0‘ COMMENT ‘使用者手機號‘,`img` varchar(100) DEFAULT ‘‘ COMMENT ‘帳戶圖片‘,`extra` text,`isDeleted` tinyint(1) unsigned NOT NULL DEFAULT ‘0‘,`created` int(11) NOT NULL,`updated` int(11) NOT NULL,PRIMARY KEY (`userId`),KEY `idx_username` (`username`),KEY `idx_nickname` (`nickname`),KEY `idx_email` (`email`),KEY `idx_phone` (`phone`),KEY `idx_img` (`img`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8另一種常見的添加索引方式alter table newuser add index `idx_extra_img`(`isDeleted`, `img`); -- 查看索引show index from newuser;輸出結果+---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| newuser | 0 | PRIMARY | 1 | userId | A | 3 | NULL | NULL | | BTREE | | || newuser | 1 | idx_username | 1 | username | A | 3 | NULL | NULL | YES | BTREE | | || newuser | 1 | idx_nickname | 1 | nickname | A | 3 | NULL | NULL | | BTREE | | || newuser | 1 | idx_email | 1 | email | A | 3 | NULL | NULL | | BTREE | | || newuser | 1 | idx_phone | 1 | phone | A | 3 | NULL | NULL | | BTREE | | || newuser | 1 | idx_img | 1 | img | A | 3 | NULL | NULL | YES | BTREE | | || newuser | 1 | idx_extra_img | 1 | isDeleted | A | 3 | NULL | NULL | | BTREE | | || newuser | 1 | idx_extra_img | 2 | img | A | 3 | NULL | NULL | YES | BTREE | | |+---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+刪除索引drop index `idx_extra_img` on newuser;drop index `idx_img` on newuser; -- 查看索引show index from newuser;輸出show index from newuser;+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| newuser | 0 | PRIMARY | 1 | userId | A | 3 | NULL | NULL | | BTREE | | || newuser | 1 | idx_username | 1 | username | A | 3 | NULL | NULL | YES | BTREE | | || newuser | 1 | idx_nickname | 1 | nickname | A | 3 | NULL | NULL | | BTREE | | || newuser | 1 | idx_email | 1 | email | A | 3 | NULL | NULL | | BTREE | | || newuser | 1 | idx_phone | 1 | phone | A | 3 | NULL | NULL | | BTREE | | |+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+強制走索引的一種方式文法： select * from table force index(索引) where xxxexplain select * from newuser force index(PRIMARY) where userId not in (3, 2, 5);-- +----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+-- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |-- +----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+-- | 1 | SIMPLE | newuser | range | PRIMARY | PRIMARY | 8 | NULL | 4 | Using where |-- +----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+ explain select * from newuser where userId not in (3, 2, 5);-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+-- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+-- | 1 | SIMPLE | newuser | ALL | PRIMARY | NULL | NULL | NULL | 3 | Using where |-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+2. 索引使用規則當一個表內有多個索引時，如何判斷自己的sql是否走到了索引，走的是哪個索引呢？可以通過 explain 關鍵字來進行輔助判斷，當然在實際寫sql時，我們也有必要瞭解下索引匹配的規則，避免設定了一些冗餘的索引，或者寫出一些走不到索引的sql測試的表結構如下*************************** 1. row ***************************Table: newuserCreate Table: CREATE TABLE `newuser` (`userId` bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘使用者id‘,`username` varchar(30) DEFAULT ‘‘ COMMENT ‘使用者登入名稱‘,`nickname` varchar(30) NOT NULL DEFAULT ‘‘ COMMENT ‘使用者暱稱‘,`password` varchar(50) DEFAULT ‘‘ COMMENT ‘使用者登入密碼 & 密文根式‘,`address` text COMMENT ‘使用者地址‘,`email` varchar(50) NOT NULL DEFAULT ‘‘ COMMENT ‘使用者郵箱‘,`phone` bigint(20) NOT NULL DEFAULT ‘0‘ COMMENT ‘使用者手機號‘,`img` varchar(100) DEFAULT ‘‘ COMMENT ‘帳戶圖片‘,`extra` text,`isDeleted` tinyint(1) unsigned NOT NULL DEFAULT ‘0‘,`created` int(11) NOT NULL,`updated` int(11) NOT NULL,PRIMARY KEY (`userId`),KEY `idx_username` (`username`),KEY `idx_nickname_email_phone` (`nickname`,`email`,`phone`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8a. 最左首碼匹配原則這個主要是針對多列非聚簇索引而言，比如有下面這個索引 idx_nickname_email_phone(nickname, email, phone) , nickname 定義在email的前面，那麼下面這幾個語句對應的情況是-- 走索引explain select * from newuser where nickname=‘小灰灰‘ and email=‘[email protected]‘; -- 1. 匹配nickname，可以走索引explain select * from newuser where nickname=‘小灰灰‘; -- 輸出:-- +----+-------------+---------+------+--------------------+--------------------+---------+-------+------+-----------------------+-- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |-- +----+-------------+---------+------+--------------------+--------------------+---------+-------+------+-----------------------+-- | 1 | SIMPLE | newuser | ref | idx_nickname_email | idx_nickname_email | 92 | const | 1 | Using index condition |-- +----+-------------+---------+------+--------------------+--------------------+---------+-------+------+-----------------------+ -- 2. 雖然匹配了email, 但是不滿足最左匹配，不走索引explain select * from newuser where email=‘[email protected]‘; -- 輸出-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+-- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+-- | 1 | SIMPLE | newuser | ALL | NULL | NULL | NULL | NULL | 3 | Using where |-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+b. 無法跳過某個列使用後續索引列即對索引 idx_nickname_email_phone(nickname, email, phone) , 如果你的sql中，只有 nickname 和 phone, 那麼phone走不到索引，因為不能跳過中間的email走索引c. 範圍查詢後的列無法使用索引如 >, <, between, like這種就是範圍查詢，下面的sql中，email 和phone都無法走到索引，因為nickname使用了範圍查詢select * from newuser where nickname like ‘小灰%‘ and email=‘[email protected]‘ and phone=15971112301 limit 10;d. 列作為函數參數或運算式的一部分-- 走不到索引explain select * from newuser where userId+1=2 limit 1; -- 輸出-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+-- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+-- | 1 | SIMPLE | newuser | ALL | NULL | NULL | NULL | NULL | 3 | Using where |-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+3. 索引缺點
- 雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對錶進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要儲存資料，還要儲存一下索引檔案。
- 建立索引會佔用磁碟空間的索引檔案。一般情況這個問題不太嚴重，但如果你在一個大表上建立了多種複合式索引，索引檔案的會膨脹很快。
4. 注意事項
- 索引不會包含有NULL值的列
- 使用短索引
- 索引列排序
- MySQL查詢只使用一個索引，因此如果where子句中已經使用了索引的話，那麼order by中的列是不會使用索引的。因此資料庫預設排序可以符合要求的情況下不要使用排序操作；盡量不要包含多個列的排序，如果需要最好給這些列建立複合索引
- like語句操作
- 一般情況下不鼓勵使用like操作，如果非使用不可，如何使用也是一個問題。like “%aaa%” 不會使用索引而like “aaa%”可以使用索引
- 不要在列上進行運算
- select * from users where YEAR(adddate)<2007;
- 盡量不使用NOT IN和<>操作
5. sql使用原則a. 使用一個sql代替多個sql通常建議是使用一個sql來替代多個sql的查詢當然若sql執行效率很低，或者出現delete等導致鎖表的操作時，也可以採用多個sql，避免阻塞其他sqlb. 分解關聯查詢將關聯join盡量放在應用中來做，盡量執行小而簡單的的sql
- 分解後的sql簡單，利於使用mysql緩衝
- 執行分解後的sql，減少鎖競爭
- 更好的擴充性和維護性（sql簡單）
- 關聯sql使用的是內嵌迴圈演算法nestloop，而應用中可以使用hashmap等結構處理資料，效率更高
c. count
- count(*) 統計的是行數
- count(列名) 統計的是列不為null的數量
d. limit
- limit offset, size; 分頁查詢，會查詢出 offset + size 條資料，擷取最後的size條資料
如 limit 1000, 20 則會查詢出滿足條件的1020條資料，然後將最後的20個返回，所以盡量避免大翻頁查詢e. union需要將where、order by、limit 這些限制放入到每個子查詢，才能重分提升效率。另外如非必須，盡量使用Union all，因為union會給每個子查詢的暫存資料表加入distinct，對每個暫存資料表做唯一性檢查，效率較差。6. mysql使用查詢a. 查看索引-- 單位為GBSELECT CONCAT(ROUND(SUM(index_length)/(1024*1024*1024), 6), ‘ GB‘) AS ‘Total Index Size‘FROM information_schema.TABLES WHERE table_schema LIKE ‘databaseName‘;b. 查看錶空間SELECT CONCAT(ROUND(SUM(data_length)/(1024*1024*1024), 6), ‘ GB‘) AS ‘Total Data Size‘FROM information_schema.TABLES WHERE table_schema LIKE ‘databaseName‘;c. 查看資料庫中所有表的資訊SELECT CONCAT(table_schema,‘.‘,table_name) AS ‘Table Name‘,table_rows AS ‘Number of Rows‘,CONCAT(ROUND(data_length/(1024*1024*1024),6),‘ G‘) AS ‘Data Size‘,CONCAT(ROUND(index_length/(1024*1024*1024),6),‘ G‘) AS ‘Index Size‘ ,CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),6),‘ G‘) AS‘Total‘FROM information_schema.TABLESWHERE table_schema LIKE ‘databaseName‘;
mysql之高效能索引
