一、什么是索引
1.1:索引的概述
MySQL官方对索引的定义:索引(index)是帮助MySQL高效获取数据的数据结构(有效),在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据, 这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。简而言之:帮助MySQL高效的查询出数据的数据结构叫做索引。一般来说,索引本身也很大,不可能全部都存储在内存中,因此索引一般都是以索引文件的形式存储在磁盘上。索引是数据库用来提高性能最好的方式。
1.2:索引的优劣势
优势:
索引类似于书籍的目录,提高数据检索的效率,减少数据库IO的成本。通过索引列对数据进行排序,降低数据排序的成本,降低CPU的消耗。
劣势:
实际上索引也是一张表,存储在磁盘上,该表保存了主键与索引字段,并指向实体类的记录。虽然索引大大提高了查询的速度,但是降低了增删改的速度,对表进行update、insert、delete时,需要对索引文件进行更新。
二、索引失效的情况
2.1:like 以%开头,索引无效;当like前缀没有%,后缀有%时,索引有效。
2.2:or语句前后没有同时使用索引。
当or左右查询字段只有一个是索引,该索引失效,只有当or左右查询字段均为索引时,才会生效
2.3:组合索引,不是使用第一列索引,索引失效。
2.4:如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引
数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型,使索引无效,产生全表扫描。
2.5:在索引字段上使用not,<>,!=。
不等于操作永远不会用到索引,因此对它的处理只会产生全表扫描。 优化方法: key<>0 改为 key>0 or key<0。
2.6:对索引字段进行计算操作、字段上使用函数。(索引为 emp(ename,empno,sal))
2.7:当全表扫描速度比索引速度快时,mysql会使用全表扫描,此时索引失效。
如果mysql估计使用全表扫描要比使用索引快,则不使用索引
2.8:当join关联时,如果关联表条件所对应表字符集不一致,导致索引失效
2.8.1.修改表字段字符集类型,保证字符集一致
2.8.2.使用convert()函数,保证关联的索引字段 转换后两边字符集一致