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
对此也有解决方案
- 如果主键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
- 先查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
- 在业务方面也可以做一些让步,比如最多只展示多少页
- mysql级别优化不了,那就可以缓存
3. order by 索引优化
如果让order by 的字段做索引,那么排序流程直接可以在索引数完成,如果排序的字段不走索引,整个排序必须先把数据放到内存,在内存里面实现排序。在内存排序也分为2种方式:
- 全字段排序模式
需要查询的字段在max_length_for_sort_data 放得下 ,就拿出所有字段放到内存,排序后返回
- 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. 慢查优化
- 查看表结构是否正确合理,比如字段的数据类型等等
- 是否正确用到了索引
- 是否用到了合适的存储引擎
- 每个表是否用到了合适的行格式
- 是否用到了合适的锁策略
- 缓存的内存区域,像innodb有bufferpool
以上是官网的建议,对此总结为
5.1. 数据库层面的优化:
- 表结构是否合理,包括是否有合适的字段类型、合适的行格式、合适的存储引擎;是否有针对线上不同的数据量进行库、表的横向、纵向拆分。横向拆表,就是统一的业务表,数据量可能会很大,为了减少单表的压力,比如说产品表,我可能会拆分为表1表2表3,把我的数据放到不同的表里面,这也是我们的id为什么要分布式id的原因,因为在横向拆表的时候,id可能会重复。纵向拆,其实就是业务拆分,不同的业务会用到不同的表,比如微服务拆分以后,商品和我们的订单,它是不同的服务,不同的库,它会隔离,
- 是否建立合适的索引,索引是否生效,是否有用到覆盖索引,是否有用到优化器的相关优化,比如count、group by、order by 等待。
- 是否基于场景设置合理的内存缓存区间,比如InnoDB缓冲池和MyISAM键缓存
- 是否用到合理的锁定策略,比如InnoDB里面不同的隔离级别会性能与一致性之间有取舍。
5.2. 硬件层面的优化
- 用更好的磁盘 ,提供磁盘寻址性能以及磁盘读写吞吐量。
- cpu层面,增加cpu解决cpu的瓶颈
- cpu不在是瓶颈后,提高内存以及网络宽带
- 可平衡的移植性,就是集群,多台集群来抗住并发的压力