[MySQL优化案例]系列 -- DISABLE/ENABLE KEYS的作用
作/译者:叶金荣
来源:http://imysql.cn
转载请注明作/译者和出处,并且不能用于商业用途,违者必究。
有一个表 tbl1 的结构如下:
CREATE TABLE `tbl1` ( `id` int(10) unsigned NOT NULL auto_increment, `name` char(20) NOT NULL default '', PRIMARY KEY (`id`), KEY `name` (`name`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;该表里已经存在了200万条记录.
现在, 需要把 tbl1 中的所有记录全部导到另一个完全相同的表 tbl2 中去.
1. 如果采用以下传统的方式, 则执行时间为: 98.01s
mysql>INSERT INTO tbl2 SELECT * FROM tbl1;Query OK, 2000000 row affected (1 min 38.01 sec)Records: 2000000 Duplicates: 0 Warnings: 02. 如果是用以下SQL语句, 则执行时间为: 80.85s (36.30 + 44.55)
mysql>ALTER TABLE tbl2 DISABLE KEYS;Query OK, 0 rows affected (0.00 sec)mysql>INSERT INTO tbl2 SELECT * FROM tbl1;Query OK, 2000000 row affected (36.30 sec)Records: 2000000 Duplicates: 0 Warnings: 0mysql>ALTER TABLE tbl2 ENABLE KEYS;Query OK, 0 rows affected (44.55 sec)从上面的测试结果来看, 在大批量导入时先禁用索引, 在完全导入后, 再开启索引, 一次性完成重建索引的效率会相对高很多, 经过反复几次测试, 感觉后者基本能比前者快 1.2 倍左右. 这也就是 LOAD DATAL INFILE 相对较快的原因之一.
分享到:
相关推荐
# ./configure --host=arm-unknown-linux-uclibcgnueabi --prefix=/usr --disable-warnings --disable-shared --enable-static LDFLAGS="--static -s" 2、mips平台 # ./configure --host=mips-unknown-linux-uclibc ...
-with-pdo-mysql=shared,mysqlnd --with-gd --with-iconv --with-zlib --enable-zip --enable-inline-optimization --disable-debug --disable-rpath --enable-shared --enable-xml --enable-bcmath --enable-shmop ...
I/data/tools/staticlib/include --disable-shared --enable-static --extra-ldflags=-L/data/tools/staticlib/lib --enable-runtime-cpudetect --enable-gpl --enable-nonfree --disable-debug --disable-ffplay --...
-enable-languages=c,c++,fortran --with-cpu=cortex-a9 --with-fpu=neon --with-float=hard --with-pkgversion='crosstool-NG 1.24.0' --enable-__cxa_atexit --disable-libmudflap --disable-libgomp --disable-...
decoder=vorbis --disable-encoder=vorbis --disable-vhook --enable-ffplay --disabl e-ffserver --disable-mpegaudio-hp --enable-pthreads --enable-liba52 --enable-non free --enable-libamr-nb --enable-...
disable/enable adb codes
../configure --prefix=/usr/local/gcc-5.4.0 --enable-threads=posix --disable-checking --disable-multilib --enable-languages=c,c++ --with-gmp=/usr/local/gmp-4.3.2 --with-mpfr=/usr/local/mpfr-2.4.2 --...
-with-float=soft --with-headers=/opt/crosstool/arm-linux/gcc-3.4.1-glibc-2.3.2/arm-linux/include --with-local-prefix=/opt/crosstool/arm-linux/gcc-3.4.1-glibc-2.3.2/arm-linux --disable-nls --enable-...
错误解决方法 ... 配置项 ./configure \ --cc=/Applications/Xcode.app/Contents/Developer/Platforms/iPhoneSimulator.platform/Developer/usr/bin/gcc \ --as='/usr/local/bin/gas-preprocessor.pl ...--disable-debug
Configured with: /scratch/julian/lite-respin/linux/src/gcc-4.3/configure --build=i686-pc-linux-gnu --host=i686-pc-linux-gnu --target=arm-none-linux-gnueabi --enable-threads --disable-libmudflap --...
Configured with: /scratch/julian/lite-respin/linux/src/gcc-4.3/configure --build=i686-pc-linux-gnu --host=i686-pc-linux-gnu --target=arm-none-linux-gnueabi --enable-threads --disable-libmudflap --...
/opt/gcc-4.6.4/configure --prefix=/usr/local/gcc-4.6.4 -enable-threads=posix -disable-checking -disable-multilib -enable-languages=c,c++ --with-gmp=/usr/local/gmp-4.2.3 --with-mpfr=/usr/local/mpfr-...
编译参数:./configure --disable-x86asm --enable-shared --disable-static --enable-gpl --enable-libx264 --extra-cflags=-I/x264/include --extra-ldflags="-L/x264/lib -static" --pkgconfigdir=/x264/lib/...
-with-float=soft --with-headers=/opt/crosstool/arm-linux/gcc-3.4.1-glibc-2.3.2/arm-linux/include --with-local-prefix=/opt/crosstool/arm-linux/gcc-3.4.1-glibc-2.3.2/arm-linux --disable-nls --enable-...
-with-float=soft --with-headers=/opt/crosstool/arm-linux/gcc-3.4.1-glibc-2.3.2/arm-linux/include --with-local-prefix=/opt/crosstool/arm-linux/gcc-3.4.1-glibc-2.3.2/arm-linux --disable-nls --enable-...
./configure --prefix=/usr/local/gcc --enable-threads=posix --disable-checking --disable-multilib --enable-languages=c,c++ --with-gmp=/usr/local/gmp --with-mpfr=/usr/local/mpfr --with-mpc=/usr/local/...
#./configure --prefix=/root/bluez/openobex --host=arm-linux CC="arm-linux-gcc -I/root/bluez/bluez-libs/include -L/root/bluez/bluez-libs/lib" --enable-bluetooth --disable-usb --enable-apps #make #...
disable-shared --disable-nls --disable-tls --with-gmp=/home/gfortran/gcc-home/bi nary/mingw32/native/x86_32/gmp --with-mpfr=/home/gfortran/gcc-home/binary/mingw3 2/native/x86_32/mpfr --enable-...
./configure --prefix=/usr/local/gcc --enable-threads=posix --disable-checking --disable-multilib --enable-languages=c,c++ --with-gmp=/usr/local/gmp --with-mpfr=/usr/local/mpfr --with-mpc=/usr/local/...
--disable-static \ --enable-shared \ --enable-gpl \ --enable-version3 \ --enable-nonfree \ --disable-doc \ --disable-ffmpeg \ --disable-ffplay \ --disable-ffprobe \ --disable-ffserver \ --disable-...