Chuẩn hóa các bảng trong cơ sở dữ liệu

8.1 Tại sao cần phải chuẩn hóa?

Hoạt động tính toán tốn kém nhất giữa các bảng lớn là tham gia. Theo đó, nếu trong một truy vấn cần phải "thông gió" cho một số bảng bao gồm nhiều triệu hàng, thì DBMS sẽ tốn rất nhiều thời gian cho việc xử lý đó.

Người dùng lúc này có thể di chuyển ra xa để uống cà phê. Tính tương tác của quá trình xử lý thực tế biến mất và tiến gần đến tính tương tác của xử lý hàng loạt. Tệ hơn nữa, ở chế độ hàng loạt, người dùng nhận được tất cả dữ liệu được yêu cầu vào ngày hôm trước vào buổi sáng và bình tĩnh làm việc với chúng, chuẩn bị các yêu cầu mới cho buổi tối.

Để tránh tình trạng liên kết nhiều, các bảng không được chuẩn hóa. Nhưng không phải dù sao đi nữa. Có một số quy tắc cho phép bạn coi các bảng không chuẩn hóa theo giao dịch là "chuẩn hóa" theo các quy tắc xây dựng bảng cho kho dữ liệu.

Có hai lược đồ chính được coi là “bình thường” trong xử lý phân tích: “bông tuyết” và “ngôi sao”. Các tên phản ánh tốt bản chất và theo trực tiếp từ hình ảnh của các bảng liên quan.

Trong cả hai trường hợp, cái gọi là bảng sự kiện là thành phần trung tâm của lược đồ, chứa các sự kiện, giao dịch, tài liệu và những điều thú vị khác mà nhà phân tích quan tâm. Nhưng nếu trong cơ sở dữ liệu giao dịch, một tài liệu bị "bôi nhọ" trên một số bảng (ít nhất hai: tiêu đề và hàng nội dung), thì trong bảng thực tế, một tài liệu, chính xác hơn, mỗi hàng của nó hoặc một tập hợp các hàng được nhóm, tương ứng thành một bản ghi.

Điều này có thể được thực hiện bằng cách không chuẩn hóa hai bảng trên.

8.2 Ví dụ về phi chuẩn hóa

Giờ đây, bạn có thể đánh giá việc DBMS sẽ thực hiện một truy vấn dễ dàng hơn bao nhiêu, chẳng hạn như loại truy vấn sau: để xác định khối lượng bán bột mì cho các khách hàng của Pirozhki LLC và Vatrushki CJSC trong khoảng thời gian đó.

Trong cơ sở dữ liệu giao dịch được chuẩn hóa:

SELECT
   SUM(dl.qty) AS total qty, SUM(dl.price) AS total amount, c.name
FROM
   docs d
   INNER JOIN doc lines dl ON d.id doc = dl.id doc
   INNER JOIN customers c ON d.id customer = c.id customer
   INNER JOIN products p ON dl.id product = p.id product
WHERE
   c.name IN (’Pirozhki LLC’,	’Vatrushki CJSC’) AND
   p.name = ’Flour’ AND
   d.date BETWEEN ’2014-01-01’ AND ’2014-02-01’
GROUP BY c.name

Trong cơ sở dữ liệu phân tích:

SELECT
   SUM(s.qty) AS total_qty, SUM(s.amount) AS total_amount, c.name
FROM
   sales s
   INNER JOIN customers c ON d.id_customer = c.id_customer
   INNER JOIN products p ON dl.id_product = p.id_product
WHERE
   c.name IN ('Pirozhki LLC', 'Vatrushki CJSC') AND
   p.name = 'Flour' AND
   s.date BETWEEN '2014-01-01' AND '2014-02-01'
GROUP BY c.name

Thay vì liên kết nặng nề giữa hai bảng tài liệu và thành phần của chúng với hàng triệu hàng, DBMS làm việc trực tiếp với bảng thực tế và liên kết nhẹ với các bảng phụ trợ nhỏ, bạn cũng có thể thực hiện việc này mà không cần biết các mã định danh.

