首页  思维导图  详情

mysql知识图谱

2022-07-26 11:07:31   0  举报





AI智能生成

为你推荐

查看更多



心理知识图谱

微服务治理知识图谱建模

知识图谱（Knowledge Graph）

总结的知识点，可以应用于面试与日常开发（持续更新）

InnoDB

作者其他创作

大纲/内容

group by

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

where与having区别

分组查询

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

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会和当前用户创建一个连接，存在内存中，如果该用户被修改了权限，下一次登录后才会生效

权限生效时间

mysql.user

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

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

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

创建用户

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

通过管理员

set password = password('密码');

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

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

修改密码

用户表

涉及到表

权限

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

MySQL之PacketTooBigException解决

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

子主题

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

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

索引失效

for循环中有查库操作

sql慢的原因

遇到的问题

datetime --》Date

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

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);

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

ALTER TABLE xxx_table ADD unique(filepath);alter table xxx_table add constraint uk_filepath unique (filepath);

创建唯一索引/唯一键

自增主键

雪花id

UUID

主键类型

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

自增>雪花id>uuid

速率

应该跟主键索引有关

保证索引的有序性

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

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

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

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

类似我们在本子上写作业

为什么

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

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

select * from xxx

如何提速到ms

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

select count(id) from xxx

查新

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

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

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

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

备份以及恢复

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

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

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

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

start slave;启动报错

原因：

解决方式：

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

主从同步

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

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

mysql默认是隐式事务

MVCC

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

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

扇区512Byte

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

like '%xxx'

索引字段参与函数运算

EXPLAIN SELECT * from t where c = 1

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

失效的场景

count（distinct）/count（总数）

什么是索引的区分度

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

什么是索引下推

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

什么是索引覆盖

什么是回表

按索引定义的顺序排序

复合索引的排序原则

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

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)

创建索引的语法

索引

一组原子性操作

什么是事务

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

mysql默认开启隐式事务

set autocommit=0;

start transaction;

显示开启事务

start transaction read only;

开启只读事务

开启事务

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

atomicity

原子性

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

consistency

一致性

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

isolation

隔离性

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

durability

持久性

ACID

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

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

锁与事务的关系

事务的特性

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

脏读

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

不可重复读

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

幻读

并发事务带来的问题

是通过视图来保证的

无视图概念

读未提交

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

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

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

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

可串行

show variables like 'transaction_isolation';

select @@tx_isolation

查看隔离级别

通过修改配置文件

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

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

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

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

修改隔离级别

事务的隔离级别

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

PROPAGATION_REQUIRED

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

PROPAGATION_REQUIRES_NEW

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

PROPAGATION_SUPPORTS

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

PROPAGATION_NOT_SUPPORTED

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

PROPAGATION_MANDATORY

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

PROPAGATION_NEVER

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

PROPAGATION_NESTED

事务的传播特性

执行时间较长的事务

长事务

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

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

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

一个数据库就是一个RM

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

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

一个事务管理器（TM）

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

一个应用程序（APP）

XA 事务的组成

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

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

两阶段提交

两阶段提交原理

分布式事务

事务

锁定整个库实例

全库的逻辑备份

使用场景

flush tables with read lock ;

unlock tables ;

加锁/释放锁

全局锁

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

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

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

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

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

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

write，以写的方式锁住表

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

unlock tables主动释放锁

表锁

表级锁

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

select ... for update

排它锁

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

select... lock in share mode

共享锁

行锁

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

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

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

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

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

2、通过主键范围查找

select * FROM t where id = 3 for UPDATE

3、锁住不存在的数据

间隙锁 Gap Lock

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

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

show engine innodb status

innodb_trx

innodb_locks

innodb_locks_waits

information_schema

查看锁的方式

锁

支持行数

支持事务

InnoDB

不支持事务

不支持行锁

MyISAM

执行引擎

执行计划分析

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

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

内存中一块区域

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

1.start trx=10;2.写入rb13.写入rb24.end trx=10;

写入内容大概长这样

redo log buffer

redo log

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

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

增量备份

作用

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

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

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

binlog_cache_size

每个线程一个binlog cache

binlog cache

SHOW VARIABLES like 'log_bin'

查看binlog 是否开启

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

server_id=2log_bin=mysql-binbinlog_format=ROW

修改配置文件

开启binlog

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

binlog

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

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

二阶段提交

mvcc会使用这个日志

undolog

log

mysql

 收藏

立即使用

mysql知识图谱