mysql创建的多有什么影响_2.MySQL是否允许在相同列上创建多个索引？如果可以，对数据库性能有何影响？...

如果动手实践过的，就发现在相同的列上创建多个索引没有任何问题。有什么影响呢？从explain查询语句的结果来看，explain时分别指定同一列上的多个索引，除了possible keys不一样，其他列都是一样的。查询效率上肯定没什么影响。

mysql> explain select * from one use index(mynick) where nickname="AAA";

+----+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------+

| 1 | SIMPLE | one | ref | mynick | mynick | 13 | const | 1 | Using index condition |

+----+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------+

1 row in set (0.00 sec)

mysql> explain select * from one use index(nickname) where nickname="AAA";

+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------+

| 1 | SIMPLE | one | ref | nickname | nickname | 13 | const | 1 | Using index condition |

+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------+

1 row in set (0.00 sec)

但是，索引都是需要维护的，增删改涉及到了此列时原本要改一次的操作，现在就需要改多次了。浪费空间，浪费操作。(从ibd文件中能看出这个浪费)