How does MySQL create an index? MySQL CREATE index

MySQL indexing function

On the index column, in addition to the ordered lookup, the database can greatly improve the query efficiency by using a variety of fast positioning techniques. In particular, when the volume of data is very large and queries involve multiple tables, using an index can often speed up the query by tens of thousands of times.

MySQL Index creation rules:

1, the table's primary key, the foreign key must have the index;
2, the data volume of more than 300 of the table should be indexed;
3. Tables that are frequently connected to other tables should be indexed on the connection field
4, often appear in the WHERE clause in the field, especially large table fields, should be indexed;
5, the index should be built on the field of high selectivity;
6, the index should be built on the small section, for large text fields and even long fields, do not build indexes;
7, the establishment of composite index needs careful analysis; Consider using a single field index instead:
A, the correct selection of the composite index of the main column field, is generally a better choice of fields;
B, how often do several fields of a composite index appear in the WHERE clause at the same time? are word-field queries very small or not? If yes,
You can set up a composite index, otherwise consider a single field index;
C, if the fields contained in the composite index often appear separately in the WHERE clause, they are decomposed into multiple single field indexes;
D, if the composite index contains more than 3 fields, consider the need to reduce the compound field carefully;
E, if both the Single field index, and the composite index on these fields, you can generally delete the composite index;
8, frequent data operation of the table, do not establish too many indexes;
9. Delete useless indexes to avoid negative impact on the execution plan; These are some common criteria for establishing indexes.
Word, the establishment of the index must be prudent, the need for each index should be carefully analyzed, to establish the basis.
Because too many indexes and inadequate, incorrect indexes are not good for performance: Each index created on the table increases storage overhead,
Indexing also increases processing overhead for insert, delete, and update operations. In addition, too many composite indexes, in the case of a single field index,
In general, there is no value, on the contrary, it will reduce the performance of the data to increase the deletion, especially for frequently updated tables, the negative impact is greater.

2. Create an index

You can create an index when you execute a CREATE TABLE statement, or you can add an index to a table by using either CREATE INDEX or ALTER TABLE alone.
1. ALTER TABLE

ALTER table is used to create a normal index, a unique index, or a primary key index.

The code is as follows	Copy Code
ALTER TABLE table_name ADD INDEX index_name (column_list) ALTER TABLE table_name ADD UNIQUE (column_list) ALTER TABLE table_name ADD PRIMARY KEY (column_list)

Where table_name is the name of the table to increase the index, column_list indicates which columns are indexed, and when multiple columns are separated by commas. Index name index_name optional, by default, MySQL assigns a name to the first indexed column. In addition, ALTER TABLE allows multiple tables to be changed in a single statement, so multiple indexes can be created at the same time.
2. CREATE INDEX

Create index adds either a normal index or a unique index to a table.

The code is as follows	Copy Code
CREATE INDEX index_name on table_name (column_list) CREATE UNIQUE INDEX index_name on table_name (column_list)

TABLE_NAME, index_name, and column_list have the same meaning as the ALTER TABLE statement, and the index name is not selectable. In addition, you cannot create a primary key index with the CREATE INDEX statement.
3. Index type

When you create an index, you can specify whether the index will contain duplicate values. If not, the index should be created as a primary key or a unique index. For a single-column uniqueness index, this guarantees that a single column does not contain duplicate values. For a multiple-column uniqueness index, the combination of multiple values is guaranteed to be repeated.

The PRIMARY key index is very similar to a unique index. In fact, the PRIMARY key index is only a unique index with a name PRIMARY. This means that a table can contain only one primary KEY, because one table cannot have two indexes with the same name.

The following SQL statement adds a primary key index to the students table on the SID.

The code is as follows	Copy Code
ALTER TABLE Students ADD PRIMARY KEY (SID)

4. Delete Index

You can use ALTER TABLE or DROP INDEX statements to delete an index. Similar to the CREATE INDEX statement, DROP Index can be handled as a statement within the ALTER TABLE, as follows.

The code is as follows	Copy Code
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 from the table_name.

The 3rd statement is only used when deleting the primary key index, because a table can have only one primary key index, so you do not need to specify an 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 deleted from the table, the index is affected. For indexes of multiple-column combinations, if you delete one of the columns, the column is also deleted from the index. If all the columns that make up the index are deleted, the entire index is deleted.

5. View Index

The code is as follows	Copy Code
Mysql> Show index from Tblname; Mysql> show the keys from Tblname;

· Table

The name of the table.

· Non_unique

0 if the index cannot include duplicate words. 1 if it is possible.

· Key_name

The name of the index.

· Seq_in_index

The column sequence number in the index, starting at 1.

· column_name

The column name.

· Collation

How the columns are stored in the index. In MySQL, there is a value of ' a ' (ascending) or null (no classification).

· Cardinality

An estimate of the number of unique values in the index. Can be updated by running Analyze table or myisamchk-a. The cardinality is counted according to the statistics that are 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 comes to syndication.

· Sub_part

The number of characters indexed if the column is only partially indexed. Null if the entire column is indexed.

· Packed

Indicates how the keyword is compressed. Null if it is not compressed.

· Null

If the column contains null, it contains Yes. If not, the column contains No.

· Index_type

Used indexing method (Btree, Fulltext, HASH, Rtree).

· Comment

Some common statements about over-citation

code is as follows

copy code

1. Add PRIMARY key (primary key index) Mysql>alter TABLE ' table_name ' Add PRIMARY KEY (' column ') 2. Add unique (unique index) m Ysql>alter table ' table_name ' Add UNIQUE ( ' column ' ) 3. Add index (normal index) Mysql>alter table ' Table_n Ame ' Add index index_name (' column ') 4. Add fulltext (Full-text indexing) Mysql>alter TABLE ' table_name ' Add fulltext (' Column ') 5. Add a multiple-column index mysql>alter TABLE ' table_name ' Add index index_name (' Column1 ', ' column2 ', ' column3 ')