MySQL学习笔记（四）MySQL慢查询优化

慢日志查询

慢速查询日志由执行时间超过 long_query_time几秒并且至少需要 min_examined_row_limit检查行的 SQL 语句组成

long_query_time

SELECT @@long_query_time; -- 默认是10 单位s
SET GLOBAL long_query_time=1; -- 设置超过1s就算慢查

min_examined_row_limit

SELECT @@min_examined_row_limit; -- 默认是 0

慢查询数据保存在mysql.slow_log表中

慢查询优化

我们发现有慢查询怎么办，肯定要想办法去优化，优化的方式有很多很多，主要分为以下几个方向：

硬件层面优化

我们知道，Mysql性能最重要的瓶颈在磁盘的IO，所以硬件层面，最重要的其实就是磁盘。

1.提高磁盘读写能力，可以用比较新型的磁盘

2.减少寻址时间，可以横向扩展，将数据添加到不同的磁盘，每个磁盘数据的寻址通常1s 100次寻址，那么单个磁盘有限制，就多个磁盘寻址

当然，除了磁盘以外，cpu、内存以及带宽也是比较重要的因素

增加服务器资源

部署主从、多主多从等集群

数据库层面优化

表结构优化

字段优化：在字段设计时遵循三范式，减少冗余数据，当然生产环境要完全满足三范式是很难的，产生冗余数据不可避免，时间与空间需要自行斟酌平衡

然后字段类型在满足业务场景的情况下尽可能小，这样占用空间越小，索引树越矮，磁盘io次数越少。

合适的存储引擎

根据适合的场景选择不同的存储引擎

分库分表

分库分表的思想其实就是将大数据分散到不同的库跟表

InnoDB存储引擎优化

增加bufferpool大小

bufferpool越大，那么内存能放的数据越大，这样，查询数据去磁盘查询的次数也就越少。当然这样的话占用的内存也越大，需要根据实际情况去考虑

隔离级别优化

某些场景可以牺牲数据一致性来换取更高性能，比如采用RC。

Sql语句优化

可以使用Explain来查询sql的执行计划

官网：https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain-output-columns

select_type：查询类型

table：表名，也可以是子查询的表

partitions：分区查询语句要走哪些分区

type：连接类型

最好要能达到range，如果达不到，要进行优化。

possible_keys：可以选择的索引查询，如果为null则没有索引可以供选择。

key：真正使用的索引

key_len：使用的键的长度

rows：执行查询必须扫描的行数，对于InnoDB来讲，这个是个预估值，不是非常准确

filtered：行数据过滤百分比

Extra

强制使用索引

你也可以强制使用、或者忽略索引：但是会带来一定的性能问题，一般不要去指定

EXPLAIN SELECT product_count FROM product_new USE INDEX (idx_type_price)
 WHERE product_type>6 GROUP BY product_count;

order by优化

        如果让orderby的字段走索引，那么排序流程直接可以在索引树完成，如果排序的字段不走索引，整个排序流程必须先把数据放到内存，在内存实现排序。这个内存的大小由sort_buffer_size配置,如果内存不够保存这个数据，那么就会启用磁盘的临时文件来进行排序。

group by优化

首先，我们来看下group by如果没有走到索引的实现流程

1.会将符合条件的数据扫描后，放到一个临时表，并且这个临时表是根据 group by的字段排序好的

2.然后在临时表根据用户的聚合需求，比如是求count、sum,返回给用户相关结果

但是如果group by写得够好，那么就可以避免创建临时表的逻辑，让直接通过索引来去group by。

count优化

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

那么count括号里的参数应该是id、还是字段、还是1 、还是*。  

其实大家平时大部分用的是*跟1 ，问题不会太大。1 是扫描到数据扫描到了就固定返回一个1，肯定不为null，不会做null判断。

*是整条数据，也进行了优化，因为整条数据肯定不会为null。所以也不需要去判断

然后，count(id）,主键id，肯定不为null，也不会去判断null,但是相对于

count(1)来讲，要去解析ID.稍微慢点，但是也可以忽略不计。

继续，count(字段)，这个就有影响了

首先，如果字段没有索引，就需要进行全表扫描，explain是all

然后如果字段不为null，那么不需要进行null逻辑判断，如果可为空，则每条数据要进行非空判断

总结：count(1)≈count(*) > count(id)>count（字段）字段是否有索引，是否是可为null，也会影响性能

Limit优化

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

针对这种情况，有以下几种方案可以进行一定的优化。

1.如果id是趋势递增的，那么每次查询都可以返回这次查询最大的ID，然后下次查询，加上大于上次最大id的条件，这样会通过主键索引去扫描，并且扫描数量会少很多很多。因为只需要扫描where条件的数据

2.先limit出来主键ID，然后用主表跟查询出来的ID进行inner join 内连接，这样，也能一定上提速，因为减少了回表，查询ID只需要走聚集索引就行。

3.当然，如果mysql级别优化不了了。我们也可以对分页数据进行缓存，比如Redis缓存，数据进行变动的时候，做好缓存依赖即可。

Sql优化实战（基于美团技术文章复现）

美团技术团队文章： https://tech.meituan.com/2014/06/30/mysql-index.html

分库分表

纵向分库分表

不同的业务用不同的数据库、表

比如微服务，每个微服务的数据库地址、库、表不一样

横向分库分表

一个表的数据过大，导致查询操作过慢，需要进行拆分成多个表，主要就是分区思想

索引失效场景

1. ‌模糊查询的前导通配符‌：当使用LIKE操作符进行模糊查询，且通配符%位于模式的开头时（如LIKE '%abc'），索引将无法使用，因为这会导致无法确定索引的具体范围。

2. ‌未使用索引字段进行过滤‌：如果查询条件没有使用到创建的索引字段，数据库可能不会使用该索引。

3. ‌‌数据类型不匹配‌：如果查询条件的数据类型与索引字段的数据类型不匹配，数据库无法使用索引。

4. ‌使用‌函数操作‌：如果查询条件中对字段进行了函数操作（如LOWER(column)），索引可能失效，因为数据库无法直接使用索引。

5. ‌‌OR运算‌：在OR运算中，如果其中一个条件使用了索引，而另一个条件没有使用索引，整个查询可能会导致索引失效。

6. ‌‌计算操作‌：对索引列进行计算操作（如YEAR(time)）会导致索引失效，因为计算会改变列的值，使得无法利用索引。

7. ‌‌隐式类型转换‌：在查询条件中对索引列进行隐式类型转换（如将整数类型的值与字符串进行比较）会导致索引失效，因为数据库需要进行类型转换以匹配查询条件，这个过程无法有效利用索引。