Design and use of MySQL index

One, indexes can effectively improve the performance of select operations, while affecting the performance of update, create, and delete operations. Each engine has a limit on the number and length of indexes on the table.

Second, the design principles of the index

(A) The index column of the search, which is not necessarily the column to select, the column that best fits the index is the column in the WHERE clause or in the JOIN clause.

(B) with a unique index, the larger the base of the indexed column, the better the index.

(C) using a short index, if the string column (Char,varchar,text) index, you should specify the prefix length, using the prefix index.

(D) using the leftmost prefix, when creating an index of N-columns, the actual creation of the N-indexes available to MySQL. The leftmost Lie in the index can be used to match rows, so column set is called the leftmost prefix.

(E) do not over-index,

(F) for tables with the InnoDB storage engine, it is important to select a good primary key by storing the primary key, the unique index, and the internal column in sequence. At the same time, the normal index holds the key value of the primary key, and the primary key selects the short data type as much as possible.

Third, btree index and hash index

(A) The characteristics of the hash index:

Equality comparison only for = and <=> operators.

The optimizer cannot use a hash index to speed up the order by operation.

MySQL cannot determine the approximate number of rows between two values.

You can only use the entire keyword to search for a row.

(B) Characteristics of the Btree index

You can use the index when you use >, <, >=, <=, between,! =, or <>, or like ' pattern ' (which does not start with a wildcard character).

Iv. Summary

Most MySQL indexes are stored using btree, and the spatial column type is indexed using Rtree.

Design and use of MySQL index