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

quakewang · 发布于 2017年11月08日 · 最后由 dddd1919 回复于 2017年11月12日 · 1455 次阅读
162
本帖已被设为精华帖!

背景

在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 :)

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

共收到 11 条回复
1 Rei 将本帖设为了精华贴 11月08日 20:33
8

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

254

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

254

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

9800
254liprais 回复

换换换。。。

775

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

Abca79
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)

4277

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

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