MySQL数据库开发三十六条规定
  在 数据库 分类下   暂无评论

MySQL数据库开发三十六条规定

  在 数据库 分类下   暂无评论

导语

总是在灾难发生后,才想起容灾的重要性
总是在吃过亏后,才记得曾经有人提醒过

核心

尽量不在数据库做运算

别让脚趾头想事情,那是脑瓜子的职责,让数据库多做她擅长的事:

尽量不在数据库做运算
复杂运算移动程序端 CPU
尽可能简单应用 MySQL

例:

MD5() / ORDER BY RAND() (不推荐)

控制单表数据量

一年内的单表数据量预估:

纯 INT 不超过1000W
含 CHAR 不超过500W

合理分表不超载:

USERID
DATE
AREA
...

建议单库不超过300-400个表

保持表身段苗条

表字段数少而精
单表多少字段合适?
单表1G体积500W行评估
单表字段数上限控制在20~50个

平衡范式与冗余

平衡是门艺术:

严格遵循三大范式?
效率优先、提升性能
没有绝对的对与错
适当时牺牲范式,加入冗余
但会增加代码复杂度

拒绝3B

数据库并发像城市交通:

非线性增长

拒绝3B:

大 SQL(Big SQL)
大事务(Big Transaction)
大批量(Big Batch)

字段类

用好数值字段类型

三类数值类型:

TINYINT(1 Byte)
SMALLINT(2 Byte)
MEDIUMINT(3 Byte)
INT(4 Byte)
BIGINT(8 Byte)

FLOAT(4 Byte)
DOUBLE(8 Byte)

DECIMAL(M, D)

将字符转化为数字

数字型 VS 字符串型索引:

更高效
查询更快
占用空间更小

例:

用无符号 INT 存储 IP 而非 CHAR(15)
INT UNSIGNED
INET_ATON()
INET_NTOA()

优先使用 ENUMSET

优先使用 ENUMSET

字符串
可能值已知且有限

存储:

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)==>64KB(注意UTF-8)

尽量不使用 TEXT/BLOB 数据类型,若必须使用则拆分到单独的表

例:

