mysql的事务、索引_mysql、事务、索引-CSDN博客

本文链接：https://blog.csdn.net/qq_36116242/article/details/114308729

1、事务的概述

事务：一批操作要么同时成功，要么同时失败

事物是为了一批操作业务的正确性保证。

事务的四大特性

事务特性	含义
原子性（Atomicity）	所有的SQL操作是一个整体，不可再拆分。事务是应该是最小的执行单元。
一致性（Consistency）	事务执行前，执行后。数据库的状态应该是一致的。如：转账前2个人总额与转换后2个人总额应该是一样的。
隔离性（Isolation）	事务是可以并发执行的，理想的情况应该是所有的事务之间不能相互影响。
持久性（Durability）	如果事务对数据库进行了操作，事物一旦成功或者失败，对数据库中数据影响是持久的。

事务的应用场景说明

转账的操作

-- 创建数据表
CREATE TABLE account (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(10),
balance DOUBLE
);

-- 添加数据
INSERT INTO account (name, balance) VALUES ('Jack', 1000), ('Rose', 1000);

转账需求

模拟Jack给Rose转500元钱，一个转账的业务操作最少要执行下面的2条语句：

-- 创建数据表
CREATE TABLE account (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(10), -- 名字
balance DOUBLE -- 余额
);

-- 添加数据
INSERT INTO account (name, balance) VALUES ('Jack', 1000), ('Rose', 1000);
select * from account;

-- 如果要完成Jack转账给Rose : 500
-- 至少2条，Jack扣钱，Rose加钱，要有2条update语句
update account set balance = balance - 500 where name='Jack';
update account set balance = balance + 500 where name='Rose';

-- 还原成1000
update account set balance = 1000;

假设当Jack账号上-500元,服务器崩溃了。Rose的账号并没有+500元，数据就出现问题了。我们需要保证其中一条SQL语句出现问题，整个转账就算失败。

只有两条SQL都成功了转账才算成功。这个时候就需要用到事务。

2 、事务：手动提交

两种方式进行事务的操作

手动提交事务
默认是自动提交事务

手动提交事务的SQL语句

功能	SQL语句
开启事务	start transaction
提交事务	commit
回滚事务	rollback

手动提交事务使用过程

开启事务
执行SQL语句
要么提交事务，要么回滚事务

3 、事务：取消全局自动提交

在默认的情况下，mysql每条SQL语句都会创建一个事务

取消自动提交

查看MySQL是否开启自动提交事务
取消自动提交事务
执行更新语句，查看数据库，发现数据并没有改变，在控制台执行commit提交任务

4、事务的回滚点和执行原理

什么是回滚点

上面的操作，如果回滚，直接回滚到事务开始前。有时我们并不需要回滚到最开始的状态，可能只需要回滚到中间的某个位置，就可以设置回滚点。

语句

回滚点的操作语句	语句
设置回滚点	savepoint 名字
回到回滚点	rollback to 名字

操作

将数据还原到1000
开启事务
让Jack账号减2次钱，每次10块
设置回滚点：savepoint two_times;
让Jack账号减2次钱，每次10块
回到回滚点：rollback to two_times;
最后commit提交事务

原理说明

一个用户登录成功以后，服务器会创建一个临时日志文件。日志文件用来保存用户事务状态。
如果没有使用事务，则所有的操作直接写到数据库中，不会使用日志文件。
如果开启事务，将所有的写操作写到日志文件中。
如果这时用户提交了事务，则将日志文件中所有的操作写到数据库中。
如果用户回滚事务，则日志文件会被清空，不会影响到数据库的操作。

5 、事务的隔离级别和并发访问的问题

并发访问数据的问题

什么是事务的并发访问：因为一张表可以同时有多个用户在访问，相互之间会有影响。例如一堆人来进行商品的秒杀。会出现各种并发访问共享数据的问题。一张账户表，可能同时有很多人访问转账。

四种隔离级别和可能出现的问题

并发事务处理会带来一些问题，所以事物与事物之间需要进行适当的隔离，但是隔离是存在级别的，并不是隔离的越高级越好，隔离越高级，安全性越高，但是性能越差!

数据库事物的隔离级别其实是安全与性能的折中：做了一个事物的隔离级别。

事物有四种隔离级别:

读未提交：出现脏读（一个事务读到了其他事务未提交的数据）。
读已提交：出现不可重复读（可以避免脏读，一个事物多次读取到别人已提交的数据）。
可重复读：出现幻读（可以重复读，可以实现一个事物多次读取到的是一样的数据，会出现幻读）。
串行化: 不会出现以上任何问题。

