CFSDN nhấn mạnh vào giá trị tạo ra nguồn mở và chúng tôi cam kết xây dựng nền tảng chia sẻ tài nguyên để mọi nhân viên CNTT có thể tìm thấy thế giới tuyệt vời của bạn tại đây.

Bài viết trên blog CFSDN này giải thích cách sử dụng lập chỉ mục chính xác trong MySQL và giải thích chi tiết về các nguyên tắc lập chỉ mục. Nó được tác giả sưu tầm và biên soạn. Nếu bạn quan tâm đến bài viết này, hãy nhớ thích nó.

1. Giới thiệu.

Tại sao có một chỉ số?

Trong các hệ thống ứng dụng nói chung, tỷ lệ đọc-ghi là khoảng 10:1, các thao tác chèn và cập nhật chung hiếm khi gây ra vấn đề về hiệu suất, trong môi trường sản xuất, những gì chúng ta gặp phải nhiều nhất và cũng có khả năng xảy ra sai sót nhất là một số. những thao tác truy vấn phức tạp nên việc tối ưu hóa các câu lệnh truy vấn rõ ràng là ưu tiên hàng đầu. Nói đến việc tăng tốc truy vấn, chúng ta phải nhắc đến các chỉ mục.

Chỉ số là gì?

Chỉ mục, còn được gọi là "khóa" trong MySQL, là cấu trúc dữ liệu được công cụ lưu trữ sử dụng để nhanh chóng tìm thấy các bản ghi. Lập chỉ mục cho hiệu suất tốt.

Nó rất quan trọng, đặc biệt khi lượng dữ liệu trong bảng ngày càng lớn thì tác động của các chỉ số đến hiệu suất càng trở nên quan trọng.

Tối ưu hóa chỉ mục phải là phương pháp hiệu quả nhất để tối ưu hóa hiệu suất truy vấn. Các chỉ mục có thể dễ dàng cải thiện hiệu suất truy vấn theo mức độ lớn.

Chỉ mục tương đương với trình tự phiên âm của từ điển. Muốn tra một từ nào đó, nếu không sử dụng bảng phiên âm, bạn cần tra từng trang từ hàng trăm trang.

Bạn có hiểu lầm về chỉ mục?

Lập chỉ mục là một khía cạnh quan trọng của thiết kế và phát triển ứng dụng. Nếu có quá nhiều chỉ mục, hiệu suất ứng dụng có thể bị ảnh hưởng. Quá ít chỉ mục sẽ ảnh hưởng đến hiệu suất truy vấn. Việc tìm kiếm điểm cân bằng là rất quan trọng đối với hiệu suất ứng dụng. Một số nhà phát triển luôn thêm chỉ mục sau đó - Tôi luôn tin rằng điều này bắt nguồn từ một mô hình phát triển sai lầm. Nếu bạn biết dữ liệu của mình sẽ được sử dụng như thế nào, bạn nên thêm các chỉ mục khi cần ngay từ đầu. Các nhà phát triển thường chỉ sử dụng cơ sở dữ liệu ở cấp ứng dụng, chẳng hạn như viết câu lệnh SQL, thủ tục lưu sẵn, v.v. Họ thậm chí có thể không biết sự tồn tại của các chỉ mục hoặc nghĩ rằng họ chỉ cần yêu cầu DBA có liên quan thêm chúng sau đó. Các DBA thường không hiểu đủ rõ về luồng dữ liệu kinh doanh và việc thêm chỉ mục đòi hỏi phải giám sát một số lượng lớn các câu lệnh SQL để tìm ra vấn đề. Thời gian cần thiết cho bước này chắc chắn lớn hơn nhiều so với thời gian cần thiết để thêm chỉ mục ban đầu và một số chỉ mục có thể. bị bỏ lỡ. Tất nhiên, nhiều chỉ mục hơn không phải lúc nào cũng tốt hơn. Tôi đã từng gặp phải một vấn đề như vậy: một máy chủ MySQL nào đó cho thấy mức sử dụng đĩa luôn là 100%. Sau khi phân tích, người ta phát hiện ra rằng nhà phát triển đã thêm quá nhiều chỉ mục và sau đó xóa chúng. một số lập chỉ mục không cần thiết, mức sử dụng đĩa giảm xuống 20% ​​ngay lập tức. Có thể thấy việc thêm chỉ số cũng rất kỹ thuật.

2. Nguyên tắc lập chỉ mục.

1. Nguyên tắc lập chỉ mục.

Mục đích của việc lập chỉ mục là để cải thiện hiệu quả truy vấn, cũng giống như mục lục chúng ta sử dụng để kiểm tra sách: trước tiên hãy tìm chương, sau đó tìm phần bên dưới chương, rồi tìm số trang. Các ví dụ tương tự bao gồm: tra từ điển, kiểm tra số tàu, chuyến bay, v.v.

Bản chất là: lọc ra kết quả mong muốn cuối cùng bằng cách liên tục thu hẹp phạm vi dữ liệu bạn muốn thu được, đồng thời biến các sự kiện ngẫu nhiên thành các sự kiện tuần tự. Nói cách khác, với cơ chế lập chỉ mục này, chúng ta luôn có thể sử dụng. phương pháp tìm kiếm tương tự để khóa dữ liệu.

