root@dax-mysql-master binlog2sql]# cat /tmp/re_aaaa_test_order.sql INSERT INTO `aaaa`.`test_order`(`id`) VALUES (12); #start 3037 end 3222 time 2018-11-06 18:54:21 INSERT INTO `aaaa`.`test_order`(`id`) VALUES (11); #start 3037 end 3222 time 2018-11-06 18:54:21 INSERT INTO `aaaa`.`test_order`(`id`) VALUES (10); #start 3037 end 3222 time 2018-11-06 18:54:21 INSERT INTO `aaaa`.`test_order`(`id`) VALUES (9); #start 3037 end 3222 time 2018-11-06 18:54:21 INSERT INTO `aaaa`.`test_order`(`id`) VALUES (8); #start 3037 end 3222 time 2018-11-06 18:54:21 INSERT INTO `aaaa`.`test_order`(`id`) VALUES (7); #start 3037 end 3222 time 2018-11-06 18:54:21 INSERT INTO `aaaa`.`test_order`(`id`) VALUES (6); #start 3037 end 3222 time 2018-11-06 18:54:21 INSERT INTO `aaaa`.`test_order`(`id`) VALUES (5); #start 3037 end 3222 time 2018-11-06 18:54:21 INSERT INTO `aaaa`.`test_order`(`id`) VALUES (4); #start 3037 end 3222 time 2018-11-06 18:54:21 INSERT INTO `aaaa`.`test_order`(`id`) VALUES (2); #start 2804 end 2949 time 2018-11-06 18:45:45 DELETE FROM `aaaa`.`test_order` WHERE `id`=12 LIMIT 1; #start 2571 end 2716 time 2018-11-06 18:45:39 DELETE FROM `aaaa`.`test_order` WHERE `id`=11 LIMIT 1; #start 2338 end 2483 time 2018-11-06 18:45:33
生成的sql顺序是倒序的，需要重新调整（github上关于binlog2sql的用法未有关于顺序的说明）： sed -i ‘1!G;h;$!d’ /tmp/re_aaaa_test_order.sql
1 2 3 4 5 6 7 8 9 10 11 12 13 14
[root@dax-mysql-master binlog2sql]# sed -i '1!G;h;$!d' /tmp/re_aaaa_test_order.sql [root@dax-mysql-master binlog2sql]# cat /tmp/re_aaaa_test_order.sql DELETE FROM `aaaa`.`test_order` WHERE `id`=11 LIMIT 1; #start 2338 end 2483 time 2018-11-06 18:45:33 DELETE FROM `aaaa`.`test_order` WHERE `id`=12 LIMIT 1; #start 2571 end 2716 time 2018-11-06 18:45:39 INSERT INTO `aaaa`.`test_order`(`id`) VALUES (2); #start 2804 end 2949 time 2018-11-06 18:45:45 INSERT INTO `aaaa`.`test_order`(`id`) VALUES (4); #start 3037 end 3222 time 2018-11-06 18:54:21 INSERT INTO `aaaa`.`test_order`(`id`) VALUES (5); #start 3037 end 3222 time 2018-11-06 18:54:21 INSERT INTO `aaaa`.`test_order`(`id`) VALUES (6); #start 3037 end 3222 time 2018-11-06 18:54:21 INSERT INTO `aaaa`.`test_order`(`id`) VALUES (7); #start 3037 end 3222 time 2018-11-06 18:54:21 INSERT INTO `aaaa`.`test_order`(`id`) VALUES (8); #start 3037 end 3222 time 2018-11-06 18:54:21 INSERT INTO `aaaa`.`test_order`(`id`) VALUES (9); #start 3037 end 3222 time 2018-11-06 18:54:21 INSERT INTO `aaaa`.`test_order`(`id`) VALUES (10); #start 3037 end 3222 time 2018-11-06 18:54:21 INSERT INTO `aaaa`.`test_order`(`id`) VALUES (11); #start 3037 end 3222 time 2018-11-06 18:54:21 INSERT INTO `aaaa`.`test_order`(`id`) VALUES (12); #start 3037 end 3222 time 2018-11-06 18:54:21
5、把这sql文件进入导入即可进行备份后的数据恢复
1 2 3
[root@dax-mysql-master binlog2sql]# mysql -u root -p12345678 aaaa < /tmp/re_aaaa_test_order.sql mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1205 (HY000) at line 1: Lock wait timeout exceeded; try restarting transaction
需要先解锁表：
1
unlock tables;
解锁完成在重新导入：
1
[root@dax-mysql-master binlog2sql]# mysql -u root -p12345678 aaaa < /tmp/re_aaaa_test_order.sql
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.
error.log日志报错：
1 2 3 4 5 6 7 8 9 10 11 12
2018-11-01T06:57:09.766303Z 6 [Note] Plugin group_replication reported: 'Group communication SSL configuration: group_replication_ssl_mode: "DISABLED"' 2018-11-01T06:57:09.768075Z 6 [Note] Plugin group_replication reported: '[GCS] Added automatically IP ranges 127.0.0.1/8,192.168.168.178/24 to the whitelist' 2018-11-01T06:57:09.769536Z 6 [Note] Plugin group_replication reported: '[GCS] Translated 'dax-mysql-master' to 192.168.168.178' 2018-11-01T06:57:09.770000Z 6 [Warning] Plugin group_replication reported: '[GCS] Automatically adding IPv4 localhost address to the whitelist. It is mandatory that it is added.' 2018-11-01T06:57:09.770200Z 6 [Note] Plugin group_replication reported: '[GCS] SSL was not enabled' 2018-11-01T06:57:09.770253Z 6 [Note] Plugin group_replication reported: 'Initialized group communication with configuration: group_replication_group_name: "740442c0-cc67-11e8-993e-525400578639"; group_replication_local_address: "dax-mysql-master:24901"; group_replication_group_seeds: "dax-mysql-master:24901,dax-mysql-slave:24901"; group_replication_bootstrap_group: false; group_replication_poll_spin_loops: 0; group_replication_compression_threshold: 1000000; group_replication_ip_whitelist: "AUTOMATIC"' 2018-11-01T06:57:09.770341Z 6 [Note] Plugin group_replication reported: '[GCS] Configured number of attempts to join: 0' 2018-11-01T06:57:09.770356Z 6 [Note] Plugin group_replication reported: '[GCS] Configured time between attempts to join: 5 seconds' 2018-11-01T06:57:09.770494Z 6 [Note] Plugin group_replication reported: 'Member configuration: member_id: 3306103; member_uuid: "d5bd8edd-9a1d-11e8-993e-525400578639"; single-primary mode: "true"; group_replication_auto_increment_increment: 7; ' 2018-11-01T06:57:09.770589Z 6 [ERROR] Plugin group_replication reported: 'Can't start group replication on secondary member with single primary-mode while asynchronous replication channels are running.' 2018-11-01T06:57:09.770682Z 6 [Note] Plugin group_replication reported: 'Requesting to leave the group despite of not being a member' 2018-11-01T06:57:09.770696Z 6 [ERROR] Plugin group_replication reported: '[GCS] The member is leaving a group without being on one.'
尝试重启dax-mysql-slave节点组复制：
1 2
start group_replication; ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.
2018-11-01T06:59:20.617005Z 114 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_applier' executed'. Previous state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 128090715, master_bind=''. New state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''. 2018-11-01T06:59:21.516164Z 111 [Note] Plugin group_replication reported: 'Group Replication applier module successfully initialized!' 2018-11-01T06:59:21.516264Z 117 [Note] Slave SQL thread for channel 'group_replication_applier' initialized, starting replication in log 'FIRST' at position 0, relay log './relay-log-group_replication_applier.000002' position: 4 2018-11-01T06:59:21.516277Z 111 [Note] Plugin group_replication reported: 'auto_increment_increment is set to 7' 2018-11-01T06:59:21.516298Z 111 [Note] Plugin group_replication reported: 'auto_increment_offset is set to 3306102' 2018-11-01T06:59:21.553616Z 0 [Note] Plugin group_replication reported: 'XCom protocol version: 3' 2018-11-01T06:59:21.553718Z 0 [Note] Plugin group_replication reported: 'XCom initialized and ready to accept incoming connections on port 24901' 2018-11-01T06:59:21.620998Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to dax-mysql-master:24901 on local port: 24901.' 2018-11-01T06:59:21.621468Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to dax-mysql-master:24901 on local port: 24901.' 2018-11-01T06:59:21.621794Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to dax-mysql-master:24901 on local port: 24901.' 2018-11-01T06:59:21.621988Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to dax-mysql-master:24901 on local port: 24901.' 2018-11-01T06:59:21.622185Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to dax-mysql-master:24901 on local port: 24901.' 2018-11-01T06:59:21.622379Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to dax-mysql-master:24901 on local port: 24901.' 2018-11-01T06:59:21.622658Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to dax-mysql-master:24901 on local port: 24901.' 2018-11-01T06:59:21.622875Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to dax-mysql-master:24901 on local port: 24901.' 2018-11-01T06:59:21.623043Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to dax-mysql-master:24901 on local port: 24901.' 2018-11-01T06:59:21.623201Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to dax-mysql-master:24901 on local port: 24901.' 2018-11-01T06:59:21.623220Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error connecting to all peers. Member join failed. Local port: 24901' 2018-11-01T06:59:21.624544Z 0 [Warning] Plugin group_replication reported: 'read failed' 2018-11-01T06:59:21.656139Z 0 [ERROR] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 24901' 2018-11-01T07:00:21.516998Z 111 [ERROR] Plugin group_replication reported: 'Timeout on wait for view after joining group' 2018-11-01T07:00:21.517630Z 111 [Note] Plugin group_replication reported: 'Requesting to leave the group despite of not being a member' 2018-11-01T07:00:21.517820Z 111 [ERROR] Plugin group_replication reported: '[GCS] The member is leaving a group without being on one.' 2018-11-01T07:00:21.519413Z 111 [Note] Plugin group_replication reported: 'auto_increment_increment is reset to 1' 2018-11-01T07:00:21.519442Z 111 [Note] Plugin group_replication reported: 'auto_increment_offset is reset to 1' 2018-11-01T07:00:21.521022Z 117 [Note] Error reading relay log event for channel 'group_replication_applier': slave SQL thread was killed 2018-11-01T07:00:21.595775Z 114 [Note] Plugin group_replication reported: 'The group replication applier thread was killed' 2018-11-01T07:10:22.699429Z 111 [Note] Aborted connection 111 to db: 'unconnected' user: 'root' host: 'localhost' (Got timeout reading communication packets)
cluster = dba.getCluster("prodCluster") Dba.getCluster: This function is not available through a session to a standalone instance (metadata exists, but GR is not active) (RuntimeError)
dba.rebootClusterFromCompleteOutage('prodCluster') Reconfiguring the cluster 'prodCluster' from complete outage... The instance 'dax-mysql-slave:3306' was part of the cluster configuration. Would you like to rejoin it to the cluster? [y/N]: y WARNING: On instance 'dax-mysql-master:3306' membership change cannot be persisted since MySQL version 5.7.22 does not support the SET PERSIST command (MySQL version >= 8.0.5 required). Please use the <Dba>.configureLocalInstance command locally to persist the changes. The cluster was successfully rebooted. <Cluster:prodCluster> MySQL dax-mysql-master:3306 JS > cluster = dba.getCluster("prodCluster") <Cluster:prodCluster> MySQL dax-mysql-master:3306 JS > cluster.status() { "clusterName": "prodCluster", "defaultReplicaSet": { "name": "default", "primary": "dax-mysql-master:3306", "ssl": "DISABLED", "status": "OK_NO_TOLERANCE", "statusText": "Cluster is NOT tolerant to any failures.", "topology": { "dax-mysql-master:3306": { "address": "dax-mysql-master:3306", "mode": "R/W", "readReplicas": {}, "role": "HA", "status": "ONLINE" }, "dax-mysql-slave:3306": { "address": "dax-mysql-slave:3306", "mode": "R/O", "readReplicas": {}, "role": "HA", "status": "ONLINE" } } }, "groupInformationSourceMember": "mysql://repl@dax-mysql-master:3306" }
2018-11-01T11:29:10.359514Z 2 [Warning] InnoDB: Table test/test_order contains 1 user defined columns in InnoDB, but 25 columns in MySQL. Please check INFORMATION_SCHEMA.INNODB_SYS_COLUMNS and http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue. 2018-11-01T11:29:10.359631Z 2 [Warning] InnoDB: Cannot open table test/test_order from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue. 2018-11-01T11:29:25.514189Z 2 [Note] InnoDB: Table `test`.`test_order` is corrupted. Please drop the table and recreate it 2018-11-01T11:29:25.514313Z 2 [Warning] InnoDB: Cannot open table test/test_order from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
root@db 11:41: [test]> desc test_order; ERROR 2013 (HY000): Lost connection to MySQL server during query root@db 11:42: [test]> show create table test_order; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 2 Current database: test ERROR 2013 (HY000): Lost connection to MySQL server during query
查看error.log：
1 2 3 4 5 6 7 8 9 10 11
2018-11-01T11:42:25.566095Z 2 [ERROR] Build InnoDB index translation table for Table ./test/test_order failed 2018-11-01T11:42:25.566445Z 2 [ERROR] Table ./test/test_order has no primary key in InnoDB data dictionary, but has one in MySQL! If you created the table with a MySQL version < 3.23.54 and did not define a primary key, but defined a unique key with all non-NULL columns, then MySQL internally treats that key as the primary key. You can fix this error by dump + DROP + CREATE + reimport of the table. 2018-11-01T11:42:25.566519Z 2 [Warning] Table ./test/test_order key_used_on_scan is 0 even though there is no primary key inside InnoDB. 2018-11-01T11:42:25.566576Z 2 [ERROR] InnoDB could not find key no 0 with name PRIMARY from dict cache for table test/test_order 11:42:25 UTC - mysqld got signal 11 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. Attempting to collect some information that could help diagnose the problem. As this is a crash and something is definitely wrong, the information collection process might fail.
root@db 11:46: [test]> desc test_order; +-----------------+----------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+----------------+------+-----+---------+----------------+ | id | bigint(20) | NO | PRI | NULL | auto_increment | | order_no | varchar(24) | NO | UNI | NULL | | | broker_id | varchar(24) | NO | MUL | NULL | | | broker_uid | bigint(20) | NO | | NULL | | | plat_id | varchar(24) | NO | | NULL | | | price_asset | varchar(8) | NO | | NULL | | | test_type | varchar(16) | NO | | NULL | | | order_type | varchar(16) | NO | | NULL | | | price | decimal(32,20) | NO | | NULL | | | number | decimal(32,20) | NO | | NULL | | | test_asset | varchar(8) | NO | MUL | NULL | | | testd_number | decimal(32,20) | NO | | NULL | | | over_number | decimal(32,20) | NO | | NULL | | | testd_money | decimal(32,20) | NO | | NULL | | | fee_asset | varchar(8) | NO | | NULL | | | fee | decimal(32,20) | NO | | NULL | | | broker_fee | decimal(32,20) | NO | | NULL | | | cloud_fee | decimal(32,20) | NO | | NULL | | | client_order_no | varchar(36) | NO | | NULL | | | state | varchar(16) | NO | | NULL | | | send_state | varchar(16) | NO | | NULL | | | error_code | varchar(24) | YES | | NULL | | | error_msg | varchar(64) | YES | | NULL | | | create_time | datetime | NO | | NULL | | | update_time | datetime | NO | | NULL | | +-----------------+----------------+------+-----+---------+----------------+ 25 rows in set (0.01 sec)
root@db 11:46: [test]> show create table test_order\G *************************** 1. row *************************** Table: test_order Create Table: CREATE TABLE `test_order` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `order_no` varchar(24) NOT NULL, `broker_id` varchar(24) NOT NULL, `broker_uid` bigint(20) NOT NULL, `plat_id` varchar(24) NOT NULL, `price_asset` varchar(8) NOT NULL, `test_type` varchar(16) NOT NULL, `order_type` varchar(16) NOT NULL, `price` decimal(32,20) NOT NULL, `number` decimal(32,20) NOT NULL, `test_asset` varchar(8) NOT NULL, `testd_number` decimal(32,20) NOT NULL, `over_number` decimal(32,20) NOT NULL, `testd_money` decimal(32,20) NOT NULL, `fee_asset` varchar(8) NOT NULL, `fee` decimal(32,20) NOT NULL, `broker_fee` decimal(32,20) NOT NULL, `cloud_fee` decimal(32,20) NOT NULL, `client_order_no` varchar(36) NOT NULL, `state` varchar(16) NOT NULL, `send_state` varchar(16) NOT NULL, `error_code` varchar(24) DEFAULT NULL, `error_msg` varchar(64) DEFAULT NULL, `create_time` datetime NOT NULL, `update_time` datetime NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `test_order_order_no_uindex` (`order_no`), UNIQUE KEY `test_order_broker_id_client_order_no_uindex` (`broker_id`,`client_order_no`), KEY `multi_price_test_id_uid_state_index` (`test_asset`,`price_asset`,`broker_uid`,`broker_id`,`state`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.01 sec)
查看error.log有无其他报错：
1 2 3 4 5 6
2018-11-01T11:45:09.272752Z 2 [ERROR] Build InnoDB index translation table for Table ./test/test_order failed 2018-11-01T11:45:09.272904Z 2 [ERROR] InnoDB: MySQL and InnoDB data dictionaries are out of sync. Unable to find the AUTOINC column id in the InnoDB table `test`.`test_order`. We set the next AUTOINC column value to 0, in effect disabling the AUTOINC next value generation. 2018-11-01T11:45:09.272935Z 2 [Note] InnoDB: You can either set the next AUTOINC value explicitly using ALTER TABLE or fix the data dictionary by recreating the table. 2018-11-01T11:45:09.272959Z 2 [ERROR] InnoDB: Table test/test_order contains 1 indexes inside InnoDB, which is different from the number of indexes 4 defined in MySQL 2018-11-01T11:45:09.272976Z 2 [ERROR] InnoDB could not find key no 1 with name test_order_order_no_uindex from dict cache for table test/test_order 2018-11-01T11:45:09.272988Z 2 [ERROR] Table test/test_order contains fewer indexes inside InnoDB than are defined in the MySQL .frm file. Have you mixed up .frm files from different installations? Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
[quote]Failed authorization procedure. test.com (http-01): urn:ietf:params:acme:error:unauthorized :: The client lacks sufficient authorization :: Invalid response from http://mydomain.fr/.well-known/acme-challenge/Xefe-sxGfexdcdezDEUJZRfexjfeeloekcdsesx [2001:1600:4:1::b]: 404 IMPORTANT NOTES: The following errors were reported by the server: Domain: test.com Type: unauthorized Detail: Invalid response from http://mydomain.fr/.well-known/acme-challenge/Xefe-sxGfexdcdezDEUJZRfexjfeeloekcdsesx [2001:1600:4:1::b]: 404 To fix these errors, please make sure that your domain name was entered correctly and the DNS A/AAAA record(s) for that domain contain(s) the right IP address.[/quote]
如果服务器的80端口被限制，会提示如下错误：
1 2
http://demo.broker.masterdax.com/.well-known/acme-challenge/pIV-Rh1355xsh8xJFHhB0llri6HS8S2yOSYRE9N5D5I: Timeout during connect (likely firewall problem)
# .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs PATH=$PATH:$HOME/bin export PATH export THREADS_FLAG=native export ORACLE_BASE=/data/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/dbhome_1 export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH:/usr/sbin export LD_LIBRARY_PATH=$ORACLE_HOME/lib export ORACLE_SID=boston export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK export NLS_DATE_FORMAT="YYYY:MM:DD HH24:MI:SS" export EDITOR=vi set -o vi umask 022 alias bdump="cd $ORACLE_BASE/diag/rdbms/otcdb/${ORACLE_SID}/trace" alias sql='sqlplus "/ as sysdba"'
drop procedure if exists test; delimiter // create procedure test() begin declare i int; set i=0; while i<300000 do insert into testfororder(newid,name,age) select concat(i,name),FLOOR(18 + (RAND() * 12)) from test1; set i=i+1; end while; end// delimiter ; 调用存储过程： call test();
insert into sequence(newid,name,age) select * from testfororder;
数据量根据自己需求可以多次导入
4、查看sequence测试表数据：
1 2 3 4 5 6 7
select count(*) from sequence; +----------+ | count(*) | +----------+ | 12600000 | +----------+ 1 row in set (2.83 sec)
age条件筛选数据量
1 2 3 4 5 6 7
select count(*) from sequence where age in (26,19,22,20,28,29,25); +----------+ | count(*) | +----------+ | 7341992 | +----------+ 1 row in set (3.82 sec)
newid条件筛选数量
1 2 3 4 5 6 7
select count(*) from sequence where newid in (116670,116677,116684,116691,116698,116705,116719,116726); +----------+ | count(*) | +----------+ | 56 | +----------+ 1 row in set (3.28 sec)
5、where条件(age,newid)在没有创建索引的情况下：
age在前，newid在后，查看执行计划：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
explain select * from sequence where age in (26,19,22,20,28,29,25) and newid in (116670,116677,116684,116691,116698,116705,116719,116726); +----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+ | 1 | SIMPLE | sequence | NULL | ALL | NULL | NULL | NULL | NULL | 12292293 | 25.00 | Using where | +----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+ 1 row in set, 1 warning (0.01 sec)
select * from sequence where age in (26,19,22,20,28,29,25) and newid in (116670,116677,116684,116691,116698,116705,116719,116726); +----------+--------+-----------+------+ | id | newid | name | age | +----------+--------+-----------+------+ | 100003 | 116670 | 2381lucy | 26 | ... | 76679487 | 116719 | 2382lucy | 29 | +----------+--------+-----------+------+ 49 rows in set (5.36 sec)
newid在前，age在后：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
explain select * from sequence where newid in (116670,116677,116684,116691,116698,116705,116719,116726) and age in (26,19,22,20,28,29,25); +----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+ | 1 | SIMPLE | sequence | NULL | ALL | NULL | NULL | NULL | NULL | 12292293 | 25.00 | Using where | +----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
select * from sequence where newid in (116670,116677,116684,116691,116698,116705,116719,116726) and age in (26,19,22,20,28,29,25); +----------+--------+-----------+------+ | id | newid | name | age | +----------+--------+-----------+------+ | 100003 | 116670 | 2381lucy | 26 | ... | 76679487 | 116719 | 2382lucy | 29 | +----------+--------+-----------+------+ 49 rows in set (4.47 sec)
explain select * from sequence where age in (26,19,22,20,28,29,25) and newid in (116670,116677,116684,116691,116698,116705,116719,116726); +----+-------------+----------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------------+ | 1 | SIMPLE | sequence | NULL | range | newid_ind | newid_ind | 4 | NULL | 56 | 50.00 | Using index condition; Using where | +----+-------------+----------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------------+ 1 row in set, 1 warning (0.01 sec)
select * from sequence where age in (26,19,22,20,28,29,25) and newid in (116670,116677,116684,116691,116698,116705,116719,116726); +----------+--------+-----------+------+ | id | newid | name | age | +----------+--------+-----------+------+ | 100003 | 116670 | 2381lucy | 26 | ... | 76679487 | 116719 | 2382lucy | 29 | +----------+--------+-----------+------+ 49 rows in set (0.01 sec)
newid在前，age在后：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
explain select * from sequence where newid in (116670,116677,116684,116691,116698,116705,116719,116726) and age in (26,19,22,20,28,29,25); +----+-------------+----------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------------+ | 1 | SIMPLE | sequence | NULL | range | newid_ind | newid_ind | 4 | NULL | 56 | 50.00 | Using index condition; Using where | +----+-------------+----------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------------+ 1 row in set, 1 warning (0.01 sec)
select * from sequence where newid in (116670,116677,116684,116691,116698,116705,116719,116726) and age in (26,19,22,20,28,29,25); +----------+--------+-----------+------+ | id | newid | name | age | +----------+--------+-----------+------+ | 100003 | 116670 | 2381lucy | 26 | ... | 76679487 | 116719 | 2382lucy | 29 | +----------+--------+-----------+------+ 49 rows in set (0.00 sec)
在newid创建索引的情况下，where后面的查询条件前后顺序对查询效率影响不大。
7、where条件(newid)在没有创建索引，age创建索引的情况下：
删除newid的索引：
1
drop index newid_ind on sequence;
创建age的索引：
1
create index age_ind on sequence(age);
age在前，newid在后：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
explain select * from sequence where age in (26,19,22,20,28,29,25) and newid in (116670,116677,116684,116691,116698,116705,116719,116726); +----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+ | 1 | SIMPLE | sequence | NULL | ALL | age_ind | NULL | NULL | NULL | 12292293 | 50.00 | Using where | +----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
select * from sequence where age in (26,19,22,20,28,29,25) and newid in (116670,116677,116684,116691,116698,116705,116719,116726); +----------+--------+-----------+------+ | id | newid | name | age | +----------+--------+-----------+------+ | 100003 | 116670 | 2381lucy | 26 | ... | 76679487 | 116719 | 2382lucy | 29 | +----------+--------+-----------+------+ 49 rows in set (4.97 sec)
newid在前，age在后：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
explain select * from sequence where newid in (116670,116677,116684,116691,116698,116705,116719,116726) and age in (26,19,22,20,28,29,25); +----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+ | 1 | SIMPLE | sequence | NULL | ALL | age_ind | NULL | NULL | NULL | 12292293 | 50.00 | Using where | +----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
select * from sequence where newid in (116670,116677,116684,116691,116698,116705,116719,116726) and age in (26,19,22,20,28,29,25); +----------+--------+-----------+------+ | id | newid | name | age | +----------+--------+-----------+------+ | 100003 | 116670 | 2381lucy | 26 | ... | 76679487 | 116719 | 2382lucy | 29 | +----------+--------+-----------+------+ 49 rows in set (4.82 sec)
drop index age_ind on sequence; create index newid_age_ind on sequence(newid,age);
age在前，newid在后：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
explain select * from sequence where age in (26,19,22,20,28,29,25) and newid in (116670,116677,116684,116691,116698,116705,116719,116726); +----+-------------+----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | sequence | NULL | range | newid_age_ind | newid_age_ind | 9 | NULL | 98 | 100.00 | Using index condition | +----+-------------+----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
select * from sequence where age in (26,19,22,20,28,29,25) and newid in (116670,116677,116684,116691,116698,116705,116719,116726); +----------+--------+-----------+------+ | id | newid | name | age | +----------+--------+-----------+------+ | 100003 | 116670 | 2381lucy | 26 | ... | 76679487 | 116719 | 2382lucy | 29 | +----------+--------+-----------+------+ 49 rows in set (0.00 sec)
newid在前，age在后：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
explain select * from sequence where newid in (116670,116677,116684,116691,116698,116705,116719,116726) and age in (26,19,22,20,28,29,25); +----+-------------+----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | sequence | NULL | range | newid_age_ind | newid_age_ind | 9 | NULL | 98 | 100.00 | Using index condition | +----+-------------+----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)
select * from sequence where newid in (116670,116677,116684,116691,116698,116705,116719,116726) and age in (26,19,22,20,28,29,25); +----------+--------+-----------+------+ | id | newid | name | age | +----------+--------+-----------+------+ | 100003 | 116670 | 2381lucy | 26 | ... | 76679487 | 116719 | 2382lucy | 29 | +----------+--------+-----------+------+ 49 rows in set (0.01 sec)
drop procedure if exists test; delimiter // create procedure test() begin declare i int; set i=0; while i<300000 do insert into testfororder(newid,name,age) select concat(i,name),FLOOR(18 + (RAND() * 12)) from test1; set i=i+1; end while; end// delimiter ; 调用存储过程： call test();
insert into sequence(newid,name,age) select * from testfororder;
数据量根据自己需求可以多次导入
4、查看sequence测试表数据：
1 2 3 4 5 6 7
select count(*) from sequence; +----------+ | count(*) | +----------+ | 12600000 | +----------+ 1 row in set (2.83 sec)
5、在newid没有索引的情况下，in和or的对比：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
repl@db 16:14: [aaaa]> select * from sequence where newid in (116670,116677,116684,116691,116698,116705,116719,116726); +----------+--------+-----------+------+ | id | newid | name | age | +----------+--------+-----------+------+ | 100003 | 116670 | 2381lucy | 26 | ... | 76679494 | 116726 | 2382bobo | 21 | +----------+--------+-----------+------+ 56 rows in set (4.47 sec)
select * from sequence where newid =116670 or newid = 116677 or newid = 116684 or newid =116691 or newid = 116698 or newid=116705 or newid=116719 or newid=116726; +----------+--------+-----------+------+ | id | newid | name | age | +----------+--------+-----------+------+ | 100003 | 116670 | 2381lucy | 26 | ... | 76679494 | 116726 | 2382bobo | 21 | +----------+--------+-----------+------+ 56 rows in set (6.77 sec)
create index newid_ind on sequence(newid); select * from sequence where newid in (116670,116677,116684,116691,116698,116705,116719,116726); +----------+--------+-----------+------+ | id | newid | name | age | +----------+--------+-----------+------+ | 100003 | 116670 | 2381lucy | 26 | ... | 76679494 | 116726 | 2382bobo | 21 | +----------+--------+-----------+------+ 56 rows in set (0.00 sec)
select * from sequence where newid =116670 or newid = 116677 or newid = 116684 or newid =116691 or newid = 116698 or newid=116705 or newid=116719 or newid=116726; +----------+--------+-----------+------+ | id | newid | name | age | +----------+--------+-----------+------+ | 100003 | 116670 | 2381lucy | 26 | ... | 76679494 | 116726 | 2382bobo | 21 | +----------+--------+-----------+------+ 56 rows in set (0.01 sec)
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/tarball/percona-xtrabackup-2.4.9-Linux-x86_64.tar.gz tar -zxvf percona-xtrabackup-2.4.9-Linux-x86_64.tar.gz