《MySQL实战45讲》学习笔记——索引

任何能够加速搜索的数据结构都可以用来做索引模型，常见的就是哈希索引，有序列表索引和多叉树索引。

• 哈希索引的特点是插入删除方便，查找更快，缺点是只能进行等值查询，不能查找范围。
• 有序索引可以使用二分查找，同时也支持范围查找，但是插入和删除效率低，适合静态数据。
• 多叉树索引索引支持范围查找，平衡了哈希索引和有有序列表索引。

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条件顺序不重要，优化器会自动优化。