Điều này cũng đúng với cơ sở dữ liệu, nhưng rõ ràng là phức tạp hơn nhiều, vì nó không chỉ phải đối mặt với các truy vấn tương đương mà còn cả các truy vấn phạm vi (>, <, giữa, trong), truy vấn mờ (như), truy vấn hợp (hoặc), vân vân. Cơ sở dữ liệu nên chọn cách nào để giải quyết mọi vấn đề? Hãy nghĩ lại ví dụ về từ điển. Chúng ta có thể chia dữ liệu thành các phân đoạn và sau đó truy vấn chúng theo từng phân đoạn không? Cách đơn giản nhất là nếu có 1.000 mẩu dữ liệu, 1 đến 100 được chia thành phần đầu tiên, 101 đến 200 được chia thành phần thứ hai, 201 đến 300 được chia thành phần thứ ba... Bằng cách này, nếu bạn tìm kiếm phần dữ liệu thứ 250, bạn chỉ cần tìm phần thứ ba, thế là xong, 90% dữ liệu không hợp lệ đã bị loại bỏ ngay lập tức. Nhưng nếu là kỷ lục 10 triệu thì nên chia thành bao nhiêu phân khúc? Những học sinh có nền tảng thuật toán một chút sẽ nghĩ đến cây tìm kiếm, có độ phức tạp trung bình là lgN và có hiệu suất truy vấn tốt. Nhưng ở đây chúng ta đã bỏ qua một vấn đề quan trọng. Mô hình phức tạp mỗi lần đều dựa trên cùng một chi phí vận hành. Việc triển khai cơ sở dữ liệu phức tạp hơn, một mặt, dữ liệu được lưu trên đĩa, mặt khác, để cải thiện hiệu suất, một phần dữ liệu có thể được đọc vào bộ nhớ để tính toán mỗi lần. chi phí truy cập vào đĩa là khoảng 100.000 nhân dân tệ khi truy cập bộ nhớ, vì vậy một cây tìm kiếm đơn giản khó đáp ứng được các tình huống ứng dụng phức tạp.

2. Đĩa IO và đọc trước.

Việc truy cập vào đĩa đã được đề cập trước đó, vì vậy đây là phần giới thiệu ngắn gọn về IO của đĩa và việc đọc trước dữ liệu từ đĩa dựa vào chuyển động cơ học. Thời gian dành cho mỗi lần đọc dữ liệu có thể được chia thành ba loại: thời gian tìm kiếm, độ trễ quay. và phần thời gian truyền, thời gian tìm kiếm đề cập đến thời gian cánh tay từ di chuyển đến vị trí đã chỉ định. Thời gian cần thiết cho một bản nhạc thường nhỏ hơn 5ms đối với các đĩa thông thường; độ trễ quay là tốc độ đĩa mà chúng ta thường nghe thấy. Ví dụ, một đĩa có tốc độ 7200 vòng/phút nghĩa là nó có thể quay 7200 lần mỗi phút, nghĩa là nó có thể quay 120 lần. mỗi giây. Độ trễ là 1/120/2. = 4,17ms; thời gian truyền là thời gian đọc hoặc ghi dữ liệu vào đĩa, thường là vài phần mười mili giây, không đáng kể so với hai lần đầu tiên. Khi đó, thời gian truy cập vào đĩa, tức là thời gian cho một đĩa IO xấp xỉ bằng 5+4,17 = 9ms, nghe có vẻ khá hay, nhưng bạn phải biết rằng máy 500-MIPS (Triệu lệnh mỗi giây) có thể thực thi 5 tỷ lệnh, vì các lệnh dựa vào bản chất của điện. Nói cách khác, khoảng 4,5 triệu lệnh có thể được thực thi trong thời gian cần thiết để thực thi một IO. Cơ sở dữ liệu thường chứa hàng trăm nghìn, hàng triệu hoặc thậm chí hàng chục triệu. dữ liệu và mỗi lần mất 9 mili giây thì rõ ràng đó là một thảm họa. Hình ảnh sau đây là biểu đồ so sánh độ trễ phần cứng máy tính để bạn tham khảo:

Xem xét rằng IO đĩa là một hoạt động rất tốn kém, hệ điều hành máy tính đã thực hiện một số tối ưu hóa. Khi thực hiện IO, không chỉ dữ liệu tại địa chỉ đĩa hiện tại mà cả dữ liệu lân cận cũng được đọc vào bộ nhớ đệm, bởi vì nguyên tắc cục bộ của nó. read-ahead cho chúng ta biết rằng khi máy tính truy cập dữ liệu tại một địa chỉ thì dữ liệu liền kề cũng sẽ được truy cập nhanh chóng. Dữ liệu được IO đọc mỗi lần được gọi là một trang. Kích thước cụ thể của dữ liệu trên một trang phụ thuộc vào hệ điều hành, thường là 4k hoặc 8k, nghĩa là khi chúng ta đọc dữ liệu trong một trang, chỉ có một IO thực sự xảy ra. Lý thuyết này rất hữu ích cho việc thiết kế cấu trúc dữ liệu của chỉ mục. .

3. Cấu trúc dữ liệu chỉ mục.

Tôi đã nói về các nguyên tắc cơ bản của việc lập chỉ mục, độ phức tạp của cơ sở dữ liệu và kiến ​​thức liên quan về hệ điều hành. Mục đích là để mọi người hiểu rằng không có cấu trúc dữ liệu nào được tạo ra một cách ngẫu nhiên. Nó phải có nền tảng và cách sử dụng. Bây giờ chúng tôi tóm tắt lại, những gì chúng tôi cần cấu trúc dữ liệu này thực hiện rất đơn giản, đó là: mỗi khi chúng tôi tìm kiếm dữ liệu, chúng tôi kiểm soát số lượng IO của đĩa ở một mức độ lớn rất nhỏ, tốt nhất là một mức không đổi. kích cỡ. Sau đó, chúng tôi nghĩ xem liệu cây tìm kiếm đa đường có khả năng kiểm soát cao có thể đáp ứng nhu cầu hay không? Bằng cách này, cây b+ ra đời (cây B+ phát triển từ cây tìm kiếm nhị phân và sau đó là cây nhị phân cân bằng, cây B).

