一、Mysql基本数据类型

1.1整数类型

整数类型包含八种，分别占用不同的位数，数值范围可根据位数计算。类型及对应存储空间位数如下：

• TINYINT： 8位存储空间
• SMALLINT 16位存储空间
• MEDIUMINT 24位存储空间
• INT 32位存储空间
• BIGINT 64位存储空间

整形在定义时有三种约束：[(M)] 、[UNSIGNED] 、[ZEROFILL]

• (M)仅仅指定字段的显示长度，未添加时默认为数值范围内的最大长度（比如-128就是4），添加后一般在跟ZEROFILL一起使用时有特殊表现。他不会影响字段的存储位数和数值范围
• UNSIGNED作为属性时会将整形置为无符号整数，数值范围从0开始
• ZEROFILL表示用0填充展示长度不够的位置，比如定义为INT(4) UNSIGNED ZEROFILL，字段值为4时，查询结果展示为0004

1.2实数类型

• Float 和 Double 分别占用4个字节和8个字节。这两种类型在做计算时仅能做近似计算，即计算结果是不可靠的
• Decimal支持精确计算，其本身计算是由mysql自己实现的。定义规则Decimal(M,N)。其中M表示类型所能记录的数字的最大长度，其范围为1～65；N表示其中小数位的长度，其范围是0～30，但不得超过M。可以用 bigint 代替 decimal ，例如Decimal(10,2)，就可以扩大100倍，使用对应的整形代替，计算效率会有提升。

1.3字符串类型（重要）

首先，本篇描述的是在innodb或myisam存储引擎下的规则

• varchar：存储可变长字符串，若Mysql表以ROW_FORMAT=FIXED创建则定长存储，其他情况下根据内容可变长度。其中需要预留1-2个字节记录长度。由于该类型存储长度可变，在update时如果空间增长且当前页没有额外空间分配会需要额外工作处理：Myisam会将行拆成不同片段存储，innodb需要分裂页。

• char：类型是定长的，适合存储很短的字符串或者基本定长的字符串。char类型会把字符串末尾的空格删除。字符串定义的长度是字符长度，具体的存储空间需要根据字符集去计算。

• blob与text：用来存储很大的字符串，二者区别在于前者采用二进制方式存储，后者采用字符。进行排序时只针对最前面的max_sort_length自己进行排序，如果只需要对前面的更少的字节进行排序，那么可以通过设置max_sort_length参数或者substring(value,length)来截取部分字符串。

二、Mysql语句执行过程

MySQL可以分为Server层和存储引擎层两部分。Server层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖MySQL的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。不同的存储引擎共用一个Server层

2.1查询语句执行

连接器 —— 查询缓存 —— 分析器 —— 优化器 —— 执行器

• 1、连接器

第一步，你会先连接到这个数据库上，这时候接待你的就是连接器。连接器负责跟客户端建立连接、获取权限、维持和管理连接。连接命令一般是这么写的： mysql -h ip -P port -u user -p。在完成经典的TCP握手后，连接器就要开始认证你的身份，这个时候用的就是你输入的用户名和密码。

• 如果用户名或密码不对，你就会收到一个"Access denied for user"的错误，然后客户端程序结束执行。
• 如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。

这就意味着，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。

• wait_timeout：控制连接器自动断开时间，默认值是8小时
• 长连接与短连接：长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个

但是全部使用长连接后，你可能会发现，有些时候MySQL占用内存涨得特别快，这是因为MySQL在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是MySQL异常重启了。

长连接导致MySQL占用内存涨得特别，这是因为MySQL在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。解决方案你可以考虑以下两种方案：

1. 定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。

2. 如果你用的是MySQL 5.7或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

• 2、查询缓存

连接建立完成后，你就可以执行select语句了。执行逻辑就会来到第二步：查询缓存。
MySQL拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以key-value对的形式，被直接缓存在内存中。key是查询的语句，value是查询的结果。如果你的查询能够直接在这个缓存中找到key，那么这个value就会被直接返回给客户端。

但是大多数情况下我会建议你不要使用查询缓存，为什么呢？因为查询缓存往往弊大于利。
查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。