SELECT
   SUM(s.qty) AS total_qty, SUM(s.amount) AS total_amount, s.id_customer
FROM
   sales s
WHERE
   s.id_customer IN (1025, 20897) AND
   s.id_product = 67294 AND
   s.date BETWEEN '2014-01-01' AND '2014-02-01'
GROUP BY s.id_customer

Hãy quay trở lại sơ đồ "ngôi sao" và "bông tuyết". Đằng sau bức tranh đầu tiên là bảng khách hàng, nhóm của họ, cửa hàng, người bán và trên thực tế là hàng hóa. Khi không chuẩn hóa, các bảng này, được gọi là kích thước, cũng được nối với bảng thực tế. Nếu bảng thực tế đề cập đến các bảng thứ nguyên có liên kết đến các thứ nguyên khác (thứ nguyên từ cấp thứ hai trở lên), thì lược đồ như vậy được gọi là "bông tuyết".

Như bạn có thể thấy, đối với các truy vấn bao gồm lọc theo nhóm khách hàng, bạn phải tạo kết nối bổ sung.

SELECT sum(amount)
FROM sales s
   INNER JOIN customers c ON s.id_customer = c.id_customer
WHERE c.id_customer_group IN (1, 2, 10, 55)

Trong trường hợp này, quá trình không chuẩn hóa có thể tiếp tục và giảm thứ nguyên cấp hai xuống thứ nguyên thứ nhất, giúp truy vấn bảng thực tế dễ dàng hơn.

Lược đồ trong đó bảng thực tế chỉ đề cập đến các kích thước không có cấp thứ hai được gọi là lược đồ hình sao. Số lượng bảng đo lường tương ứng với số lượng "tia" trong ngôi sao.

Lược đồ Star loại bỏ hoàn toàn hệ thống phân cấp thứ nguyên và nhu cầu nối các bảng tương ứng trong một truy vấn.

SELECT sum(amount)
FROM sales s
WHERE s.id_customer_group IN (1, 2, 10, 55)

Nhược điểm của việc không chuẩn hóa luôn là sự dư thừa , điều này gây ra sự gia tăng kích thước của cơ sở dữ liệu trong cả ứng dụng giao dịch và phân tích. Hãy tính toán một delta gần đúng trong ví dụ trên về việc chuyển đổi "bông tuyết" thành "ngôi sao".

Trong một số DBMS, chẳng hạn như Oracle, không có loại số nguyên đặc biệt nào ở cấp định nghĩa lược đồ cơ sở dữ liệu, bạn phải sử dụng loại boolean chung numeric(N), trong đó N là số bit được lưu trữ. Kích thước lưu trữ của một số như vậy được tính bằng một công thức đặc biệt được đưa ra trong tài liệu về lưu trữ dữ liệu vật lý và theo quy luật, nó vượt quá kích thước của các loại cấp thấp như "số nguyên 16 bit" 1-3 byte.

id_customer_groupGiả sử bảng bán hàng không sử dụng nén dữ liệu và chứa khoảng 500 triệu hàng và số lượng nhóm khách hàng là khoảng 1000. Trong trường hợp này, chúng ta có thể sử dụng một số nguyên ngắn (shortint, smallint) chiếm 2 byte làm loại định danh .

Chúng tôi sẽ giả định rằng DBMS của chúng tôi hỗ trợ loại số nguyên hai byte (ví dụ: PostgreSQL, SQL Server, Sybase và các loại khác). Sau đó, việc thêm cột tương ứng id_customer_groupvào bảng bán hàng sẽ tăng kích thước của nó lên ít nhất 500 000 000 * 2 = 1 000 000 000 byte ~ 1 GByte.

8.3 Khi nào cần chuẩn hóa?

