search
HomeDatabaseMysql Tutorial如何优化Mysql千万级快速分页_MySQL

bitsCN.com

MySQL数据库优化处理实现千万级快速分页分析,来看下吧。
数据表 collect ( id, title ,info ,vtype) 就这4个字段,其中 title 用定长,info 用text, id 是逐渐,vtype是tinyint,vtype是索引。这是一个基本的新闻系统的简单模型。现在往里面填充数据,填充10万篇新闻。
最后collect 为 10万条记录,数据库表占用硬盘1.6G。OK ,看下面这条sql语句:
select id,title from collect limit 1000,10; 很快;基本上0.01秒就OK,再看下面的
select id,title from collect limit 90000,10; 从9万条开始分页,结果?
8-9秒完成,my god 哪出问题了????其实要优化这条数据,网上找得到答案。看下面一条语句:
select id from collect order by id limit 90000,10; 很快,0.04秒就OK。 为什么?因为用了id主键做索引当然快。网上的改法是:
select id,title from collect where id>=(select id from collect order by id limit 90000,1) limit 10;
这就是用了id做索引的结果。可是问题复杂那么一点点,就完了。看下面的语句
select id from collect where vtype=1 order by id limit 90000,10; 很慢,用了8-9秒!
到了这里我相信很多人会和我一样,有崩溃感觉!vtype 做了索引了啊?怎么会慢呢?vtype做了索引是不错,你直接 select id from collect where vtype=1 limit 1000,10; 是很快的,基本上0.05秒,可是提高90倍,从9万开始,那就是0.05*90=4.5秒的速度了。和测试结果8-9秒到了一个数量级。从这里开始有人 提出了分表的思路,这个和discuz 论坛是一样的思路。思路如下:
建一个索引表: t (id,title,vtype) 并设置成定长,然后做分页,分页出结果再到 collect 里面去找info 。 是否可行呢?实验下就知道了。
10万条记录到 t(id,title,vtype) 里,数据表大小20M左右。用
select id from t where vtype=1 order by id limit 90000,10; 很快了。基本上0.1-0.2秒可以跑完。为什么会这样呢?我猜想是因为collect 数据太多,所以分页要跑很长的路。limit 完全和数据表的大小有关的。其实这样做还是全表扫描,只是因为数据量小,只有10万才快。OK, 来个疯狂的实验,加到100万条,测试性能。
加了10倍的数据,马上t表就到了200多M,而且是定长。还是刚才的查询语句,时间是0.1-0.2秒完成!分表性能没问题?错!因为我们的limit还是9万,所以快。给个大的,90万开始
select id from t where vtype=1 order by id limit 900000,10; 看看结果,时间是1-2秒!
why ?? 分表了时间还是这么长,非常之郁闷!有人说定长会提高limit的性能,开始我也以为,因为一条记录的长度是固定的,mysql 应该可以算出90万的位置才对啊? 可是我们高估了mysql 的智能,他不是商务数据库,事实证明定长和非定长对limit影响不大? 怪不得有人说 discuz到了100万条记录就会很慢,我相信这是真的,这个和数据库设计有关!
难道MySQL 无法突破100万的限制吗???到了100万的分页就真的到了极限???
答案是: NO !!!! 为什么突破不了100万是因为不会设计mysql造成的。下面介绍非分表法,来个疯狂的测试!一张表搞定100万记录,并且10G 数据库,如何快速分页!
好了,我们的测试又回到 collect表,开始测试结论是: 30万数据,用分表法可行,超过30万他的速度会慢道你无法忍受!当然如果用分表+我这种方法,那是绝对完美的。但是用了我这种方法后,不用分表也可以完美解决!
答案就是:复合索引! 有一次设计mysql索引的时候,无意中发现索引名字可以任取,可以选择几个字段进来,这有什么用呢?开始的select id from collect order by id limit 90000,10; 这么快就是因为走了索引,可是如果加了where 就不走索引了。抱着试试看的想法加了 search(vtype,id) 这样的索引。然后测试
select id from collect where vtype=1 limit 90000,10; 非常快!0.04秒完成!
再测试: select id ,title from collect where vtype=1 limit 90000,10; 非常遗憾,8-9秒,没走search索引!
再测试:search(id,vtype),还是select id 这个语句,也非常遗憾,0.5秒。
综上:如果对于有where 条件,又想走索引用limit的,必须设计一个索引,将where 放第一位,limit用到的主键放第2位,而且只能select 主键!
完美解决了分页问题了。可以快速返回id就有希望优化limit , 按这样的逻辑,百万级的limit 应该在0.0x秒就可以分完。看来mysql 语句的优化和索引时非常重要的!
好了,回到原题,如何将上面的研究成功快速应用于开发呢?如果用复合查询,我的轻量级框架就没的用了。分页字符串还得自己写,那多麻烦?这里再看一个例子,思路就出来了:
select * from collect where id in (9000,12,50,7000); 竟然 0秒就可以查完!
mygod ,mysql 的索引竟然对于in语句同样有效!看来网上说in无法用索引是错误的!
有了这个结论,就可以很简单的应用于轻量级框架了:
代码如下:
$db=dblink();
$db->pagesize=20;
$sql="select id from collect where vtype=$vtype";
$db->execute($sql);
$strpage=$db->strpage(); //将分页字符串保存在临时变量,方便输出
while($rs=$db->fetch_array()){
$strid.=$rs['id'].',';
}
$strid=substr($strid,0,strlen($strid)-1); //构造出id字符串
$db->pagesize=0; //很关键,在不注销类的情况下,将分页清空,这样只需要用一次数据库连接,不需要再开;
$db->execute("select id,title,url,sTime,gTime,vtype,tag from collect where id in ($strid)");
fetch_array()): ?>


 
 
 
 
 
 
 



