【MySQL】SQL 優化

MySQL - SQL 優化

1. 在 MySQL 中,如何定位慢查詢?

1.1 發現慢查詢

現象:頁面加載過慢、接口壓力測試響應時間過長(超過 1s)

可能出現慢查詢的場景:

  • 聚合查詢
  • 多表查詢
  • 表數據過大查詢
  • 深度分頁查詢

1.2 通過現象定位到問題是出在 MySQL 的慢查詢(查看慢日志法)

找到配置文件:

Windows:my.ini

在這里插入圖片描述

Linux:/etc/my.cnf

在這里插入圖片描述

添加或修改兩個屬性:

  1. slow_query_log=1(1 為 true,開啟慢日志)
  2. long_query_time=2(單位為秒,超過 2 秒的將記錄在慢日志)

選擇性添加或修改,slow_query_log_file 屬性,慢日志名

  • Linux 在/var/lib/mysql/localhost-slow.log
  • Windows 見配置文件默認值

重啟 MySQL:

  • Windows

    • net stop MySQL
      
    • net start MySQL
      
  • Linux

    • sudo systemctl stop mysql
      
    • sudo systemctl start mysql
      

發現問題,找到對應的慢日志,定位到問題是出在 MySQL 的慢查詢:

在這里插入圖片描述

1.3 回答問題

  1. 介紹一下當時產生問題的場景(我們當時的一個接口測試的時候非常的慢,壓力測試的結果大概 5 秒鐘);
  2. 而我們在調試階段,開啟了 MySQL 的慢日志記錄,我們設置的值為 2 秒,一旦 sql 執行超過 2 秒就會記錄在慢日志中,我們發現問題后查詢了 MySQL 的慢日志,最終定位到問題是出在 MySQL 的慢查詢;

2. 那這個 SQL 語句執行很慢,是如何分析的呢?

可以采用 MySQL 自帶的分析工具 explain

  • 通過 key 和 key_len 查詢是否命中索引,也可以判斷索引本身存在是否失效的情況;
  • 通過 type 字段查看 sql 是否有進一步的優化空間,是否村咋全索引掃描或者全盤掃描;
  • 通過 Extra 建議判斷是否出現了回表的情況,如果出現了,可以嘗試添加索引或者修改返回字段來修復;

在這里插入圖片描述

  • possible_key 當前 sql 可能會使用到的索引

  • key 當前 sql 實際命中的索引

  • key_len 索引占用的大小

  • Extra 額外的優化建議

    在這里插入圖片描述

  • type 這條 sql 的連接類型,性能由好到差:NULL、system、const、eq_ref、ref、range、index、all

    • NULL:沒有使用到表
    • system:查詢 MySQL 系統內置的表
    • const:根據主鍵索引查詢
    • eq_ref:主鍵索引查詢或者唯一索引查詢
    • ref:索引查詢
    • range:分為查詢
    • index:索引樹(全索引)掃描
    • all:全盤掃描

3. 了解過索引嗎?(什么是索引)

3.1 索引是什么

索引(index)是幫助 MySQL 高效獲取 數據的數據結構(有序)。在數據之外,數據庫系統還維護著滿足特定查找算法的數據結構**(B+ 樹)**,這些數據結構以某種方式引用(指向)數據,這樣就可以在這些數據結構上實現高級查找算法,這種數據結構就是索引。

3.2 什么是 B+ 樹

在這里插入圖片描述

在這里插入圖片描述

在這里插入圖片描述

3.3 回答

了解過索引嗎?

  • 索引(index)是幫助 MySQL 高效獲取數據的有序的數據結構;
  • 提高數據檢索的效率,降低數據庫的 IO 成本,因為不需要全表掃描;
  • 通過索引列對數據進行排序,降低數據排序的成本,降低了 CPU 的消耗;

索引列:以表中哪個列來創建索引;

索引的底層數據結構了解過嗎?

InnoDB是MySQL數據庫管理系統中的一種事務性存儲引擎。

