mysql執行計劃看是否最優

来源:https://www.cnblogs.com/wenBlog/archive/2018/03/15/8572594.html
-Advertisement-
Play Games

介紹 本篇主要通過彙總網上的大牛的知識，簡單介紹一下如何使用mysql的執行計劃，並根據執行計劃判斷如何優化和是否索引最優。 執行計劃可顯示估計查詢語句執行計劃，從中可以分析查詢的執行情況是否最優，有助於對不使用索引的語句進行優化。EXPLAIN對每個查詢返回一行信息，列出了有序的表格，MySQL處 ...


介紹

　　本篇主要通過彙總網上的大牛的知識，簡單介紹一下如何使用mysql的執行計劃，並根據執行計劃判斷如何優化和是否索引最優。

　　執行計劃可顯示估計查詢語句執行計劃，從中可以分析查詢的執行情況是否最優，有助於對不使用索引的語句進行優化。EXPLAIN對每個查詢返回一行信息，列出了有序的表格，MySQL處理語句的時候讀取他們。MySQL解決所有的連接使用嵌套連接方法。這意味讀取第一張一行，然後匹配第二張表的所有行，第三張表甚至更多表。當所有的表在處理時，MySQL會輸出已經查詢出來的列，並且回溯到表繼續查找直到所有的行被找到，從該表讀取下一行，直到程式繼續處理下一張表。

 
使用關鍵詞 EXTENDED ，EXPLAIN 會處理通過 SHOW WARNINGS 看到的一些額外信息。EXPLAIN EXTENDED 會顯示這些濾出的列。

語法：

EXPLAIN <select statement>;

輸出表格欄位如下：

    mysql> explain select * from mysql.user where user='root';  
    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+  
    | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |  
    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+  
    |  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |  
    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+

 

 

Column

JSONName

Meaning

id

select_id

查詢標識。id越大優先執行；id相同自上而下執行；

select_type

None

查詢的類型

table

table_name

查詢的表

partitions

partitions

Thematching partitions

type

access_type

連接類型

possible_keys

possible_keys

可能選擇的索引

key

key

實際使用的索引

key_len

key_length

使用的索引長度

ref

ref

哪一列或常數在查詢中與索引鍵列一起使用

rows

rows

估計查詢的行數

filtered

filtered

被條件過濾掉的行數百分比

Extra

None

解決查詢的一些額外信息

 

 

以下主要舉例說明3個欄位：select_type 、type、Extra 

 

【select_type】

alue

JSONName

Meaning

SIMPLE

None

簡單查詢 (不使用UNION或子查詢)

PRIMARY

None

外層查詢，主查詢

UNION

None

UNION中第二個語句或後面的語句

DEPENDENTUNION

dependent (true)

UNION中第二個語句或後面的語句,獨立於外部查詢

UNIONRESULT

union_result

UNION的結果

SUBQUERY

None

子查詢中第一個SELECT

DEPENDENTSUBQUERY

dependent (true)

子查詢中第一個SELECT,獨立於外部查詢

DERIVED

None

子查詢在 FROM子句中

MATERIALIZED

materialized_from_subquery

物化子查詢（不清楚是什麼樣的查詢語句？）

UNCACHEABLESUBQUERY

cacheable (false)

結果集不能被緩存的子查詢，必須重新評估外層查詢的每一行

UNCACHEABLEUNION

cacheable (false)

UNION中第二個語句或後面的語句屬於不可緩存的子查詢

 

創建測試表：

create table tabname (  
id int auto_increment not null primary key,  
name varchar(10) null,  
indate datetime null,  
tid int null,  
key(tid),  
key(indate)  
)engine=innodb;  
  
  
create table tabname2 (  
id int auto_increment not null primary key,  
name varchar(10) null,  
indate datetime null,  
tid int null,  
key(tid),  
key(indate)  
)engine=myisam;  
  
  
insert into tabname(name,indate,tid) values('love',now(),2),('lucky',now(),3),('passion',now(),4);  
insert into tabname2(name,indate,tid) values('love',now(),2),('lucky',now(),3),('passion',now(),4);

 

 



#SIMPLE

create table tabname (  
id int auto_increment not null primary key,  
name varchar(10) null,  
indate datetime null,  
tid int null,  
key(tid),  
key(indate)  
)engine=innodb;  
  
  
create table tabname2 (  
id int auto_increment not null primary key,  
name varchar(10) null,  
indate datetime null,  
tid int null,  
key(tid),  
key(indate)  
)engine=myisam;  
  
  
insert into tabname(name,indate,tid) values('love',now(),2),('lucky',now(),3),('passion',now(),4);  
insert into tabname2(name,indate,tid) values('love',now(),2),('lucky',now(),3),('passion',now(),4);

 

 

