MySQL-索引相关面试题

说出以下语法使用索引的情况

假设建立复合索引 (a、b、c)，请说出以下条件是否使用到了索引及使用情况

语法	结果
where a = 4	使用到了索引 a
where a = 4 and b = 6	使用到了索引 a、b
where a = 4 and c = 5 and b = 6	使用到了 a、b、c
where b = 4 or b = 5	没有使用到索引
where a = 4 and c = 6	使用到了索引 a
where a = 4 and b > 5 and c = 6	使用到索引 a、b
where a = 4 and b like ‘test%’ and c = 4	使用到了 a、b kk% 相当于范围
where a = 4 order by b,c	使用到了 a，不会有 filesort
where b = 5 order by a	没用到索引，会有 filesort
where b = 5 order by c	没有索引，会有 filesort
where a = 5 group by c,b	使用到了索引 a, 造成 Using temporary

大批量数据时分页操作如何优化

批量插入数据

建立表

DROP TABLE
IF EXISTS `testtestemployee`;

CREATE TABLE `testtestemployee` (
	`id` INT (11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR (20) DEFAULT NULL,
	`dep_id` INT (11) DEFAULT NULL,
	`age` INT (11) DEFAULT NULL,
	`salary` DECIMAL (10, 2) DEFAULT NULL,
	`cus_id` INT (11) DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE = INNODB AUTO_INCREMENT = 109 DEFAULT CHARSET = utf8;

随机生成字符串

#随机生成一个指定个数的字符串
delimiter $$
CREATE FUNCTION rand_str (n INT) RETURNS VARCHAR (255)
BEGIN
	#声明一个str 包含52个字母
DECLARE str VARCHAR (100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ' ; #记录当前是第几个
DECLARE i INT DEFAULT 0 ; #生成的结果
DECLARE res_str VARCHAR (255) DEFAULT '' ;
WHILE i < n DO

SET res_str = CONCAT(
	res_str,
	substr(str, floor(1 + RAND() * 52), 1)
) ;
SET i = i + 1 ;
END
WHILE ; RETURN res_str ;
END$$ 
delimiter ;

SET GLOBAL log_bin_trust_function_creators = TRUE

批量插入数据

delimiter $$
CREATE PROCEDURE insert_emp (IN max_num INT)
BEGIN

DECLARE i INT DEFAULT 0 ;
SET autocommit = 0 ;
REPEAT

SET i = i + 1 ; INSERT INTO testtestemployee (
	NAME,
	dep_id,
	age,
	salary,
	cus_id
)
VALUES
	(
		rand_str (5),
		floor(1 + rand() * 10),
		floor(20 + rand() * 10),
		floor(2000 + rand() * 10),
		floor(1 + rand() * 10)
	) ; UNTIL i = max_num
END
REPEAT
	; COMMIT ;
END$$
delimiter ;

传统分析查询

使用 limit 随着 offset 增大，查询的速度会越来越慢，会把前面的数据都取出，找到对应位置

优化后分页查询

使用子查询优化

SELECT
	*
FROM
	testemployee e
INNER JOIN (
	SELECT
		id
	FROM
		testemployee
	LIMIT 500000,
	10
) et ON e.id = et.id

SELECT
	*
FROM
	testemployee
WHERE
	id >= (
		SELECT
			id
		FROM
			testemployee
		LIMIT 500000,
		1
	)
LIMIT 10

使用 id 限定优化

记录上一页最大的 id号 使用范围查询，限制是只能使用于明确知道 id 的情况，不过一般建立表的时候，都会添加基本的id字段，这为分页查询带来很多便利

SELECT
	*
FROM
	testemployee
WHERE
	id BETWEEN 1000000
AND 1000100
LIMIT 100;

SELECT
	*
FROM
	testtestemployee
WHERE
	id >= 1000001
LIMIT 100;