MySQL基础知识点集合

④对于被foreign key约束的表，不能使用truncate table而应该使用不带where子句的delete来执行，原因就是truncate table被记录在日志中，无法触发外键约束，而delete语句是数据库操作语⾔(dml)，这个操作会放到rollback segement中，事务提交之后才生效，如果这个表有相应的trigger，那么在事务执行的时候将会触发；

⑤truncate/drop是数据库定义语⾔(ddl)，操作后立即生效，原始数据不会记录到rollback segment中，既不能回滚，也不能触发trigger；

⑥如果表中有自增列，truncate方式清空数据后，自增列也跟着重新初始化，而delete方式则不会这样；

大于(>)

①数值直接按照大小比较；

②字符则按照ASCII码对应的值进行比较，比较时按照字符对应的位置一个字符一个字符的比较；

Null注意事项

空值查询需要专门使用IS NULL或者IS NOT NULL，这是因为查询运算符、like、between and、in、not in对NULL值查询不起效，因此，建议创建表的时候，尽量设置表的字段不能为空，给字段设置⼀个默认值；

where和having的区别

where是在分组（聚合）前对记录进行筛选，而having是在分组结束后的结果里筛选，最后返回整个sql的查询结果；

可以把having理解为两级查询，即含having的查询操作先获得不含having子句时的sql查询结果表，然后在这个结果表上使用having条件筛选出符合的记录，最后返回这些记录，因此， having后是可以跟聚合函数的，并且这个聚集函数不必与select后面的聚集函数相同；

group by与聚合函数

分组中select后面只能有2种类型的列：

①出现在group by后的列；

②或者使用聚合函数的列；

我在网上搜了下关于group by与聚合函数的关系，有个有趣的案例：

主键	名称	价格
1	苹果	5
2	苹果	3
3	香蕉	2
4	西瓜	3

当我在执行group by 名称时，数据库会把主键1和2的记录合并成1条记录，效果如下：

主键

名称

价格

苹果

这里就需要把这2条记录合并成1条记录，此时此刻，聚合函数起到作用了，我需要通过聚合函数来明确要合并的列，例如上面的例子sum(价格)，通过这个【价格】来明确把多个行合并成一个行；

内连接

select 字段 from 表1 inner join 表2 on 连接条件;

select 字段 from 表1 join 表2 on 连接条件;

select 字段 from 表1, 表2 [where 关联条件];

外连接

外连接查询结果=内连接的结果+主表中有而内连接结果中没有的记录；

外连接分为2种：

①左外链接：使用left join关键字，left join左边是主表；

②右外连接：使用right join关键字，right join右边是主表；

左外连接

select 列 from 主表 left join 从表 on 连接条件;

右外连接

select 列 from 从表 right join 主表 on 连接条件;

Null注意事项

①任何值和NULL使用运算符（>、 <、 >=、 <=、 !=、 <>）或者（in、 not in、 any/some、 all）比较时，返回值都为NULL， NULL作为布尔值的时候，不为1也不为0；

②当IN和NULL比较时，是无法查询出为NULL的记录；

③当NOT IN后面有NULL值时，不论什么情况下，整个SQL查询结果都为空；

④判断是否为空只能用IS NULL、IS NOT NULL；

⑤count(字段)是无法统计字段为NULL的值，只有count(*)才可以统计值为null的行；

⑥当字段为主键的时候，字段会自动设置为not null；

总结：NULL导致的坑实在太多，鉴于这种情况，强烈建议创建字段的时候字段不允许为NULL，通过default给个默认值；

事务

4个特征

原子性Atomicity：一些列操作，像原子一样，不可切割，要么都成功，要么都失败；

一致性Consistency：指的是业务状态，从一个状态成功切换到另外一个状态，所有业务状态都符合预期效果；

隔离性Isoladon：并发执行的各个事务之间彼此之间互不干扰；

持久性Durability：当事务提交之后，数据会持久化到硬盘，修改是永久性的；

Mysql的事务

mysql中事务默认是隐式事务，数据库自动提交事务或回滚事务，其是否开启隐式事务是由变量autocommit控制的，因此，MySQL中事务分为隐式事务和显式事务；

查看变量autocommit是否开启了自动提交：

show variables like 'autocommit';

autocommit为ON则表示开启了自动提交模式；

隐式事务与显式事务区别

隐式事务模式下，事务自动提交/回滚，当执行完insert/update/delete之后，数据库自动提交修改结果，或者数据库自动回滚修改结果；

