Mysql索引优化初体验-使用索引
小明 Lv7

上回介绍了几种常见的索引类型:normal、unique、fulltext、spatial,本次,主要讲解索引的工作原理及针对normal索引的创建、删除进行举例。

索引是如何工作的

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

1
2
3
4
5
6
7
8
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如下:

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

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

1
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列添加索引:

1
2
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创建,语法如下:

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

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

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

使用ALTER语句创建,语法如下:

1
ALTER TABLE tableName ADD INDEX indexName(columnName);

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

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

建表的时候创建索引:

1
2
3
4
5
CREATE TABLE tableName(  
id INT NOT NULL,
columnName columnType,
INDEX [indexName] (columnName(length))
);

查看索引

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

1
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命令可以删除索引,例如:

1
ALTER TABLE t_user_action_log DROP INDEX index_ip_addr;

欢迎关注微信公众号,获取更多资源

程序员小明

  • 本文标题:Mysql索引优化初体验-使用索引
  • 本文作者:小明
  • 创建时间:2019-07-04 11:31:27
  • 本文链接:https://mynamecoder.com/p/3fc1.html
  • 版权声明:本博客所有文章除特别声明外,均采用 BY-NC-SA 许可协议。转载请注明出处!