MySQL là Hệ thống quản lý cơ sở dữ liệu quan hệ [RDBMS] nguồn mở được tìm kiếm nhiều nhất sử dụng Ngôn ngữ truy vấn có cấu trúc [SQL]
Truy vấn SQL được tạo kém hoặc không có tổ chức hoạt động như một kẻ giết người mạnh mẽ đối với hiệu suất tổng thể của ứng dụng của bạn. Do đó, điều quan trọng là phải tối ưu hóa hiệu suất truy vấn của bạn. MySQL đi kèm với các công cụ giúp chúng tôi tối ưu hóa các truy vấn. Hãy đi sâu vào các mẹo cần thiết và hữu ích để cải thiện Truy vấn MySQL về tốc độ và hiệu suất
1. Tối ưu hóa cơ sở dữ liệu của bạn
Bạn cần biết cách thiết kế lược đồ để hỗ trợ truy vấn hiệu quả. Các truy vấn và lược đồ được thiết kế tốt là rất quan trọng để ứng dụng của bạn hoạt động bình thường
Chỉ tối ưu hóa các truy vấn MySQL của bạn sẽ không mang lại hiệu suất cơ sở dữ liệu tuyệt vời. Cơ sở dữ liệu có cấu trúc tốt là rất quan trọng cùng với truy vấn được tối ưu hóa. Mặt khác, trong trường hợp dữ liệu tăng đột biến, hiệu suất cơ sở dữ liệu sẽ bị ảnh hưởng xấu
Các chiến lược sau đây sẽ giúp bạn tối ưu hóa cơ sở dữ liệu của mình
a. Bình thường hóa bảng
Chuẩn hóa là một kỹ thuật thiết kế cơ sở dữ liệu sắp xếp các bảng theo kiểu làm giảm sự dư thừa và phụ thuộc của dữ liệu. Nó chia các bảng lớn hơn thành các bảng nhỏ hơn và liên kết chúng bằng các mối quan hệ. Điều này giúp tránh các trường sẽ không có giá trị. Nó cũng đảm bảo rằng tất cả các trường trong bảng chỉ thuộc về một miền dữ liệu được mô tả
Ví dụ: trong bảng nhân viên, các trường có thể là id, tên, số an sinh xã hội, nhưng ba trường đó không liên quan gì đến bộ phận. Chỉ id nhân viên mô tả nhân viên thuộc bộ phận nào. Vì vậy, điều này ngụ ý rằng nhân viên thuộc bộ phận nào sẽ ở trong một bảng khác
b. Sử dụng các loại dữ liệu tối ưu
MySQL hỗ trợ các loại dữ liệu khác nhau và việc chọn đúng loại để lưu trữ dữ liệu của bạn là rất quan trọng để có hiệu suất tốt. MySQL hỗ trợ dữ liệu kiểu số, kiểu ngày và giờ, và kiểu chuỗi [ký tự] [số nguyên, số float, double, date, date_time, Varchar, text, v.v. ]. Các loại dữ liệu khác nhau phục vụ các mục đích khác nhau. Khi tạo bảng, bạn cần hiểu loại dữ liệu mà mỗi cột sẽ chứa và chọn loại dữ liệu phù hợp nhất
Nếu một trường yêu cầu một giá trị ngày, thì sử dụng kiểu dữ liệu date_time là tốt nhất vì bạn không phải chạy các hàm phức tạp để chuyển đổi trường thành ngày khi truy xuất bản ghi bằng SQL. Sử dụng các giá trị số nguyên nếu bạn muốn tất cả các giá trị là số. Khi tính toán, MySQL có thể làm tốt hơn với các giá trị số nguyên so với các kiểu dữ liệu văn bản, chẳng hạn như Varchar [lưu trữ các chuỗi ký tự có độ dài thay đổi và là kiểu dữ liệu chuỗi phổ biến nhất]
Làm cho độ dài của kiểu dữ liệu càng nhỏ càng tốt. Ví dụ: Varchar[10] luôn hoạt động tốt hơn Varchar[255]
c. Tránh các giá trị Null
Cho phép các giá trị null [không có bất kỳ giá trị nào trong một cột] trong cơ sở dữ liệu của bạn là một ý tưởng thực sự tồi trừ khi trường có thể có giá trị null một cách hợp lý. Sự hiện diện của giá trị null có thể ảnh hưởng xấu đến kết quả cơ sở dữ liệu của bạn
Ví dụ: nếu bạn muốn lấy tổng của tất cả các đơn đặt hàng trong cơ sở dữ liệu, kết quả dự đoán có thể hoạt động không tốt nếu một bản ghi đơn đặt hàng cụ thể có số lượng không. Loại hành vi sai trái này có thể không chỉ xảy ra nếu bạn đã sử dụng câu lệnh 'ifnull' của MySQL để trả về giá trị thay thế
d. Tránh Quá Nhiều Cột
Nhược điểm lớn nhất của việc có nhiều cột là thêm IO và chi phí lưu trữ
Có các bảng rộng có thể cực kỳ tốn kém và gây ra chi phí lưu trữ. Lý tưởng nhất là không vượt quá một trăm trừ khi logic kinh doanh của bạn đặc biệt yêu cầu điều này
Trái ngược với việc tạo một bảng rộng, việc chia nhỏ nó thành các cấu trúc logic có thể có lợi. Giả sử bạn đang tạo một bảng nhân viên. Trong một số trường hợp nhất định, bạn nhận ra rằng một nhân viên có thể có nhiều địa chỉ. Sau đó, tốt hơn là tạo một bảng riêng để nhập địa chỉ của nhân viên tham chiếu lại bảng nhân viên bằng trường 'employee_id'
Tránh sử dụng từ khóa “*” và chỉ định dữ liệu cần thiết trong mệnh đề chọn. Điều này có thể giúp giảm mức sử dụng mạng trong khi tìm nạp tập dữ liệu lớn
2. Tối ưu hóa tham gia
Giảm các câu lệnh nối trong các truy vấn. Một câu lệnh SQL có mẫu được thiết kế kém bao gồm nhiều phép nối có thể không hoạt động tốt. Một nguyên tắc nhỏ là có tối đa một chục phép nối cho mỗi truy vấn
3. Lập chỉ mục Tất cả các cột được sử dụng trong các mệnh đề 'where', 'order by' và 'group by'
CHỈ SỐ. Chỉ mục cơ sở dữ liệu là một cấu trúc dữ liệu giúp cải thiện tốc độ hoạt động trong một bảng. Các chỉ mục có thể được tạo bằng cách sử dụng một hoặc nhiều cột, cung cấp cơ sở cho cả tra cứu ngẫu nhiên nhanh chóng và sắp xếp hiệu quả quyền truy cập vào bản ghi
Khi tạo một chỉ mục, các điều kiện bình đẳng trong các điều kiện WHERE và JOIN thực sự quan trọng. Chẳng hạn, các điều kiện như name = ‘John’ sẽ cho phép cơ sở dữ liệu lọc hầu hết các hàng từ bảng và chạy qua một số ít hàng để trả về kết quả cần thiết. Vì vậy, chúng ta nên bắt đầu lập chỉ mục bằng cách thêm các cột này vào chỉ mục
Sau đó, bạn chỉ nên thêm một trong các điều kiện phạm vi chọn lọc nhất vì MySQL không thể xử lý nhiều điều kiện hơn. Trong một số trường hợp khi không có điều kiện phạm vi, việc thêm các cột GROUP BY / ORDER BY là hợp lý, giả sử việc sắp xếp chỉ được thực hiện theo một hướng [ASC / DESC]
Mệnh đề GROUP BY được sử dụng để sắp xếp kết quả và do đó nếu
-thứ tự đúng của chỉ mục được sử dụng hoặc
-chỉ các cột ngoài cùng bên trái được sử dụng trong nhóm theo
-cột ngoài cùng bên trái được sử dụng trong mệnh đề WHERE và phần còn lại theo đúng thứ tự trong chỉ mục mệnh đề GROUP BY sẽ được sử dụng
Hãy thử áp dụng force-index nếu mysql không sử dụng đúng chỉ mục. Hiệu suất của từng chỉ mục có thể được quan sát bằng cách sử dụng câu lệnh EXPLAIN
4. Sử dụng Tìm kiếm Toàn văn
Tìm kiếm toàn văn bản của MySQL [FTS] nhanh hơn nhiều so với các truy vấn sử dụng ký tự đại diện. Để thêm chỉ mục tìm kiếm toàn văn vào bảng mẫu của sinh viên, chúng ta có thể sử dụng lệnh MySQL bên dưới
mysql>Thay đổi bảng sinh viên THÊM FULLTEXT [first_name, last_name];
mysql>Chọn * từ các sinh viên khớp [first_name, last_name] LẠI ['Jones'];
Trong ví dụ trên, chúng tôi đã chỉ định các cột mà chúng tôi muốn khớp [first_name và last_name] với từ khóa tìm kiếm của chúng tôi ['Jones']
Chỉ một hàng sẽ được quét ngay cả khi cơ sở dữ liệu của sinh viên của chúng tôi có hàng lớn và điều này sẽ tăng tốc cơ sở dữ liệu
Hãy xem xét tình huống khi bạn muốn chạy truy vấn bằng toán tử so sánh 'hoặc' trên các trường hoặc cột khác nhau trong một bảng cụ thể. Ở đây, nếu bạn sử dụng quá nhiều từ khóa 'hoặc' trong mệnh đề WHERE, có khả năng trình tối ưu hóa MySQL có thể chọn sai quét toàn bộ bảng để truy xuất bản ghi
Nếu bạn có một chỉ mục có thể tối ưu hóa một mặt của truy vấn và một chỉ mục khác để tối ưu hóa mặt kia, mệnh đề hợp có thể làm cho truy vấn chạy nhanh hơn. Ví dụ;
mysql> chọn * từ các sinh viên trong đó first_name như 'Jones%' hoặc last_name như 'Jones%' ;
Truy vấn ở trên có thể chạy chậm hơn nhiều so với truy vấn bên dưới sử dụng toán tử hợp nhất kết hợp các kết quả của 2 truy vấn nhanh riêng biệt tận dụng lợi thế của các chỉ mục
mysql> chọn từ các sinh viên trong đó first_name như 'Jones%' liên kết tất cả chọn từ các sinh viên trong đó last_name như '
6. Bộ nhớ đệm truy vấn MySQL
Như chúng ta đã biết, bộ nhớ đệm được sử dụng để cải thiện hiệu suất. Nó sẽ nhanh hơn các trang web hoặc ứng dụng. Bộ đệm truy vấn MySQL là bộ đệm chung được chia sẻ giữa các phiên. Bộ đệm truy vấn lưu trữ kết quả của các truy vấn CHỌN cho phép trả lại nhanh truy vấn nếu nhận được một truy vấn giống hệt trong tương lai. Khi máy chủ cho cùng một truy vấn, MySQL ask sẽ lấy kết quả từ bộ đệm thay vì chạy lại truy vấn. Điều này thực sự sẽ đẩy nhanh quá trình đáng kể. Kết quả sẽ được đặt trong bộ nhớ cache như Memcached hoặc Cassandra
Chỉ thị cấu hình
Hãy để chúng tôi kiểm tra cách bật bộ nhớ đệm truy vấn trong máy chủ MySQL
Để kích hoạt bộ nhớ đệm truy vấn, chúng ta cần thêm các chỉ thị cấu hình sau
1] query_cache_size=KÍCH THƯỚC
2] query_cache_type=TÙY CHỌN
query_cache_size=SIZE
Chỉ thị đầu tiên cần thiết để cho phép bộ nhớ đệm truy vấn trong máy chủ MySQL là “query_cache_size=SIZE”
Lệnh này cho phép chúng tôi đặt kích thước bộ nhớ được phân bổ cho kết quả truy vấn bộ đệm. Trong một máy chủ điển hình, giá trị mặc định cho lệnh này sẽ là '0'. Điều đó có nghĩa là bộ đệm truy vấn bị tắt. Để bật bộ nhớ đệm truy vấn, chúng tôi cần đặt một số giá trị cho giá trị này. Chúng ta nên đặt giá trị theo dung lượng bộ nhớ mà chúng ta dự định phân bổ cho bộ nhớ đệm truy vấn