Building Efficient Data Tables: Table Design and Optimization in Doris Database

# 1. Overview of Doris Database

Doris is a distributed OLAP (Online Analytical Processing) database based on the MPP (Massively Parallel Processing) architecture. It is characterized by high performance, high availability, and high scalability, and is widely used in the field of big data analysis.

Doris adopts columnar storage and pre-aggregation technology, capable of efficiently processing massive amounts of data. Its MPP architecture distributes data across multiple nodes and processes queries in parallel, significantly enhancing query performance. Moreover, Doris supports various data types and encoding methods, allowing for flexible storage optimization based on data characteristics.

# 2. Doris Table Design Principles

### 2.1 Fundamentals of Data Modeling

#### 2.1.1 Normalization and Denormalization

**Normalization** is a data modeling method that follows certain rules to reduce data redundancy and anomalies. Normalized database design can improve data integrity and consistency.

**Denormalization** is a data modeling method that violates normalization rules, aimed at improving query performance. Denormalized design can reduce table joins, thereby increasing query speed.

#### 2.1.2 Dimensional Modeling and Fact Tables

**Dimensional modeling** is a data warehouse modeling method that organizes data into dimension tables and fact tables. Dimension tables contain attributes that describe the data, while fact tables contain the metrics.

**Fact tables** are the core tables in dimensional modeling, storing data from business transactions or events. Fact tables are typically very large and contain a significant amount of duplicate data.

### 2.2 Doris Table Structure Design

#### 2.2.1 Table Partitioning and Replication Strategy

**Table partitioning** divides the data in a table horizontally into multiple subsets, known as partitions. Partitioning can improve query performance as it allows Doris to scan only the necessary data.

**Replication strategy** specifies the number of replicas for each partition. Replicas can enhance data availability and fault tolerance.

#### 2.2.2 Data Type Selection and Encoding Methods

**Data types** specify the type of data in a column, such as integers, floats, or strings. Selecting the appropriate data type can save storage space and improve query performance.

**Encoding methods** specify how data is stored on disk. Different encoding methods have different trade-offs between space and performance.

**Code block:**

CREATE TABLE t1 (
   id INT NOT NULL,
   name VARCHAR(255) NOT NULL,
   age INT NOT NULL,
   PRIMARY KEY (id)
)
PARTITION BY RANGE (id) (
   PARTITION p0 VALUES LESS THAN (10),
   PARTITION p1 VALUES LESS THAN (20),
   PARTITION p2 VALUES LESS THAN (30)
)
DISTRIBUTED BY HASH (id) BUCKETS 3;

**Logical Analysis:**

This code block creates a table named `t1`, which includes:

* An `id` column that is an integer primary key.
* A `name` column that is a string with a maximum length of 255 characters.
* An `age` column that is an integer.

The table is partitioned into three partitions:

* Partition `p0` contains rows where `id` is less than 10.
* Partition `p1` contains rows where `id` is less than 20.
* Partition `p2` contains rows where `id` is less than 30.

The table is also distributed across 3 storage buckets using a hash partitioning strategy.

# 3.1 Index Optimization

#### 3.1.1 Index Types and Selection

Doris supports various index types, including:

- **Bitmap Index:** Suitable for columns with a low cardinality, capable of quickly filtering rows that meet the conditions.
- **BloomFilter Index:** Suitable for columns with a high cardinality, capable of quickly determining if rows that meet the conditions exist.
- **Composite Index:** Combines multiple columns into a single index, improving the efficiency of multi-column queries.
- **ZoneMap Index:** Suitable for columns with uneven data distribution, capable of quickly locating the zones where rows that meet the conditions are located.

The choice of index depends on the cardinality, data distribution, and query patterns of the columns.

#### 3.1.2 Index Design Principles

When designing indexes, the following principles should