一、子查询(Sub Query)

1. 简介

一条select查询语句的结果作为另一条select查询语句的一部分。

特点

　　子查询一般作为查询条件使用;

　　子查询必须放在()小括号中使用;

　　一般先执行子查询再进行外查询。

2. 单行子查询

查询出的结果为 一列一行(一个数据);

如：最高、最低、平均等

可以使用判断符号：>、<、=、!=等

-- 语法格式:
select 字段 from 表 where 字段 判断符号 (子查询);

例：　

　　将查询出的最高价格作为条件查询，获取商品信息

select * from products where price = (select max(price) from products);

3. 多行子查询

查询出的结果为一列多行(多个数据)

可以使用判断符号 如：in、all、any

• in：等于任意一个
  • 格式： in(值1,值2...)
• all：所有
• any：任意一个

语法格式

select 字段 from 表 where 字段 判断符号(in | any | all) (子查询);

二、存储引擎

MySQL的核心就是插件式存储引擎。

MySQL 可以通过 show engines 查看所有支持的存储引擎。

在MySQL中默认支持的存储引擎有8个。 federated 不支持。主要用来提供对远程MySQL服务器上面的数据的访问接口。

 在MySQL中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。上面Support列的值表示某种引擎是否能启用：YES表示已经启用、NO表示没有启用、DEFAULT表示该引擎为当前默认的存储引擎。

1. 引擎介绍

1.1 InnoDB

默认的存储引擎，也是所有存储引擎中唯一支持事务、XA协议的存储引擎。

1.2 MyISAM

基于ISAM（Indexed Sequential Access Method目前已经废弃）的存储引擎，特点是查询效率较高。但不支持事务和容错性。

1.3 MEMORY

纯内存型存储引擎。所有数据都在内存中，硬盘只存储.frm文件。所以当MySQL宕机或非法关闭时只生效表结构。当然了，由于所有数据都在内存上，所以相对来说性能较高。

1.4 MRG_MYISAM

以前也叫MERGE，简单理解就是对MyISAM表做了特殊的封装，对外提供单一访问入口，减少程序的复杂性。

1.5 ARCHIVE

主要用于通过较小的存储空间来存放过期的很少访问的历史数据。ARCHIVE表不支持索引，通过一个.frm的结构定义文件，一个.ARZ的数据压缩文件还有一个.ARM的meta信息文件。由于其所存放的数据的特殊性，ARCHIVE表不支持删除，修改操作，仅支持插入和查询操作。

1.6 BLACKHOLE

俗称“黑洞”存储引擎。是一个非常有意思的存储引擎。所有的数据都是有去无回。

1.7 CSV

实际上操作的就是一个标准的CSV文件，他不支持索引。起主要用途就是大家有些时候可能会需要通过数据库中的数据导出成一份报表文件，而CSV文件是很多软件都支持的一种较为标准的格式，所以我们可以通过先在数据库中建立一张CSV表，然后将生成的报表信息插入到该表，即可得到一份CSV报表文件了。

1.8 PERFORMANCE_SCHEMA

从MySQL 5.6新增的存储引擎。主要用于收集一些系统参数。

三、数据库事务控制（TCL）

1. 事务

