mysql 数据库索引

索引的概念

• 是一个排序的列表，存储着索引值和这个值所对应的物理地址
• 无需对整个表进行扫描，通过物理地址就可以找到所需要数据
• 是表中一列或者若干列值排序的方法
• 需要额外的磁盘空间
• 伴随表存在

索引的作用

• 数据库利用各种快速定位技术，能够大大加快查询速率
• 当表很大或查询涉及到多个表时，可以成千上万倍地提高查询速度
• 可以降低数据库的IO成本，并且还可以降低数据库的排序成本
• 通过创建唯一性索引保证数据表数据的唯一性
• 可以加快表与表之间的连接
• 在使用分组和排序时，可大大减少分组和排序时间

索引的分类

普通索引

• 最基本的索引类型，没有唯一性之类的限制

create index <索引的名字> on tablename(列的列表)；'//直接创建'
alter table table_name add index index_name (column(length)); '//修改表时创建'
create table `table`(
  `id` int(11) not null auto_increment,
  `title` char(255) character set utf8 collate utf8 collate utf8_general_ci not null,
  `content` text character set utf9 collate utf8_general_ci null,
  `time` int(10) null default null,
  primary key (`id`),
  index index_name (title(length)) '//创建表时创建'
)

创建唯一索引

• 与普通索引的区别是索引列的所有值只能出现一次，即必须唯一

create unique index <索引的名字> on tablename(列的列表);'//直接创建'
alter table table_name add unique index_name (column(lenght));'//修改表时创建'
create table `table`(
  `id` int(11) not null auto_increment,
  `title` char(255) character set utf8 collate utf8 collate utf8_general_ci not null,
  `content` text character set utf8 collate utf8_general_ci null,
  `time` int(10) null default null,
  primary key (`id`),
  index index_name (title(length)) '//创建表时创建'
)

创建主键索引

• 是一种特殊的唯一索引，指定为 “primary key”
• 一个表只能有一个主键，不允许有空值

create table tablename([...],primary key(列的列表))

创建全文索引

create table `table_name`(
  `id` int(11) not null auto_increment,
  `title` char(255) not null,
  primary key(`id`)
);

组合索引

• 可以是单列上创建的索引，也可以是在多列上创建的索引

create table user(
  nname varchar(9),
  age int(3),
  sex tinyin(1),
  index user(name,age,sex)
);

创建索引的原则

• 表的主键、外键必须有索引
• 记录数超过300行的表应该有索引
• 经常与其他表进行连接的表，在连接字段上应该建立索引
• 唯一性太差的字段不适合建立索引
• 更新频繁的字段不适合创建索引
• 经常出现在where子句中的字段，特别是大表的字段，应该建立索引
• 索引应该建立在选择性高的字段上
• 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引

查看索引

show index from table_name;
show keys from table_name;

删除索引

drop index index_name on table_name;
alter table table_name drop index index_name;