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

Java面试准备-(05)数据库

数据库面试笔记


数据库

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语句

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 > (pageNum10) 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三范式就是要消除传递依赖。
如:学号,课程号,系别,系主任。系别和系主任是非主属性,学号和课程号是主属性。但是系别和系主任这两个非主属性之间有函数依赖关系:系别→系主任。必造成传函依赖:学号→系别→系主任。不符合3NF
解决方案:将这个非主属性与其依赖的码都拿出来单独建表,并设置被依赖的属性为主键,在原表中则用外键表示。

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

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


存储过程Procedure

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

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

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


索引

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


复合索引

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

如果我们的查询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 索引最左前缀原理
https://blog.csdn.net/zly9923218/article/details/51330995


explain查看执行计划

id

包含一组数字,表示查询中执行select子句或操作表的顺序
如果是子查询,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(是否用到索引)

表示MySQL在表中找到所需行的方式,又称“访问类型”,常见类型如下:
ALL, index, range, ref, eq_ref, const, system, NULL 从左到右,性能从最差到最好

  • ALL:Full Table Scan, MySQL将遍历全表以找到匹配的行
  • index:Full Index Scan,index与ALL区别为index类型只遍历索引树
  • range:索引范围扫描,对索引的扫描开始于某一点,返回匹配值域的行。显而易见的索引范围扫描是带有between或者where子句里带有<, >查询。当- mysql使用索引去查找一系列值时,例如IN()和OR列表,也会显示range(范围扫描),当然性能上面是有差异的。
  • ref:使用非唯一索引扫描或者唯一索引的前缀扫描,返回匹配某个单独值的记录行
  • eq_ref:类似ref,区别就在使用的索引是唯一索引,对于每个索引键值,表中只有一条记录匹配,简单来说,就是多表连接中使用primary key或者 unique key作为关联条件
  • const、system:当MySQL对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。如将主键置于where列表中,MySQL就能将该查询转换为一个常量
  • NULL: MySQL在优化过程中分解语句,执行时甚至不用访问表或索引,例如从一个索引列里选取最小值可以通过单独索引查找完成。

一般来说,得保证查询至少达到range级别,最好能达到ref。

possible_keys

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

key

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

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

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


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,…);

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

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

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

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

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

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

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

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

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

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

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

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

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

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


索引的存储结构

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

MyISAM是非聚簇索引

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

InnoDB是聚簇索引

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存储引擎

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恢复起来更困难,能否接受;

  1. 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中的行锁与表锁(行锁基于索引)

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

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

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

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

行级锁与死锁

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

在MySQL中,行级锁并不是直接锁记录,而是锁索引。索引分为主键索引和非主键索引两种,如果一条sql语句操作了主键索引,MySQL就会锁定这条主键索引;如果一条语句操作了非主键索引,MySQL会先锁定该非主键索引,再锁定相关的主键索引。 在UPDATE、DELETE操作时,MySQL不仅锁定WHERE条件扫描过的所有索引记录,而且会锁定相邻的键值,即所谓的next-key locking。

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

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


