Is MySQL trigger for 100 Millions daily updates can impact data base cluster performance?
It is true that triggers incur some cost to the database. You should think about it in terms of the number of index writes.
When you insert or update a table without triggers, you can estimate the cost by counting the number of indexes, plus the primary key, which is the clustered index. You have nine indexes, so this is 9+1 = 10 writes per insert or update.
If you also have a trigger, the execution of trigger code is a small overhead, but it is more important to count the cost of the index writes by statements in the trigger. The index writes are more costly, compared to the trigger code itself.
So if your insert/update spawns a trigger that writes to another table, the cost will be the 10 index writes for the original table, plus as many index writes as the number of indexes in the additional table. Suppose you have one more index in that second table, it would then be 11 index writes, for a total of 21. So it is slightly more than double the cost.
My knowledge of Aurora is that it disables the InnoDB change buffer, because of the distributed storage. This means that all index writes must be merged at the time of your insert/update.
If I were working on your database, I would just add an index to the original table, using pt-online-schema-change. This allows clients to read and write the table while the index is being built. We use this at my company, and we do hundreds of schema changes per week for large tables, with no downtime.
Pt-online-schema-change uses triggers while it's building the index, but those triggers are dropped after this change is done. Also note that if you have long-running transactions against the table, it will conflict with pt-online-schema-change, because it needs a brief exclusive metadata lock at the start and the end of its work.