数据库常用存储引擎InnoDB(MySQL5.5后默认）、MyISAM(MySQL5.5前默认)

只有InnoDB支持事务处理机制，MyISAM不支持事务。

什么是事务？

事务是一个整体，由一条或者多条SQL语句组成，

这些SQL语句要么都执行成功，要么就失败，

只要有一条SQL出现异常，整个操作就会回滚。

回滚: 就是事务运行的过程中发生了某种故障，或者SQL出现了异常，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部取消，回滚到事务开始时的状态。

2. MySQL事务操作

两种方式

① MySQL默认自动提交事务；

② 手动开启事务后需要手动提交事务。

2.1 手动提交事务

1. START TRANSACTION | BEGIN 这个语句显式地标记一个事务的起始点。

2. COMMIT 表示提交事务，即提交事务的所有操作，具体地说，就是将事务中所有对数据库的更新都写到磁盘上的物理数据库中，事务正常结束。

3. ROLLBACK 回滚， 表示撤销事务，即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，回滚到事务开始时的状态。

2.2 手动提交事务的流程

只有成功或失败两种情况

3. 事务的四大特性（ACID）

原子性（Atomicity）

　　一个事务要么全部提交成功，要么全部失败回滚，不能只执行其中的一部分操作。

一致性（Consistency）

　　事务必须是数据库从一个一致性状态到另一个一致性状态。也就是说一个事务执行前后都必须处于一致性的状态。

隔离性（Isolation）

　　在并发环境中，并发的事务是相互隔离的，并发执行的各个事务间互不干扰。

持久性（Durability）

　　一旦事务提交，那么它对数据库中的对应数据的状态的变更就会永久保存到数据库中。即使发生系统崩溃或机器宕机等故障，只要数据库能重新启动，那么一定能将其恢复到事务成功结束的状态。

4. MySQL事务的并发访问

4.1 并发访问产生的问题

事务在操作时的理想状态：所有的事务之间保持隔离，互不影响。因为并发操作，多个用户同时访问同一个数据，可能引发并发访问的问题。

 4.2 四种隔离级别

通过设置隔离级别，可以防止上面的三种并发问题

 隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。

读已提交、可重复读通过MVCC机制实现

MVCC（特殊的行锁）

多版本并发控制，通过快照读的方式解决了可重复读、幻读（幻读偶尔会出现）

多次读取过程中如果变更全部数据，会读取最新的数据造成幻读。

4.3 隔离级别相关命令

① 查看隔离级别

select @@transaction_isolation;

② 设置隔离级别

-- 设置隔离级别语法格式
set session transaction isolation level 隔离级别名称;
-- 如: 设置为读未提交
set session transaction isolation level read uncommitted;
-- read uncommitted 读未提交
-- read committed 读已提交
-- repeatable read 可重复读
-- serializable 串行化

四、索引

索引是一种数据机构，通过索引可以快速找到要查询的内容

MySQL官方文档中说明MySQL在500W~800W数据以上时查询性能可能下降，所以在大量数据时建立索引提升查询性能是非常有必要的。

MySQL常用的两种引擎InnoDB和MyISAM的索引和数据存储不同：

InnoDB引擎：索引和数据都是存储在表名.idb文件中

MyISAM引擎: 索引和数据存储在不同的文件中，表名.MYD 存储数据，表名.MYI 存储索引

1. 索引的结构

• BTREE(默认)
• HASH

1.1 HASH结构

Hash底层实现是由Hash表来实现的，是根据键值 <key,value> 存储数据的结构。

非常适合根据key查找value值，也就是单个key查询，或者说等值查询。

 缺点：

　　1.模糊查询时只能遍历整个表。（哈希表是把索引字段映射成对应的哈希码然后再存放在对应的位置）

　　2.只适合精确查找，不适合范围查找。

1.2 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效率高。

2. 优点

为什么要创建索引？这是因为，创建索引可以大大提高系统的查询性能。

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

• 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

• 可以加速表和表之间的连接，特别是在实现数据的完整性方面特别有意义。

• 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

• 通过使用索引，可以在查询的过程中 ，使用查询优化器，提高系统的性能。

3. 缺点

1. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

2. 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需要的空间就会更大。

3. 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

4. 适合创建索引的字段

1. 在经常需要搜索的列上，可以加快搜索的速度；

2. 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

3. 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；

4. 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；

5. 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

6. 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

建立索引，一般按照select的where条件来建立，

比如： select的条件是where f1 and f2，那么如果我们在字段f1或字段f2上建立索引是没有用的，只有在字段f1和f2上同时建立索引才有用。

5. 不适合创建索引的字段

1. 对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

2. 对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

3. 对于那些定义为text，image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。

4. 当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

6.索引分类

单列索引、组合索引、全文索引

6.1 单列索引

单列索引就是给某个列加的索引

又分为：主键索引、普通索引、唯一索引

6.2.1 主键索引

特点：

• 它是一种特殊的唯一索引，不允许有空值

• 添加了主键约束就会自动创建主键索引，在创建或修改表时添加主键约束即可

• 每个表只能有一个主键约束，所以一张表只能有一个主键索引

创建主键索引的 3种 方式:

-- 1. 创建表时指定主键约束
create table 表名(
   字段1 类型 primary key,
   ...
);

-- 2. 为创建好表，但是并没有指定主键约束的表添加主键约束
alter table 表名 add primary key(字段名);

-- 3. 查看某张表中的所有索引
show index from 表名;

6.2.2 普通索引

特点:

这是最基本的索引类型，基于普通字段建立的索引，没有任何限制。

普通字段: 非主键约束，非外键约束，非唯一约束

创建索引的方式:

-- 1. 创建表时创建普通索引
create table 表名(
   字段1 类型,
   ...,
   index [索引名称](字段名) -- 不指定索引名称，自动生成
);

-- 2. 为创建好的表添加普通索引
alter table 表名 add index [索引名称](字段名); -- 不指定索引名称，自动生成

-- 3. 为创建好的表添加普通索引
create index <索引名称> on 表 (字段名); -- 必须指定索引名称

6.2.3 唯一索引

特点:

与"普通索引"类似，不同的就是：索引字段的值必须唯一，但允许有空值。在创建或修改表时追加唯一约束，就会自动创建对应的唯一索引

创建索引的方式:

-- 创建表时指定唯一约束，会自动创建唯一索引
create table 表名(
   字段1 类型 unique,
   ...
);

-- 为创建好的表添加唯一索引
alter table 表名 add unique index [索引名称](字段名);

-- 为创建好的表添加唯一索引
create unique index <索引名称> on 表名(字段名);

6.2 组合索引

给表中大于等于两个列添加索引。

但是需要满足最左前缀，创建组合索引相当于创建了多个索引，一般把最常用的放在最左边。

-- 语法格式：
create index 索引名 on 表名(列1，列2...)

create index index3 on demo(col1,col2,col3)

create index index3 on demo(col1,col2)

相当于创建了 col1、col1-col2、col1-col2-col3、col1-col3四种索引。

可以通过执行计划查询，如果执行计划类型为ref表示使用索引，如果类型为index表示没有匹配到合适索引。

6.3 全文索引

MySQL从3.23.23版开始支持全文索引和全文检索，FULLTEXT索引在MySQL5.6之前仅可用于 MyISAM 表，

在MySQL5.7后InnoDB也支持，MySQL8中InnoDB也支持；

他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE INDEX被添加。

对于较大的数据集，将你的数据输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把数据输入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。

全文索引对中文支持不好，如果搜索中文就只能按照最左对照进行搜索。如果是英文就可以匹配中间。

6.3.1 创建

-- 创建表
create table tb_fulltext(
    id int(11) primary key auto_increment,
    name varchar(100),
    address varchar(200),
    FULLTEXT index_name (name)
);
-- 方式2
ALTER TABLE table_name ADD FULLTEXT index_name(column);

6.3.2 使用

创建好的全文索引需要配合match(列，列) against(‘内容’)使用。

　　match中列必须和创建全文索引的列一样。

例如创建全文索引是(id,name)，match(name)无法使用全文索引，必须单独建立name列的全文索引。

against中内容有三种模式:

• 自然语言模式：IN NATURAL LANGUAGE MODE

• 布尔模式：IN BOOLEAN MODE

• 查询扩展模式：WITH QUERY EXPANSION

自然语言模式：拆分出来的关键字必须严格匹配。例如beijing只能通过beijing搜索，不能通过bei搜索。

布尔模式：支持特殊符号。即使对没有全文索引的列也可以进行搜索，但是非常慢。查询时必须从最左开始查询，例如：北京昌平，按照昌平无法查询。

本栏目推荐文章
• Jmeter 如何连接mysql数据库？
• 用于PostgreSQL索引维护的有用查询
• 如何通过Navicat把MySQL远程数据库导入到本地数据库
• 索引的一些总结
• 【LeetCode 2701. 连续递增交易】MySQL用户变量编程得到严格递增连续子序列的开始位置，结束位置，长度等统计信息
• 【Leetcode 2474. 购买量严格增加的客户】MySQL用户变量编程解决严格递增连续子序列问题
• mysql发生连接异常Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
• 【LeetCode 2494. 合并在同一个大厅重叠的活动】MySQL用户变量编程解决区间合并问题
• 如何处理Mysql死锁
• 【LeetCode1747. 应该被禁止的 Leetflex 账户】MySQL用户变量编程；尝试维护一个multiset

视图 MySQL_DCL 索引 MySQL DCL视图mysql_dcl索引mysql mysql_dcl 视图 索引mysql 06 视图 索引 语言 知识mysql dcl 触发器 视图 函数 索引 触发器 视图 索引 事务 视图 函数 索引 流程 视图mysql mysql索引