MySQL高级

¥奔跑的蜗牛

已于 2022-01-23 10:17:49 修改

阅读量655

点赞数

文章标签： mysql sql 数据库

于 2022-01-20 10:11:19 首次发布

本文链接：https://blog.csdn.net/m0_53680228/article/details/122595594

版权

MySQL高级

视图

视图就是将一个较为复杂的查询语句包装在视图中,简化语句,视图被存储在数据库中,可以重复使用.

视图中并不存储数据

-- 创建视图
CREATE VIEW sel_news AS 
	SELECT
	  n.id,
	  n.title,
	  t.name
	FROM
	  news n
	  LEFT JOIN TYPE t
	    ON n.typeid = t.id
-- 使用视图   
SELECT * FROM sel_news;  

-- 删除视图
DROP VIEW sel_news;

存储过程

对数据库的认知: 里面可以以表为单位存储数据, 使用sql语言操作数据.

数据库中也是可以向java语言一样有逻辑处理功能。

事先被编写好,存储在数据库中, 使用时直接调用即可.

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

缺点: 对数据库依赖程度较高,移植性差.

-- 存储过程
-- 基本语法
DELIMITER $$
CREATE   PROCEDURE  demo1()
  -- 存储过程体
  BEGIN
     -- DECLARE 用来声明变量的
     DECLARE v_name VARCHAR(10) DEFAULT '';      
     
     SET v_name = 'jim'; 
     
     SELECT v_name; -- 测试输出语句
  END$$
DELIMITER ;

-- 调用存储过程
CALL demo1();



-- 查询新闻类型为1的有几条  演示入参和出参
DELIMITER $$
CREATE   PROCEDURE  type_count(IN p_typeid INT,OUT p_count INT)
  -- 存储过程体
  BEGIN
       SELECT COUNT(*) INTO p_count FROM news WHERE typeid = p_typeid;     
       SELECT p_count;
  END$$
DELIMITER ;


-- 在一个存储过程中调用另一个存储,
CALL type_count(1,@p_count);








--  演示存储过程中逻辑判断
DELIMITER $$
CREATE   PROCEDURE  demo2(IN p_day INT,OUT p_name VARCHAR(10))
 
  BEGIN
         IF p_day = 1  THEN 
           SET p_name  = "星期一";
            SELECT p_name;     
         ELSEIF p_day = 2 THEN 
           SET p_name  = "星期二";
            SELECT p_name;
         ELSE   
           SET p_name = "无效日期";
           SELECT  p_name;  
       
         END IF; 
  END$$
DELIMITER ;

CALL demo2(1,@p_name);


-- 存储过程保存管理员,在存储过程中判断账号是否已经存储,不存在存储,否则返回账号已存在
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('t08','111',@p_result);

java代码和SQL组合，在SQL中写查询

DELIMITER $$

