SLTechnology News&Howtos › Database ›

The amount of MySQLl data is different, resulting in different indexes.

Shulou Source: shulou.com Published: 2022-06-01 17:05:43 10月03日 Update

1. Test environment: MySQL 5.7.17

2. Test table structure

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. The amount of data in the two tables

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

4. View the execution plan

Mysql > explain select name from a dint b where a.id=b.id +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+-- -- +-+ | 1 | SIMPLE | a | NULL | ALL | PRIMARY | 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 apene b where b.id=a.id +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+-- -- +-+ | 1 | SIMPLE | a | NULL | ALL | PRIMARY | 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 bpene a where b.id=a.id +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+-- -- +-+ | 1 | SIMPLE | a | NULL | ALL | PRIMARY | 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. Insert 3 data into table a to make the two tables have the same amount of data. Look at the execution plan and find that the execution plan of the third statement has changed.

Mysql > insert into a values; Query OK, 1 row affected (0.00 sec) mysql > insert into a values; Query OK, 1 row affected (0.00 sec) mysql > insert into a values; 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 Agile b where a.id=b.id +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+-- -+-+ | 1 | SIMPLE | a | NULL | ALL | PRIMARY | 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 apene b where b.id=a.id +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+-- -+-+ | 1 | SIMPLE | a | NULL | ALL | PRIMARY | 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 bpene a where b.id=a.id +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | 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. insert a piece of data into table a so that the amount of data in table an is larger than that in table b. Check the execution plan and find changes in the execution plan of the three statements.

Mysql > insert into a values; Query OK, 1 row affected (0.01 sec) mysql > select count (*) from sec mysql + | count (*) | +-+ | 11 | +-+ 1 row in set (0.00 sec) mysql > explain select name from Abinb where a.id=b.id +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | 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 apene b where b.id=a.id +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | 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 bpene a where b.id=a.id +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | 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)