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;