MySQL高级----索引

1.索引是什么？

索引是一种排好序的快速查找数据结构

2.使用索引的优缺点有哪些？

优点：提高检索的效率，降低数据库的I/O成本，降低数据库的排序成本，降低CPU的消耗。

缺点：在建立索引时会需要额外维护一个独立于数据库的索引表需要占据一定的物理空间。进而导致数据库更新数据速度降低。数据库不仅要维护数据库还需要额外维护索引。

索引的用途更像是生物里的界门纲目科属种，通过B数实现层层筛选最后精确定位物种的类别。这样不用查阅整个动物图谱去确定物种。

3.索引的分类

单值索引：一个索引只包含一个列，一个表可能有多个单值索引。
唯一索引：索引对应的值是唯一的，允许有空值。
复合索引：一个索引包含多个列。

4.操作索引的方法

添加

Create [UNIQUE] INDEX indexName On table(clumname)

Alter TABLE tableName ADD[UNIQUE] INDEX[indexName](clumname) 

删除

DROP INDEX [indexname] on tableName

查看

SHOW INDEX FROM tableName

5.索引优化

单表：范围之后的索引会失效

举例（这里需要了解MySQL explain 的用法）

#建立索引
ALTER TABLE employees ADD INDEX index_salary_lastName(SALARY,LAST_NAME)

SELECT * FROM employees WHERE SALARY =24000 and LAST_NAME = "King"

EXPLAIN SELECT * FROM employees WHERE SALARY =24000 and LAST_NAME = "King"

结果

这里可以看到
ref 中有两个常量 const 说明索引中的两个索引都生效了。

EXPLAIN SELECT * FROM employees WHERE SALARY >17000 and LAST_NAME = "King"

这里看到key_len 值降到了9 ref 中无常量被使用，说明只用到了索引的排序功能。

两表：相反建立索引

当有两个表时，将索引建立在非主表上。也可以交换左右表的位置。避免索引失效

三表：将索引建立在经常查询的字段上

1.尽可能减少join中NestdLoop的循环次数，用小结果集去驱动大的结果集。

即：理解为嵌套循环，让外层循环次数尽可能小于内层循环次数。

2.优先优化nestdLoop的内层循环

内层循环查询逻辑通常需要查询更多的数据，优化查询语句能够更显著的降低sql执行时间

3.保证join语句中的被驱动表上的字段已经被索引

如：select s.id,s.studentName,c.course from student s left join cource c on s.studentNum = c.studentNum
当studentNum字段在 cource表上被索引时，可以提高检索的效率

4.无法保证被驱动表join字段的索引时，不要吝啬String Buffer的设置

？

6.索引失效

以下是索引会失效的一些场景，尽可能避免出现以下情况，保证索引能够被使用。

1）用建立索引的顺序按最佳做前缀法则来检索表

索引的建立是有先后顺序的

#建立索引
ALTER TABLE employees ADD INDEX index_salary_lastName(SALARY,LAST_NAME)

这里的顺序是SALARY,LAST_NAME

2) 不在索引列上做任何操作，这样会导致索引失效

如：

select * from employees where Salary+30 > 5000

3)当where 后给定条件不再是常量时，无法再使用后面的索引

select * from book where id =1 and bookNumber >2 and bookName ="bl"

范围是一个不确定的值 会失去检索功能，只剩下排序。
`