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