Hãy xem xét một số tình huống phổ biến mà việc không chuẩn hóa có thể hữu ích.

Số lượng lớn các bảng tham gia

Trong các truy vấn tới một cơ sở dữ liệu được chuẩn hóa hoàn toàn, bạn thường phải nối tới hàng tá hoặc thậm chí nhiều bảng hơn. Và mỗi kết nối là một hoạt động rất tốn tài nguyên. Do đó, các yêu cầu như vậy sẽ tiêu tốn tài nguyên máy chủ và được thực hiện chậm.

Trong tình huống như vậy, nó có thể giúp:

• không chuẩn hóa bằng cách giảm số lượng bảng. Tốt hơn là kết hợp thành một vài bảng có kích thước nhỏ, chứa thông tin hiếm khi thay đổi (như người ta thường nói, hằng số có điều kiện hoặc tham chiếu) và thông tin có liên quan chặt chẽ về ý nghĩa.
• Nói chung, nếu bạn cần tham gia nhiều hơn năm hoặc sáu bảng trong một số lượng lớn truy vấn, bạn nên cân nhắc việc chuẩn hóa cơ sở dữ liệu.
• Không chuẩn hóa bằng cách thêm một trường bổ sung vào một trong các bảng. Trong trường hợp này, dữ liệu dư thừa xuất hiện, cần có các hành động bổ sung để duy trì tính toàn vẹn của cơ sở dữ liệu.

Giá trị ước tính

Thông thường, các truy vấn chậm và tiêu tốn nhiều tài nguyên, trong đó một số phép tính phức tạp được thực hiện, đặc biệt là khi sử dụng các nhóm và hàm tổng hợp (Sum, Max, v.v.). Đôi khi, việc thêm 1-2 cột bổ sung vào bảng chứa dữ liệu được tính toán thường xuyên sử dụng (và khó tính toán) sẽ rất hợp lý.

Giả sử bạn muốn xác định tổng chi phí của mỗi đơn đặt hàng. Để làm được điều này, trước tiên bạn phải xác định giá thành của từng sản phẩm (theo công thức "số lượng đơn vị sản phẩm" * "đơn giá sản phẩm" - chiết khấu). Sau đó, bạn cần nhóm các chi phí theo đơn đặt hàng.

Việc thực hiện truy vấn này khá phức tạp và nếu cơ sở dữ liệu lưu trữ thông tin về một số lượng lớn đơn đặt hàng thì có thể mất nhiều thời gian. Thay vì thực hiện một truy vấn như vậy, bạn có thể xác định chi phí của nó ở giai đoạn đặt hàng và lưu trữ nó trong một cột riêng của bảng đơn hàng. Trong trường hợp này, để có được kết quả mong muốn, chỉ cần trích xuất các giá trị được tính trước từ cột này là đủ.

Việc tạo một cột chứa các giá trị được tính toán trước giúp tiết kiệm rất nhiều thời gian khi chạy truy vấn nhưng yêu cầu bạn phải cập nhật dữ liệu trong cột đó một cách kịp thời.

vành dài

Nếu chúng ta có các bảng lớn trong cơ sở dữ liệu chứa các trường dài (Blob, Long, v.v.), thì chúng ta có thể tăng tốc nghiêm trọng việc thực hiện các truy vấn đến một bảng như vậy nếu chúng ta di chuyển các trường dài sang một bảng riêng biệt. Chẳng hạn, chúng tôi muốn tạo một danh mục ảnh trong cơ sở dữ liệu, bao gồm cả việc lưu trữ ảnh trong các trường blob (chất lượng chuyên nghiệp, độ phân giải cao và kích thước phù hợp). Từ quan điểm chuẩn hóa, cấu trúc bảng sau đây sẽ hoàn toàn chính xác:

• Giấy tờ tùy thân có ảnh
• ID tác giả
• Mã kiểu máy ảnh
• chính bức ảnh đó (trường blob)

