1、mysql数据库则必须使用InnoDB存储引擎
2、连接的客户端也使用utf8,若遇到EMOJ等表情符号的存储需求,可申请使用UTF8MB4字符集
3、注释及时更新
4、字段允许适当冗余,以提高查询性能,但必须考虑数据一致。
5、分库分表:单表行数超过 500 万行或者单表容量超过 2GB,才推荐进行分库分表。
6、合适的字段长度选择:unsigned tinyint 无符号值 0 到 255;Unsigned smallint 无符号值 0 到 65535; unsigned int 无符号值 0 到42.9 亿;unsigned bigint 无符号值 0 到约 10 的 19 次方
7、表必须有主键,例如自增主键。
a)主键递增,数据行写入可以提高插入性能,可以避免page分裂,减少表碎片提升空间和内存的使用
b)主键要选择较短的数据类型, Innodb引擎普通索引都会保存主键的值,较短的数据类型可以有效地减少索引的磁盘空间,提高索引的缓存效率
c) 无主键的表删除,在row模式的主从架构,会导致备库夯住
8、禁止使用外键:
如果有外键完整性约束,需要应用程序控制。
外键会导致表与表之间耦合,update与delete操作都会涉及相关联的表,十分影响sql 的性能,甚至会造成死锁。高并发情况下容易造成数据库性能,大数据高并发业务场景数据库使用以性能优先。
9、表名、字段名命名
业务名称_表的作用。
10、表达是否字段数据类型
使用 is_xxx 的方式命名:
表达逻辑删除的字段名 is_deleted,1 表示删除,0 表示未删除;
表达逻辑删除的字段名 is_deleted,1 表示删除,0 表示未删除。
11、禁用保留字
如 desc、range、match、delayed 等,参考 MySQL 保留关键字:
https://dev.mysql.com/doc/refman/5.7/en/keywords.html
12、索引名称
主键索引名为 pk_字段名;唯一索引名为 uk_字段名;普通索引名则为 idx_字段名。
说明:pk_ 即 primary key;uk_ 即 unique key;idx_ 即 index 的简称
13、小数类型
小数类型为 decimal,禁止使用 float 和 double
14、定长字段用char
CHAR类型用来存储定长字符串;适用于:很短的字符串,或者所有的值都很接近同一长度。例如MD5存储的加密信息、uuid生成的主键。总结:对于经常变更的数据,CHAR比VARCHAR更好,因为定长的CHAR类型不容易产生碎片。对于非常短的列CHAR存储效率更高。
15、可变长度字符串
Mysql-varchar是可变长字符串,不预先分配存储空间,长度不要超过 4000,如果存储长 度大于此值,定义字段类型为 mysql-text,独立出来一张表,用主键来对应,避免影响其它字段索引效率。
16、表必备三个字段
id, create_date, last_modified_date。其中 id 必为主键,类型为 unsigned bigint、单表时自增、步长为 1(oracle数据库可以为uuid)。create_date, last_modified_date 的类型均为 TIMESTAMP 类型。
17、时间字段-timestamp与datetime
datetime 和 timestamp类型所占的存储空间不同,前者8个字节,后者4个字节,这样造成的后果是两者能表示的时间范围不同。前者范围为1000-01-01 00:00:00 ~ 9999-12-31 23:59:59,后者范围为 1970-01-01 08:00:01 到 2038-01-19 11:14:07 。所以 TIMESTAMP 支持的范围比 DATATIME 要小。
一个表只能定义一个。timestamp显示与时区有关,内部总是以 UTC 毫秒来存的。
严格模式的限制优先使用timestamp,datetime也可以。
18、索引
业务上具有唯一特性的字段,即使是多个字段的组合,也必须建成唯一索引。
19、join
尽量不要用超过三个表 join。需要 join 的字段,数据类型必须绝对一致;
多表关联查询 时,保证被关联的字段需要有索引。
20、varchar字段建立索引
在 varchar 字段上建立索引时,必须指定索引长度,没必要对全字段建立索引,根据实际文本区分度决定索引长度即可。
21、count 聚合函数
count(*)会统计值为 NULL 的行,而 count(列名)不会统计此列为 NULL 行。
22、count/distinct函数
count(distinct col) 计算该列除 NULL 之外的不重复行数,注意 count(distinct col1, col2) 如果其中一列全为 NULL,那么即使另一列有不同的值,也返回为 0。
23、 NULL值比较
使用 ISNULL()来判断是否为 NULL 值。注意:NULL 与任何值的直接比较都为 NULL
1) NULL<>NULL 的返回结果是 NULL,而不是 false。
2) NULL=NULL 的返回结果是 NULL,而不是 true。
3) NULL<>1 的返回结果是 NULL,而不是 true。
24、分页sql语句
在代码中写分页查询逻辑时,若 count 为 0 应直接返回,避免执行后面的分页语句。
25、禁用存储过程
存储过程难以调试和扩展,更没有移植性
26、避免使用in
若实在避免不了,需要仔细评估 in 后边的集合元素数量,控制在 1000 个之内。
27、编码问题
如果有全球化需要,所有的字符存储与表示,均以 (utf-8mb4)utf-8 编码
SELECT LENGTH(“轻松工作”); 返回为 12 ;
SELECT CHARACTER_LENGTH(“轻松工作”); 返回为 4 ;
28、删除表
TRUNCATE TABLE 比 DELETE 速度快
且使用的系统和事务日志资源少,但 TRUNCATE 无事务且不触发 trigger,有可能造成事故,故不建议在开发代码中使用此语句。
TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。
29、sql避免使用的关键字
避免使用!=(或<>)、IS NULL 或IS NOT NULL、IN、NOT IN等这样的操作符,避免在WHERE 子句中使用非聚合表达式。这些操作符会使系统无法使用索引,而只能直接索表中的数据。
30、避免使用OR,用UNION 代替
这样使用可能 造成索引失效,导致顺序扫描整个表,大大降低查询效率。
31、not null约束列
32、用like进行模糊查询时应注意
like ‘%yue%’。由于yue前面用到了“%”,因此该查询必然走全表扫描,除非必要,否则不要在关键词前加%
33、优化多表join
1) 减少Join语句的次数,用小结果集驱动大结果集
2) 保证Join语句中被驱动表上Join条件字段已经被索引
3) 若为左连接,则把索引加到第二张表上的连接字段
4) 若为右连接,则把索引加到第一张表的连接字段
34、避免索引失效
1) 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描.
2) 存储引擎不能使用索引中范围条件右边的列。(范围之后全失效)若中间索引列用到了范围(>、<、like等),则后面的所以全失效。
3)IS NULL和IS NOT NULL也无法使用索引。
4)字符串不加单引号
5)少用or,用它来连接时索引会失效
35、Exists与In的区别
(1) exists对外表用loop逐条查询,每次查询都会查看exists的条件语句,当 exists里的条件语句能够返回记录行时(无论记录行是的多少,只要能返回),条件就为真,返回当前loop到的这条记录,反之如果exists里的条 件语句不能返回记录行,则当前loop到的这条记录被丢弃,exists的条件就像一个boolean条件,当能返回结果集则为true,不能返回结果集则为 false。not exists与exists 相反。
(2) in查询相当于多个or条件的叠加,in查询就是先将子查询条件的记录全都查出来,假设结果集为B,共有m条记录,然后在将子查询条件的结果集分解成m个,再进行m次查询。not in与in相反。
(3)比较: mysql中的in语句是把外表和内表作hash 连接,而exists语句是对外表作loop循环,每次loop循环再对内表进行查询。一直大家都认为exists比in语句的效率要高,这种说法其实是不准确的。这个是要区分环境的。
select * from A where cc in (select cc from B)
select * from A where exists(select cc from B where cc=A.cc)
not in 和not exists如果查询语句使用了not in 那么内外表都进行全表扫描,没有用到索引;而not extsts 的子查询依然能用到表上的索引。所以无论那个表达,用not exists都比not in要快。
in 与 =的性能是一样的。