MySQL索引事务详细解析

目录
  • 一、索引
    • 1.概念
    • 2.作用
    • 3.缺陷
    • 4.使用场景
    • 5.使用
      • 1.查看索引
      • 2.创建索引
      • 3.删除索引
    • 6.案例
  • 二、事务
    • 1.为什么使用事务
    • 2.事务的概念
    • 3.使用
    • 4.特性

一、索引

1.概念

索引是一种特殊的文件,包含着对数据表里所有记录的引用指针,可以对表中的一列或者多列创建索引,并指定索引的类型,各类索引有各自的数据结构实现. (这里对于索引也只是简单了解,写了些皮毛) 更浅显易懂的来说:数据库的数据保存在硬盘,硬盘不知道具体保存在哪个位置,索引就是用来告诉硬盘数据在哪个位置.

2.作用

数据库中的表、数据、索引之间的关系,类似于书架上的图书、书籍内容和书籍目录的关系 索引所起的作用类似书籍目录,可用于快速定位,检索数据 索引对于提高数据库的性能有很大的帮助

说明:某张表可以给一个字段或多个字段创建索引

使用查询语句时,根据索引字段来做条件查询就可能使用到索引,提高查询速度.

某些语句不能使用到索引,比如:

student(id,name,email),name创建索引

where name like ‘%哈哈%’ 和 name is null 不能使用索引

name=‘张三’ 能使用到

3.缺陷

索引需要占用一定的磁盘空间,插入/修改/删除操作,索引也需要更新,数据量越大,索引更新的时间越长 所以说:也不是索引建的越多越好

4.使用场景

要考虑对数据库表的某列或某几列创建索引,需要考虑以下几点:

  • 数据量较大,且经常对这些列进行条件查询
  • 该数据库表的插入操作,及对这些列的修改操作频率较低
  • 索引会占用额外的磁盘空间

满足以上条件时,考虑对表中的这些字段创建索引,以提高查询效率

反之,如果非条件查询列,或经常做插入,修改操作,或磁盘空间不足时,不考虑创建索引

5.使用

创建主键约束(PRIMARY KEY)、唯一约束(UNIQUE)、外键约束(FORGIGN KEY)时,会主动创建对应列的索引.

1.查看索引

show index from 表名;

2.创建索引

对于非主键、非唯一约束、非外键的字段,可以创建普通索引

create index 索引名 on 表名(字段名);

3.删除索引

drop index 索引名 on 表名;

6.案例

实现登录功能,数据库有user表,username(账号),password(密码)

从实现上看:

页面上,用户输入账号密码,Java程序接收到这个账号,这个密码数据库sql的实现,就有两种方式:

(1)根据账号+密码,条件查询

(2)根据账号查询,Java程序获取到这条数据,比较/校验密码

问题:登录功能发现很慢,如何优化?

如果sql是第一种查询方式,创建账号+密码两个字段的索引

第二种查询方式,创建账号一个字段的索引

二、事务

1.为什么使用事务

准备测试表:

drop table if exists accout;
create table accout(
id int primary key auto_increment,
name varchar(20) comment '账户名称',
money decimal(11,2) comment '金额'
);
insert into accout(name, money) values
('图图', 5000),
('小美', 1000);

比如说,小美向图图借了2000元

-- 图图账户减少2000
update accout set money=money-2000 where name = '图图';
-- 小美账户增加2000
update accout set money=money+2000 where name = '小美';

假如在执行以上第一句SQL时,出现网络错误,或是数据库挂掉了,图图的账户会减少2000,但是小美的账户上就没有了增加的金额 解决方案: 使用事务来控制,保证以上两句SQL要么全部执行成功,要么全部执行失败.

2.事务的概念

事务指逻辑上的一组操作,组成这组操作的各个单元,要么全部成功,要么全部失败. 在不同的环境中,都可以有事务.对应在数据库中,就是数据库事务.

3.使用

(1)开启事务:start transaction;

(2)执行多条SQL语句

(3)回滚或提交:rollback/commit

说明:rollback即是全部失败,commit即是全部成功

start transaction;
-- 图图账户减少2000
update accout set money=money-2000 where name = '图图';
-- 小美账户增加2000
update accout set money=money+2000 where name = '小美';
commit;

4.特性

存在acid四大特性(原子性,持久性,一致性,隔离性)

