MySQL下篇

下篇说明

字符集

  • utf8（utf8mb3）：用1~3个字节表示字符。
  • utf8mb4：用1~4个字节表示字符（如占4个字符的表情）。
字符集 描述
character_set_client 服务器解码请求时使用的字符集
character_set_connection 服务器处理请求时会把请求字符串从character_set_client 转为character_set_connection
character_set_results 服务器向客户端返回数据时使用的字符集

大小写规范

  • 数据库名、表名、表的别名、变量名严格区分大小写
  • 关键字、函数名不区分大小写
  • 列名、列的别名不区分大小写

sql_mode

  • 严格模式
  • 宽松模式：有些错误会自动处理

数据目录

文件位置

/var/lib/mysql/（/www/server/data/）：数据目录

/usr/bin/和/usr/sbin/：相关命令目录

/usr/share/mysql-8.0/：命令及配置文件

/etc/mysql/：配置文件（如my.cnf）

默认数据库

表在文件系统中的表示

InnoDB存储引擎模式

5.7

数据目录/ibdata1/：系统表空间

数据目录/数据库名/db.frm：数据库元数据

数据目录/数据库名/表名.frm：表结构

数据目录/数据库名/表名.ibd：独立表空间

8.0

数据目录/ibdata1/：系统表空间

数据目录/数据库名/表名.ibd：独立表空间

MyISAM存储引擎模式

数据目录/数据库名/表名.frm(或.sdi)：表结构

数据目录/数据库名/表名.MYD：表数据

数据目录/数据库名/表名.MYI：表索引

用户与权限管理

用户管理

查看

SELECT `HOST`, `user` FROM `user`;
+-----------+------------------+
| HOST      | user             |
+-----------+------------------+
| %         | zhangsan         |	# %表示支持任何ip下的用户进行连接
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+

创建

user和host不能都相同。

CREATE USER 'zhangsan' IDENTIFIED BY 'abc123';	# 使用用户名和密码创建
CREATE USER 'zhangsan'@'localhost' IDENTIFIED BY 'abc123';	# 指定host

修改

UPDATE `user` SET `user` = 'lisi' where `user` = 'zhangsan';

# 修改之后要刷新权限
FLUSH PRIVILEGES;

删除

DROP USER 'lisi';	# 默认删除host为%的
DROP USER 'lisi'@'localhost';

设置当前用户密码

ALTER USER USER() IDENTIFIED BY 'abcabc';
SET PASSWORD = 'abcabc';

修改其他用户密码

ALTER USER 'zhangsan'@'%' IDENTIFIED BY 'abcabc';
SET PASSWORD FOR 'zhangsan'@'%' = 'abcabc';

权限管理

SHOW PRIVILEGES;	# 查看有哪些权限
权限类别 具体权限
表权限 'Select', 'Insert', 'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter'
列权限 'Select', 'Insert', 'Update', 'References'
过程权限 'Execute', 'Alter Routine', 'Grant'

授予权限

GRANT 权限1, 权限2... ON 数据库名.表名 TO 用户名@地址;

GRANT SELECT, UPDATE ON atguigudb.* TO 'zhangsan'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'zhangsan'@'localhost';	# 赋予所有的权限，除了GRANT OPTION
GRANT 权限1, 权限2... ON 数据库名.表名 TO 用户名@地址  WITH GRANT OPTION;		# 该用户有赋予别人权限的权限

查看权限

SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR 'user'@'host';

收回权限

REVOKE 权限1, 权限2... ON 数据库名.表名 FROM 用户名@地址;

REVOKE SELECT ON atguigudb.* FROM 'zhangsan'@'localhost';
REVOKE ALL PRIVILEGES ON *.* FROM 'zhangsan'@'localhost';

权限表

mysql数据库中的user,db,table_priv,column_priv,proc_priv表。

角色管理

创建

CREATE ROLE 'boss';		# 不写host默认为%
CREATE ROLE 'manager'@'localhost';

给角色赋予权限

GRANT 权限1, 权限2... ON 表名 TO '角色名'[@'host名']

