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 .

©2019-2020 Toolsou All rights reserved,
Python Garbage collection and memory leak hive Summary of processing methods for a large number of small files The difference between memory overflow and memory leak , Causes and Solutions Create data mysql Library process You don't know ——HarmonyOS stay Vue Use in Web WorkerSparkSQL Achieve partition overlay write msf Generate Trojan horse attack android mobile phone Linux Page replacement algorithm C Language implementation Django Personal blog building tutorial --- Time classified archiving