mysql explain以及查询优化

最新推荐文章于 2024-07-23 12:00:40 发布

liuhehe321

最新推荐文章于 2024-07-23 12:00:40 发布

阅读量114

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/nmjhehe/article/details/98025073

版权

mysql 专栏收录该内容

20 篇文章 0 订阅

订阅专栏

SELECT id, name, age FROM test.tbl_user;

flush privileges;

select * FROM test.tbl_user;

SHOW GLOBAL STATUS LIKE 'Innodb_page_size';

set optimizer_trace="enalbed=ON";


-- type ： system>const>eq_ref>ref》range>index>ALL  （其实system执行效率最高，const次之，一般systerm和const不用优化）
-- const :用于primary key或 unique key的所有列的场景 type比较多，所以表最多有一个匹配行
-- systerm：systerm 是const的特例，表中只有一条元组匹配是为systerm.


CREATE INDEX tbl_user_name_idx on tbl_user(name);

EXPLAIN EXTENDED SELECT * from tbl_user t WHERE t.name= 'liuhehe';
show WARNINGS;
-- /* select#1 */ select `test`.`t`.`id` AS `id`,`test`.`t`.`name` AS `name`,`test`.`t`.`age` AS `age` from `test`.`tbl_user` `t` where (`test`.`t`.`name` = '牛明杰')

-- eq_ref:PRIMARY key 和unique key 索引的所有部门连接使用，最多只会返回一行记录。

-- ref ：不使用唯一索引，而是使用普通索引或者唯一索引的部分前缀，索引要和某个值相比较，可能会找到



-- extra列

create TABLE test.film_actor(
id int not null,
film_id int not null,
actor_id int not null,
remark VARCHAR(255) DEFAULT NULL,
PRIMARY KEY(id),
-- 联合索引
key idx_film_actor_id (film_id,actor_id)
);

-- useing index:查询到的裂被覆盖，并且where筛选条件是索引的前导列，是性能高的表现
EXPLAIN SELECT film_actor.film_id FROM film_actor WHERE film_id=1;  -- film_id是idx_film_actor_id (film_id,actor_id)的前导列

-- Using where
EXPLAIN SELECT * FROM film_actor WHERE actor_id=1;  -- actor_id 不是idx_film_actor_id (film_id,actor_id)的前导列，因此性能差

-- Using where; Using index 查询的列被索引覆盖，并且where筛选
EXPLAIN SELECT film_actor.film_id FROM film_actor WHERE actor_id=1;  -- actor_id 不是idx_film_actor_id (film_id,actor_id)的前导列，因此性能差

对于一个SQL语句，查询优化器先看是不是能转换成JOIN，再将JOIN进行优化
优化分为： 1. 条件优化， 2.计算全表扫描成本， 3. 找出所有能用到的索引， 4. 针对每个索引计算不同的访问
方式的成本， 5. 选出成本最小的索引以及访问方式

开启查询优化器日志：

 --开启
set optimizer_trace="enabled=on";
-- 执行sql
-- 查看日志信息
select * from information_schema.OPTIMIZER_TRACE;
-- 关闭
set optimizer_trace="enabled=off";

常量传递

a = 1 AND b > a
上面这个sql可以转换为：
a = 1 AND b > 1

等值传递

a = b and b = c and c = 5
上面这个sql可以转换为：
a = 5 and b = 5 and c = 5

移除没用的条件

a = 1 and 1 = 1
上面这个sql可以转换为：
a = 1

举例子

-- 执行sql
select * from t tbl_t WHERE tbl_t.b=c and c=2;

通过查看查询优化的日志，可以看到查询优化器优化的内容

"steps": [{
		"condition_processing": {
			"condition": "WHERE",
			"original_condition": "((`tbl_t`.`b` = `tbl_t`.`c`) and (`tbl_t`.`c` = 2))",
			"steps": [{
					"transformation": "equality_propagation",
					"resulting_condition": "(multiple equal(2, `tbl_t`.`b`, `tbl_t`.`c`))"
				},
				{
					"transformation": "constant_propagation",
					"resulting_condition": "(multiple equal(2, `tbl_t`.`b`, `tbl_t`.`c`))"
				},
				{
                    #查询优化器转化后的结果
					"transformation": "trivial_condition_removal",
					"resulting_condition": "multiple equal(2, `tbl_t`.`b`, `tbl_t`.`c`)"
				}
			]
		}
	},

计算全表扫描的代价

对于InnoDB存储引擎来说，全表扫描的意思就是把聚簇索引中的记录都依次和给定的搜索条件做一下比较，把符
合搜索条件的记录加入到结果集，所以需要将聚簇索引对应的页面加载到内存中，然后再检测记录是否符合搜索
条件。由于查询成本=I/O成本+CPU成本，所以计算全表扫描的代价需要两个信息：
1. 聚簇索引占用的页面数
2. 该表中的记录数
MySQL为每个表维护了一系列的统计信息， SHOW TABLE STATUS 语句来查看表的统计信息。

-- MySQL为每个表维护了一系列的统计信息， SHOW TABLE STATUS 语句来查看表的统计信息。
SHOW TABLE STATUS LIKE 'tbl_user';

Rows

表示表中的记录条数。对于使用MyISAM存储引擎的表来说，该值是准确的，对于使用InnoDB存储引擎的表来
说，该值是一个估计值

Data_length

表示表占用的存储空间字节数。使用MyISAM存储引擎的表来说，该值就是数据文件的大小，对于使用InnoDB存
储引擎的表来说，该值就相当于聚簇索引占用的存储空间大小，也就是说可以这样计算该值的大小：

Data_length =聚集索引的页面数量 x 每一页的大小

我们的titles使用默认16KB的页面大小，而上边查询结果显示Data_length的值是20512768，所以我们可以反向来
推导出聚簇索引的页面数量：

聚簇索引的页面数量 = Data_length ÷ 16 ÷ 1024 = 20512768 ÷ 16 ÷ 1024 = 1252

我们现在已经得到了聚簇索引占用的页面数量以及该表记录数的估计值，所以就可以计算全表扫描成本了。但是
MySQL在真实计算成本时会进行一些微调。
I/O成本： 1252*1 = 1252。 1252指的是聚簇索引占用的页面数， 1.0指的是加载一个页面的成本常数。
CPU成本： 442070*0.2=88414。 442070指的是统计数据中表的记录数，对于InnoDB存储引擎来说是一个估计
值， 0.2指的是访问一条记录所需的成本常数
总成本： 1252+88414 = 89666
综上所述，对于titles的全表扫描所需的总成本就是89666

我们前边说过表中的记录其实都存储在聚簇索引对应B+树的叶子节点中，所以只要我们通过根节点获得了最
左边的叶子节点，就可以沿着叶子节点组成的双向链表把所有记录都查看一遍。也就是说全表扫描这个过程
其实有的B+树内节点是不需要访问的，但是MySQL在计算全表扫描成本时直接使用聚簇索引占用的页面数作
为计算I/O成本的依据，是不区分内节点和叶子节点的。

深入了解请移步到github

https://github.com/heheliu321/mysql