好在MySQL也提供了这种“按需使用”的方式。你可以将参数query_cache_type设置成DEMAND，这样对于默认的SQL语句都不使用查询缓存。而对于你确定要使用查询缓存的语句，可以用SQL_CACHE显式指定，像下面这个语句一样：

mysql> select SQL_CACHE * from T where ID=10；

需要注意的是，MySQL 8.0版本直接将查询缓存的整块功能删掉了，也就是说8.0开始彻底没有这个功能了。

• 3、分析器

如果没有命中查询缓存，就要开始对SQL语句做解析。

1. 分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条SQL语句，MySQL需要识别出里面的字符串分别是什么，代表什么。

2. MySQL从你输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名T”，把字符串“ID”识别成“列ID”。

3. 做完了这些识别以后，就要做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法。

• 4、优化器

1. 在表里面有多个索引的时候，决定使用哪个索引；
2. 在一个语句有多表关联（join）的时候，决定各个表的连接顺序。比如你执行下面这样的语句，这个语句是执行两个表的join：

• 5、执行器

1. 判断有没有执行查询的权限(在工程实现上，如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证。查询也会在优化器之前调用precheck验证权限)。
2. 打开表的时候，根据表的引擎定义，去使用这个引擎提供的接口。

2.2 更新语句执行（日志系统）

在正式执行之前流程同查询过程，执行过程设计两个重要日志：redo log 和 binlog

1、redolog使用方式

• 先写redolog，更新内存：当有一条记录需要更新的时候，InnoDB引擎就会先把记录写到redo log里面，并更新内存
• 系统比较空闲时，写磁盘
• redolog写满时，写磁盘
• 存储格式：redo log是固定大小的，比如可以配置为一组4个文件，每个文件的大小是1GB，那么这块“粉板”总共就可以记录4GB的操作。从头开始写，写到末尾就又回到开头循环写；write pos是当前记录的位置；checkpoint是当前要擦除的位置
• crash-safe：有了redo log，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失

2、binlog使用方式

这两种日志有以下三点不同

1. redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用。
2. redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。
3. redo log是循环写的，空间固定会用完；binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

执行器和InnoDB引擎在执行update语句时的内部流程

1. 执行器先找引擎取ID=2这一行。ID是主键，引擎直接用树搜索找到这一行。如果ID=2这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
2. 执行器拿到引擎给的行数据，把这个值加上1，比如原来是N，现在就是N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log里面，此时redo log处于prepare状态。然后告知执行器执行完成了，随时可以提交事务。
4. 执行器生成这个操作的binlog，并把binlog写入磁盘。
5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交（commit）状态，更新完成。

3、两阶段提交

redo log被设计用来减少IO写磁盘的次数，提高性能。redo log和binlog都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致
不实用两阶段提交的问题：主要都是数据回复时的影响：

1. 先写redo log后写binlog。假设在redo log写完，binlog还没有写完的时候，MySQL进程异常重启。由于我们前面说过的，redo log写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行c的值是1。但是由于binlog没写完就crash了，这时候binlog里面就没有记录这个语句。因此，之后备份日志的时候，存起来的binlog里面就没有这条语句。然后你会发现，如果需要用这个binlog来恢复临时库的话，由于这个语句的binlog丢失，这个临时库就会少了这一次更新，恢复出来的这一行c的值就是0，与原库的值不同。

2. 先写binlog后写redo log。如果在binlog写完之后crash，由于redo log还没写，崩溃恢复以后这个事务无效，所以这一行c的值是0。但是binlog里面已经记录了“把c从0改成1”这个日志。所以，在之后用binlog来恢复的时候就多了一个事务出来，恢复出来的这一行c的值就是1，与原库的值不同。

三、Mysql索引

写在最前面，Mysql不能在索引中执行Like操作，但是可以做最左前缀匹配。比如 like '%asd%' 这种，开头通配符的，是无法使用索引的；但是 like 'abc%' 这种可以！

2.1 B-tree索引

