MySQL攻略(4)

咕咕猫_

已于 2022-05-20 10:29:28 修改

阅读量1.9k

点赞数

分类专栏： MySQL 笔记文章标签： mysql

于 2022-05-04 20:44:21 首次发布

本文链接：https://blog.csdn.net/weixin_61843013/article/details/124576117

版权

笔记同时被 2 个专栏收录

111 篇文章 5 订阅

订阅专栏

MySQL

14 篇文章 0 订阅

订阅专栏

一、索引

1.1 索引的原理

1. 没有索引会全表扫描，从而查找速度会很慢

2. 使用索引会形成一个索引的数据结构，比如二叉树

3. 索引的带价

①磁盘占用

②对dml(update、delete、insert)语句的效率影响

1.2 索引的类型

1. 主键索引，主键自动的为主索引(类型Primary key)

2. 唯一索引(UNIQUE)

3. 普通索引(INDEX)

4. 全文索引(FULLTEXT)，一般开发不适用mysql自带的全文索引，而是使用：全文搜索Solr和ElasticSearch(ES)

1.3 索引使用

添加索引

create [UNIQUE] index index_name ON table_name (col_name[(length)]) [ASC| DESC],..);

添加主键索引

ALTER TABLE 表名 ADD PRIMARY KEY(列名，.....)；

删除索引

DROP INDEX index_name ON table_name；

删除主键索引

ALTER TABLE table_name DROP PRIMARY KEY；

查找索引

show index(ex) from table_name；

show keys from table_name；

desc table_name；

二、mysql事务

2.1 什么是事务

事务用于保存数据的一致性，它由一组相关的dml语句组成，该组的dml语句要么全部成功，要么全部失败。如：转账就要用事务来处理，用以保证数据的一致性

2.2 事务和锁

当执行事务操作时(dml语句)，mysql会在表上加锁，防止其它用户改表的数据

mysql数据库控制台事务的几个重要操作

1. 开始一个事务：start transaction

2. 设置保存点：savepoint

3. 回退事务：rollback to

4. 回退全部事务：rollback

5. 提交事务，所有的操作生效，不能回退：commit

-- 事务的一个重要的概念和具体操作
-- 1. 创建一张测试表
CREATE TABLE t28(
	id INT,
	`name` VARCHAR(32));
-- 2. 开始事务
START TRANSACTION
-- 3. 设置保存点
SAVEPOINT a
-- 执行 dml 操作
INSERT INTO t28 VALUES(100,'tom');

SELECT * FROM t28;

SAVEPOINT b

-- 执行 dml 操作
INSERT INTO t28 VALUES(200,'jack');

-- 回退到 b
ROLLBACK TO b
-- 继续回退 a
ROLLBACK TO a

-- 如果这样, 表示直接回退到事务开始的状态. 
ROLLBACK
COMMIT

2.3 回退事务

介绍回退事务之前先介绍一下保存点(savepoint)。保存点是事务中的点，用于取消部分事务，当结束事务时(commit)，会自动的删除该事务所定义的所有保存点。当执行回退事务时，通过指定保存点可以回退到指定的点

2.4 提交事务

使用commit语句可以提交事务，当执行了commit语句后，会确认事务的变化、结束事务、删除保存点、释放锁、数据生效。当使用commit语句结束事务后，其他会话(其他连接)将可以查看到事务变化后的新数据(所有数据就正式生效)

2.5 事务的使用细节

1. 如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚

2. 如果开始一个事务，没有创建保存点，也是可以执行rollback，默认就是回退到事务开始的状态

3. 可以在这个事务中(还没有提交时)，创建多个保存点

4. 可以在事务没有提交前，选择回退到哪个保存点

5. mysql的事务机制需要innodb的存储引擎才可以使用，myisam不好使

6. 开始一个事务可以用start transaction，也可以使用set autocommit=off

-- 讨论 事务细节
-- 1. 如果不开始事务，默认情况下，dml 操作是自动提交的，不能回滚
INSERT INTO t28 VALUES(300,'milan'); -- 自动提交commit

SELECT * FROM t28;

