InnoDB存储引擎下快速创建索引-白红宇

InnoDB存储引擎下快速创建索引

阅读量：6542 次

发布时间：2019-06-24

本文共 5789 字，大约阅读时间需要 19 分钟。

InnoDB存储引擎下快速创建索引

在MySQL5.0版本前，新增或删除索引会很缓慢，特别是比较大的表。它需要创建一个新的表，然后将旧表中的数据插入到新的表中，更新索引，待这一切完成后再将旧表删掉并把新表的表名改成原来的表名。

从MySQL5.1版本开始，MySQL允许创建或删除索引不必拷贝整个表内容。在MySQL内置版本的InnoDB存储引擎并没有这种性能；InnoDB Plugin版本可以实现。

在InnoDB中，表中行存储在聚集索引或主键索引，称之为索引组织表。不管是内置版本的InnoDB存储引擎还是InnoDB Plugin存储引擎，更改聚集索引或主键都需要复制数据。但是添加或删除InnoDB Plugin辅助索引的速度要快得多，因为它不涉及复制数据。

Built-in InnoDB版本下：

mysql> CREATE TABLE T1(A INT PRIMARY KEY, B INT, C CHAR(1)) ENGINE=InnoDB;

mysql> set profiling = 1;

mysql> ALTER TABLE T1 ADD INDEX (B), ADD UNIQUE INDEX (C);

mysql> show profile cpu for query 8;

+----------------------+----------+----------+------------+

| Status | Duration | CPU_user | CPU_system |

+----------------------+----------+----------+------------+

| starting | 0.000044 | 0.000000 | 0.000000 |

| checking permissions | 0.000002 | 0.000000 | 0.000000 |

| checking permissions | 0.000003 | 0.000000 | 0.000000 |

| init | 0.000035 | 0.000000 | 0.000000 |

| Opening tables | 0.000056 | 0.000000 | 0.000000 |

| System lock | 0.000003 | 0.000000 | 0.000000 |

| Table lock | 0.000003 | 0.000000 | 0.000000 |

| setup | 0.000018 | 0.000000 | 0.000000 |

| creating table | 0.003217 | 0.000999 | 0.000000 |

| After create | 0.000047 | 0.000000 | 0.000000 |

| copy to tmp table | 0.000034 | 0.000000 | 0.000000 |

| rename result table | 0.001110 | 0.000000 | 0.000000 |

| end | 0.000038 | 0.000000 | 0.000000 |

| query end | 0.000004 | 0.000000 | 0.000000 |

| freeing items | 0.000127 | 0.000000 | 0.000000 |

| cleaning up | 0.000004 | 0.000000 | 0.000000 |

+----------------------+----------+----------+------------+

删除索引：

mysql> alter table T1 drop PRIMARY KEY;

mysql> show profile cpu for query 11;

+----------------------+----------+----------+------------+

| Status | Duration | CPU_user | CPU_system |

+----------------------+----------+----------+------------+

| starting | 0.000038 | 0.000000 | 0.000000 |

| checking permissions | 0.000002 | 0.000000 | 0.000000 |

| checking permissions | 0.000003 | 0.000000 | 0.000000 |

| init | 0.000034 | 0.000000 | 0.000000 |

| Opening tables | 0.000010 | 0.000000 | 0.000000 |

| System lock | 0.000003 | 0.000000 | 0.000000 |

| Table lock | 0.000004 | 0.000000 | 0.000000 |

| setup | 0.000015 | 0.000000 | 0.000000 |

| creating table | 0.003219 | 0.000000 | 0.001000 |

| After create | 0.000039 | 0.000000 | 0.000000 |

| copy to tmp table | 0.000029 | 0.000000 | 0.000000 |

| rename result table | 0.001346 | 0.001000 | 0.000000 |

| end | 0.000038 | 0.000000 | 0.000000 |

| query end | 0.000003 | 0.000000 | 0.000000 |

| freeing items | 0.000134 | 0.000000 | 0.000000 |

| cleaning up | 0.000003 | 0.000000 | 0.000000 |

+----------------------+----------+----------+------------+

InnoDB Plugin版本下：

mysql> show variables like "innodb_version";

+----------------+-------+

| Variable_name | Value |

+----------------+-------+

| innodb_version | 1.1.4 |

+----------------+-------+

mysql> CREATE TABLE T1(A INT PRIMARY KEY, B INT, C CHAR(1)) ENGINE=InnoDB;

mysql> set profiling = 1;

