MySQL – Index details

The ** index ** is used to quickly retrieve records with specific values. If there is no index, the database must perform a full table scan starting with the first record until it finds the relevant row. The more data there is, the more expensive it is to retrieve. If the table column has an index, MySQL can quickly reach the specified location to search the data file without having to look at all the data.

An overview of the

Indexes depend on the implementation of storage engines, so each storage engine may not have the same index, and each storage engine may not support all index types. All storage engines support at least 16 indexes per table, and the total index length is at least 256 bytes. Most storage engines have higher limits.

There are two storage types of indexes in MySQL: BTREE and HASH. The storage types depend on the storage engine of the table.

MyISAM and InnoDB storage engines only support BTREE indexes, while MEMORY/HEAP storage engines support HASH and BTREE indexes.

advantages

  • Speed up data query
  • A unique index ensures the uniqueness of each row of data in a database table
  • In terms of achieving referential integrity of data, you can speed up joins between tables
  • When using the grouping and sorting clauses for data queries, you can also significantly reduce the grouping and sorting time in the query

disadvantages

  • If there are a large number of indexes, index files may reach the maximum file size faster than data files (reasonable use, no problem).
  • Lossy performance (add, modify, delete) indexes need to be maintained dynamically

classification

Normal and unique indexes

  • Plain index: A basic index type in a database that allows the insertion of duplicate and null values in the column where the index is defined
  • ** Unique index: ** Index columns must have unique values, but empty values are allowed. A primary key index is a special kind of unique index that does not allow empty values (such as increment ids).

Single-column index and composite index

  • Single-column index: That is, an index contains only one column. A table can have multiple single-column indexes
  • Composite index: An index created on a combination of fields in a table. The index is used only when the left field of the field is used in the query condition

The full text indexing

  • Full-text index:A type ofFULLTEXTSupports full-text lookup of values on the columns where the index is defined, allowing the insertion of duplicate and null values in those index columns. Full-text indexes can be created on columns of type CHAR, VARCHAR, or TEXT,Only the MyISAM storage engine in MySQL supports full-text indexing

Design principles

Poor index design or lack of indexes can be a hindrance to database and application performance, and efficient indexes are important for good performance.

Matters needing attention

  1. More indexes is not always better. A large number of indexes in a table not only occupies disk space, but also affects the performance of the tableINSERT, DELETE, UPDATEAs the data in the table changes, the index is adjusted and updated
  2. Avoid designing too many indexes for frequently updated tables with as few columns as possible, and create indexes for fields that are often used in queries, but avoid adding unnecessary fields
  3. It is best not to use indexes for tables with small data volumes. Because of the small amount of data, the query may take less time than the index traversal, and indexes may not be optimized
  4. Index columns with more different values that are often used in conditional expressions. Do not index columns with fewer different values. For example, if the gender field is only male and female, there is no need to index. Indexing will not improve query efficiency, but will seriously reduce the update speed
  5. Specify a unique index when uniqueness is a characteristic of the data itself. Using unique indexes ensures the data integrity of the defined columns to speed up queries
  6. Indexes columns that are frequently sorted or grouped (that is, group by or order by operations), and composite indexes can be built on those columns if there are more than one column to be sorted

use

When you use CREATE TABLE to CREATE a TABLE, you can define the data type of the column as well as the primary key constraint, foreign key constraint, or unique constraint. Regardless of which constraint is created, it is equivalent to creating an index on the specified column at the same time.

The basic syntax for creating indexes 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]
Copy the code

paraphrase

  1. UNIQUE, FULLTEXT, and SPATIAL are optional parameters, representing UNIQUE index, full-text index, and SPATIAL index respectively
  2. INDEX and KEY are synonyms for creating an INDEX
  3. Col_name is the column for which the index is to be created. This column must be selected from multiple columns defined in the data table
  4. Index_name is the name of the specified index. If this parameter is not specified, col_name is the index value by default
  5. Length is an optional parameter, indicating the length of the index. The index length can be specified only for a string field
  6. ASC or DESC specifies the ascending or descending index value store

Normal index

DROP TABLE IF EXISTS customer1 DROP TABLE IF EXISTS customer1; CREATE TABLE 'customer1' (' customer_id 'bigint(20) NOT NULL COMMENT' customer1 ', 'customer_name' varchar(30) DEFAULT NULL COMMENT 'customer_name' varchar(30) DEFAULT NULL COMMENT 'customer_name ', INDEX 'idx_customer_id' (' customer_id ') USING BTREE) ENGINE=InnoDB DEFAULT CHARSET= UTf8MB4 COMMENT=' customer_id ';Copy the code

test

SHOW INDEX FROM customer1; EXPLAIN SELECT * FROM customer1 WHERE customer_id = 1;Copy the code

paraphrase

