Cột nào nên được lập chỉ mục trong MySQL?

Trong thời gian làm việc của mình, tôi có cơ hội làm việc với cơ sở dữ liệu MySQL có tới 700 triệu hàng. Bài đăng này sẽ nhấn mạnh cách lập chỉ mục và tối ưu hóa các truy vấn MySQL của bạn cho các chỉ mục

một năm trước   •   11 phút đọc

Bởi Akhil Mathew

Mục lục

Khi nào bạn nên tối ưu hóa cơ sở dữ liệu MySQL?

Lý tưởng nhất là việc điều chỉnh hiệu suất truy vấn nên diễn ra thường xuyên.
Lập chỉ mục không phải là quá trình một lần. Bạn nên tiến hành kiểm tra hiệu suất cơ sở dữ liệu hàng tuần hoặc hàng tháng để ngăn chặn các sự cố ảnh hưởng xấu đến ứng dụng của bạn.

Các triệu chứng rõ ràng nhất của các vấn đề về hiệu suất là

• Truy vấn mất nhiều thời gian thực hiện hơn dự kiến
• Các ứng dụng sử dụng cơ sở dữ liệu trở nên chậm hơn bình thường
• Lỗi hết thời gian kết nối
• Việc có nhiều truy vấn đồng thời chạy cùng một lúc trên một hệ thống bận rộn là điều bình thường, nhưng nó sẽ trở thành vấn đề khi các truy vấn này mất quá nhiều thời gian để hoàn thành

Cách nhận biết các vấn đề về hiệu suất truy vấn

Nắm bắt truy vấn MySQL nào của bạn bị hỏng là điều cần thiết trong việc chẩn đoán vấn đề về hiệu suất. Hãy để tôi giới thiệu hai cách giúp bạn xác định các truy vấn gây ra tắc nghẽn hiệu suất

1. MySQL Nhật ký truy vấn chậm. Kích hoạt nó
2. danh sách quy trình. Sử dụng nó

show processlist;
show full processlist;

Sử dụng EXPLAIN để xác định mức độ cần thiết của chỉ mục

Bây giờ bạn đã hiểu những kẻ gây rối trong ứng dụng là gì. Nhưng, làm thế nào bạn nhận ra rằng chúng hoạt động kém chỉ vì thiếu chỉ số?

GIẢI THÍCH hoạt động với CHỌN, XÓA, THAY THẾ và CẬP NHẬT (Từ Mysql phiên bản 5. 7 trở đi) câu lệnh. Cũng giống như bạn, MySQL sẽ có các kế hoạch tuyệt vời cho từng truy vấn về cách thực hiện, chọn chỉ mục nào, cách tham gia các bảng và thứ tự cần duy trì để đạt hiệu suất tối ưu

Kiểm tra mô tả bảng bên dưới, tôi muốn bạn ghi nhớ bảng này vì nó sẽ được sử dụng trong hầu hết các ví dụ của tôi. Hiện tại bảng không có bất kỳ chỉ mục nào

mô tả bảng

Hãy lấy một truy vấn mẫu và phân tích những gì EXPLAIN mang lại cho truy vấn đó

select
  *
from
  master_users
where
  email = '[email protected]'

Giải thích truy vấn trên đưa ra như…

Trước Chỉ mục

Hãy chia nhỏ kết quả EXPLAIN. Như bạn có thể thấy MySQL đã duyệt qua 152685 hàng (Xem cột. rows) của bảng để tìm một hàng phù hợp và nó tương đương với việc quét toàn bộ bảng vì bảng có tổng cộng 153728 hàng. Kết quả EXPLAIN có hai cột có tên là

ALTER TABLE
  master_users
ADD
  INDEX index_email(email)

9 và

select
  *
from
  master_users
where
  email = '[email protected]'
  and category_id = 3

0. Cột

ALTER TABLE
  master_users
ADD
  INDEX index_email(email)

9 cho chúng tôi biết các chỉ mục có sẵn trong một bảng hoặc các chỉ mục có thể được sử dụng cho các truy vấn của chúng tôi là gì

Cột

select
  *
from
  master_users
where
  email = '[email protected]'
  and category_id = 3

0 cho chúng tôi biết các chỉ mục mà MySQL đã sử dụng cho truy vấn được phân tích là gì. Hiện tại, cả hai đều trống. Cột

select
  *
from
  master_users
where
  email = '[email protected]'
  and category_id = 3

0 trống cung cấp cho bạn thông tin rằng truy vấn thiếu chỉ mục

