MySQLl数据量不一样，导致走不同的索引-创新互联

1、测试环境：MySQL 5.7.17

上思ssl适用于网站、小程序/APP、API接口等需要进行数据传输应用场景，ssl证书未来市场广阔！成为成都创新互联公司的ssl证书销售渠道，可以享受市场价格4-6折优惠！如果有意向欢迎电话联系或者加微信：028-86922220（备注：SSL证书合作）期待与您的合作！

2、测试表结构

mysql> show create table a; +-------+--------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table                                                                                                                         | +-------+--------------------------------------------------------------------------------------------------------------------------------------+ | a     | CREATE TABLE `a` (   `id` int(11) NOT NULL,   `name` char(20) DEFAULT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=gbk | +-------+--------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)mysql> show create table b; +-------+------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table                                                                                                                       | +-------+------------------------------------------------------------------------------------------------------------------------------------+ | b     | CREATE TABLE `b` (   `id` int(11) NOT NULL,   `tx` char(20) DEFAULT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=gbk | +-------+------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

3、两张表的数据量

mysql> select count(*) from a; +----------+ | count(*) | +----------+ |        7 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from b; +----------+ | count(*) | +----------+ |       10 | +----------+ 1 row in set (0.00 sec)

4、查看执行计划

mysql> explain select name from a,b where a.id=b.id; +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+ | id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows | filtered | Extra       | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+ |  1 | SIMPLE      | a     | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL      |    7 |   100.00 | NULL        | |  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | apex.a.id |    1 |   100.00 | Using index | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec)mysql> explain select name from a,b where b.id=a.id; +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+ | id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows | filtered | Extra       | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+ |  1 | SIMPLE      | a     | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL      |    7 |   100.00 | NULL        | |  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | apex.a.id |    1 |   100.00 | Using index | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec)mysql> explain select name from b,a where b.id=a.id; +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+ | id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows | filtered | Extra       | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+ |  1 | SIMPLE      | a     | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL      |    7 |   100.00 | NULL        | |  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | apex.a.id |    1 |   100.00 | Using index | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec)

5、向a表插入3数据，使两表数据量一样，查看执行计划，发现第三条语句的执行计划发生了变化

mysql> insert into a values(8,'test'); Query OK, 1 row affected (0.00 sec) mysql> insert into a values(9,'test'); Query OK, 1 row affected (0.00 sec) mysql> insert into a values(10,'test'); Query OK, 1 row affected (0.01 sec) mysql> select count(*) from a; +----------+ | count(*) | +----------+ |       10 | +----------+ 1 row in set (0.00 sec)mysql> explain select name from a,b where a.id=b.id; +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+ | id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows | filtered | Extra       | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+ |  1 | SIMPLE      | a     | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL      |   10 |   100.00 | NULL        | |  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | apex.a.id |    1 |   100.00 | Using index | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec)mysql> explain select name from a,b where b.id=a.id; +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+ | id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows | filtered | Extra       | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+ |  1 | SIMPLE      | a     | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL      |   10 |   100.00 | NULL        | |  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | apex.a.id |    1 |   100.00 | Using index | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec)mysql> explain select name from b,a where b.id=a.id; +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+ | id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows | filtered | Extra       | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+ |  1 | SIMPLE      | b     | NULL       | index  | PRIMARY       | PRIMARY | 4       | NULL      |   10 |   100.00 | Using index | |  1 | SIMPLE      | a     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | apex.b.id |    1 |   100.00 | NULL        | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec)

6、向a表插入1条数据，使a表数据量大于b表，查看执行计划，三条语句执行计划都发现了变化

mysql> insert into a values(11,'test'); Query OK, 1 row affected (0.01 sec) mysql> select count(*) from a; +----------+ | count(*) | +----------+ |       11 | +----------+ 1 row in set (0.00 sec)mysql> explain select name from a,b where a.id=b.id; +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+ | id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows | filtered | Extra       | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+ |  1 | SIMPLE      | b     | NULL       | index  | PRIMARY       | PRIMARY | 4       | NULL      |   10 |   100.00 | Using index | |  1 | SIMPLE      | a     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | apex.b.id |    1 |   100.00 | NULL        | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec)mysql> explain select name from a,b where b.id=a.id; +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+ | id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows | filtered | Extra       | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+ |  1 | SIMPLE      | b     | NULL       | index  | PRIMARY       | PRIMARY | 4       | NULL      |   10 |   100.00 | Using index | |  1 | SIMPLE      | a     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | apex.b.id |    1 |   100.00 | NULL        | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec)mysql> explain select name from b,a where b.id=a.id; +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+ | id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows | filtered | Extra       | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+ |  1 | SIMPLE      | b     | NULL       | index  | PRIMARY       | PRIMARY | 4       | NULL      |   10 |   100.00 | Using index | |  1 | SIMPLE      | a     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | apex.b.id |    1 |   100.00 | NULL        | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+ 2 rows in set, 1 warning (0.01 sec)

另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。

名称栏目：MySQLl数据量不一样，导致走不同的索引-创新互联
文章源于：http://6mz.cn/article/ccpdpd.html