在mysql中，如果没有明确指明，讨论的都是btree索引，但事实上很多引擎使用B+Tree实现的。索引的叶子节点指针指向了被索引的数据。树的深度和表的大小直接相关。Btree索引可以再一列或者多列上创建索引（联合索引），适合范围查找，索引排序等。可以引用Btree索引的查询包括：

• 全职匹配：与所有列匹配，精确查找

• 匹配最左前缀：匹配连续的前n列，匹配必须从第一列开始（按照创建索引时的列顺序排序）

• 匹配列前缀：可以只匹配第一列的值的开头部分，优先满足最左匹配后才能满足列前缀匹配。

• 匹配范围值：例如以金额为索引，查找金额between 100 and 200可以用到索引（因为b+tree在叶子节点时会按索引顺序指向下一个叶子节点）

• 精确匹配某一列并范围匹配另一列：第一列精准，第二列范围

• 只访问索引的查询：又有说法叫覆盖查询，表示要查询的数据仅在索引上就能够取得，不需要去读取具体的数据行（一般来说，非聚簇索引的叶子节点记录的时聚簇索引的值，聚簇索引的叶子节点保存着真是的数据行。所以，在非局促索引查询时，如果需要整行数据还要进行回表，级再走一次聚簇索引的索引树。覆盖索引减少了这个过程，是一种优化的手段）

btree索引的限制：

• 不从最左列开始查找无法使用索引
• 不能跳过中间的列，一旦跳过，只能使用前面部分的索引，跳过之后的部分无法使用索引
• 查询中若某一列是范围搜索（比如>,like），则右边的列无法使用索引

2.2 哈希索引

基于hash表实现， 只有精确匹配索引所有列的查询才有效。hash表中记录的是指向数据行的指针，且表中顺序是计算出的hashcode值的顺序不是数据的顺序，所以无法进行范围查找（毕竟不是LinkedHashMap）。不过精准查找上，理论上比btree速度更快

使用方案举例：比如待匹配列是长字符串类型（比如网址），建立btree索引因为存储内容很大影响查询效率（个人理解，因为节点空间变大导致索引页数增多，读取页的次数增加；字符串的匹配速度也很慢）。这是建立冗余列，存储字符串的hash值。建立hash索引，查询时同时查询可以提供效率。hash函数（CRC32）

sql优化

2.3 聚簇索引

聚簇索引不是某种索引类型，而是一种数据存储方式。InnoDB的聚簇索引保存了索引行和数据行。当表中有聚簇索引时，他的数据行实际上存放在叶子页中，但是节点页只保存了索引行。注意，一张表只会有一个聚簇索引。
（本人的了解是，当建表时制定了主键，则主键为聚簇索引，若没有主键，则选择一个非空的唯一索引作为聚餐索引，若还没有，则用rowid隐式的作为聚簇索引。如果建表之后再添加主键列，似乎也不会改变已有的聚簇索引，那时主键就不是聚簇索引了）

• 插入速度严重依赖插入顺序（这就是一颗树为了平衡做的牺牲，同时带着数据行跟着移位），按照主键的顺序进行插入是速度最快的
• 更新聚簇索引的代价很高
• 有页分裂的问题（这就是不按顺序查数据的后果，当然更新主键一样会造成类似的问题）
• 二级索引，即非聚簇索引会比想象的更大，因为他叶子节点记得是聚簇索引的值，其查询除非覆盖索引，不然就得查两次了（这就是回表！！！）

2.4 覆盖索引

如果一个索引包含或者说覆盖了需要查询的所有字段，则称为覆盖索引。覆盖索引因为索引上就包含了所需要的字段值，则不需要二次查询。（你要是走的主键索引，且只搜索主键，其实也挺快的）

2.5 索引排序

2.6 索引失效

