mysql btree脚本_MySQL优化之BTree索引使用规则

MySQL优化之BTree索引使用规则

从一道题开始分析：

假设某个表有一个联合索引(c1,c2,c3,c4)以下那个只能使用该联合索引的c1,c2,c3部分

explain select * from t where c1='a1' and c2='a2' and c4='a4' and c3='a3';

explain select * from t where c1='a1' and c2='a2' and c4='a4' order by c3;

explain select * from t where c1='a1' and c2='a2' and c4='a4' order by c1;

explain select * from t where c1='a1' and c4='a4'group by c3,c2;

explain select * from t where c1='a1' and c4='a4'group by c2,c3;

explain select * from t where c1='a1' and c4='a4'order by c2,c3;

explain select * from t where c1='a1' and c5='a5' order by c2,c3;

创建表后插入数据：

insert into t

values

('a1','a2','a3','a4','a5'),

('b1','b2','b3','b4','b5');

添加索引：

alter table t add index c1234(c1,c2,c3,c4);

mysql> explain select * from t where c1='a1' and c2='a2' and c4='a4' and c3='a3’;(MySQL执行时会改变其中的位置，c3会在c4的前边)

+----+-------------+-------+------+---------------+-------+---------+-------------------------+------+-----------------------+

| id | select_type | table | type | possible_keys | key   | key_len | ref                     | rows | Extra                 |

+----+-------------+-------+------+---------------+-------+---------+-------------------------+------+-----------------------+

| 1  | SIMPLE      | t     | ref  | c1234         | c1234 | 168     | const,const,const,const | 1    | Using index condition |

+----+-------------+-------+------+---------------+-------+---------+-------------------------+------+-----------------------+

1 行于数据集 (0.05 秒)

mysql> explain select * from t where c2='a1' and c3='a4’; (根据索引的左前缀规则，c2,c3部分没有索引)

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

| 1  | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL | 4    | Using where |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

1 行于数据集 (0.08 秒)

mysql> explain select * from t where c1='a1' and c2='a2' and c4='a4' order by c3;(根据索引的左前缀规则，order by部分没有索引)

+----+-------------+-------+------+---------------+-------+---------+-------------+------+------------------------------------+

| id | select_type | table | type | possible_keys | key   | key_len | ref         | rows | Extra                              |

+----+-------------+-------+------+---------------+-------+---------+-------------+------+------------------------------------+

| 1  | SIMPLE      | t     | ref  | c1234         | c1234 | 84      | const,const | 1    | Using index condition; Using where |

+----+-------------+-------+------+---------------+-------+---------+-------------+------+------------------------------------+

1 行于数据集 (0.08 秒)

mysql> explain select * from t where c1='a1' and c2='a2' and c4='a4' order by c1;(根据索引的左前缀规则，order by使用了索引，可以存在where与order by同时使用索引的情况)

+----+-------------+-------+------+---------------+-------+---------+-------------+------+-----------------------+

| id | select_type | table | type | possible_keys | key   | key_len | ref         | rows | Extra                 |

+----+-------------+-------+------+---------------+-------+---------+-------------+------+-----------------------+

| 1  | SIMPLE      | t     | ref  | c1234         | c1234 | 84      | const,const | 1    | Using index condition |

+----+-------------+-------+------+---------------+-------+---------+-------------+------+-----------------------+

1 行于数据集 (0.09 秒)

(以下四组对比，说明在group by 和order by 中，使用索引的顺序，可以避免使用临时表和filesort)

mysql> explain select * from t where c1='a1' and c4=‘a4' group by c3,c2;

+----+-------------+-------+------+---------------+-------+---------+-------+------+---------------------------------------------------------------------+

| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows | Extra                                                               |

+----+-------------+-------+------+---------------+-------+---------+-------+------+---------------------------------------------------------------------+

| 1  | SIMPLE      | t     | ref  | c1234         | c1234 | 42      | const | 1    | Using index condition; Using where; Using temporary; Using filesort |

+----+-------------+-------+------+---------------+-------+---------+-------+------+---------------------------------------------------------------------+

1 行于数据集 (0.09 秒)

mysql> explain select * from t where c1='a1' and c4='a4'group by c2,c3;

+----+-------------+-------+------+---------------+-------+---------+-------+------+------------------------------------+

| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows | Extra                              |

+----+-------------+-------+------+---------------+-------+---------+-------+------+------------------------------------+

| 1  | SIMPLE      | t     | ref  | c1234         | c1234 | 42      | const | 1    | Using index condition; Using where |

+----+-------------+-------+------+---------------+-------+---------+-------+------+------------------------------------+

1 行于数据集 (0.12 秒)

mysql> explain select * from t where c1='a1' and c4='a4'order by c2,c3;

+----+-------------+-------+------+---------------+-------+---------+-------+------+------------------------------------+

| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows | Extra                              |

+----+-------------+-------+------+---------------+-------+---------+-------+------+------------------------------------+

| 1  | SIMPLE      | t     | ref  | c1234         | c1234 | 42      | const | 1    | Using index condition; Using where |

+----+-------------+-------+------+---------------+-------+---------+-------+------+------------------------------------+

1 行于数据集 (0.13 秒)

mysql> explain select * from t where c1='a1' and c5='a5' order by c3,c2;(使用的c1索引)

+----+-------------+-------+------+---------------+-------+---------+-------+------+----------------------------------------------------+

| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows | Extra                                              |

+----+-------------+-------+------+---------------+-------+---------+-------+------+----------------------------------------------------+

| 1  | SIMPLE      | t     | ref  | c1234         | c1234 | 42      | const | 1    | Using index condition; Using where; Using filesort |

+----+-------------+-------+------+---------------+-------+---------+-------+------+----------------------------------------------------+

1 行于数据集 (0.15 秒)