比如说有这个索引 key(sex, age),当查询性别男,生日为某一天的时候,匹配到索引的时候就直接返回结果就行了 ,不需要再进行磁盘 IO 来拿出这一整列。
而且因为索引是按照列值顺序存储的,所以对于 I/O 密集的范围查询,会比随机从磁盘读取每一行数据的 I/O 少得多。
#5.6 使索引与排序顺序一致MySql 有两种方式来生成排序,一种是直接利用索引扫描顺序作为排序,一种是通过排序操作。当 EXPLAIN 出来的 type 的值为 index 时说明使用了索引进行排序。
设计索引的时候要尽量使得索引的列顺序和 ORDER BY 顺序一致。
只有当索引的顺序和 ORDER BY 的子句一致,并且所有的列排序方向都一样的时候,MYSQL 才能用索引来对结果进行排序。如果需要不同的方向进行排序,一个好的方法就是在保存的时候翻转字符串或者保存相反数。当需要关联查询多个表的时候,只有 ORDER BY 子句引用的字段全为第一个表的时候,才能用索引做排序。
索引在 Order By 中的使用限制也和 SELECT 中的限制一样
#6 索引和锁InnoDB 访问行的时候才会锁定这行,索引可以让查询访问更少行,从而锁定更少行。
然而索引是按照顺序自增的时候,并发插入会导致行锁的竞争。
在旧版中,需要提交事务才能释放行锁,在 MySql 5. 1 后的版本,InnoDB 可以在服务端过滤掉行后就释放锁。
#7 清除冗余和重复索引MySql 允许重复索引的存在,并且需要单独维护重复的索引。有时候我们需要查看未使用到的索引。
可以用 Percona Toolkit 来查看索引情况和查询索引的日志来找出重复和多余的索引。
接下来讨论一下冗余索引,冗余索引一般是增加索引(A,B)的时候,而不是去扩展已有的索引(A)。
大多数情况下都不需要冗余索引。除非当需要额外增加一个非常长的 VARCHAR 索引的时候,增加这个列会导致性能急剧下降,这时候就需要冗余索引。
#8 一些其他的索引讨论顺带一提,如果有特殊需要,可以使用其他的索引。
R-Tree:可以用任意维度进行索引,MySql 在这方面做得不好,可以考虑使用 PostgreSQL
全文索引:用于搜索,一般可以将整张表导入 elasticsearch 中。
#8.1 哈希索引在 MySql 中,只有 Memory 引擎支持显式哈希索引,它是支持非唯一哈希索引的。也就是说类似于 Java 中的 HashMap(JDK 1.8 前),散列到同一个位置的列会被保存在链表中。
如果数据库不支持哈希索引,可以在 B-Tree 中创建一个伪哈希索引,比如需要保存大量的 URL,可以在 WHERE 语句中利用特定的方法来把 URL 散列开来,可以借助 MySql 插件来做到这一点。这样的缺点就是需要使用触发器在列表更新的时候更改哈希值。