Mysql高级

已于 2022-01-22 23:36:08 修改
阅读量2.3k 收藏 1
点赞数 2
分类专栏： 笔记 文章标签： mysql sql 数据库
于 2022-01-20 23:15:39 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_51707222/article/details/122591462
版权

视图

  1. 什么是视图？
  • 视图是基于查询的虚拟表。通俗来讲，视图就是一条select语句执行后返回的结果集。
  • select语句所查询的表称为视图的基表，而查询的结果集称为虚拟表，视图本身并不储存具体的数据，视图的数据存在于视图的基表中，基本表数据发生了改变，视图的数据也会随之改变。
  • Mysql从5.0.1版本开始提供视图功能，它只保存了sql逻辑，不保存查询结果。
  1. 为什么使用视图？
    使用视图是为了方便复杂的查询语句。基本思路是将复杂的查询语句定义在视图内部，然后对视图进行查询，从而简化复杂的查询语句。
  2. 应用场景
    1. 多个地方用到同样的查询结果。
    2. 该查询结果使用的sql语句复杂。
  3. 定义视图
create view 视图名 as 查询语句

例如：

-- 创建视图
CREATE VIEW sel_news AS
SELECT a.id,a.account,a.password FROM admin a

在这里插入图片描述
优点

  • 重复使用sql语句。
  • 简化了复杂的sql操作。
  • 保护数据，提高安全性。
  1. 视图修改
    语法一：
-- 修改视图语法一:
CREATE OR REPLACE VIEW sel_news AS
SELECT a.id,a.account,a.sex FROM admin a

在这里插入图片描述
语法二：


-- 修改视图语法二:
ALTER VIEW sel_news AS
SELECT a.id,a.account,a.phone FROM admin a

在这里插入图片描述

  1. 视图删除
    语法：
-- 删除视图
DROP VIEW sel_news

在这里插入图片描述

  1. 使用视图
    语法：
-- 使用视图
SELECT * FROM sel_news

在这里插入图片描述

  1. 查看视图
    语法一：
-- 查看输入
DESC sel_news

在这里插入图片描述
语法二：


-- 查看输入语法2
SHOW CREATE VIEW sel_news

在这里插入图片描述

  1. 视图更新
    插入数据：
insert into 视图名 () values();

修改数据

update 视图名 set 列名=();

删除数据

delete from 视图名 where 条件

视图更新的可更新性与视图中查询的定义有关系下面这几种类型的视图是不能更新的

  • 包含以下关键字的sql语句：分组函数、distinct、grop by、having、union或者union all
  • 常量视图
  • select 中包含子查询
  • join（可以更改视图的内容，但是不能向视图中插入数据）
  • from 一个不能更新的视图
  • where子句的子查询引用了from 子句中的表
  1. 表与视图的对比
视图表格
create viewcreate table
基本上不占用内存（保存逻辑）占用内存（保存数据）
可以增删改查（一般不能增删改）可以增删改查

存储过程

  1. 概述
    对数据库的认知：里面可以以表存数据，使用sql语言，操作数据。

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

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

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

  3. 缺点：
    对数据库的依赖程度较高，移植性差。

在这里插入图片描述

  1. 创建存储过程的语法格式
-- 存储过程
DELIMITER $$

CREATE
    
    PROCEDURE 存储过程([in 变量名,out参数2,...])
    
       -- 存储过程体
	BEGIN
	-- DECLARE用来声明变量的
	DECLARE 变量名 类型 [DEFAULT 值];
	存储过程语句块
	END$$

DELIMITER ;

例如:

-- 查询管理员类型为1的有几条 演示入参和出参
DELIMITER $$

CREATE
    
    PROCEDURE type_count(IN p_typeid INT, OUT p_count INT)
    
       -- 存储过程体
	BEGIN

	SELECT COUNT(*) INTO p_count FROM admin WHERE TYPE=p_typeid ; -- 测试输出语句
	SELECT p_count;
	END$$

DELIMITER ;

  1. 流程控制语句 if else
