针对组合索引进行的一些测试

看起来引起了那么大的讨论，我给出一个实际的测试效果。

首先我关闭了我的查询缓存

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_free_blocks      | 0     |
| Qcache_free_memory      | 0     |
| Qcache_hits             | 0     |
| Qcache_inserts          | 0     |
| Qcache_lowmem_prunes    | 0     |
| Qcache_not_cached       | 70    |
| Qcache_queries_in_cache | 0     |
| Qcache_total_blocks     | 0     |
+-------------------------+-------+

我创建了一个比较大的基数

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|  7911096 |
+----------+
1 row in set (4.14 sec)

a 和 b 字段的基数

mysql> select count(distinct(a)), count(distinct(b)) from test;
+--------------------+--------------------+
| count(distinct(a)) | count(distinct(b)) |
+--------------------+--------------------+
|            1955748 |            1955748 |
+--------------------+--------------------+
1 row in set (8.78 sec)

建了一个 BTree 的普通索引

mysql> SHOW INDEX FROM test;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test  |          0 | PRIMARY  |            1 | id          | A         |     8945223 |     NULL | NULL   |      | BTREE      |         |               |
| test  |          1 | b + a    |            1 | b           | A         |     1973409 |     NULL | NULL   | YES  | BTREE      |         |               |
| test  |          1 | b + a    |            2 | a           | A         |     1973409 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.01 sec)

为了确保变量一致，采用连一次销毁一次的做法：

mysql -vv -uroot -h127.0.0.1 -P3306 -p123456 -Dtest -e"select count(*) from test where a = 123467 and b > 123954";
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
select count(*) from test where a = 123467 and b > 123954
--------------

+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (4.16 sec)

mysql -vv -uroot -h127.0.0.1 -P3306 -p123456 -Dtest -e"select count(*) from test where b > 123954 and a = 123467";
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
select count(*) from test where b > 123954 and a = 123467
--------------

+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (1.43 sec)

Bye

我们来看看语法优化器有没有帮我们工作

mysql> explain select count(*) from test where b > 123954 and a = 123467;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+---------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows    | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+---------+----------+--------------------------+
|  1 | SIMPLE      | test  | NULL       | range | b + a         | b + a | 5       | NULL | 4472632 |    10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+---------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                  |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select count(0) AS `count(*)` from `test`.`test` where ((`test`.`test`.`a` = 123467) and (`test`.`test`.`b` > 123954)) |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> explain select count(*) from test where a = 123467 and b > 123954;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+---------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows    | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+---------+----------+--------------------------+
|  1 | SIMPLE      | test  | NULL       | range | b + a         | b + a | 5       | NULL | 4472632 |    10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+---------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings ;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                  |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select count(0) AS `count(*)` from `test`.`test` where ((`test`.`test`.`a` = 123467) and (`test`.`test`.`b` > 123954)) |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

综上。可能还有些变量没考虑进去，欢迎提出来。