面试准备06-数据库

数据库笔记

MySQL | 标签 - 小马的笔记

MySQL(InnoDB剖析) - 这个专栏差不多就是把《MySQL技术内幕 InnoDB存储引擎》原书搬上来了
https://blog.csdn.net/qq_41453285/category_9684084_2.html

数据库设计

Chen 陈氏ER图

Chen 陈氏ER关系图由 Peter Chen（陈品山，出生于台湾的美国计算机科学家）提出：矩形表示实体，椭圆表示属性，菱形表示关系

下面是 DrawIO 的一个官方示例图，将 Crow’s Foot 中的连线也用到了陈氏 ER 图中：

Chen陈氏ER图

Entity Relationship Diagrams with draw.io
https://drawio-app.com/entity-relationship-diagrams-with-draw-io/

Crow’s Foot 鱼尾纹ER图

Crow’s Foot 鱼尾纹ER图，类似 UML 类图，每个实体用方框表示，上面写实体名，下面列出实体的属性，更简洁。实体之间通过可表示一对一或一对多关系的连线连接，这些连线看起来类似乌鸦脚，所以取名叫 Crow‘s Foot。

连线上靠近实体的三分叉表示多个，靠近实体的竖线表示1个，远离实体的空心圆圈表示0个或多个，远离实体的竖线表示1个或多个

Crow’s Foot ER 图连线说明：

Crow's Foot 鱼尾纹ER图连线说明

Crow’s Foot Notation
https://vertabelo.com/blog/crow-s-foot-notation/

SQL基础

sql查询执行流程

第一步:客户端把语句发给服务器端执行
第二步:语句解析
1）查询高速缓存（library cache）
服务器进程在接到客户端传送过来的SQL语句时，不会直接去数据库查询。服务器进程把这个SQL语句的字符转化为ASCII等效数字码，接着这个ASCII码被传递给一个HASH函数，并返回一个hash值，然后服务器进程将到shared pool中的library cache（高速缓存）中去查找是否存在相同的hash值。如果存在，服务器进程将使用这条语句已高速缓存在SHARED POOL的library cache中的已分析过的版本来执行，省去后续的解析工作，这便是软解析。若调整缓存中不存在，则需要进行后面的步骤，这便是硬解析。硬解析通常是昂贵的操作，大约占整个SQL执行的70%左右的时间，硬解析会生成执行树，执行计划，等等。

2）语句合法性检查（data dict cache）
当在高速缓存中找不到对应的SQL语句时，则服务器进程就会开始检查这条语句的合法性。这里主要是对SQL语句的语法进行检查，看看其是否合乎语法规则。如果服务器进程认为这条SQL语句不符合语法规则的时候，就会把这个错误信息反馈给客户端。在这个语法检查的过程中，不会对SQL语句中所包含的表名、列名等等进行检查，只是检查语法。

3）语言含义检查（data dict cache）
若SQL 语句符合语法上的定义的话，则服务器进程接下去会对语句中涉及的表、索引、视图等对象进行解析，并对照数据字典检查这些对象的名称以及相关结构，看看这些字段、表、视图等是否在数据库中。如果表名与列名不准确的话，则数据库会就会反馈错误信息给客户端。

4）获得对象解析锁（control structer）
当语法、语义都正确后，系统就会对我们需要查询的对象加锁。这主要是为了保障数据的一致性，防止我们在查询的过程中，其他用户对这个对象的结构发生改变。

5）数据访问权限的核对（data dict cache）
当语法、语义通过检查之后，客户端还不一定能够取得数据，服务器进程还会检查连接用户是否有这个数据访问的权限。若用户不具有数据访问权限的话，则客户端就不能够取得这些数据。要注意的是数据库服务器进程先检查语法与语义，然后才会检查访问权限。

6）确定最佳执行计划
当语法与语义都没有问题权限也匹配，服务器进程还是不会直接对数据库文件进行查询。服务器进程会根据一定的规则，对这条语句进行优化。在执行计划开发之前会有一步查询转换，如：视图合并、子查询解嵌套、谓语前推及物化视图重写查询等。为了确定采用哪个执行计划，Oracle还需要收集统计信息确定表的访问联结方法等，最终确定可能的最低成本的执行计划。

第三步：绑定变量赋值
如果SQL语句中使用了绑定变量，扫描绑定变量的声明，给绑定变量赋值，将变量值带入执行计划。若在解析的第一个步骤，SQL在高速缓冲中存在，则直接跳到该步骤。

第四步：语句执行
语句解析只是对SQL语句的语法进行解析，以确保服务器能够知道这条语句到底表达的是什么意思。等到语句解析完成之后，数据库服务器进程才会真正的执行这条SQL语句。

第五步：提取数据
当语句执行完成之后，查询到的数据还是在服务器进程中，还没有被传送到客户端的用户进程。所以，在服务器端的进程中，有一个专门负责数据提取的一段代码。他的作用就是把查询到的数据结果返回给用户端进程，从而完成整个查询动作。

Oracle SQL语句执行流程与顺序原理解析
http://www.ecdoer.com/post/oracle-sql-execution-order.html

步步深入：MySQL架构总览->查询执行流程->SQL解析顺序
https://www.cnblogs.com/annsshadow/p/5037667.html

sql语句执行分解

SQL 语言不同于其他编程语言的最明显特征是处理代码的顺序。在大多数据库语言中，代码按编码顺序被处理。但在 SQL 语句中，第一个被处理的子句式 FROM，而不是第一出现的 SELECT。
所有的查询语句都是从from开始执行的，在执行过程中，每个步骤都会为下一个步骤生成一个虚拟表，这个虚拟表将作为下一个执行步骤的输入。

SELECT a.customer_id, COUNT(b.order_id) as total_orders
FROM table1 AS a
LEFT JOIN table2 AS b
ON a.customer_id = b.customer_id
WHERE a.city = 'hangzhou'
GROUP BY a.customer_id
HAVING count(b.order_id) < 2
ORDER BY total_orders DESC;

执行顺序如下：

FROM：对FROM子句中的前两个表执行笛卡尔积（Cartesian product)(交叉联接），生成虚拟表VT1
from负责把数据库的表文件加载到内存中去
ON：对VT1应用ON筛选器。只有那些使join_condition为真的行才被插入VT2。
OUTER(JOIN)：如果指定了OUTER JOIN（相对于CROSS JOIN 或(INNER JOIN),保留表（preserved table：左外部联接把左表标记为保留表，右外部联接把右表标记为保留表，完全外部联接把两个表都标记为保留表）中未找到匹配的行将作为外部行添加到 VT2,生成VT3.如果FROM子句包含两个以上的表，则对上一个联接生成的结果表和下一个表重复执行步骤1到步骤3，直到处理完所有的表为止。
WHERE：对VT3应用WHERE筛选器。只有使where_condition为true的行才被插入VT4.
GROUP BY：按GROUP BY子句中的列列表对VT4中的行分组，生成VT5.
group by 会把临时表切分成若干临时表
CUBE|ROLLUP：把超组(Suppergroups)插入VT5,生成VT6.
HAVING：对VT6应用HAVING筛选器。只有使having_condition为true的组才会被插入VT7.
SELECT：处理SELECT列表，产生VT8.
SELECT 的执行读取规则分为sql语句中有无GROUP BY两种情况。
（1）当没有GROUP BY时，SELECT 会根据后面的字段名称对内存中的一张临时表整列读取。
（2）当查询sql中有GROUP BY时，会对内存中的若干临时表分别执行SELECT，而且只取各临时表中的第一条记录，然后再形成新的临时表。这就决定了查询sql使用GROUP BY的场景下，SELECT后面跟的一般是参与分组的字段和聚合函数，否则查询出的数据要是情况而定。另外聚合函数中的字段可以是表中的任意字段，需要注意的是聚合函数会自动忽略空值。
DISTINCT：将重复的行从VT8中移除，产生VT9.
ORDER BY：将VT9中的行按ORDER BY 子句中的列列表排序，生成游标（VC10).
TOP：从VC10的开始处选择指定数量或比例的行，生成表VT11,并返回调用者。

分析器会先看语句的第一个词，当它发现第一个词是SELECT关键字的时候，它会跳到FROM关键字，然后通过FROM关键字找到表名并把表装入内存。接着是找WHERE关键字，如果找不到则返回到SELECT找字段解析，如果找到WHERE，则分析其中的条件，完成后再回到SELECT分析字段。最后形成一张我们要的虚表。
WHERE关键字后面的是条件表达式。条件表达式计算完成后，会有一个返回值，即非0或0，非0即为真(true)，0即为假(false)。同理WHERE后面的条件也有一个返回值，真或假，来确定接下来执不执行SELECT。
分析器先找到关键字SELECT，然后跳到FROM关键字将STUDENT表导入内存，并通过指针找到第一条记录，接着找到WHERE关键字计算它的条件表达式，如果为真那么把这条记录装到一个虚表当中，指针再指向下一条记录。如果为假那么指针直接指向下一条记录，而不进行其它操作。一直检索完整个表，并把检索出来的虚拟表返回给用户。EXISTS是条件表达式的一部分，它也有一个返回值(true或false)。

SQL语句执行顺序
https://www.cnblogs.com/Qian123/p/5669259.html

关于sql和MySQL的语句执行顺序(必看！！！)
http://blog.csdn.net/u014044812/article/details/51004754

mysql（1）—— 详解一条sql语句的执行过程
https://www.cnblogs.com/cdf-opensource-007/p/6502556.html

SQL语句类型

数据定义语言DDL

数据定义语言DDL用来创建数据库中的各种对象—–表、视图、索引、同义词、聚簇等如：
CREATE TABLE/VIEW/INDEX/SYN/CLUSTER

数据操纵语言DML

数据操纵语言DML主要有三种形式：

插入：INSERT
更新：UPDATE
删除：DELETE

数据查询语言DQL

数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块：

数据控制语言DCL

数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。如：

GRANT：授权。
ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点。

SQL优化

谓词下推

谓词下推(Predicate pushdown)：将外层查询块的 WHERE 子句中的谓词移入所包含的较低层查询块（比如子查询）中，从而能够提早进行数据过滤以及有可能更好地利用索引。
基本策略是，始终将过滤表达式尽可能移至靠近数据源的位置。

索引下推(ICP)

索引下推 ICP(Index condition pushdown) 在 Mysql5.6 的版本上推出，用于优化查询。

在不使用 ICP 的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据后返回给 MySQL 服务器，服务器判断数据是否符合 where 条件

在使用 ICP 的情况下，如果存在某些被索引的列的判断条件时，MySQL 服务器将这一部分判断条件传递给存储引擎，由存储引擎通过判断索引是否符合 MySQL 服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给 MySQL 服务器

索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。

未开启 ICP 时，不能完全利用索引树及索引上存储的信息，而是愚笨的通过回表取数据，通过Using where进行数据过滤。

explain结果中 Extra 的值为 Using index condition 表示使用了索引下推。

查看是否开启了索引下推优化（默认是开启的）
show variables like '%optimizer_switch%' 看结果中的 index_condition_pushdown 是否是 on

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on

开启关闭索引下推

SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';

Mysql性能优化：什么是索引下推？
https://juejin.cn/post/6844904110072791048

SQL语句

case when

CASE关键字有两种使用方法，分别是简单case函数和 case搜索函数
简单case函数：

CASE sex
    WHEN '1' THEN '男'
    WHEN '2' THEN '女'
    ELSE '其他'
END

case搜索函数：

CASE
    WHEN sex='1' THEN '男'
    WHEN sex='2' THEN '女'
    ELSE '其他'
END

注意：使用搜索函数形式的case when时，字段名放在when中，case后面直接就是when，不要写错了，否则结果错误。

1、简单case函数是case搜索函数的真子集
简单case函数的使用方法与一些高级语言（如：java）中的switch语句相似:CASE给定匹配字段，WHEN给出具体的字段值，如果匹配到后返回THEN值。
简单case函数其实就是case搜索函数的”=”逻辑的实现。case搜索函数可以实现简单case函数的所有功能，而简单case函数却不可实现case搜索函数的”=”逻辑以外的功能。
case搜索函数与简单case函数相比的灵活之处在于可以在WHEN中书写判断式。
2、case函数匹配原则
case函数与switch的不同在于case仅返回第一个匹配到的结果，而switch则会在没有中断的情况下继续后面的判断，将会执行所有匹配的结果。

case when的应用
（1）用一个SQL语句完成不同条件的分组。
例如：stu_cls表有字段name class score，用一条sql查询每个class里及格和不及格的学生人数，结果形式class num(>=60)，num(<60)

select class,
sum(case when score>=60 then 1 else 0 end) '及格人数' ,
sum(case when score<60 then 1 else 0 end) '不及格人数',
count(*)
from stu_cls
group by class;

查询结果：

+--------+--------------+-----------------+----------+
| class  | 及格人数    | 不及格人数      | count(*) |
+--------+--------------+-----------------+----------+
| 数学  |            5 |              7 |      12 |
| 英语  |            7 |              2 |        9 |
| 语文  |            7 |              2 |        9 |
+--------+--------------+-----------------+----------+

（2）内容替换
例如：stu_cls表有字段name class score，查询class和分数，当分数>=60时显示为“及格”，否则显示为“不及格”

select class, case when score>=60 then '及格' else '不及格' end 'score'
from stu_cls

查询结果：

+--------+-----------+
| class  | score    |
+--------+-----------+
| 数学  | 及格      |
| 英语  | 不及格    |
| 语文  | 及格      |
| 语文  | 及格      |
...
+--------+-----------+

SQL语句之CASE WHEN
https://blog.csdn.net/STFPHP/article/details/53109295

MySQL面试题，查询及格人数，不及格人数
http://www.kuitao8.com/20140919/3077.shtml

MySQL分页查询

使用limit offset, count

limit关键字的用法：
LIMIT [offset,] rows
offset指定要返回的第一行的偏移量（默认为0），rows第二个指定返回行的最大数目。初始行的偏移量是0(不是1)。

语句样式: MySQL中,可用如下方法: SELECT * FROM table LIMIT M,N
适应场景: 适用于数据量较少的情况(元组百/千级)
原因/缺点: 全表扫描,速度会很慢且有的数据库结果集返回不稳定(如某次返回1,2,3,另外的一次返回2,1,3). Limit限制的是从结果集的M位置处取出N条输出,其余抛弃.

基于索引再排序

语句样式: MySQL中,可用如下方法: SELECT * FROM 表名称 WHERE id_pk > (pageNum*10) ORDER BY id_pk ASC LIMIT M
适应场景: 适用于数据量多的情况(元组数上万). 最好ORDER BY后的列对象是主键或唯一所以,使得ORDERBY操作能利用索引被消除但结果集是稳定的(稳定的含义,参见方法1)
原因: 索引扫描,速度会很快. 但MySQL的排序操作,只有ASC没有DESC(DESC是假的,未来会做真正的DESC,期待…).

MySQL大数据量分页查询方法及其优化
https://www.cnblogs.com/geningchao/p/6649907.html

连接join

内连接与外连接的区别：与内连接不同的是，外连接不只列出与连接条件相匹配的行，而是列出左表(左外连接时)、右表(右外连接时)或两个表(全外连接时)中所有符合搜索条件的数据行。

