MySQL Index and key

0. The difference between the primary key and the index

The primary key has only two uses on the physical level: uniquely identifies a row, and as an object that can be effectively referenced by a foreign key.

An index is a special kind of file (an index on a InnoDB data table is an integral part of a table space), and they contain reference pointers to all records in the datasheet. Here are some of the differences and linkages between the primary key and the index.

1. The primary key must be a unique index, and the uniqueness index is not necessarily the primary key.

2. There can be multiple unique indexes in a table, but only one primary key.

3. The primary key column does not allow null values, and the uniqueness index column allows null values.

4. Indexes can increase the speed of queries, do not constrain the unique non-nullability of fields, and the key constrains the unique non-nullability of records.

5. Whether the primary key or the foreign key must be established on the index field

First, the index

http://blog.csdn.net/xluren/article/details/32746183

Index is the statistics of the whole table, can improve the efficiency of query, but may reduce the efficiency of adding and removing; The index is used to find records, and keys are used to constrain records. Of course, unique also has a certain binding effect on records.

1. Efficiency of the query resulting from the index

If the correct design and use of the index of MySQL is a Lamborghini, then no design and use of the index of MySQL is a human tricycle. For tables that do not have an index, a single-table query might be a bottleneck for hundreds of thousands of of data, whereas large web sites can typically produce hundreds of thousands of or even millions of of the data in one day, and no index queries can become very slow. or WordPress, many of its data tables will be indexed to fields that are frequently queried. An indexed table can only be used when the index is in place to improve efficiency.

2. Index definition

An index is a special kind of file (an index on a InnoDB data table is an integral part of a table space), and they contain reference pointers to all records in the datasheet. More generally, the database index is like a directory in front of a book, which can speed up the database query. The database is faster than checking the data table directly (traversing the entire table).

The index is divided into clustered index and non-clustered index, and the clustered index is in order according to the physical location of the data, and the non-clustered index is different; The clustering index can improve the speed of multi-row retrieval, but the non-clustered index is very fast for the single-line retrieval.

3. Type of index

A. Normal index

This is the most basic index, it has no restrictions, such as the previous index created for the title field is a normal index, myiasm in the default index of the Btree type, but also in most cases we use the index.

CREATE INDEX index_name on table (column (length))

DROP INDEX index_name on table

B. Unique index uniquely

Like a normal index, the difference is that the value of the indexed column must be unique, but it allows for a null value (note differs from the primary key). If it is a composite index, the combination of column values must be unique, similar to the creation method and the normal index.

CREATE UNIQUE INDEX indexname on table (column (length))

C. Full-text indexing (fulltext)

MySQL supports full-text and full-text indexing starting from version 3.23.23, Fulltext indexes are only available for MyISAM tables; but remember that for large data tables, generating full-text indexing is a very time consuming way to consume hard disk space.

4. Constructing an index on a column

Create an index on a field that needs to be used frequently after the WHERE clause

A. Single-column index, multicolumn index

Multiple single-column indexes differ from the query effect of a single multicolumn index because MySQL can use only one index when executing a query, and one of the most restrictive indexes is selected from multiple indexes.

B. Combined index (leftmost prefix) (clustered index)

Usually use the SQL query statements generally have more restrictive conditions, so in order to further extract the efficiency of MySQL, we should consider the establishment of a composite index.

For example, the previous table establishes a composite index for title and time: ALTER Table Article ADD index Index_titme_time (title (), Time (10)).

Creating such a composite index is actually equivalent to establishing the following two sets of composite indexes:

–title,time

–title

Why is there no such combination index as time? This is because the MySQL composite index is the result of the "leftmost prefix". The simple understanding is only from the left to the beginning of the combination. Not all queries that contain these two columns will use the combined index, as shown in the following SQL:

– Use the index above

SELECT * from article Whree title= ' test ' and time=1234567890;

SELECT * from article Whree title= ' test ';

– Do not use the index above (this is the feature of the composite Index)

SELECT * from article Whree time=1234567890;

5. How to create an index reasonably

A. When do I use a clustered or nonclustered index?

Action Description using a clustered index with a nonclustered index

Columns are often sorted by grouping using the

Returns data in a range without using

One or very few different values are not used without using

A small number of different values are used without using the

A large number of different values are not used

Frequently updated columns are not used with the

Foreign key columns are used using the

Primary key columns are used using the

Frequently modified index columns are not used with

B. The index does not contain columns with null values

This column is not valid for this composite index as long as the column contains null values that will not be included in the index, as long as there is a column in the composite index that contains null values. So we don't want the default value of the field to be null when the database is designed.

C. Using a short index

Index A string, or specify a prefix length if possible. For example, if you have a column of char (255), and if the majority value is unique within the first 10 or 20 characters, do not index the entire column. Short indexes not only improve query speed but also save disk space and I/O operations.

D. Index column sorting

The MySQL query uses only one index, so if an index is already used in the WHERE clause, the column in order by is not indexed. So do not use sort operations where the default sorting of the database is acceptable, and try not to include multiple columns, if you need to create a composite index for those columns.

E.like Statement Operations

It is generally discouraged to use the like operation, which is also an issue if it is not used. Like "%aaa%" does not use the index and like "aaa%" can use the index.

F. Do not perform calculations on columns

For example: SELECT * from the users where year (adddate) <2007 will be performed on each row, which will cause the index to fail with a full table scan, so we can change to: SELECT * from Users where adddate < ' 2007-01-01′. On this point can be onlookers: a single quotation mark caused by the MySQL performance loss.

G. Conditions of application of the index

MySQL uses indexes only on the operators: <,<=,=,>,>=,between,in, and sometimes like (not in the case of a wildcard% or _). In theory each table can create up to 16 indexes, but unless the amount of data is really many, otherwise too much use of the index is not so fun, such as I just for the text type of the field to create an index, the system almost stuck.

