实际开发项目中数据库mysql的设计（参考）

一、实际开发项目中数据库的设计

1.需求

• 根据项目的原型图，进行数据库的设计。

  • 表结构的设计
  • 字段类型、是否主键、是否为null等
  • 索引的设计，在哪些字段上建立索引等
  • 数据库引擎的选择，InnoDB、MyISAM等

• 思路：词性分析法，动词体现关系，名词体现表或者字段；

2.设计

• 主键：一般业务数据不会做主键；

• mysql5.7版本之后，新出的数据类型JSON,longtext

  • 1.对于文章来说，文章图片一旦发布，不会变化，对于字符比较长，不经常变化的数据，可以使用json存储。
  • 2.这样可以减少定义一张表

• 数据库三范式：

  • 1.表中的字段不可分割，原子性；
  • 2.满族1范式的基础上，表要有主键依赖
  • 3.满族2范式的基础上，主键之间要直接依赖。

• 为了提高查询效率，可以做冗余字段的设计（空间换时间的思想，属于一种反范式的设计）

• 字段类型的选择

  • 整型的存储大小与显示大小问题
    • int(3)和int(6)的存储范围是相同的
  • char与varchar的选择
    • char不可变，提高查询效率，但有空间浪费的问题
    • varchar可变，查询效率低，但是节省空间

• 常见的数据库类型就不列举了，自行百度一下。如int,char,varchar,date,datetime等

• 索引

  • 主键Primary Key
  • 外键Foreign Key
    • 外键的作用是：保持数据的完整性
  • CASCADE
    • 在父表上更新和删除数据的时候，同步更新和删除子表的数据
      • ON DELETE：删除主表自动删除子表，删除从表，主表不变
      • ON UPDATE：更新主表自动更新子表，更新从表，主表不边
  • SET NULL
    • 在父表上更新和删除的时候，将子表上匹配记录的列设置为null（注意子表的外键列不能设为not null）
      • ON DELETE：删除主表自动更新子表为NULL，删除从表，主表不变
      • ON UPDATE：更新主表自动更新子表为NULL，更新从表，主表不变
  • NO ACTION
    • 如果子表中有匹配的记录，则不允许对父表对应的选键进行更新删除操作
    • ON DELETE：从表记录不存在，主表才可以删除。删除从表，主表数据不变。
    • ON UPDATE：从表记录不存在，主表才可以更新。删除从表，主表数据不变。
  • RESTRICT 桐 no action，都是立即检查外键约束
  • SET DEFAULT
    • 父表有变更的时候，子表将外键列设置为一个默认的值。但目前Innodb不支持。
  • 索引 Key/ Index
    • 提高查询效率，减慢增删改速度
  • 唯一约束Unique
    • 保证数据不重复

• MySQL数据库引擎

数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行增删改查数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能。使用不同的存储引擎，还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。

SHOW ENGINS # 可以来查看MySQL提供的引擎

SHOW VARIABLES LIKE 'storage_engine' # 查看数据库默认使用那个引擎

InnoDB存储引擎

InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，InnoDB是默认的MySQL引擎。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大小的自动扩展数据文件，以及两个id_logfile0和ib_logfile1的5MB大小的日志文件。

MyISAM存储引擎

MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事物。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）

MEMORY存储引擎

MEMORY存储引擎将表中的数据存储到内存中，未查询和引用其他表数据提供快速访问。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）

