mysql trunk_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