初学者的SQL耗时问题咨询

img


<select id="listUser" resultType="hexie.practice.learn.elasticsearch.entity.User">
        SELECT id, user_name, mobile, address FROM user LIMIT 0,#{size}
    select>


@Autowired
    private UserMapper userMapper;
    @Test
    void test() {
        long l = System.currentTimeMillis();
        List users = userMapper.listUser(7000000);
        long l1 = System.currentTimeMillis();
        System.out.println("耗时: " + (l1 - l) + "ms");
        System.out.println(users.size());
        // 215ms   247ms   292ms    490ms    2883ms   18500ms   62423ms
        //  100    1000    10000   100000   1000000   5000000   7000000
    }

我有一张表user, 里面有一千万个数据, 没加索引, 查询100个数据的时候花费了215ms, 1000个数据247ms, 10000个数据292ms, 如上所示, 为什么花费的时间不是简单的后面加个0?
而且在十万数据量之前, 都是按照差不多1.2的比例增长, 但从1百万开始就是6倍增长, 5百万和7百万的用时差了3.4倍, 为什么会出现这样的情况?

img

直接在Navicat里查的话, 1千万数据都不用8秒, 为什么放到代码里就要花费这么长时间?

这个问题原因比较复杂，解析基本如下：
1、你的表没有索引，查询条件也仅是记录限制，数据库的查询就是顺序读取记录。查询时间=读数据时间（从磁盘）+数据筛选时间，你这个单表顺序读，主要是读磁盘时间。
2、10-100万数据查询时间变化：数据库一般访问磁盘数据时是按页进行，一页大约多少行数据可以计算。因此查10、100、1000...都是读一页，因此耗费时间差不多。当超过一定行的时间后，就要读多页了。如10万到100万出现明显变化，可能10万仅1页，100万可能6-7页。
3、500到700万变化：这涉及到数据库缓存够不够，可能500万都读到内存处理即可，而700万时内存不够了，就要把结果缓存到磁盘上，因此时间会陡然增加。
4、最后说下程序时间比navicat长很多：navicat给出的响应时间就是发起数据库查询到形成结果所用时间。而程序中，还需要把结果一一映射成对象。当数据量很大时，生成对象是很耗时的，同时可能由于内存不够，还得用序列化到磁盘保存，这就更耗时。其实数据量不大，两者差异是不明显的，这就是大数据带来的挑战。

没有人会一下查这么多数据出来，都是分片

1、磁盘IO不同，随着数据的增长，那需要的磁盘IO次数也会增加，一次查询所需要的时间也会增加，并不是单纯的后面加个0
2、放在代码里，JDBC连接是负责和MYSQL通信的，会把sql语句发给MYSQL执行，可以看到MYSQL中，也会有线程获取到JDBC连接中的sql语句，然后去执行。
a.线程会把sql语句交给MYSQL中的sql接口，sql接口会转发给sql解析器去解析，比如，sql解析器会把一条sql语句中的表名称、WHERE关键字后面的查询条件、以及具体查询哪些字段等信息都解析出来，然后把解析好的信息交给查询优化器处理。
b.查询优化器会根据sql解析器解析好的sql语句信息，选择一个效率最高的处理方案，来作为执行sql语句的执行计划，然后转交给执行器去执行。
c.而执行器会调用MYSQL的存储引擎，这里我们用InnoDB存储引擎举例，InnoDB存储引擎中的接口就会来执行sql语句
所以花费时间更长。
参考：https://blog.csdn.net/qq_42046105/article/details/127286879

不知道你这个问题是否已经解决, 如果还没有解决的话:
  • 以下回答来自chatgpt:

    针对查询时间增加的问题，有以下可能的原因：

    1. 数据量增加，查询的时间也会增加，但不是简单的后面加个0，查询时间会呈现指数级别增加，并且不同数据量范围内增加的比例不一定相同。
    2. 数据库的索引不够优化，可能导致查询效率低下，建议对查询频率高的字段添加索引。
    3. 使用了不当的查询语句，可能导致查询效率低下，建议针对具体的查询需求编写优化过的 SQL 语句。
    4. 服务器配置不够强大，可能导致查询的时间增加，建议优化服务器配置，提高运行效率。

    针对使用代码进行查询时间非常长的问题，可能的原因有以下几点：

    1. 代码实现不够高效，可能存在效率低下的算法或者数据结构，建议优化代码。
    2. 网络通信问题，可以通过网络延迟和数据传输的带宽限制来导致查询效率降低，可以优化网络配置。
    3. 代码中未使用连接池，导致频繁地进行连接和关闭连接，建议使用连接池优化连接管理。
    4. 数据库设计问题，存在无效的数据查询，如用具体值而不是索引查询数据，建议优化数据库设计。

    针对以上可能的原因，可以采取以下措施进行优化：

    1. 对数据库建立索引，并对查询字段创建合适的索引，优化查询效率。
    2. 使用合适的查询语句，如针对查询范围不要使用模糊查询，避免全表扫描。
    3. 使用缓存技术，上下文缓存，有限缓存等，减少对数据库的访问次数。
    4. 增加服务器配置，扩容服务器等。
    5. 采用连接池，将一些复杂的操作与数据库的连接分开，优化查询效率。
    6. 引入缓存层，将数据存储在缓存中，避免频繁地查询数据库。

    同时，可以使用一些性能优化工具，如数据库性能监控工具、网络监控工具、线程监控工具等，进行数据性能诊断以及优化。


如果你已经解决了该问题, 非常希望你能够分享一下解决方案, 写成博客, 将相关链接放在评论区, 以帮助更多的人 ^-^