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
©2019-2020 Toolsou All rights reserved,
Send love - A little romance for programmers VHDL—— Design of frequency divider Python Implementation of Hanoi Tower code It's over , Starting salary 30khtml+css+js Make a simple website home page QQ Login interface implementation Hill sorting of sorting algorithm ——c++ realization 【 Wechat applet learning 】 Netease music cloud code page implementation details Resume the 13th session python Blue Bridge Cup 2022 Solution to the 13th Blue Bridge Cup ( whole )