MySql的复习(九)：索引

MySql的复习(九)：索引

什么是索引？

索引被用来快速找出在一个列上用一特定值的行。没有索引，MySQL不得不首先以第一条记录开始，然后读完整个表直到它找出相关的行。表越大，花费时间越多。

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

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

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

创建索引

如果未使用索引，查询 工资大于 1500的会执行全表扫描

索引等同于一本书的目录

主键会自动添加索引，所以尽量根据主键查询效率较高。

若经常根据sal进行查询，并且遇到了性能瓶颈，首先查看程序是否存算法问题，再考虑对sal建立索引，建立索引如下：

1. 创建索引对象

   create unique index 索引名 on 表名(列名);

create unique index u_ename on emp(ename);

2. 修改索引名称

   alter table 表名 add unique index 索引名 (列名);

查看索引

show index from emp;

使用索引

注意：一定不可以用select * … 可以看到type!=all了，说明使用了索引

explain select sal from emp where sal > 1500;

删除索引

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索引， 
mysql> ALTER TABLE EMP DROP INDEX test_index;

-- 删除后就不再使用索引了，查询会执行全表扫描。

索引的其他了解

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

• 索引的实现原理？
  通过B Tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的"物理地址"，最终通过索引检索到数据之后，获取到关联的物理地址，
  通过物理索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的。
  select ename from emp where ename = ‘SMITH’;
  通过索引转换为：
  select ename from emp where 物理地址 = 0x123;

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

• 索引什么时候失效？
  select ename from emp where ename like ’ %A% ';
  加索引
  …

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