php - MySQL INDEX Varchar Field 500 for urls

Question

我正在构建一个多语言网络应用程序。

Url 存储在数据库中，如:

id_url          bigint(18)
id_map          int(11)
id_entity       bigint(18)
active          tinyint(1)
lang            varchar(8)
path            varchar(500)
path_ids        varchar(50) 
full_path_ids   varchar(50) 
created_at      datetime

我的问题是关于 path 索引。我只需要搜索 LIKE '$str%。我不需要 LIKE '%$str'。

索引是:idx_url_path BTREE No No path (255) 22 A No

它自动降级为 255。

对如何完成工作有任何想法吗？

Answer 1

发件人:http://dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.html

By default, an index key for a single-column index can be up to 767 bytes. The same length limit applies to any index key prefix. See Section 13.1.13, “CREATE INDEX Syntax”. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a UTF-8 character set and the maximum of 3 bytes for each character. When the innodb_large_prefix configuration option is enabled, this length limit is raised to 3072 bytes, for InnoDB tables that use the DYNAMIC and COMPRESSED row formats.

因此，假设您使用的是 InnoDB 引擎，您不能索引 500 个字符，除非您启用 innodb_large_prefix 配置选项，并且您的表使用 DYNAMIC 和 COMPRESSED 行格式:

innodb_large_prefix: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_large_prefix

动态行格式: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_dynamic_row_format

压缩行格式: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_compressed_row_format