10 phương pháp hay nhất về MySQL hàng đầu. Tạp chí cơ sở dữ liệu

Rob Gravelle liệt kê 10 phương pháp hay nhất về MySQL dành cho quản trị viên cơ sở dữ liệu, kiến ​​trúc sư, nhà phát triển và nhân viên bảo mật

Nhiều nhóm và cá nhân tham gia vào lĩnh vực quản lý dữ liệu, từ quản trị viên MySQL, kiến ​​trúc sư, nhà phát triển, cũng như những người hỗ trợ cơ sở hạ tầng. Mỗi trong số này đóng một phần trong bảo mật, bảo trì và hiệu suất của cài đặt MySQL. Do đó, khi nói về các thông lệ tốt nhất, người ta phải xem xét chức năng nào trong số những chức năng này mà thông lệ cụ thể liên quan đến. Trong danh sách top 10 này, tôi sẽ cố gắng bao gồm một chút từ mỗi môn học. Tôi đã xem xét kinh nghiệm của bản thân cũng như tham khảo ý kiến ​​từ nhiều nguồn khác để tổng hợp danh sách cuối cùng này. Cho dù bạn có đồng ý với từng mục và thứ tự của chúng hay không, điều quan trọng là ít nhất hãy xem xét từng điểm được nêu ra ở đây ngày hôm nay. Vì vậy, không cần phải quảng cáo thêm, đây là danh sách 10 cá nhân hàng đầu của tôi

1. Chỉ mục các trường tìm kiếm

Bạn phải luôn lập chỉ mục các cột mà bạn định tìm kiếm trên đó. Tạo một chỉ mục trên một trường trong bảng sẽ tạo một cấu trúc dữ liệu khác chứa giá trị trường và con trỏ tới bản ghi mà nó liên quan. Cấu trúc chỉ mục này sau đó được sắp xếp, cho phép thực hiện tìm kiếm nhị phân trên nó. Một chỉ mục có thể được xác định cho một cột hoặc nhiều cột của một bảng nhất định

Quy tắc này cũng áp dụng cho các trường nơi tìm kiếm một phần chuỗi sẽ được thực hiện khi bắt đầu trường. Chẳng hạn, cụm từ “last_name LIKE ‘rob%'” sẽ sử dụng chỉ mục, trong khi “WHERE last_name LIKE '%ert%'” sẽ không

Điều này không có nghĩa là bạn càng có nhiều chỉ mục thì càng tốt. Mặc dù điều đó đúng ở một mức độ nào đó, nhưng hãy nhớ rằng mọi chỉ mục đều chiếm dung lượng ổ đĩa. Trong cơ sở dữ liệu MyISAM, tệp chỉ mục có thể nhanh chóng đạt đến giới hạn kích thước của hệ thống tệp cơ bản nếu nhiều trường trong cùng một bảng được lập chỉ mục

2. Tránh sử dụng “CHỌN *” trong Truy vấn của bạn

Theo nguyên tắc chung, càng nhiều dữ liệu được đọc từ các bảng, truy vấn càng trở nên chậm hơn. Xem xét rằng một số bảng sản xuất có thể chứa hàng tá cột, một số trong đó bao gồm các loại dữ liệu khổng lồ, sẽ thật điên rồ nếu chọn tất cả chúng. Máy chủ cơ sở dữ liệu tách biệt với máy chủ web sẽ chỉ làm trầm trọng thêm vấn đề này do dữ liệu phải được truyền qua mạng

Để nhắc lại, một thói quen tốt là luôn chỉ định cột nào bạn cần khi viết câu lệnh CHỌN

3. Đặt mật khẩu cho người dùng “root” và sau đó đổi tên người dùng

Đây là một mẹo bảo mật. Giống như với UNIX, điều đầu tiên bạn nên làm với một bản cài đặt MySQL sạch sẽ là đặt mật khẩu cho người dùng root

$ mysqladmin -u root password NEWPASSWORD

Thậm chí tốt hơn, khi bạn đã đặt mật khẩu, hãy đổi tên của người dùng “root” thành tên khác. Một tin tặc trên máy chủ MySQL có thể sẽ nhắm mục tiêu vào thư mục gốc, cả về trạng thái siêu người dùng của nó và vì đó là người dùng đã biết. Bằng cách thay đổi tên của người dùng root, bạn sẽ khiến tin tặc khó thành công hơn bằng cách sử dụng một cuộc tấn công vũ phu. Sử dụng chuỗi lệnh sau để đổi tên người dùng “root”

