About MySQL's index

Let's start by understanding the concept of MySQL indexing:

An index is a special file that contains reference pointers to all the records in the data table. More generally, the database index is like a directory in front of a book, can speed up the query speed of the database, after we have the corresponding index, the database will be directly in the index to find the eligible options. The index is divided into clustered index and non-clustered index, the clustered index is in order according to the physical location of the data, and the non-clustered index can improve the speed of multi-row retrieval, and the non-clustered index will retrieve the single line quickly.

There are two main structures of MySQL: B + Tree and hash

Hash indexes are less useful in MySQL, which organizes the index of data in hash form, so it is very fast when looking for a record. Because it is a hash structure, each key corresponds to only one value, and it is a hash distribution, so it does not support functions such as range lookup and sorting.

B + Tree: 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, so it is more suitable for processing sorting, scope lookup and other functions. Relative hash index, B + tree in the search for the speed of a single index is less than the hash index, but because more suitable for sorting and other operations, so more popular with users.

Types of MySQL indexes:

1. Normal index:
   

This is the most basic index, it does not have any restrictions, it has the following several ways to create:

(1) Create an index:

CREATE INDEX indexname on mytable (username (length));

(2) Modify table results:

ALTER mytable ADD INDEX [IndexName] on (username (length))

CREATE TABLE mytable (ID INT NOT NULL, username VARCHAR (+) not NULL, INDEX [Inde XName] (username (length)));

To delete an index:

DROP INDEXindex_name ON table

2. Unique index:

Similar to the previous normal index, the value of the indexed column must be unique, but a null value is allowed, and if it is a composite index, the combination of the column values must be unique. It is 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) When creating a table, specify directly:

CREATE TABLE mytable (ID INT not null,username VARCHAR (+) not Null,unique [IndexName] (username (length)))

3. Primary KEY index:

This is a special unique index that does not allow null values, typically creating a primary key index when a table is in progress:

CREATE TABLE mytable (id INT not null,username VARCHAR (+) not null,primary KEY (id));

It is important to note that a table can have only one primary key.

4. Combined Index:

Add multiple fields to a table for image comparison of single-column and composite indexes:

CREATE TABLE mytable (ID INT not null,username varchar (+) not null,city varchar (NO) 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 (ten), city,age)

When we built the table, the length of the username was 16, which was 10, because in general, the length of the name would not exceed 10, which would speed up the index lookup, reduce the size of the index file, and increase the update speed of the insert. If you create a single-column index on Username,city,age, so that the table has 3 single-column indexes, the query and the combined indexes above will not be as efficient, much lower than our combined index.

Considerations for Using Indexes:

1. The index will not contain a column with null values: As long as the column contains null values, it will not be included in the MySQL index, as long as there is a column in the index that contains null values, this column is invalid for this composite index, so we do not let the database design the default value of the field is null.

2. Use a short index: Index the string, or specify a prefix length if possible. Short indexes not only improve query speed, but also save disk space and I/O operations.

3. Index column ordering: The MySQL query uses only one index, so if an index is already used in the WHERE clause, the column in order by is not indexed. Therefore, if the database default ordering can meet the requirements, do not use sort operations, try not to include multiple columns of the sort operation, if you need to create a composite index for these columns.

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

MySQL various index differences:

Normal index: The most basic index, without any restrictions;

Unique indexes: Similar to normal indexes, except that the values of indexed columns must be unique, but allow null values;

Primary KEY index: It is a special unique index and is not allowed to have null values;

Full-text index: can only be used for MyISAM tables, for large data, to generate full-text index, time consuming space;

Combined index: For higher MySQL efficiency, you can create a composite index that follows the "leftmost prefix" principle.

About MySQL's index