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

This article mainly shows you how to use the mysql index, the content is easy to understand, I hope you can learn, after learning, there will be a harvest, the following let the editor to take a look at it.

What is an index: an index can help find data quickly, but basically indexes are required to be unique (some are not), so it also restricts the uniqueness of the data to some extent. The index is created on a data table object and consists of one or more fields that form "keys" to be stored in the data structure (B-tree or hash table). [can be classified into B-tree index (innodb\ myisam engine) and hash index (memory engine) according to the data structure] because the index is used to speed up data search, it is generally used on the fields that often need to be searched (such as fields commonly used in where). The index types supported by mysql are: general index, unique index, full-text index, single-column index, multi-column index, spatial index. Add: the difference between the primary key and the index: the primary key is also a kind of index, and the primary key is also a kind of unique index, but the function of the index is mainly to improve the search speed, while the primary key is mainly to identify the uniqueness of the record (of course, it is also easy to find). Create an index: a normal index: an index regular index is an index that is not required to be "unique" or "not empty". Syntax: create table table name (field data type, field data type, … Index | key [index name] (field [length of index] [asc | desc]); index | key represents that you can use the index keyword or the key keyword index name is optional. If left empty, the index name is the field name. The field can have multiple fields (multi-column indexes). The length of the index, optional (some engines set the maximum length, generally not in-depth study should be rarely encountered, not described here) asc | desc is optional, representing the "keyword" sort in the index to add the index: create index index name on table name (field [length of index] [asc | desc]) Add an index to the existing table structure: alter table table name add index | key index name (field [length of index] [asc | desc]); unique index: unique index, that is, when creating an index, the data of the field is required to be unique. Creation syntax: create table table name (field data type, field data type, … Unique index | key [index name] (field [length of index] [asc | desc]); [Parameter information refer to general index] add index to existing table structure: create unique index index name on table name (field [length of index] [asc | desc]); add index to existing table structure: alter table table name add unqiue index | key index name (field [length of index] [asc | desc]) Full-text indexing: fulltext index full-text indexing is generally used on fields with data types char,varchar and text, making it easy to find these longer data. Myisam storage engine supports full-text index creation syntax: create table table name (field data type, field data type, … Fulltext index | key [index name] (field [length of index] [asc | desc]); [Parameter information refer to general index] add index to existing table structure: create fulltext index index name on table name (field [length of index] [asc | desc]); add index to existing table structure: alter table table name add fulltext index | key index name (field [length of index] [asc | desc]) Single-column index: when the indexed field above is one, create a multi-column index for a single-column index: when there are multiple indexed fields, create a syntax for a multi-column index: just change the above (field [length of index] [asc | desc]) to (field 1 [length of index] [asc | desc], field 2 [length of index] [asc | desc], field 1 [length of index] [asc | desc]). View the index: you can view the index by viewing the table structure, you can view the index by viewing the table creation statement, and you can use the explain statement to view the index. Explain is used to determine the time efficiency of the execution of the mysql statement. So you can use the explain select statement where plus the fields of the index as a condition; the possible_key in the result is the index that may be used, and the index actually used by key. For example: explain select * from user_info where username= "lilei"

Delete index: drop index index name on table name; for example: drop index myindex on user_info; above is about how to use the mysql index, if you have learned knowledge or skills, you can share it for more people to see.

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.