Current project use MySQL database , One of the tables contains 1000 10000 records , The table structure is as follows ：
Field Type Comment
id int(11) NOT NULL Primary key (PK)
year int(4) NULL Data year
companyId int(11) NULL Company primary key
elementTemplateId int(11) NULL Element primary key
period tinyint(1) NULL period
value decimal(20,2) NULL value
memo varchar(1000) NULL remarks
status tinyint(1) NULL state (1： Effective ;0： invalid )
createUserId int(11) NULL Creator primary key
createTime datetime NULL Creation time
modifyUserId int(11) NULL Modifier primary key
modifyTime datetime NULL Modification time
The index of inclusion is as follows ：
Indexes Columns Index Type
PRIMARY id Unique
Composite1 companyId, year, period, elementTemplateId Unique
The table type is innodb, When a query statement is executed, a value greater than 30 Ten thousand results , time consuming 5 About seconds ,SQL Statement is used Composite1 Indexes ,explain Check index type The type is range,SQL as follows ：
SELECT year, elementTemplateId, value
FROM Base_Proportion_ConsBalanceSheet
WHERE companyId IN (id1, id2, id3, ...... idn) AND year=xxx AND period=x
AND status = 1
Because this is the simplest single table query , Feeling in SQL There seems to be nothing to optimize （ If there is any, please give me some advice ）, So we consider to modify it MySQL Configuration of , stay my.ini There are 3 It can be modified innodb_buffer_pool_size,innodb_log_buffer_size and innodb_log_file_size, The most important thing is innodb_buffer_pool_size（ The effect of parameters is not explained , You can guess by name , Search a lot on the Internet ）, The default value of this parameter is 8M, Our server is 32G Memory for , So I set it up 8192M,innodb_log_buffer_size Yes 16M,innodb_log_file_size Yes 256M（ after 2 You can also use the default , It's mainly the first parameter ）, Restart after modification MySQL, Execute the above statement from the original 5 It's up to about a second 1 About seconds , The effect is still very obvious .
Technology
Daily Recommendation