7、索引
- 概念：Index ，索引是在数据库中的字段上添加，是为了提高查询效率而存在的一种机制。一张表的一个字段可以添加一个索引，也可以多个字段联合起来也可以添加索引。相当于目录，是为了缩小扫描范围的一种机制。通过索引检索，效率较高。
- 【注意】：索引需要排序 ， 目的是生成区间查询。缩小扫描范围就是扫描区间 和TreeSet数据结构相同（底层是一个自平衡的二叉树）；在mysql之中索引是一个B-Tree结构，遵循左小右大原则存放。
- 实现原理：
- 【注意】：
- 1、在所有数据库之中，主键上会自动添加索引；在mysql之中，如果有unique约束的字段也会自动添加索引。
- 2、在任何数据库之中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号
- 3、在mysql之中，索引是一个单独的对象，不同的引擎以不同的形式存在。
- MyISAM -------- .MYI 文件之中
- InnoDB --------- tablespace
- MEMORY -------- 内存之中
- 无论存储在何处，索引在mysql之中都以自平衡二叉树 B-Tree 的形式存在
- 添加索引的条件：
- 1、数据量庞大
- 2、该字段经常出现在where后面，以条件的形式存在。（字段总会被扫描）
- 3、该字段有很少的DML操作，因为DML语句之后，会重新排序
- 建议不要随意添加索引，建议通过unique约束的字段或者通过主键查询
- 索引的创建与删除
- 创建：create index 索引名 on 表名(字段);
- 删除：drop index 索引名 on 表名 ；
【案例】：展示索引的创建和删除 // 添加 create index emp_ename_index on emp(ename); mysql> desc emp ; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | EMPNO | int(4) | NO | PRI | NULL | | | ENAME | varchar(10) | YES | MUL | NULL | | 其中的MUL表示索引 | JOB | varchar(9) | YES | | NULL | | | MGR | int(4) | YES | | NULL | | | HIREDATE | date | YES | | NULL | | | SAL | double(7,2) | YES | | NULL | | | COMM | double(7,2) | YES | | NULL | | | DEPTNO | int(2) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 8 rows in set (0.00 sec) // 删除 drop index emp_ename_index on emp ;
- 索引失效：
- 1、模糊查询的时候并且以 '%' 开头的时候，索引失效。所以尽量避免这种情况出现(优化策略)
- 2、使用or的时候，or两面字段至少有一个没有索引 ，索引失效。所以尽量少用or（优化策略)
- 3、使用复合索引的时候，没有使用左侧的列进行查找，索引失效。
- 4、在where当中索引添加了数学运算，索引失效。
- 5、在where当中索引列使用了函数(单行函数，分组函数不能出现在where子句中!)，索引失效
- 索引的分类：
- 单一索引
- 复合索引 ： create index emp_job_sal_index from emp(job , sal) ;
- 主键索引:
- 唯一性索引
- .....
