Shulou(Shulou.com)06/01 Report--

This article is mainly to give you a brief introduction to a detailed understanding of the indexes and transactions of mysql. You can look up the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope that this article can bring you some practical help to learn more about the indexes and transactions of mysql.

First, what is the index for?

In many cases, when your application is slow to perform SQL queries, you should consider whether it is possible to build an index.

Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in the B-tree. It's just that indexes of spatial column types use R-trees, and MEMORY tables also support hash indexes.

An index is a sorted list in which the value of the index and the physical address of the row of data containing this value are stored. When the data is very large, the index can greatly speed up the query. This is because after using the index, you do not have to scan the whole table to locate the data of a row, but first find the corresponding physical address of the data through the index table and then access the corresponding data.

Second, the advantages and disadvantages of index

Advantages: it can be retrieved quickly, reducing the number of Imax O and speeding up the retrieval speed; grouping and sorting according to the index can speed up the grouping and sorting

Disadvantages: the index itself is also a table, so it takes up storage space, generally speaking, the space occupied by the index table is 1.5 times that of the data table; the maintenance and creation of the index table requires time cost, which increases as the amount of data increases; building an index reduces the efficiency of data table modification operations (delete, add, modify), because you need to modify the index table as well as the data table

III. Classification of indexes

Common index types are: primary key index, unique index, general index, full-text index, combined index.

1. Primary key index: that is, the primary index, which is indexed according to the primary key pk_clolum (length). Repetition and null values are not allowed.

ALTER TABLE 'table_name' ADD PRIMARY KEY (' id')

2. Unique index: the value of the column used to build the index must be unique, allowing null values

ALTER TABLE 'table_name' ADD UNIQUE (' email')

3. Normal index: an index built with ordinary columns in a table without any restrictions

ALTER TABLE 'table_name' ADD INDEX index_name (' description')

4. Full-text indexing: an index built with columns of large text objects (explained in the next section)

ALTER TABLE 'table_name' ADD FULLTEXT (' content')

5. Composite index: an index built with a combination of multiple columns. Null values are not allowed in these columns.

ALTER TABLE 'table_name' ADD INDEX index_name (' col1','col2','col3')

Following the principle of "leftmost prefix", the columns most commonly used as retrieval or sorting are placed on the leftmost, decreasing in turn. The combined index is equivalent to the establishment of three indexes of col1,col1col2,col1col2col3, while col2 or col3 cannot use the index.

When using a combined index, the key of the index may be too large because the length of the column name is too long, resulting in low efficiency. If allowed, only the first few characters of col1 and col2 can be taken as the index.

ALTER TABLE 'table_name' ADD INDEX index_name (col1 (4), col2 (3))

Indicates that the first four characters of col1 and the first three characters of col2 are used as indexes

Fourth, the implementation principle of the index

MySQL supports many storage engines, and each storage engine supports different indexes, so MySQL database supports a variety of index types, such as BTree index, B+Tree index, hash index, full-text index and so on.

1. Hash index:

Only the memory (memory) storage engine supports the hash index. The hash index refers to the value of the index column to calculate the hashCode of the value, and then holds the physical location of the row data of the value in the corresponding location of the hashCode. Because the hashing algorithm is used, the access speed is very fast, but a value can only correspond to one hashCode, and it is the distribution of hash, so the hash index does not support the function of range search and sorting.

2. Full-text index:

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

/ / add the FULLTEXT index CTREATE TABLE my_table (id INT (10) PRIMARY KEY,name VARCHAR (10) NOT NULL,my_text text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,FULLTEXT (my_text)) when creating the table; / / add the FULLTEXT index ALTER my_table ADD FULLTEXT ft_index (my_text); CREATE INDEX ft_index ON my_table (my_text) when the table is created

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

The full-text index that comes with MySQL can only be used by the MyISAM storage engine. If it is another data engine, then the full-text index will not take effect.

In MySQL, full-text indexing detachment is useful in English, but does not support Chinese at present.

In MySQL, if the retrieved string is too short, the expected result cannot be retrieved, and the retrieved string is at least 4 bytes long. In addition, if the retrieved character includes the stop word, the stop word will be ignored.

3. BTree index and B+Tree index BTree index

BTree is a balanced search multifork tree. If the degree of the tree is d (d > 1) and the height is h, then BTree must satisfy the following conditions:

The height of each leaf node is the same, equal to h

Each non-leaf node consists of 1 key and n pointer point, where d

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.