-- 2. 如果开始一个事务，你没有创建保存点. 你可以执行 rollback，
-- 默认就是回退到你事务开始的状态
START TRANSACTION
INSERT INTO t28 VALUES(400, 'king');
INSERT INTO t28 VALUES(500, 'scott');
ROLLBACK -- 表示直接回退到事务开始的的状态
COMMIT;

-- 3. 你也可以在这个事务中(还没有提交时), 创建多个保存点.比如: savepoint aaa; 
-- 执行 dml , savepoint bbb

-- 4. 你可以在事务没有提交前，选择回退到哪个保存点
-- 5. InnoDB 存储引擎支持事务 , MyISAM 不支持
-- 6. 开始一个事务 start transaction, set autocommit=off

三、mysql事务隔离级别

3.1 介绍

1. 多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性

2. 如果不考虑隔离性，可能会引发脏读、不可重复读、幻读

隔离级别就是该事务读取连接到数据库时的数据，不希望读到其他事务在该时间点之后修改的数据，所以产生了前三种隔离级别

3.2 查看事务隔离级别

脏读：当一个事务读取另一个事务尚未提交的改变

不可重复读：同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生不可重复读

幻读：同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读

脏读是别的事务提交前自己就会被影响，不可重复读和幻读是提交后被影响

不可重复读是修改或删除造成的影响

幻读是指插入造成的影响

3.3 事务隔离级别

概念：Mysql隔离级别定义了事务与事务之间的隔离程度

3.4 设置事务隔离级别

Mysql默认的事务隔离级别是repeatable read，一般情况下没有特殊要求没有必要修改(因为该级别可以满足绝大部分项目需求)

查看当前会话隔离级别

select @@tx_isolation;

查看系统当前隔离级别

select @@global.tx_isolation;

设置当前会话隔离级别

set global transaction isolation level repeatable read;

设置系统当前隔离级别

set global transaction isolation level repeatable read;

-- 演示 mysql 的事务隔离级别
-- 1. 开了两个 mysql 的控制台

-- 2. 查看当前 mysql 的隔离级别
SELECT @@tx_isolation; 
-- mysql> SELECT @@tx_isolation; 
-- +-----------------+
-- | @@tx_isolation | 
-- +-----------------+
-- | REPEATABLE-READ | 
-- +-----------------+

-- 3.把其中一个控制台的隔离级别设置 Read uncommitted
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- 4. 创建表
CREATE TABLE `account`(
	id INT, `name` VARCHAR(32), money INT);

-- 查看当前会话隔离级别
SELECT @@tx_isolation

-- 查看系统当前隔离级别
SELECT @@global.tx_isolation

-- 设置当前会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- 设置系统当前隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL [你设置的级别]

四、mysql事务ACID

4.1 事务的acid特性

1. 原子性(Atomicity)：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么不发生

2. 一致性(Consistency)：事务必须使数据库从一个一致性状态变换到另外一个一致性状态

3. 隔离性(Isolation)：事务的隔离性是多个用户并发访问数据库时，数据库位每一个用户开启的事务，布恩那个被其他事务的操作数据所干扰，多个并发事务事务之间要相互隔离

4. 持久性(Durability)：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

五、mysql表类型和存储引擎

5.1 基本介绍

1. MySQL的表类型由存储引擎(Storage Engines)决定，主要包括MylSAM、innoDB、Memory等

2. MySQL数据表主要支持六种类型，分别是：CSV、Memory、ARCHIVE、MRG_MYISAM、MYISAM、InnoDB

3. 这六种又分两类，一类是“事务安全型”(transaction-safe)，比如：InnoDB；其余都属于第二类，称为“非事务安全型”(non-transaction-safe) [mysiam和memory]

5.2 主要的存储引擎/表类型特点

5.3细节说明

重点介绍：MyISAM、InnoDB、MEMORY

1. MyISAM不支持事务、也不支持外键，但其访问速度快，对事务完整型没有要求

2. InnoDB存储引擎提高了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎，InnoDB写的处理效率差一点并且会占用更多的磁盘空间以保留数据和索引

3. MEMORY存储引擎使用存在内存中的内容来创建表。每个MEMORY表指实际对应一个磁盘文件。MEMORY类型的表访问非常的快，因为他的数据是放在内存中的，并且默认使用HASH索引。但是一旦MySQL服务关闭，表中的数据就会丢失掉，但表的结构还在

