目录
索引创建原则
1). 针对于数据量较大,且查询比较频繁的表建立索引。
2). 针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引。
3). 尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高。
4). 如果是字符串类型的字段,字段的长度较长,可以针对于字段的特点,建立前缀索引。
5). 尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表,提高查询效率。
6). 要控制索引的数量,索引并不是多多益善,索引越多,维护索引结构的代价也就越大,会影响增删改的效率。
7). 如果索引列不能存储NULL值,请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时,它可以更好地确定哪个索引最有效地用于查询。
面试回答
面试官:索引创建原则有哪些?
候选人:嗯,这个情况有很多,不过都有一个大前提,就是表中的数据要超 过10万以上,我们才会创建索引,并且添加索引的字段是查询比较频繁的字 段,一般也是像作为查询条件,排序字段或分组的字段这些。
还有就是,我们通常创建索引的时候都是使用复合索引来创建,一条sql的返 回值,尽量使用覆盖索引,如果字段的区分度不高的话,我们也会把它放在 组合索引后面的字段。
如果某一个字段的内容较长,我们会考虑使用前缀索引来使用,当然并不是 所有的字段都要添加索引,这个索引的数量也要控制,因为添加索引也会导 致新增改的速度变慢。
索引什么时候会失效
为了便于演示 我们提前给tb_seller创建联合索引,字段顺序:name,status,address
1). 违反最左前缀法则
如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始,并且不跳过索引中的列。
匹配最左前缀法则,走索引:
违法最左前缀法则 , 索引失效:
如果符合最左法则,但是出现跳跃某一列,只有最左列索引生效(name命中,address没有命中):
2). 范围查询右边的列,不能使用索引 。
根据前面的两个字段 name , status 查询是走索引的, 但是最后一个条件address 没有用到索引。
3). 不要在索引列上进行运算操作, 索引将失效。
4). 字符串不加单引号,造成索引失效。
由于,在查询是,没有对字符串加单引号, MySQL的查询优化器,会自动的进行类型转换,造成索引失效。
5).以%开头的Like模糊查询,索引失效。
如果仅仅是尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引失效。
面试回答
面试官:什么情况下索引会失效 ?
候选人:嗯,这个情况比较多,我说一些自己的经验,以前遇到过的 比如,索引在使用的时候没有遵循最左匹配法则,第二个是,模糊查询,如 果%号在前面也会导致索引失效。如果在添加索引的字段上进行了运算操作 或者类型转换也都会导致索引失效。
我们之前还遇到过一个就是,如果使用了复合索引,中间使用了范围查询, 右边的条件索引也会失效 所以,通常情况下,想要判断出这条sql是否有索引失效的情况,可以使用 explain执行计划来分析