案例:当我们在进行列表查询的时候,会有很多条件,但并不是所有条件我们都会在一起查询都使用到,也不知道有哪个条件一定会使用,这让mysql的以列的左前綴的索引方式显得有些力不从心,加入有六个条件,我们总不能写6*5*4*3*2*1个索引吧,这样太浪费资源,而且十分费事,如何解决呢。
例子:加入我们有一个学生成绩表,有grade (1~6年级),姓名(name),学科(subject 数、语文、英语),成绩区间查询(score 150分满分),家庭住址(address),性别(sex)
当我们查这些时,我们使用这些条件时,会出现这些查询:
三年级的数学及格的成绩信息
所有低于30分的成绩信息
想查“张三”这位学生的所有学科信息。
查年龄在“6-12”岁所有人的数学成绩信息。
根据MySQL最左前缀我们如何选择第一列的选项,根据情况而定,可能根据需求每个频率都会用到,我们如何建立索引,建立过多的索引会给系统增加很大的负担,而且并不实用。
解决问题:
以学科举例,如果某个查询不限制性别,那么可以通过在查询条件中新增 and sex in(0,1)来让Mysql使用索引。
诀窍将查询条件分为几部分,在查询时用select框体的(选择列明显就是数量很少),区间查询,模糊查询。
通常情况下应该以select选择最小的做最左边。从左到右依次是sex(2),subject(3),grade(6)
然后在一区间进行查询score使用between()进行操作。
然后是进行模糊查询,有name和adress,很多情况下,查name我们就查姓必须有的模糊查询(即符合最左前缀),那么就应该是先name后adress,因为adress是使用like "%%"方式,是无法使用索引的,放在最后面,或者在索引中不加入此列。
所以使用索引应该为sex,subject,grade,score,name。
为什么要把sex放在前面呢,加入数据有1000条,那么男,女可能应该在500左右。而subject有三课,那么每条数据应该有330条左右。之前我们提到说在索引的排序上,在不考虑排序,group by问题时,最好用选择性高的列放在前方,那为什么这时要用性别做第一列呢。因为我们说索引的时候经常是以“=”号来考虑问题的,现在如果不选sex选项,我们只能使用in,使用in的话就会增加组合。假如sex,subject,grade我们都不选的话,那么就会有2*3*6=36种组合。所以经常会有人听到说写sql的时候尽量少使用in多使用between来减少组合。但是凡事都有量级,如果组合数达到上千个需要特别小心,在有些场景中如果无法避免,并且组合数较少的情况下,那么尽情的使用in吧。
那为什么要把between and放在select的后面呢,因为between and 是范围查询,如果放在前面,后面的索引列就使用不了了。
所以在explian上的时候,in()和>,,