MySQL进阶

淘气包海鸟

已于 2022-05-31 16:50:16 修改

阅读量86

点赞数

分类专栏： MYSQL 文章标签： mysql 数据库 database

于 2022-05-25 16:17:16 首次发布

本文链接：https://blog.csdn.net/weixin_42229564/article/details/124967951

版权

MYSQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

文章目录

数据库引擎
索引
MySQL锁
视图
存储过程
触发器
- 触发器分为事前触发和事后触发，两种触发有什么区别？语句级触发和行级触发有什么区别？

数据库引擎

数据库引擎是用于存储、处理和保护数据的核心服务。
当你访问数据库时，不管是手工访问，还是程序访问，都不是直接读写数据库文件，而是通过数据库引擎去访问数据库文件

MySQL的两种存储引擎

引擎	特性
MyISAM	不支持外键，表锁，插入数据时，锁定整个表，查表总行数时，不需要全表扫描
InnoDB	支持外键，行锁，查表总行数时，全表扫描

MySQL常见的三种存储引擎（InnoDB、MyISAM、MEMORY）的区别?

Innodb引擎：Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。
MyIASM引擎(原本Mysql的默认引擎)：不提供事务的支持，也不支持行级锁和外键。
MEMORY引擎：所有的数据都在内存中，数据的处理速度快，但是安全性不高。

MySQL存储引擎MyISAM与InnoDB如何选择

InnoDB与MyISAM的异同点
- MyISAM和InnoDB底层都是采用B+Tree这种数据结构来实现 B-Tree索引
  - MyIASM引擎，B+树的数据结构中存储的内容实际上是实际数据的地址值。也就是说它的索引和实际数据是分开的，只不过使用索引指向了实际数据。这种索引的模式被称为非聚集索引。
  - Innodb引擎的索引的数据结构也是B+树，只不过数据结构中存储的都是实际的数据，这种索引有被称为聚集索引。
- InnoDB支持事务，MyISAM不支持
- InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高
- MyISAM是非聚集索引，数据文件和索引文件是分离的，索引保存的是数据文件的指针
- InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。
- MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
- Innodb不支持全文索引，
- MyISAM支持全文索引，查询效率上MyISAM要高
- InnoDB关注事务，MyISAM关注性能（查）
InnoDB和MyISAM如何选择
- 是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM
- 如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读写也挺频繁，请使用InnoDB
- 系统奔溃后，MyISAM恢复起来更困难，能否接受

MySQL的MyISAM与InnoDB两种存储引擎在，事务、锁级别，各自的适用场景?

MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。
InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。

索引

什么是索引？

在一张数据表中不管你建不建索引都会有一个默认索引。如果有主键，则mysql会自动按照主键建索引。如果没有主键，mysql也会创建一个默认索引

索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。
更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。
索引是一个文件，它是要占据物理空间的。独立于表之外
索引表把数据变成是有序的（为什么索引让查询速度变快）

语法

创建索引

 CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (
index_col_name,... ) ;

查看索引

SHOW INDEX FROM table_name ;

删除索引

DROP INDEX index_name ON table_name ;

索引的特点

索引一旦建立, Oracle管理系统会对其进行自动维护, 而且由Oracle管理系统决定何时使用索引
用户不用在查询语句中指定使用哪个索引
在定义primary key或unique约束后系统自动在相应的列上创建索引
用户也能按自己的需求，对指定单个字段或多个字段，添加索引

索引的优点缺点

索引的优点
- 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
- 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
索引的缺点
- 时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；
- 空间方面：索引是存储在硬盘之中的，但是存在硬盘上有一定的缺陷：性能的降低。索引需要占物理空间。

索引结构

Hash索引

基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hashcode），并且Hash索引将所有的哈希码存储在索引中，同时在索引表中保存指向每个数据行的指针。
可以快速定位，但是没有顺序，IO复杂度高，避免不了回表查询数据

B-Tree索引（MySQL使用B+Tree）

B-Tree能加快数据的访问速度，因为存储引擎不再需要进行全表扫描来获取数据，数据分布在各个节点之中。

B+Tree索引

是B-Tree的改进版本，同时也是数据库索引索引所采用的存储结构。数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址。相比B-Tree来说，进行范围查找时只需要查找两个节点，进行遍历即可。而B-Tree需要获取所有节点，相比之下B+Tree效率更高。

B树和B+树的区别

