优化mysql数据库-myISAM db
我有两个关于使用 MyISAM 引擎的数据库的 msyql 性能的问题：
1）什么是聪明的方法来解决当 INSERT 或 UPDATE 一个表中的某些行时，许多 SELECT 查询被挂起的问题。
2）在数据库当前正在运行的情况下，从MyISAM更改为InnoDB是否容易？
3）为什么当InnoDB很好时，myISAM仍然是mySQL的默认选项，因为它提供行级锁定？
4）我在设计一个带有如下帖子视图的数据库时遇到一个问题：
我有一个表有很多“帖子”。
我想为一个“帖子”提供一个观看次数，每次人们在网站上查看该帖子时，观看次数都会增加。
因此，如果我将“视图”字段放在“帖子”表上，则每当访问者访问此帖子时，都会运行查询“更新帖子设置视图=视图+1”。这使得该行上的其他选择查询挂起。
如果我将“视图”字段放在其他表上，我仍然会遇到此问题，因为当我在网站上显示帖子时，我仍然需要使用内部联接查询来获取该帖子的视图编号。如果运行更新视图查询，此查询仍然卡住。
抱歉我的英语不好。
i have two question about msyql performance with my db using MyISAM engine :
1) what is smart way to solve the problem that when INSERT or UPDATE some rows in one table, many SELECT queries be hang on.
2) Is this easy to change from MyISAM to InnoDB with database is current running?
3) Why myISAM is still be default option of mySQL when InnoDB is good because it provide row-level locking?
4) I have one problem when design a database with view of post like below :
I have one table have many "posts".
I want to give one "post" a view number and this increase every time people view the post on the website.
So, if i put the "view" field on the table "Posts" this will run the query "Update Posts set view=view+1" anytime visitor visit this post. This make other select query on this Row hang on.
If i put the "view" field on other TABLE, i still get this problem because when i display a post on website i still need the view number for this post by using a inner join query. And this query still stuck if have update view query run.
Sorry for my bad English.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您已经知道几乎所有问题的答案，
切换到 InnoDB
首先检查您是否获得 InnoDB 支持。在您的 mysql 服务器上执行以下查询：
如果您得到的值为“YES”，您可以使用以下 SQL 查询更改您想要更改的每个表：
（先做好备份）
在未来的版本中MySQL将切换到InnoDB作为默认存储引擎。但由于遗留设置，这一点尚未改变
You already know the anwsers to almost all you questions,
Switch to InnoDB
First check if you got InnoDB support. On your mysql server Execute the following query:
if you get a value of 'YES' you can change every table you want to change with the following SQL Query:
(make a backup first)
In future releases MySQL will switch to InnoDB as default storage engine. But because of legacy setup this haven't been changed
Jaydee 混合使用 MyISAM 和 InnoDB 的建议是一个很好的建议。您可以将“views”表设置为 InnoDB 表，并且它不应在写入期间阻止读取。
或者，您可以在 InnoDB 中创建主表的副本，同步它们（触发、两次写入，无论需要什么），然后将它们切换出去。与
ALTER TABLE...相比，这会导致更少的停机时间，但涉及更多工作。Jaydee's suggestion of a mix of MyISAM and InnoDB is a good one. You can make the "views" table an InnoDB table and it shouldn't block reads during writes.
Alternatively, you could create a copy of of your main table in InnoDB, synchronize them, (triggers, two writes, whatever is necessary), and then switch them out. This will cause less down time than an
ALTER TABLE..., but involves more work.