索引是如何工作的
首先，在你的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的索引值分类存储在了索引空间，可以快速地查询到索引值所对应的列。
索引的使用原则
索引由于其提供的优越的查询性能，似乎不使用索引就是一个愚蠢的行为了。但是使用索引，是要付出时间和空间的代价的。因此，索引虽好不可贪多。
下面介绍几个索引的使用技巧和原则，在使用索引之前，你应该对它们有充分的认识。
写操作比较频繁的列慎重加索引
索引在提高查询速度的同时，也由于需要更新索引而带来了降低插入、删除和更新带索引列的速度的问题。一张数据表的索引越多，在写操作的时候性能下降的越厉害。
索引越多占用磁盘空间越大
与没有加索引比较，加索引会更快地使你的磁盘接近使用空间极限。
不要为输出列加索引
为查询条件、分组、连接条件的列加索引，而不是为查询输出结果的列加索引。
例如下面的查询语句：
select ip_address from t_user_action_log
where name='LiSi'
group by action
order by create_time;
所以可以考虑增加在 name action create_time 列上，而不是 ip_address。
考虑维度优势
例如action列的值包含：1、2、3、4、5，那么该列的维度就是5。
维度越高（理论上维度的最大值就是数据行的总数），数据列包含的独一无二的值就越多，索引的使用效果越好。
对于维度很低的数据列，索引几乎不会起作用，因此没有必要加索引。
例如性别列的值只有男和女，每种查询结果占比大约50%。一般当查询优化处理器发现查询结果超过全表的30%的时候，就会跳过索引，直接进行全表扫描。
对短小的值加索引
对短小的值加索引，意味着索引所占的空间更小，可以减少I/O活动，同时比较索引的速度也更快。
尤其是主键，要尽可能短小。
另外，InnoDB使用的是聚集索引（clustered index），也就是把主键和数据行保存在一起。主键之外的其他索引都是二级索引，这些二级索引也保留着一份主键，这样在查询到索引以后，就可以根据主键找到对应的数据行。如果主键太长的话，会造成二级索引占用的空间变大。
比如下面的action索引保存了对应行的id。
为字符串前缀加索引
前边已经讲过短小索引的种种好处了，有时候一个字符串的前几个字符就能唯一标识这条记录，这个时候设置索引的长度就是非常划算的做法。
前面已经提供了设置索引length的例子，这里就不举例子了。
复合索引的左侧索引
创建复合索引的语法如下：
CREATE INDEX indexName ON tableName (column1 DESC, column2 DESC, column3 ASC);
我们可以看到，最左侧的column1索引总是有效的。
索引加锁
对于InnoDB来说，索引可以让查询锁住更少的行，从而可以在并发情况下拥有更佳表现。
下面演示一下查询锁与索引之间的关系。
前面使用的t_user_action_log表目前有一个id为主键，还有一个二级索引action。
下面这条语句的修改范围是id值为1 2 3 4所在的行，查询锁会锁住id值为1 2 3 4 5所在的行。
update ijiangtao_local_db_mysql.t_user_action_log set name='c1' where id<5;
- 首先创建数据库连接1，开启事务，并执行update语句
set autocommit=0;
begin;
update ijiangtao_local_db_mysql.t_user_action_log set name='c1' where id<5;
- 然后开启另外一个连接2，分别执行下面几个update语句
-- 没有被锁
update ijiangtao_local_db_mysql.t_user_action_log set name='c2' where id=6;
-- 被锁
update ijiangtao_local_db_mysql.t_user_action_log set name='c2' where id=5;
你会发现id=5的数据行已经被锁定，id=6的数据行可以正常提交。
- 连接1提交事务，连接2的id=1和id=5的数据行可以update成功了。
-- 在连接1提交事务
commit;
覆盖索引
如果索引包含满足查询的所有数据，就被称为覆盖索引(Covering Indexes)，覆盖索引非常强大，可以大大提高查询性能。
覆盖索引高性能的原因是：
索引通常比记录要小，覆盖索引查询只需要读索引，而不需要读记录。
索引都按照值的大小进行顺序存储，相比与随机访问记录，需要更少的I/0。
大多数数据引擎能更好的缓存索引，例如MyISAM只缓存索引。
聚簇索引
聚簇索引(Clustered Indexes)保证关键字的值相近的元组存储的物理位置也相同，且一个表只能有一个聚簇索引。
字符串类型不建议使用聚簇索引，特别是随机字符串，因为它们会使系统进行大量的移动操作。
并不是所有的存储引擎都支持聚簇索引，目前InnoDB支持。
如果使用聚簇索引，最好使用AUTO_INCREMENT列作为主键，应该尽量避免使用随机的聚簇主键。
从物理位置上看，聚簇索引表比非聚簇的索引表，有更好的访问性能。
选择合适的索引类型
从数据结构角度来看，MySQL支持的索引类型有B树索引、Hash索引等。
- B树索引
B树索引对于<、<=、 =、 >=、 >、 <>、!=、 between查询，进行精确比较操作和范围比较操作都有比较高的效率。
B树索引也是InnoDB存储引擎默认的索引结构。
- Hash索引
Hash索引仅能满足=、<=>、in查询。
Hash索引检索效率非常高，索引的检索可以一次定位，不像B树索引需要从根节点到枝节点，最后才能访问到页节点这样多次的I/O访问，所以Hash索引的查询效率要远高于B树索引。但Hash索引不能使用范围查询。
