一、设计主要包括：存储引擎，字段类型，范式和逆范式 1.存储引擎 在创建表的时候我们使用sql语句，Create table tableName () engine = myisam|innodb; 这里就指明了存储引擎是myisam还是innodb。存储引擎是一种用来存储MySQL中对象（记录和索引）的一种特定的结构（文件结构），处于MySQL服务器的最底层，直接存储数据。导致上层的操作，依赖于存储引擎的选择。关系如下图： 网络接口层：与客户端通信，比如输出数据等等。 存储引擎层：存储数据的规则，方式。 本质：存储引擎就是特定的数据存储格式（方案）。 可以使用show engines 命令来查看当前MySQL支持的存储引擎列表。
① innoDB存储引擎 Mysql版本>=5.5 默认的存储引擎，MySQL推荐使用的存储引擎。支持事务，行级锁定，外键约束。事务安全型存储引擎。更加注重数据的完整性和安全性。 （1）存储格式 数据： 索引集中存储，存储于同一个表空间文件。 数据： 记录行。 索引：一种检索机制，也需要一定的空间，就相当于一本字典的目录。 示例： 创建一个test数据库，新建一张student表，选择存储引擎位innodb，然后打开MySQL的data下的test目录，发现有以下三个文件。 其中db.opt存放了数据库的配置信息，比如数据库的字符集还有编码格式。student.frm 是表结构文件，仅存储了表的机构、元数据（meta）、包括表结构定义信息等。不论是那个表引擎都会有一个frm文件。 student.ibd 是表索引文件，包括了单独一个表的数据索引内容。 如果往表里插入新数据，则在mysql的data目录下会生成ibdata文件，这个文件是存储所有innodb表的数据。 关于innodb引擎的详细介绍： 使用innodb引擎，需要理解独立表空间、共享表空间。 独立表空间：每个表都会生成一独立的文件方式存储，每个表都有一个.frm的文件描述，还有一个.ibd文件。其中这个文件包括了单独一个表的数据及索引内容，默认情况下它的存储在mysql指定的目录下。 独立表空间优缺点： 优点： 每个表独有自己独立的表空间；每个表的数据和索引都会存储在各个独立的表空间中；可以实现单表在不同的数据进行迁移；表空间可以回收（储了drop table操作，表空间不能自己回收）；drop table 操作自动回收表空间，如果对统计分析或是日值表，删除大量数据后可以通过：alter table tablename engin = innodb 进行回缩不用的空间，对于使用innodb-plugin的innodb使用truncate table 会使用空间收缩。 对于使用独立表空间，不管怎么删除，表空间的碎片都不会太严重。
缺点： 单表增加过大，如超过100G，对于单表增长过大的问题，如果使用共享表空间可以把文件分开，但同样有一个问题，如果访问的范围过大同样也会访问多个文件，一样会比较慢。对于独立表空间也有一个解决办法是：使用分区表，也可以把那个大的表空间移动到别的空间上然后做一个链接。其实从性能上出发，当一个表超过100G有可能响应也是较慢的，对于独立表空间还容易发现问题早作处理。
共享表空间优缺点： 优点： 可以将表空间分成多个文件存放到各个磁盘上（表空间文件大小不受表大小的限制，如一个表可以分布在不同的文件上），数据和文件放在一起方便管理。 缺点： 所有的数据和索引存放在一个文件里，将来会是一个很大的文件，虽然可以把一个大文件分成多个小文件，但是多个表及索引在表空间中混合存储，这样对一个表做大量删除操作后表空间将有大量的空隙，特别是对统计分析，日值系统这类应用最不适合用共享表空间。
如何开启独立表空间？ 查看是否开启独产表空间： mysql> show variables like ‘%per_table’; ±----------------------±------+ | Variable_name | Value | ±----------------------±------+ | innodb_file_per_table | OFF | ±----------------------±------+ 设置开启： 在my.cnf文件中[mysql]节点下添加innodb_file_per_table = 1 或者通过命令： set global innodb_file_per_table = 1; 注： innodb_file_per_table值来进行修改即可，但是对于之前使用过的共享表空间则不会影响，除非手动的去进行修改 innodb_file_per_table =1 为使用独占表空间 innodb_fiel_per_table = 0 位使用共享表空间 修改独占表中间的数据存储位置 innodb_data_home_dir = “C:\mysql\data” innodb_log_group_home_dir = “C:\mysql\data” innodb_data_file_path=ibdata1:10M:autoextend innodb_file_per_table=1
参数说明： 这个设置配置一个可扩展大小的尺寸位10MB的单独文件，名为ibdata1。没有给出文件位置，所以默认的实在MySQL的数据目录内。（对数据来进行初始化的设置）。 innodb_data_home_dir 代表为数据库文件所存放的目录 innodb_log_group_home_dir 为日志存放目录 innodb_fiel_per_table 是否使用共享及独占表空间 以上的几个参数必须在一起加入。
对于参数一些注意的地方 innoDB不创建目录，所以在启动服务器之前请确认"所配置的路径目录"的确存在。这对你配置的任何日志文件目录来说也是真实的。使用Unix或DOS的makdir命令来创建任何必需的目录。 通过把innodb_data_home_dir的值原原本本的部署到数据文件名，并在需要的地方添加斜杠或反斜杠，innoDB为每个数据文件形成目录路径。 如果innodb_data_home_dir 选项根本没有在my.cnf中提到，默认是"dot"目录./，这意思是MySQL数据目录。
（2）数据按照主键顺序存储 插入时做排序工作，效率低。
（3）特定功能 事务、外键约束：都是为了维护数据的完整性。 并发性处理： innodb擅长处理并发的。因为它使用了行级锁定，只该行锁了，其他行没锁。 行级锁定：row-level locking， 实现了行级锁定，在一定情况下，可以选择行级锁来提升并发性。也支持表级锁定，innodb会自带锁，不需要我们自己设置。 多版本并发控制，MVC，效果达到无阻塞读操作。 （4）总结： innodb擅长事务、数据的完整性及高并发处理，不善长快速插入（插入前要排序，消耗时间）和检索
②MyISAM存储引擎介绍
MySQL<= 5.5 MySQL 默认的存储引擎。 ISAM：indexed Sequential Access Method（索引顺序存取方法）的缩写，是一种文件系统。 擅长与处理，高速读与写。 （1）存储方式 数据和索引分别存储于不同的文件中。 （2）数据的存储顺序为插入顺序（没有经过排序） 插入速度快，空间占用量小。 （3）功能 a.全文索引支持。（mysql>=5.6时innodb 也支持） b.数据的压缩存储。.MYD文件的压缩存储。 压缩前，数据是25600kb: 进行压缩：使用工具myisamPack完成压缩功能：该工具mysql自带 进入到需要压缩表的数据目录，执行压缩指令myisampack表名。配置环境变量。 压缩后： 注意：压缩后，需要重新修复索引 查看结果，发现现在的数据变成了12741kb了，比之前更小了。 压缩又是：节省磁盘空间，减少磁盘IO开销。特点：压缩后的表变成了只读表，不可写。 如果需要更新数据，则需要先解压再更新。利用工具： myisamchk&ndash；unpack 表名进行解压 解压变成原来的25600kb 刷新表的状态：flush table myisam_2 c.并发性： 仅仅支持表级锁定，不支持高并发。 支持并发插入。写操作中的插入操作，不会阻塞读操作（其他操作）
（4）关于innodb 和mysiam的取舍 innodb：数据完整性，并发性处理，擅长更新，删除。 myisam： 告诉查询及插入。擅长插入和查询。 具体举例：
那么对于微博项目来看，选择哪个存储引擎呢？
a.微博主要是插入微博和查询微博，较为适合MyISAM； b.微博在更新微博和删除微博，要少的多，较为适合MyISAM； c.对于数据完整性的需求没有那么强烈，比如用户删除微博，关联的传播和评论并不要求都做响应的行为，较为适合MyISAM；
那么对于记账财务系统，选择那款存储引擎呢？ a.财务系统除了读取和插入，经常要进行数据的修改和删除，较为适合innodb； b.在进行财务变更的时候，如果失败需要回滚，必须用到事务，较为适合innodb； c.每个用户的财务数据完整性和同步性非常重要，需要外键支持，否则财务将会混乱，较为适合innodb。
3.其他存储引擎 （1）Archive：存档型，仅提供插入和查询操作。非常高效阻塞的插入和查询。 （2）Memory: 内存型，数据存储于内存中，存储引擎。缓存型存储引擎。 （3）插件式存储引擎：用C和C++开发的存储引擎。
4.所得概念：当客户端操作表（记录）时，为了保证操作的隔离性（多个客户端操作不能互相影响），通过枷锁来处理。 操作方面： 读锁：读操作时增加的锁，也叫共享锁，S-lock。特征是阻塞其他客户端的写操作，不阻塞读操作。（并发读） 写锁：写操作时增加的锁，也叫独占锁或排他锁，X-lock。特征时阻塞其他客户端的读，写操作。 锁定粒度（范围）： 行级：提升并发，锁本身开销大 表级：不利于并发性，锁本身开销小。
②字段类型选择 字段类型应该要满足需求吗，尽量要满足一下需求。 尽可能小（占用空间少）、尽可能定长（占用存储空间固定）、尽可能使用整数。 1.列类型之数值 （1）整形 MySQL数据库支持五种整数类型，包括：TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT五种。 整数类型占用空间和取值范围 类型 字节 最小值 最大值 TINYINT 1 有符号：-128 无符号：0 有符号：127 无符号：255
SMALLINT 2有符号：-32768无符号：0有符号：32767无符号：65535
MEDIUMINT 3有符号：-8388608无符号：0有符号：8388607无符号：16777215
INT/INTEGER 4有符号：-2147483648无符号：0有符号：2147483647无符号：4294967295
BIGINT 8 有符号：-9223372036854775808无符号：0 有符号：9223372036854775807无符号：18446744073709551615
五种整型的适用场景： TINYINT，年龄，包含在0~255之间； SMALLINT，端口号，包含在0~65535之间； MEDIUMINT，中小型网站注册会员，1600万够用； INT，身份证编号，42亿可以用很久； BIGINT，Twitter微博量，几百亿
（2）浮点型（非精确） MySQL数据库支持两种浮点类型：FLOAT(单精度)和DOUBLE(双精度)两种浮点型(非精确)占用空间和取值范围类型 字节 范围： FLOAT 4 正数范围：1.175494351E-383.402823466E+38，负数范围：-3.402823466E+38-1.175494351E-38 DOUBLE 8 正数范围：1.7976931348623157E-308~2.2250738585072014E+308 负数范围：-2.2250738585072014E+308~-1.7976931348623157E-308
（3）定点型(精确) 浮点型由于内部的存储方式是数值，导致它在一定程度上取得的是近似值而非精确值。如果使用定点型，那么就可以精确取得小数部分，因为它内部存储方式是字符串形式。 定点型(精确)占用空间和取值范围 类型 字节 范围： DECIMAL/NUMERIC M+2 M最大65位，D最大30位。 创建一个定点型格式：DECIMAL(M,D)，表示小数点D位，整数部分M位及M位内
2.列类型之日期 MySQL数据库中有五个可用的日期时间数据类型，分别为：DATE、DATETIME、TIME、YEAR、TIMESTAMP。 日期时间类型占用空间和取值范围 类型 字节 最小值 最大值 YEAR 1 1901 2155 TIME 3 -838:59:59838:59:59 DATE 4 1000-01-01 9999-12-31 TIMESTAMP 4 1970-01-01 00:00:00 2038-01-19 03:14:07 DATETIME 8 1000-01-01 00:00:00 9999-12-31 23:59:59 TIMESTAMP有几个特点： a.当更新一条数据的时候，设置此类型根据当前系统可自动更新时间。 b.如果插入一条null，也会自动插入当前系统时间。 c.创建字段时，系统会自动给一个默认值。 d.根据当前时区来存储和查询时间，存储时对当前时区进行转换，查询时再转换为当前的时区。
//查看当前时区 SHOW VARIABLES LIKE 'time_zone'; //设置为东九区，查询时间就会加1小时 SET time_zone='+9:00'; DATE占用3个字节，包含年月日，范围和DATETIME一样。DATE长度是0，无法设置。 YEAR占用1个字节，包年年份，长度默认为4位，无法设置。 TIME占用3个字节，包含时分秒，长度0到6之间，用于设置微秒。对于TIME的范围的时是-838到838的原因，是因为TIME类型不但可以保存一天的时，还可以包含时间之间的间隔。 综上考虑：使用datetime，当然也可以使用int（11）来保存时间戳。
关于INT(11)存放时间戳的优点如下： a.INT占4个字节，DATETIME占8个字节； b.INT存储索引的空间比DATETIME小，查询快，排序效率高； c.在计算机时间差等范围问题，比较方便。
3.列类型之字符 字符集校对规则utf8_general_ci表示校对时不区分大小写，相对的cs表示区分大小写。还有一个bin结尾的是字节比较。而general是地区名，这里是通用，utf8表示编码。如果是gbk，可以使用gbk_chinese_ci，如果是utf8则用utf8_general。MySQL提供了多种对字符数据的存储类型，包括：CHAR、VARCHAR、VARBINARY、BLOB、TEXT、ENUM和SET等多种字符类型。
（1）CHAR是保存定长字符串，而VARCHAR则是保存变长字符串。CHAR(5)表示必须保存5个字符，而VARCHAR(5)则表示最大保存字符为5。如果是UTF8编码下，长度为5的CHAR类型，最多可以存储15字节，也就是5个汉字的内容。因为一个汉字占3个字节。 由于CHAR类型是定长，MySQL会根据定义的长度进行分配空间，在处理速度上比VARCHAR快的多，所以适合存储例如手机、身份证这种定长的字符，否则就会造成浪费。那么CHAR类型最大可以插入255个字符，最多可以存储765个字节。
（2）BINARY和VARBINARY是采用二进制存储的，没有字符集概念，意义在于防止字符集的问题导致数据丢失，存储中文会占用两个字符，会乱码，半截会问号。因为是采用二进制存储，在比较字符和排序的时候，都是二进制进行的，所以只有需要操作二进制时才需要使用。
（3）八种适合文本内容的大数据类型：TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、TINYBLOG、BLOB、MEDIUMTEXT、LONGTEXT。
综上：短文本定长用char，变长用varchar，长文本用text
严格模式
我们使用的是WAMP集成环境，默认安装的情况下，是非严格模式，用于部署阶段。而开发调试阶段，强烈建议使用严格模式，方便开发中调试将问题及时暴露出来。因为在非严格模式下将NULL插入NOTNULL等非法操作都是被运行的。设置严格模式只要打开my.ini文件，在末尾添加一句： sql-mode=“STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION” 然后，重启服务器即可。检查SQL_MODE状态 SELECT @@global.sql_mode;
③范式与逆范式 为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式。 第一范式1NF，原子性 第二范式2NF，消除部分依赖 第三范式3NF，消除传递依赖 1、范式 （1）第一范式：具有原子性，确保每列保持原子性。 第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足第一范式。第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性本来直接将“地址”属性设计成一个数据库的表字段就行。但是如果系统经常会访问“地址”属性的”城市“部分，那么就非要将”地址“这个属性重新拆分为，省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足数据库的第一范式。 （2）第二范式：主键列与非主键列尊选完全函数依赖关系，确保表中的每列都和主键相关。
第二范式在第一范式的基础之上更上一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张库表中。
（3）第三范式：非主键列之间没有传递函数依赖关系索引，确保每列都和主键列直接相关，而不是间接相关。所谓传递函数依赖，指的是如果存在"A→B→C"的决定关系，则C传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系： 关键字段→非关键字段x→非关键字段y 比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其他信息（比如姓名、年龄等）的字段。 先满足第一范式，在满足第二范式，才能满足第三范式。
2、逆范式 逆范式是指打破范式，通过增加冗余或重复的数据来提高数据库的性能。 示例： 假如有一个商品表Goods： 字段有Goods_id(商品表), goods_name(商品名称), cat_id(所属类别的id)。 还有一个分类表Category： 字段有Cat_id(类别id), cat_name(类别名称)。 现在要查询类别id为3的商品的数量，例如分类列表查询： 分类ID 分类名称 商品数量 3 计算机 567 可以使用下列sql语句 Select c.*, count(g.goods_id) as goods_count from category as c left join goods as g c.cat_id=g.cat_id group by c.cat_id; 但是，假如商品数量较大，那么就比较耗性能了。这时，我们可以考虑重新设计Category表：增加存当前分类下商品数量的字段。 Cat_id, cat_name, goods_count 每当商品改动时，修改对应分类的数量信息。 再查询分类列表时：Select * from category; 此时额外的消耗，出现在维护该字段的正确性上，保证商品的任何更新都正确的处理该数量才可以。
原文链接：https://m.2cto.com/database/201701/557910.html 如有版权问题请联系我， 2237197911@qq.com
