MySQL 二 索引

二、MySQL 索引    
1、    索引是為了加速對錶中資料行的檢索而建立的一種分散儲存的資料結構
2、    索引的優點：
    1）索引能極大的減少儲存引擎需要掃描的資料量
    2）索引可以把隨機IO變成順序IO
    3）索引可以幫助我們在進行分組、排序等操作時，避免使用臨時表
3、 SHOW INDEX FROM T 查詢表 T 所有索引
4、 MySQL 中最多可以建立 16 個索引列
5、 MySQL 索引的邏輯分類
    1）主鍵索引：一張表只能有一個主鍵索引，不允許重複、不允許為 NULL； alter table t add primary key add (`id`);
    2）唯一索引：資料列不允許重複，允許為 NULL 值，一張表可有多個唯一索引，但是一個唯一索引只能包含一列，比如身份證號碼、卡號等都可以作為唯一索引；
                 alter table t add unique (`username`);
    3）普通索引：一張表可以建立多個普通索引，一個普通索引可以包含多個欄位，允許資料重複，允許 NULL 值插入；
                 alter table t add index index_name (`username`);
    4）全文索引：讓搜尋關鍵詞更高效的一種索引。     alter table t add fulltext (`username`);
6、 MySQL 索引的物理分類：
    1）聚集索引：一般是表中的主鍵索引，如果表中沒有顯示指定主鍵，則會選擇表中的第一個不允許為 NULL 的唯一索引，如果還是沒有的話，就採用 
        Innodb 儲存引擎為每行資料內建的 6 位元組 ROWID 作為聚集索引。每張表只有一個聚集索引，因為聚集索引的鍵值的邏輯順序決定了表中相應行的物理順序。
        聚集索引在精確查詢和範圍查詢方面有良好的效能表現（相比於普通索引和全表掃描），聚集索引就顯得彌足珍貴，聚集索引選擇還是要慎重的
        （一般不會讓沒有語義的自增 id 充當聚集索引）
    2）非聚集索引：索引的邏輯順序與磁碟上行的物理儲存順序不同（非主鍵的那一列），一個表中可以擁有多個非聚集索引    
7、 主鍵索引和唯一索引的區別
    1）主鍵索引不能為空不能重複，唯一索引可以為空，但不能重複
    2）一張表只能有一個主鍵索引，但可以有多個唯一索引
    3）主鍵索引的查詢效能要高於唯一索引
8、 唯一索引與普通索引，對於查詢操作來說兩者效能相近，都是從索引樹中進行查詢。對於更新操作來說，唯一索引要比普通索引執行的慢，因為唯一索引需要先將資料讀取
    到記憶體中，再在記憶體中進行資料的唯一校驗，所以執行起來要比普通索引更慢
9、 在 InnDB 中主鍵索引比普通索引的查詢效能高，因為普通索引的查詢會多執行一次檢索操作。如主鍵查詢 select * from t where id=10 只需要搜尋 id 的這棵 B+ 樹，
    而普通索引查詢 select * from t where f=3 會先查詢 f 索引樹，得到 id 的值之後再去搜尋 id 的 B+ 樹，因為多執行了一次檢索，所以執行效率就比主鍵索引要低
10、回表查詢：普通索引查詢到主鍵索引後，回到主鍵索引樹搜尋的過程
11、如果把一個 InnoDB 表的主鍵刪掉，是不是就沒有主鍵，就沒辦法進行回表查詢了？
    不是，如果把主鍵刪掉了，那麼 InnoDB 會自己生成一個長度為 6 位元組的 rowid 作為主鍵
12、覆蓋索引：是指索引上的資訊足夠滿足查詢請求，不需要再回到主鍵上去取資料
    覆蓋索引可減少資料庫IO，將隨機IO變為順序IO，可提高查詢效能
