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;

原文发布于微信公众号 - 程序员小明(coderxinqiji)

原文发表时间:2019-07-05

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券