显式事务模式下，当你执行完insert/update/delete之后，需要手动触发事务提交指令，或者，事务回滚指令；

2种方式手动控制事务

方式1：

set autocommit=0;//设置不自动提交事务

commit|rollback;//执行事务指令

方式2：

start transaction;//开启事务

commit|rollback;//执行事务操作

savepoint

如果一个事务中有N个操作，但我只想回滚一部分操作，这里可以借助savepoint来实现这种部分回滚的效果；

只读事务

该事务模式表示只有select操作，没有其他操作，数据库可能会对该模式有一些性能上的优化，语法如下：

start transaction read only;

（个人吐槽：这玩意有什么用）

事务的隔离级别

当多个事务同时进行的时候，如何确保当前事务中数据的正确性，这个需要依靠事务的隔离级别来保证；

不同的隔离级别中所产生的效果是不⼀样的，事务隔离级别主要是解决了多个事务之间数据可见性及数据正确性的问题，隔离级别分为4种，从弱到强依次为：

①读未提交：READ-UNCOMMITTED

②读已提交：READ-COMMITTED

③可重复读：REPEATABLE-READ

④串行：SERIALIZABLE

隔离级别越来越强，会导致数据库的并发性也越来越低；

查看当前事务隔离级别：show variables like 'transaction_isolation';

脏读

⼀个事务在执行的过程中读取到了其他事务还没有提交的数据；

读已提交

⼀个事务操作过程中可以读取到其他事务已经提交的数据；

可重复读

⼀个事务操作中对于⼀个读取操作不管多少次，读取到的结果都是⼀样的；

索引

磁盘中数据的存取

以机械硬盘来说，谈下面几个概念：

扇区

磁盘存储的最小单位，扇区一般大小为512Byte，即512字节；

磁盘块

文件系统与磁盘交互的的最小单位（计算机系统读写磁盘的最小单位），一个磁盘块由连续几个（2^N）扇区组成，块大小一般为4KB；

磁盘读取数据

磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为：寻道时间、旋转延迟、传输时间；

寻道时间指的是磁臂移动到指定磁道所需要的时间；

旋转延迟指的是磁盘转速；

传输时间指的是从磁盘读出或将数据写入磁盘的时间；

mysql中的页

mysql中和磁盘交互的最小单位称为页，页是mysql内部定义的⼀种数据结构，默认为16KB，相当于4个磁盘块，也就是说mysql每次从磁盘中读取一次数据是16KB，要么不读取，要读取就是16KB，此值可以修改的；

我们迫切的需求是什么？

迫切需要这样的数据结构、算法：

①需要一种数据存储结构：当从磁盘中检索数据的时候能，够减少磁盘的io次数，最好能够降低到一个稳定的常量值；

②需要一种检索算法：当从磁盘中读取磁盘块的数据之后，这些块中可能包含多条记录，这些记录被加载到内存中，那么需要一种算法能够快速从内存多条记录中快速检索出目标数据；

常见的算法与数据结构

算法	缺点
循环遍历查找	n条数据，时间复杂度为O(n)，最快需要1次，最坏的情况需要n次，查询效率不稳定；
折半查找	目标数组必须是有序的；
有序数组	占用内存太大，新增数据的时候，为了保证数组有序，插入数据会涉及到数组内部数据的移动
单链表/双向链表	无法像数组通过下标随机访问数据，导致查找数据需从第1个节点开始遍历，需要从头到尾遍历，最差时间是O(N)
二叉查找树	①查询数据的效率不稳定，若树左右平衡的时，最差情况为O(logN)，如果插入数据是有序的，退化为了链表，查询时间变成了O(N)； ②数据量大的情况下，会导致树的高度变高，如果每个节点对应磁盘的一个块来存储一条数据，需io次数大幅增加；
平衡二叉树	内存情况下可以采用平衡二叉树，但磁盘采用这种数据结构，每个节点对应一个磁盘块，当数据量大的时候，也会和二叉树一样，会导致树的高度变高，增加了io次数
B-树	B-不利于范围查找
B+树

B-树

B+树

B+树与B-树区别

①B+树中一个节点如果有k个关键字，最多可以包含k个子节点（k个关键字对应k个指针）；而b-树对应k+1个子节点（多了⼀个指向子节点的指针）；

②B+树除叶子节点之外其他节点值存储关键字和指向子节点的指针，而B-树还存储了数据，这样同样大小情况下，B+树可以存储更多的关键字；

