一蓑烟雨任平生

个人成长日记

0%

MySQL之索引

发表于 分类于 ，
本文字数： 2.2k 阅读时长 ≈ 2 分钟
  1. 索引是存储引擎用于快速找到记录的一种数据结构；对于性能的好坏起到决定性的作用
  2. 通过索引号找到对应的数据编号，然后根据编号或对应数据的详细信息
  3. MySQL中的索引在存储引擎中实现

索引类型

B-Tree（一般常用B+Tree）

  1. MyISAM使用前缀压缩技术使得索引更小，InnoDB则按照原数据格式进行存储
  2. MyISAM索引通过数据的物理位置引用被索引的行，InnoDB则根据主键引用被索引的行
  3. BTree中所有的值都是按顺序存储，并且每个叶子到根节点的距离相同
  4. 从索引的根节点开始进行检索，根节点的槽中存放了子节点的指针，存储引擎根据这些指针向下查找
  5. 叶子节点的指针指向被索引的数据
  6. 索引对多个值进行排序的依据是create table 语句中定义索引时列的顺序
  7. 可以使用BTree索引的查询类型：适用于全键值、键值范围或键值前缀；同时适用于order by
    • 全值匹配：索引中所有的列进行完全匹配
    • 最左前缀：最左索引列
    • 列前缀：匹配某列值的开头部分
    • 范围值：匹配索引的第一列
    • 精确匹配某一列并范围匹配另外一列
    • 只访问索引的查询：覆盖索引
  8. 限制
    • 如果不是按照索引的最左列开始查找，则不能使用索引
    • 不能跳过索引中的列进行索引查询
    • 如果查询中有某个列是范围查询，则其右侧的所有列无法使用索引进行优化查询

哈希索引

  1. 基于哈希表实现，只有精确匹配索引所有列的查询才有效
  2. 只有memory引擎显示支持哈希索引（默认索引类型）
  3. 原理：对于每一行，存储引擎会对所有索引列计算一个哈希码，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码不同。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

使用限制

  1. 哈希索引只包含哈希值和行指针，不存储字段值，所以无法实现覆盖索引
  2. 哈希索引数据并不是按照索引值顺序存储（使用slot排序，slot维护指向行的指针），所以无法排序
  3. 哈希索引使用所有列进行生成哈希码，所以不支持部分索引列匹配查询，并且只能等值查询
  4. 在没有hash冲突的情况下，访问哈希索引的数据快，冲突较多时增加维护成本和查询效率
  5. InnoDB中使用自适应哈希索引来生成哈希索引

空间数据索引（R-Tree）

  1. MyISAM支持空间索引，可以用于存储地理数据

全文索引

  1. 全文索引适用于match against操作而不是where操作

索引的优点

  1. 索引大大减少了服务器需要扫描的数据量
  2. 索引可以帮助服务器避免排序和临时表
  3. 所以可以将随机IO转换成顺序IO

注意：对于TB级别的数据，定位单条记录的意义不大，所以经常使用块级别原数据技术来替代索引。

高性能的索引策略

独立的列

如果查询中的列不是独立的，比如有计算，有函数调用则无法正常使用索引。独立列表示索引不能是表达式的一部分，也不能是函数的参数。以下是反例：

1
2
select user_name from t_ba_user where age + 1 = 6;
select user_name from t_ba_user where length(user_name) = 10;

前缀索引和索引选择性

  1. 如果索引很长的字符列，会使索引变得大且慢，处理方式一种是使用哈希索引，一种是限制索引的长度。
  2. 索引选择性计算：不重复索引值数量/数据表的记录总数，越接近于1选择性越高，索引唯一索引定位数据的速度快
  3. 计算完整列的选择性，并是前缀的选择性接近于完整列的选择性
  4. 前缀索引是一种能够是索引更小，更快的有效办法，但是无法用于排序和分组，也无法使用前缀索引进行覆盖查询

多列索引

错误的认知：

  1. 为每个列创建独立的索引
  2. 按照错误的顺序创建多列索引

在MySQL5.0+的版本中提供了“索引合并”的概念，一定层度上能够处理单列索引的局限；OR条件进行联合（union）、AND条件的相交（intersection）、以及OR与AND的组合；如果出现相关的“索引合并”则需要进行索引单列到多列的优化。可以使用optimizer_switch关闭合并功能。也可以使用explain分析sql的执行计划进行优化。

选择合适的索引列顺序

  1. 索引可以按照升序或降序进行扫描，以满足精确符合列顺序的排序、分组、去重等子句的查询需求
  2. 将选择性最高的列放在索引的最前列，并且需要重复考虑整体基数，但最好优先考虑随机IO和排序产生的性能影响
  3. 可能需要根据那些运行频率较高的查询来调整列的顺序，让这种情况下索引的选择性最高

聚簇索引

  1. 一种数据存储方式，InnoDB中聚簇索引实际上是在同一个数据结构中保存了B-Tree索引和数据行。
  2. 数据行实际存储在叶子页中。
  3. 通过主键进行聚簇索引的管理，如果没有主键则选择一个唯一非空索引替代，如果还是没有数据库则使用默认的rowid管理

优点

  1. 可以把

缺点

覆盖索引

索引使用原则

  1. 选择唯一性索引：唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
  2. 为经常需要排序、分组和联合操作的字段建立索引：
  3. 为常作为查询条件的字段建立索引。
  4. 限制索引的数目：
    • 越多的索引，会使更新表变得很浪费时间。
    • 尽量使用数据量少的索引
  5. 如果索引的值很长，那么查询的速度会受到影响：尽量使用前缀来索引
  6. 如果索引字段的值很长，最好使用值的前缀来索引。
  7. 删除不再使用或者很少使用的索引
  8. 最左前缀匹配原则，非常重要的原则。
  9. 尽量选择区分度高的列作为索引：区分度的公式是表示字段不重复的比例
  10. 索引列不能参与计算，保持列“干净”：带函数的查询不参与索引。
  11. 尽量的扩展索引，不要新建索引。