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

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会返回这一列的默认值,在以后修改时,会将真实内容写入到数据文件中。


上一篇 面试准备02-Java集合框架

下一篇 LeetCode.033.Search in Rotated Sorted Array 搜索旋转有序数组

阅读
评论
2.5k
阅读预计9分钟
创建日期 2018-03-08
修改日期 2021-06-30
类别

页面信息

location:
protocol:
host:
hostname:
origin:
pathname:
href:
document:
referrer:
navigator:
platform:
userAgent:

评论