MySQL 介绍
介绍
- Navicat 可视化工具
mysql-workbench MySQL 官方的可视化开发和管理平台，ubuntu 安装命令
snap connect mysql-workbench-community
snap connect mysql-workbench-community:password-manager-service
snap connect mysql-workbench-community:ssh-keys
- Liquibase 数据库版本管理工具
- mysql 端口
3306 MySQL Classic 协议33060 MySQL X 协议，由 mysqlx_port 配置33062 MySQL Classic 协议的管理端口，由 admin_port 配置，MySQL 8.0.14 起提供
- MYSQL SQL审核工具
.ibd 和 .frm 文件
- 配置如下参数时，每个 innodb 表一个数据
.ibd 文件，包括数据和索引
.ibd 文件通常包含了多个类型的页：
表空间描述页（FSP_HDR）：描述整个表空间的属性和状态信息段描述页（IBUF_BITMAP）：描述表的段信息，包括表的结构、索引和数据页的位置等数据页（InnoDB Data Pages）：表的行数据索引页（InnoDB Index Pages）：表的索引数据
.ibd 分析工具
- page_parser
- mysqlfrm
- innodb_space
.frm 文件是表定义文件，保存表的结构信息，如表名、列名、列数据类型、列长度等
[mysqld]
innodb_file_per_table = ON
innodb_page_size = 16384 # 默认 16KB
# 查看 MySQL 当前参数：show global variables like "innodb_file%";
命令介绍
部署
docker 部署
docker run -it -d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root mysql:5.7
docker-compose 部署
[mysqld]
character-set-system=utf8mb4
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
使用
ALTER
-- 更改数据库字符集
ALTER DATABASE [database_name]
CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
-- 更改表字符集
ALTER TABLE [table_name]
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Change a column
ALTER TABLE [table_name]
CHANGE [column_name] [column_name] VARCHAR(255)
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 调整表字段顺序
ALTER TABLE [table_name]
CHANGE COLUMN `x1` `x1` LONGTEXT NULL DEFAULT NULL AFTER `x2`;
- 配置字符集
- ALTER TABLE 的锁
- MySQL 5.6 之前的版本，整个DDL过程的就是全程锁表
- MySQL 5.6 之后的版本，新增
ONLINE DDL 的功能，大大减少锁表时间
-
- 原始表加写锁
-
- 按照原始表和执行语句的定义，重新定义一个空的临时表，并申请rowlog的空间
-
- 拷贝原表数据到临时表，此时的表数据修改操作（增删改）都会存放在rowlog中。此时该表客户端可以进行操作的
-
- 原始表数据全部拷贝完成后，会将rowlog中的改动全部同步到临时表，这个过程客户端是不能操作的
-
- 当原始表中的所有记录都被Copy临时表，并且Copy期间客户端的所有增删改操作都同步到临时表。再将临时表命名为原始表表名
- 耗时经验值：
- 16核32G 500w 数据 ALTER TABLE 加字段时，不加 default 65s，加 default 220s，锁表时间占后50%左右
optimize table
delete from table_name 之后，表会产生大量的碎片空间，使用 optimize table 可以释放该空间碎片
OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL]
TABLE tbl_name [, tbl_name] ...
- 一般输出 Table does not support optimize, doing recreate + analyze instead，等价于 `alter table <table_name> ENGINE=InnoDB`
optimize table 后 .ibd 文件的空间与 mysqldump 的大小基本持平- 900w数据，20G 表空间，耗时 650s，锁部分时间很短，基本不影响表的插入
- 参考
DELETE
DELETE 语句的锁定行为主要包括两个方面
表级锁(Table Locking) 不添加限定条件时产生（不推荐）行级锁(Row Locking) 添加限定条件，如 where、limit 等时出现，推荐删除数据时指定
rename 重命名表
# rename
rename table <old-table-name> to <new-table-name>;
# aalter
alter table <old-table-name> rename as <new-table-name>;
- MySQL 5.7+ 开始提供
json_extract 函数
- 格式
$.字段名 可以用来查询对应的 valuejson_extract('字段', '$.key') json 对象json_extract('字段', '$[数组下标].key') json 数组
JSON_EXTRACT(json_doc, path[, path] …)
# 创建数据库
create database test;
use test
# 创建表
CREATE TABLE `json_table` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
`value` json DEFAULT NULL COMMENT 'json value',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
# 表结构
mysql> desc json_table;
+-------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| value | json | YES | | NULL | |
+-------+------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> show create table json_table;
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| json_table | CREATE TABLE `json_table` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
`value` json DEFAULT NULL COMMENT 'json value',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
# 插入数据
insert into `json_table` values (1, '{"name": "xie", "age": 18}');
insert into `json_table` values (2, '{"name": "xianbin", "site": "https://www.xiexianbin.cn"}');
# 查询所有字段
mysql> select * from json_table;
+----+----------------------------------------------------------+
| id | value |
+----+----------------------------------------------------------+
| 1 | {"age": 18, "name": "xie"} |
| 2 | {"name": "xianbin", "site": "https://www.xiexianbin.cn"} |
+----+----------------------------------------------------------+
2 rows in set (0.00 sec)
# 简单示例
mysql> select json_extract('{"name":"xianbin","age":"18"}', "$.age");
+-------------------------------------------------------+
| json_extract('{"name":"xianbin","age":"18"}',"$.age") |
+-------------------------------------------------------+
| "18" |
+-------------------------------------------------------+
1 row in set (0.00 sec)
# 查询 json 的 name 字段
mysql> select json_extract(`value`, '$.name') from `json_table`;
+---------------------------------+
| json_extract(`value`, '$.name') |
+---------------------------------+
| "xie" |
| "xianbin" |
+---------------------------------+
2 rows in set (0.00 sec)
# 默认值为 NULL
mysql> select json_extract(`value`, '$.name') as `name`, json_extract(`value`, '$.site') as `site` from `json_table`;
+-----------+-----------------------------+
| name | site |
+-----------+-----------------------------+
| "xie" | NULL |
| "xianbin" | "https://www.xiexianbin.cn" |
+-----------+-----------------------------+
2 rows in set (0.00 sec)
# 数组示例
mysql> insert into `json_table` values (3, '[{"name": "xiexianbin", "site": "https://www.xiexianbin.cn"}]');
# 查询数据的内容
mysql> select json_extract(`value`, '$[0].name') from `json_table` where id = 3;
+------------------------------------+
| json_extract(`value`, '$[0].name') |
+------------------------------------+
| "xiexianbin" |
+------------------------------------+
1 row in set (0.00 sec)
# json 查询条件
mysql> select * from `json_table` where json_extract(`value`, '$.name') = 'xianbin';
+----+----------------------------------------------------------+
| id | value |
+----+----------------------------------------------------------+
| 2 | {"name": "xianbin", "site": "https://www.xiexianbin.cn"} |
+----+----------------------------------------------------------+
1 row in set (0.00 sec)
RETURNING
- 生效版本
- MySQL 5.7 20210330 及以上
- MySQL 8.0 20220330 及以上
- 使用
DELETE...RETURNING 语句返回前镜像数据INSERT/REPLACE...RETURNING 返回后镜像数据
show processlist
show processlist; 查看当前所有数据库连接的 session 状态，非root用户仅能看到自己正在运行的线程（也可以配置权限）
mysql> show processlist;
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 28 | Waiting on empty queue | NULL |
| 10 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
2 rows in set (0.00 sec)
说明：
Id 线程的唯一标识，可以通过 kill <id> 命令杀掉线程
- 本质为
information_schema.processlist 表的主键
User 启动该线程的用户Host 发送请求的客户端的 IP 和 端口号db 当前执行的命令的数据库上
Command 此刻该线程正在执行的命令，参考
- `Binlog Dump` This is a thread on a replication source for sending binary log contents to a replica.
- `Change user` The thread is executing a change user operation.
- `Close stmt` The thread is closing a prepared statement.
- `Connect` Used by replication receiver threads connected to the source, and by replication worker threads.
- `Connect Out` A replica is connecting to its source.
- `Create DB` The thread is executing a create database operation.
- `Daemon` This thread is internal to the server, not a thread that services a client connection.
- `Debug` The thread is generating debugging information.
- `Delayed insert` The thread is a delayed insert handler.
- `Drop DB` The thread is executing a drop database operation.
- `Error`
- `Execute` The thread is executing a prepared statement.
- `Fetch` The thread is fetching the results from executing a prepared statement.
- `Field List` The thread is retrieving information for table columns.
- `Init DB` The thread is selecting a default database.
- `Kill` The thread is killing another thread.
- `Long Data` The thread is retrieving long data in the result of executing a prepared statement.
- `Ping` The thread is handling a server ping request.
- `Prepare` The thread is preparing a prepared statement.
- `Processlist` The thread is producing information about server threads.
- `Query` Employed for user clients while executing queries by single-threaded replication applier threads, as well as by the replication coordinator thread.
- `Quit` The thread is terminating.
- `Refresh` The thread is flushing table, logs, or caches, or resetting status variable or replication server information.
- `Register Slave` The thread is registering a replica server.
- `Reset stmt` The thread is resetting a prepared statement.
- `Set option` The thread is setting or resetting a client statement execution option.
- `Shutdown` The thread is shutting down the server.
- `Sleep` The thread is waiting for the client to send a new statement to it.
- `Statistics` The thread is producing server status information.
- `Time` Unused.
Time 该线程运行到该状态的时间State 线程运行 Command 当前的状态，参考
- `After create`
- `altering table`
- `Analyzing`
- `checking permissions`
- `Checking table`
- `cleaning up`
- `closing tables`
- `committing alter table to storage engine`
- `converting HEAP to ondisk`
- `copy to tmp table`
- `Copying to group table`
- `Copying to tmp table`
- `Copying to tmp table on disk`
- `Creating index`
- `Creating sort index`
- `creating table`
- `Creating tmp table`
- `deleting from main table`
- `deleting from reference tables`
- `discard_or_import_tablespace`
- `end`
- `executing`
- `Execution of init_command`
- `freeing items`
- `FULLTEXT initialization`
- `init`
- `Killed`
- `Locking system tables`
- `logging slow query`
- `login`
- `manage keys`
- `Opening system tables`
- `Opening tables`
- `optimizing`
- `preparing`
- `preparing for alter table`
- `Purging old relay logs`
- `query end`
- `Receiving from client`
- `Removing duplicates`
- `removing tmp table`
- `rename`
- `rename result table`
- `Reopen tables`
- `Repair by sorting`
- `Repair done`
- `Repair with keycache`
- `Rolling back`
- `Saving state`
- `Searching rows for update`
- `Sending data`
- `Sending to client`
- `setup`
- `Sorting for group`
- `Sorting for order`
- `Sorting index`
- `Sorting result`
- `starting`
- `statistics`
- `System lock`
- `update`
- `Updating`
- `updating main table`
- `updating reference tables`
- `User lock`
- `User sleep`
- `Waiting for commit lock`
- `waiting for handler commit`
- `Waiting for tables`
- `Waiting for table flush`
- `Waiting for lock_type lock`
- `Waiting on cond`
- `Writing to net`
Info 一般记录的是线程执行的语句
- 默认只显示前 100 个字符，查看全部信息可使用
show full processlist;
事务
- 参考
事务保证一组原子性的操作，要么全部成功，要么全部失败。四种常见的隔离级别：未提交读（Read UnCommitted） 事务中的修改，即使没提交对其他事务也是可见的
提交读（Read Committed） 一个事务开始时，只能看见已提交的事务所做的修改
- 事务未提交之前，所做的修改对其他事务是不可见的
- 也叫不可重复读，同一个事务多次读取同样记录可能不同
可重复读（RepeatTable Read） 同一个事务中多次读取同样的记录结果时结果相同可串行化（Serializable） 最高隔离级别，强制事务串行执行
F&Q
Error 1366 (HY000): Incorrect string value: ‘\xFC\x00\x00\x00\x00\x00…’ for column
Error 1114 (HY000): The table ’’ is full