echo $strpage;
通过简单的变换,其实思路很简单:1)通过优化索引,找出id,并拼成 "123,90000,12000" 这样的字符串。2)第2次查询找出结果。
小小的索引+一点点的改动就使mysql 可以支持百万甚至千万级的高效分页!
通过这里的例子,我反思了一点:对于大型系统,PHP千万不能用框架,尤其是那种连sql语句都看不到的框架!因为开始对于我的轻量级框架都差点崩 溃!只适合小型应用的快速开发,对于ERP,OA,大型网站,数据层包括逻辑层的东西都不能用框架。如果程序员失去了对sql语句的把控,那项目的风险将 会成几何级数增加!尤其是用mysql 的时候,mysql 一定需要专业的dba 才可以发挥他的最佳性能。一个索引所造成的性能差别可能是上千倍!
PS: 经过实际测试,到了100万的数据,160万数据,15G表,190M索引,就算走索引,limit都得0.49秒。所以分页最好别让别人看到10万条以后的数据, 要不然会很慢!就算用索引。经过这样的优化,mysql到了百万级分页是个极限!但有这样的成绩已经很不错,如果你是用sqlserver肯定卡死!而 160万的数据用 id in (str) 很快,基本还是0秒。如果这样,千万级的数据,mysql应该也很容易应付。

作者“ERDP技术架构”

bitsCN.com
Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
理解SpringBoot和SpringMVC之间的差异及比较理解SpringBoot和SpringMVC之间的差异及比较Dec 29, 2023 am 09:20 AM

对比SpringBoot与SpringMVC,了解它们的差异随着Java开发的不断发展,Spring框架已经成为了许多开发人员和企业的首选。在Spring的生态系统中,SpringBoot和SpringMVC是两个非常重要的组件。虽然它们都是基于Spring框架的,但在功能和使用方式上却有一些区别。本文将重点对比一下SpringBoot与Sprin

WordPress 网站搭建指南:快速搭建个人网站WordPress 网站搭建指南:快速搭建个人网站Mar 04, 2024 pm 04:39 PM

WordPress网站搭建指南:快速搭建个人网站随着数字化时代的到来,拥有一个个人网站已经成为了一种时尚和必要。而WordPress作为最受欢迎的网站搭建工具,让搭建个人网站变得更加容易和便捷。本文将为大家提供一个快速搭建个人网站的指南,包含具体的代码示例,希望可以帮助到想要拥有自己网站的朋友们。第一步:购买域名和主机在开始搭建个人网站之前,首先要购买自己

Vue3中的生命周期函数:快速掌握Vue3的生命周期Vue3中的生命周期函数:快速掌握Vue3的生命周期Jun 18, 2023 am 08:20 AM

