MySQL索引和字段类型，字段长度

首先是两个表， user_info 和 user_identify_info ，这两个表通过 user_id 关联。

user_info 中 user_id 字段是int(11)类型，主键索引

user_identify_info 中 user_id 是varchar类型，普通索引

执行结果如图，未使用到索引

将 user_identify_info 字段改为int(11)或者bigint类型，使用到索引

最后查到

1、字段是varchar类型，参数是int类型，不走索引

2、字段是varchar类型，参数是字符串，走索引

3、字段是int类型，参数是int类型，走索引

4、字段是int类型，参数是字符串，走索引

添加列delivery_address 字段扩展到1000

为这个字段添加单列索引，报异常出来，索引最大长度是3072 bytes

mysql5.7不报错，但会自动将索引长度限制在768

mysql8单列索引最长也是768

复合索引总长度不能超过768

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

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

例:

总结:

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

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

总结:

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

添加语法： ALTER TABLE table_name ADD KEY(column_name(prefix_length))

在MySQL中，前缀长度最大值为255字节。对于存储引擎为MyISAM或InnoDB的数据表，前缀最长为1000字节。 必须注意的是，在MySQL中，对于TEXT和BLOB这种大数据类型的字段，必须给出前缀长度(length)才能成功创建索引。

如何确定前缀索引长度？

可以通过计算选择性来确定前缀索引的选择性，计算方法如下

全列选择性：

SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name

某一长度前缀的选择性

SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name

当前缀的选择性越接近全列选择性的时候，索引效果越好。

参考文章：

MySQL索引 *** 作命令详解

MySQL 前缀索引

---