MySQL index
1. Introduction
MySQL currently has the following index types:
1. Ordinary index
2. Unique index
3. Primary key index
4. Combined index
5. Full-text index
Second, the statement
<pre>CREATE TABLE table_name[col_name data type]
[unique|fulltext][index|key]index_name[asc|desc]</pre>
1.unique|fulltext is an optional parameter, which means unique index and full text index respectively
2. index and key are synonyms, both have the same function, used to specify the creation of an index
3. col_name is the column of the field that needs to create an index, the column must be selected from the multiple columns defined in the data table
4. index_name specifies the name of the index, which is an optional parameter. If not specified, the default col_name is the index value
5. length is an optional parameter, indicating the length of the index, only the string type field can specify the index length
6.asc or desc specify ascending or descending index value storage
Three,
index type
1. Ordinary index
It is the most basic index, it has no restrictions. It can be created in the following ways:
(1) Create index directly
<pre>CREATE INDEX index_name ON table(column(length))</pre>
(2) Add indexes by modifying the table structure
<pre>ALTER TABLE table_name ADD INDEX index_name ON (column(length))</pre>
(3) Create an index while creating a table
<pre>CREATE TABLE table (
id int(11) NOT NULL AUTO_INCREMENT,
title char(255) CHARACTER NOT NULL,
content text CHARACTER NULL,
time int(10) NULL DEFAULT NULL, PRIMARY KEY (id), INDEX index_name (title(length))
)</pre>
(4) Delete the index
<pre>DROP INDEX index_name ON table</pre>
2. Unique index
Similar to the previous ordinary index, the difference is: the value of the index column must be unique, but null values are allowed. If it is a composite index, the combination of column values must be unique. It can be created in the following ways:
(1) Create a unique index
<pre>CREATE UNIQUE INDEX indexName ON table(column(length))</pre>
(2) Modify the table structure
<pre>ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))</pre>
(3) Specify directly when creating a table
<pre>CREATE TABLE table (
id int(11) NOT NULL AUTO_INCREMENT,
title char(255) CHARACTER NOT NULL,
content text CHARACTER NULL,
time int(10) NULL DEFAULT NULL, UNIQUE indexName (title(length))
);</pre>
3. Primary key index
It is a special unique index. A table can only have one primary key and no null values are allowed. Generally, the primary key index is created at the same time when the table is built:
<pre>CREATE TABLE table (
id int(11) NOT NULL AUTO_INCREMENT,
title char(255) NOT NULL, PRIMARY KEY (id)
);</pre>
4. Combined index
Refers to an index created on multiple fields. The index will only be used when the first field when creating the index is used in the query conditions. Follow the leftmost prefix set when using composite indexes
<pre>ALTER TABLE table ADD INDEX name_city_age (name,city,age); </pre>
5. Full-text index
Mainly used to find keywords in the text, rather than directly compare with the value in the index. The fulltext index is very different from other indexes. It is more like a search engine, rather than a simple where statement parameter matching. The fulltext index is used in conjunction with the match against operation, rather than the general where statement plus like.
It can be used in create table, alter table, and create index, but currently only char, varchar, and text columns can create full-text indexes. It is worth mentioning that when the amount of data is large, now put the data into a table without a global index, and then use CREATE index to create a fulltext index, rather than creating a fulltext for a table and then writing the data The speed is much faster.
(1) Create a table suitable for adding a full-text index
<pre>CREATE TABLE table (
id int(11) NOT NULL AUTO_INCREMENT,
title char(255) CHARACTER NOT NULL,
content text CHARACTER NULL,
time int(10) NULL DEFAULT NULL, PRIMARY KEY (id),
FULLTEXT (content)
);</pre>
(2) Modify the table structure to add a full-text index
<pre>ALTER TABLE article ADD FULLTEXT index_content(content)</pre>
(3) Create index directly
<pre>CREATE FULLTEXT INDEX index_content ON article(content)</pre>
Four, shortcomings
1. Although the index greatly improves the query speed, it will also reduce the speed of updating the table, such as insert, update and delete the table. Because when updating the table, not only the data must be saved, but also the index file.
2. Index files that will occupy disk space when indexing. In general, this problem is not serious, but if you create multiple composite indexes on a large table, the index file will grow quickly.
Indexes are just a factor to improve efficiency. If there are tables with a large amount of data, you need to spend time researching and establishing the best indexes or optimizing query statements.
Five, matters needing attention
When using indexes, there are some tips and precautions:
1. The index will not contain columns with null values
As long as the column contains a null value, it will not be included in the index. As long as one column in the composite index contains a null value, then this column is invalid for the composite index. So we don't let the default value of the field be null when designing the database.
2. Use short indexes
To index the list, you should specify a prefix length if possible. For example, if there is a char(255) column, if the multi-value is unique within the first 10 or 20 characters, then do not index the entire column. Short index can not only improve query speed but also save disk space and I/O operations.
3. Index column sorting
The query uses only one index, so if the index is already used in the where clause, the column in the order by will not use the index. Therefore, do not use the sorting operation when the database default sorting can meet the requirements; try not to include multiple column sorting, if necessary, it is best to create a composite index for these columns.
4. Like statement operation
In general, it is not recommended to use the like operation. If it is necessary, how to use it is also a problem. Like "%aaa%" will not use index but like "aaa%" can use index.
5. Don't perform operations on columns
This will cause the index to fail and perform a full table scan, for example
<pre>SELECT * FROM table_name WHERE YEAR(column_name)<2017;</pre>
6. Do not use not in and <> operations
