临时表建索引 临时表可以创建索引吗

文章目录

• 1 为什么要使用索引
• 2 为表字段添加索引
• 3 索引的常见模型

• 3.1 哈希表
• 3.2 有序数组
• 3.3 二叉搜索树

• 4 InnoDB的索引模型
• 5 索引维护
• 6 覆盖索引
• 7 最左前缀原则
• 8 索引下推
• 9 为什么索引能提高查询速度

• 9.1 MySQL的基本存储结构
• 9.2 使用索引之后

• 参考

1 为什么要使用索引

1. 可以大大加快 数据的检索速度（大大减少的检索的数据量）, 这也是创建索引的最主要的原因。
2. 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
3. 帮助服务器避免进行排序和分组，以及避免创建临时表（B+Tree 索引是有序的，可以用于 ORDER BY 和 GROUP BY 操作。临时表主要是在排序和分组过程中创建，因为不需要排序和分组，也就不需要创建临时表）。
4. 将随机 I/O 变为顺序 I/O（B+Tree 索引是有序的，会将相邻的数据都存储在一起）。

2 为表字段添加索引

1.添加PRIMARY KEY（主键索引）

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

2.添加UNIQUE(唯一索引)

ALTER TABLE `table_name` ADD UNIQUE ( `column` )

3.添加INDEX(普通索引)

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4.添加FULLTEXT(全文索引)

ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

5.添加多列索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

3 索引的常见模型

可以用于提高读写效率的数据结构很多，这里主要介绍三种常见的数据结构，它们分别是哈希表、有序数组和搜索树。

3.1 哈希表

哈希表是一种以键-值（key-value）存储数据的结构，我们只要输入待查找的值即key，就可以找到其对应的值即Value。哈希的思路很简单，把值放在数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置。

不可避免地，多个key值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的一种方法是，拉出一个链表。

哈希索引做区间查询的速度挺慢的。例如，在一个记录身份证信息和姓名的表中，需要根据身份证号查找对应的名字。由于不同的身份号码通过哈希运算后值基本不相同且不是有序的，所以要查询要找身份证号在[ID_card_X, ID_card_Y]这个区间的所有用户，就必须全部扫描一遍，效率很低。

所以，哈希表这种结构适用于只有等值查询的场景，适用于Memcached及其他一些NoSQL引擎。

3.2 有序数组

有序数组在等值查询和范围查询场景中的性能就都非常优秀。

还是上面这个根据身份证号查名字的例子，假设身份证号没有重复，这个数组就是按照身份证号递增的顺序保存的。这时候如果查ID_card_N对应的名字，用二分法就可以快速得到，时间复杂度是O(log(N))。

如果仅仅看查询效率，有序数组很完美。但是，在需要更新数据的时，每在中间插入一个记录就必须挪动后面所有的记录，成本太高。

所以，有序数组索引只适用于静态存储引擎，比如要保存的是2008年某个城市的所有人口信息（这类数据后续不会再被修改）。

3.3 二叉搜索树

二叉搜索树是经典的数据结构。二叉搜索树的特点是：每个父节点都有两个子节点（子节点可能为空），每个左子节点都比父节点小，每个右子节点比父节点大。

在上图中，如果要查ID_card_n2的话，按照图中的搜索顺序就是按照UserA -> UserC -> UserF -> User2路径查得，时间复杂度是O(log(N))。

为了维持O(log(N))的查询复杂度，就需要保持这棵树是平衡二叉树。为了做这个保证，更新的时间复杂度也是O(log(N))。

二叉搜索树搜索效率挺高，但是实际上大多数的数据库存储却并不使用，原因在于数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘页（对应索引树的节点）。所以我们要减少IO次数，对于树来说，IO次数就是树的高度。

也就是说，为了让一个查询尽量少地读磁盘，就必须让树“矮下来”。那么，就得使用“N叉”树了。这里，“N叉”树中的“N”取决于数据块的大小。

以InnoDB的一个整数字段索引为例，这个N差不多是1200。这棵树高是4的时候，就可以存1200的3次方个值，这已经17亿了。考虑到树根的数据块总是在内存中的，一个10亿行的表上一个整数字段的索引，查找一个值最多只需要访问3次磁盘。

4 InnoDB的索引模型

在MySQL中，索引是在存储引擎层实现的，并没有统一的索引标准，即不同存储引擎的索引的工作方式并不一样。而即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。

在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。InnoDB使用了B+树索引模型，数据都是存储在B+树中的。每一个索引在InnoDB里面对应一棵B+树。

假设，我们有一个主键列为ID的表，表中有字段k，并且在k上有索引。

mysql> create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;

表中R1~R5的(ID,k)值分别为(100,1)、(200,2)、(300,3)、(500,5)和(600,6)，两棵树的示例示意图如下。

根据叶子节点的内容，索引类型分为主键索引和非主键索引。

主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也被称为聚簇索引（clustered index）。

非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引（secondary index）。

基于主键索引和普通索引的查询有什么区别？

• 如果语句是select * from T where ID=500，即主键查询方式，则只需要搜索ID这棵B+树；
• 如果语句是select * from T where k=5，即普通索引查询方式，则需要先搜索k索引树，得到ID的值为500，再到ID索引树搜索一次。这个过程称为回表。（回到主键索引树搜索的过程，称为回表）

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，在实际应用中尽量使用主键查询。

