SQL优化 - 常用参数

优化的原因： 性能低、执行时间太长、索引失效、服务器参数设置不合理（缓冲、线程数）

SQL编写过程

SELECT DISTINCT ...
FROM ....
JOIN ... ON ...
WHERE ....
GROUP BY ....
HAVING ...
ORDER BY ...
LIMIT ... OFFSET ...

解析过程

FROM ...
ON ... JOIN ...
WHERE ...
GROUP BY ...
HAVING ...
SELECT DISTINCT ...
ORDER BY  ...
LIMIT .... oFFSET ...

SQL优化的目标是【索引】

索引：类似于指针，指向表被索引的数据。
索引的目的： 提高数据的查询从而改善数据库的整体性能。

索引的弊端：
1.索引本身很大， 可以存放在内存/硬盘（通常为 硬盘）
2.索引不是所有情况均适用： a.少量数据  b.频繁更新的字段   c.很少使用的字段
3.索引会降低增删改的效率（增删改  查）

优势：
1提高查询效率（降低IO使用率）
2.降低CPU使用率 （...order by age desc,因为 B树索引 本身就是一个 好排序的结构，因此在排序时  可以直接使用）

MySQL语句执行的底层实现：https://www.cnblogs.com/annsshadow/p/5037667.html

索引的分类

  • 主键索引：不能重复。id 不能是null
  • 唯一索引：不能重复。 id 可以是null
  • 单列索引： 单列，age；一个表可以多个单值索引，name
  • 组合索引：多列构成的索引（相当于 二级目录：z:zhao)(name,age) (a,b,c,d, ..., n)

B数

创建索引的方式

方式一：
 
create 索引类型  索引名  on 表(字段)
 
单例索引：
create index   dept_index on  tb(dept);
唯一索引：
create unique index  name_index on tb(name) ;
组合索引
create index dept_name_index on tb(dept,name);
 
方式二：alter table 表名 索引类型  索引名（字段）
		
单列索引：
alter table tb add index dept_index(dept) ;
唯一索引：
alter table tb add unique index name_index(name);
组合索引：
alter table tb add index dept_name_index(dept,name);
 
注意：如果一个字段是primary key，则改字段默认就是 主键索引	
	
 
删除索引：
drop index 索引名 on 表名 ;
drop index name_index on tb ;
 
查询索引：
show index from 表名 ;
show index from 表名 \G

SQL性能问题

a. 分析SQL的执行计划： explain， 可以模拟SQL优化器执行SQL语句，从而让开发人员 知道自己编写的SQL状况
b. MySQL查询优化会干扰我们的优化

优化方法： https://dev.mysql.com/doc/refman/5.5/en/optimization.html

查询执行计划： explain + SQL语句

EXPLAIN SELECT * FROM tb;

EXPLAIN执行分析

mysql> EXPLAIN SELECT *
    -> FROM value;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | value | ALL  | NULL          | NULL | NULL    | NULL |    5 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
 id : 编号				
 select_type ：查询类型
 table ：表
 type   ：类型
 possible_keys ：预测用到的索引 
 key  ：实际使用的索引
 key_len ：实际使用索引的长度     
 ref  :表之间的引用
 rows ：通过索引查询到的数据量 
 Extra     :额外的信息

测试数据

create table course
(
cid int(3),
cname varchar(20),
tid int(3)
);
create table teacher
(
tid int(3),
tname varchar(20),
tcid int(3)
);
 
create table teacherCard
(
tcid int(3),
tcdesc varchar(200)
);
 
 
insert into course values(1,'java',1);
insert into course values(2,'html',1);
insert into course values(3,'sql',2);
insert into course values(4,'web',3);
 
insert into teacher values(1,'tz',1);
insert into teacher values(2,'tw',2);
insert into teacher values(3,'tl',3);
 
insert into teacherCard values(1,'tzdesc') ;
insert into teacherCard values(2,'twdesc') ;
insert into teacherCard values(3,'tldesc') ;

实例分析1： 查询课程编号为2或教师证编号为3的老师信息

