mysql數據庫索引頁號為什么從3開始

原標題：MySQL數據庫快問快答

前言

今天樓主給大家列一下關于數據庫幾個常見問題的要點，如果大家對其中的問題感興趣，可以自行擴展研究。

1. UNION ALL 與 UNION 的區別

UNION和UNION ALL關鍵字都是將兩個結果集合并為一個。

UNION在進行表鏈接后會篩選掉重復的記錄，所以在表鏈接后會對所產生的結果集進行排序運算，刪除重復的記錄再返回結果。

而UNION ALL只是簡單的將兩個結果合并后就返回。

由于UNION需要排序去重，所以 UNION ALL 的效率比 UNION 好很多。

2. TRUNCATE 與 DELETE 區別

TRUNCATE 是DDL語句，而 DELETE 是DML語句。

TRUNCATE 是先把整張表drop調，然后重建該表。而 DELETE 是一行一行的刪除，所以 TRUNCATE 的速度肯定比 DELETE 速度快。

TRUNCATE 不可以回滾，DELETE 可以。

TRUNCATE 執行結果只是返回0 rows affected，可以解釋為沒有返回結果。

TRUNCATE 會重置水平線(自增長列起始位)，DELETE 不會。

TRUNCATE 只能清理整張表，DELETE 可以按照條件刪除。

一般情景下，TRUNCATE性能比DELETE好一點。

3. TIMESTAMP 與 DATETIME 的區別

相同點

TIMESTAMP 列的顯示格式與 DATETIME 列相同。顯示列寬固定在19字符，并且格式為YYYY-MM-DD HH:MM:SS。

不同點

TIMESTAMP

4個字節存儲，時間范圍：1970-01-01 08:00:01~2038-01-19 11:14:07。

值以UTC格式保存，涉及時區轉化，存儲時對當前的時區進行轉換，檢索時再轉換回當前的時區。

DATETIME

8個字節存儲，時間范圍：1000-10-01 00:00:00~9999-12-31 23:59:59。

實際格式存儲，與時區無關。

4. 什么是聯合索引

兩個或更多個列上的索引被稱作聯合索引，聯合索引又叫復合索引。

5. 為什么要使用聯合索引

減少開銷：建一個聯合索引( col1,col2,col3)，實際相當于建了(col1),(col1,col2),(col1,col2,col3)三個索引。減少磁盤空間的開銷。

覆蓋索引：對聯合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通過遍歷索引取得數據，而無需回表，這減少了很多的隨機io操作。覆蓋索引是主要的提升性能的優化手段之一。

效率高：索引列越多，通過索引篩選出的數據越少。有1000W條數據的表，有如下sql select from table where col1=1 and col2=2 and col3=3 ，假設假設每個條件可以篩選出10%的數據，如果只有單值索引，那么通過該索引能篩選出 1000W * 10%=100w 條數據，然后再回表從100w條數據中找到符合 col2=2 and col3= 3 的數據，然后再排序，再分頁；如果是聯合索引，通過索引篩選出 1000w * 10% * 10% * 10%=1w ，效率得到明顯提升。

6. MySQL 聯合索引最左匹配原則

在 MySQL 建立聯合索引時會遵循最左前綴匹配的原則，即最左優先，在檢索數據時從聯合索引的最左邊開始匹配。

