laitimes
return

MySQL cost model: Say goodbye to blindly using EXPLAIN and predict index optimization strategies in advance

author：JD Cloud developer

èæ ̄

在 MySQL 中，当我们为表创建了一个或多个索引后，通常需要在索引定义完成后，根据具体的数据情况执行 EXPLAIN 命令，才能观察到数据库实际使用哪个索引、是否使用索引。这使得我们在添 加新索引之前，无法提前预知数据库是否能使用期望的索引。更为糟糕的是，有时甚至在添 加新的索引后，数据库在某些查询中会使用它，而在其他查询中则不会使用，这种情况下，我们无法确定索引是否发挥了预期的作用，让人感到非常苦恼。这种情况基本上意味着 MySQL 并没有为我们选择最优的索引，而我们不得不在茫茫数据中摸索，试图找到问题的症结所在。我们可能会尝试调整索引，甚至删除索引，然后重新添 加，希望 MySQL 能从中找到最优的索引选择。然而，这样的过程既耗时又费力，而且往往收效甚微。

å¦æå ̈æ·» å ç ́¢å1/4ä¹åï1/4æ们è1/2å¤é¢ç¥ç ́¢å1/4çä1/2¿ç ̈æ åμï1/4é£ä¹å ̄¹äºè¡ ̈è®3/4计å°å¤§æè£ ̈çãæ们å ̄以¥ å ̈è®3/4è®¡è¡ ̈ç»ææ¶ï1/4æ ́å æç¡®å°ç¥éåºè ̄¥éæ©åªäºç ́¢å1/4ï1/4å¦ä1/2ä1/4åç ́¢å1/4ï1/4以æé«æ¥è ̄¢æçãæ们ä ̧åéè¦ä3/4èμç²ç®å°è ̄åçæμï1/4èæ ̄å ̄以åºäºå®é çæ°æ®åæ¥è ̄¢æ åμï1/4ååºæ ́å ææºçå �³çãå æ¤ï1/4å ̄¹äº MySQL ç ̈æ·æ¥è ̄ ́ï1/4è1/2å¤é¢ç¥ç ́¢å1/4èμ°å¿çéæ±éå ̧ ̧è¿«åãæ们å ̧æè1/2æä ̧ç榹æ³ï1/4è1/2å¤è®©æ们å ̈æ·» å ç ́¢å1/4ä¹åï1/4å°±æ ̧ æ¥å°äºè§£ MySQL å°å¦ä1/2ä1/2¿ç ̈ç ́¢å1/4ï1/4以ä3/4¿æ们è1/2å¤æ ́å¥1/2å°ä1/4åè¡ ̈Èå �»æï1/4æé«æ¥è ̄¢æçãè¿å°æ大å°åè1/2»æ们çå·¥ä1/2è ́æ ï1/4æé«æ们çå·¥ä1/2æçï1/4让æ们è1/2å¤æ ́å ä ̧æ³ ̈äºä ̧å¡é»è3/4çå¤çï1/4èä ̧æ ̄å ̈ç ́¢å1/4çæμ·æ ́ä ̧æ£æã

ä ̧ºäºè§£å³è¿ä ̧ªé®é¢ï1/4æ们å ̄ä»¥æ·±å ¥ç 究 MySQL çç ́¢å1/4éæ©æºå¶ãå®é ä ̧ï1/4è¿ä ̧ªæºå¶çæ ̧å¿å° ±æ ̄代»·æ ̈¡åï1/4å®éè¿ä ̧ä ̧ªå ¬å1/4æ¥åóå®ç ́¢å1/4çéæ©çç¥ãç ̧å ̄¹äº MySQL å ¶ä»å¤æçæ¦å¿μï1/4代价æ ̈¡åå®ç°èμ·æ¥è¦ç®åå3/4å¤ãçæ代价æ ̈¡åä¹åï1/4æ们å ̄以é¢å äºè§£ MySQL å ̈æ§è¡æ¥è ̄¢âå �¶ä1/4å¦ä1/2éæ©ç ́¢å1/4ï1/4ä»èæ ́ææå°è¿è¡ç ́¢å1/4ä1/4åãå ̈æ¥ä ̧æ¥çæ« ä ̧ï1/4æå°ç»åè¿æè¿è¡ç ́¢å1/4ä1/4åçå ·ä1/2æ¡ä3/4ï1/4æ¥è ̄¦ç»è§£éå¦ä1/2è¿ç ̈»£»·æ ̈¡åæ¥ä1/4åç ́¢å1/4ã

MySQL »£»·æ ̈¡åæμ æ æ

MySQL cost model: Say goodbye to blindly using EXPLAIN and predict index optimization strategies in advance



MySQL数据库主要由4层组成：

1.è¿æ¥å±ï1/4客æ·ç« ̄åè¿æ¥æå¡ï1/4ä ̧»è¦å®æä ̧äºç±»ä1/41/4äºè¿æ¥å¤çãæ管çã以åç ̧å ³çå®å ̈æ¹æ¡ã

2.æå¡å±ï1/4ä ̧»è¦å®æ大å¤æ°çæ ̧å¿æå¡åè1/2ï1/4å¦SQLæ¥å£ï1/4并å®æç1/4åçæ¥è ̄¢ï1/4SQLçåååä1/4å以åå é ̈å1/2æ°çæ§è¡ã

3.å1/4æå±ï1/4è ́è ́£MySQLä ̧æ°æ®çåå ̈ååï1/4æå¡å ̈éè¿AP1ä ̧åå ̈å1/4æè¿è¡éä¿¡ã

4.存储层：将数据存储文件系统上，并完成与存储引擎的交互。

ç ́¢å1/4çç¥éæ©å ̈SQLä1/4åå ̈è¿è¡ç

SQL ä1/4åå ̈ä1/4åæææå ̄è1/2çæ§è¡åï1®/4éæ©ææææä1/2çæ§è¡ï1/4è¿ç§ä1/4åå ̈称ä¹ä ̧ºï1/4CBOï1/4Cost-based Optimizerï1/4åºúúææ¹çä1/4åå ̈ï1/4ã

