mysql 优化面试总结 - 索引优化

索引官方定义：索引是帮助mysql高效获取数据的数据结构。

索引的目的在于提高查询效率

可以简单理解为：排好序的快速查找的数据结构

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这种数据结构以某种方式（引用）指向数据。
这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

例如将id加索引，在mysql数据库里开辟一块存储空间来存放索引数据，查询的时候如果根据id去查询，就要走这个索引库，在索引库找到之后，就能定位这条数据，因为索引库的每一项和数据库的物理地址是绑定的，你能找到这条索引，就能找到这条数据所对应的物理地址，就可以直接获取这条数据。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。

索引结构

我们平常所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。
其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。
当然，除了B+树这种类型的索引外，还有哈希索引等。

BTree索引、hash索引、full-text全文索引、R-Tree索引，我们只关注BTree索引

B+Tree（B+）索引检索原理（mysql默认的索引结构）

索引的优缺点

• 优点

1. 提高数据检索的效率，降低数据库的io成本
2. 通过索引列对数据进行排序，降低数据排序的成本，降低了cpu的消耗

• 缺点

1. 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
   索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
2. 虽然索引大大提高了查询速度，却会降低更新表的速度，如对表进行增删改。
   因为更新表时，mysql不仅要保存数据，还要更新添加了索引列的字段，也会调整因为更新所带来的键值变化后的索引信息

什么时候需要/不需要创建索引？

需要创建索引

• 频繁作为查询条件的字段应该创建索引
• 查询中排序、统计、分组的字段
• 查询中与其他表关联的字段，外键关系建立的字段

不需要创建索引

• 表记录太少
• 更新非常频繁的字段不适合创建索引
  除了更新数据本身外还需要更新BTree树，数据量大的话是很耗费资源的。
• Where，分组，排序里用不到的字段不创建索引
• 唯一性太差(字段好多都是同一个值)（在查询的时候会索引失效）的字段不适合单独(可以使用联合索引)创建索引，即使频繁作为查询条件；

索引能够极大的提高数据检索效率，也能够改善排序分组操作的性能，但是我们不能忽略的一个问题就是索引是完全独立于基础数据之外的一部分数据，更新数据会带来的IO量和调整索引所致的计算量的资源消耗。

避免索引失效

索引失效导致的结果就是全表扫描

1. 遵守最左匹配原则
   如果是复合索引（索引了多列），就要遵守最左匹配原则，
   指的是查询从索引的最左列开始并且不跳过中间索引中的列
   如果跳过了，最左边的列依然会走索引，右边的列就不会走索引了
2. 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
3. 在使用!=或者<>无法使用索引会导致全表扫描，可以使用union all
4. is null 和 is not null 也无法使用索引，可以设置default值，避免null值
5. like以通配符开头 例如%abc ，索引失效导致全表扫描
   如果在实际业务中必须把%放在前面，我们可以使用覆盖索引
   使用覆盖索引查询时，只查询覆盖索引中的一个字段或全部，都会用到索引
6. 少用or，会引起索引失效
   少用 in 和 not in，因为in 实际上等同于 or
7. 唯一性太差，会引起索引失效。（例如一个字段有好多内容是相同的）
8. 类型转换导致索引失效

只是我常常觉得很多话无从说起。就像在平时，见到一些陌生人，一些熟悉和欢喜的人，不知道与之说些什么。也很少对身边的人谈论自己。所有的时间和记忆，都可以交付给书写。不可能再说得更多。有些在书中说过多次，却似乎并未说出真正想表达的意思。有些从无提起过，它们在黑暗中更显得郑重端庄。有些事是不能轻易说起的。书写可以，但那也是不足够的。

素年锦时
安妮宝贝