MYSQL 千万数据的分页优化--count、limit、排序、分组优化

1. count 统计优化

count是一个聚合函数，对于返回的结果集一行一行去判断统计，如果count括号里的不是null，那么累计值+1，否则不加，最后返回一个累计的总数。

select  count(id) from `user`

//在count()里面，可以传主键id 、* 、其它字段
//总结：count(*) 、count(1) 是不需要判断是否为空，速度比较快一点
//count(*) = count(1) > count(其它字段)   索引字段不为空字段  > 非索引字段为空字段              

2. limit 分页优化

limit m , n ;其实是扫描m+n条数据 ，然后过滤掉前面的m条数据 ， 当m越大 ，那么需要扫描的数据也越多，性能也会越来越慢。

//用explain ,这是全表扫描
select id ,name ,age,job from `user`  limit 1000000,10
//用explain ,这是索引扫描，性能提升不高
select id ,name ,age,job from `user` order by id  limit 1000000,10

对此也有解决方案

1. 如果主键id是递增的，每次查询返回最大id，根据id条件 进行查询

//原来的sql语句
select id ,name ,age,job   from `user` ORDER BY  id  limit 1000000,10
//原来的sql语句返回最大id为5657882，那么进行id优化查询
select id ,name ,age,job  from `user`  where id >5657882 ORDER BY  id  limit 10

1. 先查id，根据ID进行连表查询，减少回表

select u.id ,u.name ,u.age,u.job 
from  `user`  u 
INNER  JOIN 
(select id  from `user` ORDER BY  id  limit 1000000,10) a
ON a.id =u.id

1. 在业务方面也可以做一些让步，比如最多只展示多少页
2. mysql级别优化不了，那就可以缓存

3. order by 索引优化

如果让order by 的字段做索引，那么排序流程直接可以在索引数完成，如果排序的字段不走索引，整个排序必须先把数据放到内存，在内存里面实现排序。在内存排序也分为2种方式：

1. 全字段排序模式

需要查询的字段在max_length_for_sort_data 放得下 ，就拿出所有字段放到内存，排序后返回

1. row_id排序模式

需要查询的字段在max_length_for_sort_data放不下，只能主键ID与排序字段，排序后多一次回表（回主键索引拿其它字段）

4. InnoDB慢日志查询

慢日志查询，就是超过long_query_time秒数的一个执行，以及最少超过min_examined_row_limit的行数被查询到，才会进入到慢查询。

慢查询默认情况下是关闭的，可以通过slow_query_log开启。

//查询是否开启慢查
select @@slow_query_log
//开启慢查
set GLOBAL slow_query_log=1;

long_query_time 默认的超时时间是10s

min_examined_row_limit 默认是0，不会检测最少得数据量，可以配置，比如最少返回10,100条

创建表、创建索引、修改表式不会进入慢查的

默认不使用索引，也不会进入慢查，将这个值设置为1，开启后，不管是否使用索引，都会进入慢查。

select @@log_queries_not_using_indexes
SET @@GLOBAL.log_queries_not_using_indexes =1

select @@log_output //默认是保存在文件里面

//还可以设置为table，就是可以在table里面进行查询
SET GLOBAL log_output = 'table,file'

可以在系统数据库mysql库下的slow_log下查询慢查询的记录，通过记录也可以在sql_text里查看执行的sql语句，但是保存的二进制，如果想看的话，可以将它转码一下。

5. 慢查优化

1. 查看表结构是否正确合理，比如字段的数据类型等等
2. 是否正确用到了索引
3. 是否用到了合适的存储引擎
4. 每个表是否用到了合适的行格式
5. 是否用到了合适的锁策略
6. 缓存的内存区域，像innodb有bufferpool

以上是官网的建议，对此总结为

5.1. 数据库层面的优化：

1. 表结构是否合理，包括是否有合适的字段类型、合适的行格式、合适的存储引擎；是否有针对线上不同的数据量进行库、表的横向、纵向拆分。横向拆表，就是统一的业务表，数据量可能会很大，为了减少单表的压力，比如说产品表，我可能会拆分为表1表2表3，把我的数据放到不同的表里面，这也是我们的id为什么要分布式id的原因，因为在横向拆表的时候，id可能会重复。纵向拆，其实就是业务拆分，不同的业务会用到不同的表，比如微服务拆分以后，商品和我们的订单，它是不同的服务，不同的库，它会隔离，
2. 是否建立合适的索引，索引是否生效，是否有用到覆盖索引，是否有用到优化器的相关优化，比如count、group by、order by 等待。
3. 是否基于场景设置合理的内存缓存区间，比如InnoDB缓冲池和MyISAM键缓存
4. 是否用到合理的锁定策略，比如InnoDB里面不同的隔离级别会性能与一致性之间有取舍。

5.2. 硬件层面的优化

1. 用更好的磁盘 ，提供磁盘寻址性能以及磁盘读写吞吐量。
2. cpu层面，增加cpu解决cpu的瓶颈
3. cpu不在是瓶颈后，提高内存以及网络宽带
4. 可平衡的移植性，就是集群，多台集群来抗住并发的压力