Summary of index usage skills for MySQL database optimization technology_MySQL

The examples in this article summarize the index usage of MySQL database optimization technology. Share it with everyone for your reference, the details are as follows:

Here follows the previous article "Summary of Configuration Techniques of MySQL Database Optimization Technology" to further analyze the index optimization techniques:

(7) Table optimization

1. Choose the right data engine

MyISAM: Table suitable for large number of read operations

InnoDB: A table suitable for large amounts of writing and reading

2.Choose the appropriate column type

Use SELECT * FROM TB_TEST PROCEDURE ANALYSE() to analyze each field of this table and give suggestions for optimizing column types

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

4. Create a suitable index

5. Use fixed-length fields, which are faster than variable-length fields

(8) Indexing principles

1. Use indexes appropriately

A Table can only use one index in a query. Use the EXPLAIN statement to check the operation of the optimizer

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

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

3. Try to build the index in a data column with less duplicate data. It is best because it is unique

For example: the birthday column can be indexed, but the gender column should not be indexed

4. Try to index shorter values

Reduce disk IO operations, the index buffer can accommodate more key values, and improve the hit rate

If indexing a long string, you can specify a prefix length

5. Proper use of multi-column indexes

If multiple conditions often need to be combined for queries, a multi-column index must be used (because only one index can be used for a query on a table, and only one can be used to create multiple single-column indexes)

6. Make full use of the leftmost prefix

That is to say, the order of the columns in the multi-column index must be reasonably arranged, and the most commonly used ones should be ranked first

7. Don’t create too many indexes

Only fields that are frequently used in where, order by, and group by need to be indexed.

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

(9) Make full use of index

1. Try to compare data columns with the same data type

2. Make the index column independent in the comparison expression as much as possible, WHERE mycol < 4 / 2 uses the index, but WHERE mycol * 2 < 4 does not use it

3. Try not to add functions to query fields,

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 is transformed into WHERE date_col < DATE_ADD(CURDATE(), INTERVAL cutoff DAY)

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

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

6. Use force index to force the use of the specified index. For example, select * from song_lib force index(song_name) order by song_name is more efficient than not using force index

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

(10) Optimization of SQL statements

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

2. Try to avoid using subqueries and use joins instead. 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 in
(select singer_id from singer_lib
where first_char='A'
) limit 2000

Changed to:

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 to determine duplicate keys, use ON DUPLICATE KEY UPDATE:

The code is 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

The operating efficiency of cursors is extremely low. You can complete the task by adding temporary tables, using multi-table queries, multi-table updates, etc. Do not use cursors.

(11) Use Explain to analyze the use of indexes by SQL statements

When you put the keyword EXPLAIN before a SELECT statement, MySQL explains how it will process the SELECT, providing information about how the tables are joined and in what order. With the help of EXPLAIN, you can know when you must index the table to get A faster SELECT that uses an index to find records, and you also know whether the optimizer is joining the tables in an 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 For example: explain select * from an inner join b on a.id=b.id

Detailed explanation of EXPLAIN’s analysis result parameters:

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

2.type: Type of connection operation.

system: There is only one record in the table (in actual applications, tables with only one data are rarely used)

const: The table has at most one matching row, used when comparing all 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 primary key of the table)

eq_ref: For each combination of rows from the previous table, read a row from the table using the index of UNIQUE or PRIMARY KEY,

For example:

The code is 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: For each combination of rows from the previous table, read a row from the table using an index other than UNIQUE or PRIMARY KEY

For example:

The code is as follows:

select * from song_lib a inner join singer_lib b on a.singer_name=b.singer_name

and

The 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 normal index)

ref_or_null: This join type is like ref, but with the addition of MySQL, it can specifically search for rows containing NULL values,

For example:

The code is as follows:

select * from singer_lib where singer_name='ccc' or singer_name is null

index_merge: This join type indicates that the index merge optimization method is used

Key: It shows the name of the index actually used by MySQL. If it is empty (or NULL), MySQL does not use the index.

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

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

4.rows: The number of records MySQL thinks it must scan before finding the correct result. Obviously, the ideal number here is 1.

5.Extra: Many different options may appear here, most of which will have a negative impact on the query. Generally include:

using where: means using where condition

using filesort: means 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, it means that the sorting efficiency is very low. Optimization is needed, such as using force index

Readers who are interested in more MySQL-related content can check out the special topics on this site: "Summary of MySQL Index Operation Skills", "Comprehensive Collection of MySQL Log Operation Skills", "Summary of MySQL Transaction Operation Skills", "Comprehensive Collection of MySQL Stored Procedure Skills", " Summary of MySQL database lock related skills" and "Summary of commonly used MySQL functions"

I hope this article will be helpful to everyone’s MySQL database planning.