CREATE TABLE t1 (
    id INT NOT NULL AUTO_INCREMENT,
    data text NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB;

不在数据库里寸图片

图1

图2

索引类

谨慎合理添加索引

谨慎合理添加索引:

改善查询
减慢更新
索引不是越多越好

能不加索引的尽量不加:

综合评估数据密度和数据分布
最好不超过紫椴树 20%

结合核心 SQL 优先考虑覆盖索引

例:

不要给'性别'列创建索引

字符字段必须建前缀索引

区分度:

单字母区分度:26
4字母区分度:26*26*26*26=456976
5字母区分度:26*26*26*26*26=11881376
6字母区分度:26*26*26*26*26*26=308915776

字符字段必须建前缀索引:

`pinyin` varchar(100) DEFAULT NULL COMMENT '小区配音',
KEY `index_pinyin' (`pinyin` (8)),
) ENGINE=INNODB

不在索引列做运算

不在索引列进行数学运算或函数运算:

无法使用索引
导致全表扫描

例:
图3

select * from table WHERE to_days(current_date) – to_days(date_col) <= 10 (不推荐)
select * from table WHERE date_col >= DATE_SUB('2011-10-22',INTERVAL 10 DAY); (推荐)

自增列或全局IDINNODB主键

对主键建立聚簇索引
耳机索引存储主键值
主键不应更新修改
按自增顺序插入主键
忌用字符串做主键
聚簇索引分裂
推荐用独立于业务的 AUTO_INCREMENT 列或全局 ID 生成器做代理主键
若不指定主键, INNODB 会用唯一且非空值索引代替

尽量不用外键

线上 OLTP 系统(线下系统另论):

外键可节省开发量
有额外开销
逐行操作
可’到达‘其它表,意味着锁
高并发时容易死锁

由程序保证约束

SQL类

SQL 尽可能简单

大 SQL VS 多个简单 SQL:

传统设计思想
BUT MYSQL NOT
一条 SQL 只能在一个 CPU 运算
5000+ QPS 的高并发中,1秒大 SQL 意味着?
可能一条大 SQL 就把整个数据库堵死

拒绝大 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 keywords FROM tag WHERE id=999184 (推荐)

改写ORIN()

同一字段,将OR改写为IN()

OR效率:O(n)
IN 效率:O(log n)
当n很大时,OR会慢很多

注意控制IN的个数,建议n小于200

例:

SELECT * FROM opp WHERE phone='123' OR phone='456' (不推荐)
SELECT * FROM opp WHERE phone IN ('123', '456') (推荐)

改写ORUNION

不同字段,将OR改为UNION

减少对不同字段进行OR查询
Merge index 往往很弱智
如果有足够的信心:set global optimizer_switch='index_merge=off'

例:

SELECT * FROM opp WHERE phone='010-123 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.01 sec) 
MySQL> select * from post WHERE title like '%北京%' ; 572 rows in set (3.27 sec)

COUNT(*) 的几个例子

几个有趣的例子:

`id` int(10) NOT NULL AUTO_INCREMENT COMMENT '公司id', 
`sale_id` int(10) unsigned DEFAULT NULL,

COUNT(COL) VS COUNT()*
COUNT() VS COUNT(1)*
COUNT(1) VS COUNT(0) VS COUNT(100)

示例:图4

结论:

COUNT(*) = COUNT(0) = COUNT(1) = COUNT(100)
COUNT(*) != COUNT(COL)

减少 COUNT(*)

MyISAM VS INNODB

不带 WHERE COUNT()
带 WHERE COUNT()

COUNT(*) 的资源开销大,尽量少用

计数统计:

实时统计:用 Memcache 双向更新,凌晨跑基准
非实时统计:尽量用单独统计表,定期重算

LIMIT 高效分页

传统分页:

SELECT * FROM table LIMIT 100000,10

LIMIT 原理:

LIMIT 100000,10,偏移量越大则越慢

推荐分页:

  1. SELECT * FROM table WHERE id>=23434 LIMIT 11
  2. SELECT * FROM table WHERE id>=(SELECT id FROM table LIMIT 10000,1) LIMIT 10
  3. SELECT FROM table INNER JOIN (SELECT id FROM table LIMIT 10000,10) USING (id)*
  4. 程序取id: SELECT id FROM table LIMIT 10000,10
    SELECT * FROM table WHERE id IN (123,456)

可能需按场景分析并重组索引

例:

MySQL> select sql_no_cache * from post limit 10,10; 10 row in set (0.01 sec) 
MySQL> select sql_no_cache * from post limit 20000,10; 10 row in set (0.13 sec) 
MySQL> select sql_no_cache * from post limit 80000,10; 10 rows in set (0.58 sec) 
MySQL> select sql_no_cache id from post limit 80000,10; 10 rows in set (0.02 sec) 
MySQL> select sql_no_cache * from post WHERE id>=323423 limit 10; 10 rows in set (0.01 sec) 
MySQL> select * from post WHERE id >= ( select sql_no_cache id from post limit 80000,1 ) limit 10 ; 10 rows in set (0.02 sec)

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 on tag_post.tag_id=tag.id JOIN post on tag_post.post_id=post.id WHERE tag.tag=‘二手玩具’;
MySQL> Select * from tag 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.19 sec) 
MySQL> select phone,count(*) from post group by phone order by null limit 1; 1 row in set (2.02 sec)

同数据类型的列值比较

原则:数字对数字,字符对字符
数值列与字符类型比较

同时转换为双精度
进行比对

字符列与数值类型比较

字符列整列转数值
不会使用索引查询

例(字符列与数值类型比较):

字段:`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.14 sec) 
MySQL>SELECT `id`, `gift_code` FROM pool_gift WHERE `deal_id` = 640 AND remark='115127'; 1 row in set (0.005 sec)

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

图5

约定类

隔离线上线下

构建数据库的生态环境:

开发无线上数据库权限

原则:线上连线上,线下连线下

实时数据用real库
模拟环境用sim库
测试用qa库
开发用dev库

案例:图6

禁止未经 DBA 确认的子查询

MySQL 子查询:

大部分情况优化较差
特别WHERE中使用IN id的子查询
一般可用JOIN改写

例:

MySQL> select * from table1 where id in (select id from table2); (不推荐) 
MySQL> insert into table1 (select * from table2); //可能导致复制异常 (不推荐)

永远不在程序端显示加锁

永远不在程序端对数据库显式加锁:

外部锁对数据库不可控
高幵发时是灾难
极难调试和排查

幵发扣款等一致性问题:

采用事务
相对值修改
Commit前二次较验冲突

统一字符集为 UTF8

字符集:

MySQL 4.1 以前叧有latin1
为多语言支持增加多字符集
也带来了N多问题
保持简单

图7

统一字符集:UTF8
校对规则:utf8_general_ci
乱码:SET NAMES UTF8

统一命名规范

库表等名称统一用小写:

Linux VS Windows
MySQL库表大小写敏感
字段名的大小写丌敏感

索引命名默认为“idx_字段名”

库名用缩写,尽量在2~7个字母

DataSharing ==> ds

注意避免用保留字命名

……

注意避免用保留字段命名

例:

SELECT * FROM return (不推荐)
SELECT * FROM `return` (推荐)

图8

评论已关闭