间隙锁(Cap 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是如何防止幻读的?

MySQL InnoDB支持三种行锁定方式:
1、行锁(Record Lock):锁直接加在索引记录上面。
2、间隙锁(Gap Lock):锁加在不存在的空闲空间,可以是两个索引记录之间,也可能是第一个索引记录之前或最后一个索引之后的空间。
3、Next-Key Lock:行锁与间隙锁组合起来用就叫做Next-Key Lock。

默认情况下,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 间隙锁 (转)
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


分布式锁

针对分布式锁的实现,目前比较常用的有以下几种方案:
基于数据库实现分布式锁
基于缓存(redis,memcached,tair)实现分布式锁
基于Zookeeper实现分布式锁

分布式锁的几种实现方式
http://blog.csdn.net/zdy0_2004/article/details/53070209

Java分布式锁三种实现方案
https://www.jianshu.com/p/535efcab356d

基于数据库表实现

要实现分布式锁,最简单的方式可能就是直接创建一张锁表,然后通过操作该表中的数据来实现了。

当我们要锁住某个方法或资源时,我们就在该表中增加一条记录,想要释放锁的时候就删除这条记录。

基于缓存实现分布式锁

相比较于基于数据库实现分布式锁的方案来说,基于缓存来实现在性能方面会表现的更好一点。而且很多缓存是可以集群部署的,可以解决单点问题。

目前有很多成熟的缓存产品,包括Redis,memcached

可以使用缓存来代替数据库来实现分布式锁,这个可以提供更好的性能,同时,很多缓存服务都是集群部署的,可以避免单点问题。并且很多缓存服务都提供了可以用来实现分布式锁的方法,比如Tair的put方法,redis的setnx方法等。并且,这些缓存服务也都提供了对数据的过期自动删除的支持,可以直接设置超时时间来控制锁的释放。

基于redis实现分布式锁

SETNX命令(SET if Not eXists)
语法:SETNX key value
功能:原子性操作,当且仅当 key 不存在,将 key 的值设为 value ,并返回1;若给定的 key 已经存在,则 SETNX 不做任何动作,并返回0。

第一种:使用redis的setnx()、expire()方法,用于分布式锁
setnx(lockkey, 1) 如果返回0,则说明占位失败;如果返回1,则说明占位成功
expire()命令对lockkey设置超时时间,为的是避免死锁问题。
执行完业务代码后,可以通过delete命令删除key。

注意:
1、为了防止加锁结点宕机后锁无法释放,需要给key设置一个过期时间,过期后锁自动释放
2、可能出现结点A把结点B的锁释放掉的问题
A结点加锁,设置过期时间,然后A结点由于某种原因阻塞了,过期时间到后锁自动释放,然后B结点也加了这个锁,B结点解锁之前A恢复了,A执行完事务后把B加的锁释放掉,然后B再释放锁时出现问题。
解决方法是锁中设置标志位,标识是哪个结点加的锁,只有加锁的结点才能释放锁。

基于Zookeeper实现分布式锁

基于zookeeper临时有序节点可以实现的分布式锁。

大致思想即为:每个客户端对某个方法加锁时,在zookeeper上的与该方法对应的指定节点的目录下,生成一个唯一的瞬时有序节点。
判断是否获取锁的方式很简单,只需要判断有序节点中序号最小的一个。
当释放锁的时候,只需将这个瞬时节点删除即可。同时,其可以避免服务宕机导致的锁无法释放,而产生的死锁问题。


数据库事务

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

事务的ACID特性

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

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


数据库并发性带来的问题

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

更新丢失(同时写)

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

脏读(读到未提交的)

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

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

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

幻读(读时有人插入)

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


事务的隔离级别

1、读未提交(解决写丢失)

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

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

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

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

读取提交的数据。但是,可能多次读取的数据结果不一致(不可重复读,幻读)。用读写的观点就是:读取的行数据,可以写。

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

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

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

4、串行化(完全隔离,事务无法并发)

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

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

隔离级别越高,越能保证数据的完整性和一致性,但是对并发性能的影响也越大。
大多数的数据库默认隔离级别为 Read Commited,比如 SqlServer、Oracle
少数数据库默认隔离级别为:Repeatable Read 比如: MySQL InnoDB

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

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


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指令(协调者向所有参与者发送相同的指令)。

所谓的两个阶段是指

  • 准备阶段 又称投票阶段。在这一阶段,协调者询问所有参与者是否准备好提交,参与者如果已经准备好提交则回复Prepared,否则回复Non-Prepared。
  • 提交阶段 又称执行阶段。协调者如果在上一阶段收到所有参与者回复的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)


基于消息的分布式事务

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

消息事务(主事务)的流程:
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


数据库优化

主从分库:主库写、从库读

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

热点数据放Redis缓存中

按业务垂直拆分

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


Sharding分库分表(单表过大)

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

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

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

切分策略(分表依据字段)

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

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

跨节点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/

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

一致哈希

用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


mybatis

随笔分类 - Mybatis
http://www.cnblogs.com/xiaoxi/category/929946.html

分类 - Mybatis
https://blog.csdn.net/hupanfeng/article/category/1443955

MyBatis的前身叫iBatis,本是apache的一个开源项目, 2010年这个项目由apache software foundation 迁移到了google code,并且改名为MyBatis。MyBatis是支持普通SQL查询,存储过程和高级映射的优秀持久层框架。MyBatis消除了几乎所有的JDBC代码和参数的手工设置以及结果集的检索。MyBatis使用简单的XML或注解用于配置和原始映射,将接口和Java的POJOs(Plan Old Java Objects,普通的Java对象)映射成数据库中的记录。


mybatis分层架构

Mybatis的功能架构分为三层:
1) API接口层:提供给外部使用的接口API,开发人员通过这些本地API来操纵数据库。接口层一接收到调用请求就会调用数据处理层来完成具体的数据处理。
2) 数据处理层:负责具体的SQL查找、SQL解析、SQL执行和执行结果映射处理等。它主要的目的是根据调用的请求完成一次数据库操作。
3) 基础支撑层:负责最基础的功能支撑,包括连接管理、事务管理、配置加载和缓存处理,这些都是共用的东西,将他们抽取出来作为最基础的组件。为上层的数据处理层提供最基础的支撑。


mybatis分层架构

深入浅出MyBatis-快速入门
https://blog.csdn.net/hupanfeng/article/details/9068003


mybatis拦截器(插件)

拦截器有什么用?

拦截器的一个作用就是我们可以拦截某些方法的调用,我们可以选择在这些被拦截的方法执行前后加上某些逻辑,也可以在执行这些被拦截的方法时执行自己的逻辑而不再执行被拦截的方法。Mybatis拦截器设计的一个初衷就是为了供用户在某些时候可以实现自己的逻辑而不必去动Mybatis固有的逻辑。打个比方,对于Executor,Mybatis中有几种实现:BatchExecutor、ReuseExecutor、SimpleExecutor和CachingExecutor。这个时候如果你觉得这几种实现对于Executor接口的query方法都不能满足你的要求,那怎么办呢?是要去改源码吗?当然不。我们可以建立一个Mybatis拦截器用于拦截Executor接口的query方法,在拦截之后实现自己的query方法逻辑,之后可以选择是否继续执行原来的query方法。

