您需要显式地创建索引,还是在定义主键时隐式创建索引?MyISAM和InnoDB的答案一样吗?
当前回答
索引最好用于where子句中经常使用的列,以及任何类型的排序,例如“order by”。 您可能正在处理一个更复杂的数据库,因此最好记住一些简单的规则。
Indexes slow down inserts and updates, so you want to use them carefully on columns that are FREQUENTLY updated. Indexes speed up where clauses and order by. Remember to think about HOW your data is going to be used when building your tables. There are a few other things to remember. If your table is very small, i.e., only a few employees, it's worse to use an index than to leave it out and just let it do a table scan. Indexes really only come in handy with tables that have a lot of rows. Another thing to remember, that is a con in the situation of our employee’s database, is that if the column is a variable length, indexes (as well as most of MySQL) perform much less efficiently. Don't forget joins too! Indexed join fields speed things up.
其他回答
根据http://dev.mysql.com/doc/refman/5.0/en/constraint-primary-key.html的说法,这似乎是隐含的
MyISAM和InnoDB的主键都隐式索引。可以通过对使用主键的查询使用EXPLAIN来验证这一点。
主键总是自动索引且惟一。因此,注意不要创建冗余索引。
例如,如果您这样创建了一个表
CREATE TABLE mytable (foo INT NOT NULL PRIMARY KEY, bar INT NOT NULL, baz INT NOT NULL,
UNIQUE(foo), INDEX(foo)) ENGINE=InnoDB;
因为您想要索引主键并对其强制唯一性约束,所以实际上最终会在foo上创建三个索引!
索引最好用于where子句中经常使用的列,以及任何类型的排序,例如“order by”。 您可能正在处理一个更复杂的数据库,因此最好记住一些简单的规则。
Indexes slow down inserts and updates, so you want to use them carefully on columns that are FREQUENTLY updated. Indexes speed up where clauses and order by. Remember to think about HOW your data is going to be used when building your tables. There are a few other things to remember. If your table is very small, i.e., only a few employees, it's worse to use an index than to leave it out and just let it do a table scan. Indexes really only come in handy with tables that have a lot of rows. Another thing to remember, that is a con in the situation of our employee’s database, is that if the column is a variable length, indexes (as well as most of MySQL) perform much less efficiently. Don't forget joins too! Indexed join fields speed things up.
主键总是被索引。对于MyISAM和InnoDB也是如此,对于所有支持索引的存储引擎也是如此。
推荐文章
- 我可以从一个完整的mysql mysqldump文件恢复一个表吗?
- 如何使用jq从JSON获得键名
- 计数在VARCHAR字段中字符串的出现次数?
- 非加密用途的最快哈希?
- 如何在Python中直接获得字典键作为变量(而不是通过从值搜索)?
- 修改一个MySQL列为AUTO_INCREMENT
- 查询以列出数据库中每个表中的记录数量
- 如何删除数组元素,然后重新索引数组?
- 在ROR迁移期间,将列类型从Date更改为DateTime
- 如何删除所有MySQL表从命令行没有DROP数据库权限?
- 从主机连接到docker容器中的mysql
- 如果任何字段包含NULL, MySQL CONCAT将返回NULL
- MySQL中的字符串连接
- H2内存数据库。表未找到
- Laravel未知列'updated_at'