MySQL 的 InnoDB 引擎采用的 B+ 樹的數據結構來作為索引的存儲結構;

  • MySQL 的索引的底層數據結構是 B+ 樹;
  • 階數更多,路徑更短;磁盤讀寫代價低,非葉子節點只存儲指針,葉子節點存儲數據;
  • B+ 樹便于掃庫和區間的查詢,葉子節點是一個雙向鏈表;

4. 什么是聚簇索引,什么是非聚簇索引?

4.1 什么是聚集索引,什么是二級索引(非聚集索引)?

在這里插入圖片描述

在這里插入圖片描述

如果沒有主鍵,則會使用隱藏字段:DB_ROW_ID,隱藏主鍵

4.2 什么是回表查詢?

了解什么是聚集索引,什么是二級索引(非聚集索引)后,再進行理解:

在這里插入圖片描述

如果沒有主鍵,則會使用隱藏字段:DB_ROW_ID,隱藏主鍵進行回表查詢(如果不給隱藏主鍵創建索引,那么回表查詢是沒有走索引的,效率低下)

4.3 回答

  • 聚簇索引(聚集索引):數據存放到索引中,B+ 樹的葉子節點保存了整行數據,有且只有一個;
  • 非聚簇索引(二級索引,非聚集索引):數據不全部存放到索引中,B+ 樹的葉子節點保存了索引列以及對應的主鍵,可以有多個;
  • 通過二級索引找到對應的主鍵值,再到聚集索引中查找整行數據,這個過程就是回表;(如果沒有聚集索引,回表查詢就不是通過索引查詢了,而是全表查詢,非常低性能)

5. 知道什么叫覆蓋索引嗎?

覆蓋索引就是查詢使用了索引,并且需要返回的列在該索引中已經全部覆蓋到了;

在這里插入圖片描述

  • 聚集索引一定是,二級索引不需要回表查詢也是;

回答:

  • 聚集索引指的是查詢使用了索引,返回的列,在索引中全部都能找到;
    • 使用 id 查詢,直接走聚集索引查詢,一次索引掃描,直接返回全部數據,性能高;
    • 如果所需列在索引中不存在,就會觸發回表查詢,所以盡量避免使用 select *

6. MySQL 超大分頁怎么處理?

在數據量比較大的時候,如果進行 limit 分頁查詢,在查詢的時候,越往后,分頁查詢效率越低。

6.1 超大分頁場景

例如:

select * from user order by nickname limit 0, 10;
select * from user order by nickname limit 9000000, 10;

上面那個 0 毫秒不到,而下面那個甚至可以達到 10 秒以上!

因為,在執行的時候,需要加載 9000010 條記錄(每條都是 raw),再選取 9000000 - 9000010 的記錄,其他記錄丟棄,查詢排序的非常大。

而 nickname 在這里不是覆蓋索引,所以加載 9000010 條記錄時,性能很低。

  • order by 子句使用索引需要:
    1. order by 子句中的字段必須創建了索引,索引查詢的字段覆蓋需要查詢的字段;
    2. order by 子句中的字段要符合最左前綴法則(對于復合索引);
  • 像這種非覆蓋索引,回表的性能還不如全表查詢呢,所以不走索引在這里是好事;

6.2 超大分頁 SQL優化

但是我們知道這條 sql 中是覆蓋索引:

select id from user ordery by nickname limit 9000000, 10;

那么我們再拿這 10 個 id 去表中查詢即可。

因此 sql 可以優化成這樣(覆蓋索引 + 子查詢):

select * fromuser u, (select id from user order by nickname limit 9000000, 10) a
where u.id = a.id;

6.3 回答

  • 問題在于在數據量比較大時,limit 分頁查詢,需要對數據進行排序,效率低。

  • 可以用到索引(有序性)查詢,而如果不是覆蓋索引,那么可以用覆蓋索引 + 子查詢進行優化!

7. 索引創建的原則有哪些?

先陳述自己實際開發中怎么用索引的,用了什么索引,如主鍵索引、唯一索引、復合索引…

