一、基本概念
索引是一种与表相关的数据结构，用于加速数据的存取。在适合的场景对表的某些字段建立索引，可以很大程度的减少查询时的硬盘I/O。
如果把表看作一本书，索引就相当于一本书的目录。没有目录的情况下，要查找指定内容需要翻阅整个书本。
索引是数据存储的一种机制，机制里边可以简单理解有索引字段、字段对应记录的物理地址。
索引字段按照一定的规律、规则组织在一起(数据结构与算法)，可以加快信息的查找。
信息在索引内部被查找出来后，直接对应记录的"物理地址"，根据物理地址就可以快速定位需要的记录信息。
二、创建索引
数据表的全部字段都可以创建索引，但具体得根据实际情况变更。
索引类型：
① 主键索引：primary key [名称](字段)
② 唯一索引：unique key [名称] (字段)
③ 普通索引：key [名称](字段) 或者 index [名称](字段)
④ 全文索引：fulltext key [名称] (字段)
⑤ 复合索引：多个字段组成索引
索引名称可以不设置，默认与当前索引字段名称一致。
mysql5.6.4之前只有Myisam支持全文索引，之后 Myisam和Innodb都支持全文索引。
索引创建：
a)创建数据表同时设置索引
CREATE TABLE `student`(
`id` int(11) not null auto_increment comment '逻辑主键',
`name` varchar(32) not null comment '名字',
`height` decimal(6,2) not null default '0.00' comment '身高',
`introduce` text comment '个人简介',
primary key (`id`), --主键索引
unique key `name`(`name`), --唯一索引
key `height`(`height`), --普通索引
fulltext key `introduce`(`introduce`)--全文索引
)ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='学生表';
b) 给存在的数据表增加索引
CREATE TABLE `student`(
`id` int(11) not null comment '逻辑主键',
`name` varchar(32) not null comment '名字',
`height` decimal(6,2) not null default '0.00' comment '身高',
`introduce` text comment '个人简介',
)ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='学生表';
alter table `student` add primary key (`id`);
alter table `student` add unique key `name`(`name`);
alter table `student` add key `height`(`height`);
-- alter table `student` add index `height`(`height`);
alter table `student` add fulltext key `introduce`(`introduce`);
alter table `student` modify id int not null auto_increment commont '主键';
c)设置复合索引
--表创建好之后
alter table `student` add key `nh`(`name`,`height`);
--建表时
key `bh`(`name`,`height`)
三、删除索引
删除主键索引，必须先去除auto_inrement属性。
alter table 表名 modify 主键字段名 字段属性; --去掉auto_increment
alter table 表名 drop primary key;
alter table 表名 drop key 索引名;
四、索引结构
主要学习下MyISAM和InnoDB引擎的索引的数据结构。
MySQL的数据结构都是B+Tree树结构。
1.MyISAM索引结构
图中的Myisam索引结构称为"非聚合型"。
主键内容在该索引里边通过Mysql底层算法开始被查询、获得，并借助"指针"向下级寻找，直到找到对应的叶子节点，叶子节点里边有该关键字对应的记录的物理地址，从而获得对应的记录信息。
运行原理：快速定位主键id值，获得对应记录物理地址，获得记录信息。
每个主键id值都是一个节点，节点本身有指针
叶子节点与记录的物理地址直接联系
节点从上到下的层次数是索引结构的高度
每层节点的数目称为结构的宽度
结构的宽度、高度的数目由mysql底层算法计算获得(过高、过宽都不利于数据的快速获取)
Myisam其他索引结构与主键索引结构一致。
因为该索引结构的特性，所以Myisam引擎的数据文件与索引文件是分开存储的。
2.InnoDB索引结构
Innodb聚合型索引结构：
"索引"和"数据"是合并在一个后缀为[.ibd]文件里边的。
InnoDB主键索引结构：
① 通过索引结构快速定位主键id值对应的叶子节点
② 该叶子节点里边直接与整条记录信息进行对应(而在Myisam里边，叶子节点与物理地址对应)
InnoDB唯一、普通索引结构：
① 通过索引结构内部的算法快速定位该名字对应的叶子节点
② 叶子节点里边对应的是关键字的记录主键id值
③ 通过记录主键id值走主键索引即可
普通索引关键字----->记录的主键id值------>记录的整条信息
五、explain
一条sql语句在没有执行之前，先把需要的资源都计划好，例如cpu、内存等资源的分配预计，该行为就称为执行计划。
explain select语句
一般来说，
① 使用到索引，耗费资源少，查询速度快
② 没有使用到索引，耗费资源多，查询速度慢
explain各列的含义：
id
SELECT识别符。这是SELECT的查询序列号
select_type
SELECT类型,可以为以下任何一种:
SIMPLE:简单SELECT(不使用UNION或子查询)
PRIMARY:最外面的SELECT
UNION:UNION中的第二个或后面的SELECT语句
DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询
UNION RESULT:UNION 的结果
SUBQUERY:子查询中的第一个SELECT
DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询
DERIVED:导出表的SELECT(FROM子句的子查询)
table
输出的行所引用的表
type
联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序:
system:表仅有一行(=系统表)。这是const联接类型的一个特例。
const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次!
eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。
ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。
ref_or_null:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。
index_merge:该联接类型表示使用了索引合并优化方法。
unique_subquery:该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。
index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)
range:只检索给定范围的行,使用一个索引来选择行。
index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
ALL:对于每个来自于先前的表的行组合,进行完整的表扫描。
possible_keys
指出MySQL能使用哪个索引在该表中找到行
key
显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。
key_len
显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。
ref
显示使用哪个列或常数与key一起从表中选择行。
rows
显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。
filtered
显示了通过条件过滤出的行数的百分比估计值。
Extra
该列包含MySQL解决查询的详细信息
Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。
Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。
Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。
Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。
Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。
Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。
Using sort_union(...), Using union(...), Using intersect(...):这些函数说明如何为index_merge联接类型合并索引扫描。
Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。
六、使用场合
有的字段重复内容很多(例如性别字段)，则不要设置索引，因为它不会起作用。
① where条件后边的字段都可以设置索引
② 排序字段适合做索引
③ 索引覆盖
数据查询时查询的字段就是索引的内容，这样索引本身就支持数据的提供。数据查询只通过索引内容就获得需要的信息，就称为索引覆盖。
-- 假设ename和deptno已经是索引，就形成索引覆盖
explain select ename,deptno from emp limit 10,10;
我们需要的信息(ename/deptno)，单纯从索引内部就提供好了，不会再去记录中获取其他不相关的信息，因此索引本身就满足我们对数据的获取，这样的索引和查询结合起来运行速度是最快的，称为是"黄金索引"。
④ 连接查询
select 字段列表 from 表1 join 表2 on 表1.字段=表2.字段(表1.字段 也称为"外键字段")。
外键字段也适合做索引。
七、索引原则
① 字段独立原则
-- 字段独立，可以使用索引
select * from emp where empno=1345629;
-- 字段不独立(不能使用索引)
select * from emp where empno+2=1345629;
② 最左前缀原则
-- 能用索引
select * from emp where ename like "内容%";
-- 不能用索引
select * from emp where ename like "%内容%";
-- 能用索引
select * from emp where ename like "内容__";
-- 不能用索引
select * from emp where ename like "_内容__";
③ 复合索引
假设ename和deptno建立了复合索引。
复合索引的第一个字段，单独作为条件可以使用到该索引。
explain select * from emp where ename like 'abc%';
复合索引的第二个字段，单独作为条件不可以使用到该索引。
explain select * from emp where deptno = 123;
复合索引的两个字段都作为条件，则可以使用该索引。
explain select * from emp where ename like 'abc%' and deptno = 123;
④ or原则
两边都有索引，则会全部都分别使用。
一个有索引，另一个没有索引(与顺序无关)，导致结果一个索引都没有被使用。
八、设计依据
① 被频繁使用的字段设置索引
字段被频繁用在where和order等条件里边。数据表创建完毕，要预估哪些字段被经常使用，就给其创建索引。
② 执行时间长的sql语句考虑设计索引
可以利用"慢查询日志"收集这样的sql语句并优化设计索引
③ 逻辑非常重要的sql语句考虑设计索引
例如商城系统里边，会员给自己账户充值就比较重要。还有会员下订单购物，进行付款的时候也比较重要。
④ 字段内容足够花样化，可以考虑设计索引
反面教材，性别不能设计索引(内容的取值非常少)。
九、前缀索引
索引是给sql语句做优化，前缀索引，是给索引做优化。
如果一个字段的内容的前边的n位信息已经足够可以标识当前的字段内容，就可以把字段的前n位获得出来并创建索引，通过字段内容前n位创建的索引就称为前缀索引。
例如：abc dec bfg haha
前缀索引占据的物理空间要比较小，这样的索引运行速度快、效率高，对mysql整体性能提升有很大帮助。
alter table 表名 add key (字段(位数))
获得字段的前n位：substring(字段,开始位置,长度n)，该系统函数可以获取。
十、in条件索引
假设empno做略索引，in条件也可以使用索引。
select * from emp where empno in (1,2,3);
十一、全文索引
其他索引是把字段的内容作为一个整体进行索引设计。全文索引，类似我们有一篇作文，把作文中的一些关键字给获取出来当成是索引内容。
具体理解就是做like模糊查询，类似baidu搜索一些关键字效果。
字段类型必须为varchar/char/text类型
mysql 5.6.4之前只有Myisam支持，5.6.4之后则Myisam和innodb都支持。
mysql中的全文索引目前只支持英文(不支持中文)，如果需要支持中文可以使用sphinx
现实生产中mysql的全文索引不经常使用，sphinx常使用
-- 假设文章表有个body字段，我们建立全文索引
alter table articles add fulltext index `index_body` (body);
-- 使用
select * from articles where match(全文索引字段) against(内容);
复合全文索引：
alter table articles add fulltext index `index_full`(`title`,`body`);
select * from articles where match(title,body) against('haha');