5 索引维护

B+树为了维护索引有序性，在插入新值的时候需要做必要的维护。

如果数据库表中最后ID为699，新插入的行为700，则只需要按照索引顺序尾部添加一个新记录，如果添加ID为400的记录，就比较麻烦了，需要逻辑上挪动后面的数据，空出位置。

如果当前索引页面满了，根据B+树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响。当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。

基于上面的索引维护过程说明，下面来讨论建表时，在哪些场景下应该使用自增主键，而哪些场景下不应该使用。

自增主键是指自增列上定义的主键，在建表语句中一般是这么定义的：NOT NULL PRIMARY KEY AUTO_INCREMENT。插入新记录的时候可以不指定ID的值，系统会获取当前ID最大值加1作为下一条记录的ID值。

也就是说，自增主键的插入数据模式，正符合了我们前面提到的递增插入的场景。每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。而有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。

比如表中确实有一个唯一字段，比如字符串类型的身份证号，那应该用身份证号做主键，还是用自增字段做主键呢？

由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键，那么每个二级索引的叶子节点占用约20个字节，而如果用整型做主键，则只要4个字节，如果是长整型（bigint）则是8个字节。

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

所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。

有没有什么场景适合用业务字段直接做主键的呢？还是有的。比如，有些业务的场景需求是这样的：

1. 只有一个索引；
2. 该索引必须是唯一索引。

典型的KV场景。由于没有其他索引，所以也就不用考虑其他索引的叶子节点大小的问题。

这时候就要优先考虑“尽量使用主键查询”原则，直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树。

6 覆盖索引

还是以上面例子为例，如果执行查询sql语句是select * from T where k between 3 and 5，那么sql查询过程中会经历3次回表，效率很低。而将执行语句修改为select ID from T where k between 3 and 5，这时只需要查ID的值，而ID的值已经在k索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。

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

7 最左前缀原则

如果为每一种查询都设计一个索引，索引就太多了。假如有一个表记录了市民的身份证号，年龄，姓名，家庭住址等信息，如果按照市民的身份证号去查家庭地址肯定不能全表扫描，效率太低；反过来说，单独为一个不频繁的请求创建一个（身份证号，地址）的索引又感觉有点浪费。应该怎么做呢？

B+树这种索引结构，可以利用索引的“最左前缀”，来定位记录。

假如有一个市民表（user）的联合索引（name, age）:

最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。如下：

select * from user where name=xx and age=xx;  // 可以命中索引
select * from user where name=xx; 			  // 可以命中索引
select * from user where name like '张%'; 	 // 可以命中索引
select * from user where age=xx;			  // 无法命中索引

不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。

在建立联合索引的时候，如何安排索引内的字段顺序呢？

这里我们的评估标准是，索引的复用能力。因为可以支持最左前缀，所以当已经有了(name,age)这个联合索引后，单独的(age)索引就是一个冗余索引了。因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

8 索引下推

还是以市民表的联合索引（name, age）为例。如果现在有一个需求：检索出表中“名字第一个字是张，而且年龄是10岁的所有男孩”。那么，SQL语句是这么写的：

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

你已经知道了前缀索引规则，所以这个语句在搜索索引树的时候，只能用 “张”，找到第一个满足条件的记录ID4。

然后是判断其他条件是否满足。

在MySQL 5.6之前，只能从ID4开始一个个回表。到主键索引上找出数据行，再对比字段值。

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

如果没有索引下推的话，执行流程如下：

索引下推执行流程：

每一个虚线箭头表示回表一次。

第一个图中，在(name,age)索引里面去掉了age的值，这个过程InnoDB并不会去看age的值，只是按顺序把“name第一个字是’张’”的记录一条条取出来回表。因此，需要回表4次。

第二图中，InnoDB在(name,age)索引内部就判断了age是否等于10，对于不等于10的记录，直接判断并跳过。在这个例子中，只需要对ID4、ID5这两条记录回表取数据判断，就只需要回表2次。

9 为什么索引能提高查询速度

9.1 MySQL的基本存储结构

MySQL的基本存储结构是页(记录都存在页里边)：

• 各个数据页可以组成一个双向链表
• 每个数据页中的记录又可以组成一个单向链表

• 每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录
• 以其他列(非主键)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。

所以说，如果写select * from user where indexname = 'xxx'这样没有进行任何优化的sql语句，默认会这样做：

• 定位到记录所在的页：需要遍历双向链表，找到所在的页
• 从所在的页内中查找相应的记录：由于不是根据主键查询，只能遍历所在页的单链表了

很明显，在数据量很大的情况下这样查找会很慢！这样的时间复杂度为O（n）。

9.2 使用索引之后

索引做了些什么可以让我们查询加快速度呢？其实就是将无序的数据变成有序(相对)：

要找到id为8的记录简要步骤：

很明显的是：没有用索引我们是需要遍历双向链表来定位对应的页，现在通过 “目录” 就可以很快地定位到对应的页上了！（二分查找，时间复杂度近似为O(log(N))。

底层数据结构就是B+树，B+树作为树的一种实现，能够让我们很快地查找出对应的记录。