mysql单表最大记录数
其实mysql本身并没有对单表最大记录数进行限制,但是从性能考虑,肯定是有影响的。
曾广为流传的一个说法:mysql单表数据量超过2000万行,性能会明显下降,当年的百度DBA测试mysql性能时发现,当单表数据量在2000万行量级的时候,SQL操作性能急剧下降,因此结论由此而来。
阿里巴巴《java开发手册》提出单表行数超过500万行或者单表容量超过2GB,才推荐进行分库分表。
事实上,这个数值和实际记录的条数无关,而与mysql的配置以及机器的硬件有关,mysql为了提高性能,会将表的索引装载到内存中,InnoDB buffer size 足够的情况下,其能完成全加载进内存,查询不会有问题。但是当单表数据达到某个量级的时候,导致内存无法存储其索引,使得之后的SQL查询会产生磁盘IO,从而导致性能下降。当然,这个还和具体的表结构设计有关,最终导致的问题都是内存限制。
mysql配置优化
- innodb_buffer_pool_size = 500M
太小,严重影响数据库性能,服务器共500G内存,但只给mysql缓冲池分配了500M,建议设置为服务器内存的60%。 - expire_logs_days = 7
太短,只能保留7天的binlog,只能恢复7天内的数据。建议设置为参数文件里被覆盖的90天的设置。 - long_query_time = 10
太长,建议设置为2秒,让慢查询日志记录更多的慢查询。 - transaction-isolation = read-committed
建议注释掉,使用数据库默认的事务隔离基本。 - innodb_lock_wait_timeout = 5
设置的太小,会导致事务因锁等待超过5秒,就被回滚。建议和云门户设置保持一致,云门户大小为120. - autocommit = 0
建议改为mysql默认的自动提交(autocommit=1),提升性能。
数据库表设计优化
- 表字段避免null值出现,null值很难查询优化且占用额外的索引空间,推荐使用默认值代替。
- 尽量使用INT而非BIGINT,如果非负则加上UNSIGNED(这样数值容量会扩大一倍),当然能使用TINYINT,SMALLINT,MEDIUMIN更好。
- 使用枚举或整型代替字符串类型。
- 尽量使用TIMESTAMP而非DATETIME。
- 单表不要有太多字段。
- 用整型来存IP。
SQL优化
- 开启慢查询日志来找出慢的sql。
- 使用limit对查询结果的记录进行限定。
- 避免使用select * ,将需要查找的字段列出来。
- 使用join代替子查询。
- or改写成in:or的效率是n级别,in的效率是log(n)级别,in的个数建议200以内。
- 避免where子句中使用!=或<>,否则引擎将放弃索引而进行全表扫描。
- 列表数据不要拿全表,使用limit进行分页。