【myBatis】Mybatis中的拦截器
https://blog.csdn.net/moshenglv/article/details/52699976

拦截器的种类

MyBatis 允许你在已映射语句执行过程中的某一点进行拦截调用。默认情况下,MyBatis 允许使用插件来拦截的方法调用包括:
1、Executor (update, query, flushStatements, commit, rollback, getTransaction, close, isClosed)
2、ParameterHandler (getParameterObject, setParameters)
3、ResultSetHandler (handleResultSets, handleOutputParameters)
4、StatementHandler (prepare, parameterize, batch, update, query)

我们看到了可以拦截Executor接口的部分方法,比如update,query,commit,rollback等方法,还有其他接口的一些方法等。

总体概括为:
拦截执行器的方法
拦截参数的处理
拦截结果集的处理
拦截Sql语法构建的处理

MyBatis拦截器原理探究
http://www.importnew.com/24143.html

拦截器的实现原理(JDK动态代理)

Mybatis的拦截器实现机制,使用的是JDK的InvocationHandler.
当我们调用ParameterHandler,ResultSetHandler,StatementHandler,Executor的对象的时候,
实际上使用的是Plugin这个代理类的对象,这个类实现了InvocationHandler接口.
接下来我们就知道了,在调用上述被代理类的方法的时候,就会执行Plugin的invoke方法.
Plugin在invoke方法中根据@Intercepts的配置信息(方法名,参数等)动态判断是否需要拦截该方法.
再然后使用需要拦截的方法Method封装成Invocation,并调用Interceptor的proceed方法.
这样我们就达到了拦截目标方法的结果.


mybatis拦截器原理

Mybatis那些事-拦截器(Plugin+Interceptor)
https://blog.csdn.net/yhjyumi/article/details/49188051


Interceptor接口

Mybatis为我们提供了一个Interceptor接口,通过实现该接口就可以定义我们自己的拦截器。我们先来看一下这个接口的定义:

package org.apache.ibatis.plugin;

import java.util.Properties;

public interface Interceptor {

  Object intercept(Invocation invocation) throws Throwable;

  Object plugin(Object target);

  void setProperties(Properties properties);

}

我们可以看到在该接口中一共定义有三个方法,intercept、plugin和setProperties。plugin方法是拦截器用于封装目标对象的,通过该方法我们可以返回目标对象本身,也可以返回一个它的代理。当返回的是代理的时候我们可以对其中的方法进行拦截来调用intercept方法,当然也可以调用其他方法,这点将在后文讲解。setProperties方法是用于在Mybatis配置文件中指定一些属性的。

定义自己的Interceptor最重要的是要实现plugin方法和intercept方法,在plugin方法中我们可以决定是否要进行拦截进而决定要返回一个什么样的目标对象。而intercept方法就是要进行拦截的时候要执行的方法

【myBatis】Mybatis中的拦截器
https://blog.csdn.net/moshenglv/article/details/52699976


自定义拦截器

1、实现Interceptor接口,使用@Intercepts注解配置拦截信息,重写其中的方法,实现自己的逻辑
2、在配置文件中添加plugin元素,注册自定义的拦截器,及配置参数

【myBatis】Mybatis中的拦截器
https://blog.csdn.net/moshenglv/article/details/52699976

自定义拦截器示例:

package com.tiantian.mybatis.interceptor;

import java.sql.Connection;
import java.util.Properties;

import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;

@Intercepts( {
      @Signature(method = "query", type = Executor.class, args = {
              MappedStatement.class, Object.class, RowBounds.class,
              ResultHandler.class }),
      @Signature(method = "prepare", type = StatementHandler.class, args = { Connection.class }) })
public class MyInterceptor implements Interceptor {

    public Object intercept(Invocation invocation) throws Throwable {
      Object result = invocation.proceed();
      System.out.println("Invocation.proceed()");
      return result;
    }

    public Object plugin(Object target) {
      return Plugin.wrap(target, this);
    }

    public void setProperties(Properties properties) {
      String prop1 = properties.getProperty("prop1");
      String prop2 = properties.getProperty("prop2");
      System.out.println(prop1 + "------" + prop2);
    }

}

@Intercepts和@Signature注解

对于实现自己的Interceptor而言有两个很重要的注解,一个是@Intercepts,其值是一个@Signature数组。@Intercepts用于表明当前的对象是一个Interceptor,而@Signature则表明要拦截的接口、方法以及对应的参数类型。

MyInterceptor类上我们用@Intercepts标记了这是一个Interceptor,然后在@Intercepts中定义了两个@Signature,即两个拦截点。
第一个@Signature我们定义了该Interceptor将拦截Executor接口中参数类型为MappedStatement、Object、RowBounds和ResultHandler的query方法;
第二个@Signature我们定义了该Interceptor将拦截StatementHandler中参数类型为Connection的prepare方法。

setProperties()方法

这个方法在Configuration初始化当前的Interceptor时就会执行,用于获取相关配置参数。

plugin()方法

plugin方法是拦截器用于封装目标对象的,通过该方法我们可以返回目标对象本身,也可以返回一个它的代理。当返回的是代理的时候我们可以对其中的方法进行拦截来调用intercept方法

intercept()方法

