mysql中限制优化实例分析

分类：编程技术时间：2024-02-20 15:47 浏览：0 评论：0

小编给大家分享一个MySQL中极限优化的实例分析。相信大多数人对此还不是很了解，所以分享这篇文章供大家参考。希望您读完这篇文章后能有所收获。让我们一起来了解一下吧！

<跨度>
|测试|创建表 `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 '',
`主键 (`id `)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |< br/> 第一条测试语句
mysql> select * from stest where id>=(select id from stest order by id asc limi
t 900000,1) limit 50;
set 50 rows （2.58秒）
第二条测试语句
mysql> select * from stest order by id asc limit 900000,50;
集合中有 50 行（2.53 秒）
从逻辑上讲，第一个查询应该比第二个查询更快。。 [@more@]

# 创建测试环境ent:
MYSQL: 5.1.40
RHEL 5u4

创建表 `heyftest` (
`id ` int(11) NOT NULL AUTO_INCRMENT,
` name` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCRMENT=4980635 DEFAULT CHARSET=utf8

插入 heyftest(name) 值('zzzzzzzzzzzzzzzzzzzzzzzz');

insert into heyftest(name) select name from heyftest;
重复多次，使数据达到：
root@127.0.0.1 : test 12 :41:35> 从 heyftest 中选择 count(*);
+------------+
|计数（*）|
+----------+
| 2097408 |
+----------+
集合中1行（0.54秒）

#执行计划分析：

解释扩展 select * from heyftest where id>=(select id from heyftest order by id asc limit 900000,1) limit 50;
+----+---- ----------+----------+--------+ ---------------+----- ----+---------+-----+------ --+------------+-------- ---+
|编号 |选择类型 |表|类型 |可能的键 |关键| key_len |参考 |行 |过滤|额外的|
+----+-------------+------------+-------- +-------- ----------+----------+----------+------+-------- ---+- ---------+-------------+
| 1 |小学 |最高测试 |范围 |小学 |小学 | 4 |空| 1048908 | 100.00 |使用位置 |
| 2 |子查询|最高测试 |索引 |空|小学 | 4 |空| 900001 | 900001 233.09 | 233.09使用索引 |
+----+----- --------+------------+--------+---- -----------+------ ---+---------+-----+---------+- ---------+-------- -----+

首先通过子查询找到第900001行，
然后通过主键，但是这里ROWS=1048908，我不明白？？？因为我只想要 50 行，

root@127.0.0.1 : test 12:58:23>解释扩展 select * from heyftest order by id asc limit 900000,50;
+----+-------------+----------+--------+--------- -- ---+---------+---------+------+--------+-------- --- +--------+
|编号 |选择类型 |表|类型 |可能的键 |关键| key_len |参考|行 |过滤|额外 |
+----+-------- -----+----------+--------+----------------+--------- +--- ------+------+--------+----------+--------+
| 1 |简单|最高测试 |索引 |空|小学 | 4 |空| 900050 | 900050 233.08 | 233.08 |
+----+------------+---- ------+--------+-------- ---------+---------+--------- +--------+--------+---- ------+--------+
ROW=900050，可以理解，从第一行开始扫描并添加到索引中，直到900000+50

# 从逻辑读分析：

逻辑读，LIO = 两次Innodb_buffer_pool_read_requests的区别
本次测试尽量将表的所有内容保留在INNODB_BUFFER中，避免物理IO，这样数据我们get 会更准确；
所以在测试之前，请运行： select count(distinct name) from heyftest;

root@127.0.0.1 : test 13:23:05>重置查询缓存；
查询正常，0 行受影响（0.00 秒）

root@127.0。 0.1 : 测试 13:23:06> 显示类似 'Innodb_buffer_pool_read_requests' 的状态；
+---------------------------------------- ---+------- ---+
|变量名 --+----------+
| Innodb_buffer_pool_read_requests | Innodb_buffer_pool_read_requests | 57953539 |
+------------------------ ------------+-------- ---+
集合中的 1 行（0.01 秒）

root@127.0.0.1 : test 13:23:06> select * from heyftest where id>=(select id from heyftest order按 id asc limit 900000,1) limit 50;
显示状态，如“Innodb_buffer_pool_read_requests”;
+--- ------+------------- --------------+
|编号 |姓名 ----------+----------------------------+
| 2324111 | 2324111 zzzzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324113 | 2324113 zzzzzzzzzzzzzzzzzzzzzzzz |
| 2324115 | 2324115 zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324117 | 2324117 zzzzzzzzzzzzzzzzzzzzzzzzzzzz |zzzzzz |
| 2324121 | 2324121 zzzzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324123 | 2324123 zzzzzzzzzzzzzzzzzzzzzzzzzzzz | br/>| 2324129 | 2324129 zzzzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324131 | 2324131 zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324133 | 2324133 zzzzzzzzzzzzzzzzzzzzzzzz | 4139 | 4139 zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324141 | 2324141 zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324143 | 2324143 zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324145 | 2324145 zzzzzzzzzzzzzzzzzzzzzzzz | 2324151 | 2324151 zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324153 | 2324153 zzzzzzzzzzzzzzzzzzzzzzzz |
| 2324155 | 2324155 zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324157 | 2324157 zzzzzzzzzzzzzzzzzzzzzzzzzz | 161 | 161 zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324163 | 2324163 zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324165 | 2324165 zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324167 | 2324167 zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324169 | 2324169 zzzzzzzzzzzzzzzzzzzzzzzzzzzz| 324173 | 324173 zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324175 | 2324175 zzzzzzzzzzzzzzzzzzzzzzzz |
| 2324177 | 2324177 zzzzzzzzzzzzzzzzzzzzzzzz |
| 2324179 | 2324179 zzzzzzzzzzzzzzzzzzzzzzzzzz | zzzzzzzzzzzzzzzzzz |
| 2324185 | 2324185 zzzzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324187 | 2324187 zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324189 | 2324189 zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324191 | 2324191 zzzzzzzzzzzzzzzzzzzzzzzzzz| 4195 | 4195 zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324197 | 2324197呜呜呜zzzzzzzzzzzzzzzz |
| 2324199 | 2324199 zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324201 | 2324201 zzzzzzzzzzzzzzzzzzzzzzzzzzzz | zzzzzzzzzzzzzzzz |
| 2324207 | 2324207 zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324209 | 2324209 zzzzzzzzzzzzzzzzzzzzzzzzzzzz |
+---------+----------------------------------------+
集合中的 50 行（0.61 秒）

root@127.0.0.1 : test 13:23:06> 显示类似“Innodb_buffer_pool_read_requests”的状态；
+-------- - ------------------------------------------+
|变量名 |价值|
+--------------------------------+---------- +
| Innodb_buffer_pool_read_requests | Innodb_buffer_pool_read_requests | 58856559 |
+--------------------------------+--- ----- --+
集合中的 1 行（0.00 秒）

root@127.0.0.1 : test 13:23:06> select 58856559-57953539;
+ ----- -------------+
| 57953539-58856559 |
+---------------- ---+
| 903020 |
+-------------------+
1 行一组（0.00 秒）

LIO：903020< /p>

我们都用上面的方法来测试，SQL语句对应的逻辑读取如下：
SQL1: select * from heyftest where id>=(select id from heyftest order by id asc limit 900000,1) limit 50;
LIO: 903020

SQL2 : select * from heyftest order by id asc limit 900000,50;
LIO: 115503

SQL4: select id from heyftest order by id asc limit 900000,1; -- 结果：2324111
LIO: 115497

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

事实上，我们认为SQL1的理想计划是=SQL4+SQL5。事实上，即使是这样，115497+26 > 115503，所以我们在这里拒绝原发者的想法。

其实我们看到MYSQL并没有像我们想象的那样将SQL拆分成SQL4和SQL5。
从SQL1的逻辑读取，我们可以看到LIO: 903020。从执行计划来看，ROWS = 1048908

以上就是《MySQL中极限优化示例分析》一文的全部内容，感谢您的阅读！相信大家都有一定的了解，希望内容能够对大家有所帮助。红色对大家有帮助。如果您想了解更多知识，请关注行业资讯频道！

limit