mysql之索引

1.索引简介

• 什么是索引

  索引是一个单独的存储在磁盘中的数据库结构，它们包含着对数据表里的所有记录的引用指针，使用索引可以快速的找出在某列或多列中有特定值的行

• 优点

  • 通过创建唯一索引来保证数据库表中的每一行数据的唯一性
  • 可以加快数据的检索速度
  • 可以保证表数据的完整性与准确性

• 缺点

  • 索引需要占用物理空间
  • 对表中的数据进行改动时，索引也需要跟着动态维护，降低了数据的维护速度

• 索引常见类型

  • index：普通索引
  • unique：唯一索引
  • primary key：主键索引
  • foreign key：外键索引
  • fulltext：全文索引
  • 组合索引

2.普通索引与唯一索引

• 什么是普通索引

  普通索引（index）顾名思义就是各类索引中最为普通的索引，主要任务就是提高查询速度。特点是允许出现相同的索引内容，允许空（null）值

• 什么是唯一索引

  唯一索引（unique）顾名思义就是不可以出现相同的索引内容，但是可以为空（null）值

• 语法

  -- 添加索引，假如没有指定索引名时，会以默认的字段名为索引名称
  alter table 表名 add index 索引名 (字段名);
  alter table student add index index_name (name);
  
  -- 查看索引
  show index from 表名;
  show index from student;
  
  -- 删除索引
  alter table 表名 drop index 索引名;
  alter table student drop index index_name;
  

3.主键索引

• 什么是主键索引

  • 把主键添加索引就是主键索引（PRIMARY KEY），它是一种特殊的唯一索引，不允许为空值，而唯一索引是允许为空值的
  • 主键是表的某一列，这一列的值是用来标志表中的每一行数据，注意：每一张表只能有一个主键

• 语法

  -- 添加主键索引
  alter table 表名 add primary key (字段名);
  alter table test add primary key (id);
  
  -- 删除主键索引
  alter table 表名 drop primary key;
  alter table test drop primary key;
  

4.全文索引

• 什么是全文索引

  全文索引（fulltext）是将存储在数据库中的文章或者句子等任意信息查找出来的索引，单位是词。全文索引也是目前搜索引擎使用的一种关键技术

• 语法

  -- 添加全文索引
  alter table 表名 add fulltext (字段名);
  alter table command add fulltext (instruction);
  
  -- 使用全文索引
  select * from 表名 where match(字段名) against('检索内容');
  select * from command where match(instruction) against('sort,list');
  
  -- 删除全文索引
  alter table 表名 drop index 索引名;
  alter table command drop index instruction;
  

• in boolean mode模式：指定全文检索模式为布尔全文检索（简单可以理解为是检索方式）

  select * from 表名 where match(字段名) against('检索内容' in boolean mode);
  select * from command where match(instruction) against('sort,list' in boolean mode);
  

• 注意点

  • 一般情况下创建全文索引的字段数据类型为char、varchar、text，其它字段类型不可以
  • 全文索引不针对非常频繁的词做索引，比如is、no、not、you、me、yes这些，我们称之为停止词
  • 对英文检索时忽略大小写

5.外键

• 什么是外键

  外键就是作用于两个表数据之间的链接的一列或多列，用来保证表与表之间的数据的完整性和准确性

• 语法

  -- 添加外键约束：主键跟外键的字段类型一定要相同
  alter table 表名 add foreign key (字段名) references 表名 (字段名);
  alter table employee add foreign key (deptnu) references dept (deptnu);
  
  -- 删除外键约束
  alter table 表名 drop index 索引名;
  alter table employee drop index deptnu;
  

• 注意点

  • 两个表，主键跟外键的字段类型一定要相同
  • 使用外键约束表的引擎一定得是InnoDB引擎，MyISAM不支持
  • 删除外键索引之前必须先删除外键约束才能删除索引

6.联合索引

• 什么是联合索引

  联合索引又称组合索引或者复合索引，是建立在两列或者多列以上的索引

• 语法

  -- 创建联合索引
  alter table 表名 add index (字段1, 字段2);
  alter table test add index (name, content);
  
  -- 删除联合索引
  alter table 表名 drop index 索引名;
  alter table test drop index name;
  

• 为什么使用联合索引而不使用多个单列索引

  联合索引的效率远远高于多个单列索引

• 联合索引的最左原则
  数据库会根据联合索引的最左的字段来构建B+树；如果where条件中没有最左字段则联合索引失效

7.总结

• 索引并非越多越好，过多的索引会增加数据的维护速度还有磁盘空间的浪费
• 当表的数据量很大的时候，可以考虑建立索引
• 表中经常查数据的字段，可以考虑建立索引
• 想要保证表中数据的唯一性，可以考虑建立唯一索引
• 想要保证两张表中的数据的完整性跟准确性，可以考虑建立外键约束
• 经常对多列数据进行查询时，可以考虑建立联合索引