Large table millions above record non-primary key indexes how to delete large amounts of data

as we all know, millions of table data are similar to the above, even if the Where condition hits the index, the execution time will not be less than 10 minutes, consider the operation of the data will cause two tables of exclusive locks, resulting in business suspension! impact from server resources: increased IOPS, IO contention causing CPU waits (high CPU), removal of large amounts of space debris from tables, resulting in wasted space and poor performance

This requirement can be used in the intermediate table rename method, the data will be retained written to the intermediate table, after the completion of the original table deleted, and the intermediate table renamed to the original table;

The following are the steps:

1.SHOW CREATETABLE ooo_scrm_customer; 2.创建同ooo_scrm_customer表结构一样的新表：_ooo_scrm_customer_new REATE TABLE _ooo_scrm_customer_new (`id` int(11) NOTNULLAUTO_INCREMENT,    .....    .....    .....PRIMARYKEY(`id`)) ENGINE=InnoDB DEFAULTCHARSET=utf8;3.创建增删改触发器（主要用于主表向中间表写数据时，业务方请求修改的数据要一起同步到中间表）CREATETRIGGER`ooo_scrm_customer_del` AFTERDELETEONooo_scrm_customer FOREACH ROW DELETE IGNOREFROM_ooo_scrm_customer_new WHERE_ooo_scrm_customer_new.`id` <=> OLD.`id`CREATETRIGGER`ooo_scrm_customer_upd` AFTERUPDATEONooo_scrm_customer FOREACH ROW REPLACEINTO_ooo_scrm_customer_new(`id`, `其他列`) VALUES(NEW.`id`, NEW.`其他列`)CREATETRIGGER`ooo_scrm_customer_ins` AFTERINSERTONooo_scrm_customer FOREACH ROW REPLACEINTO_ooo_scrm_customer_new(`id`, `其他列`) VALUES(NEW.`id`, NEW.`其他列`)4.将要保留的数据写入中间表INSERTLOW_PRIORITY IGNOREINTO_ooo_scrm_customer_new (`id`, `其他列`) SELECT`id`, `其他列` FROM ooo_scrm_customer LOCK INSHARE MODE #where条件优化，按分批执行思想，稳步执行！5.优化中间表ANALYZE TABLE_ooo_scrm_customer_new 6.改名并删除操作记录RENAME TABLEooo_scrm_customer TO_ooo_scrm_customer_old, _ooo_scrm_customer_new TO ooo_scrm_customerDROPTRIGGERIF EXISTS `mysqlcheck`.`ooo_scrm_customer_del`DROPTRIGGERIF EXISTS `mysqlcheck`.`ooo_scrm_customer_upd`DROPTRIGGERIF EXISTS `mysqlcheck`.`ooo_scrm_customer_ins`

Other application Cases

The idea originated from the Facebook MySQL path, which is also the implementation principle of the famous Pt-online-schema-change tool.

Can be applied to the online change table structure, online modification of table data, in principle, can achieve second-level table lock, 0 damage to the business

Students also have the flexibility to use other methods when implementing the program:

SET session Lock_wait_timeout=60;set Session Innodb_lock_wait_timeout=1;set session wait_timeout=10000

(Innodb_lock_wait_timeout refers to the maximum amount of time a transaction waits for a resource to wait, and the time that it has not been allocated to a resource returns an application failure;)

If you have a similar requirement, please apply this idea to your project.

over!!

Large table millions above record non-primary key indexes how to delete large amounts of data