Detailed MySQL index summary----MySQL index type and create _mysql

With regard to the benefits of MySQL indexing, if the correct and reasonable design and use of the index MySQL is a Lamborghini, then there is no design and use of the index MySQL is a human tricycle. For tables with no indexes, a single table query may have hundreds of thousands of data that is the bottleneck, and usually a large web site may produce hundreds of thousands of or even millions of of the data, and no index queries will change very slowly. In WordPress's case, many of its data tables add indexes to fields that are often queried, such as the Wp_comments table, which 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, each data source is repeated about 100,000 times, the table structure is relatively simple, contains only a self ID, a char type, a text type and an int type, single table 2 g size, Use the Myiasm engine. The start test did not add any indexes.

Execute the following SQL statement:

The time required for the query is terrifying, and if combined with some other constraints, the database will consume memory crazily and affect the execution of front-end programs. Then add a btree index to the title field:

The comparison of the above query statement is very obvious:

The concept of MySQL indexing

An index is a special file (an index on a INNODB datasheet is an integral part of a tablespace) that contains a reference pointer to all records in the datasheet. More commonly, the database index is like a book in front of the directory, can speed up the query speed of the database. The SQL statement above, in the absence of an index, the database will traverse all 200 data to select the eligible, and after the corresponding index, the database will directly find the criteria in the index of the option. If we replace the SQL statement with the "SELECT * from article WHERE id=2000000", do you want the database to read 2 million rows of data in order to give you the result or locate it directly in the index? The above two pictures in sharp contrast have been given the answer (note: The general database by default will be the primary key generation index).

Index is divided into clustered index and nonclustered index, clustered index is based on the physical location of data storage, but not clustered index is different; clustered index can improve the speed of multiple-line retrieval, but not clustered index is fast for single line retrieval.

Types of MySQL Indexes

1. General Index

This is the most basic index, and it has no limitations, for example, the index created for the title field above is a normal index, the default btree type index in Myiasm, and the index in most cases.

 – Directly CREATE index 

create index_name on table (column (length)) 

-How the table structure is modified add index 

ALTER TABLE table_name ADD Index_name on (length) 

– Creates the index CREATE TABLE 

' table ' ( 


 ' id ' int () not NULL auto_increment while creating the tables, c7/> ' title ' char (255) CHARACTER set UTF8 COLLATE utf8_general_ci not NULL, 


 ' content ' text CHARACTER SET UTF8 COLLATE Utf8_general_ci null, 


 ' time ' int (ten) null DEFAULT null, 


 PRIMARY KEY (' id '), 


 INDEX index_name (title (Lengt h)) 

– Delete index 

DROP index index_name on table 

2. Unique index

Like a normal index, the value of an indexed column must be unique, but a null value (note is different from the primary key) is allowed. If it is a combined index, the combination of the column values must be unique, and the method created is similar to the normal index.

– Creating a unique index create 


 unique index indexname on table (column (length)) 


 -Modify table Structure 


 ALTER TABLE table_name ADD unique IndexName on (length) 


 – Specifies the CREATE TABLE 


 ' table ' ( 


 ' id ' int ') not NULL auto_increment directly when the table is created, 


 ' title ' char (255) CHARACTER set UTF8 COLLATE utf8_general_ci not NULL, 


 ' content ' text CHARACTER SET UTF8 COLLATE UTF8 _general_ci null, 


 ' time ' int (ten) null DEFAULT null, 


 PRIMARY KEY (' id '), 


 UNIQUE IndexName (title (length)) c12/>); 

3. Full-Text indexing (fulltext)

MySQL starts with version 3.23.23 for Full-text indexing and Full-text search, and fulltext indexes are available only for MyISAM tables; they can be created from a char, varchar, or text column as part of a CREATE TABLE statement, or subsequently use alter TABLE or CREATE index is added. For a larger dataset, enter your data into a table without a Fulltext index, and then create an index that is faster than entering the data into the existing Fulltext index. But it's important to remember that for large data tables, generating Full-text indexes is a very time-consuming way to consume hard disk space.

 – CREATE table's appropriate add Full-text index CREATE TABLE ' 
table ' ( 
' id ' int (one) not NULL auto_increment, 
' title ' char (255) CHARACTER SET UT F8 COLLATE utf8_general_ci NOT NULL, the 
' content ' text CHARACTER SET UTF8 COLLATE NULL, 
' time ' int ( Null DEFAULT null, 
PRIMARY KEY (' id '), 
Fulltext (content) 
 ); 
 – Modify table structure add full-text index 
ALTER table article add fulltext index_content (content) 
– Create index directly by creating 
Fulltext Index Index_content on article (content) 

4. Single column index, multiple column index

Multiple single-column indexes have different query effects than single, multiple-column indexes, because MySQL can only use one index when executing a query, and selects one of the most restrictive indexes from multiple indexes.

5. Combined index (leftmost prefix)

Usually used in the SQL query statements are generally more restrictive conditions, so in order to further squeeze the efficiency of MySQL, it is necessary to consider establishing a composite index. For example, in the previous table, a composite index is established for title and time: ALTER TABLE article ADD index Index_titme_time (title), Time (10)). Creating such a composite index is equivalent to creating the following two sets of combined indexes:

