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

Classification of the index:

General index, primary key index, unique index, full-text index, composite (combined) index.

When to create an index:

(1) Fields that are more frequently used as query criteria

(2) for fields with poor uniqueness (such as gender / status fields, etc.), even if we frequently use them as conditions for query, it is not suitable to create indexes.

(3) the fields whose data are updated very frequently (such as the current online population field of the statistical platform / the sales quantity field of goods, etc.) are not suitable for creating indexes.

3. Create an index (you can also create it directly with a visualization tool, which we demonstrate in code here):

(1) General index:

Create index index name on table name (table field)

(2) Primary key index:

When you create a table, set the primary key field, such as id, to be the primary key index.

(3) unique index:

① if the field has a unique constraint (unique), the field is a unique index

② creates a unique index directly: create unique index index name on table name (field 1, field 2...)

(4) full-text index:

As the name implies, a full-text index is mainly a search of documents and texts (such as commodity details, articles, news, etc.).

Note: with full-text indexing, the engine of the table must be set to MyISAM, otherwise it is invalid.

① creation: create fulltext index index name on table name (field 1, field 2...)

② uses:

I) selete field 1, field 2... From table name where match (matches the corresponding full-text index field) against (fuzzy retrieval of the flying Chinese string).

II) the where condition is not available: the full-text index field like'% string%', otherwise the full-text index is invalid.

III) the fulltext provided by mysql is only valid for English. Chinese processing should be done with sphinx (coresseek) technology. Later, we will talk about the installation and use of sphinx (coresseek).

③ Composite Index:

Create index index name on table name (field 1, field 2...)

4. Cost of the index:

(1)。 Creating an index requires overhead and takes up a certain amount of disk space.

(2)。 It is difficult to draw index data.

(3)。 In order to maintain the index file, it will cause the operation of adding, deleting and changing to become slow.

Common sense: 1. Show index from table name, you can view the detailed use of the index of the corresponding table

2. Explain: if sql is not executed, you can see the detailed implementation of sql, which makes it easy for us to analyze sql and optimize sql.

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.