MySQL参数之innodb_large_prefix

  • lhrbest

    2020-04-23 10:26:26
  • MySQL
  • 原创


MySQL参数之innodb_large_prefix


https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_large_prefix

https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_large_prefix



MySQL 5.6：



MySQL 5.7：


MySQL 8.0中已经没有innodb_large_prefix这个参数了。



在华为云中，在5.6中可以在界面上操作修改，在5.7中不能修改。在5.6中默认为off，可以在界面上修改为on；在5.7中默认为on，不用修改。

MySQL [(none)]> show variables like '%innodb_large_prefix%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | OFF   |
+---------------------+-------+
1 row in set (0.03 sec)
MySQL [(none)]> set global innodb_large_prefix=on;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation
MySQL [(none)]> show variables like '%innodb_large_prefix%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | ON    |
+---------------------+-------+
1 row in set (0.02 sec)




  1. 创建一张表，其中有个varchar 大字段，并且在这个字段上建索引，结果发现MySQL报错：
    ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.
    以下为建表语句：
    create table piratebay(
    SYS_ID int ,
    FILE_NAME VARCHAR(200),
    FILE_ID VARCHAR(30),
    NUM1 VARCHAR(30),
    NUM2 VARCHAR(30),
    MAGNET_LINK VARCHAR(500),
    PRIMARY KEY (sys_id),
    KEY piratebay_n1 (FILE_NAME)) 
    engine=innodb;

MySQL 环境配置：
Server version: 5.6.28-log MySQL Community Server (GPL)

Server characterset: utf8mb4
Db characterset: utf8mb4

解决办法：

（1）查看相关配置并作出如下设置

innodb_large_prefix = ON 
innodb_file_format = Barracuda 
innodb_file_per_table = ON

（2）修改建表语句，加入 row_format=DYNAMIC

create table piratebay(
SYS_ID int ,
FILE_NAME VARCHAR(200),
FILE_ID VARCHAR(30),
NUM1 VARCHAR(30),
NUM2 VARCHAR(30),
MAGNET_LINK VARCHAR(500),
PRIMARY KEY (sys_id),
KEY piratebay_n1 (FILE_NAME)) 
engine=innodb row_format=dynamic;

原因：

   MySQL 索引只支持767个字节，utf8mb4 每个字符占用4个字节，所以索引最大长度只能为191个字符，即varchar(191)，若想要使用更大的字段，mysql需要设置成支持数据压缩，并且修改表属性 row_format ={DYNAMIC|COMPRESSED}
         大家看明白了吧，吧row_formatl类型修改为这两种模式。

下面是我做的过程图：
修改Mysql索引长度限制

可以看到row_formatl类型，下面修改类型
修改Mysql索引长度限制

CREATE TABLE  test2 (  id int(11) NOT NULL AUTO_INCREMENT,  date varchar(25) DEFAULT NULL,  sess_id varchar(255) DEFAULT NULL,  keyword varchar(25) NOT NULL,  url_n varchar(3) DEFAULT NULL,  s_n varchar(3) DEFAULT NULL,  select_url varchar(255) DEFAULT NULL, UNIQUE KEY ( id, keyword) ) ENGINE=innodb DEFAULT row_format=dynamic;

修改Mysql索引长度限制

修改Mysql索引长度限制

修改Mysql索引长度限制

修改Mysql索引长度限制

ok,了这就可以插入超过767字节的索引了



在5.6中的表现：

