Mysql存储-EAV模式

Mysql存储-EAV模式

最近又又又搞一点新东西，要整合不同业务进行存储和查询，一波学习过后总结了一下可扩展性MAX的eav模式存储。

在eav这里的数据结构设计尤为关键，需要充分考虑你需要使用的字段、使用场景，当数据结构设计完成后便会发现eav模型需要多次join操作才能完成查询，因此性能优化的难点也是在如何充分使用索引

一、简介

1、概念

EAV（Entity-Attribute-Value）模式，也称为对象-属性-值模式，是一种常用于数据库设计的灵活模式，适用于具有大量属性和属性值的实体。它在MySQL数据库中的实现可以解决一些传统关系型数据库表结构无法轻松满足的需求，例如动态属性、稀疏属性等。

EAV模式的核心思想是将实体（Entity）的属性（Attribute）和值（Value）分别存储在不同的表中。这样可以在不修改表结构的情况下轻松添加或删除属性，从而提高数据库的灵活性。

EAV模式在MySQL数据库中通常包含以下三个表：

1. 实体表（Entity Table）：存储实体的基本信息，如ID、名称等。每个实体对应该表中的一行记录。
2. 属性表（Attribute Table）：存储属性的元数据，如属性ID、属性名称、数据类型等。每个属性对应该表中的一行记录。
3. 值表（Value Table）：存储实体的属性值。每个属性值对应该表中的一行记录，包括实体ID、属性ID和属性值。

2、特点

EAV模式的优点：

1. 高度灵活：可以轻松添加、删除或修改属性，而无需更改表结构。
2. 节省存储空间：对于具有大量稀疏属性的实体，EAV模式可以避免在数据表中存储大量NULL值。

EAV模式的缺点：

1. 查询复杂：由于属性和值分散在多个表中，查询和聚合操作通常需要多表连接，导致查询性能较差。
2. 数据完整性：EAV模式较难实现属性值的数据类型和约束检查，可能导致数据完整性问题。

二、详细设计

写入时：

• 在实际业务上会接入不同领域的数据，不同领域数据内容也不尽相同，在领域分治的情况下便只需要考虑单一的固定数据。

• 同一领域内数据具有一定的相似性，将较多出现的数据存放于entity表中，以减少多次join操作的情况，性能++

• 同一领域内的相同扩展字段名称可能会出现不同数据类型的情况，因此需要在attributes表中增加name、type的唯一键，进行upsert操作，保证该表数据满足全部场景

• 根据传入的interface类型，将数据存储到对应的字段中。例如，如果传入的数据是整数类型，将数据存储到int_value字段中

查询时：

• 需要增加表，用于记录单个领域下的entity中的固定字段，在查询时先查询该领域的固定字段是否cover查询要求的字段，如果cover住则不需要查询values表。
• 根据attributes表中的type字段进行“类型断言”。例如，如果attributes表中的type值为’int’，则从values表中的int_value字段中读取数据（应在各场景下最大程度地减少使用断言）
  • 类型断言是Golang内置的特性，不需要额外引入包
  • 反射是指在运行时动态获取变量的类型信息、操作变量的方法

三、demo

SQL:

CREATE TABLE entities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    status VARCHAR(255) NOT NULL,
    type VARCHAR(255) NOT NULL
);

CREATE TABLE attributes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE values (
    entity_id INT,
    attribute_id INT,
    value VARCHAR(255) NOT NULL,
    PRIMARY KEY (entity_id, attribute_id),
    FOREIGN KEY (entity_id) REFERENCES entities(id),
    FOREIGN KEY (attribute_id) REFERENCES attributes(id)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

Golang:

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
)

type Data struct {
	ID         int
	Name       string
	Status     string
	Type       string
	ExtraData  map[string]string
}

func main() {
	db, err := sql.Open("mysql", "username:password@tcp(localhost:3306)/dbname")
	if err != nil {
		panic(err)
	}
	defer db.Close()

	// 插入数据
	extraData := map[string]string{
		"check_data": "2021-10-01",
		"start_time": "10:00:00",
	}

	entityID, err := insertData(db, "name1", "status1", "type1", extraData)
	if err != nil {
		panic(err)
	}

	// 查询数据
	data, err := getData(db, entityID)
	if err != nil {
		panic(err)
	}

	fmt.Printf("Data: %+v\n", data)
}

func insertData(db *sql.DB, name, status, dataType string, extraData map[string]string) (int, error) {
	res, err := db.Exec("INSERT INTO entities (name, status, type) VALUES (?, ?, ?)", name, status, dataType)
	if err != nil {
		return 0, err
	}

	entityID, err := res.LastInsertId()
	if err != nil {
		return 0, err
	}

	for attributeName, value := range extraData {
		attributeID, err := getOrCreateAttribute(db, attributeName)
		if err != nil {
			return 0, err
		}

		_, err = db.Exec("INSERT INTO values (entity_id, attribute_id, value) VALUES (?, ?, ?)", entityID, attributeID, value)
		if err != nil {
			return 0, err
		}
	}

	return int(entityID), nil
}

func getData(db *sql.DB, entityID int) (*Data, error) {
	row := db.QueryRow("SELECT id, name, status, type FROM entities WHERE id = ?", entityID)

	var data Data
	err := row.Scan(&data.ID, &data.Name, &data.Status, &data.Type)
	if err != nil {
		return nil, err
	}

	rows, err := db.Query("SELECT a.name, v.value FROM attributes a JOIN values v ON a.id = v.attribute_id WHERE v.entity_id = ?", entityID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	data.ExtraData = make(map[string]string)
	for rows.Next() {
		var attributeName, value string
		if err := rows.Scan(&attributeName, &value); err != nil {
			return nil, err
		}
		data.ExtraData[attributeName] = value
	}

	return &data, nil
}

func getOrCreateAttribute(db *sql.DB, attributeName string) (int, error) {
	var attributeID int
	err := db.QueryRow("SELECT id FROM attributes WHERE name = ?", attributeName).Scan(&attributeID)
	if err == sql.ErrNoRows {
		res, err := db.Exec("INSERT INTO attributes (name) VALUES (?)", attributeName)
		if err != nil {
			return 0, err
		}

		id, err := res.LastInsertId()
		if err != nil {
			return 0, err
		}
		attributeID = int(id)
	} else if err != nil {
		return 0, err
	}

	return attributeID, nil
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116