事务和函数索引

事务

事务的定义

        事务（Transaction），就是将一组SQL语句放在同一批次内去执行，如果一个SQL语句出错，则该批次内 的所有SQL都将被取消执行。

事务的特点

        一个事务中如果有一个数据库操作失败，那么整个 事务的所有数据库操作都会失败，数据库数据就会回滚到该事务开始之前的状态。

事务的限制

MySQL数据库中仅InnoDB和BDB类型的数据库表 支持事务

事务的ACID原则

1、原子性：意味着数据库中的 事务执行是作为原子粒度。即不可再分，整个语句要么 执行，要么不执行

2、一致性：即在事务开始之前和 事务结束以后，数据 库的完整性约束没有 被破坏。

3、隔离性：事务的执行是互不 干扰的，一个事务 不可能看到其他事 务运行时，中间某 一时刻的数据。

4、持久性：意味着在事务完成以后 ，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会 被回滚。

事务的原子性、一致性、持久性

        事务的原子性、一致性和持久性由事务的 redo 日志和undo 日志来保证。

REDO LOG(重做日志）

        REDO LOG 称为 重做日志 ，提供再写入操作，恢复提交事务修改的页操作，用来保证事务的持久性。

UNDO LOG（回滚日志）

        UNDO LOG 称为 回滚日志 ，回滚行记录到某个特定版本，用来保证事务的原子性、一致性。

事务的隔离性

        通常数据库里都是采用锁的机制，保证事务之间的隔离性。

MySql中的锁

锁的分类

1、基于锁的属性分类：共享锁(读锁、S锁)、排他锁(写锁、X锁)

2、基于锁的粒度分类：表锁、行锁(记录锁、间隙锁、临键锁)

3、基于锁的状态分类：意向共享锁、意向排它锁。

事务并发时出现的问题

1.脏写（ Dirty Write ）

        对于两个事务 Session A、Session B，如果事务Session A 修改了 另一个 未提交 事务Session B 修改过 的数据，那就意味着发生了 脏写

2.脏读（ Dirty Read ）

        对于两个事务 Session A、Session B，Session A 读取 了已经被 Session B 更新 但还 没有被提交 的字段。之后若 Session B 回滚 ，Session A 读取 的内容就是 临时且无效 的

3.不可重复读（ Non-Repeatable Read ）

        对于两个事务Session A、Session B，Session A 读取 了一个字段，然后 Session B 更新 了该字段。 之后Session A 再次读 同一个字段， 值就不同了。那就意味着发生了不可重复读。注意，不可重复读不一定就是不好的，要看使用的场景。

4.幻读（ Phantom ）

        对于两个事务Session A、Session B, Session A 从一个表中 读取 了一个字段, 然后 Session B 在该表中 插入 了一些新的行。 之后, 如果 Session A 再次读取 同一个表, 就会多出几行。那就意味着发生了幻读。

        Session A中的事务先根据条件 studentno > 0这个条件查询表student，得到了name列值为'张三'的记录；之后Session B中提交了一个 隐式事务 ，该事务向表student中插入了一条新记录；之后Session A中的事务再根据相同的条件 studentno > 0查询表student，得到的结果集中包含Session B中的事务新插入的那条记录，这种现象也被称之为 幻读 。我们把新插入的那些记录称之为 幻影记录 。

事务的隔离级别

        事务并发问题： 在事务并发执行的时候，如果不进行事务隔离，那么就会产生脏写、脏读、不 重复读、幻读的问题。

        事务的隔离级别：

READ_UNCOMMITTED	读未提交
READ_COMMITTED	读提交（不可重复读）
REPEATABLE_READ	可重复读
SERIALIZABLE	串行化

        每个隔离级别都针对事务并发问题中的一种或几种进行解决，事务级别越高，解决的 并发事务问题也就越多，同时也意味着加的锁就越多，所以性能也会越差。

事务的隔离级别解决的问题

1. READ_UNCOMMITTED

事务读取：不加锁

事务写入：加锁

解决问题：脏写

存在问题：脏读、不可重复读、幻读

2. READ_COMMITTED

事务读取：加锁(每次select完都会释放读锁)

事务写入：加锁

解决问题：脏写、脏读

存在问题：不可重复读、幻读

3. REPEATABLE_READ

事务读取：加锁(每次select完都不会释放读锁,而是事务结束后才释放，如果是InnoDB还会加间隙锁)

事务写入：加锁

解决问题：脏写、脏读、不可重复读

存在问题：幻读（InnoDB不存在）

4. SERIALIZABLE

不管是读取还是修改所有的事务串行化执行，一个事务的执行必须等其他事务结束

InnoDB的MVCC

        MVCC （Multiversion Concurrency Control），多版本并发控制。顾名思义，MVCC 是通过数据行的多个版 本管理来实现数据库的 并发控制 。这项技术使得在InnoDB的事务隔离级别下执行 一致性读 操作有了保 证。换言之，就是为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值，这样 在做查询的时候就不用等待另一个事务释放锁。

        MVCC 的实现依赖于：隐藏字段、Undo Log、Read View。

        InnoDB就是通过MVCC机制解决可重复读中的幻读问题。

实现事务的步骤

语法

-- 1.关闭MySQL自动提交
SET AUTOCOMMIT = 0;
-- 2.开启一个事务，标记事务的起始点
START TRANSACTION;
-- 3.向数据库提交事务
COMMIT;
-- 3.将事务回滚，所有的数据库操作被取消
ROLLBACK;
-- 4.开启MySQL自动提交
SET AUTOCOMMIT = 1;

案例

bank表：

        当用户wyj借给lyf1000元时：

update bank set bmoney = bmoney-1000 where bname='wyj';
update bank set bmoney = bmoney+1000 where bname='lyf';

 

        当转账时不小心写错了lyf的名字：

update bank set bmoney = bmoney-1000 where bname='wyj';
update bank set bmoney = bmoney+1000 where bname='ly';

        我们发现wyj的钱借出去了，但是lyf并没有收到：

 

        这种情况是不允许出现的，所以我们可以使用一组事务：

-- 1. 关闭自动提交
set autocommit = 0;
-- 2. 开始事务
start transaction;
-- 3. 一组sql语句
update bank set bmoney = bmoney-1000 where bname='wyj';
update bank set bmoney = bmoney+1000 where bname='ly';
-- 4. 结束事务(判断)
    -- 提交(提交和回滚2选1，不可同时出现)
    commit;
    -- 回滚
    rollback;
-- 5. 开启自动提交
set autocommit = 1;

        当我们发现 wyj的钱借出去了，但是lyf并没有收到时，就可以选择回滚，让wyj的钱变会没借出时的金额，这样就符合我们日常的逻辑需求，如果“转账”成功，则提交。

函数

常用的日期函数

CURDATE()

返回当前的日期

CURTIME()

返回当前的时间

NOW()

返回当前的日期和时间

DATE_FORMAT(date,format)

依照指定的fmt格式格式化日期date值

SELECT DATE_FORMAT(NOW(),"%A-%B-%M")

结果： 

MONTH(date)

返回date的月份值(1~12)

DAY(date)

返回date的日

YEAR(date)

返回日期date的年份(1000~9999)

DATEDIFF(expr1,expr2)

查询两个日期的时间差(天数)

SELECT DATEDIFF(NOW(),"2024-6-25")

结果： 

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

查询距离时间的间隔时间,unit为单位(second、day、year...)

-- 一共活了多少秒(SECOND)
SELECT TIMESTAMPDIFF(SECOND,'2001-1-14',NOW())

结果：

-- 一共活了多少年
SELECT TIMESTAMPDIFF(YEAR,'2001-1-14',NOW())

结果：

根据时间间隔计算时间

写法一：

-- 五天后的时间
SELECT NOW() +INTERVAL 5 DAY
-- 五年后的时间
SELECT NOW() +INTERVAL 5 YEAR

-- 五年前的时间
SELECT NOW() -INTERVAL 5 YEAR

写法二：

-- 五年后的时间
SELECT DATE_ADD(NOW(),INTERVAL 5 YEAR)
-- 五年前的时间
SELECT DATE_SUB(NOW(),INTERVAL 5 YEAR)

常用的字符串函数

拼接字符串

SELECT CONCAT('hello','world')

用指定符号分隔字符串

SELECT CONCAT_WS('$','hello','world') -- hello$world

向下取整

SELECT FLOOR(3.9)

 结果： 

向上取整

SELECT CEILING(3.9)

结果：

 

四舍五入

SELECT ROUND(3.4)
SELECT ROUND(3.5)

结果： 

-- 保留一位小数

SELECT ROUND(3.4,1)

结果：

 

截断小数

SELECT TRUNCATE(3.99999,2)

结果：

GROUP_CONCAT(expr)

返回由属于一组的列值连接组合而成的结果

SELECT Ssex,GROUP_CONCAT(sname) FROM student GROUP BY Ssex

结果：

慢查询

定义

        MySQL默认10秒内没有响应SQL结果,则为慢查询(可以去修改MySQL慢查询默认时间)

Mysql对慢查询的操作

//显示到mysql数据库的连接数
show status like 'connections';
--查看慢查询的状态
Show variables like '%slow_query%';
--设置慢查询的到表 mysql.slow_log
set global log_output='TABLE';
--设置慢查询的时间
set global long_query_time=3;
--开启慢查询
set global slow_query_log='ON';
--慢查询的次数
show status like 'slow_queries';
--慢查询记录
select * From mysql.slow_log ;
--慢查询sql语句
select convert(sql_text using utf8) sql_text from mysql.slow_log
--关闭慢查询
set global slow_query_log='OFF';

索引

定义

        索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

索引的优点

1、高效性：利用索引可以提高数据库的查询效率

2、唯一性：索引可以确保所查的数据的唯一性

3、完整性：用户可以加速表和表之间的连接，实现表与表之间的参照完整性

4、特殊能力性：通过使用索引，可以在查询过程中，使用优化隐藏器，提高系统性能。

索引的缺点

1.虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。

2.因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。

3. 如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快

索引的分类

主键索引

在数据库关系图中为表定义一个主键将自动创建主键索引。

唯一索引

不允许具有索引值相同的行，从而禁止重复的索引或键值。

常规索引

最基本的索引类型，没有唯一性之类的限制。

全文索引

        搜索引擎的关键技术，用于检索文本信息，可以是词语或者段落。

        我们想在上述表所有字段中找到和“西安”有关的信息，就可以使用全文索引，类似于模糊查询

create table wenzhang(
    wid int PRIMARY KEY auto_increment,
    title varchar(20),  
    content text, 
    zuozhe varchar(20), 
    -- 设置全文索引
    FULLTEXT(title,content,zuozhe) with parser ngram 
);

insert into wenzhang(title,content,zuozhe) 
values
('西安往事','这是一个古老的城市，在这个城市中有很多的人，工厂，建筑物','小杨'),
('山西往事','这是一个古老的城市，这里有很多的人，工厂，建筑','老陶'),
('地球往事','这是一个古老的星球，这里有很多的人','老刘在西安'),
('银河往事','这是一个系，打算在这个系之外造一个西安','小彭');
-- 在所有字段中查找
select * from wenzhang 
where match(title,content,zuozhe) AGAINST('西安');

        此时，查询到了所有字段中含有“西安”的记录：

        那如果我们只想在title,zuozhe这两个字段中查找含有“西安”的记录呢？

select * from wenzhang where match(title,zuozhe) AGAINST('西安');

会报以下错误：
[SQL] select * from wenzhang where match(title,zuozhe) AGAINST('西安');
[Err] 1191 - Can't find FULLTEXT index matching the column list

        因为在创建表时我们设置了”FULLTEXT(title,content,zuozhe) with parser ngram“，所以必须在(title,content,zuozhe)中查找，不能随便缺少字段，所以：

ALTER table wenzhang add FULLTEXT(title,zuozhe)with parser ngram;
select * from wenzhang where match(title,zuozhe) AGAINST('西安');

结果：