MySQL核心技术-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_52163230/article/details/122656325

视图

视图就是一条 SELECT 语句执行后返回的结果集，将查询语句包装起来

-- 创建视图
CREATE VIEW sel_news AS
	SELECT n.id,n.title,nt.name
	FROM news n 
	LEFT JOIN newstype nt ON n.type=nt.id

-- 使用视图
SELECT * FROM sel_news

-- 删除视图
DROP VIEW sel_news

存储过程

数据库中也可以和java一样有逻辑处理功能，就可以将逻辑事先编辑好存在数据库中，使用时直接调用，减少数据在数据库和应用服务器之间的传输，提高数据处理的效率

在这里插入图片描述

优点：处理某个逻辑的过程直接存储在数据库中，运行速度较快

缺点：对数据库依赖程度较高，可移植性差

-- 存储过程保存管理员,在存储过程中判断账号是否已经存储,不存在存储,否则返回账号已存在
DELIMITER $$
CREATE   PROCEDURE  save_admin(IN p_account VARCHAR(10),IN p_password VARCHAR(10),OUT p_result VARCHAR(10))
  BEGIN
       -- 声明一个变量,接收查询到的结果
       DECLARE v_count INT DEFAULT 0;
       
       SELECT COUNT(*) INTO v_count FROM admin WHERE account = p_account;
          IF v_count = 0 THEN
             INSERT INTO admin(account,PASSWORD)VALUES(p_account,p_password);
             SET p_result = "保存成功";
          ELSE 
             SET p_result = "账号已存在";
             SELECT p_result;   
          END IF; 
  END$$
DELIMITER ;

CALL save_admin('admin','111',@p_result);

mybatis中使用存储过程

指定parameterMap，指定输入输出参数

<parameterMap type="map" id=“usermap"> 
    <parameter property="account" jdbcType="VARCHAR" mode="IN"/>
    <parameter property="password" jdbcType="VARCHAR" mode="IN"/>             
    <parameter property="result" jdbcType="VARCHAR" mode="OUT"/> 
</parameterMap>
                                                              
<insert id="saveAdmin" parameterMap="usermap" statementType="CALLABLE"> 		{call saveuser(?, ? ,?)}
</insert >

Map<String, Object> parms = new HashMap<String, Object>(); 						parms.put("account","jim"); 
	parms.put("password","000"); 
	userDao.saveAdmin(parms); 
	String result = parms.get(“result”);//获得输出参数

函数

类似存储过程，主要用于查询

-- 创建函数，转换管理员类型列
DELIMITER $$
CREATE  FUNCTION  findType(p_type INT)  RETURNS VARCHAR(10)
    BEGIN
       DECLARE v_type VARCHAR(10) DEFAULT '';
       IF p_type = 0 THEN
          SET v_type = '超级管理员';
       ELSE
          SET v_type = '管理员';   
       END IF;
       RETURN v_type;      
    END$$
DELIMITER ;
-- 使用函数
SELECT id,account,findType(TYPE)TYPE FROM admin

-- 创建函数 通过id查类型名称
DELIMITER $$
CREATE  FUNCTION  find_news_type(p_typeid INT)  RETURNS VARCHAR(10)
    BEGIN
       DECLARE v_type VARCHAR(10) DEFAULT '';
        SELECT NAME INTO v_type FROM newstype WHERE  id = p_typeid;
       RETURN v_type;      
    END$$
DELIMITER ;

SELECT id,title,find_news_type(TYPE) TYPE FROM news;

触发器

类似存储过程，函数，与表相关，像事件

对表新增，修改，删除前后自动触发

-- 触发器  删除admin前删除admin_role关系
DELIMITER $$
CREATE
    TRIGGER delete_admin_role 
    BEFORE 
    DELETE
    ON admin
    FOR EACH ROW -- 行级触发器  操作多行时,每行都会触发触发器
    BEGIN
           DELETE FROM admin_role WHERE admin_id = old.id;
    END$$
DELIMITER ;

DELETE FROM admin WHERE id = 46

-- 触发器  添加admin时为admin_log生成一条记录
DELIMITER $$
CREATE
    TRIGGER insert_admin_log
    AFTER
    INSERT
    ON admin
    FOR EACH ROW
    BEGIN
           INSERT INTO admin_log(id,account,oper_time)VALUES(new.id,new.account,NOW());
    END$$
