MYSQL学习笔记（二）--认识索引、使用索引、索引失效
目录
1.索引介绍
2.索引作用
3.索引特点
4.索引语法
1.创建索引
2.查看索引
3.删除索引
4.怎么判断sql使用使用索引: sql执行计划 explain sql
5.索引失效（重点）
1.索引介绍
- 索引（index）是帮助MySQL高效获取数据的数据结构(有序)。
- 在数据之外，数据库系统还维护着满足 特定查找算法的数据结构，
- 这些数据结构以某种方式引用（指向）数据，
- 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
sql查询优化 慢sql(用户堆积，sql执行时间过长): 使用索引提高查询效率 面试高频
2.索引作用
表结构以及数据如下:
假如我们要执行的SQL语句为 ： select * from emp where empno=7844;
- 无索引情况
在无索引情况下，就需要从第一行开始扫描，一直扫描到最后一行，我们称之为 全表扫描，性能很低。
- 有索引情况
如果我们针对于这张表建立了索引，假设索引结构就是二叉树，那么也就意味着，会对empno这个字段建 立一个二叉树的索引结构。
此时我们在进行查询时，只需要扫描四次就可以找到数据了，极大的提高的查询的效率。
备注： 这里我们只是假设索引的结构是二叉树，介绍一下索引的大概原理，只是一个示意图，并不是索引的真实结构，索引的真实结构，后面会详细介绍。
3.索引特点
| 优势 | 劣势 |
|---|---|
| 提高数据检索的效率，降低数据库的IO成本 | 索引列也是要占用空间的 |
| 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。 | 索引大大提高了查询效率，同时却也降低更新表的速度， 如对表进行INSERT、UPDATE、DELETE时，效率降低。 |
4.索引语法
1.创建索引
CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (
index_col_name,... ) ;
添加索引: 这张表查询多,增删改少 否则,不建议加
给那些列加索引: where中经常出现列, order by排序的列, select查询的列
给多列添加组合索引
create index index_emp_ename_job_sal on emp(ename,job,sal);
2.查看索引
SHOW INDEX FROM table_name ;
3.删除索引
DROP INDEX index_name ON table_name ;
尽量避免使用关键字取名，如果真要取，名字加``包裹
drop index `primary` on emp;
4.怎么判断sql使用使用索引: sql执行计划 explain sql
例子：
explain select * from emp where ename = 'SMITT';
查询结果：
显示为ALL，表示这样查询为全表查询，效率低
5.索引失效（重点）
如果索引是组合索引, 最左匹配原则
如果在使用联合索引时, 索引字段中的第一个字段，也就是最左边的字段。
1.只要有这个字段在，该sql已经就能走索引。否则就不会走索引
index_emp_ename_job_sal(ename,job,sal)
explain select * from emp where ename ='SMITT'; #使用
explain select * from emp where ename ='SMITT' and job='sxxx'; #使用
explain select * from emp where ename ='SMITT' and sal>1000; #使用
explain select * from emp where ename ='SMITT' and sal>1000 and job ='xxx'; #使用
explain select * from emp where sal>1000 and ename ='SMITT'; #使用
explain select * from emp where job ='xx' and sal>1000; #没有
explain select * from emp where job ='xx'; #没有
2.在like模糊查询中, %,_ 出现在左边, 索引失效
explain select * from emp where ename like 'S%'; #使用
explain select * from emp where ename like '%S%'; #没有
explain select * from emp where ename like '_S%'; #没有
3.列使用函数, 索引失效
explain select * from emp where length(ename) = 5;
4.索引覆盖, 如果select中查询的列满足索引出现的列, 使用索引
explain select ename from emp where job ='xx' and sal>1000; #使用
explain select ename,empno,comm from emp where job ='xx' and sal>1000; #没有
explain select * from emp where ename ='SMITT' and sal = '2000';
5.empno: int 赋值类型一样, 会使用索引
explain select * from emp where empno = 7788; # 使用索引
explain select * from emp where empno = '7788'; # 使用索引
6.empno: varchar 如果字段类型是文本类型, 赋值类型是int, 索引失效
explain select * from emp where empno = 7788; # 没有使用索引
mysql: 隐式类型转换（
- 若字符串是以数字开头，并且全部都是数字，则转换的数字结果是整个字符串；部分是数字，则转换的数字结果是截止到第一个不是数字的字符为止
- 若字符串不是以数字开头，则转换的数字结果是 0
）
explain select * from emp where empno = '7788'; # 使用索引
create index index_emp_comm on emp(comm);
explain select * from emp where comm=2000; # 使用索引
7.如果索引列进行加减乘除运算, 索引也失效
explain select * from emp where comm+1=2000; # 没有索引
8.order by
explain select * from emp order by empno desc limit 1000 ;
