How does MySQL decide what partitions to load into memory? I have a server where the MySQL indexes won't fit into memory (2TB total index, 512GB RAM on machine), and am wondering how MySQL decides what parts to put into memory and what not.

The relevant tables are range partitioned on ID, and I'm told (but can't find anything proving this) that the last 3 months is available in memory.

I'm hoping there's a more substantial answer available somewhere.
InnoDB's "buffer_pool" contains blocks that are kept there in (roughly) a "least recently used" order.  That is, when you touch a block for either reading or writing, it is pulled into the buffer_pool and marked as "most recently used".  If there was not room for this block, then the "least recently used" block was first removed from the buffer_pool.

PARTITIONing has nothing to do with the caching scheme.  In particular, partitioning by the PRIMARY KEY is almost always useless, and almost always redundant with letting the caching (described above) happen.

If you would like to discuss performance improvements, please provide SHOW CREATE TABLE and the slow query.

Using PARTITION BY RANGE(TO_DAYS(...)) is an excellent use of partitioning, but only because it makes dropping "old" data very efficient.  Note: I did not say BY RANGE(id).

Even if the math is correct, you may or may not have "the last 3 months" cached in RAM -- if someone scanned stuff from 4 months ago, someblocks would have to be bump to allow for that scan.  Then the 4-month-old bllcks would clutter the buffer_pool until they get bumped out.

Update

Since you have a "mission critical" system that must accept writes all the time, and have no PRIMARY KEY, you need some drastic action.  Here's what I would do.

I would back up into the code.  If there is not already code between the "users" and the database, I would start by building such a layer and force the "users" to call an API that say "write ... to the database".

To start with, the API would simply do an INSERT like the original code.  This has minimal impact.

Then I would prepare new code, wherein the API has more complex code behind it.  In particular, it would be writing to the existing table and the new table (with PRIMARY KEY, new index(es), changed datatypes, PARTITIONing, etc).

Meanwhile, another script would be copying, in chunks, the existing data from the existing table to the new table.  (This is part of what pt-online-schema-change does, but we will take some liberties.)

I suggest chunk size of 1000 rows, each chunk as a its own 'transaction' (assuming you are a using InnoDB).  The chunking should follow some index, whichever one closes to being unique.  (An yes/no column would be poor because you could not get more than 2 chunks.)

When all finished, you will have two copies of the data; you can test before dropping the old table.

Reads can proceed unchanged during the process because the old table remains intact.

Some blogs with tips:


Altering a huge table
Chunking
Partitioning
Don't use OFFSET