MySQL_DCL、TCL、索引、视图

合集 - MySQL(3)

1.MySQL_DCL、TCL、索引、视图2023-09-07

2.MySQL_DQL操作2023-09-053.MySQL数据库2023-09-04

收起

一、子查询(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搜索。

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

特殊符号	说明
+	一定要有(不含有该关键词的数据条均被忽略)
-	不可以有(排除指定关键词，含有该关键词的均被忽略)
>	提高该条匹配数据的权重值
<	降低该条匹配数据的权重值
~	将其相关性由正转负，表示拥有该字会降低相关性(但不像 - 将之排除)，只是排在较后面权重值降低
*	万用字，不像其他语法放在前面，这个要接在字符串后面。 因为搜索时只能满足最左前缀搜索like ‘内容%’，不能实现类似like ‘%内容%’这种
""	用双引号将一段句子包起来表示要完全相符，不可拆字

 查询扩展：查询时进行扩展查询，发现和条件有关系的内容都查询出来

6.3.4 中文拆词器

由于中文是没有空格的，MySQL 从5.7.6开始内置ngram中文分词插件。可以设置把整个中文按照指定大小进行拆词。设置步骤如下：

1.在my.ini中[mysqld]下添加参数，设置拆词长度（根据自己的需求进行完成即可）

2.给address创建全文索引。注意后面的with parser ngram

create fulltext index index3 on ft(address)  with parser ngram;

7. 聚集(聚簇)索引、非聚集索引

不是索引的分类，而是数据的存储方式

不同存储引擎的索引和数据存储形式不同

7.1 Innodb存储引擎（索引和数据在同一个文件中）

聚集索引 | 聚簇索引

　　并不是索引的分类，索引值和行数据存储在一起，数据会按照索引的顺序进行存储。 主键索引为聚集索引的一种，也可以自定义聚集索引（很少自定义）。
表中没有主键索引，自动找一个唯一非空的索引作为聚集索引，自动创建一个隐藏的字段作为聚集索引。
非聚集索引 | 非聚簇索引 | 二级索引 | 辅助索引

　　并不是索引的分类，索引值和主键值存储在一起，根据索引值找到主键，
根据主键找到行数据（回表查询）。

7.2 MyISAM存储引擎（索引和数据在不同的文件中）

由于MyISAM存储引擎的数据和索引并不是存放在一起的，所以不存在聚集索引。

非聚集索引

　　索引值和行数据的地址存储在一起。

五、count(*),count(列),count(1)的区别（面）

对于InnoDB引擎count(*)和count(1)没有性能的区别，都是自动寻找主键列或唯一索引且非空约束。统计的时候都是统计包含null的行，所以效果都是返回表中数据的行数。

官方解释：https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html

 

而count(列) 统计列中包含多少行数据，不包含NULL值。

统计说明：

• 在InnoDB引擎中count(*)和count(1)性能没有什么差别

• count(列)需要看列和count(*)优化后的列情况，如果count(列)使用了非索引列，而表中包含索引列则count(*)更快。如果count（列）和count(*)优化后的是同一个列则性能没有什么差别。如果表中没有索引则count（列）和count(*)性能也没有什么差别。

六、索引优化

1. 使用短索引（前缀索引）

对串列进行索引，如果可能应该指定一个前缀长度。

CREATE  INDEX  index_name  ON  table_name (column(length));

2. 索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

3. like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引，而like “aaa%”（非前导模糊查询）可以使用索引。使用后，优化到range级别。

4. 不要在列上进行计算

例如：

select * from users where YEAR(adddate)<2007，

将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：

select * from users where adddate<’2007-01-01′。

应该把计算放在业务代码上完成，而不是交给数据库。

5. 范围列可以使用索引

范围条件有：<、<=、>、>=、between等。

范围列可以用到索引（联合索引必须是最左前缀），但是范围列后面的列无法用到索引，索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。所以where中把最主要的查询条件放在第一个。

6. 类型转换会导致索引无效

当列是文本类型时，把数值类型当作列的条件会弃用索引。

explain select * from teacher where name = 20;

索引级别

const(主键查询) > ref > range > index(扫描全部索引) > all(全表扫描)

七、视图

1. 简介

1. 视图是一种虚拟表

2. 视图建立在已有表的基础上，视图建立依赖的这些表称为基表

3. 向视图提供数据内容的语句为 SELECT 语句，可以将视图理解为存储起来的 SELECT 语句

4. 视图向用户提供基表数据的另一种表现形式

2. 作用

简化复杂的查询

1. 视图本身就是一条查询SQL，我们可以将一次复杂的查询构建成一张视图，用户只要查询视图就可以获取想要得到的信息(不需要再编写复杂的SQL)，可以理解为查询视图就相当于将创建视图的SQL再次执行一次

2. 视图主要就是为了简化复杂查询

3. 视图的使用

语法格式：

-- 创建视图
create view 视图名称 as select语句; 
-- view: 表示视图
-- as: 表示视图要执行的操作
-- select: 向视图提供数据内容
-- 查询视图和查询表一样
select * from 视图名称;
-- 删除视图
drop view 视图名称;

4. 视图与表的区别

• 视图是建立在表的基础上，表存储数据库中的数据，而视图只是做一个数据的展示

• 通过视图不能改变表中数据（一般情况下视图中的数据都是表中的列 经过计算得到的结果,不允许 更新）

• 删除视图，表不受影响，而删除表，视图不再起作用

八、存储过程