Use of Limit under mysql big data

For me, who has been using Oracle, I was very surprised today. The performance of the same function in MySQL at different orders of magnitude is so different.

　Look at the table ibmng(id,title,info) unique id key index title

　First look at the two statements:

　select * from ibmng limit 1000000,10

　select * from ibmng limit 10,10

　Many People will think that there won’t be much difference, but they are all wrong. The difference is too big. (There may be a slight difference depending on the machine, but it is definitely more than 10 times.) The specific execution time is left to curious students.

Why is this? It’s all offset’s fault!

　If you want to optimize, you can find ways to reduce the offset, as follows:

　Select * From ibmng Where id >=(
　　Select id From ibmng Order By id limit 1000000,1
　) limit 10

　You will definitely see it The problem is, isn't the limit 1000000,1 the same offset and the same size? It definitely cannot be optimized. (But, I’m wrong again, I won’t know the result until after execution!)

　The reason is that id is an index, so it’s fast, so what about the following sql:

　select id from ibmng where title='mysql' order by id limit 1000000,10;

Everyone will guess wrong about this SQL again, and it will be as slow as a snail. (Everyone here will think that the title is indexed, why is this happening!)

Next, everyone executes another SQL as follows:

Select id from ibmng where title='mysql' limit 1000000,10;

After execution, you will The speed of discovery is sousou’s!

　The reason can be seen, it is all due to the use of indexes. If you want to use select id from ibmng where title='mysql' order by id limit 1000000,10; then add a composite index (title,id)!

Note: What follows has nothing to do with limit!

Finally, back to my current scenario, if tens of millions of data are read in batches, it is best not to use limit, but to use the primary key range to judge! (eg: id=1000001)