10、11、12、索引、事务、存储引擎

最新推荐文章于 2024-06-02 14:54:06 发布

吃蜡笔的小新

最新推荐文章于 2024-06-02 14:54:06 发布

阅读量129

点赞数

分类专栏：数据库文章标签： mysql 数据库 elasticsearch

本文链接：https://blog.csdn.net/Ningmengyouzhi/article/details/125244544

版权

数据库专栏收录该内容

11 篇文章 0 订阅

订阅专栏

文章目录

十、索引
- 索引的类型
十一、事务
十二、存储引擎
- 基本介绍
- 如何选择存储引擎

十、索引

说起提高数据库性能，索引是最物美价廉的东西了。不用加内存，不用改程序，不用调sql，查询速度就可能提高百倍干倍。
比如给你一个10000000000个信息的数据，让你找一条信息，是不是很难，但如果里面所有信息都有目录，那不就很快就可以找到了。

假如这里有一张100000000000条信息的海量数据，内容格式为：

现在如果在这里查找一个信息将会要很长时间（几秒），所以我们需要对他进行添加索引

CREATE INDEX empno_index ON emp (empno);-- 对emp表中empno添加索引，索引名称为empno_index；
-- 添加索引需要一段时间（几秒），索引完成后，表的内存会变大，相当于加入了一个目录，
-- 注意： 添加的是empno的索引，所以按照empno进行查找时，速度才会变快

索引的类型

主键索引，主键自动的为主索引(类型Primary key)
唯一索引(UNIQUE)
普通索引(INDEX)
全文索引(FULLTEXT) [适用于MyISAM]
一般开发，不使用mysql自带的全文索引，而是使用：全文搜索
Solr和ElasticSearch (ES)

create table t1 (
id int primary key, --主键，同时也是索引，称为主键索引.
name varchar(32));
create table t2(
id int unique, – id是唯一的，同时也是索引，称为unique索引

注意：索引的创建要考虑查询次数，如果某个字段从来就不会用它查，那给它创一个索引不就是浪费时间空间吗。

-- 演示mysql的索引的使用
CREATE TABLE t25 (
	id INT ,
	`name` VARCHAR(32));
	
-- 查询表是否有索引
SHOW INDEXES FROM t25;

-- 添加索引
-- 添加唯一索引 
CREATE UNIQUE INDEX id_index ON t25 (id);

-- 添加普通索引方式1
CREATE INDEX id_index ON t25 (id);

-- 如何选择 
-- 1. 如果某列的值，是不会重复的，则优先考虑使用unique索引, 否则使用普通索引
-- 添加普通索引方式2
ALTER TABLE t25 ADD INDEX id_index (id)

-- 添加主键索引
CREATE TABLE t26 (
	id INT ,
	`name` VARCHAR(32));
ALTER TABLE t26 ADD PRIMARY KEY (id)

SHOW INDEX FROM t25

-- 删除索引
DROP INDEX id_index ON t25
-- 删除主键索引
ALTER TABLE t26 DROP PRIMARY KEY


-- 修改索引 ， 先删除，在添加新的索引

-- 查询索引
-- 1. 方式
SHOW INDEX FROM t25
-- 2. 方式
SHOW INDEXES FROM t25
-- 3. 方式
SHOW KEYS FROM t25
-- 4 方式
DESC t25
-- 查看搜索方式
EXPLAIN SELECT * from emp;  -- 没添加索引时，查找需要遍历每一个元素

小结

较频繁的作为查询条件字段应该创建索引
select * from emp where empno = 1
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
select * from emp where sex = ‘男’
更新非常频繁的字段不适合创建索引
select * from emp where logincount = 1
不会出现在WHERE子句中字段不该创建索引

十一、事务

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

比如，你在10点的时候开始事务，然后敲代码…，在11点的时候保存一下结点，然后再敲代码…。然后12点下班了。你突然想起来11点到12点的时候，敲的代码有问题，就可以直接回到11点时候的状态(那个时候保存了结点)，如果10-11点的时候代码有问题，也可以回到10点时候的那个状态。一旦回滚到11点，之后的信息就会全部删除，不能再回到12点了。即对这段时间进行事务，一点执行就全部执行，一旦不执行就全不执行。

当执行事务操作时(dml语句) ，mysql会在表上加锁,防止其它用户改表的数据。这对用户来讲是非常重要的

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

start transaction -开始一个事务
savepoint保存点名-设置保存点
rollback to保存点名-回退事务
rollback -回退全部事务
commit -提交事务，所有的操作生效，不能回退

-- 事务的一个重要的概念和具体操作
-- 根据上面的示意图演示
-- 演示
-- 1. 创建一张测试表
CREATE TABLE t27
	( id INT,
	  `name` VARCHAR(32));
-- 2. 开始事务
START TRANSACTION  
-- 3. 设置保存点
SAVEPOINT a  -- 在这个保存点时，没有任何信息
-- 执行dml 操作
INSERT INTO t27 VALUES(100, 'tom');

SELECT * FROM t27;  -- 以从上到下依次执行的话，目前只有一条信息

SAVEPOINT b -- 再次设置一个保存点    -- 在这个保存点时，有一条信息
-- 执行dml操作
INSERT INTO t27 VALUES(200, 'jack');
INSERT INTO t27 VALUES(300, 'timi');

SELECT * FROM t27;  -- 以从上到下依次执行的话，目前有三条信息

-- 回退到 b
ROLLBACK TO b -- 以从上到下依次执行的话，又回到了b保存点时的状态，即只有一条信息了
-- 继续回退 a
ROLLBACK TO a  -- 表示直接回退a保存点时的状态，没有任何信息

ROLLBACK -- 表示回到开启事务时的状态

COMMIT  -- 提交事务，一旦提交，就不能再回退了

-- 一旦回退到a就不能再回到b，因为一旦回退，后面的信息就全部删除了，不能再回来了

事务细节：

讨论事务细节

1、如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚
INSERT INTO t27 VALUES(300, ‘milan’); – 自动提交 commit

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

3、你也可以在这个事务中(还没有提交时), 创建多个保存点。

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

事务隔离级别

以下定义理解不了的话，可以先理解案例，再来理解定义

1、多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性。(通俗解释)
2、如果不考虑隔离性，可能会引发如下问题：

➢ 脏读(dirty read)：当一个事务读取另一个事务尚未提交的改变时，产生脏读。

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

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

Mysql隔离级别（4种）脏读不可重复读幻读加锁读
读未提交(Read uncommitted) √ √ √ 不加锁
读已提交(Read committed) × √ √ 不加锁
可重复读(Repeatable read) × × × 不加锁
可串行化(Serializable ) × × × 加锁

第一行意思是：Read uncommitted这种隔离级别，会发生脏读、不可重复读、幻读，并且是没有加锁的。

接下来，我们演示一下。

提前在tmp数据库下，创建一个demo表

Mysql隔离级别（4种）	脏读	不可重复读	幻读	加锁读
读未提交(Read uncommitted)	√	√	√	不加锁
读已提交(Read committed)	×	√	√	不加锁
可重复读(Repeatable read)	×	×	×	不加锁
可串行化(Serializable )	×	×	×	加锁

案例：现在张三和李四需要对一个数据库进行操作

隔离级别演示

以读未提交为例

1、打开两个终端，代表张三和李四两个操作者，然后进行准备工作；

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jWAH1r5f-1655011532967)(C:\Users\柠檬-幼稚\AppData\Roaming\Typora\typora-user-images\image-20220419093814642.png)]$

