Shulou(Shulou.com)05/31 Report--

How to monitor index usage in mysql? Many novices are not very clear about this. In order to help you solve this problem, the following editor will explain it in detail. People with this need can come and learn. I hope you can gain something.

1. View the 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, and if this value is high, there are a lot of full index scans.

Handler_read_key represents the number of times an index is used. If we add a new index, we can see if Handler_read_key has increased. If so, sql uses the index.

Handler_read_next stands for reading the following indexes, and range scan usually occurs.

Handler_read_prev stands for reading the above column of the index, which usually occurs in ORDER BY. DESC .

Handler_read_rnd stands for reading rows in a fixed position, and if this value is high, a large number of result sets are sorted, a full table scan is performed, and the appropriate KEY is not used in the associated query.

The Handler_read_rnd_next representative does a lot of table scans and the query performance is poor.

In fact, in most application scenarios, when the index is working, the value of Handler_read_key will be very high, which represents the number of times the index value will be read, and a very low value indicates that the performance improvement of increasing the index is not high, because the index is not often used.

A high value of Handler_read_rnd_next means that the query runs inefficiently and index remediation should be established. 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 and the value of Handler_read_rnd_next is high, it usually indicates that the table index is incorrect or that the query written does not take advantage of the index

2. Check to see if the index is used to the

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,fetch is 0, it has not been used.

3. Check which indexes are used

Explain related sql. Check type to indicate which index type is used in the query.

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

From the best to the worst:

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, which generally appears only in the system table.

Const means that the query can be obtained through an 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 the query to a constant, so it is very fast.

Eq_ref unique index scan. This type usually occurs in join queries with multiple tables, and for each corresponding column joined from the previous table, the corresponding column of the current table has a unique index, and at most 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 data matching. This type usually occurs in multi-table join queries for non-unique or non-primary key indexes, or for queries that use leftmost prefix rule indexes.

Range query for the range index. The value of a range of query index keywords.

Index full-text index scan. Basically the same as all, the full text is scanned, but the fields of the query 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. The index is not used and is the least efficient.

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, but in fact, the data is put in the memory queue and is not really written to disk, which is much faster than each statement is inserted separately; on the contrary, low_priority is inserted after all other users have finished reading and writing to the table.

3) separate index files and data files on different disks (using table-building statements)

4) if you insert in bulk, you can increase the speed by adding the bulk_insert_buffer_size variable value method, but only for MyISAM tables

5) when loading a table from a text file, using load data file is usually 20 times faster than using insert

2. Optimize the group by statement:

By default, mysql sorts all group by fields, similar to order by. If the query includes group by but the user wants to avoid the consumption of sorting results, you can specify that order by null forbids sorting.

3. Optimize the order by statement:

In some cases, mysql can use an index to satisfy order by sentences, so no additional sorting is required. The where condition and order by use the same index, and the order of the order by is the same as the order of the index, and the fields of the order by are in ascending or descending order.

4. Optimize nested queries:

Mysql4.1 starts to support subqueries, but in some cases, subqueries can be replaced by more efficient join, especially when join's passive tables are indexed, because mysql no longer needs to create temporary tables in memory to complete this logically two-step query.

One last point:

A table has up to 16 indexes with a maximum index length of 256bytes, and indexes generally have no significant impact on insert performance (with the exception of a large number of small data), because the time cost of indexing is O (1) or O (logN). However, too many indexes are not good, after all, operations such as updates need to maintain the index.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.