DELIMITER ;

INSERT INTO admin(account)VALUES('admin888')

数据库三范式

第一范式
每一列属性都是不可再分的属性值，确保每一列的原子性.
比如姓名、年龄列，年龄必然满足第一范式，但是姓名可以拆分成姓和名，如果业务中不需要拆分，则满足第一范式，否则不满足
第二范式
在第一范式的基础上，非主键列完全依赖于主键
第三范式
在第二范式的基础上，确保每列都和主键列直接相关，而不是间接相关，列和列之间不存在相互依赖关系，即限制列的冗余性

MySQL架构

在这里插入图片描述

连接层：负责与客户端和程序建立连接，认证
服务层：SQl 接口（分类读写操作）、解析器（解析关键字）、查询优化器（mysql自身自动优化）、缓存
引擎：负责与数据文件系统连接，读写数据（不同环境使用不同引擎）
物理文件层 ：负责存储数据表，日志文件（mysql事务依赖于日志）

分层好处：可以准确定位问题，数据存储提取分离，插件式的存储引擎架构，可以根据实际需求选择优化

MySQL 引擎

引擎是数据库中具体与文件交互的技术，不同引擎的实现方式是有区别的

INnodb

默认的存储引擎
索引，数据不分文件存储
不存储表数据的总行数（count()就需要遍历计算）
支持事务、外键、表锁、行锁
可以并发读写（MVCC乐观锁实现）并发量大

适用于写多的场景；事务场景

MyiSam

索引，数据分文件存储；索引都是非聚簇索引
存储表的总行数（统计O(1)）
不支持事务，外键，行锁；只支持表锁，有全文索引，高并发写效率低

适合读多场景
在这里插入图片描述

Memory

数据存放在内存中，表结构放在磁盘中

有利于数据快速处理，提高整个表的效率

默认使用Hash索引

没有持久化，关机就没了，所以存储生命周期短的数据

-- 查看支持的引擎 
SHOW ENGINES; 
-- 查看表引擎 
SHOW TABLE STATUS LIKE 'admin'

索引

索引是帮助 MySQL 高效获取数据的数据结构，排好序的快速查找的数据结构

使用一种具有高级查找算法的数据结构并指向数据，方便查找和排序

如果不使用索引的话，查询（where id=100）从第一行开始，逐行向后查询，直到查询到我们需要的数据，如果数据量非常大的情况下效率就很低

索引类似于书的目录，先定位到章，然后定位到该章下的一个小节，然后找到页数，本质上都是通过不断地缩小查询范围
在这里插入图片描述

索引的优势劣势

优势

提高数据检索的效率，降低数据库的 IO 成本
通过索引列对数据进行排序，降低数据排序的成本，降低了 CPU 的消耗

劣势

也占用磁盘
降低更新表（增删改）的速度

创建索引的原则

创建

主键自动建立唯一索引
外键建议索引
频繁作为查询条件的字段应该创建索引(where 后面的语句)
查询中作为排序、统计、分组条件的字段

不创建

表记录太少
经常增删改的表
数据重复且分布均匀的字段（性别）

创建语法
在这里插入图片描述

索引的分类

站在数据结构、物理存储、逻辑三个角度

数据结构角度

B+树

InnoDB默认使用B+ 树作为数据结构存储索引

在大量数据存储中，不能将所有的数据都加载到内存，只能逐部加载节点，会发生磁盘IO，为了减少IO，使用平衡多路查找树，让每个节点承载更多的元素，用于更多的孩子，降低树高度；
在这里插入图片描述

排好序的，一个节点可以存储多个数据. 横向扩展, 使得树的高度降低了.
非叶子节点不存储数据，只存储索引，可以放更多的索引.
数据记录都存放在叶子节点中，找到了索引，也就找了数据.
所有叶子节点之间都有一个链指针，非常适合区间查询 age>20 age<50

Hash索引

Memory引擎支持；且只有当where条件中是 = 或 in 时才生效，不能进行范围查找

检索效率非常高，时间复杂度O1，一次就检索到位

FullText

InnoDB和MyISAM都支持

倒排索引。。参考ElasticSerach 倒排索引

R-Tree索引

空间数据索引见空间数据索引RTree（R树）完全解析及Java实现

物理存储角度

