Mysql索引

一.各类索引介绍

索引的优点：

• 减少服务器扫描的数据量
• 将随机IO变为顺序IO
• 帮助服务器避免排序和临时表的创建

B-Tree索引：大多数Mysql引擎都支持这种索引，通常意味着所有的索引值都是按照顺序存储的，很适合查找范围数据。

• MyISAM使用前缀压缩技术使得索引更小，索引通过数据的物理位置引用被索引的行
• InnoDB索引按照原数据格式进行存储，根据主键引用被索引的行
• B-Tree索引的查询类型，适用于全键值，键值范围或键前缀查找，其中键前缀查找只适用于根据最左前缀的查找
• B-Tree索引可以高效地使用最左前缀列

哈希索引：基于哈希表实现，只有精确匹配索引所有列的查询才有效

• 只有Memory引擎显示支持哈希索引，也是Memory的默认索引
• Memory支持非唯一哈希索引，如果多个列的哈希值相同，索引会以链表的形式存放多个记录指针到同一个哈希条目中
• InnoDB支持自适应哈希索引，当某些索引值被使用的非常频繁，InnoDB会在内存中基于B-Tree索引创建哈希索引
• 哈希索引的哈希值（slot槽）是顺序存储的，指向数据的指针不是，所以哈希索引的数据无法排序

空间数据索引：MyISAM支持空间索引，可以用作地理数据存储

• 无需像B-Tree索引一样仅可以使用最左前缀列；空间数据索引可以从任意维度索引数据，但是必须使用GIS函数维护数据

全文索引：特殊类型的索引，它查找的是文本中的关键字，而不是比较索引的值，类似于搜索引擎做的事情

聚簇索引：聚簇索引不是一种索引类型，而是一种数据存储方式，举例InnoDB说明

• InnoDB引擎，数据表的数据行是存放在聚簇索引的叶子节点中，此外叶子节点还包含相邻的索引键值
• InnoDB通过主键聚集数据，如果没有定义主键，将隐式定义一个主键作为聚簇索引
• 优点：数据集中，查找操作磁盘IO少，访问快
• 缺点：更新代价很高（B-Tree内部结构操作复杂）；插入速度依赖于插入顺序；相比较内存数据没优势；顺序插入在高并发下会有间隙锁竞争，影响性能

二.高性能索引策略

1.独立的列

索引列不能是表达式或者函数的一部分，因为在索引遍历时会有计算的额外开销，达不到高性能的要求

始终将索引列放在比较符号的一侧，避免索引时做计算

2.索引选择性

索引选择性是指不重复的索引值和数据表记录总数的比值，0-1之间；也称索引的离散度。

3.前缀索引

当一个列内的字段过长，可以考虑拆分字段内容，将字段的前缀单独存储一个列中，拆分前缀有一定的讲究，即前缀的不重复数应该接近数据表的记录数

三.其他

mysql优化器：一般在查询超过整个表20%的数据时，会考虑使用聚族索引来查找数据。

回表：通过辅助索引拿到主键，在通过主键从B树上找到行数据；回表就相当于两次索引树扫描操作，而主键查询只有一次。

数据量统计：聚簇索引存储了所有数据，而辅助索引只保存了索引列和主键，所以通过辅助索引统计数据量可以减少IO操作。

覆盖索引：根据辅助索引直接查出数据而不回表查询。

Innodb的哈希索引：实际上还是基于b-tree索引。

如果我们发现在查询一定量数据使用辅助索引要比主键索引快，而数据库又没有按照我们期望的去使用辅助索引，则我们可以通过子查询或force index来强制使用辅助索引