Mysql优化及问题定位，看这一篇就够了( 二 ) _Mysql优化

logback 配置
 <Appender name="druidSqlRollingFile" class="ch.qos.logback.core.rolling.RollingFileAppender"> <file>${logPath:-${defaultLogPath}}/druid/druid-sql.log</file> <rollingPolicy class="ch.qos.logback.core.rolling.SizeAndTimeBasedRollingPolicy"> <fileNamePattern>${logPath:-${defaultLogPath}}/druid/druid-sql.%d{yyyy-MM-dd}.%i.log </fileNamePattern> <maxFileSize>${LOG_FILE_MAX_SIZE:-10MB}</maxFileSize> <maxHistory>${LOG_FILE_MAX_HISTORY:-20}</maxHistory> </rollingPolicy> <encoder> <pattern>${FILE_LOG_PATTERN}</pattern> <charset>UTF-8</charset> </encoder> </appender> <logger name="druid.sql.Statement" level="warn" additivity="false"> <appender-ref ref="CONSOLE"/> <appender-ref ref="druidSqlRollingFile"/> </logger>2、mysql 的慢 sql 日志，从这个慢 sql 日志文件中分析出执行慢的 sql
默认是不开启慢 sql 日志记录的
-- 查看开启慢 sql show variables like 'slow_query_log%'; -- 查看执行时间大于多少为慢 sql show variables like 'long_query_time%';

文章插图

开启慢 sql 日志记录，这是动态修改，没有持久化，数据库重启就失效了。

 -- 这个值单位是秒，不要设置的太小 。不然打印日志太多，我们要先优化哪些执行时间较长的 sql 比如大于 5 秒，大于 10 秒 -- 一步一步优化 set global long_query_time=3; -- 开启慢 sql 记录 set global slow_query_log=1;

我们可以使用 select sleep(5); 来产生慢 sql。

 # Time: 2021-03-07T07:24:10.757715Z # User@Host: root[root] @ localhost []Id:    17 # Query_time: 5.070525Lock_time: 0.000000 Rows_sent: 1Rows_examined: 0 SET timestamp=1615101845; select sleep(5);

也可以在 mysql 配置文件中修改,这样数据库重启也是开启的。
[mysqld] slow_query_log=1 slow_query_log_file=/var/lib/mysql/slow-log.log long_query_time=3按 -s 指定按查询时间排序，-t 指定返回多少条记录数，也可以 -g 筛选，类似于 grep 操作。

 mysqldumpslow -s t -t 10/usr/local/var/mysql/wanguyunxiao-slow.log | more mysqldumpslow -s t -t 10 -g "left join"/usr/local/var/mysql/wanguyunxiao-slow.log | more

慢 sql 产生的原因1、可能没有用到索引，建立合适的索引
2、有的时候索引也建立了，但是你联合查询，关联 n 多个表查询速度可能慢。阿里规范推荐最多关联 3 个表，这个时候我们就需要简化 sql 了，用多个 sql 完成你的业务逻辑，而不是一条 sql 查询出你需要的数据。
3、sql 一定要写规范，索引的使用要符合最左匹配原则，这和索引的数据结构有关
4、隐式数据类型转换，条件做函数计算等等，这些都要避免
5、还有一种比较特殊，有索引，但是没有做索引，这个时候可以强制走索引，你也要去优化你的索引统计数据。或者优化你的表空间文件了

 -- 实际就是更新索引的统计数据，让索引更有效利用，一般在空闲的时候做 。 ANALYZE TABLE table_name;

6、表空间文件优化。当我们真删除数据过多，但是数据库的表空间文件可能并没有缩小，这时候我们需要在业务不忙的时候去优化表空间文件。

 -- 会锁表，优化了表空间文件及索引相关的数据 。定期执行命令即可 。 OPTIMIZE TABLE tbl_name [, tbl_name] ...

Explain 查看执行计划o 表为组织机构表，字段 id,name
oc 记录的是某个某个组织机构下某个仓库的库存数量，oid,cid,oc_num

 EXPLAIN SELECT o.`name`,t.`库存总量` FROM (SELECT oc.oid,sum(oc.oc_num) AS '库存总量' FROM oc GROUP BY oc.oid HAVING SUM(oc.oc_num)>5000 ) AS t INNER JOIN o ON t.oid=o.id;