**
Hi, Hello everyone ！ I'm daydreaming .
The topic I want to share with you today is ：“ What is the cardinal number ？”
It is recommended to read the original link
https://mp.weixin.qq.com/s/FgxwAFQbEjv5i-TxjvLK6Q
I welcome your attention , Just started writing , Ongoing update ～
Can add my wechat 17861405320, Welcome to join the big guys
One 、 What's the base number ？
Cardinality Refers to MySQL The number of different values for a column in a table .
If this class is the only index , The cardinal number = Row number .
If this column is sex, Enumeration types are only male and female , Then it's the cardinal number 2
Cardinality The higher the , The more valuable columns become indexes .MySQL The implementation plan will also be based on Cardinality Choose the index .
You can see the cardinality of the columns in the table in the following way .
For example, this classic example ：
There is a column for sex, The for sex The value stored in the column Either male or female , Its largest base is 2.
Then there is no need for sex Index . because , In order to improve you based on sex Query speed of ,MySQL A new index will be created for the new index you selected B+Tree. But you sex There are only two values , about MySQL Come on , Even if it creates B+Tree Indexes , When the query is actually executed , At most one binary query , The rest of the operation can only be traversal , So for sex It doesn't make much sense to create an index .
Two 、InnoDB When to update the base ？
Parameters ：innodb_stats_auto_recalc control MySQL Whether to actively recalculate the persistent information . The default is 1 Express true,0 Express false.
By default, when rows in a table change more than 10% when , Recalculate the cardinality information .
3、 ... and 、 The base number is estimated
The cardinality is not updated in real time ！ And it's an estimated value from sampling ！
As for the formula of cardinal number , It may not matter .
It's important that you know , It's an estimate by randomly sampling data pages .
And the number of pages randomly sampled can be determined by the parameter innodb_stats_persistent_sample_pages Set up , The default value is 20.
That means The base value is not accurate , Even if the results of each calculation are still quite large .
Four 、 Persistence base
You can use the parameter innodb_stats_persistent Controls whether the cardinality is persisted , The default is off.
Of course, you can set it for a separate table STATS_PERSISTENT=1 So it's innodb_stats_persistent Will be automatically enabled .
The advantage of opening it is ： restart MySQL It's not going to be repeated , Speed up the restart .
Four 、 How to actively update the base ？
Execute the following SQL Will trigger InnoDB Update base （ Even if you don't realize it updates the cardinality ）.
So try to choose a business low peak period
analyze table tableName;
If the number of samples is too small , The base number of the calculation is too wrong . That's likely to lead to MySQL The optimizer for has selected the wrong index . This is that you can increase the value appropriately . But add Too much may lead to ANALYZE TABLE slow .
conversely , ANALYZE TABLE It's too slow . You can adjust the parameters moderately innodb_stats_persistent_sample_pages Value . But this may lead to inaccurate calculation of cardinality .
If there is no way to balance the two . Consider reducing the number of index columns in the table or limiting the number of partitions to reduce ANALYZE TABLE complexity . The number of columns in the primary key of a table is also important , Because primary key columns are attached to every non unique index .
Reference resources ：
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