$ mysql -u root -p
mysql> use mysql;
mysql> update user set password=PASSWORD["NEWPASSWORD"] where 
User='';
mysql> flush privileges;
mysql> quit

4. Điều chỉnh truy vấn của bạn với EXPLAIN

Từ khóa EXPLAIN chắc chắn là công cụ phân tích mang tính hướng dẫn nhất trong kho vũ khí của MySQL. Sử dụng nó có thể cung cấp cho bạn cái nhìn sâu sắc có giá trị về các bước mà MySQL đang thực hiện để thực hiện truy vấn của bạn. Điều này có thể giúp bạn phát hiện ra các nút cổ chai và các vấn đề khác với truy vấn hoặc cấu trúc bảng của bạn

Trong bài viết Tối ưu hóa tốc độ truy xuất truy vấn MySQL thông qua phép nối bảng, tôi đã bao gồm cách sử dụng GIẢI THÍCH để xác định hiệu quả của phép nối bảng trong câu lệnh truy vấn sau

explain
select a.au_lname,
a.au_fname, 
ta.royaltyper, 
t.title, 
t.royalty
from authors a,
titleauthor ta,
titles t
where a.au_id = ta.au_id
and ta.title_id = t.title_id

EXPLAIN đã tạo ra các kết quả sau

id  select_type table   type    possible_keys   key         key_len  ref                             rows    Extra
1   SIMPLE      ta      ALL     [NULL]          [NULL]      [NULL]   [NULL]                          800	
1   SIMPLE      t       ref     NewIndex        NewIndex     23      crosstab_article.ta.title_id    100     Using where
1   SIMPLE      a       ALL     [NULL]          [NULL]      [NULL]   [NULL]                          1000    Using where; 
                                                                                                             Using join buffer

Kết quả của truy vấn GIẢI THÍCH sẽ cho bạn biết chỉ mục nào đang được sử dụng, cách quét và sắp xếp bảng cũng như các thông tin hữu ích khác. Ít nhất, bạn có thể chắc chắn rằng các số xuất hiện trong cột hàng càng thấp thì truy vấn sẽ chạy càng nhanh

5. Lập chỉ mục và sử dụng các loại cột giống nhau để tham gia

Nếu truy vấn của bạn chứa nhiều phép nối, bạn cần đảm bảo rằng các cột tạo nên phép nối được lập chỉ mục trên cả hai bảng. Điều này sẽ cho phép MySQL tối ưu hóa tốt hơn hoạt động tham gia

Tương tự như vậy, các cột được tham gia phải chia sẻ cùng một loại. Chẳng hạn, nếu bạn nối một cột DECIMAL với một trong các loại INT, MySQL sẽ không thể sử dụng ít nhất một trong các chỉ mục. Các cột kiểu chuỗi cũng phải sử dụng mã hóa ký tự giống nhau

6. GIỚI HẠN 1 Khi nhận được một hàng duy nhất

Có một số truy vấn chỉ trả về một hàng, chẳng hạn như những truy vấn tìm nạp một bản ghi duy nhất hoặc xác minh xem có bất kỳ bản ghi nào thỏa mãn mệnh đề WHERE hay không. Trong những trường hợp như vậy, việc thêm GIỚI HẠN 1 vào truy vấn của bạn có thể tăng hiệu suất. Điều này làm giảm thời gian thực hiện vì công cụ cơ sở dữ liệu sẽ ngừng quét các bản ghi sau khi nó tìm thấy bản ghi phù hợp đầu tiên, thay vì duyệt qua toàn bộ bảng hoặc chỉ mục

Cách sử dụng phổ biến thứ hai là trong truy vấn con. Trong câu lệnh SELECT sau đây, chúng tôi muốn truy xuất giá trị trường s2 đầu tiên được sắp xếp theo cột s1. Sau đó, chúng tôi có thể khớp nó với các giá trị truy vấn bên ngoài

SELECT * FROM t1 WHERE s1 = [SELECT s2 FROM t2 ORDER BY s1 LIMIT 1];

7. Ẩn MySQL khỏi Internet

Hầu hết các quản trị viên cơ sở dữ liệu và nhân viên an ninh có kinh nghiệm đều biết rằng không bao giờ lưu trữ cơ sở dữ liệu dưới thư mục gốc của máy chủ Web. Đối với các ứng dụng hỗ trợ Web, MySQL phải được ẩn sau tường lửa và giao tiếp chỉ nên được kích hoạt giữa các máy chủ ứng dụng và máy chủ Web của bạn. Một tùy chọn khác là sử dụng tùy chọn bỏ qua mạng của MySQL. Khi được bật, MySQL chỉ lắng nghe các kết nối ổ cắm cục bộ và bỏ qua tất cả các cổng TCP

