Xác định các truy vấn chậm bằng Công cụ phân tích của MySQL. Tạp chí cơ sở dữ liệu

Rob Gravelle xem xét lệnh Slow Query Log và Giải thích, cả hai lệnh này đều giúp xác định các truy vấn chậm và có thể được sử dụng để xác định và tối ưu hóa các truy vấn chậm nhằm loại bỏ các nút cổ chai do chúng gây ra

Sử dụng các công cụ phân tích MySQL, bạn có thể xác định và tối ưu hóa các truy vấn chậm để loại bỏ các tắc nghẽn mà chúng gây ra. Rob Gravelle kiểm tra lệnh Slow Query Log và Giải thích, cả hai lệnh này có thể giúp xác định các truy vấn chậm

Khi bạn
xem xét lượng dữ liệu gây tê liệt đang được truy cập hàng ngày bởi
mọi người trên khắp thế giới bạn nhận ra rằng công việc mà cơ sở dữ liệu quan hệ làm
thực sự là kỳ diệu. Tuy nhiên, không giống như phép lạ thực sự, những phép lạ được cung cấp bởi cơ sở dữ liệu
như MySQL không chỉ xảy ra. Họ cần một chút giúp đỡ từ các nhà thiết kế của họ,
nhà phát triển và quản trị viên. Đó là nơi các công cụ phân tích MySQL xuất hiện
Trong. Sử dụng các công cụ này, bạn có thể xác định và tối ưu hóa các truy vấn chậm để
loại bỏ các tắc nghẽn mà chúng gây ra. Hôm nay chúng ta sẽ kiểm tra Chậm
Lệnh Nhật ký Truy vấn và Giải thích, cả hai lệnh này có thể giúp xác định các truy vấn chậm

Kích hoạt Nhật ký truy vấn chậm

Chậm
nhật ký truy vấn chứa tất cả các câu lệnh SQL chiếm nhiều hơn một số lượng nhất định
giây để thực hiện

Một số
Những điều cần lưu ý. Đầu tiên, MySQL viết một câu lệnh vào nhật ký truy vấn chậm
sau khi nó được thực thi và tất cả các khóa đã được giải phóng, vì vậy thứ tự nhật ký
có thể khác với thứ tự thực hiện. Thứ hai, lưu ý rằng thời gian để có được
khóa bảng ban đầu không được tính là thời gian thực hiện. Thứ ba, truy vấn
được xử lý bởi bộ đệm truy vấn không được thêm vào nhật ký truy vấn chậm, cũng như các truy vấn
điều đó sẽ không được hưởng lợi từ sự hiện diện của một chỉ mục vì bảng có số không
hàng hoặc một hàng

Đến
bật nhật ký truy vấn chậm, khởi động chương trình máy chủ mysqld với các tùy chọn –
tùy chọn log-slow-queries[=file_name] và long_query_time. long_query_time
tùy chọn chỉ định giới hạn thời gian của các truy vấn chậm tính bằng giây. long_query_time tối thiểu
giá trị là 1 trong khi mặc định là 10. Tương tự như vậy, nếu không có file_name nào được cung cấp cho –log-slow-queries,
tên mặc định là host_name-slow. đăng nhập
Máy chủ tạo tệp trong thư mục dữ liệu trừ khi có tên đường dẫn tuyệt đối
được đưa ra để chỉ định một cái khác

Đây là
cú pháp cho phép
Nhật ký truy vấn chậm trong tệp cấu hình máy chủ MySQL trong phần mysqld

[mysqld]
log-slow-queries[=]
long_query_time=

A
ví dụ thực tế

[mysqld]
long_query_time         = 15
log-slow-queries        = /var/log/mysql/mysql-slow.log

Sử dụng
làm theo để thiết lập nhật ký truy vấn chậm thông qua tham số lệnh

root> ./bin/mysqld_safe --log_slow_queries=/mysql/logs/slow_query.log --long_query_time=20

Đến
đơn giản hóa việc kiểm tra các truy vấn trong nhật ký truy vấn chậm dài, bạn có thể sử dụng mysqldumpslow
lệnh trên một số phiên bản của MySQL