定义自己的Interceptor最重要的是要实现plugin方法和intercept方法,在plugin方法中我们可以决定是否要进行拦截进而决定要返回一个什么样的目标对象。而intercept方法就是要进行拦截的时候要执行的方法。

在每个拦截器的intercept方法内,最后一个语句一定是returninvocation.proceed()(不这么做的话拦截器链就断了,你的mybatis基本上就不能正常工作了)。invocation.proceed()只是简单的调用了下target的对应方法,如果target还是个代理,就又回到了上面的Plugin.invoke方法了。这样就形成了拦截器的调用链推进。

深入浅出Mybatis-插件原理
https://blog.csdn.net/hupanfeng/article/details/9247379

Plugin类及wrap(),invoke()方法

MyBatis提供了一个Plugin类的实现,里面有一个静态方法wrap(Object target,Interceptor interceptor),通过该方法可以决定要返回的对象是目标对象还是对应的代理。
源码如下:

package org.apache.ibatis.plugin;

import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;

import org.apache.ibatis.reflection.ExceptionUtil;

public class Plugin implements InvocationHandler {

  private Object target;
  private Interceptor interceptor;
  private Map<Class<?>, Set<Method>> signatureMap;

  private Plugin(Object target, Interceptor interceptor, Map<Class<?>, Set<Method>> signatureMap) {
    this.target = target;
    this.interceptor = interceptor;
    this.signatureMap = signatureMap;
  }

  public static Object wrap(Object target, Interceptor interceptor) {
    Map<Class<?>, Set<Method>> signatureMap = getSignatureMap(interceptor);
    Class<?> type = target.getClass();
    Class<?>[] interfaces = getAllInterfaces(type, signatureMap);
    if (interfaces.length > 0) {
      return Proxy.newProxyInstance(
          type.getClassLoader(),
          interfaces,
          new Plugin(target, interceptor, signatureMap));
    }
    return target;
  }

  public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
    try {
      Set<Method> methods = signatureMap.get(method.getDeclaringClass());
      if (methods != null && methods.contains(method)) {
        return interceptor.intercept(new Invocation(target, method, args));
      }
      return method.invoke(target, args);
    } catch (Exception e) {
      throw ExceptionUtil.unwrapThrowable(e);
    }
  }

  private static Map<Class<?>, Set<Method>> getSignatureMap(Interceptor interceptor) {
  ...
  }

  private static Class<?>[] getAllInterfaces(Class<?> type, Map<Class<?>, Set<Method>> signatureMap) {
  ...
  }
}

我们先看一下Plugin的wrap方法,它根据当前的Interceptor上面的注解定义哪些接口需要拦截,然后判断当前目标对象是否有实现对应需要拦截的接口,如果没有则返回目标对象本身,如果有则返回一个代理对象。而这个代理对象的InvocationHandler正是一个Plugin。所以当目标对象在执行接口方法时,如果是通过代理对象执行的,则会调用对应InvocationHandler的invoke方法,也就是Plugin的invoke方法。

所以接着我们来看一下该invoke方法的内容。这里invoke方法的逻辑是:如果当前执行的方法是定义好的需要拦截的方法,则把目标对象、要执行的方法以及方法参数封装成一个Invocation对象,再把封装好的Invocation作为参数传递给当前拦截器的intercept方法。如果不需要拦截,则直接调用当前的方法。Invocation中定义了定义了一个proceed方法,其逻辑就是调用当前方法,所以如果在intercept中需要继续调用当前方法的话可以调用invocation的procced方法。

配置文件中注册自定义拦截器

注册拦截器是通过在Mybatis配置文件中plugins元素下的plugin元素来进行的。一个plugin对应着一个拦截器,在plugin元素下面我们可以指定若干个property子元素。Mybatis在注册定义的拦截器时会先把对应拦截器下面的所有property通过Interceptor的setProperties方法注入给对应的拦截器。

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties resource="config/jdbc.properties"></properties>
    <typeAliases>
      <package name="com.tiantian.mybatis.model"/>
    </typeAliases>
    <plugins>
      <plugin interceptor="com.tiantian.mybatis.interceptor.MyInterceptor">
          <property name="prop1" value="prop1"/>
          <property name="prop2" value="prop2"/>
      </plugin>
    </plugins>
    <environments default="development">
      <environment id="development">
          <transactionManager type="JDBC" />
          <dataSource type="POOLED">
              <property name="driver" value="${jdbc.driver}" />
              <property name="url" value="${jdbc.url}" />
              <property name="username" value="${jdbc.username}" />
              <property name="password" value="${jdbc.password}" />
          </dataSource>
      </environment>
    </environments>
    <mappers>
      <mapper resource="com/tiantian/mybatis/mapper/UserMapper.xml"/>
    </mappers>
</configuration>

拦截器工作工作流程

1、XMLConfigBuilder解析mybatis配置文件,通过反射实例化plugin节点中的interceptor属性表示的类。
2、然后调用全局配置类Configuration的addInterceptor方法,将此拦截器添加到拦截器链中。

public void addInterceptor(Interceptor interceptor) {
    interceptorChain.addInterceptor(interceptor);
}

这个interceptorChain是Configuration的内部属性,类型为InterceptorChain,也就是一个拦截器链,里面维护这一个ArrayList()

