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

What are the categories of MySQL index? I believe many inexperienced people don't know what to do about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

1. Classification

MySQL index is divided into general index, unique index, primary key index, combined index and full-text index. The index does not contain a column with a null value, and the index entry can be null (unique index, composite index, etc.), but as long as there is a null value in the column, it is not included in the index.

(1) General index: create index index_name on table (column)

Or specify create table (..., index index_name column) when creating the table.

(2) unique index: similar to a normal index, the value of the index column must be unique (can be empty, which is different from the primary key index)

Create unique index index_name on table (column); or specify unique index_name column when creating the table

(3) Primary key index: a special unique index, which is not allowed to be empty, but can only be one. Generally, primary key (column) is specified when creating a table.

(4) combined index: create an index on multiple fields, following the leftmost prefix principle. Alter table t add index index_name (a _

(5) full-text index: mainly used to find keywords in the text, not directly compared with the values in the index, such as a search engine, used with match against, now only char,varchar,text can create a full-text index.

When the amount of data is large, put the data in a table without a full-text index, and then create a full-text index using create index, which is much faster than creating a full-text index and then inserting data.

2. When to use the index

MySQL uses only one index per query. Rather than saying that a database query can only use one index, it is more time-consuming to analyze two index B + trees than a full table scan. So it is best to use the combined index of (A _ Magi B) for this kind of query, and the B + tree is sorted according to (A _ Magi B).

Primary key, unique field; fields that are connected to other tables need to be indexed; use >, ≥, =, <, ≤, is null and between fields in where; fields in like,where A like 'China%'; aggregate functions MIN (), MAX (), which do not start with wildcards; order by and group by fields; 3. When there are too few index table records; fields with duplicate and evenly distributed data (columns with few data values) Tables that are frequently inserted, deleted and modified should reduce indexes; types such as text,image should not build indexes, and these columns have a large amount of data (if the first 10 characters of text are unique, you can also index the first 10 characters of text); MySQL can estimate that indexes are not used when full table scans are faster than using indexes 4, when the index fails, the combination index does not use the leftmost prefix, for example, the combination index (where B), where indexing does not use the leftmost prefix; like does not use the leftmost prefix, where A like'% China'; searches one index and does the order by,where index order by B on another index, using only the index on A because the query uses only one index; or will invalidate the index. If the query fields are the same, you can also use the index. For example, where A=a1 or A=a2 (effective), where invalid or invalid (invalid) if the column type is a string, use quotation marks. For example, where invalid Chinese characters, otherwise the index is invalid (type conversion will be performed); operations on index columns, functions (upper (), etc.), or,! = (), not in, etc. 5. Explain statement

Type field is All and index is not used; for ref, index is used

ALL: full table scan index: index full scan range: index range scan

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.