mysql 自增id 主键 与 普通key，查询速度 与 分页优化

自增长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种。