A summary of index usage techniques of MYSQL database optimization technology _mysql

This paper summarizes the indexing usage of MySQL database optimization technology. Share to everyone for your reference, specific as follows:

Here is a summary of the configuration techniques of MySQL database optimization technology to further analyze the techniques of index optimization:

(vii) Optimization of tables

1. Choose the right Data engine

MyISAM: Tables for a large number of read operations

InnoDB: Suitable for a large number of read-write tables

2. Select the appropriate column type

Use the SELECT * from Tb_test PROCEDURE analyse () to analyze each field of the table, giving suggestions for optimizing column types

3. Use NOT NULL for columns that do not save null values, which is especially important for the columns you want to index

4. Establish the appropriate index

5. Use fixed-length fields, faster than longer

(eight) Establishment of indexing principle

1. Rational use of indexes

A table can use only one index in a query, using the Explain statement to verify the optimizer's operation

Use analyze to help the optimizer make more accurate predictions about how indexes are used

2. Indexes should be created on the data columns involved in the search, sorting, and group operations

3. Try to build the index in a data column with less data, so the best

For example: Birthday columns, you can index, but sex columns do not index

4. Index the relatively short value as far as possible

Lower disk IO operation, can hold more key values in the index buffer, improve the hit rate

If an index is established on a long string, you can specify a prefix length

5. Rational use of multiple-column indexes

If more than one condition often requires a combination of queries, use a multiple-column index (because a table query can only use one index at a time, you can create multiple Single-column indexes only one)

6. Make full use of the left prefix

That is, to reasonably arrange the order of columns in a multiple-column index, place the most commonly used in the front

7. Do not create too many indexes

Only fields that are frequently applied to where,order by,group by are required to be indexed.

8. Use slow query log to find out slow query (Log-slow-queries, Long_query_time)

(ix) Making full use of the index

1. Compare data columns with the same data type as possible

2. Make the index column independent in the comparison expression as much as possible, where MyCol < 4/2 uses the index, and where MyCol * 2 < 4 does not use

3. Do not add function to query field as far as possible,

For example: where year (Date_col) < 1990 is transformed into where Date_col < ' 1990-01-01 '

where To_days (Date_col)-To_days (Curdate ()) < cutoff converted to where Date_col < Date_add (Curdate (), INTERVAL Cutoff day)

4. Do not use wildcard characters at the beginning of like mode

5. Use the straight join to force the optimizer to join in the order of the FROM clause, select straight join, Force all joins, or select * from a straight join B to force the order of two tables.

6. Use the Force index to force the specified index to be used. such as SELECT * FROM Song_lib Force index (SONG_NAME) order by song_name rather than force index high efficiency

7. Try to avoid using MySQL automatic type conversion, otherwise you will not be able to use the index. such as the num_col of int type with where num_col= ' 5 '

(10) Optimization of SQL statements

1. Create appropriate statistical intermediate result tables to reduce the probability of querying data from large tables

2. Try to avoid using subqueries instead of connecting. For example:

Select a.ID (select MAX (created) from posts WHERE author_id = a.id) as Latest_post from
authors a

Can be changed to:

SELECT a.id, MAX (p.created) as latest_post from
authors as a
INNER JOIN posts p on (a.id = p.author_id)
GROUP by a.id

Select song_id from Song_lib where singer_id to
(select singer_id from Singer_lib
where first_char= ' A '
) L Imit 2000

Change into:

Select song_id from Song_lib a
inner join Singer_lib B on a.singer_id=b.singer_id and first_char= ' a ' limit 2000

3. When inserting the judgment duplicate key, use on DUPLICATE key UPDATE:

Copy Code code as follows:

Insert into Db_action.action_today (User_id,song_id,action_count) VALUES (1,1,1) on DUPLICATE KEY UPDATE action_count= action_count+1;

4. Avoid using cursors

Cursors are extremely inefficient and can be accomplished by adding temporary tables, using multiple table queries, and multiple table updates, and do not use cursors.

(11) using explain to analyze the use of indexes in SQL statements

When you put a keyword explain,mysql in front of a SELECT statement explaining how it will handle the Select, provides information about how the table is joined and in what order, and with the help of explain, You know when you have to add an index to a table to get a faster select that uses an index to find records, and you know whether the optimizer joins the table in a best order. To force the optimizer to use a specific join order for a SELECT statement, add a straight_join clause.

The general syntax for the EXPLAIN command is: EXPLAIN <sql command > such as: EXPLAIN SELECT * from a INNER join B on a.id=b.id

Explain analysis of the results of the parameters detailed:

1.table: This is the name of the table.

2.type: The type of connection operation.

System: Only one record in the table (a table with very few actual applications)

Const: A table has at most one matching row, used to compare all parts of a primary key or a unique index with a constant value,

Such as:

SELECT * FROM Song_lib where song_id=2

(song_id is the primary key of the table)

Eq_ref: For each combination of rows from the previous table, a row is read from the table with the index of the unique or primary key,

Such as:

Copy Code code as follows:

SELECT * FROM Song_lib a INNER join Singer_lib B on a.singer_id=b.singer_id

(The type value of B is eq_ref)

Ref: Read a row from the table with a non-unique or primary key for each row combination from the previous table

Such as:

Copy Code code as follows:

SELECT * FROM Song_lib a INNER join Singer_lib B on a.singer_name=b.singer_name

And

Copy Code code as follows:

SELECT * from Singer_lib b where singer_name= ' CCC '

(The type value of B is ref, because B.singer_name is a normal index)

Ref_or_null: The join type is like ref, but the addition of MySQL can specialize in searching for rows that contain null values.

Such as:

Copy Code code as follows:

SELECT * from Singer_lib where singer_name= ' CCC ' or singer_name is null

Index_merge: This join type represents the use of an index merge optimization method

Key: It shows the name of the index that MySQL actually uses. If it is empty (or null), MySQL does not use the index.

Key_len: The length of the part used in the index, in bytes.

3.ref:ref column shows which column or constant is used to select rows from the table with the key

The number of records that 4.rows:mysql thinks it must scan before it finds the correct result. Obviously, the ideal figure here is 1.

5.Extra: There may be many different options, most of which will have a negative impact on the query. Generally there are:

Using where: means the Where condition is used

Using Filesort: Indicates the use of a file sort, which uses an ORDER BY clause, and does not use the index of the field in the order by, which requires additional sorting overhead, so if a using filesort indicates that the sorting is inefficient and needs to be optimized, such as the use of mandatory indexing method (Force index)

More information about MySQL interested readers can view the site topics: "MySQL Index operation skills Summary", "MySQL Log Operation skills Encyclopedia", "MySQL Transaction operation skills Summary", "MySQL stored process skills encyclopedia", "MySQL database lock related skills summary" and " MySQL common function Big Summary "

I hope this article will help you with the MySQL database meter.