索引的类型
B-Tree索引
- InnoDB中使用的是B+Tree,每一个叶子节点都包含指向下一个叶子节点的指针,从而方便叶子节点的范围遍历。InnoDB中根据主键引用被索引的行。
- MyISAM索引通过数据的物理位置引用被索引的行。
索引匹配
- 全值匹配
- 匹配最左前缀 只使用索引的第一列
- 匹配列前缀 值匹配某一列的值开头的部分
- 匹配范围值
- 精确匹配某一列并范围匹配另外一列
- 只访问索引的查询(索引覆盖)
哈希索引
- 只有精确匹配索引所有列的查询菜有效
- Memory引擎支持(非唯一哈希索引)
- 哈希索引只包含哈希值和行指针
- 无法用于排序
- 哈希冲突
空间数据索引 R-Tree
- MyISAM支持空间索引,可以用于地理数据存储。
全文索引
- 查找的是文本中的关键字
- 适用MATCH AGAINST操作而不是普通的WHERE操作
聚簇索引是一种数据存储方式,InnoDB的聚簇索引实际上在同一结构中保存了B-Tree索引和数据行。
覆盖索引:如果一个索引包含所有需要查询的字段的值,叶子节点已经包含了要查询的数据,不需要回表查询。
索引的优点
- 索引大大减少了服务器需要扫描的数据量
- 索引可以帮助服务器避免排序和临时表
- 索引可以将随机IO变为顺序IO
其他
- 对于特别大的表,索引的代价比较大,可考虑分区技术
- 如果表的数量特别多,可以建立元数据信息表
- 对于TB级别的数据,定位单条记录的意义不大,经常使用块级别的元数据技术代替索引
索引策略
独立的列:索引列不能是表达式的一部分,也不能是函数的参数
前缀索引:索引很长的字符列可以考虑索引列开始的部分字符
多列索引:同时对多个单列索引,索引合并时考虑优化成其他方式(union)
索引顺序(B-Tree):综合考虑排序、分组和范围条件
- 延迟关联 在子句中使用覆盖索引,在根据条件匹配记录。
- 压缩(前缀压缩)索引 MyISAM使用前缀压缩索引来减少索引的大小。
- 冗余索引和重复索引 唯一限制和主键限制都是通过索引实现的;尽量扩展已有的索引而不是创建新的索引
- 避免使用多个范围条件,使用多个等值条件查询代替。
- 维护索引和表 ,修复已损坏的表 CHECK TABLE
- 减少索引和数据的碎片 OPTIMIZE TABLE(或者ALTER TABLE 不做任何操作)
- 常见替代策略:反范式 、提前计算汇总表