CREATE 
PROCEDURE save_user(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 USER WHERE account =p_account;
             IF v_count=0 THEN
             INSERT INTO USER(account,PASSWORD)VALUES(p_account,p_password);
             SET p_result="保存成功";    
             ELSE
             SET p_result="账号已存在";
             SELECT p_result;
             END IF;
	END$$

DELIMITER ;

CALL save_user('t07','111',@p_result);

public class TestAdmin {

    @Test
    public void find() {
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        AdminMapper mapper = sqlSession.getMapper(AdminMapper.class);

        HashMap<String, Object> map = new HashMap<>();
        map.put("account", "t01");
        map.put("password", 111);
        mapper.saveadmin(map);
        System.out.println(map.get("result"));
        sqlSession.commit();
        sqlSession.close();
    }
}








 void saveadmin(Map<String,Object> map);









    <insert id="saveadmin" parameterMap="adminmapp" statementType="CALLABLE">
{call save_user(?,?,?)}
    </insert>

函数

函数类似于存储过程,但是函数主要用于查询

-- 全局设置,设置函数可以没有参数
SET GLOBAL log_bin_trust_function_creators=TRUE;


-- 函数
DELIMITER $$
CREATE  FUNCTION  demo()  RETURNS INT
   
    BEGIN
       DECLARE v_count INT DEFAULT 0;
       SELECT COUNT(*) INTO v_count FROM admin;
       RETURN v_count;      
    END$$

DELIMITER ;

 
SELECT demo();


-- 


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 account,id,findType(TYPE) FROM admin



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 TYPE WHERE  id = p_typeid;
       RETURN v_type;      
    END$$

DELIMITER ;

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

触发器

类似于存储过程,函数, 与表相关, 有点向事件

对表新增,修改,删除之前或之后自动触发

触发器具有以下特点：

1.与表相关联

触发器定义在特定的表上，这个表称为触发器表。

2.自动激活触发器

当对表中的数据执行 INSERT、UPDATE 或 DELETE 操作时，如果对表上的这

个特定操作定义了触发器，该触发器自动执行，这是不可撤销的。

3.不能直接调用

与存储过程不同，触发器不能被直接调用，也不能传递或接受参数。

4.作为事务的一部分

触发器与激活触发器的语句一起做为对一个单一的事务来对待，可以从触发器中

的任何位置回滚。

– 触发器
DELIMITER $$

CREATE
TRIGGER delete_admin_role
BEFORE
DELETE
ON admin
FOR EACH ROW – 行级触发器操作多行时,每行都会触发触发器
BEGIN
DELETE FROM admin_role WHERE adminid = old.id;
END$$

DELIMITER ;

DELETE FROM admin WHERE id = 2

mysql架构

在这里插入图片描述

连接层

负责与客户端和程序建立连接,认证…

最上层是一些客户端和连接服务，包含本地 sock 通信和大多数基于客户端服务端工具实现的类似于 tcp/ip 的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。

服务层

第二层架构主要完成大多数的核心服务功能，如 SQL 接口，并完成缓存的查询，SQL 的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化,如确定查询表的顺序，是否利用素引等，最后生成相应的执行操作。如果是 select 语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

sql接口

解析器

查询优化器

缓存

引擎层

负责与数据文件系统连接,读,写数据

存储引擎层，存储引擎真正的负责了 MysQL 中数据的存储和提取，服务器通过 API 与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。

物理文件层

负责存储表数据,日志文件(非常重要 mysql事务实现就是依赖于日志的)

DBA database admin 数据库管理员

Mysql存储引擎

引擎是什么

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

每张表都有对应的引擎来进行处理,

mysql有哪些常用引擎

查看支持的引擎

SHOW ENGINES;

查看表引擎

SHOW TABLE STATUS LIKE ‘表名’

修改引擎

方式 1:将 mysql.ini 中 default-storage-engine=InnoDB，重启服务.

方式 2:建表时指定 CREATE TABLE 表名(…)ENGINE=MYISAM;

方式 3:建表后修改 ALTER TABLE 表名 ENGINE = INNODB;

存储引擎主要有： 1. MyIsam , 2. InnoDB, 3. Memory, 4. Blackhole, 5. CSV,

Performance_Schema, 7. Archive, 8. Federated , 9 Mrg_Myisam

我们主要分析使用 MyIsam 和 InnoDB。

重点学习

MyiSam 不支持事务查询多

不支持事务,不支持外键,不支持行锁,支持表锁,支持全文索引,存储表的总行数
select count(*) from admin 直接获取到总行数即可快

Innodb 支持事务增删改多

支持事务,支持外键,表锁,行锁,支持缓存,支持全文索引,支持主键自增,适合于处理增,删,改比较多的场景. 不存储总行数.

select count(*) from admin 自行统计计算慢

索引

数组索引可以通过索引快速的找到某个位置的数据.

为什么要有索引呢？

不使用索引的话,查询从第一行开始,逐行向后查询,直到查询到我们需要的数据.

如果数据量非常大的情况下,查询效率比较低.

什么索引

索引是帮助 MySQL 高效获取数据的数据结构。

排好序的快速查找的数据结构.

在这里插入图片描述

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址.

为了加快 Col2 的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

在一个数据结构中将数据维护者,方便查找

索引原理

索引就类似书的目录,通过目录快速的查询到我们需要的数据. 缩小查询范围

在这里插入图片描述

索引优势

提高数据检索的效率,降低数据库的 IO 成本;

通过索引列对数据进行排序,降低数据排序的成本,降低了 CPU 的消耗;

索引劣势

索引也是需要占用硬盘空间

对表进行新增,修改,删除操作时,在操作数据的同时,也需要对索引信息作出修改

索引创建的原则

索引虽好,但不要乱用

什么情况下需要索引

主键自动建立唯一索引主键索引

作为查询条件的列适合创建索引

外键建议建立索引

排序,分组字段适合添加索引

什么情况下不建议使用索引

表记录太少
新增,修改,删除频繁的表分表将本来一张表,拆分读写分离

不是查询条件

数据重复且分布均匀的例如: 性别

索引分类

主键索引：

设定为主键后数据库会自动建立索引不能为空,唯一的一个表只有一个主键.

ALTER TABLE 表名 add PRIMARY KEY 表名(列名);

删除建主键索引：

ALTER TABLE 表名 drop PRIMARY KEY ;

单值索引：

一个索引包含一个列, 一个表可以有多个单值索引.

name account

创建单值索引

CREATE INDEX 索引名 ON 表名(列名);

删除索引：

DROP INDEX 索引名;

唯一索引:

数据不能重复，允许为 null

CREATE UNIQUE INDEX 索引名 ON 表名(列名);

删除索引

DROP INDEX 索引名 ON 表名;

组合索引(复合索引)：

即一个索引包含多个列

组合索引最左前缀原则:

使用组合索引时,要出现最左列,否则索引失效

例如表中有 a,b,c 3 列,为 a,b 两列创建组合索引

例如 select * from table where a=’’and b=’’索引生效

select * from table where b=’’and a=’’索引生效

select * from table where a=’’and c=’’索引生效

select * from table where b=’’and c=’’索引不生效

使用模糊查询 name like %张%; 这样写 ,回导致name列的索引失效 like的模糊查询不建议使用

mysql8中建议使用全文索引

全文索引

– 全文索引
CREATE FULLTEXT INDEX news_title ON news(title) WITH PARSER ngram;
SELECT * FROM news WHERE MATCH(title) AGAINST(‘小康’)

CREATE FULLTEXT INDEX 索引名 ON 表名(字段名) WITH PARSER ngram;

SELECT 结构 FROM 表名 WHERE MATCH(列名) AGAINST(搜索词’)

查看索引：

SHOW INDEX FROM 表名;

索引数据结构

mysql Innodb引擎默认使用 B+树作为数据结构存储索引.
排好序的,一个节点可以存储多个数据. 横向扩展, 使得树的高度降低了.

非叶子节点不存储数据,只存储索引,可以放更多的索引.

数据记录都存放在叶子节点中. 找到了索引,也就找了数据.

所有叶子节点之间都有一个链指针,非常适合区间查询 age>20 age<50

聚簇索引和非聚簇索引

聚簇索引: 找到了索引,就找到了数据就是聚簇索引.

主键可以直接找到数据

根据学号只查询学号可以直接命中学号此种场景学号就是聚簇的

非聚簇索引 : 找到了索引但没有找到数据, 需要根据主键再次回表查询

根据学号只查询学号,姓名虽然学号加了索引,但是还需要查询姓名,

需要根据学号,找到主键,通过主键回表查询此种场景就是非聚簇的

SELECT NO FROM student WHERE NO = 123

在这里插入图片描述

MySQL 中 InnoDB 引擎的索引和文件是存放在一起的,找到索引就可以找到数据,是聚簇式设计

在这里插入图片描述

而 MyISAM 引擎采用的是非聚簇式设计,索引文件和数据文件不在同一个文件中

在这里插入图片描述

事务

事务就是一次完整的数据库操作,这个操作过程中可能包含多条sql的执行. 这多条sql的自执行时是一个整体.
要么都执行成功,要么都执行失败.

举例转账操作从A账号向B账号转钱

A-100

B+100

网购下单支付

下订单到卖家

支付到平台

Mysql中只有Innodb引擎支持数据库事务.

事务用来管理 insert,update,delete 语句.

关系型数据事务4大特性:

原子性: 一次事务过程中的多个操作要么都成功,要么都失败.

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

隔离性: 数据库是允许同时有多个事务进行访问, 这时就需要对多个事务间的操作进行隔离,

隔离分为4个级别:

读未提交问题脏读

读已提交解决脏读不可重复读

可重复读解决不可重读幻读

串行化解决一切问题加锁效率低

**一致性: **在事务开始之前和事务结束以后，数据库的完整性没有被破坏.

例如: 我们多种方式对银行账户的余额进行多次同时操作,最终余额应该是我们所预期的结果,不能出现错误.

事务设置

默认情况下, MySQL 启用自动提交模式（变量 autocommit 为 ON）。这

意味着, 只要你执行 DML 操作的语句,MySQL 会立即隐式提交事务。

由于变量 autocommit 分会话系统变量与全局系统变量，所以查询的时候，

最好区别是会话系统变量还是全局系统变量。

MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT 来实现

BEGIN; / START TRANSACTION; 开始一个事务

ROLLBACK 事务回滚

COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

SET SESSION / GLOBAL autocommit=0; 禁止自动提交

SET SESSION / GLOBAL autocommit=1;开启自动提交

查看 autocommit 模式

SHOW SESSION / GLOBAL VARIABLES LIKE ‘autocommit’;

mysql并发操作的问题:

1.脏读读到了垃圾数据 A事务读到B事务未提交的数据

2.不可重复读 A事务开启后读取两次数据,结果两次读到的内容不一样( 预期的效果是A在同一个事务中读取到数据应该是一样)

3.幻读 A事务开启后, 读取到的两次数据数量不一致

事务隔离级别:

读未提交: A 可以读到B未提交的数据问题: 会有脏读几乎不用

在这里插入图片描述

读已提交: A不能读到B为提交的数据,只能读到B已提交的数据. 解决了脏读问题, 同时会发生不可重复读问题

在这里插入图片描述

可重复读: A事务开启后,第一次读到某个数据后,那么在这个事务中,第二次再查询同样的数据时,和原来是一致,重复读. 解决了不可重复问题

在这里插入图片描述

串行化: 解决所有问题, 一次只允许一个事务进行操作是最安全的,但是效率是最低的.

MVCC

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

为了提升mysql 读-写,写-读两个操作同时进行, 写-写mysql支持行级锁的,如果操作同一行数据,那么肯定是不可以的.

版本链

对于使用 InnoDB 存储引擎的表来说，它的聚簇索引记录中都包含两个必要的

隐藏列。

**trx_id：**每次对某条聚簇索引记录进行改动时，都会把对应的事务 id 赋

值给 trx_id 隐藏列。

**roll_pointer：**每次对某条聚簇索引记录进行改动时，都会把旧的版本写

入到 undo 日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记

录修改前的信息。

每次对表中的记录操作时,会保存一个日志(undolog) 里面会记录事务的id号.

如果有多个事务操作时,他们就会根据事务id,找到自己操作的版本记录.

不同的隔离级别在读数据时, 会根据版本链生成一个ReadView(临时读视图) 版本链快照

READ COMMITTED：每次读取数据前都生成一个 ReadView 产生不可重复读其中数据发生改变,版本链中也会发生修改, 每次读的时候ReadView中的数据就发生改变,所有不可重复读.

REPEATABLE READ：在第一次读取数据时生成一个 ReadView, 之后数据发生改变,版本链发生变化,没有关系,第一次读的时候,已经拍过照了.

mysql锁

mysql中的锁,主要是用于对写写操作.

mysql中支持行锁,间隙锁,表锁

行锁: 某个事务对某行记录进行写操作时,会把当前行锁住, 其他事务不能对当前行操作.

粒度最小,并发最高的, 频繁加锁,释放锁.

间隙锁: 在条件范围操作时,会给满足条件的区间数据行加锁

表锁: 当某个事务对某行记录操作时,可以将整个表锁住. innodb用的少, myisam只支持表锁.

**共享锁（S）：**又称读锁.

**排他锁（Ｘ）：**又称写锁。

在查询时,必要的情况下, 也可以为读操作加排他锁 select … * from 表 for update 语句

1.乐观锁：并不会真正的去锁某行记录，而是通过一个版本号来实现的.

没有加锁,可以通过行版本号来区分

2.悲观锁：上面的行锁,间隙锁,表锁等都是悲观锁.

数据库优化

牵扯表的设计,库的设计不考虑电商级别的分库分表读写分离集群

考虑范围: 管理系统级别的表设计遵循三范式第1范式第2范式第3范式

Sql 优化

正确使用sql

1.正确使用索引查询条件列,排序列添加索引项目中是如何使用的查询条件

2.应改避免索引失效 name like “%张%” 改为全文索引

在 where 子句中避免 num is null

在 where 子句中使用!=或<>操作符

避免在 where 子句中使用 or 来连接条件

在where num/2=100 使用运算符

在where中使用函数 substring()

3.mysql建议使用主键自增合理利用索引结构

4.索引不宜建立太多一般一张表6个左右可以考虑组合索引最左前缀原则

状态,类型…一般建议使用数字类型 int

varchar(变长 6 最大存储6个字符) 代替 char (定长 4 只有两个字的时候,也占4个字符)
1. 不建议使用 select * -->查询哪些列
7.一次性不要查询数据过多分页查询降低每次查询数据量不要过多查询条件

8.避免字段值为null null是占空弄间的可以给默认值’’

¥奔跑的蜗牛

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL高级

MySSQL高级视图视图就是将一个较为复杂的查询语句包装在视图中,简化语句,视图被存储在数据库中,可以重复使用.视图中并不存储数据-- 创建视图CREATE VIEW sel_news AS SELECT n.id, n.title, t.name FROM news n LEFT JOIN TYPE t ON n.typeid = t.id-- 使用视图 SELECT * FROM sel_news; -- 删除视图DROP VIEW
复制链接

扫一扫