1. 首頁 > 生活百科 > >

「MySQL高級篇」explain分析SQL，索引失效&&常見優化場景( 二 )

生活百科


NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALLsystem > const > eq_ref > ref > range > index > ALL?
一般至少要達到range級別，最好達到ref。
const唯一索引，非關聯查詢
eq_ref ， refeq_ref 跟 const 的區別是：兩者都利用唯一索引，但前者是關聯查詢，后者只是普通查詢？eq_ref 跟 ref 的區別：后者是非唯一索引
index，all都是讀全表，區別在于index是遍歷索引樹讀取，而ALL是從硬盤中讀取 。

「MySQL高級篇」explain分析SQL，索引失效&&常見優化場景

文章插圖
不走索引就會遍歷全表
「MySQL高級篇」explain分析SQL，索引失效&&常見優化場景

文章插圖
possible_keys ， keypossible_keys : 顯示可能應用在這張表的索引，一個或多個 。?
key ：實際使用的索引，如果為NULL，則沒有使用索引 。（可能是沒有走索引，需要分析）?
key_len : 表示索引中使用的字節數，在不損失精確性的前提下，長度越短越好。
  • 單列索引，那么需要將整個索引長度算進去；
  • 多列索引，不是所有列都能用到 ， 需要計算查詢中實際用到的列 。

「MySQL高級篇」explain分析SQL，索引失效&&常見優化場景

文章插圖
ref顯示索引的哪一列被使用了，如果可能的話，是一個常數 。
  • 當使用常量等值查詢 ， 顯示const
  • 當關聯查詢時，會顯示相應關聯表的關聯字段
  • 如果查詢條件使用了表達式、函數，或者條件列發生內部隱式轉換，可能顯示為func
  • 其他情況為null
  1. id是索引，而且是id=1，一個常數，故ref = const
  2. user_id不是索引，ref直接為null

「MySQL高級篇」explain分析SQL，索引失效&&常見優化場景

文章插圖
t1.id是索引，且=號后邊不是常量，故顯示t1.id，即顯示相應關聯表的關聯字段
「MySQL高級篇」explain分析SQL，索引失效&&常見優化場景

文章插圖
rows
掃描行的數量 ， 一般越小越好
  • 用索引 rows 就為1，無論是唯一索引還是非唯一索引
  • 其他情況一般是全表掃描，rows等于表的行數 。

「MySQL高級篇」explain分析SQL，索引失效&&常見優化場景

文章插圖
filtered表里符合條件的記錄數的所占的百分比 。
extra其他的額外的執行計劃信息，在該列展示，需要把前兩個優化為using index 。
EXTRA含義using filesort說明mysql會對數據使用一個外部的索引排序 ， 而不是按照表內的索引順序進行讀取，表示無法利用索引完成的排序操作，稱為 “文件排序”, 效率低 。using temporary使用了臨時表保存中間結果，MySQL在對查詢結果排序時使用臨時表 。常見于 order by 和 group by； 效率低using index表示相應的select操作使用了覆蓋索引，直接從索引中過濾掉不需要的結果 ， 無需回表，效率不錯 。using index condition索引下推??！查找使用翍Z饕?，但是需要回表查询数舅|聳本褪且蛭饕忻揮型耆檠?/td>
具體using index condition中的索引下推是什么意思，可以參考這篇 索引的原理&&設計原則
using where
不同版本好像不一樣
5.7：表示 MySQL 首先從數據表（存儲引擎）中讀取記錄 ， 返回給 MySQL 的 server 層，然后在 server 層過濾掉不滿足條件的記錄，即無法直接在存儲引擎過濾掉 。簡單來說，就是查詢時where中用的不是索引 。
「MySQL高級篇」explain分析SQL，索引失效&&常見優化場景

文章插圖
現在 ， 我們知道怎么用explain來分析SQL語句了，自然可以來剖析我們的SQL語句的性能，不過早有先人給我們總結了幾個需要優化的場景-->索引失效?
【二、索引失效】的幾個場景0. SQL準備create table `tb_seller` ( `sellerid` varchar (100), `name` varchar (100), `nickname` varchar (50), `password` varchar (60), `status` varchar (1), `address` varchar (100), `createtime` datetime,primary key(`sellerid`))engine=innodb default charset=utf8mb4; insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('alibaba','阿里巴巴','阿里小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('baidu','百度科技有限公司','百度小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('huawei','華為科技有限公司','華為小店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itcast','傳智播客教育科技有限公司','傳智播客','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itheima','黑馬程序員','黑馬程序員','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('luoji','羅技科技有限公司','羅技小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('oppo','OPPO科技有限公司','OPPO官方旗艦店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('ourpalm','掌趣科技股份有限公司','掌趣小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('qiandu','千度科技','千度小店','e10adc3949ba59abbe56e057f20f883e','2','北京市','2088-01-01 12:00:00');insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('sina','新浪科技有限公司','新浪官方旗艦店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('xiaomi','小米科技','小米官方旗艦店','e10adc3949ba59abbe56e057f20f883e','1','西安市','2088-01-01 12:00:00');insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('yijia','宜家家居','宜家家居旗艦店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');-- 創建聯合索引create index idx_seller_name_sta_addr on tb_seller(name,status,address);

推薦閱讀