11.避免使用MySQL保留字
如果库名、表名、字段名等属性含有保留字时,SQL
语句必须用反引号来引用属性名称,这将使得SQL语句书写、SHELL脚本中变量的转义等变得非常复杂。
因此,我们一般避免使用MySQL
保留字,如select、interval、desc
等等
12.不搞外键关联,一般都在代码维护
什么是外键呢?
外键,也叫
FOREIGN KEY
,它是用于将两个表连接在一起的键。FOREIGN KEY
是一个表中的一个字段(或字段集合),它引用另一个表中的PRIMARY KEY
。它是用来保证数据的一致性和完整性的。
阿里的Java
规范也有这么一条:
【强制】不得使用外键与级联,一切外键概念必须在应用层解决。
我们为什么不推荐使用外键呢?
使用外键存在性能问题、并发死锁问题、使用起来不方便等等。每次做
DELETE
或者UPDATE
都必须考虑外键约束,会导致开发的时候很难受,测试数据造数据也不方便。还有一个场景不能使用外键,就是分库分表。
13.一般都选择INNODB存储引擎
建表是需要选择存储引擎的,我们一般都选择INNODB
存储引擎,除非读写比率小于1%
, 才考虑使用MyISAM
。
有些小伙伴可能会有疑惑,不是还有MEMORY
等其他存储引擎吗?什么时候使用它呢?其实其他存储引擎一般除了都建议在DBA
的指导下使用。
我们来复习一下这MySQL
这三种存储引擎的对比区别吧:
特性 | INNODB | MyISAM | MEMORY |
---|---|---|---|
事务安全 | 支持 | 无 | 无 |
存储限制 | 64TB | 有 | 有 |
空间使用 | 高 | 低 | 低 |
内存使用 | 高 | 低 | 高 |
插入数据速度 | 低 | 高 | 高 |
是否支持外键 | 支持 | 无 | 无 |
14.选择合适统一的字符集
数据库库、表、开发程序等都需要统一字符集,通常中英文环境用utf8
。
MySQL支持的字符集有utf8、utf8mb4、GBK、latin1
等。
-
utf8:支持中英文混合场景,国际通过,3个字节长度
-
utf8mb4: 完全兼容utf8,4个字节长度,一般存储emoji表情需要用到它。
-
GBK :支持中文,但是不支持国际通用字符集,2个字节长度
-
latin1:MySQL默认字符集,1个字节长度
15.如果你的数据库字段是枚举类型的,需要在comment注释清楚
如果你设计的数据库字段是枚举类型的话,就需要在comment
后面注释清楚每个枚举的意思,以便于维护
正例如下:
`session_status` varchar(2) COLLATE utf8_bin NOT NULL COMMENT 'session授权态 00:在线-授权态有效 01:下线-授权态失效 02:下线-主动退出 03:下线-在别处被登录'
反例:
`session_status` varchar(2) COLLATE utf8_bin NOT NULL COMMENT 'session授权态'
并且,如果你的枚举类型在未来的版本有增加修改的话,也需要同时维护到comment
。
16.时间的类型选择
我们设计表的时候,一般都需要加通用时间的字段,如create_time、modified_time
等等。那对于时间的类型,我们该如何选择呢?
对于MySQL来说,主要有date、datetime、time、timestamp 和 year
。
-
date :表示的日期值, 格式
yyyy-mm-dd
,范围1000-01-01 到 9999-12-31
,3字节 -
time :表示的时间值,格式
hh:mm:ss
,范围-838:59:59 到 838:59:59
,3字节 -
datetime:表示的日期时间值,格式
yyyy-mm-dd hh:mm:ss
,范围1000-01-01 00:00:00到
9999-12-31 23:59:59```,8字节,跟时区无关 -
timestamp:表示的时间戳值,格式为
yyyymmddhhmmss
,范围1970-01-01 00:00:01到2038-01-19 03:14:07
,4字节,跟时区有关 -
year:年份值,格式为
yyyy
。范围1901到2155
,1字节
推荐优先使用datetime
类型来保存日期和时间,因为存储范围更大,且跟时区无关。
17.不建议使用Stored procedure(包括存储过程,触发器)
什么是存储过程
已预编译为一个可执行过程的一个或多个SQL语句。
什么是触发器
触发器,指一段代码,当触发某个事件时,自动执行这些代码。使用场景:
-
可以通过数据库中的相关表实现级联更改。
-
实时监控某张表中的某个字段的更改而需要做出相应的处理。
-
例如可以生成某些业务的编号。
-
注意不要滥用,否则会造成数据库及应用程序的维护困难。
对于MYSQL来说,存储过程、触发器等还不是很成熟, 并没有完善的出错记录处理,不建议使用。
18.1:N 关系的设计
日常开发中,1
对多的关系应该是非常常见的。比如一个班级有多个学生,一个部门有多个员工等等。这种的建表原则就是:在从表(N
的这一方)创建一个字段,以字段作为外键指向主表(1
的这一方)的主键。示意图如下:
19.大字段
设计表的时候,我们尤其需要关注一些大字段,即占用较多存储空间的字段。比如用来记录用户评论的字段,又或者记录博客内容的字段,又或者保存合同数据的字段。如果直接把表字段设计成text类型的话,就会浪费存储空间,查询效率也不好。
在MySQl中,这种方式保存的设计方案,其实是不太合理的。这种非常大的数据,可以保存到mongodb
中,然后,在业务表保存对应mongodb
的id
即可。
这种设计思想类似于,我们表字段保存图片时,为什么不是保存图片内容,而是直接保存图片url即可。
20.考虑是否需要分库分表
什么是分库分表呢?
-
分库:就是一个数据库分成多个数据库,部署到不同机器。
-
分表:就是一个数据库表分成多个表。
我们在设计表的时候,其实可以提前估算一下,是否需要做分库分表。比如一些用户信息,未来可能数据量到达百万设置千万的话,就可以提前考虑分库分表。
为什么需要分库分表: 数据量太大的话,SQL的查询就会变慢。如果一个查询SQL没命中索引,千百万数据量级别的表可能会拖垮整个数据库。即使SQL命中了索引,如果表的数据量超过一千万的话,查询也是会明显变慢的。这是因为索引一般是B+树结构,数据千万级别的话,B+树的高度会增高,查询就变慢啦。
分库分表主要有水平拆分、垂直拆分的说法,拆分策略有range范围、hash取模
。而分库分表主要有这些问题:
-
事务问题
-
跨库关联
-
排序问题
-
分页问题
-
分布式ID
21.sqL 编写的一些优化经验
最后的话,跟大家聊来一些写SQL的经验吧:
-
查询SQL尽量不要使用
select *
,而是select
具体字段 -
如果知道查询结果只有一条或者只要最大/最小一条记录,建议用
limit 1
-
应尽量避免在
where
子句中使用or
来连接条件 -
注意优化
limit
深分页问题 -
使用
where
条件限定要查询的数据,避免返回多余的行 -
尽量避免在索引列上使用
mysql
的内置函数 -
应尽量避免在
where
子句中对字段进行表达式操作 -
应尽量避免在
where
子句中使用!=
或<>
操作符 -
使用联合索引时,注意索引列的顺序,一般遵循最左匹配原则。
-
对查询进行优化,应考虑在
where 及 order by
涉及的列上建立索引 -
如果插入数据过多,考虑批量插入
-
在适当的时候,使用覆盖索引
-
使用explain 分析你SQL的计划