How to create and use MySQLIndex indexes _ MySQL

Source: Internet
Author: User
Tags mysql index mysql query optimization
Index Creation and usage of MySQLIndex database

1. if no index is created, all queries require full table scanning. if an index is created, the database will save an index file with a special structure such as tree B, in this way, you do not need to scan the entire table to find the records that meet the requirements.

2. generally, an index is created for the condition after the Where clause. the primary key in the database has already been indexed. Multiple indexes can be created in the database.

3. you can create indexes for different types of columns.

For Text type, you can use MySQL's full-Text retrieval function to create a full-Text index. It uses natural language methods to retrieve keywords in the text.

For example, if the = sign is used, like and % may be used for matching. Using MySQL full-text search, you can use the Match function to retrieve the columns containing keywords.

For details, see the full-text search section in the MySQL Reference Manual.

4.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 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. (Though not always. Sometimes you want to ignore the role of the optimization program. We will also introduce these situations .)

4.1.1 Benefits of indexes

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.

1. jpg

-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, if only one index is available,

Yes. 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 a customer name

And an index of the customer ID or phone number .) Using the index file as an entity independent from the data file solves this problem and allows the creation of multiple

. In addition, the rows in the index are generally shorter than those in the data file. When values are inserted or deleted, moving a shorter index value to maintain the sorting order is more effective than moving a longer data row.

Is easy.

2. jpg

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 number of possible combinations is 10 0 0 ?? 0 0 0 ?? 0 0 0 (billions), 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.

4.1.2 disadvantages of indexes

In general, if MySQL can know how to use indexes to process queries faster, it will do so. This means that, in most cases, if you do not index a table, it damages your own interests. We can see that the author depicts the many benefits of indexing. But is there any disadvantage? Yes, yes. In fact, these shortcomings are covered by advantages,

But you should understand them.

First, index files occupy disk space. If there are a large number of indexes, the index file may reach the maximum file size faster than the data file. Secondly, the index file accelerates the retrieval, but increases the insertion and deletion, and the time to update the value in the index column (that is, reduces the time for most write operations ), because write operations involve not only data rows, but also indexes. The more indexes a table has, the larger the average performance of write operations is. In section 4 "effectively loading data", we will introduce these performance problems in more detail and discuss how to solve them.

4.1.3 select an index

The syntax for creating an index is described in section 3. 4. 3 "create and delete an index. Here, we assume that you have read this section. However, knowing the syntax does not help determine how the table is indexed. To determine how a table is indexed, you must consider the table usage. This section describes how to determine and select an index column:

■ The Index column to be searched is not necessarily the column to be selected. In other words, the column most suitable for indexing is the column that appears in the WHERE clause or the column specified in the join clause, rather than the column in the selection list after the SELECT keyword:

Of course, the selected columns and columns used for the WHERE clause may also be the same. The key is to list the columns that should not be indexed in the current selection list. Columns that appear in the join clause or in expressions such as col1 = col2 are suitable for indexing. The example of col_ B and col_c in the query is as follows. If MySQL can use the join column to optimize a query, it means that it substantially 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 effect is the best, while for columns with multiple duplicate values, the index effect is the worst. For example, columns with different age values can easily distinguish rows. The columns used to record gender only contain "M" and "F", so it is not very useful to index this column (no matter which value is searched, it will produce about half of the rows ).

■ Use short indexes. If you index a string or column, you should specify a prefix length. if possible, you should do this. For example, if a CHAR (200) column exists and multiple 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 index space and make the query faster. A smaller index involves less disk I/O, and a shorter value is faster. More importantly, for shorter key values, the index cache blocks can accommodate more key values. Therefore, MySQL can also accommodate more values in the memory. This increases the possibility of finding rows without reading more data from the index. (Of course, some common sense should be used. If you only use the first character of the column value for indexing, it is impossible to have much benefit, because there will not be many different values in this index .)

■ Use the leftmost prefix. When creating an index with n columns, you actually created n indexes available for MySQL. Multi-column indexes can act as several indexes, because the leftmost column set in the index can be used to match rows. Such a column set is called the leftmost prefix. (This is different from the prefix of an indexed column. the prefix of an indexed column uses the first n characters as the index value .)

Assume that a table has an index on the 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. Therefore, the rows in the index are automatically stored in the order of state/city and state. This means that MySQL can use indexes even if only the state value or the city value is specified in the query. Therefore, this index can be used to search for the following column combinations:

State, city, zip

State, city

Sate

MySQL cannot search with no left prefix. For example, if you search by city or zip, you cannot use this index. If you want to search for a state and a zip code (Column 1 and column 3 in the index), this index cannot be used for combination of corresponding values. However, indexes can be used to find rows that match the state to reduce the search range.

BitsCN.com
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.