索引失效
1.要遵守最左前缀法则(最左边的字段必须存在,跳过某一字段后面字段索引失效).
2.索引字段不能使用函数否则失效.
3.索引字段,字符串字段使用时,字符串不加引号,导致索引失效.
4.索引字段进行模糊查询时,如果是头部进行模糊查询,则索引失效,若果是尾部进行模糊查询,索引可正常使用.
5.索引字段使用or时,or的两边都必须有存在索引的字段,否则索引失效.
6.当查询时需要数据大于不需要数据时,索引失效,会全表扫描,当查询时需要数据小于不需要数据时,索引正常使用
sql提示
1.use表示建议使用某个索引(语法:select * from 表名 use index(索引名称) where .......)
2.ignore表示建议不使用某个索引(语法:select * from 表名 ignore index(索引名称) where .......)
3.force表示强制使用某个索引(语法:select * from 表名 force index(索引名称) where .......)
覆盖索引
查询语句中尽量少使用" select * ",全表查询很容易造成回表查询(即:查询时当查询字段不存在已经使用过的索引时,会将查询到的二级索引上挂载的主键重新传回表中到聚集索引中利用主键ID再次进行查询查到整行数据(二级索引中挂载的是当前数据的id,聚集索引挂载的是当前整行数据))
前缀索引
查询语句时如果当前字段储存的数据太长建立索引耗费空间太大,可以利用前缀索引进行对数据的前几个字符进行截取.从而利用少量空间来建立索引,加速查询.
联合索引和单列索引的选择
当存在多个查询字段时最好使用联合索引,单列索引很容易造成回表查询.
索引设计原则
1.针对数据量较大,数据查询较为频繁的表建立索引
2.针对常用于查询条件,分组,排序操作的字段(where,group by,order by)建立索引.
3.尽量针对区分度高的字段创建索引,比如(学生表中的学生id,身份证等等),区分度越高,索引查询效率也就越高
4.如果字符串类型的字段,字符串较长可以建立前缀索引,截取前几个字段来建立索引提高查询效率,这里要注意的是选择性(表中截取前面的字段数据总数(去重)/表中不截取的字段数据总数,这个值越接近于1越好)
5.尽量使用联合索引,少使用单一索引,这样可以减少回表查询,而且还能节省空间,提高效率.
6.要控制索引数量,不是越多越好,索引越多,维护索引代价也就越大,索引过多会影响增删改的效率.
7.如果需要建立索引的字段不能为空值,那么建立字段时就给他设置 NOT NULL ,这样优化器知道那一列不能为空时,会更好的选择索引优化查询效率.
