表结构设计
1.范式设计
第一范式:字段不可切割
第二范式:所有属性都完全依赖于唯一主属性,不然会造成数据冗余,理解为一个实体
第三范式:每个属性和主属性都直接相关,不能间接相关,理解为实体不可再拆分
2.反范式设计
牺牲空间换取时间。通过一定的数据冗余来换取更高的性能和效率。
数据类型优化
(1)越小越好:占用的磁盘、内存、CPU越小
(2)简单就好:比如整型比字符串的操作代价更低
(3)尽量使用相同的属性来存相似或相关的值,尤其是关联的列
(4)尽量避免使用null
具体说明:
各个整型对数据库来说,指定长度是没有意义的。每种整型需要占用的空间是固定的,指定长度只是限制了显示长度,并不像字符串那样起到内存占用的限制作用。
实数类型中,Decimal支持精确计算,但占用的内存较大,Double和Float较小,如果对精度要求不高的话,可以使用浮点类型。或者使用BigInt代替Decimal,例如精度的要求是万分之一,将数字乘以10000转成BigInt去存,避免了Decimal计算代价大的问题。
Varchar作为变长数据类型,在面对短字符串存储时,额外标记变长长度的内存消耗较大,可以考虑用char。
Blob和Text类型尽量拆分出去单独成表。便于运维,减少锁表的情况;增加单页可存放数据的大小,提升QPS。
枚举类型可以替代一些常用的字符串,枚举类型的实际存储是整数的形式。
日期和时间类型,Datetime是8个字节,Timestamp是4个字节。
避免使用null的原因:
· 值计算更复杂,如聚合函数会忽略null值
· 允许为null的列会多一个字节来标记该列的值是否为null
· 索引问题。索引列如果允许为null的话,所有该列为null的数据都会被存在一起,对null值进行查询的情况下,违背了建索引的原则;而且null值增加了查询优化的难度和复杂度。
MySql的事务概念
隔离级别 | 脏读 | 不可重复读 | 幻读 |
Read Uncommited 读未提交 | 可能 | 可能 | 可能 |
Read Commited 读已提交 | - | 可能 | 可能 |
Repeatable Read 可重复读 | - | - | - (SQL标准的值为可能,但mysql在这里做了优化) |
Serializable 串行化 | - | - | - |
脏读:一个事务读到了另一个事务已修改但未提交的数据
不可重复读:事务内相同的记录在修改前后检索得到的值不一样
幻读:事务内相同的记录在增删前后检索得到的值不一样
隐式提交:
写业务时,隐式提交常见于DDL和新建事务,二者都会隐式的截断当前事务并提交已执行的部分。
索引
MySql内的索引类型:
1.哈希索引:只适合做等值查询的索引类型。自适应哈希索引是innoDB引擎缓存池中的一部分,由开发者自定义开关和分区数,引擎自己决定执行逻辑。用于监控表上二级索引,如果二级索引被频繁访问,标记为热数据,自动生成到哈希索引内来加快访问速度。
2.全文索引:采用反向索引设计。反向索引存储单词列表,对于每个单词,存储该单词出现的文档列表。
3.B+树。实际存储数据的结构,可以以磁盘的物理结构来等效的理解B+树。分为:
· 聚簇索引:用表的主键构建的B+树。将整张表的行记录数据存放在该树的叶子结点中。
· 辅助/二级索引:非主键列构建的B+树。只包含该索引列和主键的数据,所以查询的列不是索引
列或者主键列,需要回表。
· 联合/复合索引:表上多个列组合构建的B+树。只有第一个列全树有序,其它索引列局部有序。