3、Configuration先后创建Executor,ParameterHandler,ResultSetHandler,StatementHandler这四个对象的实例,例化了对应的对象之后,都会调用interceptorChain的pluginAll方法,InterceptorChain的pluginAll刚才已经介绍过了,就是遍历所有的拦截器,然后调用各个拦截器的plugin方法。

4、Plugin的wrap(Object target,Interceptor interceptor)中,判断当前目标对象是否有实现对应需要拦截的接口,如果没有则返回目标对象本身,如果有则返回一个代理对象。如果是通过代理对象执行的,则会调用对应InvocationHandler的invoke方法,也就是Plugin的invoke方法。

5、Plugin的invoke方法中,如果当前执行的方法是定义好的需要拦截的方法,则把目标对象、要执行的方法以及方法参数封装成一个Invocation对象,再把封装好的Invocation作为参数传递给当前拦截器的intercept方法。

6、所以,需要拦截的方法最终被交给Interceptor接口实现类的intercept(Invocation invocation)方法去拦截执行。

MyBatis拦截器原理探究
http://www.importnew.com/24143.html


mybatis分页

逻辑分页(内存分页)和物理分页

物理分页
物理分页指的是在SQL查询过程中实现分页,依托与不同的数据库厂商,实现也会不同。比如MySQL数据库提供了limit关键字,程序员只需要编写带有limit关键字的SQL语句,数据库返回的就是分页结果。

逻辑分页
逻辑分页依赖的是程序员编写的代码。数据库返回的不是分页结果,而是全部数据,然后再由程序员通过代码获取分页数据,常用的操作是一次性从数据库中查询出全部数据并存储到List集合中,因为List集合有序,再根据索引获取指定范围的数据。

不同数据库的物理分页语句

不同数据库对物理分页语句的支持
mysql : limit
SQL service: top
oracle: rownum
PostgreSQL: limit

hibernate和MyBatis对分页的支持

常用orm框架采用的分页技术:
1、hibernate采用的是物理分页;
2、MyBatis使用RowBounds实现的分页是逻辑分页,也就是先把数据记录全部查询出来,然在再根据offset和limit截断记录返回(数据量大的时候会造成内存溢出),不过可以用插件或其他方式能达到物理分页效果。
为了在数据库层面上实现物理分页,又不改变原来MyBatis的函数逻辑,可以编写plugin截获MyBatis Executor的statementhandler,重写SQL来执行查询

对比和使用场景

对比
1、物理分页数据库压力大,逻辑分页服务器内存压力大
物理分页每次都访问数据库,逻辑分页只访问一次数据库,物理分页对数据库造成的负担大。
逻辑分页一次性将数据读取到内存,占用了较大的内容空间,物理分页每次只读取一部分数据,占用内存空间较小。
2、实时性
逻辑分页一次性将数据读取到内存,数据发生改变,数据库的最新状态不能实时反映到操作中,实时性差。物理分页每次需要数据时都访问数据库,能够获取数据库的最新状态,实时性强。

适用场合
逻辑分页主要用于数据量不大、数据稳定的场合,物理分页主要用于数据量较大、更新频繁的场合。

物理分页与逻辑分页
https://www.cnblogs.com/tonghun/p/7122801.html


利用MyBatis的RowBounds分页(逻辑分页)

Mybatis如何分页查询?Mysql中可以使用limit语句,但limit并不是标准SQL中的,如果是其它的数据库,则需要使用其它语句。MyBatis提供了RowBounds类,用于实现分页查询。RowBounds中有两个数字,offset和limit。

Mybatis的逻辑分页比较简单,简单来说就是取出所有满足条件的数据,然后舍弃掉前面offset条数据,然后再取剩下的数据的limit条

RowBounds使用方法

1、Mapper接口中的sql语句添加RowBounds参数

List<Student> queryStudentsBySql(RowBounds rowBounds);

2、Mapper.xml文件中不需要任何改动,还是原来的sql

3、代码中调用DAO的sql时传入RowBounds参数

public List<Student> queryStudentsBySql(int currPage, int pageSize) {
    return studentMapper.queryStudentsBySql(new RowBounds(currPage,pageSize));
}

RowBounds实现原理

逻辑分页的实现原理:
在DefaultResultSetHandler中,逻辑分页会将所有的结果都查询到,然后根据RowBounds中提供的offset和limit值来获取最后的结果。

DefaultResultSetHandler实现如下:

