MySQL唯一索引大量NULL值导致从库复制延迟

 

一.背景描述

介绍完背景知识，我们来看下具体问题，问题来源于以前公司真实线上案例，下面的表结结构和数据是为了说明方便特殊构造的。

表结构如下：

CREATE TABLE `test_uk` (

`id` bigint(20) NOT NULL,

`name` varchar(30) NOT NULL DEFAULT 'ins',

`uuid` varchar(30) DEFAULT NULL,

UNIQUE KEY `idx_uuid` (`uuid`),

KEY `idx_name` (`name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

可以看到 UK 列是允许 NULL的，我们看下数据分布情况：

mysql> select count(*) from test_uk where uuid is NULL;

+----------+

| count(*) |

+----------+

| 21000000 |

+----------+

mysql> select count(ins_uuid) from test_uk where uuid is not NULL;

+-----------------+

| count(ins_uuid) |

+-----------------+

| 20600000 |

+-----------------+

我们用脚本在主库上，每隔 1s 执行下面的 SQL：
delete from test_uk where name >= 'aaa' and name <= 'aac' and uuid is NULL limit 1;

发现从库延迟越来越大，这里奇怪的是删除1行数据怎么从库会延迟？

二.问题分析

2.1 查询计划分析

主库上执行计划如下:

root@5.7.21-log hhl_test 04:02:29>desc delete from test_uk where name >= 'aaa' and name <= 'aac' and uuid is NULL limit 1;

+----+-------------+---------+------------+-------+-------------------+----------+---------+-------+------+----------+-------------+

| id | select_type | table   | partitions | type  | possible_keys     | key      | key_len | ref   | rows | filtered | Extra       |

+----+-------------+---------+------------+-------+-------------------+----------+---------+-------+------+----------+-------------+

|  1 | DELETE      | test_uk | NULL       | range | idx_uuid,idx_name | idx_name | 92      | const | 91345    |   100.00 | Using where |

+----+-------------+---------+------------+-------+-------------------+----------+---------+-------+------+----------+-------------+

1 row in set (0.01 sec)

2.2 其他表现

1)主库的read_rows正常，但是从库的read_rows异常大。

推测是从库走了唯一索引来删除数据。

 

从上面的现象，我们可以大致推出，备库跑的更慢，是因为备库在同步更新时，用错了索引，用 UK 来更新。

为什么会选错索引呢，在 ROW 格式下，备库在同步更新时，索引的选择是基于简单规则的，没有走优化器的代价模型，从源代码中找到了答案，请看sql/log_events.cc 文件中的函数：search_key_in_table

/**

  Searches the table for a given key that can be used

  according to the existing values, ie, columns set

  in the bitmap.

  The caller can specify which type of key to find by

  setting the following flags in the key_type parameter:

    - PRI_KEY_FLAG

      Returns the primary key.

    - UNIQUE_KEY_FLAG

      Returns a unique key (flagged with HA_NOSAME)

    - MULTIPLE_KEY_FLAG

      Returns a key that is not unique (flagged with HA_NOSAME

      and without HA_NULL_PART_KEY) nor PK.

  The above flags can be used together, in which case, the

  search is conducted in the above listed order. Eg, the

  following flag:

    (PRI_KEY_FLAG | UNIQUE_KEY_FLAG | MULTIPLE_KEY_FLAG)

  means that a primary key is returned if it is suitable. If

  not then the unique keys are searched. If no unique key is

  suitable, then the keys are searched. Finally, if no key

  is suitable, MAX_KEY is returned.

  @param table    reference to the table.

  @param bi_cols  a bitmap that filters out columns that should

                  not be considered while searching the key.

                  Columns that should be considered are set.

  @param key_type the type of key to search for.

  @return MAX_KEY if no key, according to the key_type specified

          is suitable. Returns the key otherwise.

*/

static

uint

search_key_in_table(TABLE *table, MY_BITMAP *bi_cols, uint key_type)

{

  DBUG_ENTER("search_key_in_table");

  KEY *keyinfo;

  uint res= MAX_KEY;

  uint key;

  if (key_type & PRI_KEY_FLAG &&

      (table->s->primary_key < MAX_KEY))

  {

    DBUG_PRINT("debug", ("Searching for PK"));

    keyinfo= table->s->key_info + table->s->primary_key;

    if (are_all_columns_signaled_for_key(keyinfo, bi_cols))

      DBUG_RETURN(table->s->primary_key);

  }

  DBUG_PRINT("debug", ("Unique keys count: %u", table->s->uniques));

  if (key_type & UNIQUE_KEY_FLAG && table->s->uniques)

  {

    DBUG_PRINT("debug", ("Searching for UK"));

    for (key=0,keyinfo= table->key_info ;

         (key < table->s->keys) && (res == MAX_KEY);

         key++,keyinfo++)

    {

      /*

        - Unique keys cannot be disabled, thence we skip the check.

        - Skip unique keys with nullable parts

        - Skip primary keys

      */

      if (!((keyinfo->flags & (HA_NOSAME | HA_NULL_PART_KEY)) == HA_NOSAME) ||

          (key == table->s->primary_key))

        continue;

      res= are_all_columns_signaled_for_key(keyinfo, bi_cols) ?

           key : MAX_KEY;

      if (res < MAX_KEY)

        DBUG_RETURN(res);

    }

    DBUG_PRINT("debug", ("UK has NULLABLE parts or not all columns signaled."));

  }

  if (key_type & MULTIPLE_KEY_FLAG && table->s->keys)

  {

    DBUG_PRINT("debug", ("Searching for K."));

    for (key=0,keyinfo= table->key_info ;

         (key < table->s->keys) && (res == MAX_KEY);

         key++,keyinfo++)

    {

      /*

        - Skip innactive keys

        - Skip unique keys without nullable parts

        - Skip indices that do not support ha_index_next() e.g. full-text

        - Skip primary keys

      */

      if (!(table->s->keys_in_use.is_set(key)) ||

          ((keyinfo->flags & (HA_NOSAME | HA_NULL_PART_KEY)) == HA_NOSAME) ||

          !(table->file->index_flags(key, 0, true) & HA_READ_NEXT) ||

          (key == table->s->primary_key))

        continue;

      res= are_all_columns_signaled_for_key(keyinfo, bi_cols) ?

           key : MAX_KEY;

      if (res < MAX_KEY)

        DBUG_RETURN(res);

    }

    DBUG_PRINT("debug", ("Not all columns signaled for K."));

  }

  DBUG_RETURN(res);

}

从上面可以看出，ROW格式复制的模式下，从库复制更新时，索引选择的规则是按照下面的列表顺序依次选择:

1.先主键(PK)

2.再UK(not null),再UK(null)

3.其他索引

4.Table Scan 

三.问题解决

对于上面的问题，思考后可以找到如下方案：

1.加法

在从库上加一个比当前UK索引更好的UK或者PK索引，确保此sql在从库执行选择新加的UK索引。－－此时要结合ROW_FORMAT=ROW, binlog_row_image=full/MINIMAL来决定加哪列索引。

 

2.减法

改造此sql，去掉UK列。但是前提要和业务开发确认好，去掉UK后此sql的逻辑是否会受影响。 －－此方案务必和业务开发确认完成后实施。