查询优化：MySQL索引及其使用技巧 | 数据分析学习历程全记录

本文包含以下内容：

1. 数据分析师为什么要了解索引
2. 什么是索引
3. 索引的优势和劣势
4. 索引的分类
5. 索引的设计原则
6. 复合索引

数据分析师为什么要了解索引：

数据分析师的工作是对数据进行处理和分析，其第一步便是从数据库中查询想要的数据，对于很多人来说，这一步看起来并不是特别重要。

但事实上一个好的数据分析师需要知道如何优化自己的查询效率，特别是对于巨量数据而言，一个经过优化的查询语句可以节省大量的资源和时间。

由此可见，虽然数据分析师并不会对索引本身进行直接操作，但是在查询时会直接使用到索引，因此明白索引的概念及其建立规则，可以指导我们写出效率更高的SQL查询语句。

什么是索引：

索引是帮助MySQL高效获取数据的数据结构，索引不存储在与之对应的表中，而是单独存储在另一张表中，索引表中存在主键列和索引列。

索引的优势劣势：

了解一些数据结构的会知道，一个好的数据结构可以帮助提高查询数据或者增删改数据的执行效率，一些表可能会经常被访问，而一些表可能经常需要删除数据或者插入数据，对于不同的表功能，需要设计不一样的数据结构。

对于数据分析师而言，我们更多的是访问数据，查询数据，因此我们所访问的表大多按此设计数据结构（MySQL中索引的数据结构默认为BTree）。

索引的优势之一，显而易见，是可以提高数据检索的效率，降低数据库的IO成本。

索引本身是经过排序的（具体可查找BTree数据结构相关文章），因此在查询中通过索引列对数据进行排序，就相当于绕过了排序阶段，提高了查询效率。

当然，一件事总有两面性，索引的劣势就在于它与其它普通表一样，需要占用存储空间，并且，因为BTree是为查询所设计的数据结构，对于更新，插入和删除数据的操作，其效率都不高。

索引的分类：

1. 单值索引：一个索引只包合单个列，一个表可以有多个单值索引，是最普通的索引类型。
2. 唯一索引：索引列的值必须唯一，允许有空值，通过唯一索引可以快速定位到需要查询的记录，因此查询效率很高。
3. 主键索引：唯一索引的一个特殊情况，即将主键列作为索引，因此不能有空值。
4. 复合索引：一个索引包含多个列，是大表中经常使用的索引类型。
5. 全文索引：用来查找文本中的关键字。这种索引将文本中的每个词出现的频率和位置按照特定词库的顺序进行整理建立索引。

索引的设计原则：

1. 对于经常需要进行查询，而较少更新、插入和删除数据的表建立索引。
2. 对数据量较大的表建立索引。对于数据量小的表而言，建立索引所提升的查询时间可忽略不计，却又占用一部分存储空间，因此意义不大。
3. 如果经常使用表中某一列作为过滤条件（即where子句中使用的列），那么该列可作为索引列。
4. 当过滤条件比较多，可以根据列的使用频率和列值的唯一性等进行选择作为索引。
5. 索引的数量需要恰当，过多的索引会导致，在增删改的操作中，需要分配更多资源更新索引。同时MySQL的优化器需要在大量的索引中进行选择以获得效率最高的查询方式，这一步也会消耗更多资源。
6. 尽量使用短索引。所谓短索引就是指索引的字段长度较短。因为索引也是一张表需要占用存储空间，对于相同效果的索引来说自然占用空间越小越好。
7. 利用复合索引的最左前缀原则（详情请见下章：复合索引）。

复合索引：

复合索引的结构举例：

该复合索引可以使用Index(Continents, Countries, Cities)来表示。

在创建该复合索引时，实际上创建了index(Continents)，index(Continents, Countries)和index(Continents, Countries, Cities)三个索引。

那么，创建一个包含三列的复合索引和创建三个单值索引有什么区别吗？

对于多个单列索引而言，需要建立多个BTree，也就是每个索引都需要占用一部分空间，造成资源浪费。

复合索引的劣势：最左前列规则。

所谓最左前列规则的意思是，以最左边的一列为起点的任何连续的索引才能匹配。

比如对于index(Continents, Countries, Cities)来说，where子句后出现Continents，出现Continents和Countries，出现Continents，Countries和Cities这三种情况都可以使用该索引，因为都是以最左前列，即Continents为开头，且顺次出现的。

但是如果仅出现Countries，仅出现Cities和仅出现Countries和Cities都无法使用该索引。

对于仅出现Continents和Cities这种情况，此时会使用到复合索引的最左前列Continents，但对于Cities来说则没有使用索引，因为Cities与Continents之间还隔有一个Countries。

因此在创建索引时需要仔细考虑索引列的顺序，才能提高后续查询的效率。

欢迎关注知乎账号：舟晓南