B 树非叶子结点和叶子结点都存储数据，因此查询数据时，时间复杂度最好为 O(1)，最坏为 O(log n)。而 B+ 树只在叶子结点存储数据，非叶子结点存储关键字，且不同非叶子结点的关键字可能重复，因此查询数据时，时间复杂度固定为 O(log n)。
B+ 树叶子结点之间用链表相互连接，因而只需扫描叶子结点的链表就可以完成一次遍历操作，B 树只能通过中序遍历。

为什么说B+比B树更适合实际应用中操作系统的文件索引和数据库索引？

B+ 树减少了 IO 次数。
由于索引文件很大因此索引文件存储在磁盘上，B+ 树的非叶子结点只存关键字不存数据，因而单个页可以存储更多的关键字，即一次性读入内存的需要查找的关键字也就越多，磁盘的随机 I/O 读取次数相对就减少了。
B+ 树查询效率更稳定
由于数据只存在在叶子结点上，所以查找效率固定为 O(log n)，所以 B+ 树的查询效率相比B树更加稳定。
B+ 树更加适合范围查找
B+ 树叶子结点之间用链表有序连接，所以扫描全部数据只需扫描一遍叶子结点，利于扫库和范围查询；B 树由于非叶子结点也存数据，所以只能通过中序遍历按序来扫。也就是说，对于范围查询和有序遍历而言，B+ 树的效率更高。

红黑树和B+树的区别

红黑树是「二叉查找树」的变种，一个Node节点只能存储一个Key和一个Value
B和B+树跟红黑树不一样，它们算是「多路搜索树」，相较于「二叉搜索树」而言，一个Node节点可以存储的信息会更多，「多路搜索树」的高度会比「二叉搜索树」更低

索引的使用场景

适合索引的场景
- 表经常进行 SELECT 操作
- 表很大(记录超多)，记录内容分布范围很广
- 列名经常在 WHERE 子句或连接条件中出现
不适合索引的场景
- 表记录太少（有无索引差别不大）
- 经常增删改的表或者字段
- Where 条件里用不到的字段不创建索引
- 过滤性不好的不适合建索引（重复性较高，比如国籍、性别之类的字段）

什么情况下无法走索引

以%开头的模糊查询
数据类型出现了隐式转换
or这个字段的值都需要有索引，有一个没有索引的他也不走索引
联合索引没用最左前缀
mysql优化器分析全表扫描比索引速度快，索引也会失效（这种情况很小）

索引分类

唯一索引：避免同一个表中某数据列中的值重复，可以有多个
主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空
聚集索引(Clustered)：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据，每个表只能有一个
- 如果存在主键，主键索引就是聚集索引
- 如果不存在主键，将使用第一个唯一索引作为聚集索引
- 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引
非聚集索引(Non-clustered)：将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键，可以存在多个

聚集索引和非聚集索引（二级索引）

聚集索引就是以主键创建的索引
非聚集索引就是以非主键创建的索引
区别
聚集索引在叶子节点存储的是表中的数据
非聚集索引在叶子节点存储的是主键和索引列
使用非聚集索引查询出数据时，拿到叶子上的主键再去查到想要查找的数据。(拿到主键再查找这个过程叫做回表)

回表查询

先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式

SQL性能分析

show [session|global] status 命令可以提供服务器状态信息
-- session 是查看当前会话 ;
-- global 是查询全局数据 ;
show profiles：耗时
explain：执行select语句的信息

最左前缀法则

顾名思义，最左优先，以最左边为起点任何连续的索引都能匹配上。同时遇到范围查询（>、<、between、like）就会停止匹配。
不需要考虑=、in等的顺序，mysql会自动优化这些条件的顺序，以匹配尽可能多的索引列。

覆盖索引

覆盖索引是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。
最常见的方法就是：将被查询的字段，建立到联合索引（如果只有一个字段，普通索引也可以）里去

前缀索引

也叫局部索引，比如给身份证的前 10 位添加索引，类似这种给某列部分信息添加索引的方式叫做前缀索引。

为tb_user表的email字段，建立长度为5的前缀索引
create index idx_email_5 on tb_user(email(5));

sql优化

插入数据

批量插入数据：Insert into tb_test values(1,‘Tom’),(2,‘Cat’),(3,‘Jerry’);
手动控制事务

start transaction; 
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry'); 
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry'); 
insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry'); 
commit;

