innodb_page_size 4k 对称

最新推荐文章于 2023-10-29 15:29:06 发布
转载最新推荐文章于 2023-10-29 15:29:06 发布 · 2.1k 阅读
0 ·
CC 4.0 BY-SA版权
原文链接：https://yq.aliyun.com/articles/695578
文章标签：
#数据库
当SSD或PCIE硬盘使用InnoDB存储引擎时，将innodb_page_size设置为4K可提升性能，但会导致最大索引长度降低至768字节。本文通过对比测试说明不同页大小对索引长度的影响。
摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >
SSD、PCIE 硬盘在使用时厂商或者优化建议需要把innodb_page_size 更改为4k,更改为4k 确实性能上有所提升,但由于线上环境索引使用不规范4k 也导致索引的大小必须小于768。
mysql 默认是 16k。
错误：
   Specified key was too long; max key length is 768 bytes
  测试:
4k:
mysql> show variables like 'innodb_page_size';         
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 4096  |
+------------------+-------+

mysql> CREATE TABLE `authroize_info_0` (                                                                                                        
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,                                                                                                  
    ->   `xxxid` varchar(32) NOT NULL,                                                                                                         
    ->   `bind_no` varchar(100) DEFAULT NULL,                                                                                                   
    ->   `auth_code` varchar(100) DEFAULT '',                                                                                                   
    ->   `auth_token` varchar(100) DEFAULT '',                                                                                                  
    ->   `refresh_token` varchar(100) DEFAULT '',                                                                                               
    ->   `expires_in` varchar(100) DEFAULT '',                                                                                                  
    ->   `reExpires_in` varchar(100) DEFAULT '',                                                                                                
    ->   `is_id_auth` varchar(10) DEFAULT '',                                                                                                   
    ->   `is_certified` varchar(10) DEFAULT '',                                                                                                 
    ->   `is_bank_auth` varchar(10) DEFAULT '',                                                                                                 
    ->   `is_licence_auth` varchar(10) DEFAULT '',                                                                                              
    ->   `is_mobile_auth` varchar(10) DEFAULT '',                                                                                               
    ->   `mobile` varchar(20) DEFAULT '',                                                                                                       
    ->   `user_id` varchar(100) DEFAULT '',                                                                                                     
    ->   `real_name` varchar(100) DEFAULT '',                                                                                                   
    ->   `cert_no` varchar(100) DEFAULT '',
    ->   `cert_type_value` varchar(100) DEFAULT '',                                                                                             
    ->   `status` int(11) DEFAULT '0',
    ->   `create_time` timestamp NULL DEFAULT NULL,                                                                                             
    ->   `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,                                                
    ->   `error_code` varchar(100) DEFAULT '',                                                                                                  
    ->   `email` varchar(255) DEFAULT '',                                                                                                       
    ->   `sex` varchar(20) DEFAULT '',                                                                                                          
    ->   `note` varchar(255) DEFAULT '',                                                                                                        
    ->   PRIMARY KEY (`id`),                                                                                                                    
    ->   KEY `aliId_status` (`user_id`,`status`) USING BTREE,                                                                                   
    ->   KEY `uptime_status_moId` (`update_time`,`status`) USING BTREE,                                                                         
    ->   KEY `email_status` (`email`,`status`) USING BTREE,                                                                                     
    ->   KEY `moId_status_aliId` (`xxid`,`status`,`user_id`,`bind_no`) USING BTREE                                                            
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 1071 (42000): Specified key was too long; max key length is 768 bytes

16k:
>show variables like 'innodb_page_size';         
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.02 sec)

CREATE TABLE `authroize_info_0` (                                                   
                                                     
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,                                                                                                  
    ->   `xxid` varchar(32) NOT NULL,                                                                                                         
    ->   `bind_no` varchar(100) DEFAULT NULL,                                                                                                   
    ->   `auth_code` varchar(100) DEFAULT '',                                                                                                   
    ->   `auth_token` varchar(100) DEFAULT '',                                                                                                  
    ->   `refresh_token` varchar(100) DEFAULT '',                                                                                               
    ->   `expires_in` varchar(100) DEFAULT '',                                                                                                  
    ->   `reExpires_in` varchar(100) DEFAULT '',                                                                                                
    ->   `is_id_auth` varchar(10) DEFAULT '',                                                                                                   
    ->   `is_certified` varchar(10) DEFAULT '',                                                                                                 
    ->   `is_bank_auth` varchar(10) DEFAULT '',                                                                                                 
    ->   `is_licence_auth` varchar(10) DEFAULT '',                                                                                              
    ->   `is_mobile_auth` varchar(10) DEFAULT '',                                                                                               
    ->   `mobile` varchar(20) DEFAULT '',                                                                                                       
    ->   `user_id` varchar(100) DEFAULT '',                                                                                                     
    ->   `real_name` varchar(100) DEFAULT '',                                                                                                   
    ->   `cert_no` varchar(100) DEFAULT '',
    ->   `cert_type_value` varchar(100) DEFAULT '',                                                                                             
    ->   `status` int(11) DEFAULT '0',
    ->   `create_time` timestamp NULL DEFAULT NULL,                                                                                             
    ->   `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,                                                
    ->   `error_code` varchar(100) DEFAULT '',                                                                                                  
    ->   `email` varchar(255) DEFAULT '',                                                                                                       
    ->   `sex` varchar(20) DEFAULT '',                                                                                                          
    ->   `note` varchar(255) DEFAULT '',                                                                                                        
    ->   PRIMARY KEY (`id`),                                                                                                                    
    ->   KEY `aliId_status` (`user_id`,`status`) USING BTREE,                                                                                   
    ->   KEY `uptime_status_moId` (`update_time`,`status`) USING BTREE,                                                                         
    ->   KEY `email_status` (`email`,`status`) USING BTREE,                                                                                     
    ->   KEY `moId_status_aliId` (`xxid`,`status`,`user_id`,`bind_no`) USING BTREE                                                            
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.06 sec)

MySQL官方解释:
  If you reduce the InnoDB page size to 8KB or 4KB by specifying the innodb_page_size option when creating the MySQL instance, the maximum length of the index key is lowered proportionally, based on the limit of 3072 bytes for a 16KB page size. That is, the maximum index key length is 1536 bytes when the page size is 8KB, and 768 bytes when the page size is 4KB.


https://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html