首先,我们先创建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 indexName ON tableName (columnName(length));
例如我们对ip_address这一列创建一个长度为16的索引:
CREATE INDEX index_ip_addr ON t_user_action_log (ip_address(16));
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;