再說原則:

  1. 數據量較大,且查詢比較頻繁的表;(10w+ 就可以創建索引增加用戶體驗了)
  2. 常常作為查詢條件、排序操作、分組操作的字段;
  3. 盡量使用聯合索引(多列索引),減少單列索引,這樣可以讓查詢更可能是覆蓋索引;
  4. 要控制索引的數量,并不是越多越好,增刪改都是需要維護的;
  5. 字段內容區分度高,盡量建立唯一索引,區分度越高性能越好;
  6. 字符串類型字段,內容較長,可以使用前綴索引;
  7. 如果索引列不能存儲 NULL 值,在創建表的時候使用 NOT NULL 約束,這有利于讓優化器選擇哪個索引進行更有效的查詢;

8. 什么情況下索引會失效?

8.1 復合索引

在這里插入圖片描述

順序見 Seq_in_index,即 name、status、address

8.2 違反最左前綴法則

SQL 的查詢條件 / 排序 / 分組從索引的最左前列開始,才會走索引:

在這里插入圖片描述

正向例子:

在這里插入圖片描述

反向例子:

在這里插入圖片描述

跳過某一列,則只有部分最左前綴索引生效:

在這里插入圖片描述

8.3 范圍查詢右邊的列,不能使用索引

在這里插入圖片描述

下面那個,name 和 status 走索引,status 右邊的字段 address 沒用到索引。

8.4 不要再索引列上進行運算操作,索引會失效

在這里插入圖片描述

8.5 字符串不加單引號,造成索引失效

在這里插入圖片描述

復雜行為往往導致索引失效~

8.6 模糊查詢有可能會導致索引失效

頭部模糊匹配,索引失效。如果僅僅是尾部模糊匹配,索引不會失效。

在這里插入圖片描述

8.7 回答

同理,先陳述自己的遭遇,如某個場景創建了索引,性能還是很慢,explain 去查看 sql 語句的執行計劃,發現索引失效了。

對于復合索引:

  1. 違反最左前綴法則;
  2. 范圍查詢右邊的列;
  3. 在索引列上進行運算操作;
  4. 字符串不加單引號,導致 MySQL 優化器進行類型轉化;
  5. 頭部模糊查詢;

通常情況下,可以使用 explain 查看 sql 的執行計劃來判斷索引是否失效。

9. 談一談你對 SQL 優化的經驗

從三個方面:

  1. 表的設計優化
  2. 索引優化(參考優化創建原則和索引失效)
  3. SQL 語句優化

9.1 表的設計優化(參考阿里開發手冊《嵩山版》)

  1. 比如設置合適的數值(tinyint、int、bigint)要根據實際情況選擇;
  2. 比如設置合適的字符串類型(char,varchar)char定長效率高,varchar可變長度,效率稍低;

9.2 SQL 語句的優化

  1. select 語句務必指明字段的名稱(避免使用 select *);
  2. SQL 語句要避免造成索引失效的寫法;
  3. 盡量用 union all 代替 union,union 會多一次過濾,效率低(union 會將重復的過濾掉,如果知道沒有重復的就可以用 union all)

在這里插入圖片描述

這個就不能用 union all,并不是存在絕對地去優化,而是看情況決定~

  1. 避免在 where 子句對字段進行表達式/函數操作;

  2. join 優化,能用 inner join 就不用 left join 或者 right join,如果必須使用,一定要以小表為驅動;

    • 小表:數據量較小的表,大表:數據量較大的表,

    • 這樣的好處就是以小表連接大表,連接次數較小,on 子句查詢的次數較小,并且由于是查詢大表,所以如果是有索引,索引效果更明顯!

    • 內連接會對兩個表進行優化,優先把小表放外邊,把大表放里面;

    • left join 或者 right join 則不會重新調整順序;

本文來自互聯網用戶投稿,該文觀點僅代表作者本人,不代表本站立場。本站僅提供信息存儲空間服務,不擁有所有權,不承擔相關法律責任。
如若轉載,請注明出處:http://www.pswp.cn/news/716662.shtml
繁體地址,請注明出處:http://hk.pswp.cn/news/716662.shtml
英文地址,請注明出處:http://en.pswp.cn/news/716662.shtml

