mysql 实用语句

-- 查询内存大小
SELECT
    TABLE_NAME,
    concat(
        TRUNCATE (data_length / 1024 / 1024, 2),
        ' MB'
    ) AS data_size,
    concat(
        TRUNCATE (index_length / 1024 / 1024, 2),
        ' MB'
    ) AS index_size
FROM
    information_schema. TABLES
WHERE
    TABLE_SCHEMA = 'demo-test' --数据库名称

> 单循环插入数据

CREATE PROCEDURE test6_insert()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i<10
DO
INSERT INTO test_2(id, user_name,  create_time) VALUES (i, CONCAT('mayun',i),FROM_UNIXTIME(
        UNIX_TIMESTAMP('2010-04-30 14:53:27') + FLOOR(0 + (RAND() * 63072000))
    ));
SET i=i+1;
END WHILE ;
commit;
END;;
CALL test6_insert();

//test_2 为相关表名称

 

> 双循环插入数据

DROP PROCEDURE IF EXISTS pre8;
create procedure pre8()
BEGIN
DECLARE i int;
DECLARE j int;
set i = 2022;
WHILE i < 2023 DO

set j = 1;
    WHILE j <13 DO
        INSERT INTO month(previous_node, next_node, valid,time) VALUES (i, j,1,1);
        SET j= j+1;

END WHILE;
SET i = i+1;
END WHILE;

commit;

END;;

CALL pre8(); 

-- 100万数据，占用空间53.57MB，加个普通索引，索引空间25.56MB

-- 限制条数查询 0.003s
explain select * from t_user LIMIT 1000,10
--  0.005s
select * from t_user LIMIT 10000,10
-- 0.031s
select * from t_user LIMIT 100000,10
-- 0.291s
select * from t_user LIMIT 1000000,10
-- 子查询优化 0.160s
select * from t_user WHERE id >= (SELECT id FROM t_user ORDER BY id LIMIT 1000000,1) LIMIT 10
-- 使用JOIN分页 0.158s
select * from t_user t1 INNER join (SELECT id FROM t_user ORDER BY id LIMIT 9000000,10) t2 on t1.id = t2.id;
-- 使用前一次查询的最大ID 0.003s
select * from t_user where id > 999990 ORDER BY id LIMIT 10
-- 没索引优化的情况下的查询效率:0.384s,加了索引后：0.006s
select * from t_user t where user_name like 'mayun100%'
-- OR索引失效：1.545s
select * from t_user t where t.user_name like 'mayun100%' OR t.address = "浙江杭州"
-- like '%xx' %在左边时索引失效:0.521s
select * from t_user t where t.user_name like '%mayun100%'