Như đã trình bày ở trên, đây là cây b+. Để định nghĩa cây b+, vui lòng tham khảo cây B+. Sau đây là một số điểm chính. Chúng tôi gọi khối màu xanh nhạt là khối đĩa. các mục (hiển thị bằng màu xanh đậm) và các con trỏ (hiển thị bằng màu vàng), ví dụ: khối đĩa 1 chứa các mục dữ liệu 17 và 35 và chứa các con trỏ P1, P2 và P3 đại diện cho các khối đĩa nhỏ hơn 17 và P2 đại diện cho các khối đĩa nhỏ hơn 17. trong khoảng từ 17 đến 35. Khối đĩa, P3 đại diện cho khối đĩa lớn hơn 35. Dữ liệu thực tồn tại trong các nút lá, cụ thể là 3, 5, 9, 10, 13, 15, 28, 29, 36, 60, 75, 79, 90, 99. Các nút không có lá không lưu trữ dữ liệu thực mà chỉ lưu trữ các mục dữ liệu hướng dẫn hướng tìm kiếm. Ví dụ: 17 và 35 không thực sự tồn tại trong bảng dữ liệu.

###B+ quá trình tìm kiếm cây.

Như trong hình, nếu bạn muốn tìm mục dữ liệu 29, trước tiên bạn sẽ tải khối đĩa 1 từ đĩa vào bộ nhớ. Lúc này, IO xảy ra. Sử dụng tìm kiếm nhị phân trong bộ nhớ để xác định rằng 29 nằm trong khoảng. 17 và 35, và khóa khối đĩa 1. Con trỏ P2, thời gian bộ nhớ không đáng kể vì rất ngắn (so với đĩa IO), thông qua từ tính Địa chỉ đĩa của con trỏ P2 của khối đĩa 1 tải khối đĩa 3 từ đĩa vào bộ nhớ. IO thứ hai xảy ra, 29 nằm trong khoảng từ 26 đến 30, khóa con trỏ P2 của khối đĩa 3 và tải khối đĩa 8 vào khối đĩa 8. bộ nhớ thông qua con trỏ. IO thứ ba xảy ra Đồng thời, một tìm kiếm nhị phân được thực hiện trong bộ nhớ để tìm 29 và tổng cộng có ba IO. Tình hình thực tế là cây b+ 3 lớp có thể biểu thị hàng triệu dữ liệu. Nếu hàng triệu tìm kiếm dữ liệu chỉ yêu cầu ba IO, thì sự cải thiện hiệu suất sẽ rất lớn. Nếu không có chỉ mục, mỗi mục dữ liệu sẽ phải có một IO. , thì cần có tổng cộng hàng triệu IO, điều này rõ ràng là rất rất tốn kém.

###b+Tính chất cây cối.

1. Trường chỉ mục phải càng nhỏ càng tốt: Qua phân tích ở trên, chúng ta biết số lượng IO phụ thuộc vào b + chiều cao của số h. Giả sử dữ liệu trong bảng dữ liệu hiện tại là N và số lượng. các mục dữ liệu trong mỗi khối đĩa là m thì có h =㏒(m+1)N, khi lượng dữ liệu N nhất định thì m càng lớn thì h càng nhỏ và m = kích thước của khối đĩa; / Kích thước của mục dữ liệu, kích thước của khối đĩa, tức là kích thước của một trang dữ liệu, là cố định. Nếu không gian mà mục dữ liệu chiếm giữ nhỏ hơn thì số lượng mục dữ liệu sẽ lớn hơn và chiều cao của mục đó sẽ lớn hơn. cây sẽ thấp hơn. Đây là lý do tại sao mỗi mục dữ liệu, tức là trường chỉ mục, phải càng nhỏ càng tốt. Ví dụ: int chiếm 4 byte, nhỏ hơn một nửa so với bigint 8 byte. Đây là lý do tại sao cây b+ yêu cầu dữ liệu thực được đặt trong các nút lá thay vì các nút bên trong. Sau khi được đặt vào các nút bên trong, các mục dữ liệu của khối đĩa sẽ giảm đáng kể, khiến chiều cao của cây tăng lên. Khi mục dữ liệu bằng 1 sẽ suy biến thành bảng tuyến tính.

2. Đặc điểm so khớp ngoài cùng bên trái của chỉ mục: Khi mục dữ liệu của cây b+ là cấu trúc dữ liệu tổng hợp như (tên, tuổi, giới tính) thì số b+ được xây dựng theo thứ tự từ trái qua phải, chẳng hạn khi ( Zhang San, 20, F) Khi lấy dữ liệu đó, cây b+ sẽ ưu tiên so sánh tên để xác định hướng tìm kiếm tiếp theo. Nếu trùng tên thì so sánh lần lượt tuổi và giới tính, cuối cùng lấy được dữ liệu nhưng khi có dữ liệu không có tên như (20, F) thì cây b+ không biết kiểm tra nút nào tiếp theo, vì khi xây dựng cây tìm kiếm Tên là yếu tố so sánh đầu tiên Bạn phải tìm kiếm dựa trên tên trước để biết nơi tìm kiếm tiếp theo. Ví dụ: khi truy xuất dữ liệu như (Zhang San, F), cây b+ có thể sử dụng tên để chỉ định hướng tìm kiếm, nhưng thiếu trường tuổi tiếp theo nên nó chỉ có thể tìm thấy tất cả dữ liệu có tên bằng Zhang San, và sau đó khớp giới tính là dữ liệu của F. Đây là một thuộc tính rất quan trọng, tức là đặc điểm khớp ngoài cùng bên trái của chỉ mục.

4. Chỉ mục cụm và chỉ mục phụ.

Trong cơ sở dữ liệu, chiều cao của cây B+ thường nằm trong khoảng từ 2 đến 4 cấp, điều đó có nghĩa là chỉ cần 2 đến 4 IO để tìm bản ghi hàng của một giá trị khóa nhất định, điều này không tệ. Bởi vì đĩa cứng cơ học thông thường hiện nay có thể thực hiện ít nhất 100 IO mỗi giây, nên 2 đến 4 IO có nghĩa là thời gian truy vấn chỉ mất 0,02 đến 0,04 giây.

Các chỉ mục cây B+ trong cơ sở dữ liệu có thể được chia thành các chỉ mục nhóm và chỉ mục phụ.

