慢sql排查

STATUS

image.png

STATUS解释

Handler_read_first
The number of times the first entry was read from an index.
If ``this value is high, it suggests that the server is doing a lot of full index scans;
for example, SELECT col1 FROM foo, assuming that col1 is indexed.
索引中第一条被读的次数。如果较高，它建议服务器正执行大量全索引扫描；例如，SELECT col1 FROM foo，假定col1有索引。

Handler_read_key
The number of requests to read a row based on a key.
If ``this value is high, it is a good indication that your tables are properly indexed ``for your queries.
根据键读一行的请求数。如果较高，说明查询和表的索引正确。

Handler_read_next
The number of requests to read the next row in key order.
This value is incremented ``if you are querying an index column with a range constraint or ``if you are doing an index scan.

按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列，该值增加。

Handler_read_prev
The number of requests to read the previous row in key order.
This read method is mainly used to optimize ORDER BY ... DESC.

按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY ... DESC。

Handler_read_rnd
The number of requests to read a row based on a fixed position.
This value is high ``if you are doing a lot of queries that require sorting of the result.
You probably have a lot of queries that require MySQL to scan entire tables or you have joins that don't use keys properly.

根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。

Handler_read_rnd_next
The number of requests to read the next row in the data file.
This value is high ``if you are doing a lot of table scans.
Generally ``this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.
|

在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。

从第二条执行计划可以看出，shotel表只用到idx_supplier_id并没有用到shotel的主键，handler_read_rev数值很大，猜测应该是扫描得到结果，所以速度很慢。