传统关系型数据库性能优化全攻略

影响数据库性能的因素

业务需求层面

• 不合理的需求

论坛发帖数实时更新
select count(*) from table

• 深翻页

比如查询第1万页的内容
冷数据加载到内存
热数据被swap出去了
目前基本已没有深翻页需求 只会一页一页往拉下

• 无用功能堆积

系统臃肿 性能底下
比如登陆网站默认开通账号 仅为了满足KIP需求
其实没有必要 很影响性能

• 不断升级造成系统复杂性影响整体性能

• 过度重视用户体验

大量非核心业务消耗了过多的数据库资源
比如图片放在数据库里面

架构层面

数据库中存放的数据是否都合适

• 不应该二进制音频视屏等富媒体数据存在blob里面

 应该存在对象存储里面 比如ceph、fastdfs

• 超大文件

大表层面 水平拆分

比如一条记录超过100K 一张表是否要超过1000万

是否利用了cache(redis/codis)

• 访问频繁 更新较少的数据

a、系统配置
b、规则数据
c、活跃用户的基本信息
d、活跃用户个性化定制信息
e、准实时的统计信息

不要使用memory保存频繁但非核心表

数据层的实现是否比较精简

• 游客留言和相册情况

优化前

优化后

• 查询广告信息（特定广告需要展示广告内容）

只需要查询广告位表 关系表 广告表即可 
等需要查询广告内容表的时候再去查询

• 过度追求扩展性 导致对象拆的过度分散

• 重复执行相同的sql

在业务逻辑层将数据缓存起来

数据库阶段优化

适度冗余 尽量减少join

在用户名更新不频繁的情况下 
联表查询变单表查询

大字段垂直拆分 减少io

比如 用户留言是大字段 拆分出来

元信息表：用户编号 用户姓名
用户留言表：用户编号 留言信息

选择合适的数据类型

• 减少存储空间

• 联表查询 连接字段使用相同数据类型 否则不会使用索引

是否需要优化query sql

1、执行频率高及消耗资源综合评定是否需要优化
优化高并发低消耗非低并发高消耗 
2、io性能监控工具:iostat;
   cpu性能监控工具:top、vmstat等;
   数据库性能监控工具:profile

定位优化对象的性能瓶颈

1、使用explain执行计划来分析性能瓶颈
2、将线上数据dump到测试环境 再使用explain 保证真实运行环境的真实性
3、从explain 入手 当前执行计划(比如实际用了全表扫描scan即超过了oracle/mysql 30%的数据量 就没有必要用索引了 直接扫描更快)、目标执行计划（希望用index）

优化规则

用小结果集驱动大结果集 非小表驱动大表

举例

比如一个查询语句需要从2个表联合查询
从表1（共100万数据）查询出50万数据
从表2（共1000万数据）查询出10万数据
则表2 left join 表1
而非表1 left join 表2

原理

联表查询使用Nested Loop的join方式
小结果集可以减少嵌套循环次数

尽可能在索引中完成排序

• 排序时利用索引的有序性

• 结果集已经有序 无需排序

不要取出无用的列 特别是Blob 除非必须 不要使用select *

• 减少网络传输量

• 排序时可以选择优化的排序算法

仅使用最有效的过滤条件

索引条件不是越多也好

尽量选择满足需求的最小列

使用优先级:
char->int->bigint->float->double->varchar->text->blob

尽量避免复杂的join和子查询

• 越复杂的join语句 锁定的资源就越多 锁阻塞的其他线程也越多

• 能不用子查询就不用

• 用连接代替子查询

绝大多数子查询都可以用连接解决

联合查询替换子查询 举例说明

• in

• not exists

左连接 left join 原理解释

表A

用户编号	用户名称
1	小孟
2	小凡
3	小霄

表B

用户编号	用户名称
1	小平
2	小凡

表A left join 表B on 用户名称

表A用户编号	表A用户名称	表B用户编号	表B用户名称
1	小孟	1
2	小凡	2	小凡
3	小霄

技巧

1、使用left join 谁在左表谁就是主表即以谁为主
如果以表A为主 则联合之后的数据有3条 
如果以表B为主 则联合之后的数据有2条
2、关联字段是表A的用户名称和表B的用户名称
则相同的则显示 不相同的则为空

重要原则

实际案例

• 实际案例一

优化方式

优化思路分析

• 实际案例二

优化方式

优化思路分析

• 在索引中完成排序

优化方式

优化思路分析

filesort在磁盘上进行排序

• 建立合理的索引

小结果集驱动大结果集

• 减少加锁时间

数据库规范

规范对象命名

设计规范

SQL规范

索引规范

方法技巧

注意事项

数据库锁

锁分类

合理的使用Innodb的行级锁

避免锁的争用

Mysql架构优化

体系结构

分散式系统

概念解释

独立部署

集中式系统

概念解释

集中部署

对服务器配置要求高 比如40H 192G 5T 万兆网卡
这种方式更多一些

分布式系统

概念解释

分布式计算

3大类架构模式

主库单机

主从架构用的比较多

概念说明

架构图

1、中间件：获取mysql binlog、解析binlog、推到叶子库
2、通常情况下不需要中间件 
   中间件：可以减轻主库的压力但延迟会更大

没有中间件主从架构

主库多机

概念说明

通过MQ分发多主架构

满足最终一致性即对一致性要求不高的场景

分布式

TiDB架构图

数据库调优

根据不同的业务特点 制定不同的数据库业务模型

目前公司基本使用性能普通型
推荐使用MySQL 5.7.21

三大优化原则

• 索引隔离列

如果where表达式中有对列的计算 比如 where id=id+1
则id不是索引隔离列即不会走索引

• 计数表

用户点击表 hit_count 有一个cnt点击次数字段

对于任何更新该计数器的更新事务实际上是一个全局的互斥量
无并发行而言

此时给表添加一个slot字段
从而将更新请求分发到不同的slot上以提高并发性

更新

udpate hit_count set cnt=cnt+1 where slot=RAND() * 100;

统计

select count(cnt) from hit_count;

• 优化Count

查询id大于5的记录
select count(*) from 表 where id >5;

如果id>5的数量远远大于id<5的行数时 

通过总行-id小于5的记录 效率就会有非常多的提高
select (select count(*) from 表) - count(*) from 表 where id < 5;

牺牲范式 提高性能