MySQL索引长度限制

作者: yusher | 来源:发表于2020-09-27 11:56 被阅读0次

    问题引入

    想必不少开发者在工作过程中会听到同事或者在网上看到以下内容:

    • MySQL索引长度不能超过191,需限制为index idx_a (a(191))
    • utf8与utf8mb4有什么区别
    • utf8mb4_general_ci与utf8mb4_unicode_ci有什么区别

    解答疑问

    • utf8utf8mb4的区别

      由于历史原因,MySQL刚开始设计的时候,"天真的"认为使用3个字节就足够存储字符串了,因此将UTF-8进行阉割;然而遇到复杂的汉字或者emoji表情等4字节的宽字符的时候,存储就会出现异常,因此在版本5.7.3开始引入utf8mb4,其表示为most bytes 4,即最多占用4个字节。

    • uft8mb4_general_ciutf8mb4_unicode_ci的区别

      utf8mb4_unicode_ci是基于官方的Unicode规则进行排序和压缩,其算法相对负责,对于大部分的语言和字符集排序有着很高的准确率;而uft8mb4_general_ci可以理解为一种为了提升速度的简化版Unicode规则,但由于它不完全遵循Unicode规则,在使用某种特定语言或者字符集时,会出现非预期的结果。

      如:

      1. 字符βuft8mb4_general_ci校对规则下,被视为s,而在utf8mb4_unicode_ci下为ss,因此遇到该情况会出现排序不准确

      2. 一些Unicode字符被定义成可忽略的,即意味着在对含有这些字符的字符集排序的时候,该略过它们而继续向前移动进行操作

      总结:

      1. utf8mb4_unicode_ci的算法更加复杂,其准确率高但是效率低,而uft8mb4_general_ci则相反
      2. 字符集若不会存在中文等亚洲语言或emoji符号等情况下,使用uft8mb4_general_ci,否则使用utf8mb4_unicode_ci
      3. 若不在乎数据库那点儿性能问题,建议选择utf8mb4_unicode_ci
    • 索引长度限制

      UTF-8编码的字符可以是1-4个字节,但是在MySQL中最大只能存储3个字节。

      在版本5.5开始引入innodb_large_prefix,其默认值为off,索引的前缀最大限制为767个字节;若值为on时(版本5.7.7开始作为默认值),最大限制为3072个字节。

      总结:

      innodb_large_prefix=on innodb_large_prefix=off
      utf8_xxx 3072/3=1024 767/3=255
      utf8mb4_xxx 3072/4=768 767/4=191

      补充

      在后期版本innodb_large_prefix将会被逐渐废弃并移除。从版本8.0开始,索引长度限制由表字段(row format)决定,若为DYNAMICCOMPRESSED时,限制值为3072;为REDUNDANTCOMPACT时,限制值为767。且row_format=dynamic时,长度3072是基于innodb_page_size=16KB,随着innodb_page_size的值按比例增减,其索引前缀长度也响应减小,如若为8KB时,长度为1536,因此在限制索引长度时,需根据使用的MySQL版本以及相应的参数进行配置决定。

    相关文章

      网友评论

        本文标题:MySQL索引长度限制

        本文链接:https://www.haomeiwen.com/subject/mjjduktx.html