MySQL 优化器

(system@127.0.0.1:3306) [trunk]> show variables like '%performance_sch%';
+----------------------------------------------------------+-------+
| Variable_name | Value |
+----------------------------------------------------------+-------+
| performance_schema | ON |

########################################
实验操作脚本：

CREATE TABLE `t1` (
`c1` int(11) NOT NULL DEFAULT '0',
`c2` varchar(128) DEFAULT NULL,
`c3` varchar(64) DEFAULT NULL,
`c4` int(11) DEFAULT NULL,
PRIMARY KEY (`c1`),
KEY `ind_c2` (`c2`),
KEY `ind_c4` (`c4`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


insert into t1 values(1,'a','A',10);
insert into t1 values(2,'b','B',20);
insert into t1 values(3,'b','BB',20);
insert into t1 values(4,'b','BBB',30);
insert into t1 values(5,'b','BBB',40);
insert into t1 values(6,'c','C',50);
insert into t1 values(7,'d','D',60);

 

mysql> select * from t1;
+----+------+------+------+
| c1 | c2 | c3 | c4 |
+----+------+------+------+
| 1 | a | A | 10 |
| 2 | b | B | 20 |
| 3 | b | BB | 20 |
| 4 | b | BBB | 30 |
| 5 | b | BBB | 40 |
| 6 | c | C | 50 |
| 7 | d | D | 60 |
+----+------+------+------+

set optimizer_trace="enabled=on";
select * from information_schema.optimizer_trace\G
SET optimizer_trace="enabled=off";

 

#################################################

1、执行以下SQL为什么不走索引ind_c2？

explain select * from t1 where c4=20 and c2='b';
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ref | ind_c2,ind_c4 | ind_c4 | 5 | const | 2 | 57.14 | Using where |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
--c4=40的过滤性比c2好很多
--filtered=57.14
---
"analyzing_roworder_intersect": {
"intersecting_indexes": [
{
"index": "ind_c4",
"index_scan_cost": 1.0011,
"cumulated_index_scan_cost": 1.0011,
"disk_sweep_cost": 1.75,
"cumulated_total_cost": 2.7511,
"usable": true,
"matching_rows_now": 2,
"isect_covering_with_this_index": false,
"chosen": true
},
{
"index": "ind_c2",
"index_scan_cost": 1.1429,
"cumulated_index_scan_cost": 2.144,
"disk_sweep_cost": 1,
"cumulated_total_cost": 3.144,
"usable": true,
"matching_rows_now": 1.3333,
"isect_covering_with_this_index": false,
"chosen": false,
"cause": "does_not_reduce_cost"
}

(system@127.0.0.1:3306) [test]> explain select * from t1 force index(ind_c2) where c4=20 and c2='b';
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ref | ind_c2 | ind_c2 | 387 | const | 4 | 20.00 | Using where |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
--key_len,rowd,filtered

 

2、where条件中字段c2和c4换个位置，索引还是不走ind_c2？为什么？

 

explain select * from t1 where c2='b' and c4=20;
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ref | ind_c2,ind_c4 | ind_c4 | 5 | const | 2 | 66.67 | Using where |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
--走index跟字段的顺序位置无关
--filtered=66.67
"analyzing_roworder_intersect": {
"intersecting_indexes": [
{
"index": "ind_c4",
"index_scan_cost": 1.0011,
"cumulated_index_scan_cost": 1.0011,
"disk_sweep_cost": 1.75,
"cumulated_total_cost": 2.7511,
"usable": true,
"matching_rows_now": 2,
"isect_covering_with_this_index": false,
"chosen": true
},
{
"index": "ind_c2",
"index_scan_cost": 1.1429,
"cumulated_index_scan_cost": 2.144,
"disk_sweep_cost": 1,
"cumulated_total_cost": 3.144,
"usable": true,
"matching_rows_now": 1.3333,
"isect_covering_with_this_index": false,
"chosen": false,
"cause": "does_not_reduce_cost"
}

3、如下语句，换个条件c2=\'c\'，为什么可以走索引ind_c2？

explain select * from t1 where c2='c' and c4=20;
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ref | ind_c2,ind_c4 | ind_c2 | 387 | const | 1 | 33.33 | Using where |
--filtered=33.33,c2=c的选择性比c4=20要好
"analyzing_roworder_intersect": {
"intersecting_indexes": [
{
"index": "ind_c2",
"index_scan_cost": 1,
"cumulated_index_scan_cost": 1,
"disk_sweep_cost": 1,
"cumulated_total_cost": 2,
"usable": true,
"matching_rows_now": 1,
"isect_covering_with_this_index": false,
"chosen": true
},
{
"index": "ind_c4",
"index_scan_cost": 1.0011,
"cumulated_index_scan_cost": 2.0011,
"disk_sweep_cost": 0,
"cumulated_total_cost": 2.0011,
"usable": true,
"matching_rows_now": 0.3333,
"isect_covering_with_this_index": false,
"chosen": false,
"cause": "does_not_reduce_cost"
}

 

4、创建复合索引

ALTER TABLE t1 ADD KEY ind_c2_c4(`c2`,`c4`);
1）为什么不走复合索引ind_c2_c4？
explain select * from t1 where c2='b' and c4=20;
+----+-------------+-------+------------+------+-------------------------+--------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------------+--------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ref | ind_c2,ind_c4,ind_c2_c4 | ind_c4 | 5 | const | 2 | 66.67 | Using where |
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "ind_c2",
"ranges": [
"b <= c2 <= b"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 4,
"cost": 5.81,
"chosen": false,
"cause": "cost"
},
{
"index": "ind_c4",
"ranges": [
"20 <= c4 <= 20"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 2,
"cost": 3.41,
"chosen": true
},
{
"index": "ind_c2_c4",
"ranges": [
"b <= c2 <= b AND 20 <= c4 <= 20"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 2,
"cost": 3.41,
"chosen": false,
"cause": "cost"
}
],

"analyzing_roworder_intersect": {
"intersecting_indexes": [
{
"index": "ind_c2_c4",
"index_scan_cost": 1.0476,
"cumulated_index_scan_cost": 1.0476,
"disk_sweep_cost": 1.75,
"cumulated_total_cost": 2.7976,
"usable": true,
"matching_rows_now": 2,
"isect_covering_with_this_index": false,
"chosen": true
},
{
"index": "ind_c4",
"cumulated_total_cost": 2.7976,
"usable": false,
"cause": "does_not_reduce_cost_of_intersect"
},
{
"index": "ind_c2",
"cumulated_total_cost": 2.7976,
"usable": false,
"cause": "does_not_reduce_cost_of_intersect"
}
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "ind_c4",
"rows": 2,
"ranges": [
"20 <= c4 <= 20"
]
},
"rows_for_plan": 2,
"cost_for_plan": 3.41,
"chosen": true

2）为什么又可以走复合索引ind_c2_c4？？
explain select c2,c4 from t1 where c2='b' and c4=20;
+----+-------------+-------+------------+------+-------------------------+-----------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------------+-----------+---------+-------------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ref | ind_c2,ind_c4,ind_c2_c4 | ind_c2_c4 | 392 | const,const | 2 | 100.00 | Using index |
+----+-------------+-------+------------+------+-------------------------+-----------+---------+-------------+------+----------+-------------+
"analyzing_roworder_intersect": {
"intersecting_indexes": [
{
"index": "ind_c2_c4",
"index_scan_cost": 1.0476,
"cumulated_index_scan_cost": 1.0476,
"disk_sweep_cost": 0,
"cumulated_total_cost": 1.0476,
"usable": true,
"matching_rows_now": 2,
"isect_covering_with_this_index": true,
"chosen": true
}
],
"clustered_pk": {
"clustered_pk_added_to_intersect": false,
"cause": "no_clustered_pk_index"
},
"chosen": false,
"cause": "too_

------
(system@127.0.0.1:3306) [test]> show variables like '%sample%';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| innodb_stats_persistent_sample_pages | 20 |--持久化
| innodb_stats_sample_pages | 8 |--32(xx)
| innodb_stats_transient_sample_pages | 8 |--32
(system@127.0.0.1:3306) [test]> show variables like '%persi%';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| innodb_stats_persistent | ON |
| innodb_stats_persistent_sample_pages | 20 |
(system@127.0.0.1:3306) [test]> show variables like 'innodb_stats_on_metadata';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_stats_on_metadata | OFF |
SHOW TABLE STATUS、 SHOW INDEX
ANALYZE TABLE