8. Sử dụng các loại dữ liệu nhỏ nhất có thể

Điều này dường như là lẽ thường đối với tôi, vì nền tảng lập trình của tôi. Khi tôi còn học đại học, vẫn còn một số triết lý “bộ nhớ khan hiếm” được áp dụng từ thời ổ cứng 256 MB. Ngày nay, dường như không ai quan tâm đến bộ nhớ hoặc dung lượng ổ cứng. “Bộ nhớ rẻ. ” là câu ngạn ngữ mới. Mặc dù điều đó đúng về mặt đô la, nhưng việc đọc các loại dữ liệu lớn vẫn mất nhiều thời gian hơn so với các loại dữ liệu nhỏ hơn, vì loại trước yêu cầu nhiều cung đĩa hơn để đọc vào bộ nhớ

Đạo đức của câu chuyện là bỏ qua sự cám dỗ để ngay lập tức nhảy vào loại dữ liệu lớn nhất khi thiết kế bảng của bạn. Cân nhắc sử dụng int thay vì bigint. Bạn cũng nên tránh các trường văn bản char[255] lớn khi một varchar hoặc char[] nhỏ hơn là đủ. Sử dụng đúng loại dữ liệu sẽ phù hợp với nhiều bản ghi hơn trong bộ nhớ hoặc khối khóa chỉ mục, nghĩa là ít lần đọc hơn, do đó hiệu suất nhanh hơn

9. Tạo dạng xem để đơn giản hóa các phép nối bảng thường được sử dụng

Như đã thảo luận trong bài viết Truy vấn có thể tái sử dụng trong Viết bài trong MySQL của tôi, các khung nhìn giúp đơn giản hóa các lược đồ phức tạp và triển khai bảo mật. Một cách mà chế độ xem góp phần bảo mật là ẩn các trường kiểm tra khỏi nhà phát triển. Chúng cũng có thể được sử dụng để lọc ra các cột chưa được lập chỉ mục, chỉ để lại các trường tìm kiếm nhanh nhất. Lưu ý duy nhất khi sử dụng kỹ thuật này là bạn phải khá chắc chắn rằng mình sẽ không cần truy cập vào một trong các cột của bảng ẩn trong tương lai;

10. Tận dụng lợi thế của bộ nhớ đệm truy vấn

Bộ đệm truy vấn lưu trữ văn bản của câu lệnh CHỌN cùng với tập kết quả tương ứng. Nếu sau đó nhận được một câu lệnh giống hệt nhau, máy chủ sẽ truy xuất kết quả từ bộ đệm truy vấn thay vì phân tích cú pháp và thực hiện lại câu lệnh đó. Bộ đệm truy vấn được chia sẻ giữa các phiên, do đó, tập hợp kết quả do một khách hàng tạo ra có thể được gửi để phản hồi cùng một truy vấn do một khách hàng khác đưa ra. Hầu hết các máy chủ MySQL đều được bật bộ nhớ đệm truy vấn theo mặc định. Đó là một trong những phương pháp hiệu quả nhất để cải thiện hiệu suất

Đó là một điều tuyệt vời, nhưng bộ nhớ đệm truy vấn không phải là không có giới hạn. Lấy tuyên bố sau đây

Vấn đề ở đây là các truy vấn chứa các hàm không xác định nhất định – đó là những truy vấn mà MySQL không thể tính toán trước – như NOW[] và RAND[] không được lưu vào bộ đệm. May mắn thay, có một cách khắc phục dễ dàng để ngăn điều này xảy ra. Tức là lưu kết quả hàm vào một biến

SET @year = Year[CURDATE[]];
SELECT emp_id, 
    bonus_id 
FROM  bonuses 
WHERE YEAR[award_date] = @year;

Và đó là 10 phương pháp hay nhất của cá nhân tôi trong MySQL. Một điều mà tôi nhận thấy khi thực hiện nghiên cứu của mình cho bài viết này là có rất nhiều ý kiến ​​về việc phương pháp nào nên xứng đáng có một vị trí trong top 10. Theo ước tính của tôi, có nhiều yếu tố có thể ảnh hưởng đến trọng lượng của một mặt hàng đối với bạn, bao gồm vai trò cụ thể, kinh nghiệm, ngành nghề kinh doanh, phiên bản phần mềm và cấu hình phần cứng, v.v. Tôi sẽ hoan nghênh bạn thêm những đóng góp của riêng bạn dưới dạng nhận xét. Mọi mẹo đều hữu ích