MySQL之 高性能的索引策略

本节内容总结自《High Performance MySQL》

高性能的索引策略

独立的列
  • 查询时，最好不要对索引字段建立表达式，也不能将索引字段作为函数的参数，因为这会使索引失效，MySQL无法自动解析这种方程。
前缀索引和索引选择性
  1. 索引选择性 = 不重复的索引值 / 数据表的记录总数 T
  2. 前缀索引
    1. 定义：通常可以索引字符串开始部分的固定个数，这样可以大大的节约索引空间，从而提高索引效率。对于BLOB，TEXT或者很长的VARCHAR类型的列，必须使用前缀索引。
    2. 前缀长度的选择：要折衷选择前缀的长度来构建索引。一个较好的方法是计算完整列的选择性，并使前缀的选择性接近于完整列的选择性。通常情况下只看平均选择性还是不够的，还需要结合最坏情况下的选择性进行折衷考虑。
    3. 缺点：MySQL无法使用前缀索引做ORDER BY和GROUP BY操作，也无法使用前缀索引做覆盖扫描。
    4. 后缀索引：MySQL并不原生支持后缀索引，但是可以通过手动把字符串反转后存储，并基于此建立前缀索引的方式实现原字符串后缀索引的建立。
多列索引
  1. 多列索引的定义：是按照一定的顺序组合列来构成一个多列索引，这与多个单列索引是不一样的，在多个列上建立单列索引大部分情况下并不能提高MySQL的查询性能。
  2. 索引合并：一定程度上可以使用表上的多个单列索引来定位指定的行。MySQL能够同时使用多个单列索引并将结果进行合并。索引合并有三种方式，OR条件的联合，AND条件的相交，组合前两种情况的联合及相交。但是，如果MySQL内部使用了索引合并，则会降低查询的效率。
  3. 索引合并的弊端
    1. 当出现服务器对多个索引做相交操作时，通常意味着需要一个包含所有相关列的多列索引，而非多个单列索引。
    2. 当服务器需要对多个索引做联合操作时，通常会耗费大量的CPU和内存资源在算法的缓存，排序和合并操作上。
    3. 更重要的是优化器不会把上述的这些成本计算到"查询成本"中，这会使得查询的成本被低估。
    4. 如果在EXPLAIN中看到有索引合并，应该好好检查一下查询和表结构。
    5. 可以通过参数optimizer_switch来关闭索引合并功能，也可以使用IGNORE_INDEX来提示优化器忽略掉某些索引。
选择合适的索引列顺序
  1. 为什么要选择合适的索引列顺序：对于B-Tree索引，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。所以，索引可以按照升序或者降序进行扫描，以满足查询需求。
  2. 常见经验法则
    1. 将选择性最高的列放到索引最前列：这种情况适用于前缀索引或者不考虑排序或分组的索引。通常不如避免随机I/O和排序那么重要。
    2. 全局基数：全局基数也是一个很重要的参考点，应该将基数更小的列放到最前面。
    3. 查询频率：应该照顾那些查询非常频繁的列顺序，来提高系统的整体性能。
    • 往往需要综合考虑查询频率，全局基数和选择性来确定合适的索引列顺序。
  3. 关于超级账户的问题：一般系统中会存在超级账号，例如对于那些没有登陆的用户，都会使用这个账户访问，或者系统中的其他账户都是这个账户的好友（这个账户需要为其他账户发送通知消息等），因此会涉及这个账户的大量某类访问。解决方案是区分这类特殊用户和组，禁止针对这类用户和组执行某些查询。
聚簇索引
  1. 定义：聚簇索引不是一种单独的索引类型，而是一种数据存储方式。InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。数据行实际上存放在索引的叶子页中。"聚簇"表示数据行和相邻的键值紧凑的存储在一起（也有可能由于页分裂分开）。一个表只能有一个聚簇索引，因为无法同时把数据行存放在两个不同的地方。叶子页包含了行的全部数据，但是节点页只包含索引项。
  2. InnoDB通过主键聚集数据：也就是说被索引的列是主键列，如果没有主键列，InnoDB会选择唯一的非空索引代替。如果没有这样的索引InnoDB会隐式定义一个主键作为聚簇索引。InnoDB只聚集在同一个页面中的记录。包含相邻键值的页面可能会相距甚远。
  3. 聚簇索引的优点
    1. 可以把相关数据保存在一起，这样只需要从磁盘读取少数的数据就能访问这些相关的数据。
    2. 数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。
    3. 使用覆盖索引扫描的查询可以直接使用叶节点中的主键值。
  4. 聚簇索引的缺点
    1. 聚簇数据最大限度提高了I/O密集型应用的性能，但是如果数据全部存放在内存中，访问顺序就没那么重要了。
    2. 插入速度严重依赖于插入顺序。按照逐渐的顺序插入是加载数据到InnoDB最快的方式。否则最好在加载完成后使用OPTIMIZE TABLE重新组织表，因为表中会有很多碎片。
    3. 更新聚簇索引列的代价很高。因为会强制InnoDB将每个被更新的行移动到新的位置。
    4. 基于聚簇索引的表在插入新行，或主键被更新导致需要移动行的时候，可能面临页分裂问题。当行的主键值必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行。页分裂会导致表占用更多的的磁盘空间。
    5. 聚簇索引可能导致全表扫描变慢，尤其是行稀疏，或者由于页分裂导致数据存储不连续时。
    6. 二级索引可能比想象的更大，因为在二级索引的叶子节点包含了引用行的主键列。
    7. 二级索引访问需要两次索引查找而不是一次。因为二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。这意味着通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获得对应的主键值，然后根据这个主键值区聚簇索引中查找对应的行。对于InnoDB，自适应哈希能够减少这样的重复工作。
