Mysql 索引原理详解

索引是什么？

索引是帮助mysql 高效获取数据的数据结构，索引好比是一本书前面的目录，能加快数据库的查询速度

索引的优势和劣势

优势：

1、可以提高数据检索的效率，降低数据库的IO车成本，

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

被索引的列会自动进行排序，包括单列索引和组合索引，只是组合索引的排序要复杂一些，如果按照索引列进行排序的话，order by 的语句来说，效率会提高很多

劣势：

索引会占据磁盘空间

索引虽然会提高查询效率，但是会降低更新表的效率，比如每次对表进行增删改的操作。

mysql不仅要保存数据，还要保存或者更新索引文件

索引的分类

单列索引、组合索引、全文索引、空间索引、位图索引

索引的使用：

--单列索引之普通索引

creat index index_name on table (column(lengh))

alter table table_name add index (column(lengh))

-- 单列所以之唯一索引

create unique index index_name on table (column(lengh))

alter table table_name add unique index (column(lengh))

--单列索引之唯一索引

create fulltext index index_name on table (column(lengh))

alter table table_name add fulltext index_name(cloumn(lengh))

--组合索引

alter table article add index index_title_time (title(50),time(10));

删除索引

drop index index_name on table

查看索引

show index from table_name

索引的原理分析

索引的存储结构

1、索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引。

2、MyISAM 和 innodb存储引擎，只支持B+ tree索引，也就是说默认使用Btree ，不能够更换。

3、MEMORY 和 HEAP存储引擎， 支持HASH索引 和 BTREE索引

B树展示

B树是为了磁盘或者其他存储设备而设计的一种多叉平衡查询树，

B树的高度一般在2-4这个高度，树的高度直接影响IO的读写的次数。

如果是三层树结构 -- 支撑的数据可以达到20G，如果是4层树结构---支撑的数据可以达到几十T

BTree 和 B+Tree 的区别

BTree 和 B+Tree 的最大区别在于非叶子节点是否存储数据的问题。

BTree 是非叶子节点和叶子节点都会存储数据

B+Tree只有叶子节点才会存储数据，而存储的数据都在一行上，而且这些数据都是有指针指向的，也就是说有顺序的。

索引的使用场景，那些情况需要创建索引？

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

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

3、多表关联查询中，关联字段应该创建索引 on 两边都要建立索引

4、查询中排序字段应该建立索引

5、覆盖索引好处是？不需要回表 组合索引

6、统计或分组字段，应该建立索引。

那些情况不应该建立索引？

1、表记录很少，索引建立是要有存储开销的

2、频繁更新 索引是需要维护的

3、查询字段使用频率不高

为什么使用组合索引？

由多个字段组成的索引，使用的顺序就是创建的顺序。

索引失效原因分析

1、like以%开头索引无效，当like以%/&结尾有效。
2、or语句两边没有全部使用索引
3、组合索引，使用的不是第一列索引，索引失效，即最左匹配规则。
4、数据类型出现隐式转换，如varchar不加单引号的时候可能会自动转换为int类型，这个时候索引失效。
5、在索引列上使用IS NULL或者IS NOT NULL时候，索引失效，因为索引是不索引空值的。
6、在索引字段上使用not 、<> 、!=时不会使用索引，对于这样的处理只会进行全表扫描（>=和=<可以使用索引）
7、对索引字段进行计算操作，函数操作时不会使用索引。
8、对全表扫描速度比索引速度快的时候不会使用索引。