今天老姜要带大家一起看看Mysql索引相关的面试题。

一. MySQL索引分类

我们先来看看MySQL中都有哪些索引。

  • 普通索引index：允许出现相同的索引内容create index name on 表名(列名)
  • 唯一索引unique：不可以出现相同的值，可以有NULL值create unique age 表名(列名)
  • 联合索引：实质上是将多个字段建到一个索引里，列值的组合必须唯一create index name on 表名(列名,列名)
  • 全文索引：可以针对值中的某个单词，但效率不高alter table 表名 engine = MyISAM;--全文索引需求在数据库MyISAM引擎 alter table 表名 add fulltext index 索引名(列名);

二. 索引的原理

索引的原理，其实就是通过不断地缩小想要获取数据的范围，来筛选出最终想要的结果，同时把随机的事件变成顺序的事件！也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

本质上来说，索引就是让我们快速检索数据而设置的一种把数据排好序的数据结构，他需要额外的空间保存并进行维护。索引底层的数据结构分别有二分查找法、Hash和B+树。

三. B+树

InnoDB引擎使用B+树作为索引结构。

特点:

  1. B+ 树的特点是能够保持数据稳定有序；
  2. 插入与修改拥有较稳定的时间复杂度；
  3. B+ 树中的元素自底向上插入；
  4. 叶子节点间通过指针链接，可以通过遍历叶子节点获取所有数据。

性质:

  1. IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块数据项的数量是m，则有h=㏒(m+1)N；
  2. m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的；
  3. 索引的最左匹配特性(即从左往右匹配)，比如(name,age,sex)的时候，b+树是按照从左到右的顺序来建立搜索树的。假如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name，来确定下一步的搜索方向。如果name相同，再依次比较age和sex，最后得到检索的数据。但当(20,F)这样没有name的数据进来的时候，b+树就不知道下一步该查哪个节点了。因为建立搜索树的时候，name是第一个比较因子，必须要先根据name来搜索，才能知道下一步去哪里查询。

四. 索引的使用

我们在使用时，还要知道到底该什么时候使用索引，有以下几种情况：

  1. 主键自动建立唯一索引；
  2. 经常作为查询条件，在WHERE或者ORDER BY 语句中出现的列，应该建立索引；
  3. 作为排序的列要建立索引；
  4. 外键关系建立索引；
  5. 高并发条件下倾向联合索引；
  6. 用于聚合函数的列可以建立索引，如max、count的字段就需要建立索引。

另外我们还要注意，某些操作不当可能会导致索引失效，比如：

  1. 使用!=或<>操作符时，会全表扫描而不使用索引(ps:如果是主键还是会走索引)；
  2. where子句中，对字段进行 null 判断会全表扫描；
  3. where 子句中，使用 or 来连接条件查询会全表扫描；
  4. like查询会全表扫描；
  5. in 和 not in 会全表扫描；
  6. where子句中，对字段进行函数操作，如where datediff(now(),bornDate)=0会导致索引失效。

现在你对索引是不是有了基本地了解了呢？