MySQL索引原理及SQL优化

MySQL官方对索引的定义为：索引(Index)是帮助MySQL高效获取数据的数据结构。索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

索引用于快速查找具有特定列值的行。如果没有索引，MySQL必须从第一行开始，然后读取整个表以查找相关行。表越大，成本越高。如果表中有相关列的索引，MySQL可以快速确定要在数据文件中间寻找的位置，而无需查看所有数据。这比按顺序读取每一行要快得多。

MySQL常用的是B+ Tree索引，下面详细介绍。

如上图，是一颗b+树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

上图中，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有

当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。

当b+树的数据项是复合的数据结构，常见的就是组合索引，比如我们给某个表添加个组合索引，包括姓名、年龄和性别三列(name,age,sex)，b+数是按照从左到右的顺序来建立搜索树的，比如查询（where name=‘马云’ and age=18 and sex=1），b+树会优先比较name来确定下一步的检索方向，如果name相同再依次比较age和sex，最后得到检索的数据；但如果我们查询(where age=18 and sex= 1),此时索引是不生效的，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当查询(where name='张三' and sex=2)的时候，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是2的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

MySQL使用索引进行这些操作：

WHERE快速 查找与子句匹配的行。

如果在多个索引之间有选择，MySQL通常使用找到最小行数的索引。

如果表具有多列索引，即组合索引，则优化程序可以使用索引的任何最左前缀来查找行。例如，如果你有一个三列索引上(col1, col2, col3)，你有索引的搜索功能(col1)， (col1, col2)以及(col1, col2, col3)。

在执行连接时从其他表中检索行。如果声明它们的类型和大小相同，MySQL可以更有效地使用列上的索引。在这种情况下， VARCHAR与 CHAR被认为是相同的，如果它们被声明为相同的大小。例如， VARCHAR(10)和 CHAR(10)大小相同，但 VARCHAR(10)与 CHAR(15)不是。

对于非二进制字符串列之间的比较，两列应使用相同的字符集。例如，将utf8列与 latin1列进行比较会排除使用索引。

不相似列的比较（例如，将字符串列与时间或数字列进行比较）可能会在没有转换的情况下无法直接比较值时阻止使用索引。对于给定的值，如1 在数值列，它可能比较等于在字符串列，例如任何数量的值 '1'，' 1'， '00001'，或'01.e1'。这排除了对字符串列的任何索引的使用。

