59. MySQL索引是如何提高查询效率的呢？（MySQL面试第二弹）上
About MySQL
MySQL（读作/maɪ ˈsiːkwəl/“My Sequel”）是一个开放源码的关系数据库管理系统，原开发者为瑞典的MySQL AB公司，目前为Oracle旗下产品。
被甲骨文公司收购后，自由软件社群们对于Oracle是否还会持续支持MySQL社群版（MySQL之中唯一的免费版本）有所隐忧，因此MySQL的创始人麦克尔·维德纽斯以MySQL为基础，成立分支计划MariaDB。原先一些使用MySQL的开源软件，部分转向了MariaDB或其它的数据库。
不可否认的是，MySQL由于其性能高、成本低、可靠性好，已经成为最流行的开源数据库之一，随着MySQL的不断成熟，它也逐渐用于更多大规模网站和应用，非常流行的开源软件组合LAMP中的“M”指的就是MySQL。
Why MySQL
在众多开源免费的关系型数据库系统中，MySQL有以下比较出众的优势：
运行速度快
易使用
SQL语言支持
移植性好
功能丰富
成本低廉对于其中运行速度，根据官方介绍，MySQL 8.0 比之前广泛使用的版本 MySQL 5.7 有了两倍的提升。
在其官方的Benchmarks中，只读的性能超过了每秒一百万次：
读写的性能接近每秒二十五万次：
MySQL Index
Why Index
从概念上讲，数据库是数据表的集合，数据表是数据行和数据列的集合。当你执行一个SELECT语句从数据表中查询部分数据行的时候，得到的就是另外一个数据表和数据行的集合。
当然，我们都希望获得这个新的集合的时间尽可能地短，效率尽可能地高，这就是优化查询。
提升查询速度的技术有很多，其中最重要的就是索引。当你发现自己的查询速度慢的时候，最快解决问题的方法就是使用索引。索引的使用是影响查询速度的重要因素。在使用索引之前其他的优化查询的动作纯粹是浪费时间，只有合理地使用索引之后，才有必要考虑其他优化方式。
索引是如何工作的
首先，在你的MySQL上创建t_user_action_log 表，方便下面进行演示。
CREATE DATABASE `ijiangtao_local_db_mysql` /*!40100 DEFAULT CHARACTER SET utf8 */; USE ijiangtao_local_db_mysql; DROP TABLE IF EXISTS 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; INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 1, CURRENT_TIMESTAMP); INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.1', 2, CURRENT_TIMESTAMP); INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 1, CURRENT_TIMESTAMP); INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.3', 1, CURRENT_TIMESTAMP); INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 2, CURRENT_TIMESTAMP); INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.4', 1, CURRENT_TIMESTAMP); INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 2, CURRENT_TIMESTAMP); INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.1', 1, CURRENT_TIMESTAMP); INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 2, CURRENT_TIMESTAMP); INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 1, CURRENT_TIMESTAMP); INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 3, CURRENT_TIMESTAMP); INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 5, CURRENT_TIMESTAMP); INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 2, CURRENT_TIMESTAMP); INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 2, CURRENT_TIMESTAMP); INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 3, CURRENT_TIMESTAMP); INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 3, CURRENT_TIMESTAMP); INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 5, CURRENT_TIMESTAMP); INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 3, CURRENT_TIMESTAMP); INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 3, CURRENT_TIMESTAMP); INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 4, CURRENT_TIMESTAMP);
假如我们要筛选 action为2的所有记录，SQL如下：
SELECT id, name, ip_address FROM t_user_action_log WHERE `action`=2;
通过查询分析器explain分析这条查询语句：
EXPLAIN SELECT id, name, ip_address FROM t_user_action_log WHERE `action`=2;
分析结果如下：
其中type为ALL表示要进行全表扫描。这样效率无疑是极慢的。
下面为action列添加索引：
ALTER TABLE t_user_action_log ADD INDEX (`action`);
然后再次执行查询分析，结果如下：
那么为什么索引会提高查询速度呢？原因是索引会根据索引值进行分类，这样就不用再进行全表扫描了。我们看到这次查询就使用索引了。加索引前Extra的值是Using Where，加索引后Extra的值为空。
比如上图，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)); SHOW INDEX FROM t_user_action_log;
建表的时候创建索引：
CREATE TABLE tableName( id INT NOT NULL, columnName columnType, INDEX [indexName] (columnName(length)) );
查看索引
可以通过show语句查看索引：
SHOW INDEX FROM t_user_action_log;
删除索引
使用ALTER命令可以删除索引，例如：
ALTER TABLE t_user_action_log DROP INDEX index_ip_addr;
索引的使用原则
索引由于其提供的优越的查询性能，似乎不使用索引就是一个愚蠢的行为了。但是使用索引，是要付出时间和空间的代价的。因此，索引虽好不可贪多。
下面介绍几个索引的使用技巧和原则，在使用索引之前，你应该对它们有充分的认识。
写操作比较频繁的列慎重加索引
索引在提高查询速度的同时，也由于需要更新索引而带来了降低插入、删除和更新带索引列的速度的问题。一张数据表的索引越多，在写操作的时候性能下降的越厉害。
索引越多占用磁盘空间越大
与没有加索引比较，加索引会更快地使你的磁盘接近使用空间极限。
不要为输出列加索引
为查询条件、分组、连接条件的列加索引，而不是为查询输出结果的列加索引。
例如下面的查询语句：
select ip_address from t_user_action_log where name='LiSi' group by action order by create_time;
所以可以考虑增加在 name action create_time 列上，而不是 ip_address。
