目录
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 ;