MySQL index usage monitoring skills (worth collecting!)

Overview

In a relational database, an index is a separate, physical storage structure that sorts the values ​​of one or more columns in a database table. It is a collection of values ​​in one or more columns in a table and a corresponding list of logical pointers to the data pages in the table that physically identify these values.

MySQL supports hash and btree indexes. InnoDB and MyISAM only support Btree indexes, while the Memory and Heap storage engines can support both hash and Btree indexes.

1. View current index usage

We can query the current index usage with the following statement:

• Handler_read_first represents the number of times the index header is read. If this value is high, it means that there are many full index scans.
• Handler_read_key represents the number of times an index is used. If we add a new index, we can check whether Handler_read_key has increased. If so, it means that SQL uses the index.
• Handler_read_next represents reading the next element of the index, which usually results in a range scan.
• Handler_read_prev represents reading the previous column of the index, which usually occurs in ORDER BY ... DESC.
• Handler_read_rnd represents reading rows at a fixed position. If this value is high, it means that a large number of result sets are sorted, full table scans are performed, and associated queries do not use appropriate keys.
• Handler_read_rnd_next indicates that many table scans are performed and query performance is poor.

In fact, in many application scenarios, when the index is working, the value of Handler_read_key will be very high. This value represents the number of times a row reads the index value. A very low value indicates that the performance improvement obtained by adding the index is not high because the index is not used frequently.

A high value for Handler_read_rnd_next means that queries are running inefficiently and should be remedied by indexing. This value means the number of requests to read the next line in the data file. If a large number of table scans are being performed, a high value for Handler_read_rnd_next usually indicates that the table is not indexed correctly or that the queries written do not take advantage of the indexes.

2. Check whether the index is used

SELECT
 object_type,
 object_schema,
 object_name,
 index_name,
 count_star,
 count_read,
 COUNT_FETCH 
FROM
 PERFORMANCE_SCHEMA.table_io_waits_summary_by_index_usage;

If the number of read and fetch are both 0, it means that it has not been used.

3. Check which indexes are used

Explain the relevant SQL and check the type to see which index type is used in the query

+-----+-------+-------+-----+--------+-------+---------+-------+
| ALL | index | range | ref | eq_ref | const | system | NULL |
+-----+-------+-------+-----+--------+-------+---------+-------+

From best to worst they are:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

• There is only one record in the system table, and generally it only appears in the system table.
• const means that the query is found through a single index query. Generally, the corresponding index column is primarykey or a constant is specified in the unique where statement. Because only one row of data is matched, MYSQL can optimize this query to a constant, so it is very fast.
• eq_ref Unique index scan. This type usually appears in multi-table join queries. For each corresponding column connected from the previous table, the corresponding column of the current table has a unique index, and at most only one row of data matches it.
• ref Non-unique index scan. Same as above, but the corresponding column of the current table does not have a unique index, and there may be multiple rows of matching data. This type of query usually occurs in multi-table join queries, queries against non-unique or non-primary key indexes, or queries using the leftmost prefix rule index.
• range query on the range index. Query the values ​​of a range of index keywords.
• index Full-text index scan. Basically the same as all, the entire text is scanned, but the queried fields are included in the index, so there is no need to read the data in the table, only the fields in the index tree.
• all Full text scan. No index is used, which is the lowest efficiency.

By the way, here are a few optimization points:

1. Optimize the insert statement:

1) Try to use insert into test values(),(),(),()...
2) If you insert multiple rows from different customers, you can get a higher speed by using the INSERT delayed statement. Delayed means that the INSERT statement is executed immediately. In fact, the data is placed in a memory queue and is not actually written to disk. This is much faster than inserting each statement separately. Low_priority is just the opposite. Insertion is performed after all other users have finished reading and writing the table.
3) Store index files and data files on different disks (using table creation statements)
4) If you are doing batch inserts, you can increase the bulk_insert_buffer_size variable value to increase the speed, but only for MyISAM tables.
5) When loading a table from a text file, use load data file, which is usually 20 times faster than using insert

2. Optimize the group by statement:

By default, MySQL sorts all group by fields, which is similar to order by. If the query includes a group by but the user wants to avoid the consumption of sorted results, you can specify order by null to suppress the sort.

3. Optimize the order by statement:

In some cases, MySQL can use an index to satisfy the ORDER BY clause, thus eliminating the need for additional sorting. The where condition and order by use the same index, and the order by order is the same as the index order, and the order by fields are in ascending or descending order.

4. Optimize nested queries:

MySQL 4.1 began to support subqueries, but in some cases, subqueries can be replaced by more efficient joins, especially when the passive table to be joined has an index. The reason is that MySQL does not need to create a temporary table in memory to complete this query that logically requires two steps.

Finally, a point:

A table can have a maximum of 16 indexes, and the maximum index length is 256 bytes. Indexes generally do not significantly affect insert performance (except for large amounts of small data) because the time overhead of creating an index is O(1) or O(logN). However, too many indexes are not good either, after all, operations such as updates require maintaining indexes.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.