如若內容造成侵權/違法違規/事實不符,請聯系多彩編程網進行投訴反饋email:809451989@qq.com,一經查實,立即刪除!

相關文章

錯誤筆記:Anaconda 錯誤(閃退、無法安裝等) + Pycharm 錯誤(無法啟動)+ python 報錯

Anaconda 錯誤 1、導航器啟動中發生-- 閃退 方法一: Windows下: 1)使用管理員運行:conda prompt 2)執行命令 conda update anaconda-navigator 方法二: 重置Anaconda配置:anaconda-navigator…

C語言第三十四彈---動態內存管理(下)

?個人主頁: 熬夜學編程的小林 💗系列專欄: 【C語言詳解】 【數據結構詳解】 動態內存管理 1、動態內存經典筆試題分析 1.1、題目1 1.2、題目2 1.3、題目3 1.4、題目4 2、柔性數組 2.1、柔性數組的特點 2.2、柔性數組的使用 2.3、…

【c++】計算樹的深度和節點數

在C語言中,計算給定樹的層數(深度)和節點總數通常需要使用遞歸方法。首先,我們需要定義樹的節點結構。這里假設我們處理的是一棵二叉樹,每個節點有兩個子節點(左子節點和右子節點)。 下面是一個…

5.STL源碼解析-算法、仿函數、適配器

算法 STL算法總覽 仿函數與適配器 C標準模板庫(STL)是C程序員的得力工具,提供了許多強大而高效的數據結構和算法。在STL中,仿函數(Functor)和適配器(Adapter)是兩個重要的概念…

C語言文件操作(fputs() 和 puts() 有兩個小區別)

fputs() 和 puts() 有兩個小區別: 1.puts() 只能向標準輸出流輸出,而 fputs() 可以向任何流輸出。 2.使用 puts() 時,系統會在自動在其后添加換行符;而使用 fputs() 時,系統不會自動添加換行符。 那么這是不是意味著使…

【C++精簡版回顧】17.io流,流中提供的函數

1.流含義 2.流類 3.流對象 4.流對象的函數 舉例&#xff1a; 要求&#xff1a;數據結構中經常需要對齊輸出數據&#xff0c;應該怎么做&#xff1f; 1.頭文件 #include<iomanip> 2.創建表格頭 cout << setiosflags(ios::left) << setw(8) << "姓名…

BUGKU 網站被黑

打開環境&#xff0c;什么都沒發現&#xff0c;使用蟻劍掃描一下&#xff0c;發現shell.php&#xff0c;打開 使用BP抓包&#xff0c;進行爆破 得到密碼&#xff1a;hack 進去得到flag

GEE高階應用python wxee——如何利用來自 GOES-16 和 MODIS 的數據來可視化火災隨時間的進展分析

火災進展 wxee 是專為處理氣象數據而設計的,但它對遙感數據也很有用。在本示例中,我們將了解 wxee 如何利用來自 GOES-16 和 MODIS 的數據來可視化火災隨時間的進展情況。 安裝和設定 #!pip install wxeeimport ee import wxeeee.Authenticate() wxee.Initialize(project=x…

每日一類:QLabel深入解析

QLabel是Qt中用于顯示文本或圖像的控件&#xff0c;屬于Qt Widgets模塊。它是展示靜態內容的理想選擇&#xff0c;支持富文本格式&#xff0c;使得文本可以包含不同的字體、顏色和鏈接。QLabel也可以用來顯示圖像&#xff0c;包括動態圖像。此外&#xff0c;它還支持文本和圖像…

【Java面試題】SpringBoot與Spring的區別

主要區別體現幾個方面&#xff1a; 1.操作簡便性 SpringBoot提供極其快速和簡化的操作&#xff0c;使得Spring開發者能更快速上手。它通過提供spring的運行配置&#xff0c;以及為通用spring項目提供許多非功能性特性&#xff0c;進一步簡化了開發過程。 2.框架擴展性 Spri…

算法學習——差分