内连接(INNER JOIN)

内连接查询操作列出与连接条件匹配的数据行，它使用比较运算符比较被连接列的列值。内连接分三种：
1、等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。
2、不等连接：在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!& lt;和<>。
3、自然连接：在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。
例，下面使用等值连接列出authors和publishers表中位于同一城市的作者和出版社：

SELECT *  FROM authors AS a INNER JOIN publishers AS p  ON a.city=p.city

又如使用自然连接，在选择列表中删除authors 和publishers 表中重复列(city和state)：

SELECT a.*,p.pub_id,p.pub_name,p.country  FROM authors AS a INNER JOIN publishers AS p  ON a.city=p.city

外连接(OUTER JOIN)

内连接时，返回查询结果集合中的仅是符合查询条件的行。而采用外连接时，它返回到查询结果集合中的不仅包含符合连接条件的行，而且还包括左表(左外连接时)、右表(右外连接时)或两个边接表(全外连接)中的所有数据行。

外联接。外联接可以是左向外联接、右向外联接或完整外部联接。
在 FROM子句中指定外联接时，可以由下列几组关键字中的一组指定：
1）LEFT JOIN或LEFT OUTER JOIN
左向外联接的结果集包括 LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。
2）RIGHT JOIN 或 RIGHT OUTER JOIN
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。
3）FULL JOIN 或 FULL OUTER JOIN
完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

如下面使用左外连接将论坛内容和作者信息连接起来：

SELECT a.*,b.* FROM luntan LEFT JOIN usertable as b  ON a.username=b.username

下面使用全外连接将city表中的所有作者以及user表中的所有作者，以及他们所在的城市：

SELECT a.*,b.*  FROM city as a FULL OUTER JOIN user as b  ON a.username=b.username

交叉连接(CROSS JOIN)

交叉连接：交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。

select * from book as a cross join stu as b order by a.id

SQL的几种连接：内连接、左联接、右连接、全连接、交叉连接(有实例和查询结果)
https://www.cnblogs.com/zxlovenet/p/4005256.html

SQL中的内连接外连接和交叉连接是什么意思?
https://www.cnblogs.com/luchaoit/archive/2014/11/11/4090251.html

group by与聚集函数

exists用法

一种通俗的可以理解为：将外查询表的每一行，代入内查询作为检验，如果内查询返回的结果取非空值，则EXISTS子句返回TRUE，这一行行可作为外查询的结果行，否则不能作为结果。

比较使用 EXISTS 和 IN 的查询。注意两个查询返回相同的结果：

select * from TableIn where exists(select BID from TableEx where BNAME=TableIn.ANAME)
select * from TableIn where ANAME in(select BNAME from TableEx)

比较使用 EXISTS 和 = ANY 的查询。注意两个查询返回相同的结果：

select * from TableIn where exists(select BID from TableEx where BNAME=TableIn.ANAME)
select * from TableIn where ANAME=ANY(select BNAME from TableEx)

对于如何写带有EXISTS查询的子句，我是这样理解的：
1.首先子查询中必须要有依赖父查询的条件，即我们单独把子查询的select语句提出来不能正常运行。
2.每次查询时父查询表中的一个元组对子查询所有的元组进行判定，如果为true则父查询中的这个元组允许放入结果表，否则进行父查询下一个元组的判定。

IN:确定给定的值是否与子查询或列表中的值相匹配。使用IN时，子查询先产生结果集，然后主查询再去结果集中寻找符合要求的字段列表，符合要求的输出，反之则不输出。
EXISTS:给定一个子查询，检测行的存在。它不返回列表的值，只返回一个True或False。其运行方式是先运行主查询一次，再去子查询中查找与其对应的结果，如果子查询返回True则输出，反之则不输出。再根据主查询中的每一行去子查询中查询。
由于IN操作符需要进行确切地比较，而EXISTS只需要验证存不存在，所以使用IN将会比使用EXISTS花费更多的成本，因此能使用EXISTS替代IN的地方，应该尽量使用EXISTS。另外，尽量使用NOT EXISTS替代NOT IN，使用EXISTS替代DISTINCT。

sql中exists,not exists的用法
https://www.cnblogs.com/cjm123/p/8177017.html

关系数据库的范式

Normal form 作为设计的标准范式，其最大的意义就是为了避免数据的冗余和插入/删除/更新的异常。

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

一般说来，数据库只需满足第三范式(3NF）就行了。

应用数据库范式可以带来许多好处，但是最重要的好处归结为三点：
1.减少数据冗余（这是最主要的好处，其他好处都是由此而附带的）
2.消除异常（插入异常，更新异常，删除异常）
3.让数据组织的更加和谐…

1NF(字段不可分)

1NF：字段是最小的的单元不可再分。这个关系数据库强制了，想建立复合的字段也建立不起来。关系数据库出现之前才有这个问题。

1NF
第一范式（1NF）要求数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值。
若某一列有多个值，可以将该列单独拆分成一个实体，新实体和原实体间是一对多的关系。
在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。

2NF(非主属性完全依赖主键)

2NF：满足1NF，表中的字段必须完全依赖于全部主键而非部分主键 (一般我们都会做到)。如果依赖于主键，则需要依赖于所有主键，不能存在依赖部分主键的情况

2NF
满足第二范式（2NF）必须先满足第一范式（1NF）。
第二范式要求实体中没一行的所有非主属性都必须完全依赖于主键；即：非主属性必须完全依赖于主键。
完全依赖：主键可能由多个属性构成，完全依赖要求不允许存在非主属性依赖于主键中的某一部分属性。
若存在哪个非主属性依赖于主键中的一部分属性，那么要将发生部分依赖的这一组属性单独新建一个实体，并且在旧实体中用外键与新实体关联，并且新实体与旧实体间是一对多的关系。

2NF，主键依赖，就是一张表里面的字段，必须是跟主键相关的，不能把无关的数据放进来。主键依赖，实质就是，这个信息如果是对象的属性，就放进来，否则就不放。

3NF(非主属性互相不依赖)

3NF：满足2NF,非主键外的所有字段必须互不依赖
消除非主属性之间的依赖关系，只保留非主属性与码的依赖关系。
3NF三范式就是要消除传递依赖。

第二范式（2NF）和第三范式（3NF）的概念很容易混淆，区分它们的关键点在于，2NF：非主键列是否完全依赖于主键，还是依赖于主键的一部分；3NF：非主键列是直接依赖于主键，还是直接依赖于非主键列。

例1，选课表（学号，课程号，系别，系主任）
系别和系主任是非主属性，学号和课程号是主属性。但是系别和系主任这两个非主属性之间有函数依赖关系：系别→系主任。必造成传函依赖：学号→系别→系主任。不符合3NF
解决方案：将这个非主属性与其依赖的码都拿出来单独建表，并设置被依赖的属性为主键，在原表中则用外键表示。

例2，订单表（订单id，商品id，商品名）
为了能在订单表上一眼看到商品名称，将商品名冗余到订单表上，导致非主属性（商品id，商品名）是有依赖关系的，不满足3NF，但为了方便可以这么冗余设计。

解释一下关系数据库的第一第二第三范式？
https://www.zhihu.com/question/24696366

存储过程Procedure

存储过程Procedure是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行。

存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。

由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中，所以存储过程运行要比单个的SQL语句块要快。同时由于在调用时只需用提供存储过程名和必要的参数信息，所以在一定程度上也可以减少网络流量、简单网络负担。

索引

MySQL索引原理及慢查询优化 - 美团技术团队
https://tech.meituan.com/2014/06/30/mysql-index.html

mysql建索引的几大原则
https://blog.csdn.net/u013412790/article/details/51612304

数据库设计索引的原则
https://baijiahao.baidu.com/s?id=1559636922403174&wfr=spider&for=pc

选择哪些字段作为索引？

为常作为where查询条件的字段建立索引
如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。

为经常需要排序、分组和联合操作的字段建立索引
经常需要ORDER BY、GROUP BY、join、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。

索引过多带来的问题(空间,修改开销)

不要设置过多的索引，在没有聚集索引的表中，最大可以设置249个非聚集索引，过多的索引首先会带来更大的磁盘空间，而且在数据发生修改时，对索引的维护是特别消耗性能的。

索引的基数、唯一索引、区分度(选择性)

索引基数：索引中惟一值的数量。
惟一索引的基数等于表中的行数。但是，非惟一索引的基数可以是从 1 到表中行数的任意数字，具体取决于每个索引键在表中出现的次数。
低基数索引就是那些惟一值相对较少的索引。例如：
account enabled 或 published 或性别(M/F)等列很可能只有两个惟一值（yes 和 no），此类列上的索引的基数为 2。
许多表的列可以包含一个很小的惟一值子集，这些列包括 Status、Color 和 Currency 等，具体情况取决于所存储的数据。

索引选择性=基数/数据行
索引的选择性是指索引列中不同值的数目与表中记录总数的比值。举个例子：假设一个表中有100条数据，该列的值中有80个不同的值，那么该列索引的选择性就是0.8，对于索引的选择性，值越高那么该列索引的效率也就越高。

选择性为1的索引叫唯一索引，这是最好的索引选择性，性能也是最好的

关于索引的选择性，它是指不重复的索引值（也称为基数cardinality)和数据表的记录总数的比值，范围从1/(数据表记录总数)到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。选择性为1的索引叫唯一索引，这是最好的索引选择性，性能也是最好的

低基数索引为什么会对性能产生负面影响
https://www.ibm.com/developerworks/cn/data/library/techarticle/dm-1309cardinal/

前缀索引的长度选择

① 前缀索引是一种能使索引更小，更快的有效办法，但另一方面也有其缺点：mysql无法使用其前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描
② 要明确使用前缀索引的目的与优势：
大大节约索引空间，从而提高索引效率
对于 BOLB 、 TEXT 或者很长的 VARCHAR 类型的列，必须使用前缀索引,因为 MySQL 不允许索引这些列的完整长度
③ 前缀索引会降低索引的选择性
④ 真正的难点在于：要选择足够长的前缀以保证较高的选择性，同时又不能太长，前缀的长度应该使前缀索引的选择性接近索引整个列，即前缀的基数应该接近于完整列的基数

索引长度越低，索引在内存中占的长度越小，排序越快，然而区分度就越低。这样不利于查找。
索引长度越长，区分度就高，虽然利于查找了，但是索引在内存中占得空间就多了。

如何选择最优索引长度？

计算完整列索引选择性

select count(distinct name) / count(*) from music;

分别计算前1个字符、2个字符、3个字符、4个字符的选择性

select
  count(distinct left(name,1))/count(*) as sel1,
  count(distinct left(name,2))/count(*) as sel2,
  count(distinct left(name,3))/count(*) as sel3,
  count(distinct left(name,4))/count(*) as sel4
from music;

索引长度与选择性

可以看到当选择前两个字符作为前缀索引后，索引的选择性达到了0.5，接近完整列的索引选择性0.5556，所以选择前2个字符作为索引可在索引长度和选择性上达到最优。

mysql前缀索引的索引选择性
https://blog.csdn.net/dhrome/article/details/72853153

复合索引

单列索引：即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。
组合索引：即一个索包含多个列。

从本质上说，联合索引也是一棵树，不同的是联合索引的键值的数量不是1，而是大于等于2

如果我们的查询where条件只有一个，我们完全可以用单列索引，这样的查询速度较快，索引也比较瘦身。如果我们的业务场景是需要经常查询多个组合列，不要试图分别基于单个列建立多个单列索引（因为虽然有多个单列索引，但是MySQL只能用到其中的那个它认为似乎最有效率的单列索引）。

如果在firstname、lastname、age这三个列上分别创建单列索引，效果是否和创建一个firstname、lastname、age的多列索引一样呢？
答案是否定的，两者完全不同。当我们执行查询的时候，MySQL只能使用一个索引。如果你有三个单列的索引，MySQL会试图选择一个限制最严格的索引。但是，即使是限制最严格的单列索引，它的限制能力也肯定远远低于firstname、lastname、age这三个列上的多列索引。

全列匹配(优化器自动调整顺序)

当按照索引中所有列进行精确匹配（这里精确匹指“=”或“IN”匹配）时，索引可以被用到。

这里有一点需要注意，理论上索引对顺序是敏感的，但是由于MySQL的查询优化器会自动调整where子句的条件顺序以使用适合的索引，例如将where中的条件顺序颠倒依然能触发索引

最左前缀匹配

现在我们有一个firstname、lastname、age列上的多列索引，我们称这个索引为fname_lname_age。它相当于我们创建了(firstname，lastname，age)、(firstname，lastname)以及(firstname)这些列组合上的索引。
为什么没有 (lastname,age)等这样的组合索引呢？这是因为 mysql 组合索引”最左前缀”（Leftmost Prefixing）的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引。

如果使用多列索引，where条件中字段的顺序非常重要，需要满足最左前缀列。最左前缀：查询条件中的所有字段需要从左边起按顺序出现在多列索引中，查询条件的字段数要小于等于多列索引的字段数，中间字段不能存在范围查询的字段(<,like等)，这样的sql可以使用该多列索引。

ALTER TABLE people ADD INDEX lname_fname_age (lame,fname,age);
上例中我们创建了lname_fname_age多列索引,相当于创建了(lname)单列索引，(lname,fname)组合索引以及(lname,fname,age)组合索引。

在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。

MySQL单列索引和组合索引的选择效率与explain分析
https://blog.csdn.net/xtdhqdhq/article/details/17582779

mysql多列索引和最左前缀
https://www.cnblogs.com/usa007lhy/p/6442570.html

没有中间条件

查询条件用到了索引中列的精确匹配，但是中间某个条件未提供
比如复合索引为A,B,C，查询时where条件为A,C，则只会使用A索引

无索引最左列

无最左列不会触发索引

匹配某列的前缀字符串

like ‘aa%’
如果通配符%不出现在开头，则可以用到索引

范围查询

范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引。同时，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引。

对于范围条件查询，MYSQL无法再使用范围后面的其他索引列了。

最左前缀匹配原则，组合索引非常重要的原则，**mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配**，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

函数或表达式无法使用索引

left(A,2)=’xx’
虽然这个查询和A like ‘xx%’ 功能相同，但是由于使用了函数left，则无法为title列应用索引，而情况五中用LIKE则可以。
WHERE emp_no - 1=’10000’
显然这个查询等价于查询emp_no为10001的函数，但是由于查询条件是一个表达式，MySQL无法为其使用索引。看来MySQL还没有智能到自动优化常量表达式的程度，因此在写查询语句时尽量避免表达式出现在查询中，而是先手工私下代数运算，转换为无表达式的查询语句。

原因
SQL语句where中如果有functionName(colname)或者某些运算，则MYSQL无法使用基于colName的索引。使用索引需要直接查询某个字段。
索引失效的原因是索引是针对原值建的二叉树，将列值计算后，原来的二叉树就用不上了；
为了解决索引列上计算引起的索引失效问题，将计算放到索引列外的表达式上。

SQL 索引最左前缀原理
https://blog.csdn.net/zly9923218/article/details/51330995

索引排序

