【MySQL】3、MySQL的索引、事务、存储引擎

create table class (id int not null,name char(10),score decimal(5,2));
insert into class values (1,'zhangsan',80.5);
update class set name='wangwu',passwd='123' where id=2;
select * from class where id=2;
drop

索引的概念

是一种帮助系统,能够更快速的查询信息的结构

作用 

数据库利用各种快速定位技术,能够大大加快查询速率
当表很大或涉及到多个表时,可以成千上万倍地提高查询速度
可以降低数据库的IO成本,并且还可以降低数据库的排序成本
通过创建唯一性索引保证数据表数据的唯一性
可以加快表与表之间的连接
在使用分组和排序时,可以减少分组和排序的时间

副作用

1.索引需要占用额外的磁盘空间对于MyISAM引擎而言,索引文件和数据文件是分离的,索引文件用于保存数据记录的地址。而InnoDB引擎的表数据文件本身就是索引文件。当表很大或查询涉及到多个表时,可以成千上万倍地提高查询速度2.在插入和修改数据时要花费更多时间,因为索引也要随之变动

索引是一个排序的列表,存储着索引值和这个值所对应的物理地址

通过物理地址就可以找到所需数据,不必对整个表扫描

是表中一列或者若干列值排序的方法

需要额外的磁盘空间

创建索引的规则

mysql的优化,哪些字段/场景适合创建索引,哪些不适合?

1.小字符
2.唯一性强的字段
3.更新不频繁,但查询表比较高的字段
4.表记录超过300行
5.主键、外键、唯一键
 创建索引的原则依据
表的主键、外键必须有索引
记录数据超过300行的表应该有索引
经常与其他表进行连接的表,在连接字段上应该建立索引唯一性太差的字段不适合建立索引
更新太频繁地字段不适合创建索引
经常出现在where子句中的字段,特别是大表的字段,应该建立索引索引应该建立在选择性高的字段上索引应该建在小字段上,对于大的文本字段甚至超长字段不要建索引

索引的分类

普通索引:针对所有字段,没有特殊的需求和规则

唯一索引:针对唯一字段,仅允许出现一次空值

组合索引:多列/多字段组合形式的索引,按照组合中的排序进行索引,否则无效

全局索引:

主键索引:针对唯一字段,且不可为空,同一张表只允许有一个主键索引

全文索引:fulltext,通过varchar、char、text、blob、clob检索内部信息来做字段的索引

 创建方式:
三种:1.直接创建索引,并指向索引的字段
create index 索引名 on 表名(类名);
2.通过修改表的字段来添加索引
alter table 表名 add index 索引名
3.创建表时,直接指定索引
create table 表名 (id int not null,name char (10),score decimal(5,2), passwd varchar(48),primary key (id),index 索引名 (name));
查看表的配置信息
show create table 表名;

普通索引:可以直接创建,没有唯一性的限制;针对所有的字段做索引,没有特殊的需求和规则

直接创建索引
create index in_name on test(name);
修改表方式创建
ALTER TABLE 表名 ADD INDEX 索引名 (列名);alter table test add index in_phone(phone);
创建表的时候指定索引
CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...],UNIQUE 索引名 (列名));create table test (id int,name char(10),cardid int(18),phone int(11),address varchar(50),remark text,index in_name(name));

唯一性索引:

与普通索引基本相同,索引列的所有值只能出现一次,必须唯一;

与普通索引类似,但区别是唯一索引列的每个值都唯一。唯一索引允许有空值(注意和主键不同)。
如果是用组合索引创建,则列值的组合必须唯一。添加唯一键将自动创建唯一索引。
直接创建唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名(列名);create unique index in_cardid on test(cardid);
 修改表方式创唯一索引
ALTER TABLE 表名 ADD UNIQUE 索引名 (列名);alter table test add unique in_phone(phone);
创建表的时候指定唯一索引
CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...],UNIQUE 索引名 (列名));create table test (id int(10),name varchar(10),cardid int(18),phone int(11),address varchar(50),remark text,unique in_id(id));

主键索引(unique)

