数据分析面试【数据库】总结之-----数据库之索引

阅读之前看这里👉：博主是正在学习数据分析的一员，博客记录的是在学习过程中一些总结，也希望和大家一起进步，在记录之时，未免存在很多疏漏和不全，如有问题，还请私聊博主指正。
博客地址：天阑之蓝的博客，学习过程中不免有困难和迷茫，希望大家都能在这学习的过程中肯定自己，超越自己，最终创造自己。

1.索引是什么

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

2.常见的查询算法

• 顺序查找（linear search ）
• 二分查找（binary search）
• 二叉排序树查找
• 哈希散列法(哈希表)
• 分块查找

3.设计索引的原则

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑这些原则，以便提升索引的使用效率，更高效地使用索引。

• 搜索的索引列，不一定是所要选择的列。换句话说，最适合索引地列是出现再where字句中地列，或连接子句中指定地列，而不是出现在select关键字后地选择列表中的列。
• 使用唯一索引。考虑某列中值的分布。索引的列的基数越大，索引的效果越好。例如，存放出生日期的列具有不同值，很容易区分各行。 而用来记录性别的列，只含有"M"和"F",则对此列进行索引没有多大用处， 因为不管搜索哪个值， 都会得出大约一半的行。
• 使用短索引。如果对字符串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做。
• 利用最左前缀。 在创建一个n列的索引时，实际是创建了MySQL 可利用的n个索引。多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。
• 不要过度索引。 不要以为索引 “越多越好”，什么东西都用索引是错误的。 每个额外的索引都要 占用额外的磁盘空间，并降低写操作的性能。
• 对于InnoDB存储引擎的表，记录默认会按照一定的顺序保存，如果有明确定义的主键，则按照主键顺序保存。 如果没有主键，但是有唯一索引，那么就是按照唯一索引的顺序保存 如果既没有主键又没有唯一索引，那么表中会自动生成一个内部列，按照这个列的顺序保存。

4.BTREE索引与HASH索引的区别

MEMORY存储引擎的表可以选择使用BTREE索引或者HASH索引，两种不同类型的索引各有其不同的适用范围。HASH索引有一些重要的特征需要在使用的时候特别注意，如下所示。

• 只用于使用＝或＜＝＞操作符的等式比较。
• 优化器不能使用HASH 索引来加速ORDER BY操作。
• 只能使用整个关键宇来搜索一行。

而对于BTREE索引， 当使用＞、＜、＞＝、＜＝、BETWEEN、!=或者<>,或者LIKE’pattern’（其中’pattern’不以通配符开始）操作符时，都可以使用相关列上的索引。

索引是在MySQL的存储引擎层中实现的， 而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同， 也不是所有的存储引擎都支持所有的索引类型。MySQL目前提供了以下4种索引。

