创建高性能的索引

in 编程
关注公众号【好便宜】( ID:haopianyi222 ),领红包啦~
阿里云,国内最大的云服务商,注册就送数千元优惠券:https://t.cn/AiQe5A0g
腾讯云,良心云,价格优惠: https://t.cn/AieHwwKl
搬瓦工,CN2 GIA 优质线路,搭梯子、海外建站推荐: https://t.cn/AieHwfX9

0x00前言

本书讲述到定稿前的MySQL5.5版,所以下面内容的适用范围止步于MySQL5.5。本文仅仅强调书中讲述的重中之重,
以便快速查阅,详细的内容还请认真阅读书本和MySQL的官方文档。


0x01索引基础

索引可以包含一个或多个列的值。

B-Tree索引

  1. 简介
    实际上很多存储引擎使用的是B+Tree(如下图)。

输入图片说明
(文章最后有B树和B+树的一些简介)
2. 可以使用B-Tree索引的查询类型

  1. B-Tree索引的限制
    这些限制都和索引列的顺序有关。

<!--more-->

哈希索引

  1. 简介
    哈希索引基于哈希表实现,只有精确匹配索引所有列的查询才有效。只有Memory引擎显式支持,,且支持的是
    非唯一哈希索引(哈希值相同的通过链表保存,再做相等比较)同时该引擎也支持B-Tree索引。需要作很多关联
    的星型schema,使用哈希索引非常适合查找表的需求。
  2. 限制
  1. 可创建自定义哈希索引,主键的哈希值作为索引(需要添加hash列作为冗余),通过触发器进行hash列的维护。
    但是不要用强哈希函数如MD5或者sha,这样hash列会非常长。查询时要在where查询加入哈希判断。

空间数据索引(R-Tree)

空间索引会从所有维度来索引数据。MySQL的MyISAM支持,InnoDB还不支持,其他数据库如PostgreSQL支持。

全文索引

复制


0x02索引的优点

“三星”索引的标准


0x03高性能的索引策略

独立的列

如果查询中的列不是独立的,则MySQL就不会使用索引。“独立的列”是指索引列不能是表达式的一部分,也不能是
函数的参数。

前缀索引和索引选择性

对于BLOB、TEXT或者很长的VARCHAR类型的列,必须使用前缀索引,因为MySQL不允许索引这些列的完整长度。
选择足够长的前缀以保证较高地选择性,同时又不能太长(以便节约空间)。
下面是选择性的公式:

SELECT COUNT(DISTINCT <col>)/COUNT(*) FROM <table>;

前缀索引是一种能使用索引更小、更快的有效办法,但另一方面也有其缺点:MySQL无法使用前缀索引做ORDER BY
和GROUP BY,也无法使用前缀做覆盖扫描。
存储网站的会话(session)时,需要在一个很长的十六进制字符串上创建索引。此时如果采用长度为8的前缀索引
通常能显著地提升性能,并且这种方法对上层应用完全透明。
有时候后缀索引(suffix index)也有用途(邮箱名称,XXXXXX@~~~.com)。

多列索引

不要对每个列创建独立的索引,或者按照错误的顺序创建多列索引(下面会解释怎样是正确的顺序)。
糟糕的索引建立:

索引合并是MySQL5.0后的新特性,使用EXPLAIN查看发现索引合并时最好重新建立何时的索引。

选择合适的索引列顺序

正确的顺序依赖于使用该索引的查询,并且同时需要考虑如何更好地满足排序和分组的需要。(仅适用于B-Tree)
经验法则:将选择性最高的列放到索引最前列。
当不需要考虑排序和分组的时候,将选择性最高的列放在前面通常是最好的,此时只是用于优化where条件的查询。
性能是根据那些运行频率最高的查询来调整索引列的顺序,如上面的公式,即具体值/整体基数。

聚簇索引(InnoDB的基本原理,重要)

并不是一种单独的索引类型,而是一种数据存储方式。数据行存放在索引的叶子页中,简单地说就是索引在数据表中。

  1. 优点:
  1. 缺点

聚簇索引的每一个叶子节点都包含:

