MySQL 索引相关知识

今天又手贱，路边走的时候顺手拽一片叶子下来。不得不说，不知是那叶子漂亮，还是单身久了看片叶子都眉清目秀的。就把它夹在了书里，做个标本。等我第二天去观察标本好了没，哦，找不到了，我就一张一张的翻过书页，找到了。
把时间拨回手贱之前。
可我还是手贱，又把人家叶子搞了一枚下来。叶子就是漂亮，并不是我单身久！这次学聪明了，在把它夹在书本里的时候，我记下了它夹在了哪一页，古娜拉黑暗之神，现。一瞬间就找到了。
这就是有无索引的区别。
那么今天简单介绍下MySQL中索引的相关概念。

1、索引及其好处

2、索引底层结构

3、Innodb、Myisam两种存储引擎的索引

4、索引的SQL语句

5、索引的优化与失效

1、为什么要使用索引

本文最前面，就是我对索引的一部分理解。
定义：索引是存储引擎用于快速找到记录的一种数据结构。
好处，那肯定是搜索数据变快，这就是索引存在的意义。

2、索引的底层结构

搜索数据，并且搜索数据的速度很快，那索引是怎么实现的呢？
上面刚说过，索引是一种数据结构，就是通过哈希表跟B树这两种数据结构实现数据的快速搜索。
实际上在MySQL中，使用的其实是B树或者说B树里面的B+树，B树也叫n阶平衡树。

对于哈希表来说，它的存储结构是key-value类型的，如果有一个好的哈希算法，减少了哈希碰撞，它的查找速度就可以达到O(1)。哈希表的缺点就是不能一次得到连续范围内的值。
哈希表的这个缺点可以由B+树得到解决。
拿一个二叉树举例子，对于这个二叉树来说，如果它的任一棵子树，左孩子的值小于父节点的值，右孩子的值大于父节点的值，这棵二叉树就被称为二阶平衡树。这也就是B-树。
所谓B+树，就是在B-树的基础上，在叶子结点上将所有的数据串在一个data域中，这些data域的值自然是从小到大，当命中索引找到欲查找范围的下限时，自然而然可以完成范围查找。
而且B树的查找速度也不赖。

3、Innodb、Myisam的索引

首先结论，Innodb是聚集索引，而Myisam是非聚集索引。
在说这两个概念之前，还有两个概念，主键索引和辅助索引（非主键）
主键索引，就是以主键作为索引，如果没有建索引，MySQL默认将 primary key设为主键索引；
辅助索引，做索引的不是主键。

InnoDb的聚集索引：

这是我的数据库里面的表，有 .frm（表的创建结构存储） 和 .idb 两个文件，
这个 .idb 文件存储的是表中数据和索引
所谓聚集索引是将表中的数据跟索引地址存储在了一起
对于主键索引来说，在B+树的叶子结点上存着该表的全部信息，
对于辅助索引，叶子节点上存的是该属性建立的索引 + 链接到主键索引的地址，也就是你通过辅助索引是拿不到该表的全部信息，还是要通过链接主键索引去得到整个表的信息。
如下图：

Myisam的非聚集索引：
对于表的数据和索引，分别用不同的文件存储起来
有 .frm (表的创建结构存储)
.MYD (表的数据存储)
.MYI (表的索引存储)

对于非聚集索引来说，只要对该属性创建了索引，都会在树的叶子上存着该表的全部信息的地址。也就是只要有索引，都只在对当前属性（字段）的创建的索引下就可以得到该表全部信息。

4、索引有关的SQL语句

1）建表的时候创建索引
比如

create table table_name (
id int primary key,
name varchar(5),
index (id)
);

上面几行代码中， index(id) 表示对 id 这个属性创建一个索引。
当然了，上面刚说过，MySQL会对 primary key自行创建索引。

2）表存在时添加索引
create index 索引名 on 表名(属性名)；
如create index idx_id on id(id);
对于索引的命名最好有一个规范，即 idx_属性名，联合索引，idx_属性名1_属性名2_属性名n

其中，这个 KEY 后即为该表的索引，展示的格式为 KEY 索引名（索引对应的字段）
3）删除索引
drop index 索引名 on 表名;

4）展示索引
show create table 表名;(\G、\g)

5）展示索引的名中过程
在SQL语句前面加 explain，如
explain select * from goods;
如，我的 test 库下的 goods 表，我是没有单独为它创建索引的，但是表中的字段（属性）name 是 primary key，对其它字段并没有创建索引

搞这样一行语句，
explain select * from goods where name = '海上灵光';，就会命中索引

这个row = 1表示这个操作影响的行数，我只查找到一行数据就找到了。
explain select * from goods where price = 9999;

这次来一个没有索引的，要进行查找5行的操作才能证明是我要找的那一行数据（表里共5行）。对于复杂的表来说，如果不创建索引，可想而知查询速度会有多么慢！
对 price 字段创建索引
create index idx_price on goods(price);

explain select * from goods where price = 9999;

创建索引后影响行数就变成了一行，索引的使用还是很有效的。

5、索引的优化与失效

首先，要明确一个道理，什么东西都不是越多越好，正所谓月盈则亏，水满则溢，阴阳相生嘛。
并不是给每一个字段创建索引就万无一失了，越多的创建索引会增加系统的性能损耗。所以尽可能只给需要的字段添加索引即可。

索引的失效：
1）like 的通配符在前面，索引就会失效
像 _**** 、%****

这个 name 字段本是 primary key，是可以命中索引的，但是现在影响的行数还是整个表的行数。

这个就可以命中索引，只不过我的整个表中以“豫”开头的有两行数据，它就影响了两行

2）没有使用联合索引的第一个字段
对 goods 表创建一个联合索引，对num和author创建索引，名为idx_num_author

在下面语句，能命中对num和author创建的联合索引idx_num_author，只有前面三个SQL语句

create index idx_num_author on goods (num, author);//num在前，author在后面
explain select * from goods where num = 2;
explain select * from goods where num = 2 and author = '许嵩';
explain select * from goods where author = '许嵩' and num = 2;
explain select * from good where author = '许嵩';

虽然是一个联合索引，但是不能命中联合索引的第二项，也就是 author字段

3）使用的 not in、!=、MySQL函数、类型转换、or等都会导致索引失效，感兴趣的童鞋可以自己验证验证。
MySQL函数：
max()函数，查询指定字段中最大的数据记录 eg:select max(Sage) from student
min()函数，查询指定字段中最小值
count()函数，计数使用 eg:select count(*) from Student;
avg()函数，指定字段的平均值
sum()函数，指定字段求和

几个索引的优化方法：
1）where 后字段宜加索引
2）在 InnoDB 上，根据非主键索引查询其它（非主键）字段信息，建立联合索引，联合索引顺序参考第一条
3）遇见 using filesort要优化
using filesort查找的时候顺便对文件排了个序才查找，这种就肯定很慢了。
4）连接两个表进行查询时，对大表字段创建索引。
因为小表是必定进行整表搜索的。
大小表的相对概念：
对其中某一个表进行某种条件筛选后，行数大的就是大表，另一个就是小表。
5）不要使用子查询，使用连接查询
子查询会产生一个中间表，影响系统开销。
6）比较大的优化（比较高级一点的优化）
①慢日志查询
②查看磁盘 I/O 读写的数据量
③show status
查看 MySQLd的运行参数，可以查看select、insert、delete、update的执行频率再分析