③B+树叶子节点中存储了所有关键字及data，并且多个节点用链表连接，子节点中数据从左向右是有序的，这样快速可以支持范围查找；

InnoDB中的索引分类

有2种索引：

①主键索引（聚集索引）：每个表只有1个主键索引， B+树结构，叶子节点同时保存了主键的值、数据记录，其他节点只存储主键的值；

②辅助索引（⾮聚集索引）：每个表可以有多个， B+树结构，叶子节点保存了索引字段的值、主键的值，其他节点只存储索引指端的值；

InnoDB数据检索过程

先在辅助索引中检索到数据，获取其主键的值，再到主键索引中根据主键的值找到对应的数据记录；

从辅助索引再到主键索引这个过程，在mysql中叫做回表；

回表

当需要查询的数据在索引树中不存在的时候，需要再次到聚集索引中去获取，这个过程叫做回表；

索引覆盖

查询中采用的索引树中包含了查询所需要的所有字段的值，不需要再去聚集索引检索数据，这种叫索引覆盖；

SQL1：select * from test1 where name='javacode3500000';

SQL2：select id,name from test1 where name='javacode3500000';

分析：在test1表中，id为主键，而name则是辅助索引，name对应idx1索引，id为主键，所以idx1索引树叶子节点中包含了name、 id的值，这个查询只用走idx1这一个索引就可以了，如果select后面使用*，还需要一次回表获取sex、 email的值；

因此，在写SQL的时候，尽量避免使用*，*可能会多一次回表操作，需要看一下是否可以使用索引覆盖来实现，效率更高一些；

索引下推

简称ICP，Index Condition Pushdown(ICP)是MySQL5.6中新特性，是一种在存储引擎层使用索引过滤数据的一种优化方式，ICP可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数；

页结构

整体构造

mysql中页是innodb中存储数据的基本单位，也是mysql中管理数据的最小单位，和磁盘交互的时候都是以页来进行的，默认是16KB，同时由于mysql中采用的是B+树存储数据，页相当于B+树中的一个节点，下面看一下页的整体结构：

头部构造

Page的头部保存了两个指针，分别指向前一个Page和后一个Page，根据这两个指针我们很容易想象出Page链接起来就是⼀个双向链表的结构，想象图如下：

主体构造

Page主体内容主要关注点在于：行数据、索引的存储；

行数据 + 索引都位于Page的User Records部分，User Records占据Page的大部分空间，User Records是由一条一条的Record组成，是一个单链表的数据结构形式；

在一个具体的Page内部，单链表的头尾由固定内容的两条记录来表示，字符串形式的"Infimum "代表开头，"Supremum"代表结尾，这两个用来代表开头、结尾的Record存储在System Records中；

综上所述，Infimum、User Records、Supremum一起组成了一个单向链表结构；

实际上，刚开始数据是按照插入的先后顺序排列的，但是随着新数据的插入、旧数据的删除，数据物理顺序会变得混乱，但他们依然通过链表的方式来保持着逻辑上的先后顺序，想象图如下：

整体效果

把User Record的组织形式和若个page组合起来，就看到了稍微完整的形式：

目录槽

innodb为了快速查找记录，在页中定义了⼀个称之为page directory的目录槽（slots）,每个槽位占用2个Byte来保存指向记录的地址；

Page directory中的多个slot组成了一个有序数组（可使用二分法来快速定位记录），行记录被Page Directory逻辑的分成了多个块，块与块之间是有序的，能够加速记录的查找，如下图：

每个行记录都有⼀个nowned的区域， nowned标识所属的slot这个块有多少条数据，伪记录Infimum的nowned值总是1，记录Supremum的nowned的取值范围为[1,8]，其他用户记录nowned的取值范围[4,8]，并且只有每个块中最大的那条记录的nowned才会有值，其他的用户记录的nowned为0；

B+树中数据检索过程

唯⼀记录检索

所有的数据都是唯⼀的，查询105的记录，过程如下：

①将P1页加载到内存；

②在内存中采用二分法查找，可以确定105位于[100,150)中间，所以我们需要去加载关联P4页；

③将P4加载到内存中，采用二分法找到105的记录后退出；

查询某个值的所有记录

查询105的所有记录，过程如下：

①将P1页加载到内存；

②在内存中采用二分法查找，可以确定105位于[100,150)中间， 100关联P4页；

③将P4加载到内存中，采用二分法找到最有一个小于105的记录，即100，然后通过链表从100开始向后访问，找到所有的105记录，直到遇到第一个大于100的值为止；