Và bây giờ hãy tưởng tượng truy vấn sẽ chạy trong bao lâu, đếm số lượng ảnh được chụp bởi bất kỳ tác giả nào ...

Giải pháp chính xác (mặc dù vi phạm các nguyên tắc chuẩn hóa) trong tình huống như vậy là tạo một bảng khác chỉ bao gồm hai trường - ID ảnh và trường blob có ảnh. Sau đó, các lựa chọn từ bảng chính (trong đó không còn trường đốm màu lớn nữa) sẽ xuất hiện ngay lập tức, nhưng khi chúng ta muốn xem chính bức ảnh đó, thì, hãy đợi ...

Làm thế nào để xác định khi không chuẩn hóa là hợp lý?

8.4 Ưu và nhược điểm của việc không chuẩn hóa

Một cách để xác định liệu các bước nhất định có hợp lý hay không là tiến hành phân tích về chi phí và lợi ích có thể có. Một mô hình dữ liệu không chuẩn hóa sẽ có giá bao nhiêu?

Xác định yêu cầu (điều chúng ta muốn đạt được) → xác định yêu cầu dữ liệu (điều chúng ta cần tuân theo) → tìm bước tối thiểu thỏa mãn các yêu cầu này → tính toán chi phí thực hiện → triển khai.

Chi phí bao gồm các khía cạnh vật lý như không gian đĩa, các tài nguyên cần thiết để quản lý cấu trúc này và các cơ hội bị mất do sự chậm trễ về thời gian liên quan đến việc duy trì quy trình này. Bạn phải trả tiền cho việc không chuẩn hóa. Cơ sở dữ liệu không chuẩn hóa làm tăng dự phòng dữ liệu, có thể cải thiện hiệu suất nhưng đòi hỏi nhiều nỗ lực hơn để kiểm soát dữ liệu liên quan. Quá trình tạo ứng dụng sẽ trở nên khó khăn hơn, vì dữ liệu sẽ bị lặp lại và khó theo dõi hơn. Ngoài ra, việc thực hiện tính toàn vẹn tham chiếu không dễ dàng - dữ liệu liên quan được chia thành các bảng khác nhau.

Các lợi ích bao gồm hiệu suất truy vấn nhanh hơn và khả năng nhận được phản hồi nhanh hơn. Bạn cũng có thể gặt hái những lợi ích khác, bao gồm tăng thông lượng, sự hài lòng của khách hàng và năng suất, cũng như sử dụng hiệu quả hơn các công cụ dành cho nhà phát triển bên ngoài.

Tỷ lệ yêu cầu và tính nhất quán về hiệu suất

Ví dụ: 72% trong số 1.000 truy vấn do doanh nghiệp tạo ra hàng ngày là truy vấn cấp độ tóm tắt, không phải truy vấn chi tiết. Khi sử dụng bảng tóm tắt, các truy vấn sẽ chạy trong khoảng 6 giây thay vì 4 phút, dẫn đến thời gian xử lý ít hơn 3.000 phút. Ngay cả sau khi điều chỉnh 100 phút phải dành để duy trì bảng tổng hợp mỗi tuần, điều đó tiết kiệm được 2.500 phút mỗi tuần, điều này chứng minh cho việc tạo bảng tổng hợp. Theo thời gian, có thể xảy ra trường hợp hầu hết các truy vấn sẽ không được giải quyết đối với dữ liệu tóm tắt mà là dữ liệu chi tiết. Càng ít truy vấn sử dụng bảng tóm tắt thì càng dễ bỏ nó mà không ảnh hưởng đến các quy trình khác.

Và…

