mysql中包含长字段索引的优化
If  is enabled (the default), the index key prefix limit is 3072 bytes for InnoDB tables that use  or row format. If  is disabled, the index key prefix limit is 767 bytes for tables of any row format.
is deprecated and will be removed in a future release.  was introduced in MySQL 5.5 to disable large index key prefixes for compatibility with earlier versions of InnoDB that do not support large index key prefixes.
The index key prefix length limit is 767 bytes for InnoDB tables that use the  or  row format. For example, you might hit this limit with a  index of more than 255 characters on a TEXT or VARCHAR column, assuming a utf8mb3character set and the maximum of 3 bytes for each character.
Attempting to use an index key prefix length that exceeds the limit returns an error. To avoid such errors in replication configurations, avoid enabling  on the master if it cannot also be enabled on slaves.
The limits that apply to index key prefixes also apply to full-column index keys.
所以在GBK编码中，最⼤可以到1536个字符，utf8/utf8mb3，1024个字符，utf8mb4为768个字符。这⼀
限制为天然限制（mariadb 因为采⽤Innodb引擎，该限制同样存在），⽆法绕开。
drop table big_index_test;
create table big_index_test(id int,content varchar(1000)) ROW_FORMAT=DYNAMIC CHARACTER set = utf8mb4 ;
create index idx_big_index on big_index_test(content);
[SQL]create index idx_big_index on big_index_test(content);
[Err] 1071 - Specified key was too long; max key length is 3072 bytes
相对于oracle来说，如果定义存在这种情况，即使记录没有或很少，仍然⽆法利⽤索引覆盖查询实现⼀些宽表的优化。
因此，对于mysql⽽⾔，对于⼀些定义很长的detail，最好的⽅式是垂直拆表，也就是将id和detail字段存储在单独的表中，然后业务代码中⼆次select的⽅式优化。如果detail字段是json类型的话，可以直接存储为json类型或存储在NoSQL中如mongodb。
注：当年5.6的时候，帮⼀个产品优化过mysql，mysql在⼤字段的处理上性能⾮常低下。
mongodb和mysql结合

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。