Một trong những vấn đề mà mình nghĩ là tốn nhiều thời gian, công sức của con người của lập trình sẵn viên sẽ là việc nâng cao performance cho hệ thống.Sẽ không có vấn đề gì nếu khối hệ thống chỉ bao gồm số users với lượng data không thật lớn, dẫu vậy nếu trái lại việc tối ưu hoá những query với database đang là chính yếu để optimize performance đến hệ thống.Trong nội dung bài viết này mình sẽ reviews về việc đánh index cho các table qua đó tăng performance cho các câu lệnh query.

Bạn đang xem: Cách đánh index

Index là gì?

Index là một kết cấu dữ liệu góp xác định hối hả các records trong table.

Hiểu một cách dễ dàng và đơn giản thì nếu không tồn tại index thì SQL đề xuất scan toàn cục table để tìm được các records tất cả liên quan. Dữ liệu càng lớn, tốc độ query đang càng chậm.

Pros và Cons khi tấn công index.

Ưu điểm

Ưu điểm của index là tăng vận tốc tìm tìm records theo câu lệnh WHERE.

Không chỉ giới hạn trong câu lệnh SELECT mà với tất cả xử lý UPDATE tuyệt DELETE có đk WHERE.

Nhược điểm

- Khi áp dụng index thì vận tốc của phần nhiều xử lý ghi dữ liệu (Insert, Update, Delete) sẽ bị chậm đi.

- do ngoài câu hỏi thêm tuyệt update thông tin data thì MYSQL cũng cần được update lại thông tin index của bảng tương ứng.

- tốc độ xử lý bị chậm đi cũng xác suất thuận với số lượng index được xử dụng trong bảng.

- do thế với hầu như table hay gồm xử lý insert, update hoặc delete và cần tốc độ xử lý cấp tốc thì tránh việc được đánh index.

- trong khi việc tiến công index cũng sẽ tốn resource của vps như thêm dung tích cho CSDL.

Các loại index

- My
SQL cung ứng 3 kiểu dáng index không giống nhau cho data sẽ là B-Tree, Hash cùng R-Tree index.

- vì chưng R-Tree được sử dụng cho những loại dữ liệu hình học không gian Spatial data với thường ít khi chạm mặt phải nên bài viết này bọn họ sẽ tập thông thường vào 2 nhiều loại index là B-Tree cùng Hash.

B-Tree index

Thông thường xuyên khi kể đến index mà không chỉ là rõ một số loại index thì default là sẽ sử dụng B-Tree index.

Cú pháp:

1 2 3 4 5 6

// Create index CREATE INDEX id_index ON table_name (column_name<, column_name…>) USING BTREE; // Or ALTER TABLE table_name showroom INDEX id_index (column_name<, column_name…>) //Drop index DROP INDEX index_name ON table_name

Các điểm sáng của B-Tree Index:

– tài liệu index được tổ chức triển khai và lưu lại trữ theo dạng tree, tức là có root, branch, leaf.

※Cách sắp đến xếp không hẳn theo dạng cây kiếm tìm kiếm nhị phân – Binary search tree vị số lá là mỗi node không xẩy ra giới hạn là 2.

– giá chỉ trị của những node được tổ chức tăng dần từ trái qua phải.

– B-Tree index được sử dụng trong các biểu thức đối chiếu dạng: =, >, >=, ORDER BY

– lúc truy vấn dữ liệu thì CSDL sẽ không scan tài liệu trên toàn bộ bảng nhằm tìm dữ liệu, việc đào bới tìm kiếm kiếm trong B-Tree là 1 quy trình đệ quy, bắt đầu từ root node cùng tìm tìm tới branch với leaf, cho khi kiếm được tất cả tài liệu – thỏa mãn nhu cầu với điều kiện truy vấn thì mới dùng lại.

Hash Index

Hash index dựa trên giải thuật Hash Function (hàm băm). Tương ứng với từng khối tài liệu (index) sẽ sinh ra một bucket key(giá trị băm) nhằm phân biệt.

Cú pháp:

1 2 3 4

