Using the MySQL Index

The world of relational databases is a world dominated by the operations of tables and collections, tables and collections. A database is a collection of tables, and a table is a collection of rows and columns. When you publish a select query to retrieve rows from a table, you get a collection of another row and column. These are abstract concepts that are used by the database system to manipulate the data in the table.
Indicates that there is not much reference value. Another abstract concept is that the operations on the table are simultaneous; the query is a conceptual set operation, and there is no time concept in the set theory. Of course, the real world is quite different. The database management system implements the abstract concept, but in the actual hardware
Within the scope of the actual physical constraints. Knot  lead  檠 ㄊ ben Qinglie  asleep coins ê 艹 silky neon Ben Huan 6 death--------------- joys LAN Mantra パ grilled beetle uranium yan  檠 āp to say Neon  scrap steel Decree uranium, Yie to raise an interregional acyl  what member hacking  Hing Gourmands  so , and ,, jin,, Dal and 檎? Fan undercover  Zhuo Shui throw  Riddle Wu ┧ the gourmands  the noisy bed 檠? Kill the noise from the PostScript 旆  Jin 鞯 queue frequency  牟檠 estimate vinegar Far oh well  Щ 牟檠  enzyme 鞯  n yi Ji ji 谩 ju cloth   Undercover Chant school  Langfeng Magic emanation perch undercover  shuo  same shade occluded dui  , this is the issue discussed in this chapter, the goal is to optimize the performance of the database system, Make it work on a variety of queries as quickly as possible. MySQL is already pretty fast, but even the fastest database can run faster in people's designs.
4.1 Using indexes
We'll discuss the index first, because it's the most important tool for speeding up queries. There are other techniques to speed up the query, but the most effective is to use the index appropriately. On MySQL's mailing list, people often ask questions about making queries faster. In a large number of cases, because there is no index on the table, generally as long as the index can solve the problem immediately. But this is not always effective, because optimization is not always that simple. However, if you do not use indexes, in many cases, improving performance by other means can only be a waste of time. You should first consider using the index for maximum performance improvement, and then look for other techniques that might be helpful.
This section describes what an index is, how it improves query performance, how an index might degrade performance, and how to select an index for a table. In the next section, we'll discuss the MySQL query optimizer. In addition to knowing how to create an index, it is good to know some of the optimizer's knowledge, because it makes better use of the indexes that are created. Some methods of writing queries actually hinder the effect of indexing and should be avoided. (although this is not always the case.) Sometimes you also want to ignore the role of the optimizer. We will also describe these situations. ）
Benefits of 4.1.1 Indexes
Let's start with an index-free table to examine how the index works. A table without an index is an unordered set of rows. For example, figure 4-1 shows the ad table that we first saw in the 1th chapter, "MySQL and SQL introduction." There are no indexes on this table, so if we look up a row for a particular company, we must look at each row in the table to see if it matches the desired value. This is a full table scan, very slow, and if only a few records in the table match the search criteria, the efficiency is fairly low.