联合索引的第二个好处是已经对第二个键值进行了排序处理。
联合索引(a,b)是根据列a,b进行排序，因此下列语句可以直接使用联合索引得到结果

select ... from table where a=xxx order by b;

而对于联合索引(a,b,c)来说，下列语句同样可以直接通过联合索引得到结果：


select ... from table where a=xxx order by b;
select ... from table where a=xxx and b=xxx order by c;

但是对于下面的语句，联合索引不能直接得到结果，其还需要执行一次filesort排序操作，因为索引(a,c)并未排序

select ... from table where a=xxx order by c;

覆盖索引(不需回表)

InnoDB支持覆盖索引（或索引覆盖），即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录
使用覆盖索引的一个好处是：不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作

B-Tree索引和Hash索引

理解了B树和Hash的内部结构和实现原理，自然就能知道何时该选择哪种索引

B-Tree索引

B-Tree 索引的特点
B-tree 索引可以用于使用 =, >, >=, <, <= 或者 BETWEEN 运算符的列比较。如果 LIKE 的参数是一个没有以通配符起始的常量字符串的话也可以使用这种索引。

Hash索引

哈希索引建立在哈希表的基础上，它对每个值采用精确查找。每一行都需要先计算哈希码，比较好的哈希算法算出比较低的重复的度，这样效率相对高一些。如果算出来的值是一样的，那么它需要再进行判断哪个值才是想要的值，所以说在表里面采用哈希索引，但是重复度又比较高，那么哈希索引效率就比较低

Hash 索引的特点
Hash 索引有着与刚才所讨论特点的相比截然不同的特点：
Hash 索引只能够用于使用 = 或者 <=> 运算符的相等比较(但是速度更快)。Hash 索引不能够用于诸如 < 等用于查找一个范围值的比较运算符。依赖于这种单值查找的系统被称为 “键-值存储”；对于这种系统，尽可能地使用 hash 索引。

（1）Hash索引仅仅能满足”=”,”IN”和”<=>”查询，不能使用范围查询。
由于 Hash 索引比较的是进行 Hash 运算之后的 Hash值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。

（2）Hash 索引无法支持排序操作。
由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash值，而且Hash值的大小关系并不一定和 Hash运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；

（3）Hash索引不能利用部分索引键查询。
对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。

（4）Hash索引在任何时候都不能避免表扫描。
前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

（5）Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

MySQL的btree索引和hash索引的区别
https://www.cnblogs.com/hanybblog/p/6485419.html

MySql最左前缀原则
https://blog.csdn.net/SkySuperWL/article/details/52583579

聚集索引(聚簇索引 Clustered Index)

聚簇索引(Clustered Index)和非聚簇索引 (Non- Clustered Index)
最通俗的解释是:聚簇索引的顺序就是数据的物理存储顺序，而对非聚簇索引的索引顺序与数据物理排列顺序无关。

建立聚簇索引使用CREATE INDEX语句，格式为：
CREATE CLUSTER INDEX index_name
ON table_name(column_name1,column_name2,…);

一般情况下主键会默认创建聚簇索引，且一张表只允许存在一个聚簇索引。
通常来说物理顺序结构只有一种，那么一个表的聚簇索引也只能有一个，通常默认都是主键，设置了主键，系统默认就为你加上了聚簇索引，当然有人说我不想拿主键作为聚簇索引，我需要用其他字段作为索引，当然这也是可以的，这就需要你在设置主键之前自己手动的先添加上唯一的聚簇索引，然后再设置主键，这样就木有问题啦。

如果你创建一个没有主键的表，InnoDB会为你选择一个，它可以是在NOT NULL列上定义的第一个UNIQUE键，或者是系统生成的键。

聚簇索引的唯一性
正式聚簇索引的顺序就是数据的物理存储顺序，所以一个表最多只能有一个聚簇索引，因为物理存储只能有一个顺序。正因为一个表最多只能有一个聚簇索引，所以它显得更为珍贵，一个表设置什么为聚簇索引对性能很关键。

聚簇索引的叶节点存什么？(存储特点)

聚集索引。表数据按照索引的顺序来存储的，也就是说索引项的顺序与表中记录的物理顺序一致。对于聚集索引，叶子结点即存储了真实的数据行，不再有另外单独的数据页。在一张表上最多只能创建一个聚集索引，因为真实数据的物理顺序只能有一种。

非聚集索引。表数据存储顺序与索引顺序无关。对于非聚集索引，叶结点包含索引字段值及指向数据页数据行的逻辑指针，其行数量与数据表行数据量一致。

聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。

插入/删除数据行时的行为

向表中插入新数据行
如果一张表没有聚集索引，那么它被称为“堆集”（Heap）。这样的表中的数据行没有特定的顺序，所有的新行将被添加到表的末尾位置。
而建立了聚簇索引的数据表则不同：最简单的情况下，插入操作根据索引找到对应的数据页，然后通过挪动已有的记录为新数据腾出空间，最后插入数据。如果数据页已满，则需要拆分数据页，调整索引指针（且如果表还有非聚集索引，还需要更新这些索引指向新的数据页）。而类似于自增列为聚集索引的，数据库系统可能并不拆分数据页，而只是简单的新添数据页。

从表中删除数据行
对删除数据行来说：删除行将导致其下方的数据行向上移动以填充删除记录造成的空白。如果删除的行是该数据页中的最后一行，那么该数据页将被回收，相应的索引页中的记录将被删除。对于数据的删除操作，可能导致索引页中仅有一条记录，这时，该记录可能会被移至邻近的索引页中，原索引页将被回收，即所谓的“索引合并”。

聚簇索引与非聚簇索引的区别
https://www.cnblogs.com/qlqwjy/p/7770580.html

什么时候适合用聚簇索引(范围查询,排序)

1、聚簇索引对于那些经常要搜索范围值的列特别有效。使用聚簇索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。例如，如果应用程序执行的一个查询经常检索某一日期范围内的记录，则使用聚集索引可以迅速找到包含开始日期的行，然后检索表中所有相邻的行，直到到达结束日期。这样有助于提高此类查询的性能。
2、同样，如果对从表中检索的数据进行排序时经常要用到某一列，则可以将该表在该列上聚簇（物理排序），避免每次查询该列时都进行排序，从而节省成本。

在聚簇索引下，数据在物理上按顺序排在数据页上，重复值也排在一起，因而在那些包含范围检查 (between、<、<=、>、>=)或使用group by或orderby的查询时，一旦找到具有范围中第一个键值的行，具有后续索引值的行保证物理上毗连在一起而不必进一步搜索，避免了大范围扫描，可以大大提高查询速度。

聚簇索引与非聚簇索引的区别
https://www.cnblogs.com/qlqwjy/p/7770580.html

辅助索引(二级索引/非聚簇索引)

辅助索引（secondary index）也称为非聚集索引，叶子节点并不包含行记录的全部数据
叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签（bookmark）。该书签用来告诉InnoDB存储引擎哪里可以找到与所以相对应的行数据
由于InnoDB存储引擎表是索引组织表，因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键

辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引
当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录
例如：如果在一棵高度为3的辅助索引树中查找数据，那需要对这棵辅助索引树遍历3次找到指定主键，如果聚集索引树的高度同样为3，那么还需要对聚集索引树进行3次查找，最终找到一个完整的行数据所在的页，因此一共需要6次逻辑IO访问以得到最终的一个数据页

MySQL(InnoDB剖析):24—B+树索引（聚集索引与辅助索引(非聚集索引)、B+树索引的分裂）- 照搬《MySQL技术内幕 InnoDB存储引擎》
https://blog.csdn.net/qq_41453285/article/details/104208974

索引的存储结构

B+Tree结构都可以用在MyISAM和InnoDB上。mysql中，不同的存储引擎对索引的实现方式不同，大致说下MyISAM和InnoDB两种存储引擎。

MyISAM是非聚簇索引

MyISAM的是非聚簇索引，B+Tree的叶子节点上的data，并不是数据本身，而是数据存放的地址。主索引和辅助索引没啥区别，只是主索引中的key一定得是唯一的。这里的索引都是非聚簇索引。非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，
这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

InnoDB是聚簇索引

InnoDB是索引组织表，即表中数据按照主键顺序存放
而聚集索引就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。聚集索引的这个特性决定了索引组织表中的数据也是索引的一部分。同B+树结构一样，每个数据页都通过一个双向链表进行链接
由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引

InnoDB的页可以用于存放数据也可以用于存放键值+指针，聚簇索引的B+树中叶子节点存放数据，非叶子节点存放键值+指针。

InnoDB的数据文件本身就是索引文件，B+Tree的叶子节点上的data就是数据本身，key为主键，这是聚簇索引。二级索引的叶子节点上的data是主键(所以聚簇索引的key，不能过长)。

InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用”where id = 14”这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。

为了更形象说明这两种索引的区别，我们假想一个表如下图存储了4行数据。其中Id作为主索引，Name作为辅助索引。图示清晰的显示了聚簇索引和非聚簇索引的差异。

Id	Name	Company
5	Gates	Microsoft
7	Bezos	Amazon
11	Jobs	Apple
14	Ellison	Oracle

InnoDB和MyISAM的索引结构如下：

InnoDB和MyISAM的索引结构对比

Mysql聚簇索引和非聚簇索引原理（数据库）
https://blog.csdn.net/lisuyibmd/article/details/53004848

InnoDB的B+树最多能存多少数据？

磁盘存储数据最小单元是扇区，一个扇区的大小是 512 字节
文件系统（例如XFS/EXT4）的最小单元是块，一个块的大小是 4k(一个文件即使只有 1B 也需要 4K 的空间存储)
InnoDB 存储引擎也有自己的最小储存单元——页（Page），一个页的大小是 16K。

高度为 2 的B+树能存多少数据？
层数为2，即存在一个根节点和若干个叶子节点，那么这棵B+树的存放总记录数为：根节点指针数单个叶子节点记录行数。
假设一行记录的数据大小为1k，则单个叶子节点中的记录数=16K/1K=16
假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节，我们一个页中能存放多少这样的单元，其实就代表有多少指针，即16384/14=1170。那么可以算出一棵高度为2的B+树，能存放 117016=18720条这样的数据记录。

高度为 3 的B+树能存多少数据？
根据同样的原理我们可以算出一个高度为 3 的B+树可以存放：1170*1170*16=21902400 条这样的记录。

所以在 InnoDB 中 B+ 树高度一般为 1-3 层，它就能满足千万级的数据存储。在查找数据时一次页的查找代表一次IO，所以通过主键索引查询通常只需要1-3次IO操作即可查找到数据。

mysql 单表多少数据后要考虑分表？

根据上面的 3 层 b+ 树可以存放 2000 多万行数据，可以很容联想到，为了避免 b+ 树扩展到 4 层，最好在 2000 万数据左右的时候考虑分库分表

为什么MySQL的索引要使用B+树而不是B树？

因为B树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致IO操作变多，查询性能变低；

使用自增主键的好处？（b+树页移动,碎片）

如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

MySQL给已存在的表增加索引时会锁表吗？

看版本，MySQL 5.6 及以上的话，支持 Online DDL 操作，不会锁表。
MySQL 5.6 以下版本，不支持 Online DDL 操作，会锁表

profile性能分析

show variables like '%profil%'; 默认 profiling 开关是关闭的
set profiling =1; 打开 profiling

mysql> begin;
mysql> set profiling =1;
mysql> select ar.*
    -> from archives_realinfo ar join archives_realinfo_tag art on ar.id = art.archives_realinfo_id
    -> where art.tag_id in (6,7,13)
    -> order by real_info_modified_date desc limit 10;

mysql> show profiles;
+----------+------------+------------------------+
| Query_ID | Duration   | Query                  |
+----------+------------+------------------------+
|        1 | 3.11221500 | 刚才的sql，太长，省略 |
+----------+------------+------------------------+

mysql> show profile for query 1;
+---------------------------+----------+
| Status                    | Duration |
+---------------------------+----------+
| starting                  | 0.001165 |
| checking permissions      | 0.000012 |
| checking permissions      | 0.000005 |
| Opening tables            | 0.000039 |
| init                      | 0.000040 |
| System lock               | 0.000012 |
| optimizing                | 0.000015 |
| statistics                | 0.001485 |
| preparing                 | 0.000021 |
| Creating tmp table        | 0.000025 |
| Sorting result            | 0.000006 |
| executing                 | 0.000003 |
| Sending data              | 0.028586 |
| converting HEAP to ondisk | 0.011546 |
| Sending data              | 2.985369 |
| Creating sort index       | 0.082668 |
| end                       | 0.000013 |
| query end                 | 0.000005 |
| removing tmp table        | 0.001156 |
| query end                 | 0.000007 |
| closing tables            | 0.000006 |
| freeing items             | 0.000021 |
| cleaning up               | 0.000010 |
+---------------------------+----------+

时间都耗费在 Sending data 上了。

什么是 Sending data 阶段？这个阶段在做什么？
官方的解释是：

The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query.

所以，这个阶段的名字非常具有误导性，这个阶段并不只是发送数据，它包含读取，处理（过滤，排序等）和发送给客户端。

这个阶段非常的巨大，它至少包含了：
Innodb 层数据的定位返回给MySQL 层
Innodb 层数据的查询返回给MySQL 层
Innodb 层数据的修改(如果是MDL)
Innodb 层加锁以及等待
等待进入Innodb层(innodb_thread_concurrency参数)
MySQL 层发送数据给客户端

基本所有和Innodb层打交道的过程都包裹在这个状态下面。

使用 performance_schema 代替 profile

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                     |
+---------+------+-------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | 'SHOW PROFILE' is deprecated and will be removed in a future release. Please use Performance Schema instead |
+---------+------+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

explain查看执行计划

8.8.2 EXPLAIN Output Format
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

explain 会触发真正的查询吗？

如果 explain 的语句在 FROM 子句中包括子查询, 那么 MySQL 实际上会执行子查询, 将其结果放在一个临时表中, 然后完成外层查询优化。
所以 explain 语句是有可能触发 sql 查询的，不能在生产环境任意使用。

id

SQL 执行的顺序的标识，SQL 从大到小的执行
id 相同时，执行顺序由上至下
如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行
id 如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

id 列表示执行顺序，id 越大，越先执行，id 相同，由上至下执行。

select_type

查询中每个 select 子句的类型