• 全值匹配顺序无影响：SQL 中查询字段的顺序，跟使用索引中字段的顺序，没有关系。优化器会在不影响 SQL 执行结果的前提下，给你自动地优化
• 不符合最左前缀法则：过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无
• 在索引列上做任何计算：在索引列上做任何操作（计算、函数、(自动 or 手动)类型转换），会导致索引失效而转向全表扫描。（类型转换例如= 右侧字符串不加引号）
• 索引列上有范围查询：将可能做范围查询的字段的索引顺序放在最后
• 尽量使用覆盖索引：不实用覆盖索引，则语句需要回表操作，如果执行器认为查询效率不高可能会变成全表扫描
• 使用不等于(!= 或者<>)的时候：
• 字段的 is not null 和 is null：当字段允许为 Null 的条件下，is not null 用不到索引，is null 可以用到索引
• ike 的前后模糊匹配
• 减少使用 or：使用 union all 或者 union 来替代

3.1 Innodb选择B+树作为索引的原因

1. 树高相对较低：树高度决定数据页加载次数，应该尽量少来提高查询速度
2. 适合范围查找：叶子节点联通，适合进行范围查找

3.2 B+树结构

B+树是B树的一种变形形式，B+树上的叶子结点存储关键字以及相应记录的地址，叶子结点以上各层作为索引使用。一棵m阶的B+树定义如下
(1)每个结点至多有m个子女；
(2)除根结点外，每个结点至少有[m/2]个子女，根结点至少有两个子女;
(3)有k个子女的结点必有k个关键字。

B+树的查找与B树不同，当索引部分某个结点的关键字与所查的关键字相等时，并不停止查找，应继续沿着这个关键字左边的指针向下，一直查到该关键字所在的叶子结点为止

3.3 聚簇索引、非聚簇索引，非聚簇不存行的原因

• 聚簇索引：叶子节点记录行数据的索引
• 非聚簇索引：聚簇索引只能有一个，其他的都是非聚簇。叶子节点记录聚餐索引的值
• 非聚簇不存行的原因：个人理解，行数据被聚簇索引按照索引顺序维护到了对应的页里，如果非聚簇也直接记录行数据，只能冗余出一份文件，浪费空间；如果记录行地址，有个很严重的问题，是行地址是会发送改变的（不按顺序插入或删除，导致页分裂或者合并），这种时候，就需要在改变地址时同时修改所有索引，耗费性能。

3.4 唯一索引和普通索引的选择

1. 查询过程：
   普通索引，查找到满足条件的第一个记录(5,500)后，需要查找下一个记录，直到碰到第一个不满足k=5条件的记录。
   唯一索引：由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。
2. 更新过程：
   唯一索引：所有的更新操作都要先判断这个操作是否违反唯一性约，而这必须要将数据页读入内存才能判断
   普通索引：如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。因此，在更新普通索引时，若对应的数据页不在内存，就通过change buffer的机制，减少磁盘Io

三、事物，扩展spring的事物传播

3.1 事物

事务是由一组SQL语句组成的逻辑处理单元,事务具有以下4个属性,通常简称为事务的ACID属性：

• 原子性(Atomicity)：事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。
• 一致性(Consistent) ：在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性;事务结束时,所有的内部数据结构(如B树索引或双向链表)也都必须是正确的。
• 隔离性(Isolation) ：数据库系统提供一定的隔离机制,保证事务在不受外部并
  发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。
• 持久性(Durable)：事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。

3.2 Innodb如何保证事物的特性

• 原子性(Atomicity)：undo log支持事物的回滚，保证事物内操作的原子性
• 一致性(Consistent) ：多数说法，一致性是应用层语义上的前后一致，也就是说，是业务层面对执行的结果符合预期。所以，一般来说，一致性是通过其他三个特性来保证的
• 隔离性(Isolation) ：通过mvcc和锁来保证事物隔离性
• 持久性(Durable)：redo log重做日志保证

3.3 并发事物可能带来的问题

更新丢失（Lost Update）
　　当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题–最后的更新覆盖了由其他事务所做的更新。

脏读（Dirty Reads）
　　一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致的状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此作进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象的叫做“脏读”。一句话：事务A读取到了事务B已经修改但尚未提交的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求。

不可重读（Non-Repeatable Reads）
　　一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读”。一句话：事务A读取到了事务B已经提交的修改数据，不符合隔离性

幻读（Phantom Reads）
　　一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。一句话：事务A读取到了事务B提交的新增数据，不符合隔离性

3.4 事务隔离级别