• B-Tree索引：最常见的索引类型， 大部分引擎都支持B树索引。
• HASH索引： 只有Memory引擎支持， 使用场景简单。
• R-Tree索引（空间索引）：空间索引是MyISAM的一个特殊索引类型， 主要用于地理空间数据类型， 通常使用较少， 不做特别介绍。
• Full-text( 全文索引）：全文索引也是MyISAM的一个特殊索引类型， 主要用于全文索引，InnoDB从MySQL5.6版本开始提供对全文索引的支持。

比较常用到的索引就是 B-Tree 索引和Hash 索引。

Hash 索引相对简单，只有 Memory/Heap引擎支持 Hash 索引。 Hash 索引适用于 Key-Value 查询，通过 Hash 索引要比通过 B-Tree 索引查询更迅速； Hash 索引不适用范围查询，例如＜、＞、＜＝、>=这类操作。如果使用 Memory/Heap 引擎并且 where 条件中不使用"=“进行索引列，那么不会用到索引。 Memory/Heap 引擎只有在”="的条件下才会使用索引。

B-Tree 索引是最常见的索引，构造类似二叉树，能根据键值提供一行或者一个行集的快速访问，通常只需要很少的读操作就可以找到正确的行。不过，需要注意 B-Tree 索引中的 B 不代表二叉树，而是代表平衡树。

6.MySQL中能够使用索引的典型场景

• 匹配全值 (Match the full value) , 对索引中所有列都指定具体值， 即是对索引中的所有列都有等值匹配的条件。
• 匹配值的范围查询 (Match a range of values) , 对索引的值能够进行范围查找。
• 匹配最左前缀(Match a leftmost prefix) , 仅仅使用索引中的最左边列进行查找
• 仅仅对索引进行查询 (Index only query), 当查询的列都在索引的字段中时， 查询的效率更高；
• 匹配列前缀 (Match a column prefix), 仅仅使用索引中的第一列，并且只包含索引第一列的开头一部分进行查找。
• 能够实现索引匹配部分精确而其他部分进行范围匹配。
• 如果列名是索引，那么使用 column_name is null 就会使用索引。

7.索引优化策略

• 最左前缀匹配原则
• 主键外键一定要建索引
• 对 where,on,group by,order by 中出现的列使用索引
• 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0
• 对较小的数据列使用索引,这样会使索引文件更小,同时内存中也可以装载更多的索引键
• 索引列不能参与计算，保持列“干净”
• 为较长的字符串使用前缀索引
• 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可
• 不要过多创建索引, 权衡索引个数与DML之间关系，DML也就是插入、删除数据操作。这里需要权衡一个问题，建立索引的目的是为了提高查询效率的，但建立的索引过多，会影响插入、删除数据的速度，因为我们修改的表数据，索引也需要进行调整重建

8.存在索引但不能使用索引的典型场景

• 以％开头的LIKE查询不能够利用B-Tree索引，执行计划中key的值为NULL表示没有使用索引
• 数据类型出现隐式转换的时候也不会使用索引，特别是当列类型是字符串，那么一定记得在where 条件中把字符常量值用引号引起来否则即便这个列上有索引， MySQL 也不会用到，因为 MySQL 默认把输入的常量值进行转换以后才进行检索。
• 复合索引的情况下， 假如查询条件不包含索引列最左边部分， 即不满足最左原则Leftmost, 是不会使用复合索引的
• 如果MySQL估计使用索引比全表扫描更慢， 则不使用索引。
• 用 or 分割开的条件， 如果 or 前的条件中的列有索引， 而后面的列中没有索引， 那么涉及的索引都不会被用到。

9.索引的优缺点

优点：
第一，通过创建唯一性的索引，可以保证数据库表中每一行数据的唯一性。
第二，可以大大加快数据的检索速度，这也使创建索引的最主要的原因。
第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
第四，在使用分组和排序子句进行数据检索时，同样可以显著的减少查询中查询中分组和排序的时间。
第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

缺点:
第一，创建索引和维护索引需要消耗时间，这种时间随着数量的增加而增加。
第二，索引需要占物理空间，除了数据表占据数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要额空间就会更大。
第三，当对表中的数据进行增加，删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

10 应该对如下的列建立索引

在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构。
在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度。
在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的。
在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
在经常使用在where子句中的列上面创建索引，加快条件的判断速度。

11 有些列不应该创建索引

在查询中很少使用或者作为参考的列不应该创建索引。
对于那些只有很少数据值的列也不应该增加索引（比如性别，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度）。
对于那些定义为text，image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
当修改性能远远大于检索性能时，不应该创建索引，因为修改性能和检索性能是矛盾的。

创建索引的方法：直接创建和间接创建（在表中定义主键约束或者唯一性约束时，同时也创建了索引）。
索引的特征：
唯一性索引和复合索引。唯一性索引保证在索引列中的全部数据是唯一的，不会包含冗余数据。复合索引就是一个索引创建在两个列或者多个列上。可以减少一在一个表中所创建的索引数量。

参考：
mysql索引优缺点以及创建的时机
《深入浅出MySQL++数据库开发、优化与管理维护》第2版

—————————————————————————————————————————————————
博主码字不易，大家关注点个赞转发再走呗 ，您的三连是激发我创作的源动力^ - ^