MySQL-index details, mysql-Index

MySQL-index details, mysql-Index

IndexIs used to quickly retrieve records with specific values. If no index exists, the database must scan the entire table from the first record until relevant rows are identified. The more data you need, the higher the retrieval cost. If an index exists in a table column during retrievalMySQLYou can quickly search for data files at a specified location without having to view all the data.

Overview

Indexes rely on the implementation of the storage engine. Therefore, the indexes of each storage engine are not necessarily the same, and each storage engine does not necessarily support all index types. All storage engines Support at least 16 indexes for each table, with a total index length of at least 256 bytes. Most storage engines have a higher quota.

There are two storage types for indexes in MySQL:BTREE and HASH, Which is related to the table storage engine;

The MyISAM and InnoDB Storage engines only support BTREE indexes, and the MEMORY/HEAP storage engines Support HASH and BTREE indexes.

Advantages

• Accelerate Data Query

• Unique index to ensure the uniqueness of each row of data in the database table

• It can accelerate the connection between tables in terms of data reference integrity.

• When you use grouping and sorting clauses to query data, you can also significantly reduce the time for grouping and sorting in the query.

Disadvantages

• Disk Space is occupied. In addition to data tables occupying data space, each index occupies a certain amount of physical space. If a large number of indexes exist, index files may be faster than data files to reach the maximum file size (reasonable use, not a problem)

• Loss Performance (ADD, modify, and delete) indexes need to be dynamically maintained

Category

Common Index and unique index

• Common Index: the basic index type in the database. Duplicate and null values can be inserted in the columns that define the index.

• Unique index: the value of the index column must be unique, but null values are allowed. The primary key index is a special unique index and does not allow null values (such as auto-increment IDs)

Single Column index and combined index

• Single-Column index: an index contains only one column. A table can have multiple single-column indexes.

• Composite Index: refers to the index created on the combination of multiple fields in the table. The index is used only when the left fields of these fields are used in the query conditions.

Full-text index

• Full-text index: Type:FULLTEXTSupports full-text search of values in the columns that define the index. duplicate values and null values can be inserted in these index columns. Full-TEXT indexes can be created on CHAR, VARCHAR, or TEXT columns. in MySQL, only the MyISAM storage engine supports full-TEXT indexes.

Design principles

Improper index design or missing indexes will cause obstacles to the performance of databases and applications. Efficient indexes are very important for obtaining good performance.

Notes

1. The more indexes, the better. A large number of indexes in a table not only occupy disk space, but also affectINSERT、DELETE、UPDATEWhen the data in the table is changed, the index is also adjusted and updated.

2. Avoid designing too many indexes for frequently updated tables, and minimize the number of columns in the index. Instead, create indexes for fields that are frequently used for queries. avoid adding unnecessary fields.

3. It is recommended that you do not use indexes for tables with a small amount of data. Because the data is small, the query time may be shorter than the index Traversal Time, And the index may not produce optimization results.

4. Create an index for columns with different values that are frequently used in conditional expressions. Do not create an index for columns with fewer values. For example, gender fields are only male and female, there is no need to create an index. If an index is created, the query efficiency is not improved, but the update speed is greatly reduced.

5. When uniqueness is a feature of a data, it specifies a unique index. To use a unique index, you must ensure the data integrity of the defined columns to increase the query speed.

6. Index the columns that are frequently sorted or grouped (group by or order by Operation). If there are multiple columns to be sorted, you can create a composite index on these columns.

Use

When using create table to CREATE a TABLE, in addition to defining the data type of the column, you can also define primary key constraints, foreign key constraints, or uniqueness constraints, defining constraints is equivalent to creating an index on the specified column.

The basic syntax for creating an index when creating a table is as follows:

CREATE TABLE table_name[col_name data_type][UNIQUE|FULLTEXT|SPATIAL][INDEX|KEY][index_name](col_name[length])[ASC|DESC]

Meaning

1. UNIQUE, FULLTEXT, and SPATIAL are optional parameters, indicating the UNIQUE index, full-text index, and SPATIAL index, respectively.

2. The INDEX and KEY are used to specify the INDEX creation function.

3. Col_name is the field column for which you want to create an index. This column must be selected from multiple columns defined in the table.

4. Index_name is the name of the specified index, which is an optional parameter. If not specified, MySQL uses col_name as the index value by default.

5. Length is an optional parameter, indicating the index length. Only string fields can specify the index length.

6. ASC or DESC specifies the storage of index values in ascending or descending order

Common Index

-- This statement is used to delete the drop table if exists customer1; create table 'mermer1' ('customer _ id' bigint (20) not null comment 'customer id ', 'Customer _ name' varchar (30) default null comment 'customer name', INDEX 'idx _ mermer_id '('customer _ id') using btree) ENGINE = InnoDB default charset = utf8mb4 COMMENT = 'customer table ';

Test

-- View the INDEX information of the current table. show index from customer1; -- use EXPLAIN to analyze whether the SQL statement uses the INDEX EXPLAIN SELECT * FROM customer1 WHERE customer_id = 1;

Meaning

The next chapter of the EXPLAIN syntax will EXPLAIN in detail. The focus of this chapter is the index.

• Select_type: Specifies the SELECT query type used. The value here is SIMPLE, which indicates a simple select statement. UNION or subquery is not used. Other values include PRIMARY, UNION, SUBQUERY, etc.

• Table: Specifies the names of the data tables read by the database. They are sorted in the read order.

• Type: Specifies the relationship between the data table and other data tables. Other values include system, const, eq_ref, ref, range, index, and All.

