面試官:談談你對mysql索引的認識?
引言
大家好,我渣渣煙。我曾經寫過一篇《面試官:談談你對錶設計的認識?》於是呢,決定再來一個mysql的資料庫專題,這篇我們就來談談關於索引方面的mysql面試題。還是老規矩,講的是在Innodb儲存引擎下的情形,畢竟我還真沒用過Mysiam之類的儲存引擎。ps
:其實很早就想寫了,一直偷懶!
其實這下面每個問題,我都可以講一篇文章出來!而且這些問題,不是我憑空編的。如下圖所示(注意看第三題)
所以我回憶了一下,索引常見考點有哪些,總結成了這篇文章!
主要題目有下面這些
-
(1)你一般怎麼建索引的?
-
(2)講講索引的分類?你知道哪些?
-
(3)如何避免回表查詢?什麼是索引覆蓋?
-
(4)現在我有一個列,裡頭的資料都是唯一的,需要建一個索引,選唯一索引還是普通索引?
-
(5)mysql索引是什麼結構的?用紅黑樹可以麼?
-
(6)mysql某表建了多個單索引,查詢多個條件時如何走索引的?
正文
1、你一般怎麼建索引的?煙哥注:
曾記得有一個粉絲來找我的時候,出現如下搞笑一幕
渣渣煙:"你這個簡歷上寫了擁有SQL優化經驗,你怎麼建索引的?"
只見該粉絲嘿嘿一笑..說道:"就那樣建啊…"
渣渣煙:"噢(第二聲),就哪樣建啊…"
粉絲:"…就網上說的那些索引規則啊"
渣渣煙:"那你怎麼知道那些SQL出問題,需要建索引呢?"粉絲:"我….."
嗯,這道題其實很基礎。但是有沒有做過,這題是可以看出來的。
開啟慢查詢日誌
slow_query_log=1
慢查詢日誌儲存路徑
slow_query_log_file=/var/log/mysql/log-slow-queries.log
SQL執行時間大於3秒,則記錄日誌
long_query_time=3
監控到慢SQL後,就馬上開始建索引?例如,當只要一行資料時使用 limit 1
然而大多數情況下,業務SQL十分複雜,沒法優化。所以就要建立索引了。這個時候,參照如下規則建立索引
-
(1)索引並非越多越好,大量的索引不僅佔用磁碟空間,而且還會影響insert,delete,update等語句的效能
-
(2)避免對經常更新的表做更多的索引,並且索引中的列儘可能少;對經常用於查詢的欄位建立索引,避免新增不必要的索引
-
(3)資料量少的表儘量不要使用索引,由於資料較少,查詢花費的時間可能比遍歷索引的時間還要短,索引可能不會產生優化效果
-
(4)在條件表示式中經常用到不同值較多的列上建立索引,在不同值很少的列上不要建立索引。比如性別欄位只有“男”“女”倆個值,就無需建立索引。如果建立了索引不但不會提升效率,反而嚴重減低資料的更新速度
-
(5)在頻繁進行排序或者分組的列上建立索引,如果排序的列有多個,可以在這些列上建立聯合索引。
2、講講索引的分類?你知道哪些?
從物理儲存角度:
聚簇索引和非聚簇索引
從資料結構角度:
B+樹索引、hash索引、FULLTEXT索引、R-Tree索引
從邏輯角度:
-
主鍵索引:主鍵索引是一種特殊的唯一索引,不允許有空值
-
普通索引或者單列索引
-
多列索引(複合索引):複合索引指多個欄位上建立的索引,只有在查詢條件中使用了建立索引時的第一個欄位,索引才會被使用。使用複合索引時遵循最左字首集合
-
唯一索引或者非唯一索引
-
空間索引:空間索引是對空間資料型別的欄位建立的索引,MYSQL中的空間資料型別有4種,分別是GEOMETRY、POINT、LINESTRING、POLYGON。
3、如何避免回表查詢?什麼是索引覆蓋?
這個問題,如果要看詳細版,請參閱文章《Innodb中索引的原理》
這裡簡單說一下。
例如此時有一張表table1
,有一個聯合索引(a,b)
執行如下SQL
select a,b from table1
在索引上就能找到結果,就不用回表去查詢!
select a,b,c from table2
c列在索引上不存在,就需要回表查詢。
需要說明的是覆蓋索引必須要儲存索引列的值,而雜湊索引、空間索引和全文索引不儲存索引列的值,所以mysql只能用B+ tree索引做覆蓋索引。
4、現在我有一個列,裡頭的資料都是唯一的,需要建一個索引,選唯一索引還是普通索引?
【強制】業務上具有唯一特性的欄位,即使是多個欄位的組合,也必須建成唯一索引
說明:不要以為唯一索引影響了 insert 速度,這個速度損耗可以忽略,但提高查詢速度是明顯的;另外,即使在應用層做了非常完善的校驗控制,只要沒有唯一索引,根據墨菲定律,必然有髒資料產生。
那好,下一問出現了!
為什麼唯一索引的插入速度比不上普通索引?為什麼唯一索引的查詢速度比普通索引快?
這麼做的優點:能將多個插入合併到一個操作中,就大大提高了非聚簇索引的插入效能。
InnoDB 從 1.0.x 版本開始引入了 Change Buffer,可以算是對 Insert Buffer 的升級。從這個版本開始,InnoDB 儲存引擎可以對 insert、delete、update 都進行快取。
唯一速度的插入比普通索引慢的原因就是:
-
唯一索引無法利用Change Buffer
-
普通索引可以利用Change Buffer
於是乎下一問又來了!為什麼唯一索引的更新不使用 Change Buffer?
因為唯一索引為了保證唯一性,需要將資料頁載入進記憶體才能判斷是否違反唯一性約束。但是,既然資料頁都載入到記憶體了,還不如直接更新記憶體中的資料頁,沒有必要再使用Change Buffer。
最後回答一下,唯一索引的搜尋速度比普通索引快的原因就是:
-
普通索引在找到滿足條件的第一條記錄後,還需要判斷下一條記錄,直到第一個不滿足條件的記錄出現。
-
唯一索引在找到滿足條件的第一條記錄後,直接返回,不用判斷下一條記錄了。
5、mysql索引是什麼結構的?用紅黑樹可以麼?
那為啥不用B Tree,而選擇B+ tree呢?
注意一下B tree的兩個明顯特點
-
樹記憶體儲資料
-
葉子節點上無連結串列
而B+ tree長下面這樣的
注意一下B+ tree的兩個明顯特點
-
資料只出現在葉子節點
-
所有葉子節點增加了一個鏈指標
接下來就可以開始編了~~比如資料庫索引採用B+ tree的主要原因是B Tree在提高了磁碟IO效能的同時並沒有解決元素遍歷的效率低下的問題。正是為了解決這個問題,B+ tree應運而生。B+ tree只要遍歷葉子節點就可以實現整棵樹的遍歷。而且在資料庫中基於範圍的查詢是非常頻繁的,如果使用B Tree,則需要做區域性的中序遍歷,可能要跨層訪問,效率太慢。
提示,我下一問就是:
6、mysql某表建了多個單索引,查詢多個條件時如何走索引的?
這裡希望大家先看看我的另一篇文章
Mysql在優化器中有一個優化器稱為Range 優化器,負責進行範圍查詢的優化!
它們是MySQL優化器對開銷代價的估算方法,前者統計速度慢但是能得到精準的值,後者統計速度快但是資料未必精準。
坦白說寫到這裡,我內心痛哭流涕,要把index dive和index statistics寫明白,真不是一件容易的事,這裡只能稍微扯扯。
對於index dive:
COST = CPU COST + IO COST
其中CPU COST指的是處理返回記錄所花的開銷。而IO COST指的是讀取頁面的開銷。
mysql會對每種索引的執行情況,進行上述成本計算,最後以成本小的方式進行執行。
但是呢,在某些情況下mysql執行index dive的成本太大。因此優化器會選擇以index statistics方式進行估算成本。
SHOW INDEX FROM tbl_name [FROM db_name]
此時出來的結果中,有一列名為Cardinality
,該值表示索引列中不重複值的個數。Cardinality
值越大,就意味著,使用索引能排除越多的資料,執行也更為高效。
相關文章
- Java高頻面試題:談談你對MySQL索引的瞭解Java面試題MySql索引
- 【搞定面試官】談談你對JDK中Executor的理解?面試JDK
- 面試官:談談你對JVM垃圾收集器的瞭解面試JVM
- C++——談談你對物件導向程式設計的認識C++物件程式設計
- 面試官:談談你對JVM垃圾收集器演算法的瞭解面試JVM演算法
- 【雜談】對CopyOnWriteArrayList的認識
- 面試——談談你對Java 平臺的理解面試Java
- 面試官:談談你對Mysql資料庫讀寫分離的瞭解,並且有哪些注意事項?面試MySql資料庫
- 淺談MySQL日誌檔案|手撕MySQL|對線面試官MySql面試
- 面試官:談談你對SpringAOP的瞭解?請加上這些內容,絕對加分!面試Spring
- 談談對資料架構的幾點認識架構
- 【Java 容器面試題】談談你對HashMap 的理解Java面試題HashMap
- 面試官:聊一下你對MySQL索引實現原理?面試MySql索引
- 【手把手帶你配 webpack】第二步, 面試官-談談你對模組化的理解Web面試
- 面試——談談你對Java 物件導向思想的理解面試Java物件
- 京東面試官讓你談談 zookeeper 和 eureka 哪個更好使?面試
- 面試官:談一下你對DDD的理解?我:馬什麼梅?面試
- 月薪不同的三人去面試,面試官問道:各自談談對 binder 的理解?面試
- 談談Markdown的認識與入門
- 【雜談】對RMI(Remote Method Invoke)的認識REM
- 淺談Mysql索引MySql索引
- 漫談mysql索引MySql索引
- 談談你對Promise的理解Promise
- JAVA面試題 請談談你對Sychronized關鍵字的理解?Java面試題Zed
- 【面試普通人VS高手系列】談談你對Seata的理解面試
- 對線面試官:Mysql組合索引的生效規則面試MySql索引
- 面試官:來談談限流-RateLimiter原始碼分析面試MIT原始碼
- MySQL淺談(索引、鎖)MySql索引
- 【大廠面試06期】談一談你對Redis持久化的理解?面試Redis持久化
- 談談MYSQL索引是如何提高查詢效率的MySql索引
- 談談面試知識點準備面試
- 面試官問我MySQL索引,我面試MySql索引
- 淺談對CSRF的認識,以及一些應對措施
- 面試官說:來談談限流-從概念到實現,一問你就懵逼了?面試
- 面試官:談談Redis快取和MySQL資料一致性問題面試Redis快取MySql
- 【搞定Jvm面試】 面試官:談談 JVM 類載入過程是怎樣的?JVM面試
- 曾奇:談談我所認識的分散式鎖分散式
- Java面試題:請談談對ThreadLocal的理解?Java面試題thread