是一种特殊的唯一索引,必须指定为“PRIMARY KEY”。一个表只能有一个主键,不允许有空值。 添加主键将自动创建主键索引。

1.创建表的时候指定索引
CREATE TABLE 表名 ([...],PRIMARY KEY (列名));create table test1 (id int(10),name varchar(10),cardid int(18),phone int(11),address varchar(50),remark text,primary key(id));
 2.修改表方式创建主键索引
ALTER TABLE 表名 ADD PRIMARY KEY (列名); alter table test add primary key(id);

组合索引

可以是单列上创建的索引,也可以是在多列上创建的索引。
需要满足最左原则,因为 select 语句的 where 条件是依次从左往右执行的,
所以在使用 select 语句查询时 where 条件使用的字段顺序必须和组合索引中的排序一致,否则索引将不会生效。
直接创建组合索引
CREATE TABLE 表名 (列名1 数据类型,列名2 数据类型,列名3 数据类型,INDEX 索引名 (列名1,列名2,列名3));
create index in_sum on test(name,cardid,phone);select 查询时 where 语句中的条件字段 要与组合索引的字段排列顺序一致(最左原则)
select * from 表名 where 列名1='...' AND 列名2='...' AND 列名3='...';
修改表方式创建组合索引
alter table 表名 add index 索引名 (字段1,字段2,字段3,...);  alter table test1 add index in_sum1(name,phone,cardid);
创建表的时候指定组合索引
CREATE TABLE 表名 (列名1 数据类型,列名2 数据类型,列名3 数据类型,INDEX 索引名 (列名1,列名2,列名3)); 
create table test2 (id int(10),name varchar(10),cardid int(18),phone int(11),address varchar(50),remark text,index in_sum2(id,cardid,phone));

全文索引(fulltext)

直接创建索引
CREATE FULLTEXT INDEX 索引名 ON 表名 (列名);create fulltext index in_remark on test(remark);
修改表方式创建
alter table test1 add fulltext in_remark(remark);
创建表的时候指定索引
CREATE TABLE 表名 (字段1 数据类型[,...],FULLTEXT 索引名 (列名));
#数据类型可以为 CHAR、VARCHAR 或者 TEXTcreate table test2 (id int(10),name varchar(10),cardid int(18),phone int(11),address varchar(50),remark text,fulltext in_full(cardid));
使用全文索引查询
SELECT * FROM 表名 WHERE MATCH(列名) AGAINST('查询内容');select * from test1 where match(remark) against('vip');
select * from test1 where remark='vip';

查看索引

1.show index from 表名;show index from 表名\G;            竖向显示表的索引信息
2.show keys from 表名;show keys from 表名\G;             

删除索引的方法

直接删除索引
drop index 索引名 on 表名;drop index in_name on test;
修改表的方式删除索引
alter table 表名 drop index 索引名;alter table test drop index in_phone;
删除主键索引
alter table 表名 drop primary key;alter table test drop primary key;

事务

事物的概念

ACID,是指在可靠数据库管理系统(DBMS)中,事务(transaction)应该具有的四个特性:原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)。这是可靠数据库所应具备的几个特性。

事务的ACID特点

原子性

指事务是一个不可再分割的工作单位,事务中的操作要么都发生,要么都不发生。

事务是一个完整的操作,事务的各元素是不可分的。
事务中的所有元素必须作为一个整体提交或回滚。
如果事务中的任何元素失败,则整个事务将失败。

案例:

A转账B,A扣款后突然断电断网,B没能收到加款,就会引起纠纷

一致性

指在事务开始之前和事务结束以后,数据库的完整性约束没有被破坏。

当事务完成时,数据必须处于一致状态。
在事务开始前,数据库中存储的数据处于一致状态。
在正在进行的事务中,数据可能处于不一致的状态。
当事务成功完成时,数据必须再次回到已知的一致状态。

案例:

对银行转帐事务,不管事务成功还是失败,应该保证事务结束后表中A和B的存款总额跟事务执行前一致。

隔离性

指在并发环境中,当不同的事务同时操纵相同的数据时,每个事务都有各自的完整数据空间。