查找特定索引列的值Min()或 Max()[`值key_col。这是由预处理器优化的，该预处理器检查您是否正在使用 索引之前出现的所有关键部分。在这种情况下，MySQL对每个或 表达式执行单个键查找，并用常量替换它。

对指定索引列进行排序或者分组，ORDER BY或者 GROUP BY

在某些情况下，可以优化查询在不查询整行数据的情况下检索值。（为查询提供所有必要结果的索引称为 [覆盖索引]）如果查询仅使用表中包含某些索引的列，则可以从索引树中检索所选值以获得更快的速度：比如

对于小型表或报表查询处理大多数或所有行的大型表的查询，索引不太重要。当查询需要访问大多数行时，顺序读取比通过索引更快。顺序读取可以最大限度地减少磁盘搜索，即使查询不需要所有行也是如此。

主键优化

表的主键表示您在最重要的查询中使用的列或列集。它具有关联的索引，以实现快速查询性能。查询性能受益于NOT NULL优化，因为它不能包含任何NULL值。使用InnoDB存储引擎，表数据在物理上进行组织，以根据主键或列进行超快速查找和排序。

如果您的表很大且很重要，但没有明显的列或列集用作主键，则可以创建一个单独的列，其中包含自动增量值以用作主键。使用外键连接表时，这些唯一ID可用作指向其他表中相应行的指针。

外键优化

如果一个表有很多列，并且您查询了许多不同的列组合，那么将频率较低的数据拆分为每个都有几列的单独表可能会很有效，并通过外键将它们与主表关联起来。这样每个小表都可以有一个主键来快速查找其数据，您可以使用连接操作查询所需的列集。根据数据的分布方式，查询可能会执行较少的I / O并占用较少的高速缓存。（为了最大限度地提高性能，查询尝试从磁盘中读取尽可能少的数据块）。

列索引

最常见的索引类型涉及单个列，在数据结构中存储该列的值的副本，允许快速查找具有相应列值的行。B树数据结构可以让索引快速查找特定值，一组值，或值的范围，例如where条件中=， >，≤， BETWEEN，IN等。

每个存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引，总索引长度至少为256个字节。

索引前缀

使用 字符串列的索引规范中的语法，可以创建仅使用列的前几个字符的索引 。以这种方式仅索引列值的前缀可以使索引文件更小。索引 或 列时， 必须为索引指定前缀长度。

如果搜索项超过索引前缀长度，则索引用于排除不匹配的行，并检查剩余的行以查找可能的匹配项。

FULLTEXT索引

FULLTEXT索引用于全文搜索。只有InnoDB和 MyISAM存储引擎支持 FULLTEXT索引和仅适用于CHAR，VARCHAR和TEXT类型的列。索引始终发生在整个列上，并且不支持列前缀索引。

空间索引（Spatial Index）

您可以在空间数据类型上创建索引。 MyISAM和InnoDB 支持空间类型的R树索引。其他存储引擎使用B树来索引空间类型（除了 ARCHIVE）。

多列索引

MySQL可以创建复合索引（即多列索引）。索引最多可包含16列。对于某些数据类型，您可以索引列的前缀。

MySQL可以对测试索引中所有列的查询使用多列索引，或者只测试第一列，前两列，前三列等的查询。如果在索引定义中以正确的顺序指定列，则单个复合索引可以加速同一表上的多种查询。

假设一个表具有以下规范：

在last_name和first_name列创建了一个组合索引，它既可以查询last_name和first_name`组合的值，也可以仅查询last_name，因为该列是索引的最左前缀。因此，下面这些查询是可以用到该索引的：

但是，该索引 不能用于以下查询中的查找：

假设您写了如何SQL语句：

如果col1和col2存在组合索引，那么可以直接获取相应的行。如果col1和col2每列都存在单列索引，那么MySQL会优化合并索引，或者尝试通过确定哪个索引会排除更多的行来查找限制性最强的索引。

如果表具有多列索引，则优化程序可以使用索引的最左前缀来查找行。例如，如果你有一个三列索引上(col1, col2, col3)，你有索引的搜索功能 (col1)，(col1, col2)以及 (col1, col2, col3)。

如果SQL语句不适用索引的最左前缀，则MySQL无法使用索引执行查找。例如以下查询语句：

如果存在索引(col1, col2, col3)，则只有前两个查询使用索引。第三和第四个查询确实包括索引的列，但不使用索引来进行查找，因为(col2)和 (col2, col3)不是的最左边的前缀 (col1, col2, col3)。

尽管为查询中使用的每个可能列创建索引很有诱惑力，但不必要的索引会浪费空间并浪费时间让MySQL确定要使用哪些索引。索引还会增加插入，更新和删除的成本，因为必须更新每个索引。您必须找到适当的平衡，以使用最佳索引集实现快速查询。

我们创建了索引，但是我们如何确定mysql使用了索引？ 答案是 使用explain语句。

下面会详细介绍Explain，以及如何优化SQL。

举个例子，最基础的主键查询

执行结果如下：

再举个关联查询的例子

执行结果如下：

id是sql执行顺序的标识，按id从大到小的顺序执行，在id相同时，执行顺序是由上至下

select 查询的类型，主要是用于区别普通查询，联合查询，嵌套的复杂查询

查询的数据库的表的名称，如果没有给表指定别名，那么table值为表的名称；否则table值为你指定的别名

表示MySQL在表中找到所需行的方式，这是一个非常重要的参数，常见的有：all , index , range , ref , eq_ref , const , system , null 八个级别。

常用的类型有： all、index、range、 ref、eq_ref、const、system、null（从左到右，性能从差到好）