Hãy thêm một chỉ mục và kiểm tra điều gì sẽ xảy ra

ALTER TABLE
  master_users
ADD
  INDEX index_email(email)

Sau khi thêm chỉ mục.

Bây giờ bạn có thể thấy rằng truy vấn được tối ưu hóa tốt hơn và MySQL có thể tìm thấy kết quả phù hợp mà không cần duyệt qua nhiều hàng. Nếu bạn để ý, chúng tôi có tên chỉ mục của chúng tôi ở cả hai cột

ALTER TABLE
  master_users
ADD
  INDEX index_email(email)

9 và

select
  *
from
  master_users
where
  email = '[email protected]'
  and category_id = 3

0

Tối ưu hóa các truy vấn phức tạp

Tôi biết rằng tối ưu hóa truy vấn bây giờ là một miếng bánh dành cho bạn. Trước khi chuyển sang các truy vấn phức tạp hơn, tôi muốn bạn tìm hiểu Quy tắc ngón tay cái trong việc lập chỉ mục

MySQL chỉ có thể sử dụng một chỉ mục cho mỗi bảng trong một truy vấn (Có trường hợp ngoại lệ. ví dụ: Hợp nhất các chỉ mục). Lý tưởng nhất là điều này có nghĩa là một bảng trong truy vấn phải sử dụng một chỉ mục duy nhất cho tất cả mệnh đề where, nối bảng, nhóm theo và sắp xếp theo thứ tự

Tin tôi đi, học quy tắc này là một bước đột phá trong sự nghiệp của tôi. Tôi đã từng tin rằng, cần phải thêm một chỉ mục riêng lẻ trên mỗi cột được sử dụng trong mệnh đề WHERE, mệnh đề THAM GIA, theo nhóm và theo thứ tự

Hãy thực hiện một truy vấn khác

select
  *
from
  master_users
where
  email = '[email protected]'
  and category_id = 3

Hãy ghi nhớ quy tắc ngón tay cái, bạn đã biết rằng việc tạo hai chỉ mục trên email và danh mục sẽ không giúp ích gì

select
  *
from
  master_users
where
  email = '[email protected]'
  and category_id = 3

6

Bây giờ, bạn sẽ làm gì?. Một chỉ mục nhiều cột (tổng hợp) để giải cứu

Chỉ số tổng hợp là gì?

Để tối ưu hóa các truy vấn như vậy, bạn nên tạo chỉ mục tổng hợp hoặc chỉ mục tổng hợp. Một chỉ mục tổng hợp có thể được mô tả như một hợp chất của nhiều cột.
Bằng cách thêm nhiều cột vào một chỉ mục, bạn có thể thu hẹp số hàng mà MySQL phải duyệt để tìm các hàng phù hợp. Một chỉ mục có thể bao gồm tối đa 16 cột.

Hãy xem cách bạn có thể tạo chỉ mục tổng hợp

select
  *
from
  master_users
where
  email = '[email protected]'

2

Thứ tự cột có quan trọng đối với chỉ mục tổng hợp không?

Vâng, nó quan trọng. Một cột có giá trị cardinality ít nhất (Có ít giá trị phân biệt hơn) phải luôn được định vị ở phía ngoài cùng bên trái trong chỉ mục tổng hợp. Thứ tự ưu tiên của các cột khác cũng phụ thuộc vào số lượng của các cột tương ứng

Bây giờ, Làm cách nào để tìm số lượng bản số hoặc số lượng giá trị riêng biệt của một cột?. Bạn chỉ có thể thực hiện một truy vấn đếm đặc biệt trên cột. (Không nên thực hiện các truy vấn như vậy được đề cập bên dưới trong một DB sản xuất khi nó đang chạy trong thời gian cao điểm của lưu lượng truy cập. )

select
  *
from
  master_users
where
  email = '[email protected]'

3

select
  *
from
  master_users
where
  email = '[email protected]'

4Số lượng đặc biệt của từng cột

Ở đây bạn có thể thấy rằng số lượng của cột

select
  *
from
  master_users
where
  email = '[email protected]'
  and category_id = 3

7 nhỏ hơn số lượng của cột email, vì vậy chúng ta nên tạo một chỉ mục tổng hợp như sau

select
  *
from
  master_users
where
  email = '[email protected]'

6

Khi bạn quan sát thứ tự mà các cột này được lập chỉ mục và do đó, cách chúng được sắp xếp sẽ hạn chế việc sử dụng chỉ mục của chúng tôi đối với một số truy vấn cụ thể

