自增长id,通常作为唯一标识。可以用于分页查询的优化。
这里记录一下自增id,看看是作为 primary key 查询更快、还是 普通key更快。
环境:mysql5.7
单表数据量:11773632 (1千万左右) 、字段数量20个。
1、id主键查询: 跳跃500万,查询10条数据。
可以看得出,走主键索引,查询所有列 与 只查id列,耗时基本相同。都是15秒左右。
2、id普通key查询:
走普通key索引,查询所有列没有走索引,属于全表扫描,速度很慢。但只查询id列时,速度确很快。
只查询id列,主键索引耗时 15秒、普通索引耗时 2秒。
1千万的数据,普通key速度快了 7倍!!
这种场景下,我们只需要根据查询条件得到10个id,10条数据一页。再根据id查询主表,得到最终数据。
这种优化,就类似于二级索引表一样。
个人分析结果:
主键索引中包含所有字段信息,所以查询较慢。
普通key索引包含信息少,并且查询内容就是索引本身,不需要二次查询。
“覆盖索引”就是这么个意思,查询条件与结果都在一个“索引表”中,所以速度快。
补充: 非主键自增ID,建表时可以这样:
`id` bigint(20) NOT NULL AUTO_INCREMENT,
KEY `i_id` (`id`)
分页优化的sql,主要有这3种形式,速度基本一致。
-- 1、2.085s
select * from t_bd_extension_show where id<=(select id from t_bd_extension_show order by id desc limit 5000000,1) order by id desc limit 10;
-- 2、2.071s
select * from t_bd_extension_show where id in(select t1.id from (select id from t_bd_extension_show order by id desc limit 5000000,10) t1);
-- 3、2.067s
SELECT t1.* FROM t_bd_extension_show t1 JOIN (select id from t_bd_extension_show order by id desc limit 5000000,10) t2 ON t1.id = t2.id;
第1种、外部还需要where一次,通常不推荐。
查询条件没变化时,子查询起始 id可以直接通过Table得到。(这样就不需要limit跳跃几百万的数据,也不需要知道查询条件的总数)
能够做成最简单的分页 ,只能上一页或下一页,所以速度也是最快的。
第2种 、第3种类似,所有条件都在子查询中处理。主查询只需要匹配id即可。
要求相对简单,个人推荐第3种。