DELIMITER$$
CREATE PROCEDURE test(IN p_day INT)
BEGIN
IF p_day=0 THEN
SELECT "星期天";
ELSEIF p_day=1 THEN
SELECT "星期一";
ELSEIF p_day=2 THEN
SELECT "星期二";
ELSE
SELECT "无效日期";
END IF;
END$$
测试
CALL test(2)
  1. mybatis调用存储过程插入信息
  • 创建带参的存储过程
-- 存储过程保存管理员,在存储过程中判断账号是否已经存储,不存在存储,则返回账号以存在
DELIMITER $$

CREATE
    
    PROCEDURE save_admin(IN p_account VARCHAR(11),IN p_password VARCHAR(50),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="保存成功";
	SELECT p_result;
	ELSE
	SET p_result ="账号已存在";
	SELECT p_result;
	END IF;

	END$$

DELIMITER ;
  • mybaits调用这个存储过程
    adminDao
 //测试存储过程
    void saveAdmins(Map<String,Object> map);

AdminMapper.xml

    <parameterMap id="adminmap" type="map">
        <parameter property="account" jdbcType="VARCHAR" mode="IN"></parameter>
        <parameter property="password" jdbcType="VARCHAR" mode="IN"></parameter>
        <parameter property="result" jdbcType="VARCHAR" mode="OUT"></parameter>

    </parameterMap>

    <insert id="saveAdmins" parameterMap="adminmap" statementType="CALLABLE">
        {CALL save_admin(?,?,?)}
    </insert>

测试类

public class TestSaveAdmin {
    @Test
    public  void saveAdmins() {
        SqlSession sqlSession= MybatisUtil.getSqlSession();
        AdminMapper mapper=sqlSession.getMapper(AdminMapper.class);
        HashMap<String,Object>map=new HashMap<>();
        map.put("account","tomcat3");
        map.put("password","123");
        mapper.saveAdmins(map);

        System.out.println(map.get("result"));
        sqlSession.commit();
        sqlSession.close();
    }
}

运行结果
在这里插入图片描述

  1. 对AdminMapper.xml中的一些解析
    在这里插入图片描述

函数

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

语句:

DELIMITER $$
create function 函数名([参数列表]) returns 数据类型
begin
DECLARE 变量；
sql 语句;
return 值;
end;
DELIMITER ;

注意：
1.参数列表包含两部分：参数名 参数类型
2.函数体：肯定会有 return 语句，如果没有会报错
3.函数体中仅有一句话，则可以省略 begin end
4.使用 delimter 语句设置结束标记设置函数可以没有参数
SET GLOBAL log_bin_trust_function_creators=TRUE;

删除函数
DROP FUNCTION

不带参函数

创建函数

# 查询admin表中的总数
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)TYPE FROM admin

结果
在这里插入图片描述
与不调用函数对比

SELECT account,id,TYPE FROM admin

在这里插入图片描述

触发器

触发器（trigger）是一种特殊的存储过程，其特殊性在于它并不需要用户直接调用，而是在对表添加、修改、删除之前或者之后自动执行的存储过程。

  • 类似于存储过程,函数,与表相关,有点像事件
  • 对表进行新增,修改,删除之后自动触发

特点

  1. 与表相关联

     触发器定义在特定的表上,这个表称为触发器表.

  2. 自动激活触发器

     当对表中的数据数据执行insert, update,或delete操作时
 如果对表上的这个特定操作定义了触发器,该触发器自动执行,这是不可撤销的

  3. 不能直接调用

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

  4. 作为事物的一部分

     触发器与激活触发器的语句一起为对一个单一的事务来对待
 可以从触发器的任何位置回滚

定义触发器

-- 触发器
DELIMITER $$

CREATE
   
    TRIGGER 触发器名称  触发时机 触发事件
    ON 表名
    FOR EACH ROW  -- 行级触发 操作多行时每行都会触发
     BEGIN
	语句
    END$$

DELIMITER ;

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

案例
删除管理员触发删除管理员与角色的关联关系

-- 触发器
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 =48

一行sql删除

