MySql（十六）--索引三表优化

最新推荐文章于 2023-04-10 00:36:03 发布

原创最新推荐文章于 2023-04-10 00:36:03 发布 · 428 阅读

1 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

37 篇文章

订阅专栏

本文介绍如何使用SQL语句创建一个名为'phone'的表，该表包含一个自增的主键和一个用于存储电话卡号的字段。此外，还演示了如何通过插入随机生成的电话卡号来填充表，并添加索引以提高查询效率。

在上一篇的基础上

添加表

create table if not exists `phone`(
 `phoneid` INT(10) unsigned NOT NULL  auto_increment,
 `card` INT(10) unsigned NOT NULL,
 primary key(`phoneid`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

insert into phone(card) values(floor(1+(RAND()*20)));
insert into phone(card) values(floor(1+(RAND()*20)));
insert into phone(card) values(floor(1+(RAND()*20)));
insert into phone(card) values(floor(1+(RAND()*20)));
insert into phone(card) values(floor(1+(RAND()*20)));
insert into phone(card) values(floor(1+(RAND()*20)));
insert into phone(card) values(floor(1+(RAND()*20)));
insert into phone(card) values(floor(1+(RAND()*20)));
insert into phone(card) values(floor(1+(RAND()*20)));
insert into phone(card) values(floor(1+(RAND()*20)));
insert into phone(card) values(floor(1+(RAND()*20)));
insert into phone(card) values(floor(1+(RAND()*20)));
insert into phone(card) values(floor(1+(RAND()*20)));
insert into phone(card) values(floor(1+(RAND()*20)));
insert into phone(card) values(floor(1+(RAND()*20)));
insert into phone(card) values(floor(1+(RAND()*20)));
insert into phone(card) values(floor(1+(RAND()*20)));
insert into phone(card) values(floor(1+(RAND()*20)));
insert into phone(card) values(floor(1+(RAND()*20)));
insert into phone(card) values(floor(1+(RAND()*20)));
insert into phone(card) values(floor(1+(RAND()*20)));

没建立索引

添加两个索引

alter table book add index y (card);
alter table phone add index z (card);