What is the implementation principle of index in MySQL?

MySQL index implementation principle is what, I believe that many inexperienced people are helpless about this, this article summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

Principle of index realization

MySQL supports many storage engines, and various storage engines support different indexes, so MySQL database supports multiple index types, such as BTree index, B+Tree index, hash index, full-text index, etc.

1. Hash index:

Only memory storage engine supports hash index, hash index uses the value of index column to calculate the hashCode of the value, and then stores the physical location of the row data where the value is located in the corresponding position of hashCode. Because of the hash algorithm, the access speed is very fast, but a value can only correspond to one hashCode, and it is the distribution mode of hash. Therefore, hash index does not support range lookup and sorting functions.

2. Full-text index:

FULLTEXT (full-text) index, can only be used for MyISAM and InnoDB, for large data, generating full-text index is very time-consuming and space-consuming. For large objects of text, or large CHAR type data, if you use a normal index, it is feasible to match the first few characters of the text, but if you want to match a few words in the middle of the text, you must use LIKE %word% to match, which takes a long time to process, and the response time will be greatly increased. In this case, you can use the FULLTEXT index. When generating the FULLTEXT index, it will generate a list of words for the text. Index at index and according to the list of words. FULLTEXT can be created at the time of table creation, or added as needed with ALTER or CREATE INDEX:

//Add FULLTEXT index CTREATE TABLE my_table when creating table id INT(10) PRIMARY KEY, name VARCHAR(10) NOT NULL, my_text TEXT, FULLTEXT(my_text))ENGINE=MyISAM DEFAULT CHARSET=utf8;//After creating the table, add the FULLTEXT index as needed ALTER TABLE my_table ADD FULLTEXT INDEX ft_index(column_name);

Full-text indexed queries also have their own special syntax and cannot use the fuzzy query syntax of LIKE % query string %

SELECT * FROM table_name MATCH(ft_index) AGAINST ('query string');

Note:

* For larger datasets, adding data to a table without a FULLTEXT index and then adding the FULLTEXT index is faster than adding data to a table that already has a FULLTEXT index.

* The full-text index included with MySQL before version 5.6 can only be used with MyISAM storage engine. If it is other data engine, full-text index will not take effect. 5.6 After the release, InnoDB storage engine began to support full-text indexing

* In MySQL, full-text indexing detachments are useful in English, but are currently not supported in Chinese. 5.7 After the release, support for Chinese was started by using the ngram plug-in.

* In MySQL, the expected results cannot be retrieved if the retrieved string is too short, the retrieved string length is at least 4 bytes, and if the retrieved characters include stop words, the stop words are ignored.

3. BTree index and B+Tree index

BTree index

BTree is a balanced search multi-branch tree, let the tree degree 2d (d>1), height h, then BTree to satisfy the following conditions:

Each leaf node has the same height, equal to h;

Each non-leaf node consists of n-1 keys and n pointer points, where d