【MySQL】进阶技术详解

MySQL进阶

1.存储引擎

1）MySQL体系结构

2）存储引擎简介

• 存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。
• 存储引擎是基于表的，而不是基于库的，存储引擎也被称为表类型
• MySQL默认是InnoDB引擎

指定存储引擎：

• 查看所有的存储引擎：show engines

3）存储引擎特点

1）InnoDB引擎

• 是兼顾高可靠性和高性能的通用存储引擎
• 特点：
  • DML操作遵循ACID模型，支持事务
  • 行级锁，提高并发访问性能
  • 支持外键FORGEIN KEY约束，保证数据的完整性和正确性
• 文件：
  xxx.ibd:xxx代表的是表名，innoDB引擎的每张表对应一个表空间，存储该表的表结构、数据和索引：
• 表空间结构：
  

2）MyISAM存储引擎

• MyISAM是MySQL早期的默认引擎
• 特点：
  • 不支持事务，不支持外键
  • 支持表锁，不支持行锁
  • 访问速度快
• 文件：
  • xxx.sdi：存储表结构信息
  • xxx.MYD: 存储数据
  • xxx.MYI: 存储索引

3）Memory存储引擎

• Memory引擎存储数据是存放在内存中的，由于受到硬件问题或者断电问题的影响，只能将这些表作为临时表或缓存使用。
• 特点：
  • 内存存放
  • hash索引（默认）
• 文件：xxx.sdi: 存储表结构信息

4）InnoDB、MyISAM、Memory三者的区别

5）存储引擎选择

2.索引

1）索引概述

• 索引是一种数据结构，可以显著提示数据的查询效率
  
• 索引的简单演示
  当没有索引时，查询一条语句select * from user where age = 45会对全表数据进行挨个查询，比对条件，这种情况为全表扫描。
  当建立了索引时（比如索引是根据age由二叉树结构建立的），查询45时只需要对比节点的左右子节点，递归找即可，往往只需要几次，大大降低对比的次数，效率显著提升。

索引的优缺点

对于一个项目来说，增删改的次数远小于查询的次数，所以缺点可以不计较。

2）索引结构

1.B+树索引

• 1.二叉树的结构分析：
  
  当数据是顺序插入时，会形成一个链表，查询性能大大降低，大数据量情况下，层级会很深，检索速度慢。

• 2.红黑树：
  
  红黑树：自平衡的树结构。
  但是问题还是当数据量过大时，由于一个节点下只能有两个节点，树的层级还是会很深，导致效率低下。

于是自然就产生了B-Tree（多路平衡查找树）

一个节点对应一个指针，每个key下面存储数据。

1）B-Tree

以5阶的B-Tree为例：（5阶：5个子节点（指针），4个key）

• 1.先插入4个数据，此时5阶的B-Tree下只能存储4个key，再插入一条数据时，树会发生裂变：中间key向上分裂。
  

• 2.插入1200，此时树会发生裂变，中间元素为345，向上分裂：
  

• 3.继续插入，当子节点下的key也为4个时，在往该范围内插入数据，则同样会发生中间数据向上裂变：
  

• 4.插入1000，会流向右节点，而右节点中已经拥有4个key了，再增加会向上裂变，中间数据：1200会向上分裂：
  

• 5.继续插入，当产生当前节点以及子节点都有四个key时，再往该范围插入数据，观察B-Tree的结构变化：
  

• 6.当插入：2456时，会流向当前节点的最右侧节点，此时二者都已经拥有4个key了，再次插入，则最右侧子节点的中间元素：1980会向上分裂，而在当前节点中，中间元素为：1200也同样会向上分裂，于是变成：
  

2）B+Tree

• B+树的所有数据都会出现在叶子节点中
• 叶子节点形成一个单向链表

以5阶B+Tree为例：（5阶（5个子节点（指针），4个key）

• 1.当插入4个数据后，当前节点已经拥有4个key，再插入一条数据则超过了：
  

• 2.再插入一个数据，当前节点已经超过4个key，中间索引数向上裂变：
  

• 3.继续插入，当前节点下的子节点超过4个key时，中间索引数也会向上裂变，而叶子节点会保留所有数据，并形成单向链表：

3）MySQL中的B+Tree索引结构

• 对经典B+Tree进行了优化，增加了一个指向相邻叶子节点的链表指针，形成类似于双向链表的指针，提高了区间访问的性能。
  

2.Hash索引

• Hash索引就是采用Hash算法，将键值换算成新的Hash值，映射到对应的槽位上，然后存储在Hash表中
• 如果两个或者多个键值通过hash算法映射到同一个槽位上，就会产生Hash冲突（hash碰撞），一般是通过链表来解决，链表过长时会引入红黑树。

• 特点：
  • 只能用于等值匹配：（=，in）等，不支持范围查询
  • 无法利用索引完成排序
  • 查询效率高，通常只需要一次索引即可，效率一般高于B+Tree

面试题：InnoDB为什么使用B+Tree作为索引结构

• 1.相对于二叉树：层级更少，搜索效率更高
• 2.相对于B-Tree：
  • 1.B-Tree叶子节点和非叶子节点都会保存数据，同时因为页的大小是固定的16k，那么同一页中存储的键值减少，指针跟着减少，同样的数据下，B-Tree需要增加树的高度，导致性能降低。而B+Tree中所有数据保存在叶子节点中，非叶子节点只保存指针，同一页中可以存放的指针数增多（度数增多）。
    防止复习忘记，这里作出详细的举例说明：比如同样的数据下，B-Tree由于要存放数据，16k的一页内存中只能开5阶的B-Tree，也就是最多只能有5个指针一页，而B+Tree由于所有数据都存放在叶子节点，索引页中能存放的指针可能可以开到10阶，那么同样的数据量，B-Tree维护的层级肯定比B+Tree高的多。
  • 2.B+Tree维护了相邻叶子节点之间的指针，对于范围查询等特定查询性能提升许多。
• 3.相对于Hash索引：Hash索引只能支持等值匹配，不能支持范围查询。

