浅谈一下索引

目录

索引概述

索引实现原理

注意事项

相关语法

创建

删除

怎么查看一个SQL语句是否使用了索引

索引失效

情况一

情况二

情况三

情况四

情况五

---

索引概述

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

如果被扫描的字段没有添加索引，查找时MySQL会进行全扫描，将字段的每个值都对比一遍，效率低。 MySQL在查询方面主要有两种方式：1.全表扫描 2.根据索引检索

索引实现原理

在MySQL中，索引是一个单独的对象，不同的存储引擎以不同的形式存在。在MyISAM存储引擎中，索引存储在一个 .MYI文件中。在InnoDB引擎中，索引存储在tablespace中，在memory存储引擎当中，索引被存储在内存中。不管索引存储在哪里，索引在MySQL当中都是以树的形式存在（自平衡二叉树：b-tree）

MySQL的索引底层是b-tree实现的，遵循左小右大原则存放。

举例：

假如现在有一条查询：

select name from user where id=100;

mysql发现自动上有索引对象，MySQL会通过索引对象进行查找。

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

因为索引底层的二叉树遵循左小右大原则，所以数据定位会比较快。定位到之后，通过id=100得到硬盘的物理存储编号，此时SQL语句就会转换为select name from user where 物理存储编号 = 0x...... ;

注意事项

在MySQL当中，主键上都会自动添加索引。一个字段上如果有唯一约束，也会自动创建索引。

可如果我们向主动给其他字段添加索引，要考虑什么？

1.数据量庞大时

2.该字段经常出现在where后面，以条件的形式存在

3.该字段很少增、删、改操作，因为增删改操作过后，索引需要重新排序

4.索引建议不要乱加，因为索引也需要维护，太多的话会降低性能、增大系统负担。建议查询的时候通过主键或唯一约束进行查询

相关语法

创建

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

意思是在为某个表上的某个字段，添加指定名称的索引

如果添加的是复合索引

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

复合索引：两个或更多字段联合起来的索引

删除

drop index 索引名 on 表;

删除某个表上的指定索引

怎么查看一个SQL语句是否使用了索引

假设现在又一张表叫做emp，共14条记录

 

该表结构如下

 

在执行的SQL前加上 EXPLAIN

EXPLAIN SELECT * FROM `emp` WHERE ename='KING';

 

我们注意到，这条记录里的type 是 ALL ,表示这条语句是全表扫描

那我们给ename这个字段加个索引

CREATE INDEX emp_ename_index ON emp(ENAME)

然后我们再执行同样的语句

EXPLAIN SELECT * FROM `emp` WHERE ename='KING'

最终得到了以下结果

 

这条记录里的type 是 ref，表示使用了索引而且不为主键和unique进行检索，而rows=1，表示只检索了一条就出结果了

索引失效

情况一

假如我们对ename这个字段进行以下查询

SELECT * FROM `emp` WHERE ename LIKE '%KI%'

ename上即使加了索引，也不会走索引

原因：模糊查询的条件语句以 "%"开头了。索引的排列顺序是根据比较字符串的首字母排序的。如果把 % 放在了前面，最左的 n 个字母 便是模糊不定的，无法根据索引的有序性准确的定位到某一个索引，只能进行全表扫描，找出符合条件的数据

总结：如果字段添加了索引，左模糊查询不会按索引方式查找。要尽量避免左模糊

情况二

使用or的时候可能会失效。如果使用or，那要求or两边的字段都有索引，才会走索引。如果其中一边的字段没有索引，另外一边的字段即便有索引，也会失效。

SELECT * FROM `emp` WHERE EMPNO=7782 OR MGR=7566

解决办法：给没有索引的字段添加索引, 这时候数据库会默认的将两个索引合并, 从而避免全表扫描

情况三

使用复合索引时，没有使用左侧的列查找，索引失效。

MySQL当中有一原则叫做最左前缀原则。

最左前缀原则就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。

情况四

索引列加入了运算时，也会导致索引失效。

比如

SELECT * FROM `emp` WHERE EMPNO+1=7783

情况五

索引列中使用了函数

SELECT * FROM `emp` WHERE LOWER(ename)='king'