1、什么是索引?
索引是在数据库表的字段上添加的,是为了缩小扫描范围,提高查询效率的机制。
一张表的一个字段可以添加一个索引,多个字段联合起来也可以添加索引
主键和UNIQUE字段会自动添加索引。
如果没有索引,mysql将进行全表扫描。
索引也需要排序,在mysql中索引是一个B-Tree数据结构,遵循左小右大原则存放,中序遍历取数据。
2、索引实现原理
在mysql中,索引是一个单独的对象,以自平衡二叉树的形式存在。
不同的存储引擎以不同的形式存在。
在MyISAM存储引擎中,索引存储在一个.MYI文件中;
在InnoDB存储引擎中,索引存储在一个逻辑名称为tablespace中;
在MEMORY存储引擎中,索引被存储在内存中。
3、什么条件下需要添加索引?
(1)数据庞大
(2)该字段经常出现在where后面,以条件的形式存在
(3)该字段很少的DML操作,因为DML后,索引需要重新排序
4、索引的创建与删除
(1)创建索引
create 索引名 on 表名(字段名);
create index emp_name_index on emp(ename);
(2)删除索引
drop index 索引名 on 表名;
drop index emp_name_index on emp;
(3)如何查看一个SQL语句是否使用了索引进行检索
explain select * from emp where ename = 'KING';
5、索引的失效
(1)模糊查询以%开始会索引失效,尽量避免模糊查询以%开始
(2)使用OR的时候会失效。
如果使用OR那么要求OR两边的条件字段都要有索引,如果其中一边有一个字段没有索引,那么另一个字段上的索引也会失效。因此不建议使用OR
union不会让索引失效
(3)【索引的最左原则】使用复合索引的时候,没有使用左侧的列查找,索引失效。
两个字段,或者更多的字段联合起来添 加一个索引,称为复合索引。
如: create index emp_job_sal_index on emp(job,sal);
(4)在where当中索引列参加了运算,索引失效
(5)在where当中索引列使用了函数,索引失效
如:explain select * from emp where lower(ename) = 'smith';
6、索引的分类
索引是数据库中进行优化的重要手段,优化优先考虑的因素就是索引
索引的类别:
单一索引, 复合索引;
主键索引, 唯一性索引(UNIQUE字段上添加)
唯一性弱的字段上添加索引用处不大。