MySQL 介绍

发布时间: 更新时间: 总字数:2595 阅读时间:6m 作者: 分享 复制网址

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 此刻该线程正在执行的命令,参考
    • 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 字符
Home Archives Categories Tags Statistics
本文总阅读量 次 本站总访问量 次 本站总访客数