Shulou(Shulou.com)06/01 Report--

This article mainly explains "the paging optimization scheme of MySQL". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn the "paging optimization scheme of MySQL"!

MySQL paging optimization:

SQL: select * from t_user u order by id limit 5000000, 10 Analysis: mysql will read 5000010 pieces of data and return only the last 10 items, which takes a long time to query. Optimization scheme: scheme 1: only the previous data is allowed to be queried, but not the data at the back. Eg: Baidu search results, at most more than 70 pages. Plan 2: when querying the next page, pass the id (lastId) of the last data on the previous page, that is, select * from t_user u where id > 5000000 order by id limit 10 Note: if the judgment condition in the where condition has other non-primary key columns, you need to establish a composite index, which must contain the columns in the where statement. Scheme 3: delay associating non-index columns, that is, select * from t_user U1 inner join (select id from t_user order by id limit 5000000, 10) U2 on u1.id=u2.id experiment: environment: there are 6274934 data in the MySQL5.6 t _ user table under win7 The following are the table creation statements of t_user: CREATE TABLE `tuser` (`id` BIGINT (20) NOT NULL AUTO_INCREMENT, `bu_ id` INT (20) NOT NULL, `name` VARCHAR (255) NOT NULL, `age` INT (11) NOT NULL, `sex` VARCHAR (255) NULL DEFAULT NULL PRIMARY KEY (`id`) COLLATE='utf8_general_ci' ENGINE=InnoDB Select * from t_user u order by id limit 5000000, 10 # 2.746 sec when there is only one query sql, the time it takes to query. # 25.615 sec has a function (or process) that constantly inserts new data into the t _ user table, the time it takes to execute the above sql query to find the data. Select * from t_user u where id > 5000000 order by id limit 10 # 0.047 sec when there is only one query sql, the time it takes to query. # 0.063 sec has a function (or process) that constantly inserts new data into the t _ user table, the time it takes to execute the above sql query to find the data. Select * from t_user U1 inner join (select id from t_user order by id limit 5000000, 10) U2 on u1.id=u2.id # 2.137 sec when there is only one query sql, the time it takes to query. # 13.604 sec has a function (or process) that constantly inserts new data into the t _ user table, the time it takes to execute the above sql query to find the data. At this point, I believe that everyone on the "MySQL paging optimization scheme" have a deeper understanding, might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.