前面已经建立了索引，优化了SQL语句，并将单线程变为多线程并行执行，去重时间由最初的35秒优化为3.5秒，是不是就到此为止呢？吴老师又使用了rocksdb存储引擎替代innodb的方法。这里有必要交代一下命题的背景。这道MySQL数据库优化的题目出自是阿里内部的竞赛题，当然我是听吴老师口述的，真正的题目及其竞赛规则与竞赛环境不甚明确，但有一条是允许自由选择MySQL存储引擎。在实际的生产环境中，几乎没有可能为了单一操作的优化而改变表的存储引擎，因为这样做的代价通常很高。
顺便提一句，据刚刚得到的消息，吴老师在今天结束的决赛中获得了第三名的好成绩（前两名都是数据库内核组的），祝贺！
一、MyRocks简介1. 支持的平台
官方支持OS：sudo yum install -y cmake gcc-c++ bzip2-devel libaio-devel bison zlib-devel snappy-devel sudo yum install -y gflags-devel readline-devel ncurses-devel openssl-devel lz4-devel gdb git
这几个包缺一不可，gflags-devel和lz4-devel在yum中没有安装包，所以要下源码编译安装。
（2）安装gflags
git clone https://github.com/gflags/gflags.git cd gflags mkdir build && cd build ccmake ..
之后按下面的提示操作
/*****make make install
（3）安装lz4
git clone https://github.com/lz4/lz4.git cd lz4 make make install
（4）升级gcc
系统自带4.4.7，升级到任一验证过的编译器，这里升级到6.1.0# 下载源码包并解压 wget http://ftp.gnu.org/gnu/gcc/gcc-6.1.0/gcc-6.1.0.tar.bz2 tar -jxvf gcc-6.1.0.tar.bz2 # 下载编译所需依赖库 cd gcc-6.1.0 ./contrib/download_prerequisites cd .. # 建立编译输出目录 mkdir gcc-build-6.1.0 # 进入此目录，执行以下命令，生成makefile文件 cd gcc-build-6.1.0 ../gcc-6.1.0/configure --enable-checking=release --enable-languages=c,c++ --disable-multilib # 编译，j后面的是核心数，编译速度会比较快 make -j4 # 安装 make install # 切换GCC到新版 # 确定新安装的GCC的路径,一般默认在/usr/local/bin下 ls /usr/local/bin | grep gcc # 添加新GCC到可选项，倒数第三个是名字，倒数第二个参数为新GCC路径，最后一个参数40为优先级，设大一些之后就自动使用新版了 update-alternatives --install /usr/bin/gcc gcc /usr/local/bin/x86_64-pc-linux-gnu-gcc-6.1.0 40 # 定义环境变量，需要设置，否则在编译MyRocks时会报错 CC=‘/usr/local/bin/x86_64-pc-linux-gnu-gcc-6.1.0‘; export CC; # 确认当前版本已经切换为新版 gcc -v
（5）更新libstdc++
需要更新libstdc++，否则启动mysqld时会报类似“/usr/lib64/libstdc++.so.6: version ‘GLIBCXX_3.4.19‘ not found”的错误。cp /root/gcc-build-6.1.0/x86_64-pc-linux-gnu/libstdc++-v3/src/.libs/libstdc++.so.6.0.22 /usr/lib64/ cd /usr/lib64/ rm -r libstdc++.so.6 ln -s libstdc++.so.6.0.22 libstdc++.so.6 strings /usr/lib64/libstdc++.so.6 | grep GLIBCXX
查看包含的GLIBCXX结果如图一所示。
图一
3. 编译MyRocks源码# 下载项目源码 git clone https://github.com/facebook/mysql-5.6.git cd mysql-5.6 git submodule init # 调用 git submodule update 用来更新 submodule 信息。 git submodule update # 生成makefile文件 cmake . -DCMAKE_BUILD_TYPE=RelWithDebInfo -DWITH_SSL=system -DWITH_ZLIB=bundled -DMYSQL_MAINTAINER_MODE=0 -DENABLED_LOCAL_INFILE=1 # 编译 make -j8 # 安装 make install
cd mysql-test ./mysql-test-run.pl --mem --async-client --parallel=16 --fast --max-test-fail=1000 --retry=0 --force --mysqld=--rocksdb --mysqld=--default-storage-engine=rocksdb --mysqld=--skip-innodb --mysqld=--default-tmp-storage-engine=MyISAM --suite=rocksdb
[mysqld] # 指定存储引擎 rocksdb default-storage-engine=rocksdb # 禁用innodb skip-innodb # 指定临时表使用的存储引擎 default-tmp-storage-engine=MyISAM # 指定字符集排序规则 collation-server=latin1_bin # 禁用binlog skip_log_bin # 禁用自动提交 autocommit=0 # 不做严格的排序规则检查，如果不设置，在varchar字段建立索引时会报错 rocksdb_strict_collation_check=0 # 启用批量数据装载 rocksdb_bulk_load=1 # 指定批次记录数 rocksdb_bulk_load_size=10000 # 允许并行写内存表 rocksdb_allow_concurrent_memtable_write = 1 # 设置写缓冲区大小 rocksdb_db_write_buffer_size = 1G # 不立即刷新内存缓冲区中的数据 rocksdb_write_disable_wal = 1
Oracle大师Thomas Kyte曾经说过大多数性能问题是设计不当引起的，尽管如此，作为DBA还是可以从数据库系统调整的角度做些有益的工作。上面这些针对rocksdb存储引擎所做的配置对去重实验的性能影响巨大。我从知道rocksdb有这个东西到本实验才两天时间，根本谈不上了解，只是有些直观的感受。例如，rocksdb_write_disable_wal感觉就和innodb_flush_log_at_trx_commit的 作用差不多。
/usr/local/mysql/scripts/mysql_install_db --defaults-file=/usr/local/mysql/my.cnf --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql
useradd mysql chown -R mysql:mysql /usr/local/mysql/data /usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/my.cnf --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql &
create table t_source ( item_id int, created_time datetime, modified_time datetime, item_name varchar(20), other varchar(20) );2. 建立目标表
create table t_target like t_source;
3. 建立生成测试数据的存储过程
delimiter //
create procedure sp_generate_data()
begin
set @i := 1;
while @i<=500000 do
set @created_time := date_add(‘2017-01-01‘,interval @i second);
set @modified_time := @created_time;
set @item_name := concat(‘a‘,@i);
insert into t_source
values (@i,@created_time,@modified_time,@item_name,‘other‘);
set @i:=@i+1;
end while;
commit;
set @last_insert_id := 500000;
insert into t_source
select item_id + @last_insert_id,
created_time,
date_add(modified_time,interval @last_insert_id second),
item_name,
‘other‘
from t_source;
commit;
end
//
delimiter ; 4. 生成测试数据
call sp_generate_data(); insert into t_source select * from t_source where item_id=1; commit;
5. 建立索引
create index idx_sort on t_source(created_time,item_name,item_id); analyze table t_source;
6. 建立并行执行的存储过程
delimiter //
create procedure sp_unique(i smallint)
begin
set @a:=‘0000-00-00 00:00:00‘;
set @b:=‘ ‘;
if (i<4) then
insert into t_target
select * from t_source force index (idx_sort)
where created_time >= date_add(‘2017-01-01‘,interval (i-1)*125000 second)
and created_time < date_add(‘2017-01-01‘,interval i*125000 second)
and (@a!=created_time or @b!=item_name)
and (@a:=created_time) is not null
and (@b:=item_name) is not null
order by created_time,item_name;
commit;
else
insert into t_target
select * from t_source force index (idx_sort)
where created_time >= date_add(‘2017-01-01‘,interval (i-1)*125000 second)
and created_time <= date_add(‘2017-01-01‘,interval i*125000 second)
and (@a!=created_time or @b!=item_name)
and (@a:=created_time) is not null
and (@b:=item_name) is not null
order by created_time,item_name;
commit;
end if;
end
//
delimiter ;7. 建立shell脚本文件duplicate_removal.sh
#!/bin/bash
/usr/local/mysql/bin/mysql -vvv test -e "truncate t_target" &>/dev/null
date ‘+%H:%M.%N‘
for y in {1..4}
do
sql="call sp_unique($y)"
/usr/local/mysql/bin/mysql -vvv test -e "$sql" &>par_sql1_$y.log &
done
wait
date ‘+%H:%M.%N‘8. 执行去重
chmod 755 duplicate_removal.sh ./duplicate_removal.sh
并行执行的4个过程调用分别用时如图二所示。
图二
将MySQL去重操作优化到极致之三弹连发（三）：用rocksdb替代innodb
原文：http://blog.csdn.net/wzy0623/article/details/54378575
