数量限制 - MySql总记录数问题

Question

现有问题如下表格： 怎么快速统计到匹配的结果有多少条记录呢？ 问题描述：因为分页的原因，我要知道总记录条数，统计的方式为常规方式，直接count后面加条件统计的。相关索引也已经加上。带入查询条件后，上面有...

阿神 · Answer

The idea is to avoid actually retrieving the entire table (only get results from the index, index-only scan)

First of all, how to write count? count(*) and count(列) are different
Create appropriate indexes and force index use if necessary (select count() FORCE INDEX)

For details, see http://dev.mysql.com/doc/refman/5.7/en/how-to-avoid-table-scan.html

伊谢尔伦 · Answer

If conditions are used to count the total number of rows, it will be very slow.

There seems to be no good solution. If the data does not change frequently, you can use caching

I once struggled with the need to count the total number of rows for paging

The solution is stupid and funny

Because there are not many filtering conditions

So I added a trigger to this table and created a table specifically to count the total number of rows

Add, update and delete the table will be triggered. After analyzing the conditions, the corresponding fields of the statistical table will be accumulated directly

When querying, you can directly read the corresponding fields in the statistical table. The accuracy is no problem, but the performance has not been tested

However, there seems to be no slow query after the table has more than 100,000 data

阿神 · Answer

1. You are right. The method in the reference URL you gave does not work for conditional queries.
2. This typical application does need to use the same condition count once, and then fetch the corresponding data according to paging. You're doing the right thing.
3. You have added related indexes, but it will still be slow when the amount of data is large---your index may not be working. The question that needs to be explored is why your index is not working, or how to optimize your SQL.
3.1 Your conditions are 5. Department, owner, and intention type should be precise queries with a single value (in theory, multiple selections are more user-friendly); phone numbers and customer names should be fuzzy queries with a single value. And all conditions may be blank. Blank or unchecked theoretically means "all". Therefore, your SQL formation process may be as follows (pseudocode):

string sql_count = "select count(id) from my_table where 1=1 "; 
if (部门 != "") {
    sql_count = sql_count + " and 部门=" + 部门; 
}
if (归属人 != "") {
    sql_count = sql_count + " and 归属人=" + 归属人; 
}
if (意向类型 != "") {
    sql_count = sql_count + " and 意向类型=" + 意向类型; 
}
if (电话号码 != "") {
    //这里可优化，判断电话号码位数、或以0开头（区号）、或以135/136/137/138/...开头则修改为精确查询
    //或>=查询（如>'135' and <'1359999999999999'
    sql_count = sql_count + " and 电话号码 like '%" + 电话号码 + "%' "; 
}
if (客户姓名 != "") {
    sql_count = sql_count + " and 客户姓名 like '%" + 客户姓名 + "%' "; 
}

int count = query(sql_count); ...

It can be seen that SQL is more complex, and if it is a fuzzy query, it is generally not possible to optimize the use of indexes (except in special cases).
If a combined index is created for the three precise query fields, the use effect will also be affected by the field order restrictions of the combined index.
So, your index may not be working. . . You can check the query plan, which should be available in general databases.
See whether an index is used (under different conditions) or a full table scan.
Conclusion: Your index is not working, so the speed is still slow even with large amounts of data

3.2 How big is your data volume? How slow is it?

从你的图上看，应该是测试数据，5000多条。对任何数据库来说，5000多笔数据应该算不上是大数据。那么，你的查询到底是多“慢”呢？几十毫秒？几百ms? 1s? 5s以上？
一般来说，这种应用让你“感觉”慢，应该是点下按钮后，到页面加载完成，可能花了5秒以上？不知道是不是这样，这段时间里，可能系统做了许多事。。。执行2次SQL，然后DB与应用服务器交互、应用代码执行（java?php?...)、WEB服务器到客户端。。。所以，到底是哪里慢？

3.3 The laziest and most effective way to deal with performance problems: add hardware.

假设你的慢确实是DB引起，那么你的DB是运行在什么环境上？什么数据库，多大数据？多少表？多少用户？负载多大？内存多少？磁盘是什么系统？不知道的话，直接加内存、换磁盘陈列、加CPU。。。如果是开发环境建议用虚拟机，调整起来非常方便。这一方法也非常有效。

3.4 Actually there is no more. What development needs to do is to clarify the content in 3.2. Find another DBA to check. How much data is in what environment, and how much concurrency and IO can be accommodated is a very complicated matter. You can use hardware or set up multiple servers for master-slave/load balancing/read-write separation, etc. (many high-level terms) . If you know more, you can post more data.

3.5 The WEB system can improve response and reduce the load on DB through caching.

The above is for reference.