创建高性能索引

时间：2021-11-23 21:18:51

引用：

http://blog.csdn.net/java_4_ever/article/details/41117811

http://blog.163.com/lgh_2002/blog/static/44017526201091910101439/


数据库的索引，了解一下大致原理以及索引的策略差不多就行了。实际运用的时候，最好收集一下数据库的查询语句，然后根据查询频繁程度调整索引策略。

索引类型

通常所说的索引都是B-Tree索引，存储引擎以不同的方式使用B-Tree索引，性能各不相同，各有优劣。

例如：MyISAM使用前缀压缩技术使得索引更小，但InnoDB则按照原数据格式进行存储。MyISAM索引通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行。

B-Tree索引适用的查找：

  • 全值匹配
  • 匹配最左前缀（匹配索引做左边的列）
  • 匹配列前缀（匹配某一列的值的开始部分）
  • 匹配范围值
  • 精确匹配某一列并范围匹配另外一列
  • 只访问索引的查询

B-Tree的限制：

  • 如果不是按照索引的最左列开始查找，则无法使用索引
  • 不能跳过索引中的列
  • 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找

哈希索引： 基于哈希表实现，所以非常快。只有精确匹配索引所有列的查询才有效。在MySQL中只有Memory引擎显示支持哈希索引。

由于这个索引不常用，这里就简单说一下优缺点： 优点：

  • 速度快，比B-Tree要快。

缺点：

  • 不存储字段值，只包含哈希值和行指针
  • 按照哈希值排序，而不是按照索引值的顺序存储，所以无法用于排序。
  • 不支持索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值。
  • 只支持等值查询
  • 如果有哈希冲突，那么一些索引维护操作的代价也会很高

InnoDB有一个“自适应哈希索引”功能，InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引。这就让B-Tree索引也具有哈希索引的一些优点，但这是一个完全自动的内部的行为，用户无法干预，顶多是关闭该功能。

创建自定义哈希索引： 基本思路是这样的，在B-Tee基础上创建一个伪哈希索引。这和真正的哈希索引不是一回事，因为还是使用B-Tree进行查找，但是它使用哈希值而不是键本身进行索引查找。你需要做的就是在查询的where子句中手动指定使用哈希函数。（这样可以用很小的索引为很长的键创建索引，但是查询的时候需要指定查询的常量，以免哈希冲突的情况下产生逻辑错误。）

索引三星系统

  • 索引将相关的记录放到一起
  • 索引中的数据顺序和查找中的排列顺序一致
  • 索引中的列包含了查询中需要的全部列

索引策略

  • 独立的列，索引不能是表达式的一部分也不能是函数的参数。（个人觉得这个反倒是写查询语句的时候需要注意的地方，估计是角度不同吧。）
  • 前缀索引和索引选择性
    • 前缀索引是指用某个键的前N个字段作为索引值
    • 索引选择性指的是不重复的索引值和总记录数的比值，当然是越接近1越好。
    • 建索引的时候如果遇到比较纠结的状况，可以优先选择索引选择性比较高的列。
  • 多列索引（一个索引包含多个列），索引合并有时候是优化的结果。
    • 当出现服务器对多个索引做相交操作时（多个and），通常意味着需要一个包含所有相关列的多列索引
    • 当出现服务器对多个索引做联合操作时（多个or），通常需要消耗大量CPU和内存资源在算法的缓存、排序和合并操作上。
    • 更重要的是，优化器不会把这些计算到成本里面去。
  • 选择合适的索引顺序
  • 聚簇索引
  • 覆盖索引，如果一个索引包含所有需要查询的字段的值，我们就称之为“覆盖索引”
    • MySQL查询优化器会在查询前判断是否有一个索引能进行覆盖。假设索引覆盖率WHERE条件的字段，但不是整个查询涉及的字段。即使条件为false，MySQL5.5和更早的版本也总是会回表获取数据行，尽管这一行最终还是会被过滤掉。
    • 关于非聚簇索引无法成为覆盖索引的一个解决办法（非聚簇索引包含所有WHERE中的字段）：就是先在非聚簇索引中根据条件查询出来主键，然后再作为子查询或者Inner Join的一部分，根据主键查询出来需要的字段。这种办法可以叫做“延迟关联”，这样在数据量比较小的时候效果很明显；如果数据量比较大，跟完全无法利用索引覆盖来比，效果差不多。
  • 利用索引扫描来做排序
    • 索引列的顺序和ORDER BY子句的顺序一致，并且所有列的排序方式都一样时
    • 如果查询关联多张表，只有当ORDER BY子句引用的字段全为第一个表时
    • ORDER BY和查找型查询的限制是一样的
    • 当前导列为常量的时候（如：sex in ('m', 'f')），ORDER BY子句可以不满足索引的最左前缀的要求
  • 压缩（前缀压缩）索引
    • MyISAM压缩每个索引块的方法是，先完全保存索引块中的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分，把这部分存储起来即可。
    • 可以在CREATE TABLE语句中指定PACK_KEYS参数来控制压缩方式
    • 优点在于减少磁盘空间（可能只需要十分之一），适用于CPU密集型业务
    • 缺点在于随机查找和倒序扫描特别慢

支持多种过滤条件

这种状况下有这么个策略，可以将那些选择性不高的列，建在索引前面，这样在查询的时候如果此列不是查询条件，可以在WHERE中使用IN（）这种策略，比如SEX。然后将那些经常会用于范围查询的放在最后面，比如AGE。

避免多个范围条件：EXPLAIN的输出中从type来看，IN和范围查询都会是range，多个范围查询会比较容易导致索引失效。

维护索引和表

使用CHECK TABLE来检查表是否损坏，REPAIR TABLE来修复，也可以使用如下不作任何操作的命令来修复：

mysql> ALTER TABLE innodb_tb1 ENGINE=INNODB;

更新索引信息：ANALYZE TABLE



需要了解MySQL 的特性:

  1. 一条 SQL 语句只能使用 1 个索引 (5.0-)，MySQL 根据表的状态，选择一个它认为最好的索引用于优化查询
  2. 联合索引，只能按从左到右的顺序依次使用
  3. 从上边可以看到结合索引，也可以叫多列索引，形如 key ('B1','B2','B3' ,'B4')等的，排序的思路通常为，先按照B1来排序，B1相同，然后按照B2排序，以此类推，这样对于（B1）,(B1,B2), (B1,B2,B3)的索引都是有效的，可是对于（B2,B3）这样的索引就无效了。

标签：

相关文章