Vậy làm cách nào để tìm ra các truy vấn gây ra sự cố và khắc phục chúng? . Khi đã bật Phân tích truy vấn [QAN] trong PMM, bạn có thể chỉ cần nhìn vào bảng để xác định truy vấn hàng đầu
Khi bạn nhấp vào truy vấn trong bảng, bạn sẽ thấy một số thống kê về truy vấn đó và [trong hầu hết các trường hợp], một ví dụ
Xem Bản trình diễn về Giám sát và Quản lý Percona
Không có giám sát và quản lý Percona
Bây giờ, hãy giả sử rằng bạn chưa cài đặt PMM [Tôi chắc chắn rằng nó đang được xử lý khi bạn đọc phần này]. Để tìm các truy vấn có vấn đề, bạn sẽ cần thực hiện một số thao tác thu thập và xử lý thủ công mà PMM thực hiện cho bạn. Sau đây là quy trình tốt nhất để thu thập và tổng hợp các truy vấn hàng đầu
- Đặt long_query_time = 0 [trong một số trường hợp, bạn có thể cần giới hạn xếp hạng để không làm ngập nhật ký]
- Bật ghi nhật ký chậm và thu thập trong một thời gian [slow_query_log = 1]
- Dừng thu thập và xử lý nhật ký bằng pt-query-digest
- Bắt đầu xem xét các truy vấn hàng đầu trong thời gian sử dụng tài nguyên
Lưu ý – bạn cũng có thể sử dụng lược đồ hiệu suất để xác định các truy vấn, nhưng việc thiết lập lược đồ đó nằm ngoài phạm vi của bài đăng này. Đây là một tài liệu tham khảo tốt về cách sử dụng P_S để tìm các truy vấn dưới mức tối ưu
Khi tìm kiếm các truy vấn không hợp lệ, một trong những chỉ báo hàng đầu là sự khác biệt lớn giữa rows_examined và rows_sent. Trong trường hợp truy vấn dưới mức tối ưu, các hàng được kiểm tra sẽ rất lớn so với một số lượng nhỏ các hàng được gửi
Khi bạn đã xác định được truy vấn của mình, đã đến lúc bắt đầu quá trình tối ưu hóa. Rất có thể các truy vấn ở đầu danh sách của bạn [trong PMM hoặc báo cáo tóm tắt] thiếu chỉ số. Các chỉ mục cho phép trình tối ưu hóa nhắm mục tiêu các hàng bạn cần thay vì quét mọi thứ và loại bỏ các giá trị không khớp. Hãy lấy truy vấn mẫu sau đây làm ví dụ
Vỏ bọc1
2
3
4
CHỌN *
TỪ người dùng
WHERE tên người dùng = "admin1"
ĐẶT HÀNG BỞI last_login DESC;
Điều này trông giống như một truy vấn đơn giản nên khá đơn giản. Tuy nhiên, nó đang hiển thị dưới dạng ngốn tài nguyên và làm chậm máy chủ. Đây là cách nó xuất hiện trong đầu ra pt-query-digest
Vỏ bọc1
2
3
4
5
# Hồ sơ
# Xếp hạng Truy vấn ID Thời gian phản hồi Cuộc gọi R/Gọi V/M Mục
# ==== ================== ==================================
# 1 0xA873BB85EEF9B3B9 0. 4011 98. 7% 2 0. 2005 0. 40 CHỌN người dùng
# MISC 0xMISC 0.0053 1.3% 7 0.0008 0.0
Vỏ bọc1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
# Truy vấn 1. 0. 18QP, 0. Đồng thời 04x, ID 0xA873BB85EEF9B3B9 tại byte 3391
# Mục này được bao gồm trong báo cáo vì nó khớp với --limit
# Điểm. V/M = 0. 40
# Phạm vi thời gian. 2018-08-30T21. 38. 38 đến 2018-08-30T21. 38. 49
# Thuộc tính pct tổng tối thiểu tối đa trung bình 95% stddev trung vị
# ======================================================
# Đếm 22 2
# Thời gian thực hiện 98 401ms 54us 401ms 201ms 401ms 284ms 201ms
# Thời gian khóa 21 305us 0 305us 152us 305us 215us 152us
# Hàng đã gửi 6 1 0 1 0. 50 1 0. 71 0. 50
# Kiểm tra hàng 99 624. 94k 0 624. 94k 312. 47k 624. 94k 441. 90k 312. 47k
# Hàng ảnh hưởng 0 0 0 0 0 0 0 0
# byte đã gửi 37 449 33 416 224. 50 416 270. 82 224. 50
# Kích thước truy vấn 47 142 71 71 71 71 0 71
# Sợi dây
# Cơ sở dữ liệu plive_2017
# Máy chủ localhost
# Lỗi cuối cùng 0
# Người dùngroot
# Phân phối thời gian truy vấn
# 1us
# 10us ###############################################
# 100us
# 1ms
# 10 mili giây
# 100ms ##############################################
# 1 giây
# 10s+
# Những cái bàn
# Hiển thị TÌNH TRẠNG BẢNG TỪ `plive_2017` THÍCH 'người dùng'\G
# HIỂN THỊ TẠO BẢNG `plive_2017`. `người dùng`\G
# GIẢI THÍCH /*. 50100 PHÂN VÙNG*/
CHỌN * TỪ người dùng WHERE username = "admin1" ORDER BY last_login DESC\G
Chúng ta có thể thấy ngay số lượng hàng được kiểm tra cao so với. các hàng đã gửi, như được đánh dấu ở trên. Vì vậy, bây giờ chúng tôi đã xác định được truy vấn vấn đề, hãy bắt đầu tối ưu hóa truy vấn đó. Bước 1 trong việc tối ưu hóa truy vấn sẽ là chạy một kế hoạch GIẢI THÍCH
Vỏ bọc1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> GIẢI THÍCH CHỌN * FROM user WHERE username = "admin1" ORDER BY last_login DESC\G
*************************** 1. hàng ***************************
id. 1
select_type. ĐƠN GIẢN
bảng. người dùng
phân vùng. NULL
loại. TẤT CẢ
possible_keys. NULL
phím. NULL
key_len. NULL
ref. NULL
hàng. 635310
được lọc. 10. 00
Bổ sung. Sử dụng ở đâu; Sử dụng filesort
1 hàng trong bộ, 1 warning [0.00 giây]
Đầu ra EXPLAIN là manh mối đầu tiên cho thấy truy vấn này không được lập chỉ mục chính xác. loại. TẤT CẢ chỉ ra rằng toàn bộ bảng đang được quét để tìm một bản ghi. Trong nhiều trường hợp, điều này sẽ dẫn đến áp lực I/O trên hệ thống nếu tập dữ liệu của bạn vượt quá bộ nhớ. Sử dụng sắp xếp tệp chỉ ra rằng một khi nó đi qua toàn bộ bảng để tìm các hàng của bạn, thì nó phải sắp xếp chúng [một triệu chứng phổ biến của việc CPU tăng đột biến]
Giới hạn hàng được kiểm tra
Một điều quan trọng cần hiểu là điều chỉnh truy vấn là một quá trình lặp đi lặp lại. Không phải lúc nào bạn cũng làm đúng ngay lần đầu tiên và các kiểu truy cập dữ liệu có thể thay đổi theo thời gian. Về mặt tối ưu hóa, điều đầu tiên chúng tôi muốn làm là lấy truy vấn này bằng cách sử dụng chỉ mục và không sử dụng quét toàn bộ. Đối với điều này, chúng tôi muốn xem xét mệnh đề WHERE. trong đó tên người dùng = “admin1”
Với cột này về mặt lý thuyết là có chọn lọc, một chỉ mục về tên người dùng sẽ là một khởi đầu tốt. Hãy thêm chỉ mục và chạy lại truy vấn
Vỏ bọc1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> THAY ĐỔI BẢNG người dùng ADD INDEX idx_name [username];
Truy vấn OK, 0 hàng affected [6.94 giây]
Bản ghi. 0 Bản sao. 0 Cảnh báo. 0
mysql> GIẢI THÍCH CHỌN * FROM user WHERE username = "admin1" ORDER BY last_login DESC\G
*************************** 1. hàng ***************************
id. 1
select_type. ĐƠN GIẢN
bảng. người dùng
phân vùng. NULL
loại. ref
possible_keys. idx_name
phím. idx_name
key_len. 131
ref. const
hàng. 1
được lọc. 100. 00
Bổ sung. Sử dụng chỉ mục điều kiện; Sử dụngfilesort
1 hàng trong bộ, 1 warning [0.01 giây]
Tối ưu hóa Sắp xếp
Vậy là chúng ta đã đi được nửa đường. loại. ref cho biết chúng tôi hiện đang sử dụng một chỉ mục và bạn có thể thấy các hàng giảm từ 635 nghìn xuống 1. Ví dụ này không phải là tốt nhất vì điều này tìm thấy một hàng, nhưng điều tiếp theo chúng tôi muốn giải quyết là sắp xếp tệp. Đối với điều này, chúng tôi sẽ cần thay đổi chỉ mục tên người dùng thành chỉ mục tổng hợp [nhiều cột]. Nguyên tắc chung cho một chỉ mục tổng hợp là làm việc theo cách của bạn từ các cột được chọn lọc nhiều nhất đến các cột được chọn lọc ít nhất, và sau đó nếu bạn cần sắp xếp, hãy giữ nguyên đó là trường cuối cùng. Với tiền đề đó, hãy sửa đổi chỉ mục mà chúng ta vừa thêm để bao gồm trường last_login
Vỏ bọc1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> THAY ĐỔI BẢNG người dùng DROP INDEX idx_name, ADD INDEX idx_name_login [username, last_login];
Truy vấn OK, 0 hàng affected [7.88 giây]
Bản ghi. 0 Bản sao. 0 Cảnh báo. 0
mysql> GIẢI THÍCH CHỌN * FROM user WHERE username = "admin1" ORDER BY last_login DESC\G
*************************** 1. hàng ***************************
id. 1
select_type. ĐƠN GIẢN
bảng. người dùng
phân vùng. NULL
loại. ref
possible_keys. idx_name_login
phím. idx_name_login
key_len. 131
ref. const
hàng. 1
được lọc. 100. 00
Bổ sung. Sử dụng ở đâu
1 hàng trong bộ, 1 warning [0.00 giây]
Và chúng tôi đã có nó. Ngay cả khi truy vấn này quét nhiều hàng, nó sẽ đọc chúng theo thứ tự được sắp xếp, do đó, CPU bổ sung cần thiết cho việc sắp xếp sẽ bị loại bỏ. Để hiển thị điều này, hãy thực hiện cùng một chỉ mục này trên một cột không phải là duy nhất [tôi đã để email là không phải là duy nhất cho bản demo này]
Vỏ bọc1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
mysql> select count[1] from user where email = "[email protected]";
+-----------+
| đếm[1] .
+-----------+
| 64 .
+-----------+
1 hàng trong bộ [0.23 giây]
mysql> THAY ĐỔI BẢNG người dùng ADD INDEX idx_email [email, last_login];
Truy vấn OK, 0 hàng affected [8.08 giây]
Bản ghi. 0 Bản sao. 0 Cảnh báo. 0
mysql> GIẢI THÍCH CHỌN * FROM user WHERE email = "[email protected]" ORDER BY last_login DESC\G
*************************** 1. hàng ***************************
id. 1
select_type. ĐƠN GIẢN
bảng. người dùng
phân vùng. NULL
loại. ref
possible_keys. idx_email
phím. idx_email
key_len. 131
ref. const
hàng. 64
được lọc. 100. 00
Bổ sung. Sử dụng ở đâu
1 hàng trong bộ, 1 warning [0.00 giây]
Tóm lại, quy trình chung để điều chỉnh truy vấn SQL tuân theo quy trình này
- Xác định truy vấn [bằng tay hoặc bằng công cụ như PMM]
- Kiểm tra kế hoạch GIẢI THÍCH của truy vấn
- Xem lại định nghĩa bảng
- Tạo chỉ mục
- Bắt đầu với các cột trong mệnh đề WHERE
- Đối với các chỉ mục tổng hợp, hãy bắt đầu với cột chọn lọc nhất và làm việc với cột ít chọn lọc nhất
- Đảm bảo các cột được sắp xếp ở cuối chỉ mục tổng hợp
- Xem lại kế hoạch giải thích được cập nhật và sửa đổi khi cần thiết
- Tiếp tục xem xét máy chủ để xác định các thay đổi trong các kiểu truy cập yêu cầu lập chỉ mục mới
Mặc dù việc tối ưu hóa truy vấn có vẻ khó khăn, nhưng việc sử dụng một quy trình có thể giúp đạt được mục tiêu dễ dàng hơn nhiều. Đương nhiên, việc tối ưu hóa các truy vấn phức tạp không đơn giản như ví dụ trên, nhưng chắc chắn là có thể thực hiện được khi chia nhỏ. Và hãy nhớ rằng các kỹ sư của Percona luôn sẵn sàng giúp đỡ bạn khi bạn gặp khó khăn. Chúc mừng tối ưu hóa
Xem Bản trình diễn về Giám sát và Quản lý Percona
Bản tóm tắt giải pháp của chúng tôi “Thiết lập và chạy với Máy chủ Percona cho MySQL” phác thảo việc thiết lập cơ sở dữ liệu MySQL® tại chỗ bằng Máy chủ Percona cho MySQL. Nó bao gồm chuyển đổi dự phòng và các thành phần kinh doanh liên tục cơ bản