MySQL高级

目录

视图存储过程函数触发器MySQL架构MySQL 引擎索引

为什么使用数据库索引？什么是索引？索引的原理索引的优势劣势创建索引的原则索引的分类

主键索引单值索引唯一索引组合索引全文索引 索引数据结构聚簇索引和非聚簇索引 事务

事务特性事务设置并发事务问题

脏读不可重复读幻读 事务的隔离级别

读未提交读已提交可重复读串行化 MVCC锁机制

行锁表锁间隙锁共享锁 / 排他锁乐观锁 / 悲观锁 SQL优化

视图

视图就是一条 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，指定输入输出参数

 
    
                 
     

                                                              
 		{call saveuser(?, ? ,?)}

Map parms = new HashMap(); 						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;

触发器

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

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

特征

与表相关联：触发器定义在特定的表上，这个表称为触发器表自动激活触发器：当对表中的数据执行 INSERT、UPDATe 或 DELETE 操作时，如果对表上的这个特定操作定义了触发器，该触发器自动执行，这是不可撤销的。不能直接调用：与存储过程不同，触发器不能被直接调用，也不能传递或接受参数。作为事务的一部分：触发器与激活触发器的语句一起做为对一个单一的事务来对待，可以从触发器中的任何位置回滚

语法解析

触发器名称：是用来标识触发器的，由用户自定义触发时机：其值是 before 或 after触发事件：其值是 insert，update 和 delete表名称：标识建立触发器的表名，即在哪张表上建立触发器语句：是触发器程序体，触发器程序可以使用 begin 和 end 作为开始和结束，中间包含多条语句；

在行级触发器代码中，可以使用 old 和 new 访问到该行的旧数据和新数据，old和 new 是对应表的行记录类型变量

-- 触发器  删除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 引擎

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

INnodb：

默认的存储引擎

支持事务，外键，表锁，行锁，缓存，主键自增

不存储表的总行数（select count(*) from admin 需要自行统计计算，速度慢）

适用于处理增删改比较多的场景

MyiSam： （索引，数据，表结构 分文件）

支持表锁，全文索引，存储表的总行数

不支持事务，外键，行锁

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

索引 为什么使用数据库索引？

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

什么是索引？

索引类似于书的目录，通过目录快速查找到我们需要的数据

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

在一个数据结构中将数据维护着，方便查找

索引的原理

与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数，本质上都是通过不断地缩小查询范围

索引的优势劣势

优势

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

劣势

也占用磁盘降低更新表（增删改）的速度 创建索引的原则

创建

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

不创建

表记录太少经常增删改的表不作为查询条件的字段数据重复且分布均匀的字段 （性别）

索引的分类 主键索引

设置为主键后会自动建立索引，不能为空，一个表只能有一个主键

-- 查看索引
SHOW INDEX FROM admin;
-- 删除索引
DROp INDEX admin_account ON admin;

单值索引

一个索引只包含单个列，一个表可以有多个单值索引 （name / account）

-- 创建单值索引
CREATE INDEX admin_account ON admin(account);

唯一索引

索引列 数据不能重复，允许为null

-- 创建唯一索引
CREATE UNIQUE INDEX admin_account ON admin(account);

组合索引

一个索引中包含多个列，比单值索引开销更小(对于相同的多个列建索引)，列数远大于行数使用

组合索引最左前缀原则：列如表中有 a,b,c 3 列，为 a,b 两列创建组合索引，只有在最左侧索引列出现在查询条件中才会生效（a）；

全文索引

模糊查询时，一般索引无效，使用全文索引

-- 创建全文索引
CREATE FULLTEXT INDEX news_title ON news(title) WITH PARSER ngram;
-- 使用
SELECT * FROM news WHERe MATCH(title) AGAINST('汇总')

索引数据结构

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

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

聚簇索引: 找到了索引，就找到了数据

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

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

根据学号只查询学号,姓名； 虽然学号加了索引，但是还需要查询姓名，需要根据学号找到主键，通过主键回表查询

而 MyISAM 引擎采用的是非聚簇式设计，即使是主键索引，因为索引是单独维护在一个文件中，而InnoDB主键索引连着数据

事务

事务就是一次完整的数据库操作，这个操作中的多条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 默认隔离级别)：同一个事务中多次读取相同的数据返回的结果是一样的。

其避免了脏读和不可重复读问题，MySQL8 后也解决幻读问题

串行化

(serializable)：事务串行执行，避免了以上所有问题，类似加锁效率低

MVCC

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

为了提升MySQL 读-写 写-读 两个操作同时进行，写-写MySQL支持行级锁，不能同时操作一行数据

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

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

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

读已提交：每次读取数据前都生成一个 ReadView 产生不可重复读 其中数据发生改变,版本链中也会发生修改, 每次读的时候ReadView中的数据就发生改变,所有不可重复读可重复读：在第一次读取数据时生成一个 ReadView，之后数据发生改变,版本链发生变化,没有关系,第一次读的时候,已经拍过照了 锁机制

MySQL中的锁主要针对写写操作

行锁

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

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

表锁

当某个事物

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

间隙锁

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

共享锁 / 排他锁

共享锁：读锁

排他锁：写锁

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

乐观锁 / 悲观锁

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

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

SQL优化

正确的使用索引（查询条件列、排序列）避免索引失效

在where 子句中 避免 where num is null在 where 子句中使用!=或<>操作符在 where 子句中使用 or 来连接条件在where 使用运算符、函数 （where num/2=100、 substring() ） 建议使用主键自增 合理利用索引结构索引不宜建立太多 ，一般一张表6个左右 可以考虑组合索引 最左前缀原则状态,类型…一般建议使用数字类型 int变长varchar代替 定长char不建议使用 select * -->查询哪些列…一次性不要查询数据过多 ，可用分页查询，降低每次查询数据量避免字段值为null null是占空弄间的 可以给默认值 ’ ’