mysql> EXPLAIN SELECT t.*
    -> FROM teacher t, course c, teacherCard tc
    -> WHERE t.tid = c.tid AND t.tcid = tc.tcid AND (c.cid = 2 or tc.tcid = 3);
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                          |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
|  1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL |    3 |                                |
|  1 | SIMPLE      | tc    | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where; Using join buffer |
|  1 | SIMPLE      | c     | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where; Using join buffer |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
3 rows in set (0.05 sec)
 
mysql> INSERT INTO teacher VALUES(4, 'wz', 4);
Query OK, 1 row affected (0.02 sec)
 
mysql> INSERT INTO teacher VALUES(5, 'wyt', 5);
Query OK, 1 row affected (0.02 sec)
mysql> EXPLAIN SELECT t.*
    -> FROM teacher t, course c, teacherCard tc
    -> WHERE t.tid = c.cid AND t.tcid = tc.tcid AND (c.cid = 2 OR tc.tcid = 3);
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                          |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
|  1 | SIMPLE      | tc    | ALL  | NULL          | NULL | NULL    | NULL |    3 |                                |
|  1 | SIMPLE      | c     | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where; Using join buffer |
|  1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where; Using join buffer |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+

id： id值相同的时候，从上往下，顺序执行。
第一次EXPLAIN: 【t -> tc -> c】 结果集： t(3) * tc(3) * c(4) = 24
第二次EXPLAIN: 【tc -> c -> t】 结果集: tc(3) * c(4) * t(5) = 60

表的执行顺序 因表中记录的个数改变而改变的原因是： 笛卡尔积
🌈结论： 【数据量小的表 优先查询】

实例分析2：查找教授SQL课程的老师的描述

【内连接-等值连接】
mysql> SELECT tc.tcdesc
    -> FROM teacherCard tc, teacher t, course c
    -> WHERE tc.tcid = t.tid AND t.tid = c.tid AND c.cname = "sql";
+--------+
| tcdesc |
+--------+
| twdesc |
+--------+
1 row in set (0.03 sec)
【对等值连接的解析】
mysql> EXPLAIN SELECT tc.tcdesc
    -> FROM teacherCard tc, teacher t, course c
    -> WHERE tc.tcid = t.tid AND t.tid = c.tid AND c.cname = "sql";
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                          |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
|  1 | SIMPLE      | tc    | ALL  | NULL          | NULL | NULL    | NULL |    3 |                                |
|  1 | SIMPLE      | c     | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where; Using join buffer |
|  1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where; Using join buffer |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
3 rows in set (0.00 sec)
 
 
【子查询使用】
mysql> SELECT teacherCard.tcdesc
    -> FROM teacherCard
    -> WHERE teacherCard.tcid IN (
    -> SELECT teacher.tcid
    -> FROM teacher
    -> WHERE tid IN
    -> (SELECT course.tid
    -> FROM course
    -> WHERE course.cname = "sql"));
