关闭

MySQL数据库开发的三十六条军规

697人阅读 评论(0) 收藏 举报
分类:
导语
  • 来自一线的实战经验
  • 每一条军规背后都是血淋淋教训
  • 不要华丽,只要实用
  • 若有一条让你受益,慰矣
  • 主要针对数据库开发人员
总是在灾难发生后,才想起容灾的重要性
总是在吃过亏后,才记得曾经有人提醒过

目录
一,核心军规(5)
二,字段类军规(6)
三,索引类军规(5)
四,SQL类军规(15)
五,约定类军规(5)

一,核心军规
尽量不在数据库做运算
  • 别让脚趾头想事情
  • 那是脑瓜子的职责
  • 让数据库多做她擅长的事情
    • 尽量不在数据库做运算
    • 复杂运算移到程序端CPU
    • 尽可能简单应用MySQL
  • 举例:md5() / order by rand()
控制单表数据量
  • 一年内的单表数据量预估
    • 纯INT不超过1000w
    • 含CHAR不超过500w
  • 合理分表不超载
    • USERID
    • DATE
    • AREA
    • ...
  • 建议单库不超过300-400个表
保持表身段苗条
  • 表字段数少而精
    • IO高效,全表遍历,表修复快,提高并发,alter table快
  • 单表多少字段合适?
  • 单表1G体积 500w行评估
    • 顺序读1G文件需N秒
    • 单行不超过200byte
    • 单表不超过50个纯INT字段
    • 单表不超过20个CHAR(10)字段
  • 单表字段数上线控制在20-50个
平衡范式与冗余
  • 平衡是门艺术
    • 严格遵循三大范式?
    • 效率优先、提升性能
    • 没有绝对的对与错
    • 适当时牺牲范式,加入冗余
    • 但会增加代码复杂度
拒绝3B
  • 数据库并发像城市交通
    • 非线性增长
  • 拒绝3B
    • 大SQL(Big SQL)
    • 大事务(Big transaction)
    • 大批量(Big batch)
  • 详细解析见后
核心军规小结
  • 尽量不在数据库做运算
  • 控制单表数据量
  • 保持表身段苗条
  • 平衡范式与冗余
  • 拒绝3B

二,字段类军规
用好数值字段类型
  • 三类数值类型
    • TINYINT(1 byte)
    • SMALLINT(2B)
    • MEDIUMINT(3B)
    • INT(4B), BIGINT(8B)
    • FLOAT(4B), DOUBLE(8B)
    • DECIMAL(M,D)
将字符转化为数字
  • 数字型VS字符串型索引
    • 更高效
    • 查询更快
    • 占用空间更小
  • 举例:用无符号INT存储IP,而非CHAR(15)
    • INT UNSIGNED
    • INET_ATON()
    • INET_NTOA()
优先使用ENUM或SET
  • 优先使用ENUM或SET
    • 字符串
    • 可能值已知且有限
  • 存储
    • ENUM占用1字节,转为数值运算
    • SET视节点定,最多占用8字节
    • 比较时需要加'单引号(即使是数值)
  • 举例
    • `sex` enum('F','M') COMMENT '性别'
    • `c1` enum('0','1','2','3') COMMENT '职介审核'
避免使用NULL字段
  • 避免使用NULL字段
    • 很难进行查询优化
    • NULL列加索引,需要额外空间
    • 含NULL符合索引无效
  • 举例
    • `a` char(32) DEFAULT NULL
    • `b` int(10) NOT NULL
    • `c` int(10) NOT NULL DEFAULT 0
少用并拆分TEXT/BLOB
  • TEXT类型处理性能远低于VARCHAR
    • 强制生成硬盘临时表
    • 浪费更多空间
    • VARCHAR(65535)==>64k(注意UTF-8)
  • 尽量不用TEXT/BLOB数据类型
  • 若必须使用则拆分到单独的表
  • 举例:CREATE TABLE t1( id INT NOT NULL AUTO_INCREMENT, data text NOT NULL, PRIMARY KEY(id))ENGINE=innodb;
不在数据库里存图片

