【超详细】关于数据库索引的解答

关于数据库索引

索引概念

1、索引就是在无序的数据中建立查询引导，每次查询就可以根据索引快速找到目标数据（类似字典的目录a-z）； 	
2、索引是一种数据结构，她可以把表中的数据按照某种规则（如哈希表、B树或B+树等）进行排序；

索引分类

1. 主键索引

    		数据列不允许重复，不允许为null，可以被引用为外键，一个表只能有一个主键索引； 	
   

2. 唯一索引

   	数据列不允许重复，允许为null，不可以被引用为外键，一个表可以创建多个唯一索引； 	
   

3. 普通索引

   	基本的索引类型，没有唯一性限制，允许为null值，不可以被引用为外键，一个表可以创建多个普通索引； 	
   

4. 聚集索引（聚簇索引）

   	在聚集索引中，表中数据行的物理位置与逻辑值（索引和数据为同一个文件）的顺序相同，一个表中只能包含一个聚集索引，因为物理顺序只能有一个。聚集索引通常提供更快的数据访问速度。
   

5. 非聚簇索引

   	索引和数据分开的索引。其中MyISAM底层采用的就是非聚簇索引，使用myi索引文件和myd数据文件分离，索引文件仅保存数据记录的指针地址。叶子节点data域存储指向数据记录的指针地址。(底层存储结构：    frm -表定义、 myi -myisam索引、myd-myisam数据) 	
   

6. 覆盖索引

   	覆盖索引指索引中包含了查询中的所有字段，这样就不需要回表查询了 	
   

7. 联合索引（组合索引）

   	使用多个列来组成一个索引，遵从最左前缀原则 	
   

8. 全文索引

   	主要用于海量数据的搜索，适用于海量数据的关键字模糊搜索，简易版的搜索引擎。
   

索引优点

1、提高查询速度；
2、提高group by、order by的效率；

索引缺点

1、新增和更新数据需要更新索引，相较于没有索引速度慢；
2、索引较多的时候占用内存空间，.myi后缀的文件存储的就是索引文件；

索引失效场景

1、模糊查询的前导通配符使用“%”时失效（如 like ‘%hello’）；
2、查询条件未使用索引字段过滤；
3、数据类型不匹配，如果字段是整型，但是查询的时候使用了引号，会导致隐式类型转化，导致索引失效；
4、使用函数操作；
5、or运算，如果两边有一个没有使用索引会导致索引失效；
6、使用not in 和not exists运算，逐渐索引使用not in有效，普通索引使用not in关键字会失效；
7、连表查询时连接字段没有索引会导致索引失效；
8、联合索引不满足最左匹配原则；
9、查询条件后面同一个表两个字段“列对比”会导致索引失效，哪怕两个字段都建立了索引；
10、使用order by语句的时候前面没有where或者limit关键字，sql将不会走索引；

什么是回表

1、查询结果中有大量的非索引列，索引就需要到表中找到相应的列信息；
2、使用聚集索引（主键或第一个唯一索引）就不会回表，普通索引会回表；