如何在 MariaDB 10 中启用大索引? [英] How to enable large index in MariaDB 10?

问题描述

在 Debian Jessie 中，我安装了 MariaDB 服务器 10.0.30，并尝试增加最大密钥长度.AFAIU 这取决于启用的配置参数 innodb_large_prefix.根据 docs，它还需要 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 设置为 Antelope，但是当 innodb_file_format_check 为 OFF 时，应该没关系.其实即使我也设置了梭子鱼，也没什么区别.

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.

在 Ubuntu 16.04 和 mysql 服务器 5.7.17 上，所有相关设置都相同(默认)，大索引没有问题(对于 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 到 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屋！