1. 数据库的事务隔离越严格,并发副作用越小,但付出的代价也就越大,因为事务隔离实质上就是使事务在一定程度上“串行化”进行,这显然与“并发”是矛盾的。
2. 不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读"和“幻读”并不敏感，可能更关心数据并发访问的能力。

• 查看当前数据库的事务隔离级别: show variables like 'tx_isolation'
• 设置事务隔离级别：set tx_isolation='REPEATABLE-READ'

隔离级别与对应问题处理

可重复读隔离级别的幻读问题：

• 如果事物A仅执行快照读的查询语句多次，且中间为插入任何当前读语句，则查询结果一定满足可重复读（mvcc保证了在A事物查询之后创建的事物影响不到A，通过undo log实现的）
• 幻读特殊情况：事物A查询当前值为1，其他事物进行+1操作。此时A查询结果不变。然后A执行+1操作，查询后发现数值总共+2。这是因为update相当于当前读，所以查到了其他事物提交的结果。这也是并发执行有可能会出现问题的地方（是否有问题视业务具体情况）。如果要解决就需要用到行锁、间隙锁了。

3.5 事务的启动时机

begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作InnoDB表的语句，事务才真正启动。如果你想要马上启动一个事务，可以使用start transaction with consistent snapshot 这个命令

3.6 undo log

image.png

图中的三个虚线箭头，就是undo log；而V1、V2、V3并不是物理上真实存在的，而是每次需要的时候根据当前版本和undo log计算出来的。比如，需要V2的时候，就是通过V4依次执行U3、U2算出来。

3.7 spring事物控制及传播

org.springframework.transaction.annotation.Transactional，让我们可以通过注解开启方法的事务控制。其中属性：
ISOLATION : 标识事务隔离级别包含5种：

• ISOLATION_DEFAULT：默认使用数据库的默认隔离级别
• ISOLATION_READ_UNCOMMITTED：读未提交
• ISOLATION_READ_COMMITTED：读已提交
• ISOLATION_REPEATABLE_READ：可重复读
• ISOLATION_SERIALIZABLE：串行化

propagation ：标识事物的传播机制包含七种：

• PROPAGATION_REQUIRED: 如果存在一个事务，则加入当前事务。如果没有事务则为自己开启事物。需要注意，REQUIRED会跟外围事物处于同一个事物里，即便外围函数对REQUIRED进行异常捕获，整体事物依然回滚

• PROPAGATION_SUPPORTS: 如果存在一个事务，则加入当前事务。如果没有事务，则非事务的执行

• PROPAGATION_MANDATORY: 如果已经存在一个事务，则加入当前事务。如果没有一个活动的事务，则抛出异常

• PROPAGATION_REQUIRES_NEW: 总是开启一个新的事务。如果一个事务已经存在，则将这个存在的事务挂起，当自身事物执行完，再执行挂起事物。 外围事物与REQUIRES_NEW不处于同一个事物，即使外围失败回滚，也不影响已执行的REQUIRES_NEW。但当内部的REQUIRES_NEW执行失败，若外围存在事物，则根据是否进行异常捕获处理拍判断外围事物是否执行回滚

• PROPAGATION_NOT_SUPPORTED: 总是非事务地执行，并挂起任何存在的事务。

• PROPAGATION_NEVER: 总是非事务地执行，如果存在一个活动事务，则抛出异常

• PROPAGATION_NESTED：如果当前存在事务，则在嵌套事务内执行。如果当前没有事务，则执行与PROPAGATION_REQUIRED类似的操作。主要区别在于，NESTED开启的是子事物，当内部事务回滚但是异常被捕获处理后，外围事物和其他子事物不用回滚

四、锁

4.1、MVCC 快照读与当前读

MySQL InnoDB存储引擎，实现的是基于多版本的并发控制协议——MVCC (Multi-Version Concurrency Control) 。MVCC最大的好处：读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能
在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)。快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录

• 快照读：简单的select操作，属于快照读，不加锁。对于同一个查询语句会保存一个快照版本，重复查询只是将快照中的数据查出来。属于伪重复读（其他事物可以在快照范围内插入数据并提交，之后，虽然当前事物判断同样id数据不存在，但是插入同样id数据会报错）
  例子：select * from table where ?;
