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

介绍:

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

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

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

如何使用索引:
 

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

大多数MySQL索引(PRIMARY KEY, UNIQUEINDEX和 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树数据结构可以让索引快速查找特定值,一组值,或值的范围,对应于运营商,如=, >, BETWEENIN,等等,一在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_namefirst_name 列。该索引可用于查询中的查找,这些查询指定已知范围内的 值last_namefirst_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)

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

优化器可以使用扩展二级索引 refrange和 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 索引。索引FULLTEXTSPATIAL 索引不支持降序 索引。

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

 

 

 

 

 

 

 

 

 

 

 

 

 

优化总篇:https://blog.csdn.net/weixin_42749765/article/details/88223273

 

 

文章持续更新,转发表明出处,方便更新!

 

 

 

 

评论 3
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值