mySQL5.6的optimizer_trace
来源:互联网 发布:知世故而不世故, 编辑:程序博客网 时间:2024/05/17 04:43
MySQL的explain是各种执行计划选择的结果,如果想看整个执行计划以及对于多种索引方案之间是如何选择的?
MySQL5.6中支持这个功能,optimizer_trace
这个是mysql的参数,默认是关闭的
1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql>
set
optimizer_trace=
"enabled=on"
;
Query OK, 0
rows
affected (0.00 sec)
mysql> show variables
like
'%optimizer_trace%'
;
+
------------------------------+----------------------------------------------------------------------------+
| Variable_name | Value |
+
------------------------------+----------------------------------------------------------------------------+
| optimizer_trace | enabled=
on
,one_line=
off
|
| optimizer_trace_features | greedy_search=
on
,range_optimizer=
on
,dynamic_range=
on
,repeated_subselect=
on
|
| optimizer_trace_limit | 1 |
| optimizer_trace_max_mem_size | 16384 |
| optimizer_trace_offset | -1 |
+
------------------------------+----------------------------------------------------------------------------+
5
rows
in
set
(0.00 sec)
具体这么使用呢?
需要设置如下:
1、开启optimizer_trace,默认是关闭的
1
SET
optimizer_trace=
"enabled=on"
;
2、设置optimizer_trace内存的大小
1
SET
OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000
3、explain查询语句
1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> explain
SELECT
*
FROM
atomuser
WHERE
`uid` =28778731
AND
ptype =
"photo"
LIMIT 0 , 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table
: atomuser
type: ref
possible_keys: uid
key
: uid
key_len: 15
ref: const,const
rows
: 1
Extra: Using
index
condition
1 row
in
set
(0.01 sec)
4、查找对于的结果
1
mysql>
select
*
from
information_schema.optimizer_trace\G
这个表包括4个字段
1
2
3
4
5
6
7
8
9
mysql> show
create
table
information_schema.optimizer_trace\G
*************************** 1. row ***************************
Table
: OPTIMIZER_TRACE
Create
Table
:
CREATE
TEMPORARY
TABLE
`OPTIMIZER_TRACE` (
`QUERY` longtext
NOT
NULL
,
`TRACE` longtext
NOT
NULL
,
`MISSING_BYTES_BEYOND_MAX_MEM_SIZE`
int
(20)
NOT
NULL
DEFAULT
'0'
,
`INSUFFICIENT_PRIVILEGES` tinyint(1)
NOT
NULL
DEFAULT
'0'
) ENGINE=MyISAM
DEFAULT
CHARSET=utf8
主要看TRACE字段,是json串,json解析结果如下:
包括join_preparation,join_optimization,join_explain
join_preparation
1
"/* select#1 */ select `atomuser`.`id` AS `id`,`atomuser`.`uid` AS `uid`,`atomuser`.`ptype` AS `ptype`,`atomuser`.`regtime` AS `regtime` from `atomuser` where ((`atomuser`.`uid` = 28778731) and (`atomuser`.`ptype` = 'photo')) limit 0,1"
这个是使用extend看到的结果
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> explain extended
SELECT
*
FROM
atomuser
WHERE
`uid` =28778731
AND
ptype =
"photo"
LIMIT 0 , 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table
: atomuser
type: ref
possible_keys: uid
key
: uid
key_len: 15
ref: const,const
rows
: 1
filtered: 100.00
Extra: Using
index
condition
1 row
in
set
, 1 warning (0.01 sec)
mysql> show warnings\G
*************************** 1. row ***************************
Level
: Note
Code: 1003
Message:
/* select#1 */
select
`test`.`atomuser`.`id`
AS
`id`,`test`.`atomuser`.`uid`
AS
`uid`,`test`.`atomuser`.`ptype`
AS
`ptype`,`test`.`atomuser`.`regtime`
AS
`regtime`
from
`test`.`atomuser`
where
((`test`.`atomuser`.`ptype` =
'photo'
)
and
(`test`.`atomuser`.`uid` = 28778731)) limit 0,1
1 row
in
set
(0.00 sec)
阅读全文
0 0
- mySQL5.6的optimizer_trace
- mysql5.6的安装
- mysql5.6 数据库的安装
- mysql5.5和mysql5.6的新特性
- mysql5.6和mysql5.7分配undo回滚段的区别
- Mysql5.5&Mysql5.6&Mysql5.7特性
- Mysql5.5&Mysql5.6&Mysql5.7特性
- Windows下的MySQL5.6的安装
- Mysql5.6的my.ini的位置
- Linux下的mysql5.6的安装
- mysql5.6对kernel_mutex的优化
- mysql5.6版本的安装配置过程
- MySQL5.6 Internals-隐藏的索引列
- mysql5.6中必要的存储引擎
- MySQL5.6不支持SET OPTION的写法
- 关于mysql5.6的一些设置
- 设置MySQL5.6的远程连接
- 体验MySQL5.6的Mencached插件
- 漫谈并发编程:用MPI进行分布式内存编程(入门篇)
- Java实现-旋转图像
- [CodeM初赛A轮]D
- 漫谈并发编程:Future模型(Java、Clojure、Scala多语言角度分析)
- Spring 架构篇——使用 Spring Boot 开发 RESTful API
- mySQL5.6的optimizer_trace
- 路由选择协议调研
- 配置jdk环境的配置方式及说明
- Hibernate继承映射【标准+全】
- interactions_anova
- 解决react-native-swiper在安卓上与TabNavigator共用时不显示内容问题
- 计算语言学之隐马尔可夫模型
- link和import的区别
- [CodeM初赛A轮]E