Điểm giống nhau giữa chỉ mục cụm và chỉ mục phụ là dù là chỉ mục cụm hay chỉ mục phụ thì bên trong của nó đều có dạng cây B+, nghĩa là chiều cao được cân bằng và các nút lá lưu trữ tất cả thông tin dữ liệu.

Sự khác biệt giữa chỉ mục nhóm và chỉ mục phụ là liệu nút lá có lưu trữ toàn bộ hàng thông tin hay không.

1. Chỉ mục cụm.

Công cụ lưu trữ #InnoDB đại diện cho một bảng được sắp xếp theo chỉ mục, nghĩa là dữ liệu trong bảng được lưu trữ theo thứ tự khóa chính. Chỉ mục được nhóm xây dựng cây B+ dựa trên khóa chính của mỗi bảng. Đồng thời, các nút lá lưu trữ dữ liệu bản ghi hàng của toàn bộ bảng. Tính năng này của chỉ mục được nhóm xác định rằng dữ liệu trong bảng được tổ chức theo chỉ mục cũng là một phần của chỉ mục.

Giống như cấu trúc dữ liệu cây B+, mỗi trang dữ liệu được liên kết thông qua danh sách liên kết đôi. #Nếu khóa chính không được xác định, MySQL lấy chỉ mục duy nhất đầu tiên (duy nhất) và chỉ chứa các cột không rỗng (KHÔNG NULL) làm khóa chính và InnoDB sử dụng nó làm chỉ mục được nhóm. #Nếu không có cột như vậy, InnoDB sẽ tự tạo một giá trị ID như vậy, có 6 byte và bị ẩn, biến nó thành một chỉ mục được nhóm. #Vì các trang dữ liệu thực tế chỉ có thể được sắp xếp theo cây B+ nên mỗi bảng chỉ có thể có một chỉ mục được nhóm. Trong trường hợp nào trình tối ưu hóa truy vấn ưu tiên các chỉ mục được nhóm.

Bởi vì chỉ mục được nhóm có thể tìm thấy dữ liệu trực tiếp trên các nút lá của chỉ mục cây B+. Ngoài ra, do thứ tự logic của dữ liệu được xác định nên chỉ mục được nhóm cho phép truy cập đặc biệt nhanh vào các truy vấn có giá trị theo phạm vi.

Một trong những lợi ích của chỉ mục được nhóm: tìm kiếm sắp xếp khóa chính và tìm kiếm theo phạm vi rất nhanh và dữ liệu của các nút lá là dữ liệu mà người dùng muốn truy vấn. Ví dụ: nếu người dùng cần tìm kiếm một bảng và truy vấn thông tin của 10 người dùng cuối cùng, vì chỉ mục cây B+ là danh sách liên kết đôi, người dùng có thể nhanh chóng tìm thấy trang dữ liệu cuối cùng và truy xuất 10 bản ghi.

Lợi ích thứ hai của chỉ mục được nhóm: truy vấn phạm vi, nghĩa là nếu bạn muốn tìm dữ liệu trong một phạm vi nhất định của khóa chính, bạn có thể lấy phạm vi trang thông qua nút trung gian phía trên của nút lá, sau đó đọc trực tiếp trang dữ liệu.

2. Chỉ số phụ trợ.

Ngoại trừ chỉ mục được nhóm, các chỉ mục khác trong bảng đều là chỉ mục phụ (Chỉ mục phụ, còn gọi là chỉ mục không được nhóm). Điểm khác biệt so với chỉ mục được nhóm là các nút lá của chỉ mục phụ không chứa tất cả dữ liệu của hàng). hồ sơ.

Ngoài giá trị khóa, nút lá còn chứa dấu trang trong hàng chỉ mục. Dấu trang này được sử dụng để báo cho công cụ lưu trữ InnoDB biết nơi tìm dữ liệu hàng tương ứng với chỉ mục.

Vì công cụ lưu trữ InnoDB là một bảng được tổ chức theo chỉ mục nên dấu trang của chỉ mục phụ của công cụ lưu trữ InnoDB là khóa chỉ mục được nhóm của hàng dữ liệu tương ứng. Như hình dưới đây.

Sự tồn tại của các chỉ mục phụ không ảnh hưởng đến việc tổ chức dữ liệu trong chỉ mục được phân cụm nên có thể có nhiều chỉ mục phụ trên mỗi bảng nhưng chỉ có thể có một chỉ mục được phân cụm. Khi tìm kiếm dữ liệu thông qua chỉ mục phụ, công cụ lưu trữ InnoDB duyệt qua chỉ mục phụ và lấy khóa chính thông qua con trỏ cấp lá, sau đó sử dụng chỉ mục khóa chính để tìm bản ghi hàng hoàn chỉnh.

Ví dụ: nếu bạn đang tìm kiếm dữ liệu trong cây chỉ mục phụ có chiều cao là 3, bạn cần duyệt qua cây chỉ mục phụ ba lần để tìm khóa chính được chỉ định. Nếu chiều cao của cây chỉ mục được phân cụm cũng là 3 thì bạn. cũng cần phải duyệt qua cây chỉ mục được nhóm. Sau 3 lần tìm kiếm, trang chứa một hàng dữ liệu hoàn chỉnh cuối cùng cũng được tìm thấy, do đó cần có tổng cộng 6 truy cập IO logic để có được trang dữ liệu cuối cùng.

5. Quản lý chỉ mục MySQL.

1. Chức năng.

1. Chức năng của chỉ mục là tăng tốc độ tìm kiếm.

2. Khóa chính, duy nhất và duy nhất chung trong mysql cũng là các chỉ mục. Ngoài việc tăng tốc tìm kiếm, các chỉ mục này còn có chức năng ràng buộc.

2. Các chỉ mục thường dùng trong MySQL.

Chỉ mục thông thường INDEX: tăng tốc độ tìm kiếm.