private void handleRowValuesForSimpleResultMap(ResultSetWrapper rsw, ResultMap resultMap, ResultHandler<?> resultHandler, RowBounds rowBounds, ResultMapping parentMapping)
      throws SQLException {
    DefaultResultContext<Object> resultContext = new DefaultResultContext<Object>();
    //跳过RowBounds设置的offset值
    skipRows(rsw.getResultSet(), rowBounds);
    //判断数据是否小于limit,如果小于limit的话就不断的循环取值
    while (shouldProcessMoreRows(resultContext, rowBounds) && rsw.getResultSet().next()) {
      ResultMap discriminatedResultMap = resolveDiscriminatedResultMap(rsw.getResultSet(), resultMap, null);
      Object rowValue = getRowValue(rsw, discriminatedResultMap);
      storeObject(resultHandler, resultContext, rowValue, parentMapping, rsw.getResultSet());
    }
  }

  private boolean shouldProcessMoreRows(ResultContext<?> context, RowBounds rowBounds) throws SQLException {
    //判断数据是否小于limit,小于返回true
    return !context.isStopped() && context.getResultCount() < rowBounds.getLimit();
  }

  //跳过不需要的行,应该就是rowbounds设置的limit和offset
  private void skipRows(ResultSet rs, RowBounds rowBounds) throws SQLException {
    if (rs.getType() != ResultSet.TYPE_FORWARD_ONLY) {
      if (rowBounds.getOffset() != RowBounds.NO_ROW_OFFSET) {
        rs.absolute(rowBounds.getOffset());
      }
    } else {
      //跳过RowBounds中设置的offset条数据
      for (int i = 0; i < rowBounds.getOffset(); i++) {
        rs.next();
      }
    }
  }

Mybatis逻辑分页原理解析RowBounds
https://blog.csdn.net/qq924862077/article/details/52611848


分页插件PageHelper(别人写好的过滤器)(物理分页)

官网
https://pagehelper.github.io/

使用方法:
1、添加jar包依赖
使用 PageHelper 你只需要在 classpath 中包含 pagehelper-x.x.x.jar 和 jsqlparser-0.9.5.jar。
如果你使用 Maven,你只需要在 pom.xml 中添加下面的依赖:

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>最新版本</version>
</dependency>

2、注册Plugin和配置参数
在MyBatis配置文件中:

<plugins>
    <plugin interceptor="com.github.pagehelper.PageHelper">
        <property name="dialect" value="mysql"/>
        <property name="offsetAsPageNum" value="false"/>
        <property name="rowBoundsWithCount" value="false"/>
        <property name="pageSizeZero" value="true"/>
        <property name="reasonable" value="false"/>
        <property name="supportMethodsArguments" value="false"/>
        <property name="returnPageInfo" value="none"/>
    </plugin>
</plugins>

3、Mapper接口中的sql语句无需任何改动
List queryStudentsBySql();

4、Mapper.xml文件中不需要任何改动,还是原来的sql

5、代码中调用DAO的sql时添加PageHelper:

public List<Student> queryStudentsBySql(int currPage, int pageSize) {
    PageHelper.startPage(currentPage, pageSize);
    return studentMapper.queryStudentsBySql(new RowBounds(currPage,pageSize));
}

优点:PageHelper的优点是,分页和Mapper.xml完全解耦,不侵入Mapper代码。实现原理是MyBatis拦截器,属于物理分页。

Mybatis分页插件PageHelper的配置和使用方法
https://www.cnblogs.com/kangoroo/p/7998433.html

分页的线程安全性(内部使用ThreadLocal保存分页参数)

什么时候会导致不安全的分页?

PageHelper 方法使用了静态的 ThreadLocal 参数,分页参数和线程是绑定的。

只要你可以保证在 PageHelper 方法调用后紧跟 MyBatis 查询方法,这就是安全的。因为 PageHelper 在 finally 代码段中自动清除了 ThreadLocal 存储的对象。

但是如果你写出下面这样的代码,就是不安全的用法:

PageHelper.startPage(1, 10);
List<Country> list;
if(param1 != null){
    list = countryMapper.selectIf(param1);
} else {
    list = new ArrayList<Country>();
}

这种情况下由于 param1 存在 null 的情况,就会导致 PageHelper 生产了一个分页参数,但是没有被消费,这个参数就会一直保留在这个线程上。当这个线程再次被使用时,就可能导致不该分页的方法去消费这个分页参数,这就产生了莫名其妙的分页。

Mybatis分页插件PageHelper简单使用
https://www.cnblogs.com/lxl57610/p/7766146.html


使用拦截器重写sql添加limit子句(物理分页)

MyBatis精通之路之分页功能的实现(数组分页、sql分页、拦截器,RowBounds分页)
https://blog.csdn.net/chenbaige/article/details/70846902

深入浅出Mybatis-分页
https://blog.csdn.net/hupanfeng/article/details/9265341


查出所有结果在代码中分页(逻辑分页)

进行数据库查询操作时,获取到数据库中所有满足条件的记录,保存在应用的临时数组中,再通过List的subList方法,获取到满足条件的所有记录。

缺点:数据库查询并返回所有的数据,而我们需要的只是极少数符合要求的数据。当数据量少时,还可以接受。当数据库数据量过大时,每次查询对数据库和程序的性能都会产生极大的影响。

手动写limit子句(物理分页)

直接在数据库语言中只检索符合条件的记录,不需要在通过程序对其作处理。
limit关键字的用法:
LIMIT [offset,] rows
offset指定要返回的第一行的偏移量(默认为0),rows第二个指定返回行的最大数目。初始行的偏移量是0(不是1)。

首先还是在StudentMapper接口中添加sql语句查询的方法,如下:
List queryStudentsBySql(Map<String,Object> data);

然后在StudentMapper.xml文件中编写sql语句通过limiy关键字进行分页:

<select id="queryStudentsBySql" parameterType="map" resultMap="studentmapper">
        select * from student limit #{currIndex} , #{pageSize}
