分页实现
jr-MyBatis
PageBean PageBeanPlugin
Spring-MyBatis.xml
sqlSessionFactory
问题:分库分表
为每个表添加一个公共的状态位,但不修改PO MAPPER
如何实现?
是否可以参考此处的分页实现原理?在SQL提交前进行语句的拼接及修改
---------------------------------
EXPLAIN
SELECT COUNT(0)
FROM p2p_td_notice site
WHERE site.C_TIME > IFNULL((
SELECT lk.C_NOTICE_TIME
FROM p2p_td_user_notice_link lk
WHERE lk.C_USER_ID = 'e21e515d47ef4f07842f65e89015f18e'
), STR_TO_DATE('2016-09-11', '%Y-%m-%d'))
AND (site.C_KIND_ID != 2
OR site.C_KIND_ID IS NULL)
AND site.C_DISPLAY_APP = 0 ;
-- 优化意见
-- 加 C_TIME的索引
-- 将 STR_TO_DATE('2016-09-11', '%Y-%m-%d') 去掉,改为 2016-09-11 即去掉此处的函数
-- 或者将整个查询去掉,应该是查询一个具体的时间
-- P2pTdNoticeMapper.xml getUnReadCount
show INDEX FROM p2p_td_notice ;
DESC p2p_td_notice ;
EXPLAIN
SELECT D.ID AS id, C_CATEGORY_ID AS categoryId, C_SUB_CATEGORY_ID AS subCategoryId, C_ITEM_ID AS itemId, C_ITEM_NAME AS itemName
, C_ITEM_SNAME AS itemSname, C_ITEM_PID AS itemPid, C_CREATE_TIME AS createTime
FROM P2P_TD_URL_DICTIONARY D
LEFT JOIN P2P_TD_NOTICE N ON D.C_ITEM_PID = N.ID
WHERE 1 = 1
AND C_CATEGORY_ID = 'C0002'
AND C_TYPE = 'notification'
AND (N.C_KIND_ID != 2
OR N.C_KIND_ID IS NULL)
ORDER BY C_TIME DESC ;
-- P2pTdUrlDictionaryMapper.xml getEntityIssue
-----------------------------------------------------------------------------
in 过大时,用 exist 代替,如何替代
in () 参数个数的上限
--------------------------------------------------------------------------
jr-MyBatis
PageBean PageBeanPlugin
Spring-MyBatis.xml
sqlSessionFactory
问题:分库分表
为每个表添加一个公共的状态位,但不修改PO MAPPER
如何实现?
是否可以参考此处的分页实现原理?在SQL提交前进行语句的拼接及修改
---------------------------------
EXPLAIN
SELECT COUNT(0)
FROM p2p_td_notice site
WHERE site.C_TIME > IFNULL((
SELECT lk.C_NOTICE_TIME
FROM p2p_td_user_notice_link lk
WHERE lk.C_USER_ID = 'e21e515d47ef4f07842f65e89015f18e'
), STR_TO_DATE('2016-09-11', '%Y-%m-%d'))
AND (site.C_KIND_ID != 2
OR site.C_KIND_ID IS NULL)
AND site.C_DISPLAY_APP = 0 ;
-- 优化意见
-- 加 C_TIME的索引
-- 将 STR_TO_DATE('2016-09-11', '%Y-%m-%d') 去掉,改为 2016-09-11 即去掉此处的函数
-- 或者将整个查询去掉,应该是查询一个具体的时间
-- P2pTdNoticeMapper.xml getUnReadCount
show INDEX FROM p2p_td_notice ;
DESC p2p_td_notice ;
EXPLAIN
SELECT D.ID AS id, C_CATEGORY_ID AS categoryId, C_SUB_CATEGORY_ID AS subCategoryId, C_ITEM_ID AS itemId, C_ITEM_NAME AS itemName
, C_ITEM_SNAME AS itemSname, C_ITEM_PID AS itemPid, C_CREATE_TIME AS createTime
FROM P2P_TD_URL_DICTIONARY D
LEFT JOIN P2P_TD_NOTICE N ON D.C_ITEM_PID = N.ID
WHERE 1 = 1
AND C_CATEGORY_ID = 'C0002'
AND C_TYPE = 'notification'
AND (N.C_KIND_ID != 2
OR N.C_KIND_ID IS NULL)
ORDER BY C_TIME DESC ;
-- P2pTdUrlDictionaryMapper.xml getEntityIssue
-----------------------------------------------------------------------------
in 过大时,用 exist 代替,如何替代
in () 参数个数的上限
--------------------------------------------------------------------------