|
【这篇文章最开始是在公司内部写的，后面抽了点时间稍微整理了一下，主要是把涉及到公司内部数据的部分去除，可能会导致一些阅读不畅】
本文的内容主要涉及到一些工具命令比如explain\optimize trace、mysql优化器的工作原理以及涉及到InnoDB的相关内容。
文章目录
mysql逻辑架构优化分析: explain和optimize traceexplainoptimize trace
优化器的工作原理成本常数基于成本的优化过程全表扫描使用索引idx_key1使用索引idx_key2
基于索引统计数据的成本计算
mysql逻辑架构
优化分析: explain和optimize trace
一条sql语句通常都会有多种执行方案，mysql的优化器会选择其认为最优的方案进行执行，这里的最优其是指成本最低，在后面的优化器工作原理中会详细讲到。在和mysql打交道的过程中，面对一些复杂的查询语句或者slow query，我们经常会想要知道mysql选择了什么样的执行方案以及为什么这样选择，mysql提供了两个命令explain和optimize trace来帮助我们解决问题。
explain
如果我们想要查看某条查询语句具体的执行计划，可以在语句前加explain运行，输出的内容就是具体的执行计划。explain属于比较简单的内容，网上资料也非常多，在此就不深入讲解。 
optimize trace
explain可以输出某条查询语句的具体的执行计划，但是有时候我们想更进一步知道优化器选择执行方案的过程，那就需要用到optimize trace。 optimize trace是mysql 5.6以后的版本中才提供的功能，该功能的开启和关闭是由系统变量optimize_trace来控制的。
# 查看变量optimize_tarce
mysql> SHOW VARIABLES LIKE 'optimizer_trace';
+-----------------+--------------------------+
| Variable_name | Value |
+-----------------+--------------------------+
| optimizer_trace | enabled=off,one_line=off |
+-----------------+--------------------------+
1 row in set (0.02 sec)
# 开启该功能
mysql> SET optimizer_trace="enabled=on";
Query OK, 0 rows affected (0.00 sec)
打开该功能后，执行想要查看的语句，然后到information_schema数据库下的OPTIMIZER_TRACE表查看具体的信息。
mysql> select * from `table_v1` where grade_id = '9149' and class_id = '4685' order by id desc limit 10;
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;
表OPTIMIZER_TRACE有4个字段，其含义如下： 下面以上述语句的trace为例进行分析：
{
"steps": [
{
// preparation阶段
"join_preparation": {
"select#": 1,
"steps": [
{
// 因为语句中用到select * ，此处先进行展开
"expanded_query": "省略"
}
]
}
},
{
// 优化阶段
"join_optimization": {
"select#": 1,
"steps": [
{ // 条件处理
"condition_processing": {
"condition": "WHERE",
"original_condition": "grade_id = '9149' and class_id = '4685' ",
"steps": [
{
// 等值传递：a = b and b = 5 --> a = 5 and b = 5
"transformation": "equality_propagation",
"resulting_condition": "grade_id = '9149' and class_id = '4685' "
},
{
// 常量传递： a = 5 and b > a --> a = 5 and b > 5
"transformation": "constant_propagation",
"resulting_condition": "grade_id = '9149' and class_id = '4685' "
},
{
// 去除无用的条件： a > b and 15 > 5 --> a > b
"transformation": "trivial_condition_removal",
"resulting_condition": "grade_id = '9149' and class_id = '4685' "
}
]
}
},
{
// 替换虚拟列
"substitute_generated_columns": {
}
},
{
// 表依赖信息
"table_dependencies": [
{
"table": "`table_v1`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
// 可使用索引的字段
"ref_optimizer_key_uses": [
{
"table": "`table_v1`",
"field": "grade_id",
"equals": "'9149'",
"null_rejecting": true
},
{
"table": "`table_v1 `",
"field": "class_id",
"equals": "'4685'",
"null_rejecting": true
}
]
},
{
// 预估单表访问的成本
"rows_estimation": [
{
// 全表扫描的成本
"table": "`table_v1`",
"range_analysis": {
"table_scan": {
"rows": 3271649,
"cost": 364606
},
// 可能使用的索引分析
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_grade_class_student",
"usable": true,
"key_parts": [
"grade",
"class",
"student",
"id"
]
}
],
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
},
// mysql8.0的新特性
"skip_scan_range": {
"potential_skip_scan_indexes": [
{
"index": "idx_grade_class_student",
"usable": false,
"cause": "query_references_nonkey_column"
}
]
},
// 分析各种可能的索引的成本
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idx_grade_class_student",
"ranges": [
"9149 |