范围查找

查询[55,150]所有记录，由于页和页之间是双向链表升序结构，页内部的数据是单项升序链表结构，所以只用找到范围的起始值所在的位置，然后通过依靠链表访问两个位置之间所有的数据即可，过程如下：

①将P1页加载到内存；

②内存中采用二分法找到55位于50关联的P3页中， 150位于P5页中；

③将P3加载到内存中，采用二分法找到第⼀个55的记录，然后通过链表结构继续向后访问P3中的60、 67，当P3访问完毕之后，通过P3的next page指针访问下⼀页P4中所有记录，继续遍历P4中的所有记录，直到访问到P5中的150为止；

模糊匹配

查询以f开头的所有记录，过程如下：

①将P1数据加载到内存中；

②在P1页的记录中采用二分法找到最后⼀个小于等于f的值，这个值是f，以及第一个大于f的，这个值是z， f指向叶节点P3， z指向叶节点P6，此时可以断定以f开头的记录可能存在于[P3,P6)这个范围的页内，即P3、 P4、 P5这三个页中；

③加载P3这个页，在内部以二分法找到第一条f开头的记录，然后以链表方式继续向后访问P4、 P5中的记录，即可以找到所有已f开头的数据；

查询包含f的记录，包含的查询在sql中的写法是%f%，通过索引我们还可以快速定位所在的页么？可以看⼀下上面的数据， f在每个页中都存在，我们通过P1页中的记录是无法判断包含f的记录在那些页的，只能通过io的方式加载所有叶子节点，并且遍历所有记录进行过滤，才可以找到包含f的记录。

所以如果使用了%值%这种方式，索引对查询是无效的；

最左匹配原则

当B+树的数据项是复合的数据结构，比如(name,age,sex)的时候， b+树是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，B+树会优先比较name来确定下⼀步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，B+树就不知道下⼀步该查哪个节点，因为建立搜索树的时候name就是第⼀个比较因子，必须要先根据name来搜索才能知道下⼀步去哪里查询。比如当(张三,F)这样的数据来检索时， B+树可以用name来指定搜索方向，但下⼀个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了，这个是非常重要的性质，即索引的最左匹配特性；

正确使用索引

数字使字符串类索引失效

表结构：

id是INT类型

name是字符串类型

sex是TINYINT类型

email是字符串类型

SQL1：select * from test1 where name = '1';

SQL2：select * from test1 where name = 1;

在test1表中，name是字符串类型，且name上建立了索引，这里涉及到一个字符串与整数的比较问题，MySQL会把字符串类型转成整数类型，从首字母开始，遇到非数字类型后终止，然后与整数进行比较，这里name = 1时就是字符串和数字比较的时候，会将字符串强制转换为数字后，然后进行比较，因此，SQL2就变成了全表扫描，只能取出每条数据，将name转换为数字和1进行比较；

核心知识点：字符串遇到整数时，字符串主动转型为数字，至于转换后的结果，不可知，所以，没办法，就只能走全表扫描了；

函数使索引无效

SQL1：select a.name+1 from test1 a where a.name = 'javacode1';

SQL2：select * from test1 a where concat(a.name,'1') = 'javacode11';

SQL1走索引，而SQL2不走索引，这是因为SQL2使用了函数，name所在的索引树是无法快速定位需要查找的数据所在的页的，只能将所有页的记录加载到内存中，然后对每条数据使用函数进行计算之后再进行条件判断，此时索引无效了，从而变成了全表数据扫描；

核心知识点：字符串遇到函数时，相当于针对每一条索引记录进行函数运算，这样之后，MySQL就不知道每一条索引经过函数运算后会是什么结果，所以，没办法，就只能走全表扫描了；

运算符使索引无效

SQL1：select * from test1 a where id = 2 - 1;

SQL2：select * from test1 a where id+1 = 2;

id上有主键索引，SQL1走索引，SQL2不走索引，这是因为在SQL2中使用了where id+1 = 2，id所在的索引树是无法快速定位需要查找的数据所在的页的，只能将所有页的记录加载到内存中，然后对每条数据的id进行计算之后再判断是否等于1，此时索引无效了，变成了全表数据扫描，总结起来就是：索引字段使用了函数将使索引无效；

核心知识点：字符串遇到函数时，相当于针对每一条索引记录进行运算，这样之后，MySQL就不知道每一条索引经过运算后会是什么结果，所以，没办法，就只能走全表扫描了；