写点什么

用数据分析的方法去做 dba,维护好 tidb 数据库。

  • 2023-04-21
    北京
  • 本文字数:2392 字

    阅读完需:约 8 分钟

作者: tidb 狂热爱好者原文来源:https://tidb.net/blog/ed6255bc


之前我的文章写过要维护好 tidb 数据库主要做 5 点


  • 定时删除大表中的过期数据,减少表的大小,释放 Region 资源。

  • 删除没用的索引,节省空间和提高性能

  • 使用 Bytebase 这个开源的工单系统来管理变更操作,提高变更操作的可控性和可追溯性

  • 建立一个数据库表维护规范,定期进行数据库备份、表检查、日志清理等操作

  • 数据库设计时,要考虑需求分析、概念设计、逻辑设计、物理设计等步骤,以构造优化的数据库逻辑模式和物理结构

第一点 定时删除大表中的过期数据,减少表的大小,释放 Region 资源。

查询 mysql 里面最大的表


SELECT table_schema as '数据库',sum(table_rows) as '记录数',sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)',sum(truncate(DATA_FREE/1024/1024, 2)) as '碎片占用(MB)'from information_schema.tablesgroup by table_schemaorder by sum(data_length) desc, sum(index_length) desc;
复制代码

第二点 删除没用的索引,节省空间和提高性能

select * from performance_schema.table_io_waits_summary_by_index_usage where object_schema = 'users' order by COUNT_READ desc limit 10;
复制代码


这个查询能查到表里面最长使用到的索引,如果你的表都索引没在列表里面就可以删除

第三点 使用 Bytebase 这个开源的工单系统来管理变更操作,可能有以下的好处:

  • 提高变更操作的可控性:通过工单流程,可以对变更操作进行分步骤的审核和执行,避免人为的错误和风险,确保变更操作符合规范和标准。

  • 提高变更操作的可追溯性:通过工单系统,可以记录变更操作的历史和状态,方便查询和回溯,也可以生成变更报告和统计分析。

  • 提高变更操作的效率:通过工单系统,可以实现变更操作的自动化和标准化,减少人工的干预和重复劳动,提高变更操作的速度和质量。

第四点 数据库表维护规范包括以下几个方面:

  • 数据命名规范:所有数据库对象名称必须使用小写字母并用下划线分割,禁止使用 MySQL 保留关键字,要能做到见名识意,并且不要超过 32 个字符

  • 数据库基本设计规范:所有表必须使用 InnoDB 存储引擎,数据库和表的字符集统一使用 UTF8MB4,所有表和字段都需要添加注释,尽量控制单表数据量的大小,建议控制在 500 万以内,尽量做到冷热数据分离,减小表的宽度,禁止在表中建立预留字段,禁止在数据库中存储图片,文件等大的二进制数据

  • 数据库字段设计规范:优先选择符合存储需要的最小的数据类型,避免使用 TEXT、BLOB、ENUM 数据类型,尽可能把所有列定义为 NOT NULL,使用 TIMESTAMP 或 DATETIME 类型存储时间,同财务相关的金额类数据必须使用 decimal 类型

  • 索引设计规范:限制每张表上的索引数量,建议单张表索引不超过 5 个,禁止给表中的每一列都建立单独的索引,每个 InnoDB 表必须有个主键,尽量创建联合索引而不是单列索引

  • 定期进行数据库备份、表检查、日志清理等操作:根据业务需求和数据重要性制定合理的备份策略和恢复计划,定期检查表的完整性和一致性,定期清理无用的日志文件和临时文件

第五点 这个过程属于数据库运行和维护的阶段,主要有以下三项内容

  • 维护数据库的安全性和完整性:通过权限管理、口令、跟踪及审计等手段保护数据库免受非法访问和破坏,通过约束条件、触发器、存储过程等机制保证数据的正确性和一致性。


写一个 python 代码自动创建表


mport sys


database=str(sys.argv[1])


import random


import string


r1 = “.join(random.sample(string.ascii_letters + string.digits, 20))


r2 = “.join(random.sample(string.ascii_letters + string.digits, 20))


print(f”create database if not exists {database} default character set utf8mb4 default collate utf8mb4_bin;“)


print(f”create user ‘{database}data’@‘172.%’ identified by ‘{r1}’ ;“)


print(f”grant select,insert, update, delete on {database}.* to ‘{database}data’@‘172.%’ ;“)


print(f”grant create,drop,alter,index,create view on {database}.* to ‘{database}data’@‘172.%’;“)


print(f”create user ‘{database}opr’@‘172.%’ identified by ‘{r2}’ ;“)


print(f”grant select,insert, update, delete on {database}.* to ‘{database}opr’@‘172.%’;“)


python3 createtest.py new_table


大佬教我的一个用户管理表结构。应用只能查询和删除。黑客通过 java 漏洞获得了权限,他也没法对数据库产生影响。一个应用的破解,无法对整个系统进行破坏,符合最小授权原则。


这样就保证应用只能访问到他自己的数据库。避免了安全问题。


监测并改善数据库性能:通过工具或命令检测数据库的运行状态,分析数据库的负荷、配置、应用和事件等信息,评估系统的整体运行状况,根据分析结果调整数据库的参数、模式、存储和查询等方面,提高系统的效率和响应速度。


  • 重新组织和构造数据库:根据业务需求和数据重要性制定合理的备份策略和恢复计划,定期对数据库进行转储和恢复测试,保证数据的可用性和可恢复性。根据数据的增长情况,及时调整数据库的存储空间,避免空间不足或浪费。根据数据的使用情况,定期对数据库进行重组和重构,优化数据的分布和结构。


重新组织数据库就是评估各个索引是否真的有用。在系统关键性能表上建一个索引的代价都很昂贵。特别对于 tidb。如果这个查询一直用到可以考虑建立聚簇索引。


一个用主键查询的索引 tidb 返回是 0.2ms


一个用二级索引返回的查询条件 tidb 最快返回是 2ms


而复杂索引并且要回表的查询基本就跑 300ms 以外去了。


数据库是需要不停的循环并评估性能的。这样才能真正用好 tidb 数据库。保证最优的性能。


大家说这以上五点不都采用了数据分析的方法么?数据分析 select 就能执行没有机器学习。没有 ai 但却能维护好 tidb 数据库的性能。简单明了。希望这篇文章对大家有用。


发布于: 刚刚阅读数: 7
用户头像

TiDB 社区官网:https://tidb.net/ 2021-12-15 加入

TiDB 社区干货传送门是由 TiDB 社区中布道师组委会自发组织的 TiDB 社区优质内容对外宣布的栏目,旨在加深 TiDBer 之间的交流和学习。一起构建有爱、互助、共创共建的 TiDB 社区 https://tidb.net/

评论

发布
暂无评论
用数据分析的方法去做dba,维护好tidb数据库。_6.x 实践_TiDB 社区干货传送门_InfoQ写作社区