An index is a structure created on a table that sorts the values of one or more columns in a database table.
Its main function is to improve the speed of query and reduce the performance cost of database system.
Through the index, the query data does not have to read the entire information of the record to match, but only query the index column
The index corresponds to the Sequencer table in the dictionary, and you can find it in the Sequencer table to query a word.
Then jump directly to the location of the sequencer without having to start over from the first page of the dictionary and match verbatim.
tips: indexes can improve query speed, but are sorted by index when inserting records, thus reducing insertion speed
The best way to do this is to delete the index first, insert a large number of records, and then create the index
Index classification
1. Normal index: Do not attach any restrictions, you can create in any data type
2. Uniqueness Index: Use the unique parameter to set the index as a unique index, when the index is created, the value that restricts the index must be unique, and the primary key is a unique index
3. Full-text indexing: Use the fulltext parameter to set the index as a full-text index. A Full-text index can only be created on a field of char, varchar, or text type. The effect is obvious when querying a string type field with a large amount of data. But only the MyISAM storage engine supports Full-text search
4. Single-column Index: An index that is created on a single field in a table, and a single-column index can be any type, as long as the index only corresponds to one field
5. Multiple-column index: An index created on multiple fields in a table that points to multiple fields corresponding to creation
6. Spatial index: Use spatial parameter to set index as spatial index, spatial index can only be built on spatial data type such as geometry, and cannot be empty, currently only MyISAM storage engine support
Create an index when you create a table
Create a normal index
Copy Code code as follows:
Mysql> CREATE TABLE Index1 (
-> ID int,
-> name varchar (20),
-> Sex Boolean,
-> Index (ID)
->);
Query OK, 0 rows affected (0.11 sec)
Here, create an index on the ID field, show create table to view
Create a unique index
Copy Code code as follows:
Mysql> CREATE TABLE Index2 (
-> ID int Unique,
-> name varchar (20),
-> Unique index index2_id (ID ASC)
->);
Query OK, 0 rows affected (0.12 sec)
An index named index2_id is created here using the ID field
The ID field here can not set a uniqueness constraint, but the index has no effect
To create a Full-text index
Copy Code code as follows:
Mysql> CREATE TABLE Index3 (
-> ID int,
-> info varchar (20),
-> Fulltext Index Index3_info (info)
->) Engine=myisam;
Query OK, 0 rows affected (0.07 sec)
Be aware that you can only use the MyISAM storage engine when creating Full-text indexes
Create a Single-column index
Copy Code code as follows:
Mysql> CREATE TABLE Index4 (
-> ID int,
-> subject varchar (30),
-> index Index4_st (subject (10))
->);
Query OK, 0 rows affected (0.12 sec)
Here the Subject field length is 30, and the index length is 10
The purpose of this is to improve the speed of the query, for character-type data without querying all information
Create a multiple-column index
Copy Code code as follows:
Mysql> CREATE TABLE Index5 (
-> ID int,
-> name varchar (20),
-> Sex char (4),
-> index Index5_ns (name,sex)
->);
Query OK, 0 rows affected (0.10 sec)
As you can see, the Name field and the Sex field are used to create an indexed column
Create a spatial index
Copy Code code as follows:
Mysql> CREATE TABLE index6 (
-> ID int,
-> space geometry is not NULL,
-> Spatial Index INDEX6_SP (space)
->) Engine=myisam;
Query OK, 0 rows affected (0.07 sec)
We need to be aware of space spaces. field cannot be empty and storage engine
To create an index on a table that already exists
Create a normal index
Copy Code code as follows:
Mysql> CREATE index index7_id on EXAMPLE0 (ID);
Query OK, 0 rows affected (0.07 sec)
records:0 duplicates:0 warnings:0
This creates an index named INDEX7_ID on the ID field of an existing table.
Create a unique index
Copy Code code as follows:
Mysql> Create unique index index8_id on example1 (course_id);
Query OK, 0 rows affected (0.16 sec)
records:0 duplicates:0 warnings:0
Here you only need to precede the Index keyword with a unique
As for the course_id field in the table, you should also set the uniqueness constraint
To create a Full-text index
Copy Code code as follows:
Mysql> Create FULLTEXT Index index9_info on example2 (info);
Query OK, 0 rows affected (0.07 sec)
records:0 duplicates:0 warnings:0
The FULLTEXT keyword is used to set the Full-text engine, where the table must be a MyISAM storage engine
Create a Single-column index
Copy Code code as follows:
Mysql> CREATE index INDEX10_ADDR on Example3 (Address (4));
Query OK, 0 rows affected (0.16 sec)
records:0 duplicates:0 warnings:0
The length of the Address field in this table is 20, where only 4 bytes are queried and no queries are required
Create a multiple-column index
Copy Code code as follows:
Mysql> CREATE index Index11_na on example4 (name,address);
Query OK, 0 rows affected (0.16 sec)
records:0 duplicates:0 warnings:0
After the index is created, you must have a name field in the query to use the
Create a spatial index
Copy Code code as follows:
Mysql> Create spatial index index12_line on example5 (spaces);
Query OK, 0 rows affected (0.07 sec)
records:0 duplicates:0 warnings:0
Here you need to be aware that the storage engine is MyISAM, and that there are spatial data types
To create an index with the ALTER TABLE statement
Create a normal index
Copy Code code as follows:
Mysql> ALTER TABLE EXAMPLE6 add index Index13_n (name (20));
Query OK, 0 rows affected (0.16 sec)
records:0 duplicates:0 warnings:0
Create a unique index
Copy Code code as follows:
Mysql> ALTER TABLE Example7 add unique index index14_id (ID);
Query OK, 0 rows affected (0.20 sec)
records:0 duplicates:0 warnings:0
To create a Full-text index
Copy Code code as follows:
Mysql> ALTER TABLE Example8 ADD FULLTEXT index Index15_info (info);
Query OK, 0 rows affected (0.08 sec)
records:0 duplicates:0 warnings:0
Create a Single-column index
Copy Code code as follows:
Mysql> ALTER TABLE EXAMPLE9 Add index INDEX16_ADDR (address (4));
Query OK, 0 rows affected (0.16 sec)
records:0 duplicates:0 warnings:0
Create a multiple-column index
Copy Code code as follows:
Mysql> ALTER TABLE EXAMPLE10 Add index index17_in (id,name);
Query OK, 0 rows affected (0.16 sec)
records:0 duplicates:0 warnings:0
Create a spatial index
Copy Code code as follows:
Mysql> ALTER TABLE EXAMPLE11 add spatial index index18_space (space);
Query OK, 0 rows affected (0.06 sec)
records:0 duplicates:0 warnings:0
There are three ways to do this, and each type of index is set up to enumerate
For an index, it is important to understand the concept of the index, to understand the type of index
More of their own experience of using
Finally, take a look at the deletion of the index
Delete Index
Copy Code code as follows:
Mysql> DROP index index18_space on EXAMPLE11;
Query OK, 0 rows affected (0.08 sec)
records:0 duplicates:0 warnings:0
Here is an index just created
Where Index18_space is the index name, EXAMPLE11 is the table name
