Summary of mysql indexes--detailed introduction to mysql index types and creation

Regarding the benefits of MySQL indexes, if MySQL that is properly designed and uses indexes is a Lamborghini, then MySQL that is not designed and uses indexes is a human tricycle. For tables without indexes, a single table query may have hundreds of thousands of data, which is the bottleneck. Usually large websites may generate hundreds of thousands or even millions of data in a single day, and queries without indexes will become very slow. Taking WordPress as an example, its multiple data tables will add indexes to frequently queried fields. For example, the wp_comments table has a BTREE index designed for 5 fields.

A simple comparison test

Take the data I tested last year as a simple example. More than 20 data sources randomly generated 2 million pieces of data. On average, each data source repeated about 100,000 pieces. Secondly, the table structure is relatively simple, containing only an auto-incrementing ID, a char type, a text type and an int type. The single table is 2G in size and uses the MyIASM engine. Starting the test without adding any indexes.

Execute the following SQL statement:

mysql> SELECT id,FROM_UNIXTIME(time) FROM article WHERE a.title='测试标题'

The time required for the query is very scary. If you add joint queries and other constraints, the database will consume memory crazily. , and will affect the execution of the front-end program. At this time, add a BTREE index to the title field:

mysql> ALTER TABLE article ADD INDEX index_article_title ON title(200);

Execute the above query statement again, and the contrast is very obvious:

The concept of MySQL index

Indexes are special files (indexes on InnoDB data tables are a component of the table space) that contain reference pointers to all records in the data table. To put it more generally, a database index is like the table of contents at the front of a book, which can speed up database queries. In the above SQL statement, if there is no index, the database will traverse all 200 pieces of data and select the ones that meet the conditions; with the corresponding index, the database will directly search for the options that meet the conditions in the index. If we change the SQL statement to "SELECT * FROM article WHERE id=2000000", do you want the database to give you the results after reading 2 million rows of data in sequence, or to directly locate it in the index? The clear time comparison between the two pictures above already gives the answer (Note: Generally, databases will generate indexes for the primary key by default).

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.

Types of MySQL indexes

1. Ordinary index

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

01    –直接创建索引    
02    CREATE INDEX index_name ON table(column(length))    
03    –修改表结构的方式添加索引    
04    ALTER TABLE table_name ADD INDEX index_name ON (column(length))    
05    –创建表的时候同时创建索引    
06    CREATE TABLE `table` (    
07    `id` int(11) NOT NULL AUTO_INCREMENT ,    
08    `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,    
09    `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,    
10    `time` int(10) NULL DEFAULT NULL ,    
11    PRIMARY KEY (`id`),    
12    INDEX index_name (title(length))    
13    )    
14    –删除索引    
15    DROP INDEX index_name ON table

2. Unique index

# is similar to a normal 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.

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

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.

01    –创建表的适合添加全文索引    
02    CREATE TABLE `table` (    
03    `id` int(11) NOT NULL AUTO_INCREMENT ,    
04    `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,    
05    `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,    
06    `time` int(10) NULL DEFAULT NULL ,    
07    PRIMARY KEY (`id`),    
08    FULLTEXT (content)    
09    );    
10    –修改表结构添加全文索引    
11    ALTER TABLE article ADD FULLTEXT index_content(content)    
12    –直接创建索引    
13    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 query effect is different At this time, MySQL can only use one index and will select the most restrictive index from multiple indexes.

5. Composite index (leftmost prefix)

The SQL query statements commonly used generally have more restrictions, so in order to further squeeze the efficiency of MySQL, it is necessary to consider establishing a composite 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 SQLs:

1    –使用到上面的索引    
2    SELECT * FROM article WHREE title='测试' AND time=1234567890;    
3    SELECT * FROM article WHREE utitle='测试';    
4    –不使用上面的索引    
5    SELECT * FROM article WHREE time=1234567890;

The above is a summary of mysql indexes-mysql index types and detailed introduction to creation. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!