聚簇索引

找到了索引，就找到了数据

比如根据学号只查询学号可以直接命中学号此种场景学号就是聚簇的
在这里插入图片描述

非聚簇索引

找到了索引但没有找到数据，需要根据主键再次回表查询

根据学号只查询学号,姓名；虽然学号加了索引，但是还需要查询姓名，需要根据学号找到主键，通过主键回表查询
在这里插入图片描述
而 MyISAM 引擎采用的是非聚簇式设计，即使是主键索引，因为索引是单独维护在一个文件中，而InnoDB主键索引连着数据

逻辑角度

主键索引：设置为主键后会自动建立索引，不能为空，一个表只能有一个主键
单值索引：一个索引只包含单个列，一个表可以有多个单值索引
唯一索引：索引列数据不能重复，允许为null
组合索引：一个索引中包含多个列，比单值索引开销更小(对于相同的多个列建索引)，列数远大于行数使用。。。组合索引最左前缀原则：列如表中有 a,b,c 3 列，为 a,b 两列创建组合索引，只有在最左侧索引列出现在查询条件中才会生效（a）；
全文索引（FULLTEXT INDEX）：模糊查询时，一般索引无效，使用全文索引

事务

事务就是一次完整的数据库操作，这个操作中的多条sql 执行是一个整体，要么都成功或不成功

MySQL只有InnoDB支持事务，事务用来管理增删改语句

例如转账操作，从A账号向B账号赚钱，数据库就需要分两步操作，这两个操作就不可分

事务特性

事务是必须满足 4 个条件（ACID）：原子性（Atomicity，或称不可分割性）、持久性（Durability）、隔离性（Isolation，又称独立性）、一致性（Consistency）

原子性：一次事务中的多个操作要么都成功，要么都失败

持久性：事务一旦提交，数据就不可改变，即使数据库服务出现问题

隔离性：数据库允许有多个事务进行访问，这时就需要对多个事物间的操作进行隔离，四个级别

读未提交问题脏读
读已提交解决脏读问题不可重复读
可重复度解决不可重读问题幻读
串行化解决一切问题加锁效率低

一致性：在事务开始前和事务结束后，数据库完整性没有被破坏

事务设置

-- 设置Mysql事务的提交方式为手动提交
-- SET SESSION (会话)/ GLOBAL(全局) autocommit=0; 禁止自动提交

  SET  GLOBAL autocommit=0; 
  
 --  SHOW   GLOBAL VARIABLES LIKE 'autocommit'; 查看事务提交模式
 SHOW   GLOBAL VARIABLES LIKE 'autocommit';
 
  BEGIN;
 
  INSERT INTO test(NAME)VALUES("aaaa");
  -- 回滚
  ROLLBACK;
  
  SELECT * FROM test;
  -- 提交
  COMMIT;

并发事务问题

脏读

A事务读到了B事务未提交的数据

事务 B 更新年龄 18
事务 A 读取数据库信息，年龄是 18
事务 B 回滚

在这里插入图片描述

不可重复读

在事务 A 中先后两次读取同一个数据，B事务在期间修改了数据并提交，A两次读取的结果不一样，预期是一样的
在这里插入图片描述

幻读

在事务 A 中先后两次读取同一个数据，B事务在期间添加了数据行数并提交，A两次读取的行数不同，一般幻读出现在范围查询
在这里插入图片描述

事务的隔离级别

只有 InnoDB 支持事务，所以这里说的事务隔离级别是指 InnoDB 下的事务隔离级别

-- 查看隔离级别
SELECT @@global.transaction_isolation,@@transaction_isolation;
-- 设置隔离级别
SET SESSION/GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

读未提交

(read uncommitted)：一个事务可以读取到另一个事务未提交的修改。

这会带来脏读，幻读，不可重复读问题

读已提交

(read committed)：一个事务只能读取另一个事务已经提交的修改。

其避免了脏读，仍然存在不可以重复读和幻读问题

可重复读

(repeatable read MySQL 默认隔离级别)：同一个事务中多次读取相同的数据返回的结果是一样的。

其避免了不可重复读问题，如果是快照读也解决了幻读问题，如果是当前读就需要使用Next-key锁解决

串行化

(serializable)：事务串行执行，避免了以上所有问题，类似加锁效率低
在这里插入图片描述

MVCC

