1. Declaration and use of index
1.1 classification of index
MySQL indexes include general index, unique index, full-text index, single column index, multi column index and spatial index.
In terms of functional logic, there are four kinds of indexes: ordinary index, unique index, primary key index and full-text index.
According to the physical implementation, indexes can be divided into two types: clustered index and non clustered index.
According to the number of action fields, it is divided into single column index and joint index.
1. General index
2. Unique index
3. Primary key index
4. Single column index
5. Multi column (combined, combined) index
6. Full text index
7. Supplement: spatial index
Summary: different storage engines support different index types. InnoDB supports indexes such as B-tree and full text, but not Hash
Indexes; MyISAM: supports B-tree, full text and other indexes, but does not support Hash indexes; Memory: support B-tree, Hash, etc
Index, full text index is not supported; NDB: support Hash index, but not B-tree, full text and other indexes; Archive: no
B-tree, Hash, full text and other indexes;
1.2 index creation
1. Create an index when creating a table
give an example:
CREATE TABLE dept (
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR ( 20 ) );
CREATE TABLE emp ( emp_id INT PRIMARY KEY AUTO_INCREMENT, emp_name VARCHAR ( 20 ) UNIQUE, dept_id INT, CONSTRAINT emp_dept_id_fk FOREIGN KEY ( dept_id ) REFERENCES dept ( dept_id ) );
However, if you create an index when explicitly creating a table, the basic syntax format is as follows:
CREATE TABLE table_name [ col_name data_type ] [ UNIQUE | FULLTEXT | SPATIAL ] [ INDEX | KEY ] [ index_name ] ( col_name [ length ] ) [ ASC | DESC]
- UNIQUE, FULLTEXT and SPATIAL are optional parameters, representing UNIQUE index, full-text index and SPATIAL index respectively;
- INDEX and KEY are synonyms. They have the same function and are used to specify INDEX creation;
- index_name specifies the name of the index. It is an optional parameter. If not specified, MySQL defaults to col_name is the index name;
- col_name is the field column to be indexed, which must be selected from multiple columns defined in the data table;
- Length is an optional parameter, which indicates the length of the index. Only string type fields can specify the index length;
- ASC or DESC specifies the storage of index values in ascending or descending order.
1. Create a general index
Year in book table_ Establish a common index on the publication field. The SQL statement is as follows:
CREATE TABLE book ( book_id INT, book_name VARCHAR ( 100 ), AUTHORS VARCHAR ( 100 ), info VARCHAR ( 100 ), COMMENT VARCHAR ( 100 ), year_publication YEAR, INDEX ( year_publication ) );
2. Create a unique index
give an example:
CREATE TABLE test1 ( id INT NOT NULL, NAME VARCHAR ( 30 ) NOT NULL, UNIQUE INDEX uk_idx_id ( id ) );
After the statement is executed, use SHOW CREATE TABLE to view the table structure:
SHOW INDEX FROM test1 \G
3. Primary key index
When set as the primary key, the database will automatically establish an index. innodb is a clustered index. Syntax:
- Index with table:
CREATE TABLE student (
id INT ( 10 ) UNSIGNED AUTO_INCREMENT,
student_no VARCHAR ( 200 ),
student_name VARCHAR ( 200 ),
PRIMARY KEY ( id ) );
- Delete primary key index:
ALTER TABLE student
drop PRIMARY KEY ;
- Modify the primary key index: you must drop the original index before creating an index
4. Create a single column index
- give an example:
CREATE TABLE test2(
id INT NOT NULL, name CHAR(50) NULL,
INDEX single_idx_name(name(20)) );
After the statement is executed, use SHOW CREATE TABLE to view the table structure:
SHOW INDEX FROM test2 \G
5. Create composite index
For example: create table test3, and establish a combined index on the id, name and age fields in the table. The SQL statement is as follows:
CREATE TABLE test3(
id INT(11) NOT NULL, name CHAR(30) NOT NULL,
age INT(11) NOT NULL, info VARCHAR(255),
INDEX multi_idx(id,name,age) );
After the statement is executed, use SHOW INDEX to view:
SHOW INDEX FROM test3 \G
6. Create full-text index
Example 1: create table test4, and establish a full-text index on the info field in the table. The SQL statement is as follows:
CREATE TABLE test4 ( id INT NOT NULL, NAME CHAR ( 30 ) NOT NULL, age INT NOT NULL, info VARCHAR ( 255 ), FULLTEXT INDEX futxt_idx_info ( info ) ) ENGINE = MyISAM;
|
On mysql5 7 and later versions can not specify the last ENGINE, because InnoDB supports full-text indexing in this version.
|
Example 2:
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR ( 200 ),
body TEXT,
FULLTEXT INDEX ( title, body )
) ENGINE = INNODB;
Create a table that adds a full-text index to the title and body fields.
Example 3:
CREATE TABLE `papers` ( `id` INT ( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT, `title` VARCHAR ( 200 ) DEFAULT NULL, `content` text, PRIMARY KEY ( `id` ), FULLTEXT KEY `title` ( `title`, `content` ) ) ENGINE = MyISAM DEFAULT CHARSET = utf8;
Queries different from like:
SELECT * FROM papers WHERE content LIKE '%Query string%';
Full text search refers to match+against query:
SELECT * FROM papers WHERE MATCH(title,content) AGAINST ('Query string');
|
Attention
1. Find out the version support before using full-text index;
2. Full text indexing is N times faster than like +%, but there may be accuracy problems;
3. If a large amount of data is needed for full-text indexing, it is recommended to add data first and then create the index.
|
7. Create spatial index
During the creation of spatial index, the field of spatial type must be non empty.
For example: create table test5 and create a spatial index on the field with spatial type of GEOMETRY. The SQL statement is as follows:
CREATE TABLE test5 (
geo GEOMETRY NOT NULL,
SPATIAL INDEX spa_idx_geo ( geo )
) ENGINE = MyISAM;
2. Create an index on an existing table
To create an index in an existing table, you can use the ALTER TABLE statement or the CREATE INDEX statement.
1. Use the ALTER TABLE statement to create an index. The basic syntax of creating an index with the ALTER TABLE statement is as follows:
ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name[length],...) [ASC | DESC]
2. Use CREATE INDEX to create an index. CREATE INDEX statement can add an index to an existing table. In MySQL, CREATE INDEX is mapped to an ALTER TABLE statement. The basic syntax structure is
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name ON table_name (col_name[length],...) [ASC | DESC]
1.3 delete index
1. Use ALTER TABLE to delete an index. The basic syntax format of ALTER TABLE to delete an index is as follows:
ALTER TABLE table_name DROP INDEX index_name;
2. Use the DROP INDEX statement to delete the index. The basic syntax format of DROP INDEX to delete the index is as follows:
DROP INDEX index_name ON table_name;
|
When prompted to delete a column in a table, if the column to be deleted is part of the index, the column will also be deleted from the index. If composition
If all columns of the index are deleted, the entire index will be deleted.
|
2. MySQL8.0 index new features
2.1 support descending index
For example: create data table ts1 in MySQL version 5.7 and MySQL version 8.0 respectively. The results are as follows:
CREATE TABLE ts1(a int,b int,index idx_a_b(a,b desc));
View the structure of data table ts1 in MySQL version 5.7. The results are as follows:
2.1 support descending index
For example: create data table ts1 in MySQL version 5.7 and MySQL version 8.0 respectively. The results are as follows:
CREATE TABLE ts1(a int,b int,index idx_a_b(a,b desc));
View the structure of data table ts1 in MySQL version 5.7. The results are as follows:
As can be seen from the results, the index is still the default ascending order.
View the structure of data table ts1 in MySQL version 8.0. The results are as follows:
From the results, we can see that the index is in descending order. Next, continue to test the performance of the descending index in the execution plan.
Insert 800 pieces of random data into the data table ts1 of MySQL version 5.7 and MySQL version 8.0 respectively. The execution statement is as follows:
DELIMITER // CREATE PROCEDURE ts_insert ( ) BEGINDECLARE i INT DEFAULT 1; WHILE i < 800 DO INSERT INTO ts1 SELECT rand( ) * 80000, rand( ) * 80000; SET i = i + 1; END WHILE; COMMIT; END // DELIMITER; #call CALL ts_insert();
View the execution plan of data table ts1 in MySQL version 5.7. The results are as follows:
EXPLAIN SELECT * FROM ts1 ORDER BY a,b DESC LIMIT 5;
It can be seen from the results that the number of scans in the execution plan is 799, and Using filesort is used.
|
Tip: Using filesort is a slow external sort in MySQL. It is best to avoid it. In most cases, the administrator
You can optimize the index to avoid Using filesort as much as possible, so as to improve the execution speed of the database.
|
View the execution plan of data table ts1 in MySQL version 8.0. As can be seen from the results, the number of scans in the execution plan is 5, and Using filesort is not used.
|
Note that the descending index is only valid for the specific sort order in the query. If it is not used properly, the query efficiency will be lower. For example, the above
The query sorting condition is changed to order by a desc, b desc, and the execution plan of MySQL 5.7 is significantly better than that of MySQL 8.0.
|
After modifying the sorting condition to order by a desc and B DESC, let's compare the effect of execution plan in different versions. View the execution plan of data table ts1 in MySQL version 5.7. The results are as follows:
EXPLAIN SELECT * FROM ts1 ORDER BY a DESC,b DESC LIMIT 5;
View the execution plan of data table ts1 in MySQL version 8.0.
It can be seen from the results that the execution plan of modified MySQL 5.7 is significantly better than that of MySQL 8.0
2.2 hidden index
In MySQL version 5.7 and before, indexes can only be deleted explicitly. At this time, if an error occurs after deleting the index, you can only create the deleted index back by explicitly creating the index. If the amount of data in the data table is very large, or the data table itself is relatively large
Large, this operation will consume too many resources of the system, and the operation cost is very high.
From MySQL 8.0 X starts to support invisible indexes. You only need to set the index to be deleted as a hidden index so that the query optimizer will no longer use this index (even if force index is used, the optimizer will not use this index). After confirming that the index is set as a hidden index, the system will not receive any response, and then you can completely delete the index. This method of setting the index to hide and then deleting the index is soft deletion.
1. When creating a table, create it directly. Create a hidden index in MySQL through the SQL statement INVISIBLE. Its syntax is as follows:
CREATE TABLE tablename ( propname1 type1 [ CONSTRAINT1 ], propname2 type2 [ CONSTRAINT2 ], ...... propnamen typen, INDEX [ indexname ] ( propname1 [ ( length ) ] ) INVISIBLE );
The above statement has one more keyword INVISIBLE than the ordinary index, which is used to mark the index as INVISIBLE.
2. Create an existing table
You can set hidden indexes for existing tables. The syntax is as follows:
CREATE INDEX indexname ON tablename(propname[(length)]) INVISIBLE;
3. Create through ALTER TABLE statement
The syntax is as follows:
ALTER TABLE tablename
ADD INDEX indexname (propname [(length)]) INVISIBLE;
4. Switch the visible state of an index. For an existing index, you can switch the visible state through the following statement:
ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; #Switch to hidden index
ALTER TABLE tablename ALTER INDEX index_name VISIBLE; #Switch to non hidden index
If index_ Switch the CNAME index to the visible state. Check the execution plan through explain and find that the optimizer has selected index_cname index.
Note that when the index is hidden, its content is still updated in real time as the normal index. If an index needs to be hidden for a long time, it can be deleted, because the existence of the index will affect the performance of insertion, update and deletion.
By setting the visibility of hidden indexes, you can see the help of indexes on tuning
5. Make hidden indexes visible to the query optimizer
In MySQL 8 In version x, a new test method is provided for the index. You can turn on a setting through a switch of the query optimizer (use_invisible_indexes) to make the hidden index visible to the query optimizer. If use_ invisible_ When indexes is set to off (the default), the optimizer ignores hidden indexes. If set to on, the optimizer will consider using the hidden index when generating the execution plan even if the hidden index is not visible.
(1) Execute the following command on the MySQL command line to view the switch settings of the query optimizer.
mysql> select @@optimizer_switch \G
Find the following attribute configuration in the output result information.
use_invisible_indexes=off
The configuration value of this property is off, indicating that the hidden index is invisible to the query optimizer by default.
(2) To make the hidden index visible to the query optimizer, you need to execute the following commands on the MySQL command line:
mysql> set session optimizer_switch="use_invisible_indexes=on";
Query OK, 0 rows affected (0.00 sec)
If the SQL statement is executed successfully, check the switch settings of the query optimizer again.
mysql > SELECT @@optimizer_switch \ G *************************** 1.ROW *************************** @@optimizer_switch : index_merge = ON, index_merge_union = ON, index_merge_sort_union = ON, index_merge_ intersection = ON, engine_condition_pushdown = ON, index_condition_pushdown = ON, mrr = ON, mrr_co st_based = ON, block_nested_loop = ON, batched_key_access = off, materialization = ON, semijoin = ON, loosescan = ON, firstmatch = ON, duplicateweedout = ON, subquery_materialization_cost_based = ON, use_index_extensions = ON, condition_fanout_filter = ON, derived_merge = ON, use_invisible_ind exes = ON, skip_scan = ON, hash_join = ON 1 ROW IN SET ( 0.00 sec )
At this time, you can see the following attribute configuration in the output result.
