【MySQL】阅读笔记

admin管理员组

文章数量:1570220

这里写目录标题

• 关系型数据库与非关系型
• MySQL架构
• 一条sql执行流程
• MySQL数据存放电脑位置
• ibd文件结构
• 行溢出是什么
• MySQL行记录存储格式
• 索引
• 为什么InnoDB选择B+树作为索引数据结构
• 什么时候需要创建索引
• 优化索引方法
• InnoDB内部怎么存储数据
• B+ 树如何进行查询
• 聚簇索引和二级索引
• 为什么MySQL要采用B+树作为索引底层结构呢？
• • 1.0 线性查找
  • 2.0 二叉查找树
  • 3.0 平衡二叉树
  • 4.0 B 树
  • B+ 树
  • InnoDB里的B+树
• 索引失效情况
• • 对索引使用左、左右模糊查询
  • 对索引进行表达式计算
  • 对索引隐式类型转换
  • 联合索引非最左匹配
  • WHERE 中 OR前和OR后
• 覆盖索引
• like “%x“，索引一定会失效吗
• count(*) 和 count(1)区别
• 事务特性
• 并行事务会引发什么问题？
• • 脏读
  • 不可重复读
  • 幻读
• 事务隔离级
• 可重复读中怎么解决幻读
• MVCC
• MySQL中的锁
• • 全局锁
  • 表级锁
  • 行级锁
• InnoDB加行级锁
• 为什么update没加索引会锁全表
• 死锁的产生
• 避免死锁
• Buffer Pool
• WAL （Write-Ahead Logging）技术
• 日志
• 主从复制
• MySQL优化
• Drop、Delete与Truncate
• 视图
• 游标
• InnoDB 与 MyISAM
• 分库分表
• 数据库三大范式
• NOW()和CURRENT_DATE()
• char 和 varchar
• 内连接与外连接

关系型数据库与非关系型

关系型数据库是指采用了关系模型也就是二维表结构来组织数据的数据库，数据按照表的形式存储，然后多表构成一个数据库。常见的关系型数据库有MySQL、Oracle、SQL Server等等。

优点：容易理解、使用方便、易于维护
缺点：后续修改表结构困难、扩展困难、难处理高并发事务（硬盘IO是个瓶颈）

非关系型数据库以键值对存储，它不局限于固定的结构，每个元组可以有不一样的字段，严格来说非关系型数据库不是一种数据库，应该是一种数据结构化存储方法的集合。常见的非关系型数据库有基于键值对的redis、基于Document的Mongdb等等。

优点：容易扩展（因为NoSQL之间的数据无关系）、数据存储模式灵活、不需要经过SQL层的解析，读写效率高
缺点：只适合存储一些较为简单的数据，较难查询复杂的数据；事务处理支持不完善，比起关系型数据库差很多。

MySQL架构

MySQL 的架构共分为两层：Server 层和存储引擎层

Server层
Server 层主要负责建立连接、分析和执行 SQL。MySQL 里大多数的核心功能模块都在这实现，主要包括连接器，查询缓存、解析器、预处理器、优化器、执行器等。另外，所有的内置函数和所有跨存储引擎的功能比如存储过程、触发器等都在 Server 层实现。

存储引擎层
存储引擎层主要负责数据的存储和提取。MySQL支持多个存储引擎，不同的存储引擎共用一个 Server 层。从 MySQL 5.5 版本开始， InnoDB 成为了 MySQL 的默认存储引擎。我们常说的索引数据结构，就是由存储引擎层实现的，不同的存储引擎支持的索引类型也不相同，比如 InnoDB 支持索引类型是 B+树 ，且是默认使用，也就是说在数据表中创建的主键索引和二级索引默认使用的是 B+ 树索引。

一条sql执行流程

第一步：通过连接器进行连接
我们首先需要连接 MySQL 服务器，然后才能执行 SQL。因为 MySQL 是基于 TCP 协议进行传输的，所以连接的过程需要先经过 TCP 的三次握手。
第二步：查询缓存
如果 SQL 是查询语句，MySQL 就会先去缓存里查找缓存数据，看看之前有没有执行过这一条命令，这个查询缓存是以 key-value 形式保存在内存中的，key 为 SQL 查询语句，value 为 SQL 语句查询的结果。
如果查询的语句命中查询缓存，那么就会直接返回 value 给客户端。如果查询的语句没有命中查询缓存中，那么就要往下继续执行，等执行完后，查询的结果就会被存入查询缓存中。

但其实对于更新比较频繁的表，查询缓存的命中率很低的，因为只要一个表有更新操作，那么这个表的查询缓存就会被清空。如果刚缓存了一个查询结果很大的数据，还没被使用的时候，刚好这个表有更新操作，查询缓冲就被清空了，那这样也太浪费资源了。所以，MySQL 8.0 版本直接将查询缓存删掉了。

第三步：解析器解析 SQL
在正式执行 SQL 查询语句之前， MySQL 会先对 SQL 语句做解析，这个工作交由「解析器」来完成。
解析器会做如下两件事情。

1，词法分析。MySQL 会根据你输入的字符串识别出关键字，比如表名、字段名、where条件这些，并且构建出 SQL 语法树。
2，语法分析。也就是检验我们输入的 SQL 语句语法是否正确，比如把 from 写成了 form，这时 MySQL 解析器就会报错。

第四步：执行SQL
SELECT 查询语句流程主要可以分为三个阶段：

1、预处理阶段；
检查 SQL 查询语句中的表或者字段是否存在；把 select* 中的 * 符号，扩展为表上的所有列；
2、优化阶段；
优化器主要负责将 SQL 查询语句的执行方案确定下来，比如在表里面有多个索引的时候，优化器会基于查询成本的考虑，来决定选择使用哪个索引。
3、执行阶段；
根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；

MySQL数据存放电脑位置

默认是在 /var/lib/mysql/ 目录里，一个xx数据库目录下有三个文件

db.opt， 用来存储当前数据库的默认字符集和字符校验规则。
xx.frm，用来存储表结构，每建立一张表都会生成一个.frm 文件。
xx.ibd，用来存储表数据，每一张表的数据都存放在一个独立的 .ibd 文件。

ibd文件结构

