MySQL索引

谈到MySQL索引，必然会想到索引概念，分类，结构，以及最左原则。

 

概念：索引是对数据库表中一个或多个列的值进行排序的结构，是帮助MySQL高效获取数据的数据结构

 

索引分类：：普通索引、唯一索引、主键索引、全文索引，组合索引。

普通索引：只是为了方便快速查询。（无限制）。

主键索引：primary key ：加速查找+约束（不为空且唯一）。

唯一索引：unique：加速查找+约束 （唯一）。

全文索引：用于搜索很长一篇文章的时候，效果最好。

组合索引：几个常用于搜索的列，组成的索引。遵循最左原则。

 

创建索引的原则（重中之重）

索引虽好，但也不是无限制的使用，最好符合一下几个原则

1） 最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2）较频繁作为查询条件的字段才去创建索引

3）更新频繁字段不适合创建索引

4）若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)

5）尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

6）定义有外键的数据列一定要建立索引。

7）对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

8）对于定义为text、image和bit的数据类型的列不要建立索引。

 

常用的索引结构：hash，二叉树，b树，b+树。

　　hash索引：索引结构是key-value结构，类似于大多数语言里的HashMap结构。这种结构将数据的key和offset保存在内存里，真是数据可以存储在disk中，从而突破内存大小的限制。

　　

　　二叉树：二叉树是一种每个结点至多只有两个子树（即二叉树的每个结点的度不大于2），并且二叉树的子树有左右之分，其次序不能任意颠倒。

　　缺点：单边递增

 

b树：

 

 

b+树

 

 

为什么使用数据索引能提高效率？

1. 数据索引的存储是有序的
2. 在有序的情况下，通过索引查询一个数据是无需遍历索引记录的
3. 极端情况下，数据索引的查询效率为二分法查询效率，趋近于 log2(N)

 

非聚集索引和聚集索引的区别在于？

　　通过聚集索引可以查到需要查找的数据， 而通过非聚集索引可以查到记录对应的主键值 ， 再使用主键的值通过聚集索引查找到需要的数据

 

加索引的副作用？

　　每次给字段建一个新索引， 字段中的数据就会被复制一份出来， 用于生成索引。 因此， 给表添加索引，会增加表的体积， 占用磁盘存储空间。

 

什么叫覆盖索引？辅助索引？聚集索引？联合索引？

　　覆盖索引：

　　　　1.覆盖索引是一种数据查询方式，不是索引类型
　　　　2.在索引数据结构中，通过索引值可以直接找到要查询字段的值，而不需要通过主键值回表查询，那么就叫覆盖索引
　　　　3.查询的字段被使用到的索引树全部覆盖到

　　　　举例子：

　　　　假设你定义一个联合索引

　　　　CREATE INDEX idx_name_age ON user(name,age);
　　　　查询名称为 liudehua 的年龄：

　　　　mysql> select name, age from user where name = 'liudehua';
　　　　上述语句中，查找的字段 name 和 age 都包含在联合索引 idx_name_age 的索引树中，这样的查询就是覆盖索引查询。

　　辅助索引（也叫非聚集索引）：

　　　　辅助索引，也叫非聚集索引。叶子节点中并不包含行记录的全部数据。

　　聚集索引：

　　　　叶子节点中包含行记录的全部数据。

　　　　注意：一个表中只能拥有一个聚集索引。

　　联合索引：

　　　　联合索引是指对表上的多个列进行索引。

　　　　注意：联合索引遵循最左原则

 

哪些情况需要创建索引

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段应该创建索引
3. 查询中与其他表关联的字段，外键关系建立索引
4. 频繁更新的字段不适合建立索引，因为每次更新不单单是更新了记录还会更新索引
5. WHERE条件里用不到的字段不创建索引
6. 单键/组合索引的选择问题，who?(在高并发下倾向创建组合索引)
7. 查询中排序的字段，排序的字段若通过索引去访问将大大提高排序速度
8. 查询中统计或者分组字段

 

哪些情况不要创建索引

1. 表记录太少
2. 经常增删改的表
3. 提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE、和DELETE。
4. 因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
5. 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据建立索引。
6. 注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

 

innodb索引和myisam索引的区别

MyISAM索引文件和数据文件是分离的，索引文件的data域保存记录所在页的地址（物理存储位置），通过这些地址来读取页，进而读取被索引的行数据。

 

 

InnoDB存储引擎表是索引组织表，即按照主键的顺序存储数据。