使用MYSQL索引提升检索速度

之前碰到一个MYSQL查询速度慢的问题，在一个4G大小的表，约835万条记录中，按照时间统计总数。后来通过使用Order By语句把查询速度提升了3倍。原帖：使用Order By语句将MySQL查询速度提高3倍。如原文所述，最后的查询结果在7秒左右。然而对于一条MYSQL语句运行7秒，这样的速度完全是不能接受的。最后为了提升速度，还是决定通过把查询字段加到索引里。

MySQL里面提供5种索引类型

• 添加PRIMARY KEY（主键索引）

顾名思义，主键索引，字段必须唯一且不能为空。因为我们这张表已经有一个id主键了，因此该类型不适合。以下是添加主键索引的MYSQL语句：

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 

• 添加UNIQUE（唯一索引）

唯一索引，字段必须是唯一的，可以为空。因为我们的索引字段start_time是bigint型，就是开始时间，没有办法确保唯一性，因此该类型也不适合。以下是添加唯一索引的MYSQL语句：

ALTER TABLE `table_name` ADD UNIQUE ( `column` )  

• 添加INDEX（普通索引）

最通用的索引方式，没有任何限制。如果其它类型不合适，就用这种类型。比较适合我们的情况。以下是添加普通索引的MYSQL语句：

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

• 添加FULLTEXT（全文索引）

这种类型适合字段为TEXT等较大的数据，生成全文索引耗时耗空间。如果用在比较短的文本，例如就一两行字的，也可以使用普通索引。因为我们的start_time是bigint型，因此也不适合。以下是添加全文索引的MYSQL语句：

ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 

• 添加多列索引（组合索引）

组合索引就是多个字段作为一个组合做索引，并且列值的组合必须唯一，并且遵循”最左前缀”原则。例如，我们对字段a，b，c做组合索引。在查询语句中可以指定a，ab，或者abc的组合查询，但是不支持bc组合查询。所以在组合索引中顺序很重要,不同的顺序会导致不同的速度。以下是添加组合索引的MYSQL语句：

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

另外，这里贴上删除索引的MYSQL语句：

ALTER TABLE table_name DROP INDEX index_name

为数据表添加索引字段

根据实际情况，我们决定为start_time添加一个普通索引。对于一个拥有800-900万的数据量级的表，mysqld约占用3G的内存，添加一个普通索引耗时约2分33秒。

通过以下命令获取mysqld的进程和使用的内存数：

ps aux | grep mysqld
pmap 11021 | tail -n 1

实际上ps指令显示的VSZ差不都就是物理内存的使用量。

提升查询速度

最终选择了为start_time添加普通索引。添加完成以后再执行一遍相同的查询语句，查询速度达到0.2秒，查询速度提升了35倍。