6、 MySQL的索引

索引是用于提高数据检索的技术。合理的使用索引可以大大降低 IO，从而提高数据访问性能。

数据库查询数据方式

1) 普通查询数据方式：从数据表第一条位置开始查询,进行逐条查询,直至查找到最后一条,将所有符合的数据找到.

2) 索引查询数据方式：先从索引表中查询，定位到大致的区间，再查真实的表。

索引的缺点：索引本身是要占数据库空间的，而且添加数据的时候要维护索引表。

创建索引语法

创建索引

单列索引是基于单个列所建立的索引:

create index 索引名 on 表名(列名)

查看表中的索引

SHOW INDEX FROM 表名

删除索引

DROP INDEX 索引名 ON 表名

复合索引

create index 索引名 on 表名(列名1,列名2);

索引的使用原则

1) 在数据量大的表上建立索引才有意义==(最起码百万级别数据才会考虑索引)==

2) 在经常查询的字段上使用索引，用在 where子句后面或者是连接条件上的字段建立索引

3) 表中数据修改频率高时，不建议建立

索引的不足

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，==MySQL不仅要==保==存数据，还要保存一下索引文件。==

==建立索引会占用磁盘空间的索引文件。==

索引（每插入、或者修改一条记录mysql都要维护索引的顺序,这个过程不需要我们参与，但是非常消耗性能）

索引失效的几种情况

1) 如果条件中有or，即使其中有条件带索引也不会使用。(例外：or两边条件都是同一个字段 )

2) 模糊like查询已 '%xxx'开头的不使用索引，以'xxx%'结尾会使用索引。

3) where语句中使用不等于 <>和 != 不使用索引

4) 如果mysql估计使用全表扫描要比使用索引快，则不使用索引。

7、mysql的内置函数

字符串函数

函数	描述	实例
char_length(s)	返回字符串 s 的字符数	SELECT CHAR_LENGTH("NewBoy") AS 长度;
concat(s1,s2...sn)	字符串 s1,s2 等多个字符串合并为一个字符串	SELECT CONCAT("SQL ", "itcast ", "Gooogle ", "Facebook")
lower(s)	将字符串 s 的所有字母变成小写字母	SELECT LOWER('NEWBOY')
uppper(s)	将字符串转换为大写	SELECT UPPER("newboy");
substr(s, start, length)	从字符串 s 的 start 位置截取长度为 length 的子字符串，从1开始计数	SELECT SUBSTR("Hello World", 7, 3);
trim(s)	去掉字符串 s 开始和结尾处的空格	SELECT TRIM(' itheima ')
replace(字符串, 源字符串,新字符串)	将字符串中的源字符串换成新的字符串	SELECT REPLACE('abcde','bc','xyz');

数学函数

函数	说明	案例
RAND()	返回 0 到 1 的随机数	SELECT RAND()
ROUND(小数,保留几位)	四舍五入保留几位小数	SELECT ROUND(1.23456, 3)

日期函数

函数	说明	案例
addDate(d,n)	计算起始日期 d 加上 n 天的日期	SELECT ADDDATE("2017-06-15", 10);
curDate()	返回当前日期	SELECT CURDATE();
dateDiff(d1,d2)	计算日期 d1->d2 之间相隔的天数	SELECT DATEDIFF('2001-01-01','2001-02-02')
now()	返回当前日期和时间	SELECT NOW()
year(日期)	获取指定日期的年份	SELECT YEAR(NOW())

示例代码

-- 统计每个员工入职的天数
SELECT e.ename, DATEDIFF(CURDATE() ,e.joindate) FROM emp e ;

-- 统计每个员工的工龄
SELECT e.ename, ROUND(DATEDIFF(CURDATE(),e.joindate)/365, 0) FROM emp e ;

-- 查询2001年入职的员工
SELECT * FROM emp e WHERE YEAR(e.`joindate`)=2001;

-- 统计入职19年以上的员工有多少个
SELECT * FROM emp e WHERE DATEDIFF(CURDATE(),e.joindate)/365 >19;

case 高级函数

-- case表达式语法1
select case 字段
   when 值1 then 返回的值
   when 值2 then 返回的值
   ...
   else
上面都不符合返回的值
end 列名
from 表名

-- case表达式语法2
select case
   when 判断条件1 then 返回的值
   when 判断条件1 then 返回的值
   ...
   else
上面条件都不成立返回的值
end
from 表名

-- case表达式功能
实现分支条件判断，与java的switch结构类似，
当字段的值与when的值匹配时返回 then 后面值，都不符合返回else的值