多版本并发控制 Multi-Version Concurrent Control

实现了对数据库的并发访问，也就是为了让MySQL 读-写写-读两个操作同时进行，写-写MySQL支持行级锁，提高数据库并发性能，用不加锁的方式处理读写冲突，是乐观锁的一种实现方式

mysql两种读

当前读：读的是最新版本的数据，读取时还要保证其他并发事务不能修改当前数据，会对读的记录加锁，像select for update、update、insert、delete这些操作
快照读：不加锁的非阻塞读，像不加锁的select读，前提是隔离级别不是串行化；为了提高并发性能，基于MVCC，快照读不一定读的是最新的数据，避免加锁操作，提高并发效率

版本链

对于InnoDB，聚簇索引记录中会包含两个隐藏列

trx_id：每次对记录改动时，都会把对应的事务id赋给此列
roll_pointer：每次对记录改动时，都会把旧的版本写入到undo日志中，这个列相当于一个指针，可以通过它找到该记录修改前的信息

每次对表中的记录操作时,会保存一个日志(undo log) 里面会记录事务的id号.
如果有多个事务操作时，他们就会根据事务id,找到自己操作的版本记录.

在这里插入图片描述
事务开始时会生成一个readview，readview记录了活跃的事务Id，也就是未提交的事务。访问数据时，获取该数据中事务id与readview中事务id比较，如果该行数据事务id比readview中的事务id都小，证明该数据中保存的是已经提交过的事务，可以放心读。如果在readview中或者更大，那就是这行数据还没有被提交，就不能读这个版本的数据，需要通过roll_pointer去获取上一个版本的数据，然后再次与readview中id比较，直到比他们都小，这时就找到了已提交的最新数据，就将这行数据读取。

读已提交：每次读取数据前都生成一个 ReadView ，会产生不可重复读，原因是A事务先后两次读某行数据都会生成readview。这时，如果有其他事务把这行数据修改并提交，A事务第二次读会生成新的readview，就相当于对该数据哪个版本是最新且提交了的数据的评判标准改变，就会导致两次读的数据不一致
可重复读：只会在第一次读取数据时生成一个 ReadView，之后数据发生改变，版本链虽然发生变化，但readview不变，只后每次读都会读第一次读的那个readview，这样一个事务先后两次读出的数据都是一致的。。。同时也解决了幻读问题，只有一个ReadView，新添加到这个区间数据的trx_id一定比ReadView中的都大，所有就不会读到。

MVCC可重复读什么情况会出现幻读
在这里插入图片描述

A事务先读，B事务插入了一条数据
A事务再读，读的数据和第一次一样（MVCC）
A事务中对新添加的数据进行修改（update操作读到的一定是最新的数据），就会把该行数据的trx_id由B事务的改成A事务的
A事再读，这次就会读到新添加的数据，就会出现幻读

当前读解决的幻读

MVCC使用的是快照读，读的都不是最新的数据，可以解决幻读；但如果我需要当前读，读的每次都是最新的数据，就会产生幻读，就需要使用 next-key锁避免幻读

next-key：行锁+间隙锁

每次读的时候不光锁要用的这一行数据，还锁他的左开右闭这个区间的数据；例如这行有1、3、5这几个值，当用next-key锁锁住1的时候，就会同时锁住（负无穷，1]，如果锁3的话，就会锁住（1,3]。锁住这个区间其他事务就不会插入，行数也不会改变。

长事务优化

让事务越短越好

查询操作放在事务外面
需要获取锁的操作放在后面（update放在insert后边）
能异步操作尽量异步
在应用层（代码）手动保证事务一致性，提高效率

锁机制

MySQL中的锁主要针对写写操作
在这里插入图片描述
优质文章：【mysql】MySQL中的锁原理（表锁、行锁、间隙锁、共享锁、排他锁）

行锁

某个事物对某行记录操作时，会把当前行锁住，其他事务就不能对当前行操作。

粒度最小，并发是最高的，频繁加锁释放锁

实现原理

InnoDB行锁是通过给索引项加锁来实现的
通过索引进行数据检索，才会用到行级锁，否则为表锁

表锁

当某个事物对某个表操作时，会把当前表锁住

开销小，加锁快，粒度大，并发度最低，锁冲突概率高

间隙锁

在条件范围操作时，会给满足条件的区间数据加锁

