sql优化-1-查询执行计划:explain
mysql优化的官网文档:https://dev.mysql.com/doc/refman/8.0/en/optimization.html
SQL执行流程
a. sql编写顺序
select dinstinct .. from .. join .. on .. where .. group by .. having .. order by ..
b. mysql服务器解析顺序:
from .. on .. join .. where .. group by .. having .. select dinstinct ..order by ..
SQL性能优化
MySQL查询优化器会自动解析优化我们写的sql,结果可能不是我们想要的,所以这时候需要分析sql的执行计划,看看sql到底是怎么去执行的。
分析SQL的执行计划使用explain
关键字,可以查看SQL优化器执行SQL语句,从而让开发人员知道自己编写的sql的执行情况,从而进行优化。
测试数据
-- 课程表 create table course( cid int, cname varchar(32), tid int ); insert into course (cid,cname,tid) values (1,'java',1),(2,'html',1),(3,'sql',2),(4,'web',3); -- 教师表 create table teacher( tid int, tname varchar(32), tcid int ); insert into teacher (tid,tname,tcid) values (1,'tz',1),(2,'tw',2),(3,'tl',3); -- 教师信息表 create table teacherCard( tcid int, tcdesc varchar(32) ); insert into teacherCard (tcid,tcdesc) values (1,'tzdesc'),(2,'twdesc'),(3,'tldesc');
explain:查看sql执行计划
explain select * from course;
mysql> explain select * from course; +----+-------------+--------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | course | ALL | NULL | NULL | NULL | NULL | 4 | NULL | +----+-------------+--------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.03 sec) mysql>
id:编号 select_type:查询类型 table:表 type:类型 possible_keys:预测用到的索引 key:实际使用的索引 key_len:实际使用索引的长度 ref:表之间的引用 rows: ~~通过索引~~ 查询到的数据量 Extra:额外的信息
id编号
查询课程编号为2 或 教师证编号为3 的老师信息,看一下这条sql的执行情况。
- id:id值相同,从上往下 顺序执行
执行顺序: teacher3 --》 teachercard3 --》 course4
mysql> explain select * from course,teacher,teacherCard where course.tid = teacher.tid and teacher.tcid = teacherCard.tcid and (course.cid=2 or teacher.tid=3); +----+-------------+-------------+------+---------------+------+---------+------+------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------+------+---------+------+------+----------------------------------------------------+ | 1 | SIMPLE | teacher | ALL | NULL | NULL | NULL | NULL | 3 | NULL | | 1 | SIMPLE | teacherCard | ALL | NULL | NULL | NULL | NULL | 3 | Using where; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | course | ALL | NULL | NULL | NULL | NULL | 4 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-------------+------+---------------+------+---------+------+------+----------------------------------------------------+ 3 rows in set (0.04 sec)
- id值不同:id值越大越优先查询(本质:在嵌套子查询时,先查内层 再查外层)
id值有相同,又有不同:值越大越优先;id值相同,从上往下 顺序执行
-- 查询教授SQL课程的老师的描述(desc) mysql> explain select course.cname,teacher.tname,teacherCard.tcdesc from course,teacher,teacherCard where course.tid = teacher.tid and teacher.tcid = teacherCard.tcid and course.cname='sql'; +----+-------------+-------------+------+---------------+------+---------+------+------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------+------+---------+------+------+----------------------------------------------------+ | 1 | SIMPLE | teacherCard | ALL | NULL | NULL | NULL | NULL | 3 | NULL | | 1 | SIMPLE | course | ALL | NULL | NULL | NULL | NULL | 4 | Using where; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | teacher | ALL | NULL | NULL | NULL | NULL | 6 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-------------+------+---------------+------+---------+------+------+----------------------------------------------------+ 3 rows in set (0.07 sec) -- 将以上 多表查询 转为子查询形式: mysql> explain select teacherCard.tcdesc from teacherCard where teacherCard.tcid = (select tcid from teacher where teacher.tid = (select tid from course where course.cname='sql') ); +----+-------------+-------------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------+------+---------+------+------+-------------+ | 1 | PRIMARY | teacherCard | ALL | NULL | NULL | NULL | NULL | 3 | Using where | | 2 | SUBQUERY | teacher | ALL | NULL | NULL | NULL | NULL | 6 | Using where | | 3 | SUBQUERY | course | ALL | NULL | NULL | NULL | NULL | 4 | Using where | +----+-------------+-------------+------+---------------+------+---------+------+------+-------------+ 3 rows in set (0.05 sec) -- 子查询+多表 形式: mysql> explain select teacherCard.tcdesc from teacher,teacherCard where teacher.tcid = teacherCard.tcid and teacher.tid= (select tid from course where course.cname='sql'); +----+-------------+-------------+------+---------------+------+---------+------+------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------+------+---------+------+------+----------------------------------------------------+ | 1 | PRIMARY | teacherCard | ALL | NULL | NULL | NULL | NULL | 3 | NULL | | 1 | PRIMARY | teacher | ALL | NULL | NULL | NULL | NULL | 6 | Using where; Using join buffer (Block Nested Loop) | | 2 | SUBQUERY | course | ALL | NULL | NULL | NULL | NULL | 4 | Using where | +----+-------------+-------------+------+---------------+------+---------+------+------+----------------------------------------------------+ 3 rows in set (0.05 sec) mysql>
select_type和table
select_type 查询类型
table 查询涉及的表,或者临时表类型及id号
(1)SIMPLE 简单查询:不包含子查询或Union查询的sql语句。
(2)PRIMARY 主查询:查询中若包含任何复杂的子部分,最外层查询则被标记为主查询。
(3) SUBQUERY 子查询。
(4) DERIVED 衍生查询,
派生表的处理方式是MySQL首先执行子查询(DERIVED部分),得到结果集,然后将这个结果集当作一个临时表来对待,供外部查询(PRIMARY查询)使用。
mysql> explain select * from (select cname,tid from course) a; +----+-------------+------------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+---------------+------+---------+------+------+-------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 4 | NULL | | 2 | DERIVED | course | ALL | NULL | NULL | NULL | NULL | 4 | NULL | +----+-------------+------------+------+---------------+------+---------+------+------+-------+ 2 rows in set (0.07 sec)
(5)UNION 和 UNION RESULT
若第二个select出现在union之后,则被标记为UNION。
UNION RESULT表示合并所有UNION操作的最终结果集。结果集是一个临时虚拟表,标记为<union1,2>
,数字1和2代表前面两个SELECT查询的id值。
mysql> explain select * from course union select * from teacher; +------+--------------+------------+------+---------------+------+---------+------+------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------+------------+------+---------------+------+---------+------+------+-----------------+ | 1 | PRIMARY | course | ALL | NULL | NULL | NULL | NULL | 4 | NULL | | 2 | UNION | teacher | ALL | NULL | NULL | NULL | NULL | 6 | NULL | | NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary | +------+--------------+------------+------+---------------+------+---------+------+------+-----------------+ 3 rows in set (0.12 sec) mysql>
type:索引类型、或者直接称为类型
常见的type值及其优先级大致如下(从最优到最差):
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_merge > range > index > ALL
实际见的比较多的类型:system > const > eq_ref > ref > range > index > all
越往左性能越高,system,const只是理想情况;实际能达到ref>range
system
在MySQL中,EXPLAIN输出的type列的system值通常表示查询可以非常快速地完成,因为它仅涉及系统表或已知的单行结果。然而,是否能获得system类型取决于多种因素,包括查询的具体情况、表的存储引擎、数据分布以及MySQL服务器的配置。
准备test01
create table test01 ( tid int(3), tname varchar(20) ); insert into test01 values(1,'a');
- 衍生表只有一条数据的主查询
explain select * from (select * from test01) t where tid=1;
⼦查询查询得出⼀条数据(该条数据构成衍⽣表),通过衍⽣表的数据进⾏主查询,其衍⽣表的索引类型为system。
linux下 可以达到system
windows下同样的sql则达不到
- 只有一条数据的表(系统表)
使用innodb存储引擎不行,是const
使用myisam可以得到system,不需要加索引
参考 mysql EXPLAIN type列的system值
mysql> create table test02 ( tid int(3), tname varchar(20) ) engine = MyISAM; Query OK, 0 rows affected (0.01 sec) mysql> insert into test02 values(1,'a'); Query OK, 1 row affected (0.00 sec) mysql> explain select tid from test02 where tid=1; +----+-------------+--------+--------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+--------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | test02 | system | NULL | NULL | NULL | NULL | 1 | NULL | +----+-------------+--------+--------+---------------+------+---------+------+------+-------+ 1 row in set (0.05 sec) mysql> explain select * from (select * from test02) t where tid=1; +----+-------------+------------+--------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+------+---------+------+------+-------+ | 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | NULL | | 2 | DERIVED | test02 | system | NULL | NULL | NULL | NULL | 1 | NULL | +----+-------------+------------+--------+---------------+------+---------+------+------+-------+ 2 rows in set (0.07 sec) mysql>
const
使用Primary key或unique索引仅仅能查到一条数据的SQL,如主键置于where列表中,MySQL就能将该查询转换为一个常量例子:
- 主键索引 可以达到const
alter table test01 add constraint tid_pk primary key(tid); -- 增加主键 -- tid是主键 mysql> explain select tid from test01 where tid=1; +----+-------------+--------+-------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------+---------+-------+------+-------------+ | 1 | SIMPLE | test01 | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index | +----+-------------+--------+-------+---------------+---------+---------+-------+------+-------------+ 1 row in set (0.06 sec)
- 单值索引 不行,达不到const
alter table test01 drop primary key ; -- 删除主键 create index test01_index on test01(tid); -- 给tid创建索引 explain select tid from test01 where tid = 1;
eq_ref:唯一性索引
唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,常见于主键或唯一索引扫描,例子:被驱动表使用主键索引,结果唯一
- 主键索引
-- 设置teacherCard的tcid为主键索引,teacher表中无索引 mysql> alter table teacherCard add constraint pk_tcid primary key(tcid); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select t.tid,tc.tcid from teacher t,teacherCard tc where t.tcid=tc.tcid; +----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------------+ | 1 | SIMPLE | tc | index | PRIMARY | PRIMARY | 4 | NULL | 3 | Using index | | 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 6 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------------+ 2 rows in set (0.07 sec) mysql> select * from teacher; +-----+-------+------+ | tid | tname | tcid | +-----+-------+------+ | 1 | tz | 1 | | 2 | tw | 2 | | 3 | tl | 3 | | 4 | tll | 4 | | 5 | tik | 5 | | 6 | tzz | 6 | +-----+-------+------+ 6 rows in set (0.05 sec) mysql> select * from teacherCard; +------+--------+ | tcid | tcdesc | +------+--------+ | 1 | tzdesc | | 2 | twdesc | | 3 | tldesc | +------+--------+ 3 rows in set (0.05 sec) mysql> delete from teacher where tid > 3; Query OK, 3 rows affected (0.01 sec) mysql> select * from teacher; +-----+-------+------+ | tid | tname | tcid | +-----+-------+------+ | 1 | tz | 1 | | 2 | tw | 2 | | 3 | tl | 3 | +-----+-------+------+ 3 rows in set (0.04 sec) mysql> explain select t.tid,tc.tcid from teacher t,teacherCard tc where t.tcid=tc.tcid; +----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+ | 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 3 | Using where | | 1 | SIMPLE | tc | eq_ref | PRIMARY | PRIMARY | 4 | db1.t.tcid | 1 | Using index | +----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+ 2 rows in set (0.06 sec) mysql> insert into teacherCard (tcid,tcdesc) values (4,'tzdesc4'); Query OK, 1 row affected (0.00 sec) mysql> explain select t.tid,tc.tcid from teacher t,teacherCard tc where t.tcid=tc.tcid; +----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+ | 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 3 | Using where | | 1 | SIMPLE | tc | eq_ref | PRIMARY | PRIMARY | 4 | db1.t.tcid | 1 | Using index | +----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+ 2 rows in set (0.06 sec) mysql>
从表(主键表)即 teacherCard 为 eq_ref :
以上SQL,用到的索引是t.tcid,即teacher表中的tcid字段;如果teacher表的数据个数 和 连接查询的数据个数一致(都是3条数据),则有可能满足eq_ref,否则无法满足。
1.给从表(主键表)添加数据,数据多余 主表(无索引的表),从表也可以得到eq_ref
2.给主表(无索引表)添加数据,数据多余 从表(主键表),不能得到eq_ref
总结:应该说是 主表的数据都要在从表中匹配上,即便从表的数据多于主表。
- 唯一索引
-- 设置teacherCard的tcid为唯一索引,teacher表中无索引 mysql> alter table teacherCard add constraint uq_tcid unique(tcid);
mysql> explain select t.tid,tc.tcid from teacher t,teacherCard tc where t.tcid=tc.tcid; +----+-------------+-------+------+---------------+---------+---------+------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+---------+---------+------------+------+-------------+ | 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 3 | Using where | | 1 | SIMPLE | tc | ref | uq_tcid | uq_tcid | 5 | db1.t.tcid | 1 | Using index | +----+-------------+-------+------+---------------+---------+---------+------------+------+-------------+ 2 rows in set (0.03 sec) mysql>
ref:非唯一性索引
非唯一性索引扫描,返回匹配某个单独值的所有行(0,一条,多条),本质上也是一种索引访问,返回所有匹配某个单独值的行,然而可能会找到多个符合条件的行,应该属于查找和扫描的混合体
给teacher的tname添加索引,根据tname查询可以得到ref
alter table teacher add index index_name(tname);
返回1条可以得到ref
mysql> select tname from teacher where tname='tz'; +-------+ | tname | +-------+ | tz | +-------+ 1 row in set (0.00 sec) mysql> explain select tname from teacher where tname='tz'; +----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+ | 1 | SIMPLE | teacher | ref | index_name | index_name | 99 | const | 1 | Using where; Using index | +----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+ 1 row in set (0.01 sec) mysql>
返回0条也可以得到ref
mysql> select tname from teacher where tname='tz1'; Empty set (0.00 sec) mysql> explain select tname from teacher where tname='tz1'; +----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+ | 1 | SIMPLE | teacher | ref | index_name | index_name | 99 | const | 1 | Using where; Using index | +----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec) mysql>
返回多条可以得到ref
mysql> insert into teacher values(4,'tz',4); -- 添加一条数据 Query OK, 1 row affected (0.01 sec) mysql> select * from teacher where tname = 'tz'; +------+-------+------+ | tid | tname | tcid | +------+-------+------+ | 1 | tz | 1 | | 4 | tz | 4 | +------+-------+------+ 2 rows in set (0.00 sec) mysql> explain select tname from teacher where tname='tz'; +----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+ | 1 | SIMPLE | teacher | ref | index_name | index_name | 99 | const | 2 | Using where; Using index | +----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+ 1 row in set (0.01 sec) mysql>
range:检索指定范围的行
只检索给定范围的行,使用一个索引来选择行,一般是where后面的范围(between,> < >= 特殊:in有时候会失效,从而转为 无索引all)
给tid添加索引
alter table teacher add index tid_index(tid);
大于、小于
mysql> explain select * from teacher where tid > 2; +----+-------------+---------+-------+---------------+-----------+---------+------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+---------------+-----------+---------+------+------+-----------------------+ | 1 | SIMPLE | teacher | range | tid_index | tid_index | 5 | NULL | 2 | Using index condition | +----+-------------+---------+-------+---------------+-----------+---------+------+------+-----------------------+ 1 row in set (0.05 sec) mysql> explain select * from teacher where tid > 2 and tid < 4; +----+-------------+---------+-------+---------------+-----------+---------+------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+---------------+-----------+---------+------+------+-----------------------+ | 1 | SIMPLE | teacher | range | tid_index | tid_index | 5 | NULL | 1 | Using index condition | +----+-------------+---------+-------+---------------+-----------+---------+------+------+-----------------------+ 1 row in set (0.05 sec) mysql>
in有可能会失效
mysql> explain select * from teacher where tid in (3,4); +----+-------------+---------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | teacher | ALL | tid_index | NULL | NULL | NULL | 4 | Using where | +----+-------------+---------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.05 sec) mysql>
between ... and ... 可能会失效,仅仅数据的改变都可能会引起索引的失效SQL优化,是一种概率层面的优化。
mysql> explain select * from teacher where tid between 2 and 4; +----+-------------+---------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | teacher | ALL | tid_index | NULL | NULL | NULL | 4 | Using where | +----+-------------+---------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.05 sec) mysql> explain select * from teacher where tid between 3 and 4; +----+-------------+---------+-------+---------------+-----------+---------+------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+---------------+-----------+---------+------+------+-----------------------+ | 1 | SIMPLE | teacher | range | tid_index | tid_index | 5 | NULL | 2 | Using index condition | +----+-------------+---------+-------+---------------+-----------+---------+------+------+-----------------------+ 1 row in set (0.05 sec) mysql>
index
全索引扫描,Full index Scan,只需要扫描索引表,不需要非索引列数据(即不需要回表查询)
mysql> explain select tid from teacher; +----+-------------+---------+-------+---------------+-----------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+---------------+-----------+---------+------+------+-------------+ | 1 | SIMPLE | teacher | index | NULL | tid_index | 5 | NULL | 4 | Using index | +----+-------------+---------+-------+---------------+-----------+---------+------+------+-------------+ 1 row in set (0.05 sec) mysql> explain select tid,tname from teacher; +----+-------------+---------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | teacher | ALL | NULL | NULL | NULL | NULL | 4 | NULL | +----+-------------+---------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.05 sec) mysql>
all
全表扫,Full Table Scan,将遍历全表以找到匹配行,需要回表查询
mysql> explain select * from teacher; -- 查询表中所有数据 +----+-------------+---------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | teacher | ALL | NULL | NULL | NULL | NULL | 4 | NULL | +----+-------------+---------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.05 sec) mysql>
possible_keys与key
possible_keys:可能用到的索引,是一种预测,不准。
key:实际使用到的索引
情况1:预测不使用索引,但实际却使用了索引 索引类型为index的例子。
情况2:预测使用索引,但实际却没使用索引
情况3:如果possible_key/key是NULL,则说明没用索引
key_len:索引的长度
表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度,在不损失精确度的情况下,长度越短越好
key_len显示的值为索引字段最大的可能长度,并非实际使用长度,即key_len是根据定义计算而得,不是通过表内检索出的
作用:用于判断复合索引是否被完全使用 (a,b,c)
长度计算方式 参考Mysql 索引 key_len 计算方式最强解析
先使用show create table teacher; 查看 字段长度
1.数据类型本身占用空间(字符类型需要乘以字符集长度)
2.如果可以为空+1
3.变长类型+2
- 先计算一个非空定长的char类型的索引长度
mysql> create table test_k1 ( name char(20) not null default '' ); Query OK, 0 rows affected (0.02 sec) mysql> alter table test_k1 add index index_name(name); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql>
mysql> explain select * from test_k1 where name = ''; +----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+ | 1 | SIMPLE | test_k1 | ref | index_name | index_name | 20 | const | 1 | Using where; Using index | +----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+ 1 row in set (0.04 sec) mysql> explain select name from test_k1 where name = 'tz'; +----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+ | 1 | SIMPLE | test_k1 | ref | index_name | index_name | 20 | const | 1 | Using where; Using index | +----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+ 1 row in set (0.05 sec) mysql> show create table test_k1; +---------+---------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+---------------------------------------------------------------------------------------------------------------------------------+ | test_k1 | CREATE TABLE `test_k1` ( `name` char(20) NOT NULL DEFAULT '', KEY `index_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +---------+---------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.04 sec) mysql>
- 新插入一个可以为null的namel列 再看看索引长度
mysql> alter table test_k1 add column namel char(20); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table test_k1 add index index_namel(namel); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select * from test_k1 where namel =''; +----+-------------+---------+------+---------------+-------------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+-------------+---------+-------+------+-----------------------+ | 1 | SIMPLE | test_k1 | ref | index_namel | index_namel | 21 | const | 1 | Using index condition | +----+-------------+---------+------+---------------+-------------+---------+-------+------+-----------------------+ 1 row in set (0.05 sec) mysql> show create table test_k1; +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test_k1 | CREATE TABLE `test_k1` ( `name` char(20) NOT NULL DEFAULT '', `namel` char(20) DEFAULT NULL, KEY `index_name` (`name`), KEY `index_namel` (`namel`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.05 sec) mysql>
如果索引字段可以为Null,则会使用1个字节用于标识。
- name和namel的单值索引改成一个复合索引
mysql> drop index index_name on test_k1 ; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> drop index index_namel on test_k1 ; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table test_k1 add index name_name1_index(name,namel); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select * from test_k1 where namel = ''; +----+-------------+---------+-------+---------------+------------------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+---------------+------------------+---------+------+------+--------------------------+ | 1 | SIMPLE | test_k1 | index | NULL | name_name1_index | 41 | NULL | 1 | Using where; Using index | +----+-------------+---------+-------+---------------+------------------+---------+------+------+--------------------------+ 1 row in set (0.05 sec) mysql> explain select * from test_k1 where name = ''; +----+-------------+---------+------+------------------+------------------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+------------------+------------------+---------+-------+------+--------------------------+ | 1 | SIMPLE | test_k1 | ref | name_name1_index | name_name1_index | 20 | const | 1 | Using where; Using index | +----+-------------+---------+------+------------------+------------------+---------+-------+------+--------------------------+ 1 row in set (0.05 sec) mysql>
- 再插入一个varchar类型可为null的列
mysql> alter table test_k1 add column name2 varchar(20); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table test_k1 add index name2_index (name2); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select * from test_k1 where name2 = ''; +----+-------------+---------+------+---------------+-------------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+-------------+---------+-------+------+-----------------------+ | 1 | SIMPLE | test_k1 | ref | name2_index | name2_index | 23 | const | 1 | Using index condition | +----+-------------+---------+------+---------------+-------------+---------+-------+------+-----------------------+ 1 row in set (0.05 sec) mysql>
20*1(拉丁 为单字节字符编码) + 2 (可变长度) +1 (可以为null)
ref :注意与type中的ref值区分。
作用:指明当前表所参照的字段。select .... where a.c = b.x ;
(可以是常量const)
mysql> explain select * from course c, teacher t where c.tid = t.tid and t.tname ='tw' ; +----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+ | 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 3 | Using where | | 1 | SIMPLE | c | ALL | tid_index | NULL | NULL | NULL | 4 | Using where; Using join buffer | +----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+ 2 rows in set (0.00 sec)
为null看看是不是没有索引,没有索引先加索引
重置测试表的数据,并添加索引
alter table course add index tid_index (tid);
alter table teacher add index index_tid (tid); --前面已添加索引
alter table teacher add index tname_index (tname);
mysql> explain select * from course c, teacher t where c.tid = t.tid and t.tname ='tw' ; +----+-------------+-------+------+-----------------------+-------------+---------+------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+-----------------------+-------------+---------+------------+------+-------------+ | 1 | SIMPLE | t | ref | tname_index,index_tid | tname_index | 99 | const | 1 | Using where | | 1 | SIMPLE | c | ref | tid_index | tid_index | 5 | test.t.tid | 1 | Using where | +----+-------------+-------+------+-----------------------+-------------+---------+------------+------+-------------+ 2 rows in set (0.00 sec)
rows:被索引优化査询的数据个数(实际通过索引而査询到的数据个数)
数据小的表 优先查询
mysql> explain select * from course,teacher,teacherCard where course.tid = teacher.tid and teacher.tcid = teacherCard.tcid and (course.cid=2 or teacher.tid=3); +----+-------------+-------------+------+---------------+------+---------+------+------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------+------+---------+------+------+----------------------------------------------------+ | 1 | SIMPLE | teacher | ALL | NULL | NULL | NULL | NULL | 3 | NULL | | 1 | SIMPLE | teacherCard | ALL | NULL | NULL | NULL | NULL | 3 | Using where; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | course | ALL | NULL | NULL | NULL | NULL | 4 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-------------+------+---------------+------+---------+------+------+----------------------------------------------------+ 3 rows in set (0.04 sec)
给teacher表插入3条数据
mysql> insert into teacher (tid,tname,tcid) values (4,'tll',4),(5,'tik',5),(6,'tzz',6); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> explain select * from course,teacher,teacherCard where course.tid = teacher.tid and teacher.tcid = teacherCard.tcid and (course.cid=2 or teacher.tid=3); +----+-------------+-------------+------+---------------+------+---------+------+------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------+------+---------+------+------+----------------------------------------------------+ | 1 | SIMPLE | teacherCard | ALL | NULL | NULL | NULL | NULL | 3 | NULL | | 1 | SIMPLE | course | ALL | NULL | NULL | NULL | NULL | 4 | Using where; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | teacher | ALL | NULL | NULL | NULL | NULL | 6 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-------------+------+---------------+------+---------+------+------+----------------------------------------------------+ 3 rows in set (0.05 sec)
执行顺序: teachercard3 --》 course4 --》 teacher6
表的执行顺序 因数据的个数改变而改变的原因:笛卡儿积
a b c 4 3 2 = 2*3=6*4=24 3*4=12*2=24
Extra:
准备数据
create table user ( id int, name char(3), age int, index idx_id(id), index idx_name(name), index idx_age(age) ); -- 新插入一列,并添加索引 alter table user add height int; alter table user add index index_height(height);
(i). using filesort:文件内排序(性能极差)
得到所需结果集,需要对所有记录进行文件排序。
典型的,在一个没有建立索引的列上进行了order by,就会触发filesort,常见的优化方案是,在order by的列上添加索引,避免每次查询都全量排序。
对于单值索引,如果order和where是同一个字段,则不会出现using filesort;如果不是同一个字段,则会出现using filesort
mysql> explain select * from user where height =180 order by id; +----+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------------+ | 1 | SIMPLE | user | ref | index_height | index_height | 5 | const | 1 | Using where; Using filesort | +----+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------------+ 1 row in set (0.00 sec) mysql> explain select * from user where height =180 order by height; +----+-------------+-------+------+---------------+--------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+--------------+---------+-------+------+-------------+ | 1 | SIMPLE | user | ref | index_height | index_height | 5 | const | 1 | Using where | +----+-------------+-------+------+---------------+--------------+---------+-------+------+-------------+ 1 row in set (0.00 sec)
对于复合索引,需要符合 最佳左前缀,否则会出现using filesort
删除所有索引,创建一个复合索引
alter table user add index index_id_name_age(id,name,age);
Ⅰ:仅使用where的情况
explain select id from user where id=20; explain select id from user where id=20 and name='tz'; explain select id from user where id=20 and name='tz' and age=20; explain select id from user where name='tz'; explain select id from user where name='tz' and age=20; explain select id from user where id=20 and age=20; mysql> explain select id from user where id=20; +----+-------------+-------+------+-------------------+-------------------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+-------------------+-------------------+---------+-------+------+--------------------------+ | 1 | SIMPLE | user | ref | index_id_name_age | index_id_name_age | 5 | const | 1 | Using where; Using index | +----+-------------+-------+------+-------------------+-------------------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec) mysql> explain select id from user where id=20 and name='tz'; +----+-------------+-------+------+-------------------+-------------------+---------+-------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+-------------------+-------------------+---------+-------------+------+--------------------------+ | 1 | SIMPLE | user | ref | index_id_name_age | index_id_name_age | 15 | const,const | 1 | Using where; Using index | +----+-------------+-------+------+-------------------+-------------------+---------+-------------+------+--------------------------+ 1 row in set (0.00 sec) mysql> explain select id from user where id=20 and name='tz' and age=20; +----+-------------+-------+------+-------------------+-------------------+---------+-------------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+-------------------+-------------------+---------+-------------------+------+--------------------------+ | 1 | SIMPLE | user | ref | index_id_name_age | index_id_name_age | 20 | const,const,const | 1 | Using where; Using index | +----+-------------+-------+------+-------------------+-------------------+---------+-------------------+------+--------------------------+ 1 row in set (0.00 sec)
总结i.如果(a,b,c,d)复合索引和使用的顺序全部一致(且不跨列使用),则复合索引全部使用。
Ⅱ:where和order by同时使用的情况
-- where和order by使用的字段拼起来不要跨列,并且不能倒序 explain select * from user where id = 1 order by name; explain select * from user where name='tz' order by id; -- Using filesort explain select * from user where age=20 order by id; -- Using filesort explain select * from user where name='tz' order by age; -- Using filesort -- and前后的字段顺序可以调换,不影响结果 explain select * from user where id=1 and name='tz' order by age; explain select * from user where name='tz' and id=1 order by age; explain select * from user where name='tz' order by name; -- where和order后面是同一字段 不发生Using filesort
mysql> explain select * from user where name='tz' order by id; +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+ | 1 | SIMPLE | user | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Using filesort | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+ 1 row in set (0.00 sec)
(ii). using temporary(性能较低)
需要建立临时表(temporary table)来暂存中间结果。
group by和order by同时存在,且作用于不同的字段时,就会建立临时表,以便计算出最终的结果集。
# 仅使用复合索引 mysql> explain select * from user where id=1 group by name order by name; +----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+ | 1 | SIMPLE | user | ref | index_id_name_age | index_id_name_age | 5 | const | 1 | Using where | +----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+ 1 row in set (0.01 sec) mysql> explain select * from user where id=1 group by name order by age; +----+-------------+-------+------+-------------------+-------------------+---------+-------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+-------------------+-------------------+---------+-------+------+----------------------------------------------+ | 1 | SIMPLE | user | ref | index_id_name_age | index_id_name_age | 5 | const | 1 | Using where; Using temporary; Using filesort | +----+-------------+-------+------+-------------------+-------------------+---------+-------+------+----------------------------------------------+ 1 row in set (0.00 sec) mysql> explain select * from user where id=1 group by age order by age; +----+-------------+-------+------+-------------------+-------------------+---------+-------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+-------------------+-------------------+---------+-------+------+----------------------------------------------+ | 1 | SIMPLE | user | ref | index_id_name_age | index_id_name_age | 5 | const | 1 | Using where; Using temporary; Using filesort | +----+-------------+-------+------+-------------------+-------------------+---------+-------+------+----------------------------------------------+ 1 row in set (0.01 sec) # 仅单值索引 mysql> explain select height from user where height=1 group by height order by height; +----+-------------+-------+------+---------------+--------------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+--------------+---------+-------+------+--------------------------+ | 1 | SIMPLE | user | ref | index_height | index_height | 5 | const | 1 | Using where; Using index | +----+-------------+-------+------+---------------+--------------+---------+-------+------+--------------------------+ 1 row in set (0.01 sec) # 单值索引和复合索引一起使用 仅使用 一个单值索引 和 复合索引中的一个字段 mysql> explain select height from user where height=1 group by name order by name; -- 会触发Using temporary +----+-------------+-------+------+---------------+--------------+---------+-------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+--------------+---------+-------+------+----------------------------------------------+ | 1 | SIMPLE | user | ref | index_height | index_height | 5 | const | 1 | Using where; Using temporary; Using filesort | +----+-------------+-------+------+---------------+--------------+---------+-------+------+----------------------------------------------+ 1 row in set (0.00 sec) mysql> explain select height from user where id=1 group by height order by height; -- 会触发Using temporary +----+-------------+-------+------+-------------------+-------------------+---------+-------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+-------------------+-------------------+---------+-------+------+----------------------------------------------+ | 1 | SIMPLE | user | ref | index_id_name_age | index_id_name_age | 5 | const | 1 | Using where; Using temporary; Using filesort | +----+-------------+-------+------+-------------------+-------------------+---------+-------+------+----------------------------------------------+ 1 row in set (0.00 sec) mysql> explain select height from user where height=1 group by name order by height; +----+-------------+-------+------+---------------+--------------+---------+-------+------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+--------------+---------+-------+------+------------------------------+ | 1 | SIMPLE | user | ref | index_height | index_height | 5 | const | 1 | Using where; Using temporary | +----+-------------+-------+------+---------------+--------------+---------+-------+------+------------------------------+ mysql> explain select height from user where height=1 group by id order by height; +----+-------------+-------+------+---------------+--------------+---------+-------+------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+--------------+---------+-------+------+------------------------------+ | 1 | SIMPLE | user | ref | index_height | index_height | 5 | const | 1 | Using where; Using temporary | +----+-------------+-------+------+---------------+--------------+---------+-------+------+------------------------------+
(iii). using index :索引覆盖(性能提升)
SQL所需要返回的所有列数据均在已使用的一棵索引树上,而无需访问实际的行记录。
不读取原文件,只从索引文件中获取数据(不需要回表查询)
例如:user表中有两个索引 一个复合索引index_id_name_age, 一个单值索引 index_height
如果用到了索引覆盖(using index时) 会对possible_keys和key造成影响:
a. 如果没有where,则索引只出现在key中;
mysql> explain select height from user;
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
| 1 | SIMPLE | user | index | NULL | index_height | 5 | NULL | 1 | Using index |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select id from user;
+----+-------------+-------+-------+---------------+-------------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------------------+---------+------+------+-------------+
| 1 | SIMPLE | user | index | NULL | index_id_name_age | 20 | NULL | 1 | Using index |
+----+-------------+-------+-------+---------------+-------------------+---------+------+------+-------------+
1 row in set (0.00 sec)
b. 如果有where则索引出现在key和possible_keys中。
mysql> explain select name from user where id=1 ;
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+--------------------------+
| 1 | SIMPLE | user | ref | index_id_name_age | index_id_name_age | 5 | const | 1 | Using where; Using index |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
mysql> explain select height from user where height=180;
+----+-------------+-------+------+---------------+--------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+--------------------------+
| 1 | SIMPLE | user | ref | index_height | index_height | 5 | const | 1 | Using where; Using index |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
mysql> explain select height from user where height=180 and id = 18;
+----+-------------+-------+------+--------------------------------+-------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+--------------------------------+-------------------+---------+-------+------+-------------+
| 1 | SIMPLE | user | ref | index_id_name_age,index_height | index_id_name_age | 5 | const | 1 | Using where |
+----+-------------+-------+------+--------------------------------+-------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)
(Ⅳ).using where
SQL使用了where条件过滤数据
需要注意的是:
(1)返回所有记录的SQL,不使用where条件过滤数据,大概率不符合预期,对于这类SQL往往需要进行优化;
(2)使用了where条件的SQL,并不代表不需要优化,往往需要配合explain结果中的type(连接类型)来综合判断;
本例虽然Extra字段说明使用了where条件过滤,但type属性是ALL,表示需要扫描全部数据,仍有优化空间。
常见的优化方法为,在where过滤属性上添加索引。
**Using where只是过滤元组,和是否读取数据文件或索引文件没有关系 **
参考MySQL查询优化概念辨析---Using where 和 Using index
参考Mysql进行Explain分析时Extra列中Using where、Using index、Using temporary、Using filesort
mysql> explain select id,height from user where id=1; -- 删掉height还是Using where +----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+ | 1 | SIMPLE | user | ref | index_id_name_age | index_id_name_age | 5 | const | 1 | Using where | +----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select id from user where id=1; +----+-------------+-------+------+-------------------+-------------------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+-------------------+-------------------+---------+-------+------+--------------------------+ | 1 | SIMPLE | user | ref | index_id_name_age | index_id_name_age | 5 | const | 1 | Using where; Using index | +----+-------------+-------+------+-------------------+-------------------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec) mysql> explain select id,height from user where height=180; -- 删掉id还是Using where +----+-------------+-------+------+---------------+--------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+--------------+---------+-------+------+-------------+ | 1 | SIMPLE | user | ref | index_height | index_height | 5 | const | 1 | Using where | +----+-------------+-------+------+---------------+--------------+---------+-------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select height from user where height=180; +----+-------------+-------+------+---------------+--------------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+--------------+---------+-------+------+--------------------------+ | 1 | SIMPLE | user | ref | index_height | index_height | 5 | const | 1 | Using where; Using index | +----+-------------+-------+------+---------------+--------------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec) mysql> explain select id from user; +----+-------------+-------+-------+---------------+-------------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+-------------------+---------+------+------+-------------+ | 1 | SIMPLE | user | index | NULL | index_id_name_age | 20 | NULL | 1 | Using index | +----+-------------+-------+-------+---------------+-------------------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select height from user; +----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+ | 1 | SIMPLE | user | index | NULL | index_height | 5 | NULL | 1 | Using index | +----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+ 1 row in set (0.00 sec)
(Ⅴ). impossible where : where子句永远为false
mysql> explain select id from user where id=1 and id=2; +----+-------------+-------+------+---------------+------+---------+------+------+------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | +----+-------------+-------+------+---------------+------+---------+------+------+------------------+ 1 row in set (0.00 sec)
(Ⅵ) Using join buffer:连接缓存(性能较低)
两个关联表join,关联字段均未建立索引,就会出现这种情况。常见的优化方案是,在关联字段上添加索引,避免每次嵌套循环计算。
例子:ref中的例子
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战