Logo

菜单

wy132
wy132
发布于 2024-07-14 / 1 阅读
0

Mysql 笔记

#学习笔记

排序与分页

使用 ORDER BY 子句排序

  • ASC（ascend）: 升序

  • DESC（descend）:降序

多列排序：

SELECT last_name, department_id, salary
FROM   employees
ORDER BY department_id, salary DESC;

  • 先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第一列数据中所有值都是唯一的，将不再对第二列进行排序。

分页：LIMIT，必须放 select语句的最后

：查询返回的记录太多了，查看起来很不方便，所以有分页

--前10条记录：
SELECT * FROM 表名 LIMIT 0,10;
或者
SELECT * FROM 表名 LIMIT 10;
​
--第11至20条记录：
SELECT * FROM 表名 LIMIT 10,10;
​
--第21至30条记录： 
SELECT * FROM 表名 LIMIT 20,10;

多表查询

：在两个表中过滤数据

等值连接：

#在e，d表中寻找department_id相同的数据
SELECT e.employee_id, e.last_name, e.department_id,
       d.department_id, d.location_id
FROM   employees e , departments d
WHERE  e.department_id = d.department_id;

非等值连接：

#查找e中的salary在j中最高和最低之间的数据
SELECT e.last_name, e.salary, j.grade_level
FROM   employees e, job_grades j
WHERE  e.salary BETWEEN j.lowest_sal AND j.highest_sal;

自连接：

：当table1和table2本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行内连接，外连接等查询

SELECT CONCAT(worker.last_name ,' works for ' 
       , manager.last_name)
FROM   employees worker, employees manager
WHERE  worker.manager_id = manager.employee_id ;

外连接：

：两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行，并为 NULL

JOIN ...ON...关键字：

SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;
SELECT employee_id, city, department_name
FROM   employees e 
JOIN   departments d
ON     d.department_id = e.department_id 
JOIN   locations l
ON     d.location_id = l.location_id;

左（右）外连接：在JOIN前加 LEFT（RIGHT）

UNION关键字：合并多条 select语句

SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2

1711626175672

  • 中图：内连接 A∩B

  • 左上图：左外连接

  • 右上图：右外连接

  • 左中图：A - A∩B

  • 右中图：B-A∩B

  • 左下图：满外连接

  • 右下图：A ∪B- A∩B 或者 (A - A∩B) ∪ （B - A∩B）

函数

单行函数：

：返回一个值，可用在 select、where等语句中

SELECT ABS(-123),ABS(32),SIGN(-23),SIGN(43),PI(),CEIL(32.32),CEILING(-43.23),FLOOR(32.32),
FLOOR(-43.23),MOD(12,5)
FROM DUAL;

聚合函数：

：返回一组数据，不能用于 where中，AVG、MIN、MAX、SUM等

GROUP BY分组关键字：对数据分组

#按department_id分组，并AVG求出每组的平均值
SELECT   AVG(salary)
FROM     employees
GROUP BY department_id ;
#GROUP BY department_id, job_id ; #多列分组

HAVING关键字：用于过滤，使用聚合函数

SELECT   department_id, MAX(salary)
FROM     employees
GROUP BY department_id
HAVING   MAX(salary)>10000 ;

查询总体基本语句

SELECT ...,....,...
FROM ... JOIN ... 
ON 多表的连接条件
JOIN ...
ON ...
WHERE 不包含组函数的过滤条件
AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...

子查询

：一个查询语句包装放到一些位置中

单行子查询：

：返回一个值

where中

SELECT last_name, job_id, salary
FROM   employees
WHERE  salary = 
                (SELECT MIN(salary)
                 FROM   employees);

having中

SELECT   department_id, MIN(salary)
FROM     employees
GROUP BY department_id
HAVING   MIN(salary) >
                       (SELECT ...);

多行子查询：

返回一组数据

HAVING中

SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
			SELECT MIN(avg_sal)
			FROM (
				SELECT AVG(salary) avg_sal
				FROM employees
				GROUP BY department_id
				) dept_avg_sal
			)

关联子查询：

：子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为 关联子查询。

