淺析MySQL代價模型:告別盲目使用EXPLAIN,提前預知索引優化策略

来源:https://www.cnblogs.com/jingdongkeji/archive/2023/12/07/17881781.html
-Advertisement-
Play Games

熟悉代價模型之後,我們可以預先瞭解 MySQL 在執行查詢時會如何選擇索引,從而更有效地進行索引優化。在接下來的文章中,我將結合近期進行索引優化的具體案例,來詳細解釋如何運用代價模型來優化索引。 ...


背景

在 MySQL 中,當我們為表創建了一個或多個索引後,通常需要在索引定義完成後,根據具體的數據情況執行 EXPLAIN 命令,才能觀察到資料庫實際使用哪個索引、是否使用索引。這使得我們在添加新索引之前,無法提前預知資料庫是否能使用期望的索引。更為糟糕的是,有時甚至在添加新的索引後,資料庫在某些查詢中會使用它,而在其他查詢中則不會使用,這種情況下,我們無法確定索引是否發揮了預期的作用,讓人感到非常苦惱。這種情況基本上意味著 MySQL 並沒有為我們選擇最優的索引,而我們不得不在茫茫數據中摸索,試圖找到問題的癥結所在。我們可能會嘗試調整索引,甚至刪除索引,然後重新添加,希望 MySQL 能從中找到最優的索引選擇。然而,這樣的過程既耗時又費力,而且往往收效甚微。

如果在添加索引之前,我們能夠預知索引的使用情況,那麼對於表設計將大有裨益。我們可以在設計表結構時,更加明確地知道應該選擇哪些索引,如何優化索引,以提高查詢效率。我們不再需要依賴盲目嘗試和猜測,而是可以基於實際的數據和查詢情況,做出更加明智的決策。因此,對於 MySQL 用戶來說,能夠預知索引走勢的需求非常迫切。我們希望能有一種方法,能夠讓我們在添加索引之前,就清楚地瞭解 MySQL 將如何使用索引,以便我們能夠更好地優化表結構,提高查詢效率。這將極大地減輕我們的工作負擔,提高我們的工作效率,讓我們能夠更加專註於業務邏輯的處理,而不是在索引的海洋中掙扎。

為瞭解決這個問題,我們可以深入研究 MySQL 的索引選擇機制。實際上,這個機制的核心就是代價模型,它通過一個公式來決定索引的選擇策略。相對於 MySQL 其他複雜的概念,代價模型實現起來要簡單得多。熟悉代價模型之後,我們可以預先瞭解 MySQL 在執行查詢時會如何選擇索引,從而更有效地進行索引優化。在接下來的文章中,我將結合近期進行索引優化的具體案例,來詳細解釋如何運用代價模型來優化索引。

MySQL代價模型淺析

MySQL資料庫主要由4層組成:

  1. 連接層:客戶端和連接服務,主要完成一些類似於連接處理、授權管理、以及相關的安全方案。

  2. 服務層:主要完成大多數的核心服務功能,如SQL介面,並完成緩存的查詢,SQL的分析和優化以及內部函數的執行。

  3. 引擎層:負責MySQL中數據的存儲和提取,伺服器通過AP1與存儲引擎進行通信。

  4. 存儲層:將數據存儲文件系統上,並完成與存儲引擎的交互。

索引策略選擇在SQL優化器進行的

SQL 優化器會分析所有可能的執行計劃,選擇成本最低的執行,這種優化器稱之為:CBO(Cost-based Optimizer,基於成本的優化器)。

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

其中,CPU Cost 表示計算的開銷,比如索引鍵值的比較、記錄值的比較、結果集的排序 ...... 這些操作都在 Server 層完成;

IO Cost 表示引擎層 IO 的開銷,MySQL 可以通過區分一張表的數據是否在記憶體中,分別計算讀取記憶體 IO 開銷以及讀取磁碟 IO 的開銷。

源碼簡讀

MySQL的數據源代碼採用了5.7.22版本,後續的代價計算公式將基於此版本進行參考。

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訪問類型索引費用計算】

        
        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訪問類型索引費用計算】

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;
}

驗證公式

創建驗證需要的表

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

驗證結果:

explain format = json
select * from store_goods_center;

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

總結公式:

全表掃描代價 = 數據總大小 / 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

驗證結果:

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

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

總結公式:

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

驗證結果:

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

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

總結公式:

下麵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



驗證結果:

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

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

總結公式:

range掃描代價 = 預估範圍行數 * 1.4 + 0.01 + 範圍數

公式簡化(去除影響較小的複雜計算) 
range掃描代價 = 預估範圍行數 * 1.4

