MySQL面试题——索引

1.什么是索引

在关系型数据库中，索引是对表中一列或多列的值进行排序的一种存储结构，他是表中一列或者多列的值，而且其中包含了对应表中记录的引用指针，更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的
举个例子：首先，先假设有一张表，表有10万个记录，其中有一条记录我们已知a=‘1’，如果想要拿到对应记录的话，需要sql的语句是 SELECT * FROM XXX WHERE A=‘1’
一般情况下，对于查询语句，在没有建立索引的时候，MySQL会进行全表扫描，而且不扫描完10万个记录不会停止，如果在某一列上建立索引，那么MySQL相当于只扫描这一列，而且这一列已经排序好，找到对应结果或结果集就可以直接返回

2.索引的优缺点

索引的优点：

• 可以大大加快数据的检索速度，这也是创建索引的最主要的原因
• 通过使用索引，可以在查询的过程中，使用优化隐藏起，提高系统的性能

索引的缺点：

• 时间方面：创建索引和维护索引要耗费时间，具体的，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率（即对于大量的写操作，MySQL会花费大量的时间动态维护索引文件）
• 空间方面：索引需要占用物理空间

3.索引的使用场景

3.1WHERE

当我们使用WHERE对表中的记录进行筛选时，如果没有对表中的列建立索引，则MySQL会对全表进行扫描，即使扫到了符合条件的，MySQL也是会继续扫描下去

3.2ORDER BY

当我们使用ORDER BY将查询结果按照某个字段排序时，如果该字段没有建立索引，那么执行计划会将查询出的所有数据使用外部排序（将数据从硬盘分批读取到内存使用内部排序，最后合并排序结果）
但是如果我们对该字段建立索引，那么由于索引本身是有序的，因此直接按照索引的顺序和映射关系逐条取出数据即可，而不用像上述那样取出所有的数据进行排序再返回某个范围内的数据

3.3索引覆盖

如果要查询的字段都建立过索引，那么引擎会直接在索引表中查询而不会访问原始数据（否则只要有一个字段没有建立索引就会做全表扫描），减少了IO时间这叫做索引覆盖，因此我们需要尽可能的在select后只写必要的查询字段，以增加覆盖的几率

4.索引类型

主键索引（PRIMARY KEY）：数据列不允许重复，不允许NULL，一个表只能有一个主键

• 可以通过 ==ALTER TABLE table_name ADD PRIMARY KEY(column)==创建主键索引

唯一索引：数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引

• 可以通过==ALTER TABLE table_name ADD UNIQUE(column);==创建唯一索引
• 可以通过==ALTER TABLE table_name ADD UNIQUE(column1,column2);==创建唯一组合索引

普通索引：基本的索引类型，没有唯一性的限制，允许为NULL值

• 可以通过==ALTER TABLE table_name ADD INDEX index_name(column):==创建普通索引
• 可以通过==ALTER TABLE table_name ADD INDEX index_name(column1,column2,column3);==创建组合索引

全文索引：是目前搜索引擎使用的一种关键技术

• 可以通过==ALTER TABLE table_name ADD FULLTEXT(column);==创建全文索引

补充：主键索引和唯一索引的区别

• 一张表只能有一个主键，但可以创建多个唯一索引
• 主键创建后一定包含一个唯一索引，唯一索引并不一定是主键
• 主键的值不允许为NULL，但唯一索引的值可以为NULL
• 主键可以作为外键，唯一索引不行