$mysql -uroot -p
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4708773Server version: 5.6.16.12-20161009-log Source distribution
 
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
mysql> CREATE TABLE `cc` (    ->   `id` int(11) NOT NULL AUTO_INCREMENT,    ->   `c` varchar(255) DEFAULT NULL,    ->   PRIMARY KEY (`id`),    ->   KEY `idx_c` (`c`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected, 1 warning (0.01 sec)
 
mysql> show warnings;
+---------+------+---------------------------------------------------------+
| Level   | Code | Message                                                 |
+---------+------+---------------------------------------------------------+
| Warning | 1071 | Specified key was too long; max key length is 767 bytes |
+---------+------+---------------------------------------------------------+1 row in set (0.00 sec)
 
mysql> show create table cc\G
*************************** 1. row ***************************
       Table: cc
Create Table: CREATE TABLE `cc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_c` (`c`(191))
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb41 row in set (0.00 sec)
mysql> show global variables like "innodb_large_prefix";
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | OFF   |
+---------------------+-------+1 row in set (0.00 sec)

在5.7中的表现

$mysql -uroot -p
Entry Port ==== 3313Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9304Server version: 5.7.13.1-AliSQL-20160928-log Source distribution
 
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
root@test 02:23:11>CREATE TABLE `cc` (    ->   `id` int(11) NOT NULL AUTO_INCREMENT,    ->   `c` varchar(255) DEFAULT NULL,    ->   PRIMARY KEY (`id`),    ->   KEY `idx_c` (`c`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.00 sec)
 
root@test 02:25:46>show warnings;
Empty set (0.00 sec)
 
root@test 02:26:17>show global variables like "innodb_large_prefix";
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | ON    |
+---------------------+-------+1 row in set (0.00 sec)
 
root@test 02:28:43>

结论

如果想在5.6和5.7统一的话，修改innodb_large_prefix这个参数同时为ON或者OFF就可以了





前缀索引



             当索引是很长的字符序列时，这个索引将会很占内存，而且会很慢，这时候就会用到前缀索引了。所谓的前缀索引就是去索引的前面几个字母作为索引，但是要降低索引的重复率，索引我们还必须要判断前缀索引的重复率。先看这样一张表：


mysql> select * from test;

+----------+-------+

| name     | score |

+----------+-------+

| zhangsan | 123   |

| wangwu   | 345   |

| zhaoliu  | 234   |

| lisisi   | 687   |

+----------+-------+

4 rows in set (0.08 sec)

如果以name作为索引，当name对应的字符串很长时，就要考虑索引的占用空间和效率问题。这时候就需要引入前缀索引，在使用前缀索引时，首先要去比较重复率。


mysql> select 1.0*count(distinct name)/count(*) from test;

+-----------------------------------+

| 1.0*count(distinct name)/count(*) |

+-----------------------------------+

|                           1.00000 |

+-----------------------------------+

1 row in set (0.00 sec)


mysql> select 1.0*count(distinct left(name,2))/count(*) from test;

+-------------------------------------------+

| 1.0*count(distinct left(name,2))/count(*) |

+-------------------------------------------+

|                                   0.75000 |

+-------------------------------------------+

1 row in set (0.00 sec)


mysql> select 1.0*count(distinct left(name,1))/count(*) from test;

+-------------------------------------------+

| 1.0*count(distinct left(name,1))/count(*) |

+-------------------------------------------+

|                                   0.75000 |

+-------------------------------------------+

1 row in set (0.00 sec)


mysql> select 1.0*count(distinct left(name,3))/count(*) from test;

+-------------------------------------------+

| 1.0*count(distinct left(name,3))/count(*) |

+-------------------------------------------+

|                                   0.75000 |

+-------------------------------------------+

1 row in set (0.00 sec)


mysql> select 1.0*count(distinct left(name,4))/count(*) from test;

+-------------------------------------------+

| 1.0*count(distinct left(name,4))/count(*) |

+-------------------------------------------+

|                                   1.00000 |

+-------------------------------------------+

1 row in set (0.00 sec)


mysql> select 1.0*count(distinct left(name,2))/count(*) from test;

+-------------------------------------------+

| 1.0*count(distinct left(name,2))/count(*) |

+-------------------------------------------+

|                                   0.75000 |

+-------------------------------------------+

1 row in set (0.00 sec)


mysql> select 1.0*count(distinct left(name,5))/count(*) from test;

+-------------------------------------------+

| 1.0*count(distinct left(name,5))/count(*) |

+-------------------------------------------+

|                                   1.00000 |

+-------------------------------------------+

1 row in set (0.00 sec)

其中left函数为字符串截取函数。


select 1.0*count(distinct name)/count(*) from test这是比较整个name的重复率，当时这是最好的情况。然后分别截取name字符的前几个字母，最后选取的计算值要接近整个取整个name时得出的计算值，然后再选中占用空间小的。由上面执行的结果可知应选中name的前4个字母作为索引最为适合。


创建索引：


mysql> alter table test add key(name(4));

Query OK, 4 rows affected (0.15 sec)

Records: 4  Duplicates: 0  Warnings: 0

随后就可以正常按name字符进行查找了。





About Me

........................................................................................................................

● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除

● 本文在itpub、博客园、CSDN和个人微 信公众号（ DB宝）上有同步更新

● 本文itpub地址： http://blog.itpub.net/26736162

● 本文博客园地址： http://www.cnblogs.com/lhrbest

● 本文CSDN地址： https://blog.csdn.net/lihuarongaini

● 本文pdf版、个人简介及小麦苗云盘地址： http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答： http://blog.itpub.net/26736162/viewspace-2134706/

● DBA宝典今日头条号地址： http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

........................................................................................................................

● QQ群号： 230161599 、618766405

● 微 信群：可加我微 信，我拉大家进群，非诚勿扰

● 联系我请加QQ好友 （ 646634621 ），注明添加缘由

● 于 2020-04-01 06:00 ~ 2020-04-30 24:00 在西安完成

● 最新修改时间：2020-04-01 06:00 ~ 2020-04-30 24:00

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

........................................................................................................................

● 小麦苗的微店： https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

● 小麦苗出版的数据库类丛书： http://blog.itpub.net/26736162/viewspace-2142121/

● 小麦苗OCP、OCM、高可用网络班： http://blog.itpub.net/26736162/viewspace-2148098/

● 小麦苗腾讯课堂主页： https://lhr.ke.qq.com/

........................................................................................................................

使用 微 信客户端扫描下面的二维码来关注小麦苗的微 信公众号（ DB宝）及QQ群（DBA宝典）、添加小麦苗微 信， 学习最实用的数据库技术。

........................................................................................................................

欢迎与我联系

 

 



请使用浏览器的分享功能分享到微信等