MySQL之SQL优化

悠悠城主
280 阅读2分钟

性能度量标准

  • QPS
  • TPS
  • RT

问题来源

  1. 慢日志，配置参数（MySQL5.7）
slow_query_log=1
long_query_time=0.5
slow_query_log=slow.log
  1. tcpdump & wireshark
tcpdump -nvvv -i eth0 -c 20 'port 3306 and host x'
  1. 监控告警 例如接口的SLA告警TP99

分析慢在哪里

性能分析profiling

sequenceDiagram
应用（JDBC）->>服务端: 创建连接
应用（JDBC）->>服务端: 发送SQL：select name from tb1 where id = 888
服务端->>应用（JDBC）: FetchSize 结果集
应用（JDBC）->>服务端: 断开连接

定位每步的开销

set profiling=1;
show profiles;
show profile for query 1;

Profile在5.7和8版本已经被标记为废弃，官方建议使用Performance Schema进行性能分析，后续补充

执行计划explain

  • type：const>eq_ref>ref>ref_or_null>range>index>all
select * from tb1 where id = 1;
select a.name from tb1 a left join tb2 b where a.id = b.id;
select * from tb1 where name='san';
select * from tb1 where name='san' or name is null;
select * from tb1 where age > 20 and age < 30;
select name from tb1;
select * from tb1;
  • rows：检索的行数
  • extra：
    • using where
    • using index
    • using index condition
    • using filesort
    • using temporary
    • using index for group by
    • using join buffer

最佳实践：

  • select
    • 查询尽量走索引，走覆盖索引，走组合索引的最左前缀
    • 避免select *
    • 避免结果集数据量过大
    • 避免在过滤和排序字段进行函数运算
    • 避免类型转换
  • join
    • 小表驱动大表
    • 连接字段走索引且类型匹配（不匹配会全表扫）
    • 优先优化nested-loop内层循环
    • 避免交叉连接（笛卡尔积）
  • limit 注意offset问题
select * from tb1 limit 100000,10;
select * from tb1 t1,(select id from tb1 order by id limit 100000,10) t2 where t1.id = t2.id limit 10;
select * from tb1 where id > $max_id order by id limit 10;
  • order by
  • group by
    • 松散索引扫描，完全利用索引查询数据再分组
    • 紧凑索引扫描，区别在于索引扫描时，读取所有满足条件的索引键，然后再根据读取的数据分组
    • 建立避免临时表排序
  • count，建议count(*)/count(1)，而count(field) 不统计null值，精确度要求不高使用information_schama.table的统计信息
  • DML
    • 大操作 -> 批量小操作，避免锁竞争和复杂延迟
    • 注意操作顺序，防止Dead Lock
    • 全表数据清理，建议使用truncate，注意自增键会重置
  • DDL（同一个表的操作合并，online DDL，第三方在线改表）

表结构

  • engine选择
  • 字段设计
    • 越小越好，例如ip使用inet_aton函数转成unsigned int，用4个字节即可，电话号码bigint > varchar
    • 越简单越好
    • not null 好于 null
    • tinyint 好于 enum
    • char,varchar 好于 blob，text
  • 拆分表
    • 垂直拆分：冷热分离，text\blob单独放表
    • 水平扩展，引入中间件分库分表
  • 索引
    • 建议自增id作为主键
    • 不建议使用md5，无序字符串作为主键，因为插入数据会导致频繁分页，占用空间大，查询性能差
    • 出现在where\join关联字段\order by\group by中的字段适合建索引
    • 更新频繁的字段不建议建索引，影响性能
    • 基数不高的列不建议建索引
    • 组合索引，按区分度从左到右
    • 组合索引字段，不建议超过5个
    • 建议使用短索引，长字符串进行截取使用前缀索引，既能优化查询，又避免了索引过大
    • 单表不建议建超过5个索引
    • 不要建冗余索引

代码

  • 使用预编译语句，防止SQL注入，提升性能
  • MyBatis一、二级缓存

数据库配置（主从、参数）

  • 读写分离
  • 同步方式

主机配置

提升配置，磁盘扩容，倍增扩库等

网络状况

分析网络耗时

加缓存

查询类占比多的情况，引入ehcache+redis等，做自己擅长的事务操作

avatar
文章
阅读
粉丝