mysql系列之troubleshooting

发表于 更新于 分类于

mysql系列之troubleshooting

慢查询

表现响应时间长

开启慢查询日志

  • slow_query_log： 以捕获执行时间超过一定数值的SQL语句。
  • long_query_time： 阈值，超过计入日志
  • log_queries_not_using_indexes： 记录没有使用索引的sql

获取慢查询信息

SELECT * FROM slow_log where start_time > ?

获取当前查询 （正常情况应该为null假定sql执行很快）

select * from information_schema.processlist
  1. 收到问题，诊断SQL
  2. 开启慢查询日志，抓出执行的慢的SQL
  3. 使用explain分析（基本上可以找到问题所在，但是如果还是没有摆平，我们的SQL在传输、网络、连接、死锁，需要进一步细粒度的查询和排查的时候就需要使用show profile）
  4. show profile
  5. 配合DBA 到my.cnf配置文件中对各种性能的参数调优和修改

Action：

  • 优化sql

  • 优化索引

  • 慢查询监控

  • 数据库分库分表

优化方案

索引的优化，sql语句的优化，表的优化

### 索引优化

加速查询最好的方法就是索引。

含有NULL值，就最好不要在此例设置索引

对于经常在where子句使用的列，最好设置索引

于有多个列where或者order by子句的，应该建立复合索引

like语句，以%或者‘-’开头的不会使用索引，以%结尾会使用索引

不要在列上进行运算

不要使用not in和<>

数据库优化

数据表拆分：主要就是垂直拆分和水平拆分。

水平切分:将记录散列到不同的表中，各表的结构完全相同，每次从分表中查询, 提高效率。

垂直切分:将表中大字段单独拆分到另外一张表, 形成一对一的关系。

SQL 优化

https://zhuanlan.zhihu.com/p/72071609

SELECT * 问题

  • 不需要的列会增加数据传输时间和网络开销
  • 对于无用的大字段，如 varchar、blob、text，会增加 io 操作
  • 失去MySQL优化器“覆盖索引”策略优化的可能性

join： 小表join 大表

避免not in 或<> - 全表扫描

避免字段null

避免or ， 可以使用union

避免在 where 子句中对字段进行表达式操作 - 全表扫描

select id from t where num/2=100
  改为
select id from t where num=100*2

避免在where子句中对字段进行函数操作

注意优化器在数据量大的时候可能不会使用索引

避免更新cluster 索引，资源耗费大

避免大事务，提高并发