Mysql优化之延迟索引和分页优化_MySQL
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Mysql优化之延迟索引和分页优化_MySQL,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含2762字,纯文字阅读大概需要4分钟。
内容图文
![Mysql优化之延迟索引和分页优化_MySQL](/upload/InfoBanner/zyjiaocheng/580/87405506b1fc4907a6a965bb76474046.jpg)
分页是一个很常见功能,select ** from tableName limit ($page - 1 ) * $n ,$n
通过一个存储过程插入10000条数据进行测试:
create table smth1 ( id int auto_increment , ver int(11) default null, content varchar(1000) not null, intro varchar(1000) not null, primary key(id), key idver(id,ver) )engine = innodb default charset = utf8; create procedure smthTest1() begin declare num int default 100001; while num < 1000000 do set num := num +1; insert into smth1 values (num ,num,'我是*****','我是谁'); end while ; end;
查询:
mysql> show profiles; +----------+------------+----------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+----------------------------------------------+ | 1 | 0.002006 | select id ,content from smth1 limit 1000,10 | | 2 | 0.030106 | select id ,content from smth1 limit 5000,10 | | 3 | 0.042428 | select id ,content from smth1 limit 9000,10 | | 4 | 0.01297225 | select id ,content from smth1 limit 10000,10 | | 5 | 0.13077625 | select id ,content from smth1 limit 20000,10 |可见随着查询$page 变大,时间会越来越大!
怎样避免这种情况?
一般我们数据库里面数据都不会直接删除,数据时很宝贵的,不舍得删除,另一方便能提高查询数据
先利用索引查询出来数据,再进行联合查询不就行了
select C.id,C.content from smth1 C inner join ( select id from smth1 where id > 1000 limit 10 ) as t on C.id = t.id ; select C.id,C.content from smth1 C inner join ( select id from smth1 where id > 5000 limit 10 ) as t on C.id = t.id ; select C.id,C.content from smth1 C inner join ( select id from smth1 where id > 9000 limit 10 ) as t on C.id = t.id ; select C.id,C.content from smth1 C inner join ( select id from smth1 where id > 10000 limit 10 ) as t on C.id = t.id ; select C.id,C.content from smth1 C inner join ( select id from smth1 where id > 20000 limit 10 ) as t on C.id = t.id ; 进行执行计划分析,没有一个大于1s的 11 | 0.04538625 | select C.id,C.content from smth1 C inner join ( select id from smth1 where id > 5000 limit 10 ) as t on C.id = t.id | | 12 | 0.023278 | select C.id,C.content from smth1 C inner join ( select id from smth1 where id > 9000 limit 10 ) as t on C.id = t.id | | 13 | 0.02320425 | select C.id,C.content from smth1 C inner join ( select id from smth1 where id > 10000 limit 10 ) as t on C.id = t.id | | 14 | 0.001938 | select C.id,C.content from smth1 C inner join ( select id from smth1 where id > 20000 limit 10 ) as t on C.id = t.id |此外,还会想到用in来查询而不是子查询,为什么不用in,使用in会先查询出来一条id,之后再去和下面进行匹配,会进行smth1进行全表扫描!
内容总结
以上是互联网集市为您收集整理的Mysql优化之延迟索引和分页优化_MySQL全部内容,希望文章能够帮你解决Mysql优化之延迟索引和分页优化_MySQL所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。