The content of this chapter entirely based on the theoretical basis of the above, actually once you understand the mechanism behind the index, so choose high performance strategy becomes pure reason, and you can understand the logic behind these strategies.

the sample database

in order to discuss the index strategy, need a small amount of data is not database as an example. This paper choose MySQL in the official documentation provides one of the sample database: employees. This database relations complexity is moderate, and large amount of data. Below is the database e-r diagram (official quote from MySQL manual) :

height=655

 

Figure 12

the MySQL in the official documentation on the database page for http://dev.mysql.com/doc/employee/en/employee.html. In this database are introduced in detail, and provides a way to download address and import, if they are interested in import this to your MySQL database can refer to the content in the paper.

the left prefix principle and related optimized

the first condition of efficiently using the index is to know what kind of query is used to the index, the problem of a and B + Tree "principle of the leftmost prefix", through the examples below leftmost prefix principle.

here said the first joint index concept. In this paper, we are assuming the index only refers to the individual columns, in fact, the index of the MySQL can be referenced in a certain order multiple columns, this index is called joint index, in general, a joint index is an ordered tuples, where each element is a column for the data table, to strictly define indexes need to actually use the relational algebra, but here I don't want to talk too much and the topic of relational algebra, because that would seem to be very boring, so there is no longer strictly defined. Single-column index, moreover, can be regarded as joint index yuan a special case of the prime number is 1.

to employees. The titles table as an example, the following to check what are the index on its first:

SHOW INDEX FROM employees. Titles;
+ -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- +
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Null | Index_type | + -- -- -- --
-- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- +
| titles |                   0 | PRIMARY  |                       1 | emp_no           | A                |               NULL |           | BTREE          |
| titles |                   0 | PRIMARY  |                       2 | title            | A                |               NULL |           | BTREE          |
| titles |                   0 | PRIMARY  |                       3 | from_date     | A                |           443308 |           | BTREE          |
| titles |                   1 | emp_no     |                       1 | emp_no           | A                |           443308 |           | BTREE          |
+ -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- +

 