Figure 4-2 shows the same table, but adds an index to the Company_num column of the table. This index contains one entry for each row in the table, but the index is sorted on the company_num. Now, instead of searching the entire table for a matching clause line by row, you can use the index to find it. If we're looking for all of the company's 13 rows, we can scan the index and get 3 lines. Then reach the company 14 line, which is a larger number than the one we're looking for. The index values are sorted, so when you read the records that contain 14, we know that there will be no more matching records and you can quit. If you look for a value that does not appear before an intermediate point in the Index table, there is also a location algorithm to find the first matching index entry, rather than a sequential scan of the table (such as the binary lookup method). In this way, you can quickly navigate to the first matching value to save a lot of search time. The database utilizes a variety of techniques for quickly locating index values, which are not important, it is important that they work properly and indexing is a good thing.
One would ask, why not just sort the data files and skip the index files? Does this not also produce the same effect in search? Well, if you have a single index,
That is true. However, it is possible to use a second index, but it is not possible to sort the same data file in two different ways. (for example, want a customer name of the rope
, and also an index of the customer ID number or phone number. The index file is resolved as a separate entity from the data file, and multiple cables are allowed
Cited. In addition, the rows in the index are typically shorter than the rows in the data file. Moving a shorter index value to keep the sort order while inserting or deleting a value is more than moving a longer row of data
For easy.

This example is consistent with the MySQL Index table method. The data rows of the table are saved in the data file, and the index values are saved in the index file. You can have more than one index on a table, and if you do have more than one index, they are saved in the same index file. Each index in the index file consists of an ordered array of key records that are used to quickly access the data file.
The preceding discussion describes the benefits of indexing in a single table query, where the use of indexes eliminates full table scans, which greatly speeds up the search. Indexes can be even more valuable when performing a connection query that involves multiple tables. In a query for a single table, the number of values that each column needs to see is the number of rows in the table. In queries in multiple tables, the number of possible combinations is enormous, because this number is the product of the number of rows in each table.
If there are three indexes of tables T 1, T 2, and T 3, they contain only columns C 1, c 2, and C 3, each of which consists of 1 rows containing a value of 1000 to 1000 respectively. The query looking for a combination of table rows with equal values looks like this:
SELECT C1,C2,C3
From T1,T2,T3
WHERE C1=c2 and C1=C3
The result of this query should be 1000 rows, and each combination contains 3 equal values. If we process this query without an index, it is not possible to know which rows contain those values. Therefore, you must look up all the combinations to get those combinations that match the WHERE clause. The number of possible combinations is 0 0x10 0 0x10 0 0 (1 billion), which is 1 million times times more than the matching number. A lot of work is wasted, and the query will be very slow, even in databases like MySQL, which can be slow to execute. This is the case with only 1000 rows per table. What happens if there are 1 million rows in each table? Obviously, this will result in extremely low performance. If you index each table, you can greatly speed up the query process, because the query that uses the index is processed as follows:
1) Select the first row from the table T1 to see the values contained in the row.
2 Use the index on the table T2 to jump directly to the row in T2 that matches the value from T1. Similarly, use the index on the table T3 to jump directly to the row in T3 that matches the value from T1.
3 Go to the next line in the table T1 and repeat the previous process until all the rows in the T1 have been checked. In this case, we still perform a full scan of the table T1, but we are able to retrieve the rows from those tables directly from the table T2 and T3 indexed lookups. In a sense, the query is 1 million times times faster than the unused index. As noted above, MySQL uses indexes to speed up searches for rows in the WHERE clause that match the criteria, or to speed up the search for rows matching rows in other tables when the connection is performed. It also uses indexes to improve the performance of other operations:
When you use the Min () and Max () functions, you can quickly find the minimum or maximum value of an indexed column.
MySQL is often able to use the index to complete ordering by clause.
Sometimes, MySQL avoids reading the entire data file. If you select a value from an indexed value column, and you do not select other columns in the table. At this point, by reading the index value, you have obtained the value of reading the data file. There is no need to read two times for the same value, so there is no need to even involve data files.
Disadvantages of 4.1.2 Index
In general, if MySQL knows how to use indexes to process queries faster, it does so. This means that in most cases, if you do not index the table, it is your own interest that is damaging. As you can see, the author paints many of the benefits of indexing. But what's the downside? Yes, there is. In fact, these shortcomings have been overshadowed by the advantages,
But there should be some understanding of them.
First, the index file takes up disk space. If you have a large number of indexes, the index file may reach the maximum file size faster than the data file. Second, index files speed up retrieval, but add inserts and deletes, and the time to update values in indexed columns (that is, the time that most involves writing is reduced) because write operations involve not only data rows but also indexes. The more indexes a table has, the greater the average performance of the write operation. In the 4. Section 4, "Effectively loading data," we will cover these performance issues in more detail and discuss how to resolve them.
4.1.3 Select Index
The syntax for creating an index is already in 3. 4. Section 3, "Creating and deleting indexes," is described in. Here, we assume that you have read the section. But knowing the syntax does not help to determine how the table is indexed. You need to consider how tables are used to determine how tables are indexed. This section describes some guidelines on how to identify and select indexed columns:
The indexed column you are searching for is not necessarily the column you want to select. In other words, the column that best fits the index is the column that appears in the WHERE clause, or the column specified in the JOIN clause, not the column in the selection list after the Select keyword:


