07 | 索引优化与查询优化-优快云博客

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：设置参数

命令开启：允许创建函数设置

# 不加global只是当前窗口有效。
set global log_bin_trust_function_creators=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 // 
DELIMITER;

#假如要删除
#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 // 
DELIMITER;

#假如要删除
#drop function rand_num;

步骤4：创建存储过程

-- 创建往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;

-- 假如要删除
-- drop PROCEDURE insert_stu;

创建往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;

#假如要删除
#drop PROCEDURE insert_class;

步骤5：调用存储过程

-- class
-- 执行存储过程，往class表添加1万条数据 
CALL insert_class(10000);

-- stu
-- 执行存储过程，往class表添加1万条数据 
CALL insert_class(10000);

步骤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 全值匹配我最爱

2.2 最佳左前缀法则

拓展：Alibaba《Java开发手册》

索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

2.3 主键插入排序

2.11 数据库和表的字符集统一使用utf8mb4

统一使用utf8mb4（5.5.3版本以上支持）兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同的字符集进行比较前需要进行转换会造成索引失效。