1 使用索引列进行查询的时候尽量不要使用表达式
explain select * from menu where mid+1=5; -- 主键索引失效
explain select * from menu where mid=4; -- 使用索引查询
2 尽量使用主键查询,因为主键查询不会触发回表查询
使用普通索引查询时,如果要返回的结果中包含非索引列,会再触发一次主键查询(回表)
3 较长字段列创建索引,可以使用前缀索引
当频繁进行查询操作的列字段较长时,可截取该字段的重复率较低的前n个长度建立索引,以避免在整个字段上建立索引消耗资源较多的情况
- 1.通过计算完整列的选择性,来选择合适的字段长度建立索引
-- 计算city字段的前n个长度的不重复值dv(distinct value),选择dv基本不变的n值建立索引
select
count(distinct left(city,3))/count(*) as c3,
count(distinct left(city,4))/count(*) as c4,
count(distinct left(city,5))/count(*) as c5,
count(distinct left(city,6))/count(*) as c6,
count(distinct left(city,7))/count(*) as c7,
count(distinct left(city,8))/count(*) as c8
from emp;
- 2.创建索引
alter table emp add index idx_city_dv(city(7));
4 使用索引排序
- 1.索引的建立最好能兼顾查询和排序,这样可最大程度的利用索引查询的效率
- 2.如果排序列可以和条件查询列组成最左前缀索引,可以提高查询的效率(查询条件要先匹配左才能生效)
- 3.查询和排序组合成最左前缀时,排序中字段的升降也要保持一样(同asc同desc),两者兼顾也会进行全表扫描,导致索引失效
- 4.查询中没有使用到索引,即使排序字段使用了索引也不会生效(会进行全表的扫描,explain中的Extra列会显示Using filesort)
5 union all,in,or都能使用索引,但是推荐使用in
- 查询相同的情况下,union all 实际是进行了两次查询,in和or都是一次查询,但是查询数据较多时in的效率要比or的效率高
- union:对两个结果集进行并集操作,不包括重复行,同时进行默认规则的排序
- union all:对两个结果集进行并集操作,包括重复行,不进行排序(效率比union高)
-- 如果查询的id列都存在,union查询返回三条数据,union all 查询返回四条数据(包含两条eid=1的数据)
select * from emp where eid=1 or eid=2 union all select * from emp where eid=1 or eid=3;
select * from emp where eid=1 or eid=2 union select * from emp where eid=1 or eid=3;
6 范围查询中可能会不使用索引
范围查询: > , <
范围查询中使用一般索引可能不会生效:在sql语句执行时,会先通过分析器优化器进行sql语句的分析优化,当发现使用索引的效率要远大于不使用索引时,索引才会生效;否则索引不会生效,会进行全表的查询。
- 主键索引进行范围查询,索引一直生效
- 范围查询列可以使用索引,但范围列后面的列将无法用到索引(失效)
7 强制类型转换可能会导致索引失效进行全表扫描
-- tel列为varchar类型,且建立有索引
explain select * from emp where tel=15738967250; -- type=all,强制类型转换,索引失效,进行全表扫描
explain select * from emp where tel='15738967250'; -- type=ref,使用索引查询
8 更新十分频繁,或者数据区分度不高的字段上不要建立索引
- 索引的更新会涉及到B+树结构的变化,更新频繁的字段建立索引会大大降低数据库性能
- 类似于性别这类区分不大的属性字段,建立索引没有意义,并不能进行有效的查询过滤
- 一般区分度在80%以上,查询操作较为频繁的字段,可以建立索引
- 区分度可以使用 count(distinct(列名))/count(*) 来计算
9 进行表连接时,最好不要超过三张表;需要join的字段,数据类型必须一致
- Simple Nested-Loop Join Algorithm :简单嵌套连接算法
连接表的个数相当于嵌套循环的层数,每次从一个循环中的第一个表中读取一行,然后将每一行传递给一个嵌套循环,该循环处理联接中的下一个表;重复此过程的次数与要连接的表的次数相同,因为NLJ算法从外循环到内循环一次传递一行,所以它通常会多次读取在内循环中处理的表。 - Index Nested-Loop Join Algorithm:索引嵌套循环连接算法
要求非驱动表的连接列有索引,可以通过索引来较少比较,加速查询;在查询时,驱动表会根据关联字段的索引进行查找,根据索引找到符合的值时,在进行回表查找(只有当匹配到索引才进行回表查询);如果非驱动表的关联字段是主键,性能会更高;如果不是主键,则要再进行回表查询,性能会比主键索引较低一些。 - Block Nested-Loop Join Algorithm:块嵌套循环连接算法
如果有索引会采用Index Nested-Loop Join Algorithm,如果没有索引会采用Block Nested-Loop Join Algorithm,该连接方式在每两个表之间会有一个join-buffer缓冲区,会将驱动表的所有关联列以及查询需要的结果列先缓存到join buffer中,然后再用join buffer中的行数据批量与非驱动表进行匹配,将第一种的每次循环匹配合并为一次匹配,降低了非驱动表的访问;join_buffer_size系统默认值是256K,可以自定义参数大小;N个表进行关联查询时,会产生N-1个buffer。
10 其他
- 创建索引的列,不允许为null,为null时可能会得到不符合预期的结果(非强制)
- 能使用limit的时候尽量使用limit,会降低数据库的压力
- 索引并不是越多越好,单个表中索引建议控制在5个以内
- 组合索引字段数不允许超过5个