MySQL优化必备知识-索引篇

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

提高SELECT操作性能的最佳方法是在查询中测试的一个或多个列上创建索引。索引项的作用类似于指向表行的指针，允许查询快速确定哪些行与WHERE子句中的条件匹配，并检索这些行的其他列值。可以索引所有MySQL数据类型。

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

MySQL索引划分

MySQL中把索引分为聚集索引（Clustered Index）和二级索引（Secondary Indexes），我们通常说的索引都是二级索引。

聚集索引

每个InnoDB表都有一个称为聚集索引的特殊索引，用于存储行数据。通过聚集索引访问行非常快，因为索引搜索直接指向包含行数据的页面。

通常，聚集索引与主键同义。为了从查询、插入和其他数据库操作中获得最佳性能，了解InnoDB如何使用聚集索引来优化常见的查找和DML操作非常重要。

如果不为表定义主键，InnoDB将使用第一个唯一索引（所有键列定义为not NULL）作为聚集索引。
如果表没有主键或合适的唯一索引，InnoDB将在包含行ID值的合成列上生成一个名为GEN_CLUST_index的隐藏聚集索引。行按InnoDB分配的行ID排序。行ID是一个6字节的字段，随着插入新行而单调增加。因此，按行ID排序的行实际上是按插入顺序排列的。

二级索引

聚集索引以外的索引称为二级索引。在InnoDB中，二级索引中的每个记录都包含该行的主键列以及为二级索引指定的列。InnoDB使用此主键值搜索聚集索引中的行。如果主键长，则二级索引占用更多空间，因此主键短是有利的。

二级索引包括唯一索引(UNIQUE)、普通索引(Normal INDEX)、全文索引(FULLTEXT)和空间数据索引(Spatial)

索引构建

除了空间索引之外，InnoDB索引都是B树数据结构。空间索引使用R树，R树是用于索引多维数据的专用数据结构。索引记录存储在其B树或R树数据结构的叶页中。索引页的默认大小为16KB。页面大小由初始化MySQL实例时的innodb_page_size设置确定。

当新记录插入到InnoDB聚集索引中时，InnoDB会尝试保留页面的1/16空间，以便将来插入和更新索引记录。如果按顺序（升序或降序）插入索引记录，当大约占15/16页则表示索引页满。如果以随机顺序插入记录，当大约占1/2到15/16页则表示索引页满。

InnoDB批量创建或重建B树索引，而不是一次插入一条索引记录，这种索引创建方法也称为排序索引构建。

在引入排序索引构建之前，索引条目是使用插入API一次一条记录地插入到B树中的。这种方法包括打开B树光标以找到插入位置，然后使用乐观插入将条目插入B树页面。如果由于页面已满而导致插入失败，则将执行悲观插入，这包括打开B树光标，并根据需要拆分和合并B树节点以查找条目的空间。这种“自上而下”的索引构建方法的缺点是搜索插入位置的成本以及B树节点的不断拆分和合并。

排序索引构建使用“自下而上”的方法来构建索引。使用这种方法，在B树的所有级别上都会保留对最右侧叶页的引用。在必要的B树深度处的最右边的叶页被分配，并且条目根据它们的排序顺序被插入。一旦一个叶页已满，就会将一个节点指针附加到父页，并为下一次插入分配一个同级叶页。此过程一直持续到插入所有条目，这可能导致插入到根级别。分配同级页时，将释放对先前固定的叶页的引用，新分配的叶页将成为最右侧的叶页和新的默认插入位置。

排序索引构建有三个阶段：
在第一阶段，扫描聚集索引，生成索引条目并将其添加到排序缓冲区。当排序缓冲区已满时，将对条目进行排序并将其写入临时中间文件。这个过程也称为“run”。
在第二阶段，将一个或多个run写入临时中间文件，对文件中的所有条目执行合并排序。
在第三个也是最后一个阶段，已排序的条目被插入到B树中。

为了给未来的索引增长留出空间，可以使用innodb_fill_actor变量来保留一定比例的B树页面空间(设置为100将使聚集索引页中的1/16空间用于将来的索引增长)。例如，在排序索引构建过程中，将innodb_fill_actor设置为80将保留B树页面中20%的空间。此设置同时适用于B树叶子页和非叶子页。它不适用于用于TEXT或BLOB条目的外部页面。保留的空间量可能与配置的不完全一样，因为innodb_fill_actor值被解释为提示而不是硬限制。

如果innodb_fill_factor低于MERGE_THRESHOLD（合并阈值，默认为50%），InnoDB将尝试收缩索引树以释放该页。innodb_fill_factor设置同时应用于B树和R树索引。

全文索引也支持排序索引生成。

索引关联

索引与表压缩

对于表压缩，以前的索引创建方法将条目附加到压缩页和未压缩页。当修改日志（表示压缩页面上的可用空间）变满时，压缩页面将被重新压缩。如果压缩由于空间不足而失败，页面将被拆分。

对于排序索引构建，条目仅附加到未压缩的页面。当未压缩的页面变满时，它将被压缩。自适应填充用于确保在大多数情况下压缩成功，但如果压缩失败，则会拆分页面并再次尝试压缩。此过程一直持续到压缩成功。

排序索引生成和Redo日志

在排序索引生成过程中，将禁用Redo日志记录。相反，有一个检查点来确保索引构建能够承受意外退出或失败。检查点强制将所有脏页写入磁盘。在排序索引构建过程中，会定期向页面清理线程发出信号以清除脏页面，以确保可以快速处理检查点操作。通常，当清理页面的数量低于设置的阈值时，页面清理线程会清除脏页面。对于已排序的索引构建，脏页会被迅速刷新，以减少检查点开销，并使I/O和CPU活动并行化。

