初学者的MySQL索引

MySQL索引

数据库索引

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

索引的作用

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

普通索引

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

1) create table infos (id int(4),name varchar(10));
   create index id_index on infos (di);
   
2) alter table infos add index name_index (name);

3) create table num (id int,index id_index(id));

唯一索引

• 与 ”普通索引“ 基本相同
• 与普通索引的区别是索引列的所有值只能出现一次，即必须唯一
• 唯一索引允许为空，但空只能有一次

create unique index unique_name on info(name);

主键索引

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

create table info (id int(3) not null primary key auto_increment,......)

组合索引（单列索引与多列索引）

• 可以是单列上创建的索引，也可以是在多列上创建的索引
• 最左原则，从左往右依次执行

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

全文索引

• 索引类型为FULLTEXT

1) 在已存在的表上创建全文索引

create fulltext index content_tag_fulltext

​	on fulltext_test(content,tag);

2) alter 创建全文索引

alter table info add fulltext index addr_index(addresss);

删除索引

drop index addr_index on info

查看索引

show index from info\G

创建索引的原则依据

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