</select>

代码中调用DAO层接口,传入两个参数:

public List<Student> queryStudentsBySql(int currPage, int pageSize) {
    Map<String, Object> data = new HashedMap();
    data.put("currIndex", (currPage-1)*pageSize);
    data.put("pageSize", pageSize);
    return studentMapper.queryStudentsBySql(data);
}

缺点:虽然这里实现了按需查找,每次检索得到的是指定的数据。但是每次在分页的时候都需要去编写limit语句,很冗余。而且不方便统一管理,维护性较差。所以我们希望能够有一种更方便的分页实现。


mybatis动态sql

#与$的区别

#{}: 解析为一个 JDBC 预编译语句(prepared statement)的参数标记符,一个 #{ } 被解析为一个参数占位符 。可以防止sql注入等等问题,所以在大多数情况下还是经常使用#{}
${}: 仅仅为一个纯碎的 string 替换,在动态 SQL 解析阶段将会进行变量替换。

#{}会在变量的值外面加引号
${}不对变量值进行任何更改直接拼接

sql注入举例

SELECT fieldlist FROM table WHERE field = ‘$EMAIL’;

EMAIL参数传入anything’ OR ‘x’=‘x,
SELECT fieldlist FROM table WHERE field = ‘anything’ OR ‘x’=’x’;
而使用


mybatis缓存

Mybatis中有一级缓存和二级缓存,默认情况下一级缓存是开启的,而且是不能关闭的。一级缓存是指SqlSession级别的缓存,当在同一个SqlSession中进行相同的SQL语句查询时,第二次以后的查询不会从数据库查询,而是直接从缓存中获取,一级缓存最多缓存1024条SQL。二级缓存是指可以跨SqlSession的缓存。

SqlSession内一级缓存

一级缓存是SqlSession级别的缓存。在操作数据库时需要构造 sqlSession对象,在对象中有一个数据结构(HashMap)用于存储缓存数据。不同的sqlSession之间的缓存数据区域(HashMap)是互相不影响的。

MyBatis 默认开启了一级缓存,一级缓存是在SqlSession 层面进行缓存的。即,同一个SqlSession ,多次调用同一个Mapper和同一个方法的同一个参数,只会进行一次数据库查询,然后把数据缓存到缓冲中,以后直接先从缓存中取出数据,不会直接去查数据库。

一级缓存的范围有SESSION和STATEMENT两种,默认是SESSION,如果我们不需要使用一级缓存,那么我们可以把一级缓存的范围指定为STATEMENT,这样每次执行完一个Mapper语句后都会将一级缓存清除。如果需要更改一级缓存的范围,请在Mybatis的配置文件中,在settings下通过localCacheScope指定。
<setting name="localCacheScope" value="SESSION"/>

跨SQLSession二级缓存(Mapper级)

二级缓存是mapper级别的缓存,多个SqlSession去操作同一个Mapper的sql语句,多个SqlSession可以共用二级缓存,二级缓存是跨SqlSession的。

二级缓存与一级缓存区别,二级缓存的范围更大,多个sqlSession可以共享一个Mapper的二级缓存区域。

二级缓存是mapper级别的,也就说不同的sqlsession使用同一个mapper查询是,查询到的数据可能是另一个sqlsession做相同操作留下的缓存。

每一个namespace的mapper都有一个二缓存区域,两个mapper的namespace如果相同,这两个mapper执行sql查询到数据将存在相同 的二级缓存区域中。

默认二级缓存是不开启的,需要手动进行配置。

<settings>
    <setting name="cacheEnabled" value="true" />
</settings>

Mybatis缓存介绍
https://blog.csdn.net/u010643307/article/details/70148723

mybatis一级与二级缓存详解
https://www.cnblogs.com/cuibin/articles/6827116.html

MyBatis 一、二级缓存和自定义缓存
https://www.cnblogs.com/moongeek/p/7689683.html

深入理解MyBatis——缓存
https://blog.csdn.net/qq_37169817/article/details/78985527


数据库连接池

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


上一篇 Java面试准备-(06)Redis和缓存

下一篇 2018年运动记录

