About Mysql indexes _ MySQL

About Mysql index bitsCN.com

The world of relational databases is a world dominated by operations on tables and sets, tables, and sets. A database is a collection of tables, and a table is a collection of rows and columns. When a SELECT query is published to retrieve rows from the table, a set of rows and columns is obtained. These are some abstract concepts, and there is little reference value for the basic representation of the data used by the database system to manipulate the table. Another abstract concept is that all operations on a table are performed simultaneously. query is a conceptual set operation without the concept of time in the set theory. Of course, the real world is quite different. The database management system implements abstract concepts, but is subject to physical constraints within the actual hardware scope. The result is that the query takes a long time. Humans are impatient and don't like waiting, so we leave the abstract world of instantaneous mathematical operations on the set to seek methods to accelerate queries. Fortunately, there are several technologies that accelerate computation, allowing you to index tables to make the database server search faster. You can consider how to make full use of these indexes to write queries. You can write queries that affect the server scheduling mechanism so that queries from multiple clients can collaborate better. We thought about how the basic hardware runs to figure out how to overcome its physical constraints to improve performance.

These are the issues discussed in this article. The goal is to optimize the performance of the database system so that it can process various queries as quickly as possible. MySQL is already quite fast, but even the fastest database can run faster with human design.

1. use 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 article describes what an index is, how it improves query performance, how it 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. (Though not always. Sometimes you want to ignore the role of the optimization program. We will also introduce these situations .)

1.1 Benefits of indexing

Let's start with an index-free table to check how indexes work. A table without indexes is a disordered row set. For example,-1 shows the ad table we first saw in chapter 1st "MySQL and SQL introduction. There is no index on this table. Therefore, if we look for a row in a specific company, we must check each row in the table to see if it matches the required value. This is a full table scan, which is very slow. if there are only a few records in the table that match the search criteria, the efficiency is quite low.

-2 provides the same table, but adds an index to the company_num column of the table. This index contains one entry per row in the table, but this index is sorted on company_num. 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 of company 13, we can scan the index and get three rows. Then, we will arrive at line 14, which is a number larger than what we are looking. The index values are sorted. Therefore, when reading a record containing 14, 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.

Some people may ask why not only sorting data files, but saving the index files? In this way, does the search result have the same effect? Well, this is the case if only one index is used. However, the second index may be used, but it is impossible to sort the same data file in two different ways at the same time. (For example, if you want an index for a customer name and an index for a customer ID or phone number .) Using the index file as an entity independent from the data file solves this problem and allows you to create multiple indexes. In addition, the rows in the index are generally shorter than those in the data file. When values are inserted or deleted, it is easier to move a shorter index value to maintain the sorting order than to move a longer data row.

This example is consistent with the MySQL index table method. The data rows of the table are stored in the data file, and the index value is stored in the index file. A table can have more than one index. if more than one index exists, it is stored in the same index file. Each index in the index file is composed of an array of key records that are sorted to quickly access the data file.

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 t 1, t 2, and t 3 contain only columns c 1, c 2, and c 3, each table is composed of 1000 rows containing numbers 1 to 1000. The query for table row combinations with equal values is as follows:
SELECT c1, c2, c3
FROM t1, t2, t3
WHERE c1 = c2 AND c1 = c3

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 possible number of combinations is 10 0 0x10 0 0x10 0 0 0 (billions), which is 1 million times more than the number of matches. 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. It also uses indexes to improve the performance of other operations:

■ When using MIN () and MAX () functions, you can quickly find the minimum or maximum value of the index column.
■ MySQL often uses indexes to sort the order by clause.
■ Sometimes, MySQL can avoid reading the entire data file. If you select a value from an index value column, and do not select other columns in the table. By reading the index value, you can obtain the value to be obtained from the data file. There is no need to read the same value twice, so it does not even involve data files. BitsCN.com