mysql大批量测试数据生成

建表

CREATE TABLE `km_tbl_companysongfilter_new` (
  `FilterID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `CompanyCode` VARCHAR(10) NOT NULL,
  `FilterType` VARCHAR(32) NOT NULL COMMENT '过滤类别',
  `FilterValue` VARCHAR(32) NOT NULL COMMENT '过滤值',
  `CreateDateTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`FilterID`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

使用存储过程批量增加数据

DELIMITER $$
CREATE  PROCEDURE add_test_data(IN n INT)  
BEGIN    
DECLARE i INT DEFAULT 1;  
WHILE i <= n  DO  
-- 这里可以自己insert多条
INSERT INTO km_tbl_companysongfilter_new  (CompanyCode,FilterType,FilterValue,CreateDateTime ) VALUES (FLOOR(RAND() * 100000),'SongID',FLOOR(RAND() * 100000000) ,NOW());  
SET i=i+1;      
END WHILE;  
END $$

创建索引和删除索引

ALTER TABLE km_tbl_companysongfilter_new ADD INDEX idx_company_filtertype(CompanyCode,FilterType);

DROP INDEX idx_company_filtertype ON km_tbl_companysongfilter_new;

调优语句

EXPLAIN
SELECT SQL_NO_CACHE FilterType,FilterValue FROM km_tbl_companysongfilter_new WHERE (CompanyCode='63284' OR CompanyCode='-1') AND FilterType<>'SongID';