mysql知识图谱思维导图模板_ProcessOn思维导图、流程图

基础

分组查询

group by

where与having区别

where 是对分组前的数据进行筛选
having 是对分组后的数据进行筛选

where、group by、having、order by、limit
这些关键字一起使用时，先后顺序有明确的限制

select 列 from 表名
where [查询条件]
group by [分组表达式]
having [分组过滤条件]
order by [排序条件]
limit [offset,] count

查看数据库版本

mysql --version 或者mysql -V 用于在未登录情况下，查看本机mysql版本：

select version(); ：登录情况下，查看链接的库版本：

登录

mysql -h ip -P 端口 -u 用户名 -p

本机：mysql -u 用户名 -p

服务

使用 service 启动：service mysql start

使用 service 启动：service mysql stop

使用 service 启动：service mysql restart

权限

原理

通过主机名+用户名来识别用户的身份

验证分为两个阶段

阶段一：连接，验证是否有连接权限

阶段2：操作，验证是否有DDL或者DML的操作权限

权限生效时间

用户及权限信息放到名为mysql的库中

如果直接操作这些表来修改权限，需要重启mysql或者
执行 flush privileges,才可生效

用户登录后，mysql会和当前用户创建一个连接，存在内存中，
如果该用户被修改了权限，下一次登录后才会生效

涉及到表

用户表

mysql.user

创建用户

create user 用户名[@主机名] [identified by '密码'];

主机名默认为%，表示可以从任何主机访问

密码可以省略，表示无密码

修改密码

通过管理员

SET PASSWORD FOR '用户名'@'主机' = PASSWORD('密码');

create user 用户名[@主机名] [identified by '密码'];

set password = password('密码');

通过修改mysql.user表修改密码
这种方式要及时刷新权限

update user set authentication_string = password('321')
where user = 'test1' and host = '%';
flush privileges;

遇到的问题

MySQL之PacketTooBigException解决

https://www.jianshu.com/p/9cc501b4fc7d

如果一个服务启动后，和数据库建立连接，这个服务不断，和数据库是同一个连接吗

不是的，需要看数据库连接池中设置的最大连接时间是多少

子主题

sql慢的原因

如果列比较多，使用select* 进行查询

索引失效

for循环中有查库操作

mysql字段类型已java类型的对应关系

datetime --》Date

Mysql 根据一个表数据更新另外一个表

update 更新表 set 字段 = (select 参考数据 from 参考表 where 参考表.id = 更新表.id);
update table_2 m set m.column = (select column from table_1 mp where mp.id= m.id);

update table_1 t1,table_2 t2 set t1.column = t2.column where t1.id = t2.pid;

创建唯一索引/唯一键

ALTER TABLE xxx_table ADD unique(filepath);

alter table xxx_table add constraint uk_filepath unique (filepath);

mysql为啥不推荐使用UUID作为主键

主键类型

自增主键

雪花id

UUID

速率

在数据大于100W的时候插入速率，
并且随着数据量增大，自增主键的
优势更明显

自增>雪花id>uuid

为什么

应该跟主键索引有关

保证索引的有序性

自增主键可以认为是按顺序插入数据的，当一页被写满之后，下一条就会写到新
页中，不会因为页分裂导致数据迁移；

但是，如果使用uuid，因为uuid是随机不重复的，无法准备判断主键值得大小，
这就会导致数据是随机写入页中的，这就会导致两种情况出现：

第一：如果当时的页已经被写满，算出的uuid在此页中
就会导致插入数据的时候，导致数据后移到新的页中

第二：如果此时满页的数据已经被写入磁盘中，
还需要将该数据从磁盘中
读出来，完成写入动作，导致了额外的磁盘IO

类似我们在本子上写作业

查新

select * from xxx

150万条数据查询需要104s左右

select count(id) from xxx

150万条数据查询需要3.2s左右

如何提速到ms

备份以及恢复

1、mysqldump -u root -p database_name > 路径/database_name.back.sql

此命令必须在bin目录下执行

2、source /路径/database_name.back.sql;

此命令必须先登陆客户端，然后创建库，用命令use xxx，切换到目标数据库，然后执行此命令

主从同步

1、两个数据库均要开启binlog，mysql8以上自动开启binlog

https://www.codenong.com/cs107089991/

遇到的问题

start slave;启动报错

原因：
slave_relay_log_info表中依然保留之前relay_log的信息，所以导致启动slave报错

