Daily 1127 MYSQL

Categories:
Tags:

选择性复制表数据 这个可, 数据库批量处理必备

亿级数据库设计

详细见 千万级汇总查询优化

基于Mysql数据库亿级数据的设计

Mysql 单标可以存储10亿级的数据，但这个时候性能非常，项目中大量的实验证明，Mysql单表容量在500万左右，性能处于最佳状态。

一张表无法搞定，那么

解决办法

分区

根据查询索引列将单表进行分区，当然这些变化对应用层是无法感知的。

分区类型 说明 使用频率
Range 分区 根据数值范围，根据时间区间或 ID 区间来切分 较多
List 分区 离散值集合 较少
Hash 分区 根据数值取模 较多
KEY 分区 KEY 分区支持 text 和 BLOB， KEY 分区不允许用用户自定义的表达式分区。 较少
  • Hash 分区，基于给定的分区个数，将数据分配到不同的分区。例如会员表的这种表。 HASH 分区只能针对整数进 HASH，对于非整形的字段只能通过表达式将其转换为整数。

如果基于绑定编号（ID）来做range或者list分区，绑定编号没有实际的业务含义，无法通过它进行查询， 因此，我们剩下了HASH分区和Key分区，HASH分区仅支持int类型的列的分区，且是其中的一列。 如果基于绑定的时间列进行分区，查询依然很慢。基于搜索列来进行分区，可以保证查询的速度。

分库

垂直分库：根据业务的耦合性，将关联度低的不同表存储在不同的数据库，以达到资源的饱和利用率。这样每个微服务系统使用独立的一个数据库。

分表

分表分为水平分表和垂直分表（也能避免跨页问题）[7]。

MySQL 底层是通过数据页存储的，一条记录占用空间过大会导致跨页，造成额外的性能开销。另外数据库以行为单位将数据加载到内存中，这样表中字段长度较短且访问频率较高，内存能加载更多的数据，命中率更高，减少了磁盘IO，从而提升了数据库性能。

在业务层增加一张业务和数据存储的表之间的关系表，比如在此方案[4]中增加了 设备-动态数据关系表（表名t_device_table_map） 来存储设备和动态数据表的关系。

最后，梳理下分表 VS 分区

  • 分区就是水平分表的数据库实现版本，水平分表的优点是可以将单张表的数据切分到多个服务器上，每个服务器具有相应的库和子表。
  • 分区只是一张表中的数据和索引的存储位置发生变化，分表是真实的有多套表的配置文件
  • 分区没法突破数据库层面，而分表可以将子表分配在同一个库中，也可以分配在不同的库中。

NoSql/NewSql #TODO

Index scan vs Bitmap scan vs Sequuentianl scan

PostgreSQL will first scan the index and compile those rows / blocks, which are needed at the end of the scan. Then PostgreSQL will take this list and go to the table to really fetch those rows. The beauty is that this mechanism even works if you are using more than just one index.[1]

PostgreSQL Bitmap-scan

联合索引

回表，在执行计划中，table access by index rowid 代表是回表动作。

联合索引的理解[6]: 联合索引结构也是 B+Tree，即按照第一个关键字进行索引，然后在叶子节点上按照第一个关键字、第二个关键字、第三个关键字…进行排序。

最左原则。

如何设计之一： 等值查询中，查询条件a返回的条目比较多，查询条件b返回的条目比较多，而同时查询a、b返回的条目比较少，那么适合建立联合索引；

如何设计之二： 等值查询、范围查询，等值查询的列建在前、范围查询的列建在后。

其他

导出数据库

mysqldump -u root -p news > news.sql

sqlalchemy session 详细介绍sqlalchemy session 几种状态，以及最佳实践。

Docker

docker 使用 docker 命令使用

Docker: Are you trying to connect to a TLS-enabled daemon without TLS?

sudo docker images

export save 区别

docker export Export a container’s filesystem as a tar archive
Docker images导出和导入 实践

BASH

tar 压缩文件夹，exclude排除文件

tar --exclude='./folder1'--exclude='./folder' --exclude='./upload/folder2' -zcvf /backup/filename.tgz .

VUE 渲染函数

参考

[1] Hans-Jürgen Schönig: POSTGRESQL INDEXING: INDEX SCAN VS. BITMAP SCAN VS. SEQUENTIAL SCAN (BASICS)

[2] zhanlijun 的博客园: 位图索引:原理（BitMap index）

[3] Markus Winand: Pagination Done the Right Way(PPT)

[4] Chaexsy 掘金: MySql 数据库分表分区实践

[5] 茶谪仙 掘金： 数据库分区一篇就透了

[6] houbb: 数据库索引-07-联合索引

[7] PHP 架构师布乐： Mysql 的分区/分库/分表总结

最后修改 April 16, 2022 : fix pic src (275a507)