MySQL-基础

发布于: 2018-03-08 12:44:26 更新于: 2021-06-30 15:05:26 分类： 开 发 标签： Tag(name='MySQL', count=20) Tag(name='数据库', count=43)

MySQL 日常使用笔记与备忘

MySQL 5.7 Reference Manual (右上角可切换文档版本 5.6/5.7/8.0) https://dev.mysql.com/doc/refman/5.7/en/

数据库内核月报 - 阿里 MySQL 内核组，文章质量很高 http://mysql.taobao.org/monthly/

叶金荣 - ORACLE MySQL ACE https://imysql.com/

MySQL字段名规范

9.2 Schema Object Names https://dev.mysql.com/doc/refman/5.7/en/identifiers.html

标识符可以用反引号引住，也可以不引住，如果标识符包含特殊字符或者本身是保留字，则必须用反引号引住。 MySQL 内部使用 Unicode(UTF-8) 保存标识符，在 BMP(Basic Multilingual Plane) 中的 Unicode 字符可出现在标识符中，但增补字符不允许。

1、允许出现在无反引号标识符中的字符：

  • ASCII 字符，[0-9,a-z,A-Z$_] 大小写字母、数字、dollar符、下划线
  • U+0080 到 U+FFFF 的 Unicode 字符

2、允许出现在反引号标识符中的字符，包括除了 U+0000 外的全部 BMP 字符：

  • U+0001 到 U+007F 的 ASCII 字符
  • U+0080 到 U+FFFF 的 Unicode 字符

3、ASCII NUL (U+0000) 字符和高于 U+10000 的增补字符不允许出现在标识符中，无论是否带反引号。

4、标识符可以以数字开头。除非反引号引住否则不允许全数字的标识符。

5、库名、表名、列表不能以空字符结尾。

全数字列名必须反引号引起来

表名、列名可以是全数字，但必须用反引号引起来，否则会报错

CREATE TABLE `474274538` (
    id     BIGINT(20) NOT NULL AUTO_INCREMENT,
    `123`  VARCHAR(255),
    PRIMARY KEY (id)
) ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8

表名最长64字符

9.2.1 Identifier Length Limits https://dev.mysql.com/doc/refman/5.7/en/identifier-length.html

数据库、表、列和索引的名称最长可达64个字符。别名最长可达256个字符。

用函数名做列名时必须反引号引住

下面的 sql 会报错，因为添加的列名 current_time 是 MySQL 的一个 函数名

alter table t1 add column current_time varchar(255);

