Article attribution: http://feiyan.info/16.html, I would like to summarize, but found this June summary of 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:
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.
02 |
CREATEINDEX index_name ONtable(column(length)) |
04 |
ALTERTABLE table_name ADD INDEX index_name ON(column(length)) |
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, |
12 |
INDEXindex_name (title(length)) |
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.
02 |
CREATEUNIQUE INDEX indexName ONtable(column(length)) |
04 |
ALTERTABLE table_name ADD UNIQUE indexName ON(column(length)) |
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, |
12 |
UNIQUEindexName (title(length)) |
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.
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, |
11 |
ALTERTABLE article ADDFULLTEXT index_content(content) |
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:
2 |
SELECT* FROM article WHREE title=‘测试‘ ANDtime=1234567890; |
3 |
SELECT* FROMarticle WHREE utitle=‘测试‘; |
5 |
SELECT* FROMarticle WHREE time=1234567890; |
Optimization 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 Description |
using a clustered index |
using nonclustered indexes |
| columns are often sorted by grouping |
using |
using |
| 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 the body of your dictionary is sorted by date, A clustered 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.
MySQL Index brief
