目录
datediff(日期1，日期2) 返回两个日期相差时间 （日期1 - 日期2）
count( distinct XXX) 可以在分完组的表格里对xxx进行去重个数统计
limit m, n 用于提取从下标为m（mysql的下标从0开始）开始在内的n条数据
表的内连接与外连接
辨析表的内连接与外连接
内连接：指连接结果仅包含符合连接条件的行，参与连接的两个表都应该符合连接条件。
外连接：连接结果不仅包含符合连接条件的行，同时也包含自身不符合条件的行。包括左外连接、右外连接和全外连接。
- 外连接就是在内连接的基础上加上主表中的未匹配数据，未匹配的部分填充NULL
- 左（右）外连接就是以左（右）表为准，去匹配右（右）表，左（右）表有多少条数据，结果就是多少条数据
- 全外连接是在内连接的基础上将左表和右表的未匹配数据都加上。
两种连接的代码比较
上图是内连接，只会将两表中都符合条件的行拼凑起来
上图是外连接，会以join左边的表为准，左边表的数据会全部显示，而右边表中如果有符合on后面条件的语句，就将其行的值与左表对应的行拼起来，否则就填充NULL
UNION与UNION ALL语句
union (或称为联合)的作用是将多个结果（如select）合并在一起显示出来。
union 和 union all 的区别是，union 会剔除多个结果集合中的重复结果，而 union all 则将所有的结果全部显示出来，不管是不是重复。
select ename,job from emp where job = 'MANAGER'
union
select ename,job from emp where job = 'SALESMAN';
注意：UNION 和 UNION ALL 内部的 SELECT 语句必须拥有相同数量的列
CASE语句
GROUP BY 语句
group by 语句是用来将表格 分成行数更小的多个表格 之后执行min count max等函数就不用将某列上的从上到下所有元素都包括进来，而是可以选取某列上某部分几行来计算。也可以这么说：如果没有出现GROUP BY 就不能用了分组函数 min max count sum avg。因为这样的得到的结果往往是不正确的
NULL
对NULL的判断只能用is null 和 is not null，如果用 < > =的话，一律返回false
on与where
数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。 在使用left jion时，on和where条件的区别如下：
on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。
on条件判断语句在where之前执行
select语句顺序
select 字段from 表名where …….group by ……..having …….order by ……..
datediff(日期1，日期2) 返回两个日期相差时间 （日期1 - 日期2）
count( distinct XXX) 可以在分完组的表格里对xxx进行去重个数统计
limit m, n 用于提取从下标为m（mysql的下标从0开始）开始在内的n条数据
on where having 的区别
三者都是用来过滤数据，但是调用的时机不同。由上面MySQL的语句执行顺序可知
WHERE是在GROUP BY之前执行的，所以WHERE的后面是不能使用聚合函数来进行数据过滤的，只能使用FROM表里的字段来进行数据过滤；
HAVING是在GROUP BY之后执行的，那么这些数据就都已经分过组了的，可以使用聚合函数来进行数据的分组过滤。
也可以这么理解：where语句后面判断词段只能是表中原有的，而不能是后期通过各种函数生成的，而having后则可以跟上count（XX）等新的词段
至于WHERE与ON，ON常与JOIN语句连起来使用，ON支持左连接和右连接，WHERE是不支持的，WHERE里面只支持内连接。
注意：having 字段 是对分完组的的各组数据进行筛选，如果要删除的话，是删掉一整个分好的组，而不是针对组内数据的删除
ANY与ALL
any,all关键字必须与一个比较操作符一起使用。
any 可以与=、>、>=、结合起来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的任何一个数据。
all可以与=、>、>=、结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的所有数据。例如
select s1 from t1 where s1 > any (select s1 from t2);一个关键语句
select ifnull(sum(字段名),0) from table_name
写成下面这种方式则是错误的
select sum ( ifnull(字段名, 0) ) from table_name
count sum 在对含有NULL的列操作时
count函数
| 情况 | 结果 |
|---|---|
| 0行 | 0 |
| 多行，包含null值 | 0 |
| 多行 ，全是null值 | 0 |
count函数会自动忽略null值
sum函数
| 情况 | 结果 |
|---|---|
| 0行 | null |
| 多行，包含null值 | 不为null |
| 多行 ，全是null值 | null |
必须只少有一行不为null的数值，结果才不为null
至于avg ，min，max这三个函数在计算时会自动忽略NULL
关于COUNT（1），COUNT（*）等性能上的差别
count() 是一个聚合函数，函数的参数不仅可以是字段名，也可以是其他任意表达式，该函数作用是统计符合查询条件的记录中，函数指定的参数不为 NULL 的记录有多少个。
select count(1) from t_order;这条语句是统计 t_order 表中，1 这个表达式不为 NULL 的记录有多少个。1 这个表达式就是单纯数字，它永远都不是 NULL，所以上面这条语句，其实是在统计 t_order 表中有多少个记录。
count(*) 其实等于 count(0)，也就是说，使用 count(*) 时，MySQL 会将 * 参数转化为参数 0 来处理。所以，count(*) 执行过程跟 count(1) 执行过程基本一样的，性能没有什么差异。
当count()的参数是主键字段时，如下
//id 为主键值
select count(id) from t_order;如果表里只有主键索引，没有二级索引时，InnoDB 循环遍历聚簇索引，将读取到的记录返回给 server 层，然后读取记录中的 id 值，就会 id 值判断是否为 NULL，如果不为 NULL，就将 count 变量加 1。
但是，如果表里有二级索引时，InnoDB 循环遍历的对象就是二级索引。
这是因为相同数量的二级索引记录可以比聚簇索引记录占用更少的存储空间，所以二级索引树比聚簇索引树小，这样遍历二级索引的 I/O 成本比遍历聚簇索引的 I/O 成本小，因此「优化器」优先选择的是二级索引。
同样的，MySQL 会对 count(*) 和 count(1) 有优化，如果有多个二级索引的时候，优化器会使用key_len 最小的二级索引进行扫描。只有当没有二级索引的时候，才会采用主键索引来进行统计。
count(字段) 的执行效率相比前面的 count(1)、 count(*)、 count(主键字段) 执行效率是最差的。会采用全表扫描的方式来计数。
————————————————————
MySQL模型
MySQL整个查询执行过程，总的来说分为5个步骤：
-
客户端向MySQL服务器发送一条查询请求
-
服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段
-
服务器进行SQL解析、预处理、再由优化器生成对应的执行计划
-
MySQL根据执行计划，调用存储引擎的API来执行查询
-
将结果返回给客户端，同时缓存查询结果
InnoDB 和 MyISAM 的比较
1. 事务：MyISAM不支持事务，InnoDB支持事务；
2. 全文索引：MyISAM 支持全文索引，InnoDB 5.6 之前不支持全文索引；
3. 关于 count()：MyISAM会直接存储总行数，InnoDB 则不会，需要按行扫描。意思就是对于 select count() from table; 如果数据量大， MyISAM 会瞬间返回，而 InnoDB 则会一行行扫描；
4. 外键：MyISAM 不支持外键，InnoDB 支持外键；
5. 锁：MyISAM 只支持表锁，InnoDB 可以支持行锁。
什么时候使用MyISAM
如果是读多写少的项目,可以考虑使用MyISAM，MyISAM的特点就是快速读取。MYISAM索引和数据是分开的，而且其索引是压缩的，可以更好地利用内存。所以它的查询性能明显优于INNODB。压缩后的索引也能节约一些磁盘空间。MYISAM拥有全文索引的功能，这可以极大地优化LIKE查询的效率。此外如果需要频繁的统计计算，也可以选择使用MyISAM。
如果读写比较频繁，则优先考虑InnoDB。因为MyISAM不提供对事务的支持，也不支持行级锁和外键，所以当执行insert和update语句时会先锁定这个表，会导致效率降低。
索引模型
数据库的常见索引模型有hash表，有序数组和搜索树
hash表：哈希表是一种以键-值（key-value） 存储数据的结构
优点：新增记录时速度很快
缺点：无法用于排序与分组、只支持精确查找，无法用于部分查找和范围查找，需要一次性将数据都读取到内存中。
有序数组：按照索引递增的顺寻存在数组中
优点：有序数组在等值查询和范围查询场景中的性能都非常优秀
缺点：新增记录的成本较高
搜索树为什么不用二叉搜索树而用b树或b+树？
随着数据库中数据的增加，索引本身大小随之增加，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级。一棵几百万节点的二叉树的深度非常之大？如果将这么大深度的一颗二叉树放磁盘上，每读取一个节点，需要一次磁盘的I/O读取，整个查找的耗时显然是不能够接受的。所以将二叉树变为m叉树（多路搜索树）来减少深度是减少IO读取的一个有效办法。
InnoDB数据的存储
磁盘本身存取比主存慢很多，速度往往是主存的几百万分之一，为了尽量减少磁盘I/O，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存，预读的长度一般为页的整数倍。
页是计算机管理存储器的逻辑块，硬件及OS往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（许多OS中，页的大小通常为4K）。主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。
数据库的索引是保存到磁盘上的，因此当我们通过索引查找某行数据的时候，就需要先从磁盘读取索引到内存，再通过索引从磁盘中找到某行数据，然后读入到内存。
在 InnoDB 存储引擎中，也有页的概念，默认每页大小为16K，即每次从磁盘读取数据时，都是由起始地址顺序向后读4个页的大小，通常，InnoDB 索引的每个节点即为1页(16K)。
B树
通过增加子结点的数量和结点中的关键字的数量来减少树的深度，从而减少访问磁盘的次数。描述一颗B树时需要指定它的阶数，阶数表示了一个节点最多有多少个孩子节点，一般用字母m表示阶数
B树的每个节点都存有索引和数据，也就是对应的key和value。
优点：降低IO操作的次数。磁盘一次读取一页的磁盘数据，这时将节点大小设置为磁盘页的大小，也正因每个节点存储着非常多个关键字，树的深度就会非常的小。进而要执行的磁盘读取操作次数就会非常少，更多的是在内存中对读取进来的数据进行查找。
B+ 树：B树的变体（Innodb的索引模型）
B-tree 已经大大改进了树家族的性能，它把多个数据集中存储在一个节点中，本身就可能减少了 I/O 次数或者寻道次数。
但是仍然有一个致命的缺陷，那就是它的索引数据与业务绑定在一块，而业务数据的大小很有可能远远超过了索引数据，这会大大减小一次 I/O 有用数据的获取，间接的增加 I/O 次数去获取有用的索引数据。
B+树相比B树的优势：
- B+树的磁盘读写代价更低 ：由于非叶子节点不存储数据，同一盘块中存放索引的个数更多，相对来说IO读写次数也就降低了
- B+树查询效率更稳定：内部节点并不是最终指向文件内容的节点，所以任何关键字的索引必须走一条从根节点到叶子节点的路
- B+树更有利于对数据库的扫描
- B+ 树有大量的冗余节点（所有非叶子节点都是冗余索引），这些冗余索引让 B+ 树在插入、删除的效率都更高，比如删除根节点的时候，不会像 B 树那样会发生复杂的树的变化；
- B+ 树的叶子节点之间是用「双向链表」进行连接，这样的好处是既能向右遍历，也能向左遍历，有利于范围查询，而 B 树要实现范围查询，因此只能通过树的中序遍历来完成范围查询，这会涉及多个节点的磁盘 I/O 操作，而B+树可以在根节点通过双向链表进行扫描。
B+ 树中，非叶子节点只保存索引数据，叶子节点保存索引数据与业务数据。
B+树索引并不能找到一个给定键值的具体行，它找到的只是被查找数据行所在的页，接着数据库会把页读入到内存，再在内存中进行查找，最后得到要查找的数据。
索引类型
按物理存储分分成聚集索引和非聚集索引
索引按物理存储分可以分成聚集索引（聚簇索引）和非聚集索引（二级索引）。
通过聚集索引可以查到需要查找的数据， 而通过非聚集索引可以查到记录对应的主键值 ， 再使用主键的值通过聚集索引查找到需要的数据。 聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致。
在innodb中，不同的索引对应不同的B+树：
-
聚集索引的叶子节点存放的是实际数据，所有完整的用户记录都存放在聚集索引的叶子节点；
-
二级索引的叶子节点存放的是主键值，而不是实际数据。
InnoDB 在创建聚簇索引时，会根据不同的场景选择不同的列作为索引：
如果有主键，默认会使用主键作为聚簇索引的索引键；
如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键；
在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键；
一张表只能有一个聚簇索引，为了实现非主键字段的快速搜索，就引出了二级索引（非聚簇索引/辅助索引），它也是利用了 B+ 树的数据结构，但是二级索引的叶子节点存放的是主键值，不是实际数据。
每有一个非聚集索引字段被创建，就会产生一个对应的B+数，B+的非叶子节点存储着索引值，叶子节点则存储着主键索引的值，当通过索引值找到主键索引时，还会要回到主键索引树来查找真正的值（称为回表）。
不过，当待查询的数据是主键值时，因为只在二级索引就能查询到，不用再去聚簇索引查，这个过程就叫作「索引覆盖」，也就是只需要查一个 B+ 树就能找到数据。
按照功能分为普通索引，唯一性索引，主键索引，全文索引
普通索引就是最基础的索引，这种索引没有任何的约束作用，它存在的主要意义就是提高查询效率。普通索引创建方式如下，name 字段就是一个普通索引（括号外面的是索引名，里边的是索引的字段）。
CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(64) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;
唯一性索引则在普通索引的基础上增加了数据唯一性的约束，一张表中可以同时存在多个唯一性索引，唯一性索引创建方式如下，name字段是唯一性索引
CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(64) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;
主键索引则是在唯一性索引的基础上又增加了不为空的约束（换言之，添加了唯一性索引的字段，是可以包含 NULL 值的），一张表里最多只有一个主键索引，当然一个主键索引中可以包含多个字段。上面的PRIMARY KEY就是主键索引。
全文索引其实很少在 MySQL 中用，不多做介绍。
主键索引和聚簇索引的关系
聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。当主键索引存在时，聚簇索引就是主键索引，而当主键索引不存在时，聚簇索引会选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键
联合索引
联合索引指的是对一张表上的多个列创建一个索引。如下idx_a_b就是一个联合索引。
CREATE TABLE t(
a int,
b int,
primary key(a),
key idx_a_b(a,b)
)
使用联合索引时必须要满足最左前缀原则： 如果想使用联合索引，联合索引的最左边的列必须作为过滤条件，否则联合索引不会生效。这也就是说，联合索引其实拥有单列索引的作用。
如果当前有联合索引（key1，key2），那么对key1进行查找时，也可以使用该联合索引，但对key2查找时就无法使用该联合索引，需要额外创建索引。
联合索引的另一个好处就是可以对第二个键值进行排序
例如有这样一个表
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`city` varchar(16) NOT NULL,
`name` varchar(16) NOT NULL,
`age` int(11) NOT NULL,
`addr` varchar(128) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `city` (`city`)
) ENGINE=InnoDB;要找出城市是“杭州”的所有人名字，并且按照姓名排序返回前 1000 个人的姓名、年龄。
select city,name,age from t where city='杭州' order by name limit 1000;我们可以在这个市民表上创建一个 city 和 name 的联合索引：
alter table t add index city_user(city, name);索引结构图如下：
在这个索引里面，我们依然可以用树搜索的方式定位到第一个满足 city='杭州’的记录，并且额外确保了，接下来按顺序取“下一条记录”的遍历过程中，只要 city 的值是杭州，name 的值就一定是有序的。这样就可以直接进行输出而不用排序。
进一步的，还可以使用覆盖索引，创建一个 city、name 和 age 的联合索引：
alter table t add index city_user_age(city, name, age);
此时只需要查询该索引的B+树即可，不需要再进行回表操作，少了一次遍历聚集索引树的过程。
但是覆盖索引有维护的代价，使用时要综合考虑
前缀索引
前缀索引是指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。
使用前缀索引的目的是为了减少索引占用的存储空间，提升查询效率。在创建表时，创建前缀索引的方式如下：
//建表时创建
CREATE TABLE table_name(column_list, INDEX(column_name(length)));
//建表后创建
CREATE INDEX index_name ON table_name(column_name(length)); 什么时候使用索引
索引最大的好处是提高查询速度，但是索引也是有缺点的，比如：
需要占用物理空间，数量越大，占用空间越大；
创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增大；
会降低表的增删改的效率，因为每次增删改索引，都需要进行动态维护
什么时候适用索引？
字段有唯一性限制的，比如商品编码；
经常用于 WHERE 查询条件的字段；
经常用于 GROUP BY 和 ORDER BY 的字段；
什么时候不需要创建索引？
WHERE 条件，GROUP BY，ORDER BY 里用不到的字段，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的。
字段中存在大量重复数据，不需要创建索引，比如性别字段，只有男女。
表数据太少的时候，不需要创建索引；
经常更新的字段不用创建索引，比如电商项目的用户余额，因为索引字段频繁修改，那就意味着需要频繁的重建索引；
索引的注意点（索引失效情况）
索引字段如果做了函数运算，那么索引将失效。
比如有时间字段t_modified存储日期，如果在where查询语句中使用month（t_modified）如
mysql> select count(*) from tradelog where month(t_modified)=7;那么该字段上的索引将失效。B+ 树提供的这个快速定位能力，来源于同一层兄弟节点的有序性。对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。
为了能够用上索引的快速定位能力，我们就要把 SQL 语句改成基于字段本身的范围查询。
此外类似于下面的对索引进行运算符计算语句也不会采用索引机制
select * from tradelog where id + 1 = 10000 应该改为
select * from tradelog where id = 10000 - 1%词语%–模糊查询也不会走索引
SELECT * FROM 'manong' WHERE `uname` LIKE '码农%' -- 走索引
SELECT * FROM 'manong' WHERE `uname` LIKE '%码农%' -- 不走索引 字符串与数字比较不走索引（涉及到字符串与数字转换）
如果有字段tradeid是字符串，那么下面的语句也不会采用该字段上的索引，因为语句会把tradeid转变为int型数据，但是tradeid在B+树中的排序方式是按照字符串大小来排列的，并不适用于int型数据。
mysql> select * from tradelog where tradeid=110717;
应该尽量避免在 where 子句中使用 != 或 not in 或 <> 操作符，尽量避免使用 or 来连接条件。 因为这几个操作符都会导致索引失效而进行全表扫描。
select * from news where id = 1 or id = 2 //索引失效MySQL 内部优化器会对 SQL 语句进行优化，如果优化器估计使用全表扫描要比使用索引快，则不使用索引。
索引语句的查看
使用Explain命令来查看语句的执行计划，MySQL在执行某个语句之前，会将该语句过一遍查询优化器，之后会拿到对语句的分析，也就是执行计划，其中包含了许多信息。可以通过其中和索引有关的信息来分析是否命中了索引
例如: possilbe_ key，key，key__len 等字段，分别说明了此语句可能会使用的索引、实际使用的索引以及使用的索引长度。
事务
事务特性（ACID）
- 原子性（Atomicity）原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
- 一致性（Consistency）事务前后数据的完整性必须保持一致。
- 隔离性（Isolation）事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
- 持久性（Durability）持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响
原子性和持久性是通过 redo log （重做日志）来保证的；
一致性是通过 undo log（回滚日志） 来保证的；
隔离性是通过 MVCC（多版本并发控制） 或锁机制来保证的；
并行事务会引发的问题
脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）
脏读
如果一个事务A读到了另一个未提交事务B修改过的数据，就意味着发生了脏读现象。
因为事务 B 是还没提交事务的，也就是A它随时可能发生回滚操作，如果在上面这种情况事务 B发生了回滚，那么事务 A 刚才得到的数据就是过期的数据，这种现象就被称为脏读。
不可重复读
在一个事务内多次读取同一个数据，如果出现前后两次读到的数据不一样的情况，就意味着发生了不可重复读现象。
幻读
在一个事务内多次查询某个符合查询条件的记录数量，如果出现前后两次查询到的记录数量不一样的情况，就意味着发生了幻读现象。幻读仅专指“新插入的行”，中途通过 update 更新数据而出现同一个事务前后两次查询的「结果集合」不一样，这种不算幻读。
事务隔离
级别包括：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable）。
- 读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
- 读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
- 可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。（MySQL InnoDB 引擎的默认隔离级别）
- 串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。
按隔离水平高低排序如下：
针对不同的隔离级别，并发事务时可能发生的现象也会不同
所以，要解决脏读现象，就要升级到「读提交」以上的隔离级别；要解决不可重复读现象，就要升级到「可重复读」的隔离级别。
不过，要解决幻读现象不建议将隔离级别升级到「串行化」，因为这样会导致数据库在并发事务时性能很差。InnoDB 引擎的默认隔离级别虽然是「可重复读」，但是它通过next-key lock 锁（行锁和间隙锁的组合）来锁住记录之间的“间隙”和记录本身，防止其他事务在这个记录之间插入新的记录，这样就避免了幻读现象。
MVCC
对于「读提交」和「可重复读」隔离级别的事务来说，它们是通过Read View来实现的，它们的区别在于创建 Read View 的时机不同：
-
「读提交」隔离级别是在每个 select 都会生成一个新的 Read View，也意味着，事务期间的多次读取同一条数据，前后两次读的数据可能会出现不一致，因为可能这期间另外一个事务修改了该记录，并提交了事务。
-
「可重复读」隔离级别是启动事务时生成一个 Read View，然后整个事务期间都在用这个 Read View，这样就保证了在事务期间读到的数据都是事务启动前的记录。
这两个隔离级别实现是通过「事务的 Read View 里的字段」和「记录中的两个隐藏列」的比对，来控制并发事务访问同一个记录时的行为，这就叫 MVCC（多版本并发控制）。
锁
全局锁
要使用全局锁，则要执行这条命：
flush tables with read lock执行后，整个数据库就处于只读状态了，这时其他线程执行以下操作，都会被阻塞：
-
对数据的增删查改操作，比如 select、insert、delete、update等语句；
-
对表结构的更改操作，比如 alter table、drop table 等语句。
如果要释放全局锁，则要退出会话或执行这条命令：
unlock tables全局锁主要应用于做全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。
但是加上全局锁，意味着整个数据库都是只读状态。如果数据库里有很多数据，备份就会花费很多的时间，而备份期间业务只能读数据，而不能更新数据，这样会造成业务停滞。
解决办法
如果数据库的引擎支持的事务支持可重复读的隔离级别，那么在备份数据库之前先开启事务，会先创建 Read View，然后整个事务执行期间都在用这个 Read View，而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作。
因为在可重复读的隔离级别下，即使其他事务更新了表的数据，也不会影响备份数据库时的 Read View，这就是事务四大特性中的隔离性，这样备份期间备份的数据一直是在开启事务时的数据。
InnoDB 存储引擎默认的事务隔离级别正是可重复读，因此可以采用这种方式来备份数据库。但是，对于 MyISAM 这种不支持事务的引擎，在备份数据库时就要使用全局锁的方法。
表级锁
MySQL 里面表级别的锁有这几种：
-
表锁；
-
元数据锁（MDL）;
-
意向锁；
-
AUTO-INC 锁；
1.表锁
如果想对学生表（t_student）加表锁，可以使用下面的命令：
//表级别的共享锁，也就是读锁；
lock tables t_student read;
//表级别的独占锁，也就是写锁；
lock tables t_stuent wirte;需要注意的是，表锁除了会限制别的线程的读写外，也会限制本线程接下来的读写操作。
也就是说如果本线程对学生表加了「共享表锁」，那么本线程接下来如果要对学生表执行写操作的语句，是会被阻塞的，当然其他线程对学生表进行写操作时也会被阻塞，直到锁被释放。
要释放表锁，可以退出会话或使用下面这条命令
unlock tables不过尽量避免在使用 InnoDB 引擎的表使用表锁，因为表锁的颗粒度太大，会影响并发性能，InnoDB 性能提高的地方在于实现了颗粒度更细的行级锁。
2.元数据锁（MDL）
MDL不需要显示的使用 ，因为当我们对数据库表进行操作时，会自动给这个表加上 MDL：
-
对一张表进行 CRUD 操作（增加(Create)、检索(Retrieve)、更新(Update)和删除(Delete)）时，加的是 MDL 读锁；
-
对一张表做结构变更操作的时候，加的是 MDL 写锁；
MDL 是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做了变更。
当有线程在执行 select 语句（ 加 MDL 读锁）的期间，如果有其他线程要更改该表的结构（ 申请 MDL 写锁），那么将会被阻塞，直到执行完 select 语句（ 释放 MDL 读锁）。
反之，当有线程对表结构进行变更（ 加 MDL 写锁）的期间，如果有其他线程执行了 CRUD 操作（ 申请 MDL 读锁），那么就会被阻塞，直到表结构变更完成（ 释放 MDL 写锁）。
MDL释放的时机
MDL 是在事务提交后才会释放，这意味着事务执行期间，MDL 是一直持有的。
如果数据库有一个长事务，那在对表结构做变更操作的时候，可能会发生意想不到的事情，比如下面这个顺序的场景：
首先，线程 A 先启用了事务（但是一直不提交），然后执行一条 select 语句，此时就先对该表加上 MDL 读锁；
然后，线程 B 也执行了同样的 select 语句，此时并不会阻塞，因为「读读」并不冲突；
接着，线程 C 修改了表字段，此时由于线程 A 的事务并没有提交，也就是 MDL 读锁还在占用着，这时线程 C 就无法申请到 MDL 写锁，就会被阻塞
那么在线程 C 阻塞后，后续有对该表的 select 语句，就都会被阻塞，如果此时有大量该表的 select 语句的请求到来，就会有大量的线程被阻塞住，这时数据库的线程很快就会爆满了。
这是因为申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现 MDL 写锁等待，会阻塞后续该表的所有 CRUD 操作。
所以为了能安全的对表结构进行变更，在对表结构变更前，先要看看数据库中的长事务，是否有事务已经对表加上了 MDL 读锁，如果可以考虑 kill 掉这个长事务，然后再做表结构的变更。
3.意向锁
-
在使用 InnoDB 引擎的表里对某些记录加上「共享锁」之前，需要先在表级别加上一个「意向共享锁」；
-
在使用 InnoDB 引擎的表里对某些纪录加上「独占锁」之前，需要先在表级别加上一个「意向独占锁」；
也就是，当执行插入、更新、删除操作，需要先对表加上「意向共享锁」，然后对该记录加独占锁。
表锁和行锁是满足读读共享、读写互斥、写写互斥的。如果没有「意向锁」，那么加「独占表锁」时，就需要遍历表里所有记录，查看是否有记录存在独占锁，这样效率会很慢。
那么有了「意向锁」，由于在对记录加独占锁前，先会加上表级别的意向独占锁，那么在加「独占表锁」时，直接查该表是否有意向独占锁，如果有就意味着表里已经有记录被加了独占锁，这样就不用去遍历表里的记录。
所以，意向锁的目的是为了快速判断表里是否有记录被加锁。
4.AUTO-INC 锁
在为某个字段声明 AUTO_INCREMENT 属性时，之后可以在插入数据时，可以不指定该字段的值，数据库会自动给该字段赋值递增的值，这主要是通过 AUTO-INC 锁实现的。
AUTO-INC 锁是特殊的表锁机制，锁不是再一个事务提交后才释放，而是再执行完插入语句后就会立即释放。
在插入数据时，会加一个表级别的 AUTO-INC 锁，然后为被 AUTO_INCREMENT 修饰的字段赋值递增的值，等插入语句执行完成后，才会把 AUTO-INC 锁释放掉。
那么，一个事务在持有 AUTO-INC 锁的过程中，其他事务的如果要向该表插入语句都会被阻塞，从而保证插入数据时，被 AUTO_INCREMENT 修饰的字段的值是连续递增的。
但是， AUTO-INC 锁再对大量数据进行插入的时候，会影响插入性能，因为另一个事务中的插入会被阻塞。
行级锁
InnoDB 引擎支持行级锁，而 MyISAM 引擎并不支持行级锁。
行级锁的类型主要有三类：
-
Record Lock，记录锁，也就是仅仅把一条记录锁上；
-
Gap Lock，间隙锁，锁的就是两个值之间的空隙，以防止其他事务在这个空隙间插入新的数据，但是不包含记录本身；
-
Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。
对记录加锁时，加锁的基本单位是 next-key lock，加锁的位置准确的说，锁是加在索引上的而非行上。它是由记录锁和间隙锁组合而成的，next-key lock 是前开后闭区间，而间隙锁是前开后开区间。
next-key锁是为了一定程度上解决可重复读级别下当前读的幻读问题而提出的
需要注意的是，next-key lock 锁的是索引，而不是数据本身，所以如果 update 语句的 where 条件没有用到索引列，那么就会全表扫描，在一行行扫描的过程中，不仅给行加上了行锁，还给行两边的空隙也加上了间隙锁，相当于锁住整个表，然后直到事务结束才会释放锁。
所以在线上千万不要执行没有带索引条件的 update 语句，不然会造成业务停滞
日志操作
MySQL有三种日志：
-
undo log（回滚日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC。
-
redo log（重做日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复；
-
binlog （归档日志）：是 Server 层生成的日志，主要用于数据备份和主从复制；
undo log
undo log 是一种用于撤销回退的日志，记录的是逻辑日志，每当 InnoDB 引擎对一条记录进行操作（修改、删除、新增）时，要把回滚时需要的信息都记录到 undo log 里。比如当 delete 一条记录时，undo log 中会记录一个对应的 insert 记录，反之亦然；当 update 时，undo log 会记录一个相反 update 记录。
在还没有提交事务之前，如果MySQL发生了崩溃，就可以通过undo log回滚到事务之前的数据
undo log 还有一个作用，通过 ReadView + undo log 实现 MVCC（多版本并发控制）。
undo log 为每条记录保存多份历史数据，MySQL 在执行快照读（普通 select 语句）的时候，会根据事务的 Read View 里的信息，顺着 undo log 的版本链找到满足其可见性的记录。
redo log
redo log 是物理日志，记录了某个数据页做了什么修改，对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新，每当执行一个事务就会产生这样的一条物理日志。
在事务提交时，只要先将 redo log 持久化到磁盘即可，可以不需要将缓存在 Buffer Pool 里的脏页数据持久化到磁盘。当系统崩溃时，虽然脏页数据没有持久化，但是 redo log 已经持久化，接着 MySQL 重启后，可以根据 redo log 的内容，将所有数据恢复到最新的状态。
有了 redo log，再通过 WAL 技术，InnoDB 就可以保证即使数据库发生异常重启，之前已提交的记录都不会丢失，这个能力称为 crash-safe（崩溃恢复）。可以看出来， redo log 保证了事务四大特性中的持久性。
WAL
WAL(Write Ahead Log)预写日志，是数据库系统中常见的一种手段，用于保证数据操作的原子性和持久性。
WAL 机制：修改并不直接写入到数据库文件中，而是写入到另外一个称为 WAL 的文件中；如果事务失败，WAL 中的记录会被忽略，撤销修改；如果事务成功，它将在随后的某个时间被写回到数据库文件中，提交修改。
一方面 WAL 中记录事务的更新内容，通过 WAL 将随机的脏页写入变成顺序的日志刷盘，另一方面，WAL 通过 buffer 的方式改单条磁盘刷入为缓冲批量刷盘，再者从 WAL 数据到最终数据的同步过程中可以采用并发同步的方式。这样极大提升数据库写入性能，因此，WAL 的写入能力决定了数据库整体性能的上限，尤其是在高并发时。
redo log 和 undo log 区别
redo log 记录了此次事务「完成后」的数据状态，记录的是更新之「后」的值；
undo log 记录了此次事务「开始前」的数据状态，记录的是更新之「前」的值；
事务提交之前发生了崩溃，重启后会通过 undo log 回滚事务，事务提交之后发生了崩溃，重启后会通过 redo log 恢复事务。
binlog
MySQL 在完成一条更新操作后，Server 层会生成一条 binlog，等之后事务提交的时候，会将该事物执行过程中产生的所有 binlog 统一写 入 binlog 文件
binlog 文件是记录了所有数据库表结构变更和表数据修改的日志，不会记录查询类的操作，比如 SELECT 和 SHOW 操作。
binlog 与 redo log区别
适用对象不同：
-
binlog 是 MySQL 的 Server 层实现的日志，所有存储引擎都可以使用；
-
redo log 是 Innodb 存储引擎实现的日志；
写入方式不同：
-
binlog 是追加写，写满一个文件，就创建一个新的文件继续写，不会覆盖以前的日志，保存的是全量的日志。
-
redo log 是循环写，日志空间大小是固定，全部写满就从头开始，保存未被刷入磁盘的脏页日志。
用途不同：
-
binlog 用于备份恢复、主从复制；
-
redo log 用于掉电等故障恢复。
如果数据库的数据被删除了，如何恢复数据
这种情况下，不可以使用 redo log 文件恢复，只能使用 binlog 文件恢复。
因为 redo log 文件是循环写，是会边写边擦除日志的，只记录未被刷入磁盘的数据的物理日志，已经刷入磁盘的数据都会从 redo log 文件里擦除。
binlog 文件保存的是全量的日志，也就是保存了所有数据变更的情况，理论上只要记录在 binlog 上的数据，都可以恢复，所以如果不小心整个数据库的数据被删除了，得用 binlog 文件恢复数据。
删除操作
1.truncate （可以理解为 drop + create）删除表中的内容，不删除表结构，释放空间；
2.delete：删除内容，不删除表结构，但不释放空间，只能作用于table
3.drop：drop语句将表所占用的空间全释放掉,可以作用于table和view
一般而言，drop > truncate > delete
具体解析
- DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。
- TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。
表和索引所占空间
- 当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小
- DELETE操作不会减少表或索引所占用的空间。
- drop语句将表所占用的空间全释放掉。
应用范围
- truncate 只能对table；delete可以是table和view
- truncate 和delete只删除数据，而drop则删除整个表（结构和数据）。
删除操作
- delete语句为DML（Data Manipulation Language)，这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 触发器，执行的时候将被触发。
- truncate、drop是DDL（Data Define Language)，操作立即生效，原数据不放到 rollback segment中，不能回滚
- Truncate table 速度快，而且效率高，使用的系统和事务日志资源少。delete 语句每次删除一行，并在事务日志中为所删除的每行记录一项。Truncate table 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
数据库的三大范式
-
第一范式：强调的是列的原子性，即数据库表的每一列都是不可分割的原子数据项；
-
第二范式：要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性；在第一范式的基础上，消除非主属性对键的部分依赖。
-
第三范式：任何非主属性不依赖于其它非主属性；在第二范式的基础上，消除非主属性对键的传递依赖
char与varchar区别
- char(n) :固定长度类型，比如:订阅char(10) ，当输入”abc" 三个字符的时候，它们占的空间还是10个字节，其他7个是空字节。char 优点：效率高，缺点：占用空间。适用场景：存储密码的md5值,，固定长度的，使用char非常合适。
- varchar(n) : 可变长度，存储的值是每个值占用的字节再加上一个用来记录其长度的字节的长度。
- char的存储方式是：对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节。varchar的存储方式是：对每个英文字符占用2个字节，汉字也占用2个字节。
所以，从空间。上考虑varcahr比较合适；从效率上考虑char比较合适，二者使用需要权衡。
Innodb为什么要用自增id作为主键？
如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。
如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置， 频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE（optimize table）来重建表并优化填充页面。
数据库为什么要分页分表
分库与分表的目的在于，减小数据库的单库单表负担，提高查询性能，缩短查询时间。
通过分表，可以减少数据库的单表负担，将压力分散到不同的表上，同时因为不同的表上的数据量少了，起到提高查询性能，缩短查询时间的作用。
此外，可以很大的缓解表锁的问题。 分表策略可以归纳为垂直拆分和水平拆分，
对于水平拆分，取模分表属于随机分表，时间维度分表属于连续分表。 对于海量用户场景，可以考虑取模分表，数据相对比较均匀，不容易出现热点和并发访问的瓶颈
对于垂直拆分，应该将不常用的字段单独拆分到另外一张扩展表；将大文本的字段单独拆分到另外一张扩展表；将不经常修改的字段放在同一张表中；将经常改变的字段放在另一张表中。
视图
视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询，不包含任何列或数据。
使用视图可以简化复杂的 sql 操作，隐藏具体的细节，保护数据；视图创建后，可以使用与表相同的方式利用它们。
视图不能被索引，也不能有关联的触发器或默认值，如果视图本身内有order by，则对视图再次order by将被覆盖。具体的使用：
creat view va as
select cust_name, cust_conctact, prod_id
from customers, orders, orderitems
where customers.cust_id = orders.cust_id
and orderitems.order_num = orders.order_num;创建出来之后，就可以使用了
select cust_name, cust_contact
from va
where prod_id = 'TNT2';主从复制/读写分离
MySql 主从同步概述
MySQL 主从同步，即 MySQL Replication，可以实现将数据从一台数据库服务器同步到多台数据库服务器。
MySQL 是基于磁盘文件的关系型数据库，我们常将 Redis 作为缓存与 MySQL 配合来使用，当有数据访问请求的时候，首先会从缓存中进行查找，如果存在就直接取出，如果不存在再访问数据库，这样就提升了读取的效率，也减少了后端数据库的访问压力。
当业务量不断增长，数据库的压力会不断变大，缓存的频繁变更也强依赖于数据的查询结果，导致数据查询效率低，负载很高，连接过多等问题。
对于电商场景来说，往往存在很多典型的读多写少场景，我们可以对 MySQL 做主从架构并且进行读写分离，让主服务器（Master）处理写请求，从服务器（Slave）处理读请求，这样可以进一步提升数据库的并发处理能力。结构如下图：
上图中，可以看到，增加了 2 个从库，这 2 个从库可以一起抗下大量的读请求，分担主库压力。从库会通过主从复制，从主库中不断的同步数据，以此来保证从库的数据和主库数据的一致。
主从同步的作用
一般来说，不是所有的系统都需要对数据库进行主从架构的设计，因为架构本身是有一定成本的。
如果我们的目的在于提升数据库高并发访问的效率，
- 那么我们首先应该优化 SQL 语句及索引，充分发挥数据库的最大性能；
- 其次是采用缓存的策略，如使用 Redis、Magodb 等缓存工具，通过其高性能的优势把数据保存在内存数据库中，提升读取的效率，
- 最后才是对数据库采用主从架构，进行读写分离。系统的使用和维护成本是根据架构的升级逐渐升高的。
主从同步不仅可以提升数据库的吞吐量，还有以下三个方面的作用：
读写分离
我们可以通过主从复制的方式来同步数据，然后通过读写分离提升数据库的并发处理能力。
简单来说就是我们的数据被放在了多个数据库中，其中一个是 Master 主库，其余的是 Slave 从库。当主库数据变化时，会自动将数据同步到从库中，而我们程序可以从从库读取数据，也就是采用读写分离的方式。
原本所有的读写压力都由一台服务器承担，现在有多个服务器共同处理读请求，减少了对主库的压力。另外还可以对从服务器进行负载均衡，让不同的读请求按照策略均匀的分配到不同的从服务器中，让读取更加顺畅。读取顺畅的另一个原因，就是减少了锁表的影响，比如我们让主库负责写，当主库出现写锁的时候，不会影响到从库的查询操作。
数据备份
主从同步也相当于是一种数据热备份机制，在主库正常运行下进行备份，不影响提供数据服务。
高可用性
数据备份实际就是一种冗余的机制，通过这种冗余的方式可以换取数据库的高可用性，当服务器出现故障、宕机等无可用的情况下，可以迅速进行故障切换，让从库充当主库，保障服务正常运行。
主从同步的原理
说到主从同步的原理，需要了解在数据库中的一个重要日志文件： Binlog 二进制文件，它记录了对数据库进行更新的事件，事实上主从同步的原理就是基于 Binlog 进行数据同步的。
在主从复制的过程中，会基于三个线程来操作，一个是 binlog dump 线程，位于 master 节点上，另外两个线程分别是 I/O 线程和 SQL 线程，它们都分别位于 Repica 节点上，如下图：
结合以上图片，我们一起来了解主从复制的核心流程：
- 当 master 节点接收到一个写请求时，这个写请求可能是增删改操作，此时会把写请求的更新操作Data Changes都记录到 binlog 日志 中。
- master 节点会把数据复制给 Repica 节点，这个过程，首先得要每个 slave 节点连接到 master 节点上，当 Repica 节点连接到 master 节点上时，master 节点会为每一个 Repica 节点分别创建一个 binlog dump 线程，用于向各个 Repica 节点发送 binlog 日志。
- binlog dump 线程会读取 master 节点上的 binlog 日志，然后将 binlog 日志 发送给 Repica 节点上的 I/O 线程。当主库读取事件的时候，会在 Binglog 上加锁，读取完成之后，再将锁释放掉。
- Repica 节点上的 I/O 线程接收到 binlog 日志 后，会将 binlog 日志 先写入到本地的 relaylog 中，relaylog 中就保存了 binlog 日志。
- Repica 节点上的 SQL 线程，会来读取 relaylog 中的 binlog 日志，将其解析成具体的增删改操作，把这些在 master 节点上进行过的操作，重新在 Repica 节点上也重做一遍，达到数据还原的效果，这样就可以保证 master 节点和 Repica 节点的数据一致性了。
主从同步的数据内容其实是二进制日志（Binlog），它虽然叫二进制日志，实际上存储的是一个又一个的事件（Event），这些事件分别对应着数据库的更新操作，比如 INSERT、UPDATE、DELETE 等。
需要注意的是，不是所有版本的 MySQL 都默认开启了服务器的二进制日志，在进行主从同步的时候，我们需要先检查服务器是否已经开启了二进制日志。
二进制日志，它是一个文件，在进行网络传输的过程中就一定会存在一些延迟，比如 200ms，这样就可能造成用户在从库上读取的数据不是最新的数据，也就会造成主从同步中的数据不一致的情况发生。比如我们对一条记录进行更新，这个操作是在主库上完成的，而在很短的时间内，比如 100ms，又对同一个记录进行读取，这时候从库还没有完成数据的同步，那么，我们通过从库读取到的数据就是一条旧的数据。
如何解决主从同步的数据一致性问题
如果我们想要操作的数据都存储在同一个数据库中，那么对数据进行更新的时候，可以对记录进行加写锁，这样在读取的时候就不会发生数据不一致的情况了。但这时从库的作用就是备份数据，没有做到读写分离，分担主库的压力。
因此我们还需要想办法，在进行读写分离的时候，解决主从同步中数据不一致的问题，也就是解决主从之间数据复制方式的问题，复制方式有以下几种。
全同步复制
全同步复制，就是当主库执行完一个事务之后，要求所有的从库也都必须执行完该事务，才可以返回处理结果给客户端。因此，虽然全同步复制数据一致性得到保证了，但是主库完成一个事物需要等待所有从库也完成，性能就比较低了。如下图：
异步复制
异步复制，就是当主库提交事物后，会通知 binlog dump 线程 发送 binlog 日志给从库，一旦 binlog dump 线程将 binlog 日志发送给从库之后，不需要等到从库也同步完成事务，主库就会将处理结果返回给客户端。
因为主库只管自己执行完事务，就可以将处理结果返回给客户端，而不用关心从库是否执行完事务，这就可能导致短暂的主从数据不一致的问题了，比如刚在主库插入的新数据，如果马上在从库查询，就可能查询不到。
而且，当主库提交事物后，如果宕机挂掉了，此时可能 binlog 还没来得及同步给从库，这时候如果为了恢复故障切换主从节点的话，就会出现数据丢失的问题，所以异步复制虽然性能高，但数据一致性上是最弱的。
mysql 主从复制，默认采用的就是异步复制这种复制策略。
半同步复制
MySQL5.5 版本之后开始支持半同步复制的方式。原理是在客户端提交 COMMIT 之后不直接将结果返回给客户端，而是等待至少有一个从库收到了 Binlog，并且写入到中继日志中，再返回给客户端。这样做的好处就是提高了数据的一致性，当然相比于异步复制来说，至少多增加了一个网络连接的延迟，降低了主库写的效率。
在 MySQL5.7 版本中还增加了一个 rpl_semi_sync_master_wait_for_slave_count 参数，我们可以对需要响应的从库数量进行设置，默认为 1，也就是说只要有一个从库进行了响应，就可以返回给客户端。如果将这个参数调大，可以提升数据一致性的强度，但也会增加主库等待从库响应的时间。
但是，半同步复制也存在以下几个问题：
- 半同步复制的性能，相比异步复制而言有所下降，相比于异步复制是不需要等待任何从库是否接收到数据的响应，而半同步复制则需要等待至少一个从库确认接收到 binlog 日志的响应，性能上是损耗更大的。
- 主库等待从库响应的最大时长是可以配置的，如果超过了配置的时间，半同步复制就会变成异步复制，那么，异步复制的问题同样也就会出现了。
- 在 MySQL 5.7.2 之前的版本中，半同步复制存在着幻读问题的。
当主库成功提交事物并处于等待从库确认的过程中，这个时候，从库都还没来得及返回处理结果给客户端，但因为主库存储引擎内部已经提交事务了，所以，其他客户端是可以到从主库中读到数据的。
但是，如果下一秒主库突然挂了，此时正好下一次请求过来，因为主库挂了，就只能把请求切换到从库中，因为从库还没从主库同步完数据，所以，从库中当然就读不到这条数据了，和上一秒读取数据的结果对比，就造成了幻读的现象了。
增强半同步复制
增强半同步复制，是 mysql 5.7.2 后的版本对半同步复制做的一个改进，原理上几乎是一样的，主要是解决幻读的问题。
主库配置了参数 rpl_semi_sync_master_wait_point = AFTER_SYNC 后，主库在存储引擎提交事务前，必须先收到从库数据同步完成的确认信息后，才能提交事务，以此来解决幻读问题。参考下图：
MYSQL 更新语句的执行过程
MYSQL 的体系结构
大体来说，MySQL 可以分为 客户端、Server 层和存储引擎层三大部分，如图所示。
Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。
更新 SQL 的执行
当我们执行一条更新 SQL 时是如何执行的呢，下面执行一条简单的 SQL 更新语句 (默认存储引擎 InnoDB)
update T set c=c+1 where ID=2;第一步：连接器
先通过连接器连接到这个数据库上。连接器负责跟客户端建立连接、校验用户名密码的正确性，同时获取该用户的权限放到缓存中、维持和管理连接
第二步：缓存
连接建立完成后，如果执行的是 SELECT 查询 语句会查询缓存中是否存在该 SQL 的结果集，如果存在结果则再校验用户表和数据的权限最终将查询到的结果返回。如果是 UPDATE，DELETE 等更新操作，那么跟这个表有关的查询缓存会置为失效，所以这条语句就会把表 T 上所有缓存结果都清空。
第三步：分析器
如果没有命中查询缓存，就要开始真正执行语句了。首先，MySQL 需要知道你要做什么，因此需要对 SQL 语句做解析。
分析器先会做 “词法分析”。你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。例如该语句中 c 列在表 T 中是否存在等。
做完了这些识别以后，就要做 “语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。该 SQL 语句中的 update、where 等是否符合 SQL 语法
第四步：优化器
经过了分析器，MySQL 就知道你要做什么了。在开始执行之前，还要先经过优化器的处理。优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序；优化器决定要使用 ID 这个索引。指定索引也就指定了后面的执行器需要调用存储引擎的哪个接口进行执行。
第五步：执行器
MySQL 通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误。执行器负责具体执行，找到这一行，然后更新。
查询缓存和Buffer Pool 的区别
- 查询缓存位于Server层，在执行查询语句时，MySQL Server首选会从查询缓存中查看是否曾经执行过这个SQL，如果曾经执行过的话，之前执行的查询结果会以Key-Value的形式保存在查询缓存中。key是SQL语句，value是查询结果。我们将这个过程称为查询缓存。但是，只要有一个sql update了该表，那么表的查询缓存就会失效。在mysql8.0的版本中，已经将查询缓存模块删除了
- Buffer Pool位于存储引擎层。Buffer Pool就是MySQL存储引擎为了加速数据的读取速度而设计的缓冲机制。Buffer Pool中的描述信息以双向链表（LRU）的形式组织在一起，通过数据页的描述信息，我们能找它所描述的缓存页的位置。
1021
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
