为啥你这个sql怎么慢呢

sql执行慢

​ 有些一句简单的sql看起来平平无奇，但是却执行很慢，一般存在这么几种可能性：

• 大多数情况下还可以，只是偶尔出现慢的情况
• 在数据量不变的情况下，这条sql语句一直执行很慢
• 整个sql运行慢

定位

​ 针对sql运行慢，我们要做的首先就是定位出这些sql，然后再看这些sql是否正确创建并使用索引

慢查询分析

​ Mysql中自带了慢查询日志的功能，使用show variables like '%slow_query_log%'来查询慢查询日志是否开启

如下图所示：

开启慢查询日志

• 临时开启

​ 代码：set global slow_query_log=1

​ 这种设置方式，只对当前数据库生效，如果Mysql重启也会失效

• 永久开启

如果要永远生效，就必须修改Mysql的配置文件my.cnf，配置如下

slow_query_log =1 slow_query_log_file=/tmp/mysql_slow.log

偶尔很慢

​ 这种情况下，由于大多数场合下还是比较快的，具有偶发性，一般来说这条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 1<age and age<100;

​ 我们知道主键索引和给主键索引时有区别的，也就是说如果走age这个字段的索引的话最后会查询到对应主键的值，然后再根据主键的值走主键索引，查询到整行数据返回。

​ 即就算age有索引，系统也不一定会走age这个字段的索引，而是可以会直接扫描，扫描全表。

• 主键索引（聚簇索引）：叶子节点存放的值是整行字段的数据。
• 非主键索引（二级索引）：叶子节点存放的是主键的值

​ 在执行这条语句的时候系统会进行预测到底是走age的索引行数少，还是直接全表扫描的行数少，毕竟age索引找到主键后还需要通过主键索引来找整行数据。

​ 所以系统就通过索引的区分度来判断：一个索引上不同的值越多，意味着出现相同数值的索引越少，意味着索引的区分度越高。我们也把区分度称之为基数，即区分度越高，基数越大。

​ 即索引的基数越大就走索引查询。

​ 但系统通过采样来判断索引基数的大小，所以存在误差判断失误。

​ 即由于统计失误，导致系统没有走索引而是走了全表扫描，导致我们sql执行缓慢

可以通过SQL语句强制走索引：

select * from t force index(a) where 1<age and age<100;

也可以通过 show index from t ;来查询索引的基数和实际是否符合，如果不符合可以重新统计：analyze table t

整个SQL运行慢

​ 如果出现这种情况，说明目前数据的承载能力已经达到了巅峰，因此我们需要使用一些数据库的扩展手段来缓解Mysql服务器

解决方案：读写分离

​ 一般情况下，对数据库而言，都是读多写少，压力主要来源于读。针对这种情况，我们可以采用数据库集群的方案，使用一个库作为主库，负责写入数据，其他库作为从库，负责读数据，这样可以减缓对数据库的访问压力

实现方案

应用层解决方案

​ 可以通过应用层对数据源做路由来实现读写分离，比如使用SpringMVC+Mybatis，可以将SQL路由交给Spring，通过AOP或者Annotation由代码显示的控制数据源。

• 优点：
  • 路由策略的扩展性和可控性较强
• 缺点
  • 需要在Spring中添加耦合控制代码

中间件解决方案

​ 通过MySQL的中间件做主从集群，比如使用Mysql Proxy、Amoeba、Atlas等中间件都能符合需求。

• 优点
  • 与应用层解耦
• 缺点
  • 增加一个服务维护的风险点，性能及稳定性待测试，需要支持代码强制主从和事务。

索引失效

​ 下面针对索引失效的几种情况作了总结。

查询条件包含or

• 执行一条sql是会走索引的：

• 当加上一个没有索引的字段一起or查询时，就不走索引了

由于后面的没有索引那就需要全表进行扫描，只有当or的条件字段也有索引才会走索引

字段是字符串时，where一定要用“”

• 当字段时字符串类型的时候，是B+树索引，如果查询条件穿了一个数字过去，是字符串和数字进行比较，mysql会做隐式的类型转化，把他们转化为浮点数再进行比较

like通配符可能导致索引失效

• 并不是使用like就会导致索引失效，而是like查询以%开头会导致索引失效
  • 可以使用覆盖索引进行优化
  • 把%放到后面

联合索引，查询时的条件列不是联合索引中的第一个列，索引失效

• 当我们创建联合索引的时候如（k1.k2.k3）相当于创建了（k1），（k1,k2）和（k1,k2,k3）三个索引，这就是最左匹配原则
• 联合索引不满足最左原则，索引一般会失效，但这个mysql的优化器还有关系

在索引列上使用mysql的内置函数索引会失效

对索引列运算索引失效

在索引字段上使用（！ = 或者<>，not in）时可能会导致索引失效

在索引字段上使用is null,is not null可能会导致索引失效

左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效。

mysql估计使用全表扫描要比使用索引快,则不使用索引。

优化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和计算机基础知识，保证让你看完有所收获，不信你打我
• 如果看完有不同的意见或者建议，欢迎多多评论一起交流。感谢各位的支持以及厚爱。