索引衝突案例

門店商品系統中主要存儲門店與商品的關聯信息,併為B端提供根據門店ID查詢關聯商品的功能。由於門店關聯的商品數據量較大,需要分頁查詢關聯商品數據。為避免深分頁問題,我們選擇基於上次最新主鍵進行查詢(核心思想:通過主鍵索引,每次定位到ID所在位置,然後往後遍歷N個數據。這樣,無論數據量多少,查詢性能都能保持穩定。我們將所有數據根據主鍵ID進行排序,然後分批次取出,將當前批次的最大ID作為下次查詢的篩選條件)。

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)】。添加該索引後,效果立竿見影。

然而,我們發現仍然有慢查詢產生,並且這些慢查詢仍然使用uniq_storegoods索引,而不是idx_station_no_and_id索引。我們開始思考,為什麼MySQL沒有為我們的系統推薦使用最優的索引?是MySQL索引推薦有問題,還是我們創建索引有問題?如何做才能讓MySQL幫我們推薦我們認為最優的索引?

當然,我們也可以使用FORCE INDEX強行讓MySQL走我們提前預設的索引,但是這種方式局限太大,後期索引維護成本變得很高,甚至可能使用該SQL的其他業務性能變低。為了突破整體優化的卡點狀態,我們需要瞭解一下MySQL索引推薦底層邏輯,即MySQL代價模型。瞭解相應規則後,現階段的問題將迎刃而解。

案例分析及優化

在回顧剛纔的問題時,我們發現問題源於原始索引產生了【Using filesort】,從而導致了慢查詢的出現。為瞭解決這個問題,我們新增了一個索引,即【KEY idx_station_no_and_id (station_no, id)】,以替代原有的索引【UNIQUE KEY uniq_storegoods (station_no, sku_id)】。然而,儘管新增索引後大部分慢查詢得到瞭解決,但仍有部分慢查詢未能消除。進一步分析發現,這些慢查詢是由於SQL沒有使用我們期望的索引,而是使用了老索引,從而引發了【Using filesort】問題。在通過explain進行分析後,我們暫時還沒有找到合適的解決方案。

問題:儘管我們新增了索引,並且大部分SQL已經能夠使用新索引進行優化,但仍存在一些SQL沒有使用新索引。

// 通過代價模型進行分析

// 使用上面的測試數據進行分析
// 新增索引後都沒有走新索引
// 老索引,掃描行數: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的代價模型,我們發現MySQL在選擇使用哪個索引時,主要取決於掃描出的數據條數。具體來說,掃描出的數據條數越少,MySQL就越傾向於選擇該索引(由於MySQL的索引數據訪問類型各異,計算公式也會有所不同。因此,在多個索引的掃描行數相近的情況下,所選索引可能與我們期望的索引有所不同)。順著這個思路排查,我們發現當id > -1時,無論是使用storeId + skuId還是storeId + id索引進行查詢,掃描出的數據條數是相同的。這是因為這兩種查詢方式都是根據門店查詢商品數據,且id值肯定大於1。因此,對於MySQL來說,由於這兩種索引掃描出的數據條數相同,所以使用哪種索引效果相差不多。這就是為什麼一部分查詢走新索引,而另一部分查詢走老索引的原因。然而,當查詢條件為id > n時,storeId + id索引的優勢便得以顯現。因為它能夠直接從索引中掃描並跳過id <= n的數據,而storeId + skuId索引卻無法直接跳過這部分數據,因此真正掃描的數據條數storeId + skuId要大於storeId + id。因此,在查詢條件為id > n時,MySQL更傾向於使用新索引。(需要註意的是,示例給出的數據索引數據訪問類型不同,一個是range索引類型,一個是ref索引類型。由於演算法不同,即使某個索引的檢索數據率略高於另一個索引,也可能導致系統將其推薦為最優索引

問題已經分析清楚,主要原因是存在多個索引,且根據索引代價計算公式的代價相近,導致難以抉擇。因此,解決這個問題的方法不應該是同時定義兩個會讓MySQL"糾結"的索引選擇。相反,應該將兩個索引融合為一個索引。具體的解決方案是根據門店查詢,將原來的主鍵id作為上次查詢的最大id替換為skuId。在演算法切換完成後,刪除新的門店+主鍵id索引。然而,這種方式可能會引發另一個問題。由於底層排序演算法發生了變化(由原來的主鍵id改為skuId),可能導致無法直接從底層服務切換。此時,應考慮從下游使用此介面服務的應用進行切換。需要註意的是,如果下游系統是單機分頁迭代查詢門店數據,那麼下游系統可以直接進行切換。但如果這種分頁查詢動作同時交給多台應用伺服器執行,切換過程將變得相當複雜,他們的切換成本與底層切換成本相同。但是,這個系統的對外服務屬於這種情況,下游調用系統會有多台應用伺服器協作分頁迭代查詢數據,為這次優化帶來很大影響。

