Shulou(Shulou.com)06/01 Report--

This article mainly introduces you to optimize mysql index need to pay attention to what points, the content of the article is the author carefully selected and edited, with a certain pertinence, the reference significance for everyone is still relatively large, the following with the author to understand what points to optimize mysql index need to pay attention to it.

First, the head plate:

Five elements of SQL statements:

1. Query conditions needed to access the result set

2. Define the query conditions required for the result set

3, the size of the result set

4, Number of tables involved in obtaining the result set

How many users modify this data simultaneously?

II. Main course: some features of index and optimization suggestions

1, often changing index column at the end, this will reduce the cost of change

2. The order of index fields is very important. If there is a range query before sorting, index sorting cannot be used, such as:

Index A(a,b.c)

select a,b,c from t where a=1 and b between d1 and d2 order by c;

So you need to sort.

Index B(a,c,b)

you don't have to sort them.

--Remarks:

Generally speaking, B will be preferred without sorting, because generally speaking, the result set of each query of a transaction is very small, and the output result set will be limited. At this time, it will be very fast without sorting. When selecting index A, the result set will be sorted. If the result set (the results satisfying the conditions) is very large, this will be very slow.

3. When the following three conditions are met at the same time, the hidden danger of over-consideration factor may occur:

1)There is no sorting in the access path.

2)The first screen responds as soon as the result is created.

3)Not all predicate fields participate in defining the index slice to be scanned

4. Use short indexes. If you index multiple columns, you should specify a prefix length whenever possible.

For example, if you have a CHAR(200) column, if most values are unique within the first 10 or 20 characters,

Then don't index the entire column. Indexing the first 10 or 20 characters saves a lot of index space

It may also make the search faster. Smaller indexes involve less disk I/O, and shorter values are faster.

More importantly, blocks in the index cache can hold more keys for shorter keys, so MySQL

More values can also be accommodated in memory. This increases the likelihood of finding rows without reading more blocks in the index.

(Of course, some common sense should be used.) For example, indexing with only the first character of a column value is unlikely to be of much benefit because there will not be many different values in the index)

Materialized result set: refers to performing the necessary database access to build the result set (such as sorting)

1)In order to avoid unnecessary work, the database system always materializes the results as late as possible. If the database system materializes the result set first, the system will retrieve records from the temporary table when FETCH is called, and the result set stored in the temporary table will not be updated when the data is updated.

2)A FETCH call materializes a record

If the conditions are met:

(1) There is no sequencing requirement

(2) There is an index that satisfies the result set.

3)prematerialization

(1) Generally speaking, sorting must be materialized in advance. Result set sorting means that only one record needs to be extracted, and the whole result set must be materialized.

6, filter factor hidden danger

Filter factor: Selectivity used to define predicates: The proportion of the total number of rows in a table that satisfy the predicate condition. This value depends on the distribution of column values. When evaluating whether an index is suitable, the worst-case filter factor is more important than the usual filter factor.

Half-width index: An index that contains all columns in the where clause. With a half-width index, the table is accessed only if a matching condition is retrieved, and this access is random.

An index that contains all the fields involved in a select statement, including projected fields and fields in where clauses, without reading the table

Conclusion: The advantage of a wide index is that it avoids read-back of the returned result set.

After reading the above points about optimizing mysql index, many readers must have some understanding, if you need to get more industry knowledge information, you can continue to pay attention to our industry information column.

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.