SELECT employee_id,salary
FROM employees e
ORDER BY (
	  SELECT department_name
	  FROM departments d
	  WHERE e.`department_id` = d.`department_id`
	);

约束

对表的数据的强制规定，如不能重复，规定范围大小等

CREATE TABLE 表名称(
	字段名  数据类型,
    字段名  数据类型 NOT NULL,   #非空约束
    字段名  数据类型 UNIQUE	#唯一约束
);

primary key关键字：主键约束（非空+唯一），一个表最多一个

auto_increment关键字：自增长列，每添加一行数据，其值++，一表一个

FOREIGN KEY关键字：


create table dept( #主表
	did int primary key,		#部门编号
    dname varchar(50)			#部门名称
);

create table emp(#从表
	eid int primary key,  #员工编号
    ename varchar(5),     #员工姓名
    deptid int,				#员工所在的部门
    foreign key (deptid) references dept(did)   #在从表中指定外键约束
    #emp表的deptid和和dept表的did的数据类型一致，意义都是表示部门的编号
);

  • emp表中添加的数据的 deptid的值须在 dept的 did值中找到

CHECK关键字：规定数据范围

age INT CHECK(age > 20)，
 gender char check ('男' or '女')

视图

：依据已有的数据创建一张视图表

  • 虚拟表，不占用数据

  • 建立在已有的表之上

  • 对视图的数据修改，基表数据也会更改，反之亦然

跟新建一张表有什么区别：视图不存储空间，减少数据冗余

CREATE VIEW empvu80
AS 
SELECT  employee_id, last_name, salary
FROM    employees
WHERE   department_id = 80;
#创建表
CREATE TABLE dept80
AS 
SELECT  employee_id, last_name, salary*12 ANNSAL, hire_date
FROM    employees
WHERE   department_id = 80;

存储过程与函数

存储过程：

：一组经过预先编译的 SQL 语句的封装，预先存储在 MySQL 服务器上，需要执行的时候，客户端只需要向服务器端发出调用存储过程的命令，服务器端就可以把预先存储好的这一系列 SQL 语句全部执行。

：没有返回值

DELIMITER //

CREATE PROCEDURE avg_employee_salary ()
BEGIN
	SELECT AVG(salary) AS avg_salary FROM emps;
END //

DELIMITER ;

存储函数：

：有返回值

DELIMITER //

CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)
DETERMINISTIC
CONTAINS SQL
BEGIN
	RETURN (SELECT email FROM employees WHERE last_name = 'Abel');
END //

DELIMITER ;

游标

：让我们能够对结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作的数据结构

：指向某行数据

触发器

：当INSERT、UPDATE、DELETE事件发生时，触发一些相关的程序

：表有数据插入、修改和删除的操作时，自动触发 2 步操作：

DELIMITER //

CREATE TRIGGER before_insert #定义
BEFORE INSERT ON test_trigger #指定在什么时候触发
FOR EACH ROW
BEGIN	#触发后的动作
	INSERT INTO test_trigger_log (t_log)
	VALUES('before_insert');

END //

DELIMITER ;

存储引擎

对比项

MyISAM

InnoDB

外键

不支持

支持

事务

不支持

支持

行表锁

表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作

行锁，操作时只锁某一行，不对其它行有影响，适合高并发的操作

缓存

只缓存索引，不缓存真实数据

不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响

自带系统表使用

Y

N

关注点

性能：节省资源、消耗少、简单业务

事务：并发写、事务、更大资源

默认安装

Y

Y

默认使用

N

Y

索引

：加快查询

创建索引：

CREATE TABLE book( 
    book_id INT , 
    book_name VARCHAR(100), 
    authors VARCHAR(100), 
    info VARCHAR(100) , 
    comment VARCHAR(100), 
    year_publication YEAR, 
    INDEX(year_publication) , #普通索引
    UNIQUE INDEX uk_idx_id(id) , #唯一索引
    PRIMARY KEY(id) , #主键索引
    INDEX multi_idx(id,name,age) #组合索引
);