// Create index CREATE INDEX id_index ON table_name (column_name<, column_name…>) USING HASH; // Or ALTER TABLE table_name địa chỉ INDEX id_index (column_name<, column_name…>) USING HASH;

Các điểm lưu ý của Hash Index:

– khác với B-Tree, thì Hash index nên làm sử dụng trong số biểu thức toán tử là = cùng . Không áp dụng cho toán từ tìm kiếm 1 khoảng giá trị như > tuyệt

– ko thể tối ưu hóa toán tử ORDER BY bằng việc thực hiện Hash index cũng chính vì nó bắt buộc tìm tìm kiếm được phần từ tiếp theo sau trong Order.

– Hash có vận tốc nhanh hơn hình dáng B-Tree.

Các hình dáng index khớp ứng với Storage Engine

Việc lựa chọn index theo phong cách B-Tree giỏi Hash không tính yếu tố về mục đích sử dụng index thì nó còn nhờ vào vào bài toán Storage Engine có cung ứng loại index kia hay không.

Storage Engine	Các hình dáng index được hỗ trợ
Inno DB	BTREE
My ISAM	BTREE
MEMORY/HEAP	HASH, BTREE
NDB	HASH, BTREE

Cách tấn công index

Đánh index một trường

Đây là bí quyết khá thường thì khi bọn họ lựa lựa chọn 1 column được sử dụng thỉnh thoảng tìm kiếm và đánh index đến nó.

Xem thêm: Htaccess cgi access cgi access, lập trình web với python (3)

Nhưng có một để ý đó là nếu số lượng giá trị chất lượng hay cực hiếm khác NULL vào column đó quá thấp so với tổng thể records của bảng thì bài toán đánh index lại không có ý nghĩa sâu sắc lắm.

Sẽ tương đối là kỳ quái nếu hầu như trường như gender xuất xắc age lại được tấn công index trong cả khi được tìm kiếm nhiều.

Đánh index các trường (B-Tree Index)

Với trường hợp tiến công index trên nhiều columns thì index chỉ có kết quả khi search theo thứ tự những trường của index.

Giả sử bao gồm table Customer:

1 2 3 4 5

CREATE TABLE Customer( last_name varchar(50) not null, first_name varchar(50) not null, dob date not null, key(last_name, first_name, dob) );

Thứ từ bỏ cột index vào câu lệnh trên là last_name, first_name với dob.

Vậy nếu điều kiện tìm kiếm như dưới thì index sẽ được sử dụng.

1	SELECT * FROM Customer WHERE last_name=’Peter’ và first_name=’Smith’

Nhưng trong số những trường phù hợp sau index sẽ không còn được sử dụng:

1 2	SELECT * FROM Customer WHERE first_name=’Smith’ và last_name=’Peter’

Như vậy nếu bạn đánh index cho nhiều columns thì việc đặc biệt nhất kia là tìm kiếm theo sản phẩm công nghệ tự các columns được đánh index

và bảo đảm an toàn rằng column trước tiên được tấn công index sẽ luôn luôn nằm trong điều kiện tìm kiếm của bạn.

Kết luận

Như vậy bọn họ đã đi bao hàm qua những khái niệm về index, các loại index cũng tương tự cách tấn công index rồi.

Bài tiếp theo mình đã đi sâu rộng và vấn đề phân tích performance của các câu lệnh sql giúp xem việc bọn họ đánh index có thực sự tác dụng không.

ITNavi - nền tảng kết nối vấn đề làm IT SQL: Phần 1- các loại index và biện pháp đánh index

Chúng ta thường hiểu được trong SQL đánh index giúp tăng tốc độ truy vấn, vậy mọi người đã biết nguyên lý của đánh index là gì ? vì sao nó lại giúp tăng tốc độ truy vấn cùng nhược điểm của chính nó là gì không ? bây giờ mình xin phép được chém gió về chủ thể này.

1. Việc thực tế

