Một một trong những vấn đề mà mình cho rằng tốn các thời gian, sức lực lao động của lập trình sẵn viên đó là việc nâng cấp performance mang lại hệ thống.Sẽ không có vấn đề gì nếu hệ thống chỉ gồm số users với lượng data không thực sự lớn, tuy thế nếu ngược lại việc về tối ưu hoá những query cùng với database đang là căn bản để optimize performance mang lại hệ thống.Trong nội dung bài viết này mình sẽ giới thiệu về bài toán đánh index cho các table thông qua đó tăng performance cho các câu lệnh query.
Bạn đang xem: Cách đánh index trong mysql
Index là gì?
Index là một cấu tạo dữ liệu góp xác định lập cập các records trong table.Hiểu một cách đơn giản và dễ dàng thì nếu không có index thì SQL nên scan toàn cục table để tìm được các records bao gồm liên quan. Tài liệu càng lớn, tốc độ query đang càng chậm.
Pros và Cons khi đánh 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à đối với tất cả xử lý UPDATE tuyệt DELETE có đk WHERE.
Nhược điểm
- Khi áp dụng index thì tốc độ của phần đa xử lý ghi dữ liệu (Insert, Update, Delete) sẽ bị chậm đi.
- bởi ngoài việc thêm hay update tin tức data thì MYSQL cũng cần được update lại tin tức index của bảng tương ứng.
- vận tốc xử lý bị trễ đi cũng phần trăm thuận với số lượng index được xử dụng trong bảng.
- do thế với phần đông table hay bao gồm xử lý insert, update hoặc delete và cần tốc độ xử lý cấp tốc thì không nên được tấn công index.
- bên cạnh đó việc tấn công index cũng trở thành tốn resource của hệ thống như thêm dung tích cho CSDL.
Các đẳng cấp index
- My
SQL cung ứng 3 vẻ bên ngoài index khác nhau cho data đó là B-Tree, Hash cùng R-Tree index.
- vị R-Tree được sử dụng cho những loại tài liệu hình học không khí Spatial data với thường ít khi gặp mặt phải nên nội dung bài viết này họ sẽ tập phổ biến vào 2 nhiều loại index là B-Tree và Hash.
B-Tree index
Thông thường xuyên khi kể tới index mà không chỉ là rõ nhiều loại index thì mặc định là sẽ thực hiện 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 add INDEX id_index (column_name<, column_name…>) //Drop index DROP INDEX index_name ON table_name |
Các đặc điểm của B-Tree Index:
– dữ liệu index được tổ chức triển khai và lưu giữ trữ theo phương thức tree, có nghĩa là có root, branch, leaf.
※Cách chuẩn bị xếp không hẳn theo dạng cây search kiếm nhị phân – Binary search tree do số lá là từng node không trở nên giới hạn là 2.
– giá trị của những node được tổ chức tăng dần từ trái qua phải.
Xem thêm: Chi tiết 4 cách đổi tên url miễn phí, đơn giản dễ thao tác, hướng dẫn thay đổi tên miền cho website
– B-Tree index được sử dụng trong số biểu thức đối chiếu dạng: =, >, >=, ORDER BY
– khi truy vấn tài liệu thì CSDL sẽ không còn scan tài liệu trên toàn bộ bảng nhằm tìm dữ liệu, việc tìm kiếm trong B-Tree là 1 quy trình đệ quy, bắt đầu từ root node với tìm kiếm tới branch cùng leaf, đến khi tìm được tất cả dữ liệu – vừa lòng 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 mã Hash Function (hàm băm). Tương xứng với từng khối dữ liệu (index) sẽ ra đời 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 sáng 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 thực hiện cho toán từ search kiếm 1 khoảng tầm 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 chính vì nó quan yếu tìm kiếm được phần từ tiếp sau trong Order.
– Hash có tốc độ nhanh hơn giao diện B-Tree.
Các hình trạng index khớp ứng với Storage Engine
Việc lựa chọn index theo kiểu B-Tree giỏi Hash quanh đó yếu tố về mục đích sử dụng index thì nó còn nhờ vào vào vấn đề Storage Engine có cung cấp loại index kia hay không.
Storage Engine | Các dạng hình index được hỗ trợ |
Inno | BTREE |
My | BTREE |
MEMORY/HEAP | HASH, BTREE |
NDB | HASH, BTREE |
Cách tiế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 nhiều khi tìm kiếm với đánh index cho nó.
Nhưng tất cả một chú ý đó là nếu con số giá trị unique hay giá trị khác NULL trong column đó cực thấp so với toàn bô records của bảng thì việc đánh index lại ko có ý nghĩa sâu sắc lắm.
Sẽ hơi là kỳ lạ nếu hầu hết trường như gender xuất xắc age lại được đánh index trong cả khi được tra cứu kiếm nhiều.
Đánh index nhiều trường (B-Tree Index)
Với trường hợp tấn công index trên nhiều columns thì index chỉ có công dụng khi tìm kiếm theo đồ vật tự những trường của index.
Giả sử 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ừ cột index trong câu lệnh trên là last_name, first_name và dob.
Vậy nếu đk tìm tìm như dưới thì index sẽ được sử dụng.
1 | SELECT * FROM Customer WHERE last_name=’Peter’ & first_name=’Smith’ |
Nhưng trong những trường thích hợp sau index sẽ không còn được sử dụng:
1 2 | SELECT * FROM Customer WHERE first_name=’Smith’ and last_name=’Peter’ |
Như vậy nếu bạn đánh index cho nhiều columns thì việc đặc biệt nhất đó là tìm kiếm theo sản phẩm tự những columns được tiến công index
và đảm bảo rằng column đầu tiên được tấn công index sẽ luôn luôn nằm trong đk tìm kiếm của bạn.
Kết luận
Như vậy họ đã đi bao gồm qua các khái niệm về index, những loại index cũng tương tự cách tấn công index rồi.
Bài tiếp sau mình vẫn đi sâu hơn và bài toán phân tích performance của những câu lệnh sql giúp thấy việc bọn họ đánh index có thực sự công dụng không.
ITNavi - nền tảng gốc rễ kết nối việc làm IT SQL: Phần 1- các loại index và cách đánh index
Mô tả- Chỉ mục vào My
Sql
Hãy chu đáo ví dụ sau để tìm hiểu và đối chiếu về tốc độ khi tiến công index cho biện pháp trường
Hướng dẫn nộp bài:
Up mã nguồn lên githubDán liên kết github vào phần nộp bài
Hướng dẫn
Các bạn download cơ sở dữ liệu mẫu classicmodels trên đường link sau:
http://www.mysqltutorial.org/mysql-sample-database.aspx
Trong cơ sở dữ liệu classicmodels, bọn họ có bảng customers như sau:
Nhìn lại câu query của bọn họ mới thật mập khiếp. Không có Possible_keys nào được sử dụng, My
SQL đề nghị duyệt qua 17123 bạn dạng ghi new tìm ra mẫu ta cần (Hãy tưởng tượng 1 forum sẽ có đến hơn 500.000 phiên bản ghi).
Các các bạn thêm chỉ mục đến bảng customers như sau:
ALTER TABLE customers showroom INDEX idx_customer
Number(customer
Number);
EXPLAIN SELECT * FROM customers WHERE customer
Number = 175;
SQL dự kiến phải tìmextra : tin tức phụ, thiệt tệ giả dụ tại cột này là “using temporary” giỏi “using filesort”
Tốt hơn nhiều rồi, dạng hình TYPE = ref gồm nghĩa rằng MYSQL hiểu rõ chỉ có một hàng đúng cùng với ý ta, và bộc lộ qua cột Rows = 1, hình trạng key= idx_customer
Number được thực hiện và chiều lâu năm key_len là 4.Chỉ tìm kiếm 1 hàng tất nhiên rằng tốt hơn các so với tra cứu 17123 hàng.
Vậy thắc mắc đặt ra là, giả dụ tôi mong thêm Index cho hầu như cột mà bao gồm thể có rất nhiều hơn 1 công dụng khi query thì sao?
Bạn tất cả thể địa chỉ cửa hàng index theo cặp như bạn địa chỉ cửa hàng index đến cặp contact
First
Name và contact
Last
Name.
ALTER TABLE customers add INDEX idx_full_name(contact
First
Name, contact
Last
Name);
EXPLAIN SELECT * FROM customers WHERE contact
First
Name = ‘Jean’ or contact
First
Name = ‘King’;
Để xoá chỉ mục vào bảng, các bạn làm như sau:
ALTER TABLE customers DROP INDEX idx_full_name;
Trên đây Code
Gym đã với bạn thực hành thực tế tạo chỉ mục vào My
Sql. Hãy chụp hình ảnh màn hình và nộp bài xích thực hành của người tiêu dùng trên Code
Gym
X để thuộc nhau luyện tập nhé!
Bạn cũng có thểThích