Vue3是目前前端界最热门的框架之一,而Vue3的生命周期函数是Vue3中非常重要的一部分。Vue3的生命周期函数可以让我们实现在特定的时机触发特定的事件,增强了组件的高度可控性。本文将从Vue3的生命周期函数的基本概念、各个生命周期函数的作用和使用方法以及实现案例等方面进行详细探究和讲解,帮助读者快速掌握Vue3的生命周期函数。一、Vue3的生命周期函数的

win10电脑怎么快速切屏win10电脑怎么快速切屏Jul 10, 2023 am 08:21 AM

电脑怎么切屏?在使用电脑的时候,有的朋友会使用两个甚至三个显示屏,但是在使用的时候,就会遇到需要切换屏幕的问题,那么电脑怎么切屏呢?一些朋友不知道电脑快速切屏方法,所以本期将教大家win10电脑怎么快速切屏。win10电脑怎么快速切屏?具体的方法如下:1、外接显示屏以后,同时按下【Fn】+【F4】或者【win】+【P】即可选择外接显示器。2、第二种方法是,在桌面空白处鼠标右键,然后选择【屏幕分辨率】。3、然后在【多显示器】中,就能够切换屏幕了。以上就是小编带来的win10电脑怎么快速切屏的全部内

分享PyCharm中快速注释代码的技巧,提高工作效率分享PyCharm中快速注释代码的技巧,提高工作效率Jan 04, 2024 pm 12:02 PM

效率提升!PyCharm中快速注释代码的方法分享在日常的软件开发工作中,我们经常需要注释掉一部分代码进行调试或者调整。如果手动逐行添加注释,这无疑会增加我们的工作量和耗费时间。而PyCharm作为一款强大的Python集成开发环境,提供了快速注释代码的功能,大大提升了我们的开发效率。本文将分享一些在PyCharm中快速注释代码的方法,并提供具体的代码示例。单

win7如何快速截屏win7如何快速截屏Jun 29, 2023 am 11:19 AM

win7如何快速截屏?win7系统之中有着很多便捷操作功能,可以为各位提供非常多样化的便捷服务。很多win7系统的用户在使用电脑的过程中,想要通过win7系统之中的快捷键进行截屏,但是却不清楚具体的快捷键是哪些,因此无法正常使用,那么,这些快捷截屏键究竟是哪些呢?下面小编就为各位带来win7快速截屏键介绍。win7快速截屏键介绍1、按Prtsc键截图这样获取的是整个电脑屏幕的内容,按Prtsc键后,可以直接打开画图工具,接粘贴使用。也可以粘贴在QQ聊天框或者Word文档中,之后再选择保存即可。2

前端开发中sessionStorage的优点及应用案例分析前端开发中sessionStorage的优点及应用案例分析Jan 11, 2024 pm 02:51 PM

sessionStorage在前端开发中的优势与应用案例分析随着Web应用的发展,前端开发的需求也越来越多样化。前端开发人员需要使用各种工具和技术来提高用户体验,其中,sessionStorage是一个非常有用的工具。本文将介绍sessionStorage在前端开发中的优势,以及几个具体的应用案例。sessionStorage是HTML5提供的一种本地存储方

VUE3初学者必备的快速开发入门指南VUE3初学者必备的快速开发入门指南Jun 15, 2023 pm 04:38 PM

VUE3初学者必备的快速开发入门指南Vue是一款流行的JavaScript框架,它的易用性、高度定制性和快速开发模式使得它在前端开发中广受欢迎。而最新的Vue3则推出了更多强大的特性,包括性能优化、TypeScript支持、CompositionAPI以及更好的自定义渲染器等等。本篇文章将为Vue3初学者提供一份快速开发入门指南,帮助你快速上手Vue3开发

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
1 months agoBy尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

SAP NetWeaver Server Adapter for Eclipse

SAP NetWeaver Server Adapter for Eclipse

Integrate Eclipse with SAP NetWeaver application server.

MinGW - Minimalist GNU for Windows

MinGW - Minimalist GNU for Windows

This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.

VSCode Windows 64-bit Download

VSCode Windows 64-bit Download

A free and powerful IDE editor launched by Microsoft

MantisBT

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

mPDF

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),