DBA SQL Review
Schema REview的注意事项
SQL review的注意事项
线上Schema 分析优化技巧
DBA Review 工作内容
表字段、索引设计优化
字段类型(针对业务、故障等类型去确定字段)
注释标准度
分区表约束
SQL编写规范
DML编写规范
子查询约束
函数使用
优化的目的:
为开发人员提出更高的建议
Schema REview的注意事项
Schema REview的目标
功能实现为主
保证节省资源
平衡业务技术各个方面,做好取舍
让数据库干自身擅长的工作
不在在DB里进行操作
减少复杂操作
字段数量
建议不超过20-50个
做好数据评估
建议纯int不超过1000万,含有char的不要超过800万
非核心表另议
可以考虑反范式设计
适合的冗余设计,减少join
核心表尽可能精简
日志表可进行水平分表
注意引擎区别 Innodb & Tokudb
Tokudb 减少update操作 (更新数据 表会变得很大)
字段设计
主键 innodb表是以主键排序存储IOT尽量使用短,自增的列做索引,复制结构中row
格式中,如果表有主键可以加速复制。
INT 无符号自增列 可以考虑BIG int
可用uuid_short()代替uuid 转成bigint 存储
注意潜在风险
tinyint 做大表主键可能导致mysql crashed
类型转型导致查询效率很低
mysql在开发上面的特点
(1)每个query 只能用到一个core(处理层)
(2)没有执行的缓存
(3)mysql默认情况下,随着连接数的增加。性能会下降 (基于连接数的压力测试)
(4)校验式嵌套处理 没有hashjoin
在主从复制结构中从库对主键的选择
(1)会选择主键
(2)会选择有效的索引
(3)全表扫描
针对高速写入的环境的主键设计
字符集问题
Emoji表情 表示用utf8mb4
将字符转数字存储
利用int 存储ip 而非char(15)
INET_ATON() &INET_NTOA()
将日期转换成数字
from_unixtime()
unix_timestamp()
null与 not null有什么坑?
C1 vchar(16)default null 不建议
C1 vchar(16)default not null 不建议
C1 vchar(16)default not null default '' 建议
schema Review
工具:
利用pt-mysql-summar 指定DB分析
利用pt-duplicate-key-checker 指定DB 查看重复索引、重复主键 官方手册
功能环境记录全量慢日志用于分析
SQL Review注意事项
SQL Review 总则
避免线上系统出现大操作
全面使用索引
优化join
去除无意义逻辑
注重查看where条件
除了select 语句,没有where条件的可以直接去掉
where条件字段 区别度高字段,注意建索引
like不要出现以%开头的查询
对于出现子查询的sql,要确定上线的mysql版本,利用explain确认
避免使用sslect *,fa方便调整字段列表,还可以减少不必要的I/O
insert 要对字段写入
整个SQL要用explain确认
去除无意义的操作
很多SQL是生成的。如ibatis,Hibernate 生成的类的SQL
其他框架生成的SQL
复杂类的SQL中无意义逻辑去除
不必要的括号也可以去除
优化join
控制最多三层join建议2个以下
小表驱动大表
字典 常用表 其他表排序
控制join后面where条件选择的的行数,尽量在1000行以下
使用union all 代替union
减少临时表出现
避免线上大的操作
分批多次操作
大事务拆分成多个事务区分间操作
频繁的查询考虑适当的缓存
对于text,blob字段。适当进行拆分