On my site I have a visitor's table with 10 million rows.
Every request to the site inserts row to the table, in case the table is locked (usually in optimize query) visitors can't get into the site
The table engine is MyISAM and I want to change it to InnoDB
I have few questions:
在我的网站上，我有一个1000万行的访问者表。对网站的每个请求都会向表中插入行，以防表被锁定（通常在优化查询中）访问者无法访问网站表引擎是MyISAM，我想将其更改为InnoDB我几乎没有问题：
- How can I change the table engine without stoping my site from working
- 如何在不停止我的网站工作的情况下更改表引擎
- There is a way to optimize InnoDB table without locking it
- 有一种方法可以优化InnoDB表而不锁定它
2 个解决方案
#1
17
The easiest way is
最简单的方法是
ALTER TABLE table_name ENGINE = InnoDB;
If you use InnoDB engine you should not worry about locking tables, because this engine locks data by rows.
如果使用InnoDB引擎，则不必担心锁定表，因为此引擎会按行锁定数据。
#2
5
oleksii.svarychevskyi is right, InnoDB uses row level locks, but if you do
oleksii.svarychevskyi是对的，InnoDB使用行级锁，但如果你这样做
ALTER TABLE table_name ENGINE = InnoDB;to change table_name from MyIsam to InnoDB, there will be a metadata locking (at table level) because the original table engine was MyIsam.
If you try to do an UPDATE over table_name, this UPDATE will be enqueued until the ALTER TABLE ends (if you do a SHOW FULL PROCESSLIST you will see a "Waiting for table metadata lock" message associated to the UPDATE).
#1
17
The easiest way is
最简单的方法是
ALTER TABLE table_name ENGINE = InnoDB;
If you use InnoDB engine you should not worry about locking tables, because this engine locks data by rows.
如果使用InnoDB引擎，则不必担心锁定表，因为此引擎会按行锁定数据。
#2
5
oleksii.svarychevskyi is right, InnoDB uses row level locks, but if you do
oleksii.svarychevskyi是对的，InnoDB使用行级锁，但如果你这样做
ALTER TABLE table_name ENGINE = InnoDB;to change table_name from MyIsam to InnoDB, there will be a metadata locking (at table level) because the original table engine was MyIsam.
If you try to do an UPDATE over table_name, this UPDATE will be enqueued until the ALTER TABLE ends (if you do a SHOW FULL PROCESSLIST you will see a "Waiting for table metadata lock" message associated to the UPDATE).