1. Không sử dụng các chỉ số thích hợp
Đây là một điều khá đơn giản, nhưng nó vẫn xảy ra thường xuyên. Khoá ngoại nên có các chỉ mục cho chúng. Nếu bạn đang sử dụng một trường trong nơi mà bạn nên (có thể) có một chỉ mục trên nó. Các chỉ mục như vậy thường nên bao gồm nhiều cột dựa trên các truy vấn bạn cần thực hiện. 2. Không tuân theo sự toàn vẹn tham chiếu
Cơ sở dữ liệu của bạn có thể thay đổi nhưng nếu cơ sở dữ liệu của bạn c tính toàn vẹn tham chiếu -- có nghĩa là tất cả các khoá ngoại được đảm bảo để trỏ đến một thực thể tồn tại -- bạn nên sử dụng nó.
Rất phổ biến để thấy sai lầm này trên cơ sở dữ liệu MySQL. Tôi không tin MyISAM hỗ trợ nó. InnoDB thì có. Bạn sẽ tìm thấy những người đang sử dụng MyISAM hoặc những người đang sử dụng InnoDB nhưng không còn sử dụng nó.
Thêm nữa:
- Các ràng buộc như NOT NULL và FOREIGN KEY quan trọng như thế nào nếu bạn thường xuyên phải kiểm soát đầu vào với php?
- Các khoá ngoại thực sự cần thiết trong thiết kế cơ sở dữ liệu?
- Các khoá ngoại cần thiết trong thiết kế cơ sở dữ liệu?
3. Sử dụng các khóa chính tự nhiên hơn là khoá chính bao quát
Các khoá tự nhiên là khóa dựa trên các dữ liệu bên ngoài có ý nghĩa (có vẻ là) duy nhất. Các ví dụ phổ biến là mã sản phẩm, mã bưu điện gồm hai chữ cái (Mỹ), số an sinh xã hội và hơn nữa. Khoá bao quát hay khoá kĩ thuật là những thứ hoàn toàn không có ý nghĩa bên ngoài hệ thống. Chúng được phát minh để xác định thực thể và thông thường các trường tự tăng(Auto-increament)(SQL Server, MySQL, others) hay tuần tự (đáng chú ý nhất là Oracle).
Theo tôi bạn nên thường xuyên sử dụng khoá bao quát. Vấn đề này đã đưa ra trong những câu hỏi sau:
- Bạn thích khóa chính của bạn như thế nào?
- Luyện tập tốt nhất cho các khóa chính trong bảng là gì?
- Bạn sử dụng định dạng khoá chính nào trong trường hợp này.
- Khoá bao quát vs. khoá tự nhiên
- Tôi nên có một trường khoá chính riêng không?
Đây là một chủ đề gây nhiều tranh cãi mà bạn sẽ không đạt được sự đồng ý chung. Trong khi bạn có thể tìm thấy một số người nghĩ rằng các khoá tự nhiên là trong một số tình huống OK, bạn sẽ không tìm thấy bất kỳ lời chỉ trích của các khoá bao quát không cần thiết. Đó là một nhược điểm nhỏ nếu bạn hỏi tôi. Nhớ lại, thậm chí các quốc gia có thể không còn tồn tại (ví dụ, Yugoslavia).
**4. Viết các yêu cầu truy vấn
Để DISTINCT làm việc**
Bạn thường thấy nó trong câu truy vẫn khởi tạo ORM. Nhìn vào log output Hibernate bạn sẽ thấy tất cả các câu truy vẫn bắt đầu với:
SELECT DISTINCT ...
This is a bit of a shortcut để đảm bảo bạn không return row trùng và nhận được các đối tượng trùng nhau. Đôi khi bạn cũng thấy những người làm việc này tốt. Nếu bạn nhìn thấy nó quá nhiều đó lại là sự báo động. Không phải do DISTINCT là tồi hoặc không có ứng dụng hợp lệ. Nó ổn (như nhau) nhưng nó không phải là một đại diện hay một stopgap để viết các câu truy vấn chính xác.
Trường hợp mọi thứ bắt đầu trở nên không theo ý kiến của tôi là khi một nhà phát triển đang xây dựng một lượng đáng kể các truy vấn, join các bảng với nhau, và đột nhiên anh ta nhận ra rằng nó trông như anh ấy đang nhận được bản sao (hoặc hơn thế) các row và phản ứng tức thời của anh ấy..."giải pháp" của anh ấy đối với "vấn đề" này là ném vào từ khóa DISTINCT và POOF và tất cả những rắc rối của anh ấy biến mất.
5. Ủng hộ việc join chung lại
Một sai lầm phổ biến khác của các nhà phát triển ứng dụng cơ sở dữ liệu là không nhận ra sự kết hợp đắt tiền hơn (ví dụ GROUP BY mệnh đề) có thể được so sánh với join.
Để đưa cho bạn một ý tưởng làm cách nào phổ biến rộng rãi cái này, Tôi đã viết về chủ đề này vài lần ở đây và được downvoted rất nhiều cho nó. Ví dụ:
Từ Các câu lệnh SQL - “join” vs “group by và having”:
First query:
SELECT userid FROM userrole WHERE roleid IN (1, 2, 3) GROUP by userid HAVING COUNT(1) = 3
Query time: 0.312 s
Second query:
SELECT t1.userid FROM userrole t1 JOIN userrole t2 ON t1.userid = t2.userid AND t2.roleid = 2 JOIN userrole t3 ON t2.userid = t3.userid AND t3.roleid = 3 AND t1.roleid = 1
Query time: 0.016 s
Đúng rồi. Join nhanh gấp 20 lần gom nhóm.
6. Không đơn giản hóa các truy vấn phức tạp thông qua các view
Không phải tất cả nhà cung cấp cơ sở dữ liệu hỗ trợ views nhưng đối với những cái có hỗ trợ, họ có thể rất đơn giản hóa các truy vấn nếu được sử dụng một cách thận trọng. Ví dụ, trên một dự án tôi sử dụng một generic Party model cho CRM. Đây là một kỹ thuật mô hình rất mạnh và linh hoạt nhưng có thể dẫn đến nhiều join. Trong mô hình này đã có:
- Party: con người và các tổ chức;
- Party Role: những điều mà các bên đã làm, ví dụ như người lao động và người sử dụng lao động;
- Party Role Relationship: cách để những vai trò liên quan đến nhau.
Example:
- Ted là một người, đang trở thành thành phần Party;
- Ted có rất nhiều vai trò, 1 trong đó là người lao động;
- Intel là một tổ chức, đang trở thành thành phần của Party;
- Intel có rất nhiều vai trò, 1 trong đó là người sử dụng lao động;
- Intel thuê Ted, có nghĩa là có một mối quan hệ giữa các vai trò tương ứng.
Vì vậy có 5 tables kết nối để liên kết Ted tới người sử dụng lao động. Bạn giả định tất cả nhân viên là Người (không phải tổ chức) và cung cấp các view hỗ trợ:
CREATE VIEW vw_employee AS SELECT p.title, p.given_names, p.surname, p.date_of_birth, p2.party_name employer_name FROM person p JOIN party py ON py.id = p.id JOIN party_role child ON p.id = child.party_id JOIN party_role_relationship prr ON child.id = prr.child_id AND prr.type = 'EMPLOYMENT' JOIN party_role parent ON parent.id = prr.parent_id = parent.id JOIN party p2 ON parent.party_id = p2.id
Và đột nhiên bạn có một view rất đơn giản về dữ liệu mà bạn muốn nhưng trên một mô hình dữ liệu có tính linh hoạt cao.
7. Không cải thiện đầu vào
Đây là một việc lớn. Bây giờ tôi thích PHP nhưng nếu bạn không biết bạn đang làm gì thì thật dễ dàng để tạo ra các trang web dễ bị tấn công. Không có gì tổng kết nó tốt hơn so với câu chuyện về Bobby Tables bé nhỏ =)).
Dữ liệu được cung cấp bởi người dùng bằng các UR, form dữ liệu và cookies nên luôn được coi là nguy hiểm và nên được cải thiện. Đẩm bảo rằng bạn đang nhận được những gì bạn mong muốn.
8.Không sử dụng các câu lệnh được chuẩn bị
câu lệnh chuẩn bị là khi bạn compile một truy vấn trừ dữ liệu được sử dụng trong mệnh đề inserts, updates và WHERE và sau đó cung cấp sau. Ví dụ:
SELECT * FROM users WHERE username = 'bob'
vs
SELECT * FROM users WHERE username = ?
hay
SELECT * FROM users WHERE username = :username
tuỳ thuộc vào nền tảng của bạn.
Tôi đã từng nhìn thấy cơ sở dữ liệu bị phá huỷ bởi làm cái này. Về cơ bản, mỗi khi cơ sở dữ liệu hiện đại gặp một truy vấn mới, nó phải biên dịch nó. Nếu nó gặp một truy vẫn đã từng trước đó, bạn đang cho csdl cơ hội truy vẫn cache đã biên dịch và thực thi nó. Bằng cách thực hiện câu truy vấn nhiều lần bạn đang chó phép cơ sở dữ liệu để tìm kiếm và tối ưu thích hợp ( ví dụ, bằng cách ghim các câu truy vấn đã biên dịch trong bộ nhớ)
Sử dụng các câu lệnh đưuọc chuẩn bị cũng sẽ cung cấp cho bạn số liệu thống kê có ý nghĩa về tần suất các truy vấn nhất định được sử dụng.
các câu lệnh đưuọc chuẩn bị cũng sẽ giúp bạn chống lại các cuộc tấn công SQL injection tốt hơn.
9. Không chuẩn hóa đủ
Chuẩn hóa cơ sở dữ liệu về cơ bản là quá trình tối ưu hóa thiết kế cơ sở dữ liệu hoặc cách bạn tổ chức dữ liệu của bạn thành các bảng.
Mới tuần này I bắt gặp đoạn code không mong muốn where nơi mà ai đó đã tách một mảng và chèn nó vào trong một trường đơn trong CSDL. Chuẩn hoá là cố gắng xử lí phần tử của mảng đó như một hàng riêng biệt trong một bảng con (từ là quan hệ một-nhiều).
Điều này cũng xuất hiện trong Phương pháp tốt nhất để lưu trữ danh sách ID người dùng:
Tôi đã từng nhìn thấy trong các hệ thống khác mà danh sách được lưu trữ trong một mảng PHP tuần tự.
Tuy nhiên, sự thiếu chuẩn hóa lại có nhiều hình thức.
Đọc thêm:
10. Chuẩn hoá quá nhiều
Điều này có vẻ như mâu thuẫn với điểm trên nhưng chuẩn hóa, giống như cái, nó là một công cụ. It is a means to an end and not an end in and of itself. Tôi nghĩ rằng nhiều nhà phát triển quên điều này và bắt đầu xử lí một "phương tiện" như là một "thành tựu đạt được". Đơn vị kiểm thử là một ví dụ điển hình của việc này.
Tôi đã từng làm việc trên một hệ thống mà đã có một hệ thống phân cấp rất lớn cho khách hàng mà đã gặp một cái gì đó như:
Licensee -> Dealer Group -> Company -> Practice -> ...
như vậy bạn phải join khoảng 11 bảng với nhau trước khi bạn có thể nhận được bất kỳ dữ liệu có ý nghĩa. ó là một ví dụ điển hình về quá trình chuẩn hóa quá xa.
Hơn nữa, cẩn thận và xem xét denormalization có thể có những lợi ích về hiệu năng rất lớn nhưng bạn phải thực sự cẩn thận khi làm việc này.
More:
- Tại sao quá nhiều Cơ sở dữ liệu chuẩn hóa có thể là một điều tồi
- Bao nhiêu là chuẩn hoá đủ trong thit kế cơ sở dữ liệu?
- Khi nào không chuẩn hoá cơ sở dữ liệu SQL
- Có thể chuẩn hoá là không chuẩn
- Ví dụ hiếm gặp của các thảo luận chuẩn hoá cơ sở dữ liệu trên Coding Horror
11. Sử dụng exclusive arcs
Một exclusive arc là một lỗi phổ biến mà một bảng được tạo ra với hai hoặc nhiều khoá ngoại, nơi một và chỉ một trong số họ có thể không null. Lỗi lớn. Đối với một điều, nó trở nên khó khăn hơn nhiều để duy trì tính toàn vẹn dữ liệu. Sau tất cả, thậm chí với tính toàn vẹn tham chiếu, không có gì ngăn cản hai hoặc nhiều hơn các khóa ngoại này được thiết lập (các ràng buộc kiểm tra phức tạp dù sao đi nữa).
Từ Hướng dẫn Thực tiễn về Thiết kế Cơ sở dữ liệu Quan hệ:
Chúng tôi đã khuyên rất nhiều để chống lại việc xây dựng exclusive arc bất cứ khi nào có thể, vì lý do tốt mà họ có thể khó sử dụng để viết code và đặt ra nhiều khó khăn bảo trì.
12. Không phân tích hiệu suất về các truy vấn dưới bất kì hình thức nào
Chủ nghĩa thực dụng thống trị tối cao, đặc biệt là trong sở dữ liệu. Nếu bạn đang gắn bó với các nguyên tắc thì bạn có lẽ đã mắc phải những sai lầm. Lấy ví dụ của các truy vấn tổng hợp từ phía trên. Phiên bản tổng hợp có thể trông "đẹp" nhưng hiệu suất của nó là rất tồi. Việc so sanh về hiệu suất nên kết thúc cuộc tranh luận (nhưng nó thì không) nhưng thêm 1 điều rằng : việc bắn quá nhiều view thông báo tệ ngay trong vị trí đầu là ngu dốt, thậm chí là nguy hiểm. 13. Quá phụ thuộc vào UNION ALL và đặc biệt là các cấu trúc UNION
Một UNION trong SQL chỉ đơn thuần nối các tập dữ liệu đồng nhất, có nghĩa là chúng có cùng kiểu và số cột. Sự khác biệt giữa chúng là UNION ALL là một sự ghép nối đơn giản và nên được ưu tiên hơn bất cứ khi nào có thể trong khi một UNION ngầm sẽ làm một DISTINCT để loại bỏ các bản sao trùng lặp.
Các UNION, như DISTINCT, có chỗ của chúng. Có các ứng dụng hợp lệ. Nhưng nếu bạn thấy mình đang làm rất nhiều, đặc biệt trong các truy vấn phụ, thì có thể bạn đang làm sai. Đó có thể là trường hợp xây dựng truy vấn kém hoặc mô hình dữ liệu được thiết kế kém buộc bạn phải làm những việc như vậy.
Các UNION, đặc biệt khi sử dụng trong các kết nối hoặc các truy vấn phụ thuộc, có thể làm tê liệt cơ sở dữ liệu. Cố gắng tránh chúng bất cứ khi nào có thể.
14. Sử dụng điều kiện OR trong truy vấn
Điều này có vẻ vô hại. Sau tất cả, các phép AND là OK. Phép OR cũng nên được OK phải không? Sai rồi. Về cơ bản, điều kiện AND và hạn chế tập dữ liệu trong khi điều kiện OR phát triển nó nhưng không theo cách để tối ưu. Đặc biệt khi các điều kiện OR khác nhau có thể giao cắt, do đó buộc trình tối ưu hóa có kết quả hiệu quả trong DISTINCT.
Tồi:
... WHERE a = 2 OR a = 5 OR a = 11
Tốt hơn:
... WHERE a IN (2, 5, 11)
Bây giờ, trình tối ưu hoá SQL của bạn có thể biến truy vấn đầu tiên thành thứ hai. Nhưng nó có thể không. Chỉ cần không làm điều đó.
15.Không thiết kế mô hình dữ liệu để có giải pháp hiệu suất cao
Đây là một điểm khó để định lượng. Nó thường được xem xét bởi hiệu quả của nó. Nếu bạn thấy mình đang viết các truy vấn gnarly cho các tác vụ tương đối đơn giản hoặc các truy vấn để tìm ra thông tin tương đối đơn giản không hiệu quả thì có lẽ bạn có một mô hình dữ liệu tồi.
Trong một số điểm này tóm tắt tất cả điều này là tổng kết tất cả các điều trước đó nhưng nó có thêm 1 cảnh báo là những việc như tối ưu truy vấn thường xong đầu tiên trong khi điều này nên được hoàn thành thứ 2. Trước hết bạn phải đảm bảo bạn có một mô hình dữ liệu tốt trước khi cố gắng tối ưu hóa hiệu suất. Như Knuth đã nói:
Tối ưu hóa sớm là gốc rễ của tất cả các điều ác
16. Sử dụng các giao dịch cơ sở dữ liệu không chính xác
Tất cả các dữ liệu thay đổi cho một quá trình cụ thể phải là rất nhỏ. nghĩa là nếu vận hành thành công, nó sẽ thực hiện đầy đủ. nếu nó không thành công, dữ liệu sẽ không thay đổi. - Không nên có những thay đổi "hoàn thành một nửa".
Lý tưởng nhất, cách đơn giản nhất để đạt được điều này là toàn bộ thiết kế hệ thống nên cố gắng hỗ trợ tất cả các thay đổi dữ liệu thông qua các câu lệnh INSERT / UPDATE / DELETE. Trong trường hợp này, không có xử lý transaction đặc biệt nào là cần thiết, vì cơ sở dữ liệu của bạn nên tự động làm như vậy.
Tuy nhiên, nếu bất kỳ quy trình nào yêu cầu nhiều lệnh được thực hiện như là một đơn vị để giữ dữ liệu ở trạng thái nhất quán, thì cần điều khiển transaction thích hợp.
- Bắt đầu transaction trước câu lệnh đầu tiên.
- Commit transaction sau câu lệnh cuối cùng.
- Trên bất kỳ lỗi nào, hãy Hoàn tác Giao dịch. Và rất quan trọng! Đừng quên bỏ qua / hủy bỏ tất cả các câu lệnh theo sau lỗi.
Cũng nên chú ý cẩn thận đến các subtelties của lớp kết nối cơ sở dữ liệu của bạn, và công cụ cơ sở dữ liệu tương tác trong vấn đề này.
17. Không hiểu mô hình "dựa trên cơ sở"
Ngôn ngữ SQL theo một mô hình cụ thể phù hợp với các loại vấn đề cụ thể. Các phần mở rộng khác nhau của nhà cung cấp cụ thể tuy nhiên, ngôn ngữ phải cố gắng để giải quyết các vấn đề thông thường trong các ngôn ngữ như Java, C #, Delphi vv
Sự thiếu hiểu biết này thể hiện theo một vài cách.
- Áp dụng không chính đáng quá nhiều quy tắc hoặc logic trên các dữ liệu.
- Việc sử dụng không phù hợp hoặc quá mức các con trỏ. Đặc biệt là khi một truy vấn duy nhất là đủ.
- Giả định sai rằng trigger thực hiện ngay khi mỗi dòng bị ảnh hưởng trong khi cập nhật nhiều hàng.
Xác định phân chia trách nhiệm rõ ràng và cố gắng sử dụng công cụ thích hợp để giải quyết từng vấn đề.