主键顺序插入，性能要高于乱序插入。
大批量插入数据：load指令

主键优化

表数据都是根据主键顺序组织存放
行数据，都是存储在聚集索引的叶子节点上
数据行是记录在逻辑结构 page 页中的，而每一个页的大小是固定的，默认16K
页分裂
页合并

满足业务需求的情况下，尽量降低主键的长度。
插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
尽量不要使用UUID（唯一识别码）做主键或者是其他自然主键，如身份证号。
业务操作时，避免对主键的修改

order by优化

根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
尽量使用覆盖索引。
多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。
如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size(默认256k)。

group by优化

在分组操作时，可以通过索引来提高效率。
分组操作时，索引的使用也是满足最左前缀法则的。

limit优化

一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化

count优化

count(字段) < count(主键 id) < count(1) ≈ count()，所以尽量使用 count()

update优化

MySQL锁

在日常操作中，UPDATE、INSERT、DELETE InnoDB会自动给涉及的数据集加排他锁，一般的 SELECT 一般是不加任何锁的。
按照锁的粒度分类，可以简单分为行锁和表锁
行锁作用在索引值上。如果sql语句命中了索引，那么锁住的是命中条件内的索引节点（行锁）,如果没有命中，锁住的是整个索引树（表锁）
- 表锁：MyISAM：开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低
- 行锁：InnoDB：开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高
- 页锁：BDB：开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般
不同的存储引擎支持的锁粒度是不一样的：
InnoDB行锁和表锁都支持！
MyISAM只支持表锁！
按使用方式：乐观锁和悲观锁
- 悲观锁与乐观锁是一种思想，而不是数据库锁机制的实现

表锁

表读锁（Table Read Lock）
表写锁（Table Write Lock）
读读不阻塞，读写阻塞，写写阻塞
读读不阻塞，读写阻塞，写写阻塞！
在mysql里边，写锁是优先于读锁的！

行锁

InnoDB是基于索引来完成行锁
行锁可以分为读锁（共享锁）和写锁（排他锁）

读锁：共享，多个事务可以同时读取同一个资源，但不允许其他事务修改

SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

写锁：排他，会阻塞其他的写锁和读锁

SELECT * FROM table_name WHERE ... FOR UPDATE

隔离级别为读未提交即脏读原因：在读的时候没有加读锁，导致可以读取出还没释放锁的记录

多粒度锁机制

为了允许行锁和表锁共存，实现多粒度锁机制

意向共享锁：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的意向共享锁。
意向排他锁：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的意向排他锁。

MVCC(Multi-Version Concurrency Control)多版本并发控制（读写冲突）

可以做到读写不阻塞，提高读写性能
数据库事务有不同的隔离级别，不同的隔离级别对锁的使用是不同的，锁的应用最终导致不同事务的隔离级别

通过生成数据快照（snapshot），并使用这个快照来提供一定级别的一致性读取
MVCC在读已提交和可重复读隔离级别下奏效
针对读已提交隔离级别：生成的是语句级快照
- 原理：在读取的时候生成一个版本号，直到事务其他commit被修改了之后，才会有新的版本号
- 事务A读取了记录(生成版本号)
- 事务B修改了记录(此时加了写锁)
- 事务A再读取的时候，是依据最新的版本号来读取的(当事务B执行commit了之后，会生成一个新的版本号)，如果事务B还没有commit，那事务A读取的还是之前版本号的数据。
针对可重复读隔离级别：生成的是事务级的快照，每次读取的都是当前事务的版本，即使被修改了，也只会读取当前事务版本的数据。
原理：read view（版本信息） undo log
- undo log：会记录修改数据之前的信息（原子性），可以找到版本的数据

乐观锁和悲观锁

写-写，可能丢失更新

丢失更新：一个事务的更新覆盖了其它事务的更新结果
解决的方法：
	使用Serializable隔离级别，事务是串行执行的！
	乐观锁
	悲观锁

乐观锁
- 乐观锁不是数据库层面上的锁，是需要自己手动去加的锁。一般我们添加一个版本字段来实现。
- 乐观锁是一种思想
- 具体实现是，表中有一个版本字段，第一次读的时候，获取到这个字段。处理完业务逻辑开始更新的时候，需要再次查看该字段的值是否和第一次的一样。如果一样更新，反之拒绝。
- 之所以叫乐观，因为这个模式没有从数据库加锁，等到更新的时候再判断是否可以更新。
悲观锁用的就是数据库的行锁，认为数据库会发生并发冲突，直接上来就把数据锁住，其他事务不能修改，直至提交了当前事务