表空间由行、页、区、段组成。
1、行
数据库表中的记录都是按行进行存放的，每行记录根据不同的行格式，有不同的存储结构。
2、页
虽然记录是按照行来存储的，但是数据库的读取并不以行为单位，因为如果每次只能处理一行数据，效率会非常低。所以，InnoDB 的数据是以「页」为单位来读写的。每当需要读一条记录的时候，会把包含这条记录的整个页从磁盘读入内存。
页是 InnoDB 存储引擎磁盘管理的最小单元，它默认的大小是16KB，所以数据库每次读写都是以 16KB 为单位的。
3、区
4、段

行溢出是什么

MySQL 中磁盘和内存交互的基本单位是页，一个页的大小一般是 16KB，也就是 16384字节，而一个 varchar(n) 类型的列最多可以存储 65532字节，一些大对象如 TEXT、BLOB 可能存储更多的数据，这时一个页可能就存不了一条记录。这个时候就会发生行溢出，多的数据就会存到另外的「溢出页」中。

MySQL行记录存储格式

InnoDB 提供了 4 种行格式，分别是 Redundant、Compact、Dynamic和 Compressed 行格式。
1、Redundant
Redundant是MySQL5.0之前使用的格式，现在已经废弃了。
2、Compact
这个比较经典，后面的Dynamic和Compressed 都是基于Compact改造而来的。

在compact行格式下，一行记录分为两个部分：额外信息和真实数据。
额外信息
额外信息包含 3 个部分：变长字段的长度列表、NULL 值列表、记录头信息

变长字段的长度列表主要是存储变长字段所占用的数据大小，然后读取的时候可以根据这个长度列表来读取对应的数据长度。它在存储时是按照列的逆序来存放的。之所以要逆序，是因为记录头信息中指向下一个记录的指针，指向的是下一条记录的「记录头信息」和「真实数据」之间的位置，这样的好处是向左读就是记录头信息，向右读就是真实数据，比较方便。
如果表里没有变长字段，那行格式就不会有变长字段长度列表。

NULL 值列表：因为表中的某些列可能会存储 NULL 值，如果把这些 NULL 值都放到记录的真实数据中会比较浪费空间，所以 Compact 行格式把这些值为 NULL 的列存储到 NULL值列表中。如果列是NULL，那就给它对应值为1的二进制位。如果列值不是NULL，就给它一个0的二进制位。存放时也是逆序存放。
当数据表的字段都定义成 NOT NULL 的时候，这时候表里的行格式就不会有 NULL 值列表了。

记录头信息：包含比较多的东西，比如delete_mask 标识这条数据是否被删除。next_record下一条记录的位置等等。

真实数据
记录真实数据部分除了我们定义的字段，还有三个隐藏字段，分别为：row_id、trx_id、roll_pointer。
如果表里既没有指定主键，又没有唯一约束，那么 InnoDB 就会为记录添加 row_id 隐藏字段，占六个字节。
trx_id是事务id，表示这个数据是由哪个事务生成的，占6个字节。
roll_pointer，这条记录上一个版本的指针。roll_pointer 是必需的，占 7 个字节。

Dynamic
Compressed 和 Dynamic 这两个行格式和 Compact 非常类似，主要的区别在于处理行溢出数据时有些区别。
Compact 在行溢出时，会在记录的真实数据处只会保存该列的一部分数据，而把剩余的数据放在「溢出页」中，然后真实数据处用 20 字节存储指向溢出页的地址，从而可以找到剩余数据所在的页。
而Compressed 和 Dynamic 在行溢出时，记录的真实数据处只存储 20 个字节的指针来指向溢出页，而实际的数据全都存储在溢出页中。

索引

索引的定义就是帮助存储引擎快速获取数据的一种数据结构，形象的说索引就相当于数据的目录。

• 按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
• 按「物理存储」分类：聚簇索引、非聚簇索引（辅助索引）。
  聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。
  在innodb存储引擎里，B+树索引可以分为聚簇索引和非聚簇索引。
• 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
• 按「字段个数」分类：单列索引、联合索引。
  多个普通字段组合在一起创建的索引就叫做联合索引，也叫组合索引。

InnoDB 存储引擎会根据不同的场景选择不同的列作为索引，默认底层使用的数据结构就是B+Tree索引。
B+Tree 是一种多叉树，只在叶子节点中按主键顺序放真实数据，在非叶子节点中只放索引。每一个叶子节点都有两个指针，分别指向下一个叶子节点和上一个叶子节点，形成一个双向链表。每一层父节点的索引值都会出现在下层子节点的索引值中，因此在叶子节点中，包括了所有的索引值信息。
B+Tree 相比于 B 树和二叉树来说，最大的优势在于查询效率很高，因为即使在数据量很大的情况，查询一个数据的磁盘 I/O 依然维持在 3-4次。
一张表只能有一个聚簇索引，那为了实现非主键字段的快速搜索，就引出了非聚簇索引，它也是利用了 B+ 树的数据结构，但是非聚簇索引的叶子节点存放的是主键值，不是实际数据。
聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致。

聚簇索引的 B+Tree 和非聚簇索引的 B+Tree 的区别
主键索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里；
二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据。查询时需要先获得主键值，再通过主键索引中的 B+Tree 树查询到对应的叶子节点来获取整行数据。这个过程叫「回表」，也就是说要查两个 B+Tree 才能查到数据。

MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址，也就是一个指向对应数据块的指针。

为什么InnoDB选择B+树作为索引数据结构

1、B+Tree vs B Tree
B+Tree 只在叶子节点存储数据，而 B 树 的非叶子节点也要存储数据，所以 B+Tree 的单个节点的数据量更小，在相同的磁盘 I/O 次数下，就能查询更多的节点。

2、B+Tree vs 二叉树
对于有 N 个叶子节点的 B+Tree，其搜索复杂度为O(logdN)，其中 d 表示节点允许的最大子节点个数为 d 个。在实际的应用当中，d 值是大于100的，这样就保证了即使数据达到千万级别，B+Tree 的高度依然维持在 3~4 层左右，也就是说一次数据查询操作只需要做 3~4 次的磁盘 I/O 操作就能查询到目标数据。
而二叉树的每个父节点的儿子节点个数只能是 2 个，意味着其搜索复杂度为 O(logN)，这已经比 B+Tree 高出不少，因此二叉树检索到目标数据所经历的磁盘 I/O 次数要更多。

3、B+Tree vs Hash
Hash 在做等值查询的时候效率很快，搜索复杂度为 O(1)。但数据库中经常查询多条数据，这时候由于B+数据的有序性，叶子节点又有链表相连，他的查询效率会比Hash快的多。

