mysql-210715-01---索引

mysql-210715-01—索引

• 索引
• 了解索引
• 查看sql语句的执行计划
• 创建索引对象
• 删除索引对象
• 索引的原理
• 索引的分类
• 索引的失效

---

索引

了解索引

索引底层采用的数据结构：B + Tree树

什么是索引，有什么作用

主键和具有unique约束的字段会自动添加索引。
根据主键查询效率较高，所以尽量根据主键检索。

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

在数据库中，查询一张表有两种检索方式：
	1）全表扫描
	2）根据索引检索（效率高）
	
索引检索效率快的原理是因为，它缩小了扫描的范围。

索引虽然可以提高检索效率，但是也不能随意的添加索引，
因为索引也是数据库中的对象，也需要数据库不断的维护。
比如，表中数据经常被修改，这样的表就不适合添加索引，
因为数据一旦修改，索引需要重新排序，进行维护。

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

select empno,ename,sal from emp where ename='smith';
当ename字段上没有添加索引的时候，这个sql语句会进行全表扫描，扫描ename字段中的所有值。
当ename字段上添加索引的时候，这个sql语句会根据索引扫描，快速定位。

什么时候适合索引

什么时候考虑给字段添加索引？？？
	1）数据量庞大。
	2）该字段很少的DML操作。
	3）该字段经常出现在where中。

查看sql语句的执行计划

// MySQL中才有这个语句
// type ALL		全表扫描
// rows 14		扫描14次
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      |ALL |NULL         |NULL|NULL   |NULL|  14|   10.00|Using where|
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----------+
1 row in set, 1 warning (0.01 sec)

---

创建索引对象

语法：
	create index 索引名称 on 表名 (字段名);

// 创建索引对象
mysql> create index emp_sal_index on emp(sal);

// 查看sql语句的执行计划
// type ref		
// rows 1		扫描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|9      |const|   1|  100.00|NULL |
+--+-----------+-----+----------+----+-------------+-------------+-------+-----+----+--------+-----+
1 row in set, 1 warning (0.00 sec)

---

删除索引对象

语法：
	drop index 索引名称 on 表名;

drop index emp_sal_index on emp;

drop index index_name on talbe_name
alter table table_name drop index index_name
alter table table_name drop primary key

其中，前两条语句是等价的，删除掉table_name中的索引index_name。
第3条语句只在删除primary key索引时使用，因为一个表只可能有一个primary key索引。

---

索引的原理

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

	select ename fromm emp where ename = 'smith' ;
	通过索引转换为
	select ename fromm emp where 物理地址 = 0x3 ;

---

索引的分类

索引的分类:
	单一索引：给单个字段添加索引
	复合索引：给多个字段联合起来添加一个索引
	主键索引：主键上会自动添加索引
	唯一索引：有unique约束的字段上会自动添加索引
	......

---

索引的失效

// 索引会失效，它知道咋分区了就
select ename fromm emp where ename like '%A%';

模糊查询的时候，当第一个通配符是 % ，这个时候索引会失效。