案例
每当我向admin表中插入一条数据,就像admin_log表中插入一条日志记录

-- 每当我向admin表中插入一条数据,就像admin_log表中插入一条日志记录
DELIMITER $$

CREATE  TRIGGER save_adminLog
 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,PASSWORD)VALUES('test001','1111');

在这里插入图片描述

MySQL架构

MySQL的完整构架图
在这里插入图片描述
连接层

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

服务层

  • 第二层构架主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化以及部分内置函数的执行

  • 所有跨存储引擎的功能也在这一层实现，如过程、函数等。

  • 在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化，如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

引擎层

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

在这里插入图片描述

MySQL引擎

引擎是什么

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

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

MySQL有哪些常见的引擎

  1. MyIsam ,
  2. InnoDB,
  3. Memory,
  4. Blackhole,
  5. CSV,

常用的引擎

MyiSam  不支持事务  查询多使用 支持外键 
	    不支持行锁，支持表锁，支持全文索引，存储表的总行数

Inn0db  支持事务  增删改多使用  支持表锁、行锁、
	    支持缓存、支持主键自增，支持全文索引，不存储表的总行数

查看支持的引擎

-- 查看支持的引擎
SHOW ENGINES

在这里插入图片描述

查看表引擎

SHOW TABLE STATUS LIKE 'admin'

在这里插入图片描述

修改引擎

方式 1:将 mysql.ini 中 default-storage-engine=InnoDB，重启服务.
方式 2:建表时指定 CREATE TABLE 表名(...)ENGINE=MYISAM;
方式 3:建表后修改 ALTER TABLE 表名 ENGINE = INNODB

索引

在学习数组的时候，我们知道可以通过索引快速的找到某个索引位置的数据。
为什么要有索引？

当数据库里的数据特别大，我们在查询数据的时候 例如：select name from admin where id=99999 mysql需要扫描全表来查找id=99999的数据，也就相当于从第一条数据向后逐页开始查找，直到找到（和循环遍历有点像）。

所以不使用索引的话，查询从第一行开始，逐行向后查询，直到查询到我们需要的数据。
如果数据量非常大的情况下，查询所消耗的事件比较长。

什么是索引？

  • 索引就是类似于书的目录，通过目录我们可以快熟查询到我们需要的数据
  • 索引是帮助MySQL高效获取数据的数据结构
  • 排好序的快速查找的数据结构
  • 在一个数据结构中将数据维护着，方便查找

索引原理

类似于书的的目录，通过目录可以快速查询到我们需要的数据，缩小查询范围。

索引优势

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

索引劣势

  • 索引也是一张表，里面保存了主键与索引字段，并指向实体表的记录，所以它需要占用磁盘空间。
  • 虽然索引大大提高了查询速度，但是也降低了更新表的速度。
    例如对表进行insert、update、delete、
    因为在更新表时，MySQL不仅要更新表数据还要更新索引文件，每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

索引分类

  • 主键索引

    设定为主键后数据库会自动建立索引

ALTER TABLE 表名 add PRIMARY KEY 表名(列名);
#删除建主键索引：
ALTER TABLE 表名 drop PRIMARY KEY

  • 单值索引：

    一个索引包含一个列 、一个表可以有多个单值索引。

-- 创建单值索引
CREATE INDEX 索引名 ON 表名(列名);

CREATE INDEX  admin_account ON  admin(account);

删除索引：
DROP INDEX 索引名

  • 唯一索引：

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

创建唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名(列名);
删除索引
DROP INDEX 索引名 ON 表

  • 组合索引(复合索引)：

    一个索引包含多个列，
当表的行数远大于索引列的数目时可以使用。
开销更小

创建复合索引
CREATE INDEX 索引名 ON 表名(列 1,列 2...);
删除索引：
DROP INDEX 索引名 ON 表名;
组合索引最左前缀原则

组合索引最左前缀原则
使用组合索引时,要出现最左列(组合索引里的最左列)在这里理解的

