MySQL 索引

  • 索引是让提高数据查询效率的数据结构。(避免全表扫描)
  • 索引的实现方式:哈希索引、B+Tree 树、倒排索引。
  • InnoDB B+Tree 的叶子节点指向主键id或存储数据行;MyISAM 的叶子节点指向数据行的内存地址
  • MySQL 索引的最左前缀原则

索引常见模型

索引常见模型:有序数组、跳表、哈希表、搜索树

有序数组(二分法):

  • 数据顺序存储,支持区间查询
  • 插入数据需要移到数据,扩容需要复制数据,成本大
  • 适合静态数据

哈希索引:

  • 哈希冲突:拉链法、探测法
  • 存储数据没有顺序,不支持区间查询
  • 适合等值查询

B+树:

  • 数据存储存储、叶子节点通过指针相连,支持区间查询
  • B+树的树高较低,只在叶子节点存储数据进一步降低树高,可以减少磁盘访问次数

InnoDB 索引模型

在 InnoDB 中,表是根据主键顺序以B+树索引形式存放的,这种存储方式的表被称为索引组织表(聚簇索引)

根据叶子节点存储内容的不同,InnoDB 的索引分为主键索引和非主键索引(二级索引)。

二级索引涉及回表(MySQL5.6索引下推)和覆盖索引。

数据插入时索引维护

为了维护索引的有序性,数据插入时需要做必要的维护;

顺序插入数据可以避免页分裂(数据页利用率)和数据移动的开销,所以需要尽量保证主键是自增的/有序的(auto_increment

在选择主键时,第二需要考虑的是主键类型要尽量小,这样可以减少二级索引的空间。

如果数据不是顺序插入的,在插入后可以通过重建表优化空间

  • alter table t1 engine=innodb
  • optimize table t1

索引回表优化

索引回表优化:覆盖索引、索引下推

覆盖索引可以减少回表次数

MySQL 5.6 引入索引下推。对于索引 (name, age),以下查询可以找到姓名是张开头的数据,在检查 age 的值,如果不是 10 则不回表,减少回表次数

select * from t where name like '张%' and age = 10

最左前缀原则

最左前缀原则(联合索引的顺序)

  • 字段区分度
  • 索引复用能力
  • 字段大小(如果需创建 ab, b 两个索引,则b字段较小时可以减少空间消耗)
posted @ 2024-10-13 21:56  廖子博  阅读(5)  评论(0编辑  收藏  举报