1.原子性:对应一组操作(主要是更新),要么全部成功,要么全部失败

2.一致性:一个事务里边,多次查询到的数据都是一样的

3.隔离性:不同事务,查询/修改的数据,是互相隔离开的 一个事务,没有提交或者回滚前,修改的数据,只有自己看得到

4.持久性:事务提交,会持久化到硬盘中

关于第三点和第四点这里做出图像解释:

mysql> drop table if exists accout;

mysql> create table accout(
    -> id int primary key auto_increment,
    -> name varchar(20) comment '账户名称',
    -> money decimal(11,2) comment '金额'
    -> );

mysql> insert into accout(name, money) values
    -> ('图图', 5000),
    -> ('小美',1000);

mysql> update accout set money=money-2000 where name = '图图';

mysql> update accout set money=money+2000 where name = '小美';

在MySQL中执行上述操作之后没有进行commit结果显示为:

但是在客户端连接MySQL服务器直接查看表格数据并没有变化:

只有在commit完成之后才会客户端才会显示:

到此这篇关于MySQL索引事务详细解析的文章就介绍到这了,更多相关MySQL 索引事务内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • MySQL数据库索引的最左匹配原则

    MySQL数据库索引的最左匹配原则

    目录 一. 联合索引说明 二. 那ac是否能用到索引呢? 三. 思考 四. 最左匹配原则的成因 一. 联合索引说明 建立三个字段的联合索引 联合索引(a,b,c)相当于建立了索引:(a),(a,b),(a,b,c) 二. 那ac是否能用到索引呢? 先给出结论:a可以命中联合索引(a,b,c),c无法命中,所以ac组合无法命中联合索引. 1.建立abc联合索引(province,city,district) ac索引查询 SELECT * FROM user_address WHERE provi

  • Mysql的复合索引如何生效

    目录 背景 认识复合索引 最左匹配原则 字段顺序的影响 复合索引可以替代单一索引吗? 小结 背景 最近频繁出现慢SQL导致系统性能问题,于是决定针对索引进行一些优化.一些表结构本身已经有了不少索引,如果再继续添加索引,势必会影响到插入数据的性能.那么,是否可以使用组合索引来达到目的呢?这篇文章咱们来一探究竟. 认识复合索引 如果where条件中使用到多个字段,并且需要对多个字段建立索引,此时就可以考虑采用复合索引(组合索引).比如查询地址时需要输入省.市,那么在省.市上建立索引,当数据量大时会明

  • MySQL Innodb索引机制详细介绍

    MySQL Innodb索引机制详细介绍

    1.什么是索引 索引是存储引擎用于快速找到记录的一种数据结构. 2.索引有哪些数据结构 顺序查找结构:这种查找效率很低,复杂度为O(n).大数据量的时候查询效率很低. 有序的数据排列:二分查找法又称折半查找法. 通过一次比较,将查找区间缩小一半.而MySQL中的数据并不是有序的序列. 二叉查找树:左子树的键值总是小于根的键值,右子树的键值总是大于根的键值.通过中序遍历得到的序列是有序序列,但如果二叉查找树构造的不好则跟顺序查找没什么区别 平衡二叉树:如果需要二叉查找树是平衡的,从而引出平衡二叉树

  • MySQL索引底层数据结构详情

    MySQL索引底层数据结构详情

    目录 一.索引类型 1.B+树 2.MyISAM和InnoDB的B+树索引实现方式的区别(聚簇索引和非聚簇索引)? 3.非聚簇索引 4.聚簇索引的优缺点 5.哈希索引 6.自适应哈希索引 一.索引类型 1.B+树 为什么是B+树而不是B树? 首先看看B树和B+树在结构上的区别 B树结构: B+树: 可以看到: B树在每个节点上都有卫星数据(数据表中的一行数据),而B+树只在叶子节点上有卫星数据.这意味着相同大小的磁盘扇区,B+树可以存储的叶子节点更多,磁盘IO次数更少:同样也意味着B+树的查找效

  • MySQL数据库之索引详解

    MySQL数据库之索引详解

    目录 一.MySQL索引简介 二.MySQL五种类型索引详解 (一)普通索引 (二)唯一性索引 (三)主键索引 (四)复合索引 (五)全文索引 三.MySQL索引使用原则 总结 今天继续给大家介绍MySQL相关知识,本文主要内容是MySQL索引相关内容. 一.MySQL索引简介 索引是MySQL数据库为了加快数据查询的速度,给表中的某一个或者是某几个列添加的一种"目录".MySQL的索引是一个特殊的文件,但是InnoDB类型引擎(关于MySQL的引擎我们会在今后的文章中进行讲解)的表的

  • 浅谈mysql哪些情况会导致索引失效

    浅谈mysql哪些情况会导致索引失效

    下面有一些培训教学机构的口诀和我个人的一些总结: 为了讲解以下索引内容,我们先建立一个临时的表 test02 CREATE TABLE `sys_user` ( `id` varchar(64) NOT NULL COMMENT '主键', `name` varchar(64) DEFAULT NULL COMMENT '名字', `age` int(64) DEFAULT NULL COMMENT '年龄', `pos` varchar(64) DEFAULT NULL COMMENT '职位

  • MySQL 数据库 索引和事务

    MySQL 数据库 索引和事务

    目录 1. 索引 1.1 概念 1.2 作用 1.3 索引的原理 1.3.1 减少磁盘的访问次数是构建索引的核心思想 1.3.2 B+ 树适用实现索引的底层 1.4 适用场景 1.5 使用语句 1.5.1 查看索引 1.5.2 创建索引 1.5.3 删除索引 2. 事务 2.1 概念 2.2 为什么使用事务 2.3 四大属性 2.3.1 原子性 2.3.2 一致性 2.3.3 持久性 2.3.4 隔离性 2.4 使用方法 1. 索引 1.1 概念 索引是为了加速对表中数据行的检索而创建的一种分散

  • mysql数据库之索引详细介绍

    mysql数据库之索引详细介绍

    目录 思维导图 简单理解 索引模型的演变 二叉查找树 自平衡二叉树 B树 B+树 聚集索引与二级索引 总结 如果你想深入了解为什么mysql可以快速的进行检索数据,那么你一定要来了解一下mysql的索引原理 思维导图 简单理解 你可以把索引理解为一本书的目录,我们可以通过索引快速的找到我们需要的数据,大概就像下面这个图,索引就像是右边的二叉树,每个节点指向具体的数据的物理地址,先通过二叉树找到数据的位置,然后再去物理磁盘中获取数据. 但是不同的二叉树的特性不同,我们还要选择合适的树来作为索引,所

  • MySQL数据库索引order by排序精讲

    MySQL数据库索引order by排序精讲

    排序这个词,我的第一感觉是几乎所有App都有排序的地方,淘宝商品有按照购买时间的排序.B站的评论有按照热度排序的... 对于MySQL,一说到排序,你第一时间想到的是什么?关键字order by?order by的字段最好有索引?叶子结点已经是顺序的?还是说尽量不要在MySQL内部排序? 事情的起因 现在假设有一张用户的朋友表: CREATE TABLE `user` ( `id` int(10) AUTO_INCREMENT, `user_id` int(10), `friend_addr`

  • MySQL索引事务详细解析

    MySQL索引事务详细解析

    目录 一.索引 1.概念 2.作用 3.缺陷 4.使用场景 5.使用 1.查看索引 2.创建索引 3.删除索引 6.案例 二.事务 1.为什么使用事务 2.事务的概念 3.使用 4.特性 一.索引 1.概念 索引是一种特殊的文件,包含着对数据表里所有记录的引用指针,可以对表中的一列或者多列创建索引,并指定索引的类型,各类索引有各自的数据结构实现. (这里对于索引也只是简单了解,写了些皮毛) 更浅显易懂的来说:数据库的数据保存在硬盘,硬盘不知道具体保存在哪个位置,索引就是用来告诉硬盘数据在哪个位置

  • MySQL索引结构详细解析

    MySQL索引结构详细解析

    目录 简介 索引结构(树) 为什么用树,而不用哈希表 BTree索引 B+Tree索引 聚簇索引与非聚簇索引 索引分类 性能分析 索引创建场景 简介 在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法.这种数据结构,就是索引. 一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上. 优点: 1.类似大学图书馆建书目索引,提高数据检索的效率,降低数据库的IO成本. 2.通过

  • Mysql中复制详细解析

    1.mysql复制概念 指将主数据库的DDL和DML操作通过二进制日志传到复制服务器上,然后在复制服务器上将这些日志文件重新执行,从而使复制服务器和主服务器的数据保持同步.复制过程中一个服务器充当主服务器(master),而一个或多个其它服务器充当从服务器(slaves).主服务器将更新重新写入二进制日志文件,并维护文件的一个索引以跟踪日志循环.这些日志可以记录发送到从服务器的更新.当一个从服务器连接主服务器时,它通知主服务器.从服务器在日志中读取的最后一次成功更新的位置.从服务器接受从那时起发

  • MySQL索引下推详细

    MySQL索引下推详细

    目录 1.最左前缀原则 2.回表 3.索引下推 前言: 索引下推(ICP)是针对MySQL使用索引从表中检索数据行的情况的优 在没有索引下推的情况下,MySQL通过存储引擎遍历索引来定位表中的数据行并将它们返回给MySQl服务器,服务器再进行WHERE条件的判断,确认是否将数据行加入结果集. 开启索引下推,且WHERE条件部分可以仅使用索引中的列来评估,这时MySQL服务器会将这部分WHERE条件下推到存储引擎,接着存储引擎使用索引条目评估推送的索引条件,仅当满足该条件时才从表中进行读取 索引下

  • MySQL读写分离原理详细解析

    MySQL读写分离原理详细解析

    目录 一.读写分离的概念 二.引入中间件MyCat 三.MyCat服务端口和管理端口 一.读写分离的概念 读写分离是基于主从复制来实现的.在实际的应用环境中,肯定是读操作多,就像我们在电商平台上去购买东西,可能看了100个也就买了一两个.所以读操作永远比写这种更新操作多很多.所以我们基于主从复制的读写分离配置,就是让一个主库专门用来做数据的修改,写的时候专门在主库上写,主库通过主从复制把数据的更改通过binlog同步到从库上去,那么其他的客户端查询的请求都会最终映射到从库上去,而我们一个主库带上

  • 关于MySQL索引的深入解析

    前言 我们知道,索引的选择是优化器阶段的工作,但是优化器并不是万能的,它有可能选错所要使用的索引.一般优化器选择索引考虑的因素有:扫描行数,是否排序,是否使用临时表. 使用explain分析sql explain是很好的自测命令,勤于使用explain有助于我们写出更合理的sql语句以及建立更合理的索引: mysql> explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) ord

  • 快速了解MySQL 索引

    MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度. 打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车. 索引分单列索引和组合索引.单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引.组合索引,即一个索包含多个列. 创建索引时,你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件). 实际上,索引也是一张表,该表保存了主键与索引字

  • 深入了解mysql索引

    1.索引原理 索引被用来快速找出在一个列上用一特定值的行.没有索引,MySQL不得不首先以第一条记录开始,然后读完整个表直到它找出相关的行.表越大,花费时间越多.对于一个有序字段,可以运用二分查找(Binary Search),这就是为什么性能能得到本质上的提高.MYISAM和INNODB都是用B+Tree作为索引结构 (主键,unique 都会默认的添加索引) 2.创建索引 如果未使用索引,我们查询 工资大于 1500的会执行全表扫描 什么时候需要给字段添加索引: -表中该字段中的数据量庞大

  • MySQL索引机制的详细解析及原理

    MySQL索引机制的详细解析及原理

    目录 一.索引的类型与常见的操作 二.常见的索引详解与创建 三.索引的原理 1.通过实验介绍B+tree 2.延伸 四.聚簇索引和非聚簇索引 1.使用聚簇索引的优势 2.什么情况下无法使用索引 总结 一.索引的类型与常见的操作 前缀索引 MySQL 前缀索引能有效减小索引文件的大小,提高索引的速度.但是前缀索引也有它的坏处:MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引,也不能把它们用作覆盖索引(Covering Index). 复合索引 集一个索引包含多个列(最左前

  • MySQL索引详细解析

    MySQL索引详细解析

    目录 1. MySQL 索引的最左前缀原则 2. 前缀索引 3. 索引下推(ICP——Index Condition Pushdown) 4. 查看 MySQL 语句是否用到索引 5. 为什么官方建议用自增长主键作为索引 6. 如何创建索引 7. 创建索引注意事项 8. 使用索引一定可以提高查询性能嘛 9. 索引失效 1. MySQL 索引的最左前缀原则 左前缀原则是联合索引在使用时要遵循的原则,查询索引可以使用联合索引的一部分,但是必须从最左侧开始.在创建联合索引时,要根据业务需求,where

随机推荐