Use of MySQL-optimized indexes (1)

Use of MySQL Indexes

We will first discuss the index, because it is the most important tool to speed up the query. There are other technologies for accelerating queries, but the most effective one is to properly use indexes. On the MySQL Mail List, people usually ask questions about faster queries. In many cases, because there is no index on the table, the problem can be solved immediately by adding an index. But this is not always effective, because optimization is not always that simple. However, if indexes are not used, in many cases, it is a waste of time to improve performance by other means. You should first consider using indexes to achieve maximum performance improvement, and then seek other technologies that may be helpful.

This section describes what an index is, how it improves query performance, when the index may degrade performance, and how to select an index for a table. In the next section, we will discuss the MySQL query optimization program. In addition to how to create an index, it is also good to know some optimization procedures, because it can make better use of the created index. Some methods for writing a query will actually impede the indexing effect, so this situation should be avoided. Although this is not always the case. Sometimes you want to ignore the role of the optimization program. We will also introduce these situations .)

Impact of indexes on queries of a single table

The index is used to quickly find rows with a specific value in a column. Without an index, MySQL has to start with the first record and then read the entire table until it finds the relevant rows. The larger the table, the more time it takes. If the table has an index on the queried columns, MySQL can quickly find the data file at a location without considering all the data. If a table has 1000 rows, this is at least 100 times faster than sequential reading. Note that you need to access almost all 1000 rows, which can be read in a faster order, because at this time we avoid disk seek.

For example, for the following student table:

Mysql> SELECT * FROM student
+ ------ + --------- +
| Id | name | english | chinese | history |
+ ------ + --------- +
| 12 | Tom | 66 | 93 | 67 |
| 56 | Paul | 78 | 52 | 75 |
| 10 | Marry | 54 | 89 | 74 |
| 4 | Tina | 99 | 83 | 48 |
| 39 | William | 43 | 96 | 52 |
| 74 | Stone | 42 | 40 | 61 |
| 86 | Smith | 49 | 85 | 78 |
| 37 | Black | 49 | 63 | 47 |
| 89 | White | 94 | 31 | 52 |
+ ------ + --------- +

In this way, when we try to perform a specific query on it, we have to perform a full table scan, which is very slow. For example, we can find all students whose english scores fail:

Mysql> SELECT name, english FROM student WHERE english <60;
+ --------- +
| Name | english |
+ --------- +
| Marry | 54 |
| William | 43 |
| Stone | 42 |
| Smith | 49 |
| Black | 49 |
+ --------- +

The WHERE clause must match each record to check whether the condition is met. This small table may not have much impact. However, for a large table, such as a very large school, we may need to store thousands of records, and such a retrieval takes a considerable amount of time.

If we create an index for the english column:

Mysql> alter table student add index (english );
+ ------------------- +
| Index for english |
+ ------------------- +
| 42 |
| 43 |
| 49 |
| 49 |
| 54 |
| 66 |
| 78 |
| 94 |
| 99 |
+ ------------------- +

As shown in the preceding table, this index is stored in the index file and contains the english Column values of each row in the table. However, this index is sorted based on english. Currently, you do not need to search for matching clauses in the entire table row by row, but can search by index. If we want to find all rows with scores less than 60, we can scan the index and obtain 5 rows. Then we reach the row with a score of 66 and the Tom record, which is a value greater than what we are looking. The index values are sorted. Therefore, when reading a record containing Tom, we know that no matching record exists and can exit. If you look for a value that does not appear before an intermediate point in the index table, you can also find its first location algorithm that matches the index, instead of sequential table scanning, such as binary search ). In this way, you can quickly locate the first matched value to save a lot of search time. Databases use a variety of technologies to quickly locate index values. These technologies are not important. What is important is that they work normally and indexing technology is a good thing.

Therefore, the following query is executed:

Mysql> SELECT name, english FROM user WHERE english <60;

The result is:
+ --------- +
| Name | english |
+ --------- +
| Stone | 42 |
| William | 43 |
| Smith | 49 |
| Black | 49 |
| Marry | 54 |
+ --------- +

You can see that this result is different from that before the english column is not indexed. It is sorted because it is officially described above.

Impact of indexes on queries of multiple tables

The preceding discussion describes the benefits of indexes in a single table query. Using indexes eliminates full table scans, greatly accelerating the search speed. Indexes are even more valuable when performing join queries involving multiple tables. In a single table query, the number of values to be viewed in each column is the number of rows in the table. In queries of multiple tables, the number of combinations may be large, because this number is the product of the number of rows in each table.