#PRIMARY / DERIVED

    mysql> explain select * from (select * from tabname) as a;  
    +----+-------------+------------+------+---------------+------+---------+------+------+-------+  
    | id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra |  
    +----+-------------+------------+------+---------------+------+---------+------+------+-------+  
    |  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    3 |       |  
    |  2 | DERIVED     | tabname    | ALL  | NULL          | NULL | NULL    | NULL |    3 |       |  
    +----+-------------+------------+------+---------------+------+---------+------+------+-------+

 

 

#PRIMARY / UNION / UNION RESULT

    mysql> explain select * from tabname union select * from tabname;  
    mysql> explain select * from tabname union all select * from tabname;  
    +----+--------------+------------+------+---------------+------+---------+------+------+-------+  
    | id | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | Extra |  
    +----+--------------+------------+------+---------------+------+---------+------+------+-------+  
    |  1 | PRIMARY      | tabname    | ALL  | NULL          | NULL | NULL    | NULL |    3 |       |  
    |  2 | UNION        | tabname    | ALL  | NULL          | NULL | NULL    | NULL |    3 |       |  
    | NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL | NULL    | NULL | NULL |       |  
    +----+--------------+------------+------+---------------+------+---------+------+------+-------+

 

    mysql> explain select * from tabname where id=(select max(id) from tabname);  
    +----+-------------+---------+-------+---------------+---------+---------+-------+------+------------------------------+  
    | id | select_type | table   | type  | possible_keys | key     | key_len | ref   | rows | Extra                        |  
    +----+-------------+---------+-------+---------------+---------+---------+-------+------+------------------------------+  
    |  1 | PRIMARY     | tabname | const | PRIMARY       | PRIMARY | 4       | const |    1 |                              |  
    |  2 | SUBQUERY    | NULL    | NULL  | NULL          | NULL    | NULL    | NULL  | NULL | Select tables optimized away |  
    +----+-------------+---------+-------+---------------+---------+---------+-------+------+------------------------------+

 

#PRIMARY / SUBQUERY

 

 

#PRIMARY / DEPENDENT SUBQUERY

[plain] view plain copy 
    mysql> explain select * from tabname a where exists(select 1 from tabname b where a.id=b.id);  
    mysql> explain select *,(select name from tabname b where a.id=b.id) from tabname a;  
    mysql> explain select * from tabname where id not in(select id from tabname);  
    +----+--------------------+---------+-----------------+---------------+---------+---------+------+------+-------------+  
    | id | select_type        | table   | type            | possible_keys | key     | key_len | ref  | rows | Extra       |  
    +----+--------------------+---------+-----------------+---------------+---------+---------+------+------+-------------+  
    |  1 | PRIMARY            | tabname | ALL             | NULL          | NULL    | NULL    | NULL |    3 | Using where |  
    |  2 | DEPENDENT SUBQUERY | tabname | unique_subquery | PRIMARY       | PRIMARY | 4       | func |    1 | Using index |  
    +----+--------------------+---------+-----------------+---------------+---------+---------+------+------+-------------+

 

 

#PRIMARY / DEPENDENT UNION / DEPENDENT SUBQUERY / UNION RESULT

    mysql> explain select * from tabname where id in (select id from tabname union select id from tabname);  
    +----+--------------------+------------+--------+---------------+---------+---------+------+------+-------------+  
    | id | select_type        | table      | type   | possible_keys | key     | key_len | ref  | rows | Extra       |  
    +----+--------------------+------------+--------+---------------+---------+---------+------+------+-------------+  
    |  1 | PRIMARY            | tabname    | ALL    | NULL          | NULL    | NULL    | NULL |    3 | Using where |  
    |  2 | DEPENDENT SUBQUERY | tabname    | eq_ref | PRIMARY       | PRIMARY | 4       | func |    1 | Using index |  
    |  3 | DEPENDENT UNION    | tabname    | eq_ref | PRIMARY       | PRIMARY | 4       | func |    1 | Using index |  
    | NULL| UNION RESULT      | <union2,3> | ALL    | NULL          | NULL    | NULL    | NULL | NULL |             |  
    +----+--------------------+------------+--------+---------------+---------+---------+------+------+-------------+

 

【type】

type

Meaning

system

表僅一行數據 (=system table).這是const連接類型的特例。

const