对数据进行修改的所有并发事务是彼此隔离的,表明事务必须是独立的,它不应以任何方式依赖于或影响其他事务。
修改数据的事务可在另一个使用相同数据的事务开始之前访问这些数据,或者在另一个使用相同数据的事务结束之后访问这些数据。
也就是说并发访问数据库时,一个用户的事务不被其他事务所干扰,各并发事务之间数据库是独立的

 事务之间的相互影响分为几种
当多个客户端并发地访问同一个表时,可能出现下面的一致性问题:
(1)脏读:读取未提交数据当一个事务A正在访问数据X,并且对数据X进行了修改,而这种修改还没有提交到数据库中,这时,另外一个事务B也访问这个数据X(被修改的),然后使用了这个数据,如果A不提交,那么数据就会发生回滚。
(2)不可重复读:前后多次读取,数据内容不一致指在一个事务内,多次读同一数据。在这个事务还没有结束时,另外一个事务也访问该同一数据。那么,在第一个事务中的两次读数据之间,由于第二个事务的修改,那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的,因此称为是不可重复读。(即不能读到相同的数据内容)
(3)幻读:前后多次读取,数据总量不一致一个事务对一个表中的数据进行了修改,这种修改涉及到表中的全部数据行。同时,另一个事务也修改这个表中的数据,这种修改是向表中插入一行新数据。那么,操作前一个事务的用户会发现表中还有一个没有修改的数据行,就好象发生了幻觉一样。
(4)丢失更新:两个事务同时读取同一条记录,A先修改记录,B也修改记录(B不知道A修改过),B提交数据后B的修改结果覆盖了A的修改结果。
事务的隔离级别决定了事务之间可见的级别。
MySQL事务支持如下四种隔离,用以控制事务所做的修改,并将修改通告至其它并发的事务:
(1)未提交读(Read Uncommitted(RU)):
允许脏读,即允许一个事务可以看到其他事务未提交的修改。
安全性最差,性能最好(不使用)(2)提交读(Read Committed(RC)):
允许一个事务只能看到其他事务已经提交的修改,未提交的修改是不可见的。防止脏读。
安全性较差,性能较好(3)可重复读(Repeatable Read(RR)):---mysql默认的隔离级别
确保如果在一个事务中执行两次相同的SELECT语句,都能得到相同的结果,不管其他事务是否提交这些修改。可以防止脏读和不可重复读。
安全性较高,性能较差(4)串行读(Serializable):---相当于锁表
完全串行化的读,将一个事务与其他事务完全地隔离。每次读都需要获得表级共享锁,读写相互都会阻塞。可以防止脏读,不可重复读取和幻读,(事务串行化)会降低数据库的效率。
安全性最高,性能最差(不使用)mysql默认的事务处理级别是 repeatable read ,而Oracle和SQL Server是 read committed 。
事务隔离级别的作用范围分为两种
全局级:对所有的会话有效
会话级:只对当前的会话有效
1.查询全局事务隔离级别:
show global variables like '%isolation%';
SELECT @@global.tx_isolation;2.查询会话事务隔离级别:
show session variables like '%isolation%';
SELECT @@session.tx_isolation; 
SELECT @@tx_isolation;3.设置全局事务隔离级别:
set global transaction isolation level read committed;
set @@global.tx_isolation='read-committed';   #重启服务后失效4.设置会话事务隔离级别:
set session transaction isolation level repeatable read;
set @@session.tx_isolation='repeatable-read';
持久性

在事务完成以后,该事务所对数据库所作的更改便持久的保存在数据库之中,并不会被回滚。

指不管系统是否发生故障,事务处理的结果都是永久的。
一旦事务被提交,事务的效果会被永久地保留在数据库中。

总结:在事务管理中,原子性是基础,隔离性是手段,一致性是目的,持久性是结果。

事务控制语句

