MySQL中cost似乎更倾向于索引？

---查看表结构；

# mysql test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 545313
Server version: 8.0.29 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show create table test12\G
*************************** 1. row ***************************
Table: test12
Create Table: CREATE TABLE `test12` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`k` int unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
`cc` int DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_kc` (`k`,`c`),
KEY `idx_pad` (`pad`(12))
) ENGINE=InnoDB AUTO_INCREMENT=20001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci MAX_ROWS=1000000
1 row in set (0.00 sec)

 ---查看k列的数据分布：

mysql> select count(1),k from test12 group by k;
+----------+---------+
| count(1) | k |
+----------+---------+
| 19988 | 0 |
| 1 | 912349 |
| 1 | 995098 |
| 1 | 997885 |
| 1 | 1000507 |
| 1 | 1002923 |
| 1 | 1003082 |
| 1 | 1004110 |
| 1 | 1004960 |
| 1 | 1005291 |
| 1 | 1057817 |
+----------+---------+
11 rows in set (0.02 sec)

 ---字段k有索引，查询k=0的数据，看看执行计划；

mysql> explain select * from test12 where k=0;
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| 1 | SIMPLE | test12 | NULL | ref | idx_kc | idx_kc | 4 | const | 9870 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

 

mysql> show warnings;
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select `test`.`test12`.`id` AS `id`,`test`.`test12`.`k` AS `k`,`test`.`test12`.`c` AS `c`,`test`.`test12`.`pad` AS `pad`,`test`.`test12`.`cc` AS `cc` from `test`.`test12` where (`test`.`test12`.`k` = 0) |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

 ---看看cost呢？

mysql> explain format=json select * from test12 where k=0;

| {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "2043.18"
},
"table": {
"table_name": "test12",
"access_type": "ref",
"possible_keys": [
"idx_kc"
],
"key": "idx_kc",
"used_key_parts": [
"k"
],
"key_length": "4",
"ref": [
"const"
],
"rows_examined_per_scan": 9870,
"rows_produced_per_join": 9870,
"filtered": "100.00",
"cost_info": {
"read_cost": "1056.18",
"eval_cost": "987.00",
"prefix_cost": "2043.18",
"data_read_per_join": "6M"
},
"used_columns": [
"id",
"k",
"c",
"pad",
"cc"
]
}
}
} |

---忽略索引看看

mysql> explain format=json select * from test12 ignore index(idx_kc)  where k=0;

| {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "2326.06"
},
"table": {
"table_name": "test12",
"access_type": "ALL",
"rows_examined_per_scan": 19740,
"rows_produced_per_join": 1974,
"filtered": "10.00",
"cost_info": {
"read_cost": "2128.66",
"eval_cost": "197.40",
"prefix_cost": "2326.06",
"data_read_per_join": "1M"
},
"used_columns": [
"id",
"k",
"c",
"pad",
"cc"
],
"attached_condition": "(`test`.`test12`.`k` = 0)"
}
}
} |

---。。。更新histogram

mysql> analyze table test12 update histogram on k;
+-------------+-----------+----------+----------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------+-----------+----------+----------------------------------------------+
| test.test12 | histogram | status | Histogram statistics created for column 'k'. |
+-------------+-----------+----------+----------------------------------------------+
1 row in set (1.65 sec)

mysql> explain select * from test12 where k=0;
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| 1 | SIMPLE | test12 | NULL | ref | idx_kc | idx_kc | 4 | const | 9870 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

 --！！！