Mysql之性能优化浅析

最新推荐文章于 2024-10-22 14:48:15 发布

ldcaws

最新推荐文章于 2024-10-22 14:48:15 发布

阅读量113

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/leijie0322/article/details/117218183

版权

mysql 专栏收录该内容

28 篇文章 1 订阅

订阅专栏

本文探讨了在MySQL数据库中面对大量数据时，如何通过创建索引、合理使用LIMIT偏移量和实施限流策略来提高分页查询效率。实例演示了如何通过调整查询方式和优化策略来显著提升查询速度，适用于大规模数据场景。

摘要由CSDN通过智能技术生成

现在，越来越多的项目中数据库采用mysql，在数据量上来后就会遇到海量数据的查询速度慢的问题。

举个栗子，mysql数据库，student表，主键自增，在select分页查询时，查询前几页速度很快，如 limit 100，10；但数据量很大，查询后面的页时就会越来越慢，

如，select * from student where name = '曹操' order by no limit 1000 000,10; 查询慢时因为limit后面的偏移量太大所致，需要先扫描出1000 000条(100w)数据，然后丢弃前面的大部分数据，只返回10条数据。

数据模拟方法：

创建表
drop table if EXISTS student;
create table student(
    id int unsigned primary key auto_increment,
    no mediumint unsigned not null default 0,
    name varchar(20) not null default "",
    mgr mediumint unsigned not null default 0,
    hiredate datetime not null
);

产生随机字符串的函数
DELIMITER $
drop FUNCTION if EXISTS rand_string;
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
    DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmlopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    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;

建立存储过程：往表中插入数据
DELIMITER $
drop PROCEDURE if EXISTS insert_student;
CREATE PROCEDURE insert_student(IN START INT(10),IN max_num INT(10))
BEGIN
    DECLARE i INT DEFAULT 0;
    /*set autocommit =0 把autocommit设置成0，把默认提交关闭*/
    SET autocommit = 0;
    REPEAT
    SET i = i + 1;
    INSERT INTO student(no,name,mgr,hiredate) VALUES ((START+i),rand_string(6),0001,now());
    UNTIL i = max_num
    END REPEAT;
    COMMIT;
END $
DELIMITER;
/*插入500W条数据*/
call insert_student(0,5000000);

建立关键字段的索引:排序、条件
CREATE INDEX idx_student_id ON student(id);
CREATE INDEX idx_student_no ON student(no);

数据测试：

select id,no,name,hiredate from student limit 100,10;
 
select id,no,name,hiredate from student limit 900000,10;

优化：因为有主键id，且在上面建了索引，可以先在索引树中找到开始位置的id，然后再根据id查询

select id,no,name,hiredate from student where id >= (select id from student order by id limit 100,1) limit 10;

select id,no,name,hiredate from student where id >= (select id from student order by id limit 900000,1) limit 10;

测试结果，查询速度得到提升，数据量越大越明显。

在遇到海量数据分页查询时也可以应用奖及策略，对偏移量进行限流，若大于某个值，就返回友情提示或空数据。