Of course, the columns you select and the columns you use for the WHERE clause may also be the same. The key is to list the flags in the select list that are not indexed by the column. Columns that appear in a join clause or in expressions such as col1= col2 are columns that are well suited for indexing. The Col_b and Col_c in the query are examples of this. If MySQL can use the connection column to optimize a query, it means that it considerably reduces the combination of table rows by eliminating full table scans.
Use a unique index. Consider the distribution of values in a column. For columns with unique values, the index works best, and columns with multiple duplicate values have the lowest index. For example, the Storage age column has different values, and it is easy to distinguish between rows. The column that is used to record sex only contains "M" and "F", and it is not useful to index this column (you will get about half the rows regardless of which value you are searching for).
Use a short index. If you are indexing a string column, you should specify a prefix length, as long as it is possible to do so. For example, if you have a char (200) column, and if most values are unique within the first 10 or 20 characters, do not index the entire column. Indexing the first 10 or 20 characters can save a lot of indexing space, or it can make queries faster. Smaller indexes involve less disk I/O, and shorter values compare faster. More importantly, for shorter key values, blocks in the index cache can hold more key values, so MySQL can also hold more values in memory. This increases the likelihood of finding rows without reading more blocks in the index. (Of course, some common sense should be used.) Indexing with the first character of a column value is not likely to have much benefit because there are not many different values in the index. ）
Use the leftmost prefix. When you create an index of n columns, you are actually creating the n indexes that MySQL can take advantage of. Multiple-column indexes can play a role in several indexes, because the leftmost Lie in the index can be used to match rows. Such a set of columns is called the leftmost prefix. (This differs from the prefix of an index column, and the prefix of an index column is to use the first n characters as the index value.) ）
Suppose a table has an index on three columns named S t a T E, city, and zip respectively. The rows in the index are stored in the order of State/city/zip, so the rows in the index are automatically stored in the order of state/city and state. This means that MySQL can take advantage of indexes even if only state values are specified in the query or only state and city values are specified. Therefore, this index can be used to search for the following column combinations:
State,city,zip
State,city
Sate
MySQL cannot use a search that does not involve a left prefix. For example, if you are searching by city or zip, you cannot use the index. If you are searching for a state and a ZIP code (columns 1 and 3 in the index), this index cannot be used for a combination of the corresponding values. However, indexes can be used to find rows that match the state to reduce the scope of the search.
Do not overdo indexing. Do not assume that the more the index "the better", the index of everything is wrong. Each additional index takes up additional disk space and reduces the performance of the write operation, as we have already described earlier. When you modify the contents of a table, the index must be updated, sometimes you may need to refactor, so the more indexes, the longer it takes. If you have an index that is rarely used or never used, it can unnecessarily slow down the table's modification speed. In addition, it takes time for MySQL to consider each index when it comes to generating an execution plan. Creating extra indexes brings more work to query optimization. Too many indexes may also make MySQL choose the best index to use. Keeping only the indexes you need facilitates query optimization. If you want to add an index to an indexed table, you should consider whether the index you want to increase is the leftmost index of the existing multiple-column index. If so, then do not bother to add the index, because there is already.
Consider the type of comparison that is made on the column. Indexes can be used for "<", "< =", "=", "> =", ">", and between operations. When a pattern has a direct measure prefix, the index is also used for like operations. If you are using only one column for other types of operations, such as strcmp (), there is no value in indexing it.