MySQL学习批量插入数据、单表索引优化

1. 往表里插入50W数据

1.1 建表

 CREATE TABLE `dept` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `deptName` VARCHAR(30) DEFAULT NULL,
 `address` VARCHAR(40) DEFAULT NULL,
 ceo INT NULL ,
 PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `emp` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `empno` INT NOT NULL ,
 `name` VARCHAR(20) DEFAULT NULL,
 `age` INT(3) DEFAULT NULL,
 `deptId` INT(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
 #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

1.2 设置参数log_bin_trust_function_creators

这个是二进制日志，MySQL主从复制用的。因为为了防止主从复制出现差错，所以默认严禁用户自己创建函数。

创建函数，假如报错：This function has none of DETERMINISTIC…

show variables like ‘log_bin_trust_function_creators’;

set global log_bin_trust_function_creators=1;

这样添加了参数以后，如果mysqld重启，上述参数又会消失，永久方法：

windows下my.ini[mysqld]加上log_bin_trust_function_creators=1
linux下 /etc/my.cnf下my.cnf[mysqld]加上log_bin_trust_function_creators=1

1.3 创建函数,保证每条数据都不同

随机产生字符串：

delimiter $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN    
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END $$
#假如要删除
#drop function rand_string;

随机产生部门编号：

#用于随机产生多少到多少的编号
DELIMITER $$
CREATE FUNCTION  rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN   
 DECLARE i INT DEFAULT 0;  
 SET i = FLOOR(from_num +RAND()*(to_num -from_num+1))   ;
RETURN i;  
 END$$ 
#假如要删除
#drop function rand_num;

1.4 创建存储过程

创建往emp表中插入数据的存储过程

DELIMITER $$
CREATE PROCEDURE  insert_emp(  START INT ,  max_num INT )
BEGIN  
DECLARE i INT DEFAULT 0;   
#set autocommit =0 把autocommit设置成0  
 SET autocommit = 0;    
 REPEAT  
 SET i = i + 1;  
 INSERT INTO emp (empno, NAME ,age ,deptid ) VALUES ((START+i) ,rand_string(6)   , rand_num(30,50),rand_num(1,10000));  
 UNTIL i = max_num  
 END REPEAT;  
 COMMIT;  
 END$$
#删除
# DELIMITER ;
# drop PROCEDURE insert_emp;

创建往dept表中插入数据的存储过程

#执行存储过程，往dept表添加随机数据
DELIMITER $$
CREATE PROCEDURE `insert_dept`(  max_num INT )
BEGIN  
DECLARE i INT DEFAULT 0;   
 SET autocommit = 0;    
 REPEAT  
 SET i = i + 1;  
 INSERT INTO dept ( deptname,address,ceo ) VALUES (rand_string(8),rand_string(10),rand_num(1,500000));  
 UNTIL i = max_num  
 END REPEAT;  
 COMMIT;  
 END$$
#删除
# DELIMITER ;
# drop PROCEDURE insert_dept;

1.5 调用存储过程

dept

 #执行存储过程，往dept表添加1万条数据
DELIMITER ;
CALL insert_dept(10000); 

emp

#执行存储过程，往emp表添加50万条数据
DELIMITER ;
CALL insert_emp(100000,500000); 

2. 批量删除某个表上的所有索引

2.1 存储过程

DELIMITER $$
CREATE  PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))
BEGIN
       DECLARE done INT DEFAULT 0;
       DECLARE ct INT DEFAULT 0;
       DECLARE _index VARCHAR(200) DEFAULT '';
       DECLARE _cur CURSOR FOR  SELECT   index_name   FROM information_schema.STATISTICS   WHERE table_schema=dbname AND table_name=tablename AND seq_in_index=1 AND    index_name <> 'PRIMARY'  ; # 定义游标
       DECLARE  CONTINUE HANDLER FOR NOT FOUND set done=2 ;      
        OPEN _cur; # 打开游标
        FETCH   _cur INTO _index;
        WHILE  _index <> '' DO 
               SET @str = CONCAT("drop index ",_index," on ",tablename ); 
               PREPARE sql_str FROM @str ;
               EXECUTE  sql_str;
               DEALLOCATE PREPARE sql_str;
               SET _index=''; 
               FETCH   _cur INTO _index; 
        END WHILE;
   CLOSE _cur;
   END$$
/**
1. 查询索引
SELECT   index_name   FROM information_schema.STATISTICS   WHERE table_schema=dbname AND table_name=tablename AND seq_in_index=1 AND    index_name <> 'PRIMARY'  ;

2. 如何循环集合
CURSOR 游标
FETCH xxx INTO xxx

3. 如何让mysql执行一个字符串
PREPARE 预编译 xxx
*/

2.2 执行存储过程

CALL proc_drop_index("dbname","tablename");

3. 单表使用索引及常见索引失效

3.1 案例索引失效

3.1.1 索引优化效果

