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


Related articles: