The Mybatis pagehelper plug-in (pagehelper) has a large amount of data

🐉 Wuhu ~ optimize the query speed

⚠ Paging queries are slow in projects using the Mybatis paging plug-in, but fast in the database?

  1. Mybatis pagination plugin (Mybatis pagination plugin, Mybatis pagination plugin, Mybatis pagination plugin) More than 2 s

  2. Finally print the SQL

    🗨 speechless… I checked it again. No wonder it took so long

    select count(*) from ("I double-checked the SQL in here.") tab 
Copy the code

  3. Solution 💡

    From version 5.0.4 of the Mybatis paging plug-in, custom Count methods are supported to replace the old Count method

    Official documents:

Implementation steps:

  1. Start by defining a Count method in the Mapper layer

    / * * *@author yeqian
 * @param
 * @return
 * @DescriptionTODO queries all order information *@Date 2021/5/25 15:41
 */
Page<Map<String, Object>> getDeviceOrderInfo(Map<String, Integer> map);

/ * * *@author yeqian
 * @param
 * @return
 * @DescriptionTODO overrides the count method for pageHelp *@Date2021/6/3 16:57 * /
Long   getDeviceOrderInfo_COUNT(a);
Copy the code

  2. Write concrete SQL in de-XML

    <! Query all order details -->
<select id="getDeviceOrderInfo_COUNT" resultType="Long" useCache="false">
SELECT
COUNT(1)
FROM order_info  orderInfo
LEFT JOIN customer_info customer ON orderInfo.customer_num = 		customer.id
</select>
Copy the code

  3. Query again, reducing the delay from 2s to the millisecond level 😊

summary

  1. The SQL statement used in the pagination plug-in to Count all the data that needs to be paginated and then generate a temporary table to Count.

  2. If the amount of data is large at this point, the temporary table production step will have multiple queries for the table, so the final Count calculation will be very slow, which will affect the paging results.

Solutions:

Overwrite the original Count SQL, according to the official documentation, custom. Note the return value type (Long) and method name (formerly _COUNT)