mysql8.0索引优化与查询优化(一)

都有哪些纬度可以进行数据库调优？简言之：
索引失效、没有充分利用所以——索引建立
关联查询太多JOIN（设计缺陷或不得已的需求）——SQL优化
服务器调优及各个参数设置（缓冲、 线程数）——调整my.cnf
数据过多——分库分表
关于数据库调优的知识点非常分散，不同DBMS，不同的公司，不同的职位，不同的项目遇到的问题都不尽相同。

虽然SQL查询优化的技术很多，但是大体方向上完全可以分为 物理查询优化和逻辑查询优化两大块。
物理查询优化是通过索引和表连接方式 等技术来进行优化，这里重点需要掌握索引的使用
逻辑查询优化就是通过SQL等价变换 提升查询效率，直白一点来讲就是，换一种执行效率更高的查询写法

1、数据准备

学员表插50万条， 班级表插1万条。

步骤1：建表
#班级表
CREATE TABLE `class` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`className` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
`monitor` INT NULL ,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

#学员表
CREATE TABLE `student` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`stuno` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`classId` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
#CONSTRAINT `fk_class_id` FOREIGN KEY (`classId`) REFERENCES `t_class` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

步骤2：设置参数
命令开启：允许创建函数设置：
set global log_bin_trust_function_creators=1;   
# 不加global只是当前窗口有效。

步骤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 //
DELIMITER ;

随机产生班级编号
#用于随机产生多少到多少的编号
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 //
DELIMITER ;

步骤4：创建存储过程
创建往stu表中插入数据的存储过程
#创建往stu表中插入数据的存储过程
DELIMITER //
CREATE PROCEDURE insert_stu(  START INT , max_num INT )
BEGIN 
	DECLARE i INT DEFAULT 0; 
	SET autocommit = 0;   #设置手动提交事务
	REPEAT  #循环
	  SET i = i + 1;  #赋值
	  INSERT INTO student (stuno, name ,age ,classId ) VALUES ((START+i),rand_string(6),rand_num(1,50),rand_num(1,1000)); 
	  UNTIL i = max_num 
	END REPEAT; 
	COMMIT;  #提交事务
END //
DELIMITER ;

创建往class表中插入数据的存储过程
#执行存储过程，往class表添加随机数据
DELIMITER //
CREATE PROCEDURE `insert_class`( max_num INT )
BEGIN 
	DECLARE i INT DEFAULT 0; 
	SET autocommit = 0;  
	REPEAT 
	  SET i = i + 1; 
	  INSERT INTO class ( classname,address,monitor ) VALUES (rand_string(8),rand_string(10),rand_num(1,100000)); 
	  UNTIL i = max_num 
	END REPEAT; 
	COMMIT;
END //
DELIMITER ;

步骤5：调用存储过程
往class表添加1万条数据
#执行存储过程，往class表添加1万条数据 
CALL insert_class(10000);

往stu表添加50万条数据,这个时间会稍微有点长，请耐心等待哟~
#执行存储过程，往stu表添加80万条数据 
CALL insert_stu(100000,800000);

查询下数据是否插入成功
SELECT COUNT(*) FROM class;
SELECT COUNT(*) FROM student;

步骤6：删除某表上的索引
创建删除索引存储过程。这是为了方便我们的学习，因为我们在演示某个索引的效果时，可能需要删除其它索引，如果需要一个个手工删除，就太费劲了。
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=1来控制游标的结束
  DECLARE  CONTINUE HANDLER FOR NOT FOUND set done=2 ;   
  #若没有数据返回,程序继续,并将变量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 //
DELIMITER ;

执行存储过程
CALL proc_drop_index("dbname","tablename");

2、索引失效案例

2.1、全值匹配我最爱

全值匹配可以充分的利用组合索引~

系统中经常出现的sql语句如下，当没有建立索引时，possible_keys和key都为NULL
# SQL_NO_CACHE表示不使用查询缓存。
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';
此时执行SQL，数据查询速度会比较慢，耗时0.12s

mysql> SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';
Empty set, 1 warning (0.12 sec)
接下来我们建立索引

CREATE INDEX idx_age ON student(age);
CREATE INDEX idx_age_classid ON student(age,classId);
CREATE INDEX idx_age_classid_name ON student(age,classId,NAME);