表索引操作
这里之所以写索引的操作主要原因在于:在导入数据前删除索引能大大提升导入速度。
DDL语句获取现有索引1show create table t_question_bak;
获取所有索引
获取单一索引1
2
3
4
5
6SELECT TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,COLUMN_NAME,SEQ_IN_INDEX,NON_UNIQUE,INDEX_TYPE
FROM INFORMATION_SCHEMA.STATISTICS i
WHERE TABLE_SCHEMA = 'xhkj_ques_0923'
AND TABLE_NAME='t_question_bak'
AND i.INDEX_NAME <> 'PRIMARY'
AND i.SEQ_IN_INDEX=1;
或者1show index from t_question_bak;
创建表索引
基本语法
在已有的表中建立索引的语法:1ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY [索引名](字段名1 [(长度)] [ASC|DESC]) [USING 索引方法];
或者1CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 ON 表名(字段名) [USING 索引方法];
例子:1ALTER TABLE projectfile ADD UNIQUE INDEX (fileuploadercode);
或者1create index index_id on sorc4(s_id);
获取建表普通索引SQL1
2
3
4
5
6
7SELECT CONCAT('ALTER TABLE ',i.TABLE_NAME,' ADD INDEX ',i.INDEX_NAME,'(',i.COLUMN_NAME,');')
FROM INFORMATION_SCHEMA.STATISTICS i
WHERE TABLE_SCHEMA = 'xhkj_ques_0923'
AND TABLE_NAME='t_question_bak'
AND i.INDEX_NAME <> 'PRIMARY'
AND i.SEQ_IN_INDEX=1
AND i.NON_UNIQUE=1;
或者1
2
3
4
5
6
7SELECT CONCAT('CREATE INDEX ',i.INDEX_NAME,' ON ',i.TABLE_NAME,'(',i.COLUMN_NAME,');')
FROM INFORMATION_SCHEMA.STATISTICS i
WHERE TABLE_SCHEMA = 'xhkj_ques_0923'
AND TABLE_NAME='t_question_bak'
AND i.INDEX_NAME <> 'PRIMARY'
AND i.SEQ_IN_INDEX=1
AND i.NON_UNIQUE=1;
获取表全文索引SQL1
2
3
4
5
6
7SELECT CONCAT('ALTER TABLE ',i.TABLE_NAME,' ADD FULLTEXT INDEX ',i.INDEX_NAME,'(',i.COLUMN_NAME,');')
FROM INFORMATION_SCHEMA.STATISTICS i
WHERE TABLE_SCHEMA = 'xhkj_ques_0923'
AND TABLE_NAME='t_question_bak'
AND i.INDEX_NAME <> 'PRIMARY'
AND i.SEQ_IN_INDEX=1
AND i.INDEX_TYPE='FULLTEXT';
或者1
2
3
4
5
6
7SELECT CONCAT('CREATE FULLTEXT INDEX ',i.INDEX_NAME,' ON ',i.TABLE_NAME,'(',i.COLUMN_NAME,');')
FROM INFORMATION_SCHEMA.STATISTICS i
WHERE TABLE_SCHEMA = 'xhkj_ques_0923'
AND TABLE_NAME='t_question_bak'
AND i.INDEX_NAME <> 'PRIMARY'
AND i.SEQ_IN_INDEX=1
AND i.INDEX_TYPE='FULLTEXT';
获取建表唯一索引SQL1
2
3
4
5
6
7SELECT CONCAT('ALTER TABLE ',i.TABLE_NAME,' ADD UNIQUE INDEX ',i.INDEX_NAME,'(',i.COLUMN_NAME,');')
FROM INFORMATION_SCHEMA.STATISTICS i
WHERE TABLE_SCHEMA = 'xhkj_ques_0923'
AND TABLE_NAME='t_question_bak'
AND i.INDEX_NAME <> 'PRIMARY'
AND i.SEQ_IN_INDEX=1
AND i.NON_UNIQUE=0;
或者1
2
3
4
5
6
7SELECT CONCAT('CREATE UNIQUE INDEX ',i.INDEX_NAME,' ON ',i.TABLE_NAME,'(',i.COLUMN_NAME,');')
FROM INFORMATION_SCHEMA.STATISTICS i
WHERE TABLE_SCHEMA = 'xhkj_ques_0923'
AND TABLE_NAME='t_question_bak'
AND i.INDEX_NAME <> 'PRIMARY'
AND i.SEQ_IN_INDEX=1
AND i.NON_UNIQUE=0;
删除表索引
导入数据前删除表索引能大大提升数据的导入效率1ALTER TABLE 表名 DROP INDEX 索引名;
或1DROP INDEX 索引名 ON 表名;
生成删除索引的SQL(排除主键索引)1
2
3
4
5SELECT CONCAT('ALTER TABLE ',i.TABLE_NAME,' DROP INDEX ',i.INDEX_NAME,';')
FROM INFORMATION_SCHEMA.STATISTICS i
WHERE TABLE_SCHEMA = 'xhkj_ques_0923'
AND TABLE_NAME='t_question_bak'
AND i.INDEX_NAME <> 'PRIMARY';
复制运行即可
一、最简单的方法1create table t2 as select * from t1;适用表数据量小的情况
二、SQL形式
mysqldump逻辑导出1mysqldump -h127.0.0.1 -P3306 -uroot -p123456–add-locks=0
表示在输出的的文件结果里,不增加”lock tbales t2 write”
–no-create-info
不需要导出表结构
–single-transaction
不需要对t2进行加锁,而是使用start transaction with consistent snapshop的方法
-set-gtid-purged=off
不导出gtid相关信息
–where=’c1>4’
过滤条件
–result-file
指定导出文件的路径
三、CSV文件形式
创建t3表结构1create table t3 like t2;
导出需要导出t2的数据1
2
3
4
5# 全部导出
SELECT * FROM t2 INTO OUTFILE '/var/lib/mysql-files/t2.csv' FIELDS TERMINATED BY ',';
# 部分导出
SELECT * FROM t2 WHERE c1>4 INTO OUTFILE '/var/lib/mysql-files/t2.csv' FIELDS TERMINATED BY ',';
将csv文件导入到t31LOAD DATA INFILE '/var/lib/mysql-files/t2.csv' INTO TABLE t3 FIELDS TERMINATED BY ',';
四、物理拷贝的方法
1、创建t3表结构1create table t3 like t2;
2、执行1alter table t3 discard tablespace;
此时t3.ibd会被删除
3、执行1flush table t2 for export;
此时test01目录下会生成一个t2.cfg文件
4、拷贝t2.ibd,t2.cfg,注意权限1
2cp t2.cfg t3.cfg
cp t2.ibd t3.ibd
5、解除表锁定1unlock tables;
这时候t2.cfg会被删除
6、执行1alter table t3 import tablespace;
将t3.ibd作为t3新的表空间,数据和t2也是相同的。
总结对于大表,物理拷贝方法最快,对于误删表的情况,比较有用,但是也存在一定的弊端:必须是全表拷贝;
必须能连服务器;
源表和目标表都必须是innodb存储引擎。
mysqldump方法可以生成insert的语句,可以加过滤条件拷贝部分数据,但是不能使用join这种比较复杂的条件。
select .... into outfile的方法最灵活,但是缺点是每次只能导出一张表。