目前做的项目使用MySQL数据库,其中有张表包含近1000万条记录,表结构如下:
Field | Type | Comment | |
id | int(11) NOT NULL | 主键(PK) | |
year | int(4) NULL | 数据年份 | |
companyId | int(11) NULL | 公司主键 | |
elementTemplateId | int(11) NULL | 元素主键 | |
period | tinyint(1) NULL | 期间 | |
value | decimal(20,2) NULL | 值 | |
memo | varchar(1000) NULL | 备注 | |
status | tinyint(1) NULL | 状态(1:有效;0:无效) | |
createUserId | int(11) NULL | 创建人主键 | |
createTime | datetime NULL | 创建时间 | |
modifyUserId | int(11) NULL | 修改人主键 | |
modifyTime | datetime NULL | 修改时间 |
包含索引如下:
Indexes | Columns | Index Type | |
---|---|---|---|
PRIMARY | id | Unique | |
Composite1 | companyId, year, period, elementTemplateId | Unique |
表类型为innodb,在执行某个查询语句时会返回大于30万条结果,耗时5秒左右,SQL语句使用到了Composite1索引,explain检查索引type类型为range,SQL如下:
SELECT year, elementTemplateId, value
FROM Base_Proportion_ConsBalanceSheet
WHERE companyId IN (id1, id2, id3, ......, idn) AND year=xxx AND period=x AND status = 1
由于这只是最简单的单表查询,感觉在SQL上好像没有什么能优化的地方了(如果有的话望各位看官给予指点),于是考虑修改MySQL的配置,在my.ini中有3处地方可以修改innodb_buffer_pool_size、innodb_log_buffer_size和innodb_log_file_size,其中最重要的就是innodb_buffer_pool_size(参数作用就不解释了,看名字就能大致猜到,网上一搜一大堆),该参数默认值为8M,我们服务器是32G的内存,所以我设了8192M,innodb_log_buffer_size设了16M,innodb_log_file_size设了256M(后2个参数使用默认的也可以,主要是第一个参数),修改完后重启MySQL,执行上述语句从原来的5秒左右提升到了1秒左右,效果还是很明显的。