3）索引分类

1.聚集索引：

• 必须有，并且只有一个，聚集索引结构中B+Tree的叶子节点存储一行的数据
• 默认主键索引就是聚集索引
• 如果没有则会默认生成rowid作为聚集索引

2.二级索引：

• 可以有多个，通常基于某个字段创建，而其索引结构中B+Tree的叶子节点存储的是对应的主键索引。
  

3.回表查询

• 通常通过二级索引找到叶子节点符合条件的数据对应的主键，然后会根据主键索引回到表中去找到对应的行数据，这叫回表查询。

4.思考题

由于B+Tree的非叶子节点不会存储数据，也就是只会存储指针和key，假设一个指针占用6个字节，一个key占用8个字节，假设一行数据大小为1k，那么每一页中可以存储16行数据：
那么16K的页中，可以放的指针数为： 6 * (n + 1) + 8 * n = 16 * 1024解得n=1170，也就是说一页中可以有1170个指针

• 两层的B+Tree中，叶子节点存储数据，当前节点下有1170个指针，也就是有1170个儿子，每个儿子里面可以存放16K：
• 3层的B+Tree就可以存储： 1170 * 1170 * 16 = 21939856大小的数据

4）索引语法

1.创建索引

create [unique/fulltext] index index_name on table_name (index_col_name, ...);

2.查看索引

show index from table_name;

3.删除索引

drop index index_name on table_name;

• 1.create index idx_name on tb_user(name);
• 2.create unique index idx_phone on tb_user(phone);
• 3.create index idx_pro_age_stu on tb_user(profession, age, status);
• 4.create index idx_email on tb_user(email);

5）SQL性能分析

1.查看SQL执行频率

通过查看数据库的增删改查频率：show [global / session] status like 'com_______';(7个_)

2.查看慢查询日志

• 查看了SQL语句的执行频率，接下来则查看哪些SQL语句查询效率比较慢，从而针对该类型的SQL设定合适的索引。

• 慢查询日志记录了所有执行时间超过指定参数的所有SQL语句的日志。

• 慢查询默认没有开启，需要在MySQL的配置文件中配置：
  

• 配置完成之后，需要重启MySQL服务器进行测试，查看慢日志文件中记录的信息：/var/lib/mysql/localhost-slow.log
  

3.profile详情

• show profiles能够在SQL优化是帮助我们知道时间耗费到哪里了

• select @@have_profiling;：查看当前MySQL是否支持profile操作

• set profiling = 1;：默认关闭，使用这个语句开启profiling

• show prifiles;：查看当前数据库下SQL语句的执行耗费时间
  

• show profile for query query_id;： 根据query_id查询指定SQL的具体耗时情况
  

4.explain执行计划

• explain或者desc命令获取MySQL如何执行select语句的信息，包括select语句执行过程中表如何连接和连接的顺序

#直接在select语句前加上关键字explain/desc
explain select * from tb_user where id = 1;

6）索引使用规则

1.验证索引的效率

对sn字段建立索引之后，查询同样的语句，需要的时间大大减少：

2.最左前缀法则

• 如果索引涉及多列（联合索引），需要遵守最左前缀法则
• 最左前缀法则：查询需要从索引的最左列开始，并且不跳过索引中的列
• 如果跳跃了其中的某一列，索引会部分失效（后面字段的索引失效）
  
• 1.当按照索引从左到右的顺序查询时，索引全部使用了
  
• 2.当依次减去右边的索引时，可以推测出索引占用的长度：

• 3.如果不添加最左侧的索引，则会发生索引失效，走全表扫描：
  

• 4.中间跳过某个索引时，会出现部分索引失效，只使用了profession的索引

• 5.只要出现最左侧索引即可，更查询顺序无关。

• 6.联合索引中使用范围查询（<,>)的，范围查询右侧的列索引失效
  

7）索引失效的情况

• 1.聚合索引没有符合最左前缀法则
  • 如果查询条件的第一个不是联合索引最左侧的索引字段，会导致全部索引都失效，走全表扫描
  • 如果中间跳过联合索引的中间某一个索引列，会导致后面的它后面的索引失效（部分索引失效）
  • 联合索引中出现范围查询时，会导致范围查询右侧的列索引失效
• 2.对建立索引的字段进行了运算，会导致索引失效
• 3.索引列进行了前段模糊匹配：%xxx会导致索引失效，后段模糊匹配则不会：xx%
• 4.字符串类型的索引列查询时没有加''，会导致对应索引失效：name = 张三
• 5.使用or连接的前后两个条件中的字段都需要有索引，如果一侧没有索引，则会导致另外一边的索引也失效：xxx or xxxx
• 6.数据分布影响：如果MySQL评估使用索引查询比全表扫描还要慢，则不会使用到索引

失效演示：

• 1.第六节中有
• 2.当对phone字段进行截取匹配时，会导致phone字段原本的索引失效：
  
• 3.使用尾部模糊匹配查询名字为like '软件%'，仍能够使用name字段的索引，而使用头部模糊匹配查询名字为like '%工程'时，走的是全表扫描，无法使用name字段的索引：
  
• 4.status字段为String类型，当查询时没有加''时，可以查询到数据，但是无法使用status字段的索引：
  
• 5.phone和status都建立了索引，而当使用or连接时，由于age没有索引，导致在or另外一侧的phone和status字段的索引都无法使用：
  
• 6.查询phone大于某一个值的数据时，由于MySQL判断根据当前条件查询的数据较多时（表中绝大多数数据都是满足条件时），会直接走全表扫描，而当根据查询条件判断查询的数据占表中的数据较少时，则会走索引：
  

8）SQL提示

• 是优化数据库的一个重要手段，
• 当同一个字段有多个索引时，我们如果希望它使用我们指定的索引来查询的话就要用到SQL提示
• SQL提示：在SQL语句中加入一些人为的提示来达到优化操作的目的
• 1.use index: explain select * from tb_user use index(idx_profession) where profession = '软件工程';;
• 2.ignore index: explain select * from tb_user ignore index(idx_profession) where profession = '软件工程';;
• 3.force index: explain select * from tb_user force index(idx_pro_age_stu) where profession = '软件工程';;
  

