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

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

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

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)

ab 字段的基数

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)

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

《L05 电商实战》
从零开发一个电商项目,功能包括电商后台、商品 & SKU 管理、购物车、订单管理、支付宝支付、微信支付、订单退款流程、优惠券等
《L02 从零构建论坛系统》
以构建论坛项目 LaraBBS 为线索,展开对 Laravel 框架的全面学习。应用程序架构思路贴近 Laravel 框架的设计哲学。
讨论数量: 1

数据上不太完全一样,2441954条,distinct(a)、distinct(b)也不一样,我的数据库版本是5.7.23

localhost:~ ws$ mysql -vv -uws -p123456 -Dtest -e"select @@version"
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
select @@version
--------------

+-----------+
| @@version |
+-----------+
| 5.7.23    |
+-----------+
1 row in set (0.00 sec)

Bye
localhost:~ ws$ mysql -vv -uws -p123456 -Dtest -e"select sql_no_cache count(*) from test where a = 26727 and b > 15512"
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
select sql_no_cache count(*) from test where a = 26727 and b > 15512
--------------

+----------+
| count(*) |
+----------+
|       31 |
+----------+
1 row in set, 1 warning (0.32 sec)

Bye
localhost:~ ws$ mysql -vv -uws -p123456 -Dtest -e"select sql_no_cache count(*) from test where  b > 15512 and a = 26727"
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
select sql_no_cache count(*) from test where  b > 15512 and a = 26727
--------------

+----------+
| count(*) |
+----------+
|       31 |
+----------+
1 row in set, 1 warning (0.32 sec)

Bye
4年前 评论

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!