Analysis of advantages and disadvantages of indexing in MySQL database

Index, it can be said that database-related optimization, especially in query optimization, one of the most common optimization means, although so, but the index is not a solution to query optimization of the Bible, can not only find query run fast enough to put the conditions in the WHERE clause all in the index. Everything is appropriate and can be stopped, too far, together to see what it is.

Index of the Profit place

The index can improve the efficiency of data retrieval, reduce the IO cost and the sorting cost of the database. The greatest benefit of creating an index on a field in a table in a database is that it can greatly improve the retrieval efficiency, speed up the retrieval time, and reduce the amount of data to be read in the retrieval process when the field is used as a retrieval condition.

Index is not the most

Indexing can greatly improve the efficiency of data retrieval, but also can improve the performance of sorting grouping operations, but we can not ignore the problem is that the index is completely independent of the underlying data part of the data. Assuming that we created the index Idx_ta_ca in the column CA in table TA, any update to the column CA would require MySQL to update the column CA in the table, as well as the index data for the list CA, and adjust the key values for the update to change The index information after the. And if we don't index the column CA, all that MySQL needs to do is just update the column CA's information in the table. In this way, the most obvious resource consumption is to increase the IO amount of the update and the amount of calculation that is caused by the index adjustment. In addition, the Column CA's index Idx_ta_ca is required to occupy storage space, and as the volume of the table TA data increases, the space occupied by the Idx_ta_ca is growing. So indexing also leads to an increase in the consumption of storage space resources.

How to determine if you need to create an index

The more frequent fields as query criteria should create an index;

Indexing is the most effective way to reduce the IO amount of query that is queried by index key fields.

Fields that are too unique are not suitable for creating indexes individually, even if they are frequently used as query criteria;

Fields that are too unique, such as Status fields, type fields, and so on. Because MySQL first takes out the key values in the index to compare with the data in the page where the table data is stored in memory, the order of the data in the data page is not the same as the order of the key values in the index queue. If the key value in the index first finds the data in the data page X and then finds the eligible data in the data page y, then MySQL destroys the data page x and reads the data page y into memory. If there is a key value B, then the key value B to find the data on the data page x, then MySQL will be the data page x read into memory. That is to say, the index to find the corresponding table data is random access. (The actual situation should be that there are several pages of data cached in memory, more than one page, but this assumes that there is only one page table in thread memory). The IO consumption caused by such random access is larger than the IO consumption of the full table scan. (It's better to traverse the entire table)

If the index field is unique, for example, it is unique, you need to change the page table at most one time.

If the uniqueness of the index field is poor, the number of pages that need to be changed correspondingly increased.

Fields that are updated very frequently are not appropriate to create indexes;

When a field in an index is updated, it is not only necessary to update the data in the table, but also to update the index data to ensure that the index information is accurate. The problem is a larger increase in IO traffic, which affects not only the response time of the update query, but also the resource consumption of the entire storage system, increasing the load on the entire storage system. Of course, there is not an updated field is not appropriate to create an index, from the above to determine the language of the policy above can also be seen, is "very frequent" field.

Fields that do not appear in the WHERE clause should not create an index;

Index to query optimization benefits, but we do not ignore its choke, can be said to be mixed, need to be based on practical application to grasp the appropriate.

Here we will use MyISAM to discuss the index.

Indexes are implemented at the storage engine layer, not the server layer. As a result, they are not standardized, and each engine's indexing works slightly differently. Even if multiple engines support the same indexes, they can be implemented differently. Below we talk about the most frequently used B-tree index types for the MyISAM storage engine.

(this index is called Secondary index in the Innodb storage engine, followed by a brief introduction)

B-tree Definition
In the 1970, R.bayer and E.mccreight proposed a tree that is suitable for external lookups, which is a balanced, forked tree called B-tree.

The primary key index and the non primary key index are identical in structure except for the PK value and the ordinary key value, which are stored on the leaf node respectively.

• Some of the index types we can create. (MyISAM)
1 primary key index (PRIMARY key)
Each table has only one, which guarantees the uniqueness of the data recordset.
2) Uniqueness Index
The only difference between a primary key index and a unique index is that the first keyword used in the definition is primary key instead of unique.
3 Normal index (index defined by keyword key or index)
4 Full-Text indexing (InnoDB not supported) (FULLTEXT)
You can create on a CHAR, VARCHAR, or TEXT column
5 Multiple-column index (or composite index)
According to the requirements of the query settings, you can complete multiple conditions query, especially large data query can greatly improve the speed of query.
Analysis of index optimization and SQL optimization
Let's take a look at a schematic, just to show the graph, please note that the data on the node is not the actual storage location of the data, just for a more intuitive image.

