Add or remove indexes for online data tables

as in the question, when I add or remove indexes for a table in the database, I have two choices

use alter table to change the table structure
use the create/drop index command

Which of these two methods is better? What I want to consider is, if the amount of data in this table is large, will it cause the entire table to be locked in the operation of creating / deleting the index and make it impossible for the user to write?

I have also thought about preparing a copy of this table and operating on the backup table, but if there must be a write operation in the course of the operation, this leads to a discrepancy between the data of the backup table and the actual table.

I wonder if there are any good methods and ideas? At ordinary times, the amount of data of my project is smaller than the price. I have not considered this problem. Please give me advice from all kinds of gods!

Php python java mysql

Mar.21,2021

I don't know exactly what database you are using. I know mysql

mysql 5.6 adding indexes in previous versions blocked reading and writing and affected business. mysql 5.6 later versions add indexes with new features online DDL , with less impact, only the start and end phases, and no blocking of reads and writes at other times.
the best way is to use the online ddl tool pt-osc , but there are some restrictions, such as must have a primary key, no trigger, etc.

Previous: Pm2 deployment Unexpected token. Report an error

Next: How to achieve this kind of shrinking paging?