DBA: SQL review

DBA review

why: 加强sql及各方面的规范, 让系统能跑得更稳定
表字段/索引设计优化: 字段类型 注释标准度 分区表约束
sql编写规范: DML编写规范 子查询约束 函数使用

schema review

目标: 功能实现为主 节省资源(全是varchar) 平衡业务/技术各个方面(取舍)
让数据库做自己擅长的事: 不要在DB里计算 减少复杂操作
字段数: <=20~50
数据评估: int<=1000w char<=800w 非核心表另议
反范式设计: 适合冗余, 减少join
核心表: 尽可能精简
日志表: 水平分表
InnoDB vs TokuDB(log表)

字段

主键: InnoDB 使用主键排序存储IOT表, 尽量使用短/自增 列作为索引
int/bigint; uuid_short() 提到 uuid()
tinyint 做大表主键可能 mysql crashed; 类型转换导致查询效率低
Emoji 使用 utf8mb4
字符 -> 数字, ip inet_aton()/inet_ntoa()
日期 -> 数字, from_unixtime()/unix_timestamp()
null/not null 有什么坑: default not null default '', null 会导致索引失效, not null 会导致无插入值时报错
案例: bigint 比 int 多 40% 存储

索引

所有表必须有显示主键
视使用情况给其他字段添加索引: 好处&坏处; 减少对索引列更新
Innodb 普通索引存储后会包含主键
复合索引注意排序问题
explain 去确认

工具

pt-mysql-summary: 指定db分析
pt-duplicate-key-checker: 指定DB, 查看重复索引
慢日志

sql review

避免线上系统出现大操作, 比如一次操作 10w 行
全面使用索引
优化join
去除无意义逻辑
避免 select *, 方便调整字段顺序, 避免不要的 I/O 读
insert 要对字段写入
整个sql要用explain确认

注意where条件

除了select, 没有where条件的可以直接拒掉, 避免全表操作导致的故障
where 条件/区别度高 字段 -> 索引
like -> 不要使用 % 开头的查询
子查询的sql, 要注意mysql 版本, 一定要 explain 确认是否可以用到索引

去除无意义的操作

很多sql是生成的, 如: ibatis, hibernate
其他框架生成的sql
复杂类的sql中无意义逻辑去除
不必要的括号也可以去除, 如: 子查询 where in -> join

优化join

控制层数<=3, 建议2个以下
小表驱动大表
控制join后面where条件选择的行数<=1000
union all 代替 union: 有其他更快的方式过滤重复数据
减少临时表出现

避免线上大操作

分批多次操作
大事务 -> 多个 小事务
频繁查询 -> cache
text/blob -> 适当拆分

线上 schema 分析技巧

了解每个表的大小, 每天执行一次, 方便历史分析

select table_name, engine, concat(round(table_rows/1000000,2), ‘M’)rows, concat(round(data_length/1024/1024/1024),2), ‘G’) data,concat(round(index_length/1024/1024/1024),2), ‘G’) idx,concat(round((data_length+index_length)/1024/1024/1024,2),’G’)total_size, round(index_length/data_length,2) idx frac frominformation_schema.tables where table_schema in (‘wubx’) group bytable_name, table_schema order by data_length+index_length desc ;

mysql5.7 sys

线上 sql 分析技巧

pt-query-diget: 慢日志分析
box/Anemometer: 慢日志分析, 有web端查看
pt-kill: 干掉执行炒作50s的sql, 避免数据库被hang住