1. Index typeWhen you create an index, you can specify whether the index can contain duplicate values. If not included, the index should be created as a primary KEY or a UNIQUE index. For single-column uniqueness indexes, this guarantees that a single column does not contain duplicate values. For multi-column uniqueness indexes, the combination of multiple values is guaranteed to be distinct. the PRIMARY KEY index is very similar to a UNIQUE index. In fact, the PRIMARY KEY index is only a UNIQUE index with the name PRIMARY. This means that a table can contain only one PRIMARY KEY because it is not possible to have two indexes with the same name in a table.
2. Index functionFirst- level index, simple understanding can be understood as the book directory, the directory corresponds to the number of pages, suddenly found content, so fast;
A Level Two index, for example:table:
| Id |
user_id |
Someting |
| 0 |
0 |
Nose |
| 1 |
0 |
Mouth |
| 2 |
1 |
Hair |
| 3 |
3 |
Eyes |
|
|
|
If we are looking for user_id=0, if we do not establish a level two index, we must traverse User_id[map:key-ID, Value---[USER_ID, Something]], algorithm complexity O (n). Set up a two-level index, using space-time-changing methods:
| user_id |
Id |
| 0 |
0 |
| 0 |
1 |
| 1 |
2 |
| 3 |
3 |
|
|
Map:key-user_id, Value---[ID] quickly locate the ID with user_id and then quickly set it to the desired record based on the ID. Understand the principle, tuning will better understand the memory, saying that indexing rules too many, so far I do not remember, the principle is unchanged.
3. Create an indexThe CREATE table can be indexed, or the CREATE index or ALTER table will be used to add indexes to the table. 1. ALTER TABLE
ALTER table is used to create a normal index, a unique index, or a primary key index.
ALTER TABLE table_name ADD INDEX index_name (column1, Column2, ...)ALTER TABLE table_name ADD UNIQUE (column1, Column2, ...)ALTER TABLE table_name ADD PRIMARY KEY (column1, Column2, ...)
index name index_name Optional, when not filled out, MySQL assigns a name based on the first indexed column.
2. CREATE INDEX
CREATE Index to add a normal or unique index to a table.
CREATE INDEX index_name on table_name (column1, Column2, ...)CREATE UNIQUE INDEX index_name on table_name (column1, Column2, ...)
4. Deleting an indexDelete index: ALTER TABLE or drop. Similar to the CREATE INDEX statement, DROP Index can be handled as a statement inside ALTER TABLE, with the following syntax.
DROP INDEX index_name on Talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY
Where the first two statements are equivalent, delete the index index_name in table_name.
The 3rd statement is only used when deleting the PRIMARY key index, because a table may have only one PRIMARY key index, so you do not need to specify the index name. If the PRIMARY KEY index is not created, but the table has one or more unique indexes, MySQL deletes the first unique index.
If a column is removed from the table, the index is affected. For multiple-column combinations of indexes, if one of the columns is deleted, the column is also removed from the index. If you delete all the columns that make up the index, the entire index is deleted.
5. View Indexshow keys from Tblname;field Interpretation:• TableTable name• Non_unique0 If the index cannot include a repeating word. 1 if it is possible• Key_namethe name of the index• Seq_in_indexthe column sequence number in the index, starting from 1• column_nameColumn Name• CollationThe column is stored in the index in what way. In MySQL, there is a value of ' a ' (ascending) or null (no classification)• Cardinalityan estimate of the number of unique values in the index. You can update by running analyze table or myisamchk-a. The cardinality is counted according to the statistics stored as integers, so even for small tables, this value is not necessarily accurate. The larger the cardinality, the greater the chance that MySQL will use the index when it is federated.• Sub_partThe number of characters that are indexed if the column is only partially indexed. Null if the entire column is indexed• Packedindicates how the keyword is compressed. Null if it is not compressed• Nullif the column contains null, it contains Yes. If not, the column contains no• Index_typeindexed methods Used (BTREE, Fulltext, HASH, RTREE)• CommentNotes
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
MySQL index view, create, delete