表最多只有一個匹配行，在查詢開始時被讀取。因為只有一個值，優化器將該列值視為常量。當在primarykey或者unique索引作為常量比較時被使用。

eq_ref(engine=myisam)

來自前面表的結果集中讀取一行，這是除system和const外最好的連接類型。當在使用PRIMARYKEY或者UNIQUENOT NULL的索引時會被使用。

ref

對於前面表的結果集匹配查詢的所有行，當連接使用索引key時，或者索引不是PRIMARYKEY和UNIQUE，則使用該類型。如果使用索引匹配少量行時，是不錯的連接類型。

ref_or_null

連接類型類似ref，只是搜索的行中包含NULL值MySQL做了額外的查找。

fulltext

使用全文索引時出現。

index_merge

使用了索引合併優化。(未成功)

unique_subquery

該類型將ref替換成以下子查詢的格式：

valueIN (SELECTprimary_key FROMsingle_table WHERE some_expr)

index_subquery

與 unique_subquery類似，但是將主鍵改為非唯一索引：

valueIN (SELECTkey_columnFROMsingle_table WHERE some_expr)

range

使用索引檢索給定範圍內的行。

index

該連接類型與ALL相同，除了掃描索引樹。如果查詢的欄位都在索引列中，則使用index類型，否則為ALL類型。

ALL

對於前面表的結果集中，進行了全表掃描。最差的一種類型，應考慮查詢優化了！

 

查詢類型性能由優到差：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

 

#system

    mysql> explain select id from(select id from tabname where id=1) as a;  
    +----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+  
    | id | select_type | table      | type   | possible_keys | key     | key_len | ref  | rows | Extra       |  
    +----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+  
    |  1 | PRIMARY     | <derived2> | system | NULL          | NULL    | NULL    | NULL |    1 |             |  
    |  2 | DERIVED     | tabname    | const  | PRIMARY       | PRIMARY | 4       |      |    1 | Using index |  
    +----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+

 

#const

    mysql> explain select * from tabname as a,tabname as b where a.id=b.id and a.id=1;  
    mysql> explain select * from tabname where id=1;  
    +----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+  
    | id | select_type | table   | type  | possible_keys | key     | key_len | ref   | rows | Extra |  
    +----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+  
    |  1 | SIMPLE      | tabname | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |  
    +----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+

 


#eq_ref(engine=myisam)

 

    mysql> explain select * from tabname2 as a,tabname2 as b where a.id=b.id;  
    +----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------+  
    | id | select_type | table | type   | possible_keys | key     | key_len | ref       | rows | Extra |  
    +----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------+  
    |  1 | SIMPLE      | a     | ALL    | PRIMARY       | NULL    | NULL    | NULL      |    3 |       |  
    |  1 | SIMPLE      | b     | eq_ref | PRIMARY       | PRIMARY | 4       | test.a.id |    1 |       |  
    +----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------+

 

 

 

 

#ref

    mysql> explain select * from tabname as a,tabname as b where a.tid=b.tid and a.tid=2;  
    mysql> explain select * from tabname where tid=2;  
    +----+-------------+---------+------+---------------+------+---------+-------+------+-------------+  
    | id | select_type | table   | type | possible_keys | key  | key_len | ref   | rows | Extra       |  
    +----+-------------+---------+------+---------------+------+---------+-------+------+-------------+  
    |  1 | SIMPLE      | tabname | ref  | tid           | tid  | 5       | const |    1 | Using where |  
    +----+-------------+---------+------+---------------+------+---------+-------+------+-------------+

 

#ref_or_null

 

    mysql> explain select id,tid from tabname where tid=2 or tid is null;  
    +----+-------------+---------+-------------+---------------+------+---------+-------+------+--------------------------+  
    | id | select_type | table   | type        | possible_keys | key  | key_len | ref   | rows | Extra                    |  
    +----+-------------+---------+-------------+---------------+------+---------+-------+------+--------------------------+  
    |  1 | SIMPLE      | tabname | ref_or_null | tid           | tid  | 5       | const |    2 | Using where; Using index |  
    +----+-------------+---------+-------------+---------------+------+---------+-------+------+--------------------------+

 

 

 

 

#fulltext

    mysql> explain select id,tid from tabname where tid=2 or tid is null;  
    +----+-------------+---------+-------------+---------------+------+---------+-------+------+--------------------------+  
    | id | select_type | table   | type        | possible_keys | key  | key_len | ref   | rows | Extra                    |  
    +----+-------------+---------+-------------+---------------+------+---------+-------+------+--------------------------+  
    |  1 | SIMPLE      | tabname | ref_or_null | tid           | tid  | 5       | const |    2 | Using where; Using index |  
    +----+-------------+---------+-------------+---------------+------+---------+-------+------+--------------------------+

 