危害：间隙锁会锁定整个范围内所有的索引键值，即使这个键值并不存在，某些不存在的键值也会被无辜的锁定，而对于这些被无辜锁定的键值我们也不能对它做任何操作

共享锁 / 排他锁

共享锁：读锁

排他锁：写锁

在查询时，必要情况下，也可以为读操作加排他锁 select … from admin for update

乐观锁 / 悲观锁

乐观锁：就是没有加锁，通过版本号区分

悲观锁：加锁（行锁，表锁，间隙锁）

Mysql日志

undo log（回滚日志）：主要用于事务的回滚，记录反向操作的SQL；事务回滚、MVCC
redo log（重写日志）：对数据库操作先在内存中操作，然后刷入磁盘。保证程序宕机后在内存中操作完成的数据能刷入磁盘（持久性的实现）
bin log（二进制日志）对数据操作就会写入。主要用于主从复制
relay log（中继日志）主从复制中从机读取主机bin log后生成的日志
error log（错误日志）
slow query log（慢查询日志）
general log（一般查询日志）

主从集群

使用多台机器，分主机和从机，读写分离，主机负责写和读，从机只负责读。

从机可以负载主机读的压力，可以提高读写的效率；同时还有数据备份、提高可用性的作用。

主机写入数据后会通过主从复制同步给从机。

主从复制

主机方面

当对主机进行写操作时，会按照顺序写入到 bin log二进制日志中，当从机连接到主机时，主机会为从机开启 bin log dump线程，当主机的 bin log发生变化时， bin log dump线程会通知从机，将相应的 bin log内容发送给从机。

从机方面

在开启主从同步后，从机会创建两个线程：一个是I/O线程，该线程连接主机，用来接收主机上的 bin log dump线程发送的 bin log内容，接收后将内容写入到 relay log中继日志当中；另一个是 sql线程根据 relay log内容来对数据库从机进行操作。

mysql的复制是异步的且串行化的
在这里插入图片描述

主从同步延迟

在整个主从同步的过程中，主库写bin log是顺序的、从机的IO操作再写入relay log也是单线程，不会出现锁的竞争，所以很快，但是到了 sql线程读取relay log然后在从机执行这个步骤，是随机的，就会和其他写操作产生锁的竞争，这是延迟的主要因素。虽然这个情况主机也存在，但是主机可以支持并发读写，而从机的 sql线程不能，当并发过高或者一个sql过大时，就会产生过久延迟；

解决方案

分库分表，减轻主机压力；同时便于扩展；
使用分布式缓存降低数据库压力；
从机的硬件设备性能高于主机，可降低延迟；
提升硬件性能，服务器网络等；
sync_binlog在slave端设置为0，sync_binlog=[N]表示每写缓冲多次就同步到磁盘；这样牺牲一定的一致性，可以获得更高的并发和性能
直接禁用从机的bin log，减少延迟但是会降低效率；
半同步复制

异步复制：主库执行SQL – 写binlog – 主库返回执行成功，dump线程异步读bin log发给从库

半同步复制：主库执行SQL – 写入bin log，同时唤醒 dump 并发送 bin log – 从机读取并写入relay log再应答 – 主库返回执行成功；
在这里插入图片描述

数据库优化

SQL和索引

慢查询优化
由自带的慢查询日志或者开源的慢查询系统定位到具体的出问题的SQL，然后使用explain等工具来逐步调优，最后经过测试达到效果后上线

SQL优化

正确的使用索引（查询条件列、排序列）
避免索引失效
- 在where 子句中避免 where num is null
- 在 where 子句中使用!=或<>操作符
- 在 where 子句中使用 or 来连接条件
- 在where 使用运算符、函数（where num/2=100、 substring() ）
- 不满足最左前缀原则
不建议使用 select * -->查询哪些列…
一次性不要查询数据过多，可用分页查询，降低每次查询数据量

数据表结构

根据数据库的三范式设计表
反三范式：根据场景需要，例如分割表
建议使用主键自增合理利用索引结构
索引不宜建立太多，一般一张表6个左右可以考虑组合索引最左前缀原则
字段类型选择
- 尽力使用tinyint、smallint、varcahr
- 使用整数代替字符串（状态、类型列）
- 避免字段值为null null是占空弄间的可以给默认值 ’ ’