Use of indexes for MySQL notes

An index is a structure created on a table that sorts values of one or more columns in a database table.

The main function is to increase the query speed and reduce the performance overhead of the database system.

Through the index, the query data does not need to read all the information of the record for matching, but only queries the index Column

The index is equivalent to the audio sequence table in the dictionary. to query a word, you can find it in the audio sequence table.

Then jump directly to the location of the audio sequence, instead of turning it over from the first page of the dictionary.

Tips:Although indexes can increase the query speed, they are sorted by indexes when inserting records, reducing the insert speed.

The best operation is to delete the index first, insert a large number of records, and then create an index.

Index category

1. Normal indexes: without any restrictions, they can be created in any data type.

2. unique index: You can use the unique parameter to set the index as a unique index. When creating an index, the value of this index must be unique. The primary key is a unique index.

3. Full-text index: You can use the fulltext parameter to set the index to full-text index. Full-text indexes can only be created on char, varchar, or text fields. When querying string-type fields with a large data volume, the effect is obvious. However, only the MyISAM storage engine supports full-text retrieval.

4. Single-Column index: The index created on a single field in the table. The single-column index can be of any type, as long as the index only corresponds to one field

5. Multi-column index: The index created on multiple fields in the table. The index points to multiple fields corresponding to the creation.

6. spatial Index: You can use the spatial parameter to set the index as a spatial index. spatial indexes can only be created on spatial data types such as geometry and cannot be empty. Currently, only the MyISAM storage engine supports

Create an index when creating a table
Create a Common Index
Copy codeThe Code is as follows:
Mysql> create table index1 (
-> Id int,
-> Name varchar (20 ),
-> Sex boolean,
-> Index (id)
-> );
Query OK, 0 rows affected (0.11 sec)

Create an index on the id field. You can view the index in show create table.

Create a unique index
Copy codeThe Code is 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)

The id field is used to create an index named index2_id.

The id field does not have the uniqueness constraint, but the index does not work.

Create full-text index
Copy codeThe Code is 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)

Note that the full-text index can only be created using the MyISAM storage engine.

Create a single column Index
Copy codeThe Code is 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 length of the subject field is 30, and the index length is 10.

The purpose of this operation is to increase the query speed, so you do not need to query all the information for the sorted data.

Create multi-column Indexes
Copy codeThe Code is 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)

We can see that the name field and sex field are used to create an index column.

Create a spatial index
Copy codeThe Code is as follows:
Mysql> create table index6 (
-> Id int,
-> Space geometry not null,
-> Spatial index index6_sp (space)
->) Engine = MyISAM;
Query OK, 0 rows affected (0.07 sec)

Note that the space field cannot be empty, and the storage engine is also required.

Create an index on an existing table
Create a Common Index
Copy codeThe Code is as follows:
Mysql> create index index7_id on example0 (id );
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0

Here, an index named index7_id is created on the id field of the existing table.

Create a unique index
Copy codeThe Code is 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

You only need to add unique before the index keyword.

As for the course_id field in the table, you must also set the uniqueness constraint.

Create full-text index
Copy codeThe Code is 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. The table here must be the MyISAM storage engine.

Create a single column Index
Copy codeThe Code is 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. Here, only 4 bytes are queried, and not all queries are required.

Create multi-column Indexes
Copy codeThe Code is 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 the name field in the query to use it.

Create a spatial index
Copy codeThe Code is as follows:
Mysql> create spatial index index12_line on example5 (space );
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0

Note that the storage engine is MyISAM and there is a space data type.

Use the alter table statement to create an index
Create a Common Index
Copy codeThe Code is 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 codeThe Code is 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


Create full-text index
Copy codeThe Code is 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 codeThe Code is 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 multi-column Indexes
Copy codeThe Code is 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 codeThe Code is 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

At this point, the creation of each index category is listed in the three operation methods.

For indexes, it is important to understand the concept of indexes and the types of indexes.

More is your own experience

Finally, let's take a look at the index deletion.

Delete Index
Copy codeThe Code is 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

Index18_space indicates the index name and example11 indicates the table name.