mysql

grin : (0~.~0)

已于 2023-10-05 00:47:03 修改

阅读量195

点赞数

分类专栏： SQL 文章标签： mysql 数据库 sql

于 2023-02-12 23:04:32 首次发布

本文链接：https://blog.csdn.net/qq_64071349/article/details/128974490

版权

SQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

2.1.3 分页查询!!!!!!!!(理解错了limit)

2.1.4执行顺序

一.自带的函数

一.

1.DDL

1.1数据库操作

1.2 表操作

1.2.1查询创建

1.2.2字段操作

2.DQL

2.1.1聚合函数

2.1.2排序查询

2.1.3 分页查询!!!!!!!!(理解错了limit)

注意 limit 索引, 要查找几行.

刚开始我理解错了,误以为第二个参数也是结尾索引,第二个是记录数

2.1.4执行顺序

二.自带的函数

官方地址：MySQL自带函数

1.字符串函数

2.数值函数

3.日期函数

4.流程函数

if()

ifnull()

case 表达式 when 匹配值 then result

三.事务

CREATE TABLE ACCOUNT(
id INT auto_increment primary key,
name VARCHAR(10),
money int
)comment '账户表';
insert into account(id,name,money) 
VALUES(null,'张三',2000),
(null,"李四",2000);

START TRANSACTION;
BEGIN;
-- 1.查询张三余额
select money
from account
where name="张三";
-- 2.将张三余额 -1000
update account 
set money=money-1000
where name="张三";
-- 3.李四余额 +1000
update account 
set money=money+1000
where name="李四";
-- 异常出现
大大大大大哒哒哒多阿达

-- 如果成功，手动执行commit语句
commit;

-- 如果失败，先执行rollback语句，而且先不要执行commit语句

ROLLBACK;


-- 恢复数据

update account
set money=2000
where name ='张三' or name='李四';



-- 如何控制事务
/*
1.
select @@autocommit ;    查看事务是否是自动提交的，如果为1，则是自动提交。这意味着，每条sql语句就是一个事务，执行一句便提交一句
2.
set @@autocommit=0;      将事务设置成手动提交,如果执行删除或更新语句时，不用commit， 则表中的数据不会发生改变，只有提交后才会发生变化

3.
 如果程序中出错，执行rollback，进行回滚，先前的执行过的操作回到原来没执行的时候。

4.除了设置set @@autocommit =0;  来设置事务的提交
也可以用 
START TRANSACTION;来开启事务
或用 begin; 开启事务

但记得手动commit,或rollback,不然事务就没关闭。

*/

-- 事务的四大特性
1.原子性
事务是不可分割的最小单元，要么全部成功，要么全部失败 
2.一致性
事务完成时，必须使所有的数据保持一致。例如，当多条语句执行时，中间某条语句出现错误，导致下面的语句并未执行，此时如果提交，那么出错的语句及剩下的语句并未执行，只有前面的语句执行，因此会造成表中数据不一致的情况。
3.隔离性
一个事务的执行不能被另一个事务干扰

4.永久性
提交后的事务，对数据库的改变是永久性的，其他操作不会对其执行结果产生影响。

select @@autocommit;
set @@autocommit=0; 
set @@autocommit=1;
COMMIT;

ReadView 在快照读的时候采用 MVCC

在使用当前读时用的是行锁+间隙锁

Mysql事务id和创建者id一样代表着：在同一个事务中 Mysql update后创建一个undo_log版本 ,此时又进行select,因此产生的readView的事务id与创建者id相同。

对于可重复读隔离级别：仅在第一次执行快照读时，生成ReadView,但是当执行当前读时，再次执行快照读会产生新的ReadView。

如下图：两次快照读：意思是同一个事务中执行两次select语句

undo-log

事务进行插入，更新，删除时，会产生一个一个 undo_log版本，类似于这种

借鉴:解释脏读，幻读，可重复读

MySQL 是支持多事务并发执行的。否则来一个事务处理一个请求，处理一个人请求的时候，其它事务都等着，那估计都没人敢用MySQL作为数据库,因为用户体验太差，估计都要砸键盘了。

既然事务可以并发操作,这里就有一些问题：一个事务在写数据的时候，另一个事务要读这行数据，该怎么处理？一个事务在写数据，另一个数据也要写这行数据，又该怎么处理这个冲突？

这就是并发事务所产生的一些问题。具体来说就是：脏读、不可重复读和幻读。

