資料庫本身會選哪個索引

A. MYSQL資料庫索引類型都有哪些

在滿足語句需求的情況下，盡量少的訪問資源是資料庫設計的重要原則，這和執行的 SQL 有直接的關系，索引問題又是 SQL 問題中出現頻率最高的，常見的索引問題包括：無索引（失效）、隱式轉換。
1. SQL 執行流程看一個問題，在下面這個表 T 中，如果我要執行 select * from T where k between 3 and 5; 需要執行幾次樹的搜索操作，會掃描多少行？mysql> create table T ( -> ID int primary key, -> k int NOT NULL DEFAULT 0, -> s varchar(16) NOT NULL DEFAULT '', -> index k(k)) -> engine=InnoDB;mysql> insert into T values(100,1, 'aa'),(200,2,'bb'), (300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');
這分別是 ID 欄位索引樹、k 欄位索引樹。

這條 SQL 語句的執行流程：

1. 在 k 索引樹上找到 k=3，獲得 ID=3002. 回表到 ID 索引樹查找 ID=300 的記錄，對應 R33. 在 k 索引樹找到下一個值 k=5，ID=5004. 再回到 ID 索引樹找到對應 ID=500 的 R4

5. 在 k 索引樹去下一個值 k=6，不符合條件，循環結束

這個過程讀取了 k 索引樹的三條記錄，回表了兩次。因為查詢結果所需要的數據只在主鍵索引上有，所以必須得回表。所以，我們該如何通過優化索引，來避免回表呢？
2. 常見索引優化2.1 覆蓋索引覆蓋索引，換言之就是索引要覆蓋我們的查詢請求，無需回表。

如果執行的語句是 select ID from T wherek between 3 and 5;，這樣的話因為 ID 的值在 k 索引樹上，就不需要回表了。

覆蓋索引可以減少樹的搜索次數，顯著提升查詢性能，是常用的性能優化手段。

但是，維護索引是有代價的，所以在建立冗餘索引來支持覆蓋索引時要權衡利弊。

2.2 最左前綴原則

B+ 樹的數據項是復合的數據結構，比如 (name,sex，age) 的時候，B+ 樹是按照從左到右的順序來建立搜索樹的，當 (張三,F,26) 這樣的數據來檢索的時候，B+ 樹會優先比較 name 來確定下一步的檢索方向，如果 name 相同再依次比較 sex 和 age，最後得到檢索的數據。

• # 有這樣一個表 P

• mysql> create table P (id int primary key, name varchar(10) not null, sex varchar(1), age int, index tl(name,sex,age)) engine=IInnoDB;

• mysql> insert into P values(1,'張三','F',26),(2,'張三','M',27),(3,'李四','F',28),(4,'烏茲','F',22),(5,'張三','M',21),(6,'王五','M',28);

• # 下面的語句結果相同

• mysql> select * from P where name='張三' and sex='F'; ## A1

• mysql> select * from P where sex='F' and age=26; ## A2

• # explain 看一下

• mysql> explain select * from P where name='張三' and sex='F';

• +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+

• | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

• +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+

• | 1 | SIMPLE | P | NULL | ref | tl | tl | 38 | const,const | 1 | 100.00 | Using index |

• +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+

• mysql> explain select * from P where sex='F' and age=26;

• +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+

• | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

• +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+

• | 1 | SIMPLE | P | NULL | index | NULL | tl | 43 | NULL | 6 | 16.67 | Using where; Using index |

• +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+

• 可以清楚的看到，A1 使用 tl 索引，A2 進行了全表掃描，雖然 A2 的兩個條件都在 tl 索引中出現，但是沒有使用到 name 列，不符合最左前綴原則，無法使用索引。所以在建立聯合索引的時候，如何安排索引內的欄位排序是關鍵。評估標準是索引的復用能力，因為支持最左前綴，所以當建立（a，b）這個聯合索引之後，就不需要給 a 單獨建立索引。原則上，如果通過調整順序，可以少維護一個索引，那麼這個順序往往就是需要優先考慮採用的。上面這個例子中，如果查詢條件里只有 b，就是沒法利用（a，b）這個聯合索引的，這時候就不得不維護另一個索引，也就是說要同時維護（a，b）、（b）兩個索引。這樣的話，就需要考慮空間佔用了，比如，name 和 age 的聯合索引，name 欄位比 age 欄位佔用空間大，所以創建（name，age）聯合索引和（age）索引佔用空間是要小於（age，name）、(name）索引的。

2.3 索引下推

• 以人員表的聯合索引（name, age）為例。如果現在有一個需求：檢索出表中「名字第一個字是張，而且年齡是26歲的所有男性」。那麼，SQL 語句是這么寫的mysql> select * from tuser where name like '張%' and age=26 and sex=M;


• 通過最左前綴索引規則，會找到 ID1，然後需要判斷其他條件是否滿足在 MySQL 5.6 之前，只能從 ID1 開始一個個回表。到主鍵索引上找出數據行，再對比欄位值。而 MySQL 5.6 引入的索引下推優化(index condition pushdown)，可以在索引遍歷過程中，對索引中包含的欄位先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數。這樣，減少了回表次數和之後再次過濾的工作量，明顯提高檢索速度。

2.4 隱式類型轉化

• 隱式類型轉化主要原因是，表結構中指定的數據類型與傳入的數據類型不同，導致索引無法使用。所以有兩種方案：
• 修改表結構，修改欄位數據類型。

• 修改應用，將應用中傳入的字元類型改為與表結構相同類型。

• 3. 為什麼會選錯索引3.1 優化器選擇索引是優化器的工作，其目的是找到一個最優的執行方案，用最小的代價去執行語句。在資料庫中，掃描行數是影響執行代價的因素之一。掃描的行數越少，意味著訪問磁碟數據的次數越少，消耗的 CPU 資源越少。當然，掃描行數並不是唯一的判斷標准，優化器還會結合是否使用臨時表、是否排序等因素進行綜合判斷。

3.2 掃描行數

• MySQL 在真正開始執行語句之前，並不能精確的知道滿足這個條件的記錄有多少條，只能通過索引的區分度來判斷。顯然，一個索引上不同的值越多，索引的區分度就越好，而一個索引上不同值的個數我們稱為「基數」，也就是說，這個基數越大，索引的區分度越好。# 通過 show index 方法，查看索引的基數mysql> show index from t;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| t | 0 | PRIMARY | 1 | id | A | 95636 | NULL | NULL | | BTREE | | || t | 1 | a | 1 | a | A | 96436 | NULL | NULL | YES | BTREE | | || t | 1 | b | 1 | b | A | 96436 | NULL | NULL | YES | BTREE | | |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+


• MySQL 使用采樣統計方法來估算基數：采樣統計的時候，InnoDB 默認會選擇 N 個數據頁，統計這些頁面上的不同值，得到一個平均值，然後乘以這個索引的頁面數，就得到了這個索引的基數。而數據表是會持續更新的，索引統計信息也不會固定不變。所以，當變更的數據行數超過 1/M 的時候，會自動觸發重新做一次索引統計。

在 MySQL 中，有兩種存儲索引統計的方式，可以通過設置參數 innodb_stats_persistent 的值來選擇：

• on 表示統計信息會持久化存儲。默認 N = 20，M = 10。

• off 表示統計信息只存儲在內存中。默認 N = 8，M = 16。

• 由於是采樣統計，所以不管 N 是 20 還是 8，這個基數都很容易不準確。所以，冤有頭債有主，MySQL 選錯索引，還得歸咎到沒能准確地判斷出掃描行數。

可以用 analyze table 來重新統計索引信息，進行修正。

• ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...


• 3.3 索引選擇異常和處理1. 採用 force index 強行選擇一個索引。2. 可以考慮修改語句，引導 MySQL 使用我們期望的索引。3. 有些場景下，可以新建一個更合適的索引，來提供給優化器做選擇，或刪掉誤用的索引。

B. 什麼是索引索引類型有幾種，各有什麼特點

索引是對資料庫表中一列或多列的值進行排序的一種結構，使用索引可快速訪問資料庫表中的特定信息。是一種單獨的、物理的對資料庫表中一列或多列的值進行排序的一種存儲結構，它是某個表中一列或若干列值的集合和相應的指向表中物理標識這些值的數據頁的邏輯指針清單。

索引有三種類型：

1. 唯一索引：唯一索引是不允許其中任何兩行具有相同索引值的索引。當現有數據中存在重復的鍵值時，大多數資料庫不允許將新創建的唯一索引與表一起保存。

2. 主鍵索引：資料庫表經常有一列或多列組合，其值唯一標識表中的每一行。該列稱為表的主鍵。在資料庫關系圖中為表定義主鍵將自動創建主鍵索引，主鍵索引是唯一索引的特定類型。該索引要求主鍵中的每個值都唯一。

3. 聚焦索引：在聚集索引中，表中行的物理順序與鍵值的邏輯（索引）順序相同。一個表只能包含一個聚集索引。如果某索引不是聚集索引，則表中行的物理順序與鍵值的邏輯順序不匹配。

(2)資料庫本身會選哪個索引擴展閱讀：

通過建立索引可以極大地提高在資料庫中獲取所需信息的速度，同時還能提高伺服器處理相關搜索請求的效率，從這個方面來看它具有以下優點：

1. 在設計資料庫時，通過創建一個惟一的索引，能夠在索引和信息之間形成一對一的映射式的對應關系，增加數據的惟一性特點。

2. 能提高數據的搜索及檢索速度，符合資料庫建立的初衷。

3. 能夠加快表與表之間的連接速度，這對於提高數據的參考完整性方面具有重要作用。

4. 在信息檢索過程中，若使用分組及排序子句進行時，通過建立索引能有效的減少檢索過程中所需的分組及排序時間，提高檢索效率。

5. 建立索引之後，在信息查詢過程中可以使用優化隱藏器，這對於提高整個信息檢索系統的性能具有重要意義。

C. 資料庫索引有哪幾種怎樣建立索引

• 種類：

1、按照索引列值的唯一性，索引可分為唯一索引和非唯一索引；

• 非唯一索引：

create index 索引名 on 表名（列名） tablespace表空間名；

• 唯一索引：

建立主鍵或者唯一約束時會自動在對應的列上建立唯一索引；

2、索引列的個數：單列索引和復合索引；

3、按照索引列的物理組織方式。

• 索引的創建格式：

CREATEUNIUQE|BITMAPINDEX<schema>.<index_name>ON<schema>.<table_name>(<column_name>|<expression>ASC|DESC,<column_name>|<expression>ASC|DESC,...)TABLESPACE<tablespace_name>STORAGE<storage_settings>LOGGING||COMPRESS<nn>NOSORT|REVERSEPARTITION|GLOBALPARTITION<partition_setting>

使用USER_IND_COLUMNS查詢某個TABLE中的相應欄位索引建立情況

使用DBA_INDEXES/USER_INDEXES查詢所有索引的具體設置情況。

在Oracle中的索引可以分為：B樹索引、點陣圖索引、反向鍵索引、基於函數的索引、簇索引、全局索引、局部索引等，下面逐一講解：

一、B樹索引：

最常用的索引，各葉子節點中包括的數據有索引列的值和數據表中對應行的ROWID，簡單的說，在B樹索引中，是通過在索引中保存排過續的索引列值與相對應記錄的ROWID來實現快速查詢的目的。其邏輯結構如圖：

可以保證無論用戶要搜索哪個分支的葉子結點，都需要經過相同的索引層次，即都需要相同的I/O次數。

B樹索引的創建示例：

create index ind_t on t1(id);

注1：索引的針對欄位創建的，相同欄位不能創建一個以上的索引；

注2：默認的索引是不唯一的，但是也可以加上unique，表示該索引的欄位上沒有重復值(定義unique約束時會自動創建)；

注3：創建主鍵時，默認在主鍵上創建了B樹索引，因此不能再在主鍵上創建索引。

二、點陣圖索引：

有些欄位中使用B樹索引的效率仍然不高，例如性別的欄位中，只有「男、女」兩個值，則即便使用了B樹索引，在進行檢索時也將返回接近一半的記錄。

所以當欄位的基數很低時，需要使用點陣圖索引。(「低」的標準是取值數量 < 行數*1%)

反向鍵索引是一種特殊的B樹索引，在存儲構造中與B樹索引完全相同，但是針對數值時，反向鍵索引會先反向每個鍵值的位元組，然後對反向後的新數據進行索引。例如輸入2008則轉換為8002，這樣當數值一次增加時，其反向鍵在大小中的分布仍然是比較平均的。

反向鍵索引的創建示例：

createindex ind_t on t1(id) reverse;

註：鍵的反轉由系統自行完成。對於用戶是透明的。

四、基於函數的索引：

有的時候，需要進行如下查詢：select * from t1 where to_char(date,'yyyy')>'2007';

但是即便在date欄位上建立了索引，還是不得不進行全表掃描。在這種情況下，可以使用基於函數的索引。其創建語法如下：

create index ind_t on t1(to_char(date,'yyyy'));

註：簡單來說，基於函數的索引，就是將查詢要用到的表達式作為索引項。

五、全局索引和局部索引：

這個索引貌似很復雜，其實很簡單。總得來說一句話，就是無論怎麼分區，都是為了方便管理。

具體索引和表的關系有三種：

1、局部分區索引：分區索引和分區表1對1

2、全局分區索引：分區索引和分區表N對N

3、全局非分區索引：非分區索引和分區表1對N

創建示例：

首先創建一個分區表

createtable student

(

stuno number(5),

sname vrvhar2(10),

deptno number(5)

)

partition by hash (deptno)

(

partition part_01 tablespace A1,

partition part_02 tablespace A2

);

創建局部分區索引(1v1)：

create index ind_t on student(stuno)

local(

partition part_01 tablespace A2,

partition part_02 tablespace A1

);--local後面可以不加

創建全局分區索引(NvN)：

create index ind_t on student(stuno)

globalpartition by range(stuno)

(

partition p1 values less than(1000) tablespace A1,

partition p2 values less than(maxvalue) tablespace A2

);--只可以進行range分區

創建全局非分區索引(1vN)

createindex ind_t on student(stuno) GLOBAL;