文章目录
一、SQL优化器执行跟踪工具—Trace
前期准备：
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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工记录表';
--利用存储过程完成批量数据插入10万条数据
CREATE DEFINER=`root`@`localhost` PROCEDURE `inserempsinfo`()
BEGIN
#Routine body goes here...
declare i int;
set i=1;
while(i<=100000)do
insert into employees(name,age,position) values(CONCAT('lyf',i),i,'test');
set i=i+1;
end while;
END
1.1、trace工具定义
在MySQL5.6版本开始，推出了对SQL跟踪的工具==>trace，可以通过trace，可以查看到MySQL优化器对SQL语句分析的结果，以及生成了一个怎样的执行计划
1.2、trace的使用
1、开启trace跟踪：
set session optimizer_trace="enabled=on",end_markers_in_json=on;
- 首先是第一阶段：SQL准备阶段，会对执行的ＳＱＬ进行格式化
{
"join_preparation": { ‐‐第一阶段：SQL准备阶段，格式化sql
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `employees`.`id` AS `id`,`employees`.`name` AS `name`,`empl
oyees`.`age` AS `age`,`employees`.`position` AS `position`,`employees`.`hire_time` AS `hire_time` from
`employees` where (`employees`.`name` > 'a') order by `employees`.`position`"
}
]
}
}
- 第二阶段是对SQL优化阶段：在这个阶段中，MySQL会预估全表扫描以及走索引产生的成本const为多少，我们可以重点观察rows_estimation中的不同方案的const值，最后会选择扫描成本较低的方案作为最优的选择，如下：
{
"join_optimization": { ‐‐第二阶段：SQL优化阶段
"select#": 1,
"steps": [
{
"condition_processing": { ‐‐条件处理
"condition": "WHERE",
"original_condition": "(`employees`.`name` > 'a')",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(`employees`.`name` > 'a')"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(`employees`.`name` > 'a')"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(`employees`.`name` > 'a')"
}
] /* steps */
} /* condition_processing */
},
{
"substitute_generated_columns": {
} /* substitute_generated_columns */
},
{
"table_dependencies": [ ‐‐表依赖详情
{
"table": "`employees`",
"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": "`employees`",
"range_analysis": {
"table_scan": { ‐‐全表扫描情况
"rows": 10123, ‐‐扫描行数
"cost": 2054.7 ‐‐查询成本
} /* table_scan */,
"potential_range_indexes": [ ‐‐查询可能使用的索引
{
"index": "PRIMARY", ‐‐主键索引
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_name_age_position", ‐‐辅助索引
"usable": true,
"key_parts": [
"name",
"age",
"position",
"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_name_age_position",
"ranges": [
"a < name" ‐‐索引使用范围
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false, ‐‐使用该索引获取的记录是否按照主键排序
"using_mrr": false,
"index_only": false, ‐‐是否使用覆盖索引
"rows": 5061, ‐‐索引扫描行数
"cost": 6074.2, ‐‐索引使用成本
"chosen": false, ‐‐是否选择该索引
"cause": "cost"
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`employees`",
"best_access_path": { ‐‐最优访问路径
"considered_access_paths": [ ‐‐最终选择的访问路径
{
"rows_to_scan": 10123,
"access_type": "scan", ‐‐访问类型：为scan，全表扫描
"resulting_rows": 10123,
"cost": 2052.6,
"chosen": true, ‐‐确定选择
"use_tmp_table": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 10123,
"cost_for_plan": 2052.6,
"sort_cost": 10123,
"new_cost_for_plan": 12176,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`employees`.`name` > 'a')",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`employees`",
"attached": "(`employees`.`name` > 'a')"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"clause_processing": {
"clause": "ORDER BY",
"original_clause": "`employees`.`position`",
"items": [
{
"item": "`employees`.`position`"
}
] /* items */,
"resulting_clause_is_simple": true,
"resulting_clause": "`employees`.`position`"
} /* clause_processing */
},
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "ORDER BY",
"steps": [
] /* steps */,
"index_order_summary": {
"table": "`employees`",
"index_provides_order": false,
"order_direction": "undefined",
"index": "unknown",
"plan_changed": false
} /* index_order_summary */
} /* reconsidering_access_paths_for_index_ordering */
},
{
"refine_plan": [
{
"table": "`employees`"
}
]
}
]
}
}
- 第三阶段是SQL执行阶段：可以分析其文件排序等信息，例：在执行计划中的Extra列值为Using filesort时，这里的值又分为单路排序和双路排序方式对数据进行排序，如下：
"join_execution": { ‐‐Sql执行阶段
"select#": 1,
"steps": [
{
"filesort_information": [
{
"direction": "asc",
"table": "`employees`",
"field": "position"
}
] /* filesort_information */,
"filesort_priority_queue_optimization": {
"usable": false,
"cause": "not applicable (no LIMIT)"
} /* filesort_priority_queue_optimization */,
"filesort_execution": [
] /* filesort_execution */,
"filesort_summary": { ‐‐文件排序信息
"rows": 10000, ‐‐预计扫描行数
"examined_rows": 10000, ‐‐参与排序的行
"number_of_tmp_files": 3, ‐‐使用临时文件的个数，这个值如果为0代表全部使用的sort_buffer内存排序，否则使用的磁盘文件排序
"sort_buffer_size": 262056, ‐‐排序缓存的大小，单位Byte
"sort_mode": "<sort_key, packed_additional_fields>" ‐‐排序方式，这里用的单路排序，双路排序的值为： "<sort_key, rowid>"
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
set session optimizer_trace="enabled=off";
二、SQL常见优化示例
2.1、联合索引的第一个字段使用范围查询优化示例
1、联合索引第一字段范围查询SQL执行计划示例如下：
2、原因：是因为联合索引的第一个字段使用了范围查找，MySQL内部可能会判断出结果集会比较大，回表的效率不高，海不如全表扫描
3、解决方案1：可以强制让其走索引，如下图：
4、解决方案2：使用覆盖索引
2.2、使用like查找数据优化示例
1、like XX%一般都是会走索引，例：
2.3、order by与group by优化示例
1、order对应排序时Extra中值，符合最左前缀原则，就不会产生using filesort，如下图：
2、反之，如果跳过age后，会产生using filesort，如下图：
总结来说：MySQL支持两种方式排序，分别是filesort和index，using index是指MySQL扫描索引本身就完成了排序，index效率比filesort效率高，尽量让order by 在索引列上完成，想要达到索引列完成排序，就需要遵从以下条件：
- order by语句使用的索引最左前列
- 使用where子句与order by子句条件列的组合满足索引最左前列
尽量
注：group by 与order by很类似，本质就是先排序后分组，遵照索引创建顺序的最左前缀法则，对应group by的优化，如果不需要排序时，可以加上order by null禁止排序，但是需要注意的是，where执行的优先级是高于having的，能写在where中的限定条件就不要去having中限定
2.4、分页查询优化示例
1、是针对索引有序自增的分页优化方案一：根据自增且连续的主键排序的分页查询，如下示例是利用其limit开始值为where条件与单纯的limit对应的执行计划比对，如下图示例：
注：这种方案只适合主键自增且连续和结果按照主键排序的场景，但这种在实际的工作中，用的不多的
这种场景就不走索引了，原因是扫描整个索引树并查找没找到索引的行产生的成本比扫描全表的成本还要高，所以内部优化器就放弃使用索引，那这种情况下，关键点在于让排序时返回的字段尽可能的少，让排序和分析时先查询出主键，外部再用id进行表的关联，具体如下：
2.5、表连接优化示例
准备：创建两个测试连接的表，并用存储过程批量插入数据
-- 表连接测试表1
CREATE TABLE `test_jion1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 表1插入一万条数据
CREATE DEFINER=`root`@`localhost` PROCEDURE `inserempsinfo`()
BEGIN
#Routine body goes here...
declare i int;
set i=1;
while(i<=10000)do
insert into test_jion1(a,b) values(i,i+1);
set i=i+1;
end while;
END
-- 表连接测试表2
create table test_jion2 like test_jion1;
-- 表2插入100条数据
CREATE DEFINER=`root`@`localhost` PROCEDURE `inserempsinfo`()
BEGIN
#Routine body goes here...
declare i int;
set i=1;
while(i<=100)do
insert into test_jion2(a,b) values(i,i+1);
set i=i+1;
end while;
END
1、首先我们先明白，表与表之间的连接查询数据是工作中常用的查询方式，那这种方式在连接的时候，其实内部是有两种算法进行表连接的，他们分别为嵌套循环连接算法（Nested-Loop Join）和基于块的嵌套循环连接算法（Block Nested-Loop Join）
首先我们先了解嵌套循环连接算法（Nested-Loop Join）：
如下示例：
在执行计划的Extra列中没有出现Using join buffer的其实就是嵌套循环连接算法（Nested-Loop Join）
表连接的过程是如下：
- 先执行的表为驱动表，上述示例中，t2就为驱动表，t1位被启动表，是先从驱动表中循环取出行，在取出的这行中取出关联的字段，根据关联字段在另一张表中取出满足条件的数据，然后取出两张表的结果合集
- 但是如果使用了left join时，就指定了左边的表为驱动表，右边的为被驱动表，right join类似，但是使用join时，MySQL内部会选择数据量较小的表作为驱动表，大表作为被驱动表
在上述表关联的过程中，在嵌套循环连接算法（Nested-Loop Join） 下，会先读取t2中的所有数据，也就是100条数据，然后遍历这每行数据中字段a的值，根据t2表中a的值索引扫描t1表中对应的行，t1和t2都是通过索引关联的，索引整个过程扫描了200行
其次我们再了解基于块的嵌套循环连接算法（Block Nested-Loop Join）：
这个其实就是嵌套循环连接算法（Nested-Loop Join） 的反例，也就是说Extra列中出现Using join buffer的其实就是基于块的嵌套循环连接算法（Block Nested-Loop Join），简单来说就是两个表关联时，关联字段用的不是索引字段，导致两个表在关联时，用到了join_buffer来进行逐个比较关联，此方式的连接过程如下
- 先把表t2的所有数据load到join_buffer中
- 再把T1的每一行都取出来 ,跟 join_buffer中的数据做对比
- 最后返回满足join条件的数据
在上述的这个过程中，t1和t2表都做了起一次全表扫描，因此扫描的总行数为：10000+100=10100，并且在join_buffer中里的数据是无序的，因此对表t1中的每一行，最坏的情况系下，都要做一百下判断，内存的判断次数就为100万次，效率极低，而且还有就是join_buffer可能一次性放不下这么多数据，那就要分段放，所以对应关联SQL的优化，我们需要：
- 关联的字段需要加索引，让MySQL的join操作尽可能的选择NLJ算法
- 小表驱动大表==>这个规则在涉及in和exsits语句中也适用
2.6、in和exsits优化
这个优化的原则是小表驱动大表，也就是数据量小的表去驱动数据量大的表：
例如：
1、in的情况如下SQL示例：当B表的数据集小于A表的数据集时，in优于exists
select * from A where id in (select id from B)
就等价于：
for(select id from B){
select * from A where A.id = B.id
}
2、exists的情况如下SQL示例：当A表的数据集小于B表的数据集时，exists优于in
select * from A where exists (select 1 from B where B.id = A.id)
就等价于:
for(select * from A){
select * from B where B.id = A.id
}
三、部分名词详解
3.1、索引下推
1、主要是针对辅助的联合索引：正常情况下是需要按照最左前缀原则，例如上述2.2中的like优化，那种情况下只会走name字段的索引，因为name过滤完之后，得到的索引行里的age和position是无序的，无法很好的利用索引，这种情况下，在MySQL5.6之前，这个查询只能在联合索引里匹配到LiLei开头的索引，然后拿到这些索引值逐个回表load相关字段值，但是在MySQL5.6之后的版本，可以在联合索引遍历过程中，对索引包含的索引字段先进行判断，过滤掉不符合条件的记录之后再做回表操作，在一定程度上减少了回表的数据，提高了查询的效率，例如上述2.2中的like优化中，先匹配到name以LiLei开头的数据，与此同时也会过滤age和position两个字段，拿到这些过滤后的数据对应的主键id才会回表查询数据
3.2、单路排序
1、定义：无论是单路排序还是双路，其实都是针对执行计划中的extra列的值为filesort衍生的，所谓的单路排序，可以理解为不需要回表操作，在排序时，是一次性取出满足条件行的所有字段，在sort_buffer中进行排序，当然，我们可以在trace工具中SQL执行阶段的sort_mode字段查看是单路还是双路，单路的字段值为：< sort_key, additional_fields >或者< sort_key,packed_additional_fields >
2、执行过程：
- 首先是从索引name找到满足条件的主键id
- 根据id取出对应的整行数据，存入到sort_buffer中
- 从索引name找到下一个满足条件的主键id
- 重复2和3步骤，知道江数据load完毕
- 开始在sort_buffer对load的数据按照指定的字段排序
- 返回排好序的数据给客户端
3.3、双路排序
1、定义：相比单路排序，这个是需要回表操作的，所以也可以称为：回表排序模式，就是说，在排序时，首先是根据条件取出排序字段以及可以直接定位行的id，然后在sort_buffer中进行排序，排序完毕之后，根据可以确定行数据的id取回对应的其他字段信息，trace中的取值为：<sort_key,row_id>
2、执行过程：
- 首先也是从索引name找到满足条件的主键id
- 根据id取出对应的排序字段值以及主键id存放到sort_buffer中去
- 从索引name找到下一个满足条件的主键id
- 重复2和3步骤，知道江数据load完毕
- 开始在sort_buffer对按照load的排序字段进行排序
- 遍历sort_buffe中排好序的id和和排序字段，按照id的值回到原表中取出所需的字段信息返回给客户端
注：选择单路还是双路其实是通过比较系统变量max_length_for_sort_data来判断的，默认是1M，如果说字段的总长度小于这个值，就走单路排序，反之就走双路，我们也可以适当调节这个峰值，但不建议修改
四、索引设计的部分原则
4.1、联合索引要尽量覆盖条件
设计一个或者多个联合索引时，让每一个联合索引都尽量去包含SQL语句中的where、order by、group by的字段，还要确保这些联合索引的字段顺序尽量满足SQL查询的最左前缀原则
4.2、不要在小基数上建立索引
所谓的小基数，就是指这个字段在表总共有多少个不同的值，例如，性别字段，非男即女，这类似的小基数要建立索引就没有太大的意义
4.3、代码先上，索引后补
设计建立索引从本质上来定位的话，离不开业务场景，一边应该等到主体业务功能开发完毕之后，把涉及到该表相关的SQL都要拿出来分析之后再建立索引
4.4、长字符可采用前缀索引
在设计索引时，我们尽量使用字段类型较小的列作为索引，比如说，tinyint之类的，因为较小的话，占用的磁盘空间也会较小，搜索的性能也会较好些，但，其实字段类型较小的也不是绝对的，很多时候针对一些业务场景使用varchar的这种类型字段建立索引，哪怕是多占用一些磁盘空间也是很有必要的，对应这种的话，如果说字段varchar(255)的大字段可能会占用较大的磁盘空间，那我们可以针对这个字段，截取前20个字符作为索引，类似于:key index(name(20),age,position)
4.5、where与order by冲突时，优先where
一般在建立索引的时候，where和order by出现冲突的时，往往都是让where条件去使用索引来快速筛选出来一部分指定的数据，接着再进行排序。因为大多数情况基于索引进行where筛选往往可以最快速度筛选出你要的少部分数据，然后做排序的成本可
能会小很多
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
