Java笔记-----（11）MySQL数据库

（1）MySQL的索引（重点掌握）

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息，就像一本书的目录一样，可以加快查询速度。

索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。
InnoDB 存储引擎的索引模型底层实现数据结构为B+树，所有数据都是存储在 B+ 树中的。

MySQL索引使用的数据结构主要有BTree索引 和 哈希索引 。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。

（1.0）如何添加索引

给age加上索引，索引方法为btree：

CREATE INDEX `index_age` ON `student`(`age`) USING BTREE;

ALTER TABLE `student` ADD INDEX `index_age`(`age`) USING BTREE; 

1.添加PRIMARY KEY(主键索引)
alter table `table_name` add primary key(`column`);

2.添加UNIQUE(唯一索引)
alter table `table_name` add unique(`column`);

3.添加普通索引
alter table `table_name` add index index_name(`column`);

4.添加全文索引
alter table `table_name` add fulltext(`column`);

5.添加多列索引
alter table `table_name` add index index_name(`column1`,`column2`,`column3`);

mysql 中添加索引的三种方法

参考文章：
mysql 中添加索引的三种方法

1.1 新建表中添加索引

① 普通索引

create table t_dept(
	no int not null primary key,
	name varchar(20) null,
	sex varchar(2) null,
	info varchar(20) null,
	index index_no(no)
)

② 唯一索引