系统中经常出现的sql语句如下：  
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30  
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4 AND emp.name = 'abcd'

SQL_NO_CACHE表示不使用缓存
索引应该如何建立 ？

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30  

的执行效果：

给age字段创建索引，再查看效果

# 给age字段创建索引
create index idx_age on emp(age);

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4

的执行效果：

创建联合索引：

# 给age和deptid字段创建复合索引
create index idx_age_deptid on emp(age,deptid);

效果：可以到优化的效果很好

同样：给最后一句创建索引后：

# 给age和deptid还有name字段创建复合索引
create index idx_age_deptid_name on emp(age,deptid,name);

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4 AND emp.name = 'abcd'  

3.1.2 最佳左前缀法则

当查询条件去掉deptid时，只命中了一个索引，命中了age字段：

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.name = 'abcd'; 

当查询条件去掉age字段时，一个字段都没有命中：

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.deptId = 4 AND emp.name = 'abcd'; 

最佳左前缀法则：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

3.1.3 不在索引列上做任何操作

不加索引时的两条SQL：

EXPLAIN  SELECT SQL_NO_CACHE * FROM emp WHERE   emp.name  LIKE 'abc%' ;

# LEFT(Str,length)返回具有指定长度的字符串的左边部分。
EXPLAIN   SELECT SQL_NO_CACHE * FROM emp WHERE   LEFT(emp.name,3)  = 'abc';

两个都是全表扫描。

给name字段加上索引：

create index idx_name on emp(name);

第一句明显优化：

第二句无效果：

总结：在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描。

3.1.4 范围查询右边的索引失效

这样一句sql，建立索引后

create index idx_age_deptid_name on emp(age,deptid,name);

EXPLAIN SELECT  SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.deptId>20 AND emp.name = 'abc' ;

只用到了age和deptId字段，name字段失效了。因为在建立索引时，是以age、deptId、name的顺序建立的，如果按照下面的方式建立索引，就不会引起索引失效了：

create index idx_age_name_deptid on emp(age,name,deptid)

重新建立索引后：

总结：存储引擎不能使用索引中范围条件右边的列，即范围查询右边的索引失效。范围查询的字段建立索引时应该放在最后。

3.1.5 不等于(!= 或者<>)的时候无法使用索引

没建索引前：

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name <> 'abc'; 

创建索引：

CREATE INDEX idx_name ON emp(NAME)
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name <> 'abc'; 

虽然有可选索引，但是没有用到索引。
总结：mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描。

3.1.6 is not null 也无法使用索引,但是is null是可以使用索引的

下面两句sql：

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age IS NULL;
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age IS NOT NULL;

给age字段建立索引后：

CREATE INDEX idx_age ON emp(age);

IS NULL的语句可以使用索引：

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age IS NULL;

IS NOT NULL的语句索引失效：

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age IS NOT NULL;

总结：is not null 也无法使用索引,但是is null是可以使用索引的。

3.1.7 like以通配符开头索引失效

没建索引时的SQL：通配符放在后面

EXPLAIN  SELECT SQL_NO_CACHE * FROM emp WHERE   emp.name  LIKE 'abc%' ;

建立索引后：通配符放在后面使用到了索引

create index idx_name on emp(name);

将通配符放在前面时：

EXPLAIN  SELECT SQL_NO_CACHE * FROM emp WHERE   emp.name  LIKE '%abc' ;

索引失效了。
总结：like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作。

3.1.8 字符串不加单引号索引失效

没建索引时的SQL：

EXPLAIN  SELECT SQL_NO_CACHE * FROM emp WHERE   emp.name = 123 ;

建立索引后：依然是全表扫描，索引失效。

create index idx_name on emp(name);

总结：字符串不加单引号索引失效。

3.1.9 总结

假设index(a,b,c)

Where语句	索引是否被使用
where a = 3	Y,使用到a
where a = 3 and b = 5	Y,使用到a，b
where a = 3 and b = 5 and c = 4	Y,使用到a,b,c
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4	N
where a = 3 and c = 5	使用到a， 但是c不可以，b中间断了
where a = 3 and b > 4 and c = 5	使用到a和b， c不能用在范围之后，b断了
where a is null and b is not null	is null 支持索引 但是is not null 不支持,所以 a 可以使用索引,但是 b不可以使用
where a <> 3	不能使用索引
where abs(a) =3	不能使用索引
where a = 3 and b like ‘kk%’ and c = 4	Y,使用到a,b,c
where a = 3 and b like ‘%kk’ and c = 4	Y,只用到a
where a = 3 and b like ‘%kk%’ and c = 4	Y,只用到a
where a = 3 and b like ‘k%kk%’ and c = 4	Y,使用到a,b,c

3.1.10 一般性建议

• 对于单键索引，尽量选择针对当前query过滤性更好的索引
• 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
• 在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引
• 在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面
• 书写sql语句时，尽量避免造成索引失效的情况