MySQL 学习笔记

1、从一个表中查询数据插入另一个表

insert into table1 (imgurl,title,author,describes,postuser,posttime,modifytime,isrecycle) 
(SELECT imgurl,title,author,describes,postuser,posttime,modifytime,isrecycle FROM table1);

 

2、百万级分页优化

#一般语句
SELECT * FROM table ORDER BY id LIMIT 1000000, 10; 

#优化1
SELECT * FROM table WHERE id >= (SELECT id FROM table LIMIT 1000000, 1) LIMIT 10; 

#优化2(连续id)
SELECT * FROM table WHERE id BETWEEN 1000000 AND 1000010; 

#优化3（跳跃id，先找到id）
SELECT * FROM table WHERE id IN(10000, 100000, 1000000...); 

参考：MySQL 百万级分页优化(Mysql千万级快速分页).http://www.jb51.net/article/31868.htm.2013-07-11