mysql索引字段设计

我会带着你远行 2024-03-29 15:44 74阅读 0赞

表字段数量与row大小限制可以参考官方文档  
[https://dev.mysql.com/doc/refman/8.0/en/column-count-limit.html\#column-count-limits][https_dev.mysql.com_doc_refman_8.0_en_column-count-limit.html_column-count-limits]

### 复合索引 ###

参考：[https://dev.mysql.com/doc/refman/8.0/en/innodb-limits.html][https_dev.mysql.com_doc_refman_8.0_en_innodb-limits.html]

#### 数量限制 ####

一个复合索引最多可以包含16个列：[https://dev.mysql.com/doc/refman/8.0/en/multiple-column-indexes.html][https_dev.mysql.com_doc_refman_8.0_en_multiple-column-indexes.html]  
官方对组合索引给出了一个比较好的可行方案：可以引入基于来自其他列的信息进行“散列”的列，方法如下：

SELECT * FROM tbl_name
      WHERE hash_col=MD5(CONCAT(val1,val2))
      AND col1=val1 AND col2=val2;

#### 大小限制 ####

innodb索引大小限制

*  对于使用DYNAMIC（5.7.9开始默认为该格式）或COMPRESSED行格式的InnoDB表，索引key前缀长度限制为3072字节。
 *  对于使用REDUNDANT或COMPACT行格式的InnoDB表，索引key前缀长度限制为767字节。例如，在TEXT或VARCHAR类型字段上列前缀索引超过191字符，可能会达到这个限制，假设一个utf8mb4字符集，每个字符最多4个字节。
 *  试图使用超过限制的索引键前缀长度将返回错误。
 *  如果你在创建mysql实例时通过innodb\_page\_size参数配置，减小InnoDB的page页大小至8KB或4KB，索引的最大长度也会基于16KB页大小3072字节的限制，按比例降低。也就是说，当页大小是8KB时最大索引键长度是1536字节，页大小是4KB时最大长度为768字节。该限制适用在索引键前缀，也同样适用在full-column索引键

**为什么是3072？**  
16KB page size

1.  BTree结构叶子结点至少两个Node（不然就退化成列表了），16/2=8KB
2.  二级索引一定包含一个主键索引key，8/2=4KB

预留空间后<4KB，取整=3\*1024=3072

#### row format ####

mysql> SHOW TABLE STATUS FROM test LIKE 't%' \G;
    *************************** 1. row ***************************
               Name: t1
             Engine: InnoDB
            Version: 10
         Row_format: Dynamic
               Rows: 0
     Avg_row_length: 0
        Data_length: 16384
    Max_data_length: 0
       Index_length: 0
          Data_free: 0
     Auto_increment: NULL
        Create_time: 2021-02-18 12:18:28
        Update_time: NULL
         Check_time: NULL
          Collation: utf8mb4_0900_ai_ci
           Checksum: NULL
     Create_options: 
            Comment:

### 计算索引长度 ###

1.  非NOT NULL字段：占用1个字节。
2.  定长字段：tinyint占1个字节、int占4个字节、bitint占8个字节、date占3个字节、datetime占5个字节，char(n)占n个字符。
3.  变长字段：varchar(n)占n个字符+2个字节。

其他类型大家可以通过本地创建测试表测试索引验证字段类型占用字节数  
常见字符集一个字符占用字节数

1.  latin1编码：每个字符占用一个字节
2.  gbk/gb2312编码：每个字符占用两个字节
3.  utf8编码：每个字符占用三个字节
4.  utf8mb4编码：每个字符占用四个字节

#### 案例 ####

CREATE TABLE `test` (
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
      `msg_id` varchar(64) NOT NULL COMMENT 'msg unique id',
      `send_to_id` varchar(128) DEFAULT '' COMMENT 'to user',
      `error_code` varchar(64) DEFAULT '' COMMENT 'errorCode',
      `db_create_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT 'Create time',
      PRIMARY KEY (`id`),
      KEY `idx_send_to_id` (`send_to_id`,`error_code`,`msg_id`,`db_create_time`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

查看执行计划

desc select msg_id,error_code 
    from test 
    where send_to_id='1' 
    and error_code in ('a') 
    and msg_id='1' 
    and db_create_time='2022-12-12'

![截屏2022-12-15 14.48.37.png][2022-12-15 14.48.37.png]

1.  varchar3个字段共占用（64+64+128）*4=1024+（2*3）=1030
2.  datetime(3)占用5+2=7
3.  两个非NOT NULL字段1+1=2

索引占用1030+7+2=1039字节

### 索引设计 ###

#### 群发消息场景 ####

1.  一个批次消息推送对应600W+用户的消息触达
2.  异步推送时消息失败需要重试，重试需要幂等处理
3.  产品需要查看一个批次消息的推送的详情，因此分库规则使用了msgId（群发消息的批次概念）

幂等规则：msg\_id+send\_to

伪代码：select msg\_id,error\_code from table where msg\_id=‘a’ and send\_to=‘1’。  
为什么需要error\_code，因为有些类型的失败消息不允许重试，比如：用户已注销账户或者用户已退订等类型

群发消息场景下，600W+条消息都是同一个批次，也就是msg\_id相同，send\_to均不相同，error\_code（大部分消息发送成功，errorCode为空字符）

#### 索引覆盖 ####

不需要二次回表，直接索引覆盖索引，字段顺序选择如下：

1.  idx\_send\_to(msg\_id,send\_to,error\_code)
2.  idx\_send\_to(error\_code,send\_to,msg\_id)
3.  idx\_send\_to(send\_to,error\_code,msg\_id)
4.  idx\_send\_to(send\_to,msg\_id,error\_code)

假设：表中每个字段的distinct值，send\_to=600W，error\_code=20，msg\_id=50W。表总数据量为：600W*20*50W=600000W

选择性  
选择性=不重复的索引值数量/表记录总数量  
msgId选择性：50W/600000W=0.000083  
send\_to选择性：600W/600000W=0.001  
error\_code选择性：20/600000W=0.0000000033  
理想情况下索引性能由高到低依次为：索引4-》索引3-〉索引2-》索引1  
但是如果你想根据error\_code统计数量（索引遍历），那么索引3就是最佳选择，例如下面的案例，mysql自动选择走索引3，而不是索引4  
![截屏2022-12-15 23.00.42.png][2022-12-15 23.00.42.png]

![截屏2022-12-15 23.02.21.png][2022-12-15 23.02.21.png]

![截屏2022-12-15 23.03.30.png][2022-12-15 23.03.30.png]

### 总结 ###

基于索引选择性创建联合索引，字段设计可以根据实际业务场景来评估  
例如：字段1（基数=10W），字段2（基数=10W），字段3（基数=20）  
虽然字段1，2独立的选择性更高，但是如果字段1与字段2基本1:N(远大于5)，而字段1与字段3基本1:5。那么字段1+字段3无疑是更佳的选择

组合索引涉及范围查询时，将范围查询的字段放在最后，例如：

1.  语句：error\_code in (‘404’,‘500’) and send\_to\_id = 1
2.  如果组合索引：(error\_code,send\_to\_id)，那么该索引只会走到error\_code字段索引，就等同于索引（error\_code）
3.  因此索引应该为：(send\_to\_id,error\_code)

分区表踩坑  
背景：mysql表按照创建时间进行范围分区，一年一个分区。监控发现生产近期慢查较多，定位到sql后，排查发现order by id desc，导致sql走了主键索引性能很差，最佳应该是用专门为其创建的组合索引，但是由于where条件缺少一个字段导致优化器没有走到组合索引  
解决方法：

1.  增加强制索引force index（不推荐）
2.  修改order by id asc，性能很好，但是不符合业务需求，并且order by不包含主键会出现漏数据问题
3.  修改order by id,create\_time desc（推荐）

[https_dev.mysql.com_doc_refman_8.0_en_column-count-limit.html_column-count-limits]: https://dev.mysql.com/doc/refman/8.0/en/column-count-limit.html#column-count-limits
[https_dev.mysql.com_doc_refman_8.0_en_innodb-limits.html]: https://dev.mysql.com/doc/refman/8.0/en/innodb-limits.html
[https_dev.mysql.com_doc_refman_8.0_en_multiple-column-indexes.html]: https://dev.mysql.com/doc/refman/8.0/en/multiple-column-indexes.html
[2022-12-15 14.48.37.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/28/83bfd50c95fe43ccac7c680b3202a1b4.png
[2022-12-15 23.00.42.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/28/f9367f35a71d4c16b813ae8e9fcd9ffa.png
[2022-12-15 23.02.21.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/28/bac916ae4f34459681249244151550ac.png
[2022-12-15 23.03.30.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/28/705d2e85e0d641aab740bd71e6396d5f.png