• 当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁（gap锁(间隙锁)）。大致理解为在这些“读”操作的范围内，增加锁，则在范围内的数据无法被插入。
  例子：
  select * from table where ? lock in share mode;（共享锁）
  select * from table where ? for update;（排它锁）
  insert into table values (…);（排它锁）
  update table set ? where ?;（排它锁）
  delete from table where ?;（排它锁）

4.2、锁类型

共享锁与排他锁

• 共享锁（读锁）：其他事务可以读，但不能写。（lock in share mode）
• 排他锁（写锁） ：其他事务不能读取，也不能写。(for update)

锁粒度

• 表级锁(table-level locking)：MyISAM 和 MEMORY 存储引擎采用
• 行级锁： InnoDB 存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行锁

不同锁粒度的比较

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。表级锁更适合于以查询为主，并发用户少，只有少量按索引条件更新数据的应用，如Web 应用
• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。最大程度的支持并发，同时也带来了最大的锁开销。在 InnoDB 中，除单个 SQL 组成的事务外，锁是逐步获得的，这就决定了在 InnoDB 中发生死锁是可能的。行级锁只在存储引擎层实现，而Mysql服务器层没有实现。 行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统

4.3、MyISAM 表锁

MyISAM表级锁模式

• 表共享读锁 （Table Read Lock）：不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求
• 表独占写锁 （Table Write Lock）：会阻塞其他用户对同一表的读和写操作

MyISAM 表不适合于有大量更新操作和查询操作应用

MyISAM 表的读操作与写操作之间，以及写操作之间是串行的。当一个线程获得对一个表的写锁后， 只有持有锁的线程可以对表进行更新操作。 其他线程的读、 写操作都会等待，直到锁被释放为止。默认情况下，写锁比读锁具有更高的优先级：当一个锁释放时，这个锁会优先给写锁队列中等候的获取锁请求，然后再给读锁队列中等候的获取锁请求

五、查询优化

5.1、explain详解

EXPLAIN的分析结果

• 表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以使用
• 哪些索引被实际使用
• 表之间的引用
• 每张表有多少行被优化器查询

执行计划各字段含义

• id：
  • id相同，执行顺序由上至下
  • id不同，如果是子查询序号会递增，id值越大优先级越高，越先被执行
  • id相同、不同同时存在，则id相同看作一组，从上到下执行，id不同的组，根据id值越大越优先执行
• select_type
  表示查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询，包括：
  • SIMPLE：简单的select查询，查询中不包含子查询或者UNION
  • PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY
  • SUBQUERY：在SELECT或WHERE列表中包含了子查询
  • DERIVED：在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中
  • UNION：若第二个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
  • UNION RESULT： 从UNION表获取结果的SELECT
• table
  显示这一行的数据是关于哪张表的，一种特殊格式<derived2>，表示指向id为2的表的衍生表
• type：查询类型
  • system：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计
  • const：表示通过索引一次就找到了，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量
  • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
  • ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
    range 只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引
  • range：只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引
  • index：Full Index Scan，Index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘读取的）
  • all：Full Table Scan 将遍历全表以找到匹配的行

system > const > eq_ref > ref > range > index > all

不要创建太多的列
mysql限制关联操作最大只能61张表

六、引擎

6.1 Innodb

选择innodb作为存储引擎主要有三点原因：

1. 独立表空间，支持事物，通过MVCC、行锁设计，提供一致性非锁定读
2. 支持外键，插入缓冲，二次写，自适应哈希索引，预读
3. 使用聚集的方式存储数据，每张表的存储都是按主键顺序存放。
4. 备份：InnoDB 支持在线热备份

6.2 MyISAM

MyISAM引擎的特点：

1. 只支持表级锁，用户在操作myisam表时，select，update，selete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据
2. 崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。
3. 其它特性：MyISAM 支持压缩表和空间数据索引。

七、数据库集群

八、分布式事物

十、双写一致性

十一、分库分表

十二、非分表建查询方案