MySQL Learning Note III (index)

Index

Database object index: A way to combine data, which can be quickly queried to specific records in a database object table by indexed objects, is the most common way to raise performance.

An index will contain one or more column fields in a table that are sorted in a certain order.

Index operations:

< Span style= "font-size:18px" >        CREATE INDEX, modify index, delete index.

/blockquote>

Database object indexes are primarily designed to increase the speed at which data is retrieved from a table. Because the data is stored in a database table, the index is created on the database object, consists of a field in the table or a number of fields generated by the keys, which are stored in the data structure (b-tree or hash table), MySQL can quickly and efficiently find the field associated with the key value. Depending on the storage type of the index, it can be divided into B-tree index and hash index

Note: The InnoDB and MyISAM storage engines support the Btree type index, and the memory storage engine supports hash type indexes, which are indexed by default.

The Database object index appears, in addition to provide the database management system's lookup speed, but also can guarantee the field uniqueness. Thus, the uniqueness of database tables is realized.

Six kinds of indexes: Normal index, unique index, full-text index, single-column index, multicolumn Index, spatial index

Advantages and Disadvantages

Lee: Improve search Speed

Cons: Too many indexes consume disk space

suitable for creating an index:

1. Fields that are frequently queried, that is, fields that appear

in the Where

2. Grouped fields, that is, fields that appear in group

by

3. A union query between the existence of a dependency and the parent table, that is, the primary key or foreign key field

4. Setting unique integrity Fields

scenarios that are not suitable for creation:

1. Fields that are seldom used in queries

2. A field with many duplicate values normal index

Create:

three different ways:

1. Create indexes when creating tables

2. Create an index on a table that already exists

3. Create an ALTER TABLE with an SQL statement

1. CREATE TABLE TableName (

column1 type,

column2 type,

....

index| KEY [IndexName] (columname [(length)] [asc| DESC])

 )
  specifies that the field is indexed by index or key.
  when you create an index, you can specify the length of the index. This is because different storage engines define the maximum number of indexes and the maximum index length
mysql supports a storage engine that 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

,

loc varchar,

index index_id (ID)

)
Then insert multiple records into the table (this is necessary if there is only one record, not the following), and then execute

Explain select * from T_index1 where id=1\g

then appears

possible key:index_id

key:index_id

indicates that the Index object for the ID is enabled

2. Create a normal index on a table that already exists

CREATE INDEX IndexName on tablename (column[(length)][asc|desc])

Create INDEX index_name on T_INDEX1 (name)
Verify:

Explain select * from T_index1 where id=1\g

typeface appears

possible Key:index_name

Key:index_name

3. Create a normal index from the SQL statement ALTER TABLE

ALTER TABLE tablename add Index|key indexname (columnname[(length)][asc|desc])

See also the two features of Create and alter. Will modifying the index use ALTER? Will deleting an index use drop? 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 the addition of the keyword unique to the normal index.

2. Create a unique index on a table that already exists
Try to write on your own

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

fully OK

3. Create an index from the SQL statement ALTER TABLE
ALTER TABLE tablename add unique Index|key indexname (columnname[(length)][asc|desc])

Full-Text indexing

A full-text index is primarily associated with fields that have a data type of char,varchar,text field, so that you can more quickly query a large number of string types
MySQL supports full-text indexing from version 3.23.23 and can only create full-text indexes on database tables that store type MyISAM.
By default, a full-text index is searched in a case-insensitive manner, and if the field associated with the full-text index is of the binary data type, it is performed in an uppercase and lowercase search.
Keyword: Fulltext index
Three ways to create the Index keyword as fulltext index is to create a full-text index

Multi-column index

When you create an index, the associated field is not a field, but multiple fields
Although queries can be made from the fields that are associated, a multicolumn index is used only if the query condition uses the first field in the associated field.
CREATE TABLE T_name (

column1 type,

column2 type,

...

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

)

There are two other ways to create the analogy.
Six kinds of indexes: Normal index, unique index, full-text index, single-column index, multicolumn Index, spatial index
There are altogether six kinds, here are three kinds.

Spatial Index Search on the internet

Spatial indexes are indexes on fields of spatial data types, and there are 4 types of spatial data in MySQL

.

are geometry, point, LINESTRING, POLYGON, respectively.

MySQL uses the spatial keyword to extend the syntax to create spatial indexes that can be used to create regular index types. To create a column for a spatial index, you must

Declare it as not NULL, and the spatial index can only be created in a table where the storage engine is MyISAM

CREATE TABLE index6 (

ID int,

space geometry not NULL,

Spatial index index6_sp (space)

);
All of the above are created indexes, have to be deleted if created

Delete Index

DROP Index indexname on tablename

The

syntax is simple.

MySQL Learning Note III (index)