Truy vấn
không sử dụng chỉ mục cũng có thể được ghi vào nhật ký truy vấn chậm bằng cách bao gồm
tùy chọn — log-queries-not-using-indexes

Tuyên bố GIẢI THÍCH

GIẢI THÍCH
câu lệnh cung cấp phân tích về câu lệnh SELECT đã chỉ định. Để sử dụng
câu lệnh EXPLAIN, chỉ cần bao gồm từ khóa EXPLAIN, theo sau là bảng hoặc
câu lệnh SELECT, như thể hiện trong cú pháp sau

EXPLAIN tbl_name
 
Or:
 
EXPLAIN SELECT select_options

trong sử dụng
Các bảng có nguồn gốc trong bài viết Truy vấn MySQL của bạn để cải thiện hiệu suất mà chúng tôi tạo
một truy vấn kiểm tra số lượng tiền thưởng cho mỗi
Nhân viên. Đây là những gì câu lệnh EXPLAIN tạo ra cho nó

EXPLAIN
SELECT e.name, 
       e.salary, 
       COUNT[b.bonus_id] AS 'Total Bonuses'
FROM employees e 
     LEFT OUTER JOIN 
[SELECT emp_id, bonus_id  FROM bonuses WHERE YEAR[award_date] = 2009] AS b
 ON e.id = b.emp_id
GROUP BY e.id;

Các
kết quả

Tôi

lựa chọn đối tượng

bàn

loại hình

có thể_keys

Chìa khóa

key_len

giới thiệu

hàng

Thêm

1

SƠ ĐẲNG

e

TẤT CẢ CÁC

[VÔ GIÁ TRỊ]

[VÔ GIÁ TRỊ]

[VÔ GIÁ TRỊ]

[VÔ GIÁ TRỊ]

8

Dùng tạm;
sử dụng filesort

1

SƠ ĐẲNG

TẤT CẢ CÁC

[VÔ GIÁ TRỊ]

[VÔ GIÁ TRỊ]

[VÔ GIÁ TRỊ]

[VÔ GIÁ TRỊ]

10

2

NGUỒN GỐC

tiền thưởng

TẤT CẢ CÁC

[VÔ GIÁ TRỊ]

[VÔ GIÁ TRỊ]

[VÔ GIÁ TRỊ]

[VÔ GIÁ TRỊ]

12

Sử dụng ở đâu

Mỗi
hàng đầu ra từ EXPLAIN chứa mười cột cung cấp thông tin về một
bàn. Đây là bảng phân tích theo từng cột của đầu ra của EXPLAIN

1. Tôi. Mã định danh CHỌN
   Đây là số thứ tự của SELECT trong truy vấn
2. lựa chọn đối tượng. Loại CHỌN, mà
   có thể là bất kỳ giá trị nào được liệt kê bên dưới

• GIẢN DỊ. CHỌN đơn giản [không sử dụng UNION hoặc truy vấn con]
• SƠ ĐẲNG. CHỌN ngoài cùng
• LIÊN HIỆP. Câu lệnh SELECT thứ hai trở lên trong UNION
• CÔNG ĐOÀN PHỤ THUỘC. Thứ hai hoặc muộn hơn
  Câu lệnh CHỌN trong UNION, phụ thuộc vào truy vấn bên ngoài
• KẾT QUẢ CÔNG ĐOÀN. Kết quả của một CÔNG ĐOÀN
• YÊU CẦU PHỤ. CHỌN đầu tiên trong
  truy vấn con
• YÊU CẦU PHỤ PHỤ THUỘC. CHỌN đầu tiên
  trong truy vấn phụ, phụ thuộc vào truy vấn bên ngoài
• NGUỒN GỐC. Bảng dẫn xuất CHỌN
  [truy vấn con trong mệnh đề TỪ]
• TRUY CẬP PHỤ KHÔNG THỂ TRUY CẬP. một truy vấn phụ
  mà kết quả không thể được lưu trữ và phải được đánh giá lại cho mỗi hàng của
  truy vấn bên ngoài

