您需要显式地创建索引,还是在定义主键时隐式创建索引?MyISAM和InnoDB的答案一样吗?
当前回答
MyISAM和InnoDB的主键都隐式索引。可以通过对使用主键的查询使用EXPLAIN来验证这一点。
其他回答
主键总是被索引。对于MyISAM和InnoDB也是如此,对于所有支持索引的存储引擎也是如此。
MyISAM和InnoDB的主键都隐式索引。可以通过对使用主键的查询使用EXPLAIN来验证这一点。
我想这就是答案
mysql> create table test(id int primary key, s varchar(20));
Query OK, 0 rows affected (0.06 sec)
mysql> show indexes from test \G
*************************** 1. row ***************************
Table: test
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)
索引最好用于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.
你不必显式地为主键创建索引…这是默认的。