BEGIN 或 START TRANSACTION:显式地开启一个事务。
COMMIT 或 COMMIT WORK:提交事务,并使已对数据库进行的所有修改变为永久性的。
ROLLBACK 或 ROLLBACK WORK:回滚会结束用户的事务,并撤销正在进行的所有未提交的修改。
SAVEPOINT S1:使用 SAVEPOINT 允许在事务中创建一个回滚点,一个事务中可以有多个 SAVEPOINT;“S1”代表回滚点名称。
ROLLBACK TO [SAVEPOINT] S1:把事务回滚到标记点。
#查看当前的隔离级别
show session variables like '%isolation%';修改隔离级别
set session transaction isolation level read committed;

使用set设置控制事务

SET AUTOCOMMIT=0;                        #禁止自动提交
SET AUTOCOMMIT=1;                        #开启自动提交,Mysql默认为1
SHOW VARIABLES LIKE 'AUTOCOMMIT';        #查看Mysql中的AUTOCOMMIT值如果没有开启自动提交,当前会话连接的mysql的所有操作都会当成一个事务直到你输入rollback|commit;当前事务才算结束。当前事务结束前新的mysql连接时无法读取到任何当前会话的操作结果。
如果开起了自动提交,mysql会把每个sql语句当成一个事务,然后自动的commit。
当然无论开启与否,begin; commit|rollback; 都是独立的事务。
#查看事务的自动提交功能是否开启
show variables like 'autocommit';
#默认是开启的
1.测试提交事务
begin;
update info set money=money - 100 where name='A';
select * from info;commit;
quitmysql -u root -p
use school;
select * from info;
2.测试回滚事务
begin;
update info set money=money + 100 where name='A';
select * from info;rollback;
quitmysql -u root -p
use school;
select * from info;
3.测试多点回滚
begin;你
update info set money=money + 100 where name='A';
select * from info;
savepoint s1;update info set money=money + 100 where name='B';
select * from info;
savepoint s2;insert into info values (3,'C',1000);select * from info;
rollback to s1;
select * from info;
4.使用set设置控制事务
#禁止自动提交
SET AUTOCOMMIT=0;
#开启自动提交,Mysq1默认为1
SET AUTOCOMMIT=1;
#个Mvsgl的AUTOCOMMIT值
SHOW VARIABLES LIKE AUTOCOMMIT';如果没有开启自动提交,当前会话连接的mysql的所有操作都会当成一个事务直到你输入rollback commit;当前事务才算结束。当前事务结束前新的mysql连接时无法读取到任何当前会话的操作结果。
如果开起了自动提交,mysql会把每个sql语句当成一个事务,然后自动的commit。
当然无论开启与否,begin;commit、rollback;都是独立的事务

存储引擎

(1)静态(固定长度)表
静态表是默认的存储格式。静态表中的字段都是非可变字段,这样每个记录都是固定长度的;
优点:存储非常迅速,容易缓存,出现故障容易恢复;
缺点:占用的空间通常比动态表多。
(2)动态表
动态表包含可变字段,记录不是固定长度的;
这样存储的优点是占用空间较少,但是频繁的更新、删除记录会产生碎片,需要定期执行 OPTIMIZE TABLE 语句或 myisamchk -r 命令来改善性能,并且出现故障的时候恢复相对比较困难。
(3)压缩表
压缩表由 myisamchk 工具创建,占据非常小的空间,因为每条记录都是被单独压缩的,所以只有非常小的访问开支。

常用存储引擎

InnoDB:支持事务、外键约束,支持行级锁定(在全表扫描时仍然表级锁定);
读写并发能力较好,在5.5版本才支持全文索引,缓存能力较好可以减少磁盘IO的压力,数据和索引是存储在一个文件中。
使用场景:适用于一致性要求较高,数据频繁更新,高并发读写的业务场景。MyISAM:不支持事务、外键约束,只支持表级锁定;
适合单独的查询或写入,读写并发能力较差,支持全文索引,占用资源较小适合硬件资源相对比较差的服务器使用,数据和索引是分开存储的。
使用场景:适用于不需要事务处理,单独的查询或插入数据的业务场景。
查看系统支持的存储引擎
show engines;
查看表使用的存储引擎
方法一:
show table status from 库名 where name='表名'\G方法二:
use 库名;
show create table 表名;
修改存储引擎
方法一:
通过 alter table 修改
use 库名;
alter table 表名 engine=MyISAM;eg:
alter table member engine=myisam;
show create table member;

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/114123.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

