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
函数
- 格式
$.字段名
可以用来查询对应的 value
json_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
1366 (HY000): Incorrect string value: ‘\xFC\x00\x00\x00\x00\x00…’ for column
- 原因字符集不兼容,解决方法:
- 调整 MySQL 字符集为
utf8mb4
- 或剔除出入字符中包含的非 UTF-8 字符