1.避免使用NULL字段
NULL字段很难查询优化,NULL字段的索引需要额外空间,NULL字段的复合索引无效,
NULL非常影响索引的查询效率 建议用0,特殊值或空串代替NULL值
age int NOT NULL DEFAULT 0; name varchar(30) NOT NULL DEFAULT '';
在对该字段进行COUNT()统计时,统计结果更准确或者执行WHERE column IS NULL检索时能快速返回结果。
2.拒绝3B
大sql,大事务,大查询.
避免在一个事务里有大查询存在这样会导致主从延迟。
3.存储ip
用int unsigned 而不是char(15)存储ip
select inet_aton('192.168.0.1');
select inet_ntoa(3232235521);
4.禁止使用存储过程,触发器,视图,自定义函数等。
因为procedure trigger function views event 外键约束会降低集群扩展,用程序来实现。
5.索引添加
select,update,delete的where条件列
order by,group by,distinct字段,多表join字段。
like 'abc%'可以用到索引 '%abc%'用不到
where条件里等号左右字段类型必须一致,否则无法利用索引
区分度最高而且使用比较频繁的列放在联合索引的最左侧,比如查询一个用户号码且支付状态,我们知道手机号码基数高,而状态基数低,但往往这两个常常组合使用,则建立idx_xxx(user_phone,user_status)
联合索引(a,b,c)相当于(a) (a,b) (a,b,c)
6.库名,表名,字段名禁止使用MySQL保留字。
例如status,name,names,number,month,hosts,year等
7.使用TINYINT来代替ENUM类型。
状态status,类型type等字段用tinyint类型节省存储空间
sex tinyint NOT NULL default 0 comment '0:男 1:女';
8.禁止在数据库中存储图片,文件等大数据。
9.没有预编译耗cpu,随着连接会话增多,性能降低
每个连接都是一个线程(非thread pool)
一个连接使用一个cpu,5.6最多能用到64核,所以严禁使用大查询语句会占用资源
10.数据库的校验字段
dba专有的校验字段,理论上业务不能用到,核心表(用户表,金钱相关)便于查问题
create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
is_delete tinyint NOT NULL DEFAULT 0 COMMENT '是否删除 0:有效 1:删除',
11.计量单位精确值
金钱 decimal(N,2) 精确到分
费率 decimal(N,5) 精确到0.005%
里程 decima(N,2) 精确到公里
12.时间类型用int来存储时间
birthday_time int unsigned NOT NULL DEFAULT 0;
select unix_timestamp('2018-02-23 09:53:57');
select from_unixtime(1519350837);
13.插入语句
insert语句指定具体字段名称,而且要批量插入insert into…values(XX),(XX),(XX)值不要超过5000个,值过多虽然上线很快,但会引起主从同步延迟。
14.字段类型避免浪费空间
int 4字节 -2^31 (-2,147,483,648) 到 2^31 - 1 (2,147,483,647)
bigint 8字节 -2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807)
tinyint 1字节 -128 - 127 unsigned无符号 0-255
smallint 2字节 -2^15 (-32,768) 到 2^15 - 1 (32,767) unsigned无符号0-65535
15.varchar与text区别
大于varchar(255)变为tinytext,大于varchar(500)变为text
对于text字段,MySQL不允许有默认值。varchar允许有默认值
尽量用varchar,超过255字节的只能用varchar或者text
一般建议用varchar类型,字符数不要超过2700
16.尽量选择数字类型来代替字符类型
能够用数字类型的字段尽量选择数字类型而不用字符串类型的(电话号码),这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接回逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。
17.禁止在where条件列上使用函数
会导致索引失效,如lower(email),f_price%4,可放到右边的常量上计算
lower(f_name)='ruining' 改为 f_name=lower('RuiNing')
18.多表JOIN
要把过滤性最大的表选为驱动表,此外,如果JOIN之后有排序,排序字段一定要属于驱动表,才能利用驱动表上的索引完成排序。
先筛选然后join.尽量把结果集缩到最小然后join
19.默认使用InnoDB存储引擎,字符集选择utf-8,字段表名都需要注释,
数据库xxx_db表示,表以业务名区分,
标准的建表语句如下:
create database test_db default character set utf-8;
create table wms_user(
user_id int unsigned primary key auto_increment comment '客户主键',
user_name varchar(30) NOT NULL default '' comment '客户姓名',
user_phone int unsigned NOT NULL default 0 comment'客户手机号码',
user_age int NOT NULL default 0 comment '客户年龄',
user_sex tinyint NOT NULL default '0' comment '客户性别 0:男 1:女',
user_birthday date NOT NULL default '1001-01-01' comment '客户出生日期',
user_status tinyint NOT NULL default '0' comment '客户状态 0:未激活 1:激活',
create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
is_delete tinyint NOT NULL DEFAULT 0 COMMENT '是否删除 0:有效 1:删除',
key idx_user_name(user_name),
key idx_phone_status(user_phone,user_status),
key idx_user_birthday(user_birthday)
)engine=innodb auto_increment=1 default charset=utf8 comment='仓库用户信息';
20.mysql字段值不区分大小
使用varbinary存储大小写敏感的变长字符串
name varbinary(N) 这里的N指的是字节而不是长度,值会区分大小写
21.加前缀索引
长度超过50的varchar字段,最好创建前缀索引而非整列索引add index(f_name(20));
不过它的缺点是对这个列排序时用不到前缀索引
22.禁止在数据库中存储明文密码。
采用加密字符串存储密码,并保证密码不可解密
23.合并ddl语句
针对同一个表的结构变更,写为一个sql
属性,注释都要填写完整
alter table wms_good add column price decimal(9,2) NOT NULL DEFAULT '0.00' comment '货物价格';
alter table wms_good add column birthday int unsigned NOT NULL DEFAULT '0' comment '生产日期';
以上多个语句改写为一个:
alter table wms_good
add column price decimal(9,2) NOT NULL DEFAULT '0.00' comment '货物价格',
add column birthday int unsigned NOT NULL DEFAULT '0' comment '生产日期',
add index idx_birthday(birthday);
24.int(4)与int(11)的区别
这两个没区别,只是设置了zerofill属性有用,这两个都占用4个字节,在设置0填充时,如果存储100数字将会变成 0100 左侧填充
num1 int zerofill
25.or改为in,union
针对同一个字段
where province='河北' or province='山东' 改为 where province in ('河北','山东');
针对索引合并
where province='河北' or phone='137' 改为
select id from t where province='河北'
union
select id from t where phone='137';
26.避免数据类型不一致
select * from t where id='19'; 用不到索引
select * from t where id=19;
27.
日期的查询,建议用int型,不用DATE, 如20160909等