mysql技巧

以下内容来自于mysql经验分享。

一、分页sql的写法

假设，一个公司产品有2000条记录，要分页取其中10个产品，假设访
问公司索引需要50个IO，2条记录需要1个表数据IO
在mysql中采用limit分页
原理：一次性根据过滤条件取出所有字段进行排序返回
select * from t
where thread_id = 771025
and deleted = 0
order by gmt_create asc limit 0, 10;

数据访问开销=索引IO+索引全部记录结果对应的表数据IO

该种写法越翻到后面执行效率越差，时间越长，尤其表数据量很大的时候

或者可以更优化的，利用表的PK
原理：先根据过滤条件取出主键id进行排序，再进行join操作取出其他
相关字段//数据量大的时候，分页时间是否越慢
select t.*from (
select id from t
where thread_id = 771025 and deleted = 0 order by gmt_create asc limit 0, 10) a, t
where a.id = t.id  ;
//假设t表主键是id列，且有覆盖索引secondary key：thread_id+deleted+gmt_create

分页SQL更多的是在业务上的优化。

二、多表关联

驱动表（from的顺序）
select a.*,b.*
from a,b ----这是错误的，b表作为驱动表
where a.id=b.id
and b.time>=‘xxxx’and b.time<=‘xxxx’and a.status=1;
驱动表（紧跟在from后面的表）原则是，结果集较小的表，而不是表较小的表

三、where的顺序
select * from t where id=1 and type=1;
select * from t where type=1 and id=1;
两种写法相同，where是没有顺序的

四、其他

innodb表都需要有一个主键

单表行数不要超过一千万，超过一千万要提前分表；

多条insert拼接成一条提交可以极大提高效率，比如1000条拼成一条；多条相关的更新语句可以放在一个事务中；