-- 表类型和存储引擎

-- 查看所有的存储引擎
SHOW ENGINES

-- innodb 存储引擎，是前面使用过. 
-- 1. 支持事务 2. 支持外键 3. 支持行级锁

-- myisam 存储引擎
CREATE TABLE t29(
	id INT,
	`name` VARCHAR(32))ENGINE MYISAM
-- 1. 添加速度快 2. 不支持外键和事务 3. 支持表级锁
START TRANSACTION;
SAVEPOINT t1
INSERT INTO t29 VALUES(1,'jack')
SELECT * FROM t29;
ROLLBACK TO t1;

-- memory 存储引擎
-- 1. 数据存储在内存中[关闭了 Mysql 服务，数据丢失, 但是表结构还在]
-- 2. 执行速度很快(没有 IO 读写) 3. 默认支持索引(hash 表)
CREATE TABLE t30(
	id INT,
	`name` VARCHAR(32))ENGINE MEMORY
DESC t30
INSERT INTO t30
	VALUES(1,'tom'),(2,'jack'),(3,'ggm');
SELECT * FROM t30

-- 指令修改存储引擎
ALTER TABLE `t29` ENGINE = INNODB

5.4 如何选择表的存储引擎

1. 如果你的应用不需要事务，处理的只是基本的CRUD操作，那么MyISAM是不二选择，速度快

2. 如果需要支持事务，选择InnoDB

3. Memory存储引擎就是将数据存储在内存中，由于没有磁盘I/O的等待，速度极快，但由于是内存存储引擎，所作的任何修改在服务器重启后都将消失(经典用法就是用户的在线状态)

5.5 修改存储引擎

ALTER TABLE 表名 ENGINE = 存储引擎；

六、视图

6.1 基本概念

视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含列，其数据来自对应的真实表(基表)

6.2 视图的基本使用

创建视图：create view 视图名 as select语句

更新成新的视图：alter view 视图名 as select语句

查看创建视图的指令：show create view 视图名

删除视图：drop view 视图名

-- 视图的使用
-- 创建一个视图 emp_view01，只能查询 emp 表的(empno、ename, job 和 deptno ) 信息
-- 创建视图
CREATE VIEW emp_view01
	AS
	SELECT empno,ename,job,deptno FROM emp;
-- 查看视图
DESC emp_view01;

SELECT * FROM emp_view01;
SELECT empno, job FROM emp_view01

-- 查看创建视图的指令
SHOW CREATE VIEW emp_view01;
-- 删除视图
DROP VIEW emp_view01;

-- 视图的细节
-- 1. 创建视图后，到数据库去看，对应视图只有一个视图结构文件(形式: 视图名.frm)
-- 2. 视图的数据变化会影响到基表，基表的数据变化也会影响到视图[insert update delete ]
-- 修改视图 会影响到基表
UPDATE emp_view01
	SET job = 'MANAGER'
	WHERE empno = 7369
	
SELECT * FROM emp; -- 查询基表

SELECT * FROM emp_view01;

-- 修改基本表， 会影响到视图
UPDATE emp
	SET job = 'SALESMAN'
	WHERE empno = 7369

-- 3. 视图中可以再使用视图 , 比如从 emp_view01 视图中，选出 empno,和 ename 做出新视图
DESC emp_view01

CREATE VIEW emp_view02
	AS
	SELECT empno,ename FROM emp_view01
	
SELECT * FROM emp_view02;

6.3 视图的使用细节

1. 创建视图后，到数据库去看，对应的视图只有一个视图结构文件(形式：视图名.frm)

2. 视图的数据变化会影响到基表，基表的数据变化也会影响到视图【insert update delete】

3. 视图中可以再使用视图，数据仍然来自基表

6.4 视图最佳实践

         1. 安全。一些数据表有着重要的信息。有些字段是保密的，不能让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查询自己需要的字段,不能查看保密的字段。
        2. 性能。关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时,数据库查询通常会用到连接（JOIN）。这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用JOIN查询数据。
        3. 灵活。如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃。然而，很多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的目的。