Second, the key

The key is a higher-level constraint on the peso, and when you create a key, the database automatically creates an index on that field. So it can be said that the key is implemented on the basis of the index. There are 2 key types: Primary key and foreign key.

1. Primary KEY PRIMARY Key

A. Role of the primary key: (unique non-null constraint)

A primary key is a constraint on a record on that field: The field cannot be duplicated and cannot be empty, so the primary key is considered to be the best combination of NOT NULL and unique constraints. If these columns are not explicitly defined as not null,mysql, these columns are implicitly defined.

(Automatically checks if the field is not empty when you insert a record)

B. Methods for declaring a primary key:

CREATE TABLE tbl_name ([Field description omitted ...], PRIMARY KEY (index_col_name));

Lter TABLE tbl_name ADD PRIMARY KEY (index_col_name,...);

C. Combining primary keys

The key is indexed and higher than the index. The primary key is actually an index, even in the MySQL term "key" is equal to "index", so "foreign key" must be set to "index", this is our next log to discuss. So the primary key should also be the same as the index, either for a separate field or for multiple fields. Combined primary key, each column implicitly defines a NOT NULL constraint, and the combination of the two is defined by the unique unique constraint. The combination of multiple fields will be constrained to a unique non-null.

CREATE TABLE Firewall (

Host varchar (one) is not NULL,

Port smallint (4),

Access enum (' Deny ', ' allow ') is not NULL,

Primary KEY (Host,port)

)

INSERT into Firewall (host, port, access)

VALUES (' 202.65.3.87 ', ' + ', ' deny ');

/* Insert a new record, no problem 1 row (s) inserted.*/

INSERT into Firewall (host, port, access)

VALUES (' 202.65.3.87 ', ' + ', ' deny ');

/* Insert failed because the primary key value of host plus port 202.65.3.87-21 already exists

#1062-duplicate entry ' 202.65.3.87-21 ' for key ' PRIMARY '

*/

INSERT into Firewall (host, port, access)

VALUES (' 192.168.0.1 ', NULL, ' deny ')

/*

Not declaring not NULL port also cannot be null

#1048-column ' port ' cannot be null

*/

Combined PRIMARY KEY constraint: The combined field cannot be empty or duplicated.

2. Foreign key FOREIGN key

Only tables of the InnoDB type can use foreign keys, and MySQL defaults to MyISAM, which does not support foreign key constraints.

A table that establishes a foreign key, called a child table, or a referenced table is called a primary table or appearance.

A. Role of foreign keys: (subset constraints: Have certain constraints on both the primary and child tables)

If the B table field has a foreign key with a table field as the reference, then the value of this field in B can only be the value that exists in table A (the child table is a subset of the primary table), and the table B is constrained by the main Table A in real time, and if the associated on delete Update, and so on when the referenced field in a is deleted or update, the corresponding record in B will also occur delete or update operation, integrity, if you do not set delete or update linkage, when you delete the records in table A, If a corresponding record exists in table B, it cannot be deleted successfully (because that will not satisfy the subset constraint). To constrain one or more of the other table B's fields to one or more of the combination fields in one table A, requiring that the record of the field in table B is a subset of the field records for the table A, as long as the subset constraints are satisfied, but not possible anyway

B. Steps to create

Specify PRIMARY KEY keyword: foreign key (column name)

Referencing foreign key keywords: references < foreign key table name > (foreign key column name)

C. Foreign Key association event trigger limit: On delete and on update, you can set parameter cascade (following foreign key changes), restrict (Restrict foreign key changes in appearance), SET null (null value), set default, [Default]no Action

CREATE TABLE Temp (

ID int,

Name Char (20),

FOREIGN key (ID) references outtable (ID) on the DELETE cascade on UPDATE cascade);

Set the ID column as a foreign key, referring to the ID column of the outer outtable, when the foreign key value is deleted, the corresponding ID record in this table is deleted, when the value of the foreign key changes the corresponding column value in this table.

D. Combining foreign keys

There can be only one foreign key, but multiple table fields can be combined into a composite foreign key. Not recommended

E. Fully allow many pairs of relationships

A record in the primary table can correspond to multiple records in the child table, and multiple records in the primary table can correspond to one record in the child table

Mysql> show create table parent; Main Table

CREATE TABLE ' Parent ' (

' id ' int (one) is not NULL,

' Name ' char (4) DEFAULT NULL,

PRIMARY KEY (' id ')

) Engine=innodb DEFAULT Charset=utf8

Mysql> Show create TABLE child; Child table

CREATE TABLE ' child ' (

' id ' int (one) is not NULL,

' parent_id ' int (one) is not NULL,

KEY ' parent_id ' (' parent_id '),

CONSTRAINT ' Child_ibfk_1 ' FOREIGN KEY (' parent_id ') REFERENCES ' parent ' (' id ') on DELETE CASCADE

) Engine=innodb DEFAULT Charset=utf8

Mysql> select * from child;

+----+-----------+

| ID | parent_id |

+----+-----------+

|         1 |   1 | The IDs of multiple child tables correspond to the parent_id of the same primary table,

|         3 | 1 |

|         1 |  2 | Simultaneously the ID of the same child table corresponds to the parent_id of multiple primary tables

|         1 | 3 |

+----+-----------+

4 rows in Set (0.00 sec)

Mysql> SELECT * from parent;

+----+------+

| ID | name |

+----+------+

| 1 | A |

| 2 | B |

| 3 | C |

+----+------+

3 Rows in Set (0.00 sec)

This article from "Tech record" blog, declined reprint!

MySQL Index and key