Mysql: 索引（4-5）

文章内容仅做记录，参考Mysql45讲。

索引的目的：为了提高数据查询的效率。
实现：索引是在存储引擎层实现的。不同引擎的索引工作方式不同。

1、索引

1.1、常见的索引结构

• 哈希表：适用于等值查询，不适合范围查询。
• 有序数组：适用于静态存储引擎（适用于等值和范围查询），但更新成本比较高。
• 二叉搜索树：为了保证查询的效率是O(log(N))，需要保证二叉搜索树是平衡的，更新的成本也是O(log(N))。
  • 但是实际上大多数的数据库存储却并不使用二叉树。
    • 其原因是，索引不止存在内存中，还要写到磁盘上。相同的数据量，二叉树比N叉树的高度更高，而树的高度决定了访问磁盘的次数。
  • 4层高度的N叉树，存储N的（h-1）次幂个节点。
  • 树根的数据块总是在内存中。
  • N叉树的N值取决于数据块的大小。（数据块存储的应该是指针+索引列的类型对应的值）存疑？
• 数据库底层存储的核心依赖这些数据模型。

1.2、Innodb索引

• 1、索引组织表：
  • 主键索引（聚簇索引）：叶子结点存储的是行数据。
  • 非主键索引（二级索引）：叶子结点存储的是主键id。
• 2、索引维护：
  • 页的分裂（要插入数据的页已经满了）和页的合并（删除数据之后页利用率很低时会合并）会影响性能。
  • 建议新建的表有自增主键：
    • 从性能角度来看：id是自增的，则会顺序存储，避免页分裂的性能损耗（业务属性的主键很难保证有序写入，需要维护B+树的平衡和页分裂）。
    • 从存储空间来看：自增主键int(4字节)、long(8字节)，可能比其他字段小一些。主键长度越小，普通索引的叶子结点越小，占用空间越少。

1.3、思考题

• 1、
  alter table T drop index k; alter table T add index(k);
alter table T drop primary key; alter table T add primary key(id);
  • 首先了解为什么要重建索引？
    • 可能由于删除和页分裂等原因，导致索引的数据页有空洞。重建索引，页面利用率得到提升。
  • 用以上两个sql删除索引、主键，再重建索引、主键有什么问题？
    • 重建辅助索引的sql没有问题。
    • 但是删除主键，重建主键的sql不合理。不论删除主键还是重建主键，都会重建表。所以连着执行这两个语句的话，第一个语句就白做了。这两个语句，你可以用这个语句代替 ： alter table T engine=InnoDB。

2、索引的使用

2.1、索引的概念

• 1、覆盖索引：假如一个索引能包含到所要查询的全部字段，这个索引就是覆盖索引。
• 2、联合索引：
  • 联合索引的字段顺序如何安排？基于索引的复用能力决定字段顺序。
  • 假如通过索引顺序的调整能减少索引的维护，那么这个顺序是可以优先考虑的。
  • 另一个参考的原则：考虑空间。（a，b）和（b）两个索引，若a的空间占用比b小，可以考虑换成（b，a）和（a）。
• 3、索引下推：
  • MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。例如，对于联合索引（a，b），查询条件是 a like ‘张%’ and b = 10 and d= 1，索引下推即会用到联合索引中的b字段，根据b先过滤一批数据再回表取d判断是否符合条件。

2.2、思考题

• 1、表T的索引： (a,b)联合索引作为主键索引。 ( c ) 、(c,a)、 (c,b)为辅助索引。索引设计的合理吗？
  • c索引树的排序是cab，ca联合索引的索引树排序也是cab（只包含主键索引中的b，a已经排过序了，没必要用caab，重复的a多占用内存），所以ca联合索引可以去掉。cb联合索引的索引树排序为cba。