Chắc hẳn các bạn làm web hầu như biết về I18n rồi đúng không ạ ? mang sử chúng ta có 2 file biên dịch là en.yml cùng vi.yml. Mỗi dòng mặt en.yml tương xứng với vi.yml với ngược lại. Ví dụ:

en.yml: table: "table"vi.yml: table: "cái bàn"có nghĩa là mỗi từ dịch bởi tiếng anh thì bắt buộc dịch bằng Tiếng Việt, vậy số chiếc trong tệp tin en.yml phải bằng vi.yml. Tuy vậy một ngày rất đẹp trời bạn nhận biết en.yml bao gồm 1000 cái mà vi.yml chỉ bao gồm 999 dòng, tức là bạn đã sa thải ko dịch 1 từ nào đó sang tiếng Việt. Bạn cần tìm ra dòng còn thiếu trong vi.yml nhằm cập nhật, vậy bạn làm cách nào để tìm ra chiếc đó nhanh nhất có thể ?

Nếu dò từ trên xuống bên dưới giữa 2 tệp tin với nhau, xui xẻo chúng ta có thể phải dò tới 999 lần, rất nhiều lần tầm nã vấn. Tuy nhiên có cách để dò tối đa 10 lần là dĩ nhiên chắn các bạn sẽ tìm ra chiếc đó. Cách đó như sau:Lần 1 bạn dò ở mẫu thứ 500 và so sánh 2 file, ví như khớp trường đoản cú khóa chứng tỏ 500 dòng đầu tiên không bị miss value (dòng còn thiếu nằm tại nửa còn lại), còn còn nếu như không khớp (bị lệch 1 dòng) thì minh chứng 500 dòng đầu tiên có chứa cái bị miss. Vậy là chỉ 1 lần test chúng ta đã "cưa đôi" được phạm vi tra cứu kiếm, cùng bạn thường xuyên "cưa đôi" thêm về tối đa 10 lần nữa các bạn sẽ tìm ra mẫu còn thiếu. Vì sao lại 10 lần ? cũng chính vì 2^10 =1024 >1000.

Cơ chế đánh index cũng dựa vào phương thức cưa đôi như vậy.

2. Cách thức đánh index

Giả sử bạn có một bảng 1000 record đựng tên người: "Phong", "Dung", "Hải", ... Bạn cần tìm ra người mang tên là "Nam". Vậy hiệ tượng đánh index sẽ chuyển động ntn trong trường vừa lòng này.

Đầu tiên khi chúng ta thực hiện tấn công index đến trường :name, index sẽ được đánh tự 0 tăng cao tới 999 ( gồm 1000 record ), đánh theo trang bị tự alphabet. Ví dụ: "An" sẽ có index = 0, "Anh" sẽ có index = 1, "Ánh" sẽ sở hữu được index =2,... Thời gian này bạn cũng có thể hiểu rằng, vô hình dung chung, các record của mình đã được bố trí lại theo lẻ tẻ tự alphabet tăng dần.

Khi bạn triển khai câu tầm nã vấn Select ... Where name = "Nam" lúc này nó vẫn dùng phương pháp cưa đôi mà mình nói sinh hoạt trên, thứ nhất lấy :name sinh sống index lắp thêm 500 ra đối chiếu với "Nam", tuy vậy ko phải đối chiếu bằng mà là đối chiếu xem lớn hơn hay nhỏ bé hơn, lấy ví dụ record index 500 có :name là "Hoàng" thì khi so sánh sẽ nhận đc tác dụng "Hoàng" 1000 ^2 = 1000000. Trong khi phương pháp truy vấn thường thì phải đối chiếu tối nhiều 999999 lần.

3. Đánh index cho các trường ( B tree)

Đánh index cho các trường hay có cách gọi khác là phương pháp B tree.

Giả sử bạn có 1 triệu record tên người, với một người tại một tỉnh/thành tốt nhất định. Bạn phải tìm ra bạn ở thức giấc "Ninh Bình" mang tên là "Nam". Chính sách index hôm nay sẽ vận động như gắng nào.