(1) SIMPLE(简单SELECT,不使用UNION或子查询等)
(2) PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)
(3) UNION(UNION中的第二个或后面的SELECT语句)
(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
(5) UNION RESULT(UNION的结果)
(6) SUBQUERY(子查询中的第一个SELECT)
(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，取决于外面的查询)
(8) DERIVED(派生表的SELECT, FROM子句的子查询)
(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

table

显示这一行的数据是关于哪张表的，有时不是真实的表名字,看到的是derivedx(x是个数字,我的理解是第几步执行的结果)

type(是否用到索引)

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-join-types

表示 MySQL 在表中找到所需行的方式，又称“访问类型”，常见类型如下:
ALL, index, range, ref, eq_ref, const, system, NULL 从左到右，性能从最差到最好
一般来说，得保证查询至少达到 range 级别，最好能达到ref。

ALL 全表扫描

Full Table Scan 全表扫描，MySQL 将遍历全表以找到匹配的行

index 全索引扫描

Full Index Scan，index 与 ALL 区别为 index 类型只遍历索引树
如果 type 为 index，且在 extra 中看到 Using index 说明 mysql 正在使用覆盖索引，它只扫描索引的数据，不需要回表

1、当查询是索引覆盖的，即所有数据均可从索引树获取的时候（Extra中有Using Index）
2、以索引顺序从索引中查找数据行的全表扫描（无 Using Index）
3、如果Extra中Using Index与Using Where同时出现的话，则是利用索引查找键值的意思
4、如单独出现，则是用读索引来代替读行，但不用于查找

using temporary
在 sql 执行过程中产生了临时表。这个操作比较耗时间。
使用临时表用来存储中间数据，这个临时表的建立过程是比较耗时的。
典型的是因为 sql 中包含 GROUP BY 或 order by 或 distinct 子句。

using where
where 子句用于限制与下一个表匹配的行记录或发送到客户端的行记录。除非您特意打算从表中提取或检查所有行，否则如果 Extra 值不是 Using where 并且表连接类型为 ALL 或 index，则查询可能会出错。

using index
表示直接访问索引就可以获取到所需要的数据(覆盖索引)，不需要通过索引回表；
一般如果 type 为 index，但是 extra 中为空，则表示使用了索引回表

using filesort
mysql 中无法利用索引来完成排序，这时候使用文件排序。其效率很低

Using index condition
表示使用了索引下推

index_merge 多索引合并

表示出现了索引合并优化(包括交集，并集以及交集之间的并集)，但不包括跨表和全文索引。这个比较复杂，目前的理解是合并单表的范围索引扫描（如果成本估算比普通的range要更优的话）
比如 a 列和 b 列各自有一个单独的索引，where 条件中同时出现了 a 列和 b 列，则 mysql 会尝试同时使用这两个索引并对数据进行合并，此时 extra 有 Using intersect(a,b); Using where

range 索引范围扫描

索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。
显而易见的索引范围扫描是带有 between 或者 where 子句里带有 < 或 > 查询。
当 mysql 使用索引去查找一系列值时，例如 IN() 和 OR 列表，也会显示 range（范围扫描），当然性能上面是有差异的。
此类扫描的开销跟索引类型相当

ref 非唯一索引等值匹配

使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行

eq_ref 唯一索引

类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用 primary key 或者 unique key 作为关联条件
每次与之前的表合并行都只在该表读取一行，这是除了 system，const 之外最好的一种，特点是使用=，而且索引的所有部分都参与join且索引是主键或非空唯一键的索引

const/system

当 MySQL 对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于 where 列表中，MySQL 就能将该查询转换为一个常量

NULL

MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

例1，有个用户关系表，要根据 update_time 做范围筛选

create table user_relation (
  id bigint auto_increment primary key,
  user1_id bigint not null comment '用户1 ID',
  user2_id bigint not null comment '用户2 ID',
  relation smallint(6) not null comment '关系',
  create_time datetime default CURRENT_TIMESTAMP not null comment '创建时间',
  update_time timestamp default CURRENT_TIMESTAMP not null comment '修改时间'
);

update_time 无索引时是 all 类型：

> EXPLAIN SELECT * FROM user_relation WHERE relation IN (2) AND  update_time >= '2020-05-01 08:00:00'  and update_time <='2020-05-01 16:00:00' ORDER BY update_time DESC    , user1_id DESC LIMIT 0, 100;
+------+-------------+---------------+------+---------------+------+---------+------+-------+-----------------------------+
| id   | select_type | table         | type | possible_keys | key  | key_len | ref  | rows  | Extra                       |
+------+-------------+---------------+------+---------------+------+---------+------+-------+-----------------------------+
|    1 | SIMPLE      | user_relation | ALL  | NULL          | NULL | NULL    | NULL | 33967 | Using where; Using filesort |
+------+-------------+---------------+------+---------------+------+---------+------+-------+-----------------------------+

给 update_time 列加索引后 alter table user_relation add index idx_update_time(update_time);
type 变为 range，能用上 idx_update_time 索引

> EXPLAIN SELECT * FROM user_relation WHERE relation IN (2)  AND  update_time >= '2020-05-01 08:00:00' and update_time <='2020-05-01 16:00:00' ORDER BY update_time DESC , user1_id DESC LIMIT 0, 100;
+------+-------------+---------------+-------+-----------------+-----------------+---------+------+------+----------------------------------------------------+
| id   | select_type | table         | type  | possible_keys   | key             | key_len | ref  | rows | Extra                                              |
+------+-------------+---------------+-------+-----------------+-----------------+---------+------+------+----------------------------------------------------+
|    1 | SIMPLE      | user_relation | range | idx_update_time | idx_update_time | 4       | NULL |    1 | Using index condition; Using where; Using filesort |
+------+-------------+---------------+-------+-----------------+-----------------+---------+------+------+----------------------------------------------------+

注意，虽然 sql 条件内有 update_time 和 relation，但也没必要建立 (update_time, relation) 联合索引，因为按 update_time 做范围筛选后，后面的联合索引字段就用不到了。

possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

key

显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
如果mysql正在使用的只是索引里的某些列，那么就可以用这个值来算出具体是哪些列。
key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

不损失精确性的情况下，长度越短越好

key_len 的长度一般跟这三个因素有关，分别是数据类型，字符编码，是否为 NULL。

同一类型，同样长度的key，如果其中一个是可为null的，则会比 not null 列的key长度多1

ref

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

rows

表示 MySQL 根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

filtered

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain_filtered
取值 0 - 100
filtered 表示通过查询条件获取的最终记录行数占通过type字段指明的搜索方式搜索出来的记录行数的百分比，也就是 where 条件筛选行数占 rows 行数的百分比。
比如索引筛选 rows = 30，where 条件筛选后剩余 10 行，则 filtered = 10 / 30 = 33.3

filtered 值越大索引越好，表示索引过滤后的行数基本就是最终 where 条件要筛选的行数

Extra

该列包含MySQL解决查询的详细信息,有以下几种情况：
Using index 此查询使用了覆盖索引(Covering Index)，即通过索引就能返回结果，无需访问表。若没显示”Using index”表示读取了表数据。

Using where 表示 MySQL 服务器从存储引擎收到行后再进行“后过滤”（Post-filter）。所谓“后过滤”，就是先读取整行数据，再检查此行是否符合 where 句的条件，符合就留下，不符合便丢弃。因为检查是在读取行后才进行的，所以称为“后过滤”。

Using temporary 表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询

Using filesort MySQL中无法利用索引完成的排序操作称为“文件排序”。若查询所需的排序与使用的索引的排序一致，因为索引是已排序的，因此按索引的顺序读取结果返回，否则，在取得结果后，还需要按查询所需的顺序对结果进行排序，这时就会出现 Using filesort 。

一种常见的可优化 SQL 就是查询条件和排序字段不是同一个，查询条件可用到索引，但排序无索引，最后还需要在内存中排序

Using join buffer 该值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。

Impossible where 这个值强调了where语句会导致没有符合条件的行。

Select tables optimized away 这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

Using index condition 使用索引条件，即索引下推

MySQL Explain详解
http://www.cnblogs.com/xuanzhi201111/p/4175635.html

EXPLAIN 命令详解
https://www.cnblogs.com/gomysql/p/3720123.html

存储引擎

InnoDB存储引擎

InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，上图也看到了，InnoDB是默认的MySQL引擎。

MyISAM存储引擎

MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事务。

MEMORY存储引擎

MEMORY存储引擎将表中的数据存储到内存中，未查询和引用其他表数据提供快速访问。

数据库—四种存储引擎
https://www.cnblogs.com/domi22/p/8059403.html

InnoDB和MyISAM区别(索引/事务/行锁/外键/聚集)

区别：
1、MyISAM不支持事务，而InnoDB支持。InnoDB的AUTOCOMMIT默认是打开的，即每条SQL语句会默认被封装成一个事务，自动提交，这样会影响速度，所以最好是把多条SQL语句显示放在begin和commit之间，组成一个事务去提交。

2、InnoDB支持数据行锁定，MyISAM不支持行锁定，只支持锁定整个表。即MyISAM同一个表上的读锁和写锁是互斥的，MyISAM并发读写时如果等待队列中既有读请求又有写请求，默认写请求的优先级高，即使读请求先到，所以MyISAM不适合于有大量查询和修改并存的情况，那样查询进程会长时间阻塞。因为MyISAM是锁表，所以某项读操作比较耗时会使其他写进程饿死。

3、InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；

4、InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

5、InnoDB不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
没有 where 的 count(*) 使用MyISAM要比InnoDB快得多。因为MyISAM内置了一个计数器，count(*) 时它直接从计数器中读，而InnoDB必须扫描全表。所以在InnoDB上执行 count(*) 时一般要伴随where，且where中要包含主键以外的索引列。为什么这里特别强调“主键以外”？因为InnoDB中primary index是和raw data存放在一起的，而secondary index则是单独存放，然后有个指针指向primary key。所以只是 count(*) 的话使用secondary index扫描更快，而primary key则主要在扫描索引同时要返回raw data时的作用较大。

6、Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高；

MyISAM和InnoDB的区别
https://www.cnblogs.com/lyl2016/p/5797519.html

Mysql 中 MyISAM 和 InnoDB 的区别有哪些？ - Oscarwin的回答 - 知乎
https://www.zhihu.com/question/20596402/answer/211492971

MySQL存储引擎中的MyISAM和InnoDB区别详解
https://blog.csdn.net/lc0817/article/details/52757194

如何选择存储引擎

如何选择：
1、是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；

2、如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读写也挺频繁，请使用InnoDB。

3、系统奔溃后，MyISAM恢复起来更困难，能否接受；

MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(5.5之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差。

MyISAM适合：(1)做很多count 的计算；(2)插入不频繁，查询非常频繁；(3)没有事务。
InnoDB适合：(1)可靠性要求比较高，或者要求事务；(2)表更新和查询都相当的频繁，并且行锁定的机会比较大的情况。

如果要提供提交、回滚、崩溃恢复能力的事物安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择
如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率
如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果
如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive
使用哪一种引擎需要灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能

数据库锁

按锁的粒度划分，可分为表级锁、行级锁、页级锁（mysql）
按锁级别划分，可分为共享锁、排他锁
按使用方式划分，可分为乐观锁、悲观锁

共享锁和排它锁

如果是排他锁（可重入锁）的话，一个线程加锁后（不论读还是写）另一个线程都不能再加锁（不论读还是写）
如果是读写锁（共享锁）的话，一个线程加读锁后其他线程可加读锁但不能加写锁，一个线程加写锁的话其他线程不能再加任何锁。即读读可并发，读写不可并发。

共享锁(读锁)

共享锁(Share Lock)又称读锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。

如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。

用法
SELECT ... LOCK IN SHARE MODE;
在查询语句后面增加LOCK IN SHARE MODE，Mysql会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据。

排它锁(写锁)

排他锁(eXclusive Lock)又称写锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。

用法
SELECT ... FOR UPDATE;
在查询语句后面增加FOR UPDATE，Mysql会对查询结果中的每行都加排他锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请排他锁，否则会被阻塞。

insert/update/delete自动加排它锁

对于insert、update、delete，InnoDB会自动给涉及的数据加排他锁（X）；
对于一般的Select语句，InnoDB不会加任何锁。

select可选手动加锁

事务可以通过以下语句给显示加共享锁或排他锁：
共享锁：SELECT ... LOCK IN SHARE MODE;
排他锁：SELECT ... FOR UPDATE;

数据库的锁机制
http://blog.csdn.net/lexang1/article/details/52248686

按锁粒度分类

表级锁

表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。
特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。

页级锁

页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。BDB支持页级锁

行级锁

行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁和排他锁。
特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

MySQL常用存储引擎的锁粒度

MySQL常用存储引擎的锁机制：
MyISAM和MEMORY采用表级锁(table-level locking)
BDB采用页面锁(page-level locking)或表级锁，默认为页面锁
InnoDB支持行级锁(row-level locking)和表级锁，默认为行级锁

一致性非锁定读

一致性非锁定读是值InnoDB存储引擎通过多版本控制（multi versioning）的方式来读取当前执行时间数据库中的数据。如果被读的数据行被加了排他锁，在读取这行数据的时候并不会等待锁释放，而是读取该行的一个快照数据。

之所以称为非锁定读，因为不需要等待被访问行的X锁的释放。快照数据是指改行之前的数据版本，该实现通过undo段来完成。

快照数据其实就是当前行数据的一个历史版本，每行记录可能有多个版本。这种技术成为行多版本技术。由此带来的并发控制，成为多版本并发控制（Multi Version Concurrency Control,MVCC）

在不同的事务隔离级别下，读取的方式不同，并不是每个事务隔离级别下都是采用非锁定的一致性读，此外，即使使用非锁定的一致性读，但是对于快照数据的定义也各不相同

在事务的隔离级别，READ COMMITED 和 REPEATABLE READ （InnoDB的默认事务隔离级别）下，对快照数据的定义不同。

在 READ COMMITTED 事务隔离级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据。如果行被锁定，则读取该行版本的最新一个快照(fresh snapshot)。
而在 REPEATABLE READ 事务隔离级别下，对于快照数据，非一致性读总是读取事务开始时的数据版本。

也就是说在 READ COMMITTED 事务隔离级别下，非锁定读读取到的数据是最新的快照版本数据，也就是可以读到另一个事务已经提交了的快照数据。而在 REPEATABLE READ下，只会读到事务开始前的数据。

注意：在 SERIALIZABLE 隔离级别下，InnoDB 会对每个SELECT语句后自动加上 LOCK IN SHARE MODE，即为每个读取操作加一个共享锁。
因此在这个事务隔离级别下，读占用了锁，对一致性的非锁定读不再支持。
即，只有 RR 和 RC 隔离级别支持一致性非锁定读， SERIALIZABLE 隔离级别不支持。

MVCC多版本并发控制

mvcc 是通过 undo log 来实现的。

面试题两个session同时操作一行数据？

参考《MySQL技术内幕 InnoDB存储引擎》6.3.2节的示例。

一致性锁定读

在默认情况下，InnoDB 存储引擎对数据采用的是一致性非锁定读。但是有些情况下为了保证数据逻辑的一致性，需要对 SELECT 的操作加锁。
InnoDB 存储引擎对于 SELECT 语句支持两种一致性的锁定读（locking read）操作。
SELECT …… FOR UPDATE
SELECT …… LOCK IN SHARE MODE

其中，SELECT …… FOR UPDATE 对读取的记录加一个X锁，其他事务不能对已锁定的行加任何锁。
而 SELECT …… LOCK IN SHARE MODE 是对读取的记录加一个S锁。

即使被读取的行被加了一致性锁定读，如果有另一个一致性非锁定读的操作来读取该行数据是不会阻塞的，读取的是该行的快照版本。

SELECT …… FOR UPDATE 和 SELECT …… LOCK IN SHARE MODE 必须在一个事务中，当一个事务提交了，锁就释放了。
因此在使用上述两个SELECT锁定语句时，必须开启事务。

InnoDB在哪些情况下会使用一致性锁定读？

1、在事务中，手动写带有 SELECT …… FOR UPDATE 和 SELECT …… LOCK IN SHARE MODE 的sql
2、在 SERIALIZABLE 隔离级别下，InnoDB 会对每个SELECT语句后自动加上 LOCK IN SHARE MODE，即为每个读取操作加一个共享锁。
因此在这个事务隔离级别下，读占用了锁，对一致性的非锁定读不再支持。
3、对于外键值的插入或更新，首先需要检查父表中的记录，既SELECT父表。但是对于父表的SELECT操作，不是使用一致性非锁定读的方式，因为这会发生数据不一致的问题，因此这时使用的是 SELECT…LOCK IN SHARE MODE 方式，即主动对父表加一个S锁。

自增长与锁

在InnoDB存储引擎中，对于每个含有自增长值的表都有一个自增长计数器(auto-increment counter)。当对含有自增长的计数器的表进行插入操作时，这个计数器会被初始化。

插入操作会根据这个自增长的计数器值加1赋予自增长列。这个实现方式称为 AUTO-INC Locking 。这种锁其实是采用一种特殊的表锁机制，为了提高插入的性能，锁不是在每一个事务完成后才释放，而是在完成对自增长插入的SQL语句后立即释放。

虽然 AUTO-INC Locking 从一定程度上提高了并发插入的效率，但是还是存在性能上的问题。首先对于有自增长值的列的并发插入性能较差，事务必须等待前一个插入的完成(虽然不用等待事务的完成)。其次对于 INSERT ….SELECT 的大数据量的插入会影响插入的性能，因为另一个事务的插入会被阻塞。

从 MySQL 5.1.22 版本开始，InnoDB提供了一种轻量级互斥量的自增长实现机制，这种机制大大提高了自增长插入的性能。并且从这个版本开始，InnoDB提供了一个参数 innodb_autoinc_lock_mode 来控制自增长的模式，其共有3个有效值可以设定 0 1 2 。

0 traditonal 通过表锁的方式进行,所有类型的 insert 都用 AUTO-inc locking
1 consecutive 默认值，产生一个轻量锁，对于simple insert 自增长值的产生使用互斥量对内存中的计数器进行累加操作，对于bulk insert 则还是使用表锁的方式进行。
2 interleaved 对所有的insert-like 自增长值的产生使用互斥量机制完成，并发性能最高，并发插入可能导致自增值不连续，可能会导致Statement 的 Replication 出现不一致，使用该模式，需要用 Row Replication的模式。

另外，在InnoDB存储引擎引擎中，自增长的列必须是索引，同时必须是索引的第一个列。如果不是第一个列，则MySQL数据库会抛出异常，而MyISAM存储引擎没有这个问题。

外键和锁

在InnoDB存储引擎中，对于一个外键列，如果没有显示地对这个列加索引，InnoDB存储引擎会自动对其加一个索引，因为这样可以避免表锁。

对于外键值的插入或更新，首先需要检查父表中的记录，既SELECT父表。但是对于父表的SELECT操作，不是使用一致性非锁定读的方式，因为这会发生数据不一致的问题，因此这时使用的是 SELECT…LOCK IN SHARE MODE 方式，即主动对父表加一个S锁。如果这时父表上已经这样加X锁，子表上的操作会被阻塞。

面试题两个session同时操作父子表？

参考《MySQL技术内幕 InnoDB存储引擎》6.3.5节的示例。

行锁的算法

Record Lock: 单个记录上的锁
Gap Lock: 间隙锁，锁定一个范围，但不包括记录本上
Next-Key Lock: Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身

Record Lock 单条记录行锁

InnoDB存储引擎支持表锁和行锁。顾名思义，表锁是锁住整张表，行锁只是锁住某些行。
InnoDB通过给索引项加锁来实现行锁，如果没有索引，则通过隐藏的聚簇索引来对记录加锁。如果操作不通过索引条件检索数据，InnoDB 则对表中的所有记录加锁，实际效果就和表锁一样。

Innodb中的行锁与表锁(行锁基于索引)

前面提到过，在Innodb引擎中既支持行锁也支持表锁，那么什么时候会锁住整张表，什么时候或只锁住一行呢？

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。
InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。

行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁。行级锁的缺点是：由于需要请求大量的锁资源，所以速度慢，内存消耗大。

行级锁与死锁

MyISAM中是不会产生死锁的，因为MyISAM总是一次性获得所需的全部锁，要么全部满足，要么全部等待。而在InnoDB中，锁是逐步获得的，就造成了死锁的可能。

在MySQL中，行级锁并不是直接锁记录，而是锁索引。

索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引；如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。

在UPDATE、DELETE操作时，MySQL不仅锁定WHERE条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的next-key locking。

当两个事务同时执行，一个锁住了主键索引，在等待其他相关索引。另一个锁定了非主键索引，在等待主键索引。这样就会发生死锁。

发生死锁后，InnoDB一般都可以检测到，并使一个事务释放锁回退，另一个获取锁完成事务。

Gap Lock 间隙锁(只在RR隔离级别下有)

采用 Next-Key Lock 的锁定技术称为 Next-Key Locking 。其设计的目的是为了解决 Phantom Problem

当查询的索引是唯一索引(不存在两个数据行具有完全相同的键值)时，InnoDB存储引擎会将Next-Key Lock降级为Record Lock，即只锁住索引本身，而不是范围。

InnoDB对于辅助索引有特殊的处理，不仅会锁住辅助索引值所在的范围，还会将其下一键值加上Gap LOCK。
就是锁对于二级索引，即使唯一命中，也会锁住其周围的gap，当然也会锁住其对应的主键。

Gap Lock的作用是为了阻止多个事务将记录插入到同一范围内，这会导致Phantom Problem问题的产生

用户可以通过以下两种方式来显式地关闭Gap Lock：
1、将事务的隔离级别设置为 READ COMMITTED
2、将参数innodb_locks_unsafe_for_binlog设置为1
即
在READ COMMITTED的事务隔离级别下，除了唯一性的约束检查以及外键约束的检查需要gap lock，InnoDB不会使用gap lock 锁算法

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

举例来说，假如emp表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL：
Select * from emp where empid > 100 for update;
是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。

RR隔离级别的InnoDB是如何防止幻读的？

在默认的隔离级别下，即REPEATABLE READ下，InnoDB采用Next-key Locking机制来避免Phantom Problem（幻象问题）。
这点可能不同于其他数据库，如Oracle数据库，因为其可能需要在SERIALIZABLE的事务隔离级别下才能解决Phantom Problem

Phantom Problem是指：在同一事务下，连续执行两次同样的SQL语句可能导致不同的结果，第二次的SQL语句可能会返回之前不存在的行。

InnoDB存储引擎默认的事务隔离级别是REPEATABLE READ，在该隔离级别下，其采用Next-Key Locking的方式来加锁。而在READ COMMITTED下，其仅采用Record Lock，所以RC隔离级别下有幻读问题（不只有幻读，还有不可重复读问题呢）。

InnoDB采用Next-Key Locking的算法避免Phantom Problem。比如表t中字段a有1，2，5这三个值，对于SQL语句，select * from t where a>2 for update ，其锁住的不是5这单个值，而是对(2，+∞)这个范围加了X锁。因此任何对于这个范围的插入都是不被允许的，从而避免Phantom Problem

默认情况下，InnoDB工作在可重复读隔离级别下，并且以Next-Key Lock的方式对数据行进行加锁，这样可以有效防止幻读的发生。Next-Key Lock是行锁与间隙锁的组合，这样，当InnoDB扫描索引记录的时候，会首先对选中的索引记录加上行锁（Record Lock），再对索引记录两边的间隙（向左扫描扫到第一个比给定参数小的值，向右扫描扫描到第一个比给定参数大的值，然后以此为界，构建一个区间）加上间隙锁（Gap Lock）。如果一个间隙被事务T1加了锁，其它事务是不能在这个间隙插入记录的。

间隙锁在InnoDB的唯一作用就是防止其它事务的插入操作，以此来达到防止幻读的发生，所以间隙锁不分什么共享锁与排它锁。另外，在上面的例子中，我们选择的是一个普通（非唯一）索引字段来测试的，这不是随便选的，因为如果InnoDB扫描的是一个主键、或是一个唯一索引的话，那InnoDB只会采用行锁方式来加锁，而不会使用Next-Key Lock的方式，也就是说不会对索引之间的间隙加锁，仔细想想的话，这个并不难理解，大家也可以自己测试一下。

要禁止间隙锁的话，可以把隔离级别降为读已提交，或者开启参数innodb_locks_unsafe_for_binlog。

MySQL(InnoDB剖析):34—锁之（行锁的3种算法(Record Lock、Gap Lock、Next-Key Lock)、解决Phantom Problem） - 照搬《MySQL技术内幕 InnoDB存储引擎》
https://blog.csdn.net/qq_41453285/article/details/104314911

Mysql innodb 间隙锁 (转)
https://www.cnblogs.com/rainwang/p/5073068.html

间隙锁的优缺点(防幻读/锁等待)

InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需要。

很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

间隙锁（Next-Key锁）
https://blog.csdn.net/xiaobluesky/article/details/50412069

意向锁

为什么需要意向锁？(提高表锁和行锁互斥排查的效率)

表是由行组成的，当我们向某个表加锁时，一方面需要检查该锁的申请是否与原有的表级锁相容；另一方面，还要检查该锁是否与表中的每一行上的锁相容。比如一个事务要在一个表上加 S 锁，如果表中的一行已被另外的事务加了 X 锁，那么该锁的申请也应被阻塞。如果表中的数据很多，逐行检查锁标志的开销将很大，系统的性能将会受到影响。为了解决这个问题，可以在表级引入新的锁类型来表示其所属行的加锁情况，这就引出了“意向锁”的概念。
意向锁的含义是如果对一个结点加意向锁，则说明该结点的下层结点正在被加锁；对任一结点加锁时，必须先对它的上层结点加意向锁。如：对表中的任一行加锁时，必须先对它所在的表加意向锁，然后再对该行加锁。这样一来，事务对表加锁时，就不再需要检查表中
每行记录的锁标志位了，系统效率得以大大提高。

所以，意向锁的目的就是提高表锁和行锁互斥排查的效率。

当再向一个表添加表级锁的时候

如果没有意向锁的话，则需要遍历所有整个表判断是否有行锁的存在，以免发生冲突。
如果有了意向锁，只需要判断该意向锁与即将添加的表级锁是否兼容即可。因为意向锁的存在代表了，有行级锁的存在或者即将有行级锁的存在。因而无需遍历整个表，即可获取结果。

意向共享锁(IS)与意向排它锁(IX)

由两种基本的锁类型（S锁、X 锁），可以自然地派生出两种意向锁：
意向共享锁（Intent Share Lock，简称 IS 锁）：如果要对一个数据库对象加S锁，首先要对其上级结点加IS 锁，表示它的后裔结点拟（意向）加 S锁；
意向排它锁（Intent Exclusive Lock，简称 IX 锁）：如果要对一个数据库对象加X 锁，首先要对其上级结点加 IX锁，表示它的后裔结点拟（意向）加X 锁。

Innodb引擎支持意向锁(自动加意向锁)

InnoDB还有两个表锁：
意向共享锁（IS）：表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁
意向排他锁（IX）：类似上面，表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。
意向锁是InnoDB自动加的，不需要用户干预。

意向锁（讲的很清楚）
https://blog.csdn.net/yabingshi_tech/article/details/30495065

InnoDB 的意向锁有什么作用？
https://www.zhihu.com/question/51513268

数据库的锁机制
http://blog.csdn.net/lexang1/article/details/52248686

乐观锁与悲观锁

乐观并发控制(乐观锁)和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。
无论是悲观锁还是乐观锁，都是人们定义出来的概念，可以认为是一种思想。其实不仅仅是关系型数据库系统中有乐观锁和悲观锁的概念，像memcache、hibernate、tair等都有类似的概念。
针对于不同的业务场景，应该选用不同的并发控制方式。所以，不要把乐观并发控制和悲观并发控制狭义的理解为DBMS中的概念，更不要把他们和数据中提供的锁机制（行锁、表锁、排他锁、共享锁）混为一谈。其实，在DBMS中，悲观锁正是利用数据库本身提供的锁机制来实现的。

乐观锁-实现:提交时对比版本号

乐观锁认为一个用户读数据的时候，别人不会去写自己所读的数据

乐观锁（ Optimistic Locking ）相对悲观锁而言，乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。

相对于悲观锁，在对数据库进行处理的时候，乐观锁并不会使用数据库提供的锁机制。一般的实现乐观锁的方式就是记录数据版本。

数据版本,为数据增加的一个版本标识。当读取数据时，将版本标识的值一同读出，数据每更新一次，同时对版本标识进行更新。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的版本标识进行比对，如果数据库表当前版本号与第一次取出来的版本标识值相等，则予以更新，否则认为是过期数据。

乐观并发控制相信事务之间的数据竞争(data race)的概率是比较小的，因此尽可能直接做下去，直到提交的时候才去锁定，所以不会产生任何锁和死锁。但如果直接简单这么做，还是有可能会遇到不可预期的结果，例如两个事务都读取了数据库的某一行，经过修改以后写回数据库，这时就遇到了问题。

悲观锁-实现:排它锁

悲观锁就刚好相反，觉得自己读数据库的时候，别人可能刚好在写自己刚读的数据，其实就是持一种比较保守的态度

悲观锁，正如其名，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度(悲观)，因此，在整个数据处理过程中，将数据处于锁定状态。

悲观锁的实现，往往依靠数据库本身提供的排它锁。

悲观并发控制实际上是“先取锁再访问”的保守策略，为数据处理的安全提供了保证。但是在效率方面，处理加锁的机制会让数据库产生额外的开销，还有增加产生死锁的机会；另外，在只读型事务处理中由于不会产生冲突，也没必要使用锁，这样做只能增加系统负载；还有会降低了并行性，一个事务如果锁定了某行数据，其他事务就必须等待该事务处理完才可以处理那行数

数据库的锁机制
http://blog.csdn.net/lexang1/article/details/52248686

数据库锁总结
https://www.cnblogs.com/ismallboy/p/5574006.html

数据库事务

事务(Transaction)是并发控制的基本单位。所谓的事务，它的根本是一个操作序列，这些操作都执行，或者都不执行，它是一个无法分割的工作单位。

事务的ACID特性

1 原子性(atomic)，事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行
2 一致性(consistent)，事务在完成时，必须使所有的数据都保持一致状态。
3 隔离性(insulation)，由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务之间互不干扰。
4 持久性(Duration)，事务完成之后，它对于系统的影响是永久性的。

脏读违反了ACID中的隔离性

对于read committed的事务隔离级别而言，由于其有脏读问题，从数据库理论的角度来看，其实违反了事务ACID中的I特性，即隔离性。

不可重复读违反了ACID中的一致性

不可重复读，两次读到的同一数据集但内容不一样，发生在读已提交隔离级别下，违反了一致性原则。

数据库事务的四大特性以及事务的隔离级别
https://www.cnblogs.com/fjdingsd/p/5273008.html

数据库并发性带来的问题

现在重点来说明下事务的隔离性，当多个线程都开启事务操作数据库中的数据时，数据库系统要能进行隔离操作，以保证各个线程获取数据的准确性，在介绍数据库提供的各种隔离级别之前，我们先看看如果不考虑事务的隔离性，会发生的几种问题：

更新丢失(同时写)

更新丢失，两个事务都同时更新一行数据，一个事务对数据的更新把另一个事务对数据的更新覆盖了。这是因为系统没有执行任何的锁操作，因此并发事务并没有被隔离开来。

脏读(读到未提交的)

脏读（Dirty Reads）：所谓脏读就是对脏数据（Drity Data）的读取，而脏数据所指的就是未提交的数据。也就是说，一个事务正在对一条记录做修改，在这个事务完成并提交之前，这条数据是处于待定状态的（可能提交也可能回滚），这时，第二个事务来读取这条没有提交的数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被称为脏读。

不可重复读(读的时候有人写)

不可重复读（Non-Repeatable Reads）：一个事务先后读取同一条记录，但两次读取的数据不同，我们称之为不可重复读。也就是说，这个事务在两次读取之间该数据被其它事务所修改。

幻读(读时有人插入)

幻读（Phantom Reads）：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为幻读。

事务的隔离级别

READ UNCOMMITTED 称为浏览访问( browse access)，仅仅针对事务而言的。
READ COMMITTED 称为游标稳定(cursor stability)。
REPEATABLE READ 是 2.9999度的隔离，没有幻读的保护。
SERIALIZABLE 称为隔离，或 3度的隔离。
SQL和SQL2标准的默认事务隔离级别是 SERIALIZABLE

读未提交(解决写丢失)

读未提交（Read UnCommited）
读未提交，顾名思义，就是一个事务可以读取另一个未提交事务的数据。
这是数据库最弱的隔离级别（完全不隔离），存在脏读、不可重复读、幻读的诸多问题。

如果一个事务已经开始写数据，则另外一个事务则不允许同时进行写操作，但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现。
避免了更新丢失，却可能出现脏读、不可重复读、幻读。也就是说事务B读取到了事务A未提交的数据。

读已提交RC(解决写丢失、脏读)

读已提交（Read Commited）
这个级别不允许事务B读取事务A还未提交的update操作更新后的数据（对于事务A的insert操作，在未提交之前，对事务B还是可见的）。
避免了脏读，但还可能出现不可重复读、幻读。
若有事务对数据进行更新（UPDATE）操作时，读操作事务要等待这个更新操作事务提交后才能读取数据，可以解决脏读问题。

读取提交的数据。但是，可能多次读取的数据结果不一致（不可重复读，幻读）。用读写的观点就是：读取的行数据，可以写。

脏读是指：在不同的事务下，当前事务可以读到另外事务未提交的数据。脏读也称为未提交读
脏读发生在隔离级别“READ UNCOMMITTED”下，而目前InnoDB默认的事务隔离级别为“READ REPEATABLE”。SQL Server默认为“READ COMMITTED”，Oracle同样也是“READ COMMITTED”

在MySQL 5.1中，READ COMMITTED 事务隔离级别默认只能工作在 bin log 为 ROW 的格式下。如果二进制日志工作在默认的STATEMENT下，会报错。

可重复读RR(解决写丢失、脏读、不可重复读)

不可重复读是指在一个事务内多次读取同一数据集合。在这个事务还没有结束时，另外一个事务页访问该同一数据接。并做了一些DML操作。因此，在第一个事务中的两次读数据之前，由于第二个事务的修改，那么第一个事务两次读到的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据时不一样的情况，这种情况称为“不可重复读”。也称为“提交读”

不可重读读与脏读的区别是：脏读是读到未提交的数据，而不可重复读读到的确实已经提交的数据，但是其违反了数据库事务一致性的要求

可重复读（Repeatable Read）
所有被Select获取的数据都不能被修改，这样就可以避免一个事务前后读取数据不一致的情况。但是却没有办法控制幻读，因为这个时候其他事务不能更改所选的数据，但是可以增加数据，因为前一个事务没有范围锁。
可避免脏读、不可重复读的发生。
重复读可以解决不可重复读问题。写到这里，应该明白的一点就是，不可重复读对应的是修改，即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作，而不是UPDATE操作。

可以重复读取，但有幻读。读写观点：读取的数据行不可写，但是可以往表中新增数据。在MySQL中，其他事务新增的数据，看不到，不会产生幻读。采用多版本并发控制（MVCC）机制解决幻读问题。

串行化(完全隔离，事务无法并发)

串行化(序列化)（Serialize）
事务之间最高的隔离界别，只能顺序的读取数据，当一个事务在读取和修改数据的时候，另外一个事务只能挂起，直到正在读取和修改数据的事务提交之后，挂起的事务才能执行。
可避免脏读、不可重复读、幻读的发生。
Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

常用数据库的默认隔离级别(InnoDB是可重复读)

隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。
大多数的数据库默认隔离级别为 Read Commited，比如 SqlServer、Oracle
少数数据库默认隔离级别为：Repeatable Read 比如： MySQL InnoDB
InnoDB存储引擎默认的事务隔离级别为READ REPEATABLE，Microsoft SQL Server数据库为READ COMMITTED，Oracle数据库同样也是READ COMMITTED。

InnoDB在可重复读隔离级别下如何避免幻读？

InnoDB 的默认隔离级别是 REPEATABLE READ ，但是与标准SQL不同的是，InnoDB 存储引擎在 REPEATABLE READ 隔离级别下，使用 Next-Key Lock 锁的算法避免了幻读的产生，达到了 SERIALIZABLE 隔离级别。这与其他数据库系统（例如SQL Server）不同。所以说，InnoDB 在默认的 REPEATABLE READ 隔离级别下已经能完全保证事务的隔离性要求，即达到SQL标准的 SERIALIZABLE 隔离级别

MySQL InnoDB 引擎的默认隔离级别虽然是「可重复读」，但是它很大程度上避免幻读现象（并不是完全解决了），解决的方案有两种：

针对 快照读（普通 select 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
针对 当前读（select … for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select … for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。

如何选择隔离级别？

大部分的用户质疑SERIALIZABLE隔离级别带来的性能问题，但是根据Jim Gray在《Transaction Processing》一书中指出，两者的开销是一样的，甚至SERIALIZABLE可能更优。
在InnoBD中选择REPEATABLE READ的事务隔离级别并不会有任何性能的损失。同样的，即使使用READ COMMITTED的隔离级别，用户也不会得到性能的大幅度提升。

因为 InnoDB 在 REPEATABLE READ 隔离级别下就可以达到3度的隔离，因此一般不在本地事务中使用 SERIALIZABLE 隔离级别。SERIALIZABLE 的事务隔离级别主要用于InnoDB存储引擎的分布式事务。

所以，如果没有特殊要求，使用默认的 RR 可重复读隔离级别是最优的。

理解事务的4种隔离级别
http://blog.csdn.net/qq_33290787/article/details/51924963

数据库中的-脏读，幻读，不可重复读
https://blog.csdn.net/d8111/article/details/2595635

事务的实现

redo log：重做日志，用来保证事务的原子性和持久性
undo log：回滚日志，用来保证事务的一致性

redo和undo的作用都可以视为一种恢复操作：
redo恢复提交事务修改的页操作
undo回滚行记录到某个特定版本

因此两者记录的内容也不同：
redo通常是物理日志，记录的是页的物理修改操作
undo是逻辑日志，根据每行记录进行记录

redo log 重做日志(原子性/持久性)

重做日志用来实现事务的持久性，即事务ACID中的D。
其由两部分组成：
一是内存中的重做日志缓冲（redo log buffer），其是易失的
二是重做日志文件（redo log file），其是持久的

InnoDB事务的存储引擎，其通过Force Log at Commit机制实现事务的持久性，即当事务提交（commit）时，必须先将该事务的所有日志写入重做日志文件进行持久化，待事务的COMMIT操作完成才算完成。
这里的日志是指重做日志，在InnoDB存储引擎中，由两部分组成：即redo log和undo log
redo log：用来保证事务的持久性。基本上都是顺序写的，在数据库运行时不需要对redo log的文件进行读取操作
undo log：用来帮助事务回滚及MVCC的功能。是需要进行随机读写的

为了确保每次日志都写入重做日志文件，在每次将重做日志缓冲写入重做日志文件后，InnoDB都需要调用一次fsync操作
由于重做日志文件打开并没有使用O_DIRECT选项，因此重做日志缓冲先写入文件系统缓存。为了确保重做日志写入磁盘，必须进行一次fsync操作
由于fsync的效率取决于磁盘的性能，因此磁盘的性能决定了事务提交的性能，也就是数据库的性能

默认情况下，事务提交时必须调用一次fsync操作，将redo log缓冲区刷入磁盘，所以磁盘性能决定了事务提交性能。

innodb_flush_log_at_trx_commit

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit
innodb_flush_log_at_trx_commit 参数该参数用来控制重做日志刷新到磁盘的策略
取值如下：
0：表示事务提交时不进行写入重做日志redo_log操作，这个操作仅在 master thread 中完成，而 master thread 中每 1 秒会进行一次重做日志文件的 fsync 操作
1（默认值）：表示事务提交时必须调用一次 fsync 操作
2：表示事务提交时将重做日志写入重做日志文件，但仅写入文件系统的缓存中，不进行 fsync 操作。在这个设置下，当 MySQL 数据库发生宕机而操作系统不发生宕机时，并不会导致事务的丢失。而当操作系统宕机时，重启数据库会丢失未从文件系统缓存刷新到重做日志文件那部分事务

我们生产系统的配置
sync_binlog 0
innodb_flush_log_at_trx_commit 0

mysql 数据安全双1配置

innodb_flush_log_at_trx_commit=1
sync_binlog=1

redo log 与 binlog的区别

二进制日志其用来进行POINT-IN-TIME（PIT）的恢复以及主从复制（Replication）环境的建立。从表面上看其和重做日志非常相似，都是记录对于数据库操作的日志。然而，从本质上看，两者有着非常大的不同。

1、redo log 日志是在InnoDB存储引擎层产生。
binlog 是在MySQL数据库的上层产生的，并且二进制日志不仅仅针对于InnoDB而言，MySQL数据库中的任何存储引擎对于数据库的更改都会产生二进制日志

2、两种日志记录的内容形式不同：
binlog 是一种逻辑日志，其记录的是对应的SQL语句
redo log 是物理格式日志，其记录的是对于每个页的修改

3、两种日志记录写入磁盘的时间点不同
binlog 只在事务提交完成后进行一次写入
redo log 在事务进行中不断地被写入，这表现为日志并不是随事务提交的顺序写入的

LSN日志序列号

LSN是Log Sequence Number的缩写，其代表的是日志序列号。在InnoDB存储引擎中，LSN占用8字节，并且单调递增
LSN表示的含义有：
重做日志写入的总量
checkpoint的位置
页的版本

LSN表示事务写入重做日志的字节的总量

启动时读redo log进行恢复

InnoDB在启动时不管上次数据库运行时是否正常关闭，都会尝试进行恢复操作
因为重做日志记录的是物理日志，因此恢复的速度比逻辑日志（如二进制日志）要快很多。与此同时，InnoDB自身也会恢复进行了一定程度的优化，如顺序读取及并行应用重做日志，这样可以进一步地提高数据库恢复的速度
由于checkpoint表示已经刷新到磁盘页上的LSN，因此在恢复过程中仅需恢复checkpoint开始的日志部分

MySQL(InnoDB剖析):39—事务之（事务的实现：redo log（重做日志）） - 照搬《MySQL技术内幕 InnoDB存储引擎》
https://blog.csdn.net/qq_41453285/article/details/104341454

undo log 回滚日志(一致性/事务回滚/MVCC)

重做日志记录了事务的行为，可以很好地通过其对页进行“重做”操作。但是事务有时还需要进行回滚操作，这时就需要undo。
因此在对数据库进行修改时，InnoDB存储引擎不但会产生redo，还会产生一定量的undo。
这样如果用户执行的事务或语句由于某种原因失败了，又或用户用一条rollback语句请求回滚，就可以利用这些undo信息将数据回滚到修改之前的样子

redo存放在重做日志文件中，与redo不同，undo存放在数据库内部的一个特殊段（segment）中，这个段称为undo段
undo段位于共享表空间内

undo回滚并不能将数据库物理恢复

用户通常对undo有这样的误解：undo用于将数据库物理地恢复到执行语句或事务之前的样子——但事实并非如此
undo是逻辑日志，因此只是将数据库逻辑地恢复到原来的样子。所有修改都被逻辑地取消了，但是数据结构和页本身在回滚之后可能大不相同。这是因为在多用户并发系统中，可能会有数十、数百甚至数千个并发事务。数据库的主要任务就是协调对数据记录的并发访问。比如，一个事务在修改当前一个页中某几条记录，同时还有别的事务在对同一个页中另几条记录进行修改。因此，不能将一个页回滚到事务开始的样子，因为这样会影响其他事务正在进行的工作

例如，用户执行了一个INSERT 10W条记录的事务，这个事务会导致分配一个新的段，即表空间会增大。在用户执行 ROLLBACK时，会将插入的事务进行回滚，但是表空间的大小并不会因此而收缩
因此，当 InnoDB存储引擎回滚时，它实际上做的是与先前相反的工作：
对于每个INSERT，InnoDB存储引擎会完成一个DELETE
对于每个DELETE，InnoDB存储引擎会执行一个INSERT
每个UPDATE，InnoDB存储引擎会执行一个相反的UPDATE，将修改前的行放回去

MVCC是通过undo log实现的

除了回滚操作，undo的另一个作用是MVCC，即在InnoDB存储引擎中MVCC的实现是通过undo来完成。当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo读取之前的行版本信息，以此实现非锁定读取。

MySQL(InnoDB剖析):40—事务之（事务的实现：undo log（回滚日志）） - 照搬《MySQL技术内幕 InnoDB存储引擎》
https://blog.csdn.net/qq_41453285/article/details/104346372

Spring事务的传播属性

当使用声明式事务模型时，您必须告诉容器如何去管理事务，例如，何时开启一个事务？哪些方法需要事务？当前不存在事务的情况下，容器是否需要为其添加事务控制？事实上，Spring提供了一个bean ——TransactionAttributSource，通过配置其事务（传播）属性（transactionattribute）来达到精确控制事务行为的目的。事务的属性总共有六种：

其中spring七个事物传播属性：
PROPAGATION_REQUIRED – 支持当前事务，如果当前没有事务，就新建一个事务。这是最常见的选择。
PROPAGATION_SUPPORTS – 支持当前事务，如果当前没有事务，就以非事务方式执行。
PROPAGATION_MANDATORY – 支持当前事务，如果当前没有事务，就抛出异常。
PROPAGATION_REQUIRES_NEW – 新建事务，如果当前存在事务，把当前事务挂起。
PROPAGATION_NOT_SUPPORTED – 以非事务方式执行操作，如果当前存在事务，就把当前事务挂起。
PROPAGATION_NEVER – 以非事务方式执行，如果当前存在事务，则抛出异常。
PROPAGATION_NESTED – 如果当前存在事务，则在嵌套事务内执行。如果当前没有事务，则进行与PROPAGATION_REQUIRED类似的操作。

分布式事务

什么是分布式事务？
简单的说，就是一次大的操作由不同的小操作组成，这些小的操作分布在不同的服务器上，且属于不同的应用，分布式事务需要保证这些小操作要么全部成功，要么全部失败。本质上来说，分布式事务就是为了保证不同数据库的数据一致性。

如果要实现分布式系统的原子性，则须保证所有节点的数据写操作，要不全部都执行（生效），要么全部都不执行（回滚）。但是，一个节点在执行本地事务的时候无法知道其它机器的本地事务的执行结果，所以它就不知道本次事务到底应该commit还是 roolback。常规的解决办法是引入一个“协调者”的组件来统一调度所有分布式节点的执行。

为什么需要分布式事务？

数据库垂直拆分

比如原来单机支撑了整个电商网站，现在对整个网站进行拆解，分离出了订单中心、用户中心、库存中心，分别对应订单数据库、用户数据库、库存数据库。这时候如果要同时对订单和库存进行操作，那么就会涉及到订单数据库和库存数据库，为了保证数据一致性，就需要用到分布式事务。

单表过大后分库分表

当数据库单表一年产生的数据超过1000W，那么就要考虑分库分表，具体分库分表的原理在此不做解释，以后有空详细说，简单的说就是原来的一个数据库变成了多个数据库。这时候，如果一个操作既访问01库，又访问02库，而且要保证数据的一致性，那么就要用到分布式事务。

深入理解分布式事务,高并发下分布式事务的解决方案
https://blog.csdn.net/mine_song/article/details/64118963

基于XA协议的两阶段提交

XA是一个分布式事务协议，由Tuxedo提出。
XA是由X/Open组织提出的分布式事务的规范。

XA规范主要定义了（全局）事务管理器（Transaction Manager）和（局部）资源管理器（Resource Manager）之间的接口。XA接口是双向的系统接口，在事务管理器（Transaction Manager）以及一个或多个资源管理器（Resource Manager）之间形成通信桥梁。XA引入的事务管理器充当上文所述全局事务中的“协调者”角色。事务管理器控制着全局事务，管理事务生命周期，并协调资源。资源管理器负责控制和管理实际资源（如数据库或JMS队列）。目前，Oracle、Informix、DB2、Sybase和PostgreSQL等各主流数据库都提供了对XA的支持。

两阶段提交(2PC)原理

两阶段提交(2PC, Two-phase Commit)
二阶段提交的算法思路可以概括为：协调者询问参与者是否准备好了提交，并根据所有参与者的反馈情况决定向所有参与者发送commit或者rollback指令（协调者向所有参与者发送相同的指令）。

所谓的两个阶段是指

prepare 准备阶段又称投票阶段。在这一阶段，协调者询问所有参与者是否准备好提交，参与者如果已经准备好提交则回复Prepared，否则回复Non-Prepared。
commit 提交阶段又称执行阶段。协调者如果在上一阶段收到所有参与者回复的Prepared，则在此阶段向所有参与者发送commit指令，所有参与者立即执行commit操作；否则协调者向所有参与者发送rollback指令，参与者立即执行rollback操作。

XA协议的缺点

总的来说，XA协议比较简单，而且一旦商业数据库实现了XA协议，使用分布式事务的成本也比较低。
但是，XA也有致命的缺点，那就是 性能不理想，特别是在交易下单链路，往往并发量很高，XA无法满足高并发场景。XA目前在商业数据库支持的比较理想，在mysql数据库中支持的不太理想，mysql的XA实现，没有记录prepare阶段日志，主备切换回导致主库与备库数据不一致。许多nosql也没有支持XA，这让XA的应用场景变得非常狭隘。

二阶段提交协议主要的问题是在提交执行过程中，所有的参与者都需要听从协调者的统一调度，期间处于阻塞状态而不能从事其他操作，这样效率及其低下。特别是当协调者发出提交通知到部分参与者后宕机，其他参与者就会阻塞。

java JTA

TA（Java Transaction API）事务

JTA允许应用程序执行分布式事务处理——在两个或多个网络计算机资源上访问并且更新数据，这些数据可以分布在多个数据库上。JDBC驱动程序的JTA支持极大地增强了数据访问能力。

分布式事务（一）两阶段提交及JTA
https://www.cnblogs.com/jasongj/p/5727897.html

深入理解分布式事务,高并发下分布式事务的解决方案
https://blog.csdn.net/mine_song/article/details/64118963

三阶段提交(3PC)

针对二阶段提交存在的问题，三阶段提交协议在prepare与commit阶段之间增加一个 pre-commit阶段。
Prepare阶段只询问参与者而不做事务，而在pre-commit阶段各个参与者才会执行本地事务但不提交。Commit阶段就是直接提交。
这样做可以避免二阶段当协调者迟迟没有发出commit或者rollback通知，参与者在超时后可以自行提交或者回滚，避免阻塞事务（这是因为经过了prepare阶段已经确认了各个参与者是可以执行的，最后第三阶段直接执行即可）。

基于事务型消息的分布式事务

基于消息中间件的事务消息来完成分布式事务。事务消息可以确保本地执行事务与消息发送是原子的：先发送一条消息到消息中间件，然后执行本地事务，当本地事务成功后再发送提交确认到消息中间件，然后这条消息才能被其他业务消费者所能感知，从而确保原子性。

这类事务机制将分布式事务分成多个本地事务，这里称之为主事务与从事务。首先主事务本地先行提交，然后通过消息通知从事务，从事务从消息中获取信息进行本地提交。可以看出这是一种异步事务机制、只能保证最终一致性；但可用性非常高，不会因为故障而发生阻塞。另外，主事务已经先行提交，如果因为从事务无法提交，要回滚主事务还是比较麻烦，所以这种模式只适用于理论上大概率等成功的业务情况，即从事务的提交失败可能是由于故障，而不大可能是逻辑错误。

事务型消息（主事务）的流程：
1、A系统向消息中间件发送一条预备消息
2、消息中间件保存预备消息并返回成功
3、A执行本地事务
4、A发送提交消息给消息中间件

基于消息中间件的两阶段提交往往用在高并发场景下，将一个分布式事务拆成一个消息事务（主事务，A系统的本地操作+发消息）+B系统的本地操作（从事务），其中B系统的操作由消息驱动，只要消息事务成功，那么A操作一定成功，消息也一定发出来了，这时候B会收到消息去执行本地操作，如果本地操作失败，消息会重投，直到B操作成功，这样就变相地实现了A与B的分布式事务。

深入理解分布式事务,高并发下分布式事务的解决方案
https://blog.csdn.net/mine_song/article/details/64118963

从银行转账失败到分布式事务：总结与思考
https://www.cnblogs.com/xybaby/p/7465816.html

TCC编程模式(Try,Confirm,Cancel)

所谓的TCC编程模式，也是两阶段提交的一个变种。TCC提供了一个编程框架，将整个业务逻辑分为三块：Try、Confirm和Cancel三个操作。
以在线下单为例，Try阶段会去扣库存，Confirm阶段则是去更新订单状态，如果更新订单失败，则进入Cancel阶段，会去恢复库存。
总之，TCC就是通过代码人为实现了两阶段提交，不同的业务场景所写的代码都不一样，复杂度也不一样，因此，这种模式并不能很好地被复用。

我们假设一个完整的业务包含一组子业务：
Try操作完成所有的子业务检查，预留必要的业务资源，实现与其他事务的隔离；
Confirm使用Try阶段预留的业务资源真正执行业务，而且Confirm操作满足幂等性，以遍支持重试；
Cancel操作释放Try阶段预留的业务资源，同样也满足幂等性。

一次完整的交易由一系列微交易的Try 操作组成，如果所有的Try 操作都成功，最终由微交易框架来统一Confirm，否则统一Cancel，从而实现了类似经典两阶段提交协议（2PC）的强一致性。

TCC由业务层保证原子性，需要较高的开发成本。

深入理解分布式事务,高并发下分布式事务的解决方案
https://blog.csdn.net/mine_song/article/details/64118963

从银行转账失败到分布式事务：总结与思考
https://www.cnblogs.com/xybaby/p/7465816.html

Saga模式

每个Saga由一系列 sub-transaction Ti 组成
每个Ti 都有对应的补偿动作Ci，补偿动作用于撤销Ti造成的结果

Saga的执行顺序有两种：
T1, T2, T3, …, Tn
T1, T2, …, Tj, Cj,…, C2, C1，其中0 < j < n

Saga定义了两种恢复策略：

backward recovery，向后恢复，补偿所有已完成的事务，如果任一子事务失败。即上面提到的第二种执行顺序，其中j是发生错误的sub-transaction，这种做法的效果是撤销掉之前所有成功的sub-transation，使得整个Saga的执行结果撤销。
forward recovery，向前恢复，重试失败的事务，假设每个子事务最终都会成功。适用于必须要成功的场景，执行顺序是类似于这样的：T1, T2, …, Tj(失败), Tj(重试),…, Tn，其中j是发生错误的sub-transaction。该情况下不需要Ci。

saga 要求子事务和补偿事务都必须是幂等的。

柔性事务(实用)

不追求整个操作过程中每一时刻的一致性（强一致性），转而追求最终结果的一致性（最终一致性）。

也即是说，在整个事务执行的流程中，我们是可以接受的短暂的数据不一致的，只要最后的结果没问题就行。

满足ACID的是刚性事务，只满足BASE（基本可用，软状态，最终一致性）的是柔性事务。

基于可靠消息一致性的柔性事务

消息发送一致性：是指产生消息的业务动作与消息发送的一致。也就是说，如果业务操作成功，那么由这个业务操作所产生的消息一定要成功投递出去(一般是发送到kafka、rocketmq、rabbitmq等消息中间件中)，否则就丢消息。

伪代码

@Transactional
public void test1(){
//1 数据库操作
//2 发送MQ消息
}

注意：
1、一定是先写库再发mq，如果先发mq，写库失败回滚后mq也收不回来了。
2、这种写法其实也无法保证写库和发mq的一致性，因为可能mq发送成功了只是ack响应超时，也会导致写库回滚但消息发出成功。

可以有如下两种方案来实现真正的消息发送一致性：
1、使用MQ的事务消息
事务消息的逻辑，由发送端 Producer进行保证(消费端无需考虑)
首先，发送一个事务消息，这个时候，RocketMQ将消息状态标记为Prepared(就是 RocketMQ half message 半消息)，注意此时这条消息消费者是无法消费到的。
接着，执行业务代码逻辑，可能是一个本地数据库事务操作
最后，确认发送消息，这个时候，RocketMQ将消息状态标记为可消费，这个时候消费者，才能真正的保证消费到这条数据。
如果确认消息发送失败了怎么办？RocketMQ会定期扫描消息集群中的事务消息，如果发现了Prepared消息，它会向消息发送端(生产者)确认。RocketMQ会根据发送端设置的策略来决定是回滚还是继续发送确认消息。这样就保证了消息发送与本地事务同时成功或同时失败。

2、假如MQ不支持事务消息，可消息先落库再发送。
将消息先发送到一个我们自己编写的一个”独立消息服务”应用中，刚开始处于prepare状态，业务逻辑处理成功后，确认发送消息，这个时候”独立消息服务”才会真正的把消息发送给消息队列。而定时扫描prepare状态的消息，向消息发送端(生产者)确认的工作也由独立消息服务来完成。
比如，发送消息时先将消息写入一个 message 表，将业务逻辑和消息落库message表放到一个事务中。消息落库message表即表示发送成功。之后有个异步的消息处理模块来发送消息并修改状态，发送成功的标为 success，失败的标为 fail。还要有个定时job不断的扫描发送失败的消息，尽最大努力将消息发出去。

7.0 柔性事务：可靠消息最终一致性 - 田守枝Java技术博客
http://www.tianshouzhi.com/api/tutorials/distributed_transaction/389

分布式事务 - 田守枝Java技术博客
http://www.tianshouzhi.com/api/tutorials/distributed_transaction

分库分表

主从分库

数据库集群、主从库，一主多从，主库写，从库读
mysql数据库集群，主从配置
主服务器（Master）负责网站NonQuery操作，从服务器负责Query操作，用户可以根据网站功能模特性块固定访问Slave服务器，或者自己写个池或队列，自由为请求分配从服务器连接。主从服务器利用MySQL的二进制日志文件，实现数据同步。二进制日志由主服务器产生，从服务器响应获取同步数据库。

如何解决主从延迟问题？

谈到MySQL数据库主从同步延迟原理，得从mysql的数据库主从复制原理说起，mysql的主从复制都是单线程的操作，主库对所有DDL和 DML产生binlog，binlog是顺序写，所以效率很高，slave的Slave_IO_Running线程到主库取日志，效率很比较高，下一步，问题来了，slave的Slave_SQL_Running线程将主库的DDL和DML操作在slave实施。DML和DDL的IO操作是随即的，不是顺序的，成本高很多，还可能可slave上的其他查询产生lock争用，由于Slave_SQL_Running也是单线程的，所以一个DDL卡主了，需要执行10分钟，那么所有之后的DDL会等待这个DDL执行完才会继续执行，这就导致了延时。有朋友会问：“主库上那个相同的DDL也需要执行10分，为什么slave会延时？”，答案是master可以并发，Slave_SQL_Running线程却不可以。

innodb_flush_log_at_trx_commit 和 sync_binlog 两个参数是控制MySQL磁盘写入策略以及数据安全性的关键参数。
show variables like “innodb_flush_log_at_trx_commit”;
show variables like “sync_binlog”;

我们生产系统的配置
sync_binlog 0
innodb_flush_log_at_trx_commit 0

innodb_flush_log_at_trx_commit 参数:
0: 由mysql的main_thread每秒将存储引擎log buffer中的redo日志写入到log file，并调用文件系统的sync操作，将日志刷新到磁盘。
1：每次事务提交时，将存储引擎log buffer中的redo日志写入到log file，并调用文件系统的sync操作，将日志刷新到磁盘。
2：每次事务提交时，将存储引擎log buffer中的redo日志写入到log file，并由存储引擎的main_thread 每秒将日志刷新到磁盘。

sync_binlog 参数：
0 ：存储引擎不进行 binlog 的刷新到磁盘，而由操作系统的文件系统控制缓存刷新。
1：每提交一次事务，存储引擎调用文件系统的 sync 操作进行一次缓存的刷新，这种方式最安全，但性能较低。
n：当提交的日志组=n时，存储引擎调用文件系统的sync操作进行一次缓存的刷新。

sync_binlog=0或sync_binlog大于1，事务被提交，而尚未同步到磁盘。因此，在电源故障或操作系统崩溃时有可能服务器已承诺尚未同步一些事务到二进制日志。因此它是不可能执行例行程序恢复这些事务，他们将会丢失二进制日志。

innodb_flush_log_at_trx_commit和sync_binlog 都为 1 时是最安全的，在mysqld 服务崩溃或者服务器主机crash的情况下，binary log 只有可能丢失最多一个语句或者一个事务。但是鱼与熊掌不可兼得，双1，1 会导致频繁的io操作，因此该模式也是最慢的一种方式。

innodb_flush_log_at_trx_commit和sync_binlog参数详解
https://zhuanlan.zhihu.com/p/34636277

按业务垂直拆分

垂直扩展，垂直划分，按业务分库

对应用透明的partition

利用数据库本身提供的 partition 机制，设定 partition key和规则，自动分子表，但还是在同一个库上。

冷数据按时间归档

对于不再修改的历史数据，比如订单历史，按时间归档

分库分表策略

Sharding的基本思想就要把一个数据库切分成多个部分放到不同的数据库(server)上，从而缓解单一数据库的性能问题。
不太严格的讲，对于海量数据的数据库，

如果是因为表多而数据多，这时候适合使用垂直切分，即把关系紧密（比如同一模块）的表切分出来放在一个server上。
如果表并不多，但每张表的数据非常多，这时候适合水平切分，即把表的数据按某种规则（比如按ID散列）切分到多个数据库(server)上。

数据库Sharding的基本思想和切分策略
http://blog.csdn.net/bluishglc/article/details/6161475

根据业务类型来具体考虑按哪个字段分表
比如：对于社交网站，几乎所有数据最终都会关联到某个用户上，基于用户进行切分就是最好的选择。再比如论坛系统，用户和论坛两个模块应该在垂直切分时被分在了两个shard里，对于论坛模块来说，Forum显然是聚合根，因此按Forum进行水平切分，把Forum里所有的帖子和回帖都随Forum放在一个shard里是很自然的。

大众点评订单分库分表方案

大众点评的订单分库分表方案：
为同时满足C端用户、B端商户、客服、运营等的需求，我们分别通过用户ID 和商户ID 进行切分，重复存储2分数据。
比如以用户id的切分为例，分为 32 个库 × 32 个表，先通过 UserId 后四位 mod 32 分到 32 个库中，同时再将 (UserId后四位除以32) Mod 32 分到一个库的 32 个表中，共计分为 1024 张表。

大众点评订单系统分库分表实践
https://tech.meituan.com/2016/11/18/dianping-order-db-sharding.html

美团到店分库分表策略

如何分库分表的？
美团不同业务的订单都是各自分离的，有个订单中台协调不同系统的订单。
用户使用集团的用户信息，不归到店系统管理。

到店订单分4个库，每个库中25个表，按user_id求余分表。这样同一个用户的订单都会在同一个表中。

按商家维度查订单，如何解决？比如查所有全聚德店铺的订单？
这个按user分表的数据库只用于处理online的用户c端业务（OLTP），保证请求的都是同一个user的数据。
后台管理端或者alap分析会去另一个库中查，通过binlog异步写一个产品维度的数据库，也是分库分表的。

多条件查询如何查？
es

分库分表中间件

MyCat

优点：
Mycat带来的最大好处就是使用是完全不用修改原有代码的，在mycat通过命令启动后，你只需要将数据库连接切换到Mycat的地址就可以了。
缺点：
不能完美解决主要是联表查询的问题，Mycat支持两个表联表的查询，多余两个表的查询不支持。其实，很多数据库中间件关于分表分库后查询的问题，都是需要自己实现的。

《MyCat权威指南》
http://www.mycat.io/document/Mycat_V1.6.0.pdf

sharding-jdbc

Apache ShardingSphere

Apache ShardingSphere 是一套开源的分布式数据库中间件解决方案组成的生态圈，它由 JDBC、Proxy 和 Sidecar（规划中）这 3 款相互独立，却又能够混合部署配合使用的产品组成。它们均提供标准化的数据分片、分布式事务和数据库治理功能，可适用于如 Java 同构、异构语言、云原生等各种多样化的应用场景。

它使用客户端直连数据库，以 jar 包形式提供服务，无需额外部署和依赖，可理解为增强版的 JDBC 驱动，完全兼容 JDBC 和各种 ORM 框架。

https://shardingsphere.apache.org/document/current/cn/overview/

分库分表后的跨表查询问题

跨节点Join的问题
只要是进行切分，跨节点Join的问题是不可避免的。但是良好的设计和切分却可以减少此类情况的发生。解决这一问题的普遍做法是分两次查询实现。在第一次查询的结果集中找出关联数据的id,根据这些id发起第二次请求得到关联数据。

跨节点的count,order by,group by以及聚合函数问题
这些是一类问题，因为它们都需要基于全部数据集合进行计算。多数的代理都不会自动处理合并工作。解决方案：与解决跨节点join问题的类似，分别在各个节点上得到结果后在应用程序端进行合并。和join不同的是每个结点的查询可以并行执行，因此很多时候它的速度要比单一大表快很多。但如果结果集很大，对应用程序内存的消耗是一个问题。

在多个表中查询，最后结果做union
优化方法：
1、双写，按name再做一次分表，这样会有数据冗余，空间换时间
2、还是按id分表，然后把id和name做一个映射放到缓存中，按name查询时先找到对应的id，再根据id去分表中查询。

双写，再以商家纬度进行hash存储，用队列手段来保障2个纬度的数据是一致的，当然这样会付出一定的冗余成本，但是由于需求是二维的所以很难再一套库表中取完全实现。

mysql分库后如何高效分页查询订单列表？
https://www.zhihu.com/question/31529593

MySQL订单分库分表多维度查询
http://blog.itpub.net/29254281/viewspace-2086198/

子表跟随父表的分表规则

MyCAT 借鉴了 NewSQL 领域的新秀 Foundation DB 的设计思路，Foundation DB 创新性的提出了 Table Group 的概念，其将子表的存储位置依赖于主表，并且物理上紧邻存放，因此彻底解决了 JION 的效率和性能问题，根据这一思路，提出了基于 E-R 关系的数据分片策略，子表的记录与所关联的父表记录存放在同一个数据分片上。

以常见的 user 用户表和 order 订单表为例，由于肯定是现有 user 再有 order，user 作为父表，order 作为子表。
user 表以 id % n 为分片规则，其中 n 是分表个数
order 表中肯定有一个 user_id 关联 user 表，所以 order 表也以 user_id % n 为分片规则
这样就能保证，同一个用户的订单和用户肯定在同一个分片上，就可以进行 join 了

分库分表后如何分页查询？

关于分库分表后的分页查询，58 架构师沈剑的这篇文章写的非常好
业界难题-“跨库分页”的四种方案
https://cloud.tencent.com/developer/article/1048654

假设有 n 个子表，查询请求的分页参数为：每页 count 条数据，查询第 offset 页

内存分页

当页数较小时，可以使用内存分页
（1）服务层将 order by time limit offset, count，改写成 order by time limit 0, offset+count，向 n 个子表分别发起sql查询
（2）服务层对得到的 n*(offset + count) 条数据进行内存排序，内存排序后再取偏移量 offset 后的 count 条记录

这种方法随着翻页的进行，性能越来越低。
比如每页 10 条数据，查第 1001 页的内容，也就是 offset = 1000*10 = 10000, count=10 ，每个子表上都要 limit 0, 10010，总共放到内存的就是 30030 条数据

聚合成单表(log流水类数据)

对于一些历史、流水、日志类的数据，可以聚合成一个单表，专门用于查询。
这类历史数据的特点是一旦产生后就不会再更改了，比如用户的行为历史，有下单历史、搜索历史、浏览历史，原本放在各自的表，可以聚合为一个单独的大表（通过定时任务，或者实时双写），专门用于分页查询行为历史。
但是对于需要不断更新的业务数据，这样的不太合适，比如用户信息user表，如果分子表后，还保留一个大表，每次修改都要双写。

偏移量转换为时间戳(禁止跳页)

（1）用内存分页的方法 limit 0, count 取得第一页数据，并得到第一页最后一条记录的时间戳 time_max
（2）每次翻页，将 order by time limit offset, count，改写成 where time > #{time_max} order by time limit count，向 n 个子表分别发起sql查询
（3）对返回的 ncount 条数据进行内存分页。
可以看到无论翻到多少页，每次内存排序的数据都是 ncount 个，不会随着翻页进行性能越来越低。

此方法要求：
1、两次查询必须是连续的，每次都记住上一次查询的最后一条记录的时间戳
2、时间戳足够精确，保证没有时间戳重复的数据。
业务上要求只保留“下一页”按钮，不能任意输入页码进行跳转。
“上一页”怎么办？
做成信息流形式的，查看过的页不删除，每次翻页只在当前页面下新增数据。

二次查询法

（1）将 order by time limit offset, count，改写成 order by time limit offset/n, count，向 n 个子表分别发起sql查询
（2）找到 n 个子表返回结果中的最小时间戳 time_min，并记录每个子表返回数据中的最大时间戳 time_i_max
（3）二次查询，在每个子表上查询 between 全局最小时间戳和此子表上次结果中最大时间戳之间的数据，order by time between #{time_min} and #{time_i_max}，向 n 个子表分别发起sql查询
（4）在返回结果集中设置虚拟 time_min ，找到 time_min 在各个分库的 offset，从而得到 time_min 在全局的offset
（5）得到了 time_min 在全局的offset，自然得到了全局的 limit offset, count

此方法要求：
1、时间戳足够精确，保证没有时间戳重复的数据。
2、分库数据均匀，每个分库的数据量大于 offset/n

近似查询

使用 patition key 进行分库，在数据量较大，数据分布足够随机的情况下，各分库所有非 patition key 属性，在各个分库上的数据分布，统计概率情况是一致的。

利用这一原理，要查询全局100页数据，limit offset, count 改写为 limit offset/n, count/n，每个分库偏移 offset/n ，获取 count/n 条数据，得到的数据集的并集，基本能够认为，是全局数据的 offset count 数据，当然，这一页数据的精度，并不是精准的。

对于搜索页面的翻页、帖子、邮件之类的数据，业务允许牺牲一定精度时，可以这么做。

不停机分库分表(在线扩容)

一致哈希

用sharding技术来扩展你的数据库（hash分布扩展，一致性哈希）
http://lib.csdn.net/article/architecture/5384

Pre-sharding

Redis的作者提出了一种叫做presharding的方案来解决动态扩容和数据分区的问题，实际就是在同一台机器上部署多个Redis实例的方式，当容量不够时将多个实例拆分到不同的机器上，这样实际就达到了扩容的效果。Pre-Sharding方法是将每一个台物理机上，运行多个不同端口的Redis实例，假如有三个物理机，每个物理机运行三个Redis实例，那么我们的分片列表中实际有9个Redis实例，当我们需要扩容时，增加一台物理机来代替9个中的一个redis，有人说，这样不还是9个么，是的，但是以前服务器上面有三个redis，压力很大的，这样做，相当于单独分离出来并且将数据一起copy给新的服务器。值得注意的是，还需要修改客户端被代替的redis的IP和端口为现在新的服务器，只要顺序不变，不会影响一致性哈希分片。

redis集群（Sharding）和在线扩容（Pre-Sharding）
https://blog.csdn.net/rosanu_blog/article/details/68066756

数据库分库分表(sharding)系列(五) 一种支持自由规划无须数据迁移和修改路由代码的Sharding扩容方案
https://blog.csdn.net/bluishglc/article/details/7970268

如何做到不停机分库分表迁移？
http://www.otpub.com/home/article/details/id/788.html

JPA和Hibernate

JPA和Hibernate 相关笔记在 Spring-Data-JPA

数据库连接池

c3p0和dbcp对比

DBCP有着比C3P0更高的效率，但是实际应用中，DBCP可能出现丢失连接的可能，而C3P0稳定性较高。

PostgreSQL和mysql区别？

mysql流行、简单、易上手。pg更稳定
MySQL早期的定位是轻量级数据库，虽然后来做了很多增强，比如事务支持，存储过程等，但和其它常见的关系数据库比起来SQL特性的支持仍比较弱，目前宽泛的SQL 99的子集。 PostgreSQL的定位是高级的对象关系数据库，从一开始对SQL标准的支持比较全面，目前支持大部分的SQL:2011特性。
MySQL是多线程模型，PostgreSQL是多进程模型。
MySQL的SQL层和存储层分离，支持多种存储引擎，例如InnoDB, MyISAM, NDB。PostgreSQL和绝大多数数据库一样是单存储引擎，但是PostgreSQL可以通过FDW( foreign-data wrapper )支持其它的储存形式，比如csv，mysql，hadoop等。
MySQL的数据库名直接对应于文件系统的目录名，表名直接对应于文件系统的文件名。这导致MySQL的数据库名和表名支持的字符以及是否大小写敏感都依赖于文件系统。PostgreSQL中的数据库对应于文件系统的目录，表对应于文件系统的文件，但目录名和文件名都是PostgreSQL内部的id号，不存在非法字符和大小写的问题。

MySQL和PostgreSQL的比较
http://blog.chinaunix.net/xmlrpc.php?r=blog/article&uid=20726500&id=5754874

TiDB/RocksDB/LevelDB

详见笔记 TiDB

当前位置 : 首页 » 文章分类 : 开发 » 面试准备06-数据库

数据库设计

Chen 陈氏ER图

Crow’s Foot 鱼尾纹ER图

SQL基础

sql查询执行流程

sql语句执行分解

SQL语句类型

数据定义语言DDL

数据操纵语言DML

数据查询语言DQL

数据控制语言DCL

SQL优化

谓词下推

索引下推(ICP)

SQL语句

case when

MySQL分页查询

使用limit offset, count

基于索引再排序

连接join

内连接(INNER JOIN)

外连接(OUTER JOIN)

交叉连接(CROSS JOIN)

group by与聚集函数

exists用法

关系数据库的范式

1NF(字段不可分)

2NF(非主属性完全依赖主键)

3NF(非主属性互相不依赖)

存储过程Procedure

索引

选择哪些字段作为索引？

索引过多带来的问题(空间,修改开销)

索引的基数、唯一索引、区分度(选择性)

前缀索引的长度选择

如何选择最优索引长度？

复合索引

全列匹配(优化器自动调整顺序)

最左前缀匹配

没有中间条件

无索引最左列

匹配某列的前缀字符串

范围查询

函数或表达式无法使用索引

索引排序

覆盖索引(不需回表)

B-Tree索引和Hash索引

B-Tree索引

Hash索引

聚集索引(聚簇索引 Clustered Index)

聚簇索引的叶节点存什么？(存储特点)

插入/删除数据行时的行为

什么时候适合用聚簇索引(范围查询,排序)

辅助索引(二级索引/非聚簇索引)

索引的存储结构

MyISAM是非聚簇索引

InnoDB是聚簇索引

InnoDB的B+树最多能存多少数据？

mysql 单表多少数据后要考虑分表？

为什么MySQL的索引要使用B+树而不是B树？

使用自增主键的好处？（b+树页移动,碎片）

MySQL给已存在的表增加索引时会锁表吗？

profile性能分析

使用 performance_schema 代替 profile

explain查看执行计划

explain 会触发真正的查询吗？

id

select_type

table

type(是否用到索引)

ALL 全表扫描

index 全索引扫描

index_merge 多索引合并

range 索引范围扫描

ref 非唯一索引等值匹配

eq_ref 唯一索引

const/system

NULL

possible_keys