Ví dụ: chỉ mục chúng tôi vừa tạo

select
  *
from
  master_users
where
  email = '[email protected]'
  and category_id = 3

8 sẽ không có lợi cho truy vấn chẳng hạn như

select
  *
from
  master_users
where
  email = '[email protected]'

8

Khi nào thì một Chỉ mục Tổng hợp KHÔNG Hữu ích?

Vâng, có một lý do cho nó

Nếu bảng có chỉ mục nhiều cột, bất kỳ tiền tố ngoài cùng bên trái nào của chỉ mục đều có thể được trình tối ưu hóa sử dụng để tra cứu hàng. Ví dụ: nếu bạn có một chỉ mục ba cột trên

select
  *
from
  master_users
where
  email = '[email protected]'
  and category_id = 3

9, thì bạn đã lập chỉ mục các khả năng tìm kiếm trên

select
  *
from
  master_users
where
  email = '[email protected]'

20,

select
  *
from
  master_users
where
  email = '[email protected]'

21 và

select
  *
from
  master_users
where
  email = '[email protected]'
  and category_id = 3

9

Điều này có nghĩa là trình tối ưu hóa MySQL không thể sử dụng chỉ mục để thực hiện tra cứu nếu truy vấn không chứa cột tiền tố ngoài cùng bên trái của chỉ mục. Vì vậy, cột ngoài cùng bên trái trong chỉ mục tổng hợp được gọi là cột Tra cứu

MySQL có thể sử dụng chỉ mục nhiều cột cho các truy vấn kiểm tra tất cả các cột trong chỉ mục hoặc các truy vấn chỉ kiểm tra cột đầu tiên, hai cột đầu tiên, ba cột đầu tiên, v.v. Nếu bạn chỉ định các cột theo đúng thứ tự trong định nghĩa chỉ mục, một chỉ mục tổng hợp duy nhất có thể tăng tốc một số loại truy vấn trên cùng một bảng

Trong truy vấn trên, tôi đã sử dụng email làm thuật ngữ mệnh đề WHERE duy nhất, do đó chỉ mục tổng hợp sẽ không hữu ích vì nó thiếu cột tra cứu của chỉ mục

Có một lợi thế khác cho chỉ số tổng hợp;

select
  *
from
  master_users
where
  email = '[email protected]'

27

Ví dụ: hãy lấy một chỉ số

select
  *
from
  master_users
where
  email = '[email protected]'

28. Chỉ mục này sẽ hữu ích trong các truy vấn bên dưới

ALTER TABLE
  master_users
ADD
  INDEX index_email(email)

9

Nhưng không phải trong các truy vấn dưới đây

select
  *
from
  master_users
where
  email = '[email protected]'

0

Lập chỉ mục cho các truy vấn với toán tử OR và AND

select
  *
from
  master_users
where
  email = '[email protected]'

1

Trong trường hợp này, MySQL sẽ không thể sử dụng chỉ mục cho các truy vấn có điều kiện OR, ngay cả khi truy vấn chứa cột tra cứu và duy trì thứ tự của mệnh đề WHERE giống như chỉ mục

Do đó, bạn nên tránh các điều kiện OR như vậy và cân nhắc chia truy vấn thành hai phần, kết hợp với một

select
  *
from
  master_users
where
  email = '[email protected]'

29 (hoặc tốt hơn nữa,

select
  *
from
  master_users
where
  email = '[email protected]'

30, trong trường hợp bạn biết sẽ không có bất kỳ kết quả trùng lặp nào)

select
  *
from
  master_users
where
  email = '[email protected]'

2

Lập chỉ mục cho các truy vấn với ORDER BY và GROUP BY

hãy lấy cùng một chỉ mục

select
  *
from
  master_users
where
  email = '[email protected]'

31 và chạy truy vấn

select
  *
from
  master_users
where
  email = '[email protected]'

3

Thao tác này sẽ sử dụng chỉ mục tổng hợp cho mệnh đề WHERE và nếu bạn cho rằng điều này sẽ thu hẹp các bản ghi có trạng thái có giá trị là 1, đáng tiếc là bây giờ bạn cần thực hiện sắp xếp các bản ghi kết quả này để sắp xếp chúng theo

select
  *
from
  master_users
where
  email = '[email protected]'
  and category_id = 3

7. Điều này là do chỉ mục không sắp xếp kết quả theo

select
  *
from
  master_users
where
  email = '[email protected]'
  and category_id = 3