最終,讓底層獨立完成切換方式最為合適。在切換過程中,關鍵在於正確區分新老演算法。老演算法在迭代過程中不應切換至新演算法。原系統對外服務提供的下次迭代用的id可用來進行區分。新演算法在返回下次迭代用的id基礎上增加一個常量值,例如10億(加完後不能與原數據衝突,也可以將迭代id由整數轉換成負數以區分新老演算法)。因此,如果是第一次訪問,直接使用新演算法;如果不是第一次訪問,需要根據下次迭代用的id具體規則來判斷是否切換新老演算法。

總結與後續規劃

使用Explan執行計劃存在無法提前預知索引選擇的局限性。然而,只要熟悉MySQL底層代價模型的計算公式,我們就能預知索引的走向。藉助代價模型,我們不僅可以分析索引衝突的原因,還可以在發生衝突之前進行預警。甚至在添加索引之前,我們也可以根據代價模型公式來排查潛在問題。此外,根據數據業務密度,我們還可以預估當前索引的合理性,以及是否可能出現全表掃描等情況。因此,深入研究MySQL代價模型對於優化索引管理具有關鍵意義。

未來我們的系統應用將結合MySQL代價模型進行集成,實現自動分析資料庫和表的信息,以發現當前索引存在的問題,例如索引衝突或未使用索引導致的全表掃描。此外,該工具還可以針對尚未添加索引的表,根據數據情況提供合適的索引推薦。同時,該工具還能夠預測當數據達到某種密度時,可能出現全表掃描的問題,從而幫助提前做好優化準備。

為了實現這些功能,我們將首先對MySQL代價模型進行深入研究,全面瞭解其計算公式和原理。這將有助於我們編寫相應的演算法,自動分析資料庫和表的信息,找出潛在的索引問題。此外,我們還關註易用性和實用性,確保用戶能夠輕鬆地輸入相關資料庫和表的信息,並獲取有關優化建議。

該工具的開發將有助於提高資料庫性能,減少全表掃描的發生,降低系統資源消耗。同時,它還可以為資料庫管理員和開發人員提供便利,使他們能夠更加專註於其他核心業務。通過結合MySQL代價模型,我們相信這個工具將在優化索引管理方面發揮重要作用,為企業帶來更高的效益。

參考資料

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

作者:京東零售 王多友

來源:京東雲開發者社區 轉載請註明來源


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • tmux教程 功能 分屏。 允許斷開Terminal連接後,繼續運行進程。 結構 // 一個tmux可以包含多個session,一個session可以包含多個window,一個window可以包含多個pane。 tmux: session 0: window 0: pane 0 pane 1 pan ...
  • 近幾天發現MarkdownPad有一些小問題,打開時會彈出以下報錯信息,告訴你打開文件的許可權不夠 解決方法如下: 1、複製報錯信息中的文件路徑'C:\Users\Administrator\AppData \Roaming\wyUpdate AU\ApricitySoftware-MarkdownP ...
  • 版本 Linux 6.5 背景 在學習cgroupv2的時候,想給子cgroup開啟cpu控制器結果失敗了: # 查看可以開啟哪些控制器 root@ubuntu-vm:/sys/fs/cgroup# cat cgroup.controllers cpuset cpu io memory hugetl ...
  • 運算符 1.算術運算符 算術運算符主要用於數學運算,其可以連接運算符前後的兩個數值或表達式,對數值或表達式進行 +,-,*,/,%運算。 1.1 加法和減法運算符 mysql> SELECT 100,100 + 0,100 - 0,100 + 50,100 + 50 - 30,100 + 35.5, ...
  • SQL CREATE INDEX 語句 SQL CREATE INDEX 語句用於在表中創建索引。 索引用於比其他方式更快地從資料庫中檢索數據。用戶無法看到索引,它們只是用於加速搜索/查詢。 註意: 使用索引更新表比不使用索引更新表需要更多的時間(因為索引也需要更新)。因此,只在經常進行搜索的列上創 ...
  • 備份類型 常見的備份有冷備份、溫備份、熱備份,還有什麼物理備份、邏輯備份、增量備份、差異備份等等。 冷備份: 需要服務停止,在備份期間不能進行讀和寫操作。 溫備份: 讀操作可執行;但寫操作不可執行 熱備份: 讀和寫都可以正常進行,不影響數據備份 邏輯備份: 導出資料庫中的數據和對象定義為標準 SQL ...
  • CS-Base —— 圖解電腦網路、操作系統、電腦組成、資料庫,共 1000 張圖 + 50 萬字,破除晦澀難懂的電腦基礎知識,讓天下沒有難懂的八股文! ...
  • 隨著人們對健康和美食的追求,越來越多的人開始自己在家烹飪,而獲取家常菜譜是一個必不可少的環節。然而,我們並不總是能輕鬆找到適合自己口味的菜譜。而今日我們要介紹的數據源API介面,就是為瞭解決這個問題而誕生的。 這個數據源API介面提供了各種不同場合、季節、年齡段、菜系等的菜譜,不僅配料食材詳盡,操作 ...