一、概念说明
以下几个概念是事务隔离级别要实际解决的问题，所以需要搞清楚都是什么意思。

1、脏读
脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并一定最终存在的数据，这就是脏读。
在这里插入图片描述

脏读最大的问题就是可能会读到不存在的数据。比如在上图中，事务B的更新数据被事务A读取，但是事务B回滚了，更新数据全部还原，也就是说事务A刚刚读到的数据并没有存在于数据库中。

从宏观来看，就是事务A读出了一条不存在的数据，这个问题是很严重的。

2、不可重复读
不可重复读指的是在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据出现不一致的情况。
在这里插入图片描述

事务 A 多次读取同一数据，但事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。

3、幻读
脏读、不可重复读上面的图文都很好的理解，对于幻读网上有很多文章都是这么解释的

幻读错误的理解

说幻读是事务A 执行两次 select 操作得到不同的数据集，即 select 1 得到 10 条记录，select 2 得到 15 条记录。这其实并不是幻读，既然第一次和第二次读取的不一致，那不还是不可重复读吗，所以这是不可重复读的一种。
正确的理解应该是

幻读，并不是说两次读取获取的结果集不同，幻读侧重的方面是某一次的 select 操作得到的结果所表征的数据状态无法支撑后续的业务操作。更为具体一些：select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读。
举例

假设有张用户表,这张表的 id 是主键。表中一开始有4条数据。
在这里插入图片描述

我们再来看下出现幻读的场景
在这里插入图片描述

这里是在RR级别下研究(可重复读),因为 RU / RC 下还会存在脏读、不可重复读，故我们就以 RR 级别来研究幻读，排除其他干扰。

1、事务A,查询是否存在 id=5 的记录，没有则插入，这是我们期望的正常业务逻辑。

2、这个时候事务B 新增的一条 id=5 的记录，并提交事务。

3、事务A,再去查询 id=5 的时候,发现还是没有记录（因为这里是在RR级别下研究(可重复读)，所以读到依然没有数据）

4、事务A,插入一条 id=5 的数据。

最终事务A 提交事务，发现报错了。这就很奇怪，查的时候明明没有这条记录，但插入的时候却告诉我主键冲突，这就好像幻觉一样。这才是所有的幻读。

不可重复读侧重表达读-读，幻读则是说读-写，用写来证实读的是鬼影。

二、事务的隔离级别
上述所说的"脏读"，“不可重复读”，"幻读"这些问题，其实就是数据库读一致性问题，必须由数据库提供的事务隔离机制来进行解决。

首先说读未提交，它是性能最好，也可以说它是最野蛮的方式，因为它压根儿就不加锁，所以根本谈不上什么隔离效果，可以理解为没有隔离。

再来说串行化。串行化就相当于上面所说的，处理一个人请求的时候，别的人都等着。读的时候加共享锁，也就是其他事务可以并发读，但是不能写。写的时候加排它锁，其他事务不能并发写也不能并发读。

最后说读提交和可重复读。这两种隔离级别是比较复杂的，既要允许一定的并发，又想要兼顾的解决问题。MySQL默认事务隔离级别为可重复读(RR),oracle默认事务隔离级别为读已提交(RC),

数据库的事务隔离越严格，并发副作用越小，但付出的代价越大；因为事务隔离本质就是使事务在一定程度上处于串行状态，这本身就是和并发相矛盾的。

同时，不同的应用对读一致性和事务隔离级别是不一样的，比如许多应用对数据的一致性没那么个高要求，相反，对并发有一定要求。

这篇文章起了个开头,接下来会写文章来回答下面的问题

1、MySQL 是如何实现这几个隔离级别的呢？它们底层的工作原理是什么呢？

2、虽然MySQL默认事务隔离级别为可重复读(RR),但它如何做到做到解决部分幻读的问题？

四、存储引擎

1.Mysql体系结构

2.存储引擎

2.1简介

不同的引擎有不同的应用场景。

每张表都可以指定存储引擎。

show create table 表名 查看建表语句

2.2 Innodb介绍

事务，行级锁，外键约束

每一个Innodb的表都有一个表空间文件 .ibd文件

查看ibd文件

页是最小存储单位

2.3 MySAM和Memory引擎

2.n 机器人补充

3.索引

3.1 概述

3.2 索引结构

3.3 b树