解决方式：
利用mysql提供了工具用来删除记录：slave reset；

查看show slave status\G; 从机状态
Slave_IO_Running：Connecting ；

原因：

解决方式：

MVCC

mysql默认是隐式事务

隐式事务，执行完insert、update、delete、select后事务会自动提交

START TRANSACTION;
执行完开启事务的语句后并不会立即产生事务id，而是在执行语句后才会产生

索引

原理

页：默认大小是16kb，可以修改

磁盘块，一般是4kb，计算机读写磁盘的最小单位

扇区512Byte

原理概述：
1、通过B+树来存储数据，快速检索数据，减少磁盘IO次数
2、通过一次读取一页的数据来快速定位到具体的数据在哪里

失效的场景

like '%xxx'

索引字段参与函数运算

索引是字符型的列，但是却用数字去检索数据

EXPLAIN SELECT * from t where c = 1

什么是索引的区分度

count（distinct）/count（总数）

什么是索引下推

直接判断复合索引的第二个字段是不是满足条件，不满足直接过滤掉，减少回表次数

什么是索引覆盖

非聚簇索引，叶子节点存储的是主键，所以如果直接检索主键值就不需要有回表的过程

select ID from T where name = 'zhangsan',name 字段有索引

什么是回表

复合索引的排序原则

按索引定义的顺序排序

聚集索引和非聚集索引的区别

重建索引

重建主键索引

alter table T engine=InnoDB

重建非主键索引

alter table T drop index k;
alter table T add index(k);

创建索引的语法

CREATE INDEX index_name ON table_name (column_list)

事务

什么是事务

一组原子性操作

开启事务

mysql默认开启隐式事务

show variables like 'autocommit';
VALUE是ON表示开启隐式事务

显示开启事务

set autocommit=0;

start transaction;

开启只读事务

start transaction read only;

事务的特性

ACID

原子性

一组操作，要么都成功，要么都失败

atomicity

一致性

consistency

事务提交前后，数据库的完整性约束没有被破坏

隔离性

isolation

并发环境中，不同事务之间不会互相影响

持久性

durability

事务一旦提交，对数据库修改是永久性的，不会因为数据库宕机而受影响

原子性，一致性、持久性是通过undo log和redo log来完成的

锁与事务的关系

是通过锁机制来保证事务的隔离性的

并发事务带来的问题

脏读

B事务修改的数据未提交，A事务就可以读到

不可重复读

A事务中读取一条数据，A事务未提交的情况下，B事务修改完数据提交了，这时A事务在次读取该条数据的时候，数据变了

幻读

是针对删除和新增，A事务开启，读取数据5条，同时B事务开启，删除其中一条并提交事务，这时A事务再去查询会发现少了一条数据

事务的隔离级别

是通过视图来保证的

读未提交

无视图概念

RC

在sql执行的时候开始创建视图

RR

事务启动的时候创建，整个事务存在期间，均使用此视图

mysql的RR是可以防止幻读产生

可串行

直接用加锁的方式避免并行访问

查看隔离级别

show variables like 'transaction_isolation';

select @@tx_isolation

修改隔离级别

通过修改配置文件

set [ global | session ] transaction isolation level Read uncommitted | Read committed | Repeatable Read| Serializable;

如果选择global，意思是此语句将应用于之后的所有session，而当前已经存在的session不受影响。

如果选择session，意思是此语句将应用于当前session内之后的所有事务。

如果什么都不写，意思是此语句将应用于当前session内的下一个还未开始的事务

事务的传播特性

PROPAGATION_REQUIRED

存在，加入。不存在，创建新的事务，最常用的一种

PROPAGATION_REQUIRES_NEW

存在，挂起，新启事务执行。不存在，新启事务

PROPAGATION_SUPPORTS

存在，加入。不存在，就以非事务运行

PROPAGATION_NOT_SUPPORTED

非事务方式执行，存在事务，挂起

PROPAGATION_MANDATORY

存在，加入。不存在，抛出异常

PROPAGATION_NEVER

非事务方式执行。存在，抛出异常

PROPAGATION_NESTED

存在，则在嵌套事务内执行，不存在，开启新的

长事务

执行时间较长的事务

分布式事务

是指允许多个独立的事务资源参与到一个全局事务中

使用分布式事务的时候，Innodb的隔离级别必须是serializable