GRANT SELECT, UPDATE ON atguigudb.employees TO 'manager';
GRANT ALL PRIVILEGES ON *.* TO 'boss'@'%';

查看角色权限

SHOW GRANTS FOR 'manager';

回收角色权限

REVOKE 权限1, 权限2... ON 表名 FROM '角色名'[@'host名']

REVOKE UPDATE ON atguigudb.employees FROM 'manager';

删除角色

DROP ROLE '角色名';

给用户赋予角色

GRANT 'manager'@'%' TO 'zhang'@'%';

查询当前角色

SELECT CURRENT_ROLE();

激活角色

SET DEFAULT ROLE 'manager'@'%' TO 'zhangsan'@'%';
SET DEFAULT ROLE ALL TO 'zhangsan'@'%';
SET GLOBAL activate_all_roles_on_login=NO;	# 设置此变量，赋予角色时自动激活

撤销用户角色

REVOKE ROLE FROM 用户;

设置强制角色

逻辑架构

  • Connectors
  • 第1层：连接层
  • 第2层：服务层
  • 第3层：引擎层
  • 存储层

SQL执行流程

查看profile

SELECT @@profiling;		# 查看profile开关
SET profiling = 1;		# 打开开关

SHOW profiles;		# 查询查询记录
SHOW profile;		# 查询最近一条语句的详细记录
SHOW profile for query 7;	# 查询具体某一条语句的详细记录

缓冲池

show variables like 'innodb_buffer_pool_size';	# 查看缓冲池大小
set global innodb_buffer_pool_size = 268435456;	# 设置缓冲池大小

可以设置多个缓冲池（建议缓冲池大于1G的时候设置多个）：

show variables like 'innodb_buffer_pool_instances';		# 查看缓冲池数量

[server]
innodb_buffer_pool_instances = 2;	# 在配置文件中修改

存储引擎

待补充

事务基础知识

只有InnoDB支持事务。

ACID

事务的状态

  • 活动的
  • 部分提交的：还没有写进磁盘
  • 提交的：写进磁盘
  • 失败的
  • 中止的：回滚执行完

显式事务

使用START TRANSACTION 或 BEGIN开启事务。START TRANSACTION后可跟read only/read write（默认）/with consistent snapshot。

使用COMMIT或ROLLBACK结束事务。

CREATE TABLE test_user(`name` VARCHAR(15) PRIMARY KEY);
BEGIN;
INSERT INTO test_user VALUES('张三');	# 还没有提交
INSERT INTO test_user VALUES('张三');
ROLLBACK;
# 第4行的语句会由于主键重复而错误，rollback之后会撤销之前的语句，所以现在查询的话表是空的

SELECT @@completion_type;
SET @@completion_type = 1;	# 开启链式事务

BEGIN;
INSERT INTO test_user VALUES('张三');
COMMIT;

INSERT INTO test_user VALUES('李四');
INSERT INTO test_user VALUES('李四');

ROLLBACK;
# 此时查询的话表中只有张三

保存点

SAVEPOINT 保存点名称;
ROLLBACK TO 保存点名称;
RELEASE SAVEPOINT 保存点名称;	# 删除保存点
语句1
语句2
SAVEPOINT s1;
语句3
ROLLBACK TO s1;		# 回滚到s1的状态然后继续执行语句4
语句4

隐式事务

autocommit关键字：默认是ON，此时每一条语句执行完后都会自动提交，可以把每一条语句看作是一个事务。只对DML操作有效，对DDL操作无效。

SHOW VARIABLES LIKE 'autocommit';

方式一

SET autocommit = FALSE;
语句1
语句2
......
COMMIT;	# 或ROLLBACK

方式二

在autocommit为true的情况下，使用START TRANSACTION 或 BEGIN开启事务，那么DML操作就不会自动提交数据。

START TRANSACTION;
语句1
语句2
......
COMMIT;	# 或ROLLBACK

隐式提交数据的情况

以下情况会导致隐式提交前面语句所属的事务：

  • 使用DDL：使用CREATE、ALTER、DROP等语句时

  • 使用或修改mysql数据库中的表：对用户、权限等的操作

  • 上一个事务还没提交或回滚就又使用了START TRANSACTION或BEGIN

