最近,在脉脉上看到一个楼主提出的问题:MySQL数据量大时,delete操作无法命中索引;并且还附上了相关案例截图。
最终,楼主通过开启MySQL分析优化器追踪,定位到是优化器搞的鬼,它觉得花费时间太长。因为我这个是测试数据,究其原因是因为数据倾斜,导致计算出的数据占比较大、花费时间长。
大家要记住一点,一条SQL语句走哪条索引是通过其中的优化器和代价分析两个部分来决定的。所以,随着数据的不断变化,最优解也要跟着变化。因此,就需要DBA来不断的优化SQL。
对于查询情况,其实MySQL提供给我们一个功能来引导优化器更好的优化,那便是MySQL的查询优化提示(Query OptimizerHints)。比如,想让SQL强制走索引的话,可以使用 FORCE INDEX 或者USE INDEX;它们基本相同,不同点:在于就算索引的实际用处不大,FORCE INDEX也得要使用索引。
同样,你也可以通过IGNORE INDEX来忽略索引。
在我看来,虽然有MySQL Hints这种好用的工具,但我建议还是不要在生产环境使用,因为当数据量增长时,你压根儿都不知道这种索引的方式是否还适应于当前的环境,还是得配合DBA从索引的结构上去优化。
接下来,我来教大家如何用MySQL的trace分析优化器是如何选择执行计划的?很重要的手段,建议多实战一下。
1、什么是Trace?
关于这个问题,我觉得去最好的描述是官方文档。
在MySQL 5.6中,MySQL优化器增加了一个新的跟踪功能。该接口由一组optimizer_trace_xxx系统变量和INFORMATION_SCHEMA.OPTIMIZER_TRACE表提供,但可能会发生变化。
通俗点,就是通过trace文件能够进一步了解为什么优化器选择A执行计划而不选择B执行计划,帮助我们更好的理解优化器的行为。
2、如何使用?
还是得看官方文档。
3、分析trace文件
根据我本地的一个例子为例,具体文件内容如下。
通过这个例子,我们可以得到全表扫描的代价如下。
分析结果:全表扫描访问的rows记录为3100,代价cost计算为719.1。
索引扫描的代价如下。
分析结果:这里看到了通过idx_gender索引过滤时,优化器预估需要返回2731记录,访问代价cost为3278.2,大于全表扫描代价719.1;因此,优化器倾向于选择全表扫描。
今晚上就熬夜写到这里吧。
领取专属 10元无门槛券
私享最新 技术干货