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

The following is about the classification of MySQL index types. The secret of the text is that it is close to the topic. So, no gossip, let's go straight to the following, I believe you will benefit from reading this article on MySQL index type classification.

I. Classification of indexes 1, unique indexes and general indexes

Normal index: a basic index type in MySQL that 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. If it is a combined index, the combination of column values must be unique.

Primary key index: is a special unique index, no null values are allowed.

2. Single-column index and combined index

Single-column index: that is, an index contains only a single column, and 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 if the left field of these fields is used in the query criteria. Follow the leftmost prefix collection when using a combined index.

3. Full-text index (fulltext)

The full-text index type is FULLTEXT, and the support on the columns that define the index is worthy of full-text search, which is allowed in these index columns.

Duplicate and null values are inserted in the. A full-text index can be created on a column of type CHAR, VARCHAR, or TEXT. MySQL 5.7.xx previously only supported full-text indexing by the MyISAM storage engine.

4. Spatial index

A spatial index is an index built on fields of spatial data types. There are 4 spatial data types in MySQL, which are:

Geometry, point, linstring and polygon. MySQL extends with the SPATIAL keyword so that columns that can be used to create spatial indexes must be declared as NOT NULL. Similarly, before MySQL 5.7.xx, spatial indexes can only be created in tables where the storage engine is MyISAM.

5. Rules for creating indexes the more indexes are created, the better. If there are a large number of indexes in a table, it will not only take up disk space, but also affect the performance of statements such as insert, delete, update, etc. Because when the data in the table changes, the index is also adjusted and updated; it is best not to create an index for a table with a small amount of data, and because there is less data, the query may take longer than traversing the index; avoid creating indexes on frequently updated data. We should create indexes on fields that are often used in queries, create indexes on columns with more different values that are often used in conditional expressions, and create unique indexes when uniqueness is a characteristic of some data itself. index on columns that are sorted or grouped frequently, if there are multiple columns sorted, you can create a combined index Create the index while creating the table. 1. Create the general index mysql > create table book-> (- > bookid int,-> bookname varchar,-> authors varchar,-> info varchar,-> comment varchar,-> year_publication year,-> index (year_publication)->) Mysql > show create table book\ G * * 1. Row * * Table: bookCreate Table: CREATE TABLE `book` (`bookid` int (11) DEFAULT NULL, `bookname` varchar (255i) DEFAULT NULL, `authors`varchar (255i) DEFAULT NULL, `info` varchar (255i) DEFAULT NULL, `comment` varchar (255i) DEFAULT NULL `year_ publication` year (4) DEFAULT NULL, KEY `year_ publication` (`year_ publication`) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)

Use explain to determine whether the index is in use, as follows:

Mysql > explain select * from book where year_publication=1999\ G * * 1. Row * * id: 1 select_type: SIMPLE table: book partitions: NULL type: refpossible_keys: year_publication key: year_publication key_len: 2 ref: Const rows: 1 filtered: 100.00 Extra: Using index condition1 row in set 1 warning (0.00 sec) 2, unique index

The main reason for unique indexes is to reduce the execution time of query index column operations. Especially the comparison of relatively large data tables. Similar to a normal index, the difference is that the value of the index column must be unique, but null values are allowed. If it is a combined index, the combination of column values must be unique.

Mysql > create table T1 (- > id int not null,-> name char (30),-> unique index Uniqidx (id)->) Mysql > show create table T1\ gateway * 1. Row * * Table: t1Create Table: CREATE TABLE `t1` (`id` int (11) NOT NULL, `name` char (30) DEFAULT NULL, UNIQUE KEY `Uniqidx` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf83, single column index

Single-column index: an index created on a field in a data table, and multiple single-column indexes can be created in a table.

Mysql > create table T2-> (- > id int not null,-> name char (50) null,-> index singleidx (name)->) Mysql > show create table T2\ G * * 1. Row * * Table: t2Create Table: CREATE TABLE `t2` (`id` int (11) NOT NULL, `name` char (50) DEFAULT NULL, KEY `singleidx` (`name`) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec) 4, combined index

Composite index: creates an index on multiple fields. Follow the leftmost prefix principle. The leftmost prefix index matches the leftmost column of the index.

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)->) Mysql > show create table T3\ gateway * 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)

Note: a combined index can play the role of several indexes, but it is not easy to query which field can be used as an index. Instead, follow the leftmost prefix: use the leftmost set of columns in the index to match rows. Such a column set is called the leftmost prefix.

5. Full-text index

Full-text indexing: FULLTEXT, which can be used for full-text search, and supports CHAR\ VARCHAR and TEXT columns. The index is always on the entire column, does not support local indexes, and is suitable for table creation of large data.