Cost = Server Cost + Engine Cost = CPU Cost + IO Cost

å ¶ä ̧ï1/4CPU Cost è¡ ̈示计ç®çå1/4éï1/4æ ̄å¦ç ́¢å1/4é®å1/4çæ ̄è3/4ãè®°å1/2å1/4çæ ̄è3/4ãç»æéçæåº ...... è¿äºæä1/2é1/2å ̈ Server å±å®æï1/4

IO Cost is¡ ̈示å1/4æå± IO çå1/4éï1/4MySQL å ̄以éè¿åºåä ̧å1/4 è¡ ̈çæ°æ®æ ̄å¦å ̈å åä ̧ï1/4åå«® è¡ç®è ̄»åå å IO å1/4é以åè ̄»åç£ç IO çå1/4éã

æºç ç®è ̄»

MySQLçæ°æ®æºä»£ç éç ̈äº5.7.22çæ¬ï1/4åç»ç代»·è®¡ç®å ¬å1/4å°åºäºæ¤çæ¬è¿è¡åèã

MySQL cost model: Say goodbye to blindly using EXPLAIN and predict index optimization strategies in advance
MySQL cost model: Say goodbye to blindly using EXPLAIN and predict index optimization strategies in advance



opt_costconstants.cc㻣»·æ ̈¡åâ计ç®æé代»·è®¡ç®ç³»æ°ã

/*
  在Server_cost_constants类中定义为静态常量变量的成本常量的值。如果服务器管理员没有在server_cost表中添加新值，则将使用这些默认成本常数值。
  5.7版本开始可用从数据库加载常量值，该版本前使用代码中写的常量值
*/

// 计算符合条件的⾏的代价，⾏数越多，此项代价越⼤
const double Server_cost_constants::ROW_EVALUATE_COST= 0.2;

// 键⽐较的代价，例如排序
const double Server_cost_constants::KEY_COMPARE_COST= 0.1;
  
/* 
   内存临时表的创建代价
   通过基准测试，创建Memory临时表的成本与向表中写入10行的成本一样高。
*/
const double Server_cost_constants::MEMORY_TEMPTABLE_CREATE_COST= 2.0;

// 内存临时表的⾏代价
const double Server_cost_constants::MEMORY_TEMPTABLE_ROW_COST= 0.2;

/*
  内部myisam或innodb临时表的创建代价
  创建MyISAM表的速度是创建Memory表的20倍。
*/
const double Server_cost_constants::DISK_TEMPTABLE_CREATE_COST= 40.0;

/*
  内部myisam或innodb临时表的⾏代价
  当行数大于1000时，按顺序生成MyISAM行比生成Memory行慢2倍。然而，没有非常大的表的基准，因此保守地将此系数设置为慢5倍（即成本为1.0）。
*/
const double Server_cost_constants::DISK_TEMPTABLE_ROW_COST= 1.0;




/*
  在SE_cost_constants类中定义为静态常量变量的成本常量的值。如果服务器管理员没有在engine_cost表中添加新值，则将使用这些默认成本常数值。
*/

// 从主内存缓冲池读取块的成本
const double SE_cost_constants::MEMORY_BLOCK_READ_COST= 1.0;

// 从IO设备（磁盘）读取块的成本
const double SE_cost_constants::IO_BLOCK_READ_COST= 1.0;

opt_costmodel.cc㻣»·æ ̈¡åââé ̈åæ¶åæ¹æ³ã

double Cost_model_table::page_read_cost(double pages) const
{
  DBUG_ASSERT(m_initialized);
  DBUG_ASSERT(pages >= 0.0);

  // 估算聚集索引内存中页面数占其所有页面数的比率
  const double in_mem= m_table->file->table_in_memory_estimate();

  const double pages_in_mem= pages * in_mem;
  const double pages_on_disk= pages - pages_in_mem;
  DBUG_ASSERT(pages_on_disk >= 0.0);

  const double cost= buffer_block_read_cost(pages_in_mem) +
    io_block_read_cost(pages_on_disk);

  return cost;
}

double Cost_model_table::page_read_cost_index(uint index, double pages) const
{
  DBUG_ASSERT(m_initialized);
  DBUG_ASSERT(pages >= 0.0);

  double in_mem= m_table->file->index_in_memory_estimate(index);

  const double pages_in_mem= pages * in_mem;
  const double pages_on_disk= pages - pages_in_mem;

  const double cost= buffer_block_read_cost(pages_in_mem) +
    io_block_read_cost(pages_on_disk);

  return cost;
}

handler.ccã代价æ ̈¡åââé ̈åæ¶åæ¹æ³ã

// 聚集索引扫描IO代价计算公式
Cost_estimate handler::read_cost(uint index, double ranges, double rows)
{

  DBUG_ASSERT(ranges >= 0.0);
  DBUG_ASSERT(rows >= 0.0);

  const double io_cost= read_time(index, static_cast<uint>(ranges),
                                  static_cast<ha_rows>(rows)) *
                        table->cost_model()->page_read_cost(1.0);
  Cost_estimate cost;
  cost.add_io(io_cost);
  return cost;
}

// 表全量扫描代价相关计算（IO-cost）
Cost_estimate handler::table_scan_cost()
{
  const double io_cost= scan_time() * table->cost_model()->page_read_cost(1.0);
  Cost_estimate cost;
  cost.add_io(io_cost);
  return cost;
}

// 覆盖索引扫描代价相关计算
Cost_estimate handler::index_scan_cost(uint index, double ranges, double rows)
{
  DBUG_ASSERT(ranges >= 0.0);
  DBUG_ASSERT(rows >= 0.0);

  const double io_cost= index_only_read_time(index, rows) *
    table->cost_model()->page_read_cost_index(index, 1.0);
  Cost_estimate cost;
  cost.add_io(io_cost);
  return cost;
}


/**
  估算在指定 keynr索引进行覆盖扫描（不需要回表），扫描 records条记录，需要读取的索引页面数

  @param keynr    Index number
  @param records  Estimated number of records to be retrieved
  @return
    Estimated cost of 'index only' scan
*/

