任何能够加速搜索的数据结构都可以用来做索引模型,常见的就是哈希索引,有序列表索引和多叉树索引。
- 哈希索引的特点是插入删除方便,查找更快,缺点是只能进行等值查询,不能查找范围。
- 有序索引可以使用二分查找,同时也支持范围查找,但是插入和删除效率低,适合静态数据。
- 多叉树索引索引支持范围查找,平衡了哈希索引和有有序列表索引。
InnoDB使用B+ 树索引模型,是一种平衡多叉树。
TIPS:
如果想在一个表上支持哈希索引,可以将一个或者几个字段拼接取哈希值,单独作为一列,并建立索引。在查询的时候将这列放在查询条件最前边,之后判断其余字段。最好在表上设置一个触发器,这样每次插入或者修改字段会自动计算这个哈希值。
主键索引和非主键索引
- 主键索引的叶子节点存的是整行数据。在 InnoDB 里,主键索引也被称为聚簇索引。
- 非主键索引的叶子节点内容是主键的值。在InnoDB里,非主键索引也被称为二级索引。
对于非主键索引的查询,只能查到主键值,然后再通过主键索引查到这行数据,这个过程称为回表。因此,在应用中应该尽量使用主键查询。
索引的优化
主键的长度越小,普通索引的叶子节点使用的存储空间就越小,普通索引使用的总空间就越小。因为可能有很多个普通索引,所以使用短的主键是比较好的选择,一般使用自增主键。
适合使用业务字段做主键的场景(KV场景):
- 只有一个索引
- 该索引必须是唯一索引
为什么要重建索引?
索引可能会因为删除,或者页分裂等原因导致效率下降,重建索引可以把数据按顺序插入,使得页面利用率最高,并节省空间。
不论是删除主键还是创建主键,都会将整个表重建。
上文说了,在非主键上查询结果需要回表,进行二次查询。对于回表过程的一个优化就是建立覆盖索引。
覆盖索引
要查询的值已经在索引树上,不需要再次查询主键索引树获取数据。这种索引就叫覆盖索引。
InnoDB引擎使用B+树作为索引数据结构,B+树有一个最左前缀匹配原则。根据最左前缀匹配原则,建立合理的联合索引,可以优化数据库的查找速度和存储大小。
建立联合索引的原则:
- 如果通过调整顺序,可以减少维护一个索引,那么这个顺序往往是要优先考虑的。
- 如果维护的索引数不变,考虑存储空间少的方案,比如name和age建立联合索引,可以建立(name, age)和age两个索引
索引下推
mysql> select * from tuser where name like '张 %' and age=10 and ismale=1;
如果有(name,age)索引,那么age的信息已经在索引树上,可以先根据age条件筛选一遍,然后再回表查询其他条件。
索引下推优化在MySQL 5.6 版本以后才有,并且也不是所有可以下推的查询都会使用下推
查询语句中的WHERE条件顺序不重要,优化器会自动优化。