文章目录
- 1.创建数据库
- 2. 创建表
- 14.【推荐】单表行数超过 500 万行或者单表容量超过 2GB,才推荐进行分库分表。
- 15. 【参考】合适的字符存储长度,不但节约数据库表空间、节约索引存储,更重要的是提升检索速度。
- 2.1 创建名称
- 2.【强制】表名、字段名必须使用小写字母或数字, 禁止出现数字开头,禁止两个下划线中间只出现数字。
- 3. 【强制】表名不使用复数名词。
- 4. 【强制】禁用保留字,如 desc、 range、 match、 delayed 等, 请参考 MySQL 官方保留字。
- 10.【推荐】表的命名最好是加上“业务名称_表的作用。
- 11.【推荐】库名与应用名称尽量一致。
- 12.【推荐】如果修改字段含义或对字段表示的状态追加时,需要及时更新字段注释。
- 13.【推荐】字段允许适当冗余,以提高查询性能,但必须考虑数据一致。冗余字段应遵循:
- 2.2 创建索引
- 1. 【强制】业务上具有唯一特性的字段,即使是多个字段的组合,也必须建成唯一索引。
- 5. 【强制】 主键索引名为 pk_字段名; 唯一索引名为 uk_字段名; 普通索引名则为 idx_字段名。
- 11.【参考】创建索引时避免有如下极端误解:
- 3.创建字段
- 6. 【强制】小数类型为 decimal,禁止使用 float 和 double。
- 10.【参考】 如果有国际化需要,所有的字符存储与表示,均以 utf-8 编码,注意字符统计函数
- 13.【推荐】字段允许适当冗余,以提高查询性能,但必须考虑数据一致。冗余字段应遵循:
1.创建数据库
我们使用SQL语句来查看我们的数据库的每个表的占用内存情况
#查询每个表有多少行数据
use information_schema;
select table_name,table_rows from tables
where TABLE_SCHEMA = '你的数据库的名称'
order by table_rows desc;
#首先查询所有数据库占用磁盘空间大小的SQL语句如下:
select TABLE_SCHEMA, concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size,
concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
from information_schema.tables
group by TABLE_SCHEMA
order by data_length desc
#查询单个数据库里面各个表所占磁盘空间大小包括其索引的大小
select TABLE_NAME, concat(truncate(data_length/1024/1024,2),’ MB’) as data_size,
concat(truncate(index_length/1024/1024,2),’ MB’) as index_size
from information_schema.tables where TABLE_SCHEMA = ‘你数据库的名称’
group by TABLE_NAME
order by data_length desc;
为什么要说这个,主要是为了说明建表的字段是有字节,字节是占用内存的,虽然现在云硬盘很便宜,但是为了更优,也是要干一次
2. 创建表
14.【推荐】单表行数超过 500 万行或者单表容量超过 2GB,才推荐进行分库分表。
说明: 如果预计三年后的数据量根本达不到这个级别,请不要在创建表时就分库分表。
15. 【参考】合适的字符存储长度,不但节约数据库表空间、节约索引存储,更重要的是提升检索速度。
正例: 如下表,其中无符号值可以避免误存负数, 且扩大了表示范围。
2.1 创建名称
2.【强制】表名、字段名必须使用小写字母或数字, 禁止出现数字开头,禁止两个下划线中间只出现数字。
数据库字段名的修改代价很大,因为无法进行预发布,所以字段名称需要慎重考虑。
说明: MySQL 在 Windows 下不区分大小写,但在 Linux 下默认是区分大小写。因此,数据库名、
表名、字段名,都不允许出现任何大写字母,避免节外生枝。
show variables like "%case%";
- lower_case_table_names = 0
区分大小写(即对表名大小写敏感),默认是这种设置。这样设置后,在mysql里创建的表名带不带大写字母都没有影响,都可以正常读出和被引用。变量lower_case_file_system说明是否数据目录所在的文件系统对文件名的大小写敏感,其中:ON说明对文件名的大小写不敏感,OFF表示敏感。
变量lower_case_file_system说明是否数据目录所在的文件系统对文件名的大小写敏感,其中:ON说明对文件名的大小写不敏感,OFF表示敏感。 - lower_case_table_names = 1
不区分大小写(即对表名大小写不敏感)。这样设置后,表名在硬盘上以小写保存,MySQL将所有表名转换为小写存储和查找表上。该行为也适合数据库名和表的别名。
也就是说,mysql设置为不分区大小写后,创建库或表时,不管创建时使用大写字母,创建成功后,都是强制以小写保存.使用该选项的不利之处是当使用SHOW TABLES或SHOW DATABASES时,看不出名字原来是用大写还是小写。
3. 【强制】表名不使用复数名词。
为什么?表名是一类名词,名词都不该有复数。
4. 【强制】禁用保留字,如 desc、 range、 match、 delayed 等, 请参考 MySQL 官方保留字。
建表语句如下:
CREATE TABLE `base_error_message_test` (
`message_id` varchar(50) NOT NULL,
`message` text ,
`send_count` int(2) DEFAULT '0' ,
`status` int(2) DEFAULT '0' ,
`last_modified_date` datetime DEFAULT NULL ,
`creation_date` datetime DEFAULT NULL ,
`topic` varchar(50) DEFAULT NULL,
`key` varchar(50) DEFAULT NULL, #注意这里用了关键字key
PRIMARY KEY (`message_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='推送失败消息记录表';
以下语句是不对的:
select key from base_error_message_test;
INSERT INTO base_error_message_test (message_id, message, send_count, status, last_modified_date, creation_date, topic, key) VALUES (.......);
原因:字段key和mysql的保留字冲突了,当mysql的字段名和保留字冲突的时候,sql语句中的字段名需要加上反引号``来加以区别,反引号可以用Esc键下面那个按键在英文模式不按shift键打出来,注意,是反引号不是单引号,回车键左边那个是单引号。
10.【推荐】表的命名最好是加上“业务名称_表的作用。
正例: alipay_task / force_project / trade_config
为什么?这个我觉的是为了便于寻找
11.【推荐】库名与应用名称尽量一致。
这个也是为了便于寻找库
12.【推荐】如果修改字段含义或对字段表示的状态追加时,需要及时更新字段注释。
13.【推荐】字段允许适当冗余,以提高查询性能,但必须考虑数据一致。冗余字段应遵循:
1) 不是频繁修改的字段。
2) 不是 varchar 超长字段,更不能是 text 字段。
正例: 商品类目名称使用频率高,字段长度短,名称基本一成不变,可在相关联的表中冗余存储类目名称,避免关联查询。
为什么?
冗余字段是用 空间,难受的二次更新来换取时间,有相当沉重的更新带价,要做好注释说明,所以要不是提高很大的性能,都不必定冗余,所以大部分不能是频繁修改的字段
而不能是varchar字段或者text字段,原因是 大部分冗余表都是数据量达到一定程度的经常访问的表(要不然冗余干吗?),查询超长字符串容易溢出。
2.2 创建索引
1. 【强制】业务上具有唯一特性的字段,即使是多个字段的组合,也必须建成唯一索引。
说明: 不要以为唯一索引影响了 insert 速度,这个速度损耗可以忽略,但提高查找速度是明显的; 另外,即使在应用层做了非常完善的校验控制,只要没有唯一索引,根据墨菲定律,必然有脏数据产生。
5. 【强制】 主键索引名为 pk_字段名; 唯一索引名为 uk_字段名; 普通索引名则为 idx_字段名。
说明: pk_ 即 primary key; uk_ 即 unique key; idx_ 即 index 的简称。
在删除无关索引的时候或者更改业务逻辑的时候,这些索引名都会为后续的维护带来便利
11.【参考】创建索引时避免有如下极端误解:
1) 宁滥勿缺。 认为一个查询就需要建一个索引。
2) 宁缺勿滥。 认为索引会消耗空间、严重拖慢更新和新增速度。
3) 抵制惟一索引。 认为业务的惟一性一律需要在应用层通过“先查后插”方式解决。
3.创建字段
6. 【强制】小数类型为 decimal,禁止使用 float 和 double。
说明: float 和 double 在存储的时候,存在精度损失的问题,很可能在值的比较时,得到不
正确的结果。如果存储的数据范围超过 decimal 的范围,建议将数据拆成整数和小数分开存储。
drop table if exists dd;
create table dd (a double);
insert into dd values(956.745),(231.34243252),(321.43534),(5464.446);
select a,round(a,2) from dd;
因为Float、Double存储的是一个近似值而不是确切的值,试图使用它们存储一个确切的值可以会导致问题。它们依赖于不同平台和不同实现方式,而官当在章节Section B.5.5.8, “Problems with Floating-Point Values中举例说明了此问题
使用decimal 解决这个问题
/*
DECIMAL列的声明语法是DECIMAL(M,D)。在MySQL 5.1中,参量的取值范围如下:
1.M是数字的最大数(精度)。其范围为1~65(在较旧的MySQL版本中,允许的范围是1~254)。
2.D是小数点右侧数字的数目(标度)。其范围是0~30,但不得超过M。
*/
drop table if exists dd;
create table dd (a double,b decimal(30,10));
insert into dd
values(956.745,956.745),(231.34243252,231.34243252),(321.43534,321.43534),
(5464.446,5464.446);
select a,round(a,2) from dd; #使用double,四舍五入不正确
select b,round(b,2) from dd; #使用了decimal 四舍五入正确
10.【参考】 如果有国际化需要,所有的字符存储与表示,均以 utf-8 编码,注意字符统计函数
的区别。
说明:
SELECT LENGTH(“轻松工作”); 返回为 12
SELECT CHARACTER_LENGTH(“轻松工作”); 返回为 4
如果需要存储表情,那么选择 utf8mb4 来进行存储,注意它与 utf-8 编码的区别。
13.【推荐】字段允许适当冗余,以提高查询性能,但必须考虑数据一致。冗余字段应遵循:
1) 不是频繁修改的字段。
2) 不是 varchar 超长字段,更不能是 text 字段。
正例: 商品类目名称使用频率高,字段长度短,名称基本一成不变,可在相关联的表中冗余存
储类目名称,避免关联查询