9）覆盖索引

• 覆盖索引：查询的数据在使用索引进行查询时就已经拿到了需要返回的数据，不再需要回表查询。

使用explain查看SQL的执行计划时，当Extra列出现：

• using index condition：说明使用了索引，但是需要回表查询数据
• using where, uing index：说明查询时使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

示例：

假设有一张表tb_user的索引结构如下：

• 1.使用select * from tb_user where id = 2;进行查询时，由于查询条件是主键，默认是聚集索引，此时查询到的key下面存放的就是整行的数据，无需再回表查询，这就是覆盖索引

• 2.使用select id, name from tb_user where name = 'Arm';进行查询时，通过在name字段建立的索引查找到对应的key值，叶子节点保存的是对应的数据id，由于查询的字段是id和name，刚好都包含在查找到的数据里，无需再回表查询，这就是覆盖索引。
  

• 3.使用select id, name ,gender from tb_user where name = 'Arm';进行查询时，会通过name字段的索引查找到对应的key值，然后由于查找的字段是id,name,gender，现在根据name索引能掌握的信息只有name=Arm对应的id =2，那么要找gender只能根据id= 2, 按照id聚集索引回到整表去找到对应的key下面的叶子节点存储的行数据获取。（这一过程也叫回表查询），这就并不是覆盖索引
  

覆盖索引面试题

• 可以根据username和password建立联合索引，这样就是一个二级索引，当执行上面的SQL语句查询时，会走这个联合索引，并找到对应的id，无需回表查询。
• 回表查询：查询时根据二级索引找到对应数据的id，然后根据id到聚集索引中查询到这一行的数据。

10）前缀索引

• 当字段类型为字符串（varchar, text）等时，有时候需要索引很长的字符串，根据该类型字段建立索引会让索引变得很大，查询时会浪费大量的磁盘IO，影响查询效率。
• 此时可以只将字符串的一部分前缀，建立索引，就可以大量节省索引空间，从而提高索引效率。
• 语法：create index idx_xxxxx on table_name(column(n));
• 前缀长度: 可以根据索引选择性来决定
• 选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高
• 比如：唯一索引的选择性是1，这是最好的索引选择性