Mysql > create table test8 (id int not null, title varchar, body varchar, fulltext index (body)) ENGINE=MyISAM Mysql > show create table T4\ Gateway * 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 `fullidx` (`info`) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec) mysql > insert into test8 values (2 WORDS OF WISDOM: Like they say in Asia, nobody should use a fork. Tradition evven dictates to "chop" all your forks and "stick" to the origiinal. '); Query OK, 1 row affected (0. 00 sec) mysql > insert into test8 values (1 In MySQL' database','In MySQL 8.0.17, we made an observation in the well-known TPC-H benchmark for one particular query') Query OK 1 row affected (0.00 sec) mysql > explain select * from test8 where match (body) against ('MySQL')\ Graph * 1. Row * * id: 1 select_type: SIMPLE table: test8 partitions: NULL type: fulltextpossible_keys: body Key: body key_len: 0 ref: const rows: 1 filtered: 100.00 Extra: Using where Ft_hints: sorted1 row in set, 1 warning (0.00 sec)

The data you see are as follows:

Note: in the way of creating the full-text index above, you can achieve the English full-text index, and each word is matched by space separation. if you want to achieve the Chinese full-text index, you need to add "with parser ngram" to bring the Chinese parser while creating the table.

The Chinese full-text index is created as follows:

Mysql > create table test10-> (- > id int not null,-> title varchar (50),-> info text,-> fulltext index (title,info) with parser ngram); mysql > insert into test10 values (1 'test', 'the organizer said that this exhibition will highlight the characteristics of strong interaction and high public participation, so that visitors can not only enjoy the visual feast, but also experience the joy of ice and snow sports.') ; Query OK, 1 row affected (0.00 sec) mysql > insert into test10 values (2) Zheng Gengsheng, secretary of the party group and vice chairman of the Chinese photographers Association, Xian Yunqiang, vice chairman of the Chinese photographers Association, and Wang Yuwen, consultant of the Chinese photographers Association) Query OK, 1 row affected (0.00 sec) mysql > explain select * from test10 where match (title Info) against ('China')\ China * 1. Row * * id: 1 select_type: SIMPLE table: test10 partitions: NULL type: fulltextpossible_keys: title key: title key_len : 0 ref: const rows: 1 filtered: 100.00 Extra: Using where Ft_hints: sorted1 row in set, 1 warning (0.00 sec)

The results obtained above are as follows:

6. Spatial index

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

Mysql > create table T5-> (- > g geometry not null,-> spatial index spaidx (g)->) engine=myisam Mysql > show create table T5\ gateway * 1. Row * * Table: t5Create Table: CREATE TABLE `t5` (`g` geometry NOT NULL SPATIAL KEY `spaidx` (`g`) ENGINE=MyISAM DEFAULT CHARSET=utf81 row in set (0.00 sec) 3. Create an index on an existing table 1 and add a unique index mysql > alter table book add unique index uniqidx (bookid) 2, add single-column index mysql > alter table book add index bkidex (comment (50)), 3, add full-text index mysql > alter table T6 add fulltext index infofulidx (info), 4, add composite index mysql > alter table book add index abc (authors (20), info), 5, add spatial index mysql > alter table T5 add spatial index spatidx (g) 4. Create indexes mysql > CREATE TABLE book1 (bookid INT NOT NULL, bookname VARCHAR (255) NOT NULL,authors VARCHAR (255) NOT NULL, info VARCHAR (255) NULL, comment VARCHAR (255) NULL,year_publication YEAR NOT NULL) on existing tables; Query OK, 0 rows affected (0.02 sec) 1, create general indexes mysql > create index bknameidex on book1 (bookname); 2. Create single-column indexes mysql > create index bkcmtidex on book1 (comment (50)) 3. Create a composite index mysql > create index bkauthandinfoidex on book1 (authors,info); 4) create a full-text index mysql > create fulltext index fullidx on book (info); 5) create a unique index mysql > create unique index uniqidx on book1 (bookid); 6) create a spatial index mysql > create table T7 (g geometry not null); mysql > create spatial index spaidx on T7 (g); 5. Delete an index

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

1. Check which indexes on some tables are mysql > show index from book\ G2, and delete the index mysql > alter table book drop index uniqidx with alter table.

Note: the unique index of the constraint field that added AUTO_INCREMENT cannot be deleted.

3. Delete with drop index

Mysql > drop index spaidx on T7

What are the relevant contents of the above MySQL index type classification, is there anything you don't understand? Or if you want to know more about it, you can continue to follow our industry information section.

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.