欢迎光临
Corasql

MySQL数据量不一样,导致走不同的索引

1、测试环境:MySQL 5.7.17

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)

分享到:更多 ()

评论 抢沙发

评论前必须登录!

DBA是一种生活方式,优化是一种生活态度

OracleMySQL