存储引擎

什么是存储引擎

数据库存储引擎是数据库底层软件组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能

使用 SHOW ENGINES 可以查看当前MySQL支持哪些存储引擎

SET default_storage_engine=< 存储引擎名 > 可修改数据库的默认存储引擎，但MySQL重启之后会恢复，要永久生效需要修改配置文件中默认引擎设置

MySQL从5.5版本之后，默认内置存储引擎是InnoDB，之前是MylSAM

常见数据库引擎的功能对比

功能	MylSAM	MEMORY	InnoDB	Archive
存储限制	256TB	RAM	64TB	None
支持事务	No	No	Yes	No
支持全文索引	Yes	No	No	No
支持树索引	Yes	Yes	Yes	No
支持哈希索引	No	Yes	No	No
支持数据缓存	No	N/A	Yes	No
支持外键	No	No	Yes	No

常用存储引擎的特点

InnoDB存储引擎

InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键。

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

主要特点

（1）灾难恢复性比较好；
（2）支持事务。默认的事务隔离级别为可重复度，通过MVCC（并发版本控制）来实现的。
（3）使用的锁粒度为行级锁，可以支持更高的并发；
（4）支持外键；
（5）配合一些热备工具可以支持在线热备份；
（6）在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度；
（7）对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，都位于B+数的叶子节点上

MyISAM存储引擎

在5.5版本之前，MyISAM是MySQL的默认存储引擎，该存储引擎并发性差，不支持事务，所以使用场景比较少

如果数据表主要用来插入和查询记录，则MyISAM（但是不支持事务）引擎能提供较高的处理效率

主要特点

（1）不支持事务；
（2）不支持外键，如果强行增加外键，不会提示错误，只是外键不起作用；
（3）对数据的查询缓存只会缓存索引，不会像InnoDB一样缓存数据，而且是利用操作系统本身的缓存；
（4）默认的锁粒度为表级锁，所以并发度很差，加锁快，锁冲突较少，所以不太容易发生死锁；
（5）支持全文索引（MySQL5.6之后，InnoDB存储引擎也对全文索引做了支持），但是MySQL的全文索引基本不会使用，对于全文索引，现在有其他成熟的解决方案，比如：ElasticSearch，Solr，Sphinx等。
（6）数据库所在主机如果宕机，MyISAM的数据文件容易损坏，而且难恢复；

MEMORY存储引擎

将数据存在内存中，和市场上的Redis，memcached等思想类似，为了提高数据的访问速度，一般只有MySQL内部查询缓存时使用。

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

主要特点

（1）支持的数据类型有限制，比如：不支持TEXT和BLOB类型，对于字符串类型的数据，只支持固定长度的行，VARCHAR会被自动存储为CHAR类型；
（2）支持的锁粒度为表级锁。所以，在访问量比较大时，表级锁会成为MEMORY存储引擎的瓶颈；
（3）由于数据是存放在内存中，所以在服务器重启之后，所有数据都会丢失；
（4）查询的时候，如果有用到临时表，而且临时表中有BLOB，TEXT类型的字段，那么这个临时表就会转化为MyISAM类型的表，性能会急剧降低

ARCHIVE存储引擎

ARCHIVE存储引擎适合的场景有限，由于其支持压缩，故主要是用来做日志，流水等数据的归档

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

主要特点

（1）支持Zlib压缩，数据在插入表之前，会先被压缩；
（2）仅支持SELECT和INSERT操作，存入的数据就只能查询，不能做修改和删除；
（3）只支持自增键上的索引，不支持其他索引

CSV存储引擎

数据中转使用

主要特点

（1）其数据格式为.csv格式的文本，可以直接编辑保存；
（2）导入导出比较方便，可以将某个表中的数据直接导出为csv，试用Excel办公软件打开

InnoDB和MyISAM的对比

由于锁粒度的不同，InnoDB比MyISAM支持更高的并发；
InnoDB为行级锁，MyISAM为表级锁，所以InnoDB相对于MyISAM来说，更容易发生死锁，锁冲突的概率更大，而且上锁的开销也更大，因为需要为每一行加锁；
在备份容灾上，InnoDB支持在线热备，有很成熟的在线热备解决方案；
查询性能上，MyISAM的查询效率高于InnoDB，因为InnoDB在查询过程中，是需要维护数据缓存，而且查询过程是先定位到行所在的数据块，然后在从数据块中定位到要查找的行；而MyISAM可以直接定位到数据所在的内存地址，可以直接找到数据；
SELECT COUNT(*)语句，如果行数在千万级别以上，MyISAM可以快速查出，而InnoDB查询的特别慢，因为MyISAM将行数单独存储了，而InnoDB需要朱行去统计行数；所以如果使用InnoDB，而且需要查询行数，则需要对行数进行特殊处理，如：离线查询并缓存；
MyISAM的表结构文件包括：.frm(表结构定义),.MYI(索引),.MYD(数据)；而InnoDB的表数据文件为:.ibd和.frm(表结构定义)；

如何选择合适的存储引擎

使用场景是否需要事务支持；
是否需要支持高并发，InnoDB的并发度远高于MyISAM；
是否需要支持外键；
是否需要支持在线热备；
高效缓冲数据，InnoDB对数据和索引都做了缓冲，而MyISAM只缓冲了索引；
索引，不同存储引擎的索引并不太一样

数据类型

常用的整数类型

类型	存储空间	属性	取值范围
tinyint	1字节	SIGNED	-128~127（-2^7 ~ 2^7-1）
	1字节	UNSIGNED	0~255（0 ~ 2^8-1）
