如何建立索引

Question

加入一个表有大量数据(用于线下测试的数据)，需要再一个字段(非主键)上建立索引，是在创建表的时候创建索引比较好还是等数据写入完之后加入数据呢？why?
希望详细点说明原理。

Answer 1

mysql下，7000万条的数据，更新索引会锁表吧，感觉更耗时一些。

Answer 2

测试了一下，建表SQL：
CREATE TABLE `aae` (
  `BH` varchar(6) CHARACTER SET gbk DEFAULT NULL,
  `XM` varchar(8) CHARACTER SET gbk DEFAULT NULL,
  `DW` varchar(6) CHARACTER SET gbk DEFAULT NULL,
  `NY` double DEFAULT NULL,
  `RYLB` varchar(2) CHARACTER SET gbk DEFAULT NULL,
  `DWLB` varchar(2) CHARACTER SET gbk DEFAULT NULL,
  `GDGZ` double DEFAULT NULL,
  `HGZ` double DEFAULT NULL,
  `BT` smallint(6) DEFAULT NULL,
  `LT` double DEFAULT NULL,
  `TGGZ` double DEFAULT NULL,
  `TSJT` smallint(6) DEFAULT NULL,
  `QTBT` double DEFAULT NULL,
  `BF` double DEFAULT NULL,
  `FZ` double DEFAULT NULL,
  `SF` double DEFAULT NULL,
  `DF` double DEFAULT NULL,
  `QF` double DEFAULT NULL,
  `HTBX` double DEFAULT NULL,
  `ZNF` double DEFAULT NULL,
  `LSDK` double DEFAULT NULL,
  `GZSH` double DEFAULT NULL,
  `JYFJ` double DEFAULT NULL,
  `YXDS` smallint(6) DEFAULT NULL,
  `SDS` double DEFAULT NULL,
  `GJJ` double DEFAULT NULL,
  `SYBX` double DEFAULT NULL,
  `YLBX` double DEFAULT NULL,
  `SFGZ` double DEFAULT NULL,
  `id` int(11) NOT NULL DEFAULT '0',
  UNIQUE KEY `BH` (`id`),
  KEY `BH1` (`BH`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8准备89000条记录，用LOAD DATA INIFILE导入，用时00:03:891
CREATE TABLE `aaf` (
  `BH` varchar(6) CHARACTER SET gbk DEFAULT NULL,
  `XM` varchar(8) CHARACTER SET gbk DEFAULT NULL,
  `DW` varchar(6) CHARACTER SET gbk DEFAULT NULL,
  `NY` double DEFAULT NULL,
  `RYLB` varchar(2) CHARACTER SET gbk DEFAULT NULL,
  `DWLB` varchar(2) CHARACTER SET gbk DEFAULT NULL,
  `GDGZ` double DEFAULT NULL,
  `HGZ` double DEFAULT NULL,
  `BT` smallint(6) DEFAULT NULL,
  `LT` double DEFAULT NULL,
  `TGGZ` double DEFAULT NULL,
  `TSJT` smallint(6) DEFAULT NULL,
  `QTBT` double DEFAULT NULL,
  `BF` double DEFAULT NULL,
  `FZ` double DEFAULT NULL,
  `SF` double DEFAULT NULL,
  `DF` double DEFAULT NULL,
  `QF` double DEFAULT NULL,
  `HTBX` double DEFAULT NULL,
  `ZNF` double DEFAULT NULL,
  `LSDK` double DEFAULT NULL,
  `GZSH` double DEFAULT NULL,
  `JYFJ` double DEFAULT NULL,
  `YXDS` smallint(6) DEFAULT NULL,
  `SDS` double DEFAULT NULL,
  `GJJ` double DEFAULT NULL,
  `SYBX` double DEFAULT NULL,
  `YLBX` double DEFAULT NULL,
  `SFGZ` double DEFAULT NULL,
  `id` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8用上述记录导入，导入时间 00:02:766
CREATE UNIQUE INDEX bh ON aaf(id) ; 用时 00:03:594
CREATE INDEX bh1 ON aaf(bh) ;用时 00:03:328

Answer 3

CREATE TABLE `aae1` (
  `BH` VARCHAR(6) CHARACTER SET gbk DEFAULT NULL,
  `XM` VARCHAR(8) CHARACTER SET gbk DEFAULT NULL,
  `DW` VARCHAR(6) CHARACTER SET gbk DEFAULT NULL,
  `NY` DOUBLE DEFAULT NULL,
  `RYLB` VARCHAR(2) CHARACTER SET gbk DEFAULT NULL,
  `DWLB` VARCHAR(2) CHARACTER SET gbk DEFAULT NULL,
  `GDGZ` DOUBLE DEFAULT NULL,
  `HGZ` DOUBLE DEFAULT NULL,
  `BT` SMALLINT(6) DEFAULT NULL,
  `LT` DOUBLE DEFAULT NULL,
  `TGGZ` DOUBLE DEFAULT NULL,
  `TSJT` SMALLINT(6) DEFAULT NULL,
  `QTBT` DOUBLE DEFAULT NULL,
  `BF` DOUBLE DEFAULT NULL,
  `FZ` DOUBLE DEFAULT NULL,
  `SF` DOUBLE DEFAULT NULL,
  `DF` DOUBLE DEFAULT NULL,
  `QF` DOUBLE DEFAULT NULL,
  `HTBX` DOUBLE DEFAULT NULL,
  `ZNF` DOUBLE DEFAULT NULL,
  `LSDK` DOUBLE DEFAULT NULL,
  `GZSH` DOUBLE DEFAULT NULL,
  `JYFJ` DOUBLE DEFAULT NULL,
  `YXDS` SMALLINT(6) DEFAULT NULL,
  `SDS` DOUBLE DEFAULT NULL,
  `GJJ` DOUBLE DEFAULT NULL,
  `SYBX` DOUBLE DEFAULT NULL,
  `YLBX` DOUBLE DEFAULT NULL,
  `SFGZ` DOUBLE DEFAULT NULL,
  `id` INT(11) NOT NULL DEFAULT '0'
) ENGINE=MYISAM DEFAULT CHARSET=utf8LOAD DATA INFILE 'r://temp//123.txt' INTO TABLE aae1
00:00:00:453
CREATE UNIQUE INDEX bh ON aae1(id) ; 
00:00:00:438
CREATE INDEX bh1 ON aae1(bh)
00:00:00:734CREATE TABLE `aae2` (
  `BH` VARCHAR(6) CHARACTER SET gbk DEFAULT NULL,
  `XM` VARCHAR(8) CHARACTER SET gbk DEFAULT NULL,
  `DW` VARCHAR(6) CHARACTER SET gbk DEFAULT NULL,
  `NY` DOUBLE DEFAULT NULL,
  `RYLB` VARCHAR(2) CHARACTER SET gbk DEFAULT NULL,
  `DWLB` VARCHAR(2) CHARACTER SET gbk DEFAULT NULL,
  `GDGZ` DOUBLE DEFAULT NULL,
  `HGZ` DOUBLE DEFAULT NULL,
  `BT` SMALLINT(6) DEFAULT NULL,
  `LT` DOUBLE DEFAULT NULL,
  `TGGZ` DOUBLE DEFAULT NULL,
  `TSJT` SMALLINT(6) DEFAULT NULL,
  `QTBT` DOUBLE DEFAULT NULL,
  `BF` DOUBLE DEFAULT NULL,
  `FZ` DOUBLE DEFAULT NULL,
  `SF` DOUBLE DEFAULT NULL,
  `DF` DOUBLE DEFAULT NULL,
  `QF` DOUBLE DEFAULT NULL,
  `HTBX` DOUBLE DEFAULT NULL,
  `ZNF` DOUBLE DEFAULT NULL,
  `LSDK` DOUBLE DEFAULT NULL,
  `GZSH` DOUBLE DEFAULT NULL,
  `JYFJ` DOUBLE DEFAULT NULL,
  `YXDS` SMALLINT(6) DEFAULT NULL,
  `SDS` DOUBLE DEFAULT NULL,
  `GJJ` DOUBLE DEFAULT NULL,
  `SYBX` DOUBLE DEFAULT NULL,
  `YLBX` DOUBLE DEFAULT NULL,
  `SFGZ` DOUBLE DEFAULT NULL,
  `id` INT(11) NOT NULL DEFAULT '0',
  UNIQUE KEY `BH` (`id`),
  KEY `BH1` (`BH`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8LOAD DATA INFILE 'r://temp//123.txt' INTO TABLE aae2
00:00:00:922对比一下结果

Answer 4

官方都说了还有啥好怀疑的，ACMAIN_CHM 也引用了一些内容。这个其实很好理解：
两种情况下创建索引所用的数据是一样的，排除其它因素影响，如下是一个常理：插入的时候如果有索引则更加耗时，没有索引更快。那么插入时就已经多花费时间了，不是吗？另外还有一些印象，没去查资料证实，各位有空查证了也麻烦告诉我：
创建索引如果拥有全部的数据会比只有部分数据来得更快：
1. 因为程序在创建索引时需要分配空间（内存、磁盘），如果知道全部的数据就可以一次性分配足够的空间，否则程序（mysqld）是增量地申请空间的，每次申请和分配以及程序移动数据都要耗费时间。
2. 另外可能还有一些算法上的优化，好像是叫B树还是什么的最后再一次性创建索引还有一个好处就是因为磁盘空间是一次性申请到的，其物理上也会尽可能在一起，使用起来减少磁盘寻道的时间，所以也会提高查询时的性能。