Thresh

An overview of the

Indexes can speed up queries and affect where queries and order by sorting.

MySQL index type:

By index storage structure: B Tree index, Hash index, FULLTEXT full-text index, R Tree index By application level: common index, unique index, primary key index, compound index by index key value type: primary key index, secondary index (secondary index) by data storage and index key value logical relationship: Clustered index (clustered index), non-clustered index (non-clustered index)Copy the code

Normal index

The most basic index type, an index based on a normal field, has no restrictions.

Create a normal index as follows:

CREATE INDEX < INDEX name > ON tablename; ALTER TABLE tablename ADD INDEX; CREATE TABLE tablename ([... , INDEX [name of the INDEX] (field name));Copy the code

The only index

Similar to “normal index”, except that the value of the index field must be unique, but empty values are allowed. When a unique constraint is appended when a table is created or modified, the corresponding unique index is automatically created.

Create a unique index as follows:

CREATE UNIQUE INDEX < INDEX name > ON tablename; ALTER TABLE tablename ADD UNIQUE INDEX; CREATE TABLE tablename ([... , UNIQUE [index name] (column name);Copy the code

The primary key index

It is a special kind of unique index that does not allow null values. The primary key constraint can be added when creating or modifying a table. Each table can have only one primary key.

Create a primary key index as follows:

CREATE TABLE tablename ( [...] , PRIMARY KEY (PRIMARY KEY)); ALTER TABLE tablename ADD PRIMARY KEY; The commonly usedCopy the code

The composite index

Users can create indexes on multiple columns called group composite indexes (composite indexes). Composite indexes can replace multiple single indexes and require less overhead than multiple single indexes.

There are two concepts of index at the same time called narrow index and wide index. Narrow index refers to the index with 1-2 columns, and wide index refers to the index with more than 2 columns. An important principle of index design is to use narrow index instead of wide index, because narrow index is often more effective than combined index.

Create a composite index as follows:

CREATE INDEX < INDEX name > ON tablename ; ALTER TABLE tablename ADD INDEX ALTER TABLE tablename ADD INDEX ; CREATE TABLE tablename ([... , INDEX [name of INDEX] (field name 1, field name 2...) );Copy the code

Precautions for using composite indexes:

1. When to use compound indexes, build indexes according to WHERE conditions. Be careful not to use too many indexes. 2. If the table is already created (col1, col2), there is no need to create it separately (col1); If there is a (COL1) index, if the query needs col1 and COL2 conditions, can create (col1,col2) composite index, for the query has a certain improvement.Copy the code

The full text indexing

In the query operation, like fuzzy query can be used when the amount of data is relatively small, but for a large amount of text data retrieval, the efficiency is very low. If full-text indexes are used, the query speed is many times faster than that of like. Prior to MySQL 5.6, only MyISAM storage engine supported full-text indexing. Since MySQL 5.6, both MyISAM and InnoDB storage engine have supported full-text indexing.

Create a full-text index as follows:

CREATE FULLTEXT INDEX < INDEX name > ON tablename; ALTER TABLE tablename ADD FULLTEXT; ALTER TABLE tablename ADD FULLTEXT; CREATE TABLE tablename ( [...] , FULLTEXT KEY [index name] (field name);Copy the code

Unlike the usual like fuzzy queries, full-text indexes have their own syntax format, using the match and Against keywords, for example

select * from user where match(name) against('aaa');
Copy the code

Notes for using full-text indexes:

1. Full-text indexes must be created on string and text fields. 2. The full-text index field value must be between the minimum and maximum characters to be valid. (InnoDB: 3-84; Myisam: 4-84) 4. The value of the full-text index field should be cut according to the syntax character, for example, b+ AAA, divided into B and AAA 4. Full-text index matching query, default use is equivalent matching, for example, A matches a, will not match AB, AC. Select * from user where match(name) against('a 'in Boolean mode); select * from user where match(name) against('a' in Boolean mode); + must include the word - Must not include the word > improve the relevance of the word, the query results < reduce the relevance of the word, the query results back (*) asterisk wildcard, can only be followed by the wordCopy the code