MySQL数据优化

select * from student;

delete from student;

SELECT COUNT(1) from student;

SELECT * FROM `student` LIMIT 10000, 10

-- 0.674s
-- 0.033s
-- 0.031s

SELECT * FROM `student` LIMIT 10000, 10;
-- 0.031s
SELECT * FROM `student` LIMIT 10000, 100;
-- 0.032s
SELECT * FROM `student` LIMIT 10000, 1000;
-- 0.032
SELECT * FROM `student` LIMIT 10000, 10000;
-- 0.043s
SELECT * FROM `student` LIMIT 10000, 100000;
-- 0.158s
SELECT * FROM `student` LIMIT 10000, 1000000;
-- 1.584s

-- 总结：从上面结果可以得出结束：偏移量越大，花费时间越长

-- 优化偏移量大问题
-- 采用子查询方式
-- 我们可以先定位偏移位置的 id，然后再查询数据
SELECT * FROM `student` LIMIT 100000, 10;
-- 0.813s

SELECT id FROM `student` LIMIT 100000, 1;
-- 0.046s

SELECT * FROM `student` WHERE id >= (SELECT id FROM `student` LIMIT 100000, 1) LIMIT 10

-- 0.046s

-- 从上面结果得出结论：
-- 第一条花费的时间最大，第三条比第一条稍微好点
-- 子查询使用索引速度更快
-- 缺点：只适用于id递增的情况

-- id非递增的情况可以使用以下写法，但这种缺点是分页查询只能放在子查询里面
-- 注意：某些 mysql 版本不支持在 in 子句中使用 limit，所以采用了多个嵌套select
SELECT * FROM `student` WHERE id IN (SELECT t.id FROM (SELECT id FROM `user_operation_log` LIMIT 10000, 10) AS t)

-- 采用 id 限定方式
-- 这种方法要求更高些，id必须是连续递增，而且还得计算id的范围，然后使用 between，sql如下

SELECT * FROM `student` WHERE id between 100000 AND 100010 LIMIT 100

SELECT * FROM `student` WHERE id >= 100000 LIMIT 100

-- 从结果可以看出这种方式非常快
--
-- 注意：这里的 LIMIT 是限制了条数，没有采用偏移量

-- 优化数据量大问题
-- 返回结果的数据量也会直接影响速度

SELECT * FROM `student` LIMIT 1, 1000000
-- 1.798s

SELECT id FROM `student` LIMIT 1, 1000000

-- 1.005s
-- 从结果可以看出减少不需要的列，查询效率也可以得到明显提升
--
-- 第一条和第三条查询速度差不多，这时候你肯定会吐槽，那我还写那么多字段干啥呢，直接 * 不就完事了
--
-- 注意本人的 MySQL 服务器和客户端是在_同一台机器_上，所以查询数据相差不多，有条件的同学可以测测客户端与MySQL分开
--
-- SELECT * 它不香吗？
--
-- 在这里顺便补充一下为什么要禁止 SELECT *。难道简单无脑，它不香吗？
--
-- 主要两点：
--
-- 用 "SELECT * " 数据库需要解析更多的对象、字段、权限、属性等相关内容，在 SQL 语句复杂，硬解析较多的情况下，会对数据库造成沉重的负担。
-- 增大网络开销，* 有时会误带上如log、IconMD5之类的无用且大文本字段，数据传输size会几何增涨。特别是MySQL和应用程序不在同一台机器，这种开销非常明显。

-- 切换数据库
use bigfile;
-- 如果批量插入程序已存在，先删除
DROP PROCEDURE IF EXISTS BatchInsert;
delimiter $$ -- 定界符
CREATE PROCEDURE BatchInsert ( IN initId INT, IN loop_counts INT ) -- 假设initId=5, loop_counts循环数=100
BEGIN
DECLARE Var INT;
DECLARE ID INT;
SET Var = 0;
SET ID = initId;
SET autoCommit = 0; -- 关闭自动提交事务，提高插入效率
WHILE var < loop_counts do -- 相当于fori(i=0;i<100;i++)，从0开始，到100结束，循环插入学生数据100条
insert into student (user_no, user_name, score, create_time, update_time, remark)
VALUES
(
CONCAT('学号', ID),
CONCAT( '姓名', ID ),
floor( 1 + rand()* 100 ),
date_add( '2020-01-01 11:29:00', INTERVAL round( rand() * 1000 + 1 ) DAY ), -- interval间隔范围：（（0，1] * 1000 + 1） 1-1000以内随机间隔多少天
date_add( '2020-01-01 11:29:00', INTERVAL round( rand() * 1000 + 1 ) DAY ),
CONCAT ( "备注", ID ));
SET ID = ID + 1;-- id从5开始插入
SET Var = Var + 1;
END WHILE;
COMMIT;-- 提交
END $$;-- 结束 --只有收到“$$”才认为指令结束可以执行;默认情况下，delimiter是分号;，遇到分号就执行

-- 测试
delimiter ;
call BatchInsert(1, 1000000); -- 从id=1开始，插入11条

delimiter ;
call BatchInsert(11, 100000000); -- 从id=12开始，插入9条