mysql中的索引

索引的类型

B-Tree索引

• InnoDB中使用的是B+Tree,每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历。InnoDB中根据主键引用被索引的行。
• MyISAM索引通过数据的物理位置引用被索引的行。

索引匹配

• 全值匹配
• 匹配最左前缀 只使用索引的第一列
• 匹配列前缀 值匹配某一列的值开头的部分
• 匹配范围值
• 精确匹配某一列并范围匹配另外一列
• 只访问索引的查询（索引覆盖）

哈希索引

• 只有精确匹配索引所有列的查询菜有效
• Memory引擎支持（非唯一哈希索引）
• 哈希索引只包含哈希值和行指针
• 无法用于排序
• 哈希冲突

空间数据索引 R-Tree

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

全文索引

• 查找的是文本中的关键字
• 适用MATCH AGAINST操作而不是普通的WHERE操作

聚簇索引是一种数据存储方式，InnoDB的聚簇索引实际上在同一结构中保存了B-Tree索引和数据行。
覆盖索引：如果一个索引包含所有需要查询的字段的值，叶子节点已经包含了要查询的数据，不需要回表查询。

索引的优点

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

其他

• 对于特别大的表，索引的代价比较大，可考虑分区技术
• 如果表的数量特别多，可以建立元数据信息表
• 对于TB级别的数据，定位单条记录的意义不大，经常使用块级别的元数据技术代替索引

---

索引策略

独立的列：索引列不能是表达式的一部分，也不能是函数的参数
前缀索引：索引很长的字符列可以考虑索引列开始的部分字符
多列索引：同时对多个单列索引，索引合并时考虑优化成其他方式（union）
索引顺序（B-Tree）：综合考虑排序、分组和范围条件

• 延迟关联 在子句中使用覆盖索引，在根据条件匹配记录。
• 压缩（前缀压缩）索引 MyISAM使用前缀压缩索引来减少索引的大小。
• 冗余索引和重复索引 唯一限制和主键限制都是通过索引实现的；尽量扩展已有的索引而不是创建新的索引
• 避免使用多个范围条件，使用多个等值条件查询代替。
• 维护索引和表 ，修复已损坏的表 CHECK TABLE
• 减少索引和数据的碎片 OPTIMIZE TABLE(或者ALTER TABLE 不做任何操作)
• 常见替代策略：反范式 、提前计算汇总表