EXPLAIN syntax is explained in detail in the next chapter, which focuses on indexes

  • Select_type: specifies the SELECT query type to use. The value is SIMPLE, indicating a SIMPLE SELECT without UNION or subquery. Other values include PRIMARY, UNION, SUBQUERY, and so on
  • Table: Specifies the name of the data table read by the database in the order in which it was read
  • Type: specifies the relationship between this table and other tables. The other values are system, const, eq_ref, ref, range, index, and All
  • Possible_keys: Possible indexes that MySQL can use when searching data records
  • Key: the actual index used by MySQL
  • Key_len: specifies the length of the index in bytes. The smaller the value of key_len, the faster it is
  • Ref: Provides the name of the column in the other table in the association
  • Rows: 指MySQLThe number of rows of data expected to be read from the current table when the query is executed
  • Extra: Provides information about associated operations

SHOW INDEX FROM syntax

  • Table: indicates the table for creating indexes
  • Non_unique: indicates that the index is not unique. 1 indicates that the index is not unique, and 0 indicates that the index is unique
  • Key_name: indicates the name of the index
  • Seq_in_index: indicates the position of the field in the index. The value of the single-column index is changed to 1. The combined index is the order defined for each field in the index
  • Column_name: indicates the column field that defines the index
  • Sub_part: indicates the index length
  • Null: indicates whether the field can be Null
  • Index_type: indicates the index type

If possible_keys and key are idx_customer_id, indexes are used in the query

The only index

A single-column index is an index created on a field in a table. Multiple single-column indexes can be created on a table. The previous two examples are all single-column indexes, such as:

DROP TABLE IF EXISTS customer1; CREATE TABLE 'customer1' (' customer_id 'BIGINT (20) NOT NULL COMMENT' customer1 ', 'customer_name' VARCHAR (30) DEFAULT NULL COMMENT 'customer_name' VARCHAR (30) DEFAULT NULL COMMENT 'customer_name ', INDEX 'idx_customer_id' (' customer_id ') USING BTREE) ENGINE = INNODB DEFAULT CHARSET = utf8MB4 COMMENT = 'utf8mb4 ';Copy the code

This means that a unique index named IDx_Customer_id has been created on the customer_ID field of the table

Composite index

A composite index creates an index on multiple fields, such as:

DROP TABLE IF EXISTS customer1; CREATE TABLE 'customer1' (' customer_id 'BIGINT (20) NOT NULL COMMENT' customer1 ', 'customer_name' VARCHAR (30) DEFAULT NULL COMMENT '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 = 'table '; SHOW INDEX FROM customer1;Copy the code

Select idx_group_customer FROM customer1, customer_name FROM customer1, customer_id FROM customer1, customer_name FROM customer1, customer_name FROM customer1, customer_name FROM customer1. You will see two records (with images)

The full text indexing

Full-text indexing can search the full TEXT, only MyISAM storage engine supports full-text indexing, and only CHAR, VARCHAR, and TEXT columns. The index is always for the whole column, not for local indexes, such as:

DROP TABLE IF EXISTS customer1; CREATE TABLE 'customer1' (' customer_id 'BIGINT (20) NOT NULL COMMENT' customer1 ', 'customer_name' VARCHAR (255) DEFAULT NULL COMMENT 'customer_name' VARCHAR (255) FULLTEXT INDEX `idx_fulltext_customer_name` (`customer_name`) ) ENGINE = MyISAM DEFAULT CHARSET = utf8mb4 COMMENT = 'Customer table '; SHOW INDEX FROM customer1;Copy the code

Since the default storage engine is InnoDB and full text index only supports MyISAM, you need to specify the engine manually when creating tables here.

This creates a FULLTEXT FULLTEXT index named IDx_FULLTEXt_customer_name on the INFO field. FULLTEXT indexes are good for large databases, but may be less useful for small data sets

Create indexes on existing tables

To CREATE an INDEX on an existing TABLE, you can use the ALTER TABLE statement or CREATE INDEX statement, so explain how to use the ALTER TABLE and CREATE INDEX statements respectively to CREATE an INDEX on a given TABLE field.

The ALTER TABLE syntax

ALTER TABLE create index;

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

Normal index

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

ALTER TABLE customer1 ADD INDEX idx_customer_id(customer_name(50));
Copy the code

When querying, only the first 50 characters need to be retrieved. If you can specify a prefix length as long as possible, for example, a CHAR(255) column, then you do not need to index the entire column if the number of digits is unique within the first 10 or 30 characters. Short indexes can not only improve query speed, but also save disk space and reduce I/O operations.

The only index

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

Composite index

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

The CREATE TABLE syntax

MySQL > ALTER TABLE CREATE INDEX; ALTER TABLE CREATE INDEX; ALTER TABLE CREATE INDEX;

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

See and ALTER INDEX statement syntax is essentially the same, put customer1 below table removed to CREATE again, all of the fields are no INDEX, CREATE an INDEX using the CREATE INDEX statement:

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`);
Copy the code

Remove the index

The last task is to DROP the INDEX. You can use ALTER TABLE and DROP INDEX to DROP the INDEX.

The ALTER TABLE syntax

ALTER TABLE syntax:

ALTER TABLE table_name DROP EXISTS index_name;

ALTER TABLE table_name DROP INDEX IF EXISTS index_name;
Copy the code

I suggest you use number two

DROP the INDEX of grammar

The basic syntax of DROP INDEX is:

DROP INDEX index_name ON table_name

DROP INDEX IF EXISTS  index_name ON table_name
Copy the code

I suggest you use number two

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