一、SQL执行流程
●各个组件
•连接管理与安全验证：MySQL有连接池(Connection Pool)管理客户端的连接。客户端连接后会验证用户名、密码、主机信息等
•缓存(Cache&Buffer)：缓存中存储了SQL命令的HASH，直接比对SQL命令的HASH和缓存中key是否对应，如果对应，直接返回结果，不再执行其他操作。由于缓存的是SQL的HASH，所以根据Hash特性SQL中空格等内容必须完全一样。缓存里面包含表缓存、记录缓存、权限缓存等。查询语句执行完成后会把查询结果缓存到缓存中。
•解析器(Parser)主要作用是解析SQL命令。将SQL命令分解成数据结构(解析树)，后续的操作都是基于这个结构的。如果在分解过程中遇到错误，出现SQL解析错误。解析时主要检查SQL中关键字，检查关键字是否正确、SQL中关键字顺序是否正确、引号是否对应是否正确等。
•预处理器：根据解析器的解析树，进一步检查表是否存在、列是否存在、名字和别名是否有歧义等。
•优化器(Optimizer)：根据官网说明在执行SQL查询时会根据开销自动选择最优查询方案。采用“选择-投影连接”的策略。先选择where中的行数。同时先选择要选择的列，而不是全部列，最后把内容合并到一起。
•执行器：包含执行SQL命令。获取返回结果。生成执行计划等。
•存储引擎：访问物理文件的媒介
● 执行流程
•客户端向服务器端发送SQL命令和连接参数
•服务器端连接模块连接并验证
•缓存模块解析SQL为Hash并与缓存中Hash表对应。如果有结果直接返回结果，如果没有对应继续向下执行。如果是MySQL8是没有查询缓存的。
•解析器解析SQL为解析树，检查关键字相关问题，如果出现错误，报SQL解析错误。如果正确，继续执行
•预处理器对解析树继续处理检查表、列别名等，处理成功后生成新的解析树。
•优化器根据开销自动选择最优执行计划，生成执行计划
•执行器执行执行计划，访问存储引擎接口
•存储引擎访问物理文件并返回结果
•如果开启查询缓存，缓存管理器把结果放入到查询缓存中。
•返回结果给客户端
二、表的约束
添加，修改，删除
•主要用在添加，修改：
添加 修改数据时，数据是否符合约束，符合可以进行操作，不符合不可以进行操作。
主键约束：非空+唯一用在当前表中
非空约束：非空 用在当前表中
唯一约束：唯一 用在当前表中
检查约束：检查 用在当前表中
默认值约束：默认值 用在当前表中
外检约束：约束关系字段用在当前表中，添加的值必须在另外一张表中存在
没有约束：可以添加，修改，删除数据
有了约束，可以让操作的数据为准确，完整的数据
●外键约束
•外键
外键指的是在主表中与从表的主键对应的那个字段。
●外键约束
使用外键约束可以让两张表之间产生一个对应关系，从而保证主从表数据的完整性。
●RESTRICT(默认) 如果子表中有匹配的记录,则不允许对父表对应候选键进行Jpdate/delete操作
No action 同RESTRICT,都是立即检查外键约束
CASCADE 在主表上update/delete记录时，同步update/delete掉从表匹配的记录
SET NULL 在主表上update/delete记录时，将子表上匹配记录的列设为null 注意：子表的外键列不能为not null
●创建外键约束
•语法格式：
①新建表时添加外键约束
constraint 外键约束名称 foreign key(外键字段名)references 主表名(主键字段)；
②为已创建好的表添加外键约束
alter table 从表名add constraint 外键约束名称 foreign key(外键字段名)references 主表名(主键字段名)；
需求：为employee表的dep_id字段添加外键约束。
①为已经创建好的employee表添加外键约束
②新创建employee表时，为dep_id添加外键约束
● 外键约束的注意：
•从表的外键类型必须和主表的主键类型保持一致
•添加从表数据时
从表中添加的外键值，必须在主表的主键中存在
•删除和变更数据主表数据时
先删除从表中的数据或将外键设置为null, 再删除主表中的数据
•通过navicat设置外键约的束变更和删除的级联操作
●外键约束-删除主表(department)中的数据
外检约束：外键字段中的值，必须在引用表的字段值中存在.
删除子表中数据时，外键约束没有用影响.
删除主表中数据时，主表中删除 修改 的数据为从表中没有对应的，可以直接删除从表中对应的数据，不能直接删
●解决 删除|修改主表中从对应的数据：
•先将子表中对应的数据删除，再删除主表中的数据
•先将子表中外键对应的字段值设置为null， 再删除主表中的数据
三、多表关系设计
一对多关系(最常见) 员工表和部门表 学生表和班级表
多对多关系 学生表和课程表 用户表和角色表
一对一关系(使用较少) 一对一的关系可以设计成一张表
●一对多关系(常见)
•一对多关系(1：n)
例如：班级和学生，部门和员工，客户和订单，类别和商品
•一对多建表原则
在从表(多方)创建一个字段,该字段作为外键指向主表的主键
●多对多关系
•多对多关系(m:n)
例如：老师和学生，学生和课程，用户和角色。
•多对多关系建表原则
多对多的关系不能直接处理，需要创建第三张表，也称为中间表，中间表至少两个字段，这两个字段分别作为外键指向各自一方的主键，实际就是将多对多拆分为两个一对多。
●一对一关系
一对一的关系可以设计为一张表。
一对一关系 可以在任意一方添加一个外键，指向另一方的主键
四、多表查询
●多表查询
•DQL：查询多张表，获取到需要的数据
●笛卡尔积
交叉连接(CROSSJOIN)是对两个或者多个表进行笛卡儿积操作，表示两个表中的每一行数据任意组合的结果。
笛卡尔积便于理解连接查询的原理。
•语法格式：
select 字段名 from 表1 cross join 表2；
●多表查询的分类
•内连接查询(等值连接)
通过指定的条件去匹配两张表中的数据，匹配上就显示比配不上就不显示
•SQL92隐式内连接
from子句后面直接写多个表名 使用where指定连接条件的 这种连接方式是隐式内连接.使用where条件过滤无用的数据。
•语法格式：
select 字段名 from 表1，表2 where 连接条件；
• SQL99显式内连接
使用inner join...on这种方式，就是显式内连接。
•语法格式：
select 字段名 from 表1[inner] join 右表on条件；
●注：
•内连接只会显示匹配的数据
•内连接不能实现不匹配数据的显示
●自连接
一张表当做两张表使用.
select *from 表1 a join 表1b on 连接条件；
●非等值连接
表连接的条件为非等值判断
●外连接和内连接
•内连接：inner join：只获取两张表中交集部分的数据。
•左外连接：left join：以左表为基准，查询左表的所有数据，以及与右表有交集的部分。••右外连接：right join:以右表为基准，查询右表的所有的数据，以及与左表有交集的部分。
●左外连接：
以左表为主，左表中的数据会全部展示，右表中没有坐标匹配的数据，填充为nu1l
●右外连接：
•特点：
①以右表为主右表中的数据全部显示
②左表匹配到数据就显示匹配到的数据
③左表没有匹配的数据显示为null
●全外连接：
•注意：MySQL中不支持FULLOUTERJOIN连接
可以使用union 实现全完连接。
•UNION:可以将两个查询结果集合并，返回的行都是唯一的，如同对整个结果集合使用了DISTINCT。
•UNION ALL:只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。
五、子查询(Sub Query)
一条select查询语句的结果作为另一条select语句的一部分
•特点：
子查询一般作为查询条件使用
使用子查询，必须将子查询放在小括号中使用
一条SQL语句含有多个select，一般是先执行子查询，再执行外查询
●单行子查询
单行子查询：查询出的结果为一列一行(一个数据)如：最高，最低，平均等，可以使用判断符号如：>|<|=|1=等
•语法格式：
select 字段 from 表 where 字段 判断符号(子查询)；
●多行子查询
多行子查询：查询出的结果为一列多行(多个数据)如：化妆品类别下的商品cid，，可以使用判断符号如： in all any
•In：等于任意一个
使用方式：
in(值1，值2..)
• all:所有
使用方式：
如：字段>| <all(值1，值2..)大于所有的值
•any:任意一个
使用方式
如：字段>|<any(值1，值2..)大于任意一个值
如：字段=any(值1，值2...)等于任意一个值效果等同于in
•语法格式：
select 字段 from 表where 字段 判断符号(in | any | all) (子查询)；
●单行子查询：查询出的结果为一列一行(一个数据)，可以使用><=！=
●多行子查询：查询出的结果为一列多行(多个数据)，需要使用in all any
六、存储引擎
数据库存储引擎：是数据库管理系统中的重要组成部分。数据库管理系统(DBMS)使用存储引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。
MySQL的核心就是插件式存储引擎。
MySQL可以通过 show engines查看所有支持的存储引擎。
在MySQL中默认支持的存储引擎有8个。 federated 默认是不启用的。主要用来提供对远程MySQL服务器上面的数据的访问接口。
在MySQL中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。
●各个引擎
•InnoDB默认的存储引擎，也是所有存储引擎中唯一支持事务、XA协议的存储引擎。
•MyISAM基于ISAM(Indexed Sequential Access Method目前已经废弃)的存储引擎，特点是查询效率较高。但不支持事多和容错性。
•MEMORY 纯内存型型存储引擎。所有数据都在内存中，硬盘只存储.frm文件。所以当MySQL宕机或非法关闭时只生效表结构。当然了，由于所有数据都在内存上，所以相对来说性能较高。
•MRG MYISAM以前也MERGE，简单理解就是对MyISAM表做了特殊的封装，对外提供单一访问入口，减少程序的复杂性。
•ARCHIVE存储引擎主要用于通过较小的存储空间来存放过期的很少访问的历史数据。ARCHIVE表不支持索引，通过一个.frm的结构定义文件，一个.ARZ的数据压缩文件还有一个.ARM的meta信息文件。由于其所存放的数据的特殊性，ARCHIVE表不支持删除，修改操作，仅支持插入和查询操作。
•BLACKHOLE俗称“黑洞”存储引擎。所有的数据都是有去无回。
•CSV存储引擎实际上操作的就是一个标准的CSV文件，他不支持索引。起主要用途就是大家有些时候可能会需要通过数据库中的数据导出成一份报表文件，CSV文件是很多软件都支持的一种较为标准的格式，
•PERFORMANCE_SCHEMA主要用于收集一些系统参数。
七、数据库事务控制(TCL)
事务是一个整体，由一条或者多条SQL语句组成，这些SQL语句要么都执行成功，要么就失败，只要有一条SQL出现异常，整个操作就会回滚。
事务中的sql要么全部成功，要么全部失败。
成功：事务的提交，将数据永久的存储，存储到本地磁盘中。
失败：事务的回滚，回到操作数据之前的状态.
回滚：就是事务运行的过程中发生了某种故障,或者SQL出现了异常,事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部取消，回滚到事务开始时的状态。
●MySQL事务操作
•MySQL中可以有两种方式进行事务的操作：
①自动提交事务(MySQL默认)
②手动提交事务
•手动提交事务
•语法格式：
开启事务 start transaction; 或者 BEGIN;
提交事务 commit;
回滚事务 rollback;
①STARTTRANSACTION这个语句显式地标记一个事务的起始点。
②COMMIT 表示提交事务，即提交事务的所有操作，具体地说，就是将事务中所 有对数据库的更新都写到磁盘上的物理数据库中，事务正常结束。
③ROLLBACK 表示撤销事务，即在事务运行的过程中发生了某种故障，事务不 能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，回滚到事务开始时的状态。
• 手动提交事务的流程
①执行成功的情况：开启事务>执行多条SQL语句>成功提交事务执行
②失败的情况：开启事务->执行多条SQL语句->事务的回滚
● 事务的四大特性 ACID
•原子性(Atomicity)
一个事务要么全部提交成功，要么全部失败回滚，不能只执行其中的一部分操作，这就是事务的原子性
•一致性(Consistency)
指事物必须是数据库从一个一致性状态到另一个一致性状态。也就是说一个事物执行之前和执行之后都必须处于一致性状态
•隔离性(Isolation)
事务的隔离性是指在并发环境中，并发的事务时相互隔离的，一个事务的执行不被其他事务干扰。即并发执行的各个事务之间不相互干扰
•持久性(Durability)
一旦事务提交，那么它对数据库中的对应数据的状态的变更就会永久保存到数据库中。即使发生系统崩溃或机器宕机等故障，只要数据库能够重新启动，那么一定能够将其恢复到事务成功结束的状态
●MySQL事务的隔离级别
•数据并发访问
一个数据库可能拥有多个访问客户端，这些客户端都可以并发方式访问数据库.数据库的相同数据可能被多个事务同时访问，如果不采取隔离措施，就会导致各种问题，破坏数据的完整性。
•并发访问产生的问题
事务在操作时的理想状态：所有的事务之间保持隔离，互不影响。因为并发操作，多个用户同时访问同一个数据，可能引发并发访问的问题。
•脏读 一个事务读取到了另一个事务中尚未提交的数据
•不可重复 一个事务中两次读取的数据内容不一致,要求的是在一个事务中多次读取时数据是一致的，这是进行update操作时引发的问题
•幻读 一个事务中,某一次的 select 操作得到的结果所表征的数据状态，无法支撑后续的业务操作，查询得到的数据状态不准确，导致幻读.
●四种隔离级别
通过设置隔离级别，可以防止上面的三种并发问题。MySQL数据库有四种隔离级别上面的级别最低，下面的级别最高。
•注：
隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。
●隔离级别相关命令
•查看隔离级别
select @transaction_isolation
八、索引
索引类似图书的目录，一种数据结构，通过索引可以快速的找到需要查询的内容。
索引和数据都是存储在.idb文件(InnoDB引擎)。
•索引的结构
索引在数据库底层有两种结构：BTREE和HASH。默认使用的是BTREE。
•HASH结构
Hash底层实现是由Hash表来实现的，是根据键值<key，value>存储数据的结构。非常适合根据key查找value值，也就是单个key查询，或者说等值查询。
•缺点：
①哈希表是把索引字段映射成对应的哈希码然后再存放在对应的位置，如果要进行模糊查找的话，哈希表这种结构是不支持的，只能遍历这个表
②适合于精确的查找，也不适合范围查询
●BTREE结构
BTree分为B-Tree和B+Tree，MySQL数据库索引采用的B+Tree，B+Tree是在B-Tree上做了优化改造。
•B-Tree结构
• 索引值和data(数据)分布在整棵树结构中
• 每个节点可以存放多个索引值以及对应的data(数据)
•树节点中的多个索引值从左到右升序排列缺点：
所有的节点都存放数据，数据会占用空间，导致存放的索引变少
•B+Tree结构
•非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值
•叶子节点包含了所有的索引值和data数据
•叶子节点用指针连接，提高区间的访问性能。
•相比B-树，B+树进行范围查找时，只需要查找定位两个节点的索引值，然后利用叶子节点的指针进行遍历即可。而B-树需要遍历范围内所有的节点和数据，显然B+Tree效率高。
●索引的优点
•创建索引可以大大提高系统的查询性能。
•通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
•可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
•可以加速表和表之间的连接，特别是在实现数据的完整性方面特别有意义。
•在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
●索引的缺点
•创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
•索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需要的空间就会更大。
•当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
●索引分类
索引分为单列索引和组合索引。
单列索引就是只是给某个列加索引；组合索引是给表中大于等于两个列添加索引。
•单列索引
单列索引又分为：主键索引、普通索引和唯一索引。
•主键索引
特点：
它是一种特殊的唯一索引，不允许有空值
在创建或修改表时添加主键约束即可，添加了主键约束就会自动创建主键索引·每个表只能有一个主键约束，所以一张表只能有一个主键索引
•语法格式：
create table 表名(
字段1 类型 primary key，
)；
alter table 表名 add primary key(字段名)；
show index from 表名；
•普通索引
特点：
这是最基本的索引类型，基于普通字段建立的索引，没有任何限制。
普通字段：非主键约束，非外键约束，非唯一约束
•语法格式：
create table 表名(
字段 类型，
···，
index[索引名](字段)
)；
alter table 表名 add index [索引名](字段)；create index<索引名> on 表(字段)；
• 唯一索引
特点：
与普通索引类似，不同的就是：索引字段的值必须唯一，但允许有空值。在创建或修改表时追加唯一约束，就会自动创建对应的唯一索引
•语法格式：
create table 表名(
字段1 类型 unique，
···
)；
alter table 表名 add unique index [索引名称](字段名)；
create unique index <索引名称> on 表名(字段名)；
•组合索引
给表中大于等于两个列添加索引。
但是需要满足最左前缀，创建组合索引相当于创建了多个索引，一般把最常用的放在最左边。
•语法格式：
create index 索引名 on 表名(列1，列2...)
create index index3 on demo(col1,co12,co13)
•全文索引
创建好的全文索引需要配合match(列，列) against(内容)使用。
match中列必须和创建全文索引的列一样。例如创建全文索引是(id,name)，match(name)无法使用全文索引，必须单独建立name列的全文索引。
•against中内容有三种模式：
•自然语言模式：IN NATURAL LANGUAGE MODE
•布尔模式：IN BOOLEAN MODE
•查询扩展模式：WITH QUERY EXPANSION
•自然语言模式：拆分出来的关键字必须严格匹配。
•+ 一定要有(不含有该关键词的数据条均被忽略)
- 不可以有(排除指定关键词，含有该关键词的均被忽略)
> 提高该条匹配数据的权重值
< 降低该条匹配数据的权重值
~ 将其相关性由正转负，表示拥有该字会降低相关性，只是排在较后面权重值降低
* 万用字，不像其他语法放在前面，这个要接在字符串后面。因为搜索时只能满足最左前缀搜索like‘内容%，不能实现类似like‘%内容%’这种
＂＂ 用双引号将一段句子包起来表示要完全相符，不可拆字
•查询扩展：查询时进行扩展查询，发现和条件有关系的内容都查询出来
•中文拆词器
由于中文是没有空格的，MySQL从5.7.6开始内置ngram中文分词插件。可以设置把整个中文按照指定大小进行拆词。
•聚集索引和非聚集索引
聚集和非聚集最主要的区别就是索引是否和数据在一起。
聚集索引(聚簇索引)之所以叫“繁集”是因为索引和数据一起存储。叶子节点中存储索引和数据。因为数据顺序和索引顺序是一样的，且顺序存储，所以查询速度比较快。当然了因为有顺序的所以新增需要重新排序会影响速度。简单记忆：主键索引就是聚集索引。
非聚集索引之所以叫“非聚集”是因为叶子节点中只存储主键索引值,索引列的值，索引值。所以在非聚集索引查询时需要通过主键再去查询想要的数据。在叶子节点上通过主键查询数据的过程就是所谓的回表。
九、count(*)，count(列)，count(1)的区别。
•在InnoDB引擎中count(*)和count(1)性能没有什么差别
•count(列)需要看列和count(*)优化后的列情况，如果count(列)使用了非索引列，而表中包含索引列则count(*)更快。如果count(列)和count(*)优化后的是同一个列则性能没有什么差别。如果表中没有索引则count(列)和count(*)性能也没有什么差别。
十、索引优化
•使用短索引(前缀索引)
对串列进行索引，如果可能应该指定一个前缀长度。短索引不仅可以提高查询速度而且可以节省磁盘空间和1/O操作。
•索引列排序
MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
•like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like“%aaa%”不会使用索引，而like“aaa%”(非前导模糊查询）可以使用索引。使用后，优化到range级别。
•不要在列上进行运算
•范围列可以使用索引，范围条件有：<、<=、>、>=、between等
•类型转换会导致索引无效，当列是文本类型时，把数值类型当作列的条件会弃用索引。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
