mysql总结-基础
简介
MySQL是一种开放源代码的关系型数据库管理系统，MySQL数据库系统使用最常用的数据库管理语言：结构化查询语言进行数据库管理。
由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。
主键
主键是表中每一行数据的唯一标识
mysql没有强制要求每个表必须建立主键，但一般我们建立表时都会建立主键，以便对数据进行操作和管理
常用的主键生成策略有：自增，uuid，其他自生成策略
自增主键和uuid主键比较
自增在存储和读取速度比uuid快
自增：数字型，占用空间小，易排序，在程序中传递方便。uuid：占的空间大，不易排序
innodb 的非主键索引都将存一个主键，uuid 相比整数 id，索引大小增加很多；
uuid 主键比较肯定比 整数慢，另外非主键索引查找最终还要引用一次主键查找；
innodb 主键索引和数据存储位置相关（簇类索引），uuid 主键可能会引起数据位置频繁变动，严重影响性能。
非主键索引搜索需要检索两遍索引：首先检索非主键索引获得主键，然后用主键到主索引中检索获得记录。
（innodb 中的主键是聚簇索引，Innodb的底层存储是B+Tree，如果InnoDB表的数据写入顺序能和B+树索引的叶子节点顺序一致的话，这时候存取效率是最高的。为了存储和查询性能应该使用自增长id做主键。对于InnoDB的主索引，数据会按照主键进行排序，由于UUID的无序性，InnoDB会产生巨大的IO压力）
为什么自增主键比uuid主键存储速度快：
如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页
如果使用uuid，由于每次插入主键的值近似于随机，新纪录可能被插到现有索引的中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据
自增主键比uuid占用空间小
自增主键是数字型，bigint占用8个字节，uuid为16为字符串占用16+1=17个字节
创建主键的规则
mysql强制的：主键在本表中是唯一的，主键不能为null
其他好习惯：
- 主键应当是对用户没有意义的。
- 主键应该是单列的，以便提高连接和筛选操作的效率
- 不要对主键做更新操作
- 主键应当有计算机自动生成。
外键
对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。
外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。
Null值与空值
如果字段定义为not null如果没有设置默认值，那么插入时就必须指定值
空值就是空的字符串’’
空值('')的长度是0，是不占用空间的；而的NULL长度是NULL，其实它是占用空间的
对字段做count统计时，如果字段值为null值，那么它不会参与统计，为空值会参与统计
null值的判断是is null和is not null 空值是用=’’
MySQL注释
普通注释
MySQL服务器支持3种注释风格：
- 从‘#’字符从行尾。
- 从‘-- ’序列到行尾。请注意‘-- ’(双破折号)注释风格要求第2个破折号后面至少跟一个空格符(例如空格、tab、换行符等等)。该语法与标准SQL注释语法稍有不同。
- 从/*序列到后面的*/序列。结束序列不一定在同一行中，因此该语法允许注释跨越多行。
下面的例子显示了3种风格的注释：
mysql> SELECT 1+1; # This comment continues to the end of line
mysql> SELECT 1+1; -- This comment continues to the end of line
mysql> SELECT 1 /* this is an in-line comment */ + 1;
mysql> SELECT 1+
/*
this is a
multiple-line comment
*/
在navicat中的快捷键是ctrl+/ 进行--注释
条件注释
语法为
/*! condition */ SQL statement条件注释允许你在特定条件下执行或禁用SQL语句的一部分
! 后面的数字代表一个条件值，而该条件值可以用于根据特定的条件来启用或禁用注释块中的SQL代码。这对于在不同的MySQL版本或不同的SQL执行环境中有不同行为的SQL语句非常有用。
常见用法：
根据MySQL版本执行不同的SQL语句：
/*!50003 ALTER TABLE mytable ADD COLUMN newcolumn INT */;根据条件执行SQL语句：
/*! IF(condition) */ SQL statement;在这个示例中，condition 是一个条件表达式，如果条件成立，将执行 SQL statement。
禁用SQL语句：
/*!0 SQL statement */;/*!0 */ 意味着永远不会执行 SQL statement，即使 condition 始终为假。
数据类型
整型
|
MySQL数据类型 |
含义（有符号） |
|
tinyint(m) |
1个字节 范围(-128~127) |
|
smallint(m) |
2个字节 范围(-32768~32767) |
|
mediumint(m) |
3个字节 范围(-8388608~8388607) |
|
int(m) |
4个字节 范围(-2147483648~2147483647) |
|
bigint(m) |
8个字节 范围(+-9.22*10的18次方) |
数值类型都是定长的，也就是说，无论你存的数值是多少，多大或者多小，占用的字节大小都是固定的
int(1)和int(11)占用的是4个字节，tinyint(1)和tinyint(4)占用的是1个字节，int(1)插入128成功
取值范围如果加了unsigned，则最大值翻倍，如tinyint unsigned的取值范围为(0~256)。
int(m)里的m是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围，没有影响到显示的宽度。m的值默认是11，即使实际数值长度大于m也没有关系，当存储的字符长度小于M值时，只有在设置了zerofill用0来填充，才能够看到效果，换句话就是说，没有zerofill，M值就是无用的。
2、浮点型
(float和double)
|
MySQL数据类型 |
含义 |
|
float(m,d) |
单精度浮点型，4字节，m总个数，d小数位 |
|
double(m,d) |
双精度浮点型，8字节，m总个数，d小数位 |
最多可以存储M个数字，m个数字中最多有d个是小数位
设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，
float(9,2),插入的是1234567.22，结果存的是1234567.25
还有聚合计算的时候也会损失精度
但总个数还以实际为准，即6位。整数部分最大是3位，如果插入数12.123456，存储的是12.1234，如果插入12.12，存储的是12.1200.
float和double如果不指定精度时默认按照实际的硬件和操作系统决定来显示
定点类型
浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。 28个有效位，对最后一位四舍五入。
定点数以字符串形式存储
decimal(a,b)
参数说明
若插入的值未指定小数部分或者小数部分不足b位则会自动补到D位小数，若插入的值小数部分超过了b为则会发生截断，截取前b位小数(四舍五入截取)。a值得是整数部分加小数部分的总长度，也即插入的数字整数部分不能超过a-b位，否则不能成功插入，会报超出范围的错误。
Decimal型的字节数是a+2,也就是说，定点数的存储空间是根据其精度决定的。
语句DECIMAL (5,2)规定了存储的值将不会超过5位数字，开且小数点后面有2位数字。
decimal和numeric 同义，被 MySQL 以同样的类型实现，用于精确存储数值
浮点数和定点数，如果小数点后面的数大于指定的数最后一位就会四舍五入
INSERT into e (f,d,m)VALUES (7.228,7.228,7.228)
最后存入的都是7.23 7.23 7.23
浮点数和定点数比较
浮点数相对于定点数的优点是在长度一定的情况下，浮点数能够表示更大的数据范围；它的缺点是会引起精度问题。在今后关于浮点数和定点数的应用中，大家要记住以下几点：
- 浮点数存在误差问题；
2、对货币等对精度敏感的数据，应该用定点数表示或存储；
3、编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较；
4、要注意浮点数中一些特殊值的处理。
字符串
(char,varchar,_text)
|
MySQL数据类型 |
含义 |
|
char(n) |
固定长度，最多255个字符 |
|
varchar(n) |
可变长度，最多65535个字符 |
|
tinytext |
可变长度，最多255个字符 |
|
text |
可变长度，最多65535个字符 |
|
mediumtext |
可变长度，最多2的24次方-1个字符 |
|
longtext |
可变长度，最多2的32次方-1个字符 |
varchar
MySQL数据库的varchar类型在4.1以下的版本中的最大长度限制为255，其数据范围可以是0~255或1~255（根据不同版本数据库来定）。在 MySQL5.0以上的版本中，varchar数据类型的长度支持到了65535，也就是说可以存放65532个字符的数据
VARCHAR(M) M定义了可变长字符串的长度，M取值可以为0~65535之间，VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则 使用两个字节)。
5.0版本以上，varchar(20)，指的是20字符，无论存放的是数字、字母还是UTF8汉字（每个汉字3字节），都可以存放20个
char和varchar：
1.char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格，varchar不限于此。
2.char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字符（4*3=12字节），varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，因为是边长所以需要多出1到2个字节用于记录数据的长度
所以varchar(4),存入3个字符将占用3*3+1=10个字节。
注：utf8，一个字符都会占用3个字节
3.char类型的字符串检索速度要比varchar类型的快。
由于CHAR是固定长度的，所以它的处理速度比VARCHAR快得多，但是其缺点是浪费存储空间，程序需要对行尾空格进行处理，所以对于那些长度变化不大并且对查询速度有较高要求的数据可以考虑使用CHAR类型来存储。
另外，随着mysql版本的不断升级，VARCHAR数据类型的性能也在不断改进并提高，所以在许多的应用中，VARCAHR类型被更多地使用。
在mysql中，不同的存储引擎对CHAR和VARCHAR的使用原则有所不同，这里简单概括如下。
MyISAM存储引擎：建议使用固定长度的数据列代替可变长度的数据列。
MEMORY存储引擎：目前都使用固定长度的数据行存储，因此无论使用CHAR或VARCHAR列都没有关系。两者都是作为CHAR类型处理。
InnoDB存储引擎：建议使用VARCHAR类型。对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此在本质上，使用固定长度的CHAR列不一定比使用可变长度VARCHAR列性能要好。因而，主要的性能因素是数据行使用的存储总量。由于CHAR平均占用的空间多于VARCHAR，因此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I/O是比较好的。
varchar和text：
1.varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，text是实际字符数+2个字节。
2.text类型不能有默认值。
3.varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text,在都创建索引的情况下，text的索引似乎不起作用。
经常变化的字段用varchar
知道固定长度的用char
尽量用varchar
超过255字符的只能用varchar或者text
能用varchar的地方不用text
5.二进制数据(Blob)
1._BLOB和_text存储方式不同，_TEXT以文本方式存储，英文存储区分大小写，而_Blob是以二进制方式存储，不分大小写。
2.BLOB存储的数据只能整体读出。
3.TEXT可以指定字符集，BLOB不用指定字符集。
text和blob
①BLOB和TEXT值也会引起自己的一些问题，特别是执行了大量的删除或更新操作的时候。删除这种值会在数据表中留下很大的"空洞"，以后填入这些"空洞"的记录可能长度不同,为了提高性能,建议定期使用 OPTIMIZE TABLE 功能对这类表进行碎片整理.
②使用合成的（synthetic）索引。合成的索引列在某些时候是有用的。一种办法是根据其它的列的内容建立一个散列值，并把这个值存储在单独的数据列中。接下来你就可以通过检索散列值找到数据行了。但是，我们要注意这种技术只能用于精确匹配的查询（散列值对于类似<或>=等范围搜索操作符 是没有用处的）。我们可以使用MD5()函数生成散列值，也可以使用SHA1()或CRC32()，或者使用自己的应用程序逻辑来计算散列值。请记住数值型散列值可以很高效率地存储。同样，如果散列算法生成的字符串带有尾部空格，就不要把它们存储在CHAR或VARCHAR列中，它们会受到尾部空格去除的影响。
合成的散列索引对于那些BLOB或TEXT数据列特别有用。用散列标识符值查找的速度比搜索BLOB列本身的速度快很多。
③在不必要的时候避免检索大型的BLOB或TEXT值。例如，SELECT *查询就不是很好的想法，除非你能够确定作为约束条件的WHERE子句只会找到所需要的数据行。否则，你可能毫无目的地在网络上传输大量的值。这也是 BLOB或TEXT标识符信息存储在合成的索引列中对我们有所帮助的例子。你可以搜索索引列，决定那些需要的数据行，然后从合格的数据行中检索BLOB或 TEXT值。
④把BLOB或TEXT列分离到单独的表中。在某些环境中，如果把这些数据列移动到第二张数据表中，可以让你把原数据表中 的数据列转换为固定长度的数据行格式，那么它就是有意义的。这会减少主表中的碎片，使你得到固定长度数据行的性能优势。它还使你在主数据表上运行 SELECT *查询的时候不会通过网络传输大量的BLOB或TEXT值。
日期时间类型
|
MySQL数据类型 |
含义 |
|
date |
日期 '2008-12-2' |
|
time |
时间 '12:25:36' |
|
datetime |
日期时间 '2008-12-2 22:06:44' |
|
timestamp |
自动存储记录修改时间 |
若定义一个字段为timestamp，这个字段里的时间数据会随其他字段修改的时候自动刷新，所以这个数据类型的字段可以存放这条记录最后被修改的时间。
DATETIME、TIMESTAMP、DATE
DATETIME
显示格式：YYYY-MM-DD HH:MM:SS
时间范围:[ '1000-01-01 00:00:00'到'9999-12-31 23:59:59']
8字节
TIMESTAMP
显示格式：YYYY-MM-DD HH:MM:SS
时间范围:[ '1970-01-01 00:00:00'到'2037-12-31 23:59:59']
4字节
DATE
显示格式：YYYY-MM-DD
时间范围：['1000-01-01'到'9999-12-31']
3字节
TIME
“-838:59:59”到“838:59:59” 3字节
TIMESTAMP和DATETIME比较
两者的存储方式不一样
对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。而对于DATETIME，不做任何改变，基本上是原样输入和输出。
对于跨时区的业务，TIMESTAMP更为合适。
TIMESTAMP类型的字段在该条记录更新的时候如果不指定就会设置成更新时数据库所在机器的时间，DATETIME类型的字段不会
DATETIME：
允许为空值，可以自定义值，系统不会自动修改其值。
不可以设定默认值，所以在不允许为空值的情况下，必须手动指定datetime字段的值才可以成功插入数据。
数据类型的属性
|
MySQL关键字 |
含义 |
|
NULL |
数据列可包含NULL值 |
|
NOT NULL |
数据列不允许包含NULL值 |
|
DEFAULT |
默认值 |
|
PRIMARY KEY |
主键 |
|
AUTO_INCREMENT |
自动递增，适用于整数类型 |
|
UNSIGNED |
无符号 |
|
CHARACTER SET name |
指定一个字符集 |
数据类型的长度和范围
各数据类型及字节长度一览表：
|
数据类型 |
字节长度 |
范围或用法 |
|
Bit |
1 |
无符号[0,255]，有符号[-128,127]，天缘博客备注：BIT和BOOL布尔型都占用1字节 |
|
TinyInt |
1 |
整数[0,255] |
|
SmallInt |
2 |
无符号[0,65535]，有符号[-32768,32767] |
|
MediumInt |
3 |
无符号[0,2^24-1]，有符号[-2^23,2^23-1]] |
|
Int |
4 |
无符号[0,2^32-1]，有符号[-2^31,2^31-1] |
|
BigInt |
8 |
无符号[0,2^64-1]，有符号[-2^63 ,2^63 -1] |
|
Float(M,D) |
4 |
单精度浮点数。D是精度，如果D<=24则为默认的FLOAT，如果D>24则会自动被转换为DOUBLE型。 |
|
Double(M,D) |
8 |
双精度浮点。 |
|
Decimal(M,D) |
M+1或M+2 |
未打包的浮点数，用法类似于FLOAT和DOUBLE，如果在ASP中使用到Decimal数据类型，直接从数据库读出来的Decimal可能需要先转换成Float或Double类型后再进行运算。 |
|
Date |
3 |
以YYYY-MM-DD的格式显示，比如：2009-07-19 |
|
Date Time |
8 |
以YYYY-MM-DD HH:MM:SS的格式显示，比如：2009-07-19 11：22：30 |
|
TimeStamp |
4 |
以YYYY-MM-DD的格式显示，比如：2009-07-19 |
|
Time |
3 |
以HH:MM:SS的格式显示。比如：11：22：30 |
|
Year |
1 |
以YYYY的格式显示。比如：2009 |
|
Char(M) |
M |
定长字符串。 |
|
VarChar(M) |
M |
变长字符串，要求M<=255 |
|
Binary(M) |
M |
类似Char的二进制存储，特点是插入定长不足补0 |
|
VarBinary(M) |
M |
类似VarChar的变长二进制存储，特点是定长不补0 |
|
Tiny Text |
Max:255 |
大小写不敏感 |
|
Text |
Max:64K |
大小写不敏感 |
|
Medium Text |
Max:16M |
大小写不敏感 |
|
Long Text |
Max:4G |
大小写不敏感 |
|
TinyBlob |
Max:255 |
大小写敏感 |
|
Blob |
Max:64K |
大小写敏感 |
|
MediumBlob |
Max:16M |
大小写敏感 |
|
LongBlob |
Max:4G |
大小写敏感 |
|
Enum |
1或2 |
最大可达65535个不同的枚举值 |
|
Set |
可达8 |
最大可达64个不同的值 |
|
Geometry |
|
|
|
Point |
|
|
|
LineString |
|
|
|
Polygon |
|
|
|
MultiPoint |
|
|
|
MultiLineString |
|
|
|
MultiPolygon |
|
|
|
GeometryCollection |
|
|
mysql数据类型长度定义
MySQL有几种数据类型可以限制类型的"长度"，有CHAR(Length)、VARCHAR(Length)、TINYINT(Length)、SMALLINT(Length)、MEDIUMINT(Length)、INT(Length)、BIGINT(Length)、FLOAT(Length, Decimals)、DOUBLE(Length, Decimals)和DECIMAL(Length, Decimals)。
- CHAR、VARCAHR的长度是指字符的长度，例如CHAR[3]则只能放字符串"123"，如果插入数据"1234"，则从高位截取，变为"123"。 VARCAHR同理。
数据类型使用
- 在指定数据类型的时候一般是采用从小原则，比如能用TINY INT的最好就不用INT，能用FLOAT类型的就不用DOUBLE类型，这样会对MYSQL在运行效率上提高很大，尤其是大数据量测试条件下。
选择数据类型的基本原则
前提：使用适合存储引擎。
选择原则：根据选定的存储引擎，确定如何选择合适的数据类型。
下面的选择方法按存储引擎分类：
MyISAM 数据存储引擎和数据列：MyISAM数据表，最好使用固定长度(CHAR)的数据列代替可变长度(VARCHAR)的数据列。
MEMORY存储引擎和数据列：MEMORY数据表目前都使用固定长度的数据行存储，因此无论使用CHAR或VARCHAR列都没有关系。两者都是作为CHAR类型处理的。
InnoDB 存储引擎和数据列：建议使用 VARCHAR类型。
对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此在本质上，使用固定长度的CHAR列不一定比使用可变长度VARCHAR列简单。因而，主要的性能因素是数据行使用的存储总量。由于CHAR平均占用的空间多于VARCHAR，因 此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I/O是比较好的。
数据类型的长度
在严格模式下如果插入数据的长度大于了指定的长度，插入就会报错，如
char(3)、varchar(3)这里指定的长度是3,如果name或者sname插入的数据的长度大于了3就会报错Data too long for column 'name'
signed和unsigned
整型的每一种都分无符号（unsigned）和有符号（signed）两种类型（float和double总是带符号的），在默认情况下声明的整型变量都是有符号的类型（char有点特别）
如果需声明无符号类型的话就需要在类型前加上unsigned。无符号版本和有符号版本的区别就是无符号类型能保存2倍于有符号类型的正整数数据，比如16位系统中一个smallint能存储的数据的范围为-32768~32767，而unsigned能存储的数据范围则是0~65535。
另外，unsigned若省略后一个关键字，大多数编译器都会认为是unsigned int。
如果设置为负数就会报错Out of range value for column
mysql严格模式和非严格模式
mysql最近的版本默认是开启严格模式的
MySQL的严格模式，简单来说就是MySQL自身对数据进行严格的校验（格式、长度、类型等），比如一个整型字段我们写入一个字符串类型的数据，在非严格模式下MySQL不会报错，同样如果定义了char或varchar类型的字段，当写入或更新的数据超过了定义的长度也不会报错。
严格模式还有这些限制：
不支持对not null字段插入null值
不支持对自增长字段插入”值
不支持text字段有默认值
linux下mysql服务下操作步骤是：
1、进入mysql服务
2、执行set操作修改，我们项目是直接设置为''
mysql> set global sql_mode=''; #这种方法修改，当前会话失效后，这个修改就没意义了，建议修改配置文件的方式修改
3、修改后，不需要重启mysql服务，立即生效
4、检查是否生效，执行sql
select @@sql_mode;
通过配置文件修改：
linux找my.cnf文件
window的修改办法是找my.ini
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
mysql行格式
compact行格式（innodb默认行格式），结构如下
变长字段长度列表，null标志位，记录头信息，列1数据，列2数据。。。。
这个格式当初的设计是为了能高效存放数据，同时还有两个隐藏列：事物ID列和回滚指针列，如果没有定义主键的话，每行还会增加个rowid列作为隐藏主键，6字节
COMPRESSED 格式存储的行数据会进行zlib算法压缩，所以适合存储blob，text之类的大长度类型的数据
DYNAMIC 这个格式的解释参考手册的：
DYNAMIC 格式考虑的是如果一个较长的数据的一部分需要存储在溢出页上，那么通常最有效的方式就是将所有数据都存储在溢出页上。较短的列仍然会存放在Btree 节点上，可以减少对任何给定行所需的最少溢出页的数量。-----适合动态，比如varchar之类的动态长度字段
fixd行格式适合静态定长类型 如char
一般使用默认就行
mysql存储引擎
mysq的存储引擎是跟数据库表关联的，创建表的时候可以指定存储引擎
什么是存储引擎
存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型(即存储和操作此表的类型)。
可用SHOW ENGINES 查看MySQL使用的引擎
常用的存储引擎有那些
MyISAM
查询速度，但不支持事务，适合于多读取插入，少更新删除的操作表。
InnoDB
提供了具有提交、回滚和崩溃恢复能力的事务安全，对比MyISAM的存储引擎
你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，可靠性要求比较高，应该使用InnoDB表
InnoDB 存储引擎存储原理
InnoDB 存储引擎的逻辑存储结构从大到小依次可以分为：表空间、段、区、页、行。
表空间作为存储结构的最高层，所有数据都存放在表空间中，默认情况下用一个共享表空间 ibdata1 ，如果开启了 innodb_file_per_table 则每张表的数据将存储在单独的表空间中，也就是每张表都会有一个文件，
表空间由各个段构成，InnoDB存储引擎由索引组织的，而索引中的叶子节点用来记录数据，存储在数据段，而非叶子节点用来构建索引，存储在索引段，而回滚段我们在后面分析锁的时候在聊。
区是由连续的页组成，任何情况下一个区都是 1MB ，
一个区中可以有多个页，每个页默认为 16KB ，所以默认情况下一个区中可以包含64个连续的页，页的大小是可以通过 innodb_page_size 设置，页中存储的是具体的行记录。一行记录最终以二进制的方式存储在文件里，我们要能够解析出一行记录中每个列的值，存储的时候就需要有固定的格式，至少需要知道每个列占多少空间，而 MySQL 中定义了一些固定长度的数据类型，例如 int、tinyint、bigint、char数组、float、double、date、datetime、timestamp 等，这些字段我们只需要读取对应长度的字节，然后根据类型进行解析即可，对于变长字段，例如 varchar、varbinary 等，需要有一个位置来单独存储字段实际用到的长度，当然还需要头信息来存储元数据，例如记录类型，下一条记录的位置等。下面我们以 Compact 行格式分析一行数据在 InnoDB 中是怎么存储的。
变长字段长度列表，该位置用来存储所申明的变长字段中非空字段实际占有的长度列表，例如有3个非空字段，其中第一个字段长度为3，第二个字段为空，第三个字段长度为1，则将用 01 03 表示，为空字段将在下一个位置进行标记。变长字段长度不能超过 2 个字节，所以 varchar 的长度最大为 65535。
NULL 标志位，占 1 个字节，如果对应的列为空则在对应的位上置为 1 ，否则为 0 ，由于该标志位占一个字节，所以列的数量不能超过 255。如果某字段为空，在后面具体的列数据中将不会在记录。这种方式也导致了在处理索引字段为空的时候需要进行额外的操作。
记录头信息，固定占 5 字节，包含下一条记录的位置，该行记录总长度，记录类型，是否被删除，对应的 slot 信息等列数据 包含具体的列对应的值，加上两个隐藏列，事务 ID 列和回滚指针列。如果没有申明主键，还会增加一列记录内部 ID。
以《MySQL 技术内幕》第二版中的例子分析下一行记录在表空间具体的存储结构。
该表定义了 3 个变长字段和 1 个定长字段，然后插入两行记录，第二行记录包含空值，我们打开表空间 mytest.ibd 文件，转换为 16 进制，并定位到如下内容：
到此，我们了解了一个数据行是怎么存储的，然而数据行并不是存储引擎管理的最小存储单位，索引只能够帮助我们定位到某个数据页，每一次磁盘读写的最小单位为也是数据页，而一个数据页内存储了多个数据行，我们需要了解数据页的内部结构才能知道存储引擎怎么定位到某一个数据行。InnoDB 的数据页由以下 7 个部分组成：
文件头（File Header） 固定 38 个字节 （页的位置，上一页下一页位置，checksum , LSN）
数据页头（ Page Header）固定 56 个字节 包含slot数目，可重用空间起始地址，第一个记录地址，记录数，最大事务ID等
虚拟的最大最小记录 （Infimum + Supremum Record）
用户记录 （User Records） 包含已经删除的记录以链表的形式构成可重用空间
待分配空间 （Free spaces） 未分配的空间
页目录 （Page Directory） slot 信息，下面单独介绍
文件尾 （File Trailer） 固定8个字节,用来保证页的完整性
页目录里维护多个 slot ，一个 slot 包含多个行记录。每个 slot 占 2 个字节，记录这个 slot 里的行记录相对页初始位置的偏移量。由于索引只能定位到数据页，而定位到数据页内的行记录还需要在内存中进行二分查找，而这个二分查找就需要借助 slot 信息，先找到对应的 slot ，然后在 slot 内部通过数据行中记录头里的下一个记录地址进行遍历。每一个 slot 可以包含 4 到 8 个数据行。如果没有 slot 辅助，链表本身是无法进行二分查找的。
排序原理
我们需要先根据查询条件获取结果集，然后在内存中对这个结果集进行排序，如果结果集数量特别大，还需要将结果集写入到多个文件里，然后单独对每个文件里的数据进行排序，然后在文件之间进行归并，排序完成后在进行 limit 操作。没错，这个就是 MySQL 实现排序的方式，前提是排序的字段没有索引。
使用 explain 发现该语句会使用 city 索引，并且会有 filesort . 我们分析下该语句的执行流程
1.初始化 sortbuffer ，用来存放结果集
2.找到 city 索引，定位到 city 等于武汉的第一条记录，获取主键索引ID
3.根据 ID 去主键索引上找到对应记录，取出 city,name,age 字段放入 sortbuffer
4.在 city 索引取下一个 city 等于武汉的记录的主键ID
5.重复上面的步骤，直到所有 city 等于武汉的记录都放入 sortbuffer
6.对 sortbuffer 里的数据根据 name 做快速排序
- 根据排序结果取前面 1000 条返回
这里是查询 city,name,age 3个字段，比较少，如果查询的字段较多，则多个列如果都放入 sortbuffer 将占有大量内存空间，另一个方案是只区出待排序的字段和主键放入 sortbuffer 这里是 name 和 id ,排序完成后在根据 id 取出需要查询的字段返回，其实就是时间换取空间的做法，这里通过 max_length_for_sort_data 参数控制，是否采用后面的方案进行排序。
另外如果 sortbuffer 里的条数很多，同样会占有大量的内存空间，可以通过参数 sort_buffer_size 来控制是否需要借助文件进行排序，这里会把 sortbuffer 里的数据放入多个文件里，用归并排序的思路最终输出一个大的文件。
以上方案主要是 name 字段没有加上索引，如果 name 字段上有索引，由于索引在构建的时候已经是有序的了，所以就不需要进行额外的排序流程只需要在查询的时候查出指定的条数就可以了，这将大大提升查询速度。我们现在加一个 city 和 name 的联合索引。
alter table person add index city_user(city, name);
总结一下，我们在有排序操作的时候，最好能够让排序字段上建有索引，另外由于查询第一百万条开始的一百条记录，需要过滤掉前面一百万条记录，即使用到索引也很慢，所以可以根据 ID 来进行区分，分页遍历的时候每次缓存上一次查询结果最后一条记录的 id ， 下一次查询加上 id > xxxx limit 0,1000 这样可以避免前期扫描到的结果被过滤掉的情况。
innoDB存储模型
InnoDB 通过一些列后台线程将相关操作进行异步处理，如下图所示，同时借助缓冲池来减小 CPU 和磁盘速度上的差异。当查询的时候会先通过索引定位到对应的数据页，然后检测数据页是否在缓冲池内，如果在就直接返回，如果不在就去聚簇索引中通过磁盘 IO 读取对应的数据页并放入缓冲池。一个数据页会包含多个数据行。缓存池通过 LRU 算法对数据页进行管理，也就是最频繁使用的数据页排在列表前面，不经常使用的排在队尾，当缓冲池满了的时候会淘汰掉队尾的数据页。从磁盘新读取到的数据页并不会放在队列头部而是放在中间位置，这个中间位置可以通过参数进行修改。缓冲池也可以设置多个实例，数据页根据哈希算法决定放在哪个缓冲池。
InnoDB 在更新数据的时候会采用 WAL 技术，也就是 Write Ahead Logging ，这个日志就是 redolog 用来保证数据库宕机后可以通过该文件进行恢复。这个文件一般只会顺序写，只有在数据库启动的时候才会读取 redolog 文件看是否需要进行恢复。该文件记录了对某个数据页的物理操作，例如某个 sql 把某一行的某个列的值改为 10 ，对应的 redolog 文件格式可能为：把第5个数据页中偏移量为99的位置写入一个值 10 。redolog 不是无限大的，他的大小是可以配置的，并且是循环使用的，例如配置大小为 4G ，一共 4 个文件，每个文件 1G 。首先从第一个文件开始顺序写，写到第四个文件后在从第一个文件开始写，类似一个环，用一个后台线程把 redolog 里的数据同步到聚簇索引上的数据页上。写入 redolog 的时候不能将没有同步到数据页上的记录覆盖，如果碰到这种情况会停下来先进行数据页同步然后在继续写入 redolog 。另外执行更新操作的时候，会先更新缓冲池里的数据页，然后写入 redolog ， 这个时候真正存储数据的地方还没有更新，也就是说这时候缓冲池中的数据页和磁盘不一致，这种数据页称为脏页，当脏页由于内存不足或者其他原因需要丢弃的时候，一定要先将该脏页对应的redolog 刷新到磁盘里的真实数据页，不然下次查询的时候由于 redolog 没有同步到磁盘，而查询直接通过索引定位到数据页就会查询出脏数据。
更新的时候先从磁盘或者缓冲池中读取对应的数据页，然后对数据页里的数据进行更改并生成 redolog 到对应的缓冲池（redolog buffer）进行缓存，当事务提交的时候将缓存写入到 redolog 的物理磁盘文件上。这里由于操作系统的文件写入 InnoDB 并没有使用 O_DIRECT 直接写入到文件，为了保证性能而是先写入操作系统的缓存，之后在进行 flush ，所以事务提交的时候 InnoDB 需要在调用一次 fsync 的系统调用来确保数据落盘。为了提高性能 InnoDB 可以通过参数 innodb_flush_log_at_trx_commit 来控制事务提交时是否强制刷盘。默认为 1 ，事务每次提交都需要调用 fsync 进行刷盘，0 表示事务提交的时候不会调用 redolog 的文件写入，通过后台线程每秒同步一次，2 表示事务提交的时候会写入文件但是只保证写入操作系统缓存，不进行 fsync 操作。redolog 文件只会顺序写，所以磁盘操作性能不会太慢，所以建议生产环境都设置为 １ ，以防止数据库宕机导致数据丢失。
在执行更新逻辑的时候还会写入另外一个日志：undolog 。这个文件存储在共享表空间中，也就是即使打开了 innodb_file_per_table 参数，所有的表的 undolog 都存储在同一个文件里。该文件主要用来做事务回滚和 MVCC 。undolog 是逻辑日志，也就是他不是记录的将物理的数据页恢复到之前的状态，而是记录的和原 sql 相反的 sql , 例如 insert 对应 delete , delete 对应 insert ，update 对应另外一个 update 。事务回滚很好理解，执行相反的操作回滚到之前的状态，而 MVCC 是指镜像读，当一个事务需要查询某条记录，而该记录已经被其他事务修改，但该事务还没提交，而当前事务可以通过 undolog 计算到之前的值。这里我们只需要知道和 redolog 一样， undolog 也是需要在执行 update 语句的时候在事务提交前需要写入到文件的。另外 undolog 的写入也会有对应的 redolog ，因为 undolog 也需要持久化，通过 WAL 可以提高效率。这里可以总结下，在事务提交的时候要保证 redolog 写入到文件里，而这个 redolog 包含 主键索引上的数据页的修改，以及共享表空间的回滚段中 undolog 的插入。另外 undolog 的清理通过一个后台线程定时处理，清理的时候需要判断该 undolog 是否所有的事务都不会用到。
熟悉 MySQL 的都知道，他通过 binlog 来进行高可用，也就是通过 binlog 来将数据同步到集群内其他的 MySQL 实例。binlog 和 redolog 的区别是，他是在存储引擎上层 Server 层写入的，他记录的是逻辑操作，也就是对应的 sql ,而 redolog 记录的底层某个数据页的物理操作，redolog 是循环写的，而binlog 是追加写的，不会覆盖以前写的数据。而binlog 也需要在事务提交前写入文件。binlog 的写入页需要通过 fsync 来保证落盘，为了提高 tps ，MySQL 可以通过参数 sync_binlog 来控制是否需要同步刷盘，该策略会影响当主库宕机后备库数据可能并没有完全同步到主库数据。由于事务的原子性，需要保证事务提交的时候 redolog 和 binlog 都写入成功，所以 MySQL 执行层采用了两阶段提交来保证 redolog 和 binlog 都写入成功后才 commit，如果一方失败则会进行回滚。
下面我们理一下一条 update 语句的执行过程：
update person set age = 30 where id = 1;
1.分配事务 ID ，开启事务，获取锁，没有获取到锁则等待。
2.执行器先通过存储引擎找到 id = 1 的数据页，如果缓冲池有则直接取出，没有则去主键索引上取出对应的数据页放入缓冲池。
3.在数据页内找到 id = 1 这行记录，取出，将 age 改为 30 然后写入内存
4.生成 redolog undolog 到内存，redolog 状态为 prepare
5.将 redolog undolog 写入文件并调用 fsync
6.server 层生成 binlog 并写入文件调用 fsync
7.事务提交，将 redolog 的状态改为 commited 释放锁
浙公网安备 33010602011771号