index

Indexes are used to quickly find rows with a specific value in a column.

Without using an index, MySQL must start from the first record and then read the entire table until it finds the relevant row. The larger the table, the more time it takes. If the queried column in the table has an index, MySQL can quickly get to a point where it searches the middle of the data file without having to look at all the data.

Of course, it is not easy to have too many indexes. The more indexes are written, the slower the modification speed will be. Because when writing modified data, the index must also be modified.

Indexes are divided into clustered indexes and non-clustered indexes. Clustered indexes are ordered according to the physical location of data storage, but non-clustered indexes are different; clustered indexes can improve many The speed of row retrieval, while non-clustered index is fast for single row retrieval.

1. Ordinary index

This is the most basic index. It has no restrictions. For example, the index created for the title field above is a normal index. The default BTREE type index in MyIASM is also the index we use in most cases. .

–Create index directly

CREATE INDEX index_name ON table(column(length))
 –修改表结构的方式添加索引
 
 ALTER TABLE table_name ADD INDEX index_name ON (column(length))
 
 –创建表的时候同时创建索引
 
 CREATE TABLE `table` (
 
 `id` int(11) NOT NULL AUTO_INCREMENT ,
 
 `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
 
 `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
 
 `time` int(10) NULL DEFAULT NULL ,
 
 PRIMARY KEY (`id`),
 
 INDEX index_name (title(length))
 
 )

–Delete index

DROP INDEX index_name ON table

2. The unique index

is similar to the ordinary index, except that the value of the index column must be unique, but null values ​​are allowed (note and primary keys are different). If it is a combined index, the combination of column values ​​must be unique, and the creation method is similar to that of a normal index.

–创建唯一索引
 
 CREATE UNIQUE INDEX indexName ON table(column(length))
 
 –修改表结构
 
 ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))
 
 –创建表的时候直接指定
 
 CREATE TABLE `table` (
 
 `id` int(11) NOT NULL AUTO_INCREMENT ,
 
 `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
 
 `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
 
 `time` int(10) NULL DEFAULT NULL ,
 
 PRIMARY KEY (`id`),
 
 UNIQUE indexName (title(length))
 
 );

3. Full-text index (FULLTEXT)

MySQL supports full-text indexing and full-text retrieval starting from version 3.23.23. FULLTEXT index is only available For MyISAM tables; they can be created from CHAR, VARCHAR, or TEXT columns as part of a CREATE TABLE statement, or added later using ALTER TABLE or CREATE INDEX. ////For larger data sets, entering your data into a table without a FULLTEXT index and then creating the index is faster than entering the data into an existing FULLTEXT index. However, remember that for large-capacity data tables, generating full-text indexes is a very time-consuming and hard-disk space-consuming process.

– Create a table suitable for adding a full-text index

CREATE TABLE `table` (
 
 `id` int(11) NOT NULL AUTO_INCREMENT ,
 
 `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
 
 `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
 
 `time` int(10) NULL DEFAULT NULL ,
 
 PRIMARY KEY (`id`),
 
 FULLTEXT (content)
 
 );

– Modify the table structure to add a full-text index

ALTER TABLE article ADD FULLTEXT index_content(content)

– Create an index directly

CREATE FULLTEXT INDEX index_content ON article(content)

4. Single-column index, multi-column index

The query effect of multiple single-column indexes and a single multi-column index is different, because the execution When querying, MySQL can only use one index and will select the most restrictive index from multiple indexes.

5. Combined index (leftmost prefix)

The SQL query statements commonly used generally have many restrictions, so in order to further squeeze the efficiency of MySQL, it is necessary to consider establishing a combined index. For example, in the above table, a combined index is created for title and time: ALTER TABLE article ADD INDEX index_titme_time (title(50), time(10)). Establishing such a combined index is actually equivalent to establishing the following two sets of combined indexes:

–title, time

–title

Why is there no combined index like time? Woolen cloth? This is a result of the "leftmost prefix" of the MySQL composite index. The simple understanding is to only start the combination from the leftmost one. Not only queries containing these two columns will use the combined index, as shown in the following SQL:

–Using the above index

SELECT * FROM article WHREE title='测试' AND time=1234567890;
 
 SELECT * FROM article WHREE utitle='测试';

– Do not use the above index

SELECT * FROM article WHREE time=1234567890;