13、聯合索引：又叫複合索引，遵循最左匹配原則，如聯合索引為 key(a,b,c)，則能觸發索引的搜尋組合是 a|ab|abc 這三種查詢
14、聯合索引特點
    1）聯合索引中如果不是按照索引最左列開始查詢，無法使用索引；
    2）聯合索引中精確匹配最左前列並範圍匹配另外一列可以用到索引；
    3）聯合索引中如果查詢中有某個列的範圍查詢，則其右邊的所有列都無法使用索引；
15、聯合索引列選擇原則
    1） 最左匹配原則及它的生效原則
        也叫最左字首原則，是 MySQL 中的一個重要原則，指的是索引以最左邊為起點任何連續的索引都能匹配上，當遇到範圍查詢（>、<、between、like）就會停止匹配。 
        生效原則，如表中有一個聯合索引欄位 index(a,b,c)：
            where a=1 只使用了索引 a；
            where a=1 and b=2 只使用了索引 a,b；
            where a=1 and b=2 and c=3 使用a,b,c；
            where b=1 or where c=1 不使用索引；
            where a=1 and c=3 只使用了索引 a；
            where a=3 and b like 'xx%' and c=3 只使用了索引 a,b。
    2） 選擇性最好（離散性最好）原則
    3） 最小空間原則
16、字首索引：也叫區域性索引，給某列部分資訊新增索引，比如給身份證的前 10 位新增索引
    給手機號的前 6 位建立索引：
    alter table t add index index_phone(phone(6));
    create index index_phone on t(phone(6));
17、字首索引優點：字首索引能有效減小索引檔案的大小，讓每個索引頁可以儲存更多的索引值，從而提高了索引查詢的速度    
18、字首索引缺點：不能在 order by 或者 group by 中觸發字首索引，也不能把它們用於覆蓋索引
19、在 MySQL 中可以使用 force index 強行選擇一個索引，具體查詢語句如下：select * from t force index(index_t)
20、索引的常見儲存演算法：
    1）雜湊儲存法：以 key、value 方式儲存，把值存入陣列中使用雜湊值確認資料的位置，如果發生雜湊衝突，使用連結串列儲存資料
    2）有序陣列儲存法：按順序儲存，優點是可以使用二分法快速找到資料，缺點是更新效率，適合靜態資料儲存
    3）搜尋樹：以樹的方式進行儲存，查詢效能好，更新速度快
21、索引失效的情況
    1）使用or
        select * from t where num=10 or num=20; 使用 or 查詢會使 MySQL 放棄索引而全表掃描，可以改為：
        select * from t where num=10 union select * from t where num=20;
    2）like '%abc%'
        like 'aaa%' 可以使用索引
        要讓 like '%abc' 也走索引，可以使用 REVERSE() 函式來建立一個函式索引，如：select * from t where reverse(f) like reverse('%abc');    
    3）在列上進行運算
    4）NOT IN 和 <> 操作
22、使用索引注意事項：
    1）使用短索引，短索引不僅可以提高查詢速度，更能節省磁碟空間和 I/O 操作；
    2）索引列排序，MySQL 查詢只使用一個索引，因此如果 where 子句中已經使用了索引的話，那麼 order by 中的列是不會使用索引的，因此資料庫預設排序可以符合要求的情況下，
        不要進行排序操作；儘量不要包含多個列的排序，如果需要最好給這些列建立複合索引
    3）索引列的資料長度能少則少。
    4）索引一定不是越多越好，越全越好，一定是建合適的。
    5）匹配列字首可用到索引 like 9999%，like %9999%、like %9999用不到索引；
    6）Where 條件中 not in 和 <>操作無法使用索引；
    7）匹配範圍值，order by 也可用到索引；
    8）多用指定列查詢，只返回自己想到的資料列，少用select *；
23、列值為 NULL 時，在 MySQL 5.6 以上的 InnoDB 儲存引擎會正常觸發索引。但為了相容低版本的 MySQL 和相容其他資料庫儲存引擎，不建議使用 NULL 值來儲存和查詢資料，
    建議設定列為 NOT NULL，並設定一個預設值，比如 0 和空字串等，如果是 datetime 型別，可以設定成 1970-01-01 00:00:00 這樣的特殊值。