7 theo bất kỳ cách có ý nghĩa nào và mệnh đề ORDER BY thiếu cột tra cứu

Điều này được gọi là Sắp xếp tệp (một số bạn có thể nhận thấy điều này trong kết quả giải thích). Điều này xảy ra vì chỉ mục mà chúng tôi đã tạo không đáp ứng được mệnh đề ORDER BY

Sắp xếp tệp. Một sắp xếp xảy ra sau truy vấn; . Điều này sẽ không cần thiết nếu dữ liệu đã được sắp xếp theo chỉ mục theo cách bạn muốn

Điều này cũng áp dụng ngay cả khi bạn chỉ muốn đọc 10 hàng

select
  *
from
  master_users
where
  email = '[email protected]'

4

Bạn vẫn sẽ tìm nạp hàng nghìn bản ghi, sắp xếp chúng và chỉ sau đó, trả lại 5 bản ghi hàng đầu trong khi loại bỏ phần còn lại của các bản ghi mà bạn đã dành thời gian xử lý

select
  *
from
  master_users
where
  email = '[email protected]'

5

Truy vấn này có thể tận dụng việc sử dụng chỉ mục được đề cập vì nó đủ điều kiện cho cả mệnh đề WHERE và mệnh đề ORDER BY

Xem xét một truy vấn khác sắp xếp các hàng theo trạng thái theo thứ tự tăng dần và sau đó theo

select
  *
from
  master_users
where
  email = '[email protected]'
  and category_id = 3

7 theo thứ tự giảm dần

select
  *
from
  master_users
where
  email = '[email protected]'

6

MySQL không thể sử dụng các chỉ mục khi sắp xếp theo thứ tự hỗn hợp (cả ASC và DESC trong cùng mệnh đề ORDER BY)

Ghi chú. Điều này đã thay đổi với việc phát hành chức năng đảo ngược chỉ mục và MySQL 8. x

Bất cứ điều gì bạn thấy đối với ORDER BY cũng có thể áp dụng cho các câu lệnh GROUP BY. nếu bạn chạy truy vấn sau với chỉ mục tổng hợp trên

select
  *
from
  master_users
where
  email = '[email protected]'

31

select
  *
from
  master_users
where
  email = '[email protected]'

7

Các bản ghi đã được sắp xếp theo

select
  *
from
  master_users
where
  email = '[email protected]'

36,

select
  *
from
  master_users
where
  email = '[email protected]'
  and category_id = 3

7 và

select
  *
from
  master_users
where
  email = '[email protected]'

38. Điều này cho phép bạn nhanh chóng lọc tất cả các bản ghi với

select
  *
from
  master_users
where
  email = '[email protected]'

39. Sau khi các kết quả này được trả về, chúng cũng được sắp xếp dựa trên

select
  *
from
  master_users
where
  email = '[email protected]'
  and category_id = 3

7 do chỉ mục sắp xếp thứ tự các hàng khác với yêu cầu trong truy vấn

Còn các Truy vấn có điều kiện THAM GIA hoặc PHẠM VI thì sao?

Bạn nên có các chỉ mục trên tất cả các cột được sử dụng trong mệnh đề THAM GIA. tôi. e, các cột ở mỗi bên của mệnh đề ON của phép nối phải được lập chỉ mục

Truy vấn được coi là truy vấn Phạm vi nếu nó sử dụng bất kỳ hoặc kết hợp các toán tử sau. Toán tử

select
  *
from
  master_users
where
  email = '[email protected]'

41 ,

select
  *
from
  master_users
where
  email = '[email protected]'

42,

select
  *
from
  master_users
where
  email = '[email protected]'

43,

select
  *
from
  master_users
where
  email = '[email protected]'

44 hoặc

select
  *
from
  master_users
where
  email = '[email protected]'

45,

select
  *
from
  master_users
where
  email = '[email protected]'

46,

select
  *
from
  master_users
where
  email = '[email protected]'

47,

select
  *
from
  master_users
where
  email = '[email protected]'

48,

select
  *
from
  master_users
where
  email = '[email protected]'

49,

select
  *
from
  master_users
where
  email = '[email protected]'

60,

select
  *
from
  master_users
where
  email = '[email protected]'

61 hoặc

select
  *
from
  master_users
where
  email = '[email protected]'

62, phép so sánh LIKE (Nếu đối số của LIKE là một chuỗi hằng số không bắt đầu bằng ký tự đại diện. )

