Problem description:
We need to access a very big table which has 40 million records. In the table, id is the primary key and program_id is indexed.
When executing a select query:
select * from program_access_log where program_id between 1 and 4000
The above query executes very slowly, we thought it was because there were too many records in the table. So we added id in the where condition to limit the search so that each time only half a million records would be read.
select * from program_access_log where id between 1 and 500000 and program_id between 1 and 4000
But this query is still very slow, it has no obvious performance improvement compared to the first query. It should be executed very fast with MySQL to process half a million records with index created.
Problem analysis:
This table contains 30 GB data, the database server memory is 16 GB, so the whole table cannot be loaded in one time, this causes the issue.
This SQL query has two conditions :
- id is between 1 and 500000
- program_id is between 1 and 4000
Since program_id has a narrower range, so the database will use it as the main index. First, it will find all ids with program_id between 1 and 4000 through the index file, this process is very fast; Then it will find all the records with the ids in first step, since these ids are discrete, so MySQL will not access the table sequentially.
And this table is very big, it cannot be loaded in the memory at once, so MySQL needs to relocate one record and load records nearby it every time it reads a record, large number of IO operations leads to the performance penalty.
Problem solution:
1. Use program_id to do table partition
2. Split the table into subtables so that each subtable is smaller than the memory size.
However, in MySQL 5.0 server, it lacks partition support, and this table is a public table, we cannot modify the structure of the table without affecting other programs, so we adopts the third method:
select * from program_access_log where id between 1 and 500000 and program_id between 1 and 15000000
Now the range of program_id is much larger than the range of id, id is the main index. So the server will search half a million records sequentially, it has the same performance as searching a table with half a million records.