适合创建索引的情况：

  1. 字段的数值有唯一性的限制

  2. 频繁作为where查询的字段

  3. 经常 GROUP BY 和 ORDER BY 的列

  4. UPDATE、DELETE** 的 WHERE 条件列

  5. DISTINCT** 字段需要创建索引

  6. 使用最频繁的列放到联合索引的左侧

  7. 数据范围小的列创建索引

  8. 区分度高(散列性高)的列适合作为索引

  9. 在多个字段都要创建索引的情况下，联合索引优于单值索引

不适合创建索引的情况：

  1. 在where中使用不到的字段，不要设置索引

  2. 数据量小的表最好不要使用索引

  3. 有大量重复数据的列上不要建立索引

  4. 避免对经常更新的表创建过多的索引

  5. 不建议用无序的值作为索引

  6. 删除不再使用或者很少使用的索引

  7. 不要定义冗余或重复的索引

索引失效情况：

  1. 最佳左前缀法则：建立联合索引时，在检索数据时从联合索引的最左边开始匹配。如果前面的索引字段不中，后面的索引字段都失效

  2. 主键插入顺序：插入的主键值忽小忽大的话，则可能会造成页面分裂和记录移位

  3. 计算、函数、类型转换(自动或手动)导致索引失效

  4. 类型转换导致索引失效

  5. 不等于(!= 或者<>)索引失效

  6. 范围条件右边的列索引失效

  7. is null可以使用索引，is not null无法使用索引

  8. like以通配符%开头索引失效

  9. OR前后存在非索引的列，索引失效

索引和查询优化

关联查询

1.为了减少内层表数据的匹配次数，所以要求被驱动表上必须有索引才行。

2.不再是逐条获取驱动表的数据，而是一块一块的获取，引入了join buffer缓冲区，将驱动表join相关的部分数据列（大小受join buffer的限制）缓存到join buffer中，然后全表扫描被驱动表，被驱动表的每一条记录一次性和join buffer中的所有驱动表记录进行匹配（内存中操作），将简单嵌套循环中的多次比较合并成一次，降低了被驱动表的访问频率。

3.MySQL8.0.18版本开始就加入了hash join默认都会使用hash join

子查询优化

可以使用连接（JOIN）查询来替代子查询。**连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能就会更好

排序优化

可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中 避免全表扫描

尽量使用 Index 完成 ORDER BY 排序

GROUP BY优化

group by 也会直接使用索引

where效率高于having

减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做

优先考虑覆盖索引

即索引列+主键包含SELECT 到 FROM之间查询的列。

范式

第一范式：

确保数据表中每个字段的值必须具有原子性，也就是说数据表中每个字段的值为不可再次拆分的最小数据单位。

第二范式：

满足数据表里的每一条数据记录，都是可唯一标识的。而且所有非主键字段，都必须完全依赖主键，不能只依赖主键的一部分

第三范式：

所有非主键属性之间不能有依赖关系，必须相互独立，仅与主键直接依赖

性能分析工具

慢查询日志分析工具：mysqldumpslow 查询慢查询日志的数据

#得到返回记录集最多的10个SQL 
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log 
#得到访问次数最多的10个SQL 
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log
#得到按照时间排序的前10条里面含有左连接的查询语句 
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log 
#另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况 
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

分析查询语句：EXPLAIN

EXPLAIN SELECT select_options

列名

描述

id

在一个大的查询语句中每个SELECT关键字都对应一个唯一的id

select_type

SELECT关键字对应的那个查询的类型

table

表名

partitions

匹配的分区信息

type

针对单表的访问方法

possible_keys

可能用到的索引

key

实际上使用的索引

key_len

实际使用到的索引长度

ref

当使用索引列等值查询时，与索引列进行等值匹配的对象信息

rows

预估的需要读取的记录条数

filtered

某个表经过搜索条件过滤后剩余记录条数的百分比

Extra

一些额外的信息

事务

：一组逻辑操作单元，执行一些操作时，由commit提交来永久保存，可回滚(rollback)到最初状态

