MySQL Slow query parsing-linux command

the MySQL slow query log can be parsed using the Mysqldumpslow command, and the parameters for Mysqldumpslow are as follows:-s, is sorted in any way, C, T, L, R are sorted by record count, time, query time, number of records returned, AC/at/al/AR indicates the corresponding flashback-T, which is the meaning of top N, is the number of data that is returned in front of-g, you can write a regular match pattern in the back , case insensitive for example1, the top 20 sqlmysqldumpslow with the longest SQL execution time-S T-T --G'Select'/opt/data/Slowquery_2016050921.log2, get the first 10 in chronological order contains the query statement containing the left link Mysqldumpalow-S T-TTen-G'Left join'/opt/data/Slowquery_2016050921.log1, explain, desc, (MySQL command) to see the efficiency of SQL execution, to help us analyze the SELECT statement explain: This command is to see how the query optimizer determines the main method of the query, this feature has limitations, is only an approximate result, Sometimes he is a good approximation, sometimes it may be very far. But its output can get the most accurate information. Eg1:explainSelect* fromStudentswhereIdinch(2,3); columns in explain: ID: Just a number, the greater the ID the higher the prioritySelecttype: There are several values simple: The simplest select, without union and subquery primary: The outermost Select, the outermost select of the statement with the subquery is primaryderived: Represents the select,mysql recursive execution in the FROM clause and puts the result in a temporary table, which is internally referred to as a derived table, type column: Refers to the type of access for MySQL, that is, the rows in the lookup table are the most important access methods, from the worst to the best all<index<range<ref<eq_ref<Const,system<NULLall: Scan entire table index: Only the index tree is scanned with all the same as the whole table is just a range from the index; Range Scan, is a restricted index scanrefA non-unique value index scan that returns all rows that match a eq_ref: Returns only one record that matches the criteriaConst/System: Converts some accesses into a constant;NULL: Ability to decompose query statements in the optimization phase key_len: Number of bytes used by index key: no index usedref: The column that displays the index is used rows: number of request rows representing query data extra:? This column contains additional information that is not suitable for display in other columns. Some of the more important ones are as follows:usingIndex "This value indicates that MySQL will use the overwrite index to avoid accessing the table. using where"indicates that the query uses the WHERE conditionusingtemporary "This means that MySQL uses a temporary table when sorting the results of the query. usingFilesort This means that MySQL uses an external index to sort the results instead of reading rows from the table in indexed order. Limitations:1, explain does not take into account the impact of triggers, stored procedures, or functions on queries2, explain does not consider the impact of caching on query results3and does not consider MySQL execution query for all specific optimizations4, is based on statistical information estimates, not accurate values5Mysql5.6 only supported beforeSelectexplainmysql Profile-analyzing the overhead of SQL execution in the MySQL database, you can enable SQL profiling by configuring the profiling parameter. When this parameter is turned on, subsequent SQL statements will record their resource overhead, such as IO, context switches, cpu,memory, and so on.?Set@ @profiling =1;? See if profile is turned on?Select@ @profiling;? 1 is on, 0 is off? When the profile is turned on, the SQL executed will be recorded by the profile. Show profiles; You can see the Sql?show profile that is currently logged forquery N; query_id?show profile CPU in #n为show profile, block io forQuery1; View the time, CPU, and IO of SQL execution

MySQL Slow query parsing-linux command