MySQL query analysis and optimization

MySQL query and Analysis and Optimization Show www.2cto.com show status -- display status information (Extended show status like 'xxx') show variables -- display system variables (Extended show variables like 'xxx ') show engine innodb status -- display the InnoDB Storage engine status show processlist -- view the current SQL Execution, mysqladmin variables-u username-p password, including execution status and whether to lock the table -- display the system variable mysqladmin extended-status-u username-p password -- display the status information ExplainTable: show the rows of data about which table possible_keys: displays the indexes that may be applied to this table. If it is null, there is no possible index. You can select an appropriate statement www.2cto.com key from the WHERE statement for the related domain: actually used index. If it is NULL, no index is used. MYSQL rarely selects indexes with insufficient optimization. In this case, you can USE the use index (index) in the SELECT statement to forcibly USE an INDEX or use ignore index (INDEX) to forcibly IGNORE the index key_len: the length of the index used. The shorter the length, the better. ref: displays which column of the index is used. If possible, it is a constant rows: mySQL considers the type of the number of rows that must be retrieved to return the request data: This is one of the most important fields and shows the type used in the query. From the best to the worst connection types are system, const, eq_reg, ref, range, index, ALLsystem, and const: You can convert the queried variables into constants. for example, id = 1; id is the primary key or unique key. eq_ref: access the index and return the data of a single row. (The index used for query is a primary key or unique key.) ref: access the index and return the data of a certain value. (multiple rows can be returned) usually use =. range: This connection type uses an index to return rows in a range, for example, using> or <lookup, in addition, when an index is created for this field (Note: Not necessarily better than the index), index: scans the entire table in the order of the index. The advantage is that no sorting is required, the disadvantage is that the full table scan is ALL: full table scan should be avoided as much as possible Extra: There are mainly the following types of using indexes for how MYSQL parses the query Extra information: only the index is used, table access is avoided. using where: use where to overwrite data. not all where clause must display using where. for example, use = to access the index. using tmporary: use temporary table using filesort: use additional sorting. (When order by v1 is used and no index is used, additional sorting will be used.) range checked for eache record (index map: N): no good index. profiling is Enabled: mysql> set profiling = 1; show profiles; you can get the time when the SQL statement is executed and IDshow profile for query 1; after obtaining the detailed information of the corresponding SQL statement execution, close the parameter: mysql> set profiling = 0