MySQL資料庫快問快答

你在我家門口發表於2019-04-09

前言

今天樓主給大家列一下關於資料庫幾個常見問題的要點,如果大家對其中的問題感興趣,可以自行擴充套件研究。

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 會一直向右匹配直到遇到範圍查詢(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引則都可以用到,a,b,d的順序可以任意調整。
  • = 和 in 可以亂序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序,mysql的查詢優化器會幫你優化成索引可以識別的形式。

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

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

8. 什麼是覆蓋索引

  • 覆蓋索引(covering index)指一個查詢語句的執行只用從索引頁中就能夠取得(如果不是聚集索引,葉子節點儲存的是主鍵+列值,最終還是要回表,也就是要通過主鍵再查詢一次),避免了查到索引後,再做回表操作,減少I/O提高效率。
  • 可以結合第10個問題更容易理解。

9. 什麼是字首索引

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

建立字首索引的語法:

ALTER TABLE table_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輔助索引在葉子節點上儲存的是資料記錄的地址,和主鍵索引一樣,所以不需要再遍歷一次主鍵索引)。

簡單的說:

  1. 主索引的區別:InnoDB的資料檔案本身就是索引檔案。而MyISAM的索引和資料是分開的。

  2. 輔助索引的區別: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
  • Seq_in_index

    • 該列在索引中的序號,從 1 開始。例如:存在聯合索引 idx_a_b_c (a,b,c),則a的Seq_in_index=1,b=2,c=3。
  • Column_name

    • 列名。
  • Collation

    • 索引的排列順序:A(ascending),D (descending),NULL (not sorted)。
  • Cardinality

    • 一個衡量該索引的唯一程度的值,可以使用ANALYZE TABLE(INNODB) 或者 myisamchk -a(MyISAM)更新該值。
    • 如果表記錄太少,該欄位的意義不大。一般情況下,該值越大,索引效率越高。
  • Sub_part

    • 對於字首索引,用於索引的字元個數。如果整個欄位都加上了索引,則顯示為NULL
  • Null

    • YES:該列允許NULL值。
    • '':該列不允許NULL值。
  • Index_type

    • 索引型別,包括(BTREE, FULLTEXT, HASH, RTREE)。

14. 如何解決like'%字串%'時索引失效?

  • LIKE問題:like 以萬用字元開頭 ('%abc...'),mysql索引失效會變成全表掃描的操作。
    • 罪魁禍首是%,不是LIKELIKE 條件是 type = range 級別
    • %xxx%:全表掃描
    • %xxx:全表掃描
    • xxx%:range
  • 解決辦法
    • 使用覆蓋索引,可以由 ALL 變為INDEX,為啥呢?覆蓋索引之後就能使用使用索引進行全表掃描。這裡要注意一下,使用符合索引的時候,命中一個欄位就可以,不用全部命中。

15. MySQL高效分頁

  • 存在SQL:SELECT * FROM ttl_product_info ORDER BY id LIMIT N,M。其中 LIMIT N,M 存在的問題最大:取出N+M行,丟棄前N行,返回 N ~ N+M 行的記錄,如果N值非常大,效率極差(表記錄1500w,N=10000000,M=30 需要9秒)。
  • 解決辦法:SQL:SELECT id FROM ttl_product_info WHERE id > N LIMIT M,id 列是索引列,id > N屬於 range 級別,效率自然高,然後從位置開始取30條記錄,效率極高(表記錄1500w,N=10000000,M=30,需要0.9毫秒)。
  • 當然想要實現上述效果的前提是:
    1. id是唯一索引,而且單調遞增。
    2. N 的值是上一次查詢的記錄的最後一條id,(需要前端儲存一下,不能直接用傳統的方法獲得)
    3. 不支援跨頁查詢,只能按照第1,2,3,4頁這樣查詢逐頁查詢。

總結

為了保持文章結構的完整性,這裡強行加上一段總結。。。

參考文章:

相關文章