数据库——Sixth article

目录

事务

概述

四大特性

事务的步骤

REDO日志和UNDO日志

事务的隔离性

实现方式

隔离级别

函数

常见函数

部分函数的操作

慢查询

概述

分析查询语句

​编辑

索引

概述

索引的操作

导致索引失效的情况

事务

概述

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

四大特性

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

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

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

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

事务的步骤

1. 关闭自动提交
2. 开始事务
3. 一组SQL语句
4. 结束事务（判断）（两种方式：回滚/提交）
5. 开启自动提交

-- 建表bank
create table bank( 
  bid int PRIMARY key auto_increment,
  bname VARCHAR(10),
  bmoney DECIMAL(20,2)
);
-- 存数据 
INSERT INTO bank(bname,bmoney) VALUES('佳',10)
INSERT INTO bank(bname,bmoney) VALUES('贺',5000000000)

-- 案例：
set autocommit = 0;  -- 关闭自动提交

start transaction;   -- 开启事务

UPDATE bank set bmoney = bmoney-1000 WHERE bname = '贺';     -- 一组SQL语句
UPDATE bank set bmoney = bmoney+1000 WHERE bname = '佳'; 
      
commit;              -- 提交

rollback;            -- 回滚

set autocommit = 1;  -- 开启自动提交

select * from bank;

REDO日志和UNDO日志

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

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

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

事务的隔离性

实现方式

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

MySql中的锁：

基于属性分类	共享锁（读锁、S锁）；排他锁（写锁、X锁）
基于粒度分类	表锁；行锁（记录锁、间隙锁、临键锁）
基于状态分类	意向共享锁；意向排他锁

隔离级别

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

MySQL默认的隔离级别是可重复读

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

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

事务隔离级别解决的问题

	读未提交	读提交	可重复读	串行化
事务读取	不加锁	加读锁	加读锁	不管读取还是修改所有事务串行化执行，一个事务的执行必须等其他事务结束
事务写入	加写锁	加写锁	加写锁
解决问题	脏写	脏写、脏读	脏写、脏读、不可重复读、幻读（若是Mysql的innodb则已解决）
存在问题	脏读、不可重复读、幻读	不可重复读、幻读	幻读（Mysql的innodb则不存在）

InnoDB的MVCC

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

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

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

函数

MySQL函数：是一种控制流程函数，属于数据库用语言

常见函数

常见的日期函数

• curdate():返回当前的日期;curtime():返回当前的时间;now():返回当前的时间和日期
• month(date):返回date的月份值(1~12);day(date):返回date的日;year(date):返回date的年份
• date_format(date,fmt):依照指定格式fmt格式格式化日期date值

字符串函数

• concat(s1,s2...,sn):将s1,s2...sn连接成字符串
• concat_ws(sep,s1,s2...,sn):将s1,s2...sn用sep字符间隔并连接成字符串

数学函数

• celling(x):返回大于x的最小整数值
• floor(x):返回小于x的最小整数值
• round(x,y):返回参数x的四舍五入的有y位小数的值
• truncate(x,y):返回数字x截断为y位小数的结果

聚合函数

数据库——Third artclehttps://mp.csdn.net/mp_blog/creation/editor/140617247

部分函数的操作

# 函数
-- 日期
SELECT curdate()   -- 日期

SELECT curtime()   -- 时间

SELECT now()       -- 现在的时间

SELECT month(now())       -- 当前的月份
SELECT month('1999-2-2')  -- 获取1999.2.2的月份

--  面试题：查出当月过生日的学生
use myschool
select * from student where month(birthday) = month(now())
--  依照指定的格式 格式化日期
select date_format(now(),'%a') 

select date_format(now(),'%a-%b-%M') 

-- 查询两个时间的时间差
--   天的差
SELECT datediff(now(),'2024-6-24')

SELECT datediff('2024-6-24',now())

--   秒的差
select timestampdiff(SECOND,'2002-2-8',now())

--   年的差
select timestampdiff(year,'2002-2-8',now())

-- 查询距离时间间隔的时间
SELECT now() + interval 5 YEAR 

SELECT now() - interval 5 YEAR 

select date_add(now(),interval 10 year)

select date_sub(now(),interval 10 year)

-- cancat 字符串拼接
SELECT concat('hello','xiaohe')

SELECT concat_ws(',','hello','xiaohe')


-- 数学函数

--  向上取整
SELECT CEILING(3.9999)

--  向下取整
SELECT floor(3.9999)

--  四舍五入：第二个参数是保留几位小数
SELECT round(3.555,2)

SELECT round(3.4,2)

SELECT round(3.6)

-- 截断：第二个参数是保留几位小数
select truncate(3.999,1)    

select truncate(3.3333,0)

-- 拼接字符串
SELECT ssex ,group_concat(sname) from student GROUP BY ssex     -- 不用

慢查询

概述

MySQL默认10秒内没有响应SQL结果，则为慢查询（默认慢查询时间可以进行修改）

# 慢查询
--  查看当前的连接数
SHOW status like 'connecttions'

--  查看慢查询的状态
show variables like '%slow_query%'

分析查询语句

MySql提供了explain语法来进行查询分析（在SQL语句前加一个“explain”即可）

-- 分析查询语句
explain select * from wenzhang 
where match(title,zuozhe) AGAINST('西安');            -- type:fulltext

EXPLAIN SELECT * FROM wenzhang where content LIKE '%西安%'       -- type:all

连接类型的解释：

• system：表只有一行：system表。这是const连接类型的特殊情况。
• const ：表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为 MYSQL先读这个值然后把它当做常数来对待。
• eq_ref：在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键 或惟一键的全部时使用。
• ref：这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的 表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好。
• range：这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况。
• index：这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）。
• ALL：这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免

索引

概述

索引：是数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。索引可分为：主键索引；唯一索引；常规索引；全文索引。

索引的操作

# 索引
use myschool
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('西安');

-- 修改索引
ALTER table wenzhang add FULLTEXT(title,zuozhe)with parser ngram;

导致索引失效的情况

1. 最佳左前缀法则
2. 主键插入顺序
3. 计算、函数导致索引失效
4. 类型转换导致索引失效
5. 范围条件右边的列索引失效
6. 不等于(!= 或者<>)索引失效
7. is null可以使用索引，is not null无法使用索引
8. like以通配符%开头索引失效
9. OR 前后存在非索引的列，索引失效
10. 数据库和表的字符集统一使用utf8mb4