首頁  >  文章  >  資料庫  >  MySQL優化及索引的方法

MySQL優化及索引的方法

PHPz
PHPz轉載
2023-06-02 13:58:211068瀏覽

索引簡單介紹

索引的本質:

  • MySQL索引或說其他關係型資料庫的索引的本質就只有一句話,以空間換時間。

索引的作用:

  • #索引關係型資料庫為了加速對資料表中的行資料擷取(磁碟儲存的)資料結構

索引的分類

資料結構上面的分類:

  • HASH 索引

    • 等值符合效率高

    • #不支援範圍查找

  • 樹狀索引

    • 二元樹,遞迴二分查找法,左小右大

    • 平衡二元樹,二元樹到平衡二元樹,主要原因是左旋右旋

    • 缺點1,IO次數過多

    • 缺點2,IO利用率不高,IO飽和度

  • 多路平衡找出樹(B-Tree)

    • 特點,大大的減少了樹的高度

  • B 樹

    • #特點,採用左閉合的比較方式

    • 根節點支節點沒有資料區,只有葉子結點才包含資料區(說白了就是即便在根節點和子節點已經定位到,因為沒有資料區的原因也不會停留,會一直找到葉子結點為止。)

當我們搜尋13這條資料時,在根節點和子節點都能定位,但一直會找到葉子結點。

MySQL優化及索引的方法

二元樹平衡二元樹,B樹對比:

如圖顯示如果是自增主鍵情況下:

二元樹顯然不適合做關係型資料庫索引(和全表掃描沒什麼差別)。

平衡二元樹呢,雖然解決了這種情況,但是同樣會導致這棵樹,又瘦又高,這同樣會造成上文所提到查詢IO次數過多以及IO利用率不高。

B樹呢,顯然已經解決了這兩個問題,所以下文來解釋,為什麼在這種情況下MySQL還用了B 樹,又做了那些增強。

MySQL優化及索引的方法

B樹與B 樹比較:

MySQL優化及索引的方法

B樹在B樹上面的最佳化:

IO效率更高(B樹每個節點都會保留資料區,而B 樹則不會,假設我們查詢一條資料要遍歷三層,那麼顯然B 樹查詢中IO消耗更小)

範圍查找效率更高(如圖,B 樹已經形成了一個天然鍊錶形式,只需要根據最結尾的鍊式結構查找)

MySQL優化及索引的方法

基於索引的資料掃描效率更高。

索引類型的分類

索引類型可分為兩類:

  • #主鍵索引

  • #輔佐索引(二級索引)

    • 唯一性索引

    • 複合索引

    • #普通索引

    • 覆蓋索引

#雖然主鍵索引效能相對最佳,但通常在SQL最佳化中,我們會在輔助索引上進行改進和補充。

B 樹在儲存引擎層級落地

  • 我們建立兩個表分別為test_innodb(採用InnoDB作為儲存引擎)test_myisam (採用MyISAM作為儲存引擎)下圖是兩張表格磁碟落地的相關文件,這兩個儲存引擎在B 樹磁碟落地式截然不同的。

MySQL優化及索引的方法

B 樹在MyISAM落地:

  • ##*.frm檔是表格骨架檔案例如這個表中的id字段name欄位是什麼類型的儲存在這裡

  • *.MYD(D=data)則儲存資料

  • *.MYI (I=index)則儲存索引

MySQL優化及索引的方法

  • #例如現在執行如下sql語句,那麼在MyISAM中他就是先在test_myisam.MYI中查找到103然後拿到0x194281這個位址然後再去test_myisam.MYD中找到這個資料回傳。

  • SELECT id,name from test_myisam where id =103

MySQL優化及索引的方法

  • 如果test_myisam表中,id为主键索引,name也是一个索引,那么在test_myisam.MYI中则会有两个平级的B+树,这也导致MyISAM引擎中主键索引和二级索引是没有主次之分的,是平级关系。因为这种机制在MyISAM引擎中,有可能使用多个索引,在InnoDB中则不会出现这种情况。

B+树在InnoDB落地:

MySQL優化及索引的方法

MySQL優化及索引的方法

  • InnoDB不像MyISAM来独立一个MYD 文件来存储数据,它的数据直接存储在叶子结点关键字对应的数据区在这保存这一个id列所有行的详细记录。

  • InnoDB 主键索引和辅助索引关系