can the titles table from the results of the main indexes for , there is also a secondary index . In order to avoid multiple index makes things complicated SQL optimizer (MySQL's behavior is complicated when multiple index), here we will assist index drop off:

the ALTER TABLE employees. Titles DROP INDEX emp_no;

so you can concentrate on the behavior of the PRIMARY analysis index.

 

a: the whole match.

the EXPLAIN the SELECT * FROM employees. The titles WHERE emp_no='10001' AND the title='pro' Engineer 'AND from_date=' 1986-06-26 ';
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- +
| | id select_type | table  | type  | possible_keys | key        | key_len | ref                            | rows | Extra |
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- +
|   1 | SIMPLE          | titles | const | PRIMARY            | PRIMARY | 59           | const, const, const |       1 |             |
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- +

 

it is clear that when applied with all the columns IN the index exactly match (here refers to an exact match "=" or "IN"), the index can be used. One thing to note here, in theory, index is sensitive to the order, but as a result of the MySQL query optimizer will automatically adjust the where clause condition in order to use the suitable index, for example we are going to the conditions in the where clause order:

the EXPLAIN the SELECT * FROM employees. The titles WHERE from_date='1986-06-26' AND emp_no='10001' AND the title='pro' Engineer ';
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- +
| | id select_type | table  | type  | possible_keys | key        | key_len | ref                            | rows | Extra |
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- +
|   1 | SIMPLE          | titles | const | PRIMARY            | PRIMARY | 59           | const, const, const |       1 |             |
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- +

 

the effect is the same.

case 2: the leftmost prefix matching.

the EXPLAIN the SELECT * FROM employees. The titles WHERE emp_no='10001';
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- +
| | id select_type | table  | type | possible_keys | key        | key_len | ref    | rows | Extra |
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- +
|   1 | SIMPLE          | titles | ref  | PRIMARY            | PRIMARY | 4             | const |       1 |             |
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- +

 

when the exact match index to the left of the continuous query condition when one or more columns, such as or , and so can be used, but can only use part of the conditions of the leftmost prefix. The above query from the results of the analysis to the PRIMARY index, but key_len is 4, shows only the index in the first column of the prefix. ,

case 3: use the query condition index column exact matches, but a condition does not provide in the middle.

the EXPLAIN the SELECT * FROM employees. The titles WHERE emp_no='10001' AND from_date='1986-06-26';
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- +
| | id select_type | table  | type | possible_keys | key        | key_len | ref    | rows | Extra            |
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- +
|   1 | SIMPLE          | titles | ref  | PRIMARY            | PRIMARY | 4             | const |       1 | Using where |
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- +

 

index usage is the same as the situation 2 right now, because the title is not provided, so queries using only the first column of the index, and then the from_date has in the index, but because the title does not exist and can not connect and left prefix, so you need to scan the results to filter from_date (because the emp_no only here, so there is no scan). If want from_date also USES the index instead of the where filtering, you can add a secondary index , as the above query will use the index. In addition, you can also use a kind of call "isolation column" optimization method, the emp_no "pit" with from_date fill in. ,

we first look at the title there are several different value:

SELECT DISTINCT (title) FROM employees. The titles;
+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +
| title                          |
+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +
| empresa Engineer      |
| Staff                          |
| Engineer                    |
| empresa Staff            |
| Assistant Engineer |
| Technique Leader    |
| Manager                      |
+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +

 

only seven. IN this as the "pit" column values less cases, consider using "IN" to fill the "pit" to form the most left prefix:

Titles.

the EXPLAIN the SELECT * FROM employees.
WHERE emp_no='10001'
AND title IN (' pro 'Engineer', 'Staff', 'Engineer', AND 'Senior Staff', 'Assistant' Engineer ', 'Technique as Leader', 'Manager')
AND from_date='1986-06-26';
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- +
| | id select_type | table  | type  | possible_keys | key        | key_len | ref  | rows | Extra            |
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- +
|   1 | SIMPLE          | titles | range | PRIMARY            | PRIMARY | 59           | NULL |       7 | Using where |
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- +

 

the key_len of 59, so all the indexes were used, but IN fact can be seen from the type and rows performs a range query, checked seven key here. Look at the two kinds of query performance comparison:

SHOW PROFILES;
+ -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +
| Query_ID | Duration    | Query                                                                                                                                                |
+ -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +
|             10 0.00058000 | | SELECT * FROM employees. The titles WHERE emp_no='10001' AND from_date='1986-06-26' |
|             11 0.00052500 | | SELECT * FROM employees. The titles WHERE emp_no='10001' AND the title IN...                   |
+ -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +

 

a little performance improvements after the "filling holes". If after emp_no screening the rest a lot of data, the latter performance advantages become more obvious. A lot of value, of course, if the title, with filling holes is not appropriate, must establish auxiliary index.

situation 4: query condition is not specified index in the first column.

the EXPLAIN the SELECT * FROM employees. The titles WHERE from_date='1986-06-26';                                    
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- +
| | id select_type | table  | type | possible_keys | key  | key_len | ref  | rows    | Extra            |
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- +
|   1 | SIMPLE          | titles | ALL  | NULL                  | NULL | NULL      | NULL | 443308 | Using where |
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- +

 

because of not the left prefix, indexes such queries with less than index obviously.

case 5: prefix match a list of strings.

the EXPLAIN the SELECT * FROM employees. The titles WHERE emp_no='10001' AND the title LIKE 'pro %';
view sourceprint?
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- +
| | id select_type | table  | type  | possible_keys | key        | key_len | ref  | rows | Extra            |
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- +
|   1 | SIMPLE          | titles | range | PRIMARY            | PRIMARY | 56           | NULL |       1 | Using where |
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- +

 

index can be used at this time, but if a wildcard is not only appear in the end, is unable to use the index.

case 6: range queries.

the EXPLAIN the SELECT * FROM employees. The titles WHERE emp_no <'10010' and the title='pro' Engineer ';
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- +
| | id select_type | table  | type  | possible_keys | key        | key_len | ref  | rows | Extra            |
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- +
|   1 | SIMPLE          | titles | range | PRIMARY            | PRIMARY | 4             | NULL |     16 | Using where |
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- +

 

columns can use index range (must be the most left prefix), but behind the range column column cannot use indexes. Most at the same time, the index columns used in a range, so if the query condition in the entire range has two columns cannot use an index.

the EXPLAIN the SELECT * FROM employees. Titles
WHERE emp_no <
AND title='10010', 'pro' Engineer ', '
AND from_date BETWEEN' 1986-01-01 'AND' 1986-12-31 s';
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- +
| | id select_type | table  | type  | possible_keys | key        | key_len | ref  | rows | Extra            |
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- +
|   1 | SIMPLE          | titles | range | PRIMARY            | PRIMARY | 4             | NULL |     16 | Using where |
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- +

 

you can see the index of the second range index could do nothing. Is to be noted here MySQL an interesting place, it is only with the explain index might not be able to distinguish between range and multiple value matches, because both are shown in the type for range. At the same time, with the "between" does not mean that is the range queries, such as the following query:

Titles.

the EXPLAIN the SELECT * FROM employees.
WHERE emp_no BETWEEN '10001' AND '10010'
AND title='pro' Engineer ', '
AND from_date BETWEEN' 1986-01-01 'AND' 1986-12-31 s';
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- +
| | id select_type | table  | type  | possible_keys | key        | key_len | ref  | rows | Extra            |
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- +
|   1 | SIMPLE          | titles | range | PRIMARY            | PRIMARY | 59           | NULL |     16 | Using where |
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- +

 

look is to use the two range queries, but the effect on the emp_no "BETWEEN" effectively "IN", that is to say, emp_no is actually a multivalued exact match. As you can see the query used to index all three columns. Therefore in MySQL to carefully distinguish multi-value and range match, otherwise it will cause confusion to the behavior of the MySQL.

7: the query condition containing a function or expression.

unfortunately, if the query condition containing a function or expression, MySQL will not as the column using the index (although some can be used in mathematical sense). For example:

the EXPLAIN the SELECT * FROM employees. The titles WHERE emp_no='10001' AND left (title, 6)='pro';
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- +
| | id select_type | table  | type | possible_keys | key        | key_len | ref    | rows | Extra            |
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- +
|   1 | SIMPLE          | titles | ref  | PRIMARY            | PRIMARY | 4             | const |       1 | Using where |
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- +

 

although the query and 5 functions the same, but due to the use of the function of the left, will not index for the title listed applications, and is understanding the LIKE you can. Such as:

the EXPLAIN the SELECT * FROM employees. The titles WHERE emp_no - 1='10000';                                              
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- +
| | id select_type | table  | type | possible_keys | key  | key_len | ref  | rows    | Extra            |
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- +
|   1 | SIMPLE          | titles | ALL  | NULL                  | NULL | NULL      | NULL | 443308 | Using where |
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- +

 

is this query is equivalent to the query emp_no function for 10001, but the query condition is an expression, MySQL is unable to use the index for it. Seems to be no intelligence to automatically optimize the MySQL constant expressions, so try to avoid when writing the query expression appeared in the query, but the first algebraic operation manual in private, is converted to a query without expression.

 

Selectivity and prefix index index

since index can speed up the query speed, as long as it is just the query needs, is built on the index? The answer is no. Because the index while speeding up the query speed, but the index is also comes at a cost: index file itself consumes storage space, at the same time index can aggravate the burden to insert, delete, and modify records, in addition, the MySQL and consumes resources at run time index maintenance, so the index is not the more the better. In both cases generally don't recommend indexed.

the first case is less table records, such as YiLiangQianTiao even only a few hundred record tables, there is no need to build index, make queries do a full table scan. As to how many record is too much, the individual has a personal point of view, my experience is based on 2000 as the dividing line, record number less than 2000 can be considered not indexed, more than 2000 can consider index.

another not indexed is index of selectivity is low. Index of the selective (Selectivity), refers to the don't repeat the index values of (also called base, Cardinality) and the table record number (#T) is the ratio of:

the Index Selectivity=Cardinality/#T

obviously selective scope is (0, 1], the higher the selectivity index value is larger, it is decided by the nature of the B + Tree. For example, using the above employees. The titles table, if the title field is often separate queries, whether to need to build index, we have a look at its selective:

SELECT count (DISTINCT) (title)/count (*) AS Selectivity FROM employees. The titles;
+ -- -- -- -- -- -- -- -- -- -- -- -- -- +
| Selectivity | + -- -- -- -- --
-- -- -- -- -- -- -- -- +
|           0.0000 |
+ -- -- -- -- -- -- -- -- -- -- -- -- -- +

 

the title selectivity of less than 0.0001 (exact value of 0.00001579), so don't really have much necessary for its indexed separately.

there is a kind of index related to the selectivity index optimization strategy called prefix index, is used to replace the entire column as index key columns prefix, when the appropriate prefix length, can not only make the prefix index selectively nearly all the column index, at the same time because the index key shorten and reduce the size of the index files and maintenance overhead. Below to employees. The employees table as an example to introduce the prefix index selection and use.

can see employees table from figure 12 only one index , so if we want to search a person by name, can only a full table scan:

the EXPLAIN the SELECT * FROM employees. Employees WHERE the first_name='Eric' AND last_name="Anido ';                                
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- +
| | id select_type | table        | type | possible_keys | key  | key_len | ref  | rows    | Extra            |
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- +
|   1 | SIMPLE          | employees | ALL  | NULL                  | NULL | NULL      | NULL | 300024 | Using where |
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- +

 

if frequent search employees by name, so obviously efficiency is very low, so we can consider to build indexes. There are two options, to build or , look at the selectivity of the two indexes:

SELECT count (DISTINCT (first_name))/count (*) AS Selectivity FROM employees. The employees;
+ -- -- -- -- -- -- -- -- -- -- -- -- -- +
| Selectivity | + -- -- -- -- --
-- -- -- -- -- -- -- -- +
|           0.0042 |
+ -- -- -- -- -- -- -- -- -- -- -- -- -- +

SELECT count (DISTINCT (concat (first_name, last_name)))/count (*) AS Selectivity FROM employees. The employees;
+ -- -- -- -- -- -- -- -- -- -- -- -- -- +
| Selectivity | + -- -- -- -- --
-- -- -- -- -- -- -- -- +
|           0.9313 |
+ -- -- -- -- -- -- -- -- -- -- -- -- -- +

      <The first_name >Apparently low selectivity, <The first_name, last_name >Selectivity is good, but the first_name and last_name combined length of 30, both length and selective way? Consider using the first_name and last_name first few characters of the index, for example <3) (last_name, first_name, left >, see the selective:

SELECT count (DISTINCT (concat (first_name, left (last_name, 3))))/count (*) AS Selectivity FROM employees. The employees;
+ -- -- -- -- -- -- -- -- -- -- -- -- -- +
| Selectivity | + -- -- -- -- --
-- -- -- -- -- -- -- -- +
|           0.7879 |
+ -- -- -- -- -- -- -- -- -- -- -- -- -- +

 

selectivity is good, but are still a little distance from 0.9313, then add the last_name prefix to the 4:

SELECT count(DISTINCT(concat(first_name, left(last_name, 4))))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.9007 |
+-------------+

 

  这时选择性已经很理想了,而这个索引的长度只有18,比短了接近一半,我们把这个前缀索引建上:

ALTER TABLE employees.employees
ADD INDEX `first_name_last_name4` (first_name, last_name(4));

 

  此时再执行一遍按名字查询,比较分析一下与建索引前的结果:

SHOW PROFILES;
+----------+------------+---------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                           |
+----------+------------+---------------------------------------------------------------------------------+
|       87 | 0.11941700 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |
|       90 | 0.00092400 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |
+----------+------------+---------------------------------------------------------------------------------+

 

  性能的提升是显著的,查询速度提高了120多倍。

  前缀索引兼顾索引大小和查询速度,但是其缺点是不能用于ORDER BY和GROUP BY操作,也不能用于Covering index(即当索引本身包含查询所需全部数据时,不再访问数据文件本身)。

  InnoDB的主键选择与插入优化

  在使用InnoDB存储引擎时,如果没有特别的需要,请永远使用一个与业务无关的自增字段作为主键。

  经常看到有帖子或博客讨论主键选择问题,有人建议使用业务无关的自增主键,有人觉得没有必要,完全可以使用如学号或身份证号这种唯一字段作为主键。不论支持哪种论点,大多数论据都是业务层面的。如果从数据库索引优化角度看,使用InnoDB引擎而不使用自增主键绝对是一个糟糕的主意。

  上文讨论过InnoDB的索引实现,InnoDB使用聚集索引,数据记录本身被存于主索引(一颗B+Tree)的叶子节点上。这就要求同一个叶子节点内(大小为一个内存页或磁盘页)的各条数据记录按主键顺序存放,因此每当有一条新的记录插入时,MySQL会根据其主键将其插入适当的节点和位置,如果页面达到装载因子(InnoDB默认为15/16),则开辟一个新的页(节点)。

  如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页。如下图所示:

  

\

 

  图13

  这样就会形成一个紧凑的索引结构,近似顺序填满。由于每次插入时也不需要移动已有数据,因此效率很高,也不会增加很多开销在维护索引上。

  如果使用非自增主键(如果身份证号或学号等),由于每次插入主键的值近似于随机,因此每次新纪录都要被插到现有索引页得中间某个位置:

  

\

 

  图14

  此时MySQL不得不为了将新记录插到合适位置而移动数据,甚至目标页面可能已经被回写到磁盘上而从缓存中清掉,此时又要从磁盘上读回来,这增加了很多开销,同时频繁的移动、分页操作造成了大量的碎片,得到了不够紧凑的索引结构,后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

  因此,只要可以,请尽量在InnoDB上采用自增字段做主键。


This concludes the body part