排序索引构建和优化器统计信息

排序后的索引构建可能会导致优化器统计信息与以前的索引创建方法生成的统计信息不同。但统计数据的差异预计不会影响工作负载性能，这是由于用于填充索引的算法不同。

MySQL如何用索引

MySQL使用索引方式：

快速查找与WHERE子句匹配的行。
如果要在多个索引之间进行选择，MySQL通常使用查找最少行数的索引（最有选择性的索引）。
如果表具有多列索引，则优化器可以使用索引最左边的任何前缀来查找行。例如，如果对（col1，col2，col3）具有三列索引，则对（col1），（col1，col2）和（col1，col2，col3）具有索引搜索功能。对于(col1,col3)，则部分有效(col1)。如果不是从最左边列开始则用不上索引，如(col2)，(col3)、(col2,col3)。
联接(join)检索（从其它表检索行），如果将连接的列声明为相同的类型和大小，MySQL可以更有效地使用它们。对于VARCHAR和CHAR，只要相同大小，则认为它们是相同的。例如，VARCHAR（10）和CHAR（10）的大小相同，但VARCHAR（10）和CHAR（15）的大小不同。
对于非二进制字符串列之间的比较，两列应使用相同的字符集。例如，将utf8列与latin1列进行比较会排除索引的使用。
比较不同的列（例如，将字符串列与时态或数字列进行比较）可能会阻止使用索引，如果不进行转换就无法直接比较值。对于给定的值（如数字列中的1），它可能与字符串列中的任意数量的值（如“1”、“1”、“00001”或“01.e1”）进行比较。这排除了对字符串列使用任何索引的可能性。
查找索引列的MIN()或MAX()值，由预处理器优化。
order by或group by按最左边可用索引完成（例如，按key_part1、key_part2排序）。如果所字段后面都有DESC，则按相反顺序读取。
在某些情况下，可以优化查询以检索值，而无需查询数据行。如果查询仅从表中使用包含在某些索引中的列，则可以从索引树中检索所选值以获得更快的速度（为查询提供所有必要结果的索引称为覆盖索引）。

对于小表(通常<300行数据)上的查询，或报表查询处理大部分或所有行的大表，索引不太重要。当查询需要访问大多数行时，按顺序读取要比处理索引快。即使查询不需要所有的行，顺序读取也可以最小化磁盘查找。

索引优化

主键优化：主键是查询中优先使用的列或列集。使用InnoDB存储引擎，可以对表数据进行物理组织，以便根据主键列进行超快速查找和排序。
外键优化：如果一个表有许多列，常查询许多不同的列组合，那么将使用频率较低的数据拆分为单独的表（每个表都有几列）通过外键同主表主键关联是一种有效的优化方法。这样，每个小表都可以有一个主键，用于快速查找其数据，并且可以使用联接操作查询所需的列集。根据数据的分布方式，查询可能会执行更少的I/O并占用更少的缓存内存（为了最大限度地提高性能，查询尝试从磁盘读取尽可能少的数据块；只有几列的表可以在每个数据块中容纳更多的行）。
单列索引：最常见的索引类型只包括一列，该列值的副本存储在索引数据结构中，从而可以快速查找具有相应列值的行。B树数据结构使索引能够快速找到一个特定值、一组值或一系列值，这些值对应于WHERE子句中的运算符，如=、>、≤、BETWEEN、IN等。为了减少索引占用空间，在字符串列的索引规范中使用col_name（N）语法，可以创建仅使用列的前N个字符的索引。以这种方式仅对列值的前缀进行索引会使索引文件变得更小。为BLOB或TEXT列编制索引时，必须为索引指定前缀长度。使用前缀需注意：如果搜索项超过索引前缀长度，则将作为不匹配的行排除。
复合索引：即在两个或以上列上建立的索引。一个索引最多可以由16列组成。如果按索引定义中顺序指定列查询，复合索引可以加快查询速度。复合索引查询必须包含最左列，否则可能导致索引失效。

索引失效

下表列出在SQL中可能导致索引失效的运算符；

运算符分类	运算符	会导致索引失效的运算符
否定操作符	not	not
逻辑运算符	and,or	or
算数运算符	*,/,+,-	表达式左边使用
比较运算符	=,>,>=,<,<=,<>或!=	<>或!=
匹配运算符	like	like非头部匹配,not like
null运算符	is null, is not null	is null, is not null
包含	in,exists	in,not in,exists,not exists
范围	between … and …	not between … and …
当成特殊运算符	内置函数或自定义函数	表达式左边使用

另外，由于MySQL优化器作用，即使同一个SQL，索引可能有效也可能失效，失效原因是优化器认为不用索引效率会更高。举例如下：
1、orders表有48252条数据。
2、在create_time列上建立索引
3、查看orders表中按create_time的数据分布情况如图：
在这里插入图片描述
4、索引无效图：

5、索引有效图：
在这里插入图片描述

分析索引使用情况

用EXPLAIN语句可分析索引使用情况。

EXPLAIN SELECT ...;
EXPLAIN INSERT ..;
EXPLAIN UPDATE ..;
EXPLAIN DELETE ..;

EXPLAIN 提供了有关MySQL如何执行语句的信息,显示来自优化器的有关语句执行计划的信息。也就是说，MySQL解释了它将如何处理该语句，包括关于表如何连接以及按何种顺序连接的信息。
EXPLAIN 输出信息：

列	含义
id	SELECT标识符
select_type	select类型
table	输出行的表
partitions	匹配的分区
type	联接类型
possible_keys	可能选择的索引
key	实际选择的索引
key_len	所选索引的长度
ref	与索引比较的列
rows	要检查的行的估计
filtered	按表条件筛选的行的百分比
Extra	附加信息