Mysql学习笔记-1

一、Mysql体系结构和存储引擎

1. 数据库与实例

• 数据库：操作系统上的文件或其他形式的文件的集合；
• 实例：一个操作数据库文件的后台进程；

MySQL中实例和数据库是一一对应的关系，但在集群模式下可能是一个数据库被多个实例使用。
_

1.1 数据库对比

Mysql/SQL server: 单进程而多线程；

Oracle：多进程；
_

1.2 配置

配置文件：/ect/my.cbf

datadir: 指定数据库所在的路径，但只是一个链接，指向 /opt/mysql_data 目录

-

2. MySQL体系结构

• 连接池组件
• 管理服务和工具组件
• SQL接口组件
• 查询分析器组件
• 优化器组件
• 缓存组件
• 插件式存储引擎组件
• 物理文件

存储引擎：保证底层物理结构的实现，是基于表的，而不是数据库的，所以一个数据库中可以有不同物理结构的表。我们可以根据不同的应用简历不同存储引擎的表。用户可以根据MySQL的存储引擎接口开发自己的存储引擎。

-

2.1 InnoDB

支持事务

支持行级锁定

支持外键

支持非锁定读

既缓冲数据也缓冲索引

表和索引在一个表空间中

具备良好的多并发性

提供一些高性能和高可用的功能

-

2.2 InnoDB索引实现

• 主键索引：B+Tree的key是表的主键；
• 聚集索引：叶子节点包含了完整的数据记录，而不仅仅是指向数据记录的指针；
• 隐含主键：在没有显示指定主键时自动生成，6个字节长整型；
• 辅助索引(非主键索引)：使用主键索引当叶子节点的数据域；所以辅助索引需要检索两遍才能找到对应的记录；不建议使用过长的字段作为主键，这样不仅主键索引过大，而且所有辅助索引也会相应过大；

InnoDB使用B+Tree作为索引结构，需要注意的是，B+树索引并不找到给定键值的具体记录行，只能找到记录行所在的页，然后将该页读入内存进行查找，最后找到数据。

1）主键索引：

MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

