本文共 9729 字，大约阅读时间需要 32 分钟。
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 bytes16k:>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
转载地址：http://pyoux.baihongyu.com/