Mysql study Note 3 (INDEX), mysql study note Index

Mysql study Note 3 (INDEX), mysql study note Index
Index

Database Object Index: A combination of data. By indexing objects, you can quickly query specific records in the database object table. It is the most common way to improve performance.

An index contains one or more columns in the table in a certain order.

Index operation:

Create, modify, and delete indexes.

The database object index mainly aims to improve the data retrieval speed from the table. Because the data is stored in the database table, the index is created on the database object and consists of keys generated by one or more fields in the table, these keys are stored in the data structure (B-tree or hash table). Through MySql, you can quickly and effectively find the fields associated with the key value. According to the index storage type, it can be divided into B-type tree indexes and hash indexes.

Note: the InnoDB and MyISAM storage engines Support B-tree indexes, and the MEMORY storage engine supports Hash indexes. The default value is the former index.

The emergence of database object indexes not only provides the search speed of the database management system, but also ensures the uniqueness of fields. The database table is unique.

Six indexes: Common Index, unique index, full-text index, single-column index, multi-column index, and spatial index

Advantages and disadvantages:

Advantage: Improves search speed

Disadvantage: too many indexes occupy disk space

Suitable for index creation:

1. fields that are frequently queried, that is, fields that appear in the where clause

2. Grouping field, that is, the field that appears in group

3. Joint query between the word table and the parent table with dependency, that is, the primary key or foreign key field

4. Set unique integrity fields

Not suitable for creation:

1. Fields rarely used in queries

2. Common Index of fields with many duplicate values

Create:

Three methods:

1. Create an index when creating a table

2. Create an index on an existing table

3. Create an SQL statement in ALTER TABLE

1. create table tablename (

Column1 type,

Column2 type,

....

INDEX | KEY [indexname] (columname [(length)] [ASC | DESC])

)
Use index or key to specify a field as an index.
You can specify the index length when creating an index. This is because different storage engines define the maximum number of indexes and the maximum index length.
The storage engine supported by mysql supports at least 16 indexes for each table, with a total index length of at least 256 bytes.
Create table t_index1 (

Id int,

Name varchar (20 ),

Loc varchar (20 ),

Index index_id (id)

)
Insert multiple records to the table (this is required. If there is only one record, the following words cannot appear), and then execute

Explain select * from t_index1 where id = 1 \ G

Later

Possible key: index_id

Key: index_id

The index object of the id is enabled.

2. Create a common index on an existing table

Create index indexname on tablename (column [(length)] [asc | desc])

Create index index_name on t_index1 (name)
Verification:

Explain select * from t_index1 where id = 1 \ G

Appearance

Possible key: index_name

Key: index_name

3. Use the SQL statement alter table to create a common index

Alter table tablename add index | key indexname (columnname [(length)] [asc | desc])

We can also see the two functions of create and alter. Will alter be used to modify the index? Will drop be used to delete indexes? Look back

Unique Index

1. Create a unique index when creating a table
Create table tablename (

Column1 type,

Column2 type,

...

Unique index | key indexname (column [(length)] [asc | desc])

)

The so-called UNIQUE index is to add the keyword UNIQUE on a common index.

2. Create a unique index on an existing table
Try to write it by yourself

Create unique index indexname on tablename (columname [(length)] [asc | desc])

Completely OK

3. Create an index using the SQL statement alter table
Alter table tablename add unique index | key indexname (columnname [(length)] [asc | desc])

Full-text index

Full-text indexes are mainly associated with the char, varchar, and text fields to query string fields with a large data volume more quickly.
Mysql supports full-text indexing from version 3.23.23 and can only create full-text indexes on database tables with the storage type of myISAM.
By default, full-text indexes are searched in case-insensitive mode. If the fields associated with the full-text indexes are binary data types, the full-text indexes are searched in case-sensitive mode.
Keyword: fulltext index
The three creation methods keep up with the above. Writing the index keyword into fulltext index creates a full-text index.

Multi-column Index

When creating an index, the associated field is not a field, but multiple fields
Although the associated fields can be queried, the multi-column index is used only when the first field in the joined field is used in the query condition.
Create table t_name (

Column1 type,

Column2 type,

...

Index | key index_columni_columnj (columni [(length)] [asc | desc], columnj [(length)] [asc | desc])

)

The other two creation methods can be used.
Six indexes: Common Index, unique index, full-text index, single-column index, multi-column index, and spatial index
There are six types. Here we talk about three types.

Spatial indexes found on the Internet

Spatial indexes are indexes created for fields of the spatial data type. There are four spatial data types in MYSQL,

They are GEOMETRY, POINT, LINESTRING, and POLYGON.

MYSQL uses the SPATIAL keyword for extension so that it can be used to create a SPATIAL index by creating a regular index syntax. Required to create a spatial index Column

Declare it as not null. spatial indexes can only be created in tables with the storage engine MYISAM.

Create table index6 (

Id int,

Space geometry not null,

Spatial index index6_sp (space)

);
All of the preceding operations create an index and delete the index.

Delete Index

Drop index indexname on tablename

The syntax is simple.