Đầu tiên bạn sẽ cần đánh index cho cả trường :province với cả trường :name, hôm nay 1 triệu record sẽ đc nhóm lại theo province trước, những province này sẽ tiến hành sắp xếp theo thứ tự alphabet, lấy ví dụ "An Giang" gồm index là 0, "Bà Rịa Vũng Tàu" tất cả index là 1, "Bắc Giang" gồm index là 2, ... Trong team tỉnh/thành, các trường :name lại được đánh index 1 lần nữa. Ví dụ: "An" : 0, "Anh": 1, "Ánh": 2, ... Vấn đề đánh index trong index như vậy này tạo cho các nhánh, nên tín đồ ta gọi là B tree.

Khi thực hiện câu tróc nã vấn Select ...where province = "Ninh Bình" and name = "Nam". Đầu tiên sẽ lọc ra những người dân ở thức giấc "Ninh Bình". Nguyên tắc như sau, từng tỉnh lôi ra record thứ nhất và đem so sánh với "Ninh Bình" theo phương thức đánh index, vn có 64 tỉnh giấc nên chỉ cần tối nhiều 6 lần đối chiếu là vẫn tìm được người nào thuộc tỉnh "Ninh Bình", từ bỏ người này sẽ lấy ra toàn bộ nhóm đó, kế tiếp từ đội người này lại dùng phương pháp index thanh lọc ra fan tên "Nam" một lượt nữa.

Hình minh họa về B tree

Câu hỏi được đặt ra là bọn họ nên đánh index mang đến trường nào trước để buổi tối ưu truy vấn vấn, :province tốt :name. Câu trả lời là trường nào không nhiều record hơn vậy thì nên tấn công trước. Ở trong bài toán này, thì :province gồm 64 tỉnh, còn :name thì có hàng nghìn ngàn vẻ bên ngoài đặt tên, vị vậy đội theo ngôi trường :province trước là phù hợp hơn, vì chưng khi thanh lọc ra trường :proivince thì bạn đã lọc được 63/64 tỉnh thành ko cần thân mật tới, tương đương với 1 lượng record rất lớn. Còn nếu bạn đánh theo ngôi trường :name, thì hầu hết record nào bạn cũng nên đụng tới ít nhất 1 lần.

Khi đánh index theo đồ vật tự làm sao thì khi truy vấn bạn phải vận dụng đúng trang bị tự kia nhé, còn nếu như không thì tấn công index sẽ không tồn tại tác dụng, ví dụ các bạn đánh index cho :province trước rồi bắt đầu tới :name thì khi sử dụng câu SQL "Select ... Where name = "Nam" và province = "Ninh Binh"" thì đánh index là ăn hại .

4. điểm yếu của phương pháp đánh index.

Đánh index góp tăng vận tốc truy vấn, tuy vậy sau những lần insert hoặc update hoặc delete (thay đổi db), chúng ta cần tiến hành lại bài toán đánh index vì vậy tốc độ khi triển khai 3 bài toán này sẽ giảm đi. Ví dụ lúc đầu "An" bao gồm index là 0, "Anh" có index là 1, "Ánh" tất cả index là 2, lúc xóa "An" đi, thì từ bây giờ "Anh" sẽ sở hữu được index là 0, "Ánh" sẽ sở hữu được index là 1, các trường vùng sau cũng phải đổi khác tương tự. Vậy điểm yếu kém của phương pháp đánh index đó là giảm vận tốc Insert, update, delete. Nói là sút nhưng thực tế khi update hoặc delete record bọn họ cũng bắt buộc truy vấn cho tới record đó buộc phải tăng dòng này giảm kia, nhưng việc giảm tự 999999 lần so sánh xuống còn trăng tròn lần so sánh thì quá xứng danh để tiến công đổi.

5. Cơ hội nào thì tránh việc dùng tấn công index ?

Khi quá không nhiều record, cụ thể là dưới 10, hôm nay đánh index ko vạc huy công dụng vì so sánh bằng dù cho là 10 lần triển khai vẫn cấp tốc hơn so với so sánh kiểm tra xem lớn hơn hay nhỏ tuổi hơn triển khai 3,4 lần.