Loại chỉ mục trong MySQL
Bất kể khi nào bạn thay đổi cấu trúc bảng dữ liệu, bạn đều cần Add Index back, giống như khi bạn thay đổi nội dung sổ sách, bạn cần phải làm lại lục mục. Vì vậy hãy cân nhắc, nếu Cơ sở dữ liệu của bạn sử dụng INSERT hay UPDATE nhiều hơn là CHỌN thì Index chỉ làm chậm thêm mà thôi Show
4. Index same as tabs. Chúng tôi làm cho nó nhanh hơn để bạn có thể tìm thấy những dữ liệu bạn cần phù hợp với một số tiêu chí nhất định. Để tạo chỉ mục tốt, bạn cần phải suy nghĩ về cách bạn sẽ truy cập dữ liệu như thế nào. Khi bạn có thông tin này, bạn sẽ có thể tạo chỉ mục và nó sẽ tăng tốc độ truy vấn của bạn ngay lập tức3. Các loại index trong mysqlỞ bài viết trước, các tác giả đã nói cụ thể về cú pháp giả sử dụng chỉ mục nên trong bài viết này, tôi xin nói thêm về các loại chỉ mục trong mysql MySQL cung cấp 3 kiểu index khác nhau cho dữ liệu đó là B-Tree, Hash và R-Tree index. Việc hiểu rõ về các loại chỉ mục này sẽ giúp chúng ta tạo ra các loại chỉ mục trong bảng một cách hiệu quả hơn. Do R-Tree được sử dụng cho các loại dữ liệu không gian dữ liệu không gian và ít khi gặp phải nên bài viết này chúng tôi sẽ tập hợp chung để so sánh 2 loại chỉ mục là B-Tree và Hash
Chỉ mục B-Tree có các đặc điểm sau
Chỉ số băm có các đặc điểm sau
=> Lựa chọn chỉ mục theo kiểu B-Tree hoặc Hash ngoài yếu tố về mục đích sử dụng chỉ mục thì nó còn phụ thuộc vào việc Storage Engine có hỗ trợ loại chỉ mục đó hay không. Ví dụ MyISAM, InnoDB hay Archive chỉ hỗ trợ B-Tree, trong khi MyISAM lại hỗ trợ cho cả 2 4. Cách sử dụng chỉ mục
Chỉ mục duy nhất được sử dụng không chỉ để tăng hiệu suất mà còn cho mục đích toàn dữ liệu. Một chỉ mục duy nhất không cho phép bất kỳ bản sao giá trị nào được thêm vào trong bảng
5 Trong trường hợp không chỉ định tên cột được đánh chỉ mục trong bảng, mysql sẽ mặc định đánh chỉ mục trên khóa chính hoặc các ràng buộc mang tính duy nhất trong cơ sở dữ liệu bảng
Một mục có thể bị xóa bằng cách sử dụng lệnh DROP INDEX trong SQL. Bạn nên cẩn thận khi xóa một mục duy nhất, bởi vì khi đó hiệu suất có thể chậm hơn hoặc không được cải thiện Cú pháp cơ bản của lệnh DROP INDEX ________số 85. Một số lưu ý khi sử dụng chỉ mụcMặc dù các mục đích chỉ nhằm mục đích nâng cao hiệu suất của Cơ sở dữ liệu, nhưng đôi khi, bạn nên tránh sử dụng chúng. Dưới đây là một số điểm bạn cần xem xét để quyết định xem có nên sử dụng chỉ mục hay không Nói chung, chỉ mục là một danh sách các bản ghi theo thứ tự bảng chữ cái có tham chiếu đến các trang mà chúng được đề cập. Trong MySQL, một chỉ mục là một cấu trúc dữ liệu được sử dụng để tìm nhanh các hàng. Chỉ mục còn được gọi là khóa và những khóa đó rất quan trọng để có hiệu suất tốt – khi dữ liệu ngày càng lớn hơn, nhu cầu sử dụng chỉ mục đúng cách có thể ngày càng trở nên quan trọng hơn. Sử dụng chỉ mục là một trong những cách hiệu quả nhất để cải thiện hiệu suất truy vấn – nếu sử dụng chỉ mục đúng cách, hiệu suất truy vấn có thể tăng lên hàng chục hoặc thậm chí hàng trăm lầnHôm nay, chúng tôi sẽ cố gắng giải thích những lợi ích và hạn chế cơ bản của việc sử dụng chỉ mục trong MySQL. Hãy nhớ rằng chỉ riêng các chỉ mục của MySQL đã xứng đáng với cả một cuốn sách, vì vậy bài đăng này sẽ không bao gồm tất cả mọi thứ, nhưng nó sẽ là một điểm khởi đầu tốt. Đối với những người quan tâm đến cách các chỉ mục hoạt động ở cấp độ sâu hơn, hãy đọc cuốn sách Relational Database Index Design and the Optimizers của Tapio Lahdenmäki và Michael Leach sẽ cung cấp thêm thông tin chi tiết. Lợi ích của việc sử dụng chỉ mụcCó một vài lợi ích chính của việc sử dụng chỉ mục trong MySQL và những lợi ích này như sau.
Tương tự, có nhiều loại chỉ mục
Bây giờ, chúng ta sẽ cố gắng tìm hiểu cả lợi ích và hạn chế của việc sử dụng chỉ mục trong MySQL. Chúng ta sẽ bắt đầu với ưu điểm có lẽ được thảo luận thường xuyên nhất – tăng tốc các truy vấn phù hợp với mệnh đề WHERE . Tăng tốc truy vấn khớp với mệnh đề WHEREChỉ mục thường được sử dụng để tăng tốc truy vấn tìm kiếm phù hợp với mệnh đề WHERE. Lý do tại sao một chỉ mục làm cho các hoạt động tìm kiếm như vậy nhanh hơn khá đơn giản – các truy vấn sử dụng một chỉ mục tránh quét toàn bộ bảng. Để tăng tốc các truy vấn phù hợp với mệnh đề WHERE , bạn có thể sử dụng câu lệnh EXPLAIN trong MySQL. Câu lệnh EXPLAIN SELECT sẽ cung cấp cho bạn một số thông tin chi tiết về cách trình tối ưu hóa truy vấn MySQL thực hiện truy vấn – nó cũng có thể cho bạn biết liệu truy vấn được đề cập có sử dụng chỉ mục hay không . Hãy xem giải thích truy vấn sau đây.
Truy vấn trên không sử dụng chỉ mục. Tuy nhiên, nếu chúng ta thêm một chỉ mục trên “ field_1”, thì chỉ mục đó sẽ được sử dụng thành công. 0Cột possible_keys mô tả các chỉ mục có thể mà MySQL có thể chọn, cột khóa mô tả chỉ mục thực sự được chọn và key_len column describes the length of the chosen key. Trong trường hợp này, MySQL sẽ thực hiện tra cứu các giá trị trong chỉ mục và trả về bất kỳ hàng nào chứa giá trị đã chỉ định – kết quả là truy vấn sẽ nhanh hơn. Mặc dù các chỉ mục giúp một số truy vấn nhanh hơn, nhưng có một số điều bạn cần lưu ý nếu muốn các chỉ mục trợ giúp các truy vấn của mình
Để giải quyết vấn đề này, hãy để riêng cột nằm sau mệnh đề WHERE – đơn giản hóa truy vấn của bạn càng nhiều càng tốt và tách biệt các cột;
Tất nhiên, việc tăng tốc các truy vấn khớp với mệnh đề WHERE cũng có thể được thực hiện theo các cách khác (ví dụ: phân vùng), nhưng đối với . Tuy nhiên, những gì chúng tôi có thể quan tâm là các loại chỉ mục khác nhau, vì vậy chúng tôi sẽ xem xét vấn đề đó ngay bây giờ Loại bỏ các giá trị trùng lặp trong một cột – Chỉ mục ĐỘC ĐÁOMục đích của chỉ mục UNIQUE UNIQUE trong MySQL là để thực thi tính duy nhất của các giá trị trong một cột. Để sử dụng một UNIQUE chỉ mục chạy một CREATE UNIQUE INDE truy vấn X. 2Đó là tất cả những gì cần thiết để thêm một chỉ mục duy nhất vào một bảng. Bây giờ, khi bạn cố gắng thêm một giá trị trùng lặp vào bảng, MySQL sẽ quay lại với lỗi sau 3Chỉ mục FULLTEXTA FULLTEXT index là một chỉ mục như vậy được áp dụng cho các cột sử dụng khả năng tìm kiếm toàn văn. Loại chỉ mục này có nhiều khả năng độc đáo bao gồm từ dừng và chế độ tìm kiếm. Danh sách từ dừng InnoDB có 36 từ trong khi danh sách từ dừng MyISAM có 143. Trong InnoDB, các từ dừng được lấy từ bảng được đặt trong biến innodb_ft_user_stopword_table, nếu không, nếu biến này không được đặt, chúng sẽ được lấy từ innodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_tableinnodb_ft_server_stopword_table variable. If neither of those two variables are set, InnoDB uses the built-in list. To see the default InnoDB stopword list, query the INNODB_FT_DEFAULT_STOPWORD . Trong MyISAM, các từ dừng được lấy từ storage/myisam/ft_static. tệp c . Biến ft_stopword_file cho phép thay đổi danh sách từ dừng mặc định. Các từ dừng sẽ bị vô hiệu nếu biến này được đặt thành một chuỗi trống, nhưng hãy nhớ rằng nếu biến này xác định một tệp, thì tệp đã xác định sẽ không được phân tích cú pháp để nhận xét – MyISAM sẽ coi tất cả các từ tìm thấy trong tệp là từ dừng. Các FULLTEXT chỉ mục cũng nổi tiếng với chế độ tìm kiếm độc đáo của nó.
Truy vấn sử dụng chỉ mục FULLTEXT trông giống như vậy. 4Hãy nhớ rằng các chỉ mục FULLTEXT thường hữu ích cho MATCH() CHỐNG() operations – not for WHERE operations meaning that if a WHERE clause would be used, the usefulness of using different index types would not be eliminated. Điều đáng nói là FULLTEXT các chỉ mục có độ dài ký tự tối thiểu. Trong InnoDB, chỉ có thể thực hiện tìm kiếm FULLTEXT khi truy vấn tìm kiếm bao gồm tối thiểu ba ký tự – giới hạn này được tăng lên bốn ký tự trong MyISAM . GIẢM GIÁCA DECENDING index là một chỉ mục mà InnoDB lưu trữ các mục nhập theo thứ tự giảm dần – trình tối ưu hóa truy vấn sẽ sử dụng một chỉ mục như vậy khi một . Một chỉ mục như vậy có thể được thêm vào một cột bằng cách chạy một truy vấn như bên dưới. 5Chỉ số tăng dần cũng có thể được thêm vào một cột – chỉ cần thay thế DESC bằng ASC. KHÓA CHÍNHA Khóa CHÍNH đóng vai trò là mã định danh duy nhất cho mỗi hàng trong bảng. Một cột có Khóa CHÍNH phải chứa các giá trị duy nhất – không có NULL giá trị . Nếu một giá trị trùng lặp được thêm vào một cột có Khóa CHÍNH , thì MySQL sẽ phản hồi với lỗi #1062. 6Nếu giá trị NULL được thêm vào cột, MySQL sẽ phản hồi với lỗi #1048. 7Các chỉ mục chính đôi khi còn được gọi là các chỉ mục nhóm (chúng tôi sẽ thảo luận về chúng sau) Bạn cũng có thể tạo chỉ mục trên nhiều cột cùng một lúc – những chỉ mục như vậy được gọi là chỉ mục nhiều cột Chỉ mục nhiều cộtLập chỉ mục trên nhiều cột thường bị hiểu sai – đôi khi các nhà phát triển và DBA lập chỉ mục cho tất cả các cột một cách riêng biệt hoặc lập chỉ mục cho chúng theo thứ tự sai. Để thực hiện các truy vấn sử dụng các chỉ mục nhiều cột hiệu quả nhất có thể, hãy nhớ rằng thứ tự các cột trong các chỉ mục sử dụng nhiều hơn một cột là một trong những nguyên nhân gây nhầm lẫn phổ biến nhất trong không gian này – vì không có “đường này hay đường kia”. . Mặc dù điều này có vẻ khá rõ ràng, nhưng hãy nhớ rằng thứ tự cột rất quan trọng khi xử lý các chỉ mục nhiều cột – hãy chọn thứ tự cột sao cho càng chọn lọc càng tốt cho các truy vấn sẽ chạy thường xuyên nhất Để đo độ chọn lọc cho các cột cụ thể, hãy lấy tỷ lệ số lượng giá trị được lập chỉ mục riêng biệt trên tổng số hàng trong bảng – cột có độ chọn lọc cao hơn phải là cột đầu tiên Đôi khi, bạn cũng cần lập chỉ mục cho các cột ký tự rất dài và trong trường hợp đó, bạn thường có thể tiết kiệm thời gian và tài nguyên bằng cách lập chỉ mục cho một vài ký tự đầu tiên – tiền tố – thay vì toàn bộ giá trị Chỉ mục tiền tốCác chỉ mục tiền tố có thể hữu ích khi các cột chứa các giá trị chuỗi rất dài, điều đó có nghĩa là việc thêm một chỉ mục trên toàn bộ cột sẽ tiêu tốn rất nhiều dung lượng đĩa. MySQL giúp giải quyết vấn đề này bằng cách cho phép bạn chỉ lập chỉ mục tiền tố của giá trị, từ đó làm cho kích thước chỉ mục nhỏ hơn. Hãy xem 8Truy vấn trên sẽ tạo chỉ mục tiền tố trên cột demo chỉ lập chỉ mục 10 ký tự đầu tiên của giá trị. Bạn cũng có thể thêm chỉ mục tiền tố vào bảng hiện có 00Vì vậy, ví dụ: nếu bạn muốn lập chỉ mục 5 ký tự đầu tiên của demo_column trên demo_table, bạn có thể chạy truy vấn sau. 01Bạn nên chọn một tiền tố đủ dài để tạo tính chọn lọc, nhưng cũng đủ ngắn để tạo khoảng trống. Tuy nhiên, điều này có thể nói dễ hơn làm – bạn cần thử nghiệm và tìm ra giải pháp phù hợp với mình Bao gồm các chỉ sốChỉ mục bao phủ “bao phủ” tất cả các trường bắt buộc để thực hiện một truy vấn. Nói cách khác, khi tất cả các trường trong một truy vấn được bao phủ bởi một chỉ mục, một chỉ mục bao phủ được sử dụng. Ví dụ cho một truy vấn như vậy 02Một chỉ số bao gồm có thể trông như thế này 03Nếu bạn muốn đảm bảo rằng một truy vấn sử dụng chỉ mục bao hàm, hãy đưa ra câu lệnh GIẢI THÍCH về truy vấn đó, sau đó xem qua . Ví dụ: nếu bảng của bạn có chỉ mục nhiều cột trên id và tiêu đề . 04Hãy nhớ rằng một chỉ mục bao gồm phải lưu trữ các giá trị từ các cột mà nó bao gồm. Điều đó có nghĩa là MySQL chỉ có thể sử dụng các chỉ mục B-Tree để bao gồm các truy vấn vì các loại chỉ mục khác không lưu trữ các giá trị này Cụm, chỉ mục phụ và chỉ số CardinalityKhi các chỉ mục được thảo luận, bạn cũng có thể nghe các thuật ngữ được nhóm lại, chỉ mục phụ và chỉ số cardinality. Nói một cách đơn giản, các chỉ mục được nhóm là một cách tiếp cận để lưu trữ dữ liệu và tất cả các chỉ mục không phải là chỉ mục được nhóm đều là các chỉ mục phụ. Mặt khác, cardinality của chỉ mục là số lượng giá trị duy nhất trong một chỉ mục Chỉ mục nhóm tăng tốc độ truy vấn vì các giá trị gần nhau cũng được lưu trữ gần nhau trên đĩa, nhưng đó cũng là lý do tại sao bạn chỉ có thể có một chỉ mục nhóm trong một bảng Chỉ mục phụ là bất kỳ chỉ mục nào không phải là chỉ mục chính. Một chỉ mục như vậy có thể có các bản sao Hạn chế của việc sử dụng chỉ mụcViệc sử dụng các chỉ mục chắc chắn có những mặt tích cực, nhưng chúng ta không được quên rằng các chỉ mục cũng có thể là một trong những nguyên nhân hàng đầu gây ra sự cố trong MySQL. Một số nhược điểm của việc sử dụng các chỉ mục như sau
Người dùng thiếu kinh nghiệm có thể nghĩ rằng truy vấn này làm cho cột id tự động tăng lên, sau đó thêm chỉ mục vào cột và làm cho cột không . Tuy nhiên, đây không phải là những gì đang xảy ra ở đây. Trong trường hợp này, cùng một cột có ba chỉ mục trên đó. một INDEX thông thường và vì MySQL thực hiện cả PRIMARY KEY và UNIQUE constraints with indexes, that adds two more indexes on the same column! Phần kết luậnTóm lại, các chỉ mục trong MySQL có vị trí riêng của chúng – các chỉ mục có thể được sử dụng trong vô số tình huống, nhưng mỗi tình huống sử dụng đó đều có nhược điểm riêng cần được xem xét trong . Để sử dụng tốt các chỉ mục, hãy lập hồ sơ các truy vấn của bạn, xem bạn có những tùy chọn nào khi nói đến chỉ mục, biết những lợi ích và bất lợi của chúng, quyết định những chỉ mục nào bạn cần dựa trên yêu cầu của bạn và sau khi bạn lập chỉ mục các cột, hãy đảm bảo rằng các chỉ mục của bạn được Loại chỉ mục là gì?Cấu trúc chỉ mục được phân loại là nhóm hoặc không nhóm . Các chỉ mục nhóm là các chỉ mục có thứ tự các hàng trong các trang dữ liệu tương ứng với thứ tự các hàng trong chỉ mục. Thứ tự này là lý do tại sao chỉ có một chỉ mục được nhóm có thể tồn tại trong bất kỳ bảng nào, trong khi đó, nhiều chỉ mục không được nhóm có thể tồn tại trong bảng.
Loại chỉ mục trong cơ sở dữ liệu là gì?Chỉ mục cơ sở dữ liệu là cấu trúc dữ liệu bổ sung được tạo bên trên dữ liệu trong bảng . Bạn chỉ định một chỉ mục trên một bảng và một cột hoặc tập hợp các cột. Điều này tạo ra một cấu trúc tìm kiếm dữ liệu bổ sung được liên kết với bảng đó và tập hợp các cột.
Làm cách nào để thay đổi loại chỉ mục trong MySQL?ALTER lệnh để thêm và loại bỏ INDEX
. ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list) − Điều này tạo ra một chỉ mục FULLTEXT đặc biệt được sử dụng cho mục đích tìm kiếm văn bản. ALTER TABLE tbl_name ADD INDEX index_name (column_list) − This adds an ordinary index in which any value may appear more than once. ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list) − This creates a special FULLTEXT index that is used for text-searching purposes.
Các loại lập chỉ mục chính là gì?Các loại lập chỉ mục . Lập chỉ mục thư mục và cơ sở dữ liệu lập chỉ mục phả hệ lập chỉ mục địa lý lập chỉ mục sách lập chỉ mục pháp lý Lập chỉ mục định kỳ và báo lập chỉ mục hình ảnh cổng chủ đề |