【重难点】【MySQL 07】分页查询优化、数据库连接池的理解和配置

313YPHU3

已于 2022-04-23 12:55:14 修改

阅读量188

点赞数

分类专栏：重难点文章标签： MySQL

于 2021-10-09 09:07:43 首次发布

本文链接：https://blog.csdn.net/qq_45593575/article/details/120532410

版权

重难点专栏收录该内容

58 篇文章 5 订阅

订阅专栏

【重难点】【MySQL 07】分页查询优化、数据库连接池的理解和优化

文章目录

【重难点】【MySQL 07】分页查询优化、数据库连接池的理解和优化
一、分页查询优化
- 1.测试用例
- 2.控制扫描的行数
二、数据库连接池的理解和配置

一、分页查询优化

1.测试用例

创建数据表

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
	`id` bigint(20) NOT NULL AUTO_INCREMENT,
	`u_name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户名',
	`u_password` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '密码',
	`u_mail` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '邮箱',
	`u_phone` varchar(11) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '手机',
	`u_sex` tinyint(1) DEFAULT NULL COMMENT '性别',
	`u_headImg` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '头像',
	PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4762599 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

添加数据

在数据表中添加 100w 张三、100w 李四、100w 王五、200w 赵六的用户，以赵六为例：

DROP PROCEDURE IF EXISTS proc_initData;
DELIMITER $
CREATE PROCEDURE proc_initData()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i<=2000000 DO
        insert into `user` (u_name,u_password,u_mail,u_phone,u_sex,u_headImg) values('赵六','000000','zhaoliu@163.com','18800000000',0,'oss.file.com/images/zhaoliu.png');
        SET i = i+1;
    END WHILE;
END $
CALL proc_initData();

查询过慢的原因

数据表和记录都已经准备好了，现在我们就需要来排查为什么分页查询页码越靠后查询速度越慢？我们先来看一个普通的分页查询：

select * from user order by id desc limit 100,10;

这是查询第十页的数据，我相信大部分的人在写分页查询的时候都是这么写的，其中 100：偏移量，意思就是说从哪里之后就是我需要的数据，10：表示需要查询多少条记录，这个就是 MySQL 的分页查询语法，你能看出这条 SQL 存在什么问题吗？乍一看好像没啥问题，真的是这样吗？我们来看几个例子

偏移量=0

select * from user order by id desc limit 0,10

查询时间：0.001s

偏移量=1000

select * from user order by id desc limit 1000,10

查询时间：0.001s

偏移量=10000

select * from user order by id desc limit 10000,10

查询时间：0.014s

偏移量=4000000

select * from user order by id desc limit 4000000,10

查询时间：2.046s

从以上几个例子中我们可以得出结论：偏移量越大，查询的时间就越久

limit 分页的原理

为什么偏移量越大，查询的时间越久？我们不妨猜测一下，函数、扫描记录过多等等都会影响查询的速度，很显然这里我们并没有使用函数，所以这会不会是扫描的记录过多呢？

如果我们使用 EXPLAIN 查看上面四条语句的执行详情就会发现，它们扫描的行数分别为 10、1010、10010、4000010。假如你现在要查询的偏移量为 100w，那么 limit 会扫描 1000010 行数据，然后丢钱前 100w 行数据，留下最后 10 行，返回给我们。所以说我们只需要控制扫描的行数，查询的速度自然就快了，接下来我将介绍如何控制扫描的行数

2.控制扫描的行数

最大 id 查询法

我们给查询添加一个 where 条件

select * from user where id > 4000000 limit 10;

查询时间为 0.009s

对比一下直接使用 limit 4000000,10，效率天差地别。但是这种方式比较局限，只能适用于自增主键，使用 uuid 生成的主键不适用

BETWEEN … AND

select * from user where id BETWEEN 4000001 AND 4000010

查询时间为 0.001s

这种方式除了需要主键为自增主键外，还要求 id 是连续的

limit id

select * from user where id > (select id from user limit 4000000,1) limit 10;

查询时间为 0.726s

这种查询方式就是先扫描 4000010 行，但是只取出 id，然后再查询 id 大于这个值的前 10 条数据，这样虽然也是扫描了 400 多万行记录，由于 id 是主键，使用索引查询单个 id 速度会快很多

延迟关联

延迟关联让 MySQL 扫描尽可能少的记录，获取到需要访问的记录后再根据关联回到原表查询需要的所有列

select * from user INNER JOIN( select id from user limit 4000000,10 ) as a USING(id)

查询时间为 0.720s

我们可以看到这种查看的方式和第三种的效率差不多，但是当字段比较多，类型的长度比较长的时候，这种方式相较之下是有优势的

分表查询

MySQL 推荐一张表的存储不要超过 500w 数据，查询 400w 不到 1s 对于一般的查询来说已经可以了，如果还要更快的化，建议使用分表存储，分表又分为水平分表和垂直分表

先来看看水平分表，假如一张表的原始数据有 1000w 条数据，我可以分为三张表存储，每张表存储 300 多万条数据，这样查询的时候压力就会小很多，并且效率也更高，那么问题来了，如何实现水平分表呢？我们可以借助 MyCat 之类的中间件。当然，也可以自己手写分表，但是自己手写分表的时候需要注意 id 重复以及如何定义搭配当前 id 在哪张表中，算法推荐使用 hash 算法

再来看看垂直分表，假如一张表的记录有 100w，一般来说查询速度不会太慢，但是由于这张表的字段过多，而且还有很多 text 类型的字段，这个时候我们可以将占用空间较小的字段分在一张表，占用空间较大的字段分在另一张表，两张表相互关联。这样，查询速度会快很多

冷热表

大家应该都用过掌上银行，在查询账单的时候会发现只能查询近几个月的数据，更早的数据需要去柜台查询，这里就用到了冷热表的设计思想

我们新建两张一模一样的表，A 表存放近三个月的记录，B 表存放更早的记录。用户产生的新记录存放在 A 表，可以在每天凌晨的时候定时扫描 A 表，只要记录是三个月之前的，我们就可以将记录迁移到 B 表，对于用户来说，查询近三个月的数据时他们是比较敏感的，三个月之前的的数据他们查询的可能并不多，所以这样的设计是完全合理的

索引

这一点相信大家都知道，添加索引可以提高查询效率，如果我们的分页查询牵扯到条件的话，我们可以给条件添加索引，数据库会维护一张对应的索引表，查询的时候会先查询索引表，根据索引表返回的记录直接查询记录表，这样也减少了扫描的行数

二、数据库连接池的理解和配置

1.简介

什么是数据库连接池

创建数据库连接是一个很耗时的操作，也容易对数据库造成安全隐患。所以，在程序初始化的时候，集中创建多个数据库连接，并把它们集中管理，供程序使用，可以保证较快的数据库读写速度，并且更加安全可靠

数据库连接池（Connection pooling）是程序启动时建立足够的数据库连接，并将这些连接组成一个连接池，由程序动态地对池中的连接进行申请、使用、释放

为什么需要连接池

任何数据库的访问首先都需要建立数据库连接。这是一个复杂、缓慢的处理。牵涉到通信建立（包括 TCP 的三次握手）、认证、授权、资源的初始化和分配等一系列任务。在应用启动时预先建立一些数据库连接，应用程序使用已有的连接可以极大地提高响应速度。另外，WEB 服务应用当客户很多时，有很多线程，连接数目过多以及频繁创建/删除连接也会影响数据库的性能

连接池带来的好处

节省了创建数据库连接的时间，通常这个时间大大超过处理数据访问请求的时间
统一管理数据库请求连接，避免了过多连接或频繁创建/删除连接带来的性能问题
监控数据库连接的运行状态和错误报告，减少应用服务的这部分代码

2.实现原理

在应用开始时创建一组数据库的连接，也可以动态创建并复用已有连接。这些连接被存储到一个共享的资源数据结构，称为连接池（典型的生产者-消费者并发模型）

连接逻辑

每个线程在需要访问数据库时借用（borrow）一个连接，使用完成则释放（release）连接回到连接池供其他线程使用。比较好的线程池构件会有两个参数动态控制线程池的大小：最小数量和最大数量

最小数量指即使负载很轻，也保持一个最小数目的数据库连接，以备不时之需。当同时访问数据库的线程数超过最小数量时，则动态创建更多连接

最大数量是允许的最大数据库连接数量，当最大数目的连接都在使用而且有新的线程需要访问数据库时，则新的线程会被阻塞，直到有连接被释放回连接池

当负载变低，池里的连接数目超过最小数目，而且只有低于或等于最小数目的连接被使用时，超过最小数目的连接会被关闭和删除，以此节省系统资源

连接泄露

编码逻辑错误或者释放连接代码没有放到 finally 代码块都会导致连接池资源枯竭从而造成系统变慢甚至完全阻塞的情况。类似于内存泄漏，因而也叫连接泄漏

连接池系统架构

连接池只是给业务应用提供已建立的连接，所有的访问请求都通过连接转发到后台数据库服务器

具体来说，连接池是两个线程池的中间通道，可以看成下面的结构：

在这里插入图片描述

上图中，连接池和应用服务线程池在同一个进程里面，每个访问数据库的应用服务进程都有自己的线程池和对应的数据库连接池。数据库服务器可能需要处理来自一个或多个服务器的多个应用服务进程内的数据库连接池的数据访问请求

连接池的运行机制

程序初始化时创建连接池
使用时向连接池申请可用连接
使用完毕，将连接返还给连接池
程序退出时，断开所有连接，并释放资源

3.配置连接池

配置考虑因素

当前连接 DB 的规模
并发情况
DB 的响应时间

作为应用服务和数据库的桥梁，连接池参数配置的目标是全局优化，具体的优化目的有四个：

尽可能满足应用服务的并发数据库访问
不让数据库服务器过载
能发现用了不还造成的死锁
不浪费系统资源

不浪费系统资源是指配置过大的连接池会浪费应用服务器的系统资源，包括内存、网络端口、同步信号等。同时线程池的重启和操作都会响应变慢。不过应用端连接池的开销不是很大，资源的浪费通常不是太大问题

配置参数解读

初始化连接：可考虑设置为 3 个连接。对于 DB 规模特别大的情况下，可考虑设置为 1 个，避免启动时间过长
最小连接：可考虑该值的设置和初始化连接保持一致
最大连接：对于 DB 规模较大的情况下，最大连接不要设置过大，避免本地维护的量过大。如果数据源的并发数较高，可考虑增大最大连接数
获取连接的超时时间：如果连接全部被占用，需要等待的时间，可以根据当前系统的响应时间容忍度来判定
获取连接和释放连接心跳检测：建议全部关闭，否则每个数据库访问指令会对数据库产生额外的两条心跳检测的指令，增加数据库的负载。连接有效性的检查可以使用后台空闲连接检查
连接有效性检测时间：该值需要结合数据库的 wait_timeout,interactive_timeout 值进行设置。假如数据库为 120s，则心跳检测时间在 120s 以内越大越好。如果太小，心跳检测时间会比较频繁。建议设置为 90s
最大空闲时间：如果连接超过该时间没有使用过，则会 close。该值不要设置得太小，避免频繁地建立连接和关闭连接；也不要设置得太大，导致一直无法关闭
心跳检查的 SQL 语句：尽量使用 ping 命令，ping 的性能比查询语句高
prepareStatement 缓存：可以根据自己的业务来判断是否要开启，开启后对性能的影响取决于具体业务和并发情况
连接使用超时：业务拿到一个连接，如果超过指定的时间未归还，是否把该连接回收