1. Declaration and use of index
1.1 classification of index
1.2 index creation
1. Create an index when creating a table
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 ) );
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
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
CREATE TABLE test1 ( id INT NOT NULL, NAME VARCHAR ( 30 ) NOT NULL, UNIQUE INDEX uk_idx_id ( id ) );
SHOW INDEX FROM test1 \G
3. Primary key index
- 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)) );
SHOW INDEX FROM test2 \G
5. Create composite index
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) );
SHOW INDEX FROM test3 \G
6. Create full-text index
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.
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR ( 200 ),
FULLTEXT INDEX ( title, body )
) ENGINE = INNODB;
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;
SELECT * FROM papers WHERE content LIKE '%Query string%';
SELECT * FROM papers WHERE MATCH(title,content) AGAINST ('Query string');
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
CREATE TABLE test5 (
geo GEOMETRY NOT NULL,
SPATIAL INDEX spa_idx_geo ( geo )
) ENGINE = MyISAM;
2. Create an index on an existing table
ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name[length],...) [ASC | DESC]
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name ON table_name (col_name[length],...) [ASC | DESC]
1.3 delete index
ALTER TABLE table_name DROP INDEX index_name;
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
CREATE TABLE ts1(a int,b int,index idx_a_b(a,b desc));
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.
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.
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.
2.2 hidden index
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;
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
mysql> select @@optimizer_switch \G
Find the following attribute configuration in the output result information.
(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.