sql优化-面试
常用SQL优化方法
1.where、order by涉及的列增加索引。
2.索引尽量建立在涉及不重复的列上
3.使用符合索引查询时,where子句的列顺序和索引列顺序一样,遵循最左原则。(如果不使用最左原则,则会导致索引失效)
4.表字段不要设置成可为空,因为当使用is null是查询时会导致索引失效而全表扫描
5.避免在where子句中使用!=或者<>操作符号,可以使用union all代替
6.避免在where子句中使用or来连接条件
7.使用like查询时,%符号不要放在最前面
8.in 和 not in也要慎用,否则会导致全表扫描,连续范围可使用between and代替,也可以使用exists和not exists代替。
9.where子句中不要使用函数、表达式、变量进行查询。否则索引会失效。
10.表索引数量不要太多,最好不要超过6个,否则会影响insert、update效率。
11.复合索引尽量简历在不怎频繁更新的字段上。如果建立在频繁更新的字段上,每次更新都会重新给索引进行排序,这样就会降低效率。
12.字段尽量使用数字类型,字符类型会降低查询和连接的性能,并会增加存储的开销
13.字符类型使用varchar,可变长度字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。
14.不要使用select * 这种查询写法,不要反悔用不到的字段。
15.尽量避免向客户端返回大量数据
16.尽量避免大事务操作,提高系统并发能力
17.尽量用inner join代替left join。
18.多表关联时,小表在前,大表在后。
19.当要删除整表数据时,使用truncate代替delete。
20.插入大量数据时,使用insert into values(1,2),(3,4),(5,6)
会使用explain查看SQL执行计划
我们需要重点关注如下几个数据:
type列,连接类型,一个号的SQL语句至少要达到range级别,杜绝出现all级别。
key列,使用到的索引名。如果没有选择索引,值是null,可以采取强制索引方式。
key_len列,扫描行数,该值是一个预估值。
extra列,详细说明。注意,常见的不太友好的值。如下:using filesort,using temporary。
详细介绍一下表中的字段:
1.select_type
SIMPLE:表示此查询布包好union查询或子查询。
PRIMARY:表示此查询时最外层的查询。
SUBQUERY:子查询中的第一个select。
UNION:表示此查询时UNION的第二或随后的查询
DEPENDENT UNION:UNION中的第二个或后面的查询语句,取决于外面的查询
UNION RESULT: UNION的结果
DEPENDENT SUBQUERY:子查询中的第一个select,取决于外面的查询,即子查询依赖于外层查询的结果。
DERIVED:衍生,表示导出表的select(from子句的子查询)
2.table
查询涉及的表或衍生的表
3.type
system:表中只有一条数据,这个类型是特殊的const类型。
const:针对主键或唯一索引的等值查询扫描,最多只返回一行数据。const查询书读非常快,因为他仅仅读取一次即可。
eq_ref:此类型通常出现在多表的join查询,表示对于钱表的每一个结果,都只能匹配到后表的一行结果。
并且查询的比较操作通常是=,查询效率较高。
ref:此类型通常出现在多表的join查询,针对于非唯一或非主键索引,或者是使用了最左前缀规则索引的查询。
range:表示使用索引范围查询,通过索引字段范围获取表中部分数据记录。这个类型通常出现在=,<>,>,>=,<,<=,is null,<=>,between,in()操作中。
index;表示全索引扫描,和all类型类似,只不过all类型是全表扫描,而index类型则仅仅扫描所有的索引,而不扫描数据。index类型通常出现在所要出现的数据直接在索引树中就可以获取到,而不需要扫描数据。当时这种情况时,extra字段会显示using index。
all:表示全表扫描,这个类型的查询时性能最差的查询之一。通常来说,我们的查询不应该出现all类型的查询
通常来说,不同的type类型的性能关系如下:all<index<range<index~merge<ref<eq_ref<const<system。all类型因为是全表扫描,因此在相同的查询条件下,他的速度是最慢的。而index类型的查询虽然不是全表扫描,但是他扫描了所有的索引,因此比all类型的稍快,后面的几种类型都是利用了索引来查询数据,因此可以过滤部分或大部分数据,因此查询效率就比较高了。