存储引擎的选择

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qh4h9f32-1571020611647)(file:///C:/Users/%E6%B8%85%E9%A3%8E/Desktop/%E9%BB%91%E9%A9%AC%E5%A4%B4%E6%9D%A1%E9%A1%B9%E7%9B%AE%E8%AF%BE%E4%BB%B6/images/mysql%E5%AD%98%E5%82%A8%E5%BC%95%E6%93%8E.png)]

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

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

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

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

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

二、理解ORM

ORM（对象关系映射） O代表object对象，R代表relation关系，M代表mapping映射

• 类映射表
• 属性映射字段
• 对象映射数据、记录。

1）ORM的优点

• 省去自己拼写SQL语句，保证了SQL的正确性
• 一次编写适配多个数据库
• 防止注入攻击
• 在数据库表名或者字段名发生变化的时候，只需要修改模型类即可。

2）ORM的缺点

• 执行效率低

3）使用ORM的方式选择

• 1.Django先有类、通过迁移创建表，实现CRUD；
  • 优点就是省事，表发生变化的时候，可以重新迁移
  • 缺点就是不能精确控制表中字段类型和细节
• 2.先用原生SQL创建数据库表，再编写模型类映射
  • 优点：可以很好的控制数据表结构的细节，避免发生迁移错误
  • 缺点：麻烦，增加了很多工作。

三、数据库理论

一、复制集与分布式

• 复制集：数据库中数据相同，起到备份作用，就是一份数据，保存到不同的机器上，多份存储，作用是数据高可用

• 分布式：数据库中数据不同，共同组成完整数据集合，每个阶段被称为一个分片，就是一份数据，拆分成多份数据保存到不同的机器上，分块存储，作用是数据高吞吐

• 应用：

  • 1.复制集与分布式可以单独使用，可以组合使用
  • 2.reids-cluster默认是集成复制集，既有高可用，也有吞吐量

二、MySQL

1）主从复制

复制分成三步：

1.master将更改记录到二进制日志（binary log）中，（这些记录叫做二进制日志事件，binary log events）；

2.slave将master的binary log events 拷贝到它的中级日志（realy log）；

3.salve重做中继日志中的事件，将改变反应它自己的数据。

用自己的话来理解：

​ 就是一个请求过来到Master，在Master上可以修改也可以插入数据，之后会生成一个日志文件叫Binary log，这个文件写在Master自己的机器上，从Slave会开启一个线程，然后读取Master的Binary_log日志，让然后写入到本地的Reloay log中，然后开启另外一个子线程，把这个日志里面的操作写入到自己的机器上，也就是开启两个线程，一个是读Master的日志，另一个是写入日志的操作。其实就是Master中执行的命令，在Slave中再执行一遍。保证数据一致。

2）读写分离对事务是否有影响？

​ 对于写操作包括开启事务和提交事务或回滚要在一台机器上执行，分赛到多态master执行后数据库原生的单机事务就失效了。

​ 对于事务中同时包含读写操作，与事务的隔离级别设置有关，如果事务隔离界别为read-committed或者read-uncommitted，读写分离没有影响，但是如果隔离界别为repeatable-read或者seiralizable，读写分裂就会有影响，因为会在slave上看到心数据，而正在事务中的master就看不到新数据。

3）分库分表

分库分表

• 垂直分表：单表字段太多，把字段拆分出去。
• 水平分表：单表数据太多，把数据拆分出去。
• 使用：分库分表的顺序应该是先垂直分，后水平分。

分库分表后面临的问题

• 事务支持

  分库分表后，就成了分布式事务了。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价； 如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担。

• 多库结果集合并（group by，order by）

• 跨库join

  分库分表后表之间的关联操作将受到限制，我们无法join位于不同分库的表，也无法join分表粒度不同的表， 结果原本一次查询能够完成的业务，可能需要多次查询才能完成。 粗略的解决方法： 全局表：基础数据，所有库都拷贝一份。 字段冗余：这样有些字段就不用join去查询了。 系统层组装：分别查询出所有，然后组装起来，较复杂。

四、数据库优化

数据库是web应用至关重要的一个环节，其性能的优劣将会影响整合Web应用，所以需要对数据库进行优化以及提高使用性能。

sql语句优化：

• 1.避免全表扫描，应考虑在 where 及 order by 涉及的列上建立索引
• 2.查询时使用select明确指明所要查询的字段，避免使用select *的操作；
• 3.SQL语句尽量大写
• 4.尽量避免在 where 子句中使用!=或<>操作符，
• 5.遵循最左原则，在where子句中写查询条件时把索引字段放在前面
• 6.能使用关联查询解决的尽量不要使用子查询，能不使用关联查询的尽量不要使用关联查询；
• 7.不需要获取全表数据的时候，不要查询全表数据，使用LIMIT来限制数据。

数据库优化：

• 在进行表设计的时候，可以适度增加冗余字段，减少JOIN操作；
• 多字段表可以进行垂直分表优化，多数据表可以进行水平分表优化
• 选择恰当的数据类型，如整形的选择；
• 对于强调快速读取的数据，可以考虑使用MyISAM数据库引擎；
• 对于较频繁的作为查询条件的字段创建索引；唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件；更新非常频繁的数据不适合创建索引；
• 编写SQL语句的时候使用上述的方案进行优化；
• 使用慢查询工具找出效率低下的SQL语句进行优化；
• 构建缓存，减少数据库的磁盘操作；
• 可以考虑结合使用内在型数据库，如Redis，进行混合存储。

五、分布式ID

• UUID

  • UUID是通用唯一标识码（Universally Unique Identifiter）的缩写，开放软件基金会（OSF）规范定义了包括网卡MAC地址、时间戳、名字空间、随或伪随机数、时序等元素。利用这些元素来生成UUID

  • UUID是由128位二进制，一般转换成十六进制，然后用String表示。

    550e8400-e29b-41d4-a716-446655440000
    

• UUID的优点：

  • 通过本地生成，没有经过网络I/O，性能较快
  • 无序，无法预测出他的生成顺序（其实也算是一个缺点）

• UUID的缺点：

  • 128位二进制一般转换成36位的16进制，太长了只能用String存储，占用空间较多。
  • 不能生成递增有序的数字

• 数据库主键自增

大家对于唯一标识最容易想到的就是主键自增，这个也是我们最常用的方法。例如我们有个订单服务，那么把订单id设置为主键自增即可。

• 单独数据库 记录主键值

• 业务数据库分别设置不同的自增起始值和固定步长，如

  第一台 start 1  step 9 
  第二台 start 2  step 9 
  第三台 start 3  step 9
  

优点:

• 简单方便，有序递增，方便排序和分页

缺点:

• 分库分表会带来问题，需要进行改造。

• 并发性能不高，受限于数据库的性能。

• 简单递增容易被其他人猜测利用，比如你有一个用户服务用的递增，那么其他人可以根据分析注册的用户ID来得到当天你的服务有多少人注册，从而就能猜测出你这个服务当前的一个大概状况。

• 数据库宕机服务不可用。

• Redis

  熟悉Redis的同学，应该知道在Redis中有两个命令Incr，IncrBy,因为Redis是单线程的所以能保证原子性。

  优点：

  • 性能比数据库好，能满足有序递增。

  缺点：

  • 由于redis是内存的KV数据库，即使有AOF和RDB，但是依然会存在数据丢失，有可能会造成ID重复。
  • 依赖于redis，redis要是不稳定，会影响ID生成。

• 雪花算法-Snowflake

​ Snowflake是Twitter，推特公司提出来的一个算法，其目的是生成一个64bit的整数。

• 雪花算法id = 时间戳 + 机器码 + 序号
• 缺陷：时间回拨，因为雪花算法基于时间，如果时间出现了回拨问题，那么就有可能会出现重复的数据，所以我们需要在业务代码中实现判断，每生产一个时间戳，来对比一下上次的时间戳，如果小于的话就抛出异常。