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 83a41420f3942eb7be1956152f4acece 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.