Shulou(Shulou.com)06/01 Report--

The index is used to quickly find a row in a column that has a specific value. Without using an index, MySQL must read the entire table from record 1 until the relevant row is found. The larger the table, the more time the query takes. If the query column in the table has an index, MySQL can quickly get to a location to search for data files without having to view all the data.

8.1. Introduction to the index

The meaning and characteristics of Index

An index is a separate database structure stored on disk that contains reference pointers to all records in the data table. Using indexes is used to quickly find rows with a specific value in one or more columns. All MySQL column types can be indexed. Using indexes on related columns is the best way to speed up query operations.

Indexes are implemented in the storage engine, so the indexes of each storage engine are not necessarily the same, and each storage engine does not necessarily support all index types. Define the maximum number of indexes and maximum engine length for each table according to the storage engine. All storage engines support at least 16 indexes per table, with a total index length of at least 256 bytes. Most engines have higher limits. There are two storage types of indexes in MySQL: BTREE and HASH, depending on the storage engine of the table.

The main points of the index are:

Greatly accelerate the speed of data retrieval

Create a uniqueness index to ensure the uniqueness of each row of data in the database table

Connection between accelerometer and meter

When you use grouping and sorting clauses for data retrieval, you can significantly reduce the time for grouping and sorting in a query.

Classification of indexes

The indexes of MySQL can be divided into the following categories:

General index and unique index

A normal index is the basic index of MySQL, which allows duplicate and null values to 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. In the case of a composite index, the composite index of column values is unique. The primary key index is a special unique index, and null values are not allowed.

Single-column index and combined index

A single-column index means that an index contains only a single column, and a table can have multiple single-column indexes.

A combined index is an index created on a combination of fields in a table, and the index is used only if the left fields of these fields are used in the query criteria. Use a composite index to follow the leftmost prefix collection.

Full-text index

The full-text index type is FULLTEXT and supports full-text lookup of values on the columns that define the index, allowing duplicate and null values to be inserted in those index columns. A full-text index can be created on a column of type CHAR, VARCHAR, or TEXT.

Spatial index

Spatial index is an index of fields of spatial data types. There are four spatial data types in MySQL: GEOMETRY, POINT, LINESTRING and POLYGON. MySQL is extended with the SPATIAL keyword to make it possible to create spatial indexes in a syntax similar to that used to create regular indexes. The column that creates a spatial index must be declared as NOT NULL.

Design principles of index

Unreasonable index design or lack of indexes can hinder the performance of databases and applications. When designing an index, the guidelines that should be followed are:

The more indexes, the better.

Avoid too many indexes on frequently updated tables and have as few columns in the index as possible

It is best not to use indexes for tables with a small amount of data

Index on columns with more different values that are often used in conditional expressions

When uniqueness is the characteristic of the data itself, only the unique index of I

Index columns that are sorted or grouped frequently.

8.2. Create an index

MySQL supports several ways to create an index on a single column or multiple columns: specify an index column in the CREATE TABLE statement that creates a table, use the ALTER TABLE statement to create an index on an existing table, or use the CREATE INDEX statement to add an index on an existing table.

Create an index when you create a table

When you create a table using CREATE TABLE, you can define not only the data type of the column, but also the primary key constraint, foreign key constraint, or uniqueness constraint. No matter which constraint is created, defining the constraint is equivalent to creating an index on the specified column. The syntax for creating an index when creating a table is:

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

Create a normal index

The most basic type of index, which has no restrictions such as uniqueness, is only used to speed up the access to data.

Create a general index on the year_publication field in the book table

Mysql > CREATE TABLE book-> (- > bookid INT NOT NULL,-> bookname VARCHAR (255NOT NULL),-> authors VARCHAR (255NOT NULL),-> info VARCHAR (255NULL),-> comment VARCHAR (255NULL),-> year_publication YEAR NOT NULL,-> INDEX (year_publication)->) Query OK, 0 rows affected (0.02 sec) View table structure mysql > SHOW CREATE table book\ Graph * 1. Row * * Table: bookCreate Table: CREATE TABLE `book` (`bookid` int (11) NOT NULL, `bookname` varchar (255) NOT NULL, `authors`varchar (255) NOT NULL, `info` varchar (255) DEFAULT NULL `comment` varchar (255) DEFAULT NULL, `year_ publication` year (4) NOT NULL KEY `year_ publication` (`publication`) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec) to check whether the index is using mysql > explain select * from book where year_publication=190\ G * * 1. Row * * id: 1 select_type: SIMPLE table: Book type: refpossible_keys: year_publication key: year_publication key_len: 1 ref: const rows: 1 Extra: Using where1 row in set 1 warning (0.00 sec)

