Cardinality Chinese translation is: base number.
It means in the database that the total number does not repeat the total number in a column of a table in a database.
, for example, table below: T

The data of

 CREATE TABLE `t` (
  `a` int(11) NOT NULL,
  `b` varchar(800) DEFAULT NULL,
  `c` int(11) NOT NULL,
  PRIMARY KEY (`a`),
  KEY `idx_c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| a     | int(11)      | NO   | PRI | NULL    |       |
| b     | varchar(800) | YES  |     | NULL    |       |
| c     | int(11)      | NO   | MUL | NULL    |       |
+-------+--------------+------+-----+---------+-------+

There are:

+----+--------+-----+
| a  | b      | c   |
+----+--------+-----+
|  3 | yanjd3 |  -3 |
|  4 | yanjd4 |  -4 |
|  5 | yanjd5 |  -5 |
|  6 | yanjd6 |  -6 |
|  7 | yanjd6 |  -7 |
|  8 | yanjd6 |  -8 |
|  9 | yanjd6 |  -9 |
| 10 | yanjd6 | -10 |
| 11 | yanjd6 | -11 |
| 12 | yanjd6 | -12 |
| 13 | yanjd6 | -13 |
| 14 | yanjd6 | -14 |
| 15 | yanjd6 | -15 |
| 16 | yanjd6 | -16 |
| 17 | yanjd6 | -17 |
| 18 | yanjd6 | -18 |
| 20 | yanjd6 | -20 |
| 21 | yanjd2 | -21 |
| 22 | yanjd3 | -22 |
| 23 | yanjd4 | -23 |
| 24 | yanjd5 | -24 |
| 25 | yanjd6 | -25 |
| 26 | yanjd6 | -26 |
| 27 | yanjd6 | -27 |
+----+--------+-----+

We can see:

The base of
column A is: 24

The base of
column B is: 5

The base of the
column C is: 24

Obviously it is easy to see, and we can’t look at the number of a certain column every time to look at the number.
In mysql, you can use the following statement to determine the base of a column:
Taking the previous table as an example, the statistics of the base A:

mysql> select count(distinct a) from t;
+-------------------+
| count(distinct a) |
+-------------------+
|                24 |
+-------------------+
1 row in set (0.00 sec)

Statistical Base B:

mysql> select count(distinct b) from t;
+-------------------+
| count(distinct b) |
+-------------------+
|                 5 |
+-------------------+

Statistical bases of column C:

mysql> select count(distinct c) from t;
+-------------------+
| count(distinct c) |
+-------------------+
|                24 |
+-------------------+
1 row in set (0.00 sec)

The use of the base only needs to be remembered: For the index column, the larger the base, the better the query effect, the smaller the base, the worse the query effect, the ideal index column satisfaction: the base/actual line = 1;

For the index column, we can view its base through the following sentence:

mysql> show indexes from t \G;

From here we can see that the database will automatically statistics on the index column and database.

In the production environment, the update operation of the index may be very frequent. If each index changes every time, it will cause a great burden on the database.
This is unacceptable, so mysql’s strategy is:
1) When the data of the index column 1/16 in the table changes, its base is statistics.
2) Stat_modify_counter> 2000000000 (if the data in a line in the table is updated frequently, the data in the table does not actually increase, and the data that changes is. Effective, so mysql is set to a timer to indicate the number of changes, and statistics are performed when the number of changes is greater than 2000000000), and its base will be counted.

Note: If a table is very large, the actual statistics may be very long, so mysql uses sampling to perform statistics.

InnoDB storage engine only samples 8 nodes to statistics, and the specific steps are as follows:
First of all, get the number of nodes in B+tree cable, remember as A;
Then, take the 8 nodes in the B+tree index randomly, count the number of different records per page, record it as P1, P2, P3 … P8;
Finally calculate the base of this index column = (P1+P2+P3+P4 …+P8)*A/8

Note: The way to get 8 nodes above is random! Intersection This means that the base information of statistics may be different at a time, which is a normal phenomenon.

still take the above table T as an example
First view the base of its index column A:

and then insert the data:

INSERT INTO T (a,b,c) values(28,'yanjd2',-21);
INSERT INTO T (a,b,c) values(29,'yanjd3',-22);
INSERT INTO T (a,b,c) values(30,'yanjd4',-23);
INSERT INTO T (a,b,c) values(31,'yanjd5',-24);
INSERT INTO T (a,b,c) values(32,'yanjd6',-25);
INSERT INTO T (a,b,c) values(33,'yanjd6',-26);
INSERT INTO T (a,b,c) values(34,'yanjd6',-27);

Then view its index base:

At this time, it was found that the base statistics information has not changed? Intersection This may be why the statistical trigger conditions have not achieved.

Solution: We can execute the following sentence to make mysql actively calculate statistics:

 analyze table t;// Actively let MySQL calculate statistics

At this time, it can be found that the base information of index A has changed.