MySQL Index summary----MySQL index type and create

With regard to the benefits of MySQL indexing, if the correct design and use of the indexed MySQL is a Lamborghini, then no design and use of the index of MySQL is a human tricycle. For tables that do not have an index, a single-table query might be a bottleneck for hundreds of thousands of of data, whereas large web sites can typically produce hundreds of thousands of or even millions of of the data in one day, and no index queries can become very slow. Or in WordPress, many of its data tables will be indexed to fields that are frequently queried, such as the wp_comments table that designs Btree indexes for 5 fields.

A simple contrast test

With the data I tested last year as a simple example, more than 20 data sources randomly generated 2 million data, the average data source is repeated about 100,000 times, the table structure is relatively simple, contains only a self-increment ID, a char type, a text type and an int type, a single table 2 g size, Use the Myiasm engine. Start the test without adding any indexes.

Execute the following SQL statement:

1	mysql> SELECTid,FROM_UNIXTIME(time) FROM article WHEREa.title=‘测试标题‘

The time required for a query is scary, and if you add a federated query and some other constraints, the database consumes memory crazily and affects the execution of the front end program. Then add a btree index to the title field:

1	mysql> ALTERTABLE article ADD INDEX index_article_title ONtitle(200);

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

The concept of MySQL indexing

An index is a special kind of file (an index on a InnoDB data table is an integral part of a table space), and they contain reference pointers to all records in the datasheet. More generally, the database index is like a directory in front of a book, which can speed up the database query. The SQL statement above, in the absence of an index, the database will traverse all 200 data after the selection of eligible, and after the corresponding index, the database will be directly in the index to find eligible options. If we replace the SQL statement with "SELECT * from article WHERE id=2000000", do you want the database to give you the result after reading 2 million rows of data sequentially or to locate it directly in the index? The two images above have been given an answer (note: The general database will be indexed by default for the primary key).

The index is divided into clustered index and non-clustered index, and the clustered index is in order according to the physical location of the data, and the non-clustered index is different; The clustering index can improve the speed of multi-row retrieval, but the non-clustered index is very fast for the single-line retrieval.

Types of MySQL Indexes

1. General Index

This is the most basic index, it has no restrictions, such as the previous index created for the title field is a normal index, myiasm in the default index of the Btree type, but also in most cases we use the index.

01

–直接创建索引

02	CREATEINDEX index_name ONtable(column(length))

03	–修改表结构的方式添加索引

04	ALTERTABLE table_name ADD INDEX index_name ON(column(length))

05	–创建表的时候同时创建索引

06	CREATETABLE`table` (

07	`id` int(11) NOTNULLAUTO_INCREMENT ,

"	' title '   ; char character  set  utf8   collate  utf8_general_ci  not  null  ,

09	`content` text CHARACTERSET utf8 COLLATE utf8_general_ci NULL,

10	`time` int(10) NULLDEFAULT NULL,

11	PRIMARYKEY(`id`),

12	INDEXindex_name (title(length))

13

)

14

–删除索引

15	DROPINDEX index_name ONtable

2. Unique index

Like a normal index, the difference is that the value of the indexed column must be unique, but it allows for a null value (note differs from the primary key). If it is a composite index, the combination of column values must be unique, similar to the creation method and the normal index.

01

–创建唯一索引

02	CREATEUNIQUE INDEX indexName ONtable(column(length))

03

–修改表结构

04	ALTERTABLE table_name ADD UNIQUE indexName ON(column(length))

05	–创建表的时候直接指定

06	CREATETABLE`table` (

07	`id` int(11) NOTNULLAUTO_INCREMENT ,

08	`title` char(255) CHARACTERSET utf8 COLLATE utf8_general_ci NOT NULL,

09	`content` text CHARACTERSET utf8 COLLATE utf8_general_ci NULL,

10	`time` int(10) NULLDEFAULT NULL,

11	PRIMARYKEY(`id`),

12	UNIQUEindexName (title(length))

13

);

3. Full-Text indexing (fulltext)

MySQL supports full-text and full-text indexing starting from version 3.23.23, fulltext indexes are available only for MyISAM tables; they can be created as part of a CREATE TABLE statement from char, varchar, or text columns, or subsequently using the ALTER TABLE or CREATE index is added. For larger datasets, enter your data into a table without a Fulltext index, and then create an index that is faster than entering the data into an existing Fulltext index. But remember, for a large data table, generating a full-text index is a very expensive way to consume hard disk space.

01	–创建表的适合添加全文索引

02	CREATETABLE`table` (

03	`id` int(11) NOTNULLAUTO_INCREMENT ,

"	' title '   ; char character  set  utf8   collate  utf8_general_ci  not  null  ,

05	`content` text CHARACTERSET utf8 COLLATE utf8_general_ci NULL,

06	`time` int(10) NULLDEFAULT NULL,

07	PRIMARYKEY(`id`),

08	FULLTEXT (content)

09

);

10	–修改表结构添加全文索引

11	ALTERTABLE article ADDFULLTEXT index_content(content)

12

–直接创建索引

13	CREATEFULLTEXT INDEX index_content ONarticle(content)

4. Single-column index, multicolumn index

Multiple single-column indexes differ from the query effect of a single multicolumn index because MySQL can use only one index when executing a query, and one of the most restrictive indexes is selected from multiple indexes.

5. Combined index (leftmost prefix)

Usually use the SQL query statements generally have more restrictive conditions, so in order to further extract the efficiency of MySQL, we should consider the establishment of a composite index. For example, the previous table establishes a composite index for title and time: ALTER Table Article ADD index Index_titme_time (title (), Time (10)). Creating such a composite index is actually equivalent to establishing the following two sets of composite indexes:

–title,time

–title

Why is there no such combination index as time? This is because the MySQL composite index is the result of the "leftmost prefix". The simple understanding is only from the left to the beginning of the combination. Not all queries that contain these two columns will use the combined index, as shown in the following SQL:

1

–使用到上面的索引

2	SELECT* FROM article WHREE title=‘测试‘ ANDtime=1234567890;

3	SELECT* FROMarticle WHREE utitle=‘测试‘;

4

–不使用上面的索引

5	SELECT* FROMarticle WHREE time=1234567890;

MySQL Index summary----MySQL index type and create