Cách tối ưu hóa truy vấn mysql. Tối ưu hóa MySQL - những điều cơ bản để triển khai đúng cách. Công cụ hỗ trợ: Percona Toolkit để xác định các chỉ mục trùng lặp
Làm việc với cơ sở dữ liệu thường là điểm yếu nhất trong hiệu suất của nhiều Ứng dụng web. Và không chỉ các DBA mới phải lo lắng về điều này. Lập trình viên phải chọn cấu trúc đúng bảng, viết các truy vấn được tối ưu hóa và mã tốt. Sau đây là các phương pháp tối ưu hóa MySQL dành cho lập trình viên.
1. Tối ưu hóa truy vấn cho bộ đệm truy vấn
Hầu hết Máy chủ MySQL Bộ nhớ đệm truy vấn được kích hoạt. Một trong những cách tốt nhất cải thiện hiệu suất chỉ đơn giản là để lại bộ nhớ đệm cho cơ sở dữ liệu. Khi một truy vấn được lặp lại nhiều lần, kết quả của nó sẽ được lấy từ bộ đệm, nhanh hơn nhiều so với việc truy cập trực tiếp vào cơ sở dữ liệu. Vấn đề chính là nhiều người chỉ đơn giản sử dụng các truy vấn không thể lưu vào bộ nhớ đệm:
// yêu cầu sẽ không được lưu trữ$r = mysql_query( "CHỌN tên người dùng TỪ người dùng WHERE signup_date >= CURDATE()"); //và nó sẽ như vậy! $hôm nay = ngày("Y-m-d" ); $r = mysql_query( "CHỌN tên người dùng TỪ người dùng WHERE signup_date >= "$today"");Lý do là truy vấn đầu tiên sử dụng hàm CURDATE(). Điều này áp dụng cho tất cả các hàm như NOW(), RAND() và các hàm khác có kết quả không xác định. Nếu kết quả của một hàm có thể thay đổi thì MySQL sẽ không lưu trữ truy vấn đó. TRONG trong ví dụ nàyđiều này có thể được ngăn chặn bằng cách tính ngày trước khi thực hiện truy vấn.
2. Sử dụng EXPLAIN cho các truy vấn SELECT của bạn
// tạo một câu lệnh đã chuẩn bị sẵn if ($stmt = $mysqli ->chuẩn bị( "CHỌN tên người dùng TỪ người dùng WHERE state=?")) { // liên kết các giá trị$stmt ->bind_param("s", $state ); // thực thi $stmt ->execute(); // liên kết kết quả$stmt ->bind_result($tên người dùng); // lấy dữ liệu$stmt ->tìm nạp(); printf("%s đến từ %s\n" , $username , $state ); $stmt ->đóng(); )13. Yêu cầu không có bộ đệm
Thông thường, khi thực hiện một yêu cầu, tập lệnh sẽ dừng và chờ kết quả thực thi. Bạn có thể thay đổi điều này bằng cách sử dụng truy vấn không có bộ đệm.
Có một mô tả hay trong tài liệu về hàm mysql_unbuffered_query():
"mysql_unbuffered_query() gửi một truy vấn SQL tới MySQL mà không truy xuất hoặc tự động đệm các hàng kết quả như mysql_query(). Một mặt, điều này tiết kiệm một lượng bộ nhớ đáng kể cho các truy vấn SQL tạo ra tập kết quả lớn. Mặt khác, bạn có thể bắt đầu làm việc với tập hợp kết quả cắt sau khi hàng đầu tiên được truy xuất: bạn không phải đợi truy vấn SQL đầy đủ chạy."
Tuy nhiên có hạn chế nhất định. Bạn sẽ phải đọc tất cả các bản ghi hoặc gọi mysql_free_result() trước khi có thể chạy một truy vấn khác. Ngoài ra, bạn không thể sử dụng mysql_num_rows() hoặc mysql_data_seek() trên kết quả hàm.
14. Lưu trữ IP trong UNSIGNED INT
Nhiều lập trình viên lưu trữ địa chỉ IP trong trường loại VARCHAR(15) mà không biết rằng nó có thể được lưu trữ ở dạng số nguyên. INT chiếm 4 byte và có kích thước trường cố định.
Đảm bảo sử dụng UNSIGNED INT vì IP có thể được viết dưới dạng số không dấu 32 bit.
Sử dụng INET_ATON() trong yêu cầu của bạn để chuyển đổi địa chỉ IP thành số và INET_NTOA() để chuyển đổi lại. Các hàm tương tự tồn tại trong PHP - ip2long() và long2ip() (trong PHP, các hàm này cũng có thể trả về giá trị âm. Lưu ý từ The_Lion).
15. Bảng kích thước cố định (tĩnh) nhanh hơn
Nếu mỗi cột trong bảng có kích thước cố định thì bảng đó được gọi là "tĩnh" hoặc "kích thước cố định". Ví dụ về các cột có độ dài không cố định: VARCHAR, TEXT, BLOB. Nếu bạn đưa trường như vậy vào bảng, trường đó sẽ không còn được sửa nữa và sẽ được MySQL xử lý theo cách khác.
Sử dụng các bảng như vậy sẽ tăng hiệu quả, bởi vì... MySQL có thể tra cứu các bản ghi trong đó nhanh hơn. Khi nào nên chọn dòng mong muốn table, MySQL có thể tính toán vị trí của nó rất nhanh. Nếu kích thước bản ghi không cố định, nó sẽ được tìm kiếm theo chỉ mục.
Các bảng này cũng dễ dàng lưu vào bộ đệm và khôi phục hơn sau khi cơ sở dữ liệu gặp sự cố. Ví dụ: nếu bạn chuyển đổi VARCHAR(20) thành CHAR(20), mục nhập sẽ chiếm 20 byte, bất kể nội dung thực tế của nó là gì.
Sử dụng phương pháp "tách dọc", bạn có thể di chuyển các cột có độ dài hàng thay đổi vào một bảng riêng biệt.
16. Tách dọc
Phân vùng dọc đề cập đến việc chia bảng thành các cột để cải thiện hiệu suất.
Ví dụ 1. Nếu địa chỉ được lưu trữ trong bảng người dùng, thì thực tế là bạn sẽ không cần đến chúng thường xuyên. Bạn có thể chia bảng và lưu trữ địa chỉ trong bàn riêng. Như vậy, bảng người dùng sẽ được giảm kích thước. Năng suất sẽ tăng lên.
Ví dụ 2: Bạn có trường "last_login" trong bảng. Nó được cập nhật mỗi khi người dùng đăng nhập vào trang web. Nhưng tất cả các thay đổi đối với bảng đều xóa bộ đệm của nó. Bằng cách lưu trữ trường này trong một bảng khác, bạn sẽ giữ những thay đổi đối với bảng người dùng ở mức tối thiểu.
Nhưng nếu bạn liên tục sử dụng các phép nối trên các bảng này sẽ dẫn đến hiệu suất kém.
17. Tách biệt các truy vấn DELETE và INSERT lớn
Nếu cần thực hiện một yêu cầu lớn để xóa hoặc chèn dữ liệu, bạn cần cẩn thận để không làm hỏng ứng dụng. Hiệu suất yêu cầu lớn có thể khóa bảng và khiến toàn bộ ứng dụng gặp trục trặc.
Apache có thể chạy nhiều tiến trình song song cùng một lúc. Do đó, nó hoạt động hiệu quả hơn nếu các tập lệnh được thực thi càng nhanh càng tốt.
Nếu bạn khóa bàn lâu dài(ví dụ: trong 30 giây hoặc lâu hơn), khi đó với lưu lượng truy cập trang web cao, một hàng đợi lớn các quy trình và yêu cầu có thể phát sinh, điều này có thể dẫn đến làm việc chậm trang web hoặc thậm chí là sự cố máy chủ.
Nếu bạn có các truy vấn như thế này, hãy sử dụng LIMIT để chạy chúng theo từng đợt nhỏ.
18. Cột nhỏ nhanh hơn
Đối với cơ sở dữ liệu, làm việc với ổ cứng có lẽ là điểm yếu nhất. Các bản ghi nhỏ và gọn thường tốt hơn về mặt hiệu suất vì... giảm công việc của đĩa.
Tài liệu MySQL có danh sách các yêu cầu lưu trữ dữ liệu cho tất cả các loại dữ liệu.
Nếu bảng của bạn chỉ lưu trữ một vài hàng thì việc đặt khóa chính thành kiểu INT là vô nghĩa; tốt hơn nên đặt khóa chính là MEDIUMINT, SMALLINT hoặc thậm chí TINYINT. Nếu bạn không cần lưu trữ thời gian, hãy sử dụng DATE thay vì DATETIME.
Tuy nhiên, hãy cẩn thận kẻo mọi chuyện không diễn ra như Slashdot.
19. Chọn loại bàn phù hợp
20. Sử dụng ORM
21. Hãy cẩn thận với các kết nối liên tục
Kết nối liên tục được thiết kế để giảm chi phí thiết lập giao tiếp với MySQL. Khi kết nối được tạo, nó vẫn mở sau khi tập lệnh hoàn tất. Lần tới, tập lệnh này sẽ sử dụng kết nối tương tự.
mysql_pconnect() trong PHP
Nhưng điều này chỉ nghe có vẻ tốt về mặt lý thuyết. Theo kinh nghiệm cá nhân của tôi (và kinh nghiệm của người khác), việc sử dụng tính năng này là không hợp lý. Bạn sẽ gặp vấn đề nghiêm trọng với giới hạn kết nối, giới hạn bộ nhớ, v.v.
Apache tạo ra nhiều luồng song song. Đây là lý do chính khiến các kết nối liên tục không hoạt động tốt như chúng ta mong muốn. Trước khi sử dụng mysql_pconnect(), hãy tham khảo ý kiến của quản trị viên hệ thống của bạn.
Từ tác giả: một người bạn của tôi đã quyết định tối ưu hóa chiếc xe của anh ấy. Đầu tiên anh ấy tháo một bánh nên cắt nóc, sau đó là động cơ... Nói chung là bây giờ anh ấy đi được. Đây đều là hậu quả của việc tiếp cận sai lầm! Do đó, để DBMS của bạn tiếp tục chạy, việc tối ưu hóa MySQL phải được thực hiện chính xác.
Khi nào cần tối ưu hóa và tại sao?
Nó không đáng để đi vào cài đặt máy chủ và thay đổi các giá trị tham số một lần nữa (đặc biệt nếu bạn không biết điều này có thể kết thúc như thế nào). Nếu chúng ta coi chủ đề này là từ “tháp chuông” về việc cải thiện hiệu suất của các tài nguyên web, thì nó rộng đến mức cần phải dành toàn bộ ấn phẩm khoa học gồm 7 tập cho nó.
Nhưng rõ ràng là tôi không có được sự kiên nhẫn như vậy với tư cách là một nhà văn, và bạn với tư cách là một độc giả cũng vậy. Chúng tôi sẽ làm điều đó đơn giản hơn và cố gắng chỉ đi sâu hơn một chút vào nội dung tối ưu hóa của máy chủ MySQL và các thành phần của nó. Bằng cách thiết lập tối ưu tất cả các tham số DBMS, bạn có thể đạt được một số mục tiêu:
Tăng tốc độ thực hiện truy vấn.
Cải thiện hiệu suất máy chủ tổng thể.
Giảm thời gian chờ tải các trang tài nguyên.
Giảm mức tiêu thụ dung lượng máy chủ lưu trữ.
Giảm dung lượng đĩa tiêu thụ.
Chúng tôi sẽ cố gắng chia toàn bộ chủ đề tối ưu hóa thành nhiều điểm để ít nhiều rõ ràng điều gì khiến “nồi sôi”.
Tại sao phải thiết lập máy chủ
Trong MySQL, tối ưu hóa hiệu suất nên bắt đầu từ máy chủ. Trước hết, bạn nên tăng tốc độ hoạt động của nó và giảm thời gian xử lý các yêu cầu. Một phương tiện phổ biến để đạt được tất cả các mục tiêu trên là kích hoạt bộ nhớ đệm. Không biết “nó là gì”? Bây giờ tôi sẽ giải thích mọi thứ.
Nếu bộ nhớ đệm được bật trên phiên bản máy chủ của bạn, hệ thống MySQL sẽ tự động “ghi nhớ” truy vấn do người dùng nhập. Và những lần lặp lại tiếp theo, kết quả truy vấn này (để lấy mẫu) sẽ không được xử lý mà lấy từ bộ nhớ hệ thống. Hóa ra bằng cách này, máy chủ “tiết kiệm” thời gian đưa ra phản hồi và kết quả là tốc độ phản hồi của trang web tăng lên. Điều này cũng áp dụng cho tốc độ tải xuống tổng thể.
Trong MySQL, tối ưu hóa truy vấn có thể áp dụng cho các công cụ và CMS hoạt động trên cơ sở DBMS và PHP này. Trong trường hợp này, mã được viết bằng ngôn ngữ lập trình, để tạo ra một trang web động, yêu cầu một số bộ phận và nội dung cấu trúc của nó (hồ sơ, kho lưu trữ và các phân loại khác) từ cơ sở dữ liệu.
Nhờ kích hoạt bộ nhớ đệm trong MySQL, việc thực thi các truy vấn tới máy chủ DBMS nhanh hơn nhiều. Do đó, tốc độ tải của toàn bộ tài nguyên nói chung sẽ tăng lên. Và điều này có tác động tích cực đến cả trải nghiệm người dùng và vị trí của trang web trong kết quả tìm kiếm.
Kích hoạt và định cấu hình bộ nhớ đệm
Nhưng hãy chuyển từ lý thuyết “nhàm chán” sang thực hành thú vị. Chúng tôi sẽ tiếp tục tối ưu hóa hơn nữa cơ sở dữ liệu MySQL bằng cách kiểm tra trạng thái bộ nhớ đệm trên máy chủ cơ sở dữ liệu của bạn. Để thực hiện việc này, bằng cách sử dụng một yêu cầu đặc biệt, chúng tôi sẽ hiển thị giá trị của tất cả các biến hệ thống:
Đó là một vấn đề hoàn toàn khác.
Chúng ta hãy tạo một cái nhìn tổng quan nhỏ về các giá trị thu được, những giá trị này sẽ hữu ích cho chúng ta trong việc tối ưu hóa cơ sở dữ liệu MySQL:
has_query_cache – giá trị cho biết bộ nhớ đệm truy vấn có “BẬT” hay không.
query_cache_type – hiển thị loại bộ đệm đang hoạt động. Chúng ta cần giá trị "BẬT". Điều này cho biết rằng bộ nhớ đệm được bật cho tất cả các loại lựa chọn (lệnh SELECT). Ngoại trừ những trường hợp sử dụng tham số SQL_NO_CACHE (cấm lưu thông tin về truy vấn này).
Chúng tôi có tất cả các cài đặt được đặt chính xác.
Chúng tôi đo bộ đệm cho các chỉ mục và khóa
Bây giờ bạn cần kiểm tra xem dung lượng RAM được phân bổ cho các chỉ mục và khóa. Bạn nên đặt tham số này, quan trọng để tối ưu hóa cơ sở dữ liệu MySQL, ở mức 20-30% dung lượng RAM có sẵn cho máy chủ. Ví dụ: nếu 4 “ha” được phân bổ cho một phiên bản DBMS, thì bạn có thể thoải mái đặt 32 “mét”. Nhưng tất cả phụ thuộc vào đặc điểm của một cơ sở dữ liệu cụ thể và cấu trúc (loại) bảng của nó.
Để đặt giá trị tham số, bạn cần chỉnh sửa nội dung của tệp cấu hình my.ini, tệp này ở Denver nằm ở đường dẫn sau: F:\Webserver\usr\local\mysql-5.5
Mở tệp bằng Notepad. Sau đó, chúng tôi tìm tham số key_buffer_size trong đó và đặt kích thước tối ưu cho hệ thống PC của bạn (tùy thuộc vào “ha” RAM). Sau này, bạn cần khởi động lại máy chủ cơ sở dữ liệu.
DBMS sử dụng một số hệ thống con bổ sung (cấp thấp hơn) và tất cả các cài đặt chính của chúng cũng được chỉ định trong tệp cấu hình này. Do đó, nếu bạn cần tối ưu hóa MySQL InnoDB, thì chào mừng bạn đến đây. Chúng tôi sẽ nghiên cứu chủ đề này chi tiết hơn trong một trong những tài liệu tiếp theo của chúng tôi.
Đo lường mức độ của chỉ số
Việc sử dụng các chỉ mục trong bảng làm tăng đáng kể tốc độ xử lý và tạo ra phản hồi DBMS cho một truy vấn đã nhập. MySQL liên tục “đo lường” mức độ sử dụng chỉ mục và khóa trong mỗi cơ sở dữ liệu. Để có được giá trị này, hãy sử dụng truy vấn:
HIỂN THỊ TRẠNG THÁI THÍCH "handler_read%"
HIỂN THỊ TRẠNG THÁI THÍCH "handler_read%" |
Trong kết quả thu được, chúng ta quan tâm đến giá trị ở dòng Handler_read_key. Nếu con số được chỉ ra là nhỏ thì điều này cho thấy rằng các chỉ mục hầu như không bao giờ được sử dụng trong cơ sở dữ liệu này. Và điều này thật tệ (như của chúng tôi).
MySQL vẫn là cơ sở dữ liệu quan hệ phổ biến nhất trên thế giới nhưng nó cũng được tối ưu hóa ít nhất. Nhiều người giữ nguyên cài đặt mặc định mà không tìm hiểu sâu hơn. Trong bài viết này, chúng ta sẽ xem xét một số mẹo tối ưu hóa MySQL kết hợp với một số tính năng mới xuất hiện gần đây.
Tối ưu hóa cấu hình
Điều đầu tiên mà mọi người dùng MySQL nên làm để cải thiện hiệu suất là điều chỉnh cấu hình. Tuy nhiên, hầu hết mọi người đều bỏ qua bước này. Trong 5.7 (phiên bản hiện tại), cài đặt mặc định tốt hơn nhiều so với cài đặt trước đó, nhưng vẫn có thể và dễ dàng cải thiện chúng.
Chúng tôi hy vọng rằng bạn đang sử dụng Linux hoặc thứ gì đó như Vagrant -box (như Homestead Cải tiến của chúng tôi) và do đó, tệp cấu hình của bạn sẽ được đặt trong /etc/mysql/my.cnf . Có thể cài đặt của bạn thực sự sẽ tải một tệp cấu hình bổ sung vào tệp này. Vì vậy, hãy xem, nếu tệp my.cnf không chứa nhiều, hãy tìm trong /etc/mysql/mysql.conf.d/mysqld.cnf .
Điều chỉnh thủ công
Các cài đặt sau đây phải được thực hiện ngay lập tức. Theo các mẹo này, hãy thêm vào tệp cấu hình trong phần:
Innodb_buffer_pool_size = 1G # (ở đây thay đổi khoảng 50%-70% tổng RAM) innodb_log_file_size = 256M innodb_flush_log_at_trx_commit = 1 # có thể đổi thành 2 hoặc 0 innodb_flush_method = O_DIRECT
- innodb_buffer_pool_size. Vùng đệm là một loại “kho” để lưu trữ dữ liệu và chỉ mục trong bộ nhớ. Nó được sử dụng để lưu trữ dữ liệu được truy cập thường xuyên trong bộ nhớ. Và khi bạn sử dụng máy chủ chuyên dụng hoặc máy chủ ảo, nơi cơ sở dữ liệu thường bị tắc nghẽn, việc cung cấp phần lớn RAM cho nó là điều hợp lý. Do đó, chúng tôi cung cấp cho nó 50-70% tổng RAM. Có hướng dẫn thiết lập nhóm này trong tài liệu MySQL.
- innodb_log_file_size. Việc đặt kích thước tệp nhật ký được mô tả rõ ràng, nhưng tóm lại, đó là lượng dữ liệu được lưu trữ trong nhật ký trước khi bị xóa. Xin lưu ý rằng nhật ký trong trường hợp này không phải là bản ghi lỗi mà là một loại ảnh chụp nhanh delta về các thay đổi chưa được chuyển vào đĩa trong các tệp innodb chính. MySQL viết ở chế độ nền, nhưng điều này vẫn ảnh hưởng đến hiệu suất tại thời điểm viết. Tệp nhật ký lớn hơn có nghĩa là hiệu suất cao hơn do tạo ra ít điểm kiểm tra mới và nhỏ hơn nhưng cũng có thời gian khôi phục lâu hơn trong trường hợp xảy ra sự cố (phải ghi lại nhiều dữ liệu hơn vào cơ sở dữ liệu).
- innodb_flush_log_at_trx_commit được mô tả và hiển thị những gì xảy ra với tệp nhật ký. Giá trị 1 là an toàn nhất vì nhật ký được xóa vào đĩa sau mỗi giao dịch. Với giá trị 0 và 2, ACID được đảm bảo ít hơn nhưng hiệu suất lại cao hơn. Sự khác biệt không đủ lớn để vượt qua lợi ích ổn định ở mức 1.
- innodb_flush_method. Trên hết khi xóa dữ liệu, cài đặt này cần được đặt thành O_DIRECT - để tránh tình trạng đệm đôi. Tôi khuyên bạn nên luôn thực hiện việc này khi hệ thống I/O vẫn còn rất chậm. Mặc dù trên hầu hết các dịch vụ lưu trữ, như DigitalOcean, bạn sẽ có ổ SSD nên hệ thống I/O sẽ hoạt động hiệu quả hơn.
Có một công cụ từ Percona sẽ giúp chúng tôi tự động tìm ra các vấn đề còn lại. Lưu ý rằng nếu chúng tôi chạy nó mà không có cài đặt thủ công này thì chỉ 1 trong 4 cài đặt sẽ được xác định, vì 3 cài đặt còn lại phụ thuộc vào tùy chọn của người dùng và môi trường ứng dụng.
Thanh tra biến
Cài đặt trình kiểm tra biến trên Ubuntu:
Wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all. deb Sudo apt-get cập nhật Sudo apt-get cài đặt bộ công cụ percona
Đối với các hệ thống khác, hãy làm theo các hướng dẫn sau.
Sau đó chạy bộ công cụ:
Pt-variable-advisor h=localhost,u=homestead,p=secret
Bạn sẽ thấy kết quả này:
# WARN delay_key_write: Các khối chỉ mục MyISAM không bao giờ bị xóa cho đến khi cần thiết. # LƯU Ý max_binlog_size: Max_binlog_size nhỏ hơn mặc định là 1GB. # LƯU Ý Sort_buffer_size-1: Biến Sort_buffer_size thường phải được để mặc định trừ khi chuyên gia xác định cần phải thay đổi nó. # LƯU Ý innodb_data_file_path: Các tệp InnoDB tự động mở rộng có thể tiêu tốn rất nhiều dung lượng ổ đĩa và rất khó lấy lại sau này. # WARN log_bin: Ghi nhật ký nhị phân bị vô hiệu hóa, do đó không thể khôi phục và sao chép tại một thời điểm.
Ghi chú người dịch:
Trên máy cục bộ của tôi, ngoài điều này, tôi còn nhận được cảnh báo sau:
Thực tế là tham số innodb_flush_method cần được đặt thành O_DIRECT và lý do đã được thảo luận ở trên. Và nếu bạn làm theo trình tự điều chỉnh như trong bài thì bạn sẽ không thấy cảnh báo này.
Không có cái nào trong số này ( xấp xỉ: do tác giả chỉ định) các cảnh báo không quan trọng nên không cần phải sửa. Điều duy nhất có thể sửa được là thiết lập nhật ký nhị phân để sao chép và chụp ảnh nhanh.
Lưu ý: trong các phiên bản mới, kích thước binlog mặc định là 1G và cảnh báo này sẽ không xảy ra.
Max_binlog_size = 1G log_bin = /var/log/mysql/mysql-bin.log server-id=master-01 binlog-format = "ROW"
- max_binlog_size. Xác định độ lớn của nhật ký nhị phân. Họ ghi lại các giao dịch và yêu cầu của bạn và lập các điểm kiểm tra. Nếu một giao dịch vượt quá mức tối đa thì nhật ký có thể vượt quá kích thước của nó khi được lưu vào đĩa; nếu không thì MySQL sẽ hỗ trợ nó trong giới hạn này.
- log_bin. Tùy chọn này cho phép ghi nhật ký nhị phân nói chung. Không có nó, việc chụp nhanh hoặc sao chép là không thể. Xin lưu ý rằng điều này có thể có tác động lớn đến dung lượng ổ đĩa của bạn. server-id là một tùy chọn bắt buộc khi bật ghi nhật ký nhị phân, do đó nhật ký "biết" chúng đến từ máy chủ nào (để sao chép) và định dạng binlog chỉ đơn giản là cách chúng được viết.
Như bạn có thể thấy, MySQL mới có các giá trị mặc định gần như đã sẵn sàng để sản xuất. Tất nhiên, mỗi ứng dụng đều khác nhau và có những thủ thuật cũng như chỉnh sửa bổ sung mà nó áp dụng.
Bộ điều chỉnh MySQL
Công cụ hỗ trợ: Percona Toolkit để xác định các chỉ mục trùng lặp
Bộ công cụ Percona mà chúng tôi đã cài đặt trước đây cũng có một công cụ để phát hiện các chỉ mục trùng lặp, công cụ này có thể hữu ích khi sử dụng CMS của bên thứ ba hoặc chỉ tự kiểm tra xem bạn có vô tình thêm nhiều chỉ mục hơn mức cần thiết hay không. Ví dụ: cài đặt mặc định của WordPress có các chỉ mục trùng lặp trên bảng wp_posts:
Pt-duplicate-key-checker h=localhost,u=homestead,p=secret # ################################ ############################################ #homestead.wp_posts # #### ###################################################### ################### # Loại khóa_status_date kết thúc bằng tiền tố của chỉ mục được nhóm # Định nghĩa chính: # KEY `type_status_date` (`post_type`,`post_status`,`post_date` ,`ID`), # PRIMARY KEY (`ID`), # Loại cột: # `post_type` varchar(20) đối chiếu utf8mb4_unicode_520_ci không null mặc định "post" # `post_status` varchar(20) đối chiếu utf8mb4_unicode_520_ci không null mặc định "xuất bản " " # `post_date` datetime không null mặc định "0000-00-00 00:00:00" # `id` bigint(20) unsigned not null auto_increment # Để rút ngắn chỉ mục nhóm trùng lặp này, hãy thực thi: ALTER TABLE `homestead`. ` wp_posts` DROP INDEX `type_status_date`, THÊM CHỈ SỐ `type_status_date` (`post_type`,`post_status`,`post_date`);
Như bạn có thể thấy ở dòng cuối cùng, công cụ này cũng cung cấp cho bạn các mẹo về cách loại bỏ các chỉ mục trùng lặp.
Công cụ trợ giúp: Bộ công cụ Percona cho các chỉ mục không sử dụng
Bộ công cụ Percona cũng có thể phát hiện các chỉ mục không sử dụng. Nếu bạn đang ghi nhật ký các truy vấn chậm (xem phần tắc nghẽn bên dưới), bạn có thể chạy tiện ích và tiện ích sẽ kiểm tra xem các truy vấn đó có sử dụng chỉ mục trên bảng hay không và như thế nào.
Sử dụng chỉ mục Pt /var/log/mysql/mysql-slow.log
Để biết thông tin chi tiết về cách sử dụng tiện ích này, hãy xem .
Nơi hẹp
Phần này mô tả cách phát hiện và giám sát các tắc nghẽn cơ sở dữ liệu.
Trước tiên, hãy bật ghi nhật ký các truy vấn chậm:
Slow_query_log = /var/log/mysql/mysql-slow.log long_query_time = 1 log-queries-not-using-indexes = 1
Các dòng trên phải được thêm vào cấu hình mysql. Cơ sở dữ liệu sẽ theo dõi các truy vấn mất hơn 1 giây để hoàn thành và những truy vấn không sử dụng chỉ mục.
Sau khi có một số dữ liệu trong nhật ký này, bạn có thể phân tích dữ liệu đó để sử dụng chỉ mục bằng tiện ích pt-index-usage ở trên hoặc sử dụng pt-query-digest, tiện ích này sẽ tạo ra kết quả giống như sau:
Pt-query-digest /var/log/mysql/mysql-slow.log # 360ms thời gian người dùng, 20ms thời gian hệ thống, 24,66M rss, 92,02M vsz # Ngày hiện tại: Thứ năm ngày 13 tháng 2 22:39:29 2014 # Tên máy chủ: * # Tệp: mysql-slow.log # Tổng thể: tổng cộng 8, 6 duy nhất, 1,14 QPS, đồng thời 0,00 lần ________________ # Phạm vi thời gian: 2014-02-13 22:23:52 đến 22:23:59 # Tổng thuộc tính trung bình tối thiểu tối thiểu Trung vị stddev 95% # ============ ======= ======= ======= ======= ===== ======== ======= # Thời gian thực thi 3ms 267us 406us 343us 403us 39us 348us # Thời gian khóa 827us 88us 125us 103us 119us 12us 98us # Hàng đã gửi 36 1 15 4,50 14,52 4,18 3,89 # Hàng giám khảo 8 7 4 30 10,88 28,75 7,37 7,70 # Kích thước truy vấn 2,15k 153 296 245,11 284,79 48,90 258,32 # === = === ========== === ===== ====== = ==== ================ # Hồ sơ # Xếp hạng ID truy vấn Thời gian phản hồi Cuộc gọi R/Gọi V/M Mục # ==== ==== ====== ====== ===== ====== === ================== # 1 0x728E539F7617C14D 0,0011 41,0% 3 0,0004 0,00 CHỌN blog_article # 2 0x1290EEE0B201F3FF 0,0003 12,8% 1 0,0003 0,00 CHỌN danh mục_ mục # 3 0x31DE4535BDBFA465 0,0003 12,6% 1 0,0003 0,00 CHỌN danh mục_mục # 4 0xF14E15D0F47A5742 0,0003 12,1% 1 0,0003 0,00 CHỌN portfolio_category # 5 0x8F848005A09C9588 0,0003 11,8% 1 0,0003 0,00 CHỌN blog_category # 6 0x55F49C 753CA2ED64 0,0003 9,7% 1 0,0003 0,00 CHỌN blog_article # ==== ============ ====== ============= ===== ==== == ===== ================ # Truy vấn 1: 0 QPS, 0x đồng thời, ID 0x728E539F7617C14D tại byte 736 ______ # Điểm: V/M = 0,00 # Phạm vi thời gian: tất cả các sự kiện xảy ra vào lúc 13-02-2014 22:23:52 # Thuộc tính pct tổng tối thiểu tối đa trung bình 95 % stddev trung bình # ============= === ======= == ===== ======= ======= === ==== ======= ======= # Đếm 37 3 # Thời gian thực hiện 40 1ms 352us 406us 375us 403us 22us 366us # Thời gian khóa 42 351us 103us 125us 117us 119us 9us 119us # Hàng gửi 25 9 1 4 3 3,89 1,37 3,89 # Hàng kiểm tra 24 21 5 8 7 7,70 1,29 7,70 # Kích thước truy vấn 47 1,02k 261 262 261,25 258,32 0 258,32 # String: # Hosts localhost # Users * # Phân phối Query_time # 1us # 10us # 100us #### ############################# ####################### ########## # 1ms # 10ms # 100ms # 1s # 10s+ # Tables # SHOW TABLE TÌNH TRẠNG THÍCH " blog_article"\G # HIỂN THỊ TẠO BẢNG `blog_article`\G # GIẢI THÍCH /*!50100 PHẦN THAM GIA*/ CHỌN b0_.id NHƯ id0, b0_.slug NHƯ slug1, b0_.title NHƯ tiêu đề2, b0_.excerpt NHƯ đoạn trích3, b0_.external_link AS external_link4, b0_.description AS description5, b0_.created AS đã tạo6, b0_.updated AS đã cập nhật7 TỪ blog_article b0_ ĐẶT HÀNG BỞI b0_.created DESC LIMIT 10
Nếu muốn phân tích các nhật ký này theo cách thủ công, bạn có thể thực hiện tương tự, nhưng trước tiên, bạn cần xuất nhật ký sang định dạng dễ phân tích cú pháp hơn. Điều này có thể được thực hiện như thế này:
Mysqldumpslow /var/log/mysql/mysql-slow.log
Với các tùy chọn bổ sung, bạn có thể lọc dữ liệu để chỉ xuất những gì bạn cần. Ví dụ: 10 truy vấn hàng đầu, được sắp xếp theo thời gian thực hiện trung bình:
Mysqldumpslow -t 10 -s tại /var/log/mysql/localhost-slow.log
Phần kết luận
Trong bài viết tối ưu hóa MySQL toàn diện này, chúng tôi đã đề cập đến nhiều phương pháp và kỹ thuật khác nhau mà qua đó chúng tôi có thể làm cho MySQL của mình hoạt động hiệu quả.
Chúng tôi đã tìm ra cách tối ưu hóa cấu hình, nâng cấp các chỉ mục và loại bỏ một số điểm nghẽn. Đây hầu hết chỉ là lý thuyết, tuy nhiên, tất cả đều có thể áp dụng cho các ứng dụng trong thế giới thực.
Ngày 9 tháng 10 năm 2008 lúc 23:37Tối ưu hóa truy vấn MySQL
- MySQL
Trong công việc hàng ngày, bạn gặp phải những lỗi khá giống nhau khi viết truy vấn.
Trong bài viết này tôi muốn đưa ra ví dụ về cách KHÔNG viết truy vấn.
- Chọn tất cả các trường
CHỌN * TỪ bảngKhi viết truy vấn, không sử dụng lựa chọn tất cả các trường - "*". Chỉ liệt kê các lĩnh vực bạn thực sự cần. Điều này sẽ làm giảm lượng dữ liệu được tìm nạp và gửi. Ngoài ra, đừng quên bao gồm các chỉ mục. Ngay cả khi bạn thực sự cần tất cả các trường trong bảng, tốt hơn hết là bạn nên liệt kê chúng. Thứ nhất, nó cải thiện khả năng đọc mã. Khi sử dụng dấu hoa thị, không thể biết được trường nào trong bảng nếu không nhìn vào. Thứ hai, theo thời gian, số lượng cột trong bảng của bạn có thể thay đổi và nếu hôm nay có năm cột INT, thì sau một tháng, các trường TEXT và BLOB có thể được thêm vào, điều này sẽ làm chậm quá trình lựa chọn.
- Yêu cầu trong một chu kỳ.
Bạn cần hiểu rõ ràng rằng SQL là ngôn ngữ vận hành theo tập hợp. Đôi khi các lập trình viên đã quen với việc suy nghĩ theo ngôn ngữ thủ tục sẽ gặp khó khăn khi chuyển suy nghĩ của họ sang ngôn ngữ của tập hợp. Điều này có thể được thực hiện khá đơn giản bằng cách áp dụng một quy tắc đơn giản - “không bao giờ thực hiện các truy vấn trong một vòng lặp”. Ví dụ về cách thực hiện việc này:1. Mẫu
$news_ids = get_list("CHỌN news_id TỪ tin tức hôm nay ");
while($news_id = get_next($news_ids))
$news = get_row("CHỌN tiêu đề, nội dung TỪ tin tức WHERE news_id = ". $news_id);Quy tắc rất đơn giản - càng ít yêu cầu thì càng tốt (mặc dù có những ngoại lệ đối với điều này, giống như bất kỳ quy tắc nào). Đừng quên cấu trúc IN(). Đoạn mã trên có thể được viết trong một truy vấn:
CHỌN tiêu đề, nội dung TỪ hôm nay_news INNER THAM GIA tin tức USING(news_id)2. Phụ trang
$log = phân tích cú pháp_log();
while($record = next($log))
query("INSERT INTO log SET value = ". $log["value"]);!}Sẽ hiệu quả hơn nhiều khi nối và thực hiện một truy vấn:
CHÈN VÀO nhật ký (giá trị) GIÁ TRỊ (...), (...)3. Cập nhật
Đôi khi bạn cần cập nhật nhiều hàng trong một bảng. Nếu giá trị cập nhật giống nhau thì mọi thứ đều đơn giản:
CẬP NHẬT tin tức SET title="test" WHERE id IN (1, 2, 3).!}Nếu giá trị được thay đổi là khác nhau đối với mỗi bản ghi thì việc này có thể được thực hiện bằng truy vấn sau:
CẬP NHẬT tin tức
tiêu đề = TRƯỜNG HỢP
KHI news_id = 1 THÌ "aa"
KHI news_id = 2 THÌ "bb" KẾT THÚC
WHERE news_id IN (1, 2)Các thử nghiệm của chúng tôi cho thấy yêu cầu như vậy nhanh hơn 2-3 lần so với một số yêu cầu riêng biệt.
- Thực hiện các thao tác trên các trường được lập chỉ mục
CHỌN user_id TỪ người dùng Ở ĐÂU blog_count * 2 = $valueTruy vấn này sẽ không sử dụng chỉ mục, ngay cả khi cột blogs_count được lập chỉ mục. Để sử dụng chỉ mục, không được thực hiện chuyển đổi nào trên trường được lập chỉ mục trong truy vấn. Đối với những yêu cầu như vậy, hãy di chuyển các chức năng chuyển đổi sang phần khác:
CHỌN user_id TỪ người dùng Ở ĐÂU blogs_count = $value / 2;Ví dụ tương tự:
CHỌN user_id TỪ người dùng WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(đã đăng ký)<= 10;Sẽ không sử dụng chỉ mục trên trường đã đăng ký, trong khi
CHỌN user_id TỪ người dùng Ở ĐÂU đã đăng ký >= DATE_SUB(CURRENT_DATE, INTERVAL 10 NGÀY);
sẽ. - Chỉ tìm nạp các hàng để đếm số lượng của chúng
$result = mysql_query("CHỌN * TỪ bảng", $link);
$num_rows = mysql_num_rows($kết quả);
Nếu bạn cần chọn số hàng thỏa mãn một điều kiện nhất định, hãy sử dụng truy vấn bảng SELECT COUNT(*) FROM thay vì chọn tất cả các hàng chỉ để đếm số lượng hàng. - Đang tìm nạp thêm hàng
$result = mysql_query("CHỌN * TỪ bảng1", $link);
while($row = mysql_fetch_assoc($result) && $i< 20) {
…
}
Nếu bạn chỉ cần tìm nạp n hàng, hãy sử dụng LIMIT thay vì loại bỏ các hàng bổ sung trong ứng dụng. - Sử dụng ĐẶT HÀNG BỞI RAND()
CHỌN * TỪ bảng ĐẶT HÀNG THEO RAND() GIỚI HẠN 1;Nếu bảng có hơn 4-5 nghìn hàng thì ORDER BY RAND() sẽ hoạt động rất chậm. Sẽ hiệu quả hơn nhiều khi chạy hai truy vấn:
Nếu bảng có khóa chính auto_increment và không có khoảng trống:
$rnd = rand(1, query("CHỌN MAX(id) TỪ bảng"));
$row = query("CHỌN * TỪ bảng WHERE id = ".$rnd);Hoặc:
$cnt = query("CHỌN ĐẾM(*) TỪ bảng");
$row = query("CHỌN * TỪ GIỚI HẠN bảng ".$cnt.", 1");
tuy nhiên, điều này cũng có thể chậm nếu có số lượng hàng rất lớn trong bảng. - Sử dụng số lượng lớn THAM GIA
LỰA CHỌN
v.video_id
một cái tên,
g.thể loại
TỪ
video NHƯ v
CHỖ NỐI BÊN TRÁI
link_actors_videos AS la ON la.video_id = v.video_id
CHỖ NỐI BÊN TRÁI
diễn viên NHƯ một ON a.actor_id = la.actor_id
CHỖ NỐI BÊN TRÁI
link_genre_video NHƯ lg TRÊN lg.video_id = v.video_id
CHỖ NỐI BÊN TRÁI
thể loại NHƯ g TRÊN g.genre_id = lg.genre_idCần phải nhớ rằng khi kết nối các bảng một với nhiều, số lượng hàng trong vùng chọn sẽ tăng lên với mỗi THAM GIA tiếp theo. Đối với những trường hợp như vậy, sẽ nhanh hơn nếu chia một truy vấn như vậy thành nhiều truy vấn đơn giản.
- Sử dụng GIỚI HẠN
CHỌN… TỪ bảng GIỚI HẠN $bắt đầu, $mỗi_trangNhiều người nghĩ rằng truy vấn như vậy sẽ trả về $per_page của bản ghi (thường là 10-20) và do đó sẽ hoạt động nhanh chóng. Nó sẽ hoạt động nhanh chóng trong vài trang đầu tiên. Nhưng nếu số lượng bản ghi lớn và bạn cần thực hiện truy vấn SELECT... FROM table LIMIT 1000000, 1000020, thì để thực hiện truy vấn như vậy, trước tiên MySQL sẽ chọn 1000020 bản ghi, loại bỏ một triệu bản ghi đầu tiên và trả về 20. Điều này có thể không nhanh chút nào. Không có cách nào tầm thường để giải quyết vấn đề. Nhiều người chỉ đơn giản giới hạn số lượng trang có sẵn ở mức hợp lý. Bạn cũng có thể tăng tốc các truy vấn như vậy bằng cách sử dụng chỉ mục bao phủ hoặc giải pháp của bên thứ ba (ví dụ: nhân sư).
- Không sử dụng TRÊN CẬP NHẬT KHÓA DUPLICATE
$row = query("CHỌN * TỪ bảng WHERE id=1");Nếu ($ hàng)
query("CẬP NHẬT bảng BỘ cột = cột + 1 WHERE id=1")
khác
query("CHÈN VÀO bảng SET cột = 1, id=1");Cấu trúc tương tự có thể được thay thế bằng một truy vấn, miễn là có khóa chính hoặc khóa duy nhất cho trường id:
XÁC NHẬN VÀO bảng BỘ cột = 1, id=1 TRÊN CẬP NHẬT KHÓA DUPLICATE cột = cột + 1
