InnoDB 普通索引成功，唯一索引失败

寄语星空

对有1000万条记录的表创建唯一索引失败，普通索引是可以的，表大小有4G

mysql> alter table prod_inst_xxx add primary key (prod_inst_id);
ERROR 1114 (HY000): The table 'prod_inst_xxx' is full

mysql> alter table prod_inst_xxx add unique index idx_prod_inst_xxx_01 (prod_inst_id);
ERROR 1114 (HY000): The table 'prod_inst_xxx' is full
CREATE UNIQUE INDEX idx_prod_inst_xxx_01 ON prod_inst_xxx (prod_inst_id);

mysql> alter table prod_inst_xxx add index idx_prod_inst_xxx_01 (prod_inst_id);
Query OK, 0 rows affected (3 min 0.83 sec)
Records: 0  Duplicates: 0  Warnings: 0

CREATE TABLE `prod_inst_xxx` (
.........
) ENGINE=InnoDB DEFAULT CHARSET=utf8  

my.cnf 参数是：
port = 3306
server_id = 1
#socket = /mysql/mysql.sock
character_set_server = gbk
#default_character_set = gbk
#lower_case_table_names=1

#basedir = /mysql/
datadir = /mysql/data/
sort_buffer_size = 16M
join_buffer_size = 64M
read_rnd_buffer_size = 2M
back_log = 100
max_connections = 200
max_connect_errors = 30
#table_cache = 1024
#innodb_data_file_path = ibdata1:1G:autoextend:max:25G
innodb_file_per_table = 1
max_allowed_packet = 32M
max_heap_table_size = 10G
query_cache_size = 128M
query_cache_limit = 4M
ft_min_word_len = 8
tmp_table_size = 2G
log_warnings
#log_slow_queries
slow_query_log = 1
long_query_time = 6
#log_long_format
tmpdir = /tmp
innodb_buffer_pool_size = 1G
innodb_data_home_dir = /mysql/data/
innodb_file_io_threads = 2
innodb_thread_concurrency = 2
innodb_flush_log_at_trx_commit = 0
innodb_log_buffer_size = 16M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_log_group_home_dir = /mysql/log/
innodb_lock_wait_timeout = 120
innodb_buffer_pool_size = 1G
[mysqld_safe]
open-files-limit = 1024

[client]
character_set_client = gbk

kider

应该是这个tmp_table_size = 2G设置太小了。

另外，你看看你的配置文件中尽然有两个nnodb_buffer_pool_size = 1G

寄语星空

tmp_table_size = 8G
改成这样也是报错，关键是普通索引是可以的，唯一索引才报错
之前修改了 key_buffer_size =512M 也是不行，默认是8M

寄语星空

唯一索引表占空间比普通索引要大50%，比不带索引表要大73%，4G的基础表建了唯一索引后有7G左右
创建唯一索引时，表要重建，占的内存空间比较大（个人理解），导致失败了
测试数据如下：
-- 1、唯一索引  Query OK, 100000 rows affected (29.72 sec)
drop table if exists prod_inst_000;
create table prod_inst_000 ENGINE=InnoDB DEFAULT CHARSET=utf8 as select * from prod_inst_551 limit 0,0;
create unique index uidx_prod_inst_000 on prod_inst_000 (prod_inst_id);
insert into prod_inst_000 select * from prod_inst_551 limit 0,100000;
commit;

-rw-rw---- 1 mysql mysql      16104 Sep 18 07:25 prod_inst_000.frm
-rw-rw---- 1 mysql mysql   88080384 Sep 18 07:25 prod_inst_000.ibd


-- 2、普通索引  Query OK, 100000 rows affected (18.18 sec)
drop table if exists prod_inst_000;
create table prod_inst_000 ENGINE=InnoDB DEFAULT CHARSET=utf8 as select * from prod_inst_551 limit 0,0;
create index idx_prod_inst_000 on prod_inst_000 (prod_inst_id);
insert into prod_inst_000 select * from prod_inst_551 limit 0,100000;
commit;

-rw-rw---- 1 mysql mysql      16104 Sep 18 07:28 prod_inst_000.frm
-rw-rw---- 1 mysql mysql   58720256 Sep 18 07:28 prod_inst_000.ibd


-- 3、唯一索引占空间比普通索引要增长50%
(88080384-58720256)/58720256=0.5

tianshizuoyi

学习了