2、隔离级别设置好后，张三开始对数据库进行操作，李四暂时还没有进行操作；

在这里插入图片描述

以上的操作就是脏读，张三对数据进行操作，并没有提交，所以张三的操作是最终结果还是草稿，并没有确定；

3、演示可重复读和幻读

在这里插入图片描述

读未提交演示完了，就可以理解其他的几个演示级别了，所有的隔离级别修改均是在李四。

读已提交(Read committed)：表示张三进行操作，只要他没有提交，李四就看不到他的操作。

可重复读(Repeatable read)：表示张三进行操作，即使他提交了，在李四这边也看不到他的操作，只有李四也操作完并且提交之后，才能看到张三的操作。

可串行化(Serializable ) ：这种是加锁的，就是当张三对标进行操作时，李四可以开启事务，可以使用表，但是李四想要查看、操作表时，就会卡住(超过一定时间就会超时)，因为李四的隔离级别是加锁的，别人操作时，李四不能操作该表，直到张三提交了表，这边才可以进行查询或者操作。

不可重复度和幻读的区别

不可重复度：在一个事务中多次读取同一个数据时，结果出现不一致。重点是修改。

比如在张三添加一个人的信息id=100，操作比没有结束，现在读到的就是id=100；

然后李四对信息进行了修改id=200，提交了事务。这时张三再读信息就是200了，同一条信息，读取结果导致了不可重复读。

幻读：在一个事务中使用相同的表两次读取，第二次读取到了其他事务新插入的行。重点在于新增或者删除。

张三现在读到的信息是10条记录，现在李四向里面添加了一条信息；

这时张三再读取信息时，就是11条信息，这就产生了幻读。

设置隔离级别

-- 查看当前会话隔离级别 
SELECT @@transaction_isolation
-- 查看系统当前隔离级别
SELECT @@global.transaction_isolation
-- 设置当前会话隔离级别为读未提交
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- 设置系统当前隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL [你设置的级别]
-- mysql默认的事务隔离级别是repeatable read ,-般情况下，没有特殊要求，没有必要修改(因为该级别可以满足绝大部分项目需求)

/*
全局修改，修改my.ini配置文件，在最后加上。这个文件怎么找在第一章有提到过
可选参数有: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.
*/

在这里插入图片描述

事务的ACID特性

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

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

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

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

十二、存储引擎

基本介绍

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

2、MySQL 数据表主要支持六种类型,分别是: CSV、Memory、ARCHIVE、MRG_MYISAM、MYISAM、InnoBDB。

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

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

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

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

可以直接在表上面进行可视化修改

在这里插入图片描述

-- 表类型和存储引擎

-- 查看所有的存储引擎
SHOW ENGINES;
-- innodb 存储引擎，是前面使用过.
-- 1. 支持事务 2. 支持外键 3. 支持行级锁


-- myisam 存储引擎
CREATE TABLE t28 (
	id INT,
	`name` VARCHAR(32)) ENGINE MYISAM
-- 1. 添加速度快 2. 不支持外键和事务 3. 支持表级锁

START TRANSACTION;
SAVEPOINT t1
INSERT INTO t28 VALUES(1, 'jack');
SELECT * FROM t28;
ROLLBACK TO t1  -- 没有效果，因为该存储引擎不支持事务

-- memory 存储引擎
-- 1. 数据存储在内存中[关闭了Mysql服务，数据丢失, 但是表结构还在] 
-- 2. 执行速度很快(没有IO读写) 3. 默认支持索引(hash表)

CREATE TABLE t29 (
	id INT,
	`name` VARCHAR(32)) ENGINE MEMORY
DESC t29
INSERT INTO t29
	VALUES(1,'tom'), (2,'jack'), (3, 'hsp');
SELECT * FROM t29

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

如何选择存储引擎

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

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