特性：

  1. 原子性：要么全部提交，要么全部失败回滚。

  2. 一致性：保证分布式，多用户的数据的一致

  3. 隔离性：一个事务的执行不能被其他事务干扰，并发执行的各个事务之间不能互相干扰。

  4. 持久性：事务一旦被提交，它对数据库中数据的改变就是永久性的

关键字：开启BEGIN，提交COMMIT，回滚ROLLBACK，保存点SAVEPOINT

数据并发问题：

  1. 脏写：两事务间的写操作混乱

  2. 脏读：会话A读取了会话B修改但未提交的数据，B回滚，则A脏读

  3. 不可重复读：会话A读取字段后，B更新该字段，A再读，值不同了

  4. 幻读：A读取字段，B添加几行，A再读表，行数改变

隔离级别：

  • READ UNCOMMITTED：读未提交，在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。不能避免脏读、不可重复读、幻读。

  • READ COMMITTED：读已提交，它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。可以避免脏读，但不可重复读、幻读问题仍然存在。

  • REPEATABLE READ：可重复读，事务A在读到一条数据之后，此时事务B对该数据进行了修改并提交，那么事务A再读该数据，读到的还是原来的内容。可以避免脏读、不可重复读，但幻读问题仍然存在。这是MySQL的默认隔离级别。

  • SERIALIZABLE：可串行化，确保事务可以从一个表中读取相同的行。在这个事务持续期间，禁止其他事务对该表执行插入、更新和删除操作。所有的并发问题都可以避免，但性能十分低下。能避免脏读、不可重复读和幻读。

日志

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

    整体流程：

    第1步：先将原始数据从磁盘中读入内存中来，修改数据的内存拷贝

    第2步：生成一条重做日志并写入redo log buffer（内存中），记录的是数据被修改后的值

    第3步：当事务commit时，将redo log buffer中的内容刷新到 redo log file（硬盘中），对 redo log file采用追加写的方式

    第4步：定期将内存中修改的数据刷新到磁盘中

    刷盘策略：每隔1s进行刷盘、每次事务提交时都将进行刷盘、每次事务提交时都只把 redo log buffer 内容写入 page cache，不进行同步。由os自己决定什么时候同步到磁盘文件

  • UNDO LOG 称为回滚日志，回滚行记录到某个特定版本，用来保证事务的原子性、一致性。记录每次增删修改前的原数据

  • 慢查询日志：记录所有执行时间超过long_query_time的所有查询，方便我们对查询进行优化。

  • 通用查询日志：记录所有连接的起始时间和终止时间，以及连接发送给数据库服务器的所有指令，对我们复原操作的实际场景、发现问题，甚至是对数据库操作的审计都有很大的帮助。

  • 错误日志：记录MySQL服务的启动、运行或停止MySQL服务时出现的问题，方便我们了解服务器的状态，从而对服务器进行维护。默认开启，无法禁止

  • 二进制日志binlog：记录所有更改数据的语句，可以用于主从服务器之间的数据同步，以及服务器遇到故障时数据的无损失恢复。存在主服务器中

  • 中继日志relaylog：用于主从服务器架构中，从服务器用来存放主服务器二进制日志内容的一个中间文件。从服务器通过读取中继日志的内容，来同步主服务器上的操作。存在从服务器中

  • 数据定义语句日志：记录数据定义语句执行的元数据操作。

binlog与redolog对比

  • redo log 它是物理日志，记录内容是“在某个数据页上做了什么修改”，属于 InnoDB 存储引擎层产生的。

  • 而 binlog 是逻辑日志，记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”，属于MySQL Server 层。

  • 虽然它们都属于持久化的保证，但是侧重点不同。

    • redo log 让InnoDB存储引擎拥有了崩溃恢复能力。

    • binlog保证了MySQL集群架构的数据一致性

锁

数据操作划分：

读锁与写锁：

  • 读锁：也称为共享锁、英文用S表示。针对同一份数据，多个事务的读操作可以同时进行而不会互相影响，相互不阻塞的。

  • 写锁：也称为排他锁、英文用X表示。当前写操作没有完成前，它会阻断其他写锁和读锁。这样就能确保在给定的时间里，只有一个事务能执行写入，并防止其他用户读取正在写入的同一资源。

