Mysql SQL优化跟踪来看看是如何优化并决策使用哪个索引或者不适用索引

系统管理员 2024-03-31 15:17 42阅读 0赞

## 背景 ##

> 使用索引字段进行筛选数据时，explain查询语句发现MySQL居然没有使用索引，产生疑问，因此决定调查清楚为什么会不用索引，而是走全表扫描  
> 原因调查出的结果是当你要查询的数据量是连续又占整个表五分之一以上那就不会走索引了，因为顺序读取更快，这个五分之一不是绝对的，而是一个经验值，实际上还是需要SQL优化时计算走这个索引耗时和不走这个索引的耗时去比较，会使用最快的一种方式查询数据。  
> 原因是大概知道了，但是还是不够信服，想进一步看一下分别是多少耗时，最后判断使用了索引或者不走索引，因此我们使用优化器跟踪来看一看。

### 试验 ###

1.  链接Mysql数据库并选择好数据库
2.  设置上记录trace

SET optimizer_trace="enabled=on";

1.  执行要分析的SQL语句

select * from PKSessionHistory where sessionCreateTime >= '2022-11-28 16:00:00'

1.  查询优化跟踪记录

select * from information_schema.optimizer_trace;

查询结果

> 建议使用json工具来查看json，例如utools里的json插件