Select_type: specifies the type of SELECT query to be used, where the value is SIMPLE, which means simple SELECT, without using UNION or subqueries. Other possible values: PRIMARY, UNION, SUNQUERY.

Table: specifies the name of the data table read by the database, in the order in which it is read

Type: specifies the association between this data table and other data tables. Possible values are system, const, eq_ref, ref, range, index, and ALL.

Possible_keys: give each index that MySQL can choose when searching for data records

Key: the index actually selected by MySQL

Key_len: gives the length of the index in bytes. The smaller the value, the faster it is.

Ref: gives the name of the data column in another data table in the association relationship

Rows: the number of rows that MySQL is expected to read from this data table when executing this query

Extra: provides information about the associated operation

Create a unique index

The main reason for creating unique indexes is to reduce the execution time of query index column operations, especially for larger data tables. The value of the index column must be unique and null values are allowed.

Create a table T1 and use the UNIQUE keyword to create a unique index on the id field in the table

Mysql > CREATE TABLE T1-> (- > id INT NOT NULL,-> name CHAR (30) NOT NULL,-> UNIQUE INDEX UniqIdx (id)->) Query OK, 0 rows affected (0.03 sec) mysql > SHOW CREATE table T1\ Graph * 1. Row * * Table: t1Create Table: CREATE TABLE `t1` (`id` int (11) NOT NULL, `name` char (30) NOT NULL, UNIQUE KEY `UniqIdx` (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)

Create a single-column index

A single-column index is an index created on a field in a data table, and a table can create multiple single-column indexes.

Create a table T2 and create a single-column index on the name field in the table

Mysql > CREATE TABLE T2-> (- > id INT NOT NULL,-> name CHAR (50) NULL,-> INDEX SingleIdx (name (20))->) Query OK, 0 rows affected (0.04 sec) mysql > SHOW CREATE table T2\ Graph * 1. Row * * Table: t2Create Table: CREATE TABLE `t2` (`id` int (11) NOT NULL, `name` char (50) DEFAULT NULL, KEY `SingleIdx` (`name` (20)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)

Create a composite index

A composite index is to create an index on multiple fields.

Create table T3 and create a combined index on the id, name, and age fields in the table, with the following SQL statement:

Mysql > CREATE TABLE T3 (- > id INT NOT NULL,-> name CHAR (30) NOT NULL,-> age INT NOT NULL,-> info VARCHAR (255),-> INDEX MultiIdx (id,name,age)) Query OK, 0 rows affected mysql > SHOW CREATE table T3\ SHOW CREATE table * 1. Row * * Table: t3Create Table: CREATE TABLE `t3` (`id` int (11) NOT NULL, `name` char (30) NOT NULL, `age`int (11) NOT NULL, `info` varchar (255) DEFAULT NULL, KEY `MultiIdx` (`id`) `name`, `age`) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)

A combined index can act as several indexes, but instead of querying any field, it follows the "leftmost prefix": the leftmost column set in the index is used to match rows, which is called the leftmost prefix. For example, the index composed of id, name and age fields is stored in the order of id/name/age in the index row. The index can search for the following field combinations: (id, name, age), (id, name), or id. If the column does not form the leftmost prefix of the index, MySQL cannot use local indexes, such as (age) or (name,age) combinations cannot use index queries.

Check the index usage mysql > explain select * from T3 where id=1 AND name='joe'\ row * * id: 1 select_type: SIMPLE table: T3 type: refpossible_keys: MultiIdx key: MultiIdx Key_len: 94 ref: const Const rows: 1 Extra: Using where1 row in set (0.00 sec)

Create a full-text index

FULLTEXT can be used for full-text search. Only the MyISAM storage engine supports full-text indexing, and only indexes are created for CHAR, VARCHAR, and TEXT columns. The index is always on the entire column and does not support local (prefix) indexes.

Create table T4 and establish a full-text index on the info field in the table

Mysql > CREATE TABLE T4-> (- > id INT NOT NULL,-> name CHAR (30) NOT NULL,-> age INT NOT NULL,-> info VARCHAR (255),-> FULLTEXT INDEX FullTxtIdx (info)->) ENGINE=MyISAM Query OK, 0 rows affected (0.12 sec) mysql > SHOW CREATE table T4\ Graph * 1. Row * * Table: t4Create Table: CREATE TABLE `t4` (`id` int (11) NOT NULL, `name` char (30) NOT NULL, `age` int (11) NOT NULL, `info` varchar (255) DEFAULT NULL FULLTEXT KEY `FullTxtIdx` (`info`) ENGINE=MyISAM DEFAULT CHARSET=utf81 row in set (0.00 sec)

Create a spatial index

A spatial index must be created in a table of type MyISAM, and fields of type space must be non-empty.

Create table T5 and create a spatial index on fields of spatial type GEOMETRY

Mysql > CREATE TABLE T5-> (g GEOMETRY NOT NULL, SPATIAL INDEX spatIdx (g)) ENGINE=MyISAM Query OK, 0 rows affected (0.02 sec) mysql > SHOW CREATE table T5\ Graph * 1. Row * * Table: t5Create Table: CREATE TABLE `t5` (`g` geometry NOT NULL, SPATIAL KEY `spatIdx` (`g`)) ENGINE=MyISAM DEFAULT CHARSET=utf81 row in set (0.00 sec)

Create an index on an existing table

Create an index using the ALTER TABLE statement

The basic syntax for ALTER TABLE to create an index is:

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

Create a normal index named BkNameIdx on the bookname field in the book table

Before adding an index View the index created in the specified table: mysql > SHOW INDEX FROM book\ Graph * 1. Row * * Table: book Non_unique: 1 Key_name: year_publication Seq_in_index: 1 Column_name: year_publication Collation: a Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: 1 row in set (0.00 sec) mysql > ALTER TABLE book ADD INDEX BkNameIdx (bookname (30)) Query OK 0 rows affected (0.19 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > SHOW INDEX FROM book\ G * * 1. Row * * Table: book Non_unique: 1 Key_name: year _ publication Seq_in_index: 1 Column_name: year_publication Collation: a Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: * * 2. Row * * Table : book Non_unique: 1 Key_name: BkNameIdx Seq_in_index: 1 Column_name: bookname Collation: a Cardinality: 0 Sub_part: 30 Packed: NULL Null: Index_type: BTREE Comment: Index_comment: 2 rows in set (0.00 sec)

Table: the table that creates the index

Non_unique: indicates that the index is not unique, 1 for non-unique index, and 0 for unique index

Key_name: the name of the index

Seq_in_index: the position of the field in the index, the single-column index value is 1, and the combined index is the order of each field in the index definition

Column_name: column fields that define the index

Sub_part: the length of the index

Null: whether this field can be empty

Index_type: index type

Create a unique index named UniqidIdx on the bookId field of the book table

Mysql > ALTER TABLE book ADD UNIQUE INDEX UniqidIdx (bookId) Query OK 0 rows affected (0.20 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > SHOW INDEX FROM book\ Graph * 1. Row * * Table: book Non_unique: 0 Key_name: UniqidIdx Seq_in_index: 1 Column_name: bookid Collation: a Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment:...

Create a single-column index on the comment field of the book table

Mysql > ALTER TABLE book ADD INDEX BkcmtIdx (comment (50)) Query OK 0 rows affected (0.19 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > SHOW INDEX FROM book\ G... * * 4. Row * * Table: book Non_unique: 1 Key_name: BkcmtIdx Seq_ In_index: 1 Column_name: comment Collation: a Cardinality: 0 Sub_part: 50 Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: 4 rows in set (0.00 sec)

Create a combined index on the authors and info fields of the book table

Mysql > ALTER TABLE book ADD INDEX BkAuAndInfoIdx (authors (20), info (50)) Query OK 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > SHOW INDEX FROM book\ G... * * 5. Row * * Table: book Non_unique: 1 Key_name: BkAuAndInfoIdx Seq_in_index: 1 Column_name: authors Collation: a Cardinality: 0 Sub_part: 20 Packed: NULL Null: Index_type: BTREE Comment: Index_comment: * 6. Row * * Table: book Non_unique: 1 Key_name: BkAuAndInfoIdx Seq_in_index: 2 Column_name: info Collation: a Cardinality: 0 Sub_part: 50 Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: 6 rows in set (0.00 sec)

Create table T6 and create a full-text index using ALTER TABLE on T6 table

Mysql > CREATE TABLE T6-> (- > id INT NOT NULL,-> info CHAR (255)->) ENGINE=MyISAM;Query OK, 0 rows affected (0.02 sec) mysql > ALTER TABLE T6 ADD FULLTEXT INDEX infoFTIdx (info) Query OK 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > SHOW index from T6\ Graph * 1. Row * * Table: T6 Non_unique: 1 Key_name: infoFTIdx Seq_in_index: 1 Column_name: info Collation: NULL Cardinality: NULL Sub_part: NULL Packed: NULL Null: YES Index_type: FULLTEXT Comment: Index_comment: 1 row in set (0.00 sec)

Create table T7 and create a spatial index named spatIdx on the spatial data type field g of T7

Mysql > CREATE TABLE T7 (g GEOMETRY NOT NULL) ENGINE=MyISAM;Query OK, 0 rows affected (0.02 sec) mysql > ALTER TABLE T7 ADD SPATIAL INDEX spatIdx (g) Query OK 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > SHOW index from T7\ Graph * 1. Row * * Table: T7 Non_unique: 1 Key_name: spatIdx Seq_in_index: 1 Column_name: G Collation: a Cardinality: NULL Sub_part: 32 Packed: NULL Null: Index_type: SPATIAL Comment: Index_comment: 1 row in set (0.00 sec)

Create an index using CREATE INDEX

The CREATE INDEX statement can add an index to an existing table, and CREATE INDEX is mapped to an ALTER TABLE statement in MySQL. The basic syntax is as follows:

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name ON table_name (col_name [length],...) [ASC | DESC] create a data table bookmysql > CREATE TABLE book-> (- > bookid INT NOT NULL,-> bookname VARCHAR (255NOT NULL),-> authors VARCHAR (255NOT NULL),-> info VARCHAR (255NULL),-> comment VARCHAR (255NULL-> year_publication YEAR NOT NULL->); Query OK, 0 rows affected (0.03sec)

Create a normal index named BkNameIdx on the bookname field in the book table

Mysql > CREATE INDEX BkNameIdx ON book (bookname) Query OK 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > SHOW INDEX FROM book\ Graph * 1. Row * * Table: book Non_unique: 1 Key_name: BkNameIdx Seq_in_index: 1 Column_name: bookname Collation: a Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: 1 row in set (0.00 sec)

Create a unique index named UniqidIdx on the bookId field of the book table

Mysql > CREATE UNIQUE INDEX UniqidIdx ON book (bookId) Query OK 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > SHOW INDEX FROM book\ G * * 1. Row * * Table: book Non_unique: 0 Key_name: UniqidIdx Seq_in_index: 1 Column_name: bookid Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment:

Create a single-column index on the comment field of the book table

Mysql > CREATE INDEX BkcmtIdx ON book (comment (50)) Query OK 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > SHOW INDEX FROM book\ G * * 3. Row * * Table: book Non_unique: 1 Key_name: BkcmtIdx Seq_in_index: 1 Column_ Name: comment Collation: a Cardinality: 0 Sub_part: 50 Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: 3 rows in set (0.00 sec)

Create a combined index on the authors and info fields of the book table

Mysql > CREATE INDEX BkAuAndInfoIdx ON book (authors (20), info (50)) Query OK 0 rows affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > SHOW INDEX FROM book\ G * * 4. Row * * Table: book Non_unique: 1 Key_name: BkAuAndInfoIdx Seq_in_index: 1 Column_name: authors Collation: a Cardinality: 0 Sub_part: 20 Packed: NULL Null: Index_type: BTREE Comment: Index_comment: * * 5. Row * * Table: book Non _ unique: 1 Key_name: BkAuAndInfoIdx Seq_in_index: 2 Column_name: info Collation: a Cardinality: 0 Sub_part: 50 Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: 5 rows in set (0.00 sec)

Delete table T6, re-establish table T6, use the CREATE INDEX statement in the T6 table, and create a full-text index on the info field of type CHAR

Mysql > drop table T6 politics query OK, 0 rows affected (0.00 sec) mysql > CREATE TABLE T6-> (- > id INT NOT NULL,-> info CHAR (255)->) ENGINE=MyISAM;Query OK, 0 rows affected (0.02 sec) mysql > CREATE FULLTEXT INDEX fullIdx ON T6 (info) Query OK 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > SHOW INDEX FROM T6\ Graph * 1. Row * * Table: T6 Non_unique: 1 Key_name: fullIdx Seq_in_index: 1 Column_name: info Collation: NULL Cardinality: NULL Sub_part: NULL Packed: NULL Null: YES Index_type: FULLTEXT Comment: Index_comment: 1 row in set (0.00 sec)

Delete table T7, recreate table T7, use the CREATE INDEX statement in table T7, and create a spatial index named spatIdx on the spatial data type field g

Mysql > drop table T7 sec query OK, 0 rows affected (0.00 sec) mysql > CREATE TABLE T7 (g GEOMETRY NOT NULL) ENGINE=MyISAM;Query OK, 0 rows affected (0.01 sec) mysql > CREATE SPATIAL INDEX spatIdx ON T7 (g) Query OK 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > SHOW INDEX FROM T7\ Graph * 1. Row * * Table: T7 Non_unique: 1 Key_name: spatIdx Seq_in_index: 1 Column_name: G Collation: a Cardinality: NULL Sub_part: 32 Packed: NULL Null: Index_type: SPATIAL Comment: Index_comment: 1 row in set (0.00 sec)

8.3. Delete the index

Delete an index using ALTER TABLE

The basic syntax format of the ALTER TABLE delete index:

ALTER TABLE table_name FROP INDEX index_name

Delete a unique index named UniqidIdx in the book table

Mysql > SHOW CREATE table book\ Graph * 1. Row * * Table: bookCreate Table: CREATE TABLE `book` (`bookid` int (11) NOT NULL, `bookname` varchar (255) NOT NULL, `authors`varchar (255) NOT NULL, `info` varchar (255) DEFAULT NULL, `comment`varchar (255) DEFAULT NULL, `year_ publication` year (4) NOT NULL UNIQUE KEY `UniqidIdx` (`bookid`), KEY `BkNameIdx` (`bookname`), KEY `BkcmtIdx` (`comment` (50)), KEY `BkAuAndInfoIdx` (`authors` (20), `info` (50)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec) mysql > ALTER TABLE book DROP INDEX UniqidIdx Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > SHOW CREATE table book\ G * * 1. Row * * Table: bookCreate Table: CREATE TABLE `book` (`bookid` int (11) NOT NULL, `bookname` varchar (255) NOT NULL, `authors` varchar (255) NOT NULL `info` varchar DEFAULT NULL, `comment` varchar DEFAULT NULL, `year_ publication` year (4) NOT NULL, KEY `BkNameIdx` (`bookname`), KEY `BkcmtIdx` (`comment` (50)), KEY `BkAuAndInfoIdx` (`authors` (20), `info` (50)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)

A unique index that adds an AUTO_INCREMENT constraint field cannot be deleted.

Delete an index using DROP INDEX

The basic syntax for DROP INDEX to delete an index:

DROP INDEX index_name ON table_name

Delete the composite index named BkAuAndInfoIdx in the book table

Mysql > SHOW CREATE table book\ Graph * 1. Row * * Table: bookCreate Table: CREATE TABLE `book` (`bookid` int (11) NOT NULL, `bookname` varchar (255) NOT NULL, `authors`varchar (255) NOT NULL, `info` varchar (255) DEFAULT NULL, `comment`varchar (255) DEFAULT NULL, `year_ publication` year (4) NOT NULL KEY `BkNameIdx` (`bookname`), KEY `BkcmtIdx` (`comment` (50)), KEY `BkAuAndInfoIdx` (`authors` (20), `info` (50)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec) mysql > DROP INDEX BkAuAndInfoIdx ON book Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > SHOW CREATE table book\ G * * 1. Row * * Table: bookCreate Table: CREATE TABLE `book` (`bookid` int (11) NOT NULL, `bookname` varchar (255) NOT NULL, `authors` varchar (255) NOT NULL `info` varchar (255) DEFAULT NULL, `comment` varchar (255th) DEFAULT NULL, `year_ publication` year (4) NOT NULL, KEY `BkNameIdx` (`bookname`), KEY `BkcmtIdx` (`comment` (50)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.