create table t_dept(
	no int not null primary key,
	name varchar(20) null,
	sex varchar(2) null,
	info varchar(20) null,
	unique index index_no(no)
）

③ 全文索引

create table t_dept(
	no int not null primary key,
	name varchar(20) null,
	sex varchar(2) null,
	info varchar(20) null,
	fulltext index index_no(no)
)

④ 多列索引

create table t_dept(
	no int not null primary key,
	name varchar(20) null,
	sex varchar(2) null,
	info varchar(20) null,
	key index_no_name(no,name)
)

1.2 在已建表中添加索引

① 普通索引

create index index_name on t_dept(name);

② 唯一索引

create unique index index_name on t_dept(name);

③ 全文索引

create fulltext index index_name on t_dept(name);

④ 多列索引

create index index_name_no on t_dept(name,no);

1.3 以修改表的方式添加索引

① 普通索引

alter table t_dept add index index_name(name);

② 唯一索引

alter table t_dept add unique index index_name(name);

③ 全文索引

alter table t_dept add fulltext index_name(name);

④ 多列索引

alter table t_dept add index index_name_no(name,no);

（1.1）底层数据结构 B+ Tree 原理

参考文章：
拜托，别再问我什么是 B+ 树了
数据结构 4 时间复杂度、B-树 B+树 具体应用与理解
简单剖析B树（B-Tree）与Ｂ+树
B+树总结
B树和B+树的插入、删除图文详解

① 数据结构

B Tree 指的是 Balance Tree，也就是平衡树。平衡树是一颗查找树，并且所有叶子节点位于同一层。

B+ Tree 是基于 B Tree 和叶子节点顺序访问指针进行实现，它具有 B Tree 的平衡性，并且通过顺序访问指针来提高
区间查询的性能。

在 B+ Tree 中，一个节点中的 key 从左到右非递减排列，如果某个指针的左右相邻 key 分别是 key i 和 key i+1 ，且不
为 null，则该指针指向节点的所有 key 大于等于 key i 且小于等于 key i+1 。

② 操作

进行查找操作时，首先在根节点进行二分查找，找到一个 key 所在的指针，然后递归地在指针所指向的节点进行查
找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找出 key 所对应的 data。

插入删除操作会破坏平衡树的平衡性，因此在插入删除操作之后，需要对树进行一个分裂、合并、旋转等操作来维护
平衡性。

③ 与红黑树的比较

红黑树等平衡树也可以用来实现索引，但是文件系统及数据库系统普遍采用 B+ Tree 作为索引结构，主要有以下两个
原因：

（一）更少的查找次数
平衡树查找操作的时间复杂度和树高 h 相关，O(h)=O(log d N)，其中 d 为每个节点的出度。
红黑树的出度为 2，而 B+ Tree 的出度一般都非常大，所以红黑树的树高 h 很明显比 B+ Tree 大非常多，查找的次数
也就更多。

（二）利用磁盘预读特性
为了减少磁盘 I/O 操作，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读
取不需要进行磁盘寻道，并且只需要很短的磁盘旋转时间，速度会非常快。

操作系统一般将内存和磁盘分割成固定大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将
索引的一个节点的大小设置为页的大小，使得一次 I/O 就能完全载入一个节点。并且可以利用预读特性，相邻的节点
也能够被预先载入。

④ B+树和B树

• B+树是B树的变种，B+树的非叶子节点只用来保存索引，不存储数据，所有的数据都保存在叶子节点；而B树的非叶子节点也会保存数据。这样就使得B+树的查询效率更加稳定，均为从根节点到叶子节点的路径。
  B+树的搜索，插入和删除操作，时间复杂度都在O（logn）级别。
• B+树的内部结点并没有指向关键字具体信息的指针，因此其内部结点相对B 树更小，同样空间可以读入更多的节点，所以B+树的磁盘读写代价更低。

B树（Balance Tree），B树，平衡树，最大的优势就是自平衡
以及始终能够维持多路平衡，能够最大程度减少磁盘的IO次数而达到性能提升

为什么B+树查询性能更优？

• 单一节点储存了更多的元素。使得树更加矮胖，使得查询的IO次数更少；
• 查询都需要到达叶子节点，查询性能稳定；
• 所有叶子节点形成有序链表，查询更加迅速。

⑤ B+树和哈希表

• 哈希表是把索引字段映射成对应的哈希码然后再存放在对应的位置，这样的话，如果我们要进行模糊查找的话，显然哈希表这种结构是不支持的，只能遍历这个表。而B+树则可以通过最左前缀原则快速找到对应的数据。
• 如果我们要进行范围查找，例如查找ID为100 ~ 400的人，哈希表同样不支持，只能遍历全表。
• 索引字段通过哈希映射成哈希码，如果很多字段都刚好映射到相同值的哈希码的话，那么形成的索引结构将会是一条很长的链表，这样的话，查找的时间就会大大增加。

（1.2）MySQL 索引

索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。

① B+Tree 索引

是大多数 MySQL 存储引擎的默认索引类型。

因为不再需要进行全表扫描，只需要对树进行搜索即可，所以查找速度快很多。

因为 B+ Tree 的有序性，所以除了用于查找，还可以用于排序和分组。

可以指定多个列作为索引列，多个索引列共同组成键。

适用于全键值、键值范围和键前缀查找，其中键前缀查找只适用于最左前缀查找。如果不是按照索引列的顺序进行查找，则无法使用索引。

InnoDB 的 B+Tree 索引分为主索引和辅助索引。主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被
称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

辅助索引的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到
主索引中进行查找。

② 哈希索引

哈希索引能以 O(1) 时间进行查找，但是失去了有序性：

• 无法用于排序与分组；
• 只支持精确查找，无法用于部分查找和范围查找。

InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之
上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

③ 全文索引

MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。

查找条件使用 MATCH AGAINST，而不是普通的 WHERE。

全文索引使用倒排索引实现，它记录着关键词到其所在文档的映射。

InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。

④ 空间数据索引

MyISAM 存储引擎支持空间数据索引（R-Tree），可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。

必须使用 GIS 相关的函数来维护数据。

（1.3）聚簇索引和非聚簇索引

• 聚簇索引也称为主键索引，其索引树的叶子节点中存的是整行数据，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。因为索引（目录）只能按照一种方法进行排序。
• 非聚簇索引（普通索引）的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

主键索引和普通索引的案例：
建表语句：

create table User(
	id int primary key, 
	uid int not null, 
	name varchar(16),
index (uid))engine=InnoDB;

插入数据：

insert into User values(1,21,'zhangsan'),(2,22,lisi),(3,23,'wangwu'),(5,25,'ywq'),(6,26,'dym');

主键上自动创建了主键索引，并且我们手动在uid字段上创建的普通索引。
主键索引树和普通索引树的形状：
其中R代表一整行的值。

左边主键索引树的叶子节点存储的是完整的记录，而普通索引树上存储的是其对应的主键的值。

（1.4）MySQL回表

如果语句是 select * from User where id=3，即主键查询方式，则只需要搜索 主键索引树。
如果语句是 select * from User where uid=23，即普通索引查询方式，则需要先搜索 普通索引树，得到其对应的主键值为 3，再到主键索引树搜索一次。这个过程称为回表。

（1.5）覆盖索引

如果在普通索引树上的查询已经直接提供了结果，不需要回表操作，这样的普通索引叫做覆盖索引。覆盖索引的使用可以显著提高查询效率，是常见的MySQL性能优化手段。

for example：

（A）select id from User where uid = 23
在uid索引树中保存有uid和id的值，不需要回表

（B）select name from User where uid = 23
在uid索引树中找到对应主键id，通过回表在主键索引树上找到满足的数据

当sql语句的所求查询字段（select列）和查询条件字段（where子句）全都包含在一个索引中，可以直接使用索引查询而不需要回表。这就是覆盖索引，通过使用覆盖索引，可以减少搜索树的次数。

（1.6）索引的最左前缀原则（重点掌握）

可以用来模糊查找，使用哈希索引则不行

在联合索引的情况下，不需要索引的全部定义，只要满足最左前缀，就可以利用索引来加快查询速度。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。最左前缀原则的利用也可以显著提高查询效率，是常见的MySQL性能优化手段。

例如对于下面这一张表

如果我们按照 name 字段来建立索引的话，采用B+树的结构，大概的索引结构如下

如果我们要进行模糊查找，查找name 以“张"开头的所有人的ID，即 sql 语句为

select ID from table where name like '张%'

由于在B+树结构的索引中，索引项是按照索引定义里面出现的字段顺序排序的，索引在查找的时候，可以快速定位到 ID 为 100 的张一，然后直接向右遍历所有张开头的人，直到条件不满足为止。

也就是说，我们找到第一个满足条件的人之后，直接向右遍历就可以了，由于索引是有序的，所有满足条件的人都会聚集在一起。

而这种定位到最左边，然后向右遍历寻找，就是我们所说的最左前缀原则。

（1.7）联合索引（重点掌握）

以联合索引(a,b,c)为例，建立这样的索引相当于建立了索引a、ab、abc三个索引。

参考：多个单列索引和联合索引的区别详解
记忆：联合索引是（a，b，c），则查询条件a，b，c，（a&b），（b&a），（a&c），（c&a），（a&b&c）有效

查看文章理解b，c，（b&a），（c&a）为什么有效 ：
mysql索引最左匹配原则的理解?
问题是对（a，b）建索引，为什么查询b，（b&a）仍然有效？ （与MySQL查询优化器有关）
引用一下@沈杰的回答：
观察explain结果中的type字段。

• 查询 b 时，type：index。
• 查询（b&a时），type：ref。

解释：
index：这种类型表示是mysql会对整个该索引进行扫描。要想用到这种类型的索引，对这个索引并无特别要求，只要是索引，或者某个复合索引的一部分，mysql都可能会采用index类型的方式扫描。但是呢，缺点是效率不高，mysql会从索引中的第一个数据一个个的查找到最后一个数据，直到找到符合判断条件的某个索引。

ref：这种类型表示mysql会根据特定的算法快速查找到某个符合条件的索引，而不是会对索引中每一个数据都进行一一的扫描判断，也就是所谓你平常理解的使用索引查询会更快的取出数据。而要想实现这种查找，索引却是有要求的，要实现这种能快速查找的算法，索引就要满足特定的数据结构。简单说，也就是索引字段的数据必须是有序的，才能实现这种类型的查找，才能利用到索引。

Q：有些了解的人可能会问，索引不都是一个有序排列的数据结构么。不过答案说的还不够完善，那只是针对单个索引，而复合索引的情况有些同学可能就不太了解了。

下面就说下复合索引：
以该表的(name,cid)复合索引为例,它内部结构简单说就是下面这样排列的：

mysql创建复合索引的规则是首先会对复合索引的最左边的，也就是第一个name字段的数据进行排序，在第一个字段的排序基础上，然后再对后面第二个的cid字段进行排序。其实就相当于实现了类似 order by name cid这样一种排序规则。

所以：第一个name字段是绝对有序的，而第二字段就是无序的了。所以通常情况下，直接使用第二个cid字段进行条件判断是用不到索引的，当然，可能会出现上面的使用index类型的索引。这就是所谓的mysql为什么要强调最左前缀原则的原因。

Q：那么什么时候才能用到呢?
当然是cid字段的索引数据也是有序的情况下才能使用咯，什么时候才是有序的呢？观察可知，当然是在name字段是等值匹配的情况下，cid才是有序的。发现没有，观察两个name名字为 c 的cid字段是不是有序的呢。从上往下分别是4 5。

这也就是mysql索引规则中要求复合索引要想使用第二个索引，必须先使用第一个索引的原因。（而且第一个索引必须是等值匹配）。

Q：建的索引是(name,cid)。而我查询的语句是cid=1 AND name=‘小红’; 我是先查询cid，再查询name的，不是先从最左面查的呀？
首先可以肯定的是把条件判断反过来变成这样 name=‘小红’ and cid=1; 最后所查询的结果是一样的。

Q：那么问题产生了？既然结果是一样的，到底以何种顺序的查询方式最好呢？
所以，而此时那就是我们的mysql查询优化器该登场了，mysql查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。所以，当然是我们能尽量的利用到索引时的查询顺序效率最高咯，所以mysql查询优化器会最终以这种顺序进行查询执行。

mysql查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。所以，当然是我们能尽量的利用到索引时的查询顺序效率最高咯，所以mysql查询优化器会最终以这种顺序进行查询执行。

（1.8）索引下推

在MySQL5.6之前，只能从根据最左前缀查询到ID开始一个个回表。到主键索引上找出数据行，再对比字段值。MySQL5.6引入的索引下推优化，（联合索引前提）可以在索引遍历过程中，对索引中包含的其余字段先做判断，直接过滤掉不满足条件的记录，减少回表次数，提升查询效率。

（1.9）哪些列上适合创建索引？创建索引有哪些开销？

经常需要作为条件查询的列上适合创建索引，并且该列上也必须有一定的区分度。创建索引需要维护，在插入数据的时候会重新维护各个索引树（数据页的分裂与合并），对性能造成影响。

（1.10）为什么建议使用主键自增的索引？（页分裂和页合并）

为了在插入数据的时候不需要调整主键索引树的结构，强烈建议在建立表的时候使用自增主键。主键的顺序按照数据记录的插入顺序排列，自动有序。

对于这颗主键索引的树：

如果我们插入 ID = 650 的一行数据，那么直接在最右边插入就可以了

但是如果插入的是 ID = 350 的一行数据，由于 B+ 树是有序的，那么需要将下面的叶子节点进行移动，腾出位置来插入 ID = 350 的数据，这样就会比较消耗时间，如果刚好 R4 所在的数据页已经满了，需要进行页分裂操作，这样会更加糟糕。

但是，如果我们的主键是自增的，每次插入的 ID 都会比前面的大，那么我们每次只需要在后面插入就行， 不需要移动位置、分裂等操作，这样可以提高性能。也就是为什么建议使用主键自增的索引。

① 解页（page）

在计算机里，无论是内存还是磁盘，操作系统都是按页的大小进行读取的（页大小通常为 4 kb），磁盘每次读取都会预读，会提前将连续的数据读入内存中，这样就避免了多次 IO，这就是计算机中有名的局部性原理，即我用到一块数据，很大可能这块数据附近的数据也会被用到，干脆一起加载，省得多次 IO 拖慢速度， 这个连续数据有多大呢，必须是操作系统页大小的整数倍，这个连续数据就是 MySQL 的页，默认值为 16 KB，也就是说对于 B+ 树的节点，最好设置成页的大小（16 KB），这样一个 B+ 树上的节点就只会有一次 IO 读。

那有人就会问了，这个页大小是不是越大越好呢，设置大一点，节点可容纳的数据就越多，树高越小，IO 不就越小了吗，这里要注意，页大小并不是越大越好，InnoDB 是通过内存中的缓存池（pool buffer）来管理从磁盘中读取的页数据的。页太大的话，很快就把这个缓存池撑满了，可能会造成页在内存与磁盘间频繁换入换出，影响性能。

通过以上分析，相信我们不难猜测出 N 叉树中的 N 该怎么设置了，只要选的时候尽量保证每个节点的大小等于一个页（16kb）的大小即可。

转载自文章：拜托，别再问我什么是B+树了

② 页分裂与页合并

如果不是自增主键，插入或更新一条记录的时候，会引起页分裂，导致性能下降。

当删除记录的时候，索引也要删除，此时可能发生页合并，否则分散的结点可能造成多次IO读，影响查找效率。

设定页合并的阈值，比如对于 N 叉树来说，当节点的个数小于 N/2 的时候就应该和附近的节点合并，不过需要注意的是合并后节点里的元素大小可能会超过 N，造成页分裂，需要再对父节点等进行调整以让它满足 N 叉树的条件。

（1.11）索引失效

参考文章：索引失效的7种情况

1.有or必全有索引;
2.复合索引未用左列字段;
3.like以%开头;
4.需要类型转换;
5.where中索引列有运算;
6.where中索引列使用了函数;
7.如果mysql觉得全表扫描更快时（数据少）;

（1.12）索引优化

① 独立的列

在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引。
例如下面的查询不能使用 actor_id 列的索引：

SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

② 多列索引

在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。例如下面的语句中，最好把
actor_id 和 film_id 设置为多列索引。

SELECT film_id, actor_ id FROM sakila.film_actor WHERE actor_id = 1 AND film_id = 1;

③ 索引列的顺序

让选择性最强的索引列放在前面。

索引的选择性是指：不重复的索引值和记录总数的比值。最大值为 1，此时每个记录都有唯一的索引与其对应。选择性越高，每个记录的区分度越高，查询效率也越高。

例如下面显示的结果中 customer_id 的选择性比 staff_id 更高，因此最好把 customer_id 列放在多列索引的前面。

SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
		COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
		COUNT(*)
FROM payment;

staff_id_selectivity: 0.0001
customer_id_selectivity: 0.0373
COUNT(*): 16049

④ 前缀索引

对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符。
前缀长度的选取需要根据索引选择性来确定。

⑤ 覆盖索引

索引包含所有需要查询的字段的值。
具有以下优点：

• 索引通常远小于数据行的大小，只读取索引能大大减少数据访问量。
• 一些存储引擎（例如 MyISAM）在内存中只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以 不使用系统调用（通常比较费时）。
• 对于 InnoDB 引擎，若辅助索引能够覆盖查询，则无需访问主索引。

（1.13）索引的优点

• 大大减少了服务器需要扫描的数据行数。
• 帮助服务器避免进行排序和分组，以及避免创建临时表（B+Tree 索引是有序的，可以用于 ORDER BY 和 GROUP BY操作。临时表主要是在排序和分组过程中创建，不需要排序和分组，也就不需要创建临时表）。
• 将随机 I/O 变为顺序 I/O（B+Tree 索引是有序的，会将相邻的数据都存储在一起）。

（1.14）索引的使用条件

• 对于非常小的表、大部分情况下简单的全表扫描比建立索引更高效；
• 对于中到大型的表，索引就非常有效；
• 但是对于特大型的表，建立和维护索引的代价将会随之增长。这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录地匹配，例如可以使用分区技术。

（2）MySQL常见的 存储引擎 有哪些？（重点掌握）

（2.0）一条查询sql的执行流程和底层原理

参考文章：
一条查询sql的执行流程和底层原理

1、一条查询SQL执行流程图

2、查询SQL执行流程之发送SQL请求

（1）客户端按照Mysql通信协议将SQL发送到服务端，SQL到达服务端后，服务端会单起一个线程执行SQL。
（2）执行时Mysql首先判断SQL的前6个字符是否为select。并且语句中是否带有SQL_NO_CACHE关键字，如果没有则进入查询缓存。

3、查询SQL执行流程之查询缓存

查询缓存说白了就是一个哈希表，将执行过的语句及其结果以键值对的格式缓存到内存中。其中key是一个哈希值，由查询SQL、当前要查询的数据库、客户端协议版本等生成的，value就是查询结果。如果要绕过查询缓存，可以在SQL中加SQL_NO_CACHE字段，如：

SELECT SQL_NO_CACHE * FROM table

注：Mysql8.0版本开始取消查询缓存

4、查询SQL执行流程之解析器

解析器执行流程分为两个阶段，词法解析和语法解析
（1）首先对SQL词法进行分析，将SQL从左到右一个字符、一个字符地输入，然后根据构词规则识别单词。将会生成4个Token，如下所示：

（2）然后对SQL语法进行解析，判断客户端传入的SQL语句是否满足Mysql语法。此时会生成一颗语法树，如下所示：

如果语法不对，将会收到如下提示

You have an error in your SQL syntax

如果解析器顺利生成语法树，就会将SQL送发到预处理器

5、查询SQL执行流程之预处理器

预处理器主要做两件事情，查看SQL中列名是否正确和权限验证
（1）首先判断SQL语句中的列名是否存在于数据表中，再看看表名是否正确，如果不对，将返回如下错误提示

Unknown column xxx in ‘where clause’

（2）预处理器对SQL进行权限验证，判断SQL是否有操作这个表的权限，若没有，则会返回如下错误信息

ERROR 1142 (42000): SELECT command denied to user 'root'@'localhost' for table 'xxx'

一切验证通过后将语法树传递给优化器

6、查询SQL执行流程之优化器

优化器的任务就是对SQL语句进行优化，达到最快的执行效果，优化器对SQL优化完成后会将SQL变成一个执行计划交给执行器

7、查询SQL执行流程之执行器

执行器就是根据执行计划来进行执行查询， 根据SQL的指令，逐条调用底层存储引擎，逐步执行。
MySQL定义了一系列抽象存储引擎API，以支持插件式存储引擎架构。Mysql实现了一个抽象接口层，叫做 handler(sql/handler.h)，其中定义了接口函数，比如：ha_open, ha_index_end, ha_create等等，存储引擎需要实现这些接口才能被系统使用。

（2.1）MySQL的基本逻辑架构图

由MySQL的逻辑架构图我们可以看出，逻辑架构包括Server层和存储引擎层。其中Server层包括连接器，分析器，优化器以及执行器；存储引擎层包括多种支持的存储引擎。各个逻辑部件的作用如下：

• 连接器：验证客户端权限，建立和断开MySQL连接
• 分析器：进行SQL语句的语法分析
• 优化器：选择索引，生成具体的SQL语句执行计划 （查询优化器，为什么联合索引（b&a）有效）
• 执行器：操作存储引擎，执行SQL，返回执行结果
• 存储引擎层：各个不同的存储引擎都提供了一些读写接口来操作数据库

（2.2）InnoDB

是 MySQL 默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其它存储引擎。

实现了四个标准的隔离级别，默认级别是可重复读（REPEATABLE READ）。在可重复读隔离级别下，通过多版本并
发控制（MVCC）+ 间隙锁（Next-Key Locking）防止幻影读。

主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。

内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能
够加速插入操作的插入缓冲区等。

支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混
合场景中，停止写入可能也意味着停止读取。

（2.3）MyISAM

设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它。

提供了大量的特性，包括压缩表、空间数据索引等。

不支持事务。

不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入（CONCURRENT INSERT）。

可以手工或者自动执行检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导致一些数据丢失，而且修复操作
是非常慢的。

如果指定了 DELAY_KEY_WRITE 选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内
存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写
入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。

（2.4）InnoDB 和 MyISAM 的比较区别

MySQL中最常见的存储引擎有InnoDB和MyISAM，它们的主要区别如下：

• MyISAM不支持事务；InnoDB是事务类型的存储引擎。
• MyISAM只支持表级锁；InnoDB支持行级锁和表级锁，默认为行级锁。
• MyISAM引擎不支持外键；InnoDB支持外键。
• 对于count(*)查询来说MyISAM更有优势，因为其保存了行数。
• InnoDB是为处理巨大数据量时的最大性能设计的存储引擎。
• MyISAM支持全文索引（FULLTEXT）；InnoDB不支持。

最主要的区别就是MyISAM表不支持事务、不支持行级锁、不支持外键。 InnoDB表支持事务、支持行级锁、支持外键。

在MySQL5.5.5版本之后，InnoDB已经成为了其默认的存储引擎，也是大部分公司的不二选择，毕竟谁家公司会不要求数据库支持事务呢？谁家公司又可以忍受表级锁导致的读写冲突呢？

除了InnoDB以及MyISAM存储引擎外，常见的考察存储引擎还有Memory，使用Memory作为存储引擎的表也可以叫做内存表，将数据存储在了内存中，所以适合做临时表来使用，在索引结构上支持B+树索引和Hash索引。

（3）MySQL的日志模块 binlog 和 redo log

在MySQL的使用中，更新操作也是很频繁的，如果每一次更新操作都根据条件找到对应的记录，然后将记录更新，再写回磁盘，那么IO成本以及查找记录的成本都很高。所以，出现了日志模块，即我们的update更新操作是先写日志，在合适的时间才会去写磁盘，日志更新完毕就将执行结果返回给了客户端。

MySQL中的日志模块主要有redo log（重做日志）和binlog（归档日志）。

（3.1）redo log 重做日志

redo log是InnoDB引擎特有的日志模块，redo log是物理日志，记录了某个数据页上做了哪些修改。InnoDB的redo log是固定大小的，比如可以配置为一组4个文件，每个文件的大小是1GB，那么redo log总共就可以记录 4GB的操作。从头开始写，写到末尾就又回到开头循环写。

InnoDB的redo log保证了数据库发生异常重启之后，之前提交的记录不会丢失，这个能力称为crash-safe。

（3.2）binlog 归档日志

binlog是Server层自带的日志模块，binlog是逻辑日志，记录本次修改的原始逻辑，说白了就是SQL语句。binlog是追加写的形式，可以写多个文件，不会覆盖之前的日志。通过mysqlbinlog可以解析查看binlog日志。binlog日志文件的格式：statement，row，mixed。

• statement格式的binlog记录的是完整的SQL语句，优点是日志文件小，性能较好，缺点也很明显，那就是准确性差，遇到SQL语句中有now()等函数会导致不准确
• row格式的binlog中记录的是数据行的实际数据的变更，优点就是数据记录准确，缺点就是日志文件较大。
• mixed格式的binlog是前面两者的混合模式

业界目前推荐使用的是 row 模式，因为很多情况下对准确性的要求是排在第一位的。

在更新数据库的时候，通过redo log和binlog的两阶段提交，可以确保数据库异常崩溃之后数据的正确恢复。
在对数据库误操作之后，可以通过备份库+binlog可以将数据库状态恢复到“任意”时刻。

（3.3）一条SQL执行得很慢的原因？（flush脏页）

当内存数据页和磁盘数据页内容不一致的时候，这个内存页就是“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，这个时候的内存页就是“干净页”。

前面我们介绍了更新数据库的时候是先写日志，当合适的机会（空闲）出现的时候才会更新磁盘。但是当redo log 写满了，要 flush 脏页，也就是把内存里的数据写入磁盘，会导致MySQL执行速度突然变慢一瞬间。

点击阅读：
腾讯面试：一条SQL语句执行得很慢的原因有哪些？—不看后悔系列

一条 SQL 执行的很慢，我们要分两种情况讨论：
1、大多数情况下很正常，偶尔很慢，则有如下原因
(1)、数据库在刷新脏页，例如 redo log 写满了需要同步到磁盘。
(2)、执行的时候，遇到锁，如表锁、行锁。

2、这条 SQL 语句一直执行的很慢，则有如下原因
(1)、没有用上索引：例如该字段没有索引；由于对字段进行运算、函数操作导致无法用索引。
(2)、数据库选错了索引。

（4）MySQL中的锁机制？（重点掌握）

MySQL数据库的锁分为表级锁和行级锁。从数据库的角度看，行级锁又可以分为独占锁和共享锁。

（4.1）独占锁（排他锁），也称X锁（Exclusive Lock）

独占锁锁定的资源只允许进行锁定操作的程序使用，其它任何对它的操作均不会被接受。执行数据更新命令，即INSERT、UPDATE 或DELETE 命令时，MySQL会自动使用独占锁。但当对象上有其它锁存在时，无法对其加独占锁。独占锁一直到事务结束才能被释放。

在select命令中使用独占锁的SQL语句为：select … for update;

（4.2）共享锁，也叫S锁（Shared Lock）

共享锁顾名思义，那就是其锁定的资源可以被其它用户读取，但其它用户不能修改。如果在select查询语句中要手动加入共享锁，那么对应的SQL语句为：select ... lock in share mode

注意：
一个事务在一行数据上加入了独占锁，那么其余事务不可以在该数据行上加入任何锁。也就是说加过排他锁的数据行在其他事务中是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select ...from...查询数据，因为普通查询没有任何锁机制。

（4.3）MySQL中的死锁

多线程为了争夺资源可能会造成死锁，也就是一种环路等待的现象。MySQL中的死锁主要是多个事务使用行级锁对某行数据加锁造成的。

MyISAM不支持行级锁，所以MySQL中的死锁主要是在说InnoDB存储引擎的死锁。

① 业务逻辑上的死锁解决方案

• 指定锁的获取顺序
• 大事务拆分成各个小事务
• 在同一个事务中，一次锁定尽量多的资源，减少死锁概率
• 给表建立合适的索引以及降低事务的隔离级别等

② 数据库的设置来解决死锁

• 通过参数 innodb_lock_wait_timeout 根据实际业务场景来设置超时时间，InnoDB引擎默认值是50s。
• 发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect设置为 on，表示开启这个逻辑（默认是开启状态）。

（4.4）行级锁什么时候会锁住整个表？

InnoDB行级锁是通过锁索引记录实现的，如果更新的列没建索引是会锁住整个表的。

（4.5）悲观锁与乐观锁

• 悲观锁：利用数据库的锁机制实现，在整个数据处理过程中都加入了锁，以保持排他性。
• 乐观锁：乐观锁可以利用CAS实现，在操作数据的时候进行一个比较，按照当前事务中的数据和数据库表中的该数据是否一致来决定是否要执行本次操作。

举例说下乐观锁的CAS实现，有如下的数据库表和数据：

create table cas_test(
	phone varchar(32) primary key, 
	name varchar(32)
)engine=InnoDB;
 
// 插入数据
insert into cas_test values("18810101035","zhangsan");

当我们将数据更新后，会比较该行数据与数据库表中的该行数据是否一致，以此来决定是否要更新数据。

（4.6）乐观锁的ABA问题？如何解决？

ABA问题是指在当前事务读取该行数据时是A，经过别的事务修改成B，但是在当前事务要更新数据的时候，该行数据又被别的事务修改为A，事实上数据行是发生过改变的，存在并发问题。

ABA问题可以通过基于数据版本（Version）记录机制来解决。也就是为数据增加一个版本标识。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。根据当前事务的数据版本号和数据库中数据的版本号对比来决定是否更新数据。

与给当前数据增加一个数据版本类似，我们也可以增加基于时间戳机制来解决ABA问题，通过时间戳来记录当前数据行变化。

① 基于数据版本Version机制

表结构设计如下所示：

② 基于时间戳机制

表结构设计如下所示：

（5）分布式事务，两阶段，三阶段提交协议

待更新

（1）数据库系统原理

（1.1）事务（重点掌握）

事务指的是满足 ACID 特性的一组操作，可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚。

① ACID 特性

事务是单个逻辑工作单元执行的一系列操作，是一个不可分割的工作单位。满足如下的四大特性：

• 原子性（Atomicity）：事务作为一个整体被执行 ，要么全部执行，要么全部不执行；
  事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚。
  回滚可以用回滚日志来实现，回滚日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可。

• 一致性（Consistency）：保证数据库状态从一个一致状态转变为另一个一致状态；
  数据库在事务执行前后都保持一致性状态。在一致性状态下，所有事务对一个数据的读取结果都是相同的。

• 隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行；
  一个事务所做的修改在最终提交以前，对其它事务是不可见的。

• 持久性（Durability）：一个事务一旦提交，对数据库的修改应该永久保存
  一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。
  使用重做日志来保证持久性。

事务的 ACID 特性概念简单，但不是很好理解，主要是因为这几个特性不是一种平级关系：

• 只有满足一致性，事务的执行结果才是正确的。
• 在无并发的情况下，事务串行执行，隔离性一定能够满足。此时只要能满足原子性，就一定能满足一致性。
• 在并发的情况下，多个事务并行执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性。
• 事务满足持久化是为了能应对数据库崩溃的情况。

② 数据库事务操作的并发一致性问题

• 丢失更新：
  两个不同事务同时获得相同数据，然后在各自事务中同时修改了该数据，那么先提交的事务更新会被后提交事务的更新给覆盖掉，这种情况先提交的事务所做的更新就被覆盖，导致数据更新丢失。

• 脏读：
  事务A读取了事务B未提交的数据，由于事务B回滚，导致了事务A的数据不一致，结果事务A出现了脏读；

• 不可重复读：
  一个事务在自己没有更新数据库数据的情况，同一个查询操作执行两次或多次得到的结果数值不同，因为别的事务更新了该数据，并且提交了事务。

• 幻读：
  事务A读的时候读出了N条记录，事务B在事务A执行的过程中增加 了1条，事务A再读的时候就变成了N+1条，这种情况就叫做幻读。

注意：幻读是指一种结构上的改变，比如说条数发生了改变；不可重复读是指读出的数值发生了改变。

③ 数据库事务的隔离级别

为了避免数据库事务操作中的问题，MySQL定义了4个事务隔离级别，不同的隔离级别对事务的处理不同。

• 读未提交(Read Uncommitted)：
  允许脏读取。如果一个事务已经开始写数据，则另外一个数据则不允许同时进行写操作，但允许其他事务读此行数据。

• 读已提交(Read Committed)：（Oracle默认隔离级别）
  允许不可重复读取，但不允许脏读取。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。

• 可重复读(Repeatable Read)：（MySQL默认隔离级别）
  禁止不可重复读取和脏读取，但是有时可能出现幻读。读取数据的事务将会禁止写事务(但允许读事务)，写事务则禁止任何其他事务。

• 序列化(Serializable)：
  提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。

事务的隔离级别越高，对数据的完整性和一致性保证越佳，但是对并发操作的影响也越大。MySQL事务默认隔离级别是可重复读。

（1.2）MVCC多版本并发控制以及undo log（回滚日志）

多版本并发控制（Multi-Version Concurrency Control, MVCC）是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。而未提交读隔离级别总是读取最新的数据行，无需使用
MVCC。可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。

版本号

• 系统版本号：是一个递增的数字，每开始一个新的事务，系统版本号就会自动递增。
• 事务版本号：事务开始时的系统版本号。

隐藏的列
MVCC 在每行记录后面都保存着两个隐藏的列，用来存储两个版本号：

• 创建版本号：指示创建一个数据行的快照时的系统版本号；
• 删除版本号：如果该快照的删除版本号大于当前事务版本号表示该快照有效，否则表示该快照已经被删除了。

Undo 日志（回滚日志）
MVCC 使用到的快照存储在 Undo 日志中，该日志通过回滚指针把一个数据行（Record）的所有快照连接起来。

MVCC可重复读隔离级别的实现过程

以下实现过程针对（可重复读）隔离级别。

当开始一个事务时，该事务的版本号肯定大于当前所有数据行快照的创建版本号，理解这一点很关键。数据行快照的创建版本号是创建数据行快照时的系统版本号，系统版本号随着创建事务而递增，因此新创建一个事务时，这个事务的系统版本号比之前的系统版本号都大，也就是比所有数据行快照的创建版本号都大。

1. SELECT

多个事务必须读取到同一个数据行的快照，并且这个快照是距离现在最近的一个有效快照。但是也有例外，如果有一
个事务正在修改该数据行，那么它可以读取事务本身所做的修改，而不用和其它事务的读取结果一致。

把没有对一个数据行做修改的事务称为 T，T 所要读取的数据行快照的创建版本号必须小于 T 的版本号，因为如果大
于或者等于 T 的版本号，那么表示该数据行快照是其它事务的最新修改，因此不能去读取它。除此之外，T 所要读取
的数据行快照的删除版本号必须大于 T 的版本号，因为如果小于等于 T 的版本号，那么表示该数据行快照是已经被删
除的，不应该去读取它。

2. INSERT

将当前系统版本号作为数据行快照的创建版本号。

3. DELETE

将当前系统版本号作为数据行快照的删除版本号。

4. UPDATE

将当前系统版本号作为更新前的数据行快照的删除版本号，并将当前系统版本号作为更新后的数据行快照的创建版本号。

可以理解为先执行 DELETE 后执行 INSERT。

快照读与当前读

1. 快照读
   使用 MVCC 读取的是快照中的数据，这样可以减少加锁所带来的开销。

select * from table ...;

2. 当前读
   读取的是最新的数据，需要加锁。以下第一个语句需要加 S 锁，其它都需要加 X 锁。

select * from table where ? lock in share mode;
select * from table where ? for update;
insert;
update;
delete;

（1.3）Next-Key Locks

Next-Key Locks 是 MySQL 的 InnoDB 存储引擎的一种锁实现。
MVCC 不能解决幻影读问题，Next-Key Locks 就是为了解决这个问题而存在的。在可重复读（REPEATABLE READ）
隔离级别下，使用 MVCC + Next-Key Locks 可以解决幻读问题。

Record Locks
锁定一个记录上的索引，而不是记录本身。
如果表没有设置索引，InnoDB 会自动在主键上创建隐藏的聚簇索引，因此 Record Locks 依然可以使用。

Gap Locks
锁定索引之间的间隙，但是不包含索引本身。例如当一个事务执行以下语句，其它事务就不能在 t.c 中插入 15。

SELECT c FROM t WHERE c BETWEEN 10 and 20 FOR UPDATE;

Next-Key Locks
它是 Record Locks 和 Gap Locks 的结合，不仅锁定一个记录上的索引，也锁定索引之间的间隙。例如一个索引包含
以下值：10, 11, 13, and 20，那么就需要锁定以下区间：

(-∞, 10]
(10, 11]
(11, 13]
(13, 20]
(20, +∞)

（1.4）关系数据库设计理论

① 函数依赖

记 A->B 表示 A 函数决定 B，也可以说 B 函数依赖于 A。

如果 {A1，A2，… ，An} 是关系的一个或多个属性的集合，该集合函数决定了关系的其它所有属性并且是最小的，那么该集合就称为键码。

对于 A->B，如果能找到 A 的真子集 A’，使得 A’-> B，那么 A->B 就是部分函数依赖，否则就是完全函数依赖。

对于 A->B，B->C，则 A->C 是一个传递函数依赖。

* 几个概念：
	1. 函数依赖：A-->B，如果通过A属性(属性组)的值，可以确定唯一B属性的值。则称B依赖于A。
		例如：学号-->姓名。  （学号，课程名称） --> 分数
	2. 完全函数依赖：A-->B，如果A是一个属性组，则B属性值得确定需要依赖于A属性组中所有的属性值。
		例如：（学号，课程名称） --> 分数
	3. 部分函数依赖：A-->B，如果A是一个属性组，则B属性值得确定只需要依赖于A属性组中某一些值即可。
		例如：（学号，课程名称） -- > 姓名
	4. 传递函数依赖：A-->B, B -- >C . 如果通过A属性(属性组)的值，可以确定唯一B属性的值，
					再通过B属性（属性组）的值可以确定唯一C属性的值，则称 C 传递函数依赖于A。
		例如：学号-->系名，系名-->系主任
	5. 码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码。
		例如：该表中码为：（学号，课程名称）
		* 主属性：码属性组中的所有属性
		* 非主属性：除过码属性组的属性

② 异常

以下的学生课程关系的函数依赖为 {Sno, Cname} -> {Sname, Sdept, Mname, Grade}，键码为 {Sno, Cname}。也
就是说，确定学生和课程之后，就能确定其它信息。

不符合范式的关系，会产生很多异常，主要有以下四种异常：

• 冗余数据：例如 学生-2 出现了两次。
• 修改异常：修改了一个记录中的信息，但是另一个记录中相同的信息却没有被修改。
• 删除异常：删除一个信息，那么也会丢失其它信息。例如删除了 课程-1 需要删除第一行和第三行，那么 学生-1 的信息就会丢失。
• 插入异常：例如想要插入一个学生的信息，如果这个学生还没选课，那么就无法插入。

③ 数据库设计的范式

概念：

• 设计数据库时，需要遵循的一些规范。要遵循后边的范式要求，必须先遵循前边的所有范式要求；
• 设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小；
• 目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

1. 第一范式（1NF）：每一列都是不可分割的原子数据项

2. 第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于码（在1NF基础上消除非主属性对主码的部分函数依赖）

3. 第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

范式理论是为了解决以上提到四种异常。
高级别范式的依赖于低级别的范式，1NF 是最低级别的范式。

1. 第一范式 (1NF)
属性不可分。

2. 第二范式 (2NF)
每个非主属性完全函数依赖于键码。
可以通过分解来满足。

以上学生课程关系中，{Sno, Cname} 为键码，有如下函数依赖：

• Sno -> Sname, Sdept
• Sdept -> Mname
• Sno, Cname-> Grade

Grade 完全函数依赖于键码，它没有任何冗余数据，每个学生的每门课都有特定的成绩。
Sname, Sdept 和 Mname 都部分依赖于键码，当一个学生选修了多门课时，这些数据就会出现多次，造成大量冗余
数据。

有以下函数依赖：

• Sno -> Sname, Sdept
• Sdept -> Mname

有以下函数依赖：

• Sno, Cname -> Grade

3. 第三范式 (3NF)
非主属性不传递函数依赖于键码。
上面的 关系-1 中存在以下传递函数依赖：

• Sno -> Sdept -> Mname

可以进行以下分解：

（1.5）ER图

Entity-Relationship，有三个组成部分：实体、属性、联系。
用来进行关系型数据库系统的概念设计。

在ER图中有如下四个成分：

• 矩形框：表示实体，在框中记入实体名。
• 菱形框：表示联系，在框中记入联系名。
• 椭圆形框：表示实体或联系的属性，将属性名记入框中。对于主属性名，则在其名称下划一下划线。
• 连线：实体与属性之间；实体与联系之间；联系与属性之间用直线相连，并在直线上标注联系的类型。
  对于一对一联系，要在两个实体连线方向各写1；
  对于一对多联系，要在一的一方写1，多的一方写N；
  对于多对多关系，则要在两个实体连线方向各写N,M。

实体的三种联系
包含一对一，一对多，多对多三种。

• 如果 A 到 B 是一对多关系，那么画个带箭头的线段指向 B；
• 如果是一对一，画两个带箭头的线段；
• 如果是多对多，画两个不带箭头的线段。

下图的 Course 和 Student 是一对多的关系：

表示出现多次的关系：

一个实体在联系出现几次，就要用几条线连接。
下图表示一个课程的先修关系，先修关系出现两个 Course 实体，第一个是先修课程，后一个是后修课程，因此需要
用两条线来表示这种关系。

联系的多向性
虽然老师可以开设多门课，并且可以教授多名学生，但是对于特定的学生和课程，只有一个老师教授，这就构成了一
个三元联系。

表示子类
用一个三角形和两条线来连接类和子类，与子类有关的属性和联系都连到子类上，而与父类和子类都有关的连到父类
上。

（2）SQL（Structured Query Language）相关考察点

（2.1）MySQL建表的约束条件，建表sql

• 主键约束（Primay Key Coustraint） 唯一性，非空性； 设置主键自增：auto_increment
• 唯一约束（Unique Counstraint） 唯一性，可以空，但只能有一个
• 非空约束（Not Null Counstraint） 非空，值不能为null
• 检查约束 (Check Counstraint) 对该列数据的范围、格式的限制
• 默认约束 (Default Counstraint) 该数据的默认值
• 外键约束 (Foreign Key Counstraint) 需要建立两表间的关系并引用主表的列

表名 student
id 主键列 自增
name 名字   唯一索引
age 年龄

用innodb引擎
编码是utf8mb4

CREATE TABLE `student` (
	`id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
	`name` VARCHAR(32) DEFAULT NULL COMMENT '姓名',
	`age` INT(11) DEFAULT NULL COMMENT '年龄',
	PRIMARY KEY (`id`),
	UNIQUE KEY `name` (`name`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4

注意：如果需要存储微信聊天中的表情，utf8是存不下的，只能存utf8mb4

int(1)中，1代表什么意思？

这个int(M)我们可以简单的理解为：
这个长度是为了告诉MYSQL数据库，我们这个字段的存储的数据的宽度为M位数, 当然如果你不是M位数（只要在该类型的存储范围之内）MYSQL也能正常存储。

属性=UNSIGNED ZEROFILL（无符号,用0来填充位数）

（2.2）MySQL中where、group by、having关键字

这三个MySQL关键字作用可以总结如下：

• where子句用来筛选from子句中指定的操作所产生的的行
• group by 子句用来分组where子句的输出
• having子句用来从分组的结果中筛选行

语法：

		select
			字段列表
		from
			表名列表
		where
			条件列表
		group by
			分组字段
		having
			分组之后的条件
		order by
			排序
		limit
			分页限定

having和where的区别：

• where 搜索条件在分组之前进行限定，如果不满足条件，则不参与分组；having搜索条件在分组之后进行限定，如果不满足结果，则不会被查询出来
• where 后不可以跟聚合函数，having可以进行聚合函数sum、avg、max等的判断
• having子句限制的是组，而不是行

当同时含有where子句、group by 子句 、having子句及聚集函数时，执行顺序如下：

• 执行where子句查找符合条件的数据
• 使用group by 子句对数据进行分组
• 对group by 子句形成的组运行聚集函数计算每一组的值
• 最后用having 子句去掉不符合条件的组

eg：

--  按照性别分组。分别查询男、女同学的平均分,人数 要求：分数低于70分的人,不参与分组,分组之后。人数要大于2个人
SELECT sex , AVG(math), COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;
SELECT sex , AVG(math), COUNT(id) 人数 FROM student WHERE math > 70 GROUP BY sex HAVING 人数 > 2;

（2.3）order by是怎么工作的？

文章来源：
【MySQL 读书笔记】“order by”是怎么工作的？

MySQL 会给每个线程分配一块内存用于排序 称为 sort_buffer。

SQL语句：
select city,name,age from t where city='杭州' order by name limit 1000  ;

① 全字段排序

1. 初始化 sort_buffer, 确认放入 name, city, age 这三个字段。
2. 从索引 city 找到第一个满足 city='杭州’条件的主键 id。
3. 回表取到 name, city, age 三个字段值，存入 sort_buffer 中。
4. 从索引 city 取下一个主键 id 记录。
5. 重复 3-4 步骤，直到 city 不满足条件。
6. 对 sort_buffer 中的数据按照字段 name 做快速排序。
7. 排序结果取前 1000 行返回给客户端。

按照 name 排序这个动作即可能在内存中完成，也可以能使用外部文件排序。这取决于 sort_buffer_size 。sort_buffer_size 的默认值是 1048576 byte 也就是 1M，如果要排序的数据量小于 1m 排序就在内存中完成，如果排序数据量大，内存放不下，则使用磁盘临时文件辅助排序。

② Rowid 排序

如果单行很大，需要的字段全部放进 sort_buffer 效果就不会很好。

MySQL 中专门用于控制排序的行数据长度有个参数 max_length_for_sort_data 默认是1024，如果超过了这个值就会使用 rowid 排序。那么执行上面语句的流程就变成了

1. 初始化 sort_buffe 确定放入两个字段即 name 和 id 。
2. 从索引 city 找到第一个满足 city = '杭州’条件的主键 id。
3. 回表取 name 和 id 两个字段 存入 sort_buffer 中。
4. 取下个满足条件的记录 重复 2 3 步骤。
5. 对 sort_buffer 中的 name 进行排序。
6. 遍历结果取前 1000 行。然后按照 id 再回一次表取的结果字段返回给客户端。

其实并不是所有 oder by 语句都需要进行上面的二次排序操作。从上面分析的执行过程，我们可以注意到。MySQL 之所以需要生成临时表，是因为要在临时表上做排序，是因为之前我们取得的是数据是无序的。

如果我们对刚才的索引修改一下，使得他是一个联合索引，那么第二个字段我们拿到的值其实就是有序的了。

联合索引满足这么一个条件，当我们的第一个索引字段是相等的情况下，第二个字段是有序的。

这能保证如果我们建立 (city,name) 索引的话，当我们在搜索 city='杭州’的情况的是时候找到的目标第二个字段 name 其实是有序的。所以查询过程可以简化成。

1. 从索引 (city, name) 找到第一个满足 city = '杭州’条件的主键 id 。
2. 回表取到 name city age 三个值返回。
3. 取下一个 id 。
4. 重复2 3 两个步骤直到 1000 条记录，或者是不满足 city = '杭州’条件结束。

也因为查询过程都可以使用到索引的有序性，所以不再需要排序也不需要时使用 sort buffer 了。

更近一步的优化就是之前说过的索引覆盖，将需要查询的字段也覆盖进索引中，再省掉回表的步骤，可以让整个查询的速度更快。

（2.4）多表查询

* 查询语法：
	select
		列名列表
	from
		表名列表
	where....
	
* 准备sql
	# 创建部门表
	CREATE TABLE dept(
		id INT PRIMARY KEY AUTO_INCREMENT,
		NAME VARCHAR(20)
	);
	INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');
	
	# 创建员工表
	CREATE TABLE emp (
		id INT PRIMARY KEY AUTO_INCREMENT,
		NAME VARCHAR(10),
		gender CHAR(1), -- 性别
		salary DOUBLE, -- 工资
		join_date DATE, -- 入职日期
		dept_id INT,
		FOREIGN KEY (dept_id) REFERENCES dept(id) -- 外键，关联部门表(部门表的主键)
	);
	INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1);
	INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2);
	INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2);
	INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);
	INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);
	
* 笛卡尔积：
	* 有两个集合A,B .取这两个集合的所有组成情况。
	* 要完成多表查询，需要消除无用的数据

① 内连接查询

1. 隐式内连接：使用where条件消除无用数据
	* 例子：
	-- 查询所有员工信息和对应的部门信息
	SELECT * FROM emp,dept WHERE emp.`dept_id` = dept.`id`;
	
	-- 查询员工表的名称，性别。部门表的名称
	SELECT 
		emp.name, emp.gender, dept.name 
	FROM 
		emp, dept 
	WHERE 
		emp.`dept_id` = dept.`id`;
	
	SELECT 
		t1.name, -- 员工表的姓名
		t1.gender,-- 员工表的性别
		t2.name -- 部门表的名称
	FROM
		emp t1,
		dept t2
	WHERE 
		t1.`dept_id` = t2.`id`;

2. 显式内连接：
	* 语法： select 字段列表 from 表名1 [inner] join 表名2 on 条件
	* 例如：
		* SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`;	
		* SELECT * FROM emp JOIN dept ON emp.`dept_id` = dept.`id`;	

3. 内连接查询：
	1. 从哪些表中查询数据
	2. 条件是什么
	3. 查询哪些字段

② 外连接查询

1. 左外连接：
	* 语法：select 字段列表 from 表1 left [outer] join 表2 on 条件；
	* 查询的是左表所有数据以及其交集部分。
	* 例子：
		-- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称
		SELECT 	
			t1.*, t2.`name` 
		FROM 
			emp t1 （from表是左表，左外连接，查询左表所有数据）
		LEFT JOIN 
			dept t2 （join表是右表，左外连接，查询右表与左表交集部分）
		ON 
			t1.`dept_id` = t2.`id`;
		
2. 右外连接：
	* 语法：select 字段列表 from 表1 right [outer] join 表2 on 条件；
	* 查询的是右表所有数据以及其交集部分。
	* 例子：
		SELECT 	
			* 
		FROM 
			dept t2 （from表是左表，右外连接，查询左表与右表交集部分）
		RIGHT JOIN 
			emp t1 （join表是右表，右外连接，查询右表所有数据）
		ON
			t1.`dept_id` = t2.`id`;

③ 子查询

* 概念：查询中嵌套查询，称嵌套查询为子查询。
	-- 查询工资最高的员工信息
	-- 1 查询最高的工资是多少 9000
	SELECT MAX(salary) FROM emp;
	
	-- 2 查询员工信息，并且工资等于9000的
	SELECT * FROM emp WHERE emp.`salary` = 9000;
	
	-- 一条sql就完成这个操作。子查询
	SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);

* 子查询不同情况
	1. 子查询的结果是单行单列的：
		* 子查询可以作为条件，使用运算符去判断。 运算符： > >= < <= =
		-- 查询员工工资小于平均工资的人
		SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);
		
	2. 子查询的结果是多行单列的：
		* 子查询可以作为条件，使用运算符 in 来判断
		-- 查询'财务部'和'市场部'所有的员工信息
		SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部';
		SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;
		-- 子查询
		SELECT * FROM emp WHERE dept_id IN 
				(SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');

	3. 子查询的结果是多行多列的：
		* 子查询可以作为一张虚拟表参与查询
		-- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
		-- 子查询
		SELECT 
			* 
		FROM 
			dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2
		WHERE 
			t1.id = t2.dept_id;
		
		-- 普通内连接
		SELECT 
			* 
		FROM 
			emp t1,dept t2 
		WHERE 
			t1.`dept_id` = t2.`id` AND t1.`join_date` >  '2011-11-11'

（2.5）limit分页查询使用方式

限制返回的行数。可以有两个参数，第一个参数为起始行，从 0 开始；第二个参数为返回的总行数。

返回前 5 行：
SELECT * FROM mytable LIMIT 5;
SELECT * FROM mytable LIMIT 0, 5;

返回第 3 ~ 5 行：
SELECT * FROM mytable LIMIT 2, 3;

（2.6）SQL语句的优化有哪些方法？

参考文章：
SQL优化有哪些方法？

1、在表中建立索引，优先考虑where、group by使用到的字段（较频繁地作为查询条件且唯一性不太差），不会再where中用到的字段不建立索引，因为建立索引也需要系统的开销。

2、减少使用 * ，用列名代替
select * from user;
要写成 select userID, userName, userSalary from user;
因为在使用 * 的时候，数据库还得查询数据字典，进而解析得到列名，而直接写出列名效率会更高些。

3、避免在开头使用模糊查询（%），该查询数据库引擎会放弃索引进行全表扫描。

4、避免进行NULL值判断，可以给字段添加默认值0，对0值进行判断；
也不要给数据库留NULL，使用NOT NULL填充。（否则会进行全表扫描，影响效率）

5、避免在where条件中等号的左边进行表达式或函数操作，可以将表达式或函数移到等号右边。（否则会全表扫描）

6、当使用where子句连接的时候，要把能过滤掉最大数量记录的条件写在最右边。（因为where是从右往左解析的）

7、需要删除所有记录的时候，用truncate而不用detele
因为delete删除表的时候，会扫描整个表再一条一条删除；
而truncate会一次性删除整个表的所有内容，不进行扫描，效率高。

8、当where和having都用的时候，先用where，再用having；where先过滤（数据量变少），再分组，效率高。

9、避免使用in和not in，会导致全表扫描
优化方式：如果是连续数值，用between代替；如果是子查询，用exists代替。

10、如果表名或列名过长，就使用别名，因为长的表名和列名也会消耗扫描时间。

（2.7）SQL异常及故障排查

一般情况下，我们遇到一个SQL异常的时候，比如说执行时间超时等，可以通过explain查看当前SQL语句的执行情况。explain +SQL语句可以查看当前的SQL语句使用的索引以及其扫描了多少行数据。也可以使用下边的语句来查看数据表的一些信息：

• show create table TableXX；查看当前表TableXX的建表语句
• show index from TableXX；查看当前表TableXX上的索引

查看了数据表的信息，一般情况下我们可以通过建立索引来提高查询速度，或者修改SQL语句，利用索引下推或者最左前缀原则等来加快查询速度。

参考文章：
EXPLAIN 命令详解

• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
• EXPLAIN不考虑各种Cache
• EXPLAIN不能显示MySQL在执行查询时所作的优化工作
• 部分统计信息是估算的，并非精确值
• EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。

（2.8）查询性能优化

① 使用 Explain 进行分析

Explain 用来分析 SELECT 查询语句，开发人员可以通过分析 Explain 结果来优化查询语句。
比较重要的字段有：

• select_type : 查询类型，有简单查询、联合查询
• key : 使用的索引
• rows : 扫描的行数

eg：

(root@yayun-mysql-server) [test]>
explain select 
	d1.age, t2.id 
from 
	(select age,name from t1 where id in (1,2)) d1, t2 
where 
	d1.age=t2.age 
group by 
	d1.age, t2.id 
order by 
	t2.id;
	
+----+-------------+------------+-------+---------------+---------+---------+--------+------+---------------------------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref    | rows | Extra                           |
+----+-------------+------------+-------+---------------+---------+---------+--------+------+---------------------------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL   |    2 | Using temporary; Using filesort |
|  1 | PRIMARY     | t2         | ref   | age           | age     | 5       | d1.age |    1 | Using where; Using index        |
|  2 | DERIVED     | t1         | range | PRIMARY       | PRIMARY | 4       | NULL   |    2 | Using where                     |
+----+-------------+------------+-------+---------------+---------+---------+--------+------+---------------------------------+
rows in set (0.00 sec)

② 优化数据访问

1. 减少请求的数据量

只返回必要的列：最好不要使用 SELECT * 语句。
只返回必要的行：使用 LIMIT 语句来限制返回的数据。
缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别在要查询的数据经常被重复查询时，缓存带来的查询性能提升将会是非常明显的。

2. 减少服务器端扫描的行数

最有效的方式是使用索引来覆盖查询。

③ 重构查询方式

1. 切分大查询
   一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。

DELETE FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH);

rows_affected = 0
do {
	rows_affected = do_query(
	"DELETE FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000")
} while rows_affected > 0

2. 分解大连接查询
   将一个大连接查询分解成对每一个表进行一次单表查询，然后在应用程序中进行关联，这样做的好处有：

• 让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用。
• 分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询。
• 减少锁竞争；
• 在应用层进行连接，可以更容易对数据库进行拆分，从而更容易做到高性能和可伸缩。
• 查询本身效率也可能会有所提升。例如下面的例子中，使用 IN() 代替连接查询，可以让 MySQL 按照 ID 顺序进行查询，这可能比随机的连接要更高效。