Assume that three unindexed tables t1, t2, and t3 contain only columns c1, c2, and c3, and each table is composed of 1000 rows containing numbers 1 to 1000. The query for table row combinations with equal values is as follows:

The query result should be 1000 rows. Each combination contains three equal values. If we process this query without indexing, it is impossible to know which rows contain those values. Therefore, you must find all the combinations to obtain those that match the WHERE clause. The number of possible combinations is 1000 × 1000 × 1000 billion), 1 million times more than the matching number. A lot of work is wasted, and the query will be very slow, even if it is executed in a database as fast as MySQL. In this case, there are only 1000 rows in each table. What if there are 1 million rows in each table? Obviously, this will produce extremely low performance results. If you index each table, the query process can be greatly accelerated, because the query process using the index is as follows:

1) Select the first row from Table t1 to view the values contained in this row.

2) use the index on table t2 to directly jump to the row in t2 that matches the value from t1. Similarly, the indexes on table t3 are used to directly jump to the rows in Table t3 that match the values from t1.

3) enter the next row of table t1 and repeat the previous process until all the rows in Table t1 have been checked.

In this case, we still perform a full scan on table t1, but we can perform index search on table t2 and table t3 to directly retrieve the rows in these tables. In principle, the query speed is 1 million times faster than that of unused indexes.

As mentioned above, MySQL uses indexes to accelerate the search of rows matching the condition in the WHERE clause, or accelerate the search of rows matching the rows in other tables when executing the join operation.

Impact of Multi-column indexes on queries

Assume that you have issued the following SELECT statement:

Mysql> SELECT * FROM tbl_name WHERE col1 = val1 AND col2 = val2;

If a multi-column index exists on col1 and col2, appropriate rows can be taken out directly. If the separate single-row and column indexes exist on col1 and col2, the optimizer tries to determine which index will find fewer rows and find more restrictive indexes and use this index to retrieve rows.
You can create a multi-column index as follows:

Mysql> alter table tbl_name add index (col1, col2 );

In this way, you should create a separate single-row-column index:

mysql>ALTER TABLE tble_name ADD INDEX(col1);mysql>ALTER TABLE tble_name ADD INDEX(col1);

If a table has multiple column indexes, any leftmost index prefix can be used by the optimizer for travel. For example, if you have a 3-column index (col1, col2, col3), you have indexed it in (col1), (col1, col2), and (col1, col2, col3).

If the column does not constitute the leftmost prefix of the index, MySQL cannot use a partial index. Assume the SELECT statement shown below:

mysql> SELECT * FROM tbl_name WHERE col1=val1;mysql> SELECT * FROM tbl_name WHERE col2=val2;mysql> SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

If an index exists on (col1, col2, col3), the index is used only for the first query shown above. The second and third queries do contain indexed columns, but (col2) and (col2, col3) are not the leftmost prefixes of (col1, col2, col3.

If the LIKE parameter is a constant string that does not start with a wildcard character, MySQL also uses indexes for LIKE comparison. For example, the following SELECT statement uses an index:

mysql> select * from tbl_name where key_col LIKE "Patrick%";mysql> select * from tbl_name where key_col LIKE "Pat%_ck%";

In the first statement, only the rows with "Patrick" <= key_col <"Patricl" are considered. In the second statement, only the rows with "Pat" <= key_col <"Pau" are considered.

The following SELECT statements do not use indexes:

mysql> select * from tbl_name where key_col LIKE "%Patrick%";mysql> select * from tbl_name where key_col LIKE other_col;

In the first statement, the LIKE value starts with a wildcard character. In the second statement, the LIKE value is not a constant.
If column_name IS an index, column_name is null.

MySQL usually uses an index to find the minimum number of rows. An index is used for the column you compared with the following operators: =,>, >=, <, <=, BETWEEN, and a column with a non-Wildcard prefix LIKE 'something %.

For a multi-column index, if indexes are used at all AND layers of the WHERE clause, the index is not used to optimize the query. To optimize queries using indexes, you must use the prefix of one or more columns of indexes in one AND condition group.
The following WHERE clause uses indexes:

... WHERE index_part1 = 1 AND index_part2 = 2... WHERE index = 1 or a = 10 AND index = 2/* index = 1 OR index = 2 */... WHERE index_part1 = 'hello' AND index_part_3 = 5/* optimized like "index_part1 = 'hello'" */These WHERE clauses do not use indexes :... WHERE index_part2 = 1 AND index_part3 = 2/* index_part_1 is not used */... WHERE index = 1 or a = 10/* No index */... WHERE index_part1 = 1 OR index_part2 = 10/* No index spans all rows */