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

The following content mainly brings you the relevant knowledge of mysql index. the knowledge mentioned is slightly different from books, which are summarized by professional and technical personnel in the process of contact with users, and have a certain value of experience sharing, hoping to bring help to the majority of readers.

Let's first look at the concept of MYSQL indexes:

An index is a special file that contains reference pointers to all records in the data table. More generally, the database index is like the catalog in front of a book, which can speed up the query speed of the database. After we have the corresponding index, the database will directly look for options that meet the criteria in the index. The index is divided into clustered index and non-clustered index. The clustered index is in the order according to the physical location of the data, but the non-clustered index is different. The clustered index can improve the speed of multi-row retrieval, while the non-clustered index can retrieve single row very fast.

Mysql has two main structures: B + tree and hash.

Hash indexes are rarely used in mysql, which organizes indexes of data in the form of hash, so when looking up a record, it is very fast. Because it is a hash structure, each key corresponds to only one value, and is distributed in a hash way, so it does not support functions such as range lookup and sorting.

B + tree: the B + tree is the most frequently used index data structure in mysql. The data structure is organized in the form of a balanced tree. Because it is a tree structure, it is more suitable to handle sorting, range search and other functions. Compared with hash index, B+ tree is not as fast as hash index in finding a single index, but it is more popular among users because it is more suitable for sorting and other operations.

Type of MYSQL index:

General Index:

This is the most basic index, it has no restrictions, and it can be created in the following ways:

(1) create an index:

CREATE INDEX indexName ON mytable (username (length))

(2) modify the table results:

ALTER mytable ADD INDEX [indexName] ON (username (length))

CREATE TABLE mytable (ID INT NOT NULL, username VARCHAR (16) NOT NULL, INDEX [indexName] (username (length)

Delete the index:

DROP INDEX index_name ON table

two。 Unique index:

Similar to the previous normal index, the difference is that the value of the index column must be unique, but null values are allowed, and in the case of a combined index, the combination of column values must be unique. It can be created in the following ways:

(1) create an index:

CREATE UNIQUE INDEX indexname ON mytable (username (length))

(2) modify the table structure:

ALTER mytable ADD UNIQUE [indexName] ON (username (length))

(3) specify directly when creating the table:

CREATE TABLE mytable (ID INT NOT NULL,username VARCHAR (16) NOT NULL,UNIQUE [indexName] (username (length)

3. Primary key index:

This is a special unique index, and null values are not allowed. Generally, when creating a table, create a primary key index:

CREATE TABLE mytable (ID INT NOT NULL,username VARCHAR 16) NOT NULL,PRIMARY KEY (ID))

Note here that a table can only have one primary key.

4. Composite index:

To vividly compare single-column indexes with combined indexes, add multiple fields to the table:

CREATE TABLE mytable (ID INT NOT NULL,username VARCHAR (16) NOT NULL,city VARCHAR (50) NOT NULL,age INT NOT NULL)

To further extract the efficiency of mysql, consider building a composite index, which is to build the above name,city,age into an index:

ALTER TABLE mytable ADD INDEX name_ciry_age (name (10), city,age)

When we create the table, the username length is 16, and here we use 10. This is because in general, the length of the name will not exceed 10, which will speed up the index lookup, reduce the size of the index file, and improve the update speed of insert. If we set up a single-column index on username,city,age and let the table have three single-column indexes, the query efficiency will not be the same as the above combined index, which is much lower than our combined index.

Considerations for using the index:

1. The index will not contain a column with a null value: as long as the column contains a null value, it will not be included in the mysql index, and as long as one column in the index contains a null value, then this column is not valid for this composite index, so we do not allow the default value of the field to be NULL when designing the database.

two。 Use short indexes: index serial columns and, if possible, specify a prefix length. Short indexes can not only improve query speed, but also save disk space and Icano operations.

3. Index column sorting: the mysql query uses only one index, so if the index is already used in the where clause, the columns in order by will not use the index. Therefore, when the default sorting of the database can meet the requirements, do not use the sort operation, try not to include the sorting operation of multiple columns, and create a compound index for these columns if necessary.

4. Do not operate on the column, which will cause the index to fail and perform a full table scan.

Mysql differences between various indexes:

General index: the most basic index, without any restrictions.

Unique index: similar to a normal index, except that the value of the index column must be unique, but null values are allowed

Primary key index: it is a special unique index and null values are not allowed.

Full-text index: can only be used for MYISAM tables. It is time-consuming and space-consuming to generate a full-text index for larger data.

Combinatorial index: in order to improve the efficiency of mysql, we can establish combinatorial index and follow the principle of "leftmost prefix".

For the above relevant knowledge about mysql index, if you have more information, you can continue to pay attention to the innovation of our industry. If you need professional solutions, you can contact the pre-sales and after-sales on the official website. I hope this article can bring you some knowledge updates.

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.