第五章 创建高性能的索引

第五章 创建高性能的索引

索引的类型：

1. B-Tree索引：如果不指名是哪种索引，那么默认就是B-Tree索引。
   • 底层的存储引擎可能使用不同的存储结构，比如B+Tree，T-Tree。当前大部分的存储引擎支持B+Tree
   • 存储引擎以不同的方式使用B-Tree索引，比如MyISAM使用前缀压缩技术使得索引更小，InnoDB使用元数据进行存储。
   • MyISAM使用数据的物理位置引用被索引的行，而InnoDB使用主键引用被索引的行
   • 当使用组合索引时，如果想要使得索引对查询有效，需要的是先全部匹配第一列，只有第一列匹配完成后，才开始匹配第二列。如果不从第一列开始查找，则不会使用该索引
2. 哈希索引
   • 基于哈希表实现，只有精确匹配索引，索引才会起效
   • 只有Memory引擎支持哈希索引，且支持非唯一哈希索引
   • Hash索引只包含哈希值与行指针
   • 不按索引顺序值排序，所以不能用于排序，也不支持范围查询
   • 不支持索引列的匹配查找，因为计算的整个字段的hash值
   • 只支持等值比较，比如 in()、=、<=>
   • 自适应hash索引：比如有的字段比较长，会导致索引比较长，这样就不好，此时就可以添加一列，这一列存储对长列的hash值，然后在这个hash值列上建索引。使用CRC32()或FNV64()作为hash函数比较合适。需要注意的是，返回多行可以在使用比较长的字段进行比较过滤。
3. 空间数据索引：R-Tree
   • MyISAM支持空间索引，可以用作地理数据存储。这类索引无需前缀查询，它会从所有维度来索引数据。
   • mysql必须使用GIS的有关函数MBRCONTAINS()来维护数据
4. 全文索引
   • 用于查找文本中的关键词，好比是倒排索引
5. 其他索引类别
   • TokuDB使用分型树索引，含有B-Tree优点，也避免了B-Tree缺点

索引的优点：

1. 大大减少服务器需要扫描的数据量
2. 帮助服务器避免排序和临时表
3. 将随机IO变为顺序IO

三星系统说明：

1. 索引将相关记录放在一起获得一星
2. 索引中数据的顺序查找中的顺序一致获得两星
3. 索引中列包含了查询所需的所有列为三星

高性能索引策略

1. 单独的列索引
   • 查询时，有索引的列不应该是表达式的一部分，也不能是函数的参数。应该单独放在表达式的一方
2. 前缀索引与索引选择性
   • 原因：当某一列比较长，是blog/text/很长的varchar时，就需要建立前缀索引，这样可以减少索引的大小
   • 前缀索引长度选择：首选需要知道当前列的分布情况，假设当前列为colum1，此时标准值应该是count(distinct column1)/count(*)，那么选择前缀的长度应该和该值接近。计算count(distinct left(column1, 4))/count(*)。注意：对于长度选择，不止看平均值，还需要看数据分布是否均匀，如果不均匀，最好还是增加长度
   • 创建方式：alter table citytable add key(cityName(7))
   • mysql不支持后缀索引，如果需要，可以将列倒叙存储
3. 在where中的每个条件建立单列索引
   • mysql有索引合并策略，查询时将索引合并查询。但是却不一定好。
   • 使用explain查看时，如果Extra中有 Using union()时，就说明有合并索引。
   • 关闭合并索引策略：尽量不要使用合并索引，通过参数optimizer_switch关闭索引合并功能
4. 索引列顺序
   • 索引列顺序选择需要考虑几个方面：第一：查询时是否使用group by/order by/distinct等。第二：哪一列选择性高
   • 需要根据实际情况进行判断哪列在前，哪列在后。计算每列的散列情况。count(diatinct colum1)/count(*)。但是需要注意的是，当前默认的是column1列比较平均。如果出现该列中大量是某一个值，则就需要修改表结构或者代码了。
5. 聚簇索引
   • 聚簇索引不是单独的索引类型，而是数据的存储方式。所以一个表只能有一个聚簇索引
   • InnoDB通过主键组织聚簇索引
   • 缺点：1. 如果数据都存在内存中，聚簇索引就没有什么优势了。2. 插入速度严重依赖插入顺序。3. 更新聚簇索引列，会导致移动数据位置。4. 插入或更新会导致“页分裂”问题，占用更大空间，此时也会导致全表扫描变慢。5. 二级索引（非聚簇索引）可能比想象的大，因为二级索引叶子节点包含了引用行的主键列。6. 二级索引访问需要两次索引查找，而不是一次。
   • 需要注意的是，在InnoDB中，自适应哈希索引能减少二级索引的第二次索引查找
