前言
在一次Mysql分享中提到过，会将相干的一些知识点整顿成相应的文章。因为前段时间忙的不可开交，始终没有工夫去整顿这些相干内容。然而必然说进去的话，就要去落实。本章内容次要以实际为主，最好是跟着入手实际。这样能力逐渐把握其中神秘。那么咱们开始吧！！！
1.装置数据库
在做这个实际之前，咱们要先装置一下mysql数据库，这边是通过源码的模式进行装置。不便后续的调试跟踪。
1.1通过git下载mysql源码：
#cd /Users/edz/Desktop/src-source/mysql-server/#git clone https://github.com/mysql/mysql-server.git#cd mysql-server#git checkout 5.6.48以后应用版本为5.6.48，所以咱们切到5.6.48版本。
1.2编译mysql源码：
#cd /Users/edz/Desktop/src-source/mysql-server/BUILD#cmake .. -DCMAKE_INSTALL_PREFIX=/usr/local/mysql5.6.48 -DMYSQL_DATADIR=/usr/local/mysql5.6.48/data -DSYSCONFDIR=/usr/local/mysql5.6.48/etc -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DMYSQL_UNIX_ADDR=/tmp/mysqld.sock -DMYSQL_TCP_PORT=3306 -DENABLED_LOCAL_INFILE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_DEBUG=1 -DWITH_UNIT_TESTS=off#make#make install没有指定mysql装置目录时，在mac零碎下默认会装置在/usr/local/mysql目录中。
implicit instantiation of undefined template 'std::basic_stringstream 谬误解决：
#include <sstream> //间接引入头即可1.3编译my.cnf内容
[mysqld]datadir=/usr/local/mysql5.6.48/datasocket=/usr/local/mysql5.6.48/data/mysql.sockexplicit_defaults_for_timestamp=truelower_case_table_names=1symbolic-links=0[mysqld_safe]log-error=/data/logs/mariadb.logpid-file=/data/mysql/mariadb.pidmy.conf内容我存在/usr/local/mysql/my.cnf
1.4初始化数据库
#cd /usr/local/mysql5.6.48/#./scripts/mysql_install_db --defaults-file=/usr/local/mysql5.6.48/etc/my.cnf --user=root --basedir=/usr/local/mysql初始化数据库过程次要是初始化一些根本数据过程。
1.5 启动数据库
#/usr/local/mysql5.6.48/bin/mysqld --defaults-file=/usr/local/mysql5.6.48/etc/my.cnf --user=root &须要启动数据库服务,能够应用命令“ps aux |grep mysqld”，看一下是否启动胜利。
1.6 连贯数据库
#/usr/local/mysql5.6.48/bin/mysql --socket=/usr/local/mysql5.6.48/data/mysql.sock -u root -h 127.0.0.1 --port 3306 -p输出本人数据库的明码即可，连贯到终端。5.6默认状况是没有设置的，mysql5.7会默认设置。
1.7 大节内容
以上的装置只针对于mysql5.6，如果是mysql5.7以上版本，须要反对boost。mysql版本也会细分为boost版本和非boost版本，非boost版本的须要连带下载boost一块编译。然而咱们通过通过-DDOWNLOAD_BOOST参数主动下载并编译。
2.实际
不晓得你有没有碰到过这种状况，一条原本能够执行得很快的语句，却因为 MySQL 选错了索引，而导致执行速度变得很慢？咱们能够一起来看一个小试验，看一下咱们Mysql5.6中的bug。
2.1 创立表实际
创立表语句：
-- 创立数据库create database t1;use t1;-- 新建一张demo1表create table demo1(id int auto_increment primary key, a int, b int, c int, v varchar(1000), key iabc(a,b,c), key ic(c)) engine = innodb;-- 插入demo1表数据insert into demo1 select null,null,null,null,null;insert into demo1 select null,null,null,null,null from demo1;insert into demo1 select null,null,null,null,null from demo1;insert into demo1 select null,null,null,null,null from demo1;insert into demo1 select null,null,null,null,null from demo1;insert into demo1 select null,null,null,null,null from demo1;-- 更新demo1表数据update demo1 set a=id/2, b=id/4, c=6-id/8, v=repeat('a',1000);-- 查看应用索引状况1explain select id from demo1 where a<3 and b in (1, 13) and c>=3 order by c desc limit 2;-- 查看应用索引状况2explain select id from demo1 force index (iabc) where a<3 and b in (1, 13) and c>=3 order by c desc limit 2;执行以上语句后，比照一下“查看应用索引状况1” 与 “查看应用索引状况2”执行后果。
查看应用索引状况1：
查看应用索引状况2:
同样的语句，应用同样的索引，然而应用了force index之后抉择的执行后影响的行数是不一样的。如果数据量大的话，理论的执行性能也会差异很大。应用range scan显然要优于index scan的全扫描。
2.问题剖析
在剖析这个问题前，咱们能够通过一个optimizer_trace进行剖析，optimizer_trace次要用于语句的优化器跟踪，能够剖析一些语句的问题。
2.1 optimizer_trace参数介绍
- QUERY: 跟踪语句的文本。
- TRACE: 跟踪，JSON格局。
- MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 每个记住的跟踪都是一个字符串，随着优化的进行扩大并将其附加数据。该optimizer_trace_max_mem_size 变量设置所有以后记忆的跟踪所应用的内存总量的限度。如果达到此限度，则以后跟踪不会扩大（因而是不残缺的），并且该MISSING_BYTES_BEYOND_MAX_MEM_SIZE列显示该跟踪失落的字节数。
- INSUFFICIENT_PRIVILEGES: 如果跟踪的查问应用SQL SECURITY值为的 视图或存储的例程 DEFINER，则可能是回绝了除定义者之外的其余用户查看查问的跟踪。在这种状况下，跟踪显示为空，INSUFFICIENT_PRIVILEGES值为1。否则，值为0。
咱们能够通过一下sql进行optimizer_trace ：
-- 设置optimizer_trace最大内存SET OPTIMIZER_TRACE_MAX_MEM_SIZE=268435456; -- 设置开启optimizer_traceSET optimizer_trace="enabled=on";select id from demo1 where a<3 and b in (1, 13) and c>=3 order by c desc limit 2;-- 查看optimizer_trace信息select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE\G;2.2 剖析起因
以下为“查看应用索引状况1”的sql语句optimizer_trace信息：
TRACE: { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `demo1`.`id` AS `id` from `demo1` where ((`demo1`.`a` < 3) and (`demo1`.`b` in (1,13)) and (`demo1`.`c` >= 3)) order by `demo1`.`c` desc limit 2" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`demo1`.`a` < 3) and (`demo1`.`b` in (1,13)) and (`demo1`.`c` >= 3))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "((`demo1`.`a` < 3) and (`demo1`.`b` in (1,13)) and (`demo1`.`c` >= 3))" }, { "transformation": "constant_propagation", "resulting_condition": "((`demo1`.`a` < 3) and (`demo1`.`b` in (1,13)) and (`demo1`.`c` >= 3))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((`demo1`.`a` < 3) and (`demo1`.`b` in (1,13)) and (`demo1`.`c` >= 3))" } ] } }, { "table_dependencies": [ { "table": "`demo1`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ ] }, { "rows_estimation": [ { "table": "`demo1`", "range_analysis": { "table_scan": { "rows": 32, "cost": 12.5 }, "potential_range_indices": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "iabc", "usable": true, "key_parts": [ "a", "b", "c", "id" ] }, { "index": "ic", "usable": true, "key_parts": [ "c", "id" ] } ], "best_covering_index_scan": { "index": "iabc", "cost": 7.4718, "chosen": true }, "setup_range_conditions": [ ], "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "iabc", "ranges": [ "NULL < a < 3" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": true, "rows": 3, "cost": 1.6146, "chosen": true }, { "index": "ic", "ranges": [ "3 <= c" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 17, "cost": 21.41, "chosen": false, "cause": "cost" } ], "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "iabc", "rows": 3, "ranges": [ "NULL < a < 3" ] }, "rows_for_plan": 3, "cost_for_plan": 1.6146, "chosen": true } } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`demo1`", "best_access_path": { "considered_access_paths": [ { "access_type": "range", "rows": 3, "cost": 2.2146, "chosen": true } ] }, "cost_for_plan": 2.2146, "rows_for_plan": 3, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "((`demo1`.`a` < 3) and (`demo1`.`b` in (1,13)) and (`demo1`.`c` >= 3))", "attached_conditions_computation": [ { "table": "`demo1`", "rechecking_index_usage": { "recheck_reason": "low_limit", "limit": 2, "row_estimate": 3, "range_analysis": { "table_scan": { "rows": 32, "cost": 40.4 }, "potential_range_indices": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "iabc", "usable": false, "cause": "not_applicable" }, { "index": "ic", "usable": true, "key_parts": [ "c", "id" ] } ], "best_covering_index_scan": { "index": "iabc", "cost": 7.4718, "chosen": true }, "setup_range_conditions": [ ], "group_index_range": { "chosen": false, "cause": "cannot_do_reverse_ordering" }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "ic", "ranges": [ "3 <= c" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 17, "cost": 21.41, "chosen": false, "cause": "cost" } ] } } } } ], "attached_conditions_summary": [ { "table": "`demo1`", "attached": "((`demo1`.`a` < 3) and (`demo1`.`b` in (1,13)) and (`demo1`.`c` >= 3))" } ] } }, { "clause_processing": { "clause": "ORDER BY", "original_clause": "`demo1`.`c` desc", "items": [ { "item": "`demo1`.`c`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`demo1`.`c` desc" } }, { "refine_plan": [ { "table": "`demo1`", "access_type": "index_scan" } ] }, { "reconsidering_access_paths_for_index_ordering": { "clause": "ORDER BY", "index_order_summary": { "table": "`demo1`", "index_provides_order": false, "order_direction": "undefined", "index": "unknown", "plan_changed": false } } } ] } }, { "join_execution": { "select#": 1, "steps": [ { "filesort_information": [ { "direction": "desc", "table": "`demo1`", "field": "c" } ], "filesort_priority_queue_optimization": { "limit": 2, "rows_estimate": 2656, "row_size": 22, "memory_available": 262144, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 3, "examined_rows": 32, "number_of_tmp_files": 0, "sort_buffer_size": 90, "sort_mode": "<sort_key, additional_fields>" } } ] } } ]}join_preparation段落
join_preparation段落展现了筹备阶段的执行过程。
join_optimization段落
join_optimization展现优化阶段的执行过程，是剖析OPTIMIZER TRACE的重点。这段内容超级长，而且分了好多步骤，无妨依照步骤逐段剖析：
condition_processing
该段用来做条件解决，次要对WHERE条件进行优化解决。
其中：
- condition：优化对象类型。WHERE条件句或者是HAVING条件句
- original_condition：优化前的原始语句
steps：次要包含三步，别离是quality_propagation（等值条件句转换），constant_propagation（常量条件句转换），trivial_condition_removal（有效条件移除的转换）
- transformation：转换类型句
- resulting_condition：转换之后的后果输入
substitute_generated_columns
substitute_generated_columns用于替换虚构生成列
table_dependencies
table_dependencies段剖析表之间的依赖关系
其中：
- table：波及的表名，如果有别名，也会展现进去
- row_may_be_null：行是否可能为NULL，这里是指JOIN操作之后，这张表里的数据是不是可能为NULL。如果语句中应用了LEFT JOIN，则后一张表的row_may_be_null会显示为true
- map_bit：表的映射编号，从0开始递增
- depends_on_map_bits：依赖的映射表。次要是当应用STRAIGHT_JOIN强行管制连贯程序或者LEFT JOIN/RIGHT JOIN有程序差异时，会在depends_on_map_bits中展现前置表的map_bit值。
ref_optimizer_key_uses
列出所有可用的ref类型的索引。如果应用了组合索引的多个局部（例如本例，用到了index(from_date, to_date) 的多列索引），则会在ref_optimizer_key_uses下列出多个元素，每个元素中会列出ref应用的索引及对应值。
rows_estimation
顾名思义，用于估算须要扫描的记录数。
其中：
- table：表名
range_analysis：
- table_scan：如果全表扫描的话，须要扫描多少行（row，2838216），以及须要的代价（cost，286799）
- potential_range_indexes：列出表中所有的索引并剖析其是否可用。如果不可用的话，会列出不可用的起因是什么；如果可用会列出索引中可用的字段；
- best_covering_index_scan: 如果有笼罩索引，列出笼罩索引状况
- setup_range_conditions：如果有可下推的条件，则带条件思考范畴查问
- group_index_range：当应用了GROUP BY或DISTINCT时，是否有适合的索引可用。当未应用GROUP BY或DISTINCT时，会显示chosen=false, cause=not_group_by_or_distinct；如应用了GROUP BY或DISTINCT，然而多表查问时，会显示chosen=false，cause =not_single_table。其余状况下会尝试剖析可用的索引（potential_group_range_indexes）并计算对应的扫描行数及其所需代价
- skip_scan_range：是否应用了skip scan
analyzing_range_alternatives：剖析各个索引的应用老本
range_scan_alternatives：range扫描剖析
- index：索引名
- ranges：range扫描的条件范畴
- index_dives_for_eq_ranges：是否应用了index dive，该值会被参数eq_range_index_dive_limit变量值影响。
- rowid_ordered：该range扫描的后果集是否依据PK值进行排序
- using_mrr：是否应用了mrr
- index_only：示意是否应用了笼罩索引
- rows：扫描的行数
- cost：索引的应用老本
- chosen：示意是否应用了该索引
- analyzing_roworder_intersect：剖析是否应用了索引合并（index merge），如果未应用，会在cause中展现起因；如果应用了索引合并，会在该局部展现索引合并的代价。
chosen_range_access_summary：在前一个步骤中剖析了各类索引应用的办法及代价，得出了肯定的两头后果之后，在summary阶段汇总前一阶段的两头后果确认最初的计划
range_access_plan：range扫描最终抉择的执行打算。
- type：展现执行打算的type，如果应用了索引合并，则会显示index_roworder_intersect
- index：索引名
- rows：扫描的行数
- ranges：range扫描的条件范畴
- rows_for_plan：该执行打算的扫描行数
- cost_for_plan：该执行打算的执行代价
- chosen：是否抉择该执行打算
considered_execution_plans
负责比照各可行打算的开销，并抉择绝对最优的执行打算。
其中：
- plan_prefix：以后打算的前置执行打算。
- table：波及的表名，如果有别名，也会展现进去
best_access_path：通过比照considered_access_paths，抉择一个最优的拜访门路
considered_access_paths：以后思考的拜访门路
- access_type：应用索引的形式，可参考explain中的type字段
- index：索引
- rows：行数
- cost：开销老本
- chosen：是否选用这种执行门路
- condition_filtering_pct：相似于explain的filtered列，是一个估算值
- rows_for_plan：执行打算最终的扫描行数，由considered_access_paths.rows 乘以 condition_filtering_pct计算取得。
- cost_for_plan：执行打算的代价，由considered_access_paths.cost相加取得
- chosen：是否抉择了该执行打算
attaching_conditions_to_tables
基于considered_execution_plans中抉择的执行打算，革新原有where条件，并针对表减少适当的附加条件，以便于单表数据的筛选。
其中：
- original_condition：原始的条件语句
- attached_conditions_computation：应用启发式算法计算已应用的索引，如果已应用的索引的拜访类型是ref，则计算用range是否应用组合索引中更多的列，如果能够，则用range的形式替换ref。
attached_conditions_summary：附加之后的状况汇总
- table：表名
- attached：附加的条件或原语句中能间接下推给单表筛选的条件。
finalizing_table_conditions
最终的、通过优化后的表条件。
refine_plan
改善执行打算。
- table：表名及别名
join_execution
join_execution段落展现了执行阶段的执行过程。
3.源码剖析
源码次要针对成本计算局部，咱们能够来看一下具体有几个计算成本。
- rows_estimation->range_analysis->table_scan->cost: 12.5 全表扫描老本。
- best_covering_index_scan->cost: 7.4718 笼罩索引老本。
- considered_execution_plans->cost_for_plan: 2.2146 执行打算的代价，由considered_access_paths.cost相加取得。
除了以上的还有一些其余的，咱们本文只会讲这几个。其余的能够参照源码形式进行获取。
3.1 全表扫描成本计算
依据2.2大节中OPTIMIZER_TRACE的输入，咱们能够看rows_estimation，是用来计算一个表在不同的拜访门路下（全表扫描、索引扫描、范畴扫描等），数据库所要付出的代价。
能够通过lldb下一个test_quick_select断点：
(lldb)b test_quick_select而后执行关上一个新终端执行“查看应用索引状况1”的sql，断点进入跟踪到下图：
能够看到咱们计算扫描老本应用到了行数，持续跟踪到下图：
能够看到咱们跟中到ha_innobase::scan_time函数，发现咱们是在innodb引擎中解决的。发现其实不同到引擎计算规定会有差别。
下图为table_scan的计算成本：
能够看到图中的“12.5”和2.2大节中OPTIMIZER_TRACE的table_scan统一。
咱们能够看一下test_quick_select中的计算代码,在sql/opt_range.cc中：
int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, table_map prev_tables, ha_rows limit, bool force_quick_range, const ORDER::enum_order interesting_order){ double scan_time; //...省略 records= head->file->stats.records; //行数 if (!records) records++; /* purecov: inspected */ scan_time= records * ROW_EVALUATE_COST + 1; read_time= head->file->scan_time() + scan_time + 1.1; //...省略 //trace信息 Opt_trace_context * const trace= &thd->opt_trace; Opt_trace_object trace_range(trace, "range_analysis"); Opt_trace_object(trace, "table_scan"). //trace 全表扫描 add("rows", head->file->stats.records). //trace 行数 add("cost", read_time); //trace 老本信息 //...省略}ROW_EVALUATE_COST宏，在sql/sql_const.h中：
//行代价#define ROW_EVALUATE_COST 0.20通过以上信息咱们能够得悉到mysql5.6.48中的计算成本：
read_time= InnoDB页数 + scan_time + 1.1;scan_time= 行数 * 行代价 + 1; 那么“行数”怎么获取，其实能够通过：
-- 获取demo1表的表状态show table status like 'demo1' \G;
那么“InnoDB页数”又怎么获取：
InnoDB页数 = 数据长度 / InnoDB页大小
其实数据长度咱们有了，是“65536”。
InnoDB页大小，能够通过show variables获取，通常是16k：
show variables like 'innodb_page_size';
InnoDB页数 = 65536 / 16384 = 4
scan_time= 32 * 0.2 + 1 = 7.4
read_time= 4 + scan_time + 1.1 = 12.5
最终read_time等于12.5，也就是table_scan的cost老本。
留神： 其实在高版本中，比方mysql8.0中scan_time对应cpu_cost，read_time对应io_cost，不过计算公式有所扭转。
3.2 笼罩索引老本
int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, table_map prev_tables, ha_rows limit, bool force_quick_range, const ORDER::enum_order interesting_order){ //...省略 int key_for_use= find_shortest_key(head, &head->covering_keys); double key_read_time= param.table->file->index_only_read_time(key_for_use, rows2double(records)) + records * ROW_EVALUATE_COST; //计算局部 //...省略 }依据计算局部咱们能够看到index_only_read_time函数，也是计算局部之一，函数原型如下（在sql/handler.cc中）：
double handler::index_only_read_time(uint keynr, double records){ double read_time; uint keys_per_block= (stats.block_size/2/ (table_share->key_info[keynr].key_length + ref_length) + 1); read_time=((double) (records + keys_per_block-1) / (double) keys_per_block); return read_time;}
源码中records ROW_EVALUATE_COST = 32 0.2 = 6.4，这个是行老本。
行老本须要加上read_time，read_time计算就绝对比较复杂。
keys_per_block = (索引块大小/2/(键长度+援用长度)+1)
依据图中能够得出：
keys_per_block = (16384/2/(15+4)+1) = 432
432其实是取整后的值。
而后read_time = ((double) (32 + 432 - 1) /
(double) 432);read_time = 463 / 432 = 1.0717
保留4位小数后等到后果1.0717，而后在加上行老本的6.4,最终失去咱们笼罩索引老本的“7.4717”
3.3 执行打算的代价
要失去执行打算代价咱们能够下一个断点：
(lldb)b best_access_path
依据上图能够来看一下咱们具体的代码实现局部，如下（在sql/sql_planner.cc中）：
void Optimize_table_order::best_access_path( JOIN_TAB *s, table_map remaining_tables, uint idx, bool disable_jbuf, double record_count, POSITION *pos, POSITION *loose_scan_pos){ //...省略 const double scan_cost= tmp + (record_count * ROW_EVALUATE_COST * rnd_records); trace_access_scan.add("rows", rows2double(rnd_records)). add("cost", scan_cost); //...省略 } scan_cost=
tmp + (record_count * ROW_EVALUATE_COST * rnd_records);record_count记录计数，以后为1，rnd_records为找到记录数以后为3。tmp计算局部比较复杂，能够留个小作业。看一下best_access_path函数中实现。
最终scan_cost=1.6146+（1 0.2 3）= 2.2146
2.2146为取小数后4位。
4. 解决抉择索引谬误计划
其实解决索引计划能够概括为几种：
- 强制应用force index（key）进行语句查问。
- 应用ANALYZE TABLE tablename; (不倡议应用)
- 不倡议应用起因是因为该计划，不倡议在生产间接应用。能够应用于低峰段，比如说凌晨。
- 删除不必要的索引；
总结
- 其实对应文章中的实际内容bug，这个是5.6中的bug。在5.7中并不存在该问题。
- 不过的成本计算形式不同，不同的引擎计算根本的形式也不同，不同的mysql版本计算成本的形式也会不同。—— 三不同
- InnoDB页大小计算= 数据长度 / InnoDB页大小。
- ROW_EVALUATE_COST行代价等于0.2。
- 解决抉择索引谬误计划能够分为三种：强制应用force index（key）、应用ANALYZE TABLE tablename、删除不必要索引。
参考文献
Tracing the Optimizer
https://dev.mysql.com/doc/int...
手把手教你意识OPTIMIZER_TRACE
http://blog.itpub.net/2821893...
MYSQL sql执行过程的一些跟踪剖析(二.mysql优化器追踪剖析)
http://blog.itpub.net/2986302...
应用 Trace 进行执行打算剖析
https://www.cnblogs.com/hbbbs...