Chỉ mục duy nhất: -Chỉ mục khóa chính PRIMARY KEY: tăng tốc độ tìm kiếm + ràng buộc (không trống, không thể lặp lại) -Chỉ mục duy nhất UNIQUE: tăng tốc tìm kiếm + ràng buộc (không thể lặp lại).

Chỉ số chung: -PRIMARY KEY (id, name): chỉ mục khóa chính chung - UNIQUE (id, name): chỉ mục duy nhất chung - INDEX (id, name): chỉ mục chung chung.

3. Hai loại chỉ mục chính là hàm băm và btree.

#Chúng ta có thể chỉ định loại chỉ mục khi tạo chỉ mục trên. Có hai loại chỉ mục loại băm: truy vấn đơn là nhanh và truy vấn phạm vi là chỉ mục loại btree: b+tree, càng nhiều lớp, lượng dữ liệu càng tăng theo cấp số nhân. (Chúng tôi chỉ sử dụng nó vì innodb hỗ trợ nó theo mặc định).

#Các công cụ lưu trữ khác nhau hỗ trợ các loại chỉ mục khác nhau. InnoDB hỗ trợ các giao dịch, khóa cấp hàng, cây B, Toàn văn và các chỉ mục khác, nhưng không hỗ trợ chỉ mục Hash MyISAM không hỗ trợ giao dịch, hỗ trợ khóa cấp bảng và hỗ trợ; B- Các chỉ mục như cây và Toàn văn không hỗ trợ các chỉ mục Hash; Bộ nhớ không hỗ trợ các giao dịch và hỗ trợ khóa cấp bảng. Nó hỗ trợ các chỉ mục B-tree, Hash và các chỉ mục khác nhưng không hỗ trợ các chỉ mục Toàn văn bản; Hỗ trợ giao dịch, hỗ trợ khóa cấp hàng, hỗ trợ chỉ mục Hash, không hỗ trợ cây B, Toàn văn và các chỉ mục khác; Lưu trữ không hỗ trợ giao dịch, hỗ trợ khóa cấp bảng và không hỗ trợ cây B, Hash, Full -văn bản và các chỉ mục khác;

4. Cú pháp tạo/xóa chỉ mục.

6. Chỉ số kiểm tra.

1. Chuẩn bị.

Mẹo: Thời gian tạo bảng phụ thuộc vào hiệu năng của máy nên các bạn hãy kiên nhẫn nhé! .

2. Kiểm tra tốc độ truy vấn không có chỉ mục.

3. Khi một lượng lớn dữ liệu đã tồn tại trong bảng, việc tạo chỉ mục cho một phân đoạn trường nhất định sẽ rất chậm.

4. Sau khi chỉ mục được thiết lập, khi trường này được sử dụng làm điều kiện truy vấn, tốc độ truy vấn sẽ được cải thiện đáng kể.

Ghi chú:

1. Đầu tiên, MySQL vào bảng chỉ mục và tìm kiếm nhanh bản ghi có ID bằng 333333333 theo nguyên tắc tìm kiếm cây b+. IO được giảm đi rất nhiều nên tốc độ được cải thiện đáng kể.

2. Chúng ta có thể vào thư mục dữ liệu của mysql để tìm bảng và thấy rằng nó chiếm rất nhiều dung lượng ổ cứng.

3. Xin lưu ý, như hình dưới đây.

5. Tóm tắt.

#1. Bạn phải tạo chỉ mục cho trường của điều kiện tìm kiếm. Ví dụ: chọn * từ s1 trong đó id = 333;

#2. Khi đã có một lượng lớn dữ liệu trong bảng, việc tạo chỉ mục sẽ rất chậm và chiếm dung lượng ổ cứng. Sau khi tạo chỉ mục, tốc độ truy vấn sẽ được tăng tốc. id); sẽ quét tất cả dữ liệu trong bảng, sau đó Sử dụng ID làm mục dữ liệu, tạo cấu trúc chỉ mục và lưu trữ nó trong bảng trên đĩa cứng. Sau khi xây dựng xong, truy vấn sẽ rất nhanh.

#3. Cần lưu ý rằng chỉ mục của bảng innodb sẽ được lưu trữ trong tệp s1.ibd, trong khi chỉ mục của bảng myisam sẽ có tệp chỉ mục riêng table1.MYI.

Tệp chỉ mục và tệp dữ liệu MySAM được tách riêng và tệp chỉ mục chỉ lưu địa chỉ của bản ghi dữ liệu. Trong innodb, bản thân tệp dữ liệu bảng là một cấu trúc chỉ mục được sắp xếp theo B+Tree (BTree là Balance True). Trường dữ liệu nút lá của cây này lưu các bản ghi dữ liệu hoàn chỉnh. Key của chỉ mục này là khóa chính của bảng dữ liệu nên bản thân file dữ liệu bảng innodb chính là chỉ mục chính. Vì các file dữ liệu của inndob được tổng hợp theo khóa chính nên innodb yêu cầu bảng phải có khóa chính (Myisam không cần xác định rõ ràng, hệ thống mysql sẽ tự động chọn một cột có thể duy nhất). xác định bản ghi dữ liệu là khóa chính. Nếu nó không tồn tại, Đối với loại cột này, MySQL sẽ tự động tạo một trường ẩn làm khóa chính cho bảng innodb. Độ dài của trường này là 6 byte và loại dài. số nguyên.

7. Sử dụng các chỉ mục một cách chính xác.

1. Chỉ số bị trượt.

Điều đó không có nghĩa là nếu chúng ta tạo chỉ mục thì chắc chắn sẽ tăng tốc truy vấn. Nếu muốn sử dụng chỉ mục để đạt được hiệu quả cải thiện tốc độ truy vấn như mong đợi, chúng ta phải tuân theo các câu hỏi sau khi thêm chỉ mục.

1. Vấn đề về phạm vi hoặc điều kiện không rõ ràng. Các ký hiệu hoặc từ khóa này xuất hiện trong điều kiện: >, >=, <, <=, !=, between...and..., like, .

Dấu lớn hơn, dấu nhỏ hơn.

