问题整理（一） 博客分类： 问题汇总

分页实现

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 () 参数个数的上限


--------------------------------------------------------------------------