MySQL索引学习笔记

在学校学习MySQL课程的时候没认真，直到前些时间面试官问道了解MySQLl索引吗？也只能尴尬回答是为了方便查询，于是花时间重新学习下，留下记录方便复习。

---

一、索引是什么？

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针（也可以说索引是帮助MySQL高效获取数据的数据结构）。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

二、建立索引的作用

索引非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。索引能够轻易将查询性能提高好几个数量级，总的来说就是可以明显的提高查询效率。

索引的好处：

1.提高数据检索的效率，降低数据库的IO（数据库文件的读写）成本。

2.在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

我们知 道，每个索引中的数据都是按照索引键键值进行排序后存放的，所以，当Query 语句中包含排序分组操作时，如果排序字段和索引键字段刚好一致，MySQL Query Optimizer 就会告诉 mysqld 在取得数据后不用排序了，因为根据索引取得的数据已经满足客户的排序要求。
那如果是分组操作呢？分组操作没办法直接利用索引完成。但是分组操作是须要先进行排序然后分组的，所以当Query 语句中包含分组操作，而且分组字段也刚好和索引键字段一致，那么mysqld 同样可以利用索引已经排好序的这个特性，省略掉分组中的排序操作。
排序分组操作主要消耗的是内存和 CPU 资源，如果能够在进行排序分组操作中利用好索引，将会极大地降低CPU资源的消耗。

索引的坏处：

1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

2.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚集索引那么需要的空间就会更大。

3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

3.什么情况下使用索引？

适合简历索引:

1.主键自动建立唯一索引。
2.频繁作为查询条件的字段应该建立索引。
3.查询中与其它表关联的字段，外键关系建立索引。
4.查询中排序的字段，排序字段若通过索引去访问将大大提高排序效率
5.查询中统计或者分组字段

不适合建立索引:

1.表数据较少的
2.经常增删改查的表或者字段
3.where条件里用不到的字段
4.过滤性（条件中存在>,<等）不好的不适合建立索引
5.唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件

索引的建立应该是随机应变而不是盲目添加。

4.索引的分类

1.从存储结构上来划分：BTree索引（B-Tree或B+Tree索引），Hash索引，full-index全文索引.R-Tree索引。这里所描述的是索引存储时保存的形式
2.从应用层次来分：普通索引，唯一索引，复合索引
3、根据中数据的物理顺序与键值的逻辑（索引）顺序关系：聚集索引，非聚集索引

平时讲的索引类型一般是指在应用层次的划分。

普通索引：即一个索引只包含单个列，一个表可以有多个单列索引

唯一索引：索引列的值必须唯一，但允许有空值

复合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

聚簇索引(聚集索引)：并不是一种单独的索引类型，而是一种数据存储方式。具体细节取决于不同的实现，InnoDB的聚簇索引其实就是在同一个结构中保存了B-Tree索引(技术上来说是B+Tree)和数据行。

5.索引的创建

索引的创建有三种方法分别是在创建数据表时、CREATE 在已经存在的表上创建索引、ALTER TABLE 在已经存在的表上创建索引

1.在创建数据表时创建索引

CREATE TABLE 表名(</