关于这个b树，看起来在空隙之间插入指针指向其他子节点，很神奇，其实是图把抽象的东西具象了，实现b树时，用一个数组储存key ，再用另一个数组存储child ，child数组的大小总是比key数组大1。

关于b树的分裂，插入完成后都可能超过节点 keys 数目限制，因此要进行分裂。

暂时的思路：先查找将要插入的位置，然后判断插入后是否超过keys的长度，如果超过就进行裂变。

3.4 b+树

b树的所有数据都会出现在叶子结点，非叶子节点用于存储key

叶子结点用链表连起来

MySql中对b+树做了优化

每个节点都占一个页

3.5 hash

mysql的hash是先计算出表中每一行的hash值，然后根据hash算法计算列的hash值，放到一个槽中，并且槽中并添加指向某行的hash值。

3.6 关于索引结构的思考

3.7

聚集索引只能有一个，将数据与索引放到一块，索引结构的叶子节点保存了行数据。

当查询name 时，先在二级索引中查询name的所在行的id，然后再去聚集索引里去查。

3.7 索引语法

3.8 性能分析-查看执行频次

模糊查询时，是7个_ 代表7个字符

3.9 慢查询日志

查看慢查询是否打开

windows和linux开启慢查询

我的慢查询日志文件默认在

C:\ProgramData\MySQL\MySQL Server 8.0\Data

3.10 show profiles

3.11 explain (重要)

关于type:

①当不访问任何表时，为null

②当访问系统表，为system

③当根据主键和唯一索引进行访问，为 const

④ 当使用非唯一索引，会出现ref

⑤当用了索引，但扫描了整个索引树，会出现 index

⑥进行全表扫描，出现all

3.12 验证索引效率- 索引原则

索引以空间换时间，添加索引后，查询速度会非常快

3.13 最左前缀原则

为什么使用范围查询> <会使联合索引左面失效，而>=会解决这种问题？

3.14 索引失效情况1

字符串不加引号，会引起隐式类型转换

3.15 索引失效情况2

当使用is null 或 not null 是，走不走索引也是看数据分布的。

而且，is null 跟普通的 =数字效果是一样的

3.16 SQL提示

use index() 给MYSQL提建议

3.16 覆盖索引

3.17 前缀索引

前缀索引会回表去比对与行中的整体字符串一样不一样

3.18 单列索引和联合索引的选择

假如name 和 phone字段上各有一个索引。

此时如果使用 and进行查询，结果只会使用phone这一个索引

实操时：由于受数据分布的影响，mysql任然选择了单列索引，因此我要给它提建议

很显然，它接受了我的建议。

3.19 索引设计原则

补充：像性别就不需要建立索引，因为区分度不高，一共就两种情况。

4.SQL优化

4.1 插入数据

insert 优化

①批量插入

插入时，进行网络传输是需要时间的。每次插入都会建立一次连接，因此插入多条能提高效率。

500-1000条

打插入几万条，需要分批插入

②手动事务提交

默认的是每次执行sql语句都会提交事务，频繁的提交事务，也会影响性能。

③主键顺序插入

④大批量插入数据load

不太理解 infile后跟文件的格式能不能为其他

查询local_infile是否打开的两种方式

4.2 主键优化

1. 数据组织方式

在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表成为索引组织表

Innodb引擎逻辑存储结构

2. 主键顺序插入效果如下;

3. 主键乱序插入效果如下：

4.页合并

5 索引设计原则

4.3 order by 优化

我来总结一下重点:

① 建立联合索引时，默认为升序排序。

当根据这两个索引来升序排序时，为using index

根据这两个索引降序排序时，会通过反向扫描 backward scan ;using index

但一个升序，一个降序时，由于默认两个都是升序，不管是正向扫描，还是反向扫描，都不会出现

ASC DESC 这种情况，因此结果为 using filesort

②当建立如下代码时，可以解决一个升序，一个降序的问题。

create index idx_user_age_pho_ad  on tb_user(age asc ,phone desc);

建立联合索引时可以指定 索引是升序还是降序


当使用Show index from 表名  ,
查看表中的索引，就会发现Collaction 那一行 ，当为B是代表着 降序，A 代表着升序

③但是，联合索引的建立是根据字段从左到右进行创建。假如建立了（age, phone）,如果你用order by phone,age 这样是会出错的，因为 order by phone,age 是先排序phone,再排序age .

设置sort_buffer_size 大小