SQL分析&分析优化器
一.Show Profile进行SQL分析
是用来分析当前会话中使用的SQL语句的资源消耗情况，可用来进行SQL的调优测量
默认情况下处于关闭状态，并保留最近15次运行结果(MySQL中有很多很厉害的功能，但是都是在默认关闭状态)
Show Profile分析步骤
- 不是所有版本的MySQL都支持，得看看当前版本
- 由于是默认关闭的，所以使用前需要手动开启
Show Profile的使用
1.开启
set profileing=1;
- 当开启了之后我们执行的sql语句运行的时间都会被记录
2.显示被记录的语句
show profiles;
3.显示某条数据个步骤具体运行时间
# 显示第二条数据具体运行时间
show profile for query 2
4.显示个别步骤运行时间
show profile block [type [, type] ... ] for query n
- 例如：
show profile block io, cpu for query 2
- 各个type
| type | 用处 |
|---|---|
| ALL | 显示所有的开销信息 |
| BLOCK IO | 显示块IO相关开销 |
| CONTEXT SWITCHES | 显示上下文切换的次数，包括主动和被动 |
| cpu | 显示CPU相关开销信息 |
| IPC | 显示发送和接收相关开销信息 |
| MEMORY | 显示内存相关开销信息 |
| PAGE FAULTS | 显示页面错误相关开销信息 |
| SOURCE | 显示源代码中的函数名称以及该函数所在文件的名称和行号 |
| SWAPS | 显示swap的次数 |
5.参数注意
converting HEAP to MyISAM查询结果太大,内存都不够用了往磁盘上搬
Creating tmp table 创建临时表
Copying to tmp table on disk 把内存中临时表复制到磁盘,危险
locked
二.全局查询日志
-
可以将所使用过的sql全部记录在MYSQL库的general_log表中
-
通过这个命令开启
set global general_log = 1;
- 将sql写入表中
set global log_output = 'TABLE';
- 查看表中记录的sql语句
select * from mysql.general_log;
三.使用show processlist
使用show processlist可以查看当前MySQL的实例连接情况。
show processlist
- 执行结果
mysql> show processlist;
+----+------+-----------------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+---------+------+----------+------------------+
| 5 | root | localhost:63361 | NULL | Query | 0 | starting | show processlist |
+----+------+-----------------+------+---------+------+----------+------------------+
1 row in set (0.01 sec)
可以看到我只有一个连接，它的各个字段意义如下：
| 字段 | 解释 |
|---|---|
| Id | 连接标识符 |
| User | 当前用户名 |
| Host | 操作的主机 |
| db | 默认数据库 |
| Command | 线程正在执行的命令类型 |
| Time | 线程处于当前状态的持续时间(以秒为单位) |
| State | 线程正在执行的操作，事件或者状态 |
| Info | 线程正在执行的语句，如果未执行任何语句，则为NULL。该语句可能是发送到服务器的那条语句，或者是最内部的语句（如果该语句执行其他语句，比如存储过程中的select语句） |
对于Command的状态：
| sleep | 正在等待客户端发送新的请求 |
| query | 正在执行查询或者正在将结果发送给客户端 |
| query | 在MySQL服务层，线程正在等待表锁 |
| analyzing and statistics | 线程正在收集存储引擎的统计信息，并生成查询的执行计划 |
| copying to tmp table | 线程正在执行查询，并且将其结果集都复制到一个临时表中 |
| sorting result | 正在对结果集进行排序 |
| sending data | 线程可能在多个状态之间传送数据，或者在生成结果集或者向客户端返回数据 |
- show processlist命令默认Info字段最多显示每条语句的前100个字符，如果想完全显示，可以使用show full processlist
四.trace分析SQL优化器
1.介绍
从前面学习的explain中，我们知道了怎么去查看SQL的执行计划，但我们还是不知道为什么优化器会去做这个决策，那如果我们想知道多种索引方案之间它是怎么选择的，或者在排序的时候会选择哪种排序的方案的话我们就该使用到trace这个了。
从 MySQL 5.6 开始，可以使用 trace 查看优化器如何选择执行计划。
通过trace，能够进一步了解为什么优化器选择A执行计划而不是选择B执行计划，或者知道某个排序使用的排序模式，帮助我们更好地理解优化器行为。
如果需要使用，先开启 trace，设置格式为 JSON，再执行需要分析的 SQL，最后查看 trace 分析结果（在 information_schema.OPTIMIZER_TRACE 中）。
开启该功能，会对 MySQL 性能有所影响，因此只建议分析问题时临时开启。
2.创建测试表
- 先建一个用于测试的表
CREATE TABLE `test_trace` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`a` INT(11) DEFAULT NULL,
`b` INT(11) DEFAULT NULL,
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
PRIMARY KEY (`id`),
KEY `idx_a` (`a`),
KEY `idx_b` (`b`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;
- 首先我们使用一条SQL
select * from test_trace where a >900 and b > 910 order by a;
-- 表示取出表 t1 中 a 的值大于 900 并且 b 的值大于 910 的数据，然后按照 a 字段排序
3.使用方法
1.开启trace
set session optimizer_trace="enabled=on",end_markers_in_json=on;
- optimizer_trace=“enabled=on” 表示开启trace
- end_markers_in_json=on 表示 JSON 输出开启结束标记
2.查看分析结果
- 在我们的MySQL中结束符可以是分号’;'也可以是反斜杠+大写的G(\G)
SELECT * FROM information_schema.OPTIMIZER_TRACE\G
- 结果为
QUERY: select * from t1 where a >900 and b > 910 order by a --SQL语句
TRACE: {
"steps": [
{
"join_preparation": { --SQL准备阶段
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `t1`.`id` AS `id`,`t1`.`a` AS `a`,`t1`.`b` AS `b`,`t1`.`create_time` AS `create_time`,`t1`.`update_time` AS `update_time` from `t1` where ((`t1`.`a` > 900) and (`t1`.`b` > 910)) order by `t1`.`a`"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": { --SQL优化阶段
"select#": 1,
"steps": [
{
"condition_processing": { --条件处理
"condition": "WHERE",
"original_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))", --原始条件
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))" --等值传递转换
},
{
"transformation": "constant_propagation",
"resulting_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))" --常量传递转换
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))" --去除没有的条件后的结构
}
] /* steps */
} /* condition_processing */
},
{
"substitute_generated_columns": {
} /* substitute_generated_columns */ --替换虚拟生成列
},
{
"table_dependencies": [ --表依赖详情
{
"table": "`t1`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [ --预估表的访问成本
{
"table": "`t1`",
"range_analysis": {
"table_scan": {
"rows": 1000, --扫描行数
"cost": 207.1 --成本
} /* table_scan */,
"potential_range_indexes": [ --分析可能使用的索引
{
"index": "PRIMARY",
"usable": false, --为false，说明主键索引不可用
"cause": "not_applicable"
},
{
"index": "idx_a", --可能使用索引idx_a
"usable": true,
"key_parts": [
"a",
"id"
] /* key_parts */
},
{
"index": "idx_b", --可能使用索引idx_b
"usable": true,
"key_parts": [
"b",
"id"
] /* key_parts */
}
] /* potential_range_indexes */,
"setup_range_conditions": [
] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
} /* group_index_range */,
"analyzing_range_alternatives": { --分析各索引的成本
"range_scan_alternatives": [
{
"index": "idx_a", --使用索引idx_a的成本
"ranges": [
"900 < a" --使用索引idx_a的范围
] /* ranges */,
"index_dives_for_eq_ranges": true, --是否使用index dive（详细描述请看下方的知识扩展）
"rowid_ordered": false, --使用该索引获取的记录是否按照主键排序
"using_mrr": false, --是否使用mrr
"index_only": false, --是否使用覆盖索引
"rows": 100, --使用该索引获取的记录数
"cost": 121.01, --使用该索引的成本
"chosen": true --可能选择该索引
},
{
"index": "idx_b", --使用索引idx_b的成本
"ranges": [
"910 < b"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 90,
"cost": 109.01,
"chosen": true --也可能选择该索引
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": { --分析使用索引合并的成本
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */,
"chosen_range_access_summary": { --确认最优方法
"range_access_plan": {
"type": "range_scan",
"index": "idx_b",
"rows": 90,
"ranges": [
"910 < b"
] /* ranges */
} /* range_access_plan */,
"rows_for_plan": 90,
"cost_for_plan": 109.01,
"chosen": true
} /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [ --考虑的执行计划
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`t1`",
"best_access_path": { --最优的访问路径
"considered_access_paths": [ --决定的访问路径
{
"rows_to_scan": 90, --扫描的行数
"access_type": "range", --访问类型：为range
"range_details": {
"used_index": "idx_b" --使用的索引为：idx_b
} /* range_details */,
"resulting_rows": 90, --结果行数
"cost": 127.01, --成本
"chosen": true, --确定选择
"use_tmp_table": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 90,
"cost_for_plan": 127.01,
"sort_cost": 90,
"new_cost_for_plan": 217.01,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": { --尝试添加一些其他的查询条件
"original_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`t1`",
"attached": "((`t1`.`a` > 900) and (`t1`.`b` > 910))"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"clause_processing": {
"clause": "ORDER BY",
"original_clause": "`t1`.`a`",
"items": [
{
"item": "`t1`.`a`"
}
] /* items */,
"resulting_clause_is_simple": true,
"resulting_clause": "`t1`.`a`"
} /* clause_processing */
},
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "ORDER BY",
"index_order_summary": {
"table": "`t1`",
"index_provides_order": false,
"order_direction": "undefined",
"index": "idx_b",
"plan_changed": false
} /* index_order_summary */
} /* reconsidering_access_paths_for_index_ordering */
},
{
"refine_plan": [ --改进的执行计划
{
"table": "`t1`",
"pushed_index_condition": "(`t1`.`b` > 910)",
"table_condition_attached": "(`t1`.`a` > 900)"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": { --SQL执行阶段
"select#": 1,
"steps": [
{
"filesort_information": [
{
"direction": "asc",
"table": "`t1`",
"field": "a"
}
] /* filesort_information */,
"filesort_priority_queue_optimization": {
"usable": false, --未使用优先队列优化排序
"cause": "not applicable (no LIMIT)" --未使用优先队列排序的原因是没有limit
} /* filesort_priority_queue_optimization */,
"filesort_execution": [
] /* filesort_execution */,
"filesort_summary": { --排序详情
"rows": 90,
"examined_rows": 90, --参与排序的行数
"number_of_tmp_files": 0, --排序过程中使用的临时文件数
"sort_buffer_size": 115056,
"sort_mode": "<sort_key, additional_fields>" --排序模式（详解请看下方知识扩展）
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
}
] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 --该字段表示分析过程丢弃的文本字节大小，本例为0，说明没丢弃任何文本
INSUFFICIENT_PRIVILEGES: 0 --查看trace的权限是否不足，0表示有权限查看trace详情
1 row in set (0.00 sec)
4.关闭trace
set session optimizer_trace="enabled=off";
5.分析结果解释
- TRACE 字段中整个文本大致分为三个过程
- 准备阶段：对应文本中的 join_preparation
- 优化阶段：对应文本中的 join_optimization
- 执行阶段：对应文本中的 join_execution
- 使用时重点关注优化阶段还有执行阶段
- 在 trace 结果的 analyzing_range_alternatives 这一项可以看到：使用索引 idx_a 的成本为 121.01，使用索引 idx_b 的成本为 109.01，显然使用索引 idx_b 的成本要低些，因此优化器选择了 idx_b 索引；
五.总结
- explain：获取 MySQL 中 SQL 语句的执行计划，比如语句是否使用了关联查询、是否使用了索引、扫描行数等；
- trace：查看优化器如何选择执行计划，获取每个可能的索引选择的代价。
- show processlist: 获取MySQL中实例对象的连接状态，比如too many connections这个报错的时候
六.索引优化
索引虽然妙用无穷，可以增加上百倍的查询速度，但是很多时候不正确的使用索引也会导致索引的失效。
1.索引的分类
| 分类角度 | 索引名称 |
|---|---|
| 数据结构 | B+树、Hash索引、B-Tree等 |
| 储存层面 | 聚簇索引、非聚簇索引 |
| 逻辑层面 | 主键索引，普通索引，复合索引(MySQL 组合索引和联合索引和复合索引都是一个东西)，唯一索引，空间索引等 |
2.回表
这个要记好，日后的面试中面试官可能会问。
- 如下的SQL语句，使用的是ID的主键索引，因为主键索引是关联一整行的，所以只要查询到了主键索引就可以直接输出一整行。
select * from test where id=1;
- 但如果我们使用了下面这条SQL语句，使用的只是一个普通索引number=10，那么就会先定位到这个普通索引的位置，然后再查出关联的主键索引，再通过主键索引查询出整行记录，这就是回表。
select * from test where number=10;
- 从普通索引查出主键索引再查出数据的过程就叫做回表，所以说使用主键索引就会比使用普通索引快，建议一般都使用主键索引。
3.覆盖索引
我们创建索引都是根据where条件来创建，通过上面讲的回表也知道要是想查询效率快：一.使用主键索引，二.避免回表。那么就是说在一个索引里面就能查到需要的所有数据这就叫做覆盖索引。如下：
select number from test where number=10;
4.建立测试表
-- 建表
create table staffs(
id int primary key auto_increment,
name varchar(24) not null default "",
age int not null default 0,
pos varchar(20) not null default "",
add_time timestamp not null default CURRENT_TIMESTAMP
)charset utf8;
-- 插入数据
-- 建表
insert into staffs(`name`,`age`,`pos`,`add_time`) values('z3',22,'manager',now());
insert into staffs(`name`,`age`,`pos`,`add_time`) values('July',23,'dev',now());
insert into staffs(`name`,`age`,`pos`,`add_time`) values('2000',23,'dev',now());
-- 插入数据
create table user(
id int not null auto_increment primary key,
name varchar(20) default null,
age int default null,
email varchar(20) default null
) engine=innodb default charset=utf8;
- 建立复合索引
create index idx_staffs_nameAgePos on staffs(name,age,pos);
5.索引优化口诀
全值匹配我最爱,最左前缀要遵守
带头大哥不能死,中间兄弟不能断
索引列上少计算,范围之后全失效
like百分写最右,覆盖索引不写星
不等空值还有or,索引失效要少用
varchar引号不可丢,SQL高级也不难
6.口诀解析
- 以上面建立的复合索引举例name,age,pos
| 口诀 | 解析 |
|---|---|
| 全值匹配我最爱 | 复合索引建立的字段做好全部用上 |
| 最左前缀要遵守 | 复合索引建立的第一个字段(name)要放在最左边 |
| 带头大哥不能死 | 复合索引的第一个字段(name)不能丢，否则索引失效 |
| 中间兄弟不能断 | 复合索引的age不能断，否则后面的pos也失效 |
| 索引列上少计算 | 在索引查询的时候不要使用计算 |
| 范围之后全失效 | 在索引查询中例如使用<或者>这种范围查询，那么从那个索引开始后面的索引全部失效 |
| like百分写最右 | 也就是说在索引搜索中(like 10%)百分号只能写在最右边 |
| 覆盖索引不写星 | 当我们使用覆盖索引的时候，select后面不要使用*号 |
| 不等空值还有or,索引失效要少用 | where条件后面的索引不得使用!=、null、or否则索引失效 |
| varchar引号不可丢,SQL高级也不难 | 在使用varchar类型的索引，一定要加引号( ’ ’ )否则索引失效 |
1059
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