使用组合索引时,要出现最左列(组合索引里的最左列),否则索引失效。
例如：表中有a、b、c三列 为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 索引名 ON 表名(字段名) WITH PARSER ngram;
SELECT 结构 FROM 表名 WHERE MATCH(列名) AGAINST(搜索词')

查看索引：

SHOW INDEX FROM 表名

索引创建原则
索引也是一把双刃剑，因此不能随便使用。

  • 什么时候需要创建索引？

      1. 主键自动建立唯一约束  主键索引
  2. 作为查询条件的列 适合创建索引
  3. 外键建议建立索引
  4. 排序的字段、分组的字段

  • 什么情况不建议使用索引？

      1. 表数据太少
  2. 经常更新的表（增删改）
  3. 不是查询条件的字段
  4. 数据重复且分布均匀的字段 例如（性别）

索引数据结构

  • MySQL Innodb引擎默认使用B+树作为数据结构存储索引 。
  • 排好序的，一个节点可以存储多个数据。（横向扩展）是数的高度降低
  • 非叶子节点不存储数据，只存储索引，可以放更多的索引。
  • 数据记录都存放在叶子节点中。找到索引也就找到了数据。
  • 所有叶子节点之间都有一个链指针。

在这里插入图片描述
聚簇索引和非聚簇索引

  • 聚簇索引

    1.找到了索引就找到了数据，这就是聚簇索引
2.主键可以直接找到数据
3.根据学号只查找学号 可以直接命中学号 
  此种场景学号就是聚簇的

  • 非聚簇索引

    1. 找到了索引但没有找到数据，需要根据主键再次回表查询
2. 根据学号查询学号、姓名 虽然学号加了索引但还是需要查询姓名 
   需要根据学号，找到主键，通过主键回表查询 此种场景就是聚簇的

事务

概念

事务就是一次完整的数据库操作,这个操作过程中可能包含了多条sql语句执行,而这多条sql语句是一个整体,要么都成功执行,要么都不执行。

例如：转账操作 （A用户向B用户转账100元）
A账户减少100元与B账户增加100元就是一个整体，必须同时执行，或者同时不执行。

MySQL中只有Innodb引擎支持数据据事务。

事务用来管理insert 、update、 delete 语句。

关系型事务的四大特性：

  1. 原子性

一次事务中的多个操作要么全部成功，要么都不成功，不会结束在某个中间环节。事务在执行过程中发生错误就会rollback（回滚）到事务开始前的状态，就像这个事务没有执行过一样。

  1. 持久性

事务处理结束后，对数据库的修改就是永久的，即便系统故障也不会丢失。

  1. 隔离性

数据库是允许同时有多个事务进行访问的，这个时候就要对多个事务之间的操作进行隔离。

隔离分为四个级别

  1. 读未提交：存在脏读问题
  2. 读已提交：解决脏读问题，存在不可重复读问题
  3. 可重复读：解决不可重复读问题，MySQL5之前存在幻读问题。之后就不存在幻读问题了。
  4. 串行化：解决一切问题，加锁 但是效率低
  1. 一致性：

事务开始之前到事务结束以后，数据库的完整性没有被破坏。

例如我们可以用很多方式对自己的银行账户余额进行多次同时操作，最终余额应该是预期的结果，不能出现错误。

事务设置：

默认情况下，MySQL启用自动提交的事务，（变量auto commit为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;开启自动提交
    SESSION :会话级别
    GLOBAL ：全局级别

查看事务（autocommit 模式）
SHOW SESSION / GLOBAL VARIABLES LIKE ‘autocommit’；

并发事务处理带来的问题

  1. 脏读（读到的是垃圾数据，A事务读到B事务未提交的数据）
  1. 事务B更新年龄为16。
  2. 事务A读取数据库信息，年龄为16。
  3. 事务B回滚，数据量信息中年龄依然为18。

在这里插入图片描述
2. 不可重复读（在事务A中先后两次读取同一个数据，两次读取的结果不一样，预期应该是一样的）

  1. 事务A读取年龄为18。
  2. 事务B更改数据库信息，更改年龄为16。
  3. 事务A读取数据库年龄为16。
    18？ 16？

在这里插入图片描述

3.幻读（事务A中按照某个条件先后查询两次，两次查询结果的条数不同。）

与不可重读的区别是:

  • 不可重读是数据变了，而幻读是数据的数量变了。
  1. 事务A读取年龄大于15的数据结果为1条。
  2. 事务B更改数据库信息，向数据空中添加了一条数据（例如age=20的数据）。
  3. 事务A读取数据库年龄大于15的数据结果为2条。
    1？ 2？【幻觉？】

在这里插入图片描述

一般幻读出现在范围查询中。

事务隔离级别
查看隔离级别

SELECT @@global.transaction_isolation,@@transaction_isolation;

设置隔离级别

-- 读未提交
SET SESSION/GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- 读已提交
SET SESSION/GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED

-- 可重复读
SET  GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ

读 未提交：

  • 事务A可以读取到事务B未提交的数据
  • 问题 ：会有脏读 几乎不用。

读 已提交：

  • 事务A不能读取到事务B未提交的数据，只能读取到事务B已提交的数据
  • 解决了脏读的问题 但存在不可重读的问题

可重复读：

  • 事务A开启后，第一次读到某个数据后，那么在这个事务中，第二次再查询同样的数据时，应该和原来一致，也就是可以重复读。
  • 解决了不可重复读问题

串行化

  • 解决所有问题。一次只允许一个事务进行操作。
  • 是最安全的，也是效率最低的。

MVCC

  • MVCC（多版本并发控制 Multi-Version Concurrent Control）。
  • 为了提升MySQL 读-写、写-读两个操作操作同时进行。
  • 写-写操作MySQL支持行级锁的，如果两个事务同时操作同一行数据，那么肯定是不可以的

不同的隔离级别在读取数据时，会根据版本链生成一个ReadView（临时视图）

  • 每次对表中的记录操作时，会保存一个日志（undolog）里面记录事务的id号。
  • 如果有多个事务操作时，它们就会根据事物的id，找到自己操作的版本记录。
  • READ COMMITTED：每次读取数据前都生成一个 ReadView 产生不可重复读，其中数据发生改变，版本链中也会发生修改，每一次读的时候ReadView中的数据就发生了改变，所以就不可重复读。
  • REPEATABLE READ：在第一次读取数据时生成一个 ReadView，之后数据发生改变，版本链发生变化，没有关系，第一次读取的时候已经拍过照了，不会从最新的去读，因此可以重复读取。

锁机制

  • MySQL中的锁主要用于对写-写。
  • MySQL中支持行锁、间隙锁、表锁。

行锁：

  • 某个事物对某行记录进行操作时，会把当前行锁住，其他事务不能对当前行进行更新操
    作。（读不受影响）
  • 力度最小，并发最高，频繁加锁释放锁。
  • 行锁分为共享锁和排他锁。

共享锁(S)：

  • 又称为读锁允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务 T 对数据对象 A 加上 S 锁，则事务 T 可以读 A 但不能修改A，其他事务只能再对 A 加 S 锁，而不能加 X 锁，直到 T 释放 A 上的 S 锁。这保证了其他事务可以读 A，但在 T 释放 A 上的 S 锁之前不能对 A 做任何修改。

排他锁（X）：

  • 又称写锁。允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务 T 对数据对象 A 加上 X 锁，事务 T可以读 A 也可以修改 A，其他事务不能再对 A 加任何锁，直到 T 释放 A 上的锁。update,delete,insert 都会自动给涉及到的数据加上排他锁，select 语句默认不会加任何锁类型，如果加排他锁可以使用 select …for update 语句，加共享锁可以使用 select … lock in share mode 语句。

还可以分为乐观锁,悲观锁：

  1. 乐观锁：并不会真正的去锁某行记录，而是通过一个版本号来实现的。
    没有加锁，可以通过版本号来区分。
  2. 悲观锁：上面的行锁、间隙锁、表锁等都是悲观锁。

间隙锁：

  • 锁的是一个区间，在条件范围操作时，会给满足条件的区间行加锁。

表锁：

  • 当某个事务对某行记录进行操作时，会将整个表锁住。Innodb引擎用的少，myisam引擎只支持表锁。
  • 开销小、加锁快、不会出现死锁；锁定力度大，发生锁冲突的概率高，并发度最低。

SQL优化

为什么要对SQL 进行优化？

随着时间的积累，业务数据量的增多，SQL的执行效率对程序的运行效率的影响逐渐增大，此时对SQL的优化就很有必要。

SQL优化的一些方法

  1. 对查询进行优化，应该尽量避免全表扫描，首先应考虑在where及order by涉及的列上建立索引。

  2. 应该尽量避免索引失效
    2.1 在where子句中对字段null值判断，否则将导致引擎放弃使用索引儿进行全表扫描。

    例如：select id from t where num is null
可以将null设置默认值为' '，
 null占内存
  ' ' 不占内存

    2.2 避免在where子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。

    2.3 应避免在where子句中使用or来连接，否则将导致引擎放弃使用索引而进行全表扫描。

     例如：select id from t where num = 10 or num =20 
 可以这样：select id from t where num in （10，20）

    2.4 in 和 not in 也要慎用，否则会导致全表扫描。

    select id from t where num in(1,2,3)
对于连续的数值，建议使用between
 select id from t where num betw

    2.5模糊查询不建议使用 like

    select id from t where name like '%abc
建议这样使用：
SELECT id FROM menu WHERE MATCH(NAME) AGAINST('abc')

    2.6 避免在where子句中对字段进行表达式操作。

    slelct id from t where  num/2 =100
应改为：
select id from t where num =100*2；

    2.7 应尽量避免在 where 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。

    例如：
select id from t where substring(name,1,3)='abc'
查找name以abc开头的id

  3. 索引并不是越多越好，索引固然可以提交相应的select 的效率，但是同时也降低了insert 及 update 的效率。

    因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。

    一个表的索引数最好不要超过 6 个，若太多则应考虑一些不常使用到的列上建的索引是否有必要或者考虑组合索引（要注意最左前缀原则）。

  4. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降
    低查询和连接的性能，并会增加存储开销。
    这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字
    型而言只需要比较一次就够了。

  5. 尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节省存储空间， 其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

  6. 任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

  7. 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。（分页查询，降低每一次查询的数据量）

  8. .尽量避免 NULL,平时开发过程中，MySQL 字段的一般都会设置为 NOT NULL

     空值(‘ ’)是不占用空间的 MySQL 中的 NULL 其实是占用空间的.
 如果查询中包含可为 NULL 的列，不走索引count()统计某列的记录数的时候
 如果采用的 NULL 值，会被系统自动忽略掉，但是空值是会进行统计到其中的。
PG.杨帅
关注
  • 2
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL怎么把输出结果建表_sql将查询结果建立为新表
weixin_35838019的博客
02-03 8646
1.sqlserver中，使用：select * into tab_new from tab_oldSELECT * into anzhiresult from (select * from factdownloads_new where storename='anzhi') b复制表结构句型，跨数据库--复制结构+数据select * into 数据库名.dbo.新表名 from 数据库名.db...
MySQL高级MySQL高级
最新发布
02-21
在这个“MySQL高级”主题中，我们将深入探讨SQL语句，这是与MySQL交互的基础。 一、SQL基础 SQL（Structured Query Language）是一种用于管理关系数据库的标准语言。在MySQL中，SQL语句主要包括以下类型： 1. **...
MYSQL把查询到数据新建成一张表
Jwg199908的博客
09-19 701
1.create table 表名 as sql语句;
WEB应用程序开发之Mybatis框架--Dao层 Mapper接口讲解
qq_51352148的博客
01-19 2023
这里写目录标题Mybatis-Dao 层 Mapper 接口Mybatis 日志参数传递简化代码测试需要使用的jar包推荐插件 Mybatis-Dao 层 Mapper 接口 Mapper 接口开发方法只需要程序员编写 Mapper 接口（相当于 Dao 接口），由 Mybatis 框架根据接口定义创建接口的动态代理对象. 使用 session.getMapper(接口.class);获得代理对象Mapper 接口开发需要遵循以下规范： 1、 Mapper.xml 文件中的 namespace 与 mapp
Mybatis使用示例
龙腾四海365的专栏
03-09 317
Mybatis使用示例 AdminMapper.xml <!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN" "http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd"> <!-- AdminMapper.xml 中的namespace 与 AdminM
mysql把结果集当做表_将mysql查询结果集显示为表
weixin_42450002的博客
01-21 1674
我想在我的网页上显示一个查询结果集作为表格。该查询不会导致任何错误，但脚本不会产生任何输出。我在数据库(玩家)中有一张桌子。该表有3行(id，firstname，lastname)。Script source code：View RecordsView Recordserror_reporting(E_ALL);ini_set('display_errors','1');$db = new PDO...
2021年MySQL高级教程视频.rar
09-24
01.MySQL高级课程内容介绍.mp4 02.MySQL高级Linux上安装MySQL.avi 03.MySQL高级启动及登录MySQL.avi 04.MySQL高级索引概述.avi 05.MySQL高级索引优势和劣势.avi 06.MySQL高级索引数据结构.avi 07.MySQL高级索引数据...
mysql高级参考课件（供实操参考）.zip
01-18
以下是基于提供的"mysql高级参考课件（供实操参考）.zip"压缩包文件中的可能涵盖的一些关键知识点： 1. **SQL语言的高级应用**： - 子查询：学习如何在SELECT语句中嵌套查询，用于获取满足特定条件的数据。 - ...
MySQL高级特性
06-14
MySQL高级特性，尚硅谷的教学笔记
mysql临时表用法分析【查询结果可存在临时表中】
09-09
主要介绍了mysql临时表用法,结合实例形式分析了MySQL将查询结果存储在临时表中的相关操作技巧,需要的朋友可以参考下
mysql把以逗号连接的字段拆分成列表
m0_58073802的博客
12-28 1415
test表的行数要大于需要拆分字段拆分后的数量,否者会少数据。创建目标表testtt。由目标表testtt。
mysql实现查询后的结果作为一个表
GoodIdea
03-15 2212
首先建立要操作的表 CREATE TABLE `jackpot_info` ( `issuenumber` varchar(32) DEFAULT NULL COMMENT '期号', `lotterytype` varchar(30) DEFAULT NULL COMMENT '彩票类型', `moneypool` varchar(50) DEFAULT NULL COMMENT '奖池...
mysql 中，将查询结果保存成表
l87133的博客
11-17 4471
方法：直接在查询语句前加上“create  table  表名”即可！
mysql将查询结果插入新表
weixin_33958366的博客
12-29 627
为什么80%的码农都做不了架构师？>>> ...
mysql查询结果更新到新表_MySQL查询结果复制到新表的方法(更新、插入)
weixin_34668850的博客
02-02 2310
MySQL中可以将查询结果复制到另外的一张表中，复制的话通常有两种情况，一种是更新已有的数据，另一种是插入一条新记录。下面通过例子来说明。首先构建两个测试表。表t1：表t2：1、如果t2表中存在score值，将score更新到t1表中。方法如下：UPDATE t1,t2SET t1.score = t2.scoreWHERE t1.id = t2.id AND t2.score IS NOT NU...
mysql 根据查询结果创建新表
热门推荐
lyw5200的博客
10-23 1万+
mysql环境下，将查询结果新建成一张表 create table result_table select * from init_table; 上述代码中 result_table是为要创建的结果表的表名 *则为要查找的内容，也可以是多个字段 init_table则为原始表 ...
Ubuntu用户管理与MySQL高级查询
"MySQL高级查询实例及CMD命令" MySQL是一种广泛使用的开源关系型数据库管理系统，它在各种规模的应用中都有应用，从个人项目到大型企业级系统。本资源着重讲解了MySQL的高级查询技巧以及在Ubuntu系统中通过CMD...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值