select count(distinct email) / count(*) from tb_user;：查看当前字段的选择性
select count(dinstinct substring(email, 1, 5) /count(*) from tb_user; ：查看截取当前字段的前1~5个字符作为索引的选择性
示例：

可以发现：当截取email的前9个字符作为索引和截取前5个字符作为索引时，选择性是一样的，那么我们肯定优先选择截取前5个字符作为索引，这样索引占用的空间更小，并且区分度还是较高的。当然如果需要最好的区分度肯定是全字段索引，查询效率高一些。

11）单列索引和联合索引的选择

• 1.单列索引：当phone和name都有单列索引，但是根据两个字段同时查询时，只会使用phone的索引，而name就会回表查询。
  

• 2.联合索引：当根据两个字段建立联合索引时，查询时使用的覆盖索引，效率更高。
  

• 当业务场景中如果存在多个查询条件，考虑针对查询字段建立索引时，建议建立联合索引，而非单列索引

• 联合索引的索引结构：
  

12）索引设计原则

• 1.对哪些表建立索引：数据量大，查询比较频繁的表
• 2.对该表的哪些字段建立索引：where、order by、group by操作的字段
• 3.建立什么索引：区分度高的列作为索引，尽量建立唯一索引，如果能建立联合索引尽量建立联合索引
  

---

3.SQL优化

1）插入数据优化

1.insert优化

• 1.批量插入：
  insert into tb_user values(1, 'Tom'),(2, 'Cat'),(3,'Jerry');
  一次插入多条数据可以避免多次与数据库的连接耗时（最多一次插入5000-10000）
• 2.手动提交事务：

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

默认自动提交事务插入一条数据就会提交一次，然后再开启事务插入，设置手动提交就可以一次批量插入多条，然后一并提交。

• 3.主键顺序插入：
  
  顺序插入效率更高

2.大批量插入

一次需要插入上百万条数据时，使用insert语句插入性能较低，此时可以使用MySQL提供的load指令进行插入。

• 1.连接MySQL服务端时，加上参数： --local-infile
mysql -- local-infile -u root -p
• 2.设置全局参数local_infile为1，开启从本地加载文件导入数据的开关：
  set global local_infile = 1;
• 3.执行load指定将准备好的数据文件中的数据加载到表结构中：

load data local infile '/root/sql.log' into table `tb_user` fields terminated by ',' lines terminated by '\n';

2）主键优化

• 1.满足业务需求的情况下，尽量降低主键的长度
• 2.插入数据时，尽量选择顺序插入，选择使用auto_increment自增主键
• 3.尽量不要使用UUID或者是其他自然主键，比如身份证号（一般不是顺序插入）
• 4.业务操作时，避免对主键的修改

数据和索引是存储在页中的，页可以为空

1.页分裂

• 1.正常主键顺序插入时：当当前页存储慢了数据时，会自动填充到下一页中，并维护两个页之间的链表关系：
  
• 2.如果主键是乱序插入：当相邻两页都存满了已经无法存入新数据时，此时需要插入一个中间的数据时，就会发生页分裂：
  将前面页的超过50%的数据转移到新的页中，然后将新数据存放到新的页的后面，最后在重新维护链表关系：

2.页合并

• 当删除一行记录时，实际上并没有实际被物理删除，只是被标记为删除并且它的空间会变成允许其他记录声明使用。
• 当页中删除的记录达到merge_threshold（默认为页的50%），InnoDB会寻找最靠近的页看看是否可以将两个页（两个页数据都50% 以内）合并，从而优化空间：
  比如当#2的数据被删除到不足50% 时，就会开始寻找相邻的页看是否可以合并，此时#3的数据也是不足50% 的，所以#2和#3就会合并：
  
  

3）排序优化（order by）

• 1.using filesort：通过表的索引或者全表扫描，读取满足条件的行数据，然后在排序缓冲区sort buffer中完成排序操作，不是通过索引直接返回排序结果的排序叫做filesort排序
• 2.using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要再额外排序，效率高。

---

• 1.当直接根据age和phone进行查询排序时：是需要将读取的数据在排序缓冲区排序之后再返回结果的：

• 2.当age和phone建立了联合索引，再根据age升序排序，age相同再根据phone升序排序返回结果时，由于索引中已经存在对age和phone的排序，这时就可以直接返回查询到的有序数据：
  

• 3.同一的如果需要根据age升序排序，再根据phone降序排序：那么我们可以建立age升序和phone降序的索引，这样查询时，返回的结果也就是根据age升序排序，phone降序排序的有序数据，直接返回，无需额外排序：
  

• 由age升序、phone降序建立的索引 以及 由age升序、phone降序建立的索引的存储结构：

• 1.根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
• 2.尽量使用覆盖索引
• 3.多字段排序，一个升序一个降序，那么可以使用联合索引在创建时设立适当的规则（asc/desc）

4）分组优化（group by）

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

5）分页查询优化（limit）

• 当limit进行大数据量的分页：比如：limit 1000000, 10，此时需要排序前1000000条数据，然后仅仅返回之后的10条记录，其余全部丢弃，查询排序的代价非常大

• 当我们执行select * from tb_sku limit 9000000, 10;时，需要排序前9000000条数据，然后仅仅返回之后的10条记录：

• 先查到9000000之后的10条记录的id：
  select id from tb_sku order by id limit 9000000, 10;

• 将其作为一个表，与原表进行多表联查：
  select * from tb_user s, (select id from tb_sku order by id limit 9000000, 10) a where s.id = a.id;

• 原本需要19s的查询，优化到了11s；

6）总计数优化（count()）

count(1)，cout(*)，count(主键)，count(字段)

7）更新优化（update）

• 当执行update语句时，如果我们对没有建立索引的字段进行更新，由于没有索引，执行事务时，行锁会升级为表锁，那么其他update语句就无法对其他行的数据进行修改，并发性降低。
• 当我们更新时，尽量选择有索引的字段进行更新（并且该索引不能失效，否则也会升级为表锁）：
  

8）总结

4.视图、存储过程、触发器

1）视图

• 是一种虚拟存在的表，视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的

• 视图只保存了查询的SQL逻辑，不保存查询结果，当我们创建视图时，主要的工作就在创建视图的SQL查询语句上

• 1.创建视图：
  create [or replace] view 视图名称[(列名列表)] as select语句 [with [cascaded | local] check option;

• 2.查询视图：

  • 查看创建视图语句：show create view 视图名称;
  • 查看视图数据：select * from 视图名称;

• 3.修改：

  • 1.create [or replace] view 视图名称[(列名列表)] as select语句 [with [cascaded | local] check option;
  • 2.alter view 视图名称[(列名列表)] as select语句 [with [cascaded | local] check option];

• 4.删除：drop view [if exists] 视图名称 [,视图名称] ....;

create view user_view as select id, name from user where id != 0;

show create view user_view;

select * from user_view;

create or replace view  user_view as select id, name, nickname from user where id != 0;

alter view user_view as select id, name from user where id != 1;

drop view user_view;

1.视图的检测选项

• 使用with check option子句创建视图时，MySQL会通过视图检测正在更改的行，例如插入，更新，删除，以使得更改符合视图定义。
• MySQL允许基于另一个视图创建视图，他还会检查依赖视图中的规则以保持一致性。
• 检查的范围：cascaded 和 local ， 默认值为cascaded

1.cascaded结联

create view v1 as select id, name from student where age <= 25 with cascaded check option;

当使用视图执行更改操作时，由于视图是虚拟的表，实际更改的还是基表中的数据：

执行：insert into v1 values('tom', 28);
开启了检查选项，则会检查当前更改的数据是否满足视图建立时的规则：age <= 25， 发现28>25，此时就不会添加数据。反之如果更改时age<=25就能更新成功；

• 2.结联：

create view v1 as select id, name from student where age <= 25;
create view v2 as select id, name from v1 where age >= 18 with cascaded check option;

这里v1视图建立时没有加检查选项，而v2是基于v1建立的视图，开了检查选项，此时插入的数据先是会通过v2的检查：age>=18，然后也需要经过v1的检查：age <= 25: 也就是说现在通过v2更新数据时，只有age >= 18 && age <= 25的数据才能更新成功。v1此时像是被加了检查选项一样。

create view v1 as select id, name from student where age <= 25;
create view v2 as select id, name from v1 where age >= 18 with cascaded check option;
create view v3 as select id, name from v2 where age <= 20;

如果此时再基于v2创建视图v3，但是未开检查选项是怎么样的：此时如果通过v3更新age >= 18 && age <= 20的数据肯定是能更新成功的，对于age >= 20 && age <= 25的数据由于v3未开检查选项所以同样能更新成功，而age>25的数据是肯定无法更新成功的

• 所谓结联：就是当根据当前视图去更新某个数据时，它不仅会检查是否满足当前视图的条件，还会检查它所有依赖的视图条件。
  

2.local

• 只检查加了检查选项的视图的条件

create view v1 as select id, name from student where age <= 25;
create view v2 as select id, name from v1 where age >= 18 with local check option;
create view v3 as select id, name from v2 where age <= 20;

与上述的视图创建一致，但是检查选项为local，当通过v3去更新数据时，如果插入age >= 18的数据都可以成功，因为通过v3,会先检查v2，发现v2有一个local检查选项，于是会检查是否满足v2的条件，而通过v2会继续检查v1的选项，而v1没有开检查选项，于是只要满足v2的选项就可以插入成功。

2.视图的更新和作用

• 视图只有在视图中的行于基表中的行之间必须存在一对一的关系时，才能进行数据的更新
• 如果包含以下任何一项，则该视图不能更新：
  

视图作用：

• 1.简单：视图不仅可以简化用户对数据的理解，也可以简化操作，经常被使用的查询可以定义为视图，从而使得用户不必为以后的操作每次指定全部的条件
• 2.安全：数据库虽然是可以授权访问的，但是最多是表授权，不能授权到特定的行和特定的列上，通过视图，用户只能查询和修改他们所能见到的数据
• 比如一个表中包含id，name和account字段，我们可以基于id，name建立视图，然后在用户需要进行查询时，使其在视图上查询和修改，这样就可以防止数据的重复存储和敏感数据的暴露
• 3.数据独立：
  当原始表结构发生变化时，比如原本的name字段变成studentName时，我们在只需要将视图中的字段studentName设置别名为name即可，这样用户就不会感受到任何的变化。

create view v3 as select id, studentName as name from v2 where age <= 20;

3.案例

• 1.基于该表的其他字段建立视图，这样用户就通过视图查看除了phone和email之外的信息

create view tb_user_view as select id, name, profession, age, gender, status, createtime  from tb_user;

• 2.当用户想查询某个学生对于的课程时，每次进行三表联查肯定是比较麻烦的，我们可以将三表联查的结果作为一个视图交给用户，这样每次只需要从视图中直接使用简单的SQL语句就能完成对于的查询：

create view tb_stu_course_view as select s.name as student_name, s.no as student_no, c.name as course_name
from student as s, student_course as sc, course as c where s.id = sc.studentid and sc.courseid = c.id;

• 单表操作就能完成查询：select * from tb_stu_course_view;
  

2）存储过程

• 事先经过编译并存储在数据库中的一段SQL语句的集合。
• 调用存储过程可以简化应用开发的工作，减少数据在数据库和服务器之间的传输，提高数据的处理效率
• 实质上是数据库SQL语句的封装和重用
• 特点：
  • 1.封装，复用
  • 2.可以接收参数，返回数据
  • 3.减少网络交互，提升数据的处理效率

1.语法

• 1.创建：

create procedure 存储过程名称([参数列表])
begin
		--语句
end;

• 2.调用：call 存储过程名称([参数]);
• 3.查看：1.select * from information_schema.routines where routine_schema = 'xxx'; 查询指定数据库的存储过程及状态信息
  2.show create procedure 存储过程名称; 查询某个存储过程的定义
• 4.删除：drop procedure [if exists] 存储过程名称;

# 创建
create procedure user_pro()
begin
    select * from user;
    select * from tb_brand;
end;

# 调用
call user_pro();

# 查看
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'lik';

show create procedure user_pro;

# 删除
drop procedure if exists user_pro;

2.变量

1）系统变量

2）用户自定义变量

• 无需提前声明
  

3）局部变量

• 需要提前声明
  

create procedure p1()
begin
    declare account int;
    select count(*) into account from user;
    select  account;
end;


call p1;

4）if条件判断

create procedure compare()
begin
    declare score int default 76;
    declare scale varchar(10);
    if score >= 80 then
        set scale = '优秀';
    elseif score >= 60 then
        set scale = '及格';
    else
        set scale = '不及格';
    end if;
    select scale;
end;

call compare;

3.参数

案例练习

create procedure rank(in score int, out result varchar(10))
begin
    if score >= 85 then
        set result = '优秀';
    elseif score >= 60 then
        set result = '及格';
    else
        set result = '不及格';
    end if;
end;

call rank(85, @result);
select @result;


create procedure precent(inout score double)
begin
    set score := score * 0.5;
end;

set @score = 190;
call precent(@score);
select @score;

4.流程控制

案例练习

• sql语句:

create procedure declareSesson(in month int, out sesson varchar(10))
begin
    case
        when month >= 1 and month <= 3 then
            set sesson := '第一季度';
        when month >= 4 and month <= 6 then
            set sesson := '第二季度';
        when month >= 7 and month <= 9 then
            set sesson := '第三季度';
        when month >= 10 and month <= 12 then
            set sesson := '第三季度';
        else
            set sesson := '非法输入';
    end case;
end;

call declareSesson(10, @sesson);

select @sesson;

5.条件判断

• 输入一个n，要求返回1~n的和

1）while

create procedure sums(in n int)
begin
    declare total int default 0;

    while n > 0 do
        set total := total + n;
        set n := n - 1;
    end while;
    select total;
end;

call sums(100);

2）repeat

• 满足条件则退出循环

create procedure sums1(in n int)
begin
    declare result int default 0;
    repeat
        set result := result +  n;
        set n =  n - 1;
    until n <= 0 end repeat;
    select result;
end;

call sums1(100);

3）loop

create procedure p(in n int)
begin
    declare  result int default 0;

    sum:loop
        if n <= 0 then
            leave sum;
        end if;
        set result := result + n;
        set n := n - 1;

    end loop sum;

    select result;
end;

call p(100);


create procedure podd(in n int)
begin
    declare  result int default 0;

    sum:loop
        if n <= 0 then
            leave sum;
        end if;
        if n % 2 = 1 then
            set n := n - 1;
            iterate sum;
        end if;
        set result := result + n;
        set n := n - 1;

    end loop sum;

    select result;
end;

call podd(100);

6.游标

• 游标是用来存储查询结果集的数据类型
• 在存储过程和函数中可以使用游标对结果集进行循环处理
• 游标的使用：
  • 1.声明游标：declare 游标名称 cursor for 查询语句;
  • 2.打开游标：open 游标名称;
  • 3.获取游标记录：fetch 游标名称 into 变量[, 变量];
  • 4.关闭游标：close 游标名称;

案例

create procedure cursorDemo(in uage int)
begin
    declare uname varchar(100);
    declare uprofession varchar(100);
    declare tb_user_cursor cursor for select name, profession from tb_user where age <= uage;

    drop table if exists tb_user_new;
    create table if not exists  tb_user_new(
        id int auto_increment primary key ,
        name varchar(100),
        profession varchar(100)
    );

    open tb_user_cursor;
    while true do
        fetch tb_user_cursor into uname, uprofession;
        insert into tb_user_new values (null, uname, uprofession);
    end while;
    
    close tb_user_cursor;
end;

call cursorDemo(45);

条件处理程序

• 可以用来定义在流程控制结果执行过程中遇到问题（条件）时相应的处理步骤：

• 上个案例中，当游标中没有数据时，由于设定了while true，就会报错误码为02000的SQL错误，那么我们可以使用条件处理程序来控制当SQL错误码为02000时，就关闭游标：

create procedure cursorDemo(in uage int)
begin
    declare uname varchar(100);
    declare uprofession varchar(100);
    declare tb_user_cursor cursor for select name, profession from tb_user where age <= uage;

#     声明一个条件处理程序，当遇到SQLSTATE为02000时触发，关闭游标
    declare exit handler for SQLSTATE  '02000' close tb_user_cursor;

    drop table if exists tb_user_new;
    create table if not exists  tb_user_new(
        id int auto_increment primary key ,
        name varchar(100),
        profession varchar(100)
    );

    open tb_user_cursor;
    while true do
        fetch tb_user_cursor into uname, uprofession;
        insert into tb_user_new values (null, uname, uprofession);
    end while;

    close tb_user_cursor;
end;

call cursorDemo(45);

• 或者使用：

#     声明一个条件处理程序，当遇到SQLSTATE为02000时触发，关闭游标
    declare exit handler for NOT FOUND close tb_user_cursor;

3）存储函数

• 依旧是1~n的累加：

create function fsum(n int)
returns int deterministic
begin
    declare total int default 0;

    while n > 0 do
        set total := total + n;
        set n := n - 1;
        end while;

    return total;
end;


select fsum(100);

4）触发器

• 与表有关的数据库对象，在insert/update/delete之前或者之后，触发并执行触发器中定义的SQL语句集合
• 可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作
• 使用别名　OLD和　NEW　来引用触发器中发生变化的记录内容，这与其他数据库是相似的
• MySQL触发器选现在只支持行级触发，不支持语句级触发
  

1.语法

2.触发器简单应用：日志记录

• insert , update, delete触发器编写：（日志记录）：

create table user_logs(
  id int(11) not null auto_increment,
  operation varchar(20) not null comment '操作类型, insert/update/delete',
  operate_time datetime not null comment '操作时间',
  operate_id int(11) not null comment '操作的ID',
  operate_params varchar(500) comment '操作参数',
  primary key(`id`)
)engine=innodb default charset=utf8;


# 插入数据的触发器
create trigger user_logs_insert_trigger
    after insert on tb_user for each row
begin
    insert into user_logs (id, operation, operate_time, operate_id, operate_params)
        values(null, 'insert', now(), new.id, concat('插入的数据为： id=', new.id , ',name=' , new.name , ',phone=' , new.phone));
end;

# 更新数据的触发器
create trigger user_logs_update_trigger
    after update on tb_user for each row
begin
    insert into user_logs (id, operation, operate_time, operate_id, operate_params)
        values(null, 'update', now(), new.id, concat('更新之后的数据为： id=', new.id , ',name=' , new.name , ',phone=' , new.phone,
                                                    ' || 更新之前的数据为： id=', old.id , ',name=' , old.name , ',phone=' , old.phone));
end;

# 删除数据的触发器
create trigger user_logs_delete_trigger
    after delete on tb_user for each row
begin
    insert into user_logs (id, operation, operate_time, operate_id, operate_params)
        values(null, 'delete', now(), old.id, concat('删除的数据为： id=', old.id , ',name=' , old.name , ',phone=' , old.phone));
end;

show triggers ;

insert into tb_user(id, name, phone, email, profession, age, gender, status, createtime)
VALUES (28,'三皇子','18809094215','erhuangzpp@163.com','软件工程',23,'1','4',now());



update tb_user set age = 32 where id = 23;

# 行级触发器，涉及到一行的更新就会触发一次
update tb_user set profession = '软件工程师' where id <= 5;

delete from tb_user where id = 27;

drop trigger user_logs_update_trigger;

delete from user_logs;

• 日志表:
  
  当执行：update tb_user set profession = '软件工程师' where id <= 5;时，因为MySQL的触发器是行级触发器，涉及到的行数为5行，于是会对应五行的修改日志：

5）总结

5.锁

1）概述

• 锁是计算机协调多个进程或者线程并发访问某一个资源的机制

• 如何保证数据并发访问的一致性、有效性是数据库必须解决的一个问题

• 锁冲突也是影响数据库并发访问性能的一个重要因素

• MySQL中的锁，按照锁的粒度来分：

  • 全局锁：锁定数据库中所有表
  • 表级锁：每次操作锁定整张表
  • 行级锁：每次操作锁住对应的行数据

2）全局锁

• 全局锁是对整个实例进行加锁，整个实例处于只读状态
• DML语句、DDL语句以及更新操作的事务提交语句都将被阻塞
• 典型使用场景为全库的备份，获取一致性视图，保证数据的完整性
  
  

语法

• 加全局锁：flush tables with read lock;
• 执行数据备份：mysqldump -u root -p 123456 itcast > itcase.sql
• 解锁：unlock tables;

弊端

• 在主库上备份，那么在备份期间都不能执行更新
• 如果在从库上备份，那么备份期间从库不能执行主库同步过来的二进制日志文件，导致主从延迟

3）表级锁

1.表锁

• 锁住整张表，粒度大
• 发生锁冲突的概率最高，并发效率最低
• 表锁分类：

  • 表共享读锁：
    （对于加锁的当前客户端，只能读，不能写，其他客户端也不能写，只能读）
    

  • 表独占写锁：
    （对于加锁的当前客户端，即可以写也能读，其他客户端不能读也不能写）
    

语法

• 加锁：lock tables 表名... read/write;
• 释放锁：unlock tables / 客户端断开连接;

2.元数据锁

• meta data lock (MDL)
• MDL是系统自动控制，在访问一张表时会自动加上
• 主要作用是维护表元数据的数据一致性，在表上有活动事务时，不可对元数据进行写入操作（表的结构的不能发生改变）
• 简单来说，开启事务之后可能会对表数据进行修改查询，这时候不能对表的结构进行修改，否则数据结构对不上。
• 为了避免DML与DDL语句的冲突，保证读写的正确性
  
  

3.意向锁

• 在MySQL的隔离级别中，当开启事务对一行数据进行修改时，会对对应行数据加行锁，而其他客户端需要对当前表进行加锁时就需要逐行扫描，看是否能对当前表进行加锁：
  
• 为了避免DML在执行时，加行锁与表锁冲突，加入意向锁使得表锁不需要再检查每行数据是否加锁，从而减少表锁的检查：
  
  

6.InnoDB引擎

1）逻辑存储结构

• 一个区的大小是1M
• 一个页的大小是16K
• 一个区中包含连续的64个页
• InnoDB中申请空间时一次性会分配4~5个区，从而保证申请的页是连续的
  

2）架构

1.内容结构

• 1.BufferPool：缓冲池，可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（如果缓冲池没有数据则会从磁盘中加载并缓存），然后再以一定的频率刷新到磁盘，从而减少磁盘IO，提高效率。
  

• 2.ChangBuffer：更改缓冲区（针对于非唯一二级索引页），在执行DML语句时，如果这些数据Page没有在BufferPool中，不会直接操作磁盘，而是将数据变更存储在更改缓冲区中，在未来数据被读取时，再将数据合并恢复到BufferPool中，然后将合并的数据刷新到磁盘中。

• ChangeBuffer的意义：与聚集索引不同，二级索引通常是非唯一的，并且以相对随机的顺序插入二级索引，同样，删除和更新可能会影响索引树中不相邻的二级索引页，如果每一次都操作磁盘，会造成大量的磁盘IO，有了ChangeBuffer之后，我们可以在缓冲池中进行合并处理。

• 3.自适应Hash索引：
  

• 4.日志缓冲区：

• 用来保存要写入磁盘中的log日志数据，然后会定期将日志数据刷新到磁盘中。

2.磁盘结构

• 系统表空间和每个表的文件表空间：
  

• 通用表空间：
  

• 双写缓冲区和
  

3）后台线程

4）事务原理

• 事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或者撤销操作请求，即这些操作要么同时成功，要么同时失败。

• 事务的特性：

  • 原子性：事务是一个不可分割的最小操作单元，要么同时成功，要么同时失败
  • 一致性：事务完成时，必须使所有数据都保持一致状态
  • 隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
  • 持久性：事务一旦提交或者回滚，他对数据库的数据改变就是永久的

事务特性的实现原理：

1.redolog（重做日志）

• 记录的是事务提交时数据页的物理修改，用来实现事务的持久性
• 该日志文件由两部分组成：重做日志缓冲（redolog buffer)和重做日志文件（redolog file)，重做日志缓冲是在内存中，重做日志文件是在磁盘中。
• 当事务提交之后会把所有修改信息保存到该日志文件中，用于在刷新脏页到磁盘发送错误时，进行数据恢复使用。
  

整个过程：

• 1.当事务进行数据增删改时，会将数据的变化信息放到redologbuffer中
• 2.当事务提交时，会将redologbuffer中的数据变化直接刷新到redologfile重做日志文件中（这个过程叫WAL（write-Ahead Logging 先行日志）
• 3.而真实的脏页数据刷新到磁盘中是定期刷新的，不是立马刷新
• 4.如果脏页数据刷新时发生了错误，就会从redologfile重做日志文件中读取对应的数据变化，完成磁盘中数据的对应修改
• 5.如果脏页数据刷新成功了，那么redologfile重做日志文件中的记录也就不需要了，会定期清理

2.undolog（回滚日志）

• 记录数据被修改前的信息，作用包括：提供回滚和MVCC（多版本并发控制）
• redolog记录的是物理日志，undolog记录的是逻辑日志。可以认为当delete一条记录是，undolog中就会记录一条对应的insert记录，反之，当update一条记录时，他记录的是一条对应相反的update记录。当执行rollback时，就可以从undolog中的逻辑记录读取到相应内容并进行回滚。

Undolog销毁：undolog在事务执行时产生，事务提交时并不会立即删除undolog，因为这些日志可能还用于MVCC
Undolog存储：undolog采用段的方式进行管理和记录，存在rollback segment中，内部包含1024个undolog segment。

5）MVCC（多版本并发控制）

1.概念

• 1.当前读：读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。

• 2.快照读：

  • 简单的select语句（不加锁）就是快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁是非阻塞读。
  • Read commited：每次select 都生成一个快照读
  • Repeatable Read：开启事务之后第一个select语句才是快照读，之后的每个select语句都是对快照读的复用，以此实现可重复读
  • Serializable：快照读会退化成当前读

• 3.MVCC（Multi-Version-Concurrency-Control）多版本并发控制：指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了非阻塞读功能，MVCC的实现主要依赖于：数据库记录中的三个隐式字段、undolog日志、readView。

2.隐藏字段

• 1.db_trx_id：最近修改的事务ID，记录插入这条记录或者最后一次修改该记录的事务ID；
• 2.db_roll_prt：回滚指针，指向这条数据的上一个版本，用于配合undolog，指向上一个版本；
• 3.db_row_id：隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。

3.undolog

• 回滚日志：在insert、update、delete的时候产生的便于数据回滚的日志
• 当insert的时候，产生的undolog日志只在回滚时需要，在事务提交之后，可被立即删除
• 而update、delete的时候，产生的undolog日志不仅在回滚时需要，在快照读时也需要，不会被立即删除

4.undolog版本链

• 不同事务或者相同事务对同一条记录进行修改，会导致改记录的undolog生成一条记录版本的链表，链表头部是最新的上一个版本记录，链表尾部是最早的版本记录。

案例模拟：

• 1.当新插入一条数据时：
  它的事务ID为1（事务ID是自增的），并且它的回滚指针此时为空，因为是第一个版本，所以此时产生的undolog，当事务提交成功时就删除，事务失败就回滚。
  
• 2.当事务2修改id为30的记录，age改为3：
  那么在undolog中首先会将原本的数据记录下来，并且保存它的地址，在数据修改完之后就会将上一个版本的数据的地址值放到db_roll_ptr中，事务ID为2：
  
• 3.当事务3继续修改id为30的记录，并将name改为A3时，undolog日志中仍会记录上一个版本的数据对应的地址值，然后将该地址值放到db_roll_ptr中，事务ID为3：
  
• 4.当事务4继续修改id为30的记录，将age改为10时，undolog会记录修改前的数据对应的地址，然后将其赋给修改后的数据的db_roll_ptr中，事务ID自增为4：
  

5.readview

• 读视图：是快照读SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id
• 核心字段：
  • m_ids：当前活跃的事务ID集合
  • min_trx_id：最小活跃事务ID
  • max_trx_id：预分配事务ID，当前最大事务ID+ 1（因为事务ID是自增的）
  • creator_trx_id：ReadView创建者的事务ID
    
• 生成ReadView的时机：
  • Read Committed（读已提交）：在事务每一次执行快照读时生成ReadView
  • Repeatable Read（可重复读）：仅在事务中第一次执行快照读时生成ReadView，后续则直接复用该ReadView

版本链数据访问规则：

在RC(Read Committed)隔离级别下，快照读获取数据的执行原理：

• 在RC隔离级别下，每次通过select执行快照读时会生成一个Read View
• 那么我们先看如图第一次查询时的快照读：此时活跃事务集合m_ids：{3， 4， 5}，最小的活跃事务ID：min_trx_id：3，预分配的事务ID:max_trx_id: 6
• 根据版本链访问规则：该ReadView提取的数据版本推理：从最新版本往前推
• 1.能否访问事务ID为4的版本的数据：
  • a. 4 != 5，数据并不是当前事务更改的
  • b. 4 > 3，说明当前事务还未提交
  • c.4 < 6，说明该事务并不是在当前readView生成后开启的
  • d. 3<= 4 <= 6,但是4在活跃事务集合中，说明无法访问该数据
    综上分析，这次快照读，查找到的数据并不是由事务ID为4修改的版本
• 2.继续往前推，事务ID为3的： 可知3也不符合。
• 3.事务ID为2，可以发现此时满足：当前事务ID比当前活跃事务的最小ID还要小，说明，当前事务是在此次Read VIew生成时已经提交的事务，允许访问这个版本的数据，就会返回这个版本对应的数据。
  

第二次查询时的快照读：

• 此时活跃事务集合m_ids：{ 4， 5}，最小的活跃事务ID：min_trx_id：4，预分配的事务ID:max_trx_id: 6
• 分析能否访问到事务ID为4的版本数据：4在 mix_trx_id 和 max_trx_id之间，但是4仍然是活跃事务集合中的ID，则无法访问该版本
• 事务ID为3的版本数据：3在 mix_trx_id 和 max_trx_id之间，3并不在活跃事务ID集合m_ids中，则说明可以访问该版本

在RR(Read Repeated)隔离级别下，快照读获取数据的执行原理：

• 在RR隔离级别下，仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView
  

6.原理

6）总结

1）逻辑存储结构

• 一个区的大小是1M
• 一个页的大小是16K
• 一个区中包含连续的64个页
• InnoDB中申请空间时一次性会分配4~5个区，从而保证申请的页是连续的

2）架构

• 1.内存结构：
  • 缓冲区：一个数据库会把80%的内存都分配给缓冲区
• 2.磁盘结构：

3）事务的原理

• 原子性：undo log
  • 事务中的操作要么同时成功要么同时失败
  • 事务如果执行失败了就需要将事务的操作全部回滚 – undo log
• 持久性：redo log
  • 事务一旦提交，对数据的更新就是持久的
  • 也就是事务提交之后需要保证数据一定更新成功 – redo log
• 隔离性：锁 + MVCC (多版本并发控制）
  • 事务不受外界并发操作的影响
  • 在事务更新修改数据时，不能与其他事务同时修改同一个记录，避免数据错乱 — 锁（写互斥，防幻读）
  • 在前后两次读取数据时，需要知道读取的数据的版本 – MVCC（保证读一致性）
• 一致性：undo log + redo log
  • 事务完成之后，要使所有数据保持一致状态
  • 事务如果提交成功就需要保证数据一定更新成功 – redo log
  • 事务如果提交失败就回滚，保证数据在事务执行前后一致 – undo log

4）MVCC

• 1.隐藏字段：

  • db_trx_id
  • db_roll_prt
  • db_row_id

• 2.undo log 版本链：

• 3.ReadView：

  • m_idx:
  • min_trx_id:
  • max_trx_id:
  • creator_trx_id:

7.MySQL管理

1）系统数据库

• 1.mysql:
  • 存储MySQL服务器正常运行所需要的各种信息（时区、主从、用户、权限等）
• 2.information_schema：提供了访问数据库元数据的各种表和视图，包含数据库、表、字段类型及访问权限
• 3.performance_schema：为MySQL服务器运行时状态提供了一个底层监控功能，主要用于收集数据库服务器性能参数
  *4.sys：包含了一系列方便DBA和开发人员利用performance_schema性能数据库进行性能调优和诊断的视图

2）常用工具

• 1.mysql
  
• 2.mysqlbinlog:
  
• 3.mysqlshow:
  
• 4.mysqldump:备份数据库以及不同数据库之间的数据迁移
  
• 5.mysqlimport/source:导入备份的数据

---

*注：上述内容来自B站黑马程序员的视频学习，仅用作学习交流，不作为商业用途，如有侵权，联系删除。