#index_merge(未成功)

mysql> explain select * from tabname where tid>1 or indate<now();  
mysql> explain select * from tabname where (tid>1 or indate>now()) AND name<'kk';

 


#unique_subquery

    mysql> explain select * from tabname where tid in(select id from tabname);  
    mysql> explain select * from tabname where id in(select id from tabname);  
    +----+--------------------+---------+-----------------+---------------+---------+---------+------+------+-------------+  
    | id | select_type        | table   | type            | possible_keys | key     | key_len | ref  | rows | Extra       |  
    +----+--------------------+---------+-----------------+---------------+---------+---------+------+------+-------------+  
    |  1 | PRIMARY            | tabname | ALL             | NULL          | NULL    | NULL    | NULL |    3 | Using where |  
    |  2 | DEPENDENT SUBQUERY | tabname | unique_subquery | PRIMARY       | PRIMARY | 4       | func |    1 | Using index |  
    +----+--------------------+---------+-----------------+---------------+---------+---------+------+------+-------------+

 

#index_subquery

    mysql> explain select * from tabname where tid in(select tid from tabname);  
    mysql> explain select * from tabname where id in(select tid from tabname);  
    +----+--------------------+---------+----------------+---------------+------+---------+------+------+--------------------------+  
    | id | select_type        | table   | type           | possible_keys | key  | key_len | ref  | rows | Extra                    |  
    +----+--------------------+---------+----------------+---------------+------+---------+------+------+--------------------------+  
    |  1 | PRIMARY            | tabname | ALL            | NULL          | NULL | NULL    | NULL |    3 | Using where              |  
    |  2 | DEPENDENT SUBQUERY | tabname | index_subquery | tid           | tid  | 5       | func |    1 | Using index; Using where |  
    +----+--------------------+---------+----------------+---------------+------+---------+------+------+--------------------------+

 

 

 

 

#range

 

    mysql> explain select * from tabname where tid between 1 and 2;  
    mysql> explain
您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • windows10 64位下安裝oracle 11g和PL/SQL Developer
    一、材料準備： oracle11g安裝包（64位） oracle11g客戶端（32位） PL\SQL Developer安裝包(32位) oracle11g安裝包（64位） oracle11g客戶端（32位） PL\SQL Developer安裝包(32位) 1、下載Oracle 11g鏈接：htt ...
  • 可能是最好的SQL入門教程
    個人博客： "這可能是最好的SQL入門教程" ...
  • MySQL事務以及隔離級別
    前言： 我一直想不到一個好的標題應該怎麼寫。我想MySQL的一些重要的內容。我在兩次面試中都遇到過的，但直接用MySQL標題好像又不太貼切。乾脆就是所寫的內容吧。 MySQL事務： transaction Transactions are atomic units of work that can ...
  • OCIEnvCreate failed with return code -1 but error message text was not available with ODP.net
    https://stackoverflow.com/questions/12901045/ocienvcreate-failed-with-return-code-1-but-error-message-text-was-not-available ...
  • SqlServer存儲過程示例
    /* 步驟1 刪除本地及海關單證待分派表、報關單表中的數據 delete from W_DOCUMENTS; delete from W_DOCUMENTS_TEST; delete from W_DECLARATIONS_TEST; 統計本地及海關單證待分派表、報關單表中的數據總條數 select ...
  • SqlServer50條常用查詢語句
    Student(S#,Sname,Sage,Ssex) 學生表 Course(C#,Cname,T#) 課程表 SC(S#,C#,score) 成績表 Teacher(T#,Tname) 教師表 問題： 1、查詢“001”課程比“002”課程成績高的所有學生的學號； select a.S# from ...
  • mysql查詢示例
    use test; create table t1(tid smallint(5) unsigned auto_increment,tname varchar(50),tkecheng varchar(50),tgrade smallint(10),primary key(tid))ENGINE=i ...
  • 開發中使用mongoTemplate進行Aggregation聚合查詢
    筆記:使用mongo聚合查詢(一開始根本沒接觸過mongo，一點一點慢慢的查資料完成了工作需求) 需求：在訂單表中，根據buyerNick分組，統計每個buyerNick的電話、地址、支付總金額以及總商品數，返回結果是CustomerDetail。 Trade表： CustomerDetail： O ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類