我们现在执行如下SQL语句,他会先去找辅助索引,然后找到辅助索引下101的主键,再去回表(二次扫描)根据主键索引查询103这条数据将其返回。

SELECT id,name from test_myisam where name ='zhangsan'

这里就有一个问题了,为什么不像MyISAM在辅助索引下直接记录磁盘地址,而是要多此一举再去回表扫描主键索引,这个问题在下面相关面试题中回答,记一下这个问题是这里来的。

MySQL優化及索引的方法

相关面试题

  • 为什么MySQL选择B+树作为索引结构

这个就不说了,上文应该讲清楚了。

  • B+树在MyISAM和InnoDB落地区别。

这个可以总结一下,MyISAM落地数据储存会有三个类型文件 ,.frm文件是表骨架文件,.MYD(D=data)则储存数据 ,.MYI (I=index)则储存索引,MyISAM引擎中主键索引和二级索引平级关系,在MyISAM引擎中,有可能使用多个索引,InnoDB则相反,主键索引和二级索有严格的主次之分在InnoDB一条语句只能用一个索引要么不用。

  • 如何判断一条sql语句是否使用了索引。

可以通过执行计划来判断 可以在sql语句前explain/ desc

set global optimizer_trace='enabled=on' 打开执行计划开关他将会把每一条查询sql执行计划记录在information_schema 库中OPTIMIZER_TRACE表中

  • 为什么主键索引最好选择自增列?

自增列,数据插入时整个索引树是只有右边在增加的,相对来说索引树的变动更小。

  • 为什么经常变动的列不建议使用索引?

和上一个问题原因一样,当一个索引经常发生变化,那么就意味这,这个缩印树也要经常发生变化。4

  • 为什么说重复度高的列,不建议建立索引?

这个原因是因为离散性,比如说,一张一百万数据的表,其中一个字段代表性别,0代表男1代表女,把这字段加了索引,那么在索引树上,将会有大量的重复数据。而我们常见的索引建立一般都是驱动型的。其目的是,尽可能的删减数据的查询范围,这个显然是不匹配的。

  • 什么是联合索引

联合索引是一个包含了多个功效的索引,他只是一个索引而不是多个,

其次,单列索引是一种特殊的联合索引

联合索引的创立要遵循最左前置原则(最常用列>离散度>占用空间小)

  • 什么是覆盖索引

通过索引项信息可直接返回所需要查询的索引列,该索引被称之为覆盖索引,说白了就是不需要做回表操作,可以从二级索引中直接取到所需数据。

  • 什么是ICP机制

索引下推,简单点来说就是,在sql执行过程中,面对where多条件过滤时,通过一个索引,完成数据搜索和过滤条件其,特点能减少io操作。

  • 在InnoDB表中不可能没有主键对还是不对原因是什么?

首先这句话是对的,但是情况有三种:

  • 就是在你手動明確指定這一個欄位為主鍵時候,會以這一個欄位為聚集索引。

  • 在沒有明確指定主鍵時候有兩種情況:

  • 他會尋找第一個UK(unique key)作為主鍵索引組織索引編排。

  • 如果既沒有指定主鍵也沒有UK的情況下,此時會以rowId(在InnoDB表中每一個記錄都會有一個隱藏(6byte)的rowId)為聚集索引。

  • 什麼是回表操作

#在InnoDB 中基於輔助索引查詢的內容,從輔助索引中無法直接取得,需要基於主鍵索引的二次掃描的操作叫做回表運算。

  • 為什麼在InnoDB 中輔助索引葉子結點資料區記錄的是主鍵索引的值而不是像MyISAM中去記錄磁碟位址。

這個原因其實很簡單,因為主鍵索引的資料結構是會經常改變的,如果在輔助索引資料區記錄磁碟位址,那麼假設我們有10個輔助索引,當我們主鍵索引結構改變後,還要一個個去通知輔助索引,且主鍵索引結構是經常發生變化的,增刪都有可能影響他的
資料結構。

以上是MySQL優化及索引的方法的詳細內容。更多資訊請關注PHP中文網其他相關文章!

陳述:
本文轉載於:yisu.com。如有侵權,請聯絡admin@php.cn刪除