Không bằng! = .

giữa ...và... 。

giống .

2. Cố gắng chọn các cột có độ phân biệt cao làm chỉ mục. Công thức phân biệt là count(distinct col)/count(*), biểu thị tỷ lệ các trường không được lặp lại. Tỷ lệ càng lớn thì chúng tôi càng quét ít bản ghi. và sự phân biệt của các khóa duy nhất Nó là 1, và một số trường trạng thái và giới tính có thể có sự phân biệt bằng 0 khi đối mặt với dữ liệu lớn. Sau đó, ai đó có thể hỏi, tỷ lệ này có giá trị thực nghiệm nào không? Các trường hợp sử dụng khác nhau khiến cho giá trị này khó xác định. Nói chung, chúng tôi yêu cầu các trường cần nối phải trên 0,1, tức là trung bình 10 bản ghi sẽ được quét trên mỗi trường.

Đầu tiên hãy xóa tất cả các chỉ mục trong bảng và chúng ta hãy tập trung vào vấn đề phân biệt.

Chúng ta viết một thủ tục lưu trữ để thêm các bản ghi theo đợt vào bảng s1. Các giá trị của trường tên đều là duduo, nghĩa là độ phân biệt của trường tên rất thấp (điều này cũng đúng với trường giới tính, chúng ta sẽ thực hiện tương tự). giải quyết sau).

Nhớ lại cấu trúc của cây b+. Tốc độ truy vấn tỷ lệ nghịch với chiều cao của cây. Nếu bạn muốn kiểm soát chiều cao của cây thật thấp, bạn cần đảm bảo rằng các mục dữ liệu trong một lớp nhất định được sắp xếp theo thứ tự từ. từ trái qua phải, từ nhỏ đến lớn, sắp xếp theo thứ tự left 1

Đối với các trường có độ phân biệt thấp, không thể tìm thấy mối quan hệ kích thước vì các giá trị đều bằng nhau. Chắc chắn rằng nếu bạn muốn sử dụng cây b+ để lưu trữ những dữ liệu có giá trị bằng nhau này, bạn chỉ có thể tăng chiều cao của trường. cây. Độ phân biệt của trường càng thấp thì chiều cao của cây càng cao. Trong trường hợp cực đoan, giá trị của các trường chỉ mục đều giống nhau nên cây b+ gần như trở thành một cây gậy. Đây là một trường hợp cực đoan trong ví dụ này, trong đó tất cả các giá trị trong trường tên là 'duoduo'.

#Bây giờ chúng ta đi đến kết luận: khi lập chỉ mục các trường có độ phân biệt thấp, chiều cao của cây chỉ mục sẽ rất cao. Tuy nhiên, điều này sẽ có tác động cụ thể như thế nào? ? ?

#1: Nếu điều kiện là name='xxxx' thì có thể xác định ngay là 'xxxx' không có trong cây chỉ mục (vì tất cả các giá trị trong cây đều là 'duoduo') nên tốc độ truy vấn là rất nhanh.

#2: Nếu điều kiện là name='duoduo', khi truy vấn, chúng ta không bao giờ có thể có được phạm vi rõ ràng từ một vị trí nhất định trong cây, chúng ta chỉ có thể tìm kiếm down, down và down. . . Con số này không khác nhiều so với số lượng IO cho một lần quét toàn bộ bảng nên tốc độ rất chậm.

phân tích.

3. = và in có thể không theo thứ tự, chẳng hạn như a = 1 và b = 2 và c = 3. Chỉ mục (a, b, c) có thể được thiết lập theo bất kỳ thứ tự nào. Trình tối ưu hóa truy vấn của MySQL sẽ giúp bạn tối ưu hóa nó. một hình thức mà chỉ mục có thể nhận ra.

4. Các cột chỉ mục không thể tham gia tính toán. Giữ các cột "sạch". Ví dụ: nếu from_unixtime(create_time) = '2014-05-29', thì không thể sử dụng chỉ mục. Lý do rất đơn giản. ​được lưu trữ trong cây b+ là các giá trị trường trong bảng dữ liệu, nhưng khi truy xuất, bạn cần áp dụng hàm cho tất cả các phần tử để so sánh, điều này rõ ràng là quá tốn kém. Vì vậy, câu lệnh phải được viết là create_time = unix_timestamp('2014-05-29').

5、 và/hoặc 。

#1. Điều kiện logic của và hoặc. Điều kiện 1 và Điều kiện 2: Tất cả các điều kiện phải đúng thì mới được coi là đúng. một điều kiện đúng thì kết quả cuối cùng sẽ đúng.

#2. Nguyên lý làm việc Điều kiện của và: a = 10 và b = 'xxx' và c > 3 và d =4 Chỉ số: Tạo chỉ số chung (d,a,b,c) Nguyên lý làm việc: Cho nhiều and liên tiếp: mysql Theo đối với chỉ mục chung, tìm trường chỉ mục có độ phân biệt cao từ trái sang phải (để có thể khóa nhanh phạm vi nhỏ) nhằm tăng tốc độ truy vấn, tức là theo thứ tự d->a->b-> c.

#3. Nguyên lý làm việc Điều kiện của or: a = 10 hoặc b = 'xxx' hoặc c > 3 hoặc d =4 Index: Tạo chỉ số chung (d,a,b,c) Nguyên tắc làm việc: Cho nhiều or: mysql It sẽ được đánh giá từ trái sang phải theo thứ tự các điều kiện, tức là a->b->c->d.

Khi điều kiện bên trái đúng nhưng độ phân biệt của trường chỉ mục thấp (tên, tăng tốc truy vấn).