思乐直播系统短视频直播系统源码 直播短视频平台系统APP源码多功能后台系统

思乐直播系统,集直播、短视频等功能,根据市场趋势开发并推出思乐直播APP,APP功能丰富且可在后台管理系统进行配置,做到按需求来开启功能。APP使用起来方便快捷,随时随地开启直播、分享短视频。 整个系统具备非常完善、…

Docker部署(5)——使用docker run命令部署运行jar项目

对于一些简单的单体项目,可以使用 docker run 命令可以直接在命令行中运行容器,无需事先构建镜像。这相较于之前使用的 dockerfile 文件来运行部署项目相当于是另外一种简单的部署方法,关于之前使用dockerfile 文件来运行部署这种方法&#x…

错误:依赖检测失败: mysql-community-libs(x86-64) >= 5.7.9 被 (已安裝) mysql-community-li

错误: 错误原因:没有删除之前安装的依赖问题 解决办法: yum remove mysql-libs 再用下面指令检查一遍: rpm -qa | grep mysql 如果有还未清理的,用下面指令: rpm -e xxx

分布式定时任务框架Quartz总结和实践(2)—持久化到Mysql数据库

本文主要介绍分布式定时任务框架Quartz集成SpringBoot持久化数据到Mysql数据库的操作,上一篇文章使用Quartz创建定时任务都是保存在内存中,如果服务重启定时任务就会失效,所以Quartz官方也提供将定时任务等信息持久化到Mysql数据库的功能&…

C语言数值表示——进制、数值存储方式

进制 进制也就是进位制,是人们规定的一种进位方法对于任何一种进制—X进制,就表示某一位置上的数运算时是逢X进一位 十进制是逢十进一,十六进制是逢十六进一,二进制就是逢二进一,以此类推,x进制就是逢x进位…

如何提高工业网关的数据传输速度?

工业网关是工业物联网系统中不可或缺的设备,提高工业网关的数据采集、传输速度,是保障和优化物联网系统运营效率的基础。如何提高工业物联网关的数据传输速度?本篇就为大家简单介绍一下。 1、选用高品质网络设备 选用具有足够带宽容量的高质…

项目实践:类平面抓取点计算(占位,后面补充)

文章目录 文章目录:3D视觉个人学习目录微信:dhlddxB站: Non-Stop_

深入浅出:手把手教你实现顺序表

一、什么是顺序表 顺序表是一种数据结构,或者说,是数据在内存中存储和管理的一种方式。顺序表要求每个数据要从第一个位置开始,依次挨着放。这就很适合使用C语言中的数组来实现。 很多朋友可能会觉得,那有啥可以讲的?我…

Qt网络通信——获取本机网络信息

查询一个主机的MAC地址或者IP地址是网络应用中常用到的功能&#xff0c;Qt提供了QHostInfo和QNetworkInterface 类可以用于此类信息的查询 1.QHostInfo 类&#xff08;显示和查找本地的信息&#xff09;是的主要函数 类别 函数原型作用公共函数QList <QHostAdress> addr…

SpringBoot项目配置文件数据库用户名密码加密

1、需求 在使用SpringBoot开发过程中&#xff0c;会将一些敏感信息配置到SpringBoot项目的配置文件中(不考虑使用配置中心的情况 )&#xff0c;例如数据库的用户名和密码、Redis的密码等。为了保证敏感信息的安全&#xff0c;我们需要将此类数据进行加密配置。 2、操作步骤 …

docker 部署springboot(成功、截图)

1.新建sringboot工程并打包 2.编写Dockerfile文件 # 基础镜像使用java FROM openjdk:8 # 作者 MAINTAINER feng # VOLUME 指定了临时文件目录为/tmp。 # 其效果是在主机 /var/lib/docker 目录下创建了一个临时文件&#xff0c;并链接到容器的/tmp VOLUME /tmp # 将jar包添加…