InnoDB和MyISAM的数据分布对比
  1. MyISAM
    1. 数据的存放：MyISAM通过一张数据表和一张索引表存放数据，数据表会根据数据插入的顺序建立唯一编号（为每行数据）。索引表则通过主键排序方式指定该索引对应数据行在数据表中的存储序号。MyISAM中主键索引和其他索引没什么不同。主键索引就是一个名字为PRIMARY的唯一非空索引。
    2. 数据的查找：通过主键在索引树的叶节点找到数据的行号，再在数据存储的表中查出行。因为行是定长的，MyISAM可以从表的开头跳过所需要的字节找到需要的行（MyISAM会根据变长还是定长的行使用不同的策略）。
  2. InnoDB
    1. 数据的存放：InnoDB的非叶子节点包含了索引列和一个指向下级节点的指针。InnoDB二级索引的叶子节点中存储的不是行指针，而是主键值，并以此作为指向行的指针。使用主键值当作指针会让二级索引占用更多的空间，换来的好处是，InnoDB在移动行时无需更新二级索引中的这个指针。对于聚簇索引，每一个叶子节点包含了主键值，事务ID，用于事务和MVCC的回滚指针以及所有的剩余列。如果主键是一个列前缀索引，InnoDB也会包含完整的主键列和剩下的其他列。
    2. 在InnoDB表中按主键顺序插入行
      1. 使用自增列作为主键：如果正在使用InnoDB表且没有什么数据需要聚集，那么可以定义一个代理键作为主键，代理键最好选取自增列，这样可以保证数据行是按顺序写入的。因为主键的值是顺序的，所以InnoDB把每一条记录都存储在上一条记录的后面。当页达到最大填充因子时，下一条记录就会写入新的页中。主键页就会近似于被顺序的记录填满。
      2. 避免随机的非常大的聚簇索引：如果使用UUID作为聚簇索引则会非常糟糕，会使得存储引擎总是需要为新的行寻找合适的位置，还有如下缺点。
        1. 写入的目标页可能已经刷到磁盘上并且从缓存中移除，或者还没有被加载到缓存中，InnoDB再插入前不得不先找到并从磁盘读取目标页到内存中。这将会导致大量的随机I/O。
        2. 因为写入是乱序的，InnoDB不得不频繁的做页分裂操作，以便为新的行分配空间。页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个页。
        3. 由于频繁的页分裂，也会变得稀疏并且被不规则的填充，所以最终数据会有碎片。导致建表之后需要做一次OPTIMIZE TABLE来重建表并优化页填充。
覆盖索引
  1. 定义：如果一个索引包含所有需要查询的字段的值，我们就称之为覆盖索引，也就是说要查询的值都在索引中，不需要回表(例如MyISAM的数据表)或者InnoDB的主键索引查询（InnoDB的二级索引的叶子节点都包含了主键的值，这意味着InnoDB的二级索引可以有效地利用这些额外的主键列来覆盖查询）。MySQL查询优化器在执行查询前判断是否有一个索引能进行覆盖。只有B-Tree索引能做覆盖索引。成功使用覆盖索引在执行计划的Extra列中可以看到Using index信息。
  2. 覆盖索引带来的好处
    1. 索引条目通常远小于数据行大小，所以要读取索引，那MySQL就会极大地减少数据访问量。
    2. 因为索引是按照列值顺序进行存储的，所以对于I/O密集型的范围查询会比随即从磁盘读取每一行数据的I/O要少得多。
    3. 由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。
  3. 索引无法覆盖查询的原因
    1. 没有任何二级索引覆盖查询
    2. MySQL不能在索引中执行LIKE操作。MySQL能在索引中做最左前缀匹配的LIKE比较，因为该操作可以转换为简单的比较操作。MySQL只能提取数据行的值而不是索引值来比较。
  4. 解决索引无法覆盖查询
    • 可以做延迟关联操作，具体操作是，先利用覆盖索引查出一个唯一字段，然后在这个结果集的基础上再做其他操作。
