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

Editor to share with you the example analysis of limit optimization in mysql, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

| | stest | CREATE TABLE `stest` (

`id`int (10) unsigned NOT NULL

`k`int (10) unsigned NOT NULL DEFAULT'0'

`c` char (120) NOT NULL DEFAULT''

`pad`char (60) NOT NULL DEFAULT''

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

The first test statement

Mysql > select * from stest where id > = (select id from stest order by id asc limi

T 900000 limit 1) 50

50 rows in set (2.58 sec)

The second test statement

Mysql > select * from stest order by id asc limit 900000550

50 rows in set (2.53 sec)

Logically speaking, the first query should be faster than the second query. [@ more@]

# establish a test environment:

MYSQL:5 . one. forty

RHEL 5u4

CREATE TABLE `heyftest` (

`id`int (11) NOT NULL AUTO_INCREMENT

`name` varchar (30) DEFAULT NULL

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=4980635 DEFAULT CHARSET=utf8

Insert into heyftest (name) values ('zzzzzzzzzzzzzzzzzzzzzzzzz')

Insert into heyftest (name) select name from heyftest

Repeat many times so that the data achieves:

Root@127.0.0.1: test 12:41:35 > select count (*) from heyftest

+-+

| | count (*) |

+-+

| | 2097408 |

+-+

1 row in set (.54 sec)

# Analysis from the implementation plan:

Explain extended select * from heyftest where id > = (select id from heyftest order by id asc limit 900000 dint 1) limit 50

+-- +

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+-- +

| | 1 | PRIMARY | heyftest | range | PRIMARY | PRIMARY | 4 | NULL | 1048908 | 100.00 | Using where |

| | 2 | SUBQUERY | heyftest | index | NULL | PRIMARY | 4 | NULL | 900001 | 233.09 | Using index |

+-- +

First, through the subquery, find row 900001

Then through the primary key to RANGE access, but here ROWS=1048908, a little do not understand? Because I only want 50 lines.

Root@127.0.0.1: test 12:58:23 > explain extended select * from heyftest order by id asc limit 900000 Jol 50

+-- +

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+-- +

| | 1 | SIMPLE | heyftest | index | NULL | PRIMARY | 4 | NULL | 900050 | 233.08 |

+-- +

ROW=900050, understandably, scans from the first line and adds to the index up to 900, 000, 50.

# analyze from logical reading:

Logical reading, LIO = the difference between two Innodb_buffer_pool_read_requests

This test tries to keep all the contents of the table in INNODB_BUFFER to avoid physical IO, so that the data we get will be more accurate.

So please run: select count (distinct name) from heyftest before testing

Root@127.0.0.1: test 13:23:05 > reset query cache

Query OK, 0 rows affected (0.00 sec)

Root@127.0.0.1: test 13:23:06 > show status like 'Innodb_buffer_pool_read_requests'

+-+ +

| | Variable_name | Value |

+-+ +

| | Innodb_buffer_pool_read_requests | 57953539 | |

+-+ +

1 row in set (0.01 sec)

Root@127.0.0.1: test 13:23:06 > select * from heyftest where id > = (select id from heyftest order by id asc limit 900000 from heyftest where id 1) limit 50

Show status like 'Innodb_buffer_pool_read_requests'

+-- +

| | id | name |

+-- +

| | 2324111 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324113 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324115 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324117 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324119 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324121 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324123 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324125 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324127 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324129 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324131 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324133 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324135 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324137 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324139 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324141 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324143 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324145 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324147 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324149 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324151 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324153 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324155 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324157 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324159 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324161 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324163 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324165 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324167 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324169 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324171 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324173 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324175 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324177 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324179 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324181 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324183 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324185 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324187 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324189 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324191 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324193 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324195 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324197 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324199 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324201 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324203 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324205 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324207 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

| | 2324209 | zzzzzzzzzzzzzzzzzzzzzzzzzz |

+-- +

50 rows in set (0.61 sec)

Root@127.0.0.1: test 13:23:06 > show status like 'Innodb_buffer_pool_read_requests'

+-+ +

| | Variable_name | Value |

+-+ +

| | Innodb_buffer_pool_read_requests | 58856559 | |

+-+ +

1 row in set (0.00 sec)

Root@127.0.0.1: test 13:23:06 > select 58856559-57953539

+-+

| | 57953539-58856559 | |

+-+

| | 903020 |

+-+

1 row in set (0.00 sec)

LIO:903020

We all use the above method to test, and the logical reading of the SQL statement corresponds to the following:

SQL1:select * from heyftest where id > = (select id from heyftest order by id asc limit 900000 dint 1) limit 50

LIO:903020

SQL2:select * from heyftest order by id asc limit 900000 550

LIO:115503

SQL4:select id from heyftest order by id asc limit 900000 1;-result: 2324111

LIO:115497

SQL5:select * from heyftest where id > = 2324111 limit 50

LIO:26

In fact, we think that the ideal plan of SQL1 is = SQL4+SQL5. In fact, even so, 115497 / 26 > 115503, so we should not lose the idea of the landlord first.

And in fact, we saw that MYSQL didn't break up SQL into SQL4,SQL5 as we thought.

Look at LIO:903020 from SQL1 logic reading, from execution plan, ROWS=1048908

The above is all the content of the article "sample Analysis of limit Optimization in mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.