About every second application I look at has some tables which have redundant or duplicate indexes so its the time to speak about these a bit.
So what is duplicate index ? This is when table has multiple indexes defined on the same columns.
Sometimes it is indexes with different names, sometimes it is different keywords used to define the index. For example it is quite frequent to see something like PRIMARY KEY(id), UNIQUE KEY id(id), KEY id2(id). The logic I heard behind this often – create
primary key as object identifier, now we create UNIQUE because we want it to be UNIQUE and we create KEY so it it can be used in the queries. This is wrong and hurts MySQL Performance. It is enough to create PRIMARY KEY and it will enforce unique values and
will be used in the queries.
The other case is simply having multiple keys on same column(s) – I guess someone thought key would make sense while did not notice it was already created. MySQL is very permissive and allows you to create many keys on the same column… furthermore these would
be real separate keys inside of storage engine
which take space on the disk and in memory and which need to be updated on update/insert delete.--索引会占用磁盘空间,占用内存(通常是缓存),消耗dml带来的额外IO.
Duplicate keys are bad so once you find them get rid of them.
Note: Order of columns in index is significant, index (A,B) is not duplicate to index (B,A)
So now what are Redundant indexes when ?
I call redundant indexes BTREE indexes which are prefix of other index, for example KEY(A), KEY (A,B), KEY(A(10)). – First and last are redundant indexes because they are prefix of KEY(A,B)
Do redundant indexes have right to exist ? In most cases it is good to get rid of them as well. Queries which take advantage of redundant index will also be able to use longer index.
Unlike with duplicate indexes, there are however cases when redundant indexes are helpful – typically if longer index is just too long, for example if A is int and B is varchar(255) which holds a lot of long values using KEY(A) might be much faster than using
KEY(A,B). So unlike in case of duplicate indexes it is good to give a good thought before removing them.--长索引和短索引都有好处,如果长索引太长,那么可以既保留长索引,也保留短索引,否则,去掉短索引,只留下长索引.