数据粒度划分：

表锁：

  1. 表级别的读写锁：可以给某一张表添加S，X锁

  2. 意向锁：在为数据行加共享 / 排他锁之前，InooDB 会先获取该数据行所在数据表的对应意向锁。如果我们给某一行数据加上了排它锁，数据库会自动给更大一级的空间，比如数据页或数据表加上意向锁，告诉其他人这个数据页或数据表已经有人上过排它锁了

  3. 元数据锁：当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。

行锁：

  1. 记录锁：仅仅把一条记录锁上

  2. 间隙锁：

  3. 临健锁：既想锁住某条记录，又想阻止其他事务在该记录前边的间隙插入新记录

  4. 插入意向锁：规定事务在等待的时候也需要在内存中生成一个锁结构，表明有事务想在某 个间隙中插入新记录，但是现在在等待

页锁：

锁的态度划分：

悲观锁：假设最坏的情况，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁，当其他线程想要访问数据时，都需要阻塞挂起，适合写操作多的场景

乐观锁：不采用数据库自身的锁机制，而是通过程序来实现。在程序上，我们可以采用版本号机制或者CAS机制实现。乐观锁适用于多读的应用类型，这样可以提高吞吐量。适合读操作多的场景

加锁的方式划分：

隐式锁：由于事务id的存在，相当于加了一个隐式锁。别的事务在对这条记录加S锁或者X锁时，由于隐式锁的存在，会先帮助当前事务生成一个锁结构，然后自己再生成一个锁结构后进入等待状态。隐式锁是一种延迟加锁的机制，从而来减少加锁的数量。

显示锁：通过语句进行加锁的锁

全局锁：

对整个数据库实例加锁。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞

处理死锁

方式1：等待，直到超时（innodb_lock_wait_timeout=50s）

即当两个事务互相等待时，当一个事务等待时间超过设置的阈值时，就将其回滚，另外事务继续进行。

方式2：使用死锁检测进行死锁处理

发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务（将持有最少行级排他锁的事务进行回滚），让其他事务得以继续执行。

MVCC

：MVCC 的实现依赖于：隐藏字段（事务id）、Undo Log、Read View。

：提高数据库的并发性能，更好的处理读写冲突

：解决脏读、脏写、不可重复读、幻读

流程：

  1. 首先获取事务自己的版本号，也就是事务 ID；

  2. 获取 ReadView；

  3. 查询得到的数据，然后与 ReadView 中的事务版本号进行比较；

  4. 如果不符合 ReadView 规则，就需要从 Undo Log 中获取历史快照；

  5. 最后返回符合规则的数据。

主从复制

：作用

  • 读写分离：提升并发性能

  • 数据备份：主库数据备份到从库

  • 高可用：当主服务器故障，可切换到从服务器运行，保证正常运行

三线程：

  1. 二进制日志转储线程（Binlog dump thread）是一个主库线程。当从库线程连接的时候， 主库可以将二进制日志发送给从库，当主库读取事件（Event）的时候，会在 Binlog 上加锁，读取完成之后，再将锁释放掉。

  2. 从库 I/O 线程会连接到主库，向主库发送请求更新 Binlog。这时从库的 I/O 线程就可以读取到主库的二进制日志转储线程发送的 Binlog 更新部分，并且拷贝到本地的中继日志 （Relay log）。

  3. 从库 SQL 线程会读取从库中的中继日志，并且执行日志中的事件，将从库中的数据与主库保持同步。

步骤：

步骤1：Master（主）将写操作记录到二进制日志（binlog）。

步骤2：Slave（从）将Master的binary log events拷贝到它的中继日志（relay log）；

步骤3：Slave重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的，而且重启后从接入点开始复制。

缺点：数据一致性问题，主从延迟问题

数据备份与恢复

物理备份：数据保存在新文件，相当于复制粘贴数据

逻辑备份：只备份sql语句，以实现数据重现

逻辑备份：关键字：mysqldump，可备份一个数据库、全部数据库、部分数据库、结构、数据、过程、函数、事件等


Redis 笔记
Java基础 学习笔记