013_索引

最新推荐文章于 2024-03-02 09:31:16 发布

原创最新推荐文章于 2024-03-02 09:31:16 发布 · 133 阅读

0 ·

CC 4.0 BY-SA版权

MySql学习专栏收录该内容

35 篇文章

订阅专栏

1. 索引介绍

1.1. 您可以在表中创建索引, 以便更加快速高效地查询数据。

1.2. 打个比方, 如果合理的设计且使用索引的MySQL是一辆兰博基尼的话, 那么没有设计和使用索引的MySQL就是一个人力三轮车。

1.3. 用户无法看到索引, 它们只能被用来加速搜索/查询。

1.4. 索引分单列索引和组合索引。单列索引, 即一个索引只包含单个列。组合索引, 即一个索引包含多个列。

1.5. 创建索引时, 你需要确保该索引是应用在SQL查询语句的条件(一般作为where子句的条件)。

1.6. 实际上, 索引也是一张表, 该表保存了主键与索引字段, 并指向实体表的记录。

1.7. 上面都在说使用索引的好处, 但过多的使用索引将会造成滥用。因此索引也会有它的缺点: 虽然索引大大提高了查询速度, 同时却会降低更新表的速度, 如对表进行insert、update和delete。因为更新表时, MySQL不仅要保存数据, 还要保存一下索引文件。

1.8. 建立索引会占用磁盘空间的索引文件。

2. 普通索引

2.1. 这是最基本的索引, 它没有任何限制。

2.2. 创建普通索引

create index `indexname` on `tablename` (`columnname`);

2.3. 修改表结构添加普通索引

alter table `tablename` add index `indexname` (`columnname`);

2.4. 创建表的时候直接指定

create table `tablename` (

`id` int(11) not null,

`username` varchar(16) not null,

index `indexname` (`username`)

);

3. 唯一索引

3.1. 唯一索引与普通索引类似, 不同的就是索引列的值必须唯一, 但允许有空值。如果是组合索引, 则列值的组合必须唯一。

3.2. 创建唯一索引

create unique `indexname` on `tablename` (`columnname`);

3.3. 修改表结构添加唯一索引

alter table `tablename` add unique index `indexname` (`columnname`);

3.4. 创建表的时候直接指定

create table `tablename` (

`id` int(11) not null,

`username` varchar(16) not null,

unique `indexname` (`username`)

);

4. 删除索引

4.1. drop index `indexname` on `tablename`;

4.2. alter table `tablename` drop index `indexname`;

5. 显示索引信息

5.1. 你可以使用show index命令来列出表中的相关的索引信息。可以通过添加\G来格式化输出信息。

show index from `tablename`; \G

6. 索引查询

6.1. 创建一张Persons表, 同时插入400多万条数据。

drop table if exists `persons`;
create table `persons` (
  `id` int(11) not null,
  `lastname` varchar(255) not null,
  `firstname` varchar(255) default null,
  `address` varchar(255) default null,
  `city` varchar(255) default null
) engine=innodb default charset=utf8;


insert into `persons` values ('1', 'san', 'zhang', 'china', 'luoyang');
insert into `persons` values ('2', 'san', 'zhang', 'china', 'luoyang');
insert into `persons` values ('3', 'san', 'zhang', 'china', 'luoyang');
insert into `persons` values ('4', 'san', 'zhang', 'china', 'luoyang');
insert into `persons` values ('5', 'san', 'zhang', 'china', 'luoyang');
insert into `persons` values ('6', 'san', 'zhang', 'china', 'luoyang');
insert into `persons` values ('7', 'san', 'zhang', 'china', 'luoyang');
insert into `persons` values ('8', 'san', 'zhang', 'china', 'luoyang');
insert into `persons` values ('9', 'san', 'zhang', 'china', 'luoyang');
......
insert into `persons` values ('3999990', 'san', 'zhang', 'china', 'luoyang');
insert into `persons` values ('3999991', 'san', 'zhang', 'china', 'luoyang');
insert into `persons` values ('3999992', 'san', 'zhang', 'china', 'luoyang');
insert into `persons` values ('3999993', 'san', 'zhang', 'china', 'luoyang');
insert into `persons` values ('3999994', 'san', 'zhang', 'china', 'luoyang');
insert into `persons` values ('3999995', 'san', 'zhang', 'china', 'luoyang');
insert into `persons` values ('3999996', 'san', 'zhang', 'china', 'luoyang');
insert into `persons` values ('3999997', 'san', 'zhang', 'china', 'luoyang');
insert into `persons` values ('3999998', 'san', 'zhang', 'china', 'luoyang');
insert into `persons` values ('3999999', 'san', 'zhang', 'china', 'luoyang');
insert into `persons` values ('4000000', 'san', 'zhang', 'china', 'luoyang');

6.2. 没有使用索引, 查询Persons表的第3000000条数据, 耗时3秒多。