一、创建高性能索引
1.1 索引原理
...
1.2 索引分类
1.2.1 B+Tree
1.2.2 Hash
- 特点:
- 哈希索引只包含哈希值和行指针,不存储字段值
- 非值顺序存储,无法用于排序
- 不支持部分索引列查询(哈希值计算使用全部索引列)
- 只支持等值比较查询
- 在哈希冲突少,查询速度快
- 哈希冲突多,索引维护代价高
- 场景
- 在数据仓库
schema
,需要关联很多查询表,哈希索引非常适合 - 例如:
url
大字段的查找
1.2.3 空间数据索引(R-Tree)
...
1.2.4 全文索引
...
1.2.5 其他
...
1.3 高性能索引策略
1.3.1 独立列
1.3.2 前缀索引和索引选择性
1.3.3 多列索引
1.3.4 选择合适的索引列顺序
1.3.5 聚集索引
- 优点:
- 数据索引合并减少IO
- 数据访问更快
- 可以使用覆盖索引
- 缺点:
- 插入速度严重依赖于插入顺序
- 更新聚集索引列代价很高
- 在插入或者更新主键触发行移动,会造成页分裂
- 二级索引(非聚集索引)占用空间
- 二级索引如果没有使用覆盖索引,需要两次查找
- 优化在InnoDB表中按主键顺序插入行
1.3.6 覆盖索引
1.3.7 使用索引扫描来做排序
1.3.8 压缩索引(前缀压缩)
1.3.9 冗余和重复索引
1.3.10 删除未使用的索引
1.3.11 索引和锁
使用索引减少锁定行数。
1.4 优化
- 支持多种过滤条件 现在需要看看哪些列拥有很多不同的取值,哪些列在
WHERE
子句中出现得最频繁。在有更多不同值的列上创建索引的选择性会更好。一般来说这样做都是对的,因为可以让MySQL更有效地过滤掉不需要的行。country
列的选择性通常不高,但可能很多查询都会用到。sex
列的选择性肯定很低,但也会在很多查询中用到。所以考虑到使用的频率,还是建议在创建不同组合索引的时候将(sex, country)
列作为前缀。 但根据传统的经验不是说不应该在选择性低的列上创建索引的吗?那为什么这里要将两个选择性都很低的字段作为索引的前缀列﹖我们的脑子坏了? 我们的脑子当然没坏。这么做有两个理由:第一点,如前所述几乎所有的查询都会用到sex
列。前面曾提到,几乎每一个查询都会用到sex
列,甚至会把网站设计成每次都只能按某一种性别搜索用户。更重要的一点是,索引中加上这一列也没有坏处,即使查询没有使用sex列也可以通过下面的“诀窍”绕过。 这个“诀窍”就是:如果某个查询不限制性别,那么可以通过在查询条件中新增AND SEX IN( 'm','f')
来让MySQL选择该索引。这样写并不会过滤任何行,和没有这个条件时返回的结果相同。但是必须加上这个列的条件,MySQL才能够匹配索引的最左前缀。这个“诀窍”在这类场景中非常有效,但如果列有太多不同的值,就会让 IN()列表太长,这样做就不行了。 - 避免多个范围条件
- 优化排序:索引排序
- 维护索引和表:减少索引和数据的碎片