字段类军规小结
  • 用好数值字段类型
  • 将字符转化为数字
  • 优先使用枚举ENUM/SET
  • 避免使用NULL字段
  • 少用并拆分TEXT/BLOB
  • 不在数据库里存图片

三,索引类军规
谨慎合理添加索引
  • 谨慎合理添加索引
    • 改善查询
    • 减慢更新
    • 索引不是越多越好
  • 能不加的索引尽量不加
    • 综合评估数据密度和数据分布
    • 最好不超过字段数20%
  • 结合核心SQL优先考虑覆盖索引
  • 举例
    • 不要给"性别"列创建索引
字符字段必须建前缀索引
  • 区分度
    • 单字母区分度:26
    • 4字母区分度:26*26*26*26=456,,976
    • 5字母区分度:26^5=11,881,376
    • 6字母区分度:26^6=308,915,776
  • 字符字段必须建前缀索引
    • `pinyin` varchar(100) default null comment '小区拼音', key `idx_pinyin`(`pinyin`(8))) engine=innodb
  • 不在索引列进行数学运算或函数运算
    • 无法使用索引
    • 导致全表扫描
  • 举例
    • BAD:select * from table where to_days(current_date)-to_days(date_col)<=10
    • GOOD:select * from table where date_col>=date_sub('2011-10-22',interval 10 day)
自增列或全局ID做INNODB主键
  • 对主键建立聚簇索引
  • 而建索引存储主键值
  • 主键不应更新修改
  • 案子增顺序插入值
  • 忌用字符串做主键
  • 聚簇索引分裂
  • 推荐用独立于业务的AUTO_INCREMENT列或全局ID生成器做代理主键
  • 若不指定主键,InnoDB会用唯一且非空值索引代替
尽量不用外键
  • 线上OLTP系统(线下系统另论)
    • 外键可节省开发量
    • 有额外开销
    • 逐行操作
    • 可’到达‘其他表,意味着锁
    • 高并发时容易死锁
  • 由程序保证约束
索引类军规小结
  • 谨慎合理添加索引
  • 字符字段必须建前缀索引
  • 不在索引列做运算
  • 自增列或全局ID做INNODB主键
  • 尽量不用外键

四,SQL类军规
SQL语句尽可能简单
  • 大SQL VS多个简单SQL
    • 传统设计思想
    • BUT MySQL NOT
    • 一条SQL之恩能够在一个CPU运算
    • 5000+QPS的高并发中,1秒大SQL意味着?
    • 可能一条大SL就把整个数据库堵死
  • 拒绝大SQL,拆解成多条简单SQL
    • 简单SQL缓存命中率更高
    • 减少锁表时间,特别是MyISAM
    • 用上多CPU
保持事务(连接)短小
  • 保持事务/DB连接短小精悍
    • 事务/连接使用原则:即开即用,用完即关
    • 与事务无关操作放到事务外面,减少锁资源的占用
    • 不破坏一致性前提下,使用多个短事务代替长事务
  • 举例
    • 发帖时的图片上传等待
    • 大量的sleep连接
尽可能避免使用SP/TRIG/FUNC
  • 线上OLTP系统(线下库另论)
    • 尽可能少用存储过程
    • 尽可能少用触发器
    • 减用使用MySQL函数对结果进行处理
  • 由客户端程序负责
尽量不使用SELECT *
  • 用select *时
    • 更多消耗CPU、内存、IO、网络带宽
    • 先向数据库请求所有列,然后丢掉不需要列?
  • 尽量不使用select*,只取需要数据列
    • 更安全的设计:减少表变化带来的影响
    • 为使用covering index提供可能性
    • select/join减少硬盘临时表生成,特别是有TEXT/BLOB时
  • 举例
    • select * from tag where id=999184
    • select keyword from tag where id=999184
改写OR为IN()
  • 同一字段,将or改为in()
    • or效率:O(n)
    • in效率:O(log n)
    • 当N很大时,or会慢很多
  • 注意控制IN的个数,建议n小于200
  • 举例
    • select * from opp where phone ='123456' or hple ='1235516'
    • select * from opp where phone in('123456' ,'1235516')
