MySQL索引

1.索引是什么

　　mysql官方对索引的定义为：索引是帮助MySQL高效获取数据的数据结构

　　由此得到索引的本质：索引是数据结构

　　索引的目的在于提高查询效率，可以类比字典（排好序的快速查找数据结构）

　　a.详解

　　　　在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，

　　　　这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引，下图就是一种可能的索引方式示例:

　　　　

　　　　左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址

　　　　为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针

　　　　这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录

　　b.结论

　　　　数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的

　　　　基础上实现高级查找算法，这种数据结构就是索引

 

　　一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上

　　平常所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，次要索引，复合索引，

　　前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除B+树这种类型的索引之外，还有哈希索引等

2.索引优势

　　1）提高数据检索的效率，降低数据库的IO成本

　　2）通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

3.索引劣势

　　1）实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的

　　2）虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE、 DELETE。因为更新表时，MySQL不仅要保存数据，

　　      还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息

　　3）索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优的索引，或者优化查询

4.mysql索引分类

　　1）单值索引

　　　　即一个索引只包含单个列，一价目表可以有多个单列索引

　　2）唯一索引

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

　　3）复合索引

　　　　即一个索引包含多个列

　　基本语法

创建
CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));

ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnname(length))

删除
DROP INDEX [indexName] ON mytable

查看
SHOW INDEX FROM table_name

5.mysql索引结构

　　1）BTree索引

　　2）Hash索引

　　3）full-text全文索引

　　4）R-Tree索引

6.哪些情况需要创建索引

　　1）主键自动建立唯一索引

　　2）频繁作为查询条件的字段应该创建索引

　　3）查询中与其它表关联的字段，外键关系建立索引

　　4）频繁更新的字段不适合创建索引（因为每次更新不单单是更新了记录还会更新索引文件）

　　5）where条件里用不到的字段不创建索引

　　6）单键、组合索引的选择问题（在高并发下倾向创建组合索引）

　　7）查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

　　8）查询中统计或者分组字段

7.哪些情况不需要创建索引

　　1）表记录太少

　　2）经常增删改的表

　　3）数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引

　　　　注意：如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果

 8.簇集索引与非簇集索引

　　MySQL innodb的主键索引是簇集索引，也就是索引的叶子节点存的是整个单条记录的所有字段值，不是主键索引的就是非簇集索引，非簇集索引的叶子节点存的是主键字段的值。

9.回表

　　举个例子：

　　表tbl有a,b,c三个字段，其中a是主键，b上建了索引，然后编写sql语句

　　　　SELECT * FROM tbl WHERE a=1

　　　　这样不会产生回表，因为所有的数据在a的索引树中均能找到

　　　　SELECT * FROM tbl WHERE b=1

　　　　这样就会产生回表，因为where条件是b字段，那么会去b的索引树里查找数据，但b的索引里面只有a,b两个字段的值，没有c，那么这个查询为了取到c字段，就要取出主键a的值，然后去a的索引树去找c字段的数据。查了两个索引树，这就叫回表。

10.索引覆盖

　　是查这个索引能查到你所需要的所有数据，不需要去另外的数据结构去查。其实就是不用回表。