在了解差分之前&#xff0c;我們首先需要知道前綴和的概念。 前綴和簡單介紹&#xff1a; 對于一個數組A&#xff0c;要求出A[0]~A[i]的和&#xff0c;我們通常的做法是遍歷一邊&#xff0c;加起來。但是要求m組這樣的和&#xff0c;我們就要花費O(mn)的時間復雜度。顯然不合…

【考研數學】湯家鳳1800題什么水平?

我覺得湯家鳳基礎武忠祥強化這個組合非常的不錯 湯家鳳老師的講課風格 湯家鳳老師的基礎課程是大家公認的講的詳細&#xff0c;并且非常照顧基礎不好的學生&#xff0c;會把基礎知識點掰開揉碎的講給大家聽&#xff0c;在上課過程中&#xff0c;還會把知識點寫在A4紙上&#…

試了下新型的360AI搜索

360AI搜索 試了下&#xff0c;感覺還是挺不錯的。 比如問這個問題&#xff1a; ERROR 1698 (28000): Access denied for user rootlocalhost 它的回答&#xff1a; 對于ERROR 1698 (28000): Access denied for user rootlocalhost的問題&#xff0c;這通常是由于MySQL密碼為…

【Javascript】設計模式之單例模式

文章目錄 1、實現單例模式2、透明的單例模式3、用代理實現單例模式4、JavaScript 中的單例模式5、惰性單例6、通用的惰性單例7、小結 定義&#xff1a; 保證一個類僅有一個實例&#xff0c;并提供一個訪問它的全局訪問點 單例模式是一種常用的模式&#xff0c;有一些對象我們往…

JavaScript 學習總結(16)—— 實用小函數總結

1.匹配正整數 // 匹配正整數 let isPositiveNum = val => {return /^[1-9]d*$/.test(val); }; console.log(isPositiveNum(9)) //true console.log(isPositiveNum(2.2)) //false 2.匹配負整數 // 匹配負整數let isNegativeNum = val => {return /^-[1-9]d*$/.test(val…

R750 install AMD MI210GPU

一、 查看服務器GPU卡信息 可以首先在服務器上check 當前GPU的詳細信息是否匹配 二、安裝 Ubuntu22.04操作系統 服務器CHECK 安裝的AMD GPU 是否被系統識別 #lspci | grep AMD 查看GPU信息 可以看到已經識別成功 三、安裝AMD GPU驅動 https://rocm.docs.amd.com/projec…

linux 根目錄下結構

/ 虛擬目錄的根的目錄&#xff0c;通常不會在這里放置文件 /bin&#xff1a;存放頻繁使用的命令,二進制文件&#xff0c;存放了很多用戶級的GNU實用工具。 /boot&#xff1a;引導目錄&#xff0c;存放引導文件&#xff0c;包含啟動Linux所需的核心文件。 /dev&#xff1a;設…

智能駕駛規劃控制理論學習05-車輛運動學規劃案例分析

目錄 案例一——Hybrid A*&#xff08;基于正向運動學&#xff09; 1、基本思想 2、 實現流程 3、啟發函數設計 4、分析擴張&#xff08;Analytic Expansions&#xff09; 5、分級規劃&#xff08;Hierarchical planning&#xff09; 案例二——State Lattice Planning&…

子矩陣的和 刷題筆記 {二維前綴和}

首先我們的目標是讓 s[i][j]表示為其左方和上方形成的矩陣所有元素的和 加上s[i-1][j]和s[i][j-1]后 s[i-1][j-1]部分重復了所以減去 最后加上a[i][j]即可完成目標 s[i][j]s[i-1][j]s[i][j-1]-s[i-1][j-1]a[i][j]; 然后看題目要求 要求x1,y1,x2,y2圍成的小正方形內的元素和…

C/C++工程師面試題(數據庫篇)

索引的優缺點 索引是一種支持快速查找特定行的數據結構&#xff0c;如果沒有索引&#xff0c;就需要遍歷整個表進行查找。用于提高數據檢索的速度和效率。 好處&#xff1a; 提高檢索速度&#xff1a; 索引可以加快數據的檢索速度&#xff0c;因為它們允許數據庫系統直接定位到…