{
        "steps": [
            {
                "join_preparation": {
                    "select#": 1,
                    "steps": [
                        {
                            "expanded_query": "/* select#1 */ select `CohabitPKSessionHistory`.`id` AS `id`,`CohabitPKSessionHistory`.`pkSessionId` AS `pkSessionId`,`CohabitPKSessionHistory`.`cohabitSessionId` AS `cohabitSessionId`,`CohabitPKSessionHistory`.`userId` AS `userId`,`CohabitPKSessionHistory`.`objId` AS `objId`,`CohabitPKSessionHistory`.`createTime` AS `createTime`,`CohabitPKSessionHistory`.`ext` AS `ext`,`CohabitPKSessionHistory`.`userScore` AS `userScore`,`CohabitPKSessionHistory`.`objScore` AS `objScore`,`CohabitPKSessionHistory`.`sessionCreateTime` AS `sessionCreateTime`,`CohabitPKSessionHistory`.`sessionEndTime` AS `sessionEndTime`,`CohabitPKSessionHistory`.`expectedSessionEndTime` AS `expectedSessionEndTime`,`CohabitPKSessionHistory`.`expectedPKEndTime` AS `expectedPKEndTime`,`CohabitPKSessionHistory`.`endSource` AS `endSource`,`CohabitPKSessionHistory`.`totalScore` AS `totalScore`,`CohabitPKSessionHistory`.`cohabitType` AS `cohabitType`,`CohabitPKSessionHistory`.`winnerId` AS `winnerId`,`CohabitPKSessionHistory`.`loserId` AS `loserId`,`CohabitPKSessionHistory`.`userRoseScore` AS `userRoseScore`,`CohabitPKSessionHistory`.`objRoseScore` AS `objRoseScore` from `CohabitPKSessionHistory` where (`CohabitPKSessionHistory`.`sessionCreateTime` >= '2022-11-28 16:00:00')"
                        }
                    ]
                }
            },
            {
                "join_optimization": {
                    "select#": 1,
                    "steps": [
                        {
                            "condition_processing": {
                                "condition": "WHERE",
                                "original_condition": "(`CohabitPKSessionHistory`.`sessionCreateTime` >= '2022-11-28 16:00:00')",
                                "steps": [
                                    {
                                        "transformation": "equality_propagation",
                                        "resulting_condition": "(`CohabitPKSessionHistory`.`sessionCreateTime` >= '2022-11-28 16:00:00')"
                                    },
                                    {
                                        "transformation": "constant_propagation",
                                        "resulting_condition": "(`CohabitPKSessionHistory`.`sessionCreateTime` >= '2022-11-28 16:00:00')"
                                    },
                                    {
                                        "transformation": "trivial_condition_removal",
                                        "resulting_condition": "(`CohabitPKSessionHistory`.`sessionCreateTime` >= '2022-11-28 16:00:00')"
                                    }
                                ]
                            }
                        },
                        {
                            "substitute_generated_columns": {}
                        },
                        {
                            "table_dependencies": [
                                {
                                    "table": "`CohabitPKSessionHistory`",
                                    "row_may_be_null": false,
                                    "map_bit": 0,
                                    "depends_on_map_bits": []
                                }
                            ]
                        },
                        {
                            "ref_optimizer_key_uses": []
                        },
                        {
                            "rows_estimation": [
                                {
                                    "table": "`CohabitPKSessionHistory`",
                                    "range_analysis": {
                                        "table_scan": {
                                            "rows": 2084,
                                            "cost": 433.9
                                        },
                                        "potential_range_indexes": [
                                            {
                                                "index": "PRIMARY",
                                                "usable": false,
                                                "cause": "not_applicable"
                                            },
                                            {
                                                "index": "idx_sessionCreateTime",
                                                "usable": true,
                                                "key_parts": [
                                                    "sessionCreateTime",
                                                    "id"
                                                ]
                                            },
                                            {
                                                "index": "idx_sessionCreateTime_totalScore",
                                                "usable": true,
                                                "key_parts": [
                                                    "sessionCreateTime",
                                                    "totalScore",
                                                    "id"
                                                ]
                                            }
                                        ],
                                        "setup_range_conditions": [],
                                        "group_index_range": {
                                            "chosen": false,
                                            "cause": "not_group_by_or_distinct"
                                        },
                                        "analyzing_range_alternatives": {
                                            "range_scan_alternatives": [
                                                {
                                                    "index": "idx_sessionCreateTime",
                                                    "ranges": [
                                                        "0x99ae790000 <= sessionCreateTime"
                                                    ],
                                                    "index_dives_for_eq_ranges": true,
                                                    "rowid_ordered": false,
                                                    "using_mrr": false,
                                                    "index_only": false,
                                                    "rows": 182,
                                                    "cost": 219.41,
                                                    "chosen": true
                                                },
                                                {
                                                    "index": "idx_sessionCreateTime_totalScore",
                                                    "ranges": [
                                                        "0x99ae790000 <= sessionCreateTime"
                                                    ],
                                                    "index_dives_for_eq_ranges": true,
                                                    "rowid_ordered": false,
                                                    "using_mrr": false,
                                                    "index_only": false,
                                                    "rows": 182,
                                                    "cost": 219.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": "idx_sessionCreateTime",
                                                "rows": 182,
                                                "ranges": [
                                                    "0x99ae790000 <= sessionCreateTime"
                                                ]
                                            },
                                            "rows_for_plan": 182,
                                            "cost_for_plan": 219.41,
                                            "chosen": true
                                        }
                                    }
                                }
                            ]
                        },
                        {
                            "considered_execution_plans": [
                                {
                                    "plan_prefix": [],
                                    "table": "`CohabitPKSessionHistory`",
                                    "best_access_path": {
                                        "considered_access_paths": [
                                            {
                                                "rows_to_scan": 182,
                                                "access_type": "range",
                                                "range_details": {
                                                    "used_index": "idx_sessionCreateTime"
                                                },
                                                "resulting_rows": 182,
                                                "cost": 255.81,
                                                "chosen": true
                                            }
                                        ]
                                    },
                                    "condition_filtering_pct": 100,
                                    "rows_for_plan": 182,
                                    "cost_for_plan": 255.81,
                                    "chosen": true
                                }
                            ]
                        },
                        {
                            "attaching_conditions_to_tables": {
                                "original_condition": "(`CohabitPKSessionHistory`.`sessionCreateTime` >= '2022-11-28 16:00:00')",
                                "attached_conditions_computation": [],
                                "attached_conditions_summary": [
                                    {
                                        "table": "`CohabitPKSessionHistory`",
                                        "attached": "(`CohabitPKSessionHistory`.`sessionCreateTime` >= '2022-11-28 16:00:00')"
                                    }
                                ]
                            }
                        },
                        {
                            "refine_plan": [
                                {
                                    "table": "`CohabitPKSessionHistory`",
                                    "pushed_index_condition": "(`CohabitPKSessionHistory`.`sessionCreateTime` >= '2022-11-28 16:00:00')",
                                    "table_condition_attached": null
                                }
                            ]
                        }
                    ]
                }
            },
            {
                "join_execution": {
                    "select#": 1,
                    "steps": []
                }
            }
        ]
    }

> 从优化器记录来看，我们两个索引耗时都是219，全表扫描耗时会是419，因此选择了第一个索引。我们可以explain看一下会是一样的结果选择了第一个索引。另外写了using index condition，是使用了索引下推，不了解的可以查阅相关博客看下索引下推，主要是把mysql服务层要做的筛选下推给了存储引擎筛选，减少两层之间传输的数据量，减少IO。

> 由于该测试表数据量目前只有两千行，因此我们创建一个复制表，留四个字段，先插入大量数据，再构建索引，然后做试验~

1.  构建表

> 索引先不要去建，等数据插入完了再建索引，因为插入数据过程中要更改索引，又要IO

create table PkSessionHistoryCopy(
    `id` int(20) unsigned NOT NULL AUTO_INCREMENT primary key COMMENT '自增ID',
     `userId` int(11) NOT NULL COMMENT '用户ID,主动方',
      `objId` int(11) NOT NULL COMMENT '用户ID,被动方',
     `sessionCreateTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'PK开始时间 '
    );

1.  插入数据

> 我先从刚那个两千来行的表把数据全部复制进去，然后自己copy自己，数据就会不断翻倍

insert into PkSessionHistoryCopy (userId,objId,sessionCreateTime) select userId,objId,sessionCreateTime from CohabitPKSessionHistory

每执行一次下面的自己数据量就翻倍，我目前执行到翻倍成两百五十万行数据

insert into PkSessionHistoryCopy (userId,objId,sessionCreateTime) select userId,objId,sessionCreateTime from PkSessionHistoryCopy

1.  建索引

alter table PkSessionHistoryCopy add index idx_time (sessionCreateTime)

1.  看下数据量

select * from PkSessionHistoryCopy order by id desc limit 1

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>userId</th> 
   <th>objId</th> 
   <th>sessionCreateTime</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>2510823</td> 
   <td>52089016</td> 
   <td>52089177</td> 
   <td>2022-12-05 16:06:59</td> 
  </tr> 
 </tbody> 
</table>

1.  开始试验，我们先来下根据索引字段查的这个执行计划

explain select * from PkSessionHistoryCopy where sessionCreateTime >= '2020-01-01 00:00:00';

> 我们可以看到结果显示不会走索引，为啥呢，我们看下优化器跟踪

select * from information_schema.optimizer_trace;

{
        
        "steps": [
            {
        
                "join_preparation": {
        
                    "select#": 1,
                    "steps": [
                        {
        
                            "expanded_query": "/* select#1 */ select `PkSessionHistoryCopy`.`id` AS `id`,`PkSessionHistoryCopy`.`userId` AS `userId`,`PkSessionHistoryCopy`.`objId` AS `objId`,`PkSessionHistoryCopy`.`sessionCreateTime` AS `sessionCreateTime` from `PkSessionHistoryCopy` where (`PkSessionHistoryCopy`.`sessionCreateTime` >= '2020-01-01 00:00:00')"
                        }
                    ]
                }
            },
            {
        
                "join_optimization": {
        
                    "select#": 1,
                    "steps": [
                        {
        
                            "condition_processing": {
        
                                "condition": "WHERE",
                                "original_condition": "(`PkSessionHistoryCopy`.`sessionCreateTime` >= '2020-01-01 00:00:00')",
                                "steps": [
                                    {
        
                                        "transformation": "equality_propagation",
                                        "resulting_condition": "(`PkSessionHistoryCopy`.`sessionCreateTime` >= '2020-01-01 00:00:00')"
                                    },
                                    {
        
                                        "transformation": "constant_propagation",
                                        "resulting_condition": "(`PkSessionHistoryCopy`.`sessionCreateTime` >= '2020-01-01 00:00:00')"
                                    },
                                    {
        
                                        "transformation": "trivial_condition_removal",
                                        "resulting_condition": "(`PkSessionHistoryCopy`.`sessionCreateTime` >= '2020-01-01 00:00:00')"
                                    }
                                ]
                            }
                        },
                        {
        
                            "substitute_generated_columns": {
        }
                        },
                        {
        
                            "table_dependencies": [
                                {
        
                                    "table": "`PkSessionHistoryCopy`",
                                    "row_may_be_null": false,
                                    "map_bit": 0,
                                    "depends_on_map_bits": []
                                }
                            ]
                        },
                        {
        
                            "ref_optimizer_key_uses": []
                        },
                        {
        
                            "rows_estimation": [
                                {
        
                                    "table": "`PkSessionHistoryCopy`",
                                    "range_analysis": {
        
                                        "table_scan": {
        
                                            "rows": 2264381,
                                            "cost": 458228
                                        },
                                        "potential_range_indexes": [
                                            {
        
                                                "index": "PRIMARY",
                                                "usable": false,
                                                "cause": "not_applicable"
                                            },
                                            {
        
                                                "index": "idx_time",
                                                "usable": true,
                                                "key_parts": [
                                                    "sessionCreateTime",
                                                    "id"
                                                ]
                                            }
                                        ],
                                        "setup_range_conditions": [],
                                        "group_index_range": {
        
                                            "chosen": false,
                                            "cause": "not_group_by_or_distinct"
                                        },
                                        "analyzing_range_alternatives": {
        
                                            "range_scan_alternatives": [
                                                {
        
                                                    "index": "idx_time",
                                                    "ranges": [
                                                        "0x99a5420000 <= sessionCreateTime"
                                                    ],
                                                    "index_dives_for_eq_ranges": true,
                                                    "rowid_ordered": false,
                                                    "using_mrr": false,
                                                    "index_only": false,
                                                    "rows": 1132190,
                                                    "cost": 1.36e6,
                                                    "chosen": false,
                                                    "cause": "cost"
                                                }
                                            ],
                                            "analyzing_roworder_intersect": {
        
                                                "usable": false,
                                                "cause": "too_few_roworder_scans"
                                            }
                                        }
                                    }
                                }
                            ]
                        },
                        {
        
                            "considered_execution_plans": [
                                {
        
                                    "plan_prefix": [],
                                    "table": "`PkSessionHistoryCopy`",
                                    "best_access_path": {
        
                                        "considered_access_paths": [
                                            {
        
                                                "rows_to_scan": 2264381,
                                                "access_type": "scan",
                                                "resulting_rows": 1.13e6,
                                                "cost": 458226,
                                                "chosen": true
                                            }
                                        ]
                                    },
                                    "condition_filtering_pct": 100,
                                    "rows_for_plan": 1.13e6,
                                    "cost_for_plan": 458226,
                                    "chosen": true
                                }
                            ]
                        },
                        {
        
                            "attaching_conditions_to_tables": {
        
                                "original_condition": "(`PkSessionHistoryCopy`.`sessionCreateTime` >= '2020-01-01 00:00:00')",
                                "attached_conditions_computation": [],
                                "attached_conditions_summary": [
                                    {
        
                                        "table": "`PkSessionHistoryCopy`",
                                        "attached": "(`PkSessionHistoryCopy`.`sessionCreateTime` >= '2020-01-01 00:00:00')"
                                    }
                                ]
                            }
                        },
                        {
        
                            "refine_plan": [
                                {
        
                                    "table": "`PkSessionHistoryCopy`"
                                }
                            ]
                        }
                    ]
                }
            },
            {
        
                "join_explain": {
        
                    "select#": 1,
                    "steps": []
                }
            }
        ]
    }

我们可以看到全表扫描的cost花费是458228，扫描了2264381行  
![在这里插入图片描述][9458edf0c54146f1b338d6697ddb2931.png]  
再看我们走索引的耗时情况,扫描行数1132190，耗时1.36e6，也就是1360000，是不是比458228多多了，因此我们可以看到写着chosen:false，意思是选择：否，也就是不选这个索引。cause:cost原因是时间消耗更多  
![在这里插入图片描述][d00e64da3f5e4f849f6783043553c19c.png]  
那数据量多少的时候又能走索引更高效呢

这个日期是我试出来的这天，然后我们看一下占比发现是百分之六，所以网络上说的百分之二十、五分之一以上的说法，其实也未必准，实际操作还是得先explain去算一下两种的耗时看一下会不会选择索引。  
还有一种办法让他高效走索引，就是加limit，限制查询的数据条数，这样直接走索引查出那些页的数据即可，比扫描全表更高效，从这次试验我们也可以看出，不是你建了索引并且用索引字段筛选就一定走索引的，他还是会在优化时计算每种方式的耗时，选择最优的，另外如果你用覆盖索引、limit减少数据量也是可以优化速度的。

[9458edf0c54146f1b338d6697ddb2931.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/e77d6fb4f3b446e1a85980283a8e917f.png
[d00e64da3f5e4f849f6783043553c19c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/4c01010f52414f3ba47f9ba6b17add13.png