If a table contains several indexes and a select query does not have a “force index” hint clause, the MySQL optimizer take charge of determining which index to use. In some cases, we find that MySQL optimizer does not select the best index.
Here we discuss a strategy to deal with the problem, using a new parameter.
1、table and index statistics
The statistics contains the number of different key values in a given index, which affects the decision of index selection.
In a big table, it is not workable that scans all the rows and counts the accurate statistics. So InnoDB pick up some example rows to estimate the result. The variable “innodb_stats_sample_pages “is used to define the number of example rows. Its default value is 8.
There should be a balance consideration about this variable. Because the function “dict_update_statistics”, which is used to update a table’s statistics, is automatically called at various times. The bigger the innodb_stats_sample_pages is, the more accurate result comes, but it may lead to excessive I/O and CPU.
2、When the dict_update_statistics called?
There are some scenarios that dict_update_statistics will be called.
a) Statements like “show status”
Such as “show index from table-name” and “show status like ‘’”. There is a variable named “innodb_stats_on_metadata” to control whether run dict_update_statistics in such queries. Default value is ON.
b) Table monitor
When a table named “innodb_table_monitor”(InnoDB table) is created, table monitor will be run every minute. “dict_update_statistics” is called here.
c) Analyze table table-name
d) Dynamically during insert/update operation.
This is why the innodb_stats_sample_pages cannot be set too big. There is a counter to record the times of modifying indexed column of this table from last dict_update_statistics. When the counter is up to 2000000000 or 1/16 of the table row number, dict_update_statistics is called.
3、Force re-calculate statistics using analyze table
Let’s look into the next steps:
Set innodb_stats_sample_pages = BIGNUM;
Analyze table table-name;
Set innodb_stats_sample_pages = 8;
Obviously this does not make sense, for the reason of d) in last section.
4、Strategy for certain requirement
But let’s think about this case, when there are lots of rows in a table, and the row number will keep relatively stable in a period.
We run the analyze table command, and test the queries that will run upon this table, check that the index selection working well.
Since the dynamically re-calculating may get wrong statistics and then leads to wrong index-selection, we plan to disable the mechanism, using a variable that can be update by command “set global”.
The variable can be named “innodb_stats_dynamically”, ON as default.
So when we think the data number is big enough and will be relatively stable in a period afterward, the sample commands can be as follow:
Set innodb_stats_dynamically = off;
Set innodb_stats_sample_pages = BIGNUM;
Analyze table table-name;
Set innodb_stats_sample_pages = 8; (optional)
- 大小: 45 KB
分享到:
相关推荐
Updating to Oracle Solaris 11.3-8
The second edition of a bestselling textbook, Using R for Introductory Statistics guides students through the basics of R, helping them overcome the sometimes steep learning curve. The author does ...
problem of refining a visual reconstruction to produce jointly optimal structure and viewing parameter estimates. Topics covered include: the choice of cost function and robustness; numerical ...
Revisiting and updating ideas pioneered by 1950s aerospace engineers in their System Safety concept, and testing her new model extensively on real-world examples, Leveson has created a new approach to...
In a nonclustered index, the leaf level contains each index key, plus a bookmark that tells SQL Server where to find the data row corresponding to the key in the index. A bookmark can take one of two ...
12.9 Migrating a Zone to a New System 334 12.10 Deleting a Zone 336 12.11 Listing the Zones on a System 336 12.12 Zones Usage Examples 337 12.12.1 Adding a Dedicated Device to a Non-Global Zone 337 ...
Dynamic Fault Diagnosis Using the Improved Linear Evidence Updating Strategy
Oracle Solaris 11.2 Updating to Oracle Solaris 11.2-12
This project model is not meant to be a tool to justify creating impositions for developers, but as a tool to facilitate coordination. It is meant as a description of the project, with an overview of...
Fortunately, to get started you just need to master a few essential techniques. Read on! Learn Git in a Month of Lunches introduces the discipline of source code control using Git. Helpful for both ...
PLEASE TAKE A LOOK AT THE "WHAT's NEW IN THIS VERSION" LINK IN THE HELP FILE AS IT HAS CONVENIENT LINKS TO ALL OF THE NEW TOPICS. ==================== Version 3.10 Build 1 - Several bug fixes. - ...
PATCH-更新系统是坚如磐石,专业,智能,清洁的解决方案,用于管理和分发游戏和应用程序的更新。 您的玩家最终可以毫无痛苦地更新您的游戏,而不必在新版本到来时再次下载整个游戏。这将为他们节省大量带宽和时间!...
This book presents a comprehensive introduction to Internetware, covering aspects ranging from the fundamental principles and engineering methodologies to operational platforms, quality measurements ...
Fuzzy Strategy Updating in the Prisoner’s Dilemma Game
1. If you have delegated domain-wide access to the service account and you want to impersonate a user account, specify the email address of the user account using the method setSubject: ```php $...
Modeling and Parameter Updating for Nosiheptide Fed-Batch Fermentation Process
Thanks go out to SER[G]ANT for updating the russion translation files already June 23 2018:Cheat Engine 6.8.1 Released: Apparently 6.8 contained a couple of annoying bugs, so here's an update that ...
android wifi-module realtek rtl8188eu driver
we design a model updating strategy to adapt the appearance changes over time by discarding degraded trees of the BTM and DTM and initializing new trees as replacements. We test the proposed tracking ...
Model updating有关使用实测数据修正有限元模型的资料