1. bàn. cái bàn mà
   hàng đầu ra đề cập đến
2. loại hình. kiểu tham gia. Đây là
   thông tin quan trọng vì một số loại tham gia thích hợp hơn những loại khác. Nơi đây
   là danh sách các loại liên kết khác nhau, được sắp xếp từ nhiều nhất đến ít nhất
   mong muốn

• hệ thống. Một bảng hệ thống chứa
  chỉ có một hàng. Đây là trường hợp đặc biệt của kiểu tham gia const [xem tiếp theo
  mục]
• hăng sô. Kết quả so sánh tất cả
  các phần của chỉ mục PRIMARY KEY hoặc UNIQUE thành các giá trị không đổi. cái bàn có
  nhiều nhất một hàng phù hợp, được đọc khi bắt đầu truy vấn. Tại vì
  chỉ có một hàng, các giá trị từ cột trong hàng này có thể được xem xét
  dưới dạng hằng số bởi phần còn lại của trình tối ưu hóa. bảng const là
  rất nhanh vì chúng chỉ được đọc một lần
• eq_ref. Được sử dụng cho các cột được lập chỉ mục
  được so sánh bằng toán tử bằng [=]. Giá trị so sánh có thể
  là một hằng số hoặc một biểu thức sử dụng các cột từ các bảng
  đọc trước bảng này. Một hàng được đọc từ bảng này cho mỗi
  sự kết hợp của các hàng từ các bảng trước đó. Khác với hệ thống
  và các loại const, đây là loại tham gia tốt nhất có thể. Nó được sử dụng
  khi tất cả các phần của một chỉ mục được sử dụng bởi liên kết và chỉ mục là một
  PRIMARY KEY hoặc chỉ mục UNIQUE
• giới thiệu. Được sử dụng nếu tham gia sử dụng
  chỉ tiền tố ngoài cùng bên trái của khóa hoặc nếu khóa không phải là chỉ mục PRIMARY KEY hoặc UNIQUE [nói cách khác, nếu liên kết không thể chọn một hàng dựa trên khóa
  giá trị]. Tất cả các hàng có giá trị chỉ mục phù hợp được đọc từ bảng này cho
  mỗi sự kết hợp của các hàng từ các bảng trước. Nếu chìa khóa đó là
  used chỉ khớp với một vài hàng, đây là một kiểu nối tốt. giới thiệu có thể là
  used for indexed columns that are compared using the = or
  nhà điều hành
• toàn văn. Phép nối được thực hiện
  sử dụng chỉ mục FULLTEXT
• ref_or_null. Loại tham gia này giống như ref,
  nhưng với việc bổ sung MySQL thực hiện tìm kiếm bổ sung cho các hàng
  chứa giá trị NULL. Tối ưu hóa loại liên kết này được sử dụng thường xuyên nhất trong việc giải quyết
  truy vấn phụ
• index_merge. Loại liên kết này chỉ ra
  rằng tối ưu hóa Hợp nhất chỉ mục được sử dụng. Trong trường hợp này, chìa khóa
  cột trong hàng đầu ra chứa danh sách các chỉ mục được sử dụng và key_len
  chứa danh sách các phần quan trọng dài nhất cho các chỉ mục được sử dụng
• unique_subquery. Chức năng tra cứu chỉ số
  thay thế hoàn toàn truy vấn con để có hiệu quả tốt hơn
• index_subquery. Kiểu nối này tương tự
  đến unique_subquery. Nó thay thế các truy vấn con IN, nhưng nó hoạt động cho
  chỉ mục không duy nhất trong truy vấn con
• phạm vi. Chỉ những hàng nằm trong một
  phạm vi đã cho được truy xuất, sử dụng chỉ mục để chọn các hàng. Chìa khóa
  cột trong hàng đầu ra cho biết chỉ mục nào được sử dụng. chìa khóa_len
  chứa phần khóa dài nhất đã được sử dụng. cột giới thiệu
  là NULL cho loại này. Phạm vi có thể được sử dụng khi một cột chính được
  so với một hằng số sử dụng bất kỳ dấu =, , >, >=,