什么时候需要创建索引

索引缺点
需要占用物理空间，索引数量越大，占用的空间就越大；
创建和维护索引要耗费时间，这种时间随着数据量的增加而增大；
索引会降低表增删改的效率，因为每次增删改索引，B+ 树为了维护索引有序性，都需要进行动态维护。

什么时候需要索引？
有经常用于 WHERE 查询条件的字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引。
字段有唯一性限制的，比如商品编码；
经常用于 GROUP BY 和 ORDER BY 的字段，这样在查询的时候就不需要再去做一次排序了，因为建立索引之后在 B+Tree 中的记录都是排序好的。
唯一、不为空、经常被查询的字段 的字段适合建索引。

什么时候不需要创建索引？
表数据太少的时候，不需要创建索引；
经常更新的字段不用创建索引，因为如果索引字段频繁修改，那么维护 B+Tree的成本就会上升，会影响数据库性能的。
WHERE 条件，GROUP BY，ORDER BY 里用不到的字段，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的，因为索引是会占用物理空间的。
字段中存在大量重复数据，比如性别字段，男女的记录分布均匀，那么无论搜索哪个值都可能得到一半的数据。这种情况MySQL很可能会通过查询优化器进行全表扫描。

优化索引方法

使用前缀索引
前缀索引顾名思义就是使用某个字段中字符串的前几个字符建立索引，比如在一些包含长字符串的字段上用它的前几个字符作为前缀索引，就可以减小索引字段大小，增加一个索引页中存储的索引值，有效提高索引的查询速度。

order by 无法使用前缀索引；
无法把前缀索引用作覆盖索引；

使用覆盖索引
覆盖索引是指 SQL 中 query 的所有字段，在索引 B+Tree 的叶子节点上都能找得到的那些索引，从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，可以避免回表的操作。
所以我们可以针对要查询的字段建立一个联合索引，从而避免回表。

使用自增主键
InnoDB 创建主键索引默认为聚簇索引，数据存放在 B+Tree 的叶子节点上。同一个叶子节点内的各个数据是按主键顺序来存放的，每当有一条新的数据插入时，数据库会根据主键把其插入到对应的叶子节点中。
如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。因为每次插入一条新记录，都是追加操作，不需要重新移动数据，因此这种插入数据的方法效率非常高。
如果我们使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其它数据来满足新数据的插入，甚至可能发生从一个页面复制数据到另外一个页面的页分裂情况。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。比如一个满的数据页里是1、3、5、9，现在要插入数据7，那就需要把这个页面分割成两个页面，一个是13、一个是579。如果是自增的主键插入就不会出现这种情况。

InnoDB内部怎么存储数据

首先，InnoDB 的数据是按「数据页」为单位来读写的，每个数据页的默认大小是 16KB。

在 文件头里有两个指针，分别指向上一个数据页和下一个数据页，这样相当于把所有的页连接成一个双向的链表。
数据页中的记录会按照「主键」顺序组成单向链表，这样插入、删除非常方便，但是检索效率不高，最差的情况下需要遍历链表上的所有节点才能完成检索。所以，数据页中有一个页目录，起到记录的索引作用，这样就能快速找到所需的记录。

页目录创建过程
首先会把将所有的记录按主键顺序划分成若干个组，每个记录组的最后一条记录就是该组的最大记录，并且会在最后一条记录的头信息中存储这个组记录的总条数。然后用页目录来存储每组最后一条记录的地址偏移量，就相当于一个槽，每个槽指向了不同组的最后一个记录，槽相当于分组记录的索引。因为记录是按照「主键值」从小到大排序的，所以我们通过槽查找记录时，可以使用二分法快速定位要查询的记录在哪个槽里，定位到槽后，再遍历槽内的所有记录，找到对应的记录，无需从最小记录开始遍历整个页中的记录链表。

所以对一个数据页中的记录检索来说，因为一个数据页中的记录是有限的，且主键值是有序的，所以通过对所有记录进行分组，然后将组号（槽号）存储到页目录，使其起到索引作用，通过二分查找的方法快速检索到记录在哪个分组，来降低检索的时间复杂度。

B+ 树如何进行查询

当我们需要存储大量的记录时，就需要多个数据页，这时我们就需要考虑如何建立合适的索引，才能方便定位记录所在的页。
InnoDB 采用了 B+ 树作为索引底层结构，InnoDB 里 B+ 树中的每个节点都是一个数据页。

只有叶子节点才存放了数据，非叶子节点仅用来存放目录项作为索引。所有节点按照索引键大小排序，构成一个双向链表，便于范围查询；

比如要查找6
从根节点开始，通过二分法快速定位到符合页内范围包含查询值的页，因为查询的主键值为 6，在[1, 7)范围之间，所以到页 30 中查找更详细的目录项；
在非叶子节点（页30）中，继续通过二分法快速定位到符合页内范围包含查询值的页，主键值大于 5，所以就到叶子节点（页16）查找记录；
接着，在叶子节点（页16）中，通过槽查找记录时，使用二分法快速定位要查询的记录在哪个槽（哪个记录分组），定位到槽后，再遍历槽内的所有记录，找到主键为 6 的记录。

可以看到，在定位记录所在哪一个页时，也是通过二分法快速定位到包含该记录的页。定位到该页后，又会在该页内进行二分法快速定位记录所在的分组（槽号），最后在分组内进行遍历查找。

聚簇索引和二级索引

为什么MySQL要采用B+树作为索引底层结构呢？

首先，我讲一下B+树的前世今生。
我们都知道MySQL 的数据是需要持久化到磁盘上的。但是计算机从磁盘中读取数据的速度比较慢，当我们通过索引查找某行数据的时候，就需要先从磁盘读取索引到内存，再通过索引从磁盘中找到对应的数据然后读入到内存里，那么这个过程会发生多次磁盘 I/O，所消耗的时间也就比较大。所以，我们希望索引的数据结构能在尽可能少的磁盘的 I/O 操作中完成查询工作。所有我们希望这个查询的过程即要少的磁盘IO，又要高效执行范围查找。

1.0 线性查找

所以为了查找的高效率，我们在想索引数据按顺序排列，然后用二分查找法怎么样。因为二分查找每次都把查询的范围减半，这样时间复杂度就从挨个查找的n降到了 O(logn)。

2.0 二叉查找树

