The events_statements_current table displays the current statement for each connection or thread. Statement event tables provide information about recently run queries. The table_io_waits_summary table provides statistical information about table and index usage. The file_summary_by_event_name table contains statistical information for obtaining file I/O. The events_statements_summary_by_digest table aggregates data similar to queries. The events_statements_history table provides statistics on the most recently executed queries. Performance Schema has a large amount of performance-related data. Users can also conduct in-depth research on individual queries to view fine-grained statistics about queries. The query analyzer of MySQL Enterprise Monitor provides an overview that enables users to quickly identify expensive queries. What are the tools for monitoring, analysis and query optimization?Īfter understanding the principles of MySQL optimizer, let’s use some tools to monitor and analyze the performance of MySQL. This may be due to the inaccuracy of the data on which the decision is based or to the inaccuracy of the cost model itself. Sometimes, however, the optimizer fails to find the best plan. Using this model, the optimizer chooses the best plan in most cases. Cost constants are stored in database tables and can be changed to better represent system characteristics. In MySQL 5.7, the cost model has been configured. From the data dictionary, we use information about rows and indexes: such as the length of rows and keys, uniqueness, and whether columns can be null. For the index, the cardinality is also obtained, that is, how many different column values are there, and the number of rows in the index range. The main statistical information is the number of rows in the table. The cost model uses information from the data dictionary and statistics from the storage engine to calculate. These are the costs of the basic operations that MySQL servers perform when executing queries. In addition to the cost formula, the cost model also contains a set of “cost constants”. The cost model consists of formulas for calculating the cost of different operations and estimating the number of rows. In addition to cost estimates, it also estimates the number of rows generated by the operation. As output, it generates an estimate of the cost of performing this operation. This is a very simple view of the cost model in MySQL.Īs input, it requires basic operations, such as reading data from a table or joining two tables. Ultimately, it will choose the plan with the lowest cost. Based on these costs, the optimizer calculates the cost of alternative plans.
All other cost figures relate to this unit of cost. The unit of cost is the cost of reading random data pages from disk.
Therefore, the overall idea of query optimization is shown in the figure above.Ĭost-based query optimizers allocate costs for different operations, such as table scanning and index lookup. It also relies on metadata information in the data dictionary. At the same time, the optimizer also makes decisions based on the statistical information of the data obtained from the storage engine. In the optimization process, the optimizer chooses the join order, decides which index to use, and so on. The cost of the plan roughly reflects the resources required for the query, and the main factor is the number of rows to be accessed when calculating the query. The MySQL optimizer takes the SQL query as input and generates a plan to execute the query.įor example, in what order should tables be joined and which indexes should be used? The goal is to find the best execution plan from many possible execution plans. Next, a brief introduction to the relevant content.
#Mysql optimizer cost model how to
Data access method and how to select index.ĥ. What are the tools for monitoring, analysis and query optimization?ģ. The content is divided into the following aspects:Ģ. The following is the relevant content collation for your reference. Xu Yitao shared the related content of MySQL query optimization at the MySQL team’s network seminar. In this article, the specific content can be concerned about the public number: Aikesheng Open Source Community, Watch Video of Web Seminar。 The article is reproduced from Public No.
0 kommentar(er)