最好避免随机的(不连续且值得分布范围非常大)聚簇索引。(主键使用AUTO_INCREMENT为佳)
InnoDB默认的最大填充因子是页大小的15/16,留出部分空间用于以后修改。InnoDB无法简单地总是把新行插入
到索引的最后,而是需要为新的行寻找合适的位置(MyISAM可以)。

InnoDB写入的缺点:

因此尽可能地使用单调递增地聚簇键的值来插入新行。

覆盖索引

MySQL可以使用索引来直接获取列的数据,这样就不在需要读取数据行。这样的优点:

哈希索引、空间索引和全文索引等都不存储索引列的值,不能做覆盖索引。
执行LIKE操作或者查询所有列不会执行聚簇索引。但是采用延迟关联可以作一定的优化。

使用索引扫描来做排序

  1. 只有当索引的列顺序和ORDER BY子句的顺序完全一致,并且所有列的排序方向都一样时,才能够使用索引来对结果排序。
  2. 如果是关联查询,只有当ORDER BY子句引用的字段全部为第一个表时,才能使用索引做排序。
  3. 满足最左前缀的要求。例如,第一列提供了常量条件,第二列可进行索引排序。
  4. 其他的都不可以。

压缩(前缀压缩)索引

MyISAM压缩每个索引块的方法是:先完全保存索引块中的第一个值,然后将其他值赫尔第一个值进行比较得
到相同前缀的字节数和剩余的不同后缀部分。

冗余和重复索引

冗余:在同一列上创建不同类型的索引来满足不同的查询需求。
重复:毫无益处。
应该尽量扩展已有的索引而不是创建新索引,但是有时候扩展已有的索引会导致其变太大。
当大量的索引到导致性能瓶颈时,删除之。

未使用的索引

删除之。

索引和锁

InnoDB只有在访问行的时候才会对其加锁,而索引能够减少InnoDB访问的行数,从而减少锁的数量。
即使使用了索引,InnoDB也可能锁住一些不需要的数据。


0x04索引案例学习(有意思)

尽可能将需要做范围查询的列放到索引的后面,以便优化器能使用尽可能多的索引列。
多个等值条件查询不是范围查询。
延迟关联的范式:

SELECT <cols> FROM profiles INNER JOIN (
  SELECT <primary key cols> FROM profiles
  WHERE new_table_name.XXX1=? ORDER BY XXX2 LIMIT 100, 10
) AS new_table_name USING (<primary key cols>);


0x05维护索引和表

减少索引和数据的碎片

使用OPTMIZE TABLE或者导出再导入的方式来重新整理数据。不支持该指令的表可以改变引擎再执行。

ALTER TABLE <table> ENGINE=<engine>;

如果进行碎片整理将数据压缩到一起,可能反而会导致后续的更新操作触发一系列的页分裂和重组,这会
对性能造成不良的影响。


0x06小结


0xff B树简述

B树又叫平衡多路查询树,一颗m阶的B树(m叉树)的特征如下:

  1. 树中每个结点最多含有m个孩子(m>=2)(一般例子m=5)
  2. 除根节点和叶子结点外吗,其他每个结点至少有ceil(m/2)个孩子(ceil()取上限,2.5取3)(m=5时,ceil(5/2)=3)
  3. 若根节点不是叶子结点,则至少有2个孩子
  4. 所有叶子结点都出现在同一层
  5. 每个非终端结点中包含:

输入图片说明

删除一个元素时,先向富裕的子结点借,再向父节点借

B树中关键字分布在整个B树中,上层结点出现过的关键字不会再出现在叶结点。
B树和B+树的差异是:


引用

《高性能MySQL · 第三版》

关注公众号【好便宜】( ID:haopianyi222 ),领红包啦~
阿里云,国内最大的云服务商,注册就送数千元优惠券:https://t.cn/AiQe5A0g
腾讯云,良心云,价格优惠: https://t.cn/AieHwwKl
搬瓦工,CN2 GIA 优质线路,搭梯子、海外建站推荐: https://t.cn/AieHwfX9
扫一扫关注公众号添加购物返利助手,领红包
Comments are closed.

推荐使用阿里云服务器

超多优惠券

服务器最低一折,一年不到100!

朕已阅去看看