虽然线性排序使用起来比较简单，但是插入新元素的效率太低。因为每插入一个新元素，都要把这个元素之后的所有元素后移一位，如果这个操作发生在磁盘里面那这太夸张了，效率极低。而且每次查找都要不断计算中间的位置。所以我们想有没有一种非线形且适合二分查找的数据结构。

我们自然而然就想到了二叉查找树，二叉查找树的特点是每个节点左子树的所有节点都小于本节点，右子树的所有节点都大于本节点。这样我们在查询数据时，不需要计算中间节点的位置了，只需把要查找的数据和节点的数据进行比较就ok了。那这样不仅查找的效率变高了，在插入新节点时也不会像线性结构那样插入元素之后的元素都向后排列。

3.0 平衡二叉树

但二叉查找树就也不适合作为索引的数据结构，为什么呢，因为二叉查找树存在一个极端情况，如果每次插入的元素都是二叉查找树中最大的元素，那么这些元素就都会往右边排，这样二叉查找树就会退化成了一条链表，它查询的时间复杂度就又退化成了 O(n)，而且随着树的高度越来越高，每次查询数据的时候磁盘 IO 次数就会越来越多，导致查询的性能严重下降。

为了解决二叉查找树会在极端情况下退化成链表的问题，后面就有人提出平衡二叉树的概念。
它主要是在二叉查找树的基础上增加了一些条件约束：每个节点左右子树的高度差不能超过 1。如果插入了元素之后导致节点左右子树高度差超过了1，那就自己维持平衡。比如往1节点的右子树是2，现在需要插入3，正常情况插入3的话还是在2子树的右边，那这就不平衡了，所以需要调整，原本1是中间节点，现在把2作为中间节点，1和3分别为2的左右子树，类似于这样的操作来维持平衡二叉树的自平衡状态。这样的话查询操作的时间复杂度就会一直维持在 O(logn) 。

4.0 B 树

但不管平衡二叉树还是有一定的缺点，因为随着插入的元素的增多，树的高度也会逐渐变高，这就意味着磁盘 I/O 操作次数多，会影响整体数据查询的效率。根本原因是因为它还是一个二叉树，它的每个节点只能保存 2 个子节点。所以我们在想，能不能树的每个节点可以有多个子节点，那这样树的高度不就下来了吗。

由此就引出了B树的概念，它不再限制每个节点只能有 2 个子节点，而是允许每个节点有 M 个子节点，以此来降低树的高度。这个M 称为 B 树的阶，所以 B 树的话它就是一个多叉树。所以相同数量的节点如果在B树的场景下，树的高度就会低很多，对应的磁盘 I/O 次数也会减少。

B+ 树

但是使用B树作为MySQL索引的结构又有了一些问题， 因为B 树的每个节点都会包含索引+记录，如果用户的记录数据的大小远远超过了索引数据的大小，那我们在查找需要的数据时就不得不从这部分数据上走过，这就需要花费更多的磁盘 I/O 操作次数来读到「有用的索引数据」。而且，当我们查询位于底层的某个节点时，其他不需要的记录数据也会从磁盘加载到内存，这样不仅增多磁盘 I/O 操作次数，也占用内存资源。

所以由此，最终的B+树概念就提出来了，B+树是对B树结构的一些改造，B+树只在叶子节点来存放实际数据索引+记录，而非叶子节点只会存放索引；所有索引也都会在叶子节点里保存一份，叶子节点之间构成一个有序链表；非叶子节点的索引也会同时存在在子节点中，并且是在子节点中所有索引的最大（或最小）。非叶子节点中有多少个子节点，就有多少个索引；
因为B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，所以在数据量相同的情况下，相比于既存索引又存记录的 B 树结构，B+树的非叶子节点可以存放更多的索引，所以 B+ 树的高度可以比 B 树更低，查询底层节点的磁盘 I/O次数会更少。而且在删除节点的时候，B+树可以直接从叶子节点中删除，这样删除非常快。
而且B+ 树所有叶子节点间还有一个链表进行连接，这种设计在进行范围查找时会更快，比如我们查 1-1 到 1-10 之间的订单，这个时候可以先查找到 1-1 所在的叶子节点，然后利用链表向右遍历，直到找到 1-10的节点，这样就不需要从根节点查询了，进一步节省查询需要的时间。

InnoDB里的B+树

然后Innodb 里使用的 B+ 树有一些特别的点，比如：
B+ 树的叶子节点之间是用「双向链表」进行连接，这样的好处是既能向右遍历，也能向左遍历。
B+ 树点节点内容是数据页，数据页里存放了用户的记录以及各种信息，每个数据页默认大小是 16 KB。

Innodb 根据索引类型不同，分为聚集和二级索引。他们区别在于，聚集索引的叶子节点存放的是实际数据，所有完整的用户记录都存放在聚集索引的叶子节点，而二级索引的叶子节点存放的是主键值，而不是实际数据。
因为表的数据都是存放在聚集索引的叶子节点里，所以 InnoDB 存储引擎一定会为表创建一个聚集索引，且由于数据在物理上只会保存一份，所以聚簇索引只能有一个，而二级索引可以创建多个。

索引失效情况

对索引使用左、左右模糊查询

使用 like 关键字进行左查询或者左右模糊查询会导致索引失效。因为索引底层是 B+ 树，它是按照「索引值」有序排列存储的，所以只能根据前缀进行比较。如果使用 like ‘%XX’ 方式来查询，因为查询的结果可能是「AXX、BXX、CXX」等之类的，所以不知道从哪个索引值开始比较，于是就只能通过全表扫描的方式来查询。

对索引进行表达式计算

在查询条件中对索引进行表达式计算，是会使索引失效的。
select * from t_user where id + 1 = 10;因为索引保存的是索引字段的原始值，而不是 id + 1 表达式计算后的值，所以这样查询无法走索引。

对索引隐式类型转换

phone 字段，是二级索引且类型是 varchar。

select * from t_user where phone = 1300000001;	# 索引失效

select * from t_user where id = '1';	# 索引有效

因为MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。
第一个例子转换函数是作用在了 phone 字段，而 phone 字段是索引，也就是对索引使用了函数，对索引使用函数是会导致索引失效的（除非额外加了函数索引）。
第二个例子转换函数是作用在了输入参数，对索引字段并没有用任何函数，因此是可以走索引扫描的。

联合索引非最左匹配

联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配。
如果创建了一个 (a, b, c) 联合索引