+--------+
| tcdesc |
+--------+
| twdesc |
+--------+
1 row in set (0.05 sec)
【对子查询的解析】
mysql> EXPLAIN SELECT teacherCard.tcdesc
    -> FROM teacherCard
    -> WHERE teacherCard.tcid IN
    -> (
    -> SELECT teacher.tcid
    -> FROM teacher
    -> WHERE tid IN
    -> (SELECT course.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 | DEPENDENT SUBQUERY | teacher     | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |
|  3 | DEPENDENT SUBQUERY | course      | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
+----+--------------------+-------------+------+---------------+------+---------+------+------+-------------+
3 rows in set (0.00 sec)
 
【等值查询和子查询同一个级别使用】
mysql> SELECT teacherCard.tcdesc
    -> FROM teacherCard, teacher
    -> WHERE teacherCard.tcid = teacher.tcid
    -> AND teacher.tid = (SELECT course.tid FROM course WHERE course.cname = "sql");
+--------+
| tcdesc |
+--------+
| twdesc |
+--------+
1 row in set (0.00 sec)
【对等值查询和子查询混合使用时的分析】
mysql> EXPLAIN SELECT teacherCard.tcdesc
    -> FROM teacherCard, teacher
    -> WHERE teacherCard.tcid = teacher.tcid
    -> AND teacher.tid = (SELECT course.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 |                                |
|  1 | PRIMARY     | teacher     | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where; Using join buffer |
|  2 | SUBQUERY    | course      | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where                    |
+----+-------------+-------------+------+---------------+------+---------+------+------+--------------------------------+
3 rows in set (0.00 sec)

🌈结论：【id值不同：id值越大越优先查询 (本质：在嵌套子查询时，先查内层 再查外层)， id值相同的时候从上向下依次执行】

查询类型： select_type

  • PRIMARY:包含子查询SQL中的 主查询 （最外层）
  • SUBQUERY：包含子查询SQL中的 子查询 （非最外层）
  • simple:简单查询（不包含子查询、union）
  • derived:衍生查询(使用到了临时表)
【🍎a.在from子查询中只有一张表】
mysql> SELECT CONCAT(tmp.tid, '-', tmp.tname) AS info
    -> FROM (SELECT tid, tname, tcid FROM teacher LIMIT 2 OFFSET 0) AS tmp;
+------+
| info |
+------+
| 1-tz |
| 2-tw |
+------+
2 rows in set (0.03 sec)
 
mysql> EXPLAIN SELECT CONCAT(tmp.tid, '-', tmp.tname) AS info
    -> FROM (SELECT tid, tname, tcid FROM teacher LIMIT 2 OFFSET 0) AS tmp;
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    2 |       |
|  2 | DERIVED     | teacher    | ALL  | NULL          | NULL | NULL    | NULL |    5 |       |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
2 rows in set (0.00 sec)
 
 
【🍎b.在from子查询中， 如果有table1 union table2 ，则table1 就是derived,table2就是union】
explain select  cr.cname from ( select * from course where tid = 1  union select * from course where tid = 2 ) cr ;
mysql> SELECT CONCAT(tmp.tid, '-', tmp.tname) AS info
    -> FROM (SELECT tid, tname, tcid FROM teacher LIMIT 2 OFFSET 0) AS tmp
    -> UNION
    -> SELECT CONCAT(tmp.tid, '-', tmp.tname) AS info
    -> FROM (SELECT tid, tname, tcid FROM teacher LIMIT 1 OFFSET 2) AS tmp;
+------+
| info |
+------+
| 1-tz |
| 2-tw |
| 3-tl |
+------+
3 rows in set (0.00 sec)
 
mysql> EXPLAIN SELECT CONCAT(tmp.tid, '-', tmp.tname) AS info
    -> FROM (SELECT tid, tname, tcid FROM teacher LIMIT 2 OFFSET 0) AS tmp
    -> UNION
    -> SELECT CONCAT(tmp.tid, '-', tmp.tname) AS info
    -> FROM (SELECT tid, tname, tcid FROM teacher LIMIT 1 OFFSET 2) AS tmp;
+----+--------------+------------+--------+---------------+------+---------+------+------+-------+
| id | select_type  | table      | type   | possible_keys | key  | key_len | ref  | rows | Extra |
+----+--------------+------------+--------+---------------+------+---------+------+------+-------+
|  1 | PRIMARY      | <derived2> | ALL    | NULL          | NULL | NULL    | NULL |    2 |       |
|  2 | DERIVED      | teacher    | ALL    | NULL          | NULL | NULL    | NULL |    5 |       |
|  3 | UNION        | <derived4> | system | NULL          | NULL | NULL    | NULL |    1 |       |
|  4 | DERIVED      | teacher    | ALL    | NULL          | NULL | NULL    | NULL |    5 |       |
| NULL | UNION RESULT | <union1,3> | ALL    | NULL          | NULL | NULL    | NULL | NULL |       |
+----+--------------+------------+--------+---------------+------+---------+------+------+-------+
5 rows in set (0.00 sec)
# union result :告知开发人员，那些表之间存在union查询

🥝type索引类型

system>const>eq_ref>ref>range>index>all ，要对type进行优化的前提：有索引
其中：system,const只是理想情况；实际能达到 ref>range
system（忽略）: 只有一条数据的系统表 ；或 衍生表只有一条数据的主查询

测试数据
create table test01
(
	tid int(3),
	tname varchar(20)
);
insert into test01 values(1,'a') ;
commit;
# 【添加主键索引 非空】
ALTER TABLE test01 ADD CONSTRAINT cons_tid_pk PRIMARY KEY(tid);
🍟type是system说明衍生表中只有一条数据的主查询
mysql> SELECT *
    -> FROM 
    -> (
    -> SELECT *
    -> FROM test01) AS temp
    -> WHERE temp.tid = 1;
+-----+-------+
| tid | tname |
+-----+-------+
|   1 | a     |
+-----+-------+
 
mysql> EXPLAIN SELECT *
    -> FROM (SELECT * FROM test01) AS temp
    -> WHERE temp.tid = 1;
+----+-------------+------------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table      | type   | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+------------+--------+---------------+------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL | NULL    | NULL |    1 |       |
|  2 | DERIVED     | test01     | ALL    | NULL          | NULL | NULL    | NULL |    1 |       |
+----+-------------+------------+--------+---------------+------+---------+------+------+-------+
# type是system说明：衍生表只有一条数据的主查询
🍟type是const：仅仅能查到【一条数据】的SQL，用于PRIMARY KEY或UNIQUE索引（类型与索引类型有关）
mysql> SELECT tid FROM test01 WHERE tid = 1;
+-----+
| tid |
+-----+
|   1 |
+-----+
1 row in set (0.00 sec)
 
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.00 sec)
🍟eq_ref:唯一性索引：对于每个索引键的查询，返回匹配唯一行数据（有且只有1个，不能多 、不能0）
select ... from ..where name = ... .常见于唯一索引 和主键索引。
 
 alter table teacherCard add constraint pk_tcid primary key(tcid);
alter table teacher add constraint uk_tcid unique index(tcid) ;
 
 
explain select t.tcid from teacher t,teacherCard tc where t.tcid = tc.tcid ;
 
以上SQL，用到的索引是 t.tcid,即teacher表中的tcid字段；
如果teacher表的数据个数 和 连接查询的数据个数一致（都是3条数据），则有可能满足eq_ref级别；否则无法满足。
🍟ref：非唯一性索引，对于每个索引键的查询，返回匹配的所有行（0，多）
准备数据：
 insert into teacher values(4,'tz',4) ;
 insert into teacherCard values(4,'tz222');
 
测试：
alter table teacher add index index_name (tname) ;
explain select * from teacher 	where tname = 'tz';
🍟range：检索指定范围的行 ,where后面是一个范围查询(between ,> < >=, 特殊:in有时候会失效 ，从而转为 无索引all)
alter table teacher add index tid_index (tid) ;
explain select t.* from teacher t where t.tid in (1,2) ;
explain select t.* from teacher t where t.tid <3 ;
 
mysql> explain select t.* from teacher t where t.tid <3 ;
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key       | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
|  1 | SIMPLE      | t     | range | tid_index     | tid_index | 5       | NULL |    1 | Using where |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
1 row in set (0.00 sec)
🍟index: 查询全部索引中数据
mysql> ALTER TABLE teacher ADD index tid_index(tid);
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 |    6 | Using index |
+----+-------------+---------+-------+---------------+-----------+---------+------+------+-------------+
1 row in set (0.00 sec)
system/const: 结果只有一条数据
eq_ref:结果多条；但是每条数据是唯一的 ；
ref：结果多条；但是每条数据是是0或多条 ；

🥝possible_keys: 可能用到的索引，是一种预测，不准。

🥝key: 实际使用到的索引

🥝key_len: 索引的长度 =》 用来判断复合索引是否被完全使用 eg: (a,b,c)

【数据准备】

-- 创建test_k1测试表
mysql> CREATE TABLE test_k1
    -> (
    -> name CHAR(20) NOT NULL DEFAULT ""
    -> );
🍎 查看一个索引的长度要以被索引字段的类型为基准
-- 在test_k1给name添加索引
mysql> ALTER TABLE test_k1 ADD INDEX idx_name(name);
 
-- 分析对name的查询
mysql> EXPLAIN SELECT *
    -> FROM test_k1
    -> WHERE name = "" \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test_k1
         type: ref
possible_keys: idx_name
          key: idx_name
      key_len: 60                                  
          ref: const
         rows: 1
        Extra: Using where; Using index
1 row in set (0.00 sec)
 
ERROR: 
No query specified
-- 💚可以看出这个名称为idx_name的索引长度为60， 因为在utf8中一个字符是3个字节则如果是20char就是60个字节
🍎 索引字段可以为Null,则会使用1个字节用于标识。
mysql> ALTER TABLE test_k1 ADD COLUMN name1  CHAR(20); -- 💚name1默认允许为空
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> DESC test_k1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(20) | NO   | MUL |         |       |
| name1 | char(20) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.03 sec)
 
mysql> ALTER TABLE test_k1 ADD INDEX idx_name1(name1);  
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> SELECT 
    -> \c
mysql> EXPLAIN SELECT *
    -> FROM test_k1
    -> WHERE name1 = ""; -- 💚name1是允许为空的则在以name1为索引的基础上该索引大小最后需要添加1个字节用来表明这个name1是允许null
+----+-------------+---------+------+---------------+-----------+---------+-------+------+-------------+
| id | select_type | table   | type | possible_keys | key       | key_len | ref   | rows | Extra       |
+----+-------------+---------+------+---------------+-----------+---------+-------+------+-------------+
|  1 | SIMPLE      | test_k1 | ref  | idx_name1     | idx_name1 | 61      | const |    1 | Using where |
+----+-------------+---------+------+---------------+-----------+---------+-------+------+-------------+
1 row in set (0.00 sec)
🍎 查看复合索引

【数据准备】

-- 删除test_k1中的单列索引
mysql> DROP INDEX idx_name ON test_k1;
mysql> DROP INDEX idx_name1 ON test_k1;
-- 在test_k1中增加一个复合索引
msyql> ALTER TABLE test_k1 ADD INDEX multi_col_idx(name, name1);
mysql> EXPLAIN SELECT * 
    -> FROM test_k1 
    -> WHERE name1 = "";
+----+-------------+---------+-------+---------------+---------------+---------+------+------+--------------------------+
| id | select_type | table   | type  | possible_keys | key           | key_len | ref  | rows | Extra                    |
+----+-------------+---------+-------+---------------+---------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test_k1 | index | NULL          | combine_index | 121     | NULL |    1 | Using where; Using index |
+----+-------------+---------+-------+---------------+---------------+---------+------+------+--------------------------+
1 row in set (0.00 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  | combine_index | combine_index | 60      | const |    1 | Using where; Using index |
+----+-------------+---------+------+---------------+---------------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
🍎 查看可变长度的索引
mysql> ALTER TABLE test_k1 
    -> ADD COLUMN name2 VARCHAR(20);
 
mysql> DESC test_k1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | char(20)    | NO   | MUL |         |       |
| name1 | char(20)    | YES  |     | NULL    |       |
| name2 | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
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  | idx_name2     | idx_name2 | 63      | const |    1 | Using where |
+----+-------------+---------+------+---------------+-----------+---------+-------+------+-------------+
 
-- 💚 63 = 20 * 3 + 1 + 2 （B)
-- name2数据类型varchar(20)大小： 20 * 3 = 60B
-- name2允许为空用1个字节来标识： 1B
-- name2是可变长度用2个字节来标识： 2B

🍎 ref： 注意与type中的ref值区分 =》 指明当前表所参照的字段

mysql> ALTER TABLE course ADD INDEX idx_tid(tid);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
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  | index_name,conbin_idx_test,tid_index | index_name | 63      | const            |    1 | Using where |
|  1 | SIMPLE      | c     | ref  | idx_tid                              | idx_tid    | 5       | learn_demo.t.tid |    1 | Using where |
+----+-------------+-------+------+--------------------------------------+------------+---------+------------------+------+-------------+

🍎 rows: 被【索引优化】查询的【数据个数】（实际通过索引而查询到的数据个数）

mysql> EXPLAIN SELECT * 
    -> FROM course c, teacher t
    -> WHERE c.tid = t.tid;
+----+-------------+-------+------+---------------+-----------+---------+------------------+------+-------------+
| id | select_type | table | type | possible_keys | key       | key_len | ref              | rows | Extra       |
+----+-------------+-------+------+---------------+-----------+---------+------------------+------+-------------+
|  1 | SIMPLE      | c     | ALL  | idx_tid       | NULL      | NULL    | NULL             |    4 |             |
|  1 | SIMPLE      | t     | ref  | tid_index     | tid_index | 5       | learn_demo.c.tid |    1 | Using where |
+----+-------------+-------+------+---------------+-----------+---------+------------------+------+-------------+
2 rows in set (0.00 sec)
 
mysql> SELECT *
    -> FROM course c, teacher t
    -> WHERE c.tid = t.tid;
+------+-------+------+------+-------+------+
| cid  | cname | tid  | tid  | tname | tcid |
+------+-------+------+------+-------+------+
|    1 | java  |    1 |    1 | tz    |    1 |
|    2 | html  |    1 |    1 | tz    |    1 |
|    3 | sql   |    2 |    2 | tw    |    2 |
|    4 | web   |    3 |    3 | tl    |    3 |
+------+-------+------+------+-------+------+
4 rows in set (0.00 sec)

🍎 extra

🌳using filesort： 表明性能消耗大需要“额外”的一次排序（查询） 常见于order by的语句中

排序： 先查询

10个人根据年龄排序。
【数据准备】

| person | CREATE TABLE `person` (
  `name` varchar(20) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  KEY `idx_01` (`name`),
  KEY `idx_02` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
🍟对于单个索引，如果查找(WHERE)和排序(ORDER BY)不是同一个字段则会出现using filesort;
mysql> EXPLAIN SELECT *
    -> FROM person
    -> WHERE name = ""
    -> ORDER BY age;
+----+-------------+--------+------+---------------+--------+---------+-------+------+-----------------------------+
| id | select_type | table  | type | possible_keys | key    | key_len | ref   | rows | Extra                       |
+----+-------------+--------+------+---------------+--------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | person | ref  | idx_01        | idx_01 | 63      | const |    1 | Using where; Using filesort |
+----+-------------+--------+------+---------------+--------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)
🍟对于单个索引，如果查找(WHERE)和排序(ORDER BY)是同一个字段则不会出现using filesort;
mysql> EXPLAIN SELECT *
    -> FROM person 
    -> WHERE name = ""
    -> ORDER BY name;
+----+-------------+--------+------+---------------+--------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys | key    | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+---------------+--------+---------+-------+------+-------------+
|  1 | SIMPLE      | person | ref  | idx_01        | idx_01 | 63      | const |    1 | Using where |
+----+-------------+--------+------+---------------+--------+---------+-------+------+-------------+
1 row in set (0.00 sec)
🍟复合索引中出现跨列、无序的时候会出现 --using filesort

【数据准备】

mysql> SHOW INDEX FROM person;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| person |          1 | idx_01   |            1 | name        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| person |          1 | idx_02   |            1 | age         | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
 
mysql> DROP INDEX idx_01 ON person;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> DROP INDEX idx_02 ON person;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> DESC person;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int(3)      | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
 
mysql> ALTER TABLE person
    -> ADD COLUMN salary DOUBLE;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> DESC person;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(20) | YES  |     | NULL    |       |
| age    | int(3)      | YES  |     | NULL    |       |
| salary | double      | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
 
mysql> SHOW INDEX FROM person;
Empty set (0.00 sec)
-- 💚添加复合索引
mysql> ALTER TABLE person ADD INDEX multi_idx(name, age, salary);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> SHOW INDEX FROM person;
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| person |          1 | multi_idx |            1 | name        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| person |          1 | multi_idx |            2 | age         | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| person |          1 | multi_idx |            3 | salary      | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
  • 💥跨列使用复合索引会出现using filesort
mysql> EXPLAIN SELECT *
    -> FROM person
    -> WHERE name = ""
    -> ORDER BY salary;
+----+-------------+--------+------+---------------+-----------+---------+-------+------+------------------------------------------+
| id | select_type | table  | type | possible_keys | key       | key_len | ref   | rows | Extra                                    |
+----+-------------+--------+------+---------------+-----------+---------+-------+------+------------------------------------------+
|  1 | SIMPLE      | person | ref  | multi_idx     | multi_idx | 63      | const |    1 | Using where; Using index; Using filesort |
+----+-------------+--------+------+---------------+-----------+---------+-------+------+------------------------------------------+
1 row in set (0.01 sec)
 
mysql> EXPLAIN SELECT *
    -> FROM person 
    -> WHERE age = ""
    -> ORDER BY salary;
+----+-------------+--------+-------+---------------+-----------+---------+------+------+------------------------------------------+
| id | select_type | table  | type  | possible_keys | key       | key_len | ref  | rows | Extra                                    |
+----+-------------+--------+-------+---------------+-----------+---------+------+------+------------------------------------------+
|  1 | SIMPLE      | person | index | NULL          | multi_idx | 77      | NULL |    1 | Using where; Using index; Using filesort |
+----+-------------+--------+-------+---------------+-----------+---------+------+------+------------------------------------------+
1 row in set (0.00 sec)
  • 💥where和order by 要按照复合索引的顺序使用不要无序使用
mysql> EXPLAIN SELECT * 
    -> FROM person 
    -> WHERE age = ""
    -> ORDER BY name;
+----+-------------+--------+-------+---------------+-----------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key       | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+---------------+-----------+---------+------+------+--------------------------+
|  1 | SIMPLE      | person | index | NULL          | multi_idx | 77      | NULL |    1 | Using where; Using index |
+----+-------------+--------+-------+---------------+-----------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
  • 🧰保证最左使用复合索引则不会出现using filesort （最左优先）
mysql> EXPLAIN SELECT *
    -> FROM person
    -> WHERE name = ""
    -> ORDER BY age;
+----+-------------+--------+------+---------------+-----------+---------+-------+------+--------------------------+
| id | select_type | table  | type | possible_keys | key       | key_len | ref   | rows | Extra                    |
+----+-------------+--------+------+---------------+-----------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | person | ref  | multi_idx     | multi_idx | 63      | const |    1 | Using where; Using index |
+----+-------------+--------+------+---------------+-----------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
 
mysql> EXPLAIN SELECT *
    -> FROM person
    -> WHERE name = ""
    -> ORDER BY age, salary;
+----+-------------+--------+------+---------------+-----------+---------+-------+------+--------------------------+
| id | select_type | table  | type | possible_keys | key       | key_len | ref   | rows | Extra                    |
+----+-------------+--------+------+---------------+-----------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | person | ref  | multi_idx     | multi_idx | 63      | const |    1 | Using where; Using index |
+----+-------------+--------+------+---------------+-----------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

🌈总结： WHERE 和 ORDER BY 按照复合索引的顺序和最左优先原则使用，不要跨列或无序使用

  • 跨列 ===》 会造成 using filesort 文件内排序，“多了一次额外的查找/排序”
  • 无序 ===》 会造成 using where 需要回表查询

跨不跨列是将WHERE 和 ORDER BY中字段合并一起判断的

🛑复合索引(a1,a2,a3)
WHERE a1=1  ORDER BY a2  【有序未跨列】
WHERE a1 = 1 ORDER BY a3 【有序跨列】
WHERE a2 = 1 ORDER BY a1 【无序未跨列】
WHERE a3 = 1 ORDER BY a1 【无序跨列】
🌳using temporary: 性能损耗大，用到了临时表。 一般出现在group by中

如何避免【using temporary】? 对什么查询就对什么分组即GROUP BY

mysql> EXPLAIN SELECT name
    -> FROM person
    -> WHERE age IN (11, 12, 13)
    -> GROUP BY age;
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-----------------------------------------------------------+
| id | select_type | table  | type  | possible_keys | key       | key_len | ref  | rows | Extra                                                     |
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-----------------------------------------------------------+
|  1 | SIMPLE      | person | index | NULL          | multi_idx | 77      | NULL |    1 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-----------------------------------------------------------+
1 row in set (0.00 sec)
 
mysql> EXPLAIN SELECT name
    -> FROM person
    -> WHERE age IN (11, 12, 13)
    -> GROUP BY name;
+----+-------------+--------+-------+---------------+-----------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key       | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+---------------+-----------+---------+------+------+--------------------------+
|  1 | SIMPLE      | person | index | NULL          | multi_idx | 77      | NULL |    1 | Using where; Using index |
+----+-------------+--------+-------+---------------+-----------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
🌳using index(好的语句标志 - 一个语句所有的字段只和索引列相关)： 说明所使用的列都在索引中即实现索引覆盖

原因： 不读取原文件，只从索引文件中获取数据（不需要回表查询）

-- 向person添加一个非索引的新字段
mysql> ALTER TABLE person
    -> ADD COLUMN motto VARCHAR(50);
mysql> DESC person;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(20) | YES  | MUL | NULL    |       |
| age    | int(3)      | YES  |     | NULL    |       |
| salary | double      | YES  |     | NULL    |       |
| motto  | varchar(50) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
mysql> SHOW INDEX FROM person;
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| person |          1 | multi_idx |            1 | name        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| person |          1 | multi_idx |            2 | age         | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| person |          1 | multi_idx |            3 | salary      | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 
-- 💚实现索引覆盖
 
mysql> EXPLAIN SELECT name, age, salary
    -> FROM person
    -> WHERE name = "" AND age = "";
+----+-------------+--------+------+---------------+-----------+---------+-------------+------+--------------------------+
| id | select_type | table  | type | possible_keys | key       | key_len | ref         | rows | Extra                    |
+----+-------------+--------+------+---------------+-----------+---------+-------------+------+--------------------------+
|  1 | SIMPLE      | person | ref  | multi_idx     | multi_idx | 68      | const,const |    1 | Using where; Using index |
+----+-------------+--------+------+---------------+-----------+---------+-------------+------+--------------------------+
 
-- 💚未实现索引覆盖
 
mysql> EXpLAIN SELECT name, age, salary
    -> FROM person
    -> WHERE name = "" AND age = "" AND motto = "";
+----+-------------+--------+------+---------------+-----------+---------+-------------+------+-------------+
| id | select_type | table  | type | possible_keys | key       | key_len | ref         | rows | Extra       |
+----+-------------+--------+------+---------------+-----------+---------+-------------+------+-------------+
|  1 | SIMPLE      | person | ref  | multi_idx     | multi_idx | 68      | const,const |    1 | Using where |
+----+-------------+--------+------+---------------+-----------+---------+-------------+------+-------------+

🌳using where: 即在where子句中需要字段回到表中去比对数据（需要回表查询）

mysql> EXPLAIN SELECT name
    -> FROM person
    -> WHERE name = "";  -- 💚需要name回表进行数据比对
+----+-------------+--------+------+---------------+-----------+---------+-------+------+--------------------------+
| id | select_type | table  | type | possible_keys | key       | key_len | ref   | rows | Extra                    |
+----+-------------+--------+------+---------------+-----------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | person | ref  | multi_idx     | multi_idx | 63      | const |    1 | Using where; Using index |
+----+-------------+--------+------+---------------+-----------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
 
mysql> EXPLAIN SELECT name 
    -> FROM person;   -- 💚不需要name回表进行数据比对
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key       | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-------------+
|  1 | SIMPLE      | person | index | NULL          | multi_idx | 77      | NULL |    1 | Using index |
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-------------+
1 row in set (0.00 sec)

🌳impossible where : where子句永远为false

mysql> EXPLAIN SELECT *
    -> FROM person
    -> WHERE name = "x" AND name = "y"; -- 💚name不可能同时为x和y
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| 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)
posted @   Felix_Openmind  阅读(195)  评论(0)  编辑  收藏  举报
编辑推荐：
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· AI与.NET技术实操系列：向量存储与相似性搜索在 .NET 中的实现
· 基于Microsoft.Extensions.AI核心库实现RAG应用
· Linux系列：如何用heaptrack跟踪.NET程序的非托管内存泄露
· 开发者必知的日志记录最佳实践
阅读排行：
· winform 绘制太阳，地球，月球 运作规律
· 超详细：普通电脑也行Windows部署deepseek R1训练数据并当服务器共享给他人
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· AI 智能体引爆开源社区「GitHub 热点速览」
· 写一个简单的SQL生成工具
*{cursor: url(https://files-cdn.cnblogs.com/files/morango/fish-cursor.ico),auto;}
点击右上角即可分享
微信分享提示