数据库 性能优化案例分析之一:软删除是慢查询的罪魁祸首?

quakewang · 2017年11月08日 · 最后由 ibcker 回复于 2017年12月21日 · 18261 次阅读
本帖已被管理员设置为精华贴

背景

在 Rails 项目里面,为了实现软删除,我们经常会使用 acts_as_paranoid 这个 gem。它会给数据库表添加一个 deleted_at 栏位,当删除数据时给这个栏位设置当前时间,查询数据时由于设置了 default_scope,会自动添加deleted_at is null的查询过滤这些数据,从而实现软删除。这个 gem 由于设置简单,使用方便,被用在很多项目中。

在给某个使用了这个 gem 的项目做性能优化时,发现很多类似这样的慢查询,平均执行时间超过 2000 毫秒:

select * from articles where id in (?, ?, ?, ...) and deleted_at is null;

分析过程

对于所有 Mysql 慢查询的分析过程都是类似的

第一步:EXPLAIN

explain select * from articles where id in (?, ?, ?, ...) and deleted_at is null\G;

得到了如下的输出:

  select_type: SIMPLE
        table: articles
   partitions: NULL
         type: ref
possible_keys: PRIMARY,index_articles_on_deleted_at
          key: index_articles_on_deleted_at
      key_len: 6
          ref: const
         rows: 9
     filtered: 0.50
        Extra: Using index condition

第二步:理解执行计划

从输出的possible_keyskey里面可以看到,这个查询有 2 个可能的索引可利用:主键索引和辅助索引,但是最终选择了辅助索引index_articles_on_deleted_at,和我们设想要用的主键索引PRIMARY有出入。为了进一步理解 mysql 为什么选择这样一个执行计划,可以把执行计划优化器的追踪参数打开,再次执行扩展分析计划,然后从INFORMATION_SCHEMA.OPTIMIZER_TRACE表中获取详细信息:

set optimizer_trace="enabled=on";
explain EXTENDED select * from articles where id in (?, ?, ?, ...) and deleted_at is null;
select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE\G;

在输出里有这样一段,Mysql 认为使用辅助索引的 IO 花销 16.8,比主键索引的花销 19.666 低,所以最终选择了这个辅助索引:

"considered_access_paths": [
  {
    "access_type": "ref",
    "index": "index_articles_on_deleted_at",
    "rows": 14,
    "cost": 16.8,
    "chosen": true
  },
  {
    "rows_to_scan": 14,
    "access_type": "range",
    "range_details": {
      "used_index": "PRIMARY"
    },
    "resulting_rows": 1.4,
    "cost": 19.666,
    "chosen": false
  }
]

第三步:分析数据

查看数据表的状态和索引状态:

show table status like 'articles'\G;

           Name: articles
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 3122573

show index from articles where key_name = 'index_articles_on_deleted_at'\G;

        Table: articles
   Non_unique: 1
     Key_name: index_articles_on_deleted_at
 Seq_in_index: 1
  Column_name: deleted_at
    Collation: A
  Cardinality: 122621

这个表有超过 300 万的数据,虽然辅助索引的分组看起来有 122621 那么多,但是都是对删除时间不同的索引,而实际大部分数据都是 NULL,所以这个查询完全利用不到辅助索引优势,导致 Mysql 预估的执行计划 cost 和实际相比有天壤之别。

解决方案

很简单,删除这个索引就好了,这个索引是acts_as_paranoid文档中推荐创建的,但是实际上大部分的数据模型都不需要建立这个索引,如果说它是罪魁祸首,也勉强说得过去。

扩展

虽然这个性能问题看起来很简单,但是每个简单问题背后都有很多更深入问题。 如果从INFORMATION_SCHEMA.OPTIMIZER_TRACE表中完整信息开始研究,我们会发现这个问题的根源在于 Mysql 5.6.9 之后引入的 optimizer_switch use_index_extensions,更进一步了解会发现辅助索引和主键索引同为 B+TREE,为什么会有不同的查询 cost 呢?

再扩展一下,如果这个问题不是在 deleted_at 这种类型的索引上,而是发生在其他栏位上,比如这样的慢查询:

select * from articles where id in (?, ?, ?, ...) and score > 85;

那是否可以通过 Cardinality 和 Rows 来判断有必要给 score 加索引呢,解决方案是否会不一样呢?

除了删除索引以外,我们还可以用 mysql 语句force index来强制指定查询使用主键索引,在 rails 里面如何方便地强制索引呢?

除了添加 deleted_at 这种标志位的方法以外,还没有其他方法实现软删除呢?相比较的优缺点是什么?

如果以上这些问题你都能回答了,恭喜你,EXP++,LV UP :)

下篇占坑 性能优化案例分析之二:时间区域查询的性能优化

Rei 将本帖设为了精华贴。 11月08日 20:33

加复合索引 (id, deleted_at) 应该也可以

这种问题的标准答案难道不是换 pg.....

另外这个问题的根本原因是 mysql 没有直方图统计信息,没办法知道索引列的数据分布,优化器拿不到准确的信息自然没办法优化了

liprais 回复

换换换。。。

pg 也不需要 deleted_at 做成索引,区分度太低

Specifies table from which the records will be fetched. For example:

Topic.select('title').from('posts')
# SELECT title FROM posts
Can accept other relation objects. For example:

Topic.select('title').from(Topic.approved)
# SELECT title FROM (SELECT * FROM topics WHERE approved = 't') subquery

Topic.select('a.title').from(Topic.approved, :a)
# SELECT a.title FROM (SELECT * FROM topics WHERE approved = 't') a

软删除我们用的状态位来表示 & 状态位一般都 enum 了 - 这样带来的坏处就是状态管理有点麻烦

然后我们还是用的 deleted_at 没有使用什么 GemConcern 手动 include 给需要的

希望楼主能给个赞赞的方案 多谢楼主的文章

然后补充一个方法

ActiveRecord::Base.connection.indexes("table_name").pluck(:name)

单列索引最好还是不要在一开始就加上吧,通常都是没用的,软删的索引还是根据业务需要加联合索引效率更高

实现软删除还要用 gem?直接设置数据的 status 不行么?

哈哈哈 如果是我的话 我会改写 destroy 方法

mico-xiaozhen 回复

这个以后会被其他人打的。

相比较的优缺点,有下篇吗?

脏数据也是要定期清理的吧

需要 登录 后方可回复, 如果你还没有账号请 注册新账号