Shulou(Shulou.com)05/31 Report--

This article shows you how to optimize MySQL tens of millions of fast pagination, concise and easy to understand, absolutely can make your eyes shine, through the detailed introduction of this article I hope you can gain something.

The data table collect ( id, title ,info ,vtype) has these four fields, where title is fixed length, info is text, id is gradual, vtype is tinyint, vtype is index. This is a simple model of a basic news system. Now fill it with data, fill it with 100,000 news stories.

Finally, collect is 100,000 records, and the database table occupies 1.6G of hard disk. OK , look at the following SQL statement:

select id,title from collect limit 1000,10; soon; basically 0.01 seconds OK, see below

select id,title from collect limit 90000,10; pagination from 90,000 entries, result?

8-9 God, what's wrong?? In fact, to optimize this data, find the answer online. Consider the following sentence:

select id from collect order by id limit 90000,10; Soon, 0.04 seconds OK. Why not? Because of the id primary key index of course fast. Online changes are:

select id,title from collect where id>=(select id from collect order by id limit 90000,1) limit 10;

This is the result of indexing with id. However, if the problem was that complicated, it would be over. Look at the following sentence

select id from collect where vtype=1 order by id limit 90000,10; slow, took 8-9 seconds!

Here I believe that many people will be like me, there is a sense of collapse! Is vtype indexed? How can it be slow? vtype index is good, you directly select id from collect where vtype=1 limit 1000,10; is very fast, basically 0.05 seconds, but improve 90 times, starting from 90,000, that is 0.05*90=4.5 seconds speed. And the test results 8-9 seconds to an order of magnitude. From here on, someone put forward the idea of dividing tables, which is the same idea as discuz. The train of thought is as follows:

Create an index table: t (id,title,vtype) and set it to a fixed length, then do pagination, paginate the results and then go to collect to find info. Is it feasible? We'll find out by experiment.

100,000 records are stored in t(id,title,vtype), and the data table size is about 20M. with

select id from t where vtype=1 order by id limit 90000,10; Soon. Basically 0.1-0.2 seconds can run. Why is this happening? I guess because collect has too much data, pagination takes a long way. limit is completely dependent on the size of the data table. In fact, this is still a full table scan, just because the amount of data is small, only 100,000 is fast. OK, a crazy experiment, up to a million, test performance.

Add 10 times the data, immediately t table to more than 200 M, and is fixed length. It was still the query statement just now, and the time was 0.1-0.2 seconds to complete! Is there any problem with the submeter performance? Wrong! Because our limit is still 90,000, so hurry up. Give me the big one, 900,000 starts.

select id from t where vtype=1 order by id limit 90000,10; see the result, the time is 1-2 seconds!

why ?? The minute table time is still so long, very depressed! Some people say that the length will improve the performance of limit, at first I also thought, because the length of a record is fixed, mysql should be able to calculate the position of 900,000 ah? However, we overestimated the intelligence of mysql, it is not a business database, it turns out that fixed length and non-fixed length have little effect on limit? No wonder some people say discuz will be slow when it reaches 1 million records, I believe this is true, this has something to do with database design!

Could MySQL not break the 1 million limit? Is it really the limit of 1 million pages?

The answer is NO !! The reason why we can't break through 1 million is because we can't design MySQL. Here's a non-table method, a crazy test! A table to get 1 million records, and 10G database, how to quickly paging!

Well, our test is back to collect table, start test conclusion is: 300,000 data, with sub-table method feasible, more than 300,000 his speed will slow you can not stand! Of course, if you use this method of sub-table + me, it is absolutely perfect. But after using my method, you can solve it perfectly without dividing the table!

The answer: composite index! Once when designing mysql index, I accidentally found that the index name can be arbitrarily selected, and several fields can be selected. What is the use of this? The initial select id from collect order by id limit 90000,10; is so fast because the index is taken, but if you add where, the index is not taken. Search(vtype,id) was added to the index. then tested

select id from collect where vtype=1 limit 90000,10; Very fast! 0.04 seconds to complete!

Test again: select id ,title from collect where vtype=1 limit 90000,10; very sorry, 8-9 seconds, did not go search index!

Test again: search(id,vtype), or select id this statement, also very sorry, 0.5 seconds.

To sum up: If there is a where condition and you want to use limit for the index, you must design an index, put the where in the first place, and the primary key used by limit in the second place, and you can only select the primary key!

Perfect solution to the paging problem. Can quickly return id there is hope to optimize the limit, according to this logic, million level limit should be in 0.0x seconds can be divided. It seems that mysql statement optimization and indexing is very important!

Okay, back to the original question, how do you successfully apply the above research to development quickly? My lightweight framework would be useless if I used composite queries. How troublesome would it be to have to write the pagination string yourself? Here's another example, and the idea comes out:

select * from collect where id in (9000,12,50,7000); 0 seconds to complete!

Mygod, mysql indexes are also valid for in statements! It seems that the Internet said in can not be used index is wrong!

With this conclusion, it can be easily applied to lightweight frameworks:

The code is as follows:

$db=dblink();

$db->pagesize=20;

$sql="select id from collect where vtype=$vtype";

$db->execute($sql);

$strpage=$db->strpage(); //Save paging strings in temporary variables for easy output

while($rs=$db->fetch_array()){

$strid.=$ rs['id']. ',';

}

$strid=substr($strid,0,strlen($strid)-1); //construct id string

$db->pagesize=0; //very critical, in the case of not logging out of the class, the paging will be cleared, so that only one database connection is needed, no need to open again;

$db->execute("select id,title,url,sTime,gTime,vtype,tag from collect where id in ($strid)");

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.