Start with hardware and software
Hardware: switch to solid state, increase the memory, increase the buffer (when the buffer size is 20-22GB, the performance is better)
Software: It’s all about creating appropriate indexes
The rationale for hardware optimization is that because MySQL is also software, the data is stored on disk. By the nature of von Leumann’s computer, any program must be loaded into memory before it can run. Therefore, the query process is to load disk data into memory first, and then search for data in memory. But mechanical drives are slower than solid-state drives. (Without further explanation, chapter 6 of Understanding Computer Systems is recommended). As memory increases, larger InnoDB buffers can be set. If the buffer is small, the queried records will be loaded into memory or replaced out of memory by the LRU algorithm, which will need to load data from disk into memory. This process is time-consuming and happens frequently. When the buffer memory is large, it keeps as much data in memory as possible, which reduces page outs and improves efficiency.
For software, control the size of data, the best control to the level of millions, tens of millions of line. Then is the execution of the SQL must go to the index, or the amount of data, will be full table scan.
Full table scanning is time-consuming in two aspects:
1. Large amount of data, if 100W data, you need to judge 100W times according to SQL conditions.
2. Full table scan requires all records to be loaded into memory. If the memory is relatively small, there will be insufficient memory. Here, the old list of obsolete page substitutions involves two disk operations, repeating the process until the full table has been executed.
So if you create a suitable index, you can greatly check the scanned data.
Next time I have time to write an article on how an SQL Is executed.
Reference Books:
In-depth Understanding of Computer Unit Systems
How MySQL Works from the root
MySQL Tech Insider: InnoDB Storage Engine