mysql更新数据索引慢_mysql添加索引，查询反而变慢

依照楼主的数据，我也造了400万数据：

mysql> select * from index_test limit 5;

id1

id2

11111

11111

22222

22222

11111

11111

22222

22222

11111

11111

id1创建索引

执行确实是id2谓词条件比较快：

mysql> select * from index_test where id1=11111;

2097152 rows in set (3.00 sec)

mysql> select * from index_test where id2=11111;

2097152 rows in set (2.32 sec)

id1的profile是这样的：

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

| Status | Duration |

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

| starting | 0.000080 |

| checking permissions | 0.000014 |

| Opening tables | 0.000024 |

| init | 0.000033 |

| System lock | 0.000015 |

| optimizing | 0.000018 |

| statistics | 0.035408 |

| preparing | 0.000033 |

| executing | 0.000007 |

| Sending data | 2.963681 |

| end | 0.000021 |

| query end | 0.000015 |

| closing tables | 0.000020 |

| freeing items | 0.003474 |

| logging slow query | 0.000117 |

| cleaning up | 0.000072 |

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

id2的profile是这样的：

Status

Duration

starting

0.000074

checking permissions

0.000012

Opening tables

0.000025

init

0.000032

System lock

0.000014

optimizing

0.000018

statistics

0.000025

preparing

0.000019

executing

0.000006

Sending data

2.318096

end

0.000020

query end

0.000034

closing tables

0.000022

freeing items

0.004327

logging slow query

0.000093

cleaning up

0.000060

可以看到id1耗时相对显著的是statistics，Sending data，主要集中在Sending data。

Sending data

The thread is reading and processing rows for a SELECT statement, and sending data to the client。

也就是说，id1=11111比id2=11111花费更多的时间在数据读取上。而id1上的是二级索引，用到该索引还有一个回表的花销，在这种数据基数小，索引的选择性就太差，这种情况不应该使用索引。

如果非要使用索引，就要避免回表，创建覆盖索引。

alter table index add index com_idx(id1,id2)；

测试结果就可以看到id1=11111比id2=11111快了：

mysql> select * from index_test where id1=11111;

2097152 rows in set (1.71 sec)

mysql> select * from index_test where id2=11111;

2097152 rows in set (2.57 sec)

对应profile如下：

id1=11111

Status

Duration

starting

0.000149

checking permissions

0.000084

Opening tables

0.000032

init

0.000026

System lock

0.000013

optimizing

0.000013

statistics

0.000198

preparing

0.000019

executing

0.000006

Sending data

1.710508

end

0.000024

query end

0.000015

closing tables

0.000019

freeing items

0.003275

logging slow query

0.000068

cleaning up

0.000025

id2=11111

Status

Duration

starting

0.000066

checking permissions

0.000012

Opening tables

0.000020

init

0.000024

System lock

0.000011

optimizing

0.000013

statistics

0.000021

preparing

0.000015

executing

0.000006

Sending data

2.566770

end

0.000025

query end

0.000077

closing tables

0.000034

freeing items

0.004227

logging slow query

0.000093

cleaning up

0.000018