I encountered several problems when setting up a data table index. Please ask for help:
1.
UPDATE table SET version = 2 WHERE id = ? AND version =?
In this statement, the id is a self-increasing id, and the version is an optimistic lock. At this time, should we create a separate index for version, a joint index with id, or no need to create an index.
SELECT * FROM table WHERE a in () AND b =? ORDER BY c DESC
in is used, the index should not be used. How to build this?
SELECT * FROM tabler WHERE a = ? AND b > ? AND b < ? ORDER BY c DESC
How to create an index
淡淡烟草味2017-06-10 09:51:40
1. Just index verson separately. Since id is the primary key and unique, the indexing efficiency is actually the highest.
2.in can enable indexing, but when in reaches a certain number, it may fail
3.a and b can be indexed separately. Or do a and b as a joint index
我想大声告诉你2017-06-10 09:51:40
Under normal circumstances, it is enough to create an index for the id field alone.
If according to the business scenario, it is expected that each ID will be modified a lot, you can create a joint index for ID and version.