where a=1；
where a=1 and b=2 and c=3；
where a=1 and b=2；
都可以

where c=3；
where b=2 and c=3；
都不行，因为在联合索引的情况下，数据是按照索引第一列排序，第一列数据相同时才会按照第二列排序

WHERE 中 OR前和OR后

在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。因为 OR 的含义就是两个只要满足一个即可，因此只有一个条件列是索引列是没有意义的，只要有条件列不是索引列，就会进行全表扫描。

覆盖索引

如果一个索引覆盖需要查询的所有字段，我们就称之为"覆盖索引"。

like “%x“，索引一定会失效吗

使用左模糊匹配 like “%xx” 并不一定会走全表扫描，关键还是看数据表中的字段。
如果数据库表中的字段只有主键+二级索引，那么即使使用了左模糊匹配，也不会走全表扫描（type=all），而是走全扫描二级索引树(type=index)。

联合索引的最左匹配和上面一样。

count(*) 和 count(1)区别

count() 是一个聚合函数，作用是统计符合查询条件的记录中，函数指定的参数不为 NULL 的记录有多少个。

count(*) 执行过程跟 count(1) 执行过程基本一样的。
当使用 count(*) 时，MySQL 会将 * 参数转化为参数 0 来处理。

select count(name) from t_order; #统计t_order 表中，name 字段不为 NULL 的记录有多少个
select count(1) from t_order;# 统计t_order 表中，1 这个表达式不为 NULL 的记录」有多少个
因为1永远都不是 NULL，所以上面这条语句，其实是在统计 t_order 表中有多少个记录。

count(1) 相比 count(主键字段) 少一个步骤，就是不需要读取记录中的字段值，因为它并不需要管这个值是不是NULL，所以通常会说 count(1) 执行效率会比 count(主键字段) 高一点。

count(1)、 count(*)、 count(主键字段)在执行的时候，如果表里存在二级索引，优化器就会选择二级索引进行扫描。

这是因为相同数量的二级索引记录可以比聚簇索引记录占用更少的存储空间，所以二级索引树比聚簇索引树小，这样遍历二级索引的 I/O 成本比遍历聚簇索引的 I/O 成本小，因此「优化器」优先选择的是二级索引。
所以，如果要执行 count(1)、 count(*)、 count(主键字段) 时，尽量在数据表上建立二级索引，这样效率会高一些。

事务特性

ACID
原子性Atomicity：一个事务中的所有操作，要么全部完成，要么全部不完成。事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过。
一致性Consistency：是指事务操作前和操作后，数据满足完整性约束，数据库保持一致性的状态。比如转账时转出账户钱少了多少，那转入账户的钱就要对应多出来多少。
隔离性Isolation：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致，因为多个事务同时使用相同的数据时，不会相互干扰，每个事务都有一个完整的数据空间，对其他并发事务是隔离的。
持久性Durability：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

并行事务会引发什么问题？

脏读

指如果一个事务「读到」了另一个未提交事务修改过的数据，就意味着发生了「脏读」现象。

A改了数据但是还没提交事务，B就能读到A改过的数据。如果这时A回滚了事务，那B读的数据就是过期数据，这就是脏读。

不可重复读

指在一个事务内多次读取同一个数据，如果出现前后两次读到的数据不一样的情况，就意味着发生了「不可重复读」现象。

A读了数据，B又改了数据，然后A再读，就会发现前后两次读到的数据是不一致的，这种现象就被称为不可重复读。
一个事务范围内两个相同的查询却返回了不同数据

幻读

大致的区别在于不可重复读是由于另一个事务对数据的更改所造成的，而幻读是由于另一个事务插入或删除引起的。
幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）

事务隔离级

读未提交隔离级别下，可能发生脏读、不可重复读和幻读现象；
读提交隔离级别下，可能发生不可重复读和幻读现象，但是不可能发生脏读现象；
可重复读隔离级别下，可能发生幻读现象，但是不可能发生脏读和不可重复读现象；
串行化隔离级别下，脏读、不可重复读和幻读现象都不可能会发生。

MySQL InnoDB 引擎的默认隔离级别是可重复读

可重复读中怎么解决幻读

针对快照读（普通 select 语句），是通过 MVCC多版本并发控制来解决了幻读的，因为在可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据保持一致，以此避免幻读问题。

针对当前读（select … for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select … for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。

MVCC

通过版本链来控制并发事务访问同一个记录时的行为就叫 MVCC。
在可重复读隔离级别中启动事务时会生成一个 Read View，类似于一个数据快照，然后整个事务期间都在用这个 Read View，这个快照有四个重要字段。

四个字段：
m_ids，在创建 Read View 时，当前数据库中活跃事务的事务 id 列表；
min_trx_id 指在创建 Read View 时，当前数据库中活跃事务id 最小的事务；
max_trx_id ：指创建 Read View 时当前数据库中应该给下一个事务的 id 值，即全局事务中最大的事务 id 值 + 1；
creator_trx_id ：指的是创建该 Read View 的事务的事务 id

聚簇索引记录的两个隐藏列：
对于使用 InnoDB 存储引擎的数据库表，它的聚簇索引记录中都包含两个隐藏列：
trx_id，当一个事务对某条聚簇索引记录进行改动时，就会把该事务的事务 id 记录在 trx_id 隐藏列里；
roll_pointer，每次对某条聚簇索引记录进行改动时，都会把旧版本的记录写入到 undo 日志中，这个隐藏列是个指针，指向每一个旧版本记录，于是就可以通过它找到修改前的记录。

当一个事务去访问记录的时候：
如果记录的 trx_id 值小于 Read View 中的 min_trx_id 值，表示这个版本的记录是在创建 Read View 前已经提交的事务生成的，所以该版本的记录对当前事务可见。
如果记录的 trx_id 值大于等于 Read View 中的 max_trx_id 值，表示这个版本的记录是在创建 Read View 后才启动的事务生成的，所以该版本的记录对当前事务不可见。
如果记录的 trx_id 值在 Read View 的 min_trx_id 和 max_trx_id 之间，若记录的 trx_id 在 m_ids 列表中，表示生成该版本记录的活跃事务依然活跃着（还没提交事务），所以该版本的记录对当前事务不可见。若记录的 trx_id 不在 m_ids列表中，表示生成该版本记录的活跃事务已经被提交，所以该版本的记录对当前事务可见。

