Mysql执行计划变蠢了?
为什么有多个索引时,mysql不选择我期望的索引进行检索?
索引的选择
首先我要弄清楚这点,先要知道mysql是如何处理请求过来的sql语句的。首先sql经过解析器与预处理对sql进行基本的分析,建立起解析树,这个时候就知道sql具体要干什么了。然后再经过查询优化器对sql进行一些优化,最后生成执行计划。在这个过程中就确定了需要使用的索引。
在优化器这一步如果可选择的索引有多种,会综合评估每一条方案的的执行成本,包括I/O操作成本、CPU成本、内存使用等。
一般是根据以下几点来分析:
- 索引的类型,比如如果多列索引可以覆盖,那肯定比使用单列索引更充分利用了索引,唯一索引肯定比非唯一索引更高校
- 等值查询肯定比范围查询更加效率高
- 索引的基数(cardinality),基数大的说明索引字段具有更多可能值,区分度更大,最后能够过滤出来的数据越少,也是优化器需要考虑的指标之一。这个可以通过show index from
table_name
命令中返回的 cardinality 字段查看到。
索引基数是通过对索引列进行采样计算得出的,不是精确的值。MySQL会扫描索引中的一部分数据页,估算出不重复值的数量。这个过程相对高效,但可能不完全准确。
索引选择为什么会失灵
有时候在分析sql慢查询的时候,会发现mysql选择了一个更慢的索引。
例如:
SELECT * FROM t1 WHERE f1 = 30 AND f2 = 'a';
优化器选择了 idx_f2索引,但该索引的性能较差,原因可能包括:
- 统计信息不准确:idx_f2 的基数较高,但统计信息过时,导致优化器未选择该索引(上面也说了索引基数是一个采样计算的不精确结果,表增删十分频繁,导致扫描行数不准确)。
- 数据分布不均:f2 的值高度集中在某些记录上,导致扫描大量记录。
解决办法:
- 更新统计信息,使用命令`ANALYZE TABLE t1; 可以重新计算统计信息
- 使用指定索引
SELECT * FROM t1 FORCE INDEX(idx_f1) WHERE f1 = 30 AND f2 = 'a';
- 当然可以直接建立多列索引,让查询全部都是用上索引。
跳出Mysql,给mysql减负,计算卸载
有时候,特别是一些统计数据,计算数据量非常大。在mysql里可能会写很多where group order 子句。mysql会执行很长的时间。
这部分功能本身数据实时性也没有那么高,一般都是定期统计一次。
由于mysql承担了所有业务的查询功能,过大的负担会对整体产生影响,可以将这部分计算让单独的服务器资源去处理。
比如在一台定时任务机器上,去使用简单的查询查询语句,甚至是通过id 范围分批获取mysql的数据,放到服务器中去进行计算聚合处理。
这种思想也属于分布式计算的一种“计算卸载”
这些操作往往需要大量的CPU和内存资源,如果全部在数据库服务器上执行,可能会导致数据库性能下降,进而影响整个系统的响应速度。
一般的场景有:
- 数据分析和报表生成, 生成复杂的报表和进行大规模的数据分析通常需要大量的计算资源。
- 大规模数据聚合。
改变业务实现方式
当然还有一种,就是改变业务实现方式,这种方式需要对业务有很深的掌握,一般是结合着业务模块重构进行。