1. 1. 索引常见模型
  2. 2. InnoDB索引模型
  3. 3. 主键索引和普通索引的查询区别
  4. 4. 索引维护
  5. 5. 覆盖索引
  6. 6. 最左前缀原则
  7. 7. 索引下推(Mysql5.6之后)

索引常见模型

  • 哈希表
    • 适用于只有等值查询的场景
    • 哈希冲突的处理办法：链表
  • 有序数组
    • 在等值查询和范围查询场景中性能都非常优秀
    • 只适用于静态存储引擎
    • 在更新数据时，需要挪动后面所有的记录，成本高
  • 搜索树

InnoDB索引模型

在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表

每一个索引在InnoDB里面对应一棵B+树

主键索引的叶子节点存的整行数据，主键索引也称为聚簇索引(clustered index)

非主键索引的叶子节点内容是主键的值，非主键索引也被称为二级索引(secondary index)

主键索引和普通索引的查询区别

  • 基于非主键索引的查询需要多扫描一棵索引数，扫描主键索引
  • 回到主键索引树搜索的过程，我们称为回表

索引维护

在插入数据时，索引的数据也会变化

如果插入的数据对应的数据页已经满了，根据B+树的算法，需要申请一个新的数据页，然后挪动部分数据到新的数据页中，该过程称为页分裂

页分裂影响性能而且也影响数据页的利用率

主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也越小

从性能和存储空间考虑，自增主键比没有使用自增主键合理

覆盖索引

select id from table where k between 3 and 5;
索引K 已经“覆盖了”我们查询需求，我们称之为覆盖索引
由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引为一个常用的性能优化手段

实例：

1
2
3
4
5
6
7
8
9
10
CREATE TABLE `tuser` (
    `id` int(11) NOT NULL,
    `id_card` varchar(32) DEFAULT NULL,
    `name` varchar(32) DEFAULT NULL,
    `age` int(11) DEFAULT NULL,
    `ismale` tinyint(1) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `id_card` (`id_card`),
    KEY `name_age` (`name`,`age`)
    ) ENGINE=InnoDB;

如果现在有一个高频请求，要根据市民的身份证号查询他的姓名和年龄，这个联合索引就有意义了。它可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间

最左前缀原则

  • B+树这种索引结构，可利用索引的“最左前缀”，来定位记录
  • 最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符
  • 最左前缀评估标准，索引的复用能力

建立联合索引时，如果安排索引内的字段顺序？

  • 如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的
  • 在联合索引时，建立单个索引考虑的原则为空间

索引下推(Mysql5.6之后)

在索引遍历的过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数
name like “zhang%” and age>10,会对匹配的数据进行回表查询，如果有索引下推机制，那么会先过滤掉age<10的数据，在执行回表查询，提高检索速度

alter table T engine=InnoDB 是用来释放 delete 操作引起的页的空洞,也就是碎片空间 操作时候尽量避免当前表的dml 操作.
表数据很大情况 建议使用 Percona Toolkit 工具来执行