这种通过「版本链」来控制并发事务访问同一个记录时的行为就叫 MVCC（多版本并发控制）。

MySQL中的锁

在 MySQL 里，根据加锁的范围，可以分为全局锁、表级锁和行锁三类。

全局锁

全局锁主要应用于做全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。

比如在全库逻辑备份期间，先备份了用户表数据，然后有用户买了商品，再备份商品表的数据。
这种情况下，备份的结果是用户表中这个用户的余额并没有扣除，反而商品表中该商品的库存被减少了，如果后面用这个备份文件恢复数据库数据的话，用户钱没少，而库存少了，等于用户白嫖了一件商品。
所以，在全库逻辑备份期间，加上全局锁，就不会出现上面这种情况了。

但是加上全局锁，意味着整个数据库都是只读状态。
那么如果数据库里有很多数据，备份就会花费很多的时间，关键是备份期间，业务只能读数据，而不能更新数据，这样会造成业务停滞。

表级锁

表锁除了会限制别的线程的读写外，也会限制本线程接下来的读写操作。尽量避免在使用 InnoDB 引擎的表使用表锁，因为表锁的颗粒度太大，会影响并发性能。表级锁不会出现死锁，发生锁冲突几率高，并发低。

元数据锁（MDL）

当我们对数据库表进行操作时，会自动给这个表加上 MDL。MDL 是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做了变更。MDL 在事务提交后才会释放，这意味着事务执行期间，MDL 是一直持有的。可能会出现线程A开启了事务但还未提交(加了MDL锁)，另一个修改表结构的线程会被阻塞，从而导致后续的大量select语句线程被阻塞的情况（因为写锁获取优先级高于读锁）。

意向锁
我们知道在读记录的时候引擎会对记录加共享锁，更新记录的时候会对记录加独占锁。而在InnoDB引擎中当对表里的记录加共享锁之前需要加一个意向共享锁，更新操作时 需要先对表加上意向独占锁，然后再对该记录加独占锁。
意向锁的目的是为了快速判断表里是否有记录被加锁。

如果没有「意向锁」，那么加「独占锁」时，就需要遍历表里所有记录，查看是否有记录存在独占锁，这样效率会很慢。
那么有了「意向锁」，由于在对记录加独占锁前，先会加上表级别的意向独占锁。然后再加「独占锁」时，会直接去查这个表有没有意向独占锁，如果有就意味着表里已经有记录被加了独占锁，这样就不用去遍历表里的记录了。

AUTO-INC 锁
我们知道一个表里的主键通常都会把它设置成自增的，之后在插入数据时，就可以不指定主键的值，数据库会自动给主键赋递增的值，这主要是通过 AUTO-INC 锁实现的。在插入数据时，会加一个表级别的 AUTO-INC 锁，然后给 AUTO_INCREMENT 修饰的字段赋值递增的值，等插入语句执行完成后，就会把 AUTO-INC 锁释放掉，而不是等到事务结束。

但是当一个事务在持有 AUTO-INC 锁的过程中，其他事务的如果要向该表插入语句都会被阻塞，从而保证插入数据时，被 AUTO_INCREMENT 修饰的字段的值是连续递增的。那这样就会影响插入性能，因为另一个事务中的插入会被阻塞。

后面从MySQL 5.1.2 版本开始，InnoDB 存储引擎提供了一种轻量级的锁来实现自增。
一样也是在插入数据的时候，会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁，给该字段赋值一个自增的值，然后就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。InnoDB 存储引擎提供了个 innodb_autoinc_lock_mode 的系统变量，是用来控制选择用 AUTO-INC 锁，还是轻量级的锁。

行级锁

InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁。不同隔离级别下，行级锁的种类是不同的。在读已提交隔离级别下，行级锁的种类只有记录锁（1）。在可重复读隔离级别下，行级锁的种类除了有记录锁，还有间隙锁（123）。行级锁会出现死锁，发生锁冲突几率低，并发高。

行级锁的类型主要有三类：
Record Lock，记录锁，锁住的是一条记录。分为有 S 锁和 X 锁，S锁之间兼容，但与X锁不兼容。X锁与S锁、X锁都不兼容。
Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身；只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。所谓的幻读是指在同一事务下，连续执行两次同样的查询语句，第二次的查询语句可能会返回之前不存在的行。比如表中有一个范围 id 为（3，5）间隙锁，那么其他事务就无法插入 id = 4 这条记录了，这样就有效的防止幻读现象的发生。间隙锁的意义只在于阻止区间被插入，间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁。
Next-Key Lock，临键锁，Record Lock + Gap Lock 的组合，前开后闭锁定一个范围，并且锁定记录本身。例如表中有一个范围 id 为（3，5] 的临键锁，那么其他事务即不能插入 id = 4 记录，也不能修改 id = 5 这条记录。所以，临键锁即能保护这条记录，又能阻止其他事务将新纪录插入到被保护记录前面的间隙中。虽然相同范围的间隙锁是多个事务相互兼容的，但对于记录锁，我们是要考虑 X 型与 S 型关系，X 型的记录锁与 X 型的记录锁是冲突的。

MySQLInnoDB引擎的行锁是通过索引加载的，也就是说，行锁是加在索引响应的行上的，要是对应的SQL语句没有走索引，则会全表扫描，行锁则无法实现，取而代之的是表锁，此时其它事务无法对当前表进行更新或插入操作。

InnoDB加行级锁

普通的 select 语句是不会对记录加锁的，因为它属于快照读，是通过 MVCC多版本并发控制实现的。
update 和 delete 操作都会加行级锁，且锁的类型都是独占锁(X型锁)。

为什么update没加索引会锁全表

InnoDB 存储引擎的默认事务隔离级别是「可重复读」，但是在这个隔离级别下，在多个事务并发的时候，可能会出现幻读的问题。
所以InnoDB 存储引擎自己实现了行锁，通过记录锁和间隙锁的组合来锁住记录本身和记录之间的“间隙”，防止其他事务在这个记录之间插入新的记录，从而避免幻读现象。
当我们执行 update 语句时，实际上是会对记录加独占锁（X锁），如果其他事务对持有独占锁的记录进行修改时就会被阻塞，直到事务结束后才会释放。

在 InnoDB 事务中，对记录加锁带基本单位是 next-key 锁，但是会因为一些条件会退化成间隙锁，或者记录锁。加锁的位置准确的说，锁是加在索引上的而非行上。

死锁的产生

