MySQL索引效能分析

itread01 2021-01-21 23:21:42
Mysql 分析 索引 效能


# 為什麼要做效能分析你有沒有這樣的情況。面對一個你沒怎麼寫過的、複雜的業務,你構思了很久,終於開始敲下了第一段程式碼。寫的過程迷迷糊糊,有的時候還能把自己搞暈了。但你還是終於把它寫完了。但是點選一執行,完了,有bug。怎麼辦?debug的方式有很多,控制檯列印是一種。通過控制檯列印的資訊,我們能根據反饋去修改程式碼,直到程式碼能正常執行為止。其實建索引也是一樣的。上篇帖子[《淺談sql索引》](https://www.cnblogs.com/tandk-blog/p/14283455.html),說過索引的難點在於針對一個具體的表去做出最合適的索引。因為這不只要看你表裡有什麼內容,更多要看你的業務,你的業務會經常根據哪些關鍵詞查詢。程式碼我們可以天天寫,索引不能天天建吧。所以多數情況下,因為不熟悉,我們一開始建立的索引往往都不是最好的,唯有根據反饋去調整索引,才能做出一個最合適這個表的索引。今天要分享的就是怎麼去看懂這個反饋,即怎麼去做效能分析。# 怎麼做效能分析使用EXPLAIN關鍵字!使用EXPLAIN關鍵字可以知道MySQL是如何處理你的SQL語句的,分析你的查詢語句或是表結構的效能瓶頸。但EXPLAIN並不能直接反饋我們建的索引的好壞。用法是這樣的,我們建好了索引,拿著業務中最常用的幾句SQL語句來EXPLAIN一下,如果反饋的效果好,那麼建立的索引就是最適合這個表的,反之則需要改進。要不改索引,要不改SQL。# EXPLAIN玩法### 語句`explain 要檢視的sql語句`(橫表)或`explain 要檢視的sql語句\G`(豎表)### 一個具體的例子![](https://img2020.cnblogs.com/blog/1573343/202101/1573343-20210121162454574-528124047.png)### 各個欄位解釋從上圖中我們可以看到,explain出來的資訊有以下欄位:|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|| ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- |你仔細看看,這麼長是不是有點像工資條?其中各個欄位代表的意思是這樣的:- id: 查看錶的讀取順序。 咱們上圖中的例子只查詢了一個表,但若是多表聯合查詢,則有: id相同的話,執行順序由上至下; id不同的話,id值越大優先順序越高;- select_type: 顯示區別聯合查詢、子查詢、普通查詢等。 以下為其可能的值,以及對應所代表的資訊: SIMPLE -- 簡單的select查詢,不包含子查詢或union; PRIMARY -- 查詢中若包含任何複雜的子部分,最外層查詢則被標記為PRIMARY; SUBQUERY -- 在select或where包含的子查詢; DERIVED -- 在from列表包含的子查詢被標記為DERIVED(衍生),MySQL會遞迴執行這些子查詢,把結果放在臨時表裡; UNION -- 若第二個select出現在union後,則被標記為union;若union包含在from子句的子查詢中,外層select將被標記為:DERIVED; UNION RESULT -- 從union表獲取結果的select;- table: 顯示錶名。- type: 顯示查詢用了何種型別。 以下為其可能的值,以及對應所代表的資訊: system -- 表只有一行記錄(等於系統表),這是const型別的特例,平時不會出現,可以忽略不記。 const -- 表示通過索引一次就找到了,const用於比較primary key(主鍵)或者unique(唯一)索引。因為只匹配一行資料,所以很快,如果將主鍵置於where列表中,mysql就能將該查詢轉換到一個常量。 eq_ref -- 唯一性掃描索引,對於每個索引鍵,表中**剛好只**有一條記錄與之匹配。常用於主鍵或唯一索引掃描。 ref -- 非唯一性掃描索引,返回匹配某個單獨值的所有行。上面`eq_ref`的多值情況。如where age=1,age=1的資料剛好只有一行,便顯示eq_ref,age=1的資料有多行,便顯示ref。 range -- 只檢索給定範圍的行,使用一個索引來選擇行。如果type列的值為`range`,key列便會顯示使用了哪個索引。一般就是在where語句中出現了between、<、>、in等的查詢。這種範圍掃描查詢比全表掃描要好。 index -- Full Index Scan(掃描全部索引),index與ALL區別為index型別只遍歷索引樹。都是讀全表,但是index是從索引中讀取,all是從硬碟中讀,而且索引檔案通常比資料檔案小。 all -- Full Table Scan(掃描全表),遍歷全表來找到匹配的行,即索引完全沒用上。 從最好到最差依次是:system>const>eq_ref>ref>range>index>ALL 細節: 1. type 是 ALL,當資料到達百萬以上一定要優化。 2. 一般來說,如果要優化得保證查詢至少達到range級別,最好達到ref。- possible_keys和key possible_key: 顯示可能應用在這張表中的索引,一個或多個。理論上可能被使用的索引,但不一定被查詢實際使用。 key: 實際使用的索引,如果為null,則沒有使用索引。 這兩列有四種情況: 1. possible_key有值,key有值:正常,有的時候前者有多個值但後者只有一個也正常。 2. possible_key有值,key無值:**索引失效了,出現問題了**。 3. possible_key無值,key有值:條件查詢(如where)沒有用到索引或沒有條件查詢,但查詢的列(select後面的欄位)剛好順序、數量和索引一致。 4. possible_key無值,key無值:正常,就是你沒建索引。- key_len: 表示索引中使用的位元組數。 可通過該列計算【查詢中使用的索引的長度】,在查詢結果一樣的情況下,該值越小越好。 key_len顯示的值為索引欄位的最大可能長度,而非實際使用長度,即通過表定義計算而得,不是通過表內檢索而得。 假設你建立複合索引(col1,col2),如果【通過col1條件查詢】和【通過col1和col2條件查詢】的結果一樣,那麼前者比較好,因為只需要用一個欄位,key_len的值會比較小,上面也說過是通過表定義的長度來決定key_len的值。- ref: 顯示key列中索引參照的值。 有兩種可能的值,以及對應所代表的資訊: 庫名.表名.欄位名 -- 表示索引參照的值是哪個庫的哪個表的哪個欄位; const -- 表示索引參照的值是常量,一般是where id=1這樣才會出現;- rows: 根據表統計資訊以及索引選用情況,大致估算出找到要查詢的記錄需要讀取的行數。- Extra: 十分重要的額外資訊。 以下為其可能的值,以及對應所代表的資訊: Using filesort -- 說明mysql完全或部分沒有按照你所建的索引排序,比較需要優化了。MySQL無法利用索引完成的排序操作稱為“檔案排序”; Using temporary-- 使用了臨時表儲存中間結果,mysql對查詢結果排序時使用臨時表。這也比較需要優化,因為臨時表的建立和刪除都是比較費效能的,常見於order by和group by; Using index -- 表示相應的select操作中使用了覆蓋索引,避免了訪問表的資料行,效率不錯。如果同時出現Using where,表明索引被用來執行索引鍵值的查詢,如果沒有出現,表明索引被用來讀取資料而非執行查詢動作; Using where -- 使用了where過濾; Using join buffer -- 使用了連線快取,如果總是出現這個欄位,可以去配置檔案中適當調大這個值; Impossible where -- where子句的值總是false,不能用來獲取任何元組; Select tables optimized away -- 在沒有group by子句的情況下,基於索引優化MIN/MAX操作或者對於MyISAM儲存引擎優化count(*)操作,不必等到執行階段再進行計算,查詢執行計劃生成的階段即完成優化; distinct -- 優化distinct操作,在找到第一組匹配的元組後馬上停止找相同值的動作。 注意: 1. 使用group by等排序時,如果有用到索引,最好嚴格按照索引的順序來,比如,存在複合索引(col1,col2),排序時如果跳過col1,直接使用col2排序,會導致出現Using filesort、Using temporary等比較嚴重的問題。 2. 儘量使用覆蓋索引,select後面的列名完全與建立的索引順序、數量一致。這樣可以直接使用索引讀取資料,避免讀取表的資料行。# 案例最後來看一個簡單的案例,我會先放題目,再放思路,最後放答案。### 題目要求是寫出SQL的執行順序。![](https://img2020.cnblogs.com/blog/1573343/202101/1573343-20210121172815826-249366881.png)### 思路1. 首先看id列,id越大優先順序越高,索引從id為4的那一行開始看,這一行的table為t2,即查詢的是t2,所以最先查詢的反而是最後的部分`select name,id from t2`。2. id為3的這一行,table是t1,所以查的是`select id,name from t1 where other_column=''`;這一行後面的Extra列的`Using where`同樣佐證了這點。3. id為2的這一行,table是t3,所以查的是`select id from t3`;另外,key列是primary說明用到了主鍵作為索引,Extra列的`Using Index`表示用到了覆蓋索引(即索引用在了select後面)。4. id為1的這一行,
版权声明
本文为[itread01]所创,转载请带上原文链接,感谢
https://www.itread01.com/content/1611234184.html

  1. 【计算机网络 12(1),尚学堂马士兵Java视频教程
  2. 【程序猿历程,史上最全的Java面试题集锦在这里
  3. 【程序猿历程(1),Javaweb视频教程百度云
  4. Notes on MySQL 45 lectures (1-7)
  5. [computer network 12 (1), Shang Xuetang Ma soldier java video tutorial
  6. The most complete collection of Java interview questions in history is here
  7. [process of program ape (1), JavaWeb video tutorial, baidu cloud
  8. Notes on MySQL 45 lectures (1-7)
  9. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  10. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  11. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  12. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  13. 【递归,Java传智播客笔记
  14. [recursion, Java intelligence podcast notes
  15. [adhere to painting for 386 days] the beginning of spring of 24 solar terms
  16. K8S系列第八篇(Service、EndPoints以及高可用kubeadm部署)
  17. K8s Series Part 8 (service, endpoints and high availability kubeadm deployment)
  18. 【重识 HTML (3),350道Java面试真题分享
  19. 【重识 HTML (2),Java并发编程必会的多线程你竟然还不会
  20. 【重识 HTML (1),二本Java小菜鸟4面字节跳动被秒成渣渣
  21. [re recognize HTML (3) and share 350 real Java interview questions
  22. [re recognize HTML (2). Multithreading is a must for Java Concurrent Programming. How dare you not
  23. [re recognize HTML (1), two Java rookies' 4-sided bytes beat and become slag in seconds
  24. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  25. RPC 1: how to develop RPC framework from scratch
  26. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  27. RPC 1: how to develop RPC framework from scratch
  28. 一次性捋清楚吧,对乱糟糟的,Spring事务扩展机制
  29. 一文彻底弄懂如何选择抽象类还是接口,连续四年百度Java岗必问面试题
  30. Redis常用命令
  31. 一双拖鞋引发的血案,狂神说Java系列笔记
  32. 一、mysql基础安装
  33. 一位程序员的独白:尽管我一生坎坷,Java框架面试基础
  34. Clear it all at once. For the messy, spring transaction extension mechanism
  35. A thorough understanding of how to choose abstract classes or interfaces, baidu Java post must ask interview questions for four consecutive years
  36. Redis common commands
  37. A pair of slippers triggered the murder, crazy God said java series notes
  38. 1、 MySQL basic installation
  39. Monologue of a programmer: despite my ups and downs in my life, Java framework is the foundation of interview
  40. 【大厂面试】三面三问Spring循环依赖,请一定要把这篇看完(建议收藏)
  41. 一线互联网企业中,springboot入门项目
  42. 一篇文带你入门SSM框架Spring开发,帮你快速拿Offer
  43. 【面试资料】Java全集、微服务、大数据、数据结构与算法、机器学习知识最全总结,283页pdf
  44. 【leetcode刷题】24.数组中重复的数字——Java版
  45. 【leetcode刷题】23.对称二叉树——Java版
  46. 【leetcode刷题】22.二叉树的中序遍历——Java版
  47. 【leetcode刷题】21.三数之和——Java版
  48. 【leetcode刷题】20.最长回文子串——Java版
  49. 【leetcode刷题】19.回文链表——Java版
  50. 【leetcode刷题】18.反转链表——Java版
  51. 【leetcode刷题】17.相交链表——Java&python版
  52. 【leetcode刷题】16.环形链表——Java版
  53. 【leetcode刷题】15.汉明距离——Java版
  54. 【leetcode刷题】14.找到所有数组中消失的数字——Java版
  55. 【leetcode刷题】13.比特位计数——Java版
  56. oracle控制用户权限命令
  57. 三年Java开发,继阿里,鲁班二期Java架构师
  58. Oracle必须要启动的服务
  59. 万字长文!深入剖析HashMap,Java基础笔试题大全带答案
  60. 一问Kafka就心慌?我却凭着这份,图灵学院vip课程百度云