1. What is the cardinality? If this type of index is unique, then the cardinality = number of rows. If this column is sex, and the enumeration type only has male and female, then its cardinality is 2 The higher the Cardinality, the more worthy the column is to be indexed. MySQL execution plan also selects indexes based on Cardinality. The cardinality of each column in the table can be seen in the following way. Take this classic example: 2. When does InnoDB update the cardinality? 3. The base number is estimated The base number is not updated in real time! And it is a value estimated through sampling! As for what the formula for the base is, it may not be important. It is important to know that it is an estimate calculated by randomly sampling data pages. And the number of randomly sampled pages can be set by the parameter innodb_stats_persistent_sample_pages, the default value is 20.
4. Persistent Cardinality You can use the parameter innodb_stats_persistent to control whether the cardinality is persisted. The default setting is off. Of course you can set STATS_PERSISTENT=1 for a single table and its innodb_stats_persistent will be automatically enabled. The advantage of enabling it is that restarting MySQL will not recalculate this value, thus speeding up the restart process. 5. How to actively update the base? Executing the following SQL will trigger InnoDB to update the cardinality (even if you don't realize it will update the cardinality). So try to choose a low business peak period analyze table tableName; If the number of samples is too small, the calculated cardinality will be extremely wrong. That will most likely cause the MySQL optimizer to choose the wrong index. This is where you can increase the value appropriately. But increasing it too much may cause ANALYZE TABLE to run slowly. Conversely, ANALYZE TABLE runs too slowly. You can adjust the value of the parameter innodb_stats_persistent_sample_pages appropriately. However, this may lead to inaccurate cardinality calculation.
refer to: https://dev.mysql.com/doc/refman/5.7/en/innodb-persistent-stats.html https://dev.mysql.com/doc/refman/5.7/en/innodb-analyze-table-complexity.html The above is a brief analysis of the details of MySQL cardinality statistics. For more information about MySQL cardinality statistics, please pay attention to other related articles on 123WORDPRESS.COM! You may also be interested in:
|
<<: Detailed explanation of web page loading progress bar (recommended)
>>: Solution to the problem of insufficient storage resource pool of Docker server
MySQL query by year, month, week, day group 1. Qu...
Table of contents 1. Animated Christmas Tree Made...
Just as the title! The commonly used font-family l...
Table of contents Preface 1. Uninstall MySQL 2. I...
MySQL supports hash and btree indexes. InnoDB and...
float:left/right/none; 1. Same level floating (1)...
<br />In the first section of this series, w...
MySQL escape Escape means the original semantics ...
1. addtime() Add the specified number of seconds ...
Table of contents Preface 1. Background 2. Simula...
1. Problem During development, when inserting dat...
I have used the vi editor for several years, but ...
1. Introduction MySQL locks can be divided into g...
Table of contents Create a global shared content ...
MySQL replace and replace into are both frequentl...