MySQL database optimization technology-index usage tips and mysql usage tips

MySQL database optimization technology-index usage tips and mysql usage tips

This example summarizes the index usage of MySQL database optimization technology. We will share this with you for your reference. The details are as follows:

Next to the MySQL database optimization technology configuration tips summary, we will further analyze the index optimization skills:

(7) Table Optimization

1. select an appropriate Data Engine

MyISAM: Suitable for reading a large number of tables.

InnoDB: Suitable for writing and reading a large number of tables

2. Select the appropriate column type

You can use SELECT * FROM TB_TEST procedure analyse () to analyze each field in the table and provide suggestions for optimizing the column type.

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

4. Create an appropriate index

5. Use a fixed-length field to accelerate the process.

(8) Indexing principles

1. Use indexes reasonably

One Table can only use one index in one query. The EXPLAIN statement is used to check the operation of the optimization program.

Use analyze to help the optimizer make more accurate predictions on the index usage.

2. Indexes should be created on the Data columns involved in operations such as search, sorting, and grouping.

3. Try to create an index in a column with few duplicate data, so it is best to make the index unique.

For example, you can create an index for the birthday column, but do not create an index for the Gender column.

4. Try to compare shorter values for Indexing

Reduces disk I/O operations. The index buffer can accommodate more key values and increase the hit rate.

If you create an index on a long string, you can specify a prefix length.

5. Rational Use of Multi-column Indexes

If multiple conditions often need to be combined for query, you need to use multiple column indexes (because only one index can be used for a single table query at a time, and only one index can be used for creating multiple single column indexes)

6. Make full use of the leftmost prefix

That is to say, we need to reasonably arrange the order of the columns in the Multi-column index to rank the most commonly used columns first.

7. Do not create too many indexes.

Only the fields that are often used in where, order by, and group by must be indexed.

8. Use the slow query log to find the slow query (log-slow-queries, long_query_time)

(9) Make full use of Indexes

1. Try to compare columns with the same data type

2. Try to make the index column independent in the comparison expression. WHERE mycol <4/2 uses the index, while WHERE mycol * 2 <4 does not use

3. Try not to add a function to the query field,

For example, where year (date_col) <1990 is transformed into WHERE date_col <'2017-01-01'

WHERE TO_DAYS (date_col)-TO_DAYS (CURDATE () <cutoff transformed to WHERE date_col <DATE_ADD (CURDATE (), INTERVAL cutoff DAY)

4. Do not use wildcards at the beginning of the LIKE mode.

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

6. force index is used to force the specified index. For example, select * from song_lib force index (song_name) order by song_name is more efficient than force index.

7. Avoid using MySQL for automatic type conversion whenever possible. Otherwise, indexes cannot be used. For example, use where num_col = '5' for int-type num_col'

(10) Optimization of SQL statements

1. Create a suitable statistical intermediate result table to reduce the chance of querying data from a large table

2. Try to avoid subqueries and use the connection method instead. For example:

SELECT a.id, (SELECT MAX(created) FROM posts WHERE author_id = a.id) AS latest_postFROM authors a

You can change it:

SELECT a.id, MAX(p.created) AS latest_postFROM authors AS aINNER JOIN posts p ON (a.id = p.author_id)GROUP BY a.id

select song_id from song_lib where singer_id in(select singer_id from singer_libwhere first_char='A') limit 2000

Changed:

select song_id from song_lib ainner join singer_lib b on a.singer_id=b.singer_id and first_char='A' limit 2000

3. When the duplicate key is inserted, use the on duplicate key update:
Copy codeThe Code is as follows: insert into db_action.action_today (user_id, song_id, action_count) values (1, 1) on duplicate key update action_count = action_count + 1;

4. Avoid using cursors.

The operation efficiency of cursors is extremely low. You can add temporary tables, use multi-table queries, and update multiple tables to complete tasks. Do not use cursors.

(11) Use Explain to analyze the index usage of SQL statements

When you put the keyword "EXPLAIN" in front of a SELECT statement, MySQL explains how it will process the SELECT statement, and provides information about how the table is joined and in what order, with the help of "EXPLAIN, you can know when to add an index to the table to get a faster SELECT statement that uses indexes to search for records. You can also know whether the optimizer joins the table in the optimal order. To force the optimizer to use a specific join order for a SELECT statement, add a STRAIGHT_JOIN clause. .

The general syntax of the EXPLAIN command is: EXPLAIN <SQL command> For example: explain select * from a inner join B on a. id = B. id

EXPLAIN the parameters of the EXPLAIN analysis result:

1. table: the name of the table.

2. type: type of the connection operation.

System: there is only one record in the table (only one data table is used in actual applications)

Const: A table can have at most one matching row. It is used to compare all the parts of the primary key or UNIQUE index with a constant value,

For example:

select * from song_lib where song_id=2

(Song_id is the table's primary key)

Eq_ref: for each row combination from the preceding table, read a row from the table using the index of UNIQUE or primary key,

For example:
Copy codeThe Code is as follows: select * from song_lib a inner join singer_lib B on a. singer_id = B. singer_id
(Type value of B is eq_ref)

Ref: for each row combination from the preceding table, read a row from the table with a non-UNIQUE or primary key index.

For example:
Copy codeThe Code is as follows: select * from song_lib a inner join singer_lib B on a. singer_name = B. singer_name
And
Copy codeThe Code is as follows: select * from singer_lib B where singer_name = 'ccc '(The type value of B is ref, because B. singer_name is a common index)

Ref_or_null: The join type is like ref, but MySQL can search for rows containing NULL values,

For example:
Copy codeThe Code is as follows: select * from singer_lib where singer_name = 'ccc 'or singer_name is null

Index_merge: The join type indicates that the index merge optimization method is used.

Key: it displays the name of the index actually used by MySQL. If it is NULL, MySQL does not use an index.

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

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

4. rows: number of records that MySQL deems necessary to scan before finding the correct result. Obviously, the ideal number here is 1.

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

Using where: indicates that the where condition is used.

Using filesort: indicates that File Sorting is used, that is, the order by clause is used, and the index of the Field in order by is not used, which requires additional sorting overhead, therefore, if using filesort appears, the sorting efficiency is very low and needs to be optimized. For example, the force index method is used)