13、索引

一 什么是索引

1）概念

索引就相当于一本书的目录，通过目录可以快速的找到对应的资源。

2）检索的方式

在数据库方面，查询一张表有两种检索方式：

第一种：全表扫描

第二种：根据索引检索(效率很高）

3）索引提高效率的原因

最根本的原因就是缩小了扫描的范围。就像用字典的目录，可以快速定位。

4）索引的使用

索引虽然可以提过检索效率，但是不能随意添加索引，因为索引也是在数据库中的对象，也需要数据库不断的维护，是有维护成本的。

比如该字段数据经常被修改，则不适合添加索引，因为数据一旦修改，索引需要重新拍下，进行维护。

5）索引添加的对象

添加索引是给某一个字段，或者说某些字段添加索引。

 

实例：

select ename,sal from emp where ename = '张三';

当ename字段上没有添加索引的时候，以上sql语句会进行全表扫描，扫描ename字段中所有的值。

当ename字段上添加索引的时候，以上sql语句会根据索引扫描，快速定位。

 

 

二 语法

index 动词本意：为…编索引;将…编入索引

创建索引对象：
CREATE INDEX 索引名 ON 表名(字段名);
create index emp_sal_index on emp(sal);
在emp表sal字段 之上 添加名为emp_sal_index的索引 （on 介词 在..上）

删除索引对象：
drop index 索引名称 on 表名;

 

 [ explain 命令 ] 查看一条SQL的执行情况

1）没有添加索引

explain 本意 解释说明  [ɪkˈspleɪn]

重点看type字段 all代表全表扫描 rows 是扫描的记录数目

explain select ename,sal from emp where sal=5000;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

2）添加一条索引

create index emp_sal_index on emp(sal);//给sal字段添加一个索引

3）再次查看SQL语句执行

type字段值为ref rows字段值为1  只扫描了一条数据

mysql> explain select ename,sal from emp where sal=5000;
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ref  | emp_sal_index | emp_sal_index | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+

 

三 什么时候考虑给字段添加索引

1、数据量庞大（根据客户的要求 根据线上的环境）

2、该字段很少DML操作 （因为数据频繁修改 索引需要经常维护）

3、该字段经常出现在where子句中 （经常查询的字段）

 

四 注意点

主键和unique字段会自动添加索引，不用手动添加。

根据主键查询效率较高，尽量根据主键进行查询。

 

五 索引实现的原理

索引底层采用的数据结构是 B + Tree。

索引通过B Tree缩小扫描范围，底层索引进行了排序和分区。索引会写到数据在表中的“物理地址”，最终通过索引检索到数据之后，获取到了关联的物理地址，通过物理地址定位表中的数据，效率是最高的。

select ename form emp where ename='张三';

通过索引后转换为：

select emane from emp 物理地址 = 0 x 3；

原理图：

 

 

 

 

六 索引的分类

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

复合索引: 给多个字段联合起来添加1个索引

主键索引：主键上会自动添加索引

唯一索引：有unique约束的字段上会自动添加索引

 

七 索引什么时候失效

select ename from emp where ename like '%A%';
模糊查询的时候，第一个通配符使用的是%，这个时候索引是失效的。

 

mysql 如何给SQL添加索引

 

1.添加PRIMARY KEY(主键索引)

alter table `table_name` add primary key(`column`);

2.添加UNIQUE(唯一索引)

alter table `table_name` add unique(`column`);

3.添加普通索引

alter table `table_name` add index index_name(`column`);

4.添加全文索引

alter table `table_name` add fulltext(`column`);

5.添加多列索引

alter table `table_name` add index index_name(`column1`,`column2`,`column3`);