mysql索引篇explain命令詳解
前言
mysql中的explain命令可以用來查看sql語句是否使用了索引，用了什么索引，有沒有做全表掃描?？梢詭椭覀儍?yōu)化查詢語句。
explain出來的信息有10列，文章主要介紹type、key、Extra這幾個字段。
演示中涉及到的表結(jié)構(gòu)如下：
CREATE TABLE `dept_desc` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
`desc` varchar(255) NOT NULL,
PRIMARY KEY (`dept_no`)
) ENGINE=InnoDB
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`),
KEY `dept_no` (`dept_no`),
CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,
CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE
) ENGINE=InnoDB
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` enum('M','F') NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB
上面的表都是mysql中測試庫的表，需要的同學可以自行去下載。
官方文檔：https://dev.mysql.com/doc/employee/en/employees-installation.html；
key
sql語句實際執(zhí)行時使用的索引列，有時候mysql可能會選擇優(yōu)化效果不是最好的索引，這時，我們可以在select語句中使用force index(INDEXNAME)來強制mysql使用指定索引或使用ignore index(INDEXNAME)強制mysql忽略指定索引
type
訪問類型，表示數(shù)據(jù)庫引擎查找表的方式，常見的type類型有：all,index,range,ref,eq_ref，const。
all：
全表掃描，表示sql語句會把表中所有表數(shù)據(jù)全部讀取讀取掃描一遍。效率最低，我們應盡量避免。
mysql> explain select * from dept_emp; +----+-------------+----------+------+---------------+------+---------+------+--------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+--------+-------+ | 1 | SIMPLE | dept_emp | ALL | NULL | NULL | NULL | NULL | 331570 | NULL | +----+-------------+----------+------+---------------+------+---------+------+--------+-------+
index：
- 全索引掃描,表示sql語句將會把整顆二級索引樹全部讀取掃描一遍，因為二級索引樹的數(shù)據(jù)量比全表數(shù)據(jù)量小，所以效率比all高一些。一般查詢語句中查詢字段為索引字段，且無where子句時，type會為index。如下，mysql確定使用dept_no這個索引，然后掃描整個dept_no索引樹得到結(jié)果。
mysql> explain select dept_no from dept_emp; +----+-------------+----------+-------+---------------+---------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+---------------+---------+---------+------+--------+-------------+ | 1 | SIMPLE | dept_emp | index | NULL | dept_no | 4 | NULL | 331570 | Using index | +----+-------------+----------+-------+---------------+---------+---------+------+--------+-------------+
range：
部分索引掃描，當查詢?yōu)閰^(qū)間查詢，且查詢字段為索引字段時，這時會根據(jù)where條件對索引進行部分掃描。
mysql> explain select * from dept_emp where emp_no > '7'; +----+-------------+----------+-------+---------------+---------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+---------------+---------+---------+------+--------+-------------+ | 1 | SIMPLE | dept_emp | range | PRIMARY | PRIMARY | 4 | NULL | 165785 | Using where | +----+-------------+----------+-------+---------------+---------+---------+------+--------+-------------+
ref：
出現(xiàn)于where操作符為‘=’，且where字段為非唯一索引的單表查詢或聯(lián)表查詢。
// 單表 mysql> explain select * from dept_emp where dept_no = 'd005'; +----+-------------+----------+------+---------------+---------+---------+-------+--------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+---------+---------+-------+--------+-----------------------+ | 1 | SIMPLE | dept_emp | ref | dept_no | dept_no | 4 | const | 145708 | Using index condition | +----+-------------+----------+------+---------------+---------+---------+-------+--------+-----------------------+ // 聯(lián)表 mysql> explain select * from dept_emp,departments where dept_emp.dept_no = departments.dept_no; +----+-------------+-------------+-------+---------------+-----------+---------+-------------------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+---------------+-----------+---------+-------------------------------+------+-------------+ | 1 | SIMPLE | departments | index | PRIMARY | dept_name | 42 | NULL | 9 | Using index | | 1 | SIMPLE | dept_emp | ref | dept_no | dept_no | 4 | employees.departments.dept_no | 1 | NULL | +----+-------------+-------------+-------+---------------+-----------+---------+-------------------------------+------+-------------+
eq_ref：
出現(xiàn)于where操作符為‘=’，且where字段為唯一索引的聯(lián)表查詢。
mysql> explain select * from departments,dept_desc where departments.dept_name=dept_desc.dept_name; +----+-------------+-------------+--------+---------------+-----------+---------+-------------------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+--------+---------------+-----------+---------+-------------------------------+------+-------------+ | 1 | SIMPLE | dept_desc | ALL | NULL | NULL | NULL | NULL | 1 | NULL | | 1 | SIMPLE | departments | eq_ref | dept_name | dept_name | 42 | employees.dept_desc.dept_name | 1 | Using index | +----+-------------+-------------+--------+---------------+-----------+---------+-------------------------------+------+-------------+
const：
出現(xiàn)于where操作符為‘=’，且where字段為唯一索引的單表查詢,此時最多只會匹配到一行。
mysql> explain select * from departments where dept_no = 'd005'; +----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | departments | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL | +----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+
綜上，單從type字段考慮效率，const > eq_ref > ref > range > index > all.
注意：我們不能僅僅根據(jù)type去判斷兩條sql的執(zhí)行速度。例如type為range的查詢不一定比type為index的全表查詢速度要快，還要看具體的sql。因為type為index時，查詢是不需要回表操作的，而type為range時，有可能需要回表操作。如sqlA("select dept_no from dept_emp;")和sqlB("select from_date from dept_emp where dept_no > 'd005';"),這個時候sqlB根據(jù)where條件掃描索引樹后，需要回表查詢相應的行數(shù)據(jù)，以獲取from_date的值，而sqlA雖然掃描了整顆索引樹，但并不需要回表，所以速度可能會比sqlB更快。
回表操作、索引相關(guān)可以閱讀mysql索引(覆蓋索引,聯(lián)合索引,索引下推)這篇文章
Extra
extra列會包含一些十分重要的信息，我們可以根據(jù)這些信息進行sql優(yōu)化
- using index: sql語句沒有where查詢條件，使用覆蓋索引，不需要回表查詢即可拿到結(jié)果
- using where: 沒有使用索引/使用了索引但需要回表查詢且沒有使用到下推索引
- using index && useing where: sql語句有where查詢條件，且使用覆蓋索引，不需要回表查詢即可拿到結(jié)果。
- Using index condition:使用索引查詢，sql語句的where子句查詢條件字段均為同一索引字段，且開啟索引下推功能，需要回表查詢即可拿到結(jié)果。
- Using index condition && using where:使用索引查詢，sql語句的where子句查詢條件字段存在非同一索引字段，且開啟索引下推功能，需要回表查詢即可拿到結(jié)果。
- using filesort: 當語句中存在order by時，且orderby字段不是索引，這個時候mysql無法利用索引進行排序，只能用排序算法重新進行排序，會額外消耗資源。
- Using temporary：建立了臨時表來保存中間結(jié)果，查詢完成之后又要把臨時表刪除。會很影響性能，需盡快優(yōu)化。
下推索引、覆蓋索引相關(guān)介紹可閱讀mysql索引(覆蓋索引,聯(lián)合索引,索引下推)這篇文章
有時在extra字段中會出現(xiàn)"Impossible WHERE noticed after reading const tables"這種描述。翻看網(wǎng)上資料后，個人發(fā)現(xiàn)這是mysql一種很怪的處理方式。
當sql語句滿足：
- 1、根據(jù)主鍵查詢或者唯一性索引查詢；
- 2、where操作符為"="時。
在sql語句優(yōu)化階段，mysql會先根據(jù)查詢條件找到相關(guān)記錄，這樣，如果這條數(shù)據(jù)不存在，實際上就進行了一次全掃描，然后得出一個結(jié)論，該數(shù)據(jù)不在表中。這樣對于并發(fā)較高的數(shù)據(jù)庫，會加大負載。所以，如果數(shù)據(jù)不用唯一的話，普通的索引比唯一索引更好用。（文章鏈接：MySQL慢查詢現(xiàn)象解決案例）
到此這篇關(guān)于mysql索引篇explain命令詳解的文章就介紹到這了,更多相關(guān)mysql explain命令內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
被告知在多實例場景下 MySQL Server hang 住，無法測試下去，原生版本不存在這個問題，而新版本上出現(xiàn)了這個問題，不禁心頭一顫，心中不禁感到奇怪，還好現(xiàn)場環(huán)境還在，為排查問題提供了一個好的環(huán)境，隨即便投入到緊張的問題排查過程當中2016-10-10
MySQL數(shù)據(jù)庫遷移data文件夾位置詳細步驟
這篇文章主要介紹了MySQL數(shù)據(jù)庫遷移data文件夾詳細步驟,需要的朋友可以參考下2014-03-03
Mysql中校對集utf8_unicode_ci與utf8_general_ci的區(qū)別說明
一直對utf8_unicode_ci與utf8_general_ci這2個校對集很迷惑，今天查了手冊有了點眉目。不過對中文字符集來說采用utf8_unicode_ci與utf8_general_ci時有何區(qū)別還是不清楚2012-03-03
mysql自動增量備份的例子(本地備份與遠程備份)，有需要的朋友可以參考下2013-02-02
這篇文章主要介紹了MySQL分表和分區(qū)的具體實現(xiàn)方法,文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值,需要的朋友可以參考下2019-06-06
這篇文章主要介紹了ssm框架如何調(diào)用mysql存儲過程,首先是建表,創(chuàng)建存儲過程,本文結(jié)合示例代碼給大家介紹的非常詳細,感興趣的朋友跟隨小編一起看看吧2024-05-05
MySQL實戰(zhàn)窗口函數(shù)SQL分析班級學生考試成績及生活消費
這篇文章主要為大家介紹了MySQL實戰(zhàn)，利用窗口函數(shù)SQL來分析班級學生的考試成績及生活消費的示例過程，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步2021-10-10
本文介紹了如何使用MySQL和Navicat進行列轉(zhuǎn)行操作,首先,創(chuàng)建了一個名為`grade`的表,并插入多條數(shù)據(jù),然后,通過修改查詢SQL語句,使用`CASE`和`IF`函數(shù)將列轉(zhuǎn)換為行,總結(jié)指出,`SUM`可以替換為`MAX`、`MIN`、`AVG`等聚合函數(shù),并且在查詢中需要對普通字段進行分組2025-01-01
本文主要介紹了MySQL軟件的下載、解壓、環(huán)境變量配置、初始化及啟動、登錄與密碼設置等步驟,詳細說明了每一步的操作方法,感興趣的可以了解一下2024-10-10
mysql密碼忘記怎么辦？卸載重裝？這篇文章主要為大家詳細介紹了不卸載也能重新設置密碼的方法，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-02-02