求MYSQL 负载优化方案。

Question

求MYSQL 负载优化方案。（PHP+mysql开发的）
比如 我有个表 table 有大概  50W 左右的记录，
使用limit 实现PHP的分页时候，就非常卡，经常脚本超时后来发现了一种方案 ，用比较法 
  select * from table 
      where id >= ( select id from table limit 1,1) limit 20
这种
但是这种试过了 ，，如果进行复杂的where 或者非索引的字段进行 order by 操作 性能照样 差得很
不知道，哪位高手有好的方案，？
用存储过程实现分页？还是其他什么呢？？

Answer 1

用了10W记录的一个表做了测试。 直接用 limit 应该还可以啊 (id 上有索引)你具体的语句是什么？问题在哪里？
mysql> select SQL_NO_CACHE id,col from t1 order by id limit 00000,2;
+----+------+
| id | col  |
+----+------+
|  1 |    1 |
|  2 |    2 |
+----+------+
2 rows in set (0.00 sec)mysql> select SQL_NO_CACHE id,col from t1 order by id limit 10000,2;
+-------+-------+
| id    | col   |
+-------+-------+
| 10001 | 10001 |
| 10002 | 10002 |
+-------+-------+
2 rows in set (0.01 sec)mysql> select SQL_NO_CACHE id,col from t1 order by id limit 20000,2;
+-------+-------+
| id    | col   |
+-------+-------+
| 20001 | 20001 |
| 20002 | 20002 |
+-------+-------+
2 rows in set (0.05 sec)mysql> select SQL_NO_CACHE id,col from t1 order by id limit 30000,2;
+-------+-------+
| id    | col   |
+-------+-------+
| 30001 | 30001 |
| 30002 | 30002 |
+-------+-------+
2 rows in set (0.03 sec)

Answer 2

谢谢 
如果 你将 limit 500,50
后面那个 offset 值设置在大点就很慢了 哦
我是在研究 这个 PHP+MYSQL 分页的时候 的执行效率

Answer 3

测试如下. 好象没有你所说的问题。 建议你给出你的查询语句，否则别人无法模拟理解你的问题。mysql> select SQL_NO_CACHE id,col from t1 order by id limit 000000,50;
+----+------+
| id | col  |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 || 50 |   50 |
+----+------+
50 rows in set (0.00 sec)mysql> select SQL_NO_CACHE id,col from t1 order by id limit 10000,50;
+-------+-------+
| id    | col   |
+-------+-------+
| 10001 | 10001 |
| 10002 | 10002 |
| 10003 | 10003 || 10050 | 10050 |
+-------+-------+
50 rows in set (0.03 sec)mysql> select SQL_NO_CACHE id,col from t1 order by id limit 20000,50;
+-------+-------+
| id    | col   |
+-------+-------+
| 20001 | 20001 |
| 20002 | 20002 |
| 20003 | 20003 || 20050 | 20050 |
+-------+-------+
50 rows in set (0.02 sec)mysql> select SQL_NO_CACHE id,col from t1 order by id limit 30000,50;
+-------+-------+
| id    | col   |
+-------+-------+
| 30001 | 30001 |
| 30002 | 30002 |
| 30003 | 30003 || 30050 | 30050 |
+-------+-------+
50 rows in set (0.05 sec)mysql> select SQL_NO_CACHE id,col from t1 order by id limit 70000,50;
+-------+-------+
| id    | col   |
+-------+-------+
| 70001 | 70001 |
| 70002 | 70002 |
| 70003 | 70003 || 70050 | 70050 |
+-------+-------+
50 rows in set (0.03 sec)mysql>

Answer 4

1、没有where条件的可以这样
您可以建立一个分页表,插入页面(假设一页20条记录)
CREATE TABLE `page` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `page` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `page` (`page`)
) DEFAULT CHARSET=utf8;insert into page(id,page) select id,ceil(id/20) from your_tbl;# 这样检索,如page=1000
select your_tbl.* from your_tbl join page on your_tbl.id=page.id where page=1000;2、有where条件的推荐您使用sphinx做检索，然后和您的表连接
http://www.sphinxsearch.com/wiki/doku.php?id=sphinx_chinese_tutorial

Answer 5

直接用LIMIT，记录多、用户多时，速度肯定有影响，楼上方法值得试试

Answer 6

试下这样的，假如你id是主键的话， 估计速度有不少提升：
select * from table 
      where id in ( select id from table order by id limit 500,50)

Answer 7

50W条记录还用LIKE啊？过20W就应该考虑使用搜索引擎啊，否则稍微复杂点检索不死才怪~~
到sphinx.com官网上仔细研究下，百万级数据检索在毫秒之内！

Answer 8

哎，看来LZ是个开发人员 可能还是一直搞Oracle方面的

Answer 9

如果table表中的有记录被删掉，那这个page还有意义吗？
还是说这种方法只适用于那些不能删除的数据？

Answer 10

sphinx   这东西似乎是好东西。。alandy这位兄弟，老在推荐。。楼主的 where 如果  有like  可以考虑。。下。。

Answer 11

楼主用的什么数据引擎呢？手册里的提到的优化注意事项不知道看过没有。
myisam引擎最好不要把varchar字段与非varchar字段放在一个表里，分开放，手册里这样说的。
查询中的where条件，把最能筛选掉记录的条件放在最后面。我用2000w条记录做过limit 6000000,1000的测试，虽然比较慢，但还不致于超时。处于中间的数据比较慢，处于头和尾的比较快。
复杂的where和非索引的order by 必然是慢，估计没办法。

Answer 12

LIMIT 在大的表中寻找对应的指针将会非常的慢。 所以你要设计好对应的摘要表： 比如， 页码表，目表等等。
我博客上有例子。