设计:存储引擎,字段类型,范式与逆范式
功能:索引,缓存,分区分表。
架构:主从复制,读写分离,负载均衡。
合理SQL:测试,经验。
一、存储引擎
在创建表的时候我们使用sql语句,Create table tableName () engine=myisam|innodb;
这里就指明了存储引擎是myisam还是innodb。存储引擎是一种用来存储MySQL中对象(记录和索引)的一种特定的结构(文件结构),处于MySQL服务器的最底层,直接存储数据。导致上层的操作,依赖于存储引擎的选择。地位如下图:
网络接口层:与客户端通信,比如传输数据等等。存储引擎层:存储数据的规则,方式。
本质:存储引擎就是特定的数据存储格式(方案)。
可以使用show engines命令来查看当前MySQL支持的存储引擎列表。
1、InnoDB存储引擎介绍
Mysql版本>=5.5 默认的存储引擎,MySQL推荐使用的存储引擎。支持事务,行级锁定,外键约束。事务安全型存储引擎。更加注重数据的完整性和安全性。
(1)存储格式
数据,索引集中存储,存储于同一个表空间文件中。
数据:记录行。索引:一种检索机制,也需要一定的空间,就相当于一本字典的目录。
示例: 创建一个test数据库,新建一张student表,选择存储引擎为innodb, 然后打开mysql的data下的test目录,发现有以下3个文件。
其中db.opt存放了数据库的配置信息,比如数据库的字符集还有编码格式。student.frm是表结构文件,仅存储了表的结构、元数据(meta),包括表结构定义信息等。不论是哪个表引擎都会有一个frm文件。student.ibd是表索引文件,包括了单独一个表的数据及索引内容。
如果往表里插入了新的数据,则在mysql的data目录下会生成ibdata1文件,这个文件是存储了所有innodb表的数据。
关于innodb引擎的详细介绍:
使用innodb引擎时,需要理解独立表空间、共享表空间。
独立表空间:每个表都会生成以独立的文件方式来存储,每个表都一个.frm的描述文件,还有一个.ibd文件。其中这个文件包括了单独一个表的数据及索引内容,默认情况下它的存储在mysql指定的目录下。
独立表空间优缺点
优点:
每个表都有自己独立的表空间;每个表的数据和索引都会存储在各个独立的表空间中;可以实现单表在不同的数据进行迁移;表空间可以回收(除了drop table操作,表空不能自己回收);drop table 操作自动回收表空间,如果对统计分析或是日值表,删除大量数据后可以通过 :alter table tablename engin=innodb进行回缩不用的空间;对于使用inodb-plugin的innodb使用truncate table会使用空间收缩。;对于使用独立表空间,不管怎么删除,表空间的碎片都不会太严重。
缺点:
单表增加过大,如超过100G。对于单表增长过大的问题,如果使用共享表空间可以把文件分开,但有同样有一个问题,如果访问的范围过大同样会访问多个文件,一样会比较慢。对于独立表空间也有一个解决办法是:使用分区表,也可以把那个大的表空间移动到别的空间上然后做一个连接。其实从性能上出发,当一个表超过100个G有可能响应也是较慢了,对于独立表空间还容易发现问题早做处理。
共享表空间:某一个数据库所有的表数据,索引文件全部都放在一个文件中,默认这个共享表空间的文件路径在data目录下,默认的文件名为 ibdata1,初始化为10M。
共享表空间优缺点
优点:可以将表空间分成多个文件存放在各个磁盘上(表空间文件大小不受表大小的限制,如一个表可以分布在不同的文件上),数据和文件放在一起方便管理。
缺点:所有的数据和索引存放到一个文件中,将来会是一个很大的文件,虽然可以把一个大文件分成多个小文件,但是多个表及索引在表空间中混合存储,这样对一个表做了大量删除操作后表空间将有大量的空隙,特别是对统计分析、日值系统这类应用最不适合用共享表空间。
如何开启独立表空间?
查看是否开启独产表空间:
mysql> show variables like '%per_table';
±----------------------±------+
| Variable_name | Value |
±----------------------±------+
| innodb_file_per_table | OFF |
±----------------------±------+
设置开启:
在my.cnf文件中[mysqld] 节点下添加innodb_file_per_table=1
或者通过命令:set global innodb_file_per_table=1;
注:
innodb_file_per_table值来进行修改即可,但是对于之前使用过的共享表空间则不会影响,除非手动的去进行修改或者是
innodb_file_per_table=1 为使用独占表空间
innodb_file_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_file_per_table 是否使用共享以及独占表空间来
以上的几个参数必须在一起加入。
对于参数一些注意的地方
InnoDB不创建目录,所以在启动服务器之前请确认”所配置的路径目录”的确存在。这对你配置的任何日志文件目录来说也是真实的。使用Unix或DOS的mkdir命令来创建任何必需的目录。
通过把innodb_data_home_dir的值原原本本地部署到数据文件名,并在需要的地方添加斜杠或反斜杠,InnoDB为每个数据文件形成目录路径。
如果innodb_data_home_dir选项根本没有在my.cnf中提到,默认值是“dot”目录 ./,这意思是MySQL数据目录。
(2)数据按照主键顺序存储
插入时做排序工作,效率低。
(3)特定功能
事务、外键约束 : 都是为了维护数据的完整性。
并发性处理:
innodb擅长处理并发的。因为它使用了行级锁定,只该行锁了,其它行没有锁。
行级锁定:row-level locking,实现了行级锁定,在一定情况下,可以选择行级锁来提升并发性。也支持表级锁定,Innodb会自带锁,不需要我们自己设置。
多版本并发控制, MVCC,效果达到无阻塞读操作。
(4)总结:innodb擅长事务、数据的完整性及高并发处理,不擅长快速插入(插入前要排序,消耗时间)和检索。
2.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 –unpack 表名 进行解压
解压后,变成了原来的25600KB
刷新表的状态:flush table myisam_2
c.并发性:
仅仅支持表级锁定,不支持高并发。
支持并发插入。写操作中的插入操作,不会阻塞读操作(其他操作)
(4)关于Innodb 和myisam的取舍:
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
4.列类型之属性
无符号(UNSIGNED)和填充零(ZEROFILL),还有是否为空、默认值、主键、自动编号。
严格模式
我们使用的是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;
此时额外的消耗,出现在维护该字段的正确性上,保证商品的任何更新都正确的处理该数量才可以。