MyException - 我的异常网
当前位置:我的异常网» MySQL » mysql 分页话语优化

mysql 分页话语优化

www.MyException.Cn  网友分享于:2018-03-12  浏览:0次
mysql 分页语句优化

 测试数据表结构

 

   现在有两张表   customer,以及order_test, customerId的主键是order_test的外键

   

CREATE TABLE `customer` (
  `customerId` int(11) NOT NULL AUTO_INCREMENT,
  `phone` varchar(11) NOT NULL COMMENT '手机号',
  `password` varchar(20) NOT NULL COMMENT '密码',
  
  PRIMARY KEY (`customerId`)
 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8
CREATE TABLE `order_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `orderNo` char(17) COLLATE utf8_bin NOT NULL,
  `customerId` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `111_idx` (`customerId`),
  CONSTRAINT `111` FOREIGN KEY (`customerId`) REFERENCES `customer` (`customerId`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=100880001 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

 

   customer表又1亿多条数据,order_test有9000w条数据.

 

   下面针对上面两张表做一些常用的语句进行优化

 

 limit 分页优化方法

     explain用法详见: http://zcf9916.iteye.com/blog/2409731

    1.假设现在要查处customer表的按customerId排序的第500000到500200中的200条数据

      正常的写法就是这样

      SELECT customerId FROM customer  LIMIT 500000,200;   执行时间是0.108秒

      这个语句会查出前500200条数据,然后再丢弃前500000条数据,所以速度很慢

      执行explain + sql,可以看到type=all,进行了全表扫描 

  1. id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra | 
  2. +----+-------------+---------+------+---------------+------+---------+------+--------+----------+-------+ 
  3. |  1 | SIMPLE      | customer | ALL  | NULL          | NULL | NULL    | NULL | 99808800 |   100.00 |       |

 

     如果limit 后面只有一个数字,limit 200,那么数据库只会扫描这两百条,所以我们可以从这里入手.

 

 

    1.  假设id是递增的,且数据没有删除,

     建立主键或唯一索引, 利用索引(假设每页10条)

    ---语句样式:mysql中,可用如下方法: SELECT * FROM customer WHERE customerId > 500000 LIMIT 200,执行时间0.0032秒

    ---适应场景: 适用于数据量多的情况(元组数上万)
    ---原因: 索引扫描,速度会很快. 

    执行explain + sql,可以看到type=range, possible_keys=primary 说明使用了主键索引

  •     (ps:  range:只检索给定范围的行,使用一个索引来选择行。)
  1. id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra | 
  2. +----+-------------+---------+------+---------------+------+---------+------+--------+----------+-------+ 
  3. |  1 | SIMPLE      | customer | range  | PRIMARY          | PRIMARY | 4    | NULL | 49808800 |   100.00 |       

 

   2.假设id是递增的,且有数据被删除过,假设第499800-499999数据被删除过,那么就不能单纯的靠customerId > 500000来定位了,因为实际id范围是50020-500400了.

    ---语句样式:可用如下方法: SELECT * FROM customer WHERE customerId >= 

   (SELECT customerId FROM customer order by customerId  LIMIT 500000,1)  ORDER BY customerId  LIMIT 200

   

  1. id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra | 
  2. +----+-------------+---------+------+---------------+------+---------+------+--------+----------+-------+ 
  3. |  1 | PRIMARY      | customer | range  | PRIMARY   | PRIMARY | 4    | NULL | 49808800 |   100.00 |   using where    
  4. |  2 | SUBQUERY      | customer | index  |          | PRIMARY | 4    | NULL | 500001 |   100.00 |   using index

 

     

  PS:如果limit offset,数量.  如果offset值过大,那么查询时间还是会很长,所以当offset超过一定量的时候,最好就不做处理。   

  PS:在实验中发现 。SELECT customerId FROM customer order by customerId  LIMIT 0,20;和SELECT customerId FROM customer   LIMIT 0,20;虽然cutomer是递增的,但是两句话的结果可能不同,因为不加order就会根据索引的指针顺序去访问,不一定是按顺序来的.

 

 

文章评论

要嫁就嫁程序猿—钱多话少死的早
要嫁就嫁程序猿—钱多话少死的早
如何成为一名黑客
如何成为一名黑客
5款最佳正则表达式编辑调试器
5款最佳正则表达式编辑调试器
团队中“技术大拿”并非越多越好
团队中“技术大拿”并非越多越好
中美印日四国程序员比较
中美印日四国程序员比较
那些性感的让人尖叫的程序员
那些性感的让人尖叫的程序员
代码女神横空出世
代码女神横空出世
为啥Android手机总会越用越慢?
为啥Android手机总会越用越慢?
不懂技术不要对懂技术的人说这很容易实现
不懂技术不要对懂技术的人说这很容易实现
Web开发者需具备的8个好习惯
Web开发者需具备的8个好习惯
亲爱的项目经理,我恨你
亲爱的项目经理,我恨你
程序员必看的十大电影
程序员必看的十大电影
做程序猿的老婆应该注意的一些事情
做程序猿的老婆应该注意的一些事情
10个帮程序员减压放松的网站
10个帮程序员减压放松的网站
10个调试和排错的小建议
10个调试和排错的小建议
老美怎么看待阿里赴美上市
老美怎么看待阿里赴美上市
鲜为人知的编程真相
鲜为人知的编程真相
为什么程序员都是夜猫子
为什么程序员都是夜猫子
 程序员的样子
程序员的样子
程序员周末都喜欢做什么?
程序员周末都喜欢做什么?
程序员眼里IE浏览器是什么样的
程序员眼里IE浏览器是什么样的
漫画:程序员的工作
漫画:程序员的工作
我的丈夫是个程序员
我的丈夫是个程序员
旅行,写作,编程
旅行,写作,编程
2013年中国软件开发者薪资调查报告
2013年中国软件开发者薪资调查报告
当下全球最炙手可热的八位少年创业者
当下全球最炙手可热的八位少年创业者
程序员的一天:一寸光阴一寸金
程序员的一天:一寸光阴一寸金
老程序员的下场
老程序员的下场
Web开发人员为什么越来越懒了?
Web开发人员为什么越来越懒了?
60个开发者不容错过的免费资源库
60个开发者不容错过的免费资源库
“肮脏的”IT工作排行榜
“肮脏的”IT工作排行榜
Google伦敦新总部 犹如星级庄园
Google伦敦新总部 犹如星级庄园
程序员都该阅读的书
程序员都该阅读的书
那些争议最大的编程观点
那些争议最大的编程观点
看13位CEO、创始人和高管如何提高工作效率
看13位CEO、创始人和高管如何提高工作效率
程序猿的崛起——Growth Hacker
程序猿的崛起——Growth Hacker
如何区分一个程序员是“老手“还是“新手“?
如何区分一个程序员是“老手“还是“新手“?
每天工作4小时的程序员
每天工作4小时的程序员
2013年美国开发者薪资调查报告
2013年美国开发者薪资调查报告
写给自己也写给你 自己到底该何去何从
写给自己也写给你 自己到底该何去何从
程序员和编码员之间的区别
程序员和编码员之间的区别
聊聊HTTPS和SSL/TLS协议
聊聊HTTPS和SSL/TLS协议
Java 与 .NET 的平台发展之争
Java 与 .NET 的平台发展之争
我是如何打败拖延症的
我是如何打败拖延症的
程序员最害怕的5件事 你中招了吗?
程序员最害怕的5件事 你中招了吗?
程序员的鄙视链
程序员的鄙视链
编程语言是女人
编程语言是女人
科技史上最臭名昭著的13大罪犯
科技史上最臭名昭著的13大罪犯
十大编程算法助程序员走上高手之路
十大编程算法助程序员走上高手之路
软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有