double handler::index_only_read_time(uint keynr, double records)
{
  double read_time;
  uint keys_per_block= (stats.block_size/2/
                        (table_share->key_info[keynr].key_length + ref_length) +
                        1);
  read_time=((double) (records + keys_per_block-1) /
             (double) keys_per_block);
  return read_time;
}

sql_planner.ccãç ̈äºref访é®ç±»åç ́¢å1/4è ́¹ç ̈计ç®ã

double tmp_fanout= 0.0;
        if (table->quick_keys.is_set(key) && !table_deps &&          //(C1)
            table->quick_key_parts[key] == cur_used_keyparts &&      //(C2)
            table->quick_n_ranges[key] == 1+MY_TEST(ref_or_null_part))  //(C3)
        {
          tmp_fanout= cur_fanout= (double) table->quick_rows[key];
        }
        else
        {
          // Check if we have statistic about the distribution
          if (keyinfo->has_records_per_key(cur_used_keyparts - 1))
          {
            cur_fanout= keyinfo->records_per_key(cur_used_keyparts - 1);
            
            if (!table_deps && table->quick_keys.is_set(key) &&     // (1)
                table->quick_key_parts[key] > cur_used_keyparts)    // (2)
                {
                  trace_access_idx.add("chosen", false)
                      .add_alnum("cause", "range_uses_more_keyparts");
                  is_dodgy= true;
                  continue;
                }

            tmp_fanout= cur_fanout;
          }
          else
          {
            
            rec_per_key_t rec_per_key;
            if (keyinfo->has_records_per_key(
                  keyinfo->user_defined_key_parts - 1))
              rec_per_key=
                keyinfo->records_per_key(keyinfo->user_defined_key_parts - 1);
            else
              rec_per_key=
                rec_per_key_t(tab->records()) / distinct_keys_est + 1;

            if (tab->records() == 0)
              tmp_fanout= 0.0;
            else if (rec_per_key / tab->records() >= 0.01)
              tmp_fanout= rec_per_key;
            else
            {
              const double a= tab->records() * 0.01;
              if (keyinfo->user_defined_key_parts > 1)
                tmp_fanout=
                  (cur_used_keyparts * (rec_per_key - a) +
                   a * keyinfo->user_defined_key_parts - rec_per_key) /
                  (keyinfo->user_defined_key_parts - 1);
              else
                tmp_fanout= a;
              set_if_bigger(tmp_fanout, 1.0);
            }
            cur_fanout= (ulong) tmp_fanout;
          }

          if (ref_or_null_part)
          {
            // We need to do two key searches to find key
            tmp_fanout*= 2.0;
            cur_fanout*= 2.0;
          }
         
          if (table->quick_keys.is_set(key) &&
              table->quick_key_parts[key] <= cur_used_keyparts &&
              const_part &
              ((key_part_map)1 << table->quick_key_parts[key]) &&
              table->quick_n_ranges[key] == 1 + MY_TEST(ref_or_null_part &
                                                     const_part) &&
              cur_fanout > (double) table->quick_rows[key])
          {
            tmp_fanout= cur_fanout= (double) table->quick_rows[key];
          }
        }


······

······ 

          // Limit the number of matched rows
          const double tmp_fanout=
            min(cur_fanout, (double) thd->variables.max_seeks_for_key);
          if (table->covering_keys.is_set(key)
              || (table->file->index_flags(key, 0, 0) & HA_CLUSTERED_INDEX))
          {
            // We can use only index tree
            const Cost_estimate index_read_cost=
              table->file->index_scan_cost(key, 1, tmp_fanout);
            cur_read_cost= prefix_rowcount * index_read_cost.total_cost();
          }
          else if (key == table->s->primary_key &&
                   table->file->primary_key_is_clustered())
          {
            const Cost_estimate table_read_cost=
              table->file->read_cost(key, 1, tmp_fanout);
            cur_read_cost= prefix_rowcount * table_read_cost.total_cost();
          }
          else
            cur_read_cost= prefix_rowcount *
              min(table->cost_model()->page_read_cost(tmp_fanout),
                  tab->worst_seeks);

handler.ccãç ̈äºrange访é®ç±»åç ́¢å1/4è ́¹ç ̈计ç®ã

handler::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq,
                                     void *seq_init_param, uint n_ranges_arg,
                                     uint *bufsz, uint *flags, 
                                     Cost_estimate *cost)
{
  KEY_MULTI_RANGE range;
  range_seq_t seq_it;
  ha_rows rows, total_rows= 0;
  uint n_ranges=0;
  THD *thd= current_thd;
  
  /* Default MRR implementation doesn't need buffer */
  *bufsz= 0;

  DBUG_EXECUTE_IF("bug13822652_2", thd->killed= THD::KILL_QUERY;);

  seq_it= seq->init(seq_init_param, n_ranges, *flags);
  while (!seq->next(seq_it, &range))
  {
    if (unlikely(thd->killed != 0))
      return HA_POS_ERROR;
    
    n_ranges++;
    key_range *min_endp, *max_endp;
    if (range.range_flag & GEOM_FLAG)
    {
      min_endp= &range.start_key;
      max_endp= NULL;
    }
    else
    {
      min_endp= range.start_key.length? &range.start_key : NULL;
      max_endp= range.end_key.length? &range.end_key : NULL;
    }
    
    
    int keyparts_used= 0;
    if ((range.range_flag & UNIQUE_RANGE) &&                        // 1)
        !(range.range_flag & NULL_RANGE))
      rows= 1; /* there can be at most one row */
    else if ((range.range_flag & EQ_RANGE) &&                       // 2a)
             (range.range_flag & USE_INDEX_STATISTICS) &&           // 2b)
             (keyparts_used= my_count_bits(range.start_key.keypart_map)) &&
             table->
               key_info[keyno].has_records_per_key(keyparts_used-1) && // 2c)
             !(range.range_flag & NULL_RANGE))
    {
      rows= static_cast<ha_rows>(
        table->key_info[keyno].records_per_key(keyparts_used - 1));
    }
    else
    {
      DBUG_EXECUTE_IF("crash_records_in_range", DBUG_SUICIDE(););
      DBUG_ASSERT(min_endp || max_endp);
      if (HA_POS_ERROR == (rows= this->records_in_range(keyno, min_endp, 
                                                        max_endp)))
      {
        /* Can't scan one range => can't do MRR scan at all */
        total_rows= HA_POS_ERROR;
        break;
      }
    }
    total_rows += rows;
  }
  
  if (total_rows != HA_POS_ERROR)
  {
    const Cost_model_table *const cost_model= table->cost_model();

    /* The following calculation is the same as in multi_range_read_info(): */
    *flags|= HA_MRR_USE_DEFAULT_IMPL;
    *flags|= HA_MRR_SUPPORT_SORTED;

    DBUG_ASSERT(cost->is_zero());
    if (*flags & HA_MRR_INDEX_ONLY)
      *cost= index_scan_cost(keyno, static_cast<double>(n_ranges),
                             static_cast<double>(total_rows));
    else
      *cost= read_cost(keyno, static_cast<double>(n_ranges),
                       static_cast<double>(total_rows));
    cost->add_cpu(cost_model->row_evaluate_cost(
      static_cast<double>(total_rows)) + 0.01);
  }
  return total_rows;
}

