Mysql学习日记：L25-索引

目录

一、索引（index）

二、索引的实现原理

三、索引的添加条件

四、索引的创建和删除

五、索引的失效

六、索引的分类

---

一、索引（index）

 

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

对于一本字典来说，查找某个汉字有两种方式。

第一种：一页一页挨着找，直到找到为止，这种查找方式属于全字典扫描，效率比较低。

第二种：通过目录（索引）去定位一个大概的位置，然后直接定位到这个位置，做局域性扫描，缩小扫描的范围，快速的查找。这种查找方式属于通过索引检索，效率较高。

例：t_user

id                        name

-------------------------------------------------------------

1                        jack

2                        lisi

3                        zhangsan

select * from t_user where name='zhangsan';

以上的这条sql语句会从在name字段上进行扫描，因为查询条件时name=‘zhangsan’，

如果没有给name字段添加索引（目录），或者说没有给name字段创建索引，那么mysql会进行全表扫描，会将name字段上的每一个值都比对一遍（一个一个查，先jack，之后lisi，再zhangsan），效率低。

mysql在查询方面主要就两种方式

1.全表扫描

2.根据索引检索

注意：在实际中，汉语字典目录前面是需要排序的，按照a，b，c，d……排序，因为只有排序了才会有区间查找这一说（缩小扫描范围其实就是扫描某个区间）

二、索引的实现原理

就是缩小扫描的范围，避免全表扫描

假设一张用户表：t_user

id（PK）                                name                        每一行记录在硬盘上都有物理存储编号

100                                        zhangsan                  0x1111

20                                          lisi                              0x7777

80                                          wangwu                     0x4444

注意：①在任何数据库当中，主键都会自动添加索引对象，id字段上自动有索引，是因为id是主键PK。

在mysql当中，如果一个字段上有unique约束，那么也会自动创建索引对象

②在任何数据库当中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号。

③在mysql中，索引是一个单独的对象，不同的存储引擎以以不同的形式存在，在MyISAM存储引擎中，索引存储在一个.MYI文件当中；在InnoDB中，索引存储在一个逻辑名称叫做tablespace的当中；在MEMORY存储引擎当中，索引存储在内存当中。不管索引存储在哪里，在mysql当中都以树的形式存在。（自平衡二叉树：B-Tree）

（二叉树遵循左小右大的原则 ，当给字段创建索引之后，会形成一个树的结构，目的是缩小扫描范围，拿到物理存储编号，用编号去定位记录） 

三、索引的添加条件

mysql中，主键上以及unique字段上都会自动添加索引的

什么条件下，我们会给字段添加索引呢？

条件1：数据量庞大（到底多大算庞大，需要测试，因为每一个硬件环境不同）

条件2：该字段经常出现在where的后面，以条件的形式存在，也就是说这个数据总是被扫描

条件3：该字段很少的DML操作update，delete，insert（因为DML之后，数据需要重新排序）

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

四、索引的创建和删除

4.1 创建索引

语法格式：create index emp_ename_index on emp (ename);

给emp表的ename字段添加索引，起名：emp_ename_index

4.2 删除索引

语法格式：drop index emp_ename_index on emp; 

4.3 查看是否使用了索引

explain select * from emp where ename='king';

扫描除了14条记录，说明没有使用索引。type=ALL

在创建了索引后再查

 rows=1，type=ref，所以使用了索引。

五、索引的失效

第①种情况：模糊查询以‘%’开头

select * from emp where ename like '%T';

ename上即使添加了索引，也不会走索引（直接全局查询），因为模糊匹配中以‘%’开头，尽量避免模糊查询中以‘%’开始，这是一种优化策略/手段。

第②种情况：使用or的时候

使用or的时候会失效，如果使用or那么要求or的两边的条件字段都要有索引，如果其中一边有一个字段没有索引，那么另一个字段上的索引也会失效。所以这就是为什么不建议使用or的原因。

 

第③种情况：使用复合索引，没有使用左侧的列查找，索引失效。

复合索引：两个字段或者多个字段联合起来添加一个索引，叫做复合索引。

create index emp_job_sal_index on emp(job,sal);

explain select * from emp where job='manager';（索引有效，因为他是左侧的列）

 explain select * from emp where sal=2450;（索引无效，因为他是右侧的列）

 第④种情况：在where当中，索引列参加了运算，索引失效。

索引有效

 索引无效

 第⑤种情况：在where当中索引列使用了函数

 索引无效

六、索引的分类

索引是各种数据库进行优化的各种手段，优化的时候优先考虑的因素就是索引。

索引的分类

单一索引：一个字段上添加索引

复合索引：两个字段或者多个字段添加索引

主键索引：主键上添加索引

唯一性索引：具有unique约束的字段上添加索引

注意：唯一性比较弱的字段上添加索引用处不大，因为可能会出现大量的重复数据，索引起不了太大的作用，越唯一效率越高。