两个事务先要查询该订单是否存在，不存在才插入记录。
for update语句，目的为了防止事务执行的过程中，有其他事务插入了记录，而出现幻读的问题。
select id from t_order where order_no = 1007 for update; 事务A，语句1
select id from t_order where order_no = 1008 for update; 事务B，语句2
Insert into t_order (order_no, create_date) values (1007, now()); 事务A，语句3
Insert into t_order (order_no, create_date) values (1008, now()); 事务B，语句4

语句1后，共加了两个锁，分别是表锁：X意向锁；行锁：X临键锁；

事务A在二级索引index_order上加的是X型的 next-key 锁，锁范围是(1006, +∞]

语句3后，因为事务A要向事务B生成的间隙锁中插入一条记录，所以事务 A 的插入操作生成了一个插入意向锁。

虽然插入意向锁名字里虽然有意向锁这三个字，但是它并不是意向锁，它属于行级锁，是一种特殊的间隙锁，只用于并发插入操作。两个事务却不能在同一时间内，一个拥有间隙锁，另一个拥有该间隙区间内的插入意向锁。
每插入一条新记录，都需要看一下待插入记录的下一条记录上是否已经被加了间隙锁，如果已加间隙锁，此时会生成一个插入意向锁，然后锁的状态设置为等待状态，现象就是 Insert 语句会被阻塞。

语句4后，当事务 B 往事务 A next-key 锁的范围 (1006, +∞] 里插入 id = 1008 的记录就会被锁住：因为需要在插入间隙上获取插入意向锁，而插入意向锁与间隙锁是冲突的。

事务 A 和事务 B 在执行完后 select … for update 语句后都持有范围为(1006,+∞]的next-key 锁，而接下来的插入操作为了获取到插入意向锁，都在等待对方事务的间隙锁释放，于是就造成了循环等待，导致死锁。

避免死锁

死锁的四个必要条件：互斥、占有且等待、不可强占用、循环等待。只要系统发生死锁，这些条件必然成立，但是只要破坏任意一个条件就死锁就不会成立。

设置事务等待锁的超时时间：当一个事务的等待时间超过该值后，就对这个事务进行回滚。
开启主动死锁检测：当检测到死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。innodb_deadlock_detect = on

Buffer Pool

MySQL 的数据都是存在磁盘中的，那么我们要更新一条记录的时候，得先要从磁盘读取该记录，然后在内存中修改这条记录。那修改完这条记录后会缓存起来，这样下次有查询语句命中了这条记录，直接读取缓存中的记录，就不需要从磁盘获取数据了。Innodb 存储引擎的缓冲池Buffer Pool就是这样来提高数据库的读写性能。

当读取数据时，如果数据存在于 Buffer Pool 中，客户端就会直接读取 Buffer Pool 中的数据，否则再去磁盘中读取。
当修改数据时，如果数据存在于 Buffer Pool 中，那直接修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页（因为这个页的内存数据和磁盘上的数据已经不一致），为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。

在 MySQL 启动的时候，InnoDB 会为 Buffer Pool 申请一片连续的内存空间，然后按照默认的16KB的大小划分出一个个的页， Buffer Pool 中的页就叫做缓存页。

WAL （Write-Ahead Logging）技术

Buffer Pool 虽然提高了读写效率，但是有个问题Buffer Pool 是基于内存的，如果断电重启，还没来得及落盘的脏页数据就会丢失。
为了防止断电导致数据丢失的问题，当有一条记录需要更新的时候，InnoDB 引擎就会先更新内存（同时标记为脏页），然后将本次对这个页的修改以 redo log 的形式记录下来，这个时候更新就算完成了。
之后InnoDB 引擎会把缓存在 Buffer Pool 的脏页刷新到磁盘里，这就是 WAL技术，先写日志，然后在合适的时间再写到磁盘上。

日志

undo log 回滚日志：是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC多版本并发控制。

在每个事务的执行过程中，都记录下回滚时需要的信息到一个日志里，那么在事务执行中途发生了 MySQL 崩溃后，我们可以通过这个日志回滚到事务之前的数据。

redo log 重做日志：是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于断电等故障恢复；

redo log 它记录了某个数据页做了什么修改，比如对表空间A中的B数据页C偏移量的地方做了更新，每当执行一个事务就会产生这样的一条或者多条物理日志。
在事务提交时，会先把 redo log 持久化到磁盘。当系统崩溃 MySQL 重启后，可以根据 redo log 的内容，把所有数据恢复到最新的状态。

undo log 记录了此次事务「开始前」的数据状态，记录的是更新之前的值；
redo log 记录了此次事务「完成后」的数据状态，记录的是更新之后的值；

binlog 归档日志：是 Server 层生成的日志，主要用于数据备份和主从复制；

MySQL 在完成一条更新操作后，Server 层还会生成一条 binlog，等之后事务提交的时候，会将该事务执行过程中产生的所有 binlog 统一写 入 binlog 文件。
binlog 文件是记录了所有数据库表结构变更和表数据修改的日志，不会记录查询类的操作，比如 SELECT 和 SHOW 操作。

binlog 是 MySQL 的 Server 层实现的日志，所有存储引擎都可以使用；
redo log 是 Innodb 存储引擎实现的日志；

binlog 用于备份恢复、主从复制；
redo log 用于断电等故障恢复。

主从复制

MySQL 的主从复制依赖于 binlog ，也就是记录 MySQL 上的所有变化并以二进制形式保存在磁盘上。复制的过程就是将 binlog 中的数据从主库传输到从库上。

MySQL 集群的主从复制过程梳理成 3 个阶段：
写入 Binlog：主库写 binlog 日志，提交事务，并更新本地存储数据。MySQL 主库在收到客户端提交事务的请求之后，会先写入 binlog，再提交事务，更新存储引擎中的数据，事务提交完成后，返回给客户端“操作成功”的响应。
同步 Binlog：把 binlog 复制到所有从库上，每个从库把 binlog 写到暂存日志中。从库会创建一个专门的 I/O 线程，连接主库的 log dump 线程，来接收主库的 binlog 日志，再把 binlog 信息写入 relay log 的中继日志里，再返回给主库“复制成功”的响应。
回放 Binlog：回放 binlog，并更新存储引擎中的数据。从库会创建一个用于回放 binlog 的线程，去读 relay log 中继日志，然后回放 binlog 更新存储引擎中的数据，最终实现主从的数据一致性。

