存档

文章标签 ‘InnoDB’

MySQL的InnoDB大表建索引

2015年11月11日 没有评论

对250G的大表新建索引,等了24小时没结束。注:innodb_buffer_pool_size大小为20G
按自己的理解,应该会生成一个略大于原表的临时表,替换原表。
在大表的同目录下只发现一个文件名以#开始的小文件,未见大文件。

-rw-rw---- 1 mysql mysql         8923  Nov   9 01:14 #sql-4220_ble924.sql

/tmp下面也没见大文件,用du -sh查到的结果也只有20k。
用lsof -p查到一批文件,发现打开了/tmp下两个30G的文件,但标记为已删除

# lsof -p 16928
...
mysqld  16928 mysql  txt    REG                8,1     41662738    671857 /data/software/mysql-5.5.8/bin/mysqld
...
mysqld  16928 mysql  263u   REG                8,1  30028070912  25591817 /tmp/ibZMaeaz (deleted)
mysqld  16928 mysql  268u   REG                8,1  30028070912  25591818 /tmp/ibRE7pZq (deleted)

用iostat -x 1查看,其所在磁盘的ioutil为100%,主要为r,少量w。
因为磁盘的空余空间不足,只好kill了。kill以后,ioutil依然是100%,读入速度没变,写入速度达到读取速度。
kill之前process状态为

| 11659556 | root | localhost | quad | Query | 84097 | manage keys | ALTER TABLE T1 ADD INDEX I1 (C1, C2) |

kill之后变成

| 11659556 | root | localhost | quad | Killed | 84097 | manage keys | ALTER TABLE T1 ADD INDEX I1 (C1, C2) |

传言回滚会耗费两倍以上的时间(24小时*2=48小时),实测发现十分钟内搞定。完成后lsof未见那两个30G的文件,df查到磁盘空间已经释放。

后又用17G的中表进行测试,半个小时生成的tmp文件200M,所此估算约需要42小时,放弃之。

下面是建议:
更多内容…

MySQL简单查询的默认排序问题

2015年4月20日 没有评论

一个问题的备忘。

测试MySQL版本:5.5.8
测试MySQL存储引擎:InnoDB

初衷:
大表SQL查询按主键正序order by后再limit,速度比较慢,需要优化。

优化过程:
后发现把order by去掉后速度会快非常多,且结果依然正确。

反例被发现:
测试中发现某些特定查询,用到的索引不是主键,默认的顺序是所用到的索引的正序,跟主键的正序不一样。

反例的两个优化推广:
1、如果用到的索引序和主键序完全一样,可以省略order by操作。
2、如果通过主键索引的查询性能优于另一个索引的先排序再limit,可以考虑强制索引:FORCE INDEX (PRIMARY)

欢迎提出推广的反例或其它意见~

分类: 工作 标签: , , , ,

InnoDB中关于有符号与无符号整数的测试

2014年11月18日 没有评论

如题,对InnoDB中的tinyint类型做了有符号和无符号转换的测试,几种情况:

1、有符号的-128转无符号时,预期是转成128,原因是0x80,实际上变成了0
2、无符号的设置为256时,预期为0,实为255
3、无符号的255转为有符号时,预期结果为-1,实为127。
4、无符号的255转为有符号时,为127,再转为无符号时,还是127不变。

初步结论:超出值域时,会自动把值修正为离当前值近的边界值,原值丢弃。通过改回原类型无法找回原来的值。

分类: 工作 标签: , , ,