(图inndb主键索引）是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

2）InnoDB的辅助索引

InnoDB的所有辅助索引都引用主键作为data域。例如，下图为定义在Col3上的一个辅助索引：

InnoDB 表是基于聚簇索引建立的。因此InnoDB 的索引能提供一种非常快速的主键查找性能。不过，它的辅助索引（Secondary Index， 也就是非主键索引）也会包含主键列，所以，如果主键定义的比较大，其他索引也将很大。如果想在表上定义 、很多索引，则争取尽量把主键定义得小一些。InnoDB 不会压缩索引。

文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

尽量使用业务无关的自增列作为主键，主要原因：

（1）InnoDB数据是按照主键聚簇的，数据在物理上按照主键大小顺序存储，自增的主键保证新增的数据在插入时对B+Tree影响最小。相反，随机IO导致插入性能下降。

（2）所有二级索引都存储了主键的，采用二级索引查询，首先找到的主键，然后通过主键定位数据，如果直接使用组合字段作为主键，会导致二级索引占用空间较大，bufferpool中存储的记录数较少，影响性能，而自增列只占4或者8个字节，代价非常小。

InnoDB索引和MyISAM索引的区别：
一是主索引的区别：InnoDB的数据文件本身就是索引文件。而MyISAM的索引和数据是分开的。
二是辅助索引的区别：InnoDB的辅助索引data域存储相应记录主键的值而不是地址。而MyISAM的辅助索引和主索引没有多大区别。

-

2.3 MyISAM

不支持事务、行级锁定、外键、非锁定读

支持全文搜索

主要面向一些OLAP（联机分析处理On-Line Analytical Processing）数据库

只缓冲索引，不缓冲数据

每个表被存在分离的文件中

-

2.4 MyISAM索引实现

非聚集索引：索引和数据是分开的；
辅助索引：和主键索引没有什么区别，只是B+Tree的Key可以是重复的；

1）主键索引：

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM主键索引的原理图：

这里设表一共有三列，假设我们以Col1为主键，图myisam1是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。

-

2）辅助索引（Secondary key）
在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

2.5 全文检索

对于B+树索引，通过索引字段的前缀（xxx%）进行查找是效率很高的，但是在实际应用中，我们往往需要查找的是一个文本中是否包含关键字（%xxx%），比如博文、存在数据库的一本书等。这种查找就不是B+树所擅长的了。全文检索（Full Text Index）就是将存在数据库中整本书或整篇文章中的任意信息查找出来的技术，而全文检索一般是通过倒排索引（inverted index）实现的。

---

二、InnoDB

1. 内存缓冲

1.1 缓冲池

基于磁盘的存储引擎，按页进行管理，利用缓冲池提供性能。

读取页的时候，如果在缓存池中没有命中则读取磁盘上的页。

允许多个缓存池实例，每个页根据哈希值分配到不同的缓存池。

缓存的页的类型：

（1）数据页

（2）索引页

（3）插入缓冲：插入缓冲，并不是缓存的一部分，而是物理页，对于非聚集索引的插入或更新操作，不是每一次直接插入索引页。而是先判断插入的非聚集索引页是否在缓冲池中。如果在，则直接插入，如果不再，则先放入一个插入缓冲区中。然后再以一定的频率执行插入缓冲和非聚集索引页子节点的合并操作。

（4）锁信息

（5）自适应哈希：InnoDB存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以带来速度提升，则建立哈希索引，称之为自适应哈希索引(Adaptive Hash Index， AHI)。AHI是通过缓冲池的B+树页构造而来，因此建立的速度很快，而且不需要对整张表构建哈希索引。InnoDB存储引擎会自动根据访问的频率和模式来自动地为某些热点页建立哈希索引。

（6）数据字典信息：InnoDB数据字典由内部系统表组成，它包含了用于维持对象（比如表、索引和表列）记录的元数据。元数据的物理位置位于InnoDB系统表空间。由于历史原因，数据字典元数据和存储在InnoDB表元数据文件中的信息有一定程度的重叠。

有几个关键的用于管理页的数据结构：

• LRU链表： 包含所有读入内存的数据页；
• Flush_list： 包含被修改过的脏页；
• unzip_LRU： 包含所有解压页；
• Free list： 上存放当前空闲的block。

-

LRU链表

LRU最近最少使用算法：

（1）新数据插入到链表头部。

（2）每当缓存命中（即缓存数据被访问），则将数据移到链表头部。

（3）当链表满的时候，将链表尾部的数据丢弃。

【缺点】

但偶发性的、周期性的批量操作会导致LRU命中率急剧下降，因为SELECT到的数据都会放到链表的头部，会把真正的热点数据挤出缓存池。

【InnoDB的改进】

**新读取的页插入到距离末尾37%的位置，而不是头部。**37%的位置被指为midpoint，midpoint之前的是热点页，

-

Flush链表

脏页：被修改了，与磁盘上的数据不一致的页。

Flush链表记录了所有脏页，

1.2 重做日志缓冲

redo log buffer

InnoDB会先将重做日志信息存在这个缓冲区，再按照一定的频率和规则刷新到重做日志：

• Mater Thread每秒执行一次刷新；
• 每个事务提交的时候刷新一次；
• 当重做日志缓冲区剩余空间小于一半时，刷新；

redo log的作用是记录事务日志。万一实例或介质失败，可以通过redo log进行恢复，保证数据的完整性。比如提交事务时，先写redo日志，再修改页，也就是Write Ahead Log策略。

每个InnoDB存储引擎至少有1个redo log文件组，每个文件组下至少有2个redo log文件。为了得到更高的可靠性，可以设置多个mirrored log group，将不同的文件组放在不同的磁盘上。日志组中每个redo log文件的大小一致，并以循环方式使用。InnoDB存储引擎先写redo log文件1，当达到文件的最后是，会切换至文件2，当文件2也被写满时，会再切换到文件1中。

参数innodb_log_file_size指定了redo log文件的大小；innodb_log_files_in_group指定了redo log文件组中redo log文件的数量，默认为2；innodb_mirrored_log_groups指定了日志镜像文件组的数量，默认为1，代表只有一个文件组，没有镜像；innodb_log_group_home_dir指定了日志文件组所在路径，默认在数据库路径下。

redo log文件的大小不能设置得太大，如果设置得太大，在恢复时可能需要很多的时间，另一方面又不能太小，否则可能导致一个事务的日志需要多次切换redo log文件。

1.3 额外的内存池

一些管理用的数据结构从这里申请内存，比如LRU、锁、等待等信息。所以当缓冲池比较大，数据量比较大时，也应该相应地增加额外的内存池。

---

2. Checkpoint技术

按照一定的策略将内存的数据（脏页、重做日志缓冲）刷新回磁盘，故数据库发生故障后只用对checkpoint之后的重做日志进行重做恢复。

（1）Master Thread Checkpoint：每秒或每十秒从Flush_list中刷新一部分比例的脏页到磁盘；异步，非阻塞。

（2）Flush LRU List Checkpoint：InnoDB要保证LRU列表中有差不都100个空闲页，否则会对LRU列表末端的页进行移除操作，如果其中有脏页，那么就需要Checkpoint。

（3）Async/Sync Flush Checkpoint：保证重做日志的循环使用的后台线程，刷新一部分脏页到磁盘，从而重做日志的部分空间就可以被覆盖了。

（4）Dirty Page Too Much Checkpoint：当缓冲池中的脏页比例高于75%时触发。

---

3. InnoDB关键特性

3.1 插入缓冲

Insert Buffer

由于InnoDB使用了B+树，一部分记录和索引在内存当中，大部分在磁盘当中，所以不论是从B+Tree的插入性能考虑，还是从磁盘IO来讲，递增地顺序插入的效率要比随机读写高得多。

但是一张表中出了往往还有辅助索引，这些索引往往是非单调而且不唯一的，但是辅助索引在磁盘上的存储顺序依然是经过排序的。所以，插入一条记录如果涉及到辅助索引的更新，就会有性能比较低的随机读写和磁盘IO。

优化的方法是，对于辅助索引的更新，先判断索引页是否在内存当中，如果存在，则直接插入；如果不存在，则放入插入缓冲；InnoDB会按照一定的频率对这些缓冲的辅助索引的插入操作进行合并，也就是同在一个索引页的操作作为一批一起执行，这样就减少了磁盘IO的开销。

Change Buffer

可以理解为Insert Buffer的升级，对于Insert、Delete、Update都使用类似的缓冲机制。

3.2 两次写

为什么要用doublewrite？
目的是为了保证出现部分写失效（partial page write）–即数据页写到一半时就出现故障时的数据安全性。Innodb并不在日志中记录整个数据页，而是使用一种称之为“生理”日志的技术，即日志项中只包含页号、对数据进行的操作（如更新一行记录）和日志***等信息。这一日志结构的优点是能够减少写入到日志的数据量，但这也要求要保持页内部的一致性。页的版本号是没关系的，数据页可以是当前版本（这时Innodb（故障恢复时）会跳过对页的更新操作）若是早期版本（这时Innodb将执行更新操作）。但如果页内部数据不一致，恢复将不能进行。

部分写失效
什么是部分写失效，为什么会出现这一现象呢？部分写失效指的是提交给操作系统的写数据页操作只完成了一部分。例如一个16K的Innodb数据页中只有4K被更新了，其余部分数据还是旧的。大部分的部分写失效都在断电时产生，在操作系统崩溃时也可能出现，这是由于操作系统可能将一个写16K数据的操作分割成多个写操作（这可能由文件碎片导致），而在执行这多个写操作的过程中发出的故障。当使用软件RAID技术时，数据页可能恰好跨越分片（stripe）的边界，这时也需要执行多个IO操作，因此也可能导致部分写失效。当使用硬件RAID又没有使用带电池的缓存时，断电时出现部分写失效也是可能的。当发送到磁盘本身只有一个写操作时，理论上硬件可以保证写操作即使在断电时也可以完成，因为驱动器内部应该积累有足够的电量来完成这一操作。但实话说我并不知道事实是不是这样，这很难检测，而且这也不是部分写失效的唯一原因。我所知道的只是部分写失效可能会出现，当Innodb实现 doublewrite功能前，我遇到过很多由于这一原因而导致数据被破坏。

doublewrite如何工作？

准确来说，doublewrite涉及一次内存中的复制，两次磁盘写操作：

（1）将脏页复制到内存中doublewrite Buffer；

（2）每次1M地将脏页顺序地写入到物理磁盘上的共享表空间，然后调用fsync同步磁盘；

（3）将doublewrite buffer中的数据写入到各个表空间文件中去，这时的写是离散的；

故障恢复时Innodb检查共享表空间与数据页原存储位置的内容，

doublewrite缓冲区对MySQL有何影响？
虽然doublewrite要求每个数据页都要被写二次，其性能开销远远小于二倍。

（1）写出到共享表空间时是顺序写，因此开销很小。

（2）doublewrite同时还可以降低Innodb执行的fsync()操作，即不需要写每个页时都调用一下fsync()，而可以提交多个写操作最后再调用一次fsync()操作，这使得操作系统可以优化写操作的执行顺序及并行使用多个存储设备。但在不使用doublewrite技术时也可以用这些优化，事实上这些优化是与doublewrite同时实现的。因此总体来说，我预计使用doublewrite技术带来的性能开销不会超过5%到10%。

3.3 自适应哈希

AHI：Adaptive Hash Index

首先，如果数据项都在内存当中，而且链表并不长，则从一个很短的链表中查找一个数据项的耗时是非常小的，甚至可以忽略不计。因此，我们常常说哈希是一种非常快的查找方法，时间复杂度是O(1)。而B+树的查找次数或者说磁盘IO次数取决于B+树的高度。在生产环境中，B+树的高度往往在3~4层。

（1）AHI只能用来搜索等值的查询，比如 WHERE a=xxx AND b=yyy；

（2）以相同的模式连续访问了100次，则InnoDB自动建立一条AHI，无需DBA干预；

3.4 异步IO

AIO：Asynchronous IO，一种消息模型，也叫非阻塞I/O

同步IO：在一个线程中，CPU执行代码的速度极快，然而，一旦遇到IO操作，如读写文件、发送网络数据时，就需要等待IO操作完成，才能继续进行下一步操作。

CPU高速执行能力和IO设备的龟速严重不匹配，多线程和多进程是解决这一问题的一种方法。比如如果遇到IO导致线程被挂起，其他用户的线程不受影响。但是系统不能无上限地增加线程。由于系统切换线程的开销也很大，所以，一旦线程数量过多，CPU的时间就花在线程切换上了，真正运行代码的时间就少了，结果导致性能严重下降。

另一种解决IO问题的方法是异步IO，即在一个线程中异步地处理多个IO事件。当代码需要执行一个耗时的IO操作时，它只发出IO指令，并不等待IO结果，然后就去执行其他代码了。一段时间后，当IO返回结果时，再通知CPU进行处理。可以想象如果按普通顺序写出的代码实际上是没法完成异步IO的：

do_some_code()
f = open('/path/to/file'， 'r')
r = f.read() # <== 线程停在此处等待IO操作结果
# IO操作完成后线程才能继续执行:
do_some_code(r)

异步IO模型需要一个消息循环，在消息循环中，主线程不断地重复“读取消息-处理消息”这一过程：

loop = get_event_loop()
while True:
    event = loop.get_event()
    process_event(event)

当遇到IO操作时，代码只负责发出IO请求，不等待IO结果，然后直接结束本轮消息处理，进入下一轮消息处理过程。当IO操作完成后，将收到一条“IO完成”的消息，处理该消息时就可以直接获取IO操作结果。

消息模型其实早在应用在桌面应用程序中了。一个GUI程序的主线程就负责不停地读取消息并处理消息。所有的键盘、鼠标等消息都被发送到GUI程序的消息队列中，然后由GUI程序的主线程处理。由于GUI线程处理键盘、鼠标等消息的速度非常快，所以用户感觉不到延迟。某些时候，GUI线程在一个消息处理的过程中遇到问题导致一次消息处理时间过长，此时，用户会感觉到整个GUI程序停止响应了，敲键盘、点鼠标都没有反应。这种情况说明在消息模型中，处理一个消息必须非常迅速，否则，主线程将无法及时处理消息队列中的其他消息，导致程序看上去停止响应。

3.5 刷新邻接页

Flush Neighbor Page

当刷新一个脏页的时，InnoDB会检查该页所在区中是否有其他脏页，如果有则一起刷新。

这个特性对于机械硬盘有提升效率的作用，如果是SSD硬盘，由于有着超高的IOPS，建议不开启这个特性。

---

三、锁

• MyISAM引擎：表锁；
• SQL Server：页锁；
• InnoDB：非锁定读，行级锁支持；

乐观锁

大多是基于数据版本（ Version ）记录机制实现。即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

悲观锁

正如其名，具有强烈的独占和排他特性，在整个数据处理过程中，将数据处于锁定状态。

自旋锁

一个执行单元要想访问被自旋锁保护的共享资源，必须先得到锁，在访问完共享资源后，必须释放锁。如果在获取自旋锁时，没有任何执行单元保持该锁，那么将立即得到锁；如果在获取自旋锁时锁已经有保持者，那么获取锁操作将自旋在那里，直到该自旋锁的保持者释放了锁。

自旋锁是一种比较低级的保护数据结构或代码片段的原始方式。自旋锁可能会过多占用cpu资源，如果不加限制，由于申请者一直在循环等待，因此自旋锁在锁定的时候，如果不成功，不会睡眠，会持续的尝试，单cpu的时候自旋锁会让其它process动不了。因此，一般自旋锁实现会有一个参数限定最多持续尝试次数。 超出后，自旋锁放弃当前time slice， 等下一次机会。

1. Lock与Latch的差别


2. InnoDB中的锁

行锁：

• 共享锁/读锁（S）：获取后可以读一行数据；
• 排他锁/写锁（X）：获取后可以删除或者修改一行数据；

表锁：也有读锁（TS）和写锁（TX）

表锁和行锁可能有冲突。比如事务A申请了表中某一行的读锁，事务B此时想要申请整张表的写锁，如果事务B申请成功，则表示它有权改写表中任何一行的数据，所以TX锁与S锁冲突了。所以事务B申请整张表的写锁时，数据库要做如下检查：

（1）这张表上有没有TS或者TX锁；

（2）如果没有，还要检查每一个记录行上有没有S或者X锁；

这样做显然非常低效，所以InnoDB创造了**意向锁**IS和IX。接着上面的例子，事务A成功申请了表中某一行的读锁时，数据库自动给这个表加上一个意向读锁IS，表示这个表中的某一行或者几行已经有了读锁，其他事务如果申请整张表的写锁则都需要等待。同理，如果一张表上有IX锁，则表用其中一行或几行有X锁，其他事务申请整张表的TS或者TX都得等待。

---

一致性非锁定读

如果不考虑事务的隔离性，往往会出现以下4种问题：

（1）脏读：脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。

（2）不可重复读：不可重复读是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值。

（3）幻读：事务在操作过程中进行两次查询，第二次查询的结果包含了第一次查询中未出现的数据或者缺少了第一次查询中出现的数据（这里并不要求两次查询的SQL语句相同）。这是因为在两次查询过程中有另外一个事务插入数据造成的。

为了解决这些问题，InnoDB为我们提供的四种隔离级别：

① Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。

② Repeatable read (可重复读)：可避免脏读、不可重复读的发生。

③ Read committed (读已提交)：可避免脏读的发生。

④ Read uncommitted (读未提交)：最低级别，任何情况都无法保证。

InnoDB的默认事务隔离级别是Repeatable Read。

一致性非锁定读（consistent non-locking read ）是指InnoDB通过行多版本控制（multi versioning）的方式来读取正在执行Update或者Delete操作的记录行的手段。也就是不会去等待行上X锁的释放，而是读取该行的一个历史版本。其中的历史版本，也称之为快照，是通过Undo段来完成的，由发起的事务自己创建一个快照。但是，在不同的事务隔离级别上有不同的读取快照的策略。

• 在REPEATABLE READ事务隔离级别下，一致性读的效果是同一事务内的一致性读均会读取该事务中第一个读创建的快照，其他事务在之后提交或未提交的更新对当前事务的读均不可见。
• 而在READ COMMIT隔离级别下，事务内的每个一致性读均会设置和读取自己新鲜的快照。其他事务在之后提交的更新对当前事务的读可见，未提交的更新对当前事务不可见。

---

一致性锁定读

在默认的Repeatable Read级别下，InnoDB执行SELECT操作会使用一致性非锁定读，但是在有的场景下，用户需要显示地对SELECT操作加锁以保证数据逻辑的一致性，InnoDB支持两种锁定读操作。

（1）SELECT…FOR UPDATE

会对读取的记录加X锁，阻塞其他事务的读请求和修改请求，直至事务提交释放锁资源。

select * from t for update --会等待行锁释放之后，返回查询结果。
select * from t for update nowait --不等待行锁释放，提示锁冲突，不返回结果
select * from t for update wait 5 --等待5秒，若行锁仍未释放，则提示锁冲突，不返回结果
select * from t for update skip locked --查询返回查询结果，但忽略有行锁的记录

（2）SELECT…LOCK IN SHARE MODE

对读取的记录加S锁，阻塞其他事务的修改请求但不会阻塞读取请求，直至事务提交释放锁资源。

也正因为SELECT…FOR UPDATE和SELECT…LOCK IN SHARE MODE分别需要对查询的记录加X锁和S锁，因此分别会被其他正在读写和写的事务阻塞，直到这些事务结束。需要注意的是SELECT…FOR UPDATE仅适用于autocommit=0或者通过START TRANSACTION明确开启事务的情况。

set autocommit=0;　　
--设置完autocommit后，我们就可以执行我们的正常业务了。具体如下：
--0.开始事务
begin;/begin work;/start transaction; (三者选一就可以)
--1.查询出商品信息
select status from t_goods where id=1 for update;
--2.根据商品信息生成订单
insert into t_orders (id,goods_id) values (null,1);
--3.修改商品status为2
update t_goods set status=2;
--4.提交事务
commit;/commit work;

---

自增长锁

自增长在数据库中是一种非常常见的一种属性，也是很多DBA或开发人员或者DBA人员首选的主键方式。在InnoDB存储引擎的内存结构中，对每个含有自增长值的表都有一个自增长计数器(auto_increment counter)。当对含有自增长的计数器的表进行插入操作时，这个计数器会被初始化，执行如下的语句来得到计数器的值：

select max(auto_inc_col) from test for update;

插入操作会根据这个自增长的计数器值加1赋予自增长列。InnoDB提供了一个可配置的锁定机制，可以显着提高使用AUTO_INCREMENT列向表中添加行的SQL语句的可伸缩性和性能。 要对InnoDB表使用AUTO_INCREMENT机制，必须将AUTO_INCREMENT列定义为索引的一部分，以便可以对表执行相当于索引的SELECT MAX（ai_col）查找以获取最大列值。

在继续讨论新的自增长方式实现方式之前，需要对自增长的插入进行分类，如下：

• insert-like：指所有的插入语句，如insert，replace，insert—select,replace—select,load data等
• simple insert：指能在插入之前就确定插入行数的语句。这些语句包含insert、replace等，需要注意的是：simple inserts不包含insert—on duplicater key update这类SQL语句
• bulk inserts：指在插入之前不能确定得到插入行数的语句，如insert—select，replace–select，load data
• mixed-mode inserts：指插入中有一部分的值是自增长的，有一部分是确定的

其中参数innodb_autoinc_lock_mode以及各个设置下对自增的影响，其总共有三个可供设定，即0、1、2，具体说明如下：

• 0-traditional：在此锁定模式下，所有“INSERT-like”语句获得一个特殊的表级AUTO-INC锁，用于插入具有AUTO_INCREMENT列的表。此锁定通常保持到语句结束（不是事务结束），以确保为给定的INSERT语句序列以可预测和可重复的顺序分配自动递增值，并确保自动递增由任何给定语句分配的值是连续的。
• 1-consecutive：这是默认的锁定模式.在这个模式下,“bulk inserts”仍然使用AUTO-INC表级锁,并保持到语句结束.这适用于所有INSERT ... SELECT，REPLACE ... SELECT和LOAD DATA语句。同一时刻只有一个语句可以持有AUTO-INC锁。“Simple inserts”（要插入的行数事先已知）通过在mutex（轻量锁）的控制下获得所需数量的自动递增值来避免表级AUTO-INC锁， 它只在分配过程的持续时间内保持，而不是直到语句完成。
• 2-interleaved：在此锁定模式下，自动递增值保证在所有并发执行的“类INSERT”语句中是唯一且单调递增的。但是，由于多个语句可以同时生成数字（即，跨语句交叉编号），为任何给定语句插入的行生成的值可能不是连续的。如果执行的语句是“simple inserts”，其中要插入的行数已提前知道，则除了“混合模式插入”之外，为单个语句生成的数字不会有间隙。然而，当执行“批量插入”时，在由任何给定语句分配的自动递增值中可能存在间隙。

---

外键和锁

create table temp(
id int,
name char(20),
foreign key(id) references outTable(id) on delete cascade on update cascade
)engine=INNODB;

说明：

把id列设为外键 参照外表outTable的id列；

当外键的值删除，本表中对应的列筛除；

当外键的值改变，本表中对应的列值改变。

（1）InnoDB会自动对外键列添加索引，比如上面例子中的id列。

（2）外键值的插入和更新，首先需要查询父表中的记录，使用的是SELECT…LOCK IN SHARE MODE。

---

锁的3种算法

MySQL InnoDB支持三种行锁定方式：

• 行锁（Record Lock）:锁直接加在索引记录上面，锁住的是key。
• 间隙锁（Gap Lock）:锁定索引记录间隙，确保索引记录的间隙不变。间隙锁是针对事务隔离级别为可重复读或以上级别而已的。
• Next-Key Lock ：行锁和间隙锁组合起来就叫Next-Key Lock。

默认情况下，InnoDB工作在可重复读隔离级别下，并且会以Next-Key Lock的方式对数据行进行加锁，这样可以有效防止幻读的发生。Next-Key Lock是行锁和间隙锁的组合，当InnoDB扫描索引记录的时候，会首先对索引记录加上行锁（Record Lock），再对索引记录两边的间隙加上间隙锁（Gap Lock）。加上间隙锁之后，其他事务就不能在这个间隙修改或者插入记录。

---

四、事务

1. 事务的实现

1.1 redo

• redo：恢复提交事务时修改的页操作；保证事务的持久性D；在数据库运行时基本上都是顺序写；
• undo：回滚行记录到某个特定版本；保证事务的一致性C以及MVCC功能；需要进行随机读写；
  

innodb_flush_log_at_trx_commit参数用来控制redo日志刷新到磁盘的策略：

• 0：commit时不write，不flush。每次事务提交时只将日志记录到log buffer，Master Thread会每秒一次地将log buffer中的数据写入log file中，并且同时刷到磁盘，即调用fsync操作。
• 1：commit时write+flush。每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去，该模式为系统默认。
• 2：commit时只write，不flush。每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作。

1.2 binlog

MySQL的二进制日志，在数据库上层产生，不针对特定引擎。

redo日志是innoDB的日志，是物理格式的日志，记录的是对每个页的更改；binlog记录的是SQL语句。

1.3 undo

如果用户的事务或语句因为某种原因失败了，又或者一条rollback语句请求回滚，就可以利用undo日志将数据回滚到修改之前的样子。但是undo是逻辑日志，他引导的回滚可以让数据恢复到之前的样子，但是不保证物理存储依然和之前一致。

在InnoDB存储引擎中，undo log可分为以下两种类型：

• insert undo log
• update undo log

insert undo log是指在insert操作中产生的undo log。因为insert操作的记录，只对事务本身可见，对其他事务不可见（这是事务隔离性的要求），故该undo log可以在事务提交后直接删除。不需要进行purge操作。

update undo log记录的是对delete和update操作产生的undo log。该undo log可能需要提供MVCC机制，因此不能在事务提交时就进行删除。提交时放入undo log链表，等待purge线程进行最后的删除。在每个记录上有两个重要的系统数据列，用于多版本的控制：

• DB_TRX_ID：最近修改这个记录的事务ID
• DB_ROLL_PTR：指向由于最近的更新创建的回滚段

同时在每个undo record上也会指向旧的undo记录，从而形成了一条更新链，通过这个更新链，不同的事务可以找到其对应的版本的undo信息，组合成就版本记录。当一个事务提交时，由于该事务生成的undo log和标记删除的记录可能被其他事务所使用。当没有事务需要这些数据时，标记删除的记录和相关的undo log records可以被purge掉。

---

五、数据类型

UNSIGNED

例如，INT的类型范围是-2147483648～2147483647，INT UNSIGNED的范围类型就是0～4294967295。但UNSIGNED类型的数据进行减法操作而结果为负数时，在不同的操作系统最后的结果不同，所以尽量不要使用UNSIGNED，因为可能会带来一些意想不到的效果。

ZEROFILL

ALTER TABLE t CHANGE COLUMN a a int（ 4） UNSIGNED ZEROFILL；

括号里的数字和ZEROFILL配合设置了数据显示时的宽度，高位自动用0填充。但是int类型在存储的时候依然是占4个字节。

主键类型

• 自增整型：ID int primary key AUTO_INCREAMENT；一般就够用了；
• UUID：MySQL中并没有这么一个类型，它在MySQL中充当主键的类型实际上是字符串 id varchar(50) NOT NULL PRIMARY KEY；

如果是微型系统，而且肯定数据库建好了就不会动了，数据也不会去动，那么数据库主键就用自增int就够了。如果是比较中大型的系统，比如电商网站，erp，crm之类的系统，主键最好用UUID，可以保证数据的唯一性，而且数据迁移数据合并分库分表的时候会更省心，如果是int主键，数据迁移和合并会让你很头疼，如果涉及到外键关联，更要考虑主键ID重复性的问题，所以宁肯牺牲一点性能使用UUID做主键，长期来讲带来的好处远超过int带来的这点性能优势。

UUID

UUID保证对在同一时空中的所有机器都是唯一的。通常平台会提供生成的API。按照开放软件基金会(OSF)制定的标准计算，用到了以太网卡地址、纳秒级时间、芯片ID码和许多可能的数字。UUID由以下几部分的组合：
（1）当前日期和时间，UUID的第一个部分与时间有关，如果你在生成一个UUID之后，过几秒又生成一个UUID，则第一个部分不同，其余相同。
（2）时钟序列。
（3）全局唯一的IEEE机器识别号，如果有网卡，从网卡MAC地址获得，没有网卡以其他方式获得。

UUID的唯一缺陷在于生成的结果串会比较长。关于UUID这个标准使用最普遍的是微软的GUID(Globals Unique Identifiers)。在ColdFusion中可以用CreateUUID()函数很简单地生成UUID，
其格式为：xxxxxxxx-xxxx- xxxx-xxxxxxxxxxxxxxxx(8-4-4-16)，其中每个 x 是 0-9 或 a-f 范围内的一个十六进制的数字。而标准的UUID格式为：xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx (8-4-4-4-12);

Java生成UUID

import java.util.UUID;

public static void main(String[] args){
  for(int i=0;i<10;i++){
    String uuid = UUID.randomUUID().toString().replaceAll("-", "");
    System.out.println(uuid);
  }
}

---

CHAR(M), VARCHAR(M)不同之处

CHAR(M)定义的列的长度为固定的，M取值可以为0～255之间，当保存CHAR值时，在它们的右边填充空格以达到指定的长度。当检 索到CHAR值时，尾部的空格被删除掉。在存储或检索过程中不进行大小写转换。CHAR存储定长数据很方便，CHAR字段上的索引效率级高，比如定义 char(10)，那么不论你存储的数据是否达到了10个字节，都要占去10个字节的空间,不足的自动用空格填充。

VARCHAR(M)定义的列的长度为可变长字符串，M取值可以为0~65535之间，(VARCHAR的最大有效长度由最大行大小和使用 的字符集确定。整体最大长度是65,532字节）。VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则 使用两个字节)。VARCHAR值保存时不进行填充。当值保存和检索时尾部的空格仍保留，符合标准SQL。varchar存储变长数据，但存储效率没有 CHAR高。如果一个字段可能的值是不固定长度的，我们只知道它不可能超过10个字符，把它定义为 VARCHAR(10)是最合算的。VARCHAR类型的实际长度是它的值的实际长度+1。为什么”+1″呢？这一个字节用于保存实际使用了多大的长度。 从空间上考虑，用varchar合适；从效率上考虑，用char合适，关键是根据实际情况找到权衡点。

CHAR和VARCHAR最大的不同就是一个是固定长度，一个是可变长度。由于是可变长度，因此实际存储的时候是实际字符串再加上一个记录 字符串长度的字节(如果超过255则需要两个字节)。如果分配给CHAR或VARCHAR列的值超过列的最大长度，则对值进行裁剪以使其适合。如果被裁掉 的字符不是空格，则会产生一条警告。如果裁剪非空格字符，则会造成错误(而不是警告)并通过使用严格SQL模式禁用值的插入。