Shulou(Shulou.com)05/31 Report--

This article mainly explains "MySQL database query and index optimization", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's take you to learn "MySQL database query and index optimization"!

First, the optimization of database query performance involves a wide range of technical aspects, and it is generally recommended to use the following means to implement:

1. Reduce data access

A related technique is to create an appropriate index that converts time-consuming operations such as full table scans and index scans into index searches. Establishing the correct index can improve the database query performance by 100-1000 times or even higher, just like a very thick dictionary. If you want to look up something without any index, it is quite exhausting. You need to check the whole book again. If there is an index, you can locate it directly according to the index. This is the most important way to improve performance.

2. Reduce returned data

Bandwidth is limited when transmitting data over a network, and it works well if you can extract the least amount of data on demand. Note here that in SQL, do not appear select *, but what field is needed, extract what field.

Reduce the number of interactions with the database

Limited network resources and obviously frequent database interactions are also a performance constraint. A good suggestion is to use stored procedures, or batch statements, which reduce interaction with the database and improve some performance.

4. Reduce CPU load

Here, cache plans are used primarily. In queries, use parameterized queries whenever possible. In this case, the database caches query parameters, thereby reusing query plans.

5. Improve hardware performance

This is the last resort, if other aspects have been done very well, performance bottlenecks in the CPU, memory and disk, then take the solution to improve hardware performance will be more appropriate, otherwise it is better to optimize other places.

The performance improvements brought about by the above five levels of optimization decrease in turn, which is an inverted pyramid.

Second, let's talk about the index and optimization suggestions

Indexing can greatly improve query and sort performance, but it is necessary to maintain the index order during insertion, deletion, and modification of primary keys. If a table changes frequently, it is not advisable to create too many indexes, and the negative performance impact of indexes will not be worth it.

Index optimization is a delicate matter, it needs to find a balance point.

MySQL optimization is mainly divided into scheme optimization and query optimization. The high-performance indexing strategies discussed in this chapter fall primarily into the category of structural optimization. The content of this chapter is based entirely on the theoretical foundation above, and in fact once the mechanism behind indexing is understood, selecting high-performance strategies becomes pure reasoning and the logic behind these strategies can be understood.

Index optimization recommendations

1. Prefix index

Prefix index is to replace the whole column with the prefix of the column as the index key. When the prefix length is appropriate, the selectivity of the prefix index can be close to the full-column index, and the size and maintenance overhead of the index file can be reduced because the index key becomes shorter.

In general, prefix indexes can be used in the following cases:

String string (varchar,char,text, etc.), need to match all fields or pre-match. ='xxx' or like 'xxx%'

The string itself may be long, and the first few characters start to differ. For example, it makes no sense for us to use prefix index for Chinese names, because Chinese names are very short, and it is not very practical to use prefix index for receiving addresses, because on the one hand, receiving addresses generally start with XX province, that is to say, the first few characters are similar, and receiving addresses are generally searched like '%xxx %', without using pre-matching. On the contrary, the prefix index can be used for the name of foreigners, because its characters are longer and the selectivity of the first few characters is higher. Also e-mail is a field that can be indexed with prefixes.

The index selectivity of the first half characters is already close to the index selectivity of the full field. If the length of the entire field is 20, the index selectivity is 0.9, and we establish a prefix index for the first 10 characters with a selectivity of only 0.5, then we need to continue to increase the length of the prefix characters, but at this time the advantage of prefix index is not obvious, and there is no need to establish a prefix index too much.

2. The index must be built for primary key external inspection.

Use indexes for columns that appear in where,on,group by,order by.

4, try to choose the column with high discrimination as the index, the formula of discrimination is count(distinct col)/count(*), indicating the proportion of fields that are not repeated, the larger the proportion, the fewer the number of records we scan, the discrimination of the unique key is 1, and some status, gender fields may be distinguished in front of large data is 0.

5. Use indexes on smaller data columns, which makes the index file smaller and allows more index keys to be loaded in memory.

6, index column can not participate in the calculation, keep the column "clean", such as from_unixtime(create_time) = '2014-05-29' can not use the index, the reason is very simple, b+ tree stored in the data table field values, but when retrieving, you need to apply all elements to compare, obviously the cost is too high. So the statement should be written create_time = unix_timestamp ('2014 -05- 29').

Use prefix indexes for longer strings.

8, try to expand the index, do not create a new index. For example, the table already has an index of a, and now you want to add the index of (a,b), then you only need to modify the original index.

9, do not create too many indexes, weigh the relationship between the number of indexes and DML, DML is to insert, delete data operations. There is a trade-off here. The purpose of establishing indexes is to improve query efficiency, but too many indexes will affect the speed of inserting and deleting data, because the table data we modify also needs to be adjusted and rebuilt.

For like queries,"%" should not be placed in front.

SELECT * FROMhoudunwangTHEREunameLIKE 'backing %'--go index.

SELECT * FROMhoudunwangWHEREunameLIKE "% backing %" --does not take index.

11, query where condition data type does not match can not use the index.

String and number comparisons do not use indexes;

CREATE TABLEa(achar(10))。

EXPLAIN SELECT * FROMaTHEREA ="1" -Go index.

EXPLAIN SELECT * FROM A WHERE A=1 -No index.

Regular expressions don't use indexes, which should be easy to understand, so the reason why it's hard to see the regexp keyword in SQL.

At this point, I believe that everyone has a deeper understanding of "MySQL database query and index optimization", may wish to actually operate it! Here is the website, more related content can enter the relevant channels for inquiry, pay attention to us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.