使用索引扫描来做排序
  1. MySQL生成有序结果的方法：MySQL有两种可以生成有序结果的方法：通过排序操作，或者按照索引顺序扫描。如果执行计划中type列的值为index，则说明MySQL使用了索引扫描来排序。索引扫描本身是非常快的。但如果索引不是覆盖索引，那么不得不没扫描一条索引记录就回表查询对应的行。这基本上都是随机I/O，因此按索引顺序读取数据的速度通常比顺序的全表扫描要慢，尤其是在I/O密集型的工作负载时。
  2. 使用索引扫描排序的条件：只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（正序或逆序）都一样时，MySQL才能使用索引来对接过做排序。
  3. 多表关联下使用索引排序：只有当ORDER BY子句引用的字段全部为第一个表时才能使用索引做排序。在ORDER BY中的字段也需要满足最左前缀的顺序。也有一种情况可以不需要满足最左前缀的要求，那就是在WHERE子句或者JOIN子句中对那些被跳过的列指定了常量值，这时候将可以使用索引扫描排序。
    • 这里有一个小问题，在Inner_join时，优化器会选择较小的表当作关联的第一张表，因此有可能你SQL语句中的第一张表与实际执行的第一张表并不是同一张表，所以有可能理论上使用了索引排序而实际上不会使用索引排序。
前缀压缩索引
  1. MyISAM中的前缀压缩索引： MyISAM使用前缀压缩来减少索引的大小，从而让更多索引可以放入内存。默认值只压缩字符串，但通过参数设置也可以对整数做压缩，可以在CREATE TABLE语句中指定PACK_KEYS参数来控制索引压缩方式。
  2. MyISAM压缩每个索引块的方法：先完全保存索引块中的第一个值，然后将其它值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分，把剩余部分存储起来即可。例如，索引块中第一个值是"perform"，第二个值是"performance"，那么第二个值的前缀索引后存储的是类似"7,ance"这样的形式。
  3. MyISAM中压缩索引的缺点：因为每个值的压缩前缀都依赖前面的值，所以MyISAM查找时无法在索引块使用二分查找而只能从头开始扫描。因此选用压缩索引需要在磁盘和CPU内存资源两者之间做权衡。对于CPU密集型应用压缩索引很显然不好，对于I/O密集型应用来说好处会比成本多很多。
冗余和重复索引
  1. 重复索引：在相同的列上按照相同的顺序创建相同类型的索引。应该避免这样的索引，发现之后应该立即删除。
  2. 冗余索引：新创建的索引是其他已经存储索引的最左前缀索引。通常情况下都不需要冗余索引，应该尽量扩展现有的索引而不是创建新索引。但有时也会出于性能的考虑来创建冗余索引，如果现有的扩展使得原有索引变得太大，从而影响其他使用该索引的查询的性能，这种情况可以使用冗余索引来分担原来索引的负担。
未使用的索引
  • 可能还会有一些服务器永远用不到的索引，这样的索引完全是累赘，建议考虑删除。有两个工具可以帮助定位未使用的索引。最简单有效的办法是在Percona Server或MariaDB中先打开userstates服务器变量（默认是关闭的），然后让服务器正常运行一段时间，再通过查询INFORMATION_SCHEMA.INDEX_STATISTICS就可以查到每个索引的使用频率。另外还可以使用Percona Toolkit中的pt-index-usage，该工具可以读取查询日志并对日志中的每条查询进行EXPLAIN操作，然后打印出关于索引和查询的报告。这个工具还可以帮助你定位那些偶尔服务质量差的查询。该工具也可以将结果写入到的MySQL的表中，方便查询。
索引和锁
  1. 索引可以减少锁：索引可以让查询锁定更少的行，锁定超过需要的行会增加锁争用并减少并发性。InnoDB只有在访问行的时候才会对其加锁，而索引能够减少InnoDB访问的行数，从而减少锁的数量。
  2. 当索引无法过滤所有不需要的行时：只有当InnoDB在存储引擎层能够过滤掉所有不需要的行时才有效。因为如果索引无法过滤掉无效的行，那么当数据返还给服务器层之后，MySQL服务器就会应用WHERE子句（此时在执行计划的Extra列中显示Using Where）。这时已经无法避免行锁定了。InnoDB在服务器端过滤掉行之后会释放锁。
  3. InnoDB在二级索引上使用共享锁，但访问主键索引需要排他锁。如果索引无法过滤所有无效的列，会导致服务器Using Where，这会导致无法使用覆盖索引。并且会使得SELECT FOR UPDATE 比LOCK IN SHARE MODE 或非锁定查询要慢得多。
posted @ 2021-01-30 21:17  Cruel_King  阅读(114)  评论(0)  编辑  收藏  举报