3. mysql Performance analysis

One, MySQL query optimizer

1. mysql has an optimizer module dedicated to optimizing the SELECT statement, the main function: through the calculation of statistical information collected in the analysis system, the client's Query to provide him with the best execution plan (he thinks the best way to retrieve data, but not necessarily the DBA is considered to be optimal, This part is the most time-consuming) 2. When the client wants MySQL to request a query, and the command parser module completes the classification, distinguishing between select and forwarding to MySQL query optimizer, MySQL query optimizer first optimizes the entire query and handles some constants expression, and then analyze the Hint information in Query (if any) to see whether the Hint information can fully determine the execution plan of the query. If no Hint or Hint information is sufficient to fully determine the execution plan, the statistics of the objects involved are read, the corresponding calculation analysis is written according to query, and then the final execution plan is obtained.

Second, MySQL common bottleneck

1. When saturated, the cpu:cpu typically occurs when data is loaded into memory or read from disk 2. IO: Disk I/O bottlenecks occur when loading data is much larger than memory capacity 3. Performance bottlenecks for server hardware: Top, free, iostat, and Vmstat to see the performance status of the system

Third, the use of explain analysis of SQL (later in detail ...). ）

3. mysql Performance analysis