精华内容
下载资源
问答
  • mysql数据库设计
    千次阅读
    2018-07-04 10:31:20

    简述

            在我们项目开发中,数据库的设计可以说是非常重要,我遇到过很多数据库设计比较杂乱的项目,像表名、字段名命名混乱、字段类型设计混乱等待。写本篇博文的目的就是总结一下设计MySQL 数据库原则,有一个小小的规范会使得我们的项目更加强壮。

    设计原则

    1. 命名规则

            数据库的的名字一般都是很随意,最好和我们的项目业务一致,这样比较好进行区分;数据表的命名应尽可能和所服务的业务模块名一致,也就是我们所存储实体的名称,表名应尽量包含与所存放数据对应的单词,一眼可以看出此表所保存的是什么;表字段的命名也应尽量保持和实际数据相对应,联合索引名称应尽量包含所有索引键字段名或缩写,且各字段名在索引名中的顺序应与索引键在索引中的索引顺序一致。

    2. 字段类型

            经常需要计算和排序等消耗CPU的字段,应该尽量选择更为迅速的字段,如用TIMESTAMP(4个字节,最小值1970-01-0100:00:00)代替Datetime(8个字节,最小值1001-01-01 00:00:00),通过整型替代浮点型和字符型;变长字段使用varchar,不要使用char,注意设计合适的长度;对于二进制多媒体数据,流水队列数据(如日志),超大文本数据不要放在数据库字段中;业务逻辑执行过程必须读到的表中字段必须要有初始的值,避免业务读出为负或无穷大的值导致程序失败。

    3. 存储引擎选择

            一般情况可以选择MyISAM存储引擎,如果需要事务支持必须使用InnoDB存储引擎。关于各类数据库存储引擎的区别,在这里就不在赘述,想了解的可以Google。

    4. 表设计

            业务逻辑执行过程必须读到的表中字段必须要有初始的值,避免业务读出为负或无穷大的值导致程序失败。并不需要一定遵守范式理论,字段适度的冗余,可以让Query尽量减少Join,提高查询速度。访问频率较低的大字段拆分出数据表。有些大字段占用空间多,访问频率较其他字段明显要少很多,这种情况进行拆分,频繁的查询中就不需要读取大字段,造成IO资源的浪费。大表可以考虑水平拆分。大表影响查询效率,根据业务特性有很多拆分方式,像根据时间递增的数据,可以根据时间来分。以id划分的数据,可根据id%数据库个数的方式来拆分。

    5. 索引设计

    a) 业务需要的相关索引是根据实际的设计所构造sql语句的where条件来确定的,业务不需要的不要建索引,不允许在联合索引(或主键)中存在多于的字段。特别是该字段根本不会在条件语句中出现。唯一确定一条记录的一个字段或多个字段要建立主键或者唯一索引,不能唯一确定一条记录,为了提高查询效率建普通索引。业务使用的表,有些记录数很少,甚至只有一条记录,为了约束的需要,也要建立索引或者设置主键。
    b) 对于取值不能重复,经常作为查询条件的字段,应该建唯一索引(主键默认唯一索引),并且将查询条件中该字段的条件置于第一个位置。没有必要再建立与该字段有关的联合索引。对于经常查询的字段,其值不唯一,也应该考虑建立普通索引,查询语句中该字段条件置于第一个位置,对联合索引处理的方法同样。

    c) 业务通过不唯一索引访问数据时,需要考虑通过该索引值返回的记录稠密度,原则上可能的稠密度最大不能高于0.2,如果稠密度太大,则不合适建立索引了。需要联合索引(或联合主键)的数据库要注意索引的顺序。SQL语句中的匹配条件也要跟索引的顺序保持一致。

    6. 查询语句设计

            Insert语句中,根据测试,批量一次插入1000条时效率最高,多于1000条时,要拆分,多次进行同样的插入,应该合并批量进行。注意query语句的长度要小于mysqld的参数max_allowed_packet。查询条件中各种逻辑操作符性能顺序是and,or,in,因此在查询条件中应该尽量避免使用在大集合中使用in。永远用小结果集驱动大记录集,因为在mysql中,只有NestedJoin一种Join方式,就是说mysql的join是通过嵌套循环来实现的。通过小结果集驱动大记录集这个原则来减少嵌套循环的循环次数,以减少IO总量及CPU运算次数。尽量优化Nested Join内层循环。只取需要的columns,尽量不要使用select *。仅仅使用最有效的过滤字段,where 字句中的过滤条件少为好。尽量避免复杂的Join和子查询。

    更多相关内容
  • MYSQL数据库设计规范,借鉴了阿里和其他大佬,分为强制、高危、建议三个规范指标。文档后面附有参考链接
  • MySQL数据库设计

    千次阅读 2020-11-21 18:59:33
    根据项目产品原型图中的部分,进行数据库设计。 表结构 字段类型、是否允许为null、是否有默认值 索引设计 数据库引擎的选择 2 注意事项 为了查询效率,可以做冗余字段设计(空间换时间的思想,属于一种反范式...

    1. 简介

    1.1 定义

    • 建库/建表规范
    • 设计数据库表的规范(表不能再分,列不能在分)
    • 为了查询效率,可以做冗余字段设计(空间换时间的思想,属于一种反范式设计)

    1. 应用场景

    • 根据项目产品原型图中的部分,进行数据库设计。

    1. E-R模型

    1.1 简介

    1. 定义

    • E-R模型即实体-关系模型,E-R模型就是描述数据库存储数据的结构模型。
    • E-R模型由 实体、属性、实体之间的关系构成,主要用来描述数据库中表结构。
    • 开发流程是先画出E-R模型,然后根据三范式进行数据库表结构设计

    2. 原理

    E-R模型的效果图:

    E-R模型图
    说明:

    • 实体: 用矩形表示,并标注实体名称
    • 属性: 用椭圆表示,并标注属性名称,
    • 关系: 用菱形表示,并标注关系名称
      • 一对一:关系字段存储在两个任意一个表中的一个字段
      • 一对多:关系字段存储在多的那个表的字段
      • 多对多:关系字段存储在另外一张表(中间表),表有两个字段,两个表的主键
        一对一的关系:

    一对一
    说明:

    • 关系也是一种数据,需要通过一个字段存储在表中
    • 1对1关系,在表A或表B中创建一个字段,存储另一个表的主键值
      一对多的关系:

    一对多

    说明:

    • 1对多关系,在多的一方表(学生表)中创建一个字段,存储班级表的主键值
      多对多的关系:

    一对多
    说明:

    • 多对多关系,新建一张表C,这个表只有两个字段,一个用于存储A的主键值,一个用于存储B的主键值

    1.2 使用场景

    • 对于大型公司开发项目,我们需要根据产品经理的设计,我们先使用建模工具, 如:power designer,db desinger等这些软件来画出实体-关系模型(E-R模型)
    • 然后根据三范式设计数据库表结构

    2. 三范式

    2.1 简介

    • 范式: 对设计数据库提出的一些规范,目前有迹可寻的共有8种范式,一般遵守3范式即可。
    • 范式就是设计数据库的一些通用规范。
      • 1NF强调字段是最小单元,不可再分
      • 2NF强调在1NF基础上必须要有主键和非主键字段必须完全依赖于主键,也就是说 不能部分依赖(复合主键)
      • 3MF强调在2NF基础上 非主键字段必须直接依赖于主键,也就是说不能传递依赖(间接依赖)。

    2.2 分类

    • 第一范式(1NF): 强调的是列的原子性,即列不能够再分成其他几列。
    • 第二范式(2NF): 满足 1NF,另外包含两部分内容,一是表必须有一个主键;二是非主键字段 必须完全依赖于主键,而不能只依赖于主键的一部分。
    • 第三范式(3NF): 满足 2NF,另外非主键列必须直接依赖于主键,不能存在传递依赖。即不能存在:非主键列 A 依赖于非主键列 B,非主键列 B 依赖于主键的情况。

    1. 第一范式

    如图所示的表结构:
    第一范式的问题
    说明:

    • 这种表结构设计就没有达到 1NF,要符合 1NF 我们只需把列拆分,即:把 contact 字段拆分成 name 、tel、addr 等字段。

    2. 第二范式

    如图所示的表结构:

    第二范式的问题
    说明:

    • 这种表结构设计就没有达到 2NF,因为 Discount(折扣),Quantity(数量)完全依赖于主键(OrderID),而 UnitPrice单价,ProductName产品名称 只依赖于 ProductID, 所以 OrderDetail 表不符合 2NF。
    • 我们可以把【OrderDetail】表拆分为【OrderDetail】(OrderID,ProductID,Discount,Quantity)和【Product】(ProductID,UnitPrice,ProductName)这样就符合第二范式了。

    3. 第三范式

    如图所示的表结构:

    第三范式的问题
    说明:

    • 这种表结构设计就没有达到 3NF,因为 OrderDate,CustomerID,CustomerName,CustomerAddr,CustomerCity 等非主键列都完全依赖于主键(OrderID),所以符合 2NF。不过问题是 CustomerName,CustomerAddr,CustomerCity 直接依赖的是 CustomerID(非主键列),而不是直接依赖于主键,它是通过传递才依赖于主键,所以不符合 3NF。
    • 可以把【Order】表拆分为【Order】(OrderID,OrderDate,CustomerID)和【Customer】(CustomerID,CustomerName,CustomerAddr,CustomerCity)从而达到 3NF。

    3. 数据类型和约束

    3.1 简介

    • 数据库中的数据保存在数据表中,在表中为了更加准确的存储数据,保证数据的正确有效,可以在创建表的时候,为表添加一些强制性的验证,比如:数据类型和约束。

    3.2 数据类型

    1. 简介

    • 数据类型是指在创建表的时候为表中字段指定数据类型,只有数据符合类型要求才能存储起来,使用数据类型的原则是:够用就行,尽量使用取值范围小的,而不用大的,这样可以更多的节省存储空间。
    • (够用就行)

    2. 常见数据类型

    • 整数:int,bit

    整数类型

    • 小数:decimal(float,double)
    • 字符串:varchar,char(固定)

    字符串

    • 日期时间:date,time,datetime
      事件类型

    • 枚举类型:enum

    • 图片,音频,视频:保存路径

    • 数据类型说明:

    • decimal表示浮点数,如 decimal(5, 2) 表示共存5位数,小数占 2 位.

    • char表示固定长度的字符串,如char(3),如果填充’ab’时会补一个空格为’ab ',3表示字符数

    • varchar表示可变长度的字符串,如varchar(3),填充’ab’时就会存储’ab’,3表示字符数

    • 对于图片、音频、视频等文件,不存储在数据库中,而是上传到某个服务器上,然后在表中存储这个文件的保存路径.

    • 字符串 text 表示存储大文本,当字符大于 4000 时推荐使用, 比如技术博客.

    1. 字段类型的选择

    1.1 整型的存储大小与显示大小

    mysql的字段,unsigned int(3), 和unsinged int(6), 能存储的数值范围是否相同。如果不同,分别是多大?

    建立下面这张表:

    CREATE TABLE `test` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `i1` int(3) unsigned zerofill DEFAULT NULL,
        `i2` int(6) unsigned zerofill DEFAULT NULL,
        PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8
    

    插入一些数据后
    在这里插入图片描述
    发现,无论是int(3), int(6), 都可以显示6位以上的整数。但是,当数字不足3位或6位时,前面会用0补齐。

    手册解释是这样的:

    MySQL还支持选择在该类型关键字后面的括号内指定整数值的显示宽度(例如,INT(4))。该可选显示宽度规定用于显示宽度小于指定的列宽度的值时从左侧填满宽度。显示宽度并不限制可以在列内保存的值的范围,也不限制超过列的指定宽度的值的显示。

    也就是说,int的长度并不影响数据的存储精度,长度只和显示有关,在上面例子的建表语句中,使用了zerofill。

    最终答案:存储范围相同

    1.2 char 与 varchar 的选择

    • char 不可变,查询效率高,可能造成存储浪费
    • varchar 可变,查询效率不如char,节省空间
      常见MySQL数据类型
      1
      2

    3.3 数据约束

    1. 简介

    • 约束是指数据在数据类型限定的基础上额外增加的要求.

    2. 常见约束

    • 主键 primary key: 物理上存储的顺序. MySQL 建议所有表的主键字段都叫 id, 类型为 int unsigned.
    • 非空 not null: 此字段不允许填写空值.
    • 惟一 unique: 此字段的值不允许重复.
    • 默认 default: 当不填写字段对应的值会使用默认值,如果填写时以填写为准.
    • 外键 foreign key: 对关系字段进行约束, 当为关系字段填写值时, 会到关联的表中查询此值是否存在, 如果存在则填写成功, 如果不存在则填写失败并抛出异常.

    3.4 作用

    数据类型和约束保证了表中数据的准确性和完整性

    4. 索引设计

    4.1 定义

    • 索引是加快数据库的查询速度的一种手段
    • 索引在MySQL中也叫做“键”,它是一个特殊的文件,它保存着数据表里所有记录的位置信息,更通俗的来说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度。
    • 键(key):主键,外键,存储数据表里所有记录的位置信息的表

    4.2 应用场景

    • 当数据库中数据量很大时,查找数据会变得很慢,就可以通过索引来提高数据库的查询效率。

    4.2 作用

    • 给表中字段添加索引,添加一个索引表(目录), 提高大型数据库的查询效率
    • 注意:增删改效率不会提高,甚至会降低

    4.3 操作

    1. 查看

    show index from 表名;
    show create table 表名;
    

    2. 创建

    alter table 表名 add index索引名[可选](列名,...)
    # 主键会自动创建索引
    # 索引名不指定,默认使用字段名
    -- 给name字段添加索引
    alter table classes add index my_name (name);
    

    3. 删除

    alter table表名 drop index 索引名
    -- 如果不知道索引名,可以查看创表sql语句
    show create table classes;
    alter table classes drop index my_name;
    

    4.4 案例

    验证索引查询性能
    创建测试表testindex:

    create table test_index(title varchar(10));
    

    向表中插入十万条数据:

    from pymysql import connect
    
    def main():
        # 创建Connection连接
        conn = connect(host='localhost',port=3306,database='python',user='root',password='mysql',charset='utf8')
        # 获得Cursor对象
        cursor = conn.cursor()
        # 插入10万次数据
        for i in range(100000):
            cursor.execute("insert into test_index values('ha-%d')" % i)
        # 提交数据
        conn.commit()
    
    if __name__ == "__main__":
        main()
    

    验证索引性能操作

    -- 开启运行时间监测:
    set profiling=1;
    -- 查找第1万条数据ha-99999
    select * from test_index where title='ha-99999';
    -- 查看执行的时间:
    show profiles;
    -- 给title字段创建索引:
    alter table test_index add index (title);
    -- 再次执行查询语句
    select * from test_index where title='ha-99999';
    -- 再次查看执行的时间
    show profiles;
    

    4.5 分类

    1.普通索引

    2. 联合(复合索引)

    2.1 定义

    • 联合索引又叫复合索引,即一个索引覆盖表中两个或者多个字段,一般用在多个字段一起查询的时候。

    2.2 好处

    • 减少磁盘空间开销,因为每创建一个索引,其实就是创建了一个索引文件,那么会增加磁盘空间的开销。

    2.3 操作

    -- 创建teacher表
    create table teacher
    (
        id int not null primary key auto_increment,
        name varchar(10),
        age int
    );
    
    -- 创建联合索引
    alter table teacher add index (name,age);
    

    4.6 最左原则

    在使用联合索引的时候,我们要遵守一个最左原则,即index(name,age)支持 name 、name 和 age 组合查询,而不支持单独 age 查询,因为没有用到创建的联合索引。

    最左原则示例:

    -- 下面的查询使用到了联合索引
    select * from stu where name='张三' -- 这里使用了联合索引的name部分
    select * from stu where name='李四' and age=10 -- 这里完整的使用联合索引,包括 name 和 age 部分 
    -- 下面的查询没有使用到联合索引
    select * from stu where age=10 -- 因为联合索引里面没有这个组合,只有 name | name age 这两种组合
    

    说明:

    • 在使用联合索引的查询数据时候一定要保证联合索引的最左侧字段出现在查询条件里面,否则联合索引失效

    4.7 MySQL中索引的优点和缺点和使用原则

    • 优点:

      • 加快数据的查询速度
    • 缺点:

      • 创建索引会耗费时间和占用磁盘空间,并且随着数据量的增加所耗费的时间也会增加
    • 使用原则:

      • 通过优缺点对比,不是索引越多越好,而是需要自己合理的使用。
      • 对经常更新的表就避免对其进行过多索引的创建,对经常用于查询的字段应该创建索引,
      • 数据量小的表最好不要使用索引,因为由于数据较少,可能查询全部数据花费的时间比遍历索引的时间还要短,索引就可能不会产生优化效果。
      • 在一字段上相同值比较多不要建立索引,比如在学生表的"性别"字段上只有男,女两个不同值。相反的,在一个字段上不同值较多可是建立索引。

    4.8 算法

    • bTree(b树):二分查找
    • b+树
    • hash表

    5. 外键约束

    5.1 简介

    1. 定义

    • 存储在另一张表主键的字段

    2. 作用

    • 对外键字段的值进行更新和插入时会和引用表中字段的数据进行验证,数据如果不合法则更新和插入会失败,保证数据的有效性

    5.2 操作

    1. 对于已经存在的字段添加外键约束
    • 添加外键约束:
    alter table 从表 add foreign key(外键字段) references 主表(主键字段);
    
    -- 为cls_id字段添加外键约束
    alter table students add foreign key(cls_id) references classes(id);
    
    1. 在创建数据表时设置外键约束
    -- 创建学校表
    create table school(
        id int not null primary key auto_increment, 
        name varchar(10)
    );
    
    -- 创建老师表
    create table teacher(
        id int not null primary key auto_increment, 
        name varchar(10), 
        s_id int not null, 
        foreign key(s_id) references school(id)
    );
    
    1. 删除外键约束
    • 删除外键约束:
    alter table 表名 drop foreign key 外键名;
    
    -- 需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称
    show create table teacher;
    
    -- 获取名称之后就可以根据名称来删除外键约束
    alter table teacher drop foreign key teacher_ibfk_1;
    

    5.3 特点

    • 安全,但效率低

    5.4 索引

    • 主键 Primary Key
    • 外键 Foreign Key
      • 保持数据完整性
    ALTER TABLE tbl_name
        ADD [CONSTRAINT [symbol]] FOREIGN KEY
        [index_name] (index_col_name, ...)
        REFERENCES tbl_name (index_col_name,...)
        [ON DELETE reference_option]
        [ON UPDATE reference_option]
    

    例如:

    ALTER TABLE `user_resource` CONSTRAINT `FKEEAF1E02D82D57F9` FOREIGN KEY (`user_Id`) REFERENCES `sys_user` (`Id`)
    

    CASCADE

    在父表上update/delete记录时,同步update/delete掉子表的匹配记录

    • ON DELETE:删除主表时自动删除从表。删除从表,主表不变
    • ON UPDATE:更新主表时自动更新从表。更新从表,主表不变

    SET NULL

    在父表上update/delete记录时,将子表上匹配记录的列设为null (要注意子表的外键列不能为not null)

    • ON DELETE:删除主表时自动更新从表值为NULL。删除从表,主表不变
    • ON UPDATE:更新主表时自动更新从表值为NULL。更新从表,主表不变

    NO ACTION

    如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作

    • ON DELETE:从表记录不存在时,主表才可以删除。删除从表,主表不变
    • ON UPDATE:从表记录不存在时,主表才可以更新。更新从表,主表不变

    RESTRICT

    同no action, 都是立即检查外键约束

    SET DEFAULT

    父表有变更时,子表将外键列设置成一个默认的值 但Innodb目前不支持

    • 索引 Key / Index

      • 提升查询效率,减慢增删改速度
    • 唯一约束 Unique

      • 保证数据不重复

    6. 数据库引擎选择

    6.1 简介

    • 数据库存储引擎是数据库底层软件组织,数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据。
    • 不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能,使用不同的存储引擎,还可以 获得特定的功能。
    • 现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎

    6.2 操作

    SHOW ENGINES  # 命令来查看MySQL提供的引擎
    
    SHOW VARIABLES LIKE 'storage_engine'; # 查看数据库默认使用哪个引擎
    

    6.3 分类

    1. InnoDB存储引擎

    1.1 简介

    • InnoDB是事务型数据库的首选引擎,支持事务安全表(ACID),支持行锁定和外键,InnoDB是默认的MySQL引擎。

    1.2 InnoDB特性

    1、InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全(ACID兼容)存储引擎

    • InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中,可以自由地将InnoDB类型的表和其他MySQL的表类型混合起来,甚至在同一个查询中也可以混合

    2、InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎锁不能匹敌的

    3、InnoDB存储引擎完全与MySQL服务器整合,InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引在一个逻辑表空间中,表空间可以包含数个文件(或原始磁盘文件)。这与MyISAM表不同,比如在MyISAM表中每个表被存放在分离的文件中。InnoDB表可以是任何尺寸,即使在文件尺寸被限制为2GB的操作系统上

    4、InnoDB支持外键完整性约束

    5、存储表中的数据时,每张表的存储都按主键顺序存放,如果没有显示在表定义时指定主键,InnoDB会为每一行生成一个6字节的ROWID,并以此作为主键

    6、InnoDB被用在众多需要高性能的大型数据库站点上

    InnoDB不创建目录,使用InnoDB时,MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件,以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件

    2. MyISAM存储引擎

    2.1 简介

    • MyISAM基于ISAM存储引擎,并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度,但不支持事务

    2.2 MyISAM主要特性

    1、大文件(达到63位文件长度)在支持大文件的文件系统和操作系统上被支持

    2、当把删除和更新及插入操作混合使用的时候,动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块,以及若下一个块被删除,就扩展到下一块自动完成

    3、每个MyISAM表最大索引数是64,这可以通过重新编译来改变。每个索引最大的列数是16

    4、最大的键长度是1000字节,这也可以通过编译来改变,对于键长度超过250字节的情况,一个超过1024字节的键将被用上

    5、BLOB和TEXT列可以被索引

    6、NULL被允许在索引的列中,这个值占每个键的0~1个字节

    7、所有数字键值以高字节优先被存储以允许一个更高的索引压缩

    8、每个MyISAM类型的表都有一个AUTO_INCREMENT的内部列,当INSERT和UPDATE操作的时候该列被更新,同时AUTO_INCREMENT列将被刷新。所以说,MyISAM类型表的AUTO_INCREMENT列更新比InnoDB类型的AUTO_INCREMENT更快

    9、可以把数据文件和索引文件放在不同目录

    10、每个字符列可以有不同的字符集

    11、有VARCHAR的表可以固定或动态记录长度

    12、VARCHAR和CHAR列可以多达64KB

    使用MyISAM引擎创建数据库,将产生3个文件。文件的名字以表名字开始,扩展名之处文件类型:frm文件存储表定义、数据文件的扩展名为.MYD(MYData)、索引文件的扩展名时.MYI(MYIndex)

    3. MEMORY存储引擎

    3. 1 简介

    MEMORY存储引擎将表中的数据存储到内存中,未查询和引用其他表数据提供快速访问。

    3.2 MEMORY主要特性

    1、MEMORY表的每个表可以有多达32个索引,每个索引16列,以及500字节的最大键长度

    2、MEMORY存储引擎执行HASH和BTREE缩影

    3、可以在一个MEMORY表中有非唯一键值

    4、MEMORY表使用一个固定的记录长度格式

    5、MEMORY不支持BLOB或TEXT列

    6、MEMORY支持AUTO_INCREMENT列和对可包含NULL值的列的索引

    7、MEMORY表在所由客户端之间共享(就像其他任何非TEMPORARY表)

    8、MEMORY表内存被存储在内存中,内存是MEMORY表和服务器在查询处理时的空闲中,创建的内部表共享

    9、当不再需要MEMORY表的内容时,要释放被MEMORY表使用的内存,应该执行DELETE FROM或TRUNCATE TABLE,或者删除整个表(使用DROP TABLE)

    6.4 存储引擎选择

    • 不同的存储引擎都有各自的特点,以适应不同的需求,如下表所示:

    存储引擎的选择

    • 如果要提供提交、回滚、崩溃恢复能力的事物安全(ACID兼容)能力,并要求实现并发控制,InnoDB是一个好的选择

    • 如果数据表主要用来插入和查询记录,则MyISAM引擎能提供较高的处理效率

    • 如果只是临时存放数据,数据量不大,并且不需要较高的数据安全性,可以选择将数据保存在内存中的Memory引擎,MySQL中使用该引擎作为临时表,存放查询的中间结果

    • 如果只有INSERT和SELECT操作,可以选择Archive,Archive支持高并发的插入操作,但是本身不是事务安全的。Archive非常适合存储归档数据,如记录日志信息可以使用Archive

    • 使用哪一种引擎需要灵活选择,一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求,使用合适的存储引擎,将会提高整个数据库的性能

    展开全文
  • Mysql数据库设计规范

    2018-05-07 17:06:06
    在使用Mysql数据库时,首先要对MySQL数据库设计规范有所了解,有了深刻的了解之后才能设计出更加规范和文档的数据库
  • MySQL数据库设计、优化
  • MySQL数据库设计总结

    2018-09-22 17:55:04
    一些工作时常用的数据库经验和使用规则,能够提高数据库的运行效率
  • MySQL数据库设计.pdf

    2021-10-02 12:03:43
    MySQL数据库设计.pdf
  • MySQL数据库设计-案例

    千次阅读 2022-05-11 17:30:41
    数据库设计案例。

    大家好,我是忘鱼。分享一下数据库设计(案例)

     

    目录

    一、案例      

     二、代码

    三、总结


    一、案例      

         如下进行数据库设计。这是黑马上的一道练习题。

     二、代码

    drop table if EXISTS music;
    drop table if EXISTS song;
    drop table if EXISTS Review;
    drop table if EXISTS user1;
    CREATE TABLE music -- 专辑 
    (
    title varchar ( 32 ),-- 专辑名,
    alias varchar ( 32 ),-- 专辑别名,
    image varchar ( 64 ),-- 封面图片,
    style varchar ( 8 ),-- 流派(如经典、流行、民谣、电子等),
    type varchar ( 4 ),-- 类型(专辑、单曲等),
    med varchar ( 4 ),-- 介质( CD、黑胶、数字等),
    publish_time date,-- 发行时间,
    publisher varchar ( 16 ),-- 出版者,
    numbertiny int,-- 唱片数,
    barcodebig int,-- 条形码,
    summary varchar ( 1024 ),-- 简介,
    artist varchar ( 16 ),-- 艺术家,
    id int  primary key auto_increment-- 编号(唯一)
    );
    
    
    CREATE TABLE song -- 曲目
    (
    song_id int,
    CONSTRAINT fk_song_music FOREIGN KEY (song_id) REFERENCES music(id),
    name varchar(32),-- 歌曲名
    serial_numbertiny int,-- 歌曲序号
    id int  primary key auto_increment -- 编号(唯一)
     );
     
     CREATE TABLE Review(
     content varchar(256),-- 评论内容
     ratingtiny int,-- 评分(1~5)
     review_time datetime, -- 评论时间 
    music_id int,
    user1_id int,
     CONSTRAINT fk_review_music FOREIGN key (music_id) REFERENCES music(id),
     CONSTRAINT fk_review_user1 FOREIGN key (user1_id) REFERENCES user1(id)
     );
     
     CREATE TABLE user1(
     id int PRIMARY key auto_increment,
     username varchar(16),#用户名(唯一)
     image varchar(64),#用户头像图片地址
     signature varchar(64),#个人签名,例如(万般各所是 一切皆圆满)
     nickname varchar(16)  #用户昵称idint用户编号(主键)
     );
     
     #一对多,多的添加外键。多对多,引入第三方表。一对一引入第三方表 UNIQUE 声明即可
     
     

    三、总结

          虽然很水,还是要坚持写博客。

    展开全文
  • MySQL 数据库设计实践

    2015-11-22 09:55:19
    MySQL 数据库设计实践 ,不错的好资料,可以下来看看。
  • Mysql数据库设计标准

    2019-01-15 22:19:34
    Mysql数据库设计标准,规范设计数据库表结构进行的标准说明文档,为设计人员设计数据模型时参考标准 mysql 数据模型表设计标准
  • MySQL数据库设计.docx

    2021-10-03 14:37:59
    MySQL数据库设计.docx
  • mysql数据库设计原则

    2012-09-25 10:43:56
    mysql数据库设计原则 索引使用,语句优化等
  • MySQL数据库设计 本讲大纲 1 创建数据库和数据表 2 定义数据库访问类 MySQL数据库设计 留言本模块是一个中小型的信息平台关于数据库的选择需要充分考虑到成本问题及用户需求如跨平台等问题而MySQL是世界上最为流行的...
  • 阿里巴巴MySQL数据库设计规范

    千次阅读 2020-11-08 09:51:26
    (一) 建表规约 【强制】表达是与否概念的字段,必须使用is_xxx的...数据库字段名的修改代价很大,因为无法进行预发布,所以字段名称需要慎重考虑。 正例:getter_admin,task_config,level3_name 反例:GetterAdmin,

    (一) 建表规约

    1. 【强制】表达是与否概念的字段,必须使用is_xxx的方式命名,数据类型是unsignedtinyint( 1表示是,0表示否),此规则同样适用于odps建表。
      说明:任何字段如果为非负数,必须是unsigned。
    2. 【强制】表名、字段名必须使用小写字母或数字;禁止出现数字开头,禁止两个下划线中间只出现数字。数据库字段名的修改代价很大,因为无法进行预发布,所以字段名称需要慎重考虑。
      正例:getter_admin,task_config,level3_name
      反例:GetterAdmin,taskConfig,level_3_name
    3. 【强制】表名不使用复数名词。
      说明:表名应该仅仅表示表里面的实体内容,不应该表示实体数量,对应于DO类名也是单数形式,符合表达习惯。
    4. 【强制】禁用保留字,如desc、range、match、delayed等,参考官方保留字。
    5. 【强制】唯一索引名为uk_字段名;普通索引名则为idx_字段名。
      说明:uk_即 unique key;idx_即index的简称。
    6. 【强制】小数类型为decimal,禁止使用float和double。
      说明:float和double在存储的时候,存在精度损失的问题,很可能在值的比较时,得到不正确的结果。如果存储的数据范围超过decimal的范围,建议将数据拆成整数和小数分开存储。
    7. 【强制】如果存储的字符串长度几乎相等,使用CHAR定长字符串类型。
    8. 【强制】varchar是可变长字符串,不预先分配存储空间,长度不要超过5000,如果存储长度大于此值,定义字段类型为TEXT,独立出来一张表,用主键来对应,避免影响其它字段索引效率。
    9. 【强制】表必备三字段:id, gmt_create, gmt_modified。
      说明:其中id必为主键,类型为unsigned bigint、单表时自增、步长为1;分表时改为从TDDL Sequence取值,确保分表之间的全局唯一。gmt_create,gmt_modified的类型均为date_time类型。
    10. 【推荐】表的命名最好是加上“业务名称_表的作用”,避免上云梯后,再与其它业务表关联时有混淆。
      正例:tiger_task / tiger_reader / mpp_config
    11. 【推荐】库名与应用名称尽量一致。
    12. 【推荐】如果修改字段含义或对字段表示的状态追加时,需要及时更新字段注释。
    13. 【推荐】字段允许适当冗余,以提高性能,但是必须考虑数据同步的情况。冗余字段应遵循:
      1)不是频繁修改的字段。
      2)不是varchar超长字段,更不能是text字段。
      正例:各业务线经常冗余存储商品名称,避免查询时需要调用IC服务获取。
    14. 【推荐】单表行数超过500万行或者单表容量超过2GB,才推荐进行分库分表。
      说明:如果预计三年后的数据量根本达不到这个级别,请不要在创建表时就分库分表。
      反例:某业务三年总数据量才2万行,却分成1024张表,问:你为什么这么设计?答:分1024张表,不是标配吗?
    15. 【参考】合适的字符存储长度,不但节约数据库表空间、节约索引存储,更重要的是提升检索速度。
      正例:人的年龄用unsignedtinyint(表示范围0-255,人的寿命不会超过255岁);海龟就必须是smallint,但如果是太阳的年龄,就必须是int;如果是所有恒星的年龄都加起来,那么就必须使用bigint。

    (二) 索引规约

    1. 【强制】业务上具有唯一特性的字段,即使是组合字段,也必须建成唯一索引。
      说明:不要以为唯一索引影响了insert速度,这个速度损耗可以忽略,但提高查找速度是明显的;另外,即使在应用层做了非常完善的校验和控制,只要没有唯一索引,根据墨菲定律,必然有脏数据产生。
    2. 【强制】超过三个表禁止join。需要join的字段,数据类型保持绝对一致;多表关联查询时,保证被关联的字段需要有索引。
      说明:即使双表join也要注意表索引、SQL性能。
    3. 【强制】在varchar字段上建立索引时,必须指定索引长度,没必要对全字段建立索引,根据实际文本区分度决定索引长度。
      说明:索引的长度与区分度是一对矛盾体,一般对字符串类型数据,长度为20的索引,区分度会高达90%以上,可以使用count(distinct left(列名,索引长度))/count(*)的区分度来确定。
    4. 【强制】页面搜索严禁左模糊或者全模糊,如果需要请走搜索引擎来解决。
      说明:索引文件具有B-Tree的最左前缀匹配特性,如果左边的值未确定,那么无法使用此索引。
    5. 【推荐】如果有order by的场景,请注意利用索引的有序性。order by 最后的字段是组合索引的一部分,并且放在索引组合顺序的最后,避免出现file_sort的情况,影响查询性能。
      正例:where a=? and b=? order by c; 索引:a_b_c
      反例:索引中有范围查找,那么索引有序性无法利用,如:WHERE a>10 ORDER BY b; 索引a_b无法排序。
    6. 【推荐】利用覆盖索引来进行查询操作,来避免回表操作。
      说明:如果一本书需要知道第11章是什么标题,会翻开第11章对应的那一页吗?目录浏览一下就好,这个目录就是起到覆盖索引的作用。
      正例:IDB能够建立索引的种类:主键索引、唯一索引、普通索引,而覆盖索引是一种查询的一种效果,用explain的结果,extra列会出现:using index.
    7. 【推荐】利用延迟关联或者子查询优化超多分页场景。
      说明:MySQL并不是跳过offset行,而是取offset+N行,然后返回放弃前offset行,返回N行,那当offset特别大的时候,效率就非常的低下,要么控制返回的总页数,要么对超过特定阈值的页数进行SQL改写。
      正例:先快速定位需要获取的id段,然后再关联:
      SELECT a.* FROM 表1a, (selectid from 表1 where 条件 LIMIT 100000,20 ) b where a.id=b.id
    8. 【推荐】SQL性能优化的目标:至少要达到 range级别,要求是ref级别,如果可以是consts最好。
      说明:
      1)consts单表中最多只有一个匹配行(主键或者唯一索引),在优化阶段即可读取到数据。
      2)ref指的是使用普通的索引。(normal index)
      3)range对索引进范围检索。
      反例:explain表的结果,type=index,索引物理文件全扫描,速度非常慢,这个index级别比较range还低,与全表扫描是小巫见大巫。
    9. 【推荐】建组合索引的时候,区分度最高的在最左边。
      正例:如果where a=? and b=? ,a列的几乎接近于唯一值,那么只需要单建idx_a索引即可。
      说明:存在非等号和等号混合判断条件时,在建索引时,请把等号条件的列前置。如:where a>? and b=? 那么即使a的区分度更高,也必须把b放在索引的最前列。
    10. 【参考】创建索引时避免有如下极端误解:
      1)误认为一个查询就需要建一个索引。
      2)误认为索引会消耗空间、严重拖慢更新和新增速度。
      3)误认为唯一索引一律需要在应用层通过“先查后插”方式解决。

    (三) SQL规约

    1. 【强制】不要使用count(列名)或count(常量)来替代count(),count()就是SQL92定义的标准统计行数的语法,跟数据库无关,跟NULL和非NULL无关。
      说明:count(*)会统计值为NULL的行,而count(列名)不会统计此列为NULL值的行。
    2. 【强制】count(distinct col) 计算该列除NULL之外的不重复数量。注意 count(distinct col1, col2) 如果其中一列全为NULL,那么即使另一列有不同的值,也返回为0。
    3. 【强制】当某一列的值全是NULL时,count(col)的返回结果为0,但sum(col)的返回结果为NULL,因此使用sum()时需注意NPE问题。
      正例:可以使用如下方式来避免sum的NPE问题:SELECT IF(ISNULL(SUM(g)),0,SUM(g))FROM table;
    4. 【强制】使用ISNULL()来判断是否为NULL值。注意:NULL与任何值的直接比较都为NULL。
      说明:
      1) NULL<>NULL的返回结果是NULL,不是false。
      2) NULL=NULL的返回结果是NULL,不是true。
      3) NULL<>1的返回结果是NULL,而不是true。
    5. 强制】在代码中写分页查询逻辑时,若count为0应直接返回,避免执行后面的分页语句。
    6. 【强制】不得使用外键与级联,一切外键概念必须在应用层解决。
      说明:(概念解释)学生表中的student_id是主键,那么成绩表中的student_id则为外键。如果更新学生表中的student_id,同时触发成绩表中的student_id更新,则为级联更新。外键与级联更新适用于单机低并发,不适合分布式、高并发集群;级联更新是强阻塞,存在数据库更新风暴的风险;外键影响数据库的插入速度。
    7. 【强制】禁止使用存储过程,存储过程难以调试和扩展,更没有移植性。
    8. 【强制】IDB数据订正时,删除和修改记录时,要先select,避免出现误删除,确认无误才能提交执行。
    9. 【推荐】in操作能避免则避免,若实在避免不了,需要仔细评估in后边的集合元素数量,控制在1000个之内。
    10. 【参考】因阿里巴巴全球化需要,所有的字符存储与表示,均以utf-8编码,那么字符计数方法注意:
      说明:
      SELECT LENGTH(“阿里巴巴”); 返回为12
      SELECT CHARACTER_LENGTH(“阿里巴巴”); 返回为4
      如果要使用表情,那么使用utfmb4来进行存储,注意它与utf-8编码。
    11. 【参考】TRUNCATE TABLE 比 DELETE速度快,且使用的系统和事务日志资源少,但TRUNCATE无事务且不触发trigger,有可能造成事故,故不建议在开发代码中使用此语句。
      说明:TRUNCATE TABLE 在功能上与不带 WHERE子句的 DELETE语句相同。

    (四) ORM规约

    1. 【强制】在表查询中,一律不要使用 *作为查询的字段列表,需要哪些字段必须明确写明。
      说明:1)增加查询分析器解析成本。2)增减字段容易与resultMap配置不一致。
    2. 【强制】POJO类的boolean属性不能加is,而数据库字段必须加is_,要求在resultMap中进行字段与属性之间的映射。
      说明:参见定义POJO类以及数据库字段定义规定,在sql.xml增加映射,是必须的。
    3. 【强制】不要用resultClass当返回参数,即使所有类属性名与数据库字段一一对应,也需要定义;反过来,每一个表也必然有一个与之对应。
      说明:配置映射关系,使字段与DO类解耦,方便维护。
    4. 【强制】xml配置中参数注意使用:#{},#param#不要使用${}此种方式容易出现SQL注入。
    5. 【强制】iBATIS自带的queryForList(String statementName,int start,int size)不推荐使用。
      说明:其实现方式是在数据库取到statementName对应的SQL语句的所有记录,再通过subList取start,size的子集合,线上因为这个原因曾经出现过OOM。
      正例:在sqlmap.xml中引入 #start#, #size#
      Map<String, Object> map = new HashMap<String,Object>();
      map.put(“start”, start);
      map.put(“size”, size);
    6. 【强制】不允许直接拿HashMap与HashTable作为查询结果集的输出。
      反例:某同学为避免写一个,直接使用HashTable来接收数据库返回结果,结果出现日常是把bigint转成Long值,而线上由于数据库版本不一样,解析成BigInteger,导致线上问题。
    7. 【强制】更新数据表记录时,必须同时更新记录对应的gmt_modified字段值为当前时间。
    8. 【推荐】不要写一个大而全的数据更新接口,传入为POJO类,不管是不是自己的目标更新字段,都进行update table set c1=value1,c2=value2,c3=value3; 这是不对的。执行SQL时,尽量不要更新无改动的字段,一是易出错;二是效率低;三是binlog增加存储。
    9. 【参考】@Transactional事务不要滥用。事务会影响数据库的QPS,另外使用事务的地方需要考虑各方面的回滚方案,包括缓存回滚、搜索引擎回滚、消息补偿、统计修正等。
      10.【参考】中的compareValue是与属性值对比的常量,一般是数字,表示相等时带上此条件;表示不为空且不为null时执行;表示不为null值时执行。
    展开全文
  • MySQL 数据库系统设计实现与管理第六版课后习题答案
  • PHP培训之MySQL数据库设计 ,有助于了解Mysql数据库设计
  • MySQL数据库设计规范

    2016-01-15 16:12:24
    细解mysql数据库设计,命名方式、常用SQL语句,及数据库优化
  • Mysql数据库课程设计

    万次阅读 多人点赞 2021-04-14 16:49:09
    今天给大家分享一下使用javafx编写的前端的 Mysql数据库课程设计 题库与试卷生成系统!废话不多说了,直接上截图,希望对大家有所帮助! (建议拿来参考不建议直接CV哦!) 1.系统需求分析 1-1、功能分析 通过深入...
  • 浅谈MySQL数据库设计流程

    千次阅读 2022-01-26 21:31:37
    本次文章的内容是记录有关于MySQL学习中对数据库设计流程的一个记录和在设计流程中需要注意的一些事情。 文章目录系列文章目录前言一、数据库设计1.1为什么要着重于对数据库的详细设计1.2数据库的设计流程(1)...
  • 学生图书馆系统mysql数据库设计

    千次阅读 2022-01-21 09:15:50
    目的: 为了练习创建和设计数据库的能力,设计简单的系统数据库案例 参考了网上的部分文档 原文链接 原文链接 https://wenku.baidu.com/view/6ea98bc848649b6648d7c1c708a1284ac8500515.html 数据库mysql -- 创建...
  • 该实践性环节对于巩固学生的数据库知识,系统地掌握数据库设计理论与应用,加强学生的实践动手能力和提高学生的综合开发经验十分重要和必要。 本课程分为系统需求分析与数据库设计、应用程序功能设计和系统集成调试...
  • mysql数据库设计规范、mysql数据库设计规范、mysql数据库设计规范、mysql数据库设计规范、mysql数据库设计规范mysql数据库设计规范mysql数据库设计规范
  • MySQL数据库原理、设计与应用

    千次阅读 2021-02-03 23:34:34
    目录第1章数据库入门11.1数据库基础知识11.1.1数据库概述11.1.2数据库技术的发展21.1.3三级模式和二级映像31.1.4数据模型61.1.5关系运算101.1.6SQL语言121.1.7常见的数据库产品141.2MySQL安装与配置151.2.1获取MySQL...
  • 黑马程序员编的《MySQL数据库原理、设计与应用》,简单易懂,适合初学者,这个是书的教学大纲。
  • Mysql数据库表结构设计导出

    千次阅读 2021-12-03 21:29:09
    COLUMNS WHERE -- 填写要导出表结构的数据库名称即可 table_schema = 'workstation_db' -- AND -- 表名,填写要导出的表的名称 -- 如果不写的话,默认查询所有表中的数据 -- table_name = '' 复制为制表符分隔值
  • MySQL数据库设计六个步骤

    千次阅读 2021-11-25 11:53:19
    在软件开发的过程中,数据库设计是非常重要的,它需要根据需求分析抽象出E-R图,概念结构设计、逻辑结构设计、物理结构设计,实施及运维。 在公司中一般的流程是: 对问题以及可行性进行分析 进入需求分析阶段,由...
  • mysql数据库课程设计
  • 基于MYSQL的图书管理系统数据库设计

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 498,165
精华内容 199,266
关键字:

mysql数据库设计

mysql 订阅