MySQL 會一直向右匹配直到遇到范圍查詢(>、 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調整。

7. 什么是聚集和非聚集索引

聚集索引就是以主鍵創建的索引。

非聚集索引就是以非主鍵創建的索引。

8. 什么是覆蓋索引

覆蓋索引(covering index)指一個查詢語句的執行只用從索引頁中就能夠取得(如果不是聚集索引，葉子節點存儲的是主鍵+列值，最終還是要回表，也就是要通過主鍵再查找一次)，避免了查到索引后，再做回表操作，減少I/O提高效率。

可以結合第10個問題更容易理解。

9. 什么是前綴索引

前綴索引就是對文本的前幾個字符(具體是幾個字符在創建索引時指定)創建索引，這樣創建起來的索引更小。但是MySQL不能在ORDER BY或GROUP BY中使用前綴索引，也不能把它們用作覆蓋索引。

創建前綴索引的語法：

ALTERTABLEtable_name ADD

KEY(column_name(prefix_length))

10. InnoDB 與 MyISAM 索引存儲結構的區別

MyISAM索引文件和數據文件是分離的，索引文件僅保存數據記錄的地址。

而在InnoDB中，表數據文件本身就是按B+Tree組織的一個索引結構，這棵樹的葉節點data域保存了完整的數據記錄。這個索引的key是數據表的主鍵，因此InnoDB表數據文件本身就是主索引，所以必須有主鍵，如果沒有顯示定義，自動為生成一個隱含字段作為主鍵，這個字段長度為6個字節，類型為長整型。

InnoDB的輔助索引(Secondary Index，也就是非主鍵索引)存儲的只是主鍵列和索引列，如果主鍵定義的比較大，其他索引也將很大。

MyISAM引擎使用B+Tree作為索引結構，索引文件葉節點的data域存放的是數據記錄的地址，指向數據文件中對應的值，每個節點只有該索引列的值。

MyISAM主索引和輔助索引(Secondary key)在結構上沒有任何區別，只是主索引要求key是唯一的，輔助索引可以重復，(由于MyISAM輔助索引在葉子節點上存儲的是數據記錄的地址，和主鍵索引一樣，所以不需要再遍歷一次主鍵索引)。

簡單的說：

主索引的區別： InnoDB的數據文件本身就是索引文件。而MyISAM的索引和數據是分開的。

輔助索引的區別： InnoDB的輔助索引data域存儲相應記錄主鍵的值而不是地址。而MyISAM的輔助索引和主索引沒有多大區別。

11. 為什么盡量選擇單調遞增數值類型的主鍵

InnoDB中數據記錄本身被存于主索引(B+樹)的葉子節點上。這就要求同一個葉子節點內(大小為一個內存頁或磁盤頁)的各條數據記錄按主鍵順序存放，因此每當有一條新的記錄插入時，MySQL會根據其主鍵將其插入適當的結點和位置，如果頁面達到裝載因子(InnoDB默認為15/16)，則開辟一個新的頁。

如果使用自增主鍵，那么每次插入新的記錄，記錄就會順序添加到當前索引結點的后續位置，當一頁寫滿，就會自動開辟一個新的頁，這樣就會形成一個緊湊的索引結構，近似順序填滿。由于每次插入時也不需要移動已有數據，因此效率很高，也不會增加很多開銷在維護索引上。

如果使用非自增主鍵，由于每次插入主鍵的值近似于隨機，因此每次新紀錄都要被插入到現有索引頁的中間某個位置，此時MySQL不得不為了將新記錄查到合適位置而移動元素，甚至目標頁可能已經被回寫到磁盤上而從緩存中清掉，此時又要從磁盤上讀回來，這增加了很多開銷，同時頻繁的移動、分頁操作造成了大量的碎片，得到了不夠緊湊的索引結構，后續不得不通過 OPTIMIZE TABLE 來重建表并優化填充頁面。

簡單的說：

索引樹只能定位到某一頁，每一頁內的插入還是需要通過比較、移動插入的。所以有序主鍵可以提升插入效率。

12. 建表時，int 后面的長度的意義

int占多少個字節，已經是固定的了，長度代表了顯示的最大寬度。如果不夠會用0在左邊填充，但必須搭配zerofill使用。也就是說，int的長度并不影響數據的存儲精度，長度只和顯示有關。

13. SHOW INDEX 結果字段代表什么意思

Table：

表名。

Non_unique：

0：該索引不含重復值。

1：該索引可含有重復值。

Key_name：

索引名稱，如果是注解索引，名稱總是為PRIMARY。