MySQL面试：主键索引、唯一索引、普通索引、全文索引、组合索引的区别

索引简介

索引：排好序的快速查找数据结构！索引会影响where后面的查找，和order by 后面的排序

为什么要用索引

• 加快查询速度

索引的缺点

• 空间方面：每次创建索引，会自动生成一个索引文件，比较占用内存
• 时间方面：当增加，删除数据时，索引文件也需要更新

为什么加了索引能够提高效率

• 索引底层实现是B+树。B+树类似折半查找，比如说如果有10亿条数据，一次就能砍到一半。
• 数据量越多，索引的作用越大。

索引的分类

mysql>show index from  `table_name`;  -- 查看索引是否成功

从存储结构上分（根据索引存储时保存的形式）

• B树索引（B-Tree树或者B+Tree树）
• Hash索引
• full-index全文索引
• R-Tree索引。这里描述的是索引存储时保存的形式

BTree索引（B-Tree或B+Tree索引）

Hash索引

full-index全文索引

R-Tree索引

从应用层次上分（根据索引使用过程中进行的分类）

• 主键索引：
  • 它是一种特殊的唯一索引，不允许有空值。
  • 通常不会删除主键，因为设计主键一定与业务逻辑无关。
• 普通索引：
  • 即一个索引值包含单个列。一个表可以有多个单例索引
  • 它是最基本的索引，没有任何限制
  • 普通索引允许被索引的数据列包含重复的值
• 唯一索引：
  • 与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。
• 联合索引：
  • 一个索引包含多个列，专门用于组合搜索，其效率大于索引合并
• 全文索引：
  • 全文索引基本上不可能用到。如果有这样的需求，请用第三方框架

普通索引

• 最基本的索引，没有任何限制

mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )  -- 在索引上添加mul
mysql> ALTER TABLE `table_name` DROP INDEX `column`;
mysql> create index uname on table_name(column);   -- 在索引上添加mul

• 普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。
• 因此，应该只为那些最经常出现在查询条件（WHEREcolumn=）或排序条件（ORDERBYcolumn）中的数据列创建索引。
• 只要有可能，就应该选择一个数据最整齐、最紧凑的数据列（如一个整数类型的数据列）来创建索引。

唯一索引

• 与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。

mysql>ALTER TABLE `table_name` ADD UNIQUE ( `column` )  -- 在现有的表上增加
mysql> ALTER TABLE `table_name` DROP INDEX `column`;    
mysql> CREATE UNIQUE INDEX index_name ON table_name (column_list)

• 普通索引允许被索引的数据列包含重复的值。比如说，因为人有可能同名，所以同一个姓名在同一个“员工个人资料”数据表里可能出现两次或更多次。

如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。这么做的好处：

• 一是简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；
• 二是MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，MySQL将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。

唯一索引用的不多

主键索引

• 它是一种特殊的唯一索引，不允许有空值。

 mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

• 在前面已经反复多次强调过：必须为主键字段创建一个索引，这个索引就是所谓的“主索引”。主索引与唯一索引的唯一区别是：前者在定义时使用的关键字是PRIMARY而不是UNIQUE。

全文索引

• 仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时好空间。
• Innodb不要用全文索引

现在基本上全部时Inoodb引擎了，所以全文索引基本上不可能用到。如果有这样的需求，请用第三方框架

组合索引

为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。

根据数据的物理顺序与键值的逻辑（索引）顺序关系

根据数据的物理顺序与键值的逻辑（索引）顺序关系：聚集索引（clustered index），非聚集索引（non-clustered index）。

聚簇索引

• 聚集索引：不是一种单独的索引类型，而是一种数据存储方式。具体细节取决于不同的实现。InnoBD的聚簇索引其实就是在同一个结构中保存了B-Tree索引(技术上来说是B+Tree)和数据行。

非聚簇索引

• 非聚集索引：不是聚集索引，就是非聚集索引