smallint	2字节	SIGNED	-32768~32767（-2^15 ~ 2^15-1）
	2字节	UNSIGNED	0~65535（0 ~ 2^16-1）
mediumint	3字节	SIGNED	-8388608~8388607（-2^23 ~ 2^23-1）
	3字节	UNSIGNED	0~16777215（0 ~ 2^24-1）
int	4字节	SIGNED	-2147483648~2147483647（-2^31 ~ 2^31-1）
	4字节	UNSIGNED	0~4294968295（0 ~ 2^32-1）
bigint	8字节	SIGNED	-9223372036854775808~9223382036854775807（-2^63 ~ 2^63-1）
	8字节	UNSIGNED	0~18446744073709551615（0 ~ 2^64-1）

常用的小数类型

类型	存储空间	精确类型	说明
FLOAT(M,N)	4个字节	否	单精度浮点型，精度范围大概为7位左右
DOUBLE(M,N)	8个字节	否	双精度浮点型，精确范围大概为15位左右
DECIMAL(M,N)	每4个字节存9个数字，小数点占一个字节	是	定点型

M：代表总长度 D：代表小数部分长度

整数部分的长度 = M - D

小数型：带有小数点或者范围超出整型的数值类型。包括浮点型和定点型

浮点型：小数点浮动，精度有限，而且会丢失精度
定点型：小数点固定，精度固定，不会丢失精度

浮点型

单/双精度的解释

float单精度存储浮点类型的话就是 ==4x8=32位的长度== , 所以float单精度浮点数在内存中占 4 个字节，并且用 32 位二进制进行描述

double双精度存储浮点类型就是 ==8x8 =64位的长度==, 所以double双精度浮点数在内存中占 8 个字节，并且用 64 位二进制进行描述通过计算、那么64位就可以获得更多的尾数。尾数 : ==就是小数点后的有多少个数位==

*结论 *

float单精度小数部分只能精确到后面6位，加上小数点前的一位，即有效数字为7位
double双精度小数部分能精确到小数点后的15位，加上小数点前的一位有效位数为16位。
最后就区别出了小数点后边位数的长度，越长越精确!

double 和 float 的区别:

在内存中占有的字节数不同, 单精度内存占4个字节, 双精度内存占8个字节
有效数字位数不同(尾数) 单精度小数点后有效位数7位, 双精度小数点后有效位数16位
数值取值范围不同根据IEEE标准来计算!
在程序中处理速度不同,一般来说，CPU处理单精度浮点数的速度比处理双精度浮点数快

double 和 float 的优缺点

类型	优点	缺点
float	float单精度在一些处理器上比double双精度更快而且只占用double双精度一半的空间	当值很大或很小的时候，它将变得不精确
double	double 跟 float比较, 必然是 double 精度高，尾数可以有 16 位,而 float 尾数精度只有 7 位	double 双精度是消耗内存的,并且是 float 单精度的两倍! double 的运算速度比 float 慢得多, 因为double 尾数比float 的尾数多, 所以计算起来必然是有开销的

如何选择double 和 float 的使用场景!

首先: 能用单精度时不要用双精度以省内存，加快运算速度!

float: 当你需要小数部分并且对精度的要求不高时，选择float单精度浮点型比较好!
double: 因为小数位精度高的缘故，所以双精度用来进行高速数学计算、科学计算、卫星定位计算等处理器上双精度型实际上比单精度的快, 所以: 当你需要保持多次反复迭代的计算精确性时，或在操作值很大的数字时，双精度型是最好的选择

定点型

在存储同样范围的值时，定点型通常比浮点型使用更少的空间，float使用4个字节存储，double使用8个字节 ,而 decimal依赖于M和D的值,所以decimal使用更少的空间。在实际的企业级开发中，经常遇到需要存储金额（3888.00元）的字段，这时候就需要用到数据类型decimal。

decimal的使用语法是：decimal(M,D)，其中，M 的范围是165，D 的范围是030，而且D不能大于M

常用的时间类型

类型	存储空间	格式	范围
DATE	3字节	YYYY-MM-DD	‘1000-01-01’ 到 ‘9999-23-31’
TIME	3~6字节	HH:MM:SS[.微秒值]	‘-838:59:59’ 到 ‘838:59:59’ （可以表示累加的时间，不只是一天的24小时）
YEAR	1字节	YYYY	1901 到 2155
DATETIME	5-8字节	YYYY-MM-DD HH:MM:SS[.微秒值]	1000-01-01 00:00:00 到 9999-12-31 23:59:59
TIMESTAMP	4-7字节	YYYY-MM-DD HH:MM:SS[.微秒值]	1970-01-01 00:00:00 UTC 到 2038-01-19 03:14:07 UTC

表中有记录的创建时间和修改时间字段的话如下定义可实现自动维护相关事件，不用在程序中维护

1 2	`create_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `update_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

==注意：==

在5.5到5.6.4版本里，对于DEFAULT CURRENT_TIMESTAMP 子句，只能TIMESTAMP类型列上指定。

而从5.6.5开始以后的版本，对于DEFAULT CURRENT_TIMESTAMP子句可以指定到TIMESTAMP或者DATETIME类型列上。

常用的字符串类型

类型	范围	说明
Char(M)	M=1~255个字符	固定长度
Varchar(M)	一行中所有varchar类型的列所占用的字节数不能超过65535个字节。	存储可变长度的M个字符
TinyText	最大长度255个字节	可变长度
Text	最大长度65535个字节	可变长度
MediumText	最大长度16777215个字节	可变长度
LongText	最大长度4294967295个字节	可变长度
Enum	集合最大数目为65535	只能插入列表中的值