Look at the pictures and speak and ask questions:

• A composite index (UID,CATID,DAY) is established on the diagram
• After the table is established, with the data, MySQL will be based on the index of your data to generate the above structure, each root to the leaf to the root of the distance is the same, is ordered.
* From the above figure can be seen if your UID is a fixed value (=), CATID is a fixed value (=), Day (=) is a fixed value, is the most ideal state, fully used in the index
• If your UID is a fixed value, CATID is a fixed value, day is a range, you can use it entirely
uid Fixed value, catid range, day is a range, you can use UID and catid, because CATID you use a range, the following day is not available, because the row of trees does not meet your query needs.
•？ 1--if UID fixed value, CATID range, day range, which index can be used, why?
•？ 2--if the data in the table is updated or new, would you like to ask if the indexed index will not change?
Queries that can use the B-tree index

• Match full Name
• Match the leftmost prefix
• Match Range value
• Match parts exactly and match another part of a range.
• Access to index only queries
Limitations

• If the lookup does not start at the far left of the index column, it will be of little use.
• Columns in the index cannot be skipped.
• The storage engine cannot optimize access to any column to the right of the first range condition.
Let's elaborate on some of the areas where we often blur when writing SQL or using indexes, and the conventions to follow.

The most perfect index you set is full use, not full table scans.

1. The sequence specification of the index.
Note: The order of the index is very critical, if it is a composite index, is more than one column, please be sure to carefully analyze the business query, the limited query field in the front, the most commonly used must be placed in the front, the scope of the query field as far as possible.
Because indexes are not allowed to skip columns in the index, a less-common or range-column is preceded by a result that the following index fields cannot be used.
2. The order of the indexes and the order of the fields in the where condition try to keep the same
MySQL usually adjusts the order of the query criteria fields in its where statement instead of the order of the query fields, and it optimizes the query to select the appropriate index.
At the moment my MySQL 5.0.45 or 77 will choose the right index based on the index, so the order of where condition is not entirely relevant to the index,
But some of the previous versions required consistency, so we tried to stay consistent so that it was consistent with our business and readability, as well as avoiding some versions dealing with different issues.
Of course you can pass the test to see the execution plan for indexing analysis.
3. The more the index is, the better.
Indexes are not as much as possible, while indexes can increase the efficiency of the corresponding select, but also reduce the efficiency of insert and UPDATE, because the insert or update is likely to rebuild the index, so how to build the index needs careful consideration, depending on the circumstances. It is best not to have more than 6 indexes on one table, and if too many you should consider the need for indexes built on columns that are infrequently used. (The question 2 above should know the answer.)
4. Try to avoid null (choose default NULL based on actual situation)
The column should be specified not NULL unless you want to store null. Www.111cn.net in MySQL, columns with null values are difficult to query optimization because they make indexing, indexing, and comparison operations more complex. You should use 0, a special value, or an empty string instead of a null value.
5. Do not perform functions, arithmetic operations, or other expression operations on the left side of "=" in the WHERE clause, otherwise the system may not be able to use the index correctly.

such as select a from t where a-5>100;

If an index on a is not used, a full table scan is performed;

Select a from t where a>105;

You can use the index.
6. If you optimize the query, you should try to avoid full table scan, first of all should consider in the where and order by the columns involved in indexing.
7. Please avoid using SELECT * to query, please list the field you actually want to consult, do not return any fields you do not use, generosity is not a good thing.
Say a method that forces an index to be used:

EXPLAIN SELECT UID
From TableName
FORCE INDEX (UID_CID)
WHERE uid = 1
and CID > 1

(Try it yourself, hehe!) Well, now, first of all, these are some summary, the main thing is to shop first concept, we understand the storage engine, supported index types, as well as the actual establishment and use of considerations.

Later, if you have time, you'll see the explain tools to view the execution plan, and the show command in MySQL, and you'll show what you want. Or to create a table when "set UTF8 collate utf8_bin not NULL default" "So what is the role of such a row, hehe, follow-up discussion."

The follow-up will also discuss the InnoDB in the B+tree Index, clustered index and other related aspects of content, slowly come hehe!