阅读
30,576
阅读预计112分钟
创建日期 2018-04-03
修改日期 2018-11-20
类别
目录
  1. 数据库
    1. SQL基础
      1. sql查询执行流程
      2. sql语句执行分解
    2. SQL语句
      1. case when
      2. MySQL分页查询
        1. 使用limit offset, count
        2. 基于索引再排序
      3. 连接join
        1. 内连接(INNER JOIN)
        2. 外连接(OUTER JOIN)
        3. 交叉连接(CROSS JOIN)
      4. group by与聚集函数
      5. exists用法
    3. 关系数据库的范式
      1. 1NF(字段不可分)
      2. 2NF(对主键完全依赖)
      3. 3NF(非主属性互相不依赖)
    4. 存储过程Procedure
    5. 索引
      1. 选择哪些字段作为索引?
      2. 索引过多带来的问题(空间,修改开销)
      3. 索引的基数、唯一索引、区分度(选择性)
      4. 前缀索引的长度选择
        1. 如何选择最优索引长度?
      5. 复合索引
        1. 全列匹配(优化器自动调整顺序)
        2. 最左前缀匹配
        3. 没有中间条件
        4. 无索引最左列
        5. 匹配某列的前缀字符串
        6. 范围查询
        7. 函数或表达式无法使用索引
      6. explain查看执行计划
        1. id
        2. select_type
        3. table
        4. type(是否用到索引)
        5. possible_keys
        6. key
      7. B-Tree索引和Hash索引
        1. B-Tree索引
        2. Hash索引
      8. 聚集索引(聚簇索引 Clustered Index)
        1. 聚簇索引的叶节点存什么?(存储特点)
        2. 插入/删除数据行时的行为
        3. 什么时候适合用聚簇索引(范围查询,排序)
      9. 索引的存储结构
        1. MyISAM是非聚簇索引
        2. InnoDB是聚簇索引
    6. 存储引擎
      1. InnoDB存储引擎
      2. MyISAM存储引擎
      3. MEMORY存储引擎
      4. InnoDB和MyISAM区别(事务/行锁/外键/聚集)
      5. 如何选择存储引擎
    7. 数据库锁
      1. 共享锁和排它锁
        1. 共享锁(读锁)
        2. 排它锁(写锁)
        3. insert/update/delete自动加排它锁
        4. select可选手动加锁
      2. 按锁粒度分类
        1. 表级锁
        2. 页级锁
        3. 行级锁
        4. MySQL常用存储引擎的锁粒度
        5. Innodb中的行锁与表锁(行锁基于索引)
        6. 行级锁与死锁
        7. 间隙锁(Cap Lock)
          1. RR隔离级别的InnoDB是如何防止幻读的?
          2. 间隙锁的优缺点(防幻读/锁等待)
      3. 意向锁
        1. 为什么需要意向锁?(提高表锁和行锁互斥排查的效率)
        2. 意向共享锁(IS)与意向排它锁(IX)
        3. Innodb引擎支持意向锁(自动加意向锁)
      4. 乐观锁与悲观锁
        1. 乐观锁-实现:提交时对比版本号
        2. 悲观锁-实现:排它锁
    8. 分布式锁
      1. 基于数据库表实现
      2. 基于缓存实现分布式锁
        1. 基于redis实现分布式锁
      3. 基于Zookeeper实现分布式锁
    9. 数据库事务
      1. 事务的ACID特性
      2. 数据库并发性带来的问题
        1. 更新丢失(同时写)
        2. 脏读(读到未提交的)
        3. 不可重复读(读的时候有人写)
        4. 幻读(读时有人插入)
      3. 事务的隔离级别
        1. 1、读未提交(解决写丢失)
        2. 2、读已提交(解决写丢失、脏读)
        3. 3、可重复读(解决写丢失、脏读、不可重复读)
        4. 4、串行化(完全隔离,事务无法并发)
        5. 常用数据库的默认隔离级别(mysql是可重复读)
      4. Spring事务的传播属性
    10. 分布式事务
      1. 为什么需要分布式事务?
        1. 数据库垂直拆分
        2. 单表过大后分库分表
      2. 基于XA协议的两阶段提交
        1. 两阶段提交(2PC)原理
        2. XA协议的缺点
        3. java JTA
      3. 三阶段提交(3PC)
      4. 基于消息的分布式事务
      5. TCC编程模式(Try,Confirm,Cancel)
    11. 数据库优化
      1. 主从分库:主库写、从库读
      2. 热点数据放Redis缓存中
      3. 按业务垂直拆分
      4. Sharding分库分表(单表过大)
        1. 切分策略(分表依据字段)
        2. 分库分表后的跨表查询问题
        3. 不停机分库分表(在线扩容)
          1. 一致哈希
          2. Pre-sharding
  2. mybatis
    1. mybatis分层架构
    2. mybatis拦截器(插件)
      1. 拦截器有什么用?
      2. 拦截器的种类
      3. 拦截器的实现原理(JDK动态代理)
      4. Interceptor接口
      5. 自定义拦截器
        1. @Intercepts和@Signature注解
        2. setProperties()方法
        3. plugin()方法
        4. intercept()方法
        5. Plugin类及wrap(),invoke()方法
        6. 配置文件中注册自定义拦截器
        7. 拦截器工作工作流程
    3. mybatis分页
      1. 逻辑分页(内存分页)和物理分页
        1. 不同数据库的物理分页语句
        2. hibernate和MyBatis对分页的支持
        3. 对比和使用场景
      2. 利用MyBatis的RowBounds分页(逻辑分页)
        1. RowBounds使用方法
        2. RowBounds实现原理
      3. 分页插件PageHelper(别人写好的过滤器)(物理分页)
        1. 分页的线程安全性(内部使用ThreadLocal保存分页参数)
      4. 使用拦截器重写sql添加limit子句(物理分页)
      5. 查出所有结果在代码中分页(逻辑分页)
      6. 手动写limit子句(物理分页)
    4. mybatis动态sql
      1. #与$的区别
      2. sql注入举例
    5. mybatis缓存
      1. SqlSession内一级缓存
      2. 跨SQLSession二级缓存(Mapper级)
  3. 数据库连接池
    1. c3p0和dbcp对比
  4. PostgreSQL和mysql区别?
百度推荐