Nếu bạn đang sử dụng một chỉ mục cho các truy vấn phạm vi, hãy cố gắng đảm bảo rằng cột mà bạn đang chỉ định toán tử phạm vi được sắp xếp cuối cùng trong chỉ mục. Bạn chỉ nên thêm một trong số chúng cho mỗi bảng — điều kiện chọn lọc nhất, vì MySQL chỉ có thể xử lý một 'cột có khoảng cách' trong mỗi chỉ mục

Tôi nên lập chỉ mục những cột nào?

Bạn nên nhận ra từ tất cả những gì chúng ta đã thảo luận rằng nó phụ thuộc vào

• Những cột bạn sẽ truy vấn
• Những gì THAM GIA bạn sẽ thực hiện
• ĐẶT HÀNG/NHÓM THEO, v.v.

Bạn cũng có thể tham khảo hướng dẫn lập chỉ mục và thiết kế lược đồ MySQL này

Thưởng. Bao gồm các chỉ số

Trước khi hiểu chỉ mục bao gồm là gì, hãy tìm hiểu cách MySQL tìm nạp các hàng phù hợp cho một truy vấn

select
  *
from
  master_users
where
  email = '[email protected]'

63

Nếu bạn có MySQL với công cụ InnoDB, bạn sẽ thường sử dụng các chỉ mục như Chỉ mục cụm và chỉ mục phụ

Khi bạn xác định KHÓA CHÍNH trên bảng của mình, InnoDB sẽ sử dụng nó làm chỉ mục nhóm. Lưu trữ bảng InnoDB được tổ chức dựa trên giá trị của các cột khóa chính, để tăng tốc các truy vấn và sắp xếp liên quan đến các cột khóa chính. Tất cả các chỉ mục khác với chỉ mục nhóm được gọi là chỉ mục phụ

Bây giờ, điểm chính ở đây là trong InnoDB, mỗi bản ghi trong chỉ mục phụ chứa các cột khóa chính cho hàng, cũng như các cột được chỉ định cho chỉ mục phụ. InnoDB sử dụng giá trị khóa chính này để tìm kiếm hàng trong chỉ mục được nhóm

Vì vậy, MySQL phải phụ thuộc vào chỉ mục Khóa chính để thực hiện giai đoạn tìm nạp ngay cả khi tất cả các hàng phù hợp được xác định bằng chỉ mục (Chỉ mục phụ)

select
  *
from
  master_users
where
  email = '[email protected]'

8

Xem xét truy vấn trên và giả sử bạn đã tạo một chỉ mục trên

select
  *
from
  master_users
where
  email = '[email protected]'

64

MySQL có thể dễ dàng truy xuất các giá trị Khóa chính cho tất cả các bản ghi có giá trị chế độ là 2, tuy nhiên, để tìm nạp cột di động, MySQL vẫn cần sử dụng các giá trị khóa chính để tìm nạp dữ liệu hàng từ chỉ mục Khóa chính

Bây giờ, nếu chúng ta thêm một chỉ mục như thế này thì sao?

select
  *
from
  master_users
where
  email = '[email protected]'

9

Sử dụng chỉ mục trên, MySQL có thể dễ dàng truy xuất các giá trị Khóa chính cho tất cả các bản ghi có giá trị

select
  *
from
  master_users
where
  email = '[email protected]'

64 là 2 và để tìm nạp cột di động, MySQL không cần phụ thuộc vào chỉ mục Khóa chính để tìm nạp dữ liệu hàng. Truy vấn trên được bao phủ hoàn toàn bởi chỉ mục và do đó được gọi là chỉ mục bao phủ

Thiết kế cơ sở dữ liệu lý tưởng sử dụng chỉ mục bao phủ khi thực tế;

Các phương pháp hay nhất để lập chỉ mục cơ sở dữ liệu MySQL

• Không tạo chỉ mục trừ khi bạn biết bạn sẽ cần chúng
• Không lập chỉ mục riêng cho từng cột trong bảng
• Tránh sử dụng các chức năng ở phía bên tay trái của Người vận hành. Ví dụ

ALTER TABLE
  master_users
ADD
  INDEX index_email(email)

0

Nếu bạn sử dụng một hàm ở phía bên trái của toán tử thì MySQL sẽ không sử dụng chỉ mục ngay cả khi cột có chỉ mục trên đó. Nhưng, bạn có thể có một chức năng trong tay phải, chẳng hạn như

Những cột nào nên được lập chỉ mục SQL?

Những cột nào tốt cho việc lập chỉ mục?

Tôi có nên lập chỉ mục tất cả các cột MySQL không?