数据并发问题

以下四种按严重性递减。

  • 脏写：写了但写了之后又被覆盖了
  • 脏读：读到了错误的数据
  • 不可重复读：多次读取结果不一样
  • 幻读：比前一次读到了更多的数据

四种隔离级别

查看隔离级别

SHOW VARIABLES LIKE 'transaction_isolation';
SELECT @@transaction_isolation;

设置隔离级别

事务日志

redo日志

组成

  • redu log buffer（redo日志缓冲区）：服务器启动时申请的连续内存空间，划分为若干连续的redo log block，每个block512字节。其大小可以通过参数来设置：

    SHOW VARIABLES LIKE '%innodb_log_buffer_size%';

  • redu log file：保存在磁盘中，如数据目录中的ib_logfile0和ib_logfile1。

redu log的刷盘策略

undo日志

执行INSERT、DELETE、UPDATE时会产生undo日志，SELECT不会。undo日志也会产生redo日志。

类型

锁

按数据操作类型

共享锁、读锁（S Lock）

SELECT ... LOCK IN SHARE MODE;
# 或
SELECT ... FOR SHARE;	# 8.0语法

排他锁、写锁（X Lock）

SELECT ... FOR UPDATE;

示例

# 多个终端执行以下代码可以查询成功。如果之前有X锁，则要等待X锁释放
BEGIN;
SELECT * FROM test_user LOCK IN SHARE MODE;
# 之前有S锁或X锁时会阻塞
BEGIN;
SELECT * FROM test_user FOR UPDATE;

MySQL8新特性

X锁的加锁情况

简单来说：delete加X锁；update未修改主键且占用空间无变化加X锁；update其他情况先delete再insert；insert加隐式锁。

按数据操作粒度

表锁

查看加锁情况
SHOW OPEN TABLES;
SHOW OPEN TABLES WHERE in_use > 0;
S锁、X锁
LOCK TABLES test_user READ;		# 加S锁
LOCK TABLES test_user WRITE;	# 加X锁
UNLOCK TABLES;		# 释放锁

意向锁

自增锁（AUTO-INC锁）

表对AUTO_INCREMENT的主键加的锁。从MySQL8.0开始交错锁是默认设置，即不会使用自增锁，所以插入时生成的值可能是不连续的，但一定是唯一且单调的。

元数据锁（MDL锁）

行锁

只有InnoDB支持。

记录锁（Record Lock）

如：锁定了id为1的那条记录。

间隙锁（Gap Lock）

防止插入幻影记录。这里用S锁或X锁没有区别。

比如有id为1、5、10的记录。进程1（的事务中）查询id为3的记录，则在1到5之间加间隙锁，此时其他进程可以查询id为1到5的记录但不能插入1到5的记录。如果进程1查询id为12的记录，则其他进程不能在10到∞插入记录。

临键锁（Next-Key Lock）

插入意向锁（Insert Intention Lock）

页锁

按对待锁的态度

悲观锁

悲观锁总是假设最坏的情况，每次拿数据都会上锁。就是之前的锁机制，可以保证正确性。适合写操作多。

乐观锁

不采用数据库自身的锁机制，而是通过程序来实现。如采用版本号机制或CAS机制。java.util.concurrent.atomic包就是乐观锁的一种实现方式。适合读操作多。

按加锁的方式

隐式锁

（有点懵）

显示锁

就是可以查询到的锁。显示的S锁、X锁。

SELECT * FROM performance_schema.data_lock_waits\G;

全局锁

对整个数据库实例加锁。如进行备份时。

FLUSH TABLES WITH READ LOCK;

死锁

innodb_lock_wait_timeout：事务超时时间，默认50s。

innodb_deadlock_detect：是否开启死锁检测，默认on。

视频从182集开始

Java开发
#数据库 #MySQL
MySQL下篇
https://shuusui.site/blog/2022/08/13/mysql-2/
作者
Shuusui
发布于
2022年8月13日
更新于
2022年8月13日
许可协议