说出以下语法使用索引的情况
假设建立复合索引 (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;
