The only way to make this run faster
select distinct subpid from transactions;
is to add
INDEX(subpid)
You have a lot of indexes. And none are "compound". Let's see some other slow SELECTs; there may be further suggestions. Are INSERTs going slower than you would like? Note that your indexes take up more disk (7GB) than the data (6GB).
There are a lot of BIGINTs; I suspect some of them don't need to be 'big' -- BIGINT takes 8 bytes; INT takes 4.
Since your buffer_pool is nearly 5GB, I hope you have at least 7GB of RAM?
This is probably redundant:
KEY `id_idx` (`id`),
Any EXPLAIN that says "ALL" will have to run through the entire 6GB of data. This gets put into your 5GB buffer_pool, effectively flushing out anything that was there. This means subsequent queries will be slow. My suggested INDEX would mean scanning ALL of that one index, but that is (currently) much less than the buffer_pool, so it would be less harmful.
Since you have an auto_increment, it is possible (but complicated) to effectively do certain ALTERs by copying the table over, a few rows at a time.
1. CREATE new table(s) with desired indexes, normalization, partitioning, whatever.
2. Change clients to look in two places for the data (using UNION or some ad hoc code).
3. Loop through, copying 100 rows from this table to the new one(s). Delete from old table as you go.
Immediately after step 2, the clients will find all the data in the old table.
During step 3, they will find data from two places.
After step 3, they will find all the data in the new table(s).
To clean up,
4. Change clients to look only at the new tables.
5. Drop the old table (which is empty now anyway).
For example
select distinct subpid from transactions;
would become
select distinct subpid from transactions
UNION DISTINCT
select distinct subpid from transactions2;
Recommend you practice on 100,000 rows copied from transactions to a test table.