One, foreword
MySQL pagination query as a Java interview as a frequent interview questions, here it is necessary to practice, after all, practice is the real knowledge. Many students suffer from lack of massive data when doing tests, but the official test library is actually a set.
2. Simulated data
Here, the simulated data can be imported in two cases. If only data testing is required, the official data is recommended. If the official numbers don’t cut it, we can simulate them ourselves.
1. Import the official test library
Download the official database file or download it on Github.
The test library contains six tables.
Enter employees_DB and execute the import data instruction
mysql -uroot -proot -t < employees.sql
Copy the codeSome environments may report errors
ERROR 1193 (HY000) at line 38: Unknown system variable 'storage_engine'
Copy the codeConnect to mysql and check the default engine. It is not a local environment problem.
mysql> show variables like '%engine%'; +----------------------------------+--------+ | Variable_name | Value | +----------------------------------+--------+ | default_storage_engine | InnoDB | | default_tmp_storage_engine | InnoDB | | disabled_storage_engines | | | Internal_tmp_disk_storage_engine | InnoDB | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - + 4 rows in the set (0.01 SEC)Copy the codeModify the employees.sql script
set default_storage_engine = InnoDB;
-- set storage_engine = MyISAM;
-- set storage_engine = Falcon;
-- set storage_engine = PBXT;
-- set storage_engine = Maria;
select CONCAT('storage engine: ', @@default_storage_engine) as INFO;
Copy the codeThe discovery import succeeded again
➜ employees_db mysql -uroot -proot -t < employees. SQL mysql: [Warning] Using a password on thecommandline interface can be insecure. +-----------------------------+ | INFO | +-----------------------------+ | CREATING DATABASE STRUCTURE | +-----------------------------+ +------------------------+ | INFO | +------------------------+ | storage engine: InnoDB | +------------------------+ +---------------------+ | INFO | +---------------------+ | LOADING departments | +---------------------+ +-------------------+ | INFO | +-------------------+ | LOADING employees | +-------------------+ +------------------+ | INFO | +------------------+ | LOADING dept_emp | +------------------+ +----------------------+ | INFO | +----------------------+ | LOADING dept_manager | +----------------------+ +----------------+ | INFO | +----------------+ | LOADING titles | +----------------+ +------------------+ | INFO | +------------------+ | LOADING salaries | +------------------+Copy the codeVerify the result (same as the previous configuration modification)
➜ employees_db mysql -uroot -proot -t < test_employees_sha. SQL mysql: [Warning] Using a password on the command line interface can be insecure. +----------------------+ | INFO | +----------------------+ | TESTING INSTALLATION | +----------------------+ +--------------+------------------+------------------------------------------+ | table_name | expected_records | expected_crc | +--------------+------------------+------------------------------------------+ | departments | 9 | 4b315afa0e35ca6649df897b958345bcb3d2b764 | | dept_emp | 331603 | d95ab9fe07df0865f592574b3b33b9c741d9fd1b | | dept_manager | 24 | 9687a7d6f93ca8847388a42a6d8d93982a841c6c | | employees | 300024 | 4d4aa689914d8fd41db7e45c2168e7dcb9697359 | | salaries | 2844047 | b5a1785c27d75e33a4173aaa22ccf41ebd7d4a9f | | titles | 443308 | d12d5f746b88f07e69b9e36675b6067abb01b60e | +--------------+------------------+------------------------------------------+Copy the codeWe can see that emP has about 330,000 pieces of data.
2. The stored procedure imports simulated data
Here we can choose stored procedure batch import.
Start by creating a table
drop table if exists `user`;
create table `user`(
`id` int unsigned auto_increment,
`username` varchar(64) not null default '',
`score` int(11) not null default 0,
primary key(`id`)
)ENGINE = InnoDB;
Copy the codeCreating a stored procedure
DROP PROCEDURE IF EXISTS batchInsert; delimiter ? Create PROCEDURE batchInsert() -- Create a stored procedure begin -- Start of the stored procedure body Declare num int; Set num=1; Insert into user(' username ', 'score') values(concat('user ', num),num); insert into user(' username ', 'score') values(concat('user ', num),num); Set num=num+1; -- Loop variable increment end while; -- End loop? -- End of stored procedure body delimiter; # recovery; End CALL batchInsert. -- Execute the stored procedureCopy the codeYou can see that the test 300W pieces of data were inserted in about 1046s. Well, the planned import of 1000W results took too long.
Repeat and optimize the common MySQL paging query problems.
We tested the existing table user, which had 300W entries.
1. Pre-check
Start by looking at the structure of the table and what indexes currently exist
mysql> desc user; +----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(30) | NO | | | | | score | int(11) | NO | | 0 | | + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 3 rows in the set (0.00 SEC) mysql > show the index the from user; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -------+---------+---------------+ | user | 0 | PRIMARY | 1 | id | A | 2991886 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the codeYou can see that there is only the ID primary key index.
Second, check whether caching is enabled (to avoid query caching affecting execution efficiency)
mysql> show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
6 rows in set (0.00 sec)
mysql> show profiles;
Empty set, 1 warning (0.00 sec)
Copy the codeHave_query_cache and query_cache_type Note Caching is supported but not enabled. If the show Profiles display is empty, the Profiles function is disabled.
Open the profiles
mysql> SET profiling = 1; Query OK, 0 rows affected, 1 warning (0.00 SEC) mysql> show profiles; +----------+------------+-------------------+ | Query_ID | Duration | Query | + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 1 | | 0.00012300 SET profiling | = 1 + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set, 1 warning (0.00 SEC)Copy the code2. Query without index paging
Order by + limit M,n. Now let’s test paging performance
Select * from user order by score limit 0,10; -- 10 rows in set (0.65 SEC) select * from user order by score limit 10000,10; -- 10 rows in set (0.83 SEC) select * from user order by score limit 1000000,10; -- 10 rows in set (1.03 SEC) select * from user order by score limit 1000000,10; -- 10 rows in set (1.14 SEC)Copy the codeLet’s make sure we use the index here
Mysql > explain select * from user order by score limit 1000000,10; +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------- -----+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------- -- -- -- -- -- + | | 1 SIMPLE | user | NULL | | NULL ALL | NULL | NULL | NULL | 2991995 | | 100.00 Using filesort | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------- -----+ 1 row in set, 1 Warning (0.00 SEC)Copy the codeAs you can see, no indexes are used, and a full table scan of 100W data pages takes about 1.14s.
3. Query on index pages
Select * from user order by id limit 10000,10; -- 10 rows in set (0.0sec) select * from user order by id limit 1000000,10; -- 10 rows in set (0.18 SEC) SELECT * from user order by ID limit 200000,10; -- 10 rows in set (0.35 SEC)Copy the codeThis query uses the primary key index, so the query is relatively efficient. It can be seen that when the amount of data increases, the query efficiency decreases significantly.
Let’s make sure we use the index here
Mysql > explain select * from user order by id limit 200000010; +----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+------- + | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+------- + | 1 | SIMPLE | user | NULL | index | NULL | PRIMARY 4 | NULL | | 2000010 | | NULL | 100.00 +----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+------- + 1 row in set, 1 warning (0.00 SEC)Copy the codeYou can see that a full index scan was used to query a total of 2000010 rows of data.
4. The optimization
We use MYSQL’s Query diagnostic analysis tool to view the time spent on each SQL statement operation. It can be seen that the 2000010 records obtained by the query are returned to the client, and the time is mainly concentrated in the Sending data stage. But the client only needs 10 pieces of data. Can we only return 10 pieces of data to the client?
mysql> show profiles; +----------+------------+---------------------------------------------------------+ | Query_ID | Duration | Query | + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | | SET profiling 0.00012300 = 1 | | | | SET profiling 0.00009200 = 1 2 | | 3 | | 0.35689500 select * from user order id limit by 2000000, | | | 4 0.00023900 | explain the select * from user order by id 2000000, 10 | limit +----------+------------+---------------------------------------------------------+ 4 rows in set, 1 warning (0.00 SEC) mysql> show profile for query 3; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + | Status | Duration | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + | starting 0.000071 | | | 0.000007 checking permissions | | | Opening tables 0.000012 | | | init 0.000017 | | | System lock 0.000008 | | | Optimizing 0.000005 | | | statistics 0.000024 | | | preparing 0.000016 | | | Sorting result 0.000004 | | | executing | 0.356653 0.000003 | | Sending data | | | end 0.000013 | | | | 0.000005 query end | | closing tables 0.000008 | | | Freeing the items 0.000019 | | | cleaning up 0.000030 | | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + 16 rows in the set, 1 warning (0.00 SEC)Copy the codeOnline optimization scheme: sub-query + overwrite index
mysql> select * from user where id > (select id from user order by id limit 2000000, 1) limit 10; +---------+--------------+---------+ | id | username | score | +---------+--------------+---------+ | 2000002 | user-2000002 | 2000002 | | 2000003 | user-2000003 | 2000003 | | 2000004 | user-2000004 | 2000004 | | 2000005 | user-2000005 | 2000005 | | 2000006 | user-2000006 | 2000006 | | 2000007 | user-2000007 | 2000007 | | 2000008 | user-2000008 | 2000008 | | 2000009 | user-2000009 | 2000009 | | 2000010 | user-2000010 | 2000010 | | 2000011 | User - 2000011 | 2000011 | + -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- - + 10 rows in the set (0.29 SEC) mysql > explain the select * from user where id > (select id from user order by id limit 2000000, 1) limit 10; +----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+------- ------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+------ -- -- -- -- -- -- -- + | 1 | PRIMARY | user | NULL | range | PRIMARY | PRIMARY 4 | NULL | | 1495997 | | 100.00 Using the where | | | 2 SUBQUERY | user | NULL | index | NULL | PRIMARY 4 | NULL | | 2000001 | | 100.00 Using index | +----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+------- ------+ 2 rows in set, 1 warning (0.30 SEC)Copy the codeHowever, it does not improve query performance. Don’t see what the problem is? As you can see from the execution plan, the indexes are in line with our expectations. “Rows” retrieves a lot of rows here. Look at the subqueries separately
mysql> select id from user order by id limit 2000000, 1; + -- -- -- -- -- -- -- -- -- + | | id + -- -- -- -- -- -- -- -- -- + | 2000001 | + -- -- -- -- -- -- -- -- -- + 1 row in the set (0.29 SEC) mysql > explain the select id from the user for the order by id limit 2000000, 1; +----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+------- ------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+------ -- -- -- -- -- -- -- + | | 1 SIMPLE | user | NULL | index | NULL | PRIMARY 4 | NULL | | 2000001 | | 100.00 Using index | +----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+------- ------+ 1 row in set, 1 Warning (0.00 SEC)Copy the codeHere you can see that the subquery still takes about 3s even if it overwrites the index, which I think is the normal index IO time. No official test data was found for comparison, as well as the time taken by MySQL for an IO query.
Int primary key a page can store 1000 key, root permanent memory, then B+Tree second layer about 100W key, test data in 200W pages, theoretically need 2 IO to find data. If the time of 2 IO is 3s, 1 IO should be about 1.5s. Let’s query the page around 99W to see if it fits our hypothesis.
Mysql > select id from user order by id limit 990000,1; + -- -- -- -- -- -- -- -- + | | id + -- -- -- -- -- -- -- -- + | 990001 | + -- -- -- -- -- -- -- -- + 1 row in the set (0.15 SEC)Copy the codeSo the author’s bold guess here is that the result is normal overhead
Four, the last
Originally wanted to check whether the paging optimization scheme on the web is reliable, but the expected results are still different. I hope the intelligent readers have different opinions. There is the author’s wechat QR code in the official account.