6. Nguyên tắc khớp tiền tố ngoài cùng bên trái là một nguyên tắc rất quan trọng đối với các chỉ mục kết hợp, mysql sẽ luôn khớp về bên phải cho đến khi gặp một truy vấn phạm vi (>, <, between, like) và dừng khớp (có nghĩa là phạm vi đó lớn). , và tốc độ chỉ số cũng chậm), chẳng hạn như a = 1 và b = 2 và c > 3 và d = 4 Nếu tạo chỉ mục theo thứ tự (a, b, c, d) thì chỉ mục của d sẽ không được sử dụng. Nếu tạo chỉ mục theo thứ tự (a, b, d, c) thì có thể sử dụng The. có thể sử dụng thứ tự a, b, d theo ý muốn.

7. Các tình huống khác.

Những cân nhắc khác.

- Tránh sử dụng select * - count(1) hoặc count(column) thay vì count(*) - Cố gắng sử dụng char thay vì varchar khi tạo bảng - Thứ tự trường của bảng trước tiên là các trường có độ dài cố định - Thay thế chỉ mục kết hợp nhiều chỉ mục một cột (thường được sử dụng Khi truy vấn với nhiều điều kiện) - Cố gắng sử dụng các chỉ mục ngắn - Sử dụng phép nối (THAM GIA) thay vì truy vấn phụ (Truy vấn phụ) - Khi nối các bảng, hãy chú ý đến thực tế là các loại điều kiện phải nhất quán - Giá trị băm chỉ mục (có ít lần lặp lại) không phù hợp để lập chỉ mục, ví dụ: giới tính không phù hợp.

8. Chỉ số chung và chỉ số bao phủ.

1. Chỉ số chung.

Chỉ mục chung đề cập đến việc kết hợp nhiều cột trên bảng để tạo chỉ mục. Phương pháp tạo một chỉ mục chung cũng giống như phương pháp tạo một chỉ mục duy nhất, điểm khác biệt duy nhất là có nhiều cột chỉ mục như sau.

Vậy khi nào bạn cần sử dụng chỉ mục chung? Trước khi thảo luận về vấn đề này, chúng ta hãy nhìn vào kết quả nội bộ của chỉ số chung. Về bản chất, chỉ mục chung là cây B+. Sự khác biệt là số lượng giá trị khóa trong chỉ mục chung không phải là 1 mà là >= 2. Tiếp theo, hãy thảo luận về chỉ số chung gồm hai cột số nguyên. Giả sử rằng hai giá trị khóa lần lượt được đặt tên là a và b, như trong hình.

Bạn có thể thấy rằng điều này không khác gì cây B+ khóa đơn mà chúng ta đã thấy trước đây. Các giá trị khóa đều được sắp xếp và tất cả dữ liệu có thể được đọc một cách logic và tuần tự thông qua các nút lá trong ví dụ trên. 1,1), (1,2), (2,1), (2,4), (3,1), (3,2), dữ liệu được lưu trữ theo thứ tự (a, b).

Do đó, đối với truy vấn chọn * từ bảng trong đó a=xxx và b=xxx, rõ ràng có thể sử dụng chỉ mục chung (a, b). Đối với truy vấn, hãy chọn * từ bảng trong đó a=xxx cho một cột duy nhất a, nó cũng có thể được sử dụng (a,b) của chỉ mục này.

Nhưng đối với truy vấn chọn * từ bảng có b=xxx, bạn không thể sử dụng chỉ mục (a, b). Trên thực tế, không khó để tìm ra lý do tại sao b trên nút lá là 1, 2, 1. , 4, 1, 2 rõ ràng là không được sắp xếp nên truy vấn trên cột b không thể sử dụng chỉ mục (a, b).

Lợi ích thứ hai của chỉ mục chung là khi khóa đầu tiên giống nhau thì khóa thứ hai đã được sắp xếp. Ví dụ: trong nhiều trường hợp, ứng dụng cần truy vấn tình huống mua sắm của người dùng và sắp xếp theo thời gian, cuối cùng là lấy. ra ba bản ghi mua hàng cuối cùng Tại thời điểm này, việc sử dụng chỉ mục chung có thể giúp chúng ta tránh được một thao tác sắp xếp nữa, vì bản thân chỉ mục đã được sắp xếp trên các nút lá, như sau.

2. Chỉ số trang bìa.

Công cụ lưu trữ InnoDB hỗ trợ chỉ mục bao phủ (chỉ mục bao phủ hoặc phạm vi bao phủ chỉ mục), nghĩa là các bản ghi truy vấn có thể được lấy từ chỉ mục phụ mà không cần truy vấn các bản ghi trong chỉ mục được nhóm.

Một lợi ích của việc sử dụng chỉ mục bao phủ là chỉ mục phụ không chứa tất cả thông tin của toàn bộ bản ghi hàng, do đó kích thước của nó nhỏ hơn nhiều so với chỉ mục được nhóm, do đó có thể giảm một số lượng lớn thao tác IO.

Lưu ý: Công nghệ chỉ mục bao phủ lần đầu tiên được hoàn thiện và triển khai trong Plugin InnoDB, có nghĩa là đối với các phiên bản InnoDB dưới 1.0 hoặc các phiên bản cơ sở dữ liệu MySQL dưới 5.0, công cụ lưu trữ InnoDB không hỗ trợ tính năng chỉ mục bao phủ.

Đối với chỉ mục phụ của công cụ lưu trữ InnoDB, vì nó chứa thông tin khóa chính nên dữ liệu được lưu trữ trong các nút lá của nó là (khóa chính1, key2,..., key1, key2,...). Ví dụ.

Một lợi ích khác của việc bao gồm các chỉ số là giải quyết một số vấn đề thống kê nhất định. Dựa trên bảng buy_log được tạo trong bản tóm tắt trước đó, kế hoạch truy vấn như sau.

Công cụ lưu trữ innodb không chọn truy vấn chỉ mục được nhóm để thống kê. Do bảng buy_log có chỉ mục phụ và chỉ số phụ nhỏ hơn nhiều so với chỉ mục được nhóm nên việc chọn chỉ mục phụ có thể giảm bớt các thao tác IO nên trình tối ưu hóa chọn chỉ mục phụ userid như trên.