• Possible_keys: The indexes available for MySQL to search for data records

• Key: actual index used by MySQL

• Key_len: the length of the index calculated by byte. The smaller the value of key_len, the faster the index is.

• Ref: provide the name of the data column in another data table in the association relationship.

• Rows: refersMySQLNumber of data rows expected to be read from the current data table during Query

• Extra: Provides information related to associated operations.

Show index from Syntax

• Table: The table for which the index is created.

• Non_unique: indicates that the index is not a unique index. 1 indicates that the index is not unique, and 0 indicates that the index is unique.

• Key_name: indicates the index name.

• Seq_in_index: indicates the position of the field in the index. The value of this field is 1 for a single index, and the composite index is the sequence defined by each field in the index.

• Column_name: column field that defines the index

• Sub_part: The index length.

• Null: indicates whether the field can be Null.

• Index_type: indicates the index type.

Whenpossible_keysAndkeyAllidx_customer_idThe index is used for query.

Unique Index

A single-column index is an index created on a field in a data table. Multiple Single-Column indexes can be created in a table. The indexes created in the preceding two examples are single-column indexes, for example:

Drop tableif exists customer1; create table 'mermer1' ('customer _ id' BIGINT (20) not null comment 'customer id', 'customer _ name' VARCHAR (30) default null comment 'customer name', unique index 'idx _ mermer_id '('customer _ id') using btree) ENGINE = innodb default charset = utf8mb4 COMMENT = 'customer table ';

In this way,customer_idA field namedidx_customer_idUnique index

Composite Index

A composite index is used to create an index on multiple fields. For example:

Drop tableif exists customer1; create table 'mermer1' ('customer _ id' BIGINT (20) not null comment 'customer id', 'customer _ name' VARCHAR (30) default null comment 'customer name', INDEX 'idx _ group_customer '('customer _ id', 'customer _ name') using btree) ENGINE = innodb default charset = utf8mb4 COMMENT = 'customer table'; show index from customer1;

This iscustomer_id、customer_nameTwo fields are successfully createdidx_group_customerThe composite indexSHOW INDEX FROM customer1;Two records are displayed (with the picture)

Full-text index

Full-TEXT indexes can be searched for the full TEXT. Only the MyISAM storage engine supports full-TEXT indexes, and only the CHAR, VARCHAR, and TEXT columns are supported. The index always applies to the entire column and does not support partial indexes. For example:

Drop tableif exists customer1; create table 'mermer1' ('customer _ id' BIGINT (20) not null comment 'customer id', 'customer _ name' VARCHAR (255) default null comment 'customer name', fulltext index 'idx _ fulltext_customer_name '('customer _ name') ENGINE = MyISAM default charset = utf8mb4 COMMENT = 'customer table '; show index from customer1;

Because the default storage engine isInnoDBThe full-text index only supportsMyISAMTherefore, you must manually specify the engine when creating a table.

As you can see, a name namedidx_fulltext_customer_nameFull-text index of FULLTEXT. Full-text index is very suitable for large databases. For small datasets, it may be of little use.

Create an index on an existing table

You can use the alter table statement or the create index statement to CREATE an INDEX on an existing TABLE.

Alter table syntax

The basic syntax for creating an index in alter table is:

ALTER TABLE table_name ADD [UNIQUE|FUUTEXT|SPATIAL][INDEX|KEY] [index_name] (col_name[length],...) [ASC|DESC]

Common Index

ALTER TABLE customer1 ADD INDEX idx_customer_id(`customer_id`);ALTER TABLE customer1 ADD INDEX idx_customer_id(customer_name(50));

This means that you only need to retrieve the first 50 characters during the query. Here we will specifically mention the index of string fields. If you can specify a prefix length as much as possible, for example, a CHAR (255) column, if multiple values are unique within the first 10 or the first 30 characters, you do not need to index the entire column, short indexes not only increase query speed, but also save disk space and reduce I/O operations.

Unique Index

ALTER TABLE customer1 ADD UNIQUE INDEX `idx_customer_id` (`customer_id`);

Composite Index

ALTER TABLE customer1 ADD INDEX `idx_group_customer` (`customer_id`,`customer_name`);

Create table syntax

The create index statement can be used to add an INDEX to an existing TABLE. in MySQL, the create index is mapped to an alter table statement. The basic syntax structure is as follows:

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name(col_name[length],...)[ASC|DESC]

The syntax is basically the same as that of the alter index statement.customer1After the table is deleted and created again, all fields have no indexes. Use the create index statement to CREATE an INDEX:

CREATE INDEX idx_customer_id ON customer1(`customer_id`);CREATE UNIQUE INDEX idx_customer_id ON customer1(`customer_id`);CREATE INDEX idx_group_customer ON customer1(`customer_id`,`customer_name`);

Delete Index

The last task is to delete the index. You can useAlter table and DROP INDEXDelete an index.

Alter table syntax

The basic syntax of alter table is:

ALTER TABLE table_name DROP EXISTS index_name;ALTER TABLE table_name DROP INDEX IF EXISTS index_name;

We recommend that you use Article 2.

Drop index syntax

The basic syntax of drop index is:

DROP INDEX index_name ON table_nameDROP INDEX IF EXISTS index_name ON table_name

We recommend that you use Article 2.

Note that when you delete a column in a table, if the column to be deleted is a part of the entire index, the column will also be deleted from the index; if all the columns that make up the index are deleted, the entire index will be deleted.

Architecture: 697579751