改写or为union
  • 不同字段,将or改为union
    • 减少对不同字段进行"or"查询
    • merge index往往很弱智
    • 如果有足够信心:set global optimizer_switch='index_merge=off'
  • 举例
    • select * frmo opp where phone='010-88886666' or cellphone='13800138000'
    • select * from opp where phone='010-88886666' union select * from opp where cellphone='13800138000'
避免负向查询和%前缀模糊查询
  • 避免负向查询
    • not, !=, <>, !<, !>, not exists, not in, not like等
  • 米面%前缀模糊查询
    • B+ tree
    • 使用不了索引
    • 导致全表扫描
  • 举例
    • MySQL> select * from post where title like '北京%';
    • 298 rows in set(0.01sec)
    • MySQL> select * from post where title like '%北京%';
    • 572 rows in set(3.27sec)
count(*)的几个例子
  • 几个有趣的例子:
    • count(col) VS count(*)
    • count(*) VS count(1)
    • count(1) VS count(0) VS count(100)
  • 结论
    • count(*) = count(1)
    • count(0) = count(1)
    • count(1) = count(100)
    • count(*) != count(col)
    • WHY?
减少count(*)
  • MyISAM VS InnoDB
    • 不带where count()
    • 带where count()
  • count(*)的资源开销大,尽量不用少用
  • 计数统计
    • 实时统计:用memcache,双向更新,凌晨跑基准
    • 非实时统计:尽量用单独统计表,定期重算
LIMIT高效分页
  • 传统分页
    • select * from table limit 10000,10;
  • LIMIT原理:
    • limit 10000,10
    • 偏移量越大则越慢
  • 推荐分页:
    • select * from table where id>=23423 limit 11; #10+1(每页10条)
    • select * from table where id>=23434 limit 11;
LIMIT的高效分页
  • 分页方式二:
    • select * from table where id >=(select id from table limit 10000,1)limit 10;
  • 分页方式三:
    • select * from table inner join (select id from table limit 10000,1) using(id);
  • 分页方式四:
    • 程序取ID:select id from table limit 10000,10;
    • select * from table where id in(123,456,...)
  • 可能需按场景分析并重组索引
LIMIT的高效分页
  • 示例
    • MySQL> select sql_no_cache * from post limit 10,10;
    • 10 row in set(0.01sec)
    • MySQL> select sql_no_cache * from post limit 20000,10;
    • 10 row in set(0.13sec)
    • MySQL> select sql_no_cache * from post limit 80000,10;
    • 10 rows in set(0.58sec)
    • MySQL> select sql_no_cache id from post limit 80000,10;
    • 10 rows in set(0.02sec)
    • MySQL> select sql_no_cache * from post where id>=323423 limit 10;
    • 10 rows in set(0.01sec)
    • MySQL> select * from post where id >=(select sql_no_cache id from post limit 80000,1) limit 10;
    • 10 rows in set(0.02sec)
用UNION ALL而非UNION
  • 若无需对结果进行去重,则用UNION ALL
    • UNION有去重开销
  • 举例
    • MySQL> select * from detail20091128 UNION ALL
    • select * from detail20110427 union all
    • select * from detail20110426 union all
    • select * from detail20110425 union all
    • select * from detail20110424 union all
    • select * from detail20110423;
分解连接保证高并发
  • 高并发DB不建议进行两个表以上的JOIN
  • 适当分解连接保证高并发
    • 可换成大量早期数据
    • 使用了多个MyISAM表
    • 对大表的小ID IN()
    • 连接引用同一个表多次
  • 举例
      • MySQL> select * from tag join tag_post n tag_post.tag_id=tag.id join post on tag_post.post_id=post.id where tag.tag='二手玩具'
      • ->
      • MySQL> select * from tab where tag='二手玩具';
      • MySQL> select * from tag_post where tag_id=1321;
      • MySQL> select * from post where post.id in(123,456,314,141);
GROUP BY 去除排序
  • GROUP BY实现
    • 分组
    • 自动排序
  • 无需排序:order by null
  • 特定排序:group by desc/asc
  • 举例
  • MySQL> select phone, count(*) from post group by phone limit 1;
  • 1 row in set(2.19sec)
  • MySQL> select phone,count(*) from post group by phone order by null limit 1;
  • 1 row in set(2.02sec)
