"Database" MySQL database index

article attribution:http://feiyan.info/16.html, I want to write to myself, but found this June summed up very detailed. Moved right over here.

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:

Mysql> SELECT Id,from_unixtime (time) from article WHERE a.title= ' test title '

View Code

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:

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

View Code

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.

– Creating indexes directly create index index_name on table (column (length)) – Modify the table structure by adding an index ALTER TABLE table_name ADD index index_name on (colum n (length)) – CREATE table at the same time creating the index created table ' table ' (' id ' int (one) not NULL auto_increment, ' title ' char (255) CHARACTER SET UTF8 CO Llate utf8_general_ci not NULL, ' content ' text CHARACTER SET UTF8 COLLATE utf8_general_ci null, ' time ' int (ten) null Defaul T NULL, PRIMARY KEY (' id '), index index_name (title (length))) – Delete the index drop, index index_name on table

View Code

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.

– Creating unique index create unique index indexname on table (column length) – Modify table structure ALTER TABLES table_name ADD UNIQUE indexname on (column ( Length) – Specify CREATE TABLE ' table ' (' id ' int (one) not NULL auto_increment, ' title ' char (255) CHARACTER SET UTF8 Collat when creating tables) E utf8_general_ci not NULL, ' content ' text CHARACTER SET UTF8 COLLATE utf8_general_ci null, ' time ' int (ten) null DEFAULT NU LL, PRIMARY KEY (' id '), UNIQUE IndexName (title (length)));

View Code

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.

– CREATE table that is appropriate to add full-text Indexing create    table ' table ' (    ' id ' int (one) not NULL auto_increment,    ' title ' char (255) CHARACTER SET UT F8 COLLATE utf8_general_ci not NULL,    ' content ' text CHARACTER SET UTF8 COLLATE utf8_general_ci 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 creating    Fulltext Index Index_content on article (content)

View Code

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:

– Use the index 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;

View CodeOptimization of MySQL Index

The benefits of using indexes are described above, but excessive use of indexes will result in abuse. Therefore, the index also has its drawbacks: Although the index greatly improves query speed, it also slows down the updating of tables, such as INSERT, update, and delete on tables. Because when updating a table, MySQL not only saves the data, but also saves the index file. Index files that create indexes that consume disk space. The general situation is not too serious, but if you create multiple combinations of indexes on a large table, the index file will swell up quickly. Indexing is just one factor in efficiency, and if your MySQL has a large data size table, you need to spend time studying to build the best indexes, or refine the query statements. Here are some tips and optimizations for summarizing and collecting MySQL indexes.

1. When do I use a clustered or nonclustered index?

Action Stroke	using a clustered index	using nonclustered indexes
columns are often sorted by group	use	use
to return data in a range	use	do not use
One or very few different values	do not use	do not use
Small number of different values	use	do not use
large number of different values	do not use	use
frequently updated columns	do not use	use
foreign key column	using	using
primary key column	using	using
frequently modifying indexed columns	do not use	use

In fact, we can understand the above table through examples of the previous clustered index and the definition of a nonclustered index. For example, to return data in a range. For example, if you have a table with a time column and you have the aggregate index in that column, you will be very fast when you query the entire data from January 1, 2004 to October 1, 2004, because your dictionary body is sorted by date, clustering The index only needs to find the beginning and end data in all the data to be retrieved, rather than a nonclustered index, you must first look up the page number for 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 later development of the project slowly learn.

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 there is a column in the composite index that contains null values. So we don't want the default value of the field to be null when the database is designed.

3. Using a short index

Index A string, or specify a prefix length if possible. For example, if you have a column of char (255), and if the majority value is unique within the first 10 or 20 characters, do not index the entire column. Short indexes not only improve query speed but also save disk space and I/O operations.

4. Index column Sorting

The MySQL query uses only one index, so if an index is already used in the WHERE clause, the column in order by is not indexed. So do not use sort operations where the default sorting of the database is acceptable, and try not to include multiple columns, if you need to create a composite index for those columns.

5. Like statement operations

It is generally discouraged to use the like operation, which is also an issue if it is not used. Like "%aaa%" does not use the index and like "aaa%" can use the index.

6. Do not perform calculations on columns

For example: SELECT * from the users where year (adddate) <2007 will be performed on each row, which 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 the MySQL performance loss.

Finally, MySQL uses the index only for the operator: <,<=,=,>,>=,between,in, and sometimes like (not in the case of a wildcard% or _). In theory each table can create up to 16 indexes, but unless the amount of data is really many, 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.

"Database" MySQL database index