Các tiêu chí được liệt kê ở trên không phải là những tiêu chí duy nhất cần xem xét khi quyết định có nên thực hiện bước tiếp theo trong quá trình tối ưu hóa hay không. Các yếu tố khác cần được xem xét, bao gồm các ưu tiên kinh doanh và nhu cầu của người dùng cuối. Người dùng phải hiểu làm thế nào, từ quan điểm kỹ thuật, kiến ​​trúc hệ thống bị ảnh hưởng bởi yêu cầu của người dùng muốn tất cả các yêu cầu được hoàn thành trong vài giây. Cách dễ nhất để đạt được sự hiểu biết này là phác thảo các chi phí liên quan đến việc tạo và quản lý các bảng đó.

8.5 Làm thế nào để thực hiện chuẩn hóa một cách thành thạo.

Lưu bảng chi tiết

Để không giới hạn khả năng của cơ sở dữ liệu quan trọng đối với doanh nghiệp, cần phải áp dụng chiến lược cùng tồn tại chứ không phải thay thế, tức là giữ các bảng chi tiết để phân tích sâu, thêm các cấu trúc không chuẩn hóa vào chúng. Ví dụ, bộ đếm lượt truy cập. Đối với doanh nghiệp, bạn cần biết số lượt truy cập vào một trang web. Nhưng để phân tích (theo thời kỳ, theo quốc gia...), rất có thể chúng ta sẽ cần dữ liệu chi tiết - một bảng có thông tin về mỗi lượt truy cập.

Sử dụng trình kích hoạt

Có thể không chuẩn hóa cấu trúc cơ sở dữ liệu và vẫn tận hưởng những lợi ích của việc chuẩn hóa bằng cách sử dụng trình kích hoạt cơ sở dữ liệu để duy trì tính integritytoàn vẹn của dữ liệu trùng lặp.

Ví dụ: khi thêm một trường được tính toán, mỗi cột mà trường được tính toán phụ thuộc vào sẽ được treo lên bằng trình kích hoạt gọi một thủ tục được lưu trữ duy nhất (điều này rất quan trọng!), Thủ tục này ghi dữ liệu cần thiết vào trường được tính toán. Chỉ cần không bỏ qua bất kỳ cột nào mà trường được tính toán phụ thuộc vào.

Hỗ trợ phần mềm

Nếu bạn không sử dụng các trình kích hoạt tích hợp sẵn và các thủ tục được lưu trữ, thì các nhà phát triển ứng dụng nên quan tâm đến việc đảm bảo tính nhất quán của dữ liệu trong cơ sở dữ liệu không chuẩn hóa.

Tương tự với các trình kích hoạt, sẽ có một chức năng cập nhật tất cả các trường phụ thuộc vào trường được thay đổi.

kết luận

Khi không chuẩn hóa, điều quan trọng là phải duy trì sự cân bằng giữa việc tăng tốc độ của cơ sở dữ liệu và tăng nguy cơ dữ liệu không nhất quán, giữa việc làm cho cuộc sống của các lập trình viên viết dễ dàng hơn Select-svà làm phức tạp nhiệm vụ của những người cung cấp dữ liệu và cập nhật dữ liệu cho cơ sở dữ liệu. Do đó, cần phải phi chuẩn hóa cơ sở dữ liệu rất cẩn thận, rất chọn lọc, chỉ ở những nơi không thể thiếu.

Nếu không thể tính toán trước ưu và nhược điểm của việc không chuẩn hóa, thì ban đầu cần triển khai một mô hình với các bảng được chuẩn hóa và chỉ sau đó, để tối ưu hóa các truy vấn có vấn đề, hãy thực hiện việc không chuẩn hóa.

Điều quan trọng là giới thiệu quá trình không chuẩn hóa dần dần và chỉ dành cho những trường hợp có các lần tìm nạp dữ liệu liên quan lặp đi lặp lại từ các bảng khác nhau. Hãy nhớ rằng, khi sao chép dữ liệu, số lượng bản ghi sẽ tăng lên, nhưng số lần đọc sẽ giảm xuống. Nó cũng thuận tiện để lưu trữ dữ liệu được tính toán trong các cột để tránh các lựa chọn tổng hợp không cần thiết.