同数据类型的列植比较
  • 原则:数字对数字,字符对字符
  • 数值列于字符类型比较
    • 同时转换为双精度
    • 进行比对
  • 字符列与数值类型比较
    • 字符列整列转数值
    • 不会使用索引查询
同数据类型的列植比较
  • 举例:字符列与数值类型比较
    • 字段: `remark` varchar(50) not null comment '备注,默认为空';
    • MySQL> select `id`,`gift_code` from gift where `deal_id` = 640 and remark=115127;
    • 1 row in set(0.14sec)
    • MySQL> select `id`,`gift_code` from pool_gift where `deal_id`=640 and remark='115127';
    • 1 row in set(0.005sec)
Load data 导数据
  • 批量数据快导入:
    • 成批装载比单行装载更快,不需要每次刷新缓存
    • 无索引时装载比索引装载更快
    • insert values, values, values减少索引刷新
    • load data比insert快约20倍
  • 尽量不用insert...select
    • 延迟
    • 同步出错
打散大批量更新                                                                                                                                                                                                                        
  • 大批量更新凌晨操作,避开高峰
  • 凌晨不限制
  • 白天上线默认为100条/秒(特殊再议)
  • 举例:
    • update post set tag=1 where id in (1,2,3);
    • sleep 0.01;
    • update post set tag=1 where id in (4,5,6);
    • sleep 0.01;
    • ...
know every SQL
  • show profile
  • MySQLsla
  • MySQLdumpslow
  • explain
  • show slow log
  • show processlist
  • show query_response_time(percona)
SQL类军规小结
  • SQL语句尽可能简单
  • 保持事务(连接)短小
  • 尽可能避免使用SP/TRIG/FUNC
  • 尽量不用select *
  • 改写or语句
  • 避免负向查询和%前缀模糊查询
  • 减少count(*)
  • limit的高效分页
  • 用union all 而非union
  • 分解连接保证高并发
  • group by去除排序
  • 统数据类型的列植比较
  • load data导数据
  • 打散大批量更新
  • know every SQL

五,约定类军规
隔离线上线下
  • 构建数据库的生态环境
    • 开发无线上库操作权限
  • 原则:线上连线上,线下连线下
    • 实时数据用real库
    • 模拟环境用sim库
    • 测试用qa库
    • 开发用dev库
  • 案例
禁止未经DBA确认的子查询
  • MySQL子查询
    • 大部分情况优化较差
    • 特别where汇总使用in id的子查询
    • 一般可用join改写
  • 举例:
  • MySQL> select * from table 1 where id in(select id from table2);MySQL> insert into table1 (select * from table2); //可能导致复制异常
永远不在程序端显式加锁
  • 永远不在程序端对数据库显式加锁
    • 外部锁对数据库不可控
    • 高并发时是灾难
    • 极难调试和排查
  • 并发扣款等一致性问题
    • 采用事务
    • 相对值修改
    • commit前二次校验冲突
统一字符集为UTF8
  • 字符集
    • MySQL4.1以前只有latin1
    • 为多语言支持增加多字符集
    • 也带来N多问题
    • 保持简单
  • 统一字符集:UTF8
  • 校对规则:utf8_general_ci
  • 乱码:set names utf8
统一命名规范
  • 库表等名称统一用小写
    • linux VS windows
    • MySQL库表大小写敏感
    • 字段名大小写不敏感
  • 索引命名默认为"idx_字段名"
  • 库名用缩写,尽量在2~7个字母
    • datasharing==>ds
  • 注意避免用保留字命名
  • ...
约定类军规小些
  • 隔离线上线下
  • 禁止未经DBA确认的子查询上线
  • 永远不在程序端显式加锁
  • 统一字符集为UTF8
  • 统一命名规范
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:33288次
    • 积分:474
    • 等级:
    • 排名:千里之外
    • 原创:2篇
    • 转载:68篇
    • 译文:2篇
    • 评论:0条