千万行的表怎么加索引

执行过程如下（前提是停止写入防止数据异常）

1、创建一个表结构和对象一样的表

2、给新表加索引

3、给新表插入老表数据

4、把2个表重命名。

5、删除重命名后老表（可做可不做）

create table tmp like paper_author;
ALTER TABLE tmp ADD INDEX ( `PaperID` )

insert into tmp(ooo，...)  select  ooo,... from paper_author
Query OK, 35510600 rows affected (9 min 24.99 sec)
Records: 35510600  Duplicates: 0  Warnings: 0

RENAME TABLE paper_author TO tmp2, tmp to paper_author;

drop table tmp2;

 

执行过程如下

mysql> use seewoserzs;
Database changed
mysql> desc t_gameinfo;
+----------+---------------------+------+-----+-------------------+----------------+
| Field    | Type                | Null | Key | Default           | Extra          |
+----------+---------------------+------+-----+-------------------+----------------+
| gameid   | varchar(32)         | YES  | UNI | 10000             |                |
| gamename | varchar(32)         | YES  |     | NULL              |                |
| cpid     | bigint(20)          | YES  |     | NULL              |                |
| gametype | varchar(20)         | YES  |     | NULL              |                |
| addtime  | timestamp           | YES  |     | CURRENT_TIMESTAMP |                |
| id       | bigint(20) unsigned | NO   | PRI | NULL              | auto_increment |
+----------+---------------------+------+-----+-------------------+----------------+
6 rows in set

mysql> create table t1 like t_gameinfo;
Query OK, 0 rows affected

mysql> desc t1;
+----------+---------------------+------+-----+-------------------+----------------+
| Field    | Type                | Null | Key | Default           | Extra          |
+----------+---------------------+------+-----+-------------------+----------------+
| gameid   | varchar(32)         | YES  | UNI | 10000             |                |
| gamename | varchar(32)         | YES  |     | NULL              |                |
| cpid     | bigint(20)          | YES  |     | NULL              |                |
| gametype | varchar(20)         | YES  |     | NULL              |                |
| addtime  | timestamp           | YES  |     | CURRENT_TIMESTAMP |                |
| id       | bigint(20) unsigned | NO   | PRI | NULL              | auto_increment |
+----------+---------------------+------+-----+-------------------+----------------+
6 rows in set

mysql> insert into t1 select * from t_gameinfo;
Query OK, 34 rows affected
Records: 34  Duplicates: 0  Warnings: 0

mysql> rename table t_gameinfo to t2 ,t1 to t_gameinfo;
Query OK, 0 rows affected

mysql>