SQL索引

索引：

1、索引是什么

索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制，一张表的一个字段可以添加一个索引，索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。
当然，多个字段联合起来也可以添加索引。

对于一本字典来说，查找某个汉字有两种方式:
①一页一页挨着找，直到找到为止，这种查找方式属于全字典扫描第一种方式:效率比较低。
②第二种方式:先通过目录(索引) 去定位一个大概的位置，然后直接定位到这个位置，做局域性扫描，缩小扫描的范围，快速的查找。这种查找方式属于通过索引检索，效率较高。

如果查找某字段时，改字段上没有添加索引(目录)或者说没有给该字段创建索引，MysQL会进行全扫描，会将该字段上的每一个值都比对一遍。效率比较低
MySQL在查询方面主要就是两种方式:第一种方式:全表扫描第二种方式:根据索引检索。例如给某一字段加上索引，那么只需进行区域扫描。

注意:在实际中，汉语字典前面的目录是排序的，按照a b c d e f…排序，为什么排序呢?因为只有排序了才会有区间查找这一说! (缩小扫描范围其实就是扫描某个区间罢了!
在mysql数据库当中索引也是需要排序的，并且这个所以的排序和TreeSet数据结构相同。TreeSet (TreeMap)底层是一个自平衡的二又树!在mysql当中索引是一个B-Tree数据结构。
索引左小右大原则存放，采用中序遍历方式遍历数据（先访问左子树，再访问根节点，再访问右子树）。

1.2、索引的实现原理

提醒1:在任何数据库当中主键上都会自动添加索引对象，另外在mysql当中，一个字段上如果有unique约束的话，也会自动创建索引对象。
提醒2：在任何数据库中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号。
提醒3:在mysql当中，索引是一个单独的对象，不同的存储引擎以不同的形式存在，

在MyISAM存储引擎中，索引存储在一个.MYI文件中。
在InnoDB存储引警中索引存储在一个逻辑名称叫做tablespace的当中。
在MEMORY存储引擎当中索引被存储在内存当中。

不管索引存储在哪里，在mysql中索引都是一个树的形式存在。

2、索引形式

索引的树形式（B-tree）：


注意：

主键和unique字段上都会自动添加索引！

那么什么条件下考虑对字段添加索引？

①数据量庞大（根据硬件环境不同）
②该字段经常出现在where后面，以条件的形式存在，也就是该字段经常被扫描
③该字段很少的DML操作（因为DML之后，索引需要重新排序）

建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能。
建议通过主键查询，建议通过unique约束的字段进行查询，效率是比较高的。

3、添加索引

3.1 创建索引

比如现在要给room表里的name字段加上索引。

create index 索引名称 on 表名(字段名);
create index room_name_index on room(name);

3.2 删除索引

drop index 索引名称 on 表名;
drop index room_name_index on room;

3.3 查看sql语句是否使用索引进行检索

查看这条where语句是都使用索引进行检索

explain select * from t_room where t_name = '吴京';

rows = 3：扫描3条记录,说明没有使用索引
rows = 1：只扫描一条记录，说明使用索引
type = ALL：全盘扫描 
type = ref：使用索引

在添加了索引之后，能看到与之前的区别：

3.4 索引的失效

索引有时候也会失效
例如：
①模糊查询以%开头时

select * from t_room where t_name like '%T';

这里即使t_name加上索引，也不会走索引，因为走索引必须避免“%”开始！这是一种优化的手段/策略。当使用模糊查询且以百分号为开头，索引便会失效。
②使用or的时候，要求or两边的字段必须都要有索引，才会走索引，如果有其中有一个字段没有字段，那么另一个字段索引也会失效。
③使用复合索引的时候没有使用左侧的列查找，索引失效。
复合索引：两个字段或者更多的字段联合起来添加一个索引，叫做复合索引。

create index 索引名 on 表名(字段名1，字段名2);

那么查询时只有使用左边的字段名1查询才会用到索引，利用字段2查询便会失效。

index_merge:
我们的 where 中可能有多个条件(或者join)涉及到多个字段，
它们之间进行 AND 或者 OR，那么此时就有可能会使用到 index merge 技术。
index merge 技术如果简单的说，其实就是：
对多个索引分别进行条件扫描，然后将它们各自的结果进行合并(intersect/union)。

④当where当中的索引列参加了运算，索引失效。
⑤在where当中索引列使用了函数。
⑥类型转换时索引失效。

索引分类

索引是各种数据库进行优化的重要手段。优化的时候优先考的因素就是索引，索引在数据库当中的分类：
单一索引:一个字段上添加索引。
复合索引: 两个字段或者更多的字段上添加索引。
主键索引:主键上添加索引。
唯一性索引: 具有unique约束的字段上添加索引，但是索引效率不高（都唯一了还索引个p）。