如何在MariaDB 10中启用大型索引? [英] How to enable large index in MariaDB 10?
问题描述
在Debian Jessie中,我安装了MariaDB服务器10.0.30,并尝试增加最大密钥长度。 AFAIU取决于启用配置参数 innodb_large_prefix
。根据文档,它还需要 barracuda
文件格式和 innodb_file_per_table
。在配置中设置它们并重新启动服务器后,我在客户端看到这些参数设置正确:
In Debian Jessie I installed MariaDB server 10.0.30 and I try to increase max key length. AFAIU it depends of the config parameter innodb_large_prefix
being enabled. According to the docs, it also requires barracuda
file format and innodb_file_per_table
. After setting them in config and restarting server I see in client, that those parameters are set correctly:
> SHOW GLOBAL VARIABLES LIKE 'innodb_large%';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| innodb_large_prefix | ON |
+---------------------+-------+
1 row in set (0.00 sec)
> SHOW GLOBAL VARIABLES LIKE 'innodb_file%';
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| innodb_file_format | Barracuda |
| innodb_file_format_check | OFF |
| innodb_file_format_max | Antelope |
| innodb_file_per_table | ON |
+--------------------------+-----------+
4 rows in set (0.00 sec)
> SHOW GLOBAL VARIABLES LIKE 'innodb_page%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.00 sec)
我不确定,为什么 innodb_file_format_max
设置为羚羊
,但是当 innodb_file_format_check
为OFF时,无关紧要。实际上,即使我还设置了 Barracuda
,它也没有什么区别。
I am not sure, why innodb_file_format_max
is set Antelope
, but while innodb_file_format_check
is OFF, it should not matter. Actually, even if I had it also set Barracuda
, it did not made difference.
如果我现在尝试创建具有大索引的表:
If i try now create table with large index like:
CREATE TABLE `some_table` (
`some_tableID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`column` varchar(750) COLLATE utf8mb4_estonian_ci NOT NULL DEFAULT '',
PRIMARY KEY (`some_tableID`),
KEY `column` (`column`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_estonian_ci;
我收到错误:
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.
在带有mysql服务器5.7.17的Ubuntu 16.04上,所有相关设置都是相同的(默认情况下)并且有没有大索引的问题(对于utf8mb4,它是750 * 4 = 3000)。
On Ubuntu 16.04 with mysql server 5.7.17 are all related settings same (by default) and there is no problem with large index (for utf8mb4 it is 750*4 = 3000).
我的MariaDB设置有什么问题?
What is wrong with my MariaDB setup?
推荐答案
它需要的不仅仅是这两个设置......
It requires more than just those two settings...
SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=ON;
SET GLOBAL innodb_large_prefix=1;
logout & login (to get the global values);
ALTER TABLE tbl ROW_FORMAT=DYNAMIC; -- or COMPRESSED
也许您只需要添加 ROW_FORMAT = .. 。
到您的 CREATE TABLE
。
Perhaps all you need is to add ROW_FORMAT=...
to your CREATE TABLE
.
5.6.3 up需要这些说明到5.7.7。从5.7.7开始,系统默认正确处理更大的字段。
These instructions are needed for 5.6.3 up to 5.7.7. Beginning with 5.7.7, the system defaults correctly to handle larger fields.
或者,您可以使用前缀索引:
Alternatively, you could use a "prefix" index:
INDEX(column(191))
(但前缀索引在很多方面存在缺陷。)
(But prefix indexing is flawed in many ways.)
如果服务器稍后创建更高的表格格式,则将innodb_file_format_max设置为该值意味着该设置为不是问题。
"If the server later creates a higher table format, innodb_file_format_max is set to that value" implies that that setting is not an issue.
这篇关于如何在MariaDB 10中启用大型索引?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!