Đối với các chỉ mục chung có dạng (a, b), thông thường không thể chọn được cái gọi là điều kiện truy vấn trong b. Nhưng nếu đó là một hoạt động thống kê và là một chỉ mục bao trùm thì trình tối ưu hóa sẽ vẫn chọn sử dụng chỉ mục như sau.

9. Tạo tác tối ưu hóa truy vấn-giải thích.

Tôi tin rằng mọi người đều quen thuộc với lệnh giải thích. Để biết cách sử dụng cụ thể và ý nghĩa của trường, vui lòng tham khảo trang web chính thức. Cần nhấn mạnh ở đây rằng các hàng là chỉ báo cốt lõi. Hầu hết các câu lệnh có hàng nhỏ phải được thực thi nhanh chóng (có. trường hợp ngoại lệ sẽ được thảo luận dưới đây). Vì vậy, các câu lệnh tối ưu hóa về cơ bản là tối ưu hóa các hàng.

Kế hoạch thực hiện: Hãy để mysql ước tính hoạt động thực hiện (nói chung là chính xác) tất cả < chỉ mục < phạm vi < chỉ mục_merge < ref_or_null < ref < eq_ref < system/const id,email Chậm: chọn * từ thông tin người dùng3 trong đó tên = 'alex' giải thích chọn * từ thông tin người dùng trong đó name='alex' loại: TẤT CẢ (quét toàn bộ bảng) chọn * từ giới hạn userinfo3 1; Nhanh: chọn * từ userinfo3 trong đó email='alex' loại: const (được lập chỉ mục).

Văn bản tham khảo: http://www.zzvips.com/article/99872.html.

10. Các bước cơ bản để tối ưu hóa truy vấn chậm.

0. Chạy nó trước để xem nó có thực sự chậm hay không. Hãy chú ý đến việc thiết lập SQL_NO_CACHE 1. Khi có điều kiện truy vấn bảng đơn, hãy khóa bảng ghi trả về tối thiểu. Ý nghĩa của câu này là áp dụng WHERE của câu lệnh truy vấn vào bảng có số lượng bản ghi được trả về nhỏ nhất, bắt đầu từ bảng và truy vấn từng trường của một bảng riêng biệt để xem trường nào có mức độ khác biệt cao nhất. 2. Giải thích để xem kế hoạch thực hiện, có Phù hợp với mong đợi ở phần 1 hay không (bắt đầu truy vấn từ bảng có ít bản ghi bị khóa hơn) 3. Câu lệnh SQL ở dạng thứ tự theo giới hạn cho phép truy vấn bảng đã sắp xếp trước 4. Hiểu được kịch bản sử dụng của phía doanh nghiệp 5. Khi thêm chỉ mục, hãy tham khảo các nguyên tắc chính của việc xây dựng chỉ mục 6. Nếu kết quả quan sát được không đáp ứng mong đợi, hãy tiếp tục phân tích từ 0.

11. Quản lý nhật ký chậm.

Nhật ký chậm - Thời gian thực hiện > 10 - Chỉ mục bị thiếu - Đường dẫn tệp nhật ký Cấu hình: - Bộ nhớ hiển thị các biến như '%query%'; --defaults-file='E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\my-default.ini' nội dung my.conf: Slow_query_log = ON Slow_query_log_file = D:/... . Lưu ý: Sau khi sửa đổi tệp cấu hình, bạn cần khởi động lại dịch vụ.

Quản lý nhật ký MySQL.

====================================================== ====== 。

Nhật ký lỗi: ghi lại các lỗi khởi động, tắt và chạy máy chủ MySQL: còn gọi là nhật ký binlog, ghi lại các hoạt động trong cơ sở dữ liệu ngoài SELECT dưới dạng tệp nhị phân. vượt quá Nhật ký chuyển tiếp hoạt động tại thời điểm đã chỉ định: Cơ sở dữ liệu dự phòng sao chép nhật ký nhị phân của cơ sở dữ liệu chính vào nhật ký chuyển tiếp của chính nó, từ đó phát lại nhật ký chung cục bộ: Tài khoản nào được kiểm tra, trong khoảng thời gian nào và sự kiện nào được thực hiện? Nhật ký giao dịch hoặc nhật ký làm lại: ghi lại thông tin liên quan đến giao dịch Innodb như thời gian thực hiện giao dịch, điểm kiểm tra, v.v. ============= ===== ==========================================.

1、bin-log。

1. Kích hoạt.

2. Tạm dừng.

3. Xem.

Xem tất cả:

Byte:

4. Cắt bớt nhật ký bin (tạo tệp nhật ký bin mới).

a. Khởi động lại máy chủ mysql.

b. # mysql -uroot -p123 -e 'xóa nhật ký' 。

5. Xóa tệp bin-log.

2. Nhật ký truy vấn.

Cho phép ghi nhật ký truy vấn chung.

3. Nhật ký truy vấn chậm.

Cho phép ghi nhật ký truy vấn chậm.

MySQL 5.6

Kiểm tra nhật ký truy vấn chậm.

Bài kiểm tra

Tóm tắt.

Trên đây là toàn bộ nội dung bài viết mong rằng nội dung bài viết có giá trị tham khảo nhất định cho quá trình học tập, làm việc của mọi người. Nếu có thắc mắc gì có thể để lại tin nhắn để trao đổi.

Liên kết gốc: https://www.cnblogs.com/ManyQian/p/9076247.html.

Cuối cùng, bài viết này về cách sử dụng lập chỉ mục chính xác trong Mysql và giải thích chi tiết về nguyên tắc lập chỉ mục kết thúc ở đây. Nếu bạn muốn biết thêm về cách sử dụng lập chỉ mục chính xác trong Mysql và giải thích chi tiết về nguyên tắc lập chỉ mục, vui lòng tìm kiếm. cho các bài viết của CFSDN hoặc tiếp tục duyệt các bài viết liên quan, tôi hy vọng bạn sẽ ủng hộ blog của tôi trong tương lai! .