报错： [42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'current_time varchar(255)' at line 1

解决： 使用反引号将 current_time 引起来：

alter table t1 add column `current_time` varchar(255);

设计与实践

存储树形结构

一般比较普遍的就是四种方法：（具体见 SQL Anti-patterns这本书）

  • Adjacency List：邻接表，每一条记录存parent_id
  • Path Enumerations：路径枚举，每一条记录存整个tree path经过的node枚举
  • Nested Sets：每一条记录存 nleft 和 nright
  • Closure Table：闭包表，维护一个表，所有的tree path作为记录进行保存。

怎样在 MySQL 表中存储树形结构数据？ - 卢钧轶的回答 - 知乎 https://www.zhihu.com/question/20417447/answer/15078011

树状结构的数据表如何设计? https://segmentfault.com/q/1010000000126370

干货：在关系型数据库中优雅地存储树形结构 https://www.jianshu.com/p/951b742fd137

用大间隔int来表示状态码

比如一个实体类表5种有序的状态，可以用status表示

`status` TINYINT NOT NULL DEFAULT 0
COMMENT '结点状态，0-未知，1-阶段1，2-阶段2，3-阶段3，4-阶段4，5-阶段5',

假如这是一个订单的状态的话，这样设计有一个潜在的风险，就是如果之后需求发生变化，需要再插入中间状态，就需要刷数据了，因为间隔为1的int中间再无法再插入值了。 所以建议设计为

`status` TINYINT NOT NULL DEFAULT 0
COMMENT '结点状态，0-未知，10-阶段1，20-阶段2，30-阶段3，40-阶段4，50-阶段5',

用间隔为10的int来表示状态码，哪天需求变了要加中间状态就很方便，比如可以在10和20直接加一个15状态码。

给一个很大的线上表加字段并刷数据

给一个很大的线上表加字段，并且加字段后要设置初始化数据，要考虑哪些因素？怎么做？

考虑： 1、加字段是否会锁表？ 2、加字段后设置初始值是否需要更新表的update_time？ 比如 加字段 a，根据某些条件 set a 的初始值后，表中所有行的update_time都会更新为sql脚本执行时间，如果业务逻辑中有根据 update_time 做判断的，需要仔细考虑： （1）比如我们系统中有个根据 update_time 拉取最近 24 小时内有更新的数据的接口，如果刷数据后全量数据的 update_time 都更新为当前时间，会导致这个接口能遍历出全量数据，影响性能。 （2）比如有的表的查询逻辑需要按 update_time 排序后取最新的一个，如果刷数据后所有记录 update_time 都变为一样的，就会影响业务逻辑。 为了避免这种情况，刷数据时可以保留原有的 update_time ，如下：

update table t
set t.a = xx, t.update_time = t.update_time
where xxx

一种方案： 1、先拷贝出镜像表 2、服务中双写 3、改名替换

如果用这个方案，需要注意什么？ 1、双写必须在一个事务中 2、两个表里的自增id是无法对齐的，需要处理

三方工具

Yearning MYSQL SQL语句审核平台

https://guide.yearning.io/

运维

3.5亿数据上执行truncate耗时4分钟

3.5亿数据，占用180g磁盘空间，线上有高频写入（但没有删除和更新） truncate 耗时 177 秒。

3亿mysql表单字段加索引约40分钟-1个半小时

2亿8709万数据mysql表，单字段加索引，耗时40分钟53秒 alter table tt add index idx_column1(column1);

3亿2900万数据mysql表，create_time 字段加索引，耗时1小时35分钟。

3亿2900万数据mysql表，create_time 字段加索引，耗时1小时16分钟。

2.6亿mysql表改字段名和类型约4个半小时

2亿6581万数据mysql表， modified_date 字段原来是 datetime 类型，改为 datetime(3) 同时名字改为 update_time 。 modified_date 字段上无索引，并且 t1 表无任何其他读写。 SQL如下 alter table t1 change column modified_date update_time datetime(3) not null comment '更新时间'; 耗时4小时23分钟。

1亿mysql表全表单字段修改耗时约3小时

update table_test1 set device_id = concat('device_', id%100); 1亿数据，表 20+ 列，单行数据约 2kb, 执行sql耗时 2 小时 45 分钟 1000万数据，表 20+ 列，单行数据约 2kb, 执行sql耗时 6分半 500万数据，表 20+ 列，单行数据约 2kb, 执行sql耗时 3 分钟

20亿mysql表单字段加索引约16小时

800万mysql表单字段加索引1分30秒

表10+字段，没有很长的text字段，数据持续读写更新中 在引用其他表id的字段（user_id bigint）上加索引，耗时 1分30秒

1236 error: Could not find first log file name in binary log index file

原因：从库读取主库 binlog 出错 有可能是mysql服务器异常断电造成的，也可能是有人手动删除了master上的binlog文件，

解决： 重建从库

ERROR 1010 (HY000): Error dropping database (can't rmdir './test/', errno: 17)

删除db时报错： ERROR 1010 (HY000): Error dropping database (can't rmdir './test/', errno: 17)

原因： test目录下存在着MySQL数据库不知道的文件，即MySQL数据库中没有该文件的数据字典信息。

解决： 直接去data目录下删除对应的 test 目录 https://stackoverflow.com/questions/4584458/error-dropping-database-cant-rmdir-test-errno-17

mysql删除数据后何时释放磁盘空间？

drop table table_name 立刻释放磁盘空间 ，不管是 Innodb和MyISAM ； truncate table table_name立刻释放磁盘空间 ，不管是 Innodb和MyISAM; delete from table_name 删除表的全部数据，对于MyISAM 会立刻释放磁盘空间 ，而InnoDB 不会释放磁盘空间； delete from table_name where xx 带条件的删除, 不管是innodb还是MyISAM都不会释放磁盘空间； delete 操作以后使用 optimize table table_name 会立刻释放磁盘空间。不管是innodb还是myisam 。所以要想达到释放磁盘空间的目的，delete以后执行optimize table 操作。优化表期间会锁定表，所以要在空闲时段执行optimize table ，测试十几个G数据的表执行optimize table 大概20多分钟。

delete from表以后虽然未释放磁盘空间，但是下次插入数据的时候，仍然可以使用这部分空间。

delete删除数据的时候，mysql并没有把数据文件删除，而是将数据文件的标识位删除，没有整理文件，因此不会彻底释放空间。被删除的数据将会被保存在一个链接清单中，当有新数据写入的时候，mysql会利用这些已删除的空间再写入。即，删除操作会带来一些数据碎片，正是这些碎片在占用硬盘空间。

OPTIMIZE TABLE命令优化表，该命令会重新利用未使用的空间，并整理数据文件的碎片；该命令将会整理表数据和相关的索引数据的物理存储空间，用来减少占用的磁盘空间，并提高访问表时候的IO性能；但是具体对表产生的影响是依赖于表使用的存储引擎的。该命令对视图无效。

innodb log sequence number is in the future

mysql掉电后无法启动 InnoDB: is in the future http://blog.cuicc.com/blog/2015/10/12/mysql-can-not-startup-after-loss-power/

show master status

show master status 用于看主从同步中的主库状态，只有在主库上执行才有结果。 结果中可以看到当前使用的 binlog 文件和 偏移量 position 数字

mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
             File: source-bin.000002
         Position: 1307
     Binlog_Do_DB: test
 Binlog_Ignore_DB: manual, mysql
Executed_Gtid_Set: 3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5
1 row in set (0.00 sec)

File: mysql-master-bin.000001//当前正在写入的binlog文件 Position: 1112 //当前正在写入的位置。 Binlog_Do_DB:// 只记录指定数据库的二进制文件。 Binlog_Ignore_DB://不记录指定数据库的二进制文件 Executed_Gtid_Set://5.7在mysql库下引入了新的表gtid_executed，会记录当前执行的GTID。

https://dev.mysql.com/doc/refman/5.7/en/show-master-status.html

数据库性能测试

sysbench – oltp 测试

tpc-c – 事务性能测试

tpc-h – olap 测试

挑战MySQL建立10万连接

MySQL Challenge: 100k Connections https://www.percona.com/blog/2019/02/25/mysql-challenge-100k-connections/

【译】MySQL挑战：建立10万连接 https://juejin.cn/post/6844903793360896013

MySQL 8.0

窗口函数

窗口函数(window functions)是数据库的标准功能之一，主流的数据库比如Oracle，PostgreSQL都支持窗口函数功能。 窗口函数是一种特殊的函数，它可以在一组查询行上执行类似于聚合的操作，但是不会将查询行折叠为单个输出行，而是为每个查询行生成一个结果。 窗口函数可以用来处理复杂的报表统计分析场景，例如计算移动平均值、累计和、排名等

JSON 支持

MySQL8.0 版本开始正式支持 JSON 相关的语法，提供了 JSON 格式的数据库字段类型

  • 自动验证。错误的JSON格式会报错。
  • 存储格式优化。数据保存为二进制格式，文件存储很紧凑，读取速度快。
  • MySQL可以通过键或数组索引查询和修改对应的值，不用把整个字符串都读出来。

Instant Add Column 快速加列

在MySQL 8.0版本中，不论表的数据量有多大，加列操作都能瞬间完成，这就是内核原生的快速加列（Instant Add Column）功能。有了快速加列以后，直接通过SQL语句添加列即可。加列以后，MySQL并不会真的去把每一行数据都修改一遍，仅仅是在元数据中增加一列。在读取时，MySQL会返回这一列的默认值，在以后修改时，会将真实内容写入到数据文件中。