第一章 MySQL业务优化与设计
第一节 MySQL索引查询优化
数据库索引查找
全表扫描-索引查找
InnoDB表聚簇索引
1. 创建索引
单列索引
create index idx_test1 on tb_student (name);
联合索引
create index idx_test2 on tb_student (name, age);
索引中先根据name排序,name相同的情况下,根据age排序
l 索引维护由数据库自动完成
l 插入/修改/删除每一个索引行都
l 变成一个内部封装的事务
l 索引越多,事务越长,代价越高
l 索引越多对表的插入和索引字
l 段修改就越慢
2. 如何使用索引
依据WHERE查询条件建立索引
排序ORDWR BY、GEROUP BY、DISTINCT字段添加索引
3. 索引与字段选择性
l 某个字段其值的重复程度，重复程度越低选择性越好，越高选择性越差。
l 选择性很差的字段通常不适合创建单列索引
l 联合索引中选择性好的字段应该排在前面
联合索引与前缀索引查询：
l 联合索引能为前缀单列,复列查询提供帮助
l 合理创建联合索引，避免冗余
长字段的索引：
l 在非常长的字段上建立索引影响性能
l InnoDB索引单字段(utf8)只能取前767 bytes
l 对长字段处理的方法
l Email类,建立前缀索引
l 住址类，拆分字段
索引覆盖扫描：
l 最核心SQL考虑索引覆盖
SELECT Name FROM tb_user WHERE UserID=?
KEY IDX_UID_NAME(UsegID,Name)
l 不需要回表获取name字段,IO最小,效率最高
4. 如何确定一个查询走没走索引,走了哪个索引
l explain是确定—个查询如何走索引最简便有效的方法
explain select from tb_test ;
l 关注的项目：
n type:查询access的方式
n Key :本次查询最终选择使用哪个索引,NULL为未使用索引
n key_len:选择的索引使用的前缀长度或者整个长度
n rows:可以理解为查询逻辑读,需要扫描过的记录行数
n extra:额外信息,主要指的fetch data的具体方式
第二节 MySQL数据库设计
1. 什么是Schema设计
l 设计数据库的表,索引,以及表和表的关系
l 在数据建模的基础上将关系模型转化为数据库表
l 满足业务模型需要基础上根据数据库和应用特点优化表结构
2. 为什么Schema需要设计
Schema关系到应用程序功能与性能
l 满足业务功能需要
l 同性能密切相关
l 数据库扩展性
l 满足周边需求(统计,迁移等)
关系型数据库修改Schema经常是高危操作
l Schema设计要体现一定的前瞻性
3. 基于性能的表设计
l 根据查询需要设计好索引
l 根据核心查询需求,适当调整表结构
l 基于一些特殊业务需求,调整实现方式
4. 索引
l 正确使用索引
l 更新尽可能使用主键或唯一索引
l 主键尽可能使用自增ID字段
l 核心查询覆盖扫描
用户登录需要根据用户名返回密码用于验证
create index idx_uname_passwd on tb_user (username, password);
建立联合索引避免回表取数据
5. 反范式,冗余必要字段
l 针对核心SQL保留查询结果所必须的冗余字段,避免频繁join
例:消息表中冗余了每次读消息必须返回的nickname字段,避免每次读消息都
变成join操作.代价是用户修改nickname成本变高.
6. 拆分大字段
l 拆分大字段到单独表中,避免范围扫描代价大
例:博文表拆分两份,标题表只保留标题和内容缩率部分,用于快速批量返回标题列表.正文表保存大段博文内容,用于点开文章单个读取
7. 避免过多字段或过长行
l 根据SQL必要返回设计字段,有必要就拆表,避免过多字段
l 一次没有必要获取那么多列数据
l 行过长导致表数据页记录变少,范围扫描性能降低
l 更新数据页代价增加
l 16k页最少放2行,可能出现行迁移
8. 分页查询
l 避免limit+offset过大
l 应该使用自增主键ID模拟分页
第一页,直接查
得到第一页的max(id)=123(一般是最后一条记录）
第二页,带上id>123查询: where id>123 limit 100
这样每次只需要扫描100条数据
l 要求业务上禁止查询xx页之后的数据
9. 热点读数据特殊处理
根据数据获取的频率或数量不同对热点数据做特殊处理
10. 准实时统计
对不需要精确结果的计数等统计要求,建立定期更新结果表
11. 实时统计改进1一触发器实时统计
对需要精确统计的计数利用数据库触发器维护计数表
12. 实时统计改进2–缓存实时统计
对需要精确统计的计数利用前端缓存实时维护计数
13. 实时统计改进3–最大自增ID获取总数
很多逻辑可以利用自增ID主键最大值直接作为总数
例:用户量冲亿活动要求实时统计,用户表加上自增ID作为主键,只要取当时
max(ID)就可以得到用户总数
14. 可扩展性设计
l 可扩展性
硬件资源增长有极限的情况下处理尽可能久的线上业务
l 数据分级,冷数据归档与淘汰
可以不断释放空间供新数据使用
l 为数据分布式做准备
分库分表
水平拆分
牺牲一定的关系模型支持
15. 分区表与数据淘汰
l range分区
l 适合数据需要定期过期的大表
l 单个分区扫描迁移数据到历史库避免全表扫描IO开销
l 删除单个分区非常高效
16. 分区表与垂直分区
l list分区
l 适合将来可能要基于地区,类目等方式垂直拆分数据的方式
l 清理节点上不要的数据非常高效
17. 分区表与水平分区
l hash分区
l 适合将来需要做水平拆分的表
l 清理节点上不要的数据非常高效
18. MySQL分区表的局限
l 主键或唯一键必须包含在分区字段内
l 分区字段必须是整数类型,或者加上返回整数的函数
19. 满足周边需求
l 为周边需求额外增加表设计
l 为后台统计任务增加特殊索引
l 为数据迁移或统计需求增加时间戳
20. 统计和后台需求
l 统计运行SQL往往和线上有很大不同
l 利用MySQL一主多从,主从可以建不同索引的特性将统计分流到特定从库
l 包括一些特殊用户批量查询等,所有对线上有IO压力的查询都要读写分离
21. 自动更新时间戳
l 统计需求经常要求从线上读走增量数据
l 表的第一个timestamp类型字段在写入时如果不填值,会自动写入系统时间戳
l 表的第一个timestamp类型字段每次记录发生更新后都会自动更新
l 在update_time字段上建索引用于定时导出增量数据
22. Schema设计与前瞻性
l 基于历史经验教训预防和解决同类问题
l 把折腾DBA够呛的所有Schema改造的原因记录并分析总结
例:
l 业务为了用户信息加密做了大改造
数据库结果大量改动,增加了加密字段,验证策略表,所有表重新订正数据等等
是否所有用到用户信息管理的应用都要去上线就用密文?
例：
l 程序bug误删数据,线上风险大
n 改造业务流程,不再删除数据,加入is_deleted标记位,经常给各种表加
n 今后的类似表是否一上线就都用标记位的方式,并加上修改原因字段?
l 支付类应用后期做了风控改造
n 对线上订单大表改造,加了限额,终端类型等字段
n 遇到支付类应用,是否一上线就提示业务是否需要考虑风控并留好相关字段?
第三节 MySQL业务审核与优化
1. 什么是业务审核
l 类似与code review
l 评审业务Schema和SQL设计
l 偏重关注性能
l 是业务优化的主要入口之一
n 审核提前发现问题，进行优化
n 上线后通过监控或巡检发现问题，进行优化
2. Schema设计审核
l 表和字段命名是否合规
l 字段类型，长度设计是否适当
l 表关联关系是否合理
l 主键,更新时间保留字段等是否符合要求
l 约束,默认值等配置是否恰当
l 了解业务,表数据量增长模式
l 数据访问模式，均衡度
l 根据业务需求,表是否需要分区,是否有数据什么周期
3. SLQ语句审核
l SQL语句的执行频率
l 表上是否有合适的索引
l 单次执行的成本
l 执行模式，锁情况分析
l 关注事务上下文
4. 什么时候需要审核
l 业务开发阶段,上线前
l 业务版本变更线上更新前
n 新表和SQL上线
n SQL查询条件变化
n SQL查询频率变化
n 业务逻辑导致现有表数据量规模变化
5. 业务发布流程
l SQL审核需要开发与应用运维支持
l 充分沟通，做好必要性说明和教育工作
l 制定业务发布流程,嵌入DBA审核环节
l 积累经验，不断完善评审方法
*第二章 MySQL容量评估
第一节 MySQL容量评估
l 了解线上服务器选型时应该关注的性能指标
l 了解线上数据库业务需要关注的特征
l 了解业务特征对性能指标的依赖关系
l 通过实际案例说明如何结合业务分析线上容量规划
1. 性能容量评估
l 分析线上业务场景
l 评估数据库服务器所需性能指标
l 预估可能成为瓶颈的服务器资源
l 帮助数据库性能调优
2.
 |
|
|---|---|
数据库服务器硬件性能指标项
磁盘IO性能、内存容量、CPU 、内存、网络吞吐量、磁盘容量
3. 数据库业务特点关键词
l OLTP/OL AP类型
电商，微博：OLTP系统
查询：OLAP系统
OLTP与OLAP
n T = Transaction
n 面向广大用户,高并发较短事务操作
n 互联网应用绝大部分属于OLTR
n OLTP看重服务器CPU,内存,写事务较多或内存不够则依赖磁盘I0
n A= Analytical
n 通常面向内部人员,大规模复杂查询
n OLAP看重磁盘扫描的IO能力,部分依赖内存排序
l 并发请求
并发请求-衡量线上业务繁忙程度
u 业务高峰时数据库的每秒并发访问量是多少
u 通过应用服务器数量,连接池配置判断
u 通过产品估算初上线用户规模和用户增长速度判断
u 通过实际业务业务类型判断
u 并发量相关资源:CPU
l 读写比例
读写比例-描述应用程序如何使用数据库
u 线_上业务select只读与update/delte/insert写操作比例
u delete/update通常都是先读再写
u insert需要区分数据写入是持续insert还是大量导入数据
u 根据业务实际场景分析
u 多读场景相关资源:内存
u 多写场景相关资源:磁盘IO
l 数据量
数据量-总量
u 数据库服务器存储设备可扩容能力的上限
u 根据估算的业务量写入模式分析数据增长量
u 预估-一个硬件升级周期内数据库可存放数据的总量,上线时要留好余量
u 数据总量相关资源:磁盘容量
l 冷热数据比
冷数据与热数据-有用数据的实时集合e
u 热数据,线上最新- 定周期内将被反复访问的数据
u 冷数据,线上保存着的,最近不会被在线用户用到的数据
u 估算活跃用户量,数据增长量等预估热数据量
u 内存大小尽可足够存放线上实时热数据
u 热数据相关资源:内存
l 数据分级存储
线上数据库分层存储-缓解线上磁盘空间压力
u 最新热数据确保放在内存中
u 还可能访问到的较早数据存放在线上库磁盘中
u 更早的不会常规访问的数据定期迁移至历史库中
u 区分哪些数据时效性强可以迁移
4. 服务器资源选型-可选方案
5. 案例：网易云音乐曲库数据库服务器评估
第二节 MySQL性能测试
了解MySQL常用的性能测试工具
能够根据需求针对业务做好性能测试
1. 为什么需要做性能测试
l 对线上产品缺乏心理预估
l 重现线.上异常
l 规划未来的业务增长
l 测试不同硬件软件配置
2. 性能测试分类
l 设备层的测试
l 业务层的测试
l 数据库层的测试
u MySQL测试分类
CPU Bound
IO Bound
写入测试、更新测试、纯读测试、混合模式——根据业务需求选择
u 开源的MySQL性能测试工具
sysbench
tpcc-mysql
mysqlslap
u 性能测试衡量指标：
服务吞吐量( TPS , QPS )
服务响应时间
服务并发性
3. 测试工具
A. Sysbench : 只有Linux版本
l 业界较为出名的性能测试工具
l 可以测试磁盘、CPU. 数据库
l 支持多种数据库: Oracle. DB2、 MySQL
l 需要自己下载编译安装
l 建议版本: sysbench0.5
Sysbench流程：
初始化—Prepare语法：
<br />测试- Run语法：<br /> <br /> <br /> <br /> <br /> <br /> <br />
数据清理- cleanup
l 手动drop掉表和database
l 使用sysbench提供的cleanup命令
TPcc 测试模型：Tpcc-mysq|
l TPC-C是专门针对联机交易处理系统( OLTP系统)的规范
l Tpcc-mysq|由percona根据规范实现
B. 使用tpcc-mysql的步骤
l 创建表结构.
create table.sql
add. fkey jidx.sql
l 创建表和索引
Tpcc-load
tpcc_ load [server] [DB] [user] [pass] [warehouse]
l 导入数据进行测试
l 总结：
u I0 Bound测试数据量要远大于内存、CPU Bound测试数据量要小于内存
u 测试时间建议大于60分钟,减小误差
u Sysbench更倾向于测试MySQL性能、TPCC更接近于业务
u 运行测试程序需要同时监控机器负载, MySQL各项监控指标
若有收获，就点个赞吧
0 人点赞
