Mysql数据库索引详解

一、索引是什么：

1.索引是帮助MYSQL高效获取数据的排好序的数据结构。
2.索引存储在文件中，不是在内存中，使用的时候从磁盘中加载到内存中。
3.索引的文件存储形式与存储引擎有关。
存储引擎：不同的数据表在文件系统中的组织形式。
（1）与表同名的存储引擎（Innodb——>.ibd结尾）（聚簇索引）

ibd文件中数据和索引一起存放,frm存放原表数据。
（2）与表同名的存储引擎（Myisam）（非聚簇索引）

.MYD文件存放数据，.MYI文件中存放索引，.frm存放原表数据。

二、索引的创建条件

当唯一率为>80%的时候可以创建索引

三、创建索引最终的解决的问题是IO，解决方法：

1.减少IO次数
2.减少IO量 例如select *

四、哪些情况需要创建索引

1. 主键和唯一键自动建立唯一索引。
2. 频繁作为查询条件的字段应该创建索引。
3. 查询中与其它表关联的字段，外键关系建立索引。
4. 单键/组合索引的选择问题，怎么选?(在高并发下倾向创建组合索引)。
5. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。
6. 查询中统计或者分组字段

五、哪些情况不需要创建索引

1. 表记录太少
2. 经常增删改的表
3. 数据重复且分布平均的表字段。（因此应该只为最经常查询和最经常排序的数据列建立索引如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果）
4. 频繁更新的字段不适合创建索引、因为每次更新不单单是更新了数据库中的数据，还会更新索引文件中的数据。
5. Where条件里用不到的字段不创建索引。

六、索引分类

1.按照索引的存储来划分：
聚簇：innodb
非聚簇：myisam
2.按照使用来分：
主键索引：ID、或唯一键或rowid
唯一索引：列为unique约束的。
普通索引（辅助）：（既不是主键列也不是唯一列作为索引）
组合索引：多个字段一起组成索引。
全文索引：当数据库表中有文章，提取关键字的时候，用Like效率低，所以用全文索引

七、索引查找方式有：

1.通过“id+offset+数据文件”查找。
2.通过“B+树”查找。

八、Innodb和Myisam区别：

1.数据组织的形式不同
2.Innodb支持事务Myisam不支持事务
3.Myisam只支持表锁，Innodb支持行锁和表锁
4.Myisam不支持外键，Innodb支持

图表如下：

既然Innodb优于Myisam，为什么有Myisam？
Mysql最开始的时候索引结构是Myisam。（历史遗留问题）

九、索引文件的结构

1.Hash ：（memory在用）
缺点：
（1）利用hash存储，会将所有的数据文件添加到内存，比较耗费内存空间。
（2）hash等值查询较快，但范围查询较慢。
（3）良好的hash算法很重要，否则空间会多余占用内存。
2.二叉树（AVL树）
特点：最长子树和最短子树之间的高度之差不能超过1；插入性能低，查询高
3.红黑树（相对平衡）：
特点：最长路径不超过最短路径的两倍，根节点是黑色，插入节点是红色，黑色和红色节点不能挨着。
4.B树
Degree(度、阶)：每一个节点里最多放多少个数据。
Innodb默认读取16k，也就是4页。

紫色是索引列(key)的值，黄色表示对应列整行的值，蓝色表示指针域。查一条数据就要搜索16^3 = 2^12次。
5.B+树（共3-4层）
（1）特点：非叶子节点存储key值，叶子节点存储全部的数据
（2）非叶子节点共三个，每个节点只存key值和指针，一条数据大小约为10byte，一个节点共16kb，共能存储1600个数据，则高度为3的B+树共能存储1600160016=40960000个数据。
（3）叶子节点是双向链表，而且是排好序的；在B+Tree上有两个指针，一个指向根节点，另一个指向关键字最小的叶子节点。
（4）B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。
（5）表中的索引，如果有id则可以用id主键作为索引，若没有id，则查找其他列是否有唯一键作为索引，若没有唯一键，则使用6字节的rowid作为索引，rowid对用户不可见（oracle可见（表右键编辑）,mysql不可见）
（6）当name作为索引列的时候，会创建另外一棵B+树，这棵树的索引值为name,叶子节点存储相对应主键的值，再从第一课B+树，根据该主键的值找到目标数据

十、回表

Select id,age from test where name = ‘aaa’：从name的b+树查找主键值，再去主键的b+树查找。这个过程叫做回表。

十一、索引覆盖（using index）

就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。
例：Select id,name from test 。有一个组合索引恰好由id,name两个字段组成。
注意：
如果使用覆盖索引，一定要注意select列表中只取出需要的列，*不可select ，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

十二、索引下推 : （ using index condition）

索引下推与和组合索引相关。
Select * from test where name = ? and age = ?
1.先从存储引擎中把所有符合Name的数据拉取回来，然后在server层进行age的筛选
2.从存储引擎拉取数据的时候把name和age都做了筛选。
很明显，第二种方式效率要高。
谓词下推：
Select t1.name,t2.name from t1 join t2 on t1.id = t2.id
1.先进行表的关联，将需要的id,name取出
2.先把所有需要的字段取出，然后再做关联（效率高）

十三、最左匹配

与组合索引在一起
name,age(组合索引)
Select * from test where name = ? and age = ?
Select * from test where name = ?
Select * from test where age = ?
Select * from test where age = ? and name = ?
第124句都能用到组合索引，第四句的where条件会在server层将name和age顺序做个调换，再利用组合索引。

十四、 主键索引和唯一索引的区别：

唯一索引可以有空值，主键索引不可以。

十五、什么时候会有组合索引失效？

（1）like ‘%’ ：like查询%开头都会失效。
（2）组合索引如果中间某一个值使用了范围查询也会失效。
Id,name age
Id = 10 AND name >’smith’ AND age = 20 :索引只使用了id和Name ,age 用不到，因为中间出现了范围查询。
（3）使用函数的时候会失效
（4）使用表达式的时候会失效
（5）类型不匹配的时候，涉及隐式转化的时候会失效，例如where id +1 =4。
（6）数据量特别大的时候也会失效
（7）or在某些情况下会失效

组合索引的B+数为1棵，索引值有两列。