mysql8.0 之索引优化《一》之介绍和如何使用总览总结

本文链接：https://blog.csdn.net/weixin_42749765/article/details/88354563

介绍：

提高操作性能的最近方法在select 查询中创建索引。索引条目作用类似于表行指针，运行查询快速找到匹配的行，并检索其他行内人可以操作mysql 所有数据类型

尽管查询中索引很快但不必要的索引会浪费mysql使用的空间，索引还会增加插入更改删除的成本，因为更新表会更新每个索引，索引必须找到一个最佳的平衡配置。

mysql 如何使用索引
主键优化
空间索引优化
列索引
多列索引
验证索引使用情况
innoDB和MyISAM 索引统计信息收集
Btree和好像索引比较
索引扩展的使用
生成列索引的优化程序使用
隐形指数
降序索引

如何使用索引：

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

大多数MySQL索引（PRIMARY KEY， UNIQUE，INDEX和 FULLTEXT）存储在 Btree。例外：空间数据类型的索引使用R树; MEMORY 表也支持hash index; InnoDB使用反向列表作为FULLTEXT索引。

如果你有一个三列索引上 (col1, col2, col3)，你有索引的搜索功能(col1)， (col1, col2)以及(col1, col2, col3)。

主键优化

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

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

外键优化

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

列索引

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

索引前缀

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

CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

前缀包括命名规范和字段长度等

多列索引

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

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name)
);

该name指数是在一个索引 last_name和first_name 列。该索引可用于查询中的查找，这些查询指定已知范围内的值last_name和first_name值组合的值。它也可以用于仅指定last_name值的查询，因为该列是索引的最左前缀（如本节后面所述）。因此，name索引用于以下查询中的查找：

SELECT * FROM test WHERE last_name='Widenius';

SELECT * FROM test
  WHERE last_name='Widenius' AND first_name='Michael';

SELECT * FROM test
  WHERE last_name='Widenius'
  AND (first_name='Michael' OR first_name='Monty');

SELECT * FROM test
  WHERE last_name='Widenius'
  AND first_name >='M' AND first_name < 'N';

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

SELECT * FROM tbl_name
  WHERE col1=val1 AND col2=val2;

如果你有一个三列索引上(col1, col2, col3)，你有索引的搜索功能 (col1)，(col1, col2)以及 (col1, col2, col3)。

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

总结：index(1,2,3) 索引可用于：1,12,123，索引不适用于：2,23

索引扩展的使用

InnoDB通过将主键列附加到每个辅助索引来自动扩展它们。考虑这个表定义：

CREATE TABLE t1 (
  i1 INT NOT NULL DEFAULT 0,
  i2 INT NOT NULL DEFAULT 0,
  d DATE DEFAULT NULL,
  PRIMARY KEY (i1, i2),
  INDEX k_d (d)
) ENGINE = InnoDB;

此表定义列上的主键(i1, i2)。它还k_d在列上定义了二级索引 (d)，但在内部InnoDB扩展了此索引并将其视为列(d, i1, i2)。

在确定如何以及是否使用该索引时，优化程序会考虑扩展二级索引的主键列。这可以带来更高效的查询执行计划和更好的性能。

优化器可以使用扩展二级索引 ref，range和 index_merge索引访问，松散索引扫描访问，参加和排序优化，以及 MIN()/ MAX() 优化。

以下示例显示优化程序是否使用扩展二级索引如何影响执行计划。

现在考虑这个查询：

EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'

在这种情况下，优化器不能使用主键，因为它包含列(i1, i2)而查询未引用i2。取而代之的是，优化器可以使用二级索引k_d上 (d)，并且执行计划依赖于扩展索引是否被使用。

降序索引

MySQL支持降序索引：DESC在索引定义中不再被忽略，但会导致按键降序存储键值。以前，索引可能会以相反的顺序扫描，但性能会受到影响。可以按正向顺序扫描降序索引，这样更有效。当最有效的扫描顺序混合某些列的升序和其他列的降序时，降序索引还使优化器可以使用多列索引。

请考虑以下表定义，其中包含两列和四列两列索引定义，用于列上升序和降序索引的各种组合：

CREATE TABLE t (
  c1 INT, c2 INT,
  INDEX idx1 (c1 ASC, c2 ASC),
  INDEX idx2 (c1 ASC, c2 DESC),
  INDEX idx3 (c1 DESC, c2 ASC),
  INDEX idx4 (c1 DESC, c2 DESC)
);

表定义产生四个不同的索引。优化器可以为每个ORDER BY子句执行正向索引扫描，无需使用 filesort操作：

ORDER BY c1 ASC, c2 ASC    -- optimizer can use idx1
ORDER BY c1 DESC, c2 DESC  -- optimizer can use idx4
ORDER BY c1 ASC, c2 DESC   -- optimizer can use idx2
ORDER BY c1 DESC, c2 ASC   -- optimizer can use idx3

降序索引的使用受以下条件限制：

只有InnoDB存储引擎支持降序索引，具有以下限制：
- 如果索引包含降序索引键列或主键包含降序索引列，则不支持对辅助索引进行更改缓冲。
- 该InnoDBSQL解析器不使用降序索引。对于InnoDB 全文搜索，这意味着FTS_DOC_ID索引表的列所需的索引不能定义为降序索引。
升序索引可用的所有数据类型都支持降序索引。
降序索引支持普通（nongenerated）和生成的列（包括VIRTUAL和 STORED）。
DISTINCT 可以使用包含匹配列的任何索引，包括降序键部分。
具有降序关键部分的索引不用于 MIN()/MAX() 优化调用聚合函数但没有GROUP BY子句的查询。
支持降序索引BTREE但不支持 HASH 索引。索引FULLTEXT或SPATIAL 索引不支持降序索引。

明确指定ASC并 DESC为代号 HASH，FULLTEXT和 SPATIAL一个错误的索引结果。