极客时间MySQL实战45讲笔记四. 索引实现

最新推荐文章于 2022-07-01 22:56:57 发布

chengyu4856

最新推荐文章于 2022-07-01 22:56:57 发布

阅读量179

点赞数

文章标签：数据库 memcached 数据结构与算法

原文链接：https://my.oschina.net/u/575836/blog/3023341

版权

1. 索引常见模型

常见的索引数据结构包括：哈希表、有序数组以及树。

1.1 哈希表

Hash表是一种以键-值存储数据的结构。hash的底层是一个数组，通过一个哈希函数把key换算成一个确定位置，然后把value放在数组的这个位置。

多个key值的情况下，会出现位置重复的情况。处理这种情况的一种方式是使用链表或者树。

hash表结构索引适应只有等值查询的场景，比如Memcached及其他NoSQL引擎。

1.2 有序数组

**有序数组索引适应与等值查询和范文查询。**但其更新成本太高。所以有序数组索可以加在一些更新不是很频繁的表中。

1.3 二叉树

平衡二叉树查询复杂的和更新复杂度都为O(log(N))。同时N叉树又适配机械磁盘的访问模式，所以广泛使用。

二叉作为是数据存储结构，会导致树高太高，不适合磁盘的读写模式。所以使用N叉树

InnoDB的一个整数索引的N差不多为1200.

2. InnoDB的索引模型

2.1 索引模型

索引组织表：InnoDB中，表根据主键顺序以索引的形式存放。

每一个索引对应一棵B+树

索引类型分为：主键索引和非主键索引。主键索引在InnoDB中也成为聚簇索引。主键索引的叶子节点存储的是整行数据。非主键索引存储的主键的值。

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

基于主键索引查询方式时，只需要扫描主键的B+索引树
基于普通索引查询时，需要先扫描普通索引的B+索引树并得到对应的主键值，再到主键索引树上再搜索一次。这个过程称为回表。即：非主键索引的查询需要多扫描一课索引树。

没有主键的表，InnoDB会默认创建一个Rowid作为主键

2.2 索引的维护

页分裂：如果新插入的数据所在的数据页已经满了，则需要申请一个新的数据页，并挪动部分数据过去。页分裂操作会影响数据页的利用效率

如果相邻的数据页，删除了部分数据，利用率下降，则会将数据页进行合并。

自增主键索引，因为自增，所以都是追加操作，不需要挪动索引树的其他积累，也不会出发叶子节点的分裂。

业务逻辑做主键，不容以保证有序的插入，有可能触发逻辑移动主键索引树以及页分裂。

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

适合用业务字段直接做主键的场景

只有一个索引
该索引必须是唯一索引

3. InnoDB索引特性

3.1. 覆盖索引

在使用普通索引进行数据查询时，如果普通索引上的主键值已经达到我们的查询要求，则不会进行回表操作，称之为"覆盖索引"

覆盖索引可以减少树的搜索次数，显著提升查询性能，所以是一个常用的性能优化手段。

假设如果有以下建表语句

mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;
insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

使用以下两种SQL语句执行的树搜索的区别：

select * from T where k betweent 3 and 5;
select ID from T where k betweent 3 and 5;

语句1中要求的数据必须通过主键索引查询得到，所以不得不回表。

语句2中的ID值在k的索引树上可以直接得到，索引不需要回表，从而避免回表。即：覆盖索引。

3.2 最左侧原则

B+树这种结构可以利用索引的"最左前缀"来定位记录。即：索引项是按照索引定义里面的出现的字段顺序排序的。

最左侧前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。

示例：

有如下建表语句：

CREATE TABLE `test`.`Untitled`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `b` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `c` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `index_a_b`(`a`, `b`) USING BTREE
) ENGINE = InnoDB;

在a、b字段上存在联合索引(a,b),则根据最左侧原则，以下sql会使用到该索引：

EXPLAIN SELECT * from t where a = 'aa';  -- 走索引
EXPLAIN SELECT * from t where a = 'aa' and b = 'bb'; -- 走索引
EXPLAIN SELECT * from t where b = 'bb' and a = 'aa'; -- 走索引

以下SQL不会走索引。

EXPLAIN SELECT * from t where b = 'bb';  -- 不走索引

执行如下：

在创建联合索引的时候，如何安排索引内的字段顺：

如果通过调整顺序，可以减少维护一个索引，那么这个顺序就是游戏考虑采用
考虑空间。比如:有name和age两个字段，因为name字段比age字段占用空间多，所以推荐创建(name,age)的联合索引和一个(age)索引。

3.3 索引下推

MySQL 5.6 引入索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

示例：

有以下SQL执行：

mysql> select * from tuser where name like '张 %' and age=10 and ismale=1;

其中表tuser上建立了（name，age）联合索引。那么在MySQL 5.6之前执行的过程：

根据最左侧前缀索引规则，使用联合索引找到“张”
之后再根据age字段进行挨个回表判断操作是否符合条件。执行如图:

在MySQL 5.6 之后，则使用索引的过程中，先做判断，过滤掉不符合条件的记录。如下图：

4. 思考问题

重建主键索引，可以使用语句以下语句，可以达到减少空间多目的。

alert table T engine = InnoDB

转载于:https://my.oschina.net/u/575836/blog/3023341