MySQL数据库设计、优化.pptx

文档介绍：MySQL数据库设计、优化
叶金荣
微博: ***@yejinrong
微信公众号: MySQL中文网
QQ群: 125572178、272675472

提纲
规范
基础规范
命名规范
库表规范
字段规范
索引规范
开发环境
优化
规范
基础规范
全部使用InnoDB引擎,MyISAM适用场景非常少
字符集:latin1 => utf8 => gbk
用数据库来持久化存储以及保证事务一致性,不是运算器
读写分离,主库只写和少量实时读取请求
采用队列方式合并多次写请求,持续写入,避免瞬间压力
超长text/blob进行垂直拆分,并先行压缩
冷热数据进行水平拆分,LRU原则
快速更新大数据表禁止直接运行count(*)统计
规范
基础规范
单表行记录数控制在1000万以内,行平均长度控制在16KB以内,单表20GB以内
单实例下数据表数量不超过2000个,单库下数据表数量不超过500个
禁止开发环境直连线上生产环境
最少授权,只授予最基础权限需求
压力分散,在线表和归档表(日志表)分开存储
线上数据库和测试数据库尽可能保持一致
禁止明文存储机密数据,需至少两次加密(部分数据可逆运算)
规范
命名规范
涉及系统目录、文件、数据库、表、字段名
强烈建议只用小写字符、数字、下划线组合
命名长度不超过32个字符
不使用select、show、update等保留字
全英文或全中文,言之有意,不要半洋半中
临时用加上 tmp/temp 前缀/后缀
统计表加上 stat/statistic 前缀/后缀
历史归档加上完整日期,例如:20130802
mkdir -p /backup/user_log/2013/08
create table user_detail
create table xxx_1234
create table access_log_20130820
规范
库表规范
少用分区表等未完善的新特性
不对InnoDB引擎表做在线实时count(*)统计
分库、分表策略
以用户ID=123456为例,取N/100%10=4,取N%10=6
最大10个分库,10个分表,共100个分表
则分配到DB_04库下,分表TABLE_06中
采用预存映射关系动态分配更灵活,不受分表算法变化而影响,但数据库开销大
规范
字段规范
显式指定自增 int/bigint unsigned not null 作为主键
杜绝使用UUID/HASH/MD5类型作为主键
无须预留,越短越好,此处无须18cm O(∩_∩)O哈哈~
MySQL ,Online DDL越来越方便
显式约束:NOT NULL
规范
SQL规范
简化每一条SQL,短事务、快速执行、无阻塞
固定模式业务逻辑封装成存储过程
用括号显式确定AND、OR的先后顺序,避免混淆
注意引号问题会导致类型转换(where id = ‘1234’)
所有查询想尽一切办法使用索引:主键=>唯一索引=>索引
有些查询只需要扫描索引,无需扫描数据(SELECT id,user FROM table WHERE id = 1234)