Use of the index of MySQL notes
- 2020-05-17 06:39:15
- OfStack
An index is a structure that is created on a table to sort the values of one or more columns in a database table
Its main function is to improve the query speed and reduce the performance cost of the database system
Through the index, the query data does not have to read all the information of the record to match, but only the index column
An index is the equivalent of a lexicon in which a word can be found when it is queried
Then jump directly to where the 1 is, without having to start on the first page of the dictionary, and match word for word
tips: indexes can improve query speed, but they are sorted by index when inserting records, thus reducing the insertion speed
The best way to do this is to drop the index first, insert a large number of records, and then create the index
The index classification
1. Normal index: can be created in any data type without any restrictions
2. Exclusive index: the index can be set as a unique index using the unique parameter. When creating an index, the value of the index must be limited to only 1, and the primary key is a kind of exclusive index
3. Full-text index: you can use fulltext parameter to set the index as full-text index. Full-text indexes can only be created on fields of type char, varchar, or text. When querying a string type field with a large amount of data, the effect is obvious. But only the MyISAM storage engine supports full-text retrieval
4. Single-column index: an index created on a single field in a table. A single-column index can be of any type, as long as the index corresponds to only one field
5. Multi-column index: an index created on multiple fields in a table that points to multiple fields corresponding to the time of creation
6. Spatial index: you can use spatial parameter to set the index as a spatial index, which can only be built on the spatial data type such as geometry, and cannot be empty. Currently, only MyISAM storage engine supports it
The index is created when the table is created
Create a normal index
mysql> create table index1(
-> id int,
-> name varchar(20),
-> sex boolean,
-> index(id)
-> );
Query OK, 0 rows affected (0.11 sec)
Here you create an index on the id field, which is available for viewing on show create table
Create a unique index
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 be set without the exclusive 1 constraint, but the 1 index is useless
Create a full-text index
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 you can only use the MyISAM storage engine when creating full-text indexes
Create a single-column index
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 goal is to speed up queries without having to query all the information for character data
Create a multi-column index
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 sex field are used to create the index column
Create a spatial index
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 here that the space space field cannot be empty, and there is a storage engine
Create an index on an existing table
Create a normal index
mysql> create index index7_id on example0(id);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
An index named index7_id is created on the id field of the existing table
Create a unique index
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 just need to add unique before the index keyword
For the course_id field in the table, the most important thing to do is to set a one-only constraint
Create a full-text index
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 up the full-text engine, where the table must be the MyISAM storage engine
Create a single-column index
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, only 4 bytes are queried here, not all of them are required
Create a multi-column index
mysql> create index index11_na on example4(name,address);
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0
Once the index is created, the name field must be present in the query
Create a spatial index
mysql> create spatial index index12_line on example5(space);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
Note here that the storage engine is MyISAM, and also the spatial data type
Create the index with the alter table statement
Create a normal index
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
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 a full-text index
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
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 multi-column index
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
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 three operations, the establishment of each index category are listed
For indexes, it is important to understand the concept of indexes and the types of indexes
More is their own use experience
Finally, let's look at index deletes
Remove the index
mysql> create table index2(
-> id int unique,
-> name varchar(20),
-> unique index index2_id(id ASC)
-> );
Query OK, 0 rows affected (0.12 sec)
8
Here is the index you just created
Where index18_space is the index name and example11 is the table name