mysql> ALTER TABLE T1 ADD INDEX (B), ADD UNIQUE INDEX (C);

mysql> show profile cpu for query 16;

+------------------------------+----------+----------+------------+

| Status | Duration | CPU_user | CPU_system |

+------------------------------+----------+----------+------------+

| starting | 0.000048 | 0.000000 | 0.000000 |

| checking permissions | 0.000004 | 0.000000 | 0.000000 |

| checking permissions | 0.000003 | 0.000000 | 0.000000 |

| init | 0.000007 | 0.000000 | 0.000000 |

| Opening tables | 0.000095 | 0.000000 | 0.000000 |

| System lock | 0.000006 | 0.000000 | 0.000000 |

| setup | 0.000021 | 0.000000 | 0.000000 |

| creating table | 0.001577 | 0.000000 | 0.000000 |

| After create | 0.000026 | 0.000000 | 0.000000 |

| manage keys | 0.084208 | 0.003000 | 0.010998 |

| rename result table | 0.000158 | 0.000000 | 0.000000 |

| end | 0.000017 | 0.000000 | 0.000000 |

| Waiting for query cache lock | 0.000002 | 0.000000 | 0.000000 |

| end | 0.000005 | 0.000000 | 0.000000 |

| query end | 0.000003 | 0.000000 | 0.000000 |

| closing tables | 0.000007 | 0.000000 | 0.000000 |

| freeing items | 0.000013 | 0.000000 | 0.000000 |

| cleaning up | 0.000002 | 0.000000 | 0.000000 |

+------------------------------+----------+----------+------------+

重构聚集索引或主键索引时，InnoDB需要拷贝数据。如下：

mysql> CREATE TABLE T2 (A INT, B INT) ENGINE=InnoDB;

mysql> set profiling = 1;

mysql> ALTER TABLE T2 ADD PRIMARY KEY (B);

mysql> show profile cpu for query 1;

+------------------------------+----------+----------+------------+

| Status | Duration | CPU_user | CPU_system |

+------------------------------+----------+----------+------------+

| starting | 0.000046 | 0.000000 | 0.000000 |

| checking permissions | 0.000004 | 0.000000 | 0.000000 |

| checking permissions | 0.000004 | 0.000000 | 0.000000 |

| init | 0.000006 | 0.000000 | 0.000000 |

| Opening tables | 0.000109 | 0.001000 | 0.000000 |

| System lock | 0.000006 | 0.000000 | 0.000000 |

| setup | 0.000016 | 0.000000 | 0.000000 |

| creating table | 0.016539 | 0.000000 | 0.001000 |

| After create | 0.000059 | 0.000000 | 0.000000 |

| copy to tmp table | 0.000040 | 0.000000 | 0.000000 |

| rename result table | 0.036043 | 0.000999 | 0.001000 |

| end | 0.000042 | 0.000000 | 0.000000 |

| Waiting for query cache lock | 0.000002 | 0.000000 | 0.000000 |

| end | 0.000008 | 0.000000 | 0.000000 |

| query end | 0.000003 | 0.000000 | 0.000000 |

| closing tables | 0.000008 | 0.000000 | 0.000000 |

| freeing items | 0.000022 | 0.000000 | 0.000000 |

| cleaning up | 0.000003 | 0.000000 | 0.000000 |

+------------------------------+----------+----------+------------+

在创建UNIQUE或PRIMARY KEY索引时，Innodb还需要做一些额外的工作。对于UNIQUE索引，InnoDB需要检查表不包含重复的键值。对于PRIMARY KEY索引，InnoDB需要检查主键列有没包含NULL。因此最好在创建表的同时，创建主键。

注意：如何添加或删除REFERENCES外键约束，子表还是需要复制数据的。

转载于:https://blog.51cto.com/who0168/530068

你可能感兴趣的文章

说清楚讲明白vxlan在openstack中的使用场景

查看>>

RHCE 学习笔记（36) - MariaDB

查看>>

我的友情链接

查看>>

my-innodb-heavy-4G.cnf

Java进行HTML数据采集:浅谈强大的group正则

构建插件式的应用程序框架(八)－－－－视图服务的简单实现

查看>>

How to disable vertical scrolling in UIScrollView (Obj-C)

查看>>

《LAMP+FastCGI+XCache》强强结合，性能不在是问题

asp.net调用前台js调用后台代码分享

查看>>

Nebula-orchestartor，广域网容器资源协调器

what is the mean of thread safe ?线程安全是什么意思呢？

查看>>