【71-71】索引

概述

什么是索引和作用

1. 索引就像是一本书的目录，可以通过目录快速找到对应资源
2. 查询一张表的两种方式：全表扫描，根据索引检索（效率好）
3. 索引提高效率的原理就是缩小扫描范围
4. 索引也是数据库中的对象，也需要数据库不断维护，表中的数据经常被修改就不适合添加索引，因为数据一旦修改，索引需要重新排序维护
5. 添加索引是给某些字段添加索引
6. 如以下代码，如果ename上没有添加索引就全表扫描，添加索引就根据索引扫描

   select ename, sal from emp where ename = 'SMITH';

7. 主键和具有unique约束的字段会自动添加索引，根据主键查询效率高

给字段添加索引的条件

1. 数据量庞大
2. 该字段很少DML操作
3. 该字段进场出现在where子句

索引采用的数据结构

Btree

索引的实现原理

• 会在内存或者硬盘生成一个索引（取决于存储引擎），会将该字段数据自动排序，并分区存储到Btree上
  

索引分类

1. 单一索引：单个字段添加索引
2. 复合索引：给多个字段联合起来添加一个索引
3. 主键索引：主键自动添加索引
4. 唯一索引：unique约束的字段上自动添加索引

索引什么时候失效

select ename from emp where ename like '%A%';

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

演示

查看语句执行状况

• 以下 table 是 ALL 所以是全表扫描

  explain select ename, sal from emp where sal = 5000;
  
  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 |
  +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

创建索引对象

• 给sal字段添加索引

  create index emp_sal_index on emp(sal);
  explain select ename, sal from emp where sal = 5000;
  
  mysql> create index emp_sal_index on emp(sal);
  Query OK, 0 rows affected (0.04 sec)
  Records: 0  Duplicates: 0  Warnings: 0
  
  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 emp_sal_index on emp;