MySQL索引優化深入

来源:https://www.cnblogs.com/ZekiChen/archive/2020/02/15/12304913.html
-Advertisement-
Play Games

創建 test 測試表 CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c1` varchar(10) DEFAULT NULL, `c2` varchar(10) DEFAULT NULL, `c3` varchar(10) ...


創建 test 測試表

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` varchar(10) DEFAULT NULL,
  `c2` varchar(10) DEFAULT NULL,
  `c3` varchar(10) DEFAULT NULL,
  `c4` varchar(10) DEFAULT NULL,
  `c5` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_test_c1234` (`c1`,`c2`,`c3`,`c4`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into `test` (`id`, `c1`, `c2`, `c3`, `c4`, `c5`) values('1','a1','a2','a3','a4','a5');
insert into `test` (`id`, `c1`, `c2`, `c3`, `c4`, `c5`) values('2','b1','b2','b3','b4','b5');
insert into `test` (`id`, `c1`, `c2`, `c3`, `c4`, `c5`) values('3','c1','c2','c3','c4','c5');
insert into `test` (`id`, `c1`, `c2`, `c3`, `c4`, `c5`) values('4','d1','d2','d3','d4','d5');
insert into `test` (`id`, `c1`, `c2`, `c3`, `c4`, `c5`) values('5','e1','e2','e3','e4','e5');

分析以下Case索引使用情況

Case 1

執行以下SQL語句:

① EXPLAIN SELECT * FROM test WHERE c1='a1' AND c2='a2' AND c3='a3' AND c4='a4'
② EXPLAIN SELECT * FROM test WHERE c1='a1' AND c3='a3' AND c2='a2' AND c4='a4'
③ EXPLAIN SELECT * FROM test WHERE c1='a1' AND c4='a4' AND c3='a3' AND c2='a2'
④ EXPLAIN SELECT * FROM test WHERE c4='a4' AND c2='a2' AND c3='a3' AND c1='a1'

分析:創建聯合索引的順序為c1,c2,c3,c4,上述四組explain執行結果都一樣:type=ref,key_len=132,ref=const,const,const,const。

結論:在執行常量等值查詢時,改變索引列的順序並不會更改explain的執行結果,因為MySQL底層優化器會自動進行優化,但還是推薦按照索引順序列編寫SQL語句。

Case 2

執行SQL語句:EXPLAIN SELECT * FROM test WHERE c1='a1' AND c2='a2'

執行SQL語句:EXPLAIN SELECT * FROM test WHERE c1='a1' AND c2='a2' AND c3>'a3' AND c4='a4'

分析:當出現範圍的時候,type=range,key_len=99,比不用範圍key_len=66增加了,說明使用上了索引,但對比Case 1中的執行結果,說明c4上s索引失效。

結論:範圍右邊索引列失效,但是範圍當前位置(c3)的索引是有效的,從key_len=99可證明。

Case 2.1

執行SQL語句:EXPLAIN SELECT * FROM test WHERE c1='a1' AND c2='a2' AND c4>'c4' AND c3='a3'

分析:與上面explain執行結果對比,key_len=132說明索引用到了4個,因此對此SQL語句MySQL底層優化器會進行優化(優化成WHERE c1='a1' AND c2='a2' AND c3='a3' AND c4>'c4'):範圍右邊索引列失效(c4右邊已經沒有索引列了),註意索引的順序(c1,c2,c3,c4),所以c4右邊不會出現失效的索引列,因此4個索引全部用上。

結論:範圍右邊索引列失效,是有順序的:c1,c2,c3,c4,如果c3有範圍,則c4失效;如果c4有範圍,則沒有失效的索引列,從而會使用全部索引。

Case 2.2:(聲明:這個Case的解釋有待考察)

執行SQL語句:EXPLAIN SELECT * FROM test WHERE c1>'a1' AND c2='a2' AND c3='c3' AND c4='a4'

分析:如果在c1處使用範圍,則type=ALL,key=NULL,索引失效,全表掃描,這裡違背了最佳左首碼原則,帶頭大哥已死,因為c1主要用於範圍,而不是查詢。

解決方式:使用覆蓋索引。

結論:在索引最佳左首碼原則中,如果最左前列(帶頭大哥)的索引失效,則後面的索引失效。

Case 3

執行SQL語句:EXPLAIN SELECT * FROM test WHERE c1='a1' AND c2='a2' AND c4='a4' ORDER BY c3

分析:利用最佳左首碼原則:中間兄弟不能斷,因此用到了c1和c2索引(查找),從key_len=66,ref=const,const可以看出來,c3索引列也用在order by排序過程中(即也用到了c3索引)。

提問:如何證明order by c3也用到了索引?

執行SQL語句:EXPLAIN SELECT * FROM test WHERE c1='a1' AND c2='a2' AND c4='a4' ORDER BY c5

分析:因為c5非索引欄位,當用order by c5排序時,extra列出現了Using filesort,用到了文件排序,代表沒有使用索引排序,性能低。

Case 3.1

執行SQL語句:EXPLAIN SELECT * FROM test WHERE c1='a1' AND c2='a2' ORDER BY c3

分析:從key_len=66,ref=const,const可以看出來,查找只用到了c1和c2索引,c3索引用於排序。

Case 3.2

執行SQL語句:EXPLAIN SELECT * FROM test WHERE c1='a1' AND c2='a2' ORDER BY c4

分析:從key_len=66,ref=const,const可以看出來,查詢使用了c1和c2索引,由於使用了c4進行排序,跳過了c3,中間斷了,也無法使用c4的索引進行排序,出現了Using filesort。

Case 4

執行SQL語句:EXPLAIN SELECT * FROM test WHERE c1='a1' AND c5='a5' ORDER BY c2,c3

分析:查找只用到索引c1,c2和c3索引用於排序,無Using filesort。

Case 4.1

執行SQL語句:EXPLAIN SELECT * FROM test WHERE c1='a1' AND c5='a5' ORDER BY c3,c2

分析:和Case 4中explain的執行結果一樣,但是出現了Using filesort,因為索引的創建順序為c1,c2,c3,c4,但是排序的時候c2和c3顛倒位置了。

Case 4.2

執行SQL語句:EXPLAIN SELECT * FROM test WHERE c1='a1' AND c2='a2' ORDER BY c2,c3

執行SQL語句:EXPLAIN SELECT * FROM test WHERE c1='a1' AND c2='a2' AND c5='a5' ORDER BY c2,c3

分析:在查詢時增加了c5,但是explain的執行結果一樣,因為c5並未創建索引。

Case 4.3

執行SQL語句:EXPLAIN SELECT * FROM test WHERE c1='a1' AND c2='a2' AND c5='a5' ORDER BY c3,c2

分析:與Case 4.1相比,在Extra中並未出現Using filesort,因為c2是常量,在排序中被優化,所以索引未顛倒,不會出現Using filesort。

Case 5

執行SQL語句:EXPLAIN SELECT * FROM test WHERE c1='a1' AND c4='a4' GROUP BY c2,c3

分析:如果gourp by沒有使用索引,會導致生成臨時表(Using temporary),底層會先用order by排序,要想group by使用索引分組,前提條件是滿足order by使用索引排序。上面只用到c1上的索引進行查詢,因為c4中間斷了,根據索引最左首碼原則,索引key_len=33,ref=const,表示只用到一個索引。

Case 5.1

執行SQL語句:EXPLAIN SELECT * FROM test WHERE c1='a1' AND c4='a4' GROUP BY c3,c2

分析:對比Case 5,在group by時交換了c2和c3的位置,導致無法滿足order by(Using filesort),即無法滿足group by(Using temporary),極度惡劣。原因:c3和c2與索引創建順序相反。

Case 6

執行SQL語句:EXPLAIN SELECT * FROM test WHERE c1>'a1' ORDER BY c1

分析:

① 在c1,c2,c3,c4上創建了索引,直接在c1上使用範圍,導致了索引失效,全表掃描:type=ALL,ref=NULL。因為此時c1主要用於排序,並不是查詢。

② 使用c1進行排序,出現了Using filesort。

③ 解決方法:使用覆蓋索引。

執行SQL語句:EXPLAIN SELECT c1 FROM test WHERE c1>'a1' ORDER BY c1

Case 7

執行SQL語句:

EXPLAIN SELECT c1 FROM test ORDER BY c1 ASC, c2 DESC

分析:雖然排序的欄位列與索引順序一樣,且order by預設升序,這裡c2 DESC變成了降序,導致與索引的排序方式不同,從而產生Using filesort。

Case 8:

執行SQL語句:EXPLAIN SELECT c1 FROM test WHERE c1 IN('a1','b1') ORDER BY c2,c3

分析:對於排序來說,多個相等條件也是範圍查詢。

總結: 

① MySQL支持兩種方式的排序filesort和index,Using index是指MySQL掃描索引本身完成排序。index效率高,filesort效率低。

② order by滿足兩種情況會使用Using index:

a. order by語句使用索引最左前列。

b. 使用where子句與order by子句條件列組合滿足索引最左前列。

③ 儘量在索引列上完成排序,遵循索引建立(索引創建的順序)時的最佳左首碼原則。

④ 如果order by的條件不在索引列上,就會產生Using filesort。

⑤ group by與order by很類似,其實質是先排序後分組,遵照索引創建順序的最佳左首碼原則。註意where高於having,能寫在where中的限定條件就不要去having限定了。

通俗理解口訣

全值匹配我最愛,最左首碼我的菜;

帶頭大哥不能死,中間兄弟斷狗帶;

索引列上少計算,範圍之後全完蛋;

覆蓋索引不寫星,Like百分右邊站;

不等空值還有or,索引失效要少用。

補充:in和exists優化

原則:小表驅動大表,即小的數據集驅動大的數據集。

in:當B表的數據集小於A表的數據集時,in優於exists

select * from A where id in (select id from B)

等價於:

for select id from B

for select * from A where A.id=B.id

exists:當A表的數據集小於B表的數據集時,exists優於in

select * from A where exists (select 1 from B where B.id=A.id)

等價於:

for select * from A

for select * from B where B.id=A.id

(A表與B表的id欄位應建立索引)


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 問題: 由於長時間沒有操作過自己安裝的虛擬機,導致連root用戶的密碼都忘了,也真是沒誰了。 我使用的是centos6.5,今天就記錄一下,如何重置root用戶的密碼。 操作步驟: 步驟一: 當開機啟動出現啟動的圖標的時候,這時快速按下Esc或者E,一定要快點,不然總是來不及,也可以啟動後就一直點。 ...
  • API:application program interface ABI:application binary interface linux系統的ABI文件是ELF格式的 windows系統的ABI文件是exe,msi格式的 系統級開發語言:c/c++ ​ 作品:httpd,vsftpd,ngi ...
  • ARM 前幾天剛發佈了 Cortex-M 家族最新一款內核 - Cortex-M55 以及首款面向 Cortex-M 系列的 microNPU - Ethos-U55。Cortex-M55 是第一款面向 AI/ML 的 Cortex-M 內核,痞子衡也專門為此寫過一篇小文 《為AI/ML而生(Cor... ...
  • 上一篇文章學習了用戶及文件相關許可權,本篇繼續學習防火牆技術。 ...
  • 一、 使用Java操作Redis前,請先運行Redis服務與下載Redis驅動,以maven工程為例,引入如下jar <dependency> <groupId>redis.clients</groupId> <artifactId>jedis</artifactId> <version>2.9.0 ...
  • 這是高級開發者面試時經常被問的問題。實際我們在平時的開發中,經常會遇到的,在用SQLyog等工具創建表時,就有一個引擎項要你去選。如下圖: Mysql的存儲引擎有這麼多種,實際我們在平時用的最多的莫過於InnoDB和MyISAM了。 所有如果面試官問道mysql有哪些存儲引擎,你只需要告訴這兩個常用 ...
  • 大數據技術體系的知識量是比較大的,而且涉及到的內容也具有一定的難度,對於初學者的知識結構還是有一定要求的。通常來說,要想學習大數據技術,需要具有一定的數學和電腦基礎,如果具有一定的統計學基礎會更好一些。 ...
  • 問題概述 "新冠期間"遠程辦公,需要重新搭建一套ClouderaManager(CM)開發環境,一位測試同事發現HBase的RegionServer無法啟動,在CM界面上啟動總是失敗,觀察一下日誌,也沒有什麼明顯的報錯。我就專門看了一下。 排查思路 1. 因為有opentsdb在讀寫Hbase Re ...
一周排行
    -Advertisement-
    Play Games
  • Timer是什麼 Timer 是一種用於創建定期粒度行為的機制。 與標準的 .NET System.Threading.Timer 類相似,Orleans 的 Timer 允許在一段時間後執行特定的操作,或者在特定的時間間隔內重覆執行操作。 它在分散式系統中具有重要作用,特別是在處理需要周期性執行的 ...
  • 前言 相信很多做WPF開發的小伙伴都遇到過表格類的需求,雖然現有的Grid控制項也能實現,但是使用起來的體驗感並不好,比如要實現一個Excel中的表格效果,估計你能想到的第一個方法就是套Border控制項,用這種方法你需要控制每個Border的邊框,並且在一堆Bordr中找到Grid.Row,Grid. ...
  • .NET C#程式啟動閃退,目錄導致的問題 這是第2次踩這個坑了,很小的編程細節,容易忽略,所以寫個博客,分享給大家。 1.第一次坑:是windows 系統把程式運行成服務,找不到配置文件,原因是以服務運行它的工作目錄是在C:\Windows\System32 2.本次坑:WPF桌面程式通過註冊表設 ...
  • 在分散式系統中,數據的持久化是至關重要的一環。 Orleans 7 引入了強大的持久化功能,使得在分散式環境下管理數據變得更加輕鬆和可靠。 本文將介紹什麼是 Orleans 7 的持久化,如何設置它以及相應的代碼示例。 什麼是 Orleans 7 的持久化? Orleans 7 的持久化是指將 Or ...
  • 前言 .NET Feature Management 是一個用於管理應用程式功能的庫,它可以幫助開發人員在應用程式中輕鬆地添加、移除和管理功能。使用 Feature Management,開發人員可以根據不同用戶、環境或其他條件來動態地控制應用程式中的功能。這使得開發人員可以更靈活地管理應用程式的功 ...
  • 在 WPF 應用程式中,拖放操作是實現用戶交互的重要組成部分。通過拖放操作,用戶可以輕鬆地將數據從一個位置移動到另一個位置,或者將控制項從一個容器移動到另一個容器。然而,WPF 中預設的拖放操作可能並不是那麼好用。為瞭解決這個問題,我們可以自定義一個 Panel 來實現更簡單的拖拽操作。 自定義 Pa ...
  • 在實際使用中,由於涉及到不同編程語言之間互相調用,導致C++ 中的OpenCV與C#中的OpenCvSharp 圖像數據在不同編程語言之間難以有效傳遞。在本文中我們將結合OpenCvSharp源碼實現原理,探究兩種數據之間的通信方式。 ...
  • 一、前言 這是一篇搭建許可權管理系統的系列文章。 隨著網路的發展,信息安全對應任何企業來說都越發的重要,而本系列文章將和大家一起一步一步搭建一個全新的許可權管理系統。 說明:由於搭建一個全新的項目過於繁瑣,所有作者將挑選核心代碼和核心思路進行分享。 二、技術選擇 三、開始設計 1、自主搭建vue前端和. ...
  • Csharper中的表達式樹 這節課來瞭解一下表示式樹是什麼? 在C#中,表達式樹是一種數據結構,它可以表示一些代碼塊,如Lambda表達式或查詢表達式。表達式樹使你能夠查看和操作數據,就像你可以查看和操作代碼一樣。它們通常用於創建動態查詢和解析表達式。 一、認識表達式樹 為什麼要這樣說?它和委托有 ...
  • 在使用Django等框架來操作MySQL時,實際上底層還是通過Python來操作的,首先需要安裝一個驅動程式,在Python3中,驅動程式有多種選擇,比如有pymysql以及mysqlclient等。使用pip命令安裝mysqlclient失敗應如何解決? 安裝的python版本說明 機器同時安裝了 ...