XA是分布式事务协议，分布式事务又称为XA事务

XA 事务的组成

XA事务由一个或者多个资源管理器（RM）

一个数据库就是一个RM

一个事务管理器（TM）

协调参与全局事务中的各个事务

一个应用程序（APP）

指定全局事务中的操作（insert、delete、update、select）

两阶段提交原理

两阶段提交

第一阶段：所有事务prepare，准备好提交

第二阶段：TM告诉RM执行commit或者rollback，任何一个RM出现问题，所有的RM均要回滚

锁

全局锁

锁定整个库实例

使用场景

全库的逻辑备份

加锁/释放锁

flush tables with read lock ;

unlock tables ;

表级锁

一种是元数据锁（metadata lock，MDL）

不用手动添加，当修改表结构时自动添加

表锁

表锁的语法是lock tables … read/write

SHOW PROCESSLIST；lyqtest是库名，在更新t_user这张表时，被metadata lock阻塞

read，以只读的方式去锁住表

所有会话不能修改包含自己，但是均可以读数据

write，以写的方式锁住表

只有当前会话可以修改，但是不能查询，查询会阻塞，其它会话不能修改和查询

unlock tables主动释放锁

行锁

排它锁

select ... for update

同一行记录只能加一把排他锁，可以加多把共享锁

共享锁

select... lock in share mode

加了S锁和X锁的数据，只能被其它事务读，不能被修改

间隙锁 Gap Lock

锁被加在不存在的空闲空间，不包含本身

1、通过主键索引加X锁，进行一条记录锁定，不会触发间隙锁

例如：SELECT * FROM `test` WHERE `id` = 5 FOR UPDATE;

2、通过主键范围查找

START TRANSACTION;
select * FROM t where id BETWEEN 5 and 15 for UPDATE

锁住范围（5,10）、（10，15）、（15,20）

也就是5到20内是插不进数据的

3、锁住不存在的数据

select * FROM t where id = 3 for UPDATE

会产生间隙锁，锁住（0,3）这个范围

间隙锁和行锁合称next-key lock，mysql 是通过 next-key lock 来防止幻读的

锁定的范围是（ ]，左开右闭

查看锁的方式

show engine innodb status

information_schema

innodb_trx

innodb_locks

innodb_locks_waits

执行引擎

InnoDB

支持行数

支持事务

MyISAM

不支持事务

不支持行锁

执行计划分析

log

redo log

是一个物理文件,所有连接共享的

redo log 有多个，多个之间采用环形结构和几个变量来做到重复利用的

当redo log满了，或者系统比较闲的时候，会对redo log文件进行处理

redo log buffer

内存中一块区域

1、开启事务的时候生成一个全局事务trx_id = xx
2、开始在内存中修改数据
3、当提交事务的时候，会将redo log buffer 中的内容写到磁盘中的redo log中

写入内容大概长这样

1.start trx=10;
2.写入rb1
3.写入rb2
4.end trx=10;

注意：一个trx_id,一定是start 和end成对出现在redo log，才表示这一组操作成功

binlog

作用

详细记录了对数据库进行了什么操作，可以理解成记录的是sql语句

主从同步

从库读取主库的binlog信息，然后在从库中执行

增量备份

binlog cache

一个事务中可能会有很多操作，mysql会把整个过程中产生的binlog写入binlog cache

一个事务的binlog是不能被分开的，所以无论这个事务多大，也要确保一次性写入

binlog_cache_size

控制单个线程内binlog cache 所占内存大小，如果超过这个大小就要暂存磁盘

每个线程一个binlog cache

查看binlog 是否开启

SHOW VARIABLES like 'log_bin'

开启binlog

修改配置文件

注意：5.7以上一定要加service-id，
另外创建的目录一定要加上mysql组的权限
chown -R mysql.mysql /var/lib/mysql

server_id=2
log_bin=mysql-bin
binlog_format=ROW

参考：https://www.cnblogs.com/clschao/articles/8192345.html

二阶段提交

作用：确保redo log 和binlog 二者数据一致

1、开启事务
2、修改写入 redo log buffer
3、修改写入 binlog cache
4、cimmit；
5、将 redo log buffer 数据写入redo log ，这步叫redo log prepare
6、将bilog cache 写入 binlog
7、像redo log 中插入一条数据，end txr=xxx，表示redo log中这个事务完成，这步叫redo log commit

undolog

mvcc会使用这个日志