MySQL-基础

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/

设计与实践

存储树形结构

一般比较普遍的就是四种方法：（具体见 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会返回这一列的默认值，在以后修改时，会将真实内容写入到数据文件中。

当前位置 : 首页 » 文章分类 : 开发 » MySQL-基础