6. InnoDB与MyISAM数据分布对比
   • MyISAM按照按照数据插入的顺序存储在磁盘上，在每个行前添加行号。其主键索引也没有什么特殊情况。
   • InnoDB按照主键索引的方式存储数据。聚簇索引存储的就是表（完整的表），另外每个叶子节点还有事务ID、用于事务与MVCC的回滚指针等。
   • 另外，INnoDB使用二级索引，其引用的是主键值，那么当移动行后就不需要更新二级索引中的引用了
   • 如果InnoDB中没有需要聚簇的，那么可以设置AUTO_INCREMENT作为主键，此时数据行就是按顺序写入的，根据主键做关联操作性能会更好。需要避免随机值、分布范围大的数据作为主键，比如UUID，这样会导致碎片等的产生。
   • 如果有页分裂碎片，可以使用OPTIMIZT TABLE重建表，优化页填充
   • InnoDB使用AUTO_INCREMENT时，可能出现问题，导致并发情况下，主键上界成为热点，所有插入操作都发生在这里，且还会有锁竞争的问题，此时可以重新考虑设计表，或是更改innodb_autonic_lock_mode配置

7. 覆盖索引
   • 定义：索引覆盖了所有要查询的字段值
   • 覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引都不存储索引列的值，所以mysql只能使用B-Tree索引做覆盖索引。
   • 当使用覆盖查询时，执行计划中的Extra列展示的是Using index
8. 使用索引扫描做排序
   • mysql提供两种方式实现排序，第一种是通过排序操作。第二种是索引顺序扫描。
   • 只有当索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方向都一样，mysql才使用索引来对结果进行排序。当查询多个表时，则只有当order by子句引用的字段全部来自第一个表时，才会使用索引做排序。
9. 压缩（前缀压缩）索引
   • MyISAM使用前缀压缩来减少索引的大小。方法是：比如索引块第一个值为“perform”，第二个值为“performance”，此时第二个压缩后存储类似“7,ance”
   • 使用这种方式导致每个值值的前缀都依赖前一个值，无法使用二分查找，只能从头开始扫描，尤其是倒叙扫描问题更大
   • 但是对于CPU密集应用，扫描需要随机查找，使得索引查找很慢。对于IO密集应用来说，压缩索引需要的磁盘空间小，此时查询需要获取的数据少，查询更快
10. 冗余和重复索引、未使用的索引：

• 重复索引完全不必要，mysql允许相同列上建立相同类型的索引
• 冗余索引，比如已有（A,B）索引，此时又建了（A），此时就是冗余索引，一般这种索引也没有需要
• 未使用的索引，应该删除

11. 索引与锁
    • InnoDB存储引擎对行进行加锁，这个过程在存储引擎层。**当对数据进行查询时，首先在存储引擎层会过滤掉一些数据，然后将数据传给服务器层，在服务层才应用where子句（这句话是由问题的，需要对语句进行分析执行计划，当Extra中有Using where时，表示会在服务器层进行where子句判断）。**此时返回给服务器的行已经在存储引擎层加锁了，若果无法在数据引擎层过滤大部分数据，那么就会对很多的行加锁。

索引常用的操作

1. 对于大部分查询都使用的条件加载索引中，即便这些列选择性不强(比如性别)。如果这么建立了索引，但有的查询却不使用性别，此时可以使用in(‘男’,‘女’)，强制使用索引

2. 避免使用多个范围条件，因为一个范围条件使用索引后，另外一个就无法使用了。若果需要两个范围条件，那么最好将这两个范围条件中的一个转换为 in() 这种方式

3. 优化排序：当需要查询很多页后的数据时，即便是建立了索引，我们也需要从头开始一直找到需要的那个数据。这也就是查询页码越往后的数据，查询越慢。

   • 现在有下面查询语句

   

   维护索引与表

   1. 修复表：REPAIR TABLE 或 ALTER TABLE innodb_table ENGINE=INNODB
   2. 更行索引统计信息：ANALYZE TABLE
   3. 减少索引和数据碎片：OPTIMIZE TABLE 或 ALTER TABLE table_name ENGINE=engine_name

说明

B+Tree：由下图可以看出：

1. 叶子节点到根节点的层数是一样的
2. 在非叶子节点上不存储数据，而是在叶子节点中存数据，每个叶子节点表示的是一页，每页之间用引用连接起来，就会方便范围的查找。可以根据每个叶子节点的值（直接或间接的）获得真是数据所在的位置。