一周排行
    -Advertisement-
    Play Games
  • 背景 在瀏覽器中訪問本地靜態資源html網頁時,可能會遇到跨域問題如圖。 是因為瀏覽器預設啟用了同源策略,即只允許載入與當前網頁具有相同源(協議、功能變數名稱和埠)的內容。 WebView2預設情況下啟用了瀏覽器的同源策略,即只允許載入與主機相同源的內容。所以如果我們把靜態資源發佈到iis或者通過node ...
  • 最近看幾個老項目的SQL條件中使用了1=1,想想自己也曾經這樣寫過,略有感觸,特別拿出來說道說道。編寫SQL語句就像炒菜,每一種調料的使用都會影響菜品的最終味道,每一個SQL條件的加入也會影響查詢的執行效率。那麼 1=1 存在什麼樣的問題呢?為什麼又會使用呢? ...
  • 好久不見,我又回來了。 給大家分享一個我最近使用c#代碼操作ftp伺服器的代碼示例: 1 public abstract class FtpOperation 2 { 3 /// <summary> 4 /// FTP伺服器地址 5 /// </summary> 6 private string f ...
  • 一:背景 1. 講故事 過年喝了不少酒,腦子不靈光了,停了將近一個月沒寫博客,今天就當新年開工寫一篇吧。 去年年初有位朋友找到我,說他們的系統會偶發性崩潰,在網上也發了不少帖子求助,沒找到自己滿意的答案,讓我看看有沒有什麼線索,看樣子這是一個牛皮蘚的問題,既然對方有了dump,那就分析起來吧。 二: ...
  • 自己製作的一個基於Entity Framework Core 的資料庫操作攔截器,可以列印資料庫執行sql,方便開發調試,代碼如下: /// <summary> /// EF Core 的資料庫操作攔截器,用於在資料庫操作過程中進行日誌記錄和監視。 /// </summary> /// <remar ...
  • 本文分享自華為雲社區《Go併發範式 流水線和優雅退出 Pipeline 與 Cancellation》,作者:張儉。 介紹 Go 的併發原語可以輕鬆構建流數據管道,從而高效利用 I/O 和多個 CPU。 本文展示了此類pipelines的示例,強調了操作失敗時出現的細微之處,並介紹了乾凈地處理失敗的 ...
  • 在上篇文章中,我們介紹到在多線程環境下,如果編程不當,可能會出現程式運行結果混亂的問題。出現這個原因主要是,JMM 中主記憶體和線程工作記憶體的數據不一致,以及多個線程執行時無序,共同導致的結果。 ...
  • 1、下載安裝包首先、進入官網下載安裝包網址:https://www.python.org/downloads/windows/下載步驟:進入下載地址,根據自己的電腦系統選擇相應的python版本 選擇適配64位操作系統的版本(查看自己的電腦操作系統版本), 點擊下載安裝包 也可以下載我百度雲分享的安 ...
  • 簡介 git-commit-id-maven-plugin 是一個maven 插件,用來在打包的時候將git-commit 信息打進jar中。 這樣做的好處是可以將發佈的某版本和對應的代碼關聯起來,方便查閱和線上項目的維護。至於它的作用,用官方說法,這個功能對於大型分散式項目來說是無價的。 功能 你 ...
  • 序言 在數字時代,圖像生成技術正日益成為人工智慧領域的熱點。 本討論將重點聚焦於兩個備受矚目的模型:DALL-E和其他主流AI繪圖方法。 我們將探討它們的優勢、局限性以及未來的發展方向。通過比較分析,我們期望能夠更全面地瞭解這些技術,為未來的研究和應用提供啟示。 Q: 介紹一下 dall-e Ope ...