–title,time

–title

Why not have time such a combination index? This is because the MySQL composite index "leftmost prefix" results. The simple understanding is to just start from the left. The combined index is not used as long as the query that contains both columns, as shown in the following few sql:

 – Use the index of the above to 
SELECT * from article Whree title= ' test ' and time=1234567890; 
SELECT * from article Whree utitle= ' test ';

– Do not use the above index 
SELECT * from article Whree time=1234567890;

Optimization of MySQL Index

The benefits of using indexes are mentioned above, but excessive use of indexes can cause abuse. So the index has its drawbacks: while indexing greatly improves query speed, it reduces the speed at which tables are updated, such as INSERT, UPDATE, and delete tables. Because when you update the table, MySQL not only saves the data, but also saves the index file. Index files that create indexes that consume disk space. Generally this is not a serious problem, but if you create multiple combinations of indexes on a large table, the index files will swell up very quickly. Indexing is just a factor in improving efficiency, and if your MySQL has large data tables, it takes time to study the best indexes, or optimize the query statements. Here are some summaries and favorites for MySQL indexing Considerations and Optimization methods.

1. When do I use clustered or nonclustered indexes?

Action Description	Using Clustered Indexes	Using Nonclustered indexes
Columns are often sorted in groups	Use	Use
Returns data in a range	Use	Do not use
One or very few different values	Do not use	Do not use
A small number of different values	Use	Do not use
A large number of different values	Do not use	Use
frequently updated columns	Do not use	Use
FOREIGN key columns	Use	Use
Primary key columns	Use	Use
Frequently modify index columns	Do not use	Use

In fact, we can understand the table above by using examples of the definitions of the previous clustered and nonclustered indexes. For example, returns a range of data items. For example, if you have a table with a time column that happens when you set up the aggregate index in that column, this speed will be quick when you query all the data from January 1, 2004 to October 1, 2004, because the text of your dictionary is sorted by date, The clustering index only needs to find the beginning and end data in all the data to be retrieved, and unlike nonclustered indexes, you must first look up the page number of each item in the table of contents, and then find the specific content based on the page number. In fact, this specific usage I am not very understanding, can only wait for the late project development slowly learned.

2. The index does not contain columns with null values

This column is not valid for this composite index as long as the column contains null values that will not be included in the index, as long as one column in the composite index contains null values. So we don't want the default value of the field to be null when designing the database.

3. Using Short Index

Index A string column, if possible, to specify a prefix length. For example, if you have a column with char (255), if most values are unique within the first 10 or 20 characters, do not index the entire column. Short indexing can not only improve query speed but also save disk space and I/O operations.

4. Indexed column Sorting

The MySQL query uses only one index, so the columns in the order by are not indexed if the index is already used in the WHERE clause. Therefore, do not use sort operations when the database default sort meets the requirements, and try not to include sorting of multiple columns, preferably if you need to create a composite index for these columns.

5. Like statement operation

It is generally discouraged to use like operations, and how to use them is also a problem if not used. Like "%aaa%" does not use indexes and like "aaa%" can use indexes.

6. Do not perform operations on columns

For example: SELECT * from the users where year (adddate) <2007, which will operate on each row, will cause the index to fail with a full table scan, so we can change to: SELECT * from Users where adddate < ' 2007-01-01′. On this point can be onlookers: a single quotation mark caused by MySQL performance loss.

Finally, MySQL uses the index only for the operator: <,<=,=,>,>=,between,in, and sometimes like (without the wildcard% or _). In theory each table can create up to 16 indexes, but unless it is really a lot of data, otherwise too much use of the index is not so fun, such as I just for the text type of the field to create an index, the system almost stuck to death.

The above is the entire content of this article, I hope to help you learn, but also hope that we support the cloud habitat community.