sql实验

数据表xiami_1,结构如下:

CREATE TABLE xiami_1(
    id int(11)   not null auto_increment,
    singer varchar(50) not null,
    title varchar(50) not null,
    song_url varchar(50) not null,
    hot int(11) default 0,
    area var(50) not null,
    Primary Key id
) Engine=InnDB default charset=utf8;

解释一下表数据:

id: 序号

singer: 歌手名

title:歌曲名称

song_url: 歌曲链接

hot:歌曲听过的次数

area:歌手所属地区

数据量:390万

 

查询语句	操作次数	总时间 (查询次数*每一次查询时间)
insert into xiami_1(singer, title, area, song_url, hot) values('周杰伦', '十里香', '台湾', '/song/zjl/', 10000);	100	4.289s
select * from xiami_1 where id=3932618;	100	0.089s
select * from xiami_1 where singer="周杰伦";	10	26.199s
select * from xiami_1 where singer like "周%";	10	26.358s
select * from xiami_1 where singer like "%杰%";	10	30.245s
select * from xiami_1 order by id;	1	189.183s
select * from xiami_1 order by id desc;	1	201.280s
select * from xiami_1 order by singer;	1	304.599s

 

给singer字段添加索引

ALTER TABLE xiami_1 ADD INDEX(singer);

 

查询语句	操作次数	总时间(查询次数*每一次查询时间)
insert into xiami_1(singer, title, area, song_url, hot) values('周杰伦', '十里香', '台湾', '/song/zjl/', 10000);	100	4.084s
select * from xiami_1 where singer = "周杰伦";	10	0.106s
select * from xiami_1 where singer like  "周%";	10	0.068s
select * from xiami_1 where singer like "%杰%";	10	31.686s
select * from xiami_1 order by singer;	1	308.771s