探秘二叉树后序遍历:从叶子到根的深度之旅

本篇博客会讲解力扣“145. 二叉树的后序遍历”的解题思路&#xff0c;这是题目链接。 本题的思路是&#xff1a; 先创建一个数组&#xff0c;用来存储二叉树后序遍历的结果。数组的大小跟树的结点个数有关。树的结点个数可以使用递归实现&#xff0c;即总个数左子树结点个数右…

Ansible

目录 Ansible简介 ansible 环境安装部署 #管理端安装 ansible //ansible 目录结构 //配置主机清单 //配置密钥对验证 ansible 命令行模块 1&#xff0e;command 模块 2&#xff0e;shell 模块 3&#xff0e;cron 模块 4&#xff0e;user 模块 5&#xff0e;group 模块 6&am…

人生的回忆

回忆是人类宝贵的精神财富&#xff0c;它们像一串串珍珠&#xff0c;串联起我们生活中的每一个片段。 回忆是时间的见证者&#xff0c;它们承载着我们成长、经历、悲欢离合的点点滴滴。 回忆让我们重温过去的欢笑与眼泪&#xff0c;感受那些已经逝去的时光。它们就像一本翻开的…

Caffine和Guava的refreshAfterWrite的异同

背景: guava和caffine的refreshAfterWrite方法在用于本地缓存的场景是非常常用的&#xff0c;本文通过例子列举下caffine的refreshAfterWrite方法和guava的refreshAfterWrite的相同点和不同点 相同点/不同点&#xff1a; 以下都是使用keyXYZ作为例子 场景1&#xff1a;一开…

时序预测 | MATLAB实现基于QPSO-BiGRU、PSO-BiGRU、BiGRU时间序列预测

时序预测 | MATLAB实现基于QPSO-BiGRU、PSO-BiGRU、BiGRU时间序列预测 目录 时序预测 | MATLAB实现基于QPSO-BiGRU、PSO-BiGRU、BiGRU时间序列预测效果一览基本描述程序设计参考资料 效果一览 基本描述 1.时序预测 | MATLAB实现基于QPSO-BiGRU、PSO-BiGRU、BiGRU时间序列预测&a…

基于沙猫群算法优化的BP神经网络(预测应用) - 附代码

基于沙猫群算法优化的BP神经网络&#xff08;预测应用&#xff09; - 附代码 文章目录 基于沙猫群算法优化的BP神经网络&#xff08;预测应用&#xff09; - 附代码1.数据介绍2.沙猫群优化BP神经网络2.1 BP神经网络参数设置2.2 沙猫群算法应用 4.测试结果&#xff1a;5.Matlab代…

NFTScan | 08.21~08.27 NFT 市场热点汇总

欢迎来到由 NFT 基础设施 NFTScan 出品的 NFT 生态热点事件每周汇总。周期&#xff1a;2023.08.21~ 2023.08.27 NFT Hot News 01/ NFT 品牌体验平台 Recur 将于 11 月 16 日彻底关闭&#xff0c;此前曾获 5000 万美元融资 8 月 21 日&#xff0c;NFT 品牌体验平台 Recur 在 X…

mall :rabbit项目源码解析

文章目录 一、mall开源项目1.1 来源1.2 项目转移1.3 项目克隆 二、RabbitMQ 消息中间件2.1 rabbit简介2.2 分布式后端项目的使用流程2.3 分布式后端项目的使用场景 三、安装RabbitMQ(Win10)3.1安装erLang语言&#xff0c;配置环境变量3.2 安装RabbitMQ服务端3.3 测试安装效果 四…

海康机器人工业相机SDK MVS安装教程

文章目录 一. 海康机器人介绍二. 工业相机客户端安装教程 一. 海康机器人介绍 海康机器人是面向全球的机器视觉和移动机器人产品及解决方案提供商&#xff0c;业务聚焦于工业物联网、智慧物流和智能制造&#xff0c;构建开放合作生态&#xff0c;为工业和物流领域用户提供服务…