乐观锁和 MVCC 的区别？

多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。这样在读操作不用阻塞写操作，写操作不用阻塞读操作的同时，避免了脏读和不可重复读。
乐观并发控制（OCC）是一种用来解决写-写冲突的无锁并发控制，认为事务间争用没有那么多，所以先进行修改，在提交事务前，检查一下事务开始后，有没有新提交改变，如果没有就提交，如果有就放弃并重试。乐观并发控制类似自选锁。乐观并发控制适用于低数据争用，写冲突比较少的环境。

间隙锁GAP

间隙锁只会在Repeatable read隔离级别下使用

当我们用范围条件检索数据而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合范围条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”

Select * from  emp where empid > 100 for update;
是一个范围查询，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。

InnoDB使用间隙锁的目的有两个：
- 为了防止幻读(Repeatable read隔离级别下再通过GAP锁即可避免了幻读)
- 满足恢复和复制的需要
- MySQL的恢复机制要求：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读
- 间隙锁的存在会导致并发插入问题，尽量减少范围查询

预防死锁

以固定的顺序访问表和行。比如对两个job批量更新的情形，简单方法是对id列表先排序，后执行，这样就避免了交叉等待锁的情形；将两个事务的sql顺序调整为一致，也能避免死锁。
大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。
在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。
降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。
为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大。

视图

什么是视图？以及视图的使用场景有哪些？

视图是一种基于数据表的一种虚表

视图是一种虚表
视图建立在已有表的基础上, 视图赖以建立的这些表称为基表
向视图提供数据内容的语句为 SELECT 语句,可以将视图理解为存储起来的 SELECT 语句
视图向用户提供基表数据的另一种表现形式
视图没有存储真正的数据，真正的数据还是存储在基表中
程序员虽然操作的是视图，但最终视图还会转成操作基表
一个基表可以有0个或多个视图

视图作用

简单
- 视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件
安全
- 数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据
数据独立
- 视图可帮助用户屏蔽真实表结构变化带来的影响。

视图语法

-- 创建视图 
create or replace view stu_v_1 as select id,name from student where id <= 10; -- 查询视图 
show create view stu_v_1; select * from stu_v_1; select * from stu_v_1 where id < 3; 
-- 修改视图 
alter view stu_v_1 as select id,name from student where id <= 10;
 -- 删除视图 
drop view if exists stu_v_1;
 -- 查看视图结构 
desc stu_v_1

视图检查选项

CASCADED 和 LOCAL，默认值为 CASCADED

CASCADED 级联
- 比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 cascaded，但是v1视图创建时未指定检查选项。则在执行检查时，不仅会检查v2，还会级联检查v2的关联视图v1。
LOCAL 本地
- 比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 local ，但是v1视图创建时未指定检查选项。则在执行检查时，知会检查v2，不会检查v2的关联视图v1。

视图的更新

视图更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新：

 A. 聚合函数或窗口函数（SUM()、 MIN()、 MAX()、 COUNT()等） 
 B. DISTINCT 
 C. GROUP BY 
 D. HAVING E. UNION 或者 UNION ALL

存储过程

什么是存储过程？有哪些优缺点？

存储过程就像我们编程语言中的函数一样，封装了我们的代码(PLSQL、T-SQL)。

存储过程的优点：
- 能够将代码封装起来
- 保存在数据库之中
- 让编程语言进行调用
- 存储过程是一个预编译的代码块，执行效率比较高
- 一个存储过程替代大量T_SQL语句，可以降低网络通信量，提高通信速率
存储过程的缺点：
- 每个数据库的存储过程语法几乎都不一样，十分难以维护（不通用）
- 业务逻辑放在数据库上，难以迭代

存储过程和函数的区别

存储过程是用户定义的一系列sql语句的集合，涉及特定表或者其他对象的任务，用户可以调用存储过程，
函数通常是数据库已定义的方法，它接受参数并返回某种类型的值并且不涉及特定用户表。

存储代码

-- 创建 
create procedure p1() begin select count(*) from student; end; 
-- 调用 
call p1();
-- 查看 
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'itcast'; show create procedure p1;
-- 删除 
drop procedure if exists p1;