MySQL 介绍

发布时间: 更新时间: 总字数:2755 阅读时间:6m 作者: IP上海 分享 网址

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 部署

  • my.cnf
[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 的功能,大大减少锁表时间
      1. 原始表加写锁
      1. 按照原始表和执行语句的定义,重新定义一个空的临时表,并申请rowlog的空间
      1. 拷贝原表数据到临时表,此时的表数据修改操作(增删改)都会存放在rowlog中。此时该表客户端可以进行操作的
      1. 原始表数据全部拷贝完成后,会将rowlog中的改动全部同步到临时表,这个过程客户端是不能操作的
      1. 当原始表中的所有记录都被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>;

json_extract 处理 json 字段

  • 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 当前执行的命令的数据库上
    • 未指定数据库该值为 NULL
  • Command 此刻该线程正在执行的命令,参考
thread-commands ...
  - `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 当前的状态,参考
general-thread-states ...
  - `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

  • 原因字符集不兼容,解决方法(扩展Unicode 和 UTF-8 介绍):
    • 调整 MySQL 字符集为 utf8mb4
    • 或剔除出入字符中包含的非 UTF-8 字符

Error 1114 (HY000): The table ’’ is full

  • 磁盘满了导致的

Error 2006: MySQL server has gone away

  • 配置 my.cnf 并重启
[mysqld]
wait_timeout=90000
Home Archives Categories Tags Statistics
本文总阅读量 次 本站总访问量 次 本站总访客数