目录
一、存储引擎的选择
1.MyISAM
2.MEMORY
3.InnoDB
二、表结构设计
1. 范式设计,消除冗余
2.反范式设计,适当冗余
三、主键
四、选择数据类型
一、存储引擎的选择
数据库存储引擎:是数据库底层软件组织,数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能,使用不同的存储引擎,还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是插件式存储引擎。
常用的存储引擎:
1.MyISAM
MyISAM引擎是MySQL 5.1及之前版本的默认引擎,它的特点是:
不支持行锁,读取时对需要读到的所有表加锁,写入时则对表加排它锁
不支持事务
不支持外键
不支持崩溃后的安全恢复
在表有读取查询的同时,支持往表中插入新纪录
支持BLOB和TEXT的前500个字符索引,支持全文索引
支持延迟更新索引,极大提升写入性能
对于不会进行修改的表,支持压缩表,极大减少磁盘空间占用
2.MEMORY
将所有数据保存在内存中,访问速度快,通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制,太大的表无法缓存在内存中,而且无法保障数据的安全性。
3.InnoDB
InnoDB在MySQL 5.5后成为默认索引,它的特点是:
支持行锁,采用MVCC来支持高并发
支持事务
支持外键
支持崩溃后的安全恢复
不支持全文索引
mvcc全称是multi version concurrent control(多版本并发控制)。mysql把每个=操作都定义成一个事务,每开启一个事务,系统的事务版本号自动递增。每行记录都有两个隐藏列:创建版本号和删除版本号。
二、表结构设计
1. 范式设计,消除冗余
数据库范式是确保数据库结构合理,满足各种查询需要、避免数据库操作异常的数据库设计方式。满足范式要求的表,称为规范化表,范式产生于20世纪70年代初,一般表设计满足前三范式就可以,在这里简单介绍一下前三范式
通俗的三范式解释:
第一范式:属性(字段)的原子性约束,要求属性具有原子性,不可再分割;
第二范式:记录的惟一性约束,要求记录有惟一标识,每条记录需要有一个属性来做为实体的唯一标识。
第三范式:属性(字段)冗余性的约束,即任何字段不能由其他字段派生出来,在通俗点就是:主键没有直接关系的数据列必须消除(消除的办法就是再创建一个表来存放他们,当然外键除外)
2.反范式设计,适当冗余
数据库设计的实用原则是:在数据冗余和处理速度之间找到合适的平衡点,满足范式的表一定是规范化的表,但不一定是最佳的设计。
很多情况下会为了提高数据库的运行效率,常常需要降低范式标 准:适当增加冗余,达到以空间换时间的目的。比如我们有一个表,产品名称,单价,库存量,总价值。这个表是不满足第三范式的,因为“总价值”可以由“单价”乘以“数量”得到,说明“金额”是冗余字段。但是,增加“总价值”这个冗余字段,可以提高查询统计的速度,这就是以空间换时间的作法。合理的冗余可以分散数据量大的表的并发压力,也可以加快特殊查询的速度,冗余字段可以有效减少数据库表的连接,提高效率。
三、主键
主键:根据第二范式,需要有一个字段去标识这条记录,主键无疑是最好的标识,需要满足唯一性、非空性,但是很多表也不一定需要主键,但是对于数据量大,查询频繁的数据库表,一定要有主键,主键可以增加效率、防止重复等优点。
四、选择数据类型
MySQL支持的数据类型非常多, 选择正确的数据类型对于获得高性能至关重要。
更小的通常更好:更小的数据类型通常更快, 因为它们占用更少的磁盘、 内存和CPU缓存, 并且处理时需要的CPU周期也更少。
简单就好:简单数据类型的操作通常需要更少的CPU周期。 例如, 整型比字符操作代价更低, 因为字符集和校对规则(排序规则 )使字符比较比整型比较更复杂。
尽量避免NULL:如果查询中包含可为NULL 的列, 对MySQL来说更难优化, 因为可为NULL 的列使得索引、 索引统计和值比较都更复杂。
数据类型尽量用数字型,数字型的比较比字符型的快很多。