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

傷城~ 2022-03-20 14:56 316阅读 0赞

### 文章目录 ###

*  索引简介
 *   *  为什么要用索引
     *  为什么加了索引能够提高效率
     *  索引的缺点
     *  什么字段适合加索引
     *  索引的分类
     *  MySQL索引的创建原则
 *  普通索引
 *  唯一索引
 *  主键索引
 *  全文索引
 *  组合索引

# 索引简介 #

## 为什么要用索引 ##

*  加快查询速度

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

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

## 索引的缺点 ##

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

## 什么字段适合加索引 ##

*  对查询频率高的字段创建索引
 *  注意那些选择率不高的建立索引  
    最重要的一句话:  
    select count(distinct(column\_name))/count(\*) from table\_name; 超过了0.5就适合做索引，越接近1越适合  
    等于1的表示这个列的数据没有重复的，选择率十分高，是最好的选择

## 索引的分类 ##

*  唯一索引，普通索引，全文索引，主键索引，联合索引等等

## MySQL索引的创建原则 ##

*  对查询频率高的字段创建索引
 *  选择那些选择性高的字段作为索引
 *  对排序、分组、联合查询频率高的字段创建索引
 *  索引的数目不宜太多
 *  若在实际中，需要将多个列设置索引时，可以采用多列索引
 *  尽量使用数据量少的索引
 *  尽量使用前缀来索引
 *  删除不再使用或者很少使用的索引

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

# 普通索引 #

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

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效率可建立组合索引，遵循”最左前缀“原则。  
一个实验：

mysql>create index ind_c3_c4 on su(c3, c4)
    查看下面语句的执行计划
    select * from su where c3 = xxx;
    select * from su where c4 = xxx;
    select * from su where c4 = xxx order by c3;
    select * from su where c3 = xxx order by c4;

auto\_increment属性的列必须是索引  
　　  
参考：https://www.cnblogs.com/interdrp/p/8031087.html