éªè ̄å ¬å1/4

å建éªè ̄éè¦çè¡ ̈

CREATE TABLE `store_goods_center`
(
    `id`           bigint(20)  NOT NULL AUTO_INCREMENT COMMENT '主键id',
    `sku_id`       bigint(20)  NOT NULL COMMENT '商品skuid',
    `station_no`   varchar(20) NOT NULL COMMENT '门店编号',
    `org_code`     bigint(20)  NOT NULL COMMENT '商家编号',
    `extend_field` text COMMENT '扩展字段',
    `version`      int(11)          DEFAULT '0' COMMENT '版本号',
    `create_time`  datetime         DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `create_pin`   varchar(50)      DEFAULT '' COMMENT '创建人',
    `update_time`  datetime         DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
    `update_pin`   varchar(50)      DEFAULT '' COMMENT '更新人',
    `yn`           tinyint(4)       DEFAULT '0' COMMENT '删除标示  0:正常  1：删除',
    `ts`           timestamp   NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '时间戳',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uniq_storegoods` (`station_no`, `sku_id`) USING BTREE,
    KEY `idx_storegoods_org` (`org_code`, `sku_id`, `station_no`),
    KEY `idx_sku_id` (`sku_id`),
    KEY `idx_station_no_and_id` (`station_no`, `id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='门店商品关系表';

éè¿åå ̈è¿ç ̈åå§åæμè ̄æ°æ®

DELIMITER //
CREATE PROCEDURE callback()
BEGIN
    DECLARE num INT;
    SET num = 1;
    WHILE
        num <= 100000 DO
        INSERT INTO store_goods_center(sku_id, station_no, org_code) VALUES (num + 10000000, floor(50+rand()*(100-50+1)), num);
        SET num = num + 1;
    END WHILE;
END;

æ§è¡åå ̈è¿ç ̈çææ°æ®

CALL callback();

1.全表扫描计算代价公式

计算过程：

// 不同引擎计算方式有所区别
// innodb引擎实现handler.h
// 预估记录数：ha_innobase::info_low
// 页数量：ha_innobase::scan_time【数据总大小(字节) / 页大小】

// 查询全表数据大小（7880704） 
SHOW TABLE STATUS LIKE 'store_goods_center'; 
// 查询数据库页大小（默认：16384） 
SHOW VARIABLES LIKE 'innodb_page_size';

// 全表扫描计算代价
// 页数量
page = 数据总大小(字节) / 页大小 = 7880704 / 16384 = 481;
// 预估范围行数（总数据条数：10万，预估数据条数：99827，有一定误差）
records = 99827;


// 计算总代价
// 481 * 1 中的系数1 代表从主内存缓冲池读取块的成本（SE_cost_constants::IO_BLOCK_READ_COST= 1.0）
// 99827 * 0.2 中的系数0.2 代表计算符合条件的⾏的代价（ROW_EVALUATE_COST= 0.2）
cost = IO-cost + CPU-cost = (481 * 1) + (99827 * 0.2) = 481 + 19965.4 = 20446.4

éªè ̄ç»æï1/4

explain format = json
select * from store_goods_center;

"cost_info": {"query_cost": "20446.40"}

æ"ç»å ¬å1/4ï1/4

全表扫描代价 = 数据总大小 / 16384 + 预估范围行数 * 0.2

2.覆盖索引扫描计算代价公式

计算过程：

// 查询全表数据大小（7880704） 
SHOW TABLE STATUS LIKE 'store_goods_center'; 
// 查询数据库页大小（默认：16384） 
SHOW VARIABLES LIKE 'innodb_page_size';

// 预估范围行数（总数据条数：1999，预估数据条数：1999，有一定误差） 1999;
records = 1999

// keys_per_block计算
// block_size是文件的block大小，mysql默认为16K；
// key_len是索引的键长度；
// ref_len是主键索引的长度；
keys_per_block = (stats.block_size / 2 / (table_share->key_info[keynr].key_length + ref_length) + 1);
// table_share->key_info[keynr].key_length 为联合索引，分别是station_no和sku_id
// station_no 为varchar(20)且为utf8mb4，长度 = 20 * 4 + 2 (可变长度需要加2) = 82
// sku_id bigint类型，长度为8
// 主键索引为bigint类型，长度为8
keys_per_block = 16384 / 2 / (82 + 8 + 8) + 1 ≈ 84

// 计算总代价
read_time = ((double) (records + keys_per_block - 1) / (double) keys_per_block);
read_time = (1999 + 84 - 1) / 84 = 24.78;

// 计算总代价
// 24.78 * 1 中的系数1 代表从主内存缓冲池读取块的成本（SE_cost_constants::IO_BLOCK_READ_COST= 1.0）
// 1999 * 0.2 中的系数0.2 代表计算符合条件的⾏的代价（ROW_EVALUATE_COST= 0.2）
cost = IO-cost + CPU-cost = (24.78 * 1) + (1999 * 0.2) = 24.78 + 399.8 = 424.58

éªè ̄ç»æï1/4

explain format = json
select station_no from store_goods_center where station_no = '53';

"cost_info": {"query_cost": "424.58"}

æ"ç»å ¬å1/4ï1/4

keys_per_block = 8192 / 索引长度 + 1
覆盖索引扫描代价 = (records + keys_per_block - 1) / keys_per_block + 预估范围行数 * 0.2

公式简化（去除影响较小的复杂计算）
覆盖索引扫描代价 = (records * 涉及索引长度) / 8192 + 预估范围行数 * 0.2

3.ref索引扫描计算代价公式

计算过程：

// cardinality = 49（基数，即有多少个不同key统计。）
SHOW TABLE STATUS LIKE 'store_goods_center'; 

// 页数量 
page = 数据总大小(字节) / 页大小 = 7880704 / 16384 = 481; 

// 计算代价最低索引(sql_planner.cc 中find_best_ref函数)
// IO COST最坏不会超过全表扫描IO消耗的3倍(或者总记录数除以10) 
// 其中s->found_records表示表上的记录数，s->read_time在innodb层表示page数
// s-> worst_seeks = min((double) s -> found_records / 10, (double) s -> read_time * 3);
// cur_read_cost= prefix_rowcount * min(table->cost_model() -> page_read_cost(tmp_fanout), tab -> worst_seeks);

// 预估范围行数（总数据条数：10万，预估数据条数：99827，有一定误差）  
total_records = 99827; 
// 预估范围行数（总数据条数：1999，预估数据条数：1999，有一定误差） 1999;
records = 1999

// 计算总代价 
// 1999 * 0.2 中的系数0.2 代表计算符合条件的⾏的代价（ROW_EVALUATE_COST= 0.2）
// s-> worst_seeks = min((double) s -> found_records / 10, (double) s -> read_time * 3) -> min(99827 / 10, 481 * 3) = 481 * 3
// min(table->cost_model() -> page_read_cost(tmp_fanout), tab -> worst_seeks) -> min(page_read_cost(1999), 481 * 3) = 481 * 3
cost = IO-cost + CPU-cost = 481 * 3 + (1999 * 0.2) = 1443 + 399.8 = 1842.80

éªè ̄ç»æï1/4

explain format = json
select * from store_goods_center where station_no = '53';

"cost_info": {"query_cost": "1842.80"}

æ"ç»å ¬å1/4ï1/4

下面3个公式，取值最低的
1.(数据总大小 / 16384) * 3 + 预估范围行数 * 0.2
2.总记录数 / 10 + 预估范围行数 * 0.2
3.扫描出记录数 + 预估范围行数 * 0.2

4.range索引扫描计算代价公式 

// 预估范围行数（总数据条数：1299，预估数据条数：1299，有一定误差） 1299;
records = 1299

// 计算代价最低索引(handler.cc 中 multi_range_read_info_const 函数)
// 计算总代价 
// 1299 * 0.2 计算公式：cost_model->row_evaluate_cost(static_cast<double>(total_rows))
// + 0.01 计算公式：cost->add_cpu(cost_model->row_evaluate_cost(static_cast<double>(total_rows)) + 0.01);
// 1299 + 1 中的 +1 ：单个扫描区间（ id > 35018 ）
// 1299 + 1 计算公式：*cost= read_cost(keyno, static_cast<double>(n_ranges), static_cast<double>(total_rows));
// (1299 * 0.2 + 0.01 + 1299) * 1 中的系数1 代表从主内存缓冲池读取块的成本（SE_cost_constants::IO_BLOCK_READ_COST= 1.0） 
// 1299 * 0.2 中的系数0.2 代表计算符合条件的⾏的代价（ROW_EVALUATE_COST= 0.2） 
cost = IO-cost + CPU-cost = ((1299 * 0.2 + 0.01 + 1299 + 1) * 1) + (1299 * 0.2) = 1559.81 + 259.8 = 1819.61

éªè ̄ç»æï1/4

explain format = json
select * from store_goods_center where station_no = '53' and id > 35018;

"cost_info": {"query_cost": "1819.61"}

æ"ç»å ¬å1/4ï1/4

range扫描代价 = 预估范围行数 * 1.4 + 0.01 + 范围数

公式简化（去除影响较小的复杂计算） 
range扫描代价 = 预估范围行数 * 1.4

索引冲突案例

é ̈åºååç³»ç»ä ̧ä ̧»è¦åå ̈é ̈åºä ̧ååçå ³èä¿¡æ ̄ï1/4并ä ̧ºBç« ̄æä3/4æ ¹æ®é ̈åºIDæ¥è ̄¢å ³èååçåè1/2ãç±äºé ̈åºå ³èçåå°æ®éè3/4大ï1/4éè¦åé¡μæ¥è ̄¢å ³èåå°æ®ãä ̧ºé¿å æ·±åé¡μé®é¢ï1/4æ们éæ©åºäºä ̧次ææ°ä ̧»é®è¿è¡æ¥è ̄¢ï1/4æ ̧å¿ææ³ï1/4éè¿ä ̧»é®ç ́¢å1/4ï1/4æ ̄次å®ä1/2å° IDæå ̈ä1/2ç1/2®ï1/4ç¶åå3/4åéåNä ̧ªæ°æ®ãè¿æ ·ï1/4æ 论æ°æ®éå¤å°ï1/4æ¥è ̄¢æ§è1/2é1/2è1/2ä¿æç ̈³å®ãæ们å°ææ°æ®æ ¹æ®ä ̧»é®IDè¿è¡æåºï1/4ç¶åå¹æ¬¡ååºï1/4å°å1/2åæ¹æ¬¡çæ大IDä1/2ä ̧ºä ̧次æ¥è ̄¢ççéæ¡ä»¶ï1/4ã

select 字段1，字段2 ... from store_goods_center where station_no = ‘门店id’ and id > 上次查询最大id order by id asc

为了确保门店与商品组合的唯一性，我们在MySQL表中为门店ID和商品ID添 加了组合唯一索引【UNIQUE KEY uniq_storegoods (station_no, sku_id) USING BTREE】。由于该索引包含门店ID并且在联合索引的第一个位置，查询会使用该索引。但是，当分页查询命中该索引后，由于排序字段无法使用索引，产生了【Using filesort】，导致门店商品系统出现了一些慢查询。为了解决这个问题，我们对慢查询进行了优化，优化思路是创建一个新的索引，使该SQL可以使用索引的排序来规避【Using filesort】的负面影响，新添 加的索引为【KEY idx_station_no_and_id (station_no, id)】。添 加该索引后，效果立竿见影。

ç¶èï1/4æ们åç°ä»ç¶ææ ¢æ¥è ̄¢äº§çï1/4并ä ̧è¿äºæ ¢æ¥è ̄¢ä»ç¶ä1/2¿ç ̈ uniq_storegoodsç ́¢å1/4ï1/4èä ̧æ ̄ idx_station_no_and_idç ́¢å11 /4ãæ们å1/4å§æèï1/4a ̧ºä»ä¹MySQL没æä ̧ºæ»¬çç³»ç»æ ̈èä1/2¿ç ̈æä1/4çç ́¢å1/4ï1/4æ ̄MySQLç ́¢å1/4æ ̈èæ®é é¢ï1/4è¿æ ̄æ们å建ç ́¢å1/4æé®é¢ï1/4å¦ä1/2åæè1/2让MySQLå ̧®æ们æ ̈èæ们认a ̧ºæä1/4çç ́¢å1/4ï1/4

å1/2ç¶ï1/4æ们ä¹å ̄以ä1/2¿ç ̈FORCE INDEXå1/4ºè¡è®©MySQLèμ°æ们æåé¢è®3/4çç ́¢å1/4ï1/4ä1/2æ ̄è¿ç§æ¹å1/4å±é太大ï1 /4åæç ́¢å1/4ç» ́æ¤ææ¬åå3/4å3/4é«ï1/4çè³å ̄è1/2ä1/2¿ç ̈è ̄¥SQLçå ¶ä»ä ̧å¡æ§è1/2åä1/2ãä ̧ºäºçªç ́æ ́ä1/2ä1/4åçå¡ç¹ç¶æï1/4æ们éè¦äºè§£ä ̧ä ̧MySQLç ́¢å1/4æ ̈èåºå±é»è3/4ï1/4å³MySQL代»·æ ̈¡åãºè§£ç ̧åºè§ååï1/4ç°é¶æ®μçé®é¢å°è¿åè解ã

MySQL cost model: Say goodbye to blindly using EXPLAIN and predict index optimization strategies in advance

案例分析及优化

å ̈åé¡3/4åæçé®é¢æ¶ï1/4æ们åç°é®é¢æºäºåå§ç ́¢å1/4产çäºãUsing filesortãï1/4ä»èå ̄1/4è ́äºæ ¢æ¥è ̄¢çåºç°ãä ̧ºú解å³è¿§ªé®é¢ï1/4æ们æ°å¢äºä ̧ªçªç ́¢å1/4ï1/4å³ãKEY idx_station_no_and_id (station_no, id)ãï1/4以æ¿ä»£åæçç ́¢å1/4ãUNIQUE KEY uniq_storegoods (station_no, sku_id)ããç¶èï1/4å°1/2管æ°å¢ç ́¢å1/4å大é ̈åæ ¢æ¥è ̄¢å3/4å °äºè§£å³ï1/4ä1/2ä»æé ̈åæ ¢æ¥è ̄¢æªè1/2æ¶é¤ãè¿ä ̧æåç°ï1/4è¿äºæ ¢æ¥è ̄¢æ ̄ç±äºSQL没æä1/2¿ç ̈æ们ææçç ́¢å1/4ï1/4èæ ̄ä1/2¿ç ̈äºèç ́¢å1/4ï1/4ä»èå1/4åäºãUsing filesortãé®é¢ãå ̈éè¿explainè¿è¡åæåï1/4æ们ææ¶è¿æ²¡ææ3/4å°åéç解å³æ¹æ¡ã

é®é¢ï1/4å°1/2管æ们æ°å¢äºç ́¢å1/4ï1/4并ä ̧大é ̈åSQLå·²ç»è1/2å¤ä1/2¿ç ̈æ°ç ́¢å1/4è¿è¡ä1/4åï1/4ä1/2ä»åå ̈ä ̧äºSQL没æä1/2¿ç ̈æ°ç ́¢å1/4ã

// 通过代价模型进行分析

// 使用上面的测试数据进行分析
// 新增索引后都没有走新索引
// 老索引，扫描行数：1999，代价计算值：1842.80，ref类型索引
// 新索引，扫描行数：1999，代价计算值：1850.46，range类型索引
select 字段1，字段2 ... from store_goods_center where station_no = ‘门店id’ and id > -1 order by id asc;

// 新增索引后走新索引
// 老索引，扫描行数：1999，代价计算值：1842.80，ref类型索引 
// 新索引，扫描行数：1299，代价计算值：1819.61，range类型索引
select 字段1，字段2 ... from store_goods_center where station_no = ‘门店id’ and id > 35018 order by id asc;

ç»è¿åæMySQLç代»·æ ̈¡åï1/4æ们åç°MySQLå ̈éæ©ä1/2¿ç ̈åªä ̧ªç ́¢å1/4æ¶ï1/4ä ̧»è¦åå³äºæ«æåºçæ°æ®æ¡æ°ãå ·ä1/2æ¥è ̄ ́ï1/4æ«æåºçæ°æ®æ¡æ°è¶å°ï1/4MySQLå°±è¶å3/4åäºéæ©è ̄¥ç ́¢å1/4ï1/4ç±äºMySQLçç ́¢å1/4æ°æ®è®¿é®ç±»ååå1/4ï1/4计ç®å ¬å1/4ä¹ä1/4ææä ̧ååå æ¤ï1/4å ̈å¤ä ̧ªç ́¢å1/4çæ«æè¡æ°ç ̧è¿çæ åμä ̧ï1/4æéç ̧ ̧è �¢å1/4å ̄è1/2ä ̧æ们ææçç ́¢å1/4ææä ̧åï1/4ã顺çè¿ä ̧ªæè· ̄ææ¥ï1/4æ们åç°å1/2id > -1æ¶ï1/4æ 论æ ̄ä1/2¿ç ̈storeId + skuIdè¿æ ̄storeId + idç ́¢å1/4è¿è¡æ¥è ̄¢ï1/4æ«æåºçæ°æ®æ¡æ°æ ̄ç ̧åçãè¿æ ̄å ä ̧ºè¿ä ̧¤ç§æ¥è ̄¢æ¹å1/4åå �1/2æ ̄æ ¹æ®é ̈åºæ¥è ̄¢ååæ°æ®ï1/4ä ̧idå1/4è ̄å®å¤§äº1ãå æ¤ï1/4å ̄¹äºMySQLæ¥è ̄ ́ï1/4ç±äºè¿ä ̧¤ç§ç ́¢å1/4æ«æåºçæ°æ®æ¡æ°ç ̧åï1/4æ以ä1/2¿ç ̈åªç§ç ́¢å1/4ææç ̧å·®ä ̧å¤ãè¿å°±æ ̄ä ̧ºä»ä¹ä ̧é ̈åæ¥è ̄¢èμ°æ°ç ́¢åå ́ 1/4ï1/4èå¦ä ̧é ̈åæ¥è ̄¢èμ°èç ́¢å1/4çåå ãç¶èï1/4å1/2æ¥è ̄¢æ¡ä»¶ä ̧ºid > næ¶ï1/4storeId + idç ́¢å1/4çä1/4å¿ä3/4¿å3/4以æ3/4ç°ãå ä ̧ºå®è1/2å¤ç ́æ¥ä»ç ́¢å1/4ä ̧æ«æ并跳è¿id <= nçæ°æ®ï1/4èstoreId + skuIdç ́¢å1/4å ́æ æ³ç ́æ ́æ ¥è·³è¿è¿é ̈åæ°æ®ï1/4å æ¤çæ£æ¦¦°æ®æ¡æ°storeId + skuIdè¦å¤§äºstoreId + idå æ¤ï1/4å ̈æ¥è ̄¢æ¡ä»¶ä ̧ºid > næ¶ï1/4MySQLæ ́å3/4åäºä1/2¿ç ̈æ°ç ́¢å1/4ãï1/4éè¦æ³ ̈æçæ ̄ï1/4示ä3/4ç»åºçæ°æ®ç ́¢å1/4æ°æ®è®¿é®ç±»åä ̧åï1/4ä ́a ́ �ä ̧ªæ ̄rangeç ́¢å1/4ç±»åï1/4a ̧ä ̧ªæ ̄refç ́¢å1/4ç±»åãç±äºç®æ³ä ̧åï1/4å³ä1/2¿æä ̧ªç ́¢å1/4çæ£ç ́¢æ°æ®çç¥é«äºå¦ä ̧ä ̧ªç ́¢å1/4ï1/4ä¹å ̄è1/2å ̄1/4è ́ç³»ç»å°å ¶æ ̈èä ̧ºæä1/4ç ́¢å1/4ï1/4

é®é¢å·²ç»åææ ̧ æ¥ï1/4ä ̧»è¦åå æ ̄åå ̈å¤ä ̧ªç ́¢å1/4ï1/4ä ̧æ ¹æ®ç ́¢å1/4代价计ç®å ¬å1/4ç代价ç ̧è¿ï1/44 å ̄1/4è ́é3/4以ææ©ãå æ¤ï1/4解å³è¿ä ̧ªé®é¢çæ¹æ³ä ̧åºè ̄¥æ ̄åæ¶å®ä¹ä ̧¤ä ̧ªä1/4让MySQL"çº ç»"çç ́¢å1/4éæ©ãç ̧åï1/4åºè ̄¥å°ä ̧¤ä ̧ªç ́¢å1/4èåä ̧ºä ̧ä ̧ªç ́¢å1/4ãå ·ä1/2ç解å³æ¹æ¡æ ̄æ ¹æ®é ̈åºæ¥è ̄¢ï1/4å °åæ¥çä ̧»é®idä1/2ä ̧ºä ̧次æ¥è ̄¢çæ大idæ¿æ¢ä ̧ºskuIdãå ̈ç®æ³åæ¢å®æåï1/4å é¤æ°çé ̈åº+ä ̧»é®idç ́¢å1/4ãç¶èï1/4è¿ç§æ¹å1/4å ̄è1/2ä1/4åå¦ä ̧ä ̧ªé®é¢ãç±äºåºå±æåºç®æ³åçäºååï1/4ç±åæ¥çä ̧»é®idæ¹ä ̧ºskuIdï1/4ï1/4å ̄è1/2å ̄1/4è ́æ æ³ç ́æ¥ä»åºå±æå¡åæ¢ãæ¤æ¶ï1/4åºèèä»ä ̧æ ̧ ̧ä1/2¿ç ̈æ¤æ¥å£æ¥¡çåºç ̈è¿è¡å榢ãé¦æ³ ̈æçó � ̄ï1/4å¦æä ̧æ ̧ ̧ç³»ç»æ ̄åæºåé¡μè¿ä»£æ¥è ̄¢é ̈åºæ°æ ï1/4é£ä¹ä®̧æ ̧ ̧ç³»ç»å ̄以ç ́æ¥è¿è¡åæ¢ãä1/2å¦æè¿ç§åé¡μæ¥è ̄¢å ̈ä1/2åæ¶äº¤ç»å¤å°åºç ̈æå¡å ̈æ§è¡ï1/4åæ¢è¿ç ̈å°åå3/4ç ̧å1/2å¤å �ï1/4ä»ä»¬çåæ¢ææ¬ä ̧åºå±åæ¢ææ¬ç ̧åãä1/2æ ̄ï1/4è¿ä ̧ªç³»ç»çå ̄¹å¤æå¡å±äºè¿ç§æ åμï1/4ä ̧æ ̧ ̧è°ç ̈ç³»ç»ä1/4æå¤å°åºç ̈æå¡å ̈åä1/2åé¡μè¿ä»£æ¥è ̄¢æ°æ®ï1/4ä ̧ºè¿æ¬¡ä1/4åå ̧¦æ¥å3/4大å1/2±åã

最终，让底层独立完成切换方式最为合适。在切换过程中，关键在于正确区分新老算法。老算法在迭代过程中不应切换至新算法。原系统对外服务提供的下次迭代用的id可用来进行区分。新算法在返回下次迭代用的id基础上增加一个常量值，例如10亿（加完后不能与原数据冲突，也可以将迭代id由整数转换成负数以区分新老算法）。因此，如果是第一次访问，直接使用新算法；如果不是第一次访问，需要根据下次迭代用的id具体规则来判断是否切换新老算法。

æ»ç»ä ̧åç»è§å

ä1/2¿ç ̈Explanæ§è¡è®¡ååå ̈æ æ³æåé¢ç¥ç ́¢å1/4éæ©çå±éæ§ãç¶èï1/4åªè¦çæMySQLåºå±ä»£»·æ ̈¡åç计ç®å ¬å1/4ï1/4æ们就è1/2é¢ç¥ç ́¢å1/4çèμ°ååãåå©ä»£ä»·æ ̈¡åï1/4æ们ä ̧ä» å ̄以åæç ́¢å1/4å²çªçåå ï1/4è¿å ̄以å ̈åçå²çªä¹åè¿è¡é¢è¦ãçè³å ̈æ·» å ç ́¢å1/4ä¹åï1/4æ们ä¹å ̄ä»¥æ ¹æ®ä»£ä»·æ ̈¡åå ¬å1/4æ¥æææåæ1/2å ̈é®é¢ãæ¤å¤ï1/4æ ¹æ®æ°æ®ä ̧å¡å ̄åº �ï1/4æ们è¿å ̄以é¢ä1/4°å1/2åç ́¢å1/4çåçæ§ï1/4以åæ ̄å¦å ̄è1/2åºç°å ̈è¡ ̈æ«æçæ åμãå æ¤ï1/4æ·±å ¥ç 究MySQL代价æ ̈¡åå ̄¹äºä1/4åç ́¢å1/4管çå ·æå ³é®æä¹ã

æªæ¥æä"¬çç³"ûç»åºç ̈å°ç"åMySQLä"£ä»·æ ̈¡åè¿è¡éæï1/4å®ç°èªå ̈åææ°æ®åºåè¡ ̈çä¿¡æ ̄ï1/4ä"¥åç°å1/2 Åç ́¢å1/4åå ̈çé®é¢ï1/4ä3/4å¦ç ́¢å1/4å²çªææªä1/2¿ç ̈ç ́¢å1/4å ̄1/4è ́çå ̈è¡ ̈æ«æ¤å¤ï1/4è ̄¥å·¥å · è¿å ̄以éå ̄¹å°æªæ·» Å ç ́¢å1/4çè¡ ̈ï1/4æ ¹æ®æ°æ®æ åμæä3/4åéçç ́¢å1/4æ ̈èãåæ¶ï1/4è ̄¥å·¥å ·è¿è1/2å¤é¢æμå1/2æ°æ®è3/43/4å°æç§å ̄度æ¶ï1/4å ̄è1/2åºç°å ̈è¡ ̈æ«æçé®é¢ï1/4ä"èå ̧®å©æååå¥1/2ä1/4å¤ã

ä ̧ºäºå®ç°è¿äºåè1/2ï1/4æ们å°é¦å å ̄¹MySQL代价æ ̈¡åè¿è¡æ·±å ¥ç 究ï1/4å ̈é¢äºè§£å ¶è®¡ç®å ¬å1/4åååçãè¿å°æå©äºæ们ç1/4åç ̧åºçç®æ³ï1/4èªå ̈åææ°æ®åºåè¡ ̈çä¿¡æ ̄ï1/4æ3/4åºæ1/2å ̈çç ́¢å1/4é®é¢ãæ¤å¤ï1/4æ们è¿å ³æ³ ̈æç ̈æ§åå®ç ̈æ§ï1/4ç¡®ä¿ç ̈æ·è1/2å¤è1/2»æ3/4å°è3/4å¥ç ̧å ³æ°æ®åºåè¡ ̈çä¿¡æ ̄ï1/4并è·åå³ä1/4å建议ã

è ̄¥å·¥å ·çå1/4åå°æå©äºæé«æ«æ®åºæ§è1/2ï1/4åå°å ̈è¡ ̈æ«æçåçï1/4éä1/2ç³»ç»èμæºæ¶èãåæ¶ï1/4å®è¿å ̄以ä ̧ºæ°æ®åºç®¡çåå1/4å人åää3/4ä3/4¿å©ï1/4ä1/2¿ä»ä»¬è1/2å¤æ ́å ä ̧æ³ ̈äºå ¶ä»æ ̧å¿ä ̧å¡ãéè¿ç»åMySQL代价æ ̈¡åï1/4æ们ç ̧ä¿¡è¿ä ̧ªå·¥å ·å°å ̈ä1/4åç ́¢å1/4管çæ¹é¢åæ¥éè¦ä1/2ç ̈ï1/4ä ̧ºä1/4ä ̧å ̧¦æ¥æ ́é«çæçã

参考资料

https://github.com/mysql/mysql-server

ä1/2è ï1/4京ä ̧é¶å® çå¤å

来源：京东云开发者社区

model
Previous: Subsequent! The girl was scolded by the delivery man for her fat head and big ears, and the insider revealed more information, and the restaurant was implicated
Next: One move MAX reduced by 10 times, and now it's mine| JD Retail Technology Team

Read on