Mysql索引优化

索引是如何工作的

首先，我们先创建t_user_action_log表，方便下面进行演示。

CREATE TABLE `t_user_action_log` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name` VARCHAR(32) DEFAULT NULL COMMENT '用户名',
  `ip_address` VARCHAR(50) DEFAULT NULL COMMENT 'IP地址',
  `action` INT4 DEFAULT NULL COMMENT '操作：1-登录，2-登出，3-购物，4-退货，5-浏览',
  `create_time` TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

假如我们要筛选 action为2的所有记录，SQL如下：

SELECT id, name, ip_address FROM t_user_action_log WHERE `action`=2;

通过查询分析器explain分析这条查询语句(就在这个语句前面加上explain)：

EXPLAIN SELECT id, name, ip_address FROM t_user_action_log WHERE `action`=2;

结果如下：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t_user_action_log		ALL					1	100.00	Using where

其中type为ALL表示要进行全表扫描。这样效率无疑是极慢的。

下面为action列添加索引：

ALTER TABLE t_user_action_log ADD INDEX (`action`);

然后再次执行查询分析，结果如下：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t_user_action_log		ref	action	action	5	const	1	100.00

我们看到这次查询就使用索引了。加索引前Extra的值是Using Where，type的值为ALL，加索引后Extra的值为空，type的值为ref，表示使用引用。

那么为什么索引会提高查询速度呢？原因是索引会根据索引值进行分类，这样就不用再进行全表扫描了。

比如上图，action值为2的索引值分类存储在了索引空间，可以快速地查询到索引值所对应的列。

如何使用

下面介绍一下如何使用SQL创建、查看和删除索引。

创建索引的三种方式：

使用CREATE INDEX创建，语法如下：

CREATE INDEX indexName ON tableName (columnName(length));

例如我们对ip_address这一列创建一个长度为16的索引：

CREATE INDEX index_ip_addr ON t_user_action_log (ip_address(16));

使用ALTER语句创建，语法如下：

ALTER TABLE tableName ADD INDEX indexName(columnName);

ALTER语句创建索引前面已经有例子了。下面提供一个设置索引长度的例子：

ALTER TABLE t_user_action_log ADD INDEX ip_address_idx (ip_address(16));

建表的时候创建索引：

CREATE TABLE tableName(  
  id INT NOT NULL,   
  columnName  columnType,
  INDEX [indexName] (columnName(length))  
);

查看索引

可以通过show语句查看索引：

SHOW INDEX FROM t_user_action_log;

Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
t_user_action_log	0	PRIMARY	1	id	A	199,368				BTREE
t_user_action_log	1	action	1	action	A	4			YES	BTREE
t_user_action_log	1	index_ip_addr	1	ip_address	A	1	16		YES	BTREE

删除索引

使用ALTER命令可以删除索引，例如：

ALTER TABLE t_user_action_log DROP INDEX index_ip_addr;