sql explain

最新推荐文章于 2024-11-06 10:53:30 发布
最新推荐文章于 2024-11-06 10:53:30 发布
阅读量506 收藏
点赞数 1
分类专栏： # mysql 文章标签： sql 数据库 java mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wan212000/article/details/132985129
版权

目录

1. sql explain

使用 Explain 可以查看 sql 的性能瓶颈信息, 并根据结果进行 sql 的相关优化。在 select 语句前加上 explain 关键字, 执行的时候并不会真正执行 sql 语句, 而是返回 sql 查询语句对应的执行计划信息。

当然如果 select 语句的 from 后面有一个子查询的话, 就会执行子查询了并把结果放到一个临时表中。

有三张表:

-- 演员表
 CREATE TABLE `actor` (
 `id` INT ( 11 ) NOT NULL,
 `name` VARCHAR ( 45 ) DEFAULT NULL,
 `update_time` datetime DEFAULT NULL,
 PRIMARY KEY ( `id` )  
) ENGINE = INNODB DEFAULT CHARSET = utf8;
 
INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (1,'a','2017-12-22 15:27:18'), (2,'b','2017-12-22 15:27:18'), (3,'c','2017-12-22 15:27:18');
 
-- 电影表
CREATE TABLE `film` (
 `id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
 `name` VARCHAR ( 10 ) DEFAULT NULL,
 PRIMARY KEY ( `id` ),
 KEY `idx_name` ( `name` )  
) ENGINE = INNODB DEFAULT CHARSET = utf8;
 
INSERT INTO `film` (`id`, `name`) VALUES (3,'film0'),(1,'film1'),(2,'film2');
 
-- 演员和电影中间表
CREATE TABLE `film_actor` (
 `id` INT ( 11 ) NOT NULL,
 `film_id` INT ( 11 ) NOT NULL,
 `actor_id` INT ( 11 ) NOT NULL,
 `remark` VARCHAR ( 255 ) DEFAULT NULL,
 PRIMARY KEY ( `id` ),
 KEY `idx_film_actor_id` ( `film_id`, `actor_id` )  
) ENGINE = INNODB DEFAULT CHARSET = utf8;
 
INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (1,1,1),(2,1,2),(3,2,1);

执行 explain select * from actor;

结果:

mysql> explain select * from actor;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | actor | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

根据返回的信息可以分析 sql 的性能瓶颈从而进行优化。

下面分析其中每个字段对应的含义。

每个字段对应的含义

1.1. id

代表 sql 中查询语句的序列号, 序列号越大则执行的优先级越高, 序号一样谁在前谁先执行。id 为 null 则最后执行。

1.2. select_type

查询类型, 表示当前被分析的 sql 语句的查询的复杂度。这个字段有多个值。

  • SIMPLE: 表示简单查询。
mysql> explain select * from actor;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | actor | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

  • PRIMARY: 表示复杂查询中的最外层的 select 查询语句。

  • SUBQUERY: 表是子查询语句 跟在 select 关键字后面的 select 查询语句;

mysql> explain select (select 1 from film where id =1) from actor;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | actor | NULL       | index | NULL          | PRIMARY | 4       | NULL  |    3 |   100.00 | Using index |
|  2 | SUBQUERY    | film  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
  • derived: 派生查询, 跟在一个 select 查询语句的 from 关键字后面的 select 查询语句 例如:
mysql> set session optimizer_switch='derived_merge=off'; -- 关闭 mysql5.7 新特性对衍生表的合并优化
Query OK, 0 rows affected (0.00 sec)

mysql> explain select (select 1 from actor where id =1) from (SELECT * from film where id=1) ac;
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | <derived3> | NULL       | system | NULL          | NULL    | NULL    | NULL  |    1 |   100.00 | NULL        |
|  3 | DERIVED     | film       | NULL       | const  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL        |
|  2 | SUBQUERY    | actor      | NULL       | const  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

1.3. table

表示当前访问的表的名称。

当 from 中有子查询时, table 字段显示的是 <derivedN> N 为 derived 的 id 的值。

1.4. partitions

返回的是数据分区的信息, 不常用 这里不做分析。

1.5. type

这个字段决定 mysql 如何查找表中的数据, 查找数据记录的大概范围。这个字段的所有值表示的从最优到最差依次为:

system > const > eq_ref > ref > range > index > all;

一般来说我们优化到 range 就可以了, 最好到 ref。

  • null: type 字段的值如果为 null, 那么表示当前的查询语句不需要访问表, 只需要从索引树中就可以获取我们需要的数据;

一般如果是主键索引的话 , 查询主键字段或者唯一索引的话 查询主键字段 type 字段的值就为 null。

mysql> explain select id from actor where id =1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | actor | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
  • system/const: 用户主键索引或者唯一索引查询时, 只能匹配 1 条数据 一般可以对 sql 查询语句优化成一个常量, 那么 type 一般就是 system 或者 const, system 是 const 的一个特例。
mysql> explain select * from (select * from film where id = 1) tmp;
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
|  1 | PRIMARY     | <derived2> | NULL       | system | NULL          | NULL    | NULL    | NULL  |    1 |   100.00 | NULL  |
|  2 | DERIVED     | film       | NULL       | const  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)
  • eq_ref: 在进行连接查询时, 例如 left join 时, 如果是使用主键索引或者唯一索引连接查询 , 结果返回一条数据, 则 type 的值为一般为 eq_ref。
mysql> explain SELECT * from film_actor left join film on film.id = film_actor.film_id;
+----+-------------+------------+------------+--------+---------------+---------+---------+--------------------------+------+----------+-------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref                      | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+---------+---------+--------------------------+------+----------+-------+
|  1 | SIMPLE      | film_actor | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                     |    3 |   100.00 | NULL  |
|  1 | SIMPLE      | film       | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | mysql.film_actor.film_id |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+--------+---------------+---------+---------+--------------------------+------+----------+-------+
2 rows in set, 1 warning (0.01 sec)

分析下这个 sql, 首先我们需要查询的是 film_actor 中间表 且这个表是与 film 表进行主键关联的, 索引 film_actor 表中的 film_id 字段在 film 表中只有一个唯一值, 所以: eq_ref

那么, 反过来在看一下

mysql>  explain SELECT * from film left join film_actor on film_actor.film_id = film.id;
+----+-------------+------------+------------+-------+-------------------+----------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table      | partitions | type  | possible_keys     | key      | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+------------+------------+-------+-------------------+----------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | film       | NULL       | index | NULL              | idx_name | 33      | NULL |    3 |   100.00 | Using index                                        |
|  1 | SIMPLE      | film_actor | NULL       | ALL   | idx_film_actor_id | NULL     | NULL    | NULL |    3 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+------------+------------+-------+-------------------+----------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.01 sec)

film 表和 film_actor 中间表关联查询, 根据 film 电影表中的主键 id 和 film_actor 表中的 film_id 字段进行关联的。电影表中的主键 id 在 film_actor 中并不是唯一的。所以: index ALL

对于 film 需要确定查询 id 从索引树中就可以获取值 所以是 index。对于 film_actor 就是全表扫描了。

  • ref: 相比较 eq_ref, 不使用主键索引或者唯一索引, 使用的是普通索引或者唯一索引的部分前缀, 索引与一个值进行比较后可能获取到多个符合条件的行, 不在是唯一的行了。

简单查询, name 是普通索引

mysql> explain select * from film where name = 'film1';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | film  | NULL       | ref  | idx_name      | idx_name | 33      | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

复杂查询, film_actor 有联合索引 idx_film_actor_id('film_id','actor_id') 这里使用了联合索引的左前缀 film_id

mysql> explain select fa.film_id from film f left join film_actor fa on fa.film_id = f.id;
+----+-------------+-------+------------+-------+-------------------+-------------------+---------+------------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys     | key               | key_len | ref        | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+-------------------+-------------------+---------+------------+------+----------+-------------+
|  1 | SIMPLE      | f     | NULL       | index | NULL              | idx_name          | 33      | NULL       |    3 |   100.00 | Using index |
|  1 | SIMPLE      | fa    | NULL       | ref   | idx_film_actor_id | idx_film_actor_id | 4       | mysql.f.id |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+-------------------+-------------------+---------+------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
  • range: 通常使用范围查找, 例如 between, in, <, >, >= 等使用索引进行范围检索。
mysql> explain select * from film where id >2;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | film  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
  • index: 扫描索引树就能获取到的数据, 一般是扫描二级索引, 并且不会从根节点扫描, 一般直接扫描二级索引的叶子节点, 速度比较慢。因为二级索引叶子节点不保存表中其他字段数据 只保存主键, 所以二级索引还是比较小的, 扫描速度相比 All 还是很快的。这里用到了覆盖索引, 什么是覆盖索引: 可以直接遍历索引树就能获取数据叫做覆盖索引。这里遍历 name 索引树就可以获取到主键 id 的值就是覆盖索引。
mysql> explain select id from film;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | film  | NULL       | index | NULL          | idx_name | 33      | NULL |    3 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
  • ALL: 这是一种效率最低的 type, 需要扫描主键索引树的叶子节点, 获取数据是表中其他列的数据, 即全表扫描。

和 index 有什么区别呢?

拿 film 电影表举例: 添加一个 remark 影评字段, film 表结构如下:

CREATE TABLE `film` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `remark` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

表中建了两个索引: id 主键索引 idx_name(name) 二级索引。

那么:

mysql> explain select id,name from film ;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | film  | NULL       | index | NULL          | idx_name | 33      | NULL |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

上述 sql 查询 id, name 两个字段, 分析 mysql 索引数据结构, 以及 mysql 优化后一般扫描二级索引, 索引会扫描 idx_name 索引树的叶子节点, 那么根据 B+Tree 树的结构, 叶子节点保存的是 name 字段的索引值 和 data 数据(主键 id)。而正好我们只需要查询 id 和 name 两个字段, 我们查询的字段被索引(二级索引)给覆盖了 这就是覆盖索引, 因此 type 的类型就是 index。

再来:

mysql> explain select remark from film ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | film  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

比较上一个 sql, 这个 sql 只查询了一个字段: remark, 经过上面分析, 这个字段是不在 idx_name 索引树的叶子节点上的, 所以 mysql 不会在扫描 idx_name 索引树了, 直接扫描主键索引的叶子节点, 即进行全表扫描, 这个时候 type 类型为 ALL。

1.6. possible_keys

这个字段显示的是 sql 在查询时可能使用到的索引, 但是不一定真的使用, 只是一种可能。

如果在进行 explain 分析 sql 时, 发现这一列有值, 但是 key 列为 null, 因为 mysql 觉得可能会使用索引, 但是又因为表中的数据很少, 使用索引反而没有全表扫描效率高, 那么 mysql 就不会使用索引查找, 这种情况是可能发生的。

如果该列是 NULL, 则没有相关的索引。在这种情况下, 可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能, 然后用 explain 查看效果。

1.7. key

sql 执行中真正用到的索引字段。

1.8. key_len

用到的索引字段的长度, 通过这个字段可以显示具体使用到了索引字段中的哪些列(主要针对联合索引): 计算公式如下

  • 字符串
    • char(n): n 字节长度
    • varchar(n): 如果是 utf-8, 则长度 3n + 2 字节, 加的 2 字节用来存储字符串长度
  • 数值类型
    • tinyint: 1 字节
    • smallint: 2 字节
    • int: 4 字节
    • bigint: 8 字节
  • 时间类型
    • date: 3 字节
    • timestamp: 4 字节
    • datetime: 8 字节
  • 如果字段允许为 NULL, 需要 1 字节记录是否为 NULL

索引最大长度是 768 字节, 当字符串过长时, mysql 会做一个类似左前缀索引的处理, 将前半部分的字符提取出来做索引。

1.9. ref

表示那些列或常量被用于查找索引列上的值

1.10. rows

表示在查询过程中检索了多少列 但是并不一定就是返回这么多列数据。

1.11. Extra

展示一些额外信息。

索引实践

以下实践以 employees 表为例。一个主键索引 一个联合索引

CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

联合索引最左列原则

例 1:

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei';

使用联合索引中的 name 字段索引。

例 2:

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22;

使用联合索引中的 name 和 gae 字段索引。

例 3:

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';

使用联合索引中的 name age position 字段索引。

例 4:

EXPLAIN SELECT * FROM employees WHERE age = 30 AND position = 'dev';

仅仅使用了联合索引中的 name 字段, 因为中间 age 字段断了, 所以 position 字段索引并未用到。解释一下:

索引是一个有序的数据结构, 也就是说使用索引时, 需要索引保证有序, 那么在联合索引中, 是先按照 name 排序, name 相同情况下, 在按照 age 排序, age 相同情况下 在按照 position 排序, 因此如果 age 不确定情况下, position 是无序的, 所以即使你是用 position 查询了 也无法走索引的。这就是最左列原则并且中间不能断。

例 5:

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manager';

这个使用了联合索引中的 name 和 age 字段, 没有使用 position, 为什么? 原理其实和上面差不多。分析一波:

首先按照顺序 name->age->position,name 已经确定了等于 LiLei, 那么 age 就是有序的了, 所以检索 age>22 的就很容易了 因为 age 有序。但是 age 值其实是不确定的, age 可以是 23,24,25… 等等, 所以在 age 不确定情况下 position 是无序的 因此是不走 position 索引字段的。

全值匹配

mysql> EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys         | key                   | key_len | ref               | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_name_age_position | idx_name_age_position | 140     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

不建议在索引列上做任何操作, 否则索引会失效转而全表扫描

-- 查询 name 的最左变的两个字符为 Li 的行
mysql> EXPLAIN SELECT * FROM employees WHERE LEFT(name,2) = 'Li';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

尽量使用覆盖索引 不需要再回表查询了 效率较高

再试用 != 或 <> 不等于查询时, 会导致索引失效。

mysql> EXPLAIN SELECT * FROM employees WHERE name != 'LiLei';
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys         | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | idx_name_age_position | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

尽量不要使用 or, in 操作, 在某些情况下也会导致索引失效。

  • 第一种情况: 当表中只有两条数据 数据量很少的时候
mysql> explain SELECT * from employees where name in ('LiLei','abc');
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys         | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | idx_name_age_position | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

使用 in 查询, 没有走索引, 进行了全表扫描, 为什么? 分析一波:

首先 如果使用索引的话, mysql 大概会怎么操作? 应该先在 name 索引树中定位到 name=LiLei 这个节点(最少一次 I/O), 然后定位到 name=abc 这个节点(一次 I/O), 然后分别拿到主键 id, 在去主键索引树上扫描定位(最少又要两次 I/O), 总共 4 次 I/O。

如果不使用索引, 直接全表扫描, 那么直接扫描主键索引树的叶子节点 只需要两次 I/O 即可(因为只有两条数据), 所以 mysql 评估全表扫描效率可能会更高, 就不会在走索引了。

  • 第二种情况: 当表中数据量很多, 例如 7 条数据

同样的 sql 查询

mysql> explain SELECT * from employees where name in ('LiLei','abc');
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys         | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | idx_name_age_position | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

结果: 走了索引

为什么会出现这种情况? 再来分析一波:

首先走索引的话 大概需要 4 次 I/O 上面已经分析过了。

那么不走索引的话 需要全表扫描 最坏的情况需要扫描 7 次, 进行 7 次 I/O,mysql 评估一下发现全表扫描的效率可能是低于走索引的, 所以就走了索引。

  • 第三种情况: 数据还是 7 条, 但是我 in 查询时条件有 8 个
mysql> explain SELECT * from employees where name in ('LiLei','abc','cde','asc','ssw','2dff','wsa','sda');
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys         | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | idx_name_age_position | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

看下结果:

为啥又不走索引了呢? 经过上面的两波强势分析, 这里也很容知道原因, 就不过多的赘述了。or 查询的情况类似。

is null, is not null 一般情况下也无法使用索引

是用字符串查询 不见引号 索引也会失效

mysql> explain SELECT * from employees where name = 1324;
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys         | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | idx_name_age_position | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)

针对范围查找的不走索引的优化

首先看个例子:

-- 先给 age 加一个独立索引
mysql> ALTER TABLE `employees` ADD INDEX `idx_age` (`age`) USING BTREE ;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查询 age 在 1 到 2000 分为内的数据
mysql> explain SELECT * from employees where age >1 and age < 2000 ;
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | range | idx_age       | idx_age | 4       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

显然并没有走索引 为什么? 再来强势分析一波:

首先, 我们脑海中要有一个 age 的索引树:

age

我们要找到 1-2000 的数据, 那么在这棵树书上怎么定位?

如果我来定位的话 我会定位一个 age=2 在树上的位置 在定位一个 age=1999 在树上的位置, 然后从 age=2 的节点开始取右边的节点, 一直取下去 直到 age=1999 为止, 但是我们表总只有 7 条数据, mysql 觉得这样操作还没有全表扫描快, 毕竟一共才几条数据全表扫描反而更快些, 所以 mysql 就去全表扫描了。

怎么优化呢?

mysql> explain SELECT * from employees where age >1 and age < 1000 ;
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | range | idx_age       | idx_age | 4       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain SELECT * from employees where age >1001 and age < 2000 ;
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | range | idx_age       | idx_age | 4       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

把一个大的范围拆成多个小的范围 可以利用索引查询。

like 查询建议使用 xxx% 方式匹配, %xxx 或者 %xxx% 索引失效

mysql> EXPLAIN SELECT * FROM employees WHERE name like '%Lei';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM employees WHERE name like '%Lei%';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

结果: 全表扫描

思考下在索引树上 name 的排序规则, 先按照第一个字符比较然后第二个字符依次向后比较, 如果是用 %xxx, 字符串前面的字符不确定, 怎么在树上定位呢? 显然没法按照顺序定位, 只能一个一个遍历比较 所以不会走索引。%xxx% 也一样。

在看下面这个例子

mysql> EXPLAIN SELECT * FROM employees WHERE name like 'Lei%';
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys         | key                   | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | range | idx_name_age_position | idx_name_age_position | 74      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

结果: 走了索引

其实 Lei% 匹配相当于范围查询, 只要 name 的值的前三个字符为 Lei 符合条件, 等价于查找前三个字符 =Lei 的字符串, 这个在索引树上是有序的, 当然可以使用索引定位。

总结:

  1. 使用 explain 关键字, 可以分析出 sql 的性能瓶颈并加以优化
  2. 了解 explain 返回的各字段值代表的意义, 结合索引数据结构有助于我们对 sql 的查询效率的分析和优化
  3. 列举部分可能不会进行索引检索的情况, 例如 !=, <>, is null, like 的某些情况, or 或者 in 的某些情况, 字符串不加引号等
  4. 对某些不走索引查询的情况作了一些比较详细的分析
sql explain优化
yuzheh521的博客
04-03 900
一. 什么是explain？ explain是Mysql的一个关键字，用来分析某条SQL语句执行过程和执行效率。 语法: explain select * from books where bookName = 'swj521'; 执行结果: id：执行编号，表示SELECT所属的行。如果SQL语句中没有子查询或者关联查询，那么id只有唯一的 1，如果有子查询和关联查询，那么就会有多个id。 ...
SQL explain函数解析
kanseu的博客
05-12 1056
建表、插入数据 DROP TABLE IF EXISTS actor; CREATE TABLE actor ( id int(11) NOT NULL, name varchar(45) DEFAULT NULL, update_time datetime DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO actor (id, name, update_time) VALUES (1,'a
sql EXPLAIN 详解 type\rows
weixin_46484674的博客
09-05 3337
EXPLAIN 指令提供了 MySQL 如何执行一条 SQL 语句的信息。通过使用 EXPLAIN，我们可以查看 SQL 语句的执行方式，进而排查 SQL 语句的瓶颈，然后进行优化。
SQL：EXPLAIN使用指南
Del_Esc的博客
07-17 870
EXPLAIN是 MySQL 中用于显示查询执行计划的关键字，它可以帮助你理解 MySQL 是如何执行你的查询的。EXPLAIN返回的结果包含多列信息，每一列都提供了关于查询执行的不同方面的信息。
JAVA中Explain注解用法,Java SqlExplain类代码示例
weixin_33675283的博客
03-14 296
import org.apache.calcite.sql.SqlExplain; //导入依赖的package包/类@Overridepublic List toResult(String sql, SqlNode sqlNode) throws Exception {try {final SqlExplain node = SqlNodeUtil.unwrap(sqlNode, SqlExpl...
SQL Explain 各项参数详解
qq_43012298的博客
11-17 2273
目录 一、表信息 二、explain 的两种使用方式 三、explain中的列 id列 select_type列 table列 type列 possible_keys列 key列 key_len列 ref列 rows列 Extra列 四、索引最佳实践 使用EXPLAIN关键字可以模拟优化器执行SQL语句，从而知道MySQL是 如何处理你的SQL语句的。分析你的查询语句或是结构的性能瓶颈 下面是使用 explain 的例子： 在 select 语句之前增加 expl
基本使用explain分析SQL
热门推荐
张彦峰的博客
05-14 1万+
目录 一、慢查询explain分析的基本思路 二、explain返回字段详解 三、索引优化的原则以及失效情况举例 建立索引 优化索引 索引失效 四、通过explain优化sql的基本思路 五、知识补充 六、参考相关博客 一、慢查询explain分析的基本思路 explain命令是分析慢查询的一个常用方式，可以用来分析select 语句的运行效果，通过explain命令可以得到下面这些信息: 表的读取顺序，数据读取操作的操作类型 ，哪些索引可以使用，哪些索引被实际使用，表之间的引用，每张
spark3 spark-sql explain 命令的执行过程
houzhizhen的专栏
09-19 563
以 explain 命令为基础，介绍了 SPARK sql 的执行过程。
sql优化之explain
qq_37436172的博客
07-04 1021
举以一个子SUBQUERY查询依赖外外面的例子 解析查询流程 1.看出执行顺序首先执行id为3的子查询 ，直接全表查出来，全表IO 2.查询出外查询数据结果，挨行去匹配子查询的结果。外查询 挨个根据条件查子查询最大的 travel_no 3.我们知道自查询匹配时笛卡尔积，即 primar的size乘subquery...
SQL中EXPLAIN命令详解
FSWZYC的博客
09-02 3177
SQL中EXPLAIN命令详解
该gem自动记录每个查询SQL EXPLAIN-Ruby开发
05-27
ActiveRecord :: Explainer该gem自动记录每个查询SQL EXPLAIN。 您可以自然地从开发日志中检查每个SQL EXPLAIN，而无需执行。 用法如果使用Rails，则无需配置ActiveRecord :: Explainer此gem自动记录每个查询SQL ...
Ruby-这个gem自动记录每个查询的SQLEXPLAIN
08-15
`activerecord-explainer` gem是专门为了解决这个问题而设计的，它允许开发者自动记录并分析每个查询的SQL EXPLAIN计划，这对于理解数据库如何执行查询以及如何改进它们的效率非常有帮助。下面将详细介绍这个gem的...
sqlEXPLAIN
09-01
SQL EXPLAIN是一个用于查询优化的工具。它可以解析SQL查询语句并展示查询计划，帮助开发者分析和优化查询性能。通过执行EXPLAIN语句，我们可以获取查询的执行计划，包括表的连接顺序、索引使用情况、排序方式等关键...
SQL Server身份验证模式
huaqianzkh的专栏
11-01 629
SQL Server是一个广泛使用的关系数据库管理系统，通常使用两种身份验证模式：Windows身份验证和SQL Server身份验证。理解这些身份验证方式的概念与更改方式的操作，对于数据库管理员和开发者至关重要。本文将详细介绍身份验证方式的概念以及如何更改SQL Server的身份验证方式，并提供示例步骤和代码。
【SQL实战进阶】——视图的定义、使用
jd1813346972的博客
11-02 1082
该篇文章主要利用15个案例帮助读者快速掌握SQL的视图相关用法，涉及视图的创建、删除、更新、更改等等。
数据特征工程：如何计算Teager能量算子(TEO)？ | 基于SQL实现
石榴姐yyds
11-02 726
TEO（Teager能量算子），由Kaiser于1990年代提出的非线性分析方法（参见Kaiser, 1990;1993），是一种有效的非线性信号处理工具，它能即时反映信号能量的变化。通过计算相邻采样点的值，TEO能够迅速跟踪信号的动态特性。在给定的信号中，较大的TEO能量值指示更快的幅值或频率变化，从而用于检测信号的奇异性。具体而言，TKEO的离散实现遵循如下数学表达式：其中，𝑚m为指数参数，调节着能量估计的敏感度；
6.qsqlquerymodel源码分析
最新发布
adsd1233123的博客
11-06 343
qsqlquerymodel 继承与qabstracttablemodel 负责填充数据 浅析qsqlquery 你会发现提供的构造qsqlquery中如果 携带sql字符串语句则自动执行，具体请看 所以在使用qsqlquery的过程中如果传入sql语句则不用手动执行 否则就需要自己执行 数据返回 sqlResult 是由不同的数据库驱动提供的 而为什么qsqlquery可以使用qsqlresult的protected东西，虽然不是相互继承关系 但因为在qsqlresult中声明了qsqlquery为友元类
使用PostgreSQL进行高效数据管理
qq_36287830的博客
11-01 1174
PostgreSQL是一个开源的关系型数据库管理系统，以其可靠性和强大的功能而著称。它支持SQL标准，并提供了许多高级功能，如事务完整性、多版本并发控制（MVCC）、复杂查询、外键、触发器、视图、事务等。通过本文，你已经学习了如何使用PostgreSQL进行高效的数据管理。我们介绍了PostgreSQL的基本概念、安装方法、配置PostgreSQL、基本操作、性能优化、备份和恢复、高级特性（分区表、全文搜索、地理空间数据）、监控和日志、故障排除等内容。
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

云满笔记

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值