怎么增长MySQL Limit查询的性能

发布时间：2022-06-15 14:25:09 所属栏目：MySql教程来源：互联网

导读：怎么提高MySQL Limit查询的性能?我们主要是在mysql limit上下功夫了,当然还有其它的像对数据表,数据库服务器配置等,但我们作为程序只只要在mysql查询语句的性能上进行优化即可了. 有个几千万条记录的表on MySQL 5.0.x,现在要读出其中几十万万条左右的记录,常

　　怎么提高MySQL Limit查询的性能?我们主要是在mysql limit上下功夫了,当然还有其它的像对数据表,数据库服务器配置等,但我们作为程序只只要在mysql查询语句的性能上进行优化即可了.

　　有个几千万条记录的表on MySQL 5.0.x,现在要读出其中几十万万条左右的记录,常用方法,依次循环,代码如下:

　　调用如下代码:

　　explain select * from mytable where index_col = xxx limit offset, limit;

　　显示 type = ALL

　　在 MySQL optimization 的文档写到"All"的解释:

　　A full table scan is done for each combination of rows from the previous tables. This is

　　SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

　　LIMIT子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT接受一个或两个数字参数。参数必须是一个整数常量。

　　如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)。

　　为了与 PostgreSQL 兼容,MySQL 也支持句法：LIMIT # OFFSET #,代码如下:

　　 mysql> SELECT * FROM table LIMIT 5,10; //检索记录行6-15
　　 //为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为-1
　　代码如下复制代码
　　 mysql> SELECT * FROM table LIMIT 95,-1; //检索记录行96-last
　　   //phpfensi.com
　　 //如果只给定一个参数，它表示返回最大的记录行数目，换句话说，LIMIT n 等价于 LIMIT 0,n
　　代码如下复制代码
　　 mysql> SELECT * FROM table LIMIT 5;
　　//检索前5个记录行
　　MySQL的limit给分页带来了极大的方便,但数据量一大的时候,limit的性能就急剧下降,同样是取10条数据,下面两句就不是一个数量级别的,代码如下:

　　select * from table limit 10000,10

　　select * from table limit 0,10

　　文中不是直接使用limit，而是首先获取到offset的id然后直接使用limit size来获取数据,根据他的数据,明显要好于直接使用limit。

　　这里我具体使用数据分两种情况进行测试,offset比较小的时候,代码如下:
　　 select * from table limit 10000,10
　　 //多次运行，时间保持在0.0187左右
　　
　　 Select * From table Where vid ＞=(Select vid From table Order By vid limit 10000,1) limit 10
　　//多次运行，时间保持在0.0061左右，只有前者的1/3。可以预计offset越大，后者越优
　　下面我们来看个mysql千万级数据分页的方法,也是基于limit的,我们来做一个测试ipdatas表,代码如下:

　　CREATE TABLE `ipdatas` (
　　   `id` INT(11) NOT NULL AUTO_INCREMENT,
　　   `uid` INT(8) NOT NULL DEFAULT '0',
　　   `ipaddress` VARCHAR(50) NOT NULL,
　　   `source` VARCHAR(255) DEFAULT NULL,
　　   `track` VARCHAR(255) DEFAULT NULL,
　　   `entrance` VARCHAR(255) DEFAULT NULL,

　　ipdatas.MYD 3.99 GB (4,288,979,008 字节)

　　ipdatas.MYI 1.28 GB (1,377,600,512 字节)

　　这里面我要说的是如果真的是大数据如果时间需要索引还是最好改成数字字段,索引的大小和查询速度都比时间字段可观.

　　步入正题:

　　1.全表搜索

　　返回结构是67015297条数据,代码如下:

　　SELECT COUNT(id) FROM ipdatas;
　　SELECT COUNT(uid) FROM ipdatas;
　　SELECT COUNT(*) FROM ipdatas;
　　首先这两个全表数据查询速度很快,mysql中包含数据字典应该保留了数据库中的最大条数.

　　查询索引条件,代码如下:

　　SELECT COUNT(*) FROM ipdatas WHERE uid=1;   返回结果时间:2分31秒594
　　SELECT COUNT(id) FROM ipdatas WHERE uid=1; 返回结果时间:1分29秒609
　　SELECT COUNT(uid) FROM ipdatas WHERE uid=1; 返回结果时间:2分41秒813
　　第二次查询都比较快因为mysql中是有缓存区的所以增大缓存区的大小可以解决很多查询的优化,真可谓缓存无处不在啊在程序开发中也是层层都是缓存.

（编辑：常州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!