(7) Design and use of MySQL index

Overview

?? Indexes are used to quickly find rows that have a specific value in a column. Without an index, MySQL must start with the 1th record and then read the entire table until the relevant rows are found. The larger the table, the more time it takes. If there is an index to the column queried in the table, MySQL can quickly reach a location to find the middle of the data file, and there is no need to look at all the data.
?? All types of data columns in MySQL can be indexed, and using indexes on related columns is the best way to improve the performance of select operations. Both the MyISAM and InnoDB storage engine tables are indexed by default Btree indexes. MyISAM also supports full-text indexing, which can be used to create full-text searches. By default, the memory storage engine uses a hash index, but it also supports btree indexes.

Design Indexing Principles

1. The most appropriate column for the index is the column that appears in the WHERE clause, or the column in the join clause.
2. With a unique index, the larger the cardinality of the indexed column, the better the index, such as the column that holds the birthday has different values, it is easy to distinguish between the rows, and the Record sex column, which contains only "M" and "F", the index of this column is not much use, because no matter what value is searched, the approximate half of the
3. With the prefix index, if you index a string column, you should specify a prefix length.
4. If multiple columns (composite indexes) are indexed, follow the leftmost prefix rule. The so-called leftmost column, which refers to the query starting at the leftmost top of the index and does not skip the columns in the index.
5. Do not use the index too much. Each time the update, delete, insert will maintain the index of the table; more indexes mean more space; too small a table may be slower to build:) (read a 2-page brochure, and you'll find the catalogue first?) ）。
6. For InnoDB storage engine tables, records are saved in a certain order by default, and if there is a well-defined primary key, it is saved in the primary key order, and the normal index of the InnoDB table holds the key value of the primary key, and all primary keys choose the shorter data type as far as possible.
7. An indexed column should not be part of an expression, or a function can be used on an indexed column.

Description:

CREATE TABLE (Friends)

Create (a composite index of UID and FUID).

• The 4th article explains:
  
  The 2nd statement, starting from the second column of the index, fails with the index, causing MySQL to use the All Access policy, which is the full table query. In development, you should try to avoid full table queries.
• The 7th article explains:
  
  Expressions are used in both the 2nd and 3 statements, and indexing is not useful.

Reference

Reference 1:http://thephper.com/?p=142
Reference 2:http://my.oschina.net/longniao/blog/110384

(7) Design and use of MySQL index