在完成主从复制之后，你就可以在写数据时只写主库，在读数据时只读从库，这样即使写请求会锁表或者锁记录，也不会影响读请求的执行。

MySQL优化

1、创建合适的索引
2、写高效率的SQL，避免使用 Select *，列出需要查询的字段
3、选择合适的字段属性，例如char(255)改成char(1)
4、MySQL库读写分离

Drop、Delete与Truncate

Drop、Delete、Truncate都表示删除：

Delete用来删除表中的记录，执行delete之后，需要提交事务才能生效。如果有相应的触发器，执行时将被触发。

Truncate用来清空表中的所有记录，执行后会立即生效，也不会触发这个表上的触发器，TRUNCATE比delete更快，占用的空间更小。

Drop一般用来从数据库中删除表、视图等，同时表中的所有记录、索引等也会被删除。

视图

视图是一个”虚表”，用大白话说，就是从已经存在的表中，挑选出来一部分字段或数据，组成另一张”并不存在的表”,这张虚表被称之”视图”。视图中的字段与对应的数据均来自已经存在的表。对于视图来说，这些已经存在的表就被称为”基表”。视图不能被索引。视图主要用于简化检索，保护数据，并不用于更新。

游标

大白话就是[游动的标志]，它就类似于一个指针，我们正常使用select查询时，数据库会一次性把查询到的结果集打印到屏幕上，我们也不能做其他操作。而开启游标功能后再去执行查询语句，系统会把得到的结果集先保存起来，然后提供一个游标接口，当我们需要数据的时候就借助游标去一行行取数据，每取一条记录游标指针就往后移动一次，直到取完全部数据。这样通过使用游标就可以对查询出来的结果集作为一个单元来有效的处理。

InnoDB 与 MyISAM

1）事务：InnoDB支持事务，MyISAM不支持事务 。
2）锁级别： InnoDB 最小粒度支持行级锁，MyISAM 最小粒度支持表级锁。
3）索引：InnoDB主键索引采用聚集索引，B+树叶子存储数据；MyISAM采用非聚集索引，B+树叶子存储指向数据文件的指针。
4）外键：InnoDB支持外键，而MyISAM不支持。

适用场景：
1、InnoDB适合需要事务的场景，MyISAM适合不需要事务的场景；
2、读查询比较多的可以考虑MyISAM，如果既有读也有写使用InnoDB。
3、系统奔溃后，MyISAM恢复起来更困难；
4、如果需要使用外键，那这两个表必须是InnoDB表，因为MyISAM表暂时不支持外键。

分库分表

分库分表主要是为了解决由于数据量过大而导致数据库性能降低的问题，分库就是把原来独立的数据库拆分成若干个数据库，分表就是把一个数据大表拆分成若干数据表，这样使单一的数据库和单一的数据表中的数据量变小，从而达到提升数据库性能的目的。
分库分表通常从垂直和水平两个方向来进行。

垂直方向、针对业务
垂直分库：把原库里的表按照业务分类划分到不同的数据库里面，核心理念是专库专用，解决业务层面的耦合。
垂直分表：把一个表中的字段按照业务或者调用频率的不同划分为多个表。通过分表把核心数据和非核心数据分开，让表的结构更清晰，职责更单一，更便于维护。

水平方向、针对数据
水平分库：把同一个表的数据按一定规则拆到不同的数据库中，每个库可以放在不同的服务器上。解决单库数据量大，磁盘IO的性能瓶颈问题。
水平分表：在同一个数据库内，把同一个表的数据按一定规则拆到多个表中。优化单一表数据量过大而产生的性能问题；

数据库三大范式

第一范式
所谓第一范式是指数据库表的每一列都是不可分割的基本数据项，也就是实体中的某个属性不能有多个值或者不能有重复的属性，不满足第一范式那就不是关系型数据库。简而言之，第一范式就是无重复的列。

第二范式
满足第二范式必须先满足第一范式，第二范式要求数据库表中的每行必须可以被唯一地区分。这个唯一区分的方法通常是给表加上一个存储唯一标识的列，其实也就是主键。简而言之，第二范式就是有主键，非主键字段依赖主键。

第三范式
满足第三范式必须先满足第二范式。第三范式要求一个在数据库表中不能包含其它表中的非主关键字。

例如，在一个部门信息表中，有部门编号、部门名称等信息，部门编号是主键。那么在另一个员工信息表中，最多只能再加入部门编号，而不能再加入部门名称等字段，否则就会有大量的数据冗余。

简而言之，第三范式就是属性不依赖于其它非主属性。

NOW()和CURRENT_DATE()

NOW（）命令用于显示当前年份，月份，日期，小时，分钟和秒。
CURRENT_DATE（）仅显示当前年份，月份和日期。

char 和 varchar

1、char是定长的，不足的部分会用空格填充；varchar是不定长的，更灵活一些。
2、char类型来说，最多只能存放的字符个数为255，和编码无关；MySQL行默认最大是65535字节，所有列相加要小于这个值，所以VARCHAR的最大值受此限制。当只有这一列且字符串中的字符都只占1个字节时，varchar能达到大约65532个字符，因为允许NULL时有NULL标识列会占1字节数，记录长度的标识大约占1到2个字节。
3、char的存取效率会比varchar高一些。char类型在存放数据的时候已经确认了列的长度，不够长的会默认添加空格，所有MySQL在查询数据的时候，不需要再计算这个数据段的长度直接去找就行了；而varchar在每个数据段开头都要预留空间来存放变长数据段的长度，但是char却不用，所以MySQL对varchar类型的数据读取时间会更长。

内连接与外连接

内连接：指连接执行后结果仅包含符合连接条件的行。

内连接也就是最常见的等值连接，返回两张表都满足条件的记录。

外连接：连接结果既包含符合连接条件的行，也包含不符合条件的行，主要看是左外连接、右外连接还是全外连接。

左外连接 left outer join 或者 left join
左外连接就是在等值连接的基础上加上左表中的未匹配数据。

右外连接 right outer join 或者 right join
右外连接就是在等值连接的基础上加上右表中的未匹配数据。

全外连接 full outer join 或者 full join
全外连接是在等值连接的基础上将左表和右表的未匹配数据都加上。

交叉连接 / 笛卡尓积：它会将左右两张需要连接的表进行组合。例如，左表有200行，右表有100行，则交叉连接的结果有200*100行，即2000行。

本文标签： 笔记mysql