sql执行慢
有些一句简单的sql看起来平平无奇,但是却执行很慢,一般存在这么几种可能性:
大多数情况下还可以,只是偶尔出现慢的情况
在数据量不变的情况下,这条sql语句一直执行很慢
整个sql运行慢
定位
针对sql运行慢,我们要做的首先就是定位出这些sql,然后再看这些sql是否正确创建并使用索引
慢查询分析
Mysql中自带了慢查询日志的功能,使用show variables like '%slow_query_log%'
来查询慢查询日志是否开启
如下图所示:
![4dbcb88c53aeb3faf9c1bf899ed651cd.png](https://img-blog.csdnimg.cn/img_convert/4dbcb88c53aeb3faf9c1bf899ed651cd.png)
开启慢查询日志
临时开启
代码:set global slow_query_log=1
![79a768823b077dbdd52b9575389123dd.png](https://img-blog.csdnimg.cn/img_convert/79a768823b077dbdd52b9575389123dd.png)
这种设置方式,只对当前数据库生效,如果Mysql重启也会失效
永久开启
如果要永远生效,就必须修改Mysql的配置文件my.cnf,配置如下
slow_query_log =1 slow_query_log_file=/tmp/mysql_slow.log
分析sql运行情况
使用explain 分析你SQL的计划,如下图所示:
explain结果是基于数据库表中现有数据的
![5a4369043aab83757fd519266ed23bbd.png](https://img-blog.csdnimg.cn/img_convert/5a4369043aab83757fd519266ed23bbd.png)
其中:
id:选择标识符,id越大优先级越高,越先被执行
select_type:表示查询的类型
SIMPLE:简单查询
UNION:联合查询
SUBQUERY:子查询(不在from子句中)
PRIMARY:表明当前行对应的select是复杂查询中最外层的select
DERIVED:表明当前行对应的select是包含在from子句中的子查询
table:输出结果集的表
partitons:匹配的分区
type:表示表的连接类型
all:扫描全表数据
index:遍历索引
range:索引范围查找
index_subquery:在子查询中使用ref
unique_subquery:在子查询中使用eq_ref
ref_or_null:对null进行索引的优化的ref
fulltext:使用全文索引
ref:使用非唯一索引查找数据
eq_ref:在join查询中使用主键或唯一索引关联
const:将一个主键放置到 where 后面作为条件查询, MySQL 优化器就能把这次查询优化转化为一个常量,如何转化以及何时转化,这个取决于优化器,这个比 eq_ref 效率高一点。
possible_keys:表示查询时,可能使用到的索引
key:表示查询时实际用到的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:大概估算的行数
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明
偶尔很慢
这种情况下,由于大多数场合下还是比较快的,具有偶发性,一般来说这条sql的书写本身没什么问题,更多的问题在于其他的外部原因导致的
数据库在刷新脏页
要知道,我们往数据库里插入一条数据,或者更新一条数据的时候,数据库会在内存中把相应字段的数据给更新了,但是更新以后,数据不会立马同步持久化到磁盘中去,而是把这些记录写到redo log日志中去。等到空闲的时候,再通过redo log里的日志把最新的数据同步到磁盘中去。
不过容易造成一个问题,就是redo log里面的容量是有限的,如果数据库一直很忙,更新又很频繁,这个时候redo log被塞满了,这个时候就只能暂停其他的操作,先把数据同步到磁盘中去,这个时候就导致我们平时执行很快的sql变得很慢。
拿不到锁
我们执行的这个SQL语句的时候,正好这条sql语句涉及到的表,被人加锁了,我们暂时拿不到锁,就只好等待。或者是表没有加锁,而是要使用到的某一行被加锁了。
所以我们要判断一下,是否是因为锁问题导致的,可以使用show processlist这个来查看当前的状态(mysql)。
一直很慢
先假设一个表,其中只有主键id和两个普通字段age和salary
CREATE TABLE `t` (
`id` INT ( 11 ) NOT NULL,
`age` INT ( 11 ) DEFAULT NULL,
`salary` INT ( 11 ) DEFAULT NULL,
PRIMARY KEY ( `id` )
) ENGINE = INNODB;
没用上索引
字段没有索引
字段有索引却没有用上索引
例如:
select * from t where age-1=19;
因为我们在字段的左边做了运算,导致在查询的时候就用不上。
函数操作导致没有用上索引
我们在查询的时候对字段进行了函数操作,这也会导致没有用上索引。
数据库选错索引
例子:
select * from t where 1and age<100;
我们知道主键索引和给主键索引时有区别的,也就是说如果走age这个字段的索引的话最后会查询到对应主键的值,然后再根据主键的值走主键索引,查询到整行数据返回。
即就算age有索引,系统也不一定会走age这个字段的索引,而是可以会直接扫描,扫描全表。
主键索引(聚簇索引):叶子节点存放的值是整行字段的数据。
非主键索引(二级索引):叶子节点存放的是主键的值
在执行这条语句的时候系统会进行预测到底是走age的索引行数少,还是直接全表扫描的行数少,毕竟age索引找到主键后还需要通过主键索引来找整行数据。
所以系统就通过索引的区分度来判断:一个索引上不同的值越多,意味着出现相同数值的索引越少,意味着索引的区分度越高。我们也把区分度称之为基数,即区分度越高,基数越大。
即索引的基数越大就走索引查询。
但系统通过采样来判断索引基数的大小,所以存在误差判断失误。
即由于统计失误,导致系统没有走索引而是走了全表扫描,导致我们sql执行缓慢
可以通过SQL语句强制走索引:
select * from t force index(a) where 1and age<100;
也可以通过 show index from t ;来查询索引的基数和实际是否符合,如果不符合可以重新统计:analyze table t
整个SQL运行慢
如果出现这种情况,说明目前数据的承载能力已经达到了巅峰,因此我们需要使用一些数据库的扩展手段来缓解Mysql服务器
解决方案:读写分离
一般情况下,对数据库而言,都是读多写少,压力主要来源于读。针对这种情况,我们可以采用数据库集群的方案,使用一个库作为主库,负责写入数据,其他库作为从库,负责读数据,这样可以减缓对数据库的访问压力
实现方案
应用层解决方案
可以通过应用层对数据源做路由来实现读写分离,比如使用SpringMVC+Mybatis,可以将SQL路由交给Spring,通过AOP或者Annotation由代码显示的控制数据源。
优点:
路由策略的扩展性和可控性较强
缺点
需要在Spring中添加耦合控制代码
中间件解决方案
通过MySQL的中间件做主从集群,比如使用Mysql Proxy、Amoeba、Atlas等中间件都能符合需求。
优点
与应用层解耦
缺点
增加一个服务维护的风险点,性能及稳定性待测试,需要支持代码强制主从和事务。
优化SQL查询速度
索引优化
尽量使用主键查询,而非其他索引,因为主键查询不会触发回表查询;
查询语句尽可能简单,大语句拆小语句,减少锁时间;
尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型;
用 exists 替代 in 查询;
避免在索引列上使用 is null 和 is not null。
回表查询:普通索引查询到主键索引后,回到主键索引树搜索的过程,我们称为回表查询。
数据拆分
垂直拆分
将一张表拆分成多张表,其原则:
把不常用的字段单独放到一张表中
把text,blob等大字段拆分出来放到附表中
经常组合查询的列放到一张表中。
水平拆分
把一张表中的数据拆成多张表来存放,一般情况下使用取模的方式来进行拆分。
表的其他优化方案
使用可以存下数据最小的数据类型;
使用简单的数据类型,int 要比 varchar 类型在 MySQL 处理简单;
尽量使用 tinyint、smallint、mediumint 作为整数类型而非 int;
尽可能使用 not null 定义字段,因为 null 占用 4 字节空间;
尽量少用 text 类型,非用不可时最好考虑分表;
尽量使用 timestamp,而非 datetime;
单表不要有太多字段,建议在 20 个字段以内。
总结
一条sql 执行很慢的原因:
偶尔很慢:
数据库在刷新脏页,例如redo log满了需要同步到磁盘
执行的时候遇到锁:表锁、行锁
一直很慢:
没有用上索引:
该字段没有索引
由于对字段进行运算或者函数操作导致无法使用索引
数据库选错了索引
最后
如果觉得看完有收获,希望能给我点个赞,这将会是我更新的最大动力,感谢各位的支持
欢迎各位关注我的公众号【java冢狐】,专注于java和计算机基础知识,保证让你看完有所收获,不信你打我
如果看完有不同的意见或者建议,欢迎多多评论一起交流。感谢各位的支持以及厚爱。
![766a4b07667e4c2082ad428451df7c3d.png](https://img-blog.csdnimg.cn/img_convert/766a4b07667e4c2082ad428451df7c3d.png)