SQL优化
优化口诀:
全职匹配我最爱,最左前缀要遵守。
带头大哥不能死,中间兄弟不能断。
索引列上少计算,范围之后全失效。
LIKE百分写最右,覆盖索引不写*。
不等空值还有OR,索引影响要注意。
var引号不可丢,SQL优化有诀窍。
索引
索引概念
索引:索引(Index)是帮助MySQL高效获取数据的数据结构。可以得到索引的本质:索引就是数据接口。
可以简单理解为:排好序的快速查找的数据结构。
一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上,我们平常所说的索引,如果没
有特别的指明,都是指B树,(多路搜索树,并不一定是二叉的)结构组织的索引
索引分类
-单值索引: 即一个索引只包含单个列,一个表可以有多个单列索引。
-唯一索引: 唯一索引的只必须唯一,但允许有空值。
-复合索引: 即一个索引可以包含多个列。
主键一般只需要一个,但是可以建立在多个列上,称为:联合主键。
索引同样也可以建立在多个列上,称为:联合索引。
创建:
create [unique] index indexName on tableName(col1,col2,...);
alter table 表名 add [unique] index [indexName] on (columnName(length));
删除:
drop index indexname on tableName;
查看:
show index from tableName
Explain关键字
使用explain关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。
模拟执行SQL语句的一个工具,通过模拟执行的参数,可以知道mysql是如何处理我们写的SQL。可以通过explain参数知道我们sql
语句的性能瓶颈,进一步优化SQL。
性能分析> Explain
解释:
使用Explain关键字可以模拟优化器执行sql查询语句,从而知道mysql是如何处理你的sql语句。
分析你的查询语句或者是表结构的性能瓶颈。
建立索引常用的规则如下:(索引最好不要超过6,7个)
能干嘛:
- 1.表的读取顺序
- 2.数据读取操作的操作类型
- 3.哪些索引可以使用
- 4.哪些索引被实际引用
- 5.表之间的引用
- 6.每张表有多少行被优化器查询
语法:
explain sql
id:
select 查询的序列,包含一组数字,表示查询中执行select字句或操作表的顺序
三种情况:
1.id相同,执行顺序由上到下
2.id不同,如果是子查询,id序号会递增,id值越大优先级越高,越先被执行。
3.id不同,同时存在
select_type:
查询的类型,主要是用于区别 普通查询、联合查询、子查询等的复杂查询
1.simple
简单的select查询,查询中不包含子查询或者union
2.primary
查询中若包含任何复杂的子部分,最外层查询则被标记为primary。
3.subquery
在select或where列表中包含子查询
4.derived/materialized
在from列表中包含的子查询被标记为derived(衍生) mysql会递归执行这些子查询,把结果放在临时表中
5.union
若第二个select出现在union之后,则会标记为union;若union包含在from子句的子查询中,外层select将标记为:derived
6.union result
从union表获取结果的select、。
table:
显示这一行的数据是关于哪一张表的
type:
显示查询使用了哪种索引类型:(好>差)
system > const > eq_ref > ref > range > index > all
system:
表只用一行记录(等于系统表),这是const类型的特列,平时不会出现,这个也忽略不计
const:
表示通过索引一次就找到了,const用于比较primary key或unique索引。因为只匹配一行数据,索引很快,
如果将主键置于where列表中,mysql就能将该查询转换为一个常量。
eq_ref:
唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描
ref:
非唯一性索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,它返回所有匹配某个单独值的行
然而,它可能会找到多个符合条件的行,所以它应该属于查找和扫描的混合体。
range:
只检索给定的范围的行,使用一个索引来选择行。key列显示使用了哪个索引,一般就是在你的where字句中出
现了between,<,> ,in 等查询,这种范围扫描索引比全表扫描要好,因为它只需要开始于索引的某一点,
而结束与另一点,不用扫描全部索引。
index:
full index scan, index 与 all区别为index类型只遍历索引树,这通常比all快,因为索引文件通常比
数据文件小。(也就是说虽然all和index都是读全表,但是index是从索引中读取的,而all是从硬盘中读取的)
all:
full table scan,将遍历全表以找到匹配的行。
备注:一般来说:得保证查询至少达到range级别,最好能达到ref。
System类型:直接拿出就可以,不需要匹配。
const 类型:使用索引查询具体的值。
eq_ref 类型: 如下所示:在b表中user_id列上,每一个值在a表中都只有一个。
ref类型:如下所示:在a表中id列上,每一个值在b表中不仅仅只有一个值,所以是re而不是eq_ref.
range类型:
index类型:
all类型:
possible_keys:
显示可能应用在这张表中的索引,一个或多个。
查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用。
key:
实际使用的索引。如果为null,则没有使用索引。
查询中若使用了覆盖索引,则该索引和查询的select字段重叠。
key_len:
表示索引中使用的字节数,key 通过该列计算查询中使用的索引的长度,在不损失精确性的情况下,长度越短越好。
key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算得到,而不是通过表内检索得出。
key_len索引字段的类型最大长度。int = 4,使用的是UTF-8编码。varchar(20) ==> 20 * 3 + null(1字节)+记录长度(2字节) = 63 ;
ascii:1字节
gbk:2字节
utf:3字节
表结构:
由上可以计算出下列 key_len: 152 = 50 * 3 + 2 = 152
- utf-8 一个字符占三个字节数
- 2:2个用于记录varchar的实际存储长度
- 由于使用的 not null 所以无需使用单独一个字节用于保存null。
表结构修改:
对应的结果:
ref:
显示索引的哪一列被使用了,如果可能的话,是一个常数,哪些列或常量被用于查找索引列上的值
rows:
根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数
extra:
包含不适合在其他列中显示但十分重要的额外信息
1.using filesort
说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。mysql中无法利用索引
完成的排序操作称为“文件排序”。
--> 如果出现了using filesort,则表示在order by中没有使用到联合索引。
2.using temporary
使用了临时表保存中间结果,mysql在对查询结果排序时使用临时表,常见于排序order by 和
分组查询group by
3.using index
表示相应的select操作中使用了覆盖索引,避免访问了表的数据行,效率不错。
如果同时出现了using where,表明索引被用来执行索引键值的查找。如果没有同时出现using where,
表明索引用来读取数据而非执行查找动作。
4.using where
表明使用了where过滤
5.using join buffer
使用了连接缓存
6.impossible where
where字句的值总是false,不能用来获取让任何元组
7.select tables optimized away
在没有group by字句的情况下,基于索引优化min/max操作或者对于myisam存储引擎优化count(*)操作。
不必等到执行阶段在进行计算,查询执行计划生成的阶段即完成优化
8.distinct
优化distinct操作,在找到第一匹配的元组后即停止找同样值的动作。
示例:
下面设计user表的时候,设计了一个联合索引,(name,age,position)。
当只使用到了联合索引中的一个时,索引都生效。
1.使用到联合索引中的第一列,可以使用到索引
2.使用到联合索引中的第二列,可以使用到索引
3.使用到联合索引中的第三列,可以使用到索引
4.使用联合索引中的第一二列,可以使用到索引
5.使用联合索引中的第二三列,出现了using filesort,需要优化
6.使用联合索引中的第一三列,出现了using filesort,需要优化
如何注意索引优化:
1.sql语句的优化。
2.创建数据结构时,要尽量考虑到索引的使用。复合索引在建立和使用时,尽量考虑在用户应用查询时,常用的排序方向和字段组合顺序一致;
索引失效
1.全值匹配我最爱
2.最佳左前缀法则(如果索引了多列,要遵守最左前缀法则,指的是查询从索引的最左列并且不跳过索引中的列)
3.不在索引列上做任何操作(计算,函数,(自动或手动)类型转换),会导致索引失效而转向全表扫描
4.存储引擎不能使用索引中范围条件右边的列
从下面第一条和第三条sql的key_len可以看出,使用到的索引个数是一样的,即第三个sql中,age之后的,即position索引没有生效。
即在范围后面的列的索引是不会生效的。
5.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select *
6.mysql在使用不等于(!=或者<>)的时候无法使用索引而导致全表扫描、
7.is null. is not null 也无法使用索引
8.like 以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作
已知,name属性建立了索引
1.没有使用通配符,使用到了索引
2.使用了前后通配符,没有使用到索引,索引失效,扫描全表。
3.使用了前通配符,没有使用到索引,索引失效,导致扫描全表。
4.使用了后通配符,使用到了索引,索引类型为range。
9.字符串不加单引号索引失效
1.正确写法,使用了索引,索引类型为ref
2.字符串不加单引号时,会导致索引失效,扫描全表。
10.少用or,用它来连接时会索引失效
可以查询,索引失效了,扫描全表
建立索引常用的规则如下:
1、表的主键、外键必须有索引;
2、数据量超过300的表应该有索引;
3、经常与其他表进行连接的表,在连接字段上应该建立索引;
4、经常出现在Where子句中的字段,非凡是大表的字段,应该建立索引;
5、索引应该建在选择性高的字段上(枚举型字段不建索引);
6、索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引;
7、在Join表的时候使用相同类型的列,并将其索引。
- 如果有很多Join的操作,Join的字段应该加索引,同时保证这些字段的类型一致。外键字段加上索引
8、复合索引的建立需要进行仔细分析;尽量考虑用单字段索引代替:
-
A、正确选择复合索引中的主列字段,一般是选择性较好的字段;
-
B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中?单字段查询是否极少甚至没有?假如是,则可以建立复合索引;否则考虑单字段索引;
-
C、假如复合索引中包含的字段经常单独出现在Where子句中,则分解为多个单字段索引;
-
D、假如复合索引所包含的字段超过3个,那么仔细考虑其必要性,考虑减少复合的字段;
-
E、假如既有单字段索引,又有这几个字段上的复合索引,一般可以删除复合索引;
9、频繁进行数据操作的表,不要建立太多的索引;
10、删除无用的索引,避免对执行计划造成负面影响;
11、避免使用select *
- 从数据库里读出越多的数据,那么查询就会变得很慢。所以,应该养成需要什么字段就取什么字段的好习惯。
以上是一些普遍的建立索引时的判定依据。一言以蔽之,索引的建立必须慎重,对每个索引的必要性都应该经过仔细分析,要有建立的依据。因为太多的索引与不充分、不正确的索引对性能都毫无益处:在表上建立的每个索引都会增加存储开销,索引对于插入、删除、更新操作也会增加处理上的开销。另外,过多的复合索引,在有单字段索引的情况下,一般都是没有存在价值的;相反,还会降低数据增加删除时的性能,凡是对频繁更新的表来说,负面影响更大
4、避免多个范围条件
实际开发中,我们会经常使用多个范围条件,比如想查询某个时间段内登录过的用户:
select user.* from user where login_time > '2017-04-01' and age between 18 and 30;
这个查询有一个问题:它有两个范围条件,login_time列和age列,MySQL可以使用login_time列的索引或者age列的索引,但无法同时使用它们。
5、覆盖索引
如果一个索引包含或者说覆盖所有需要查询的字段的值,那么就没有必要再回表查询,这就称为覆盖索引。覆盖索引是非常有用的工具,可以极大的提高性能,因为查询只需要扫描索引会带来许多好处:
- 索引条目远小于数据行大小,如果只读取索引,极大减少数据访问量
- 索引是有按照列值顺序存储的,对于I/O密集型的范围查询要比随机从磁盘读取每一行数据的IO要少的多
6、使用索引扫描来排序
MySQL有两种方式可以生产有序的结果集,其一是对结果集进行排序的操作,其二是按照索引顺序扫描得出的结果自然是有序的。如果explain的结果中type列的值为index表示使用了索引扫描来做排序。
扫描索引本身很快,因为只需要从一条索引记录移动到相邻的下一条记录。但如果索引本身不能覆盖所有需要查询的列,那么就不得不每扫描一条索引记录就回表查询一次对应的行。这个读取操作基本上是随机I/O,因此按照索引顺序读取数据的速度通常要比顺序地全表扫描要慢。
在设计索引时,如果一个索引既能够满足排序,又满足查询,是最好的。
只有当索引的列顺序和ORDER BY子句的顺序完全一致,并且所有列的排序方向也一样时,才能够使用索引来对结果做排序。如果查询需要关联多张表,则只有ORDER BY子句引用的字段全部为第一张表时,才能使用索引做排序。ORDER BY子句和查询的限制是一样的,都要满足最左前缀的要求(有一种情况例外,就是最左的列被指定为常数,下面是一个简单的示例),其它情况下都需要执行排序操作,而无法利用索引排序。
// 最左列为常数,索引:(date,staff_id,customer_id)
select staff_id,customer_id from demo where date = '2015-06-01' order by staff_id,customer_id
7、冗余和重复索引
冗余索引是指在相同的列上按照相同的顺序创建的相同类型的索引,应当尽量避免这种索引,发现后立即删除。比如有一个索引(A,B),再创建索引(A)就是冗余索引。冗余索引经常发生在为表添加新索引时,比如有人新建了索引(A,B),但这个索引不是扩展已有的索引(A)。
大多数情况下都应该尽量扩展已有的索引而不是创建新索引。但有极少情况下出现性能方面的考虑需要冗余索引,比如扩展已有索引而导致其变得过大,从而影响到其他使用该索引的查询。
8、删除长期未使用的索引
定期删除一些长时间未使用过的索引是一个非常好的习惯。
关于索引这个话题打算就此打住,最后要说一句,索引并不总是最好的工具,只有当索引帮助提高查询速度带来的好处大于其带来的额外工作时,索引才是有效的。对于非常小的表,简单的全表扫描更高效。对于中到大型的表,索引就非常有效。对于超大型的表,建立和维护索引的代价随之增长,这时候其他技术也许更有效,比如分区表。最后的最后,explain后再提测是一种美德。
特定类型查询优化
优化COUNT()查询
COUNT()可能是被大家误解最多的函数了,它有两种不同的作用,其一是统计某个列值的数量,其二是统计行数。统计列值时,要求列值是非空的,它不会统计NULL。如果确认括号中的表达式不可能为空时,实际上就是在统计行数。最简单的就是当使用COUNT(*)时,并不是我们所想象的那样扩展成所有的列,实际上,它会忽略所有的列而直接统计所有的行数。
我们最常见的误解也就在这儿,在括号内指定了一列却希望统计结果是行数,而且还常常误以为前者的性能会更好。但实际并非这样,如果要统计行数,直接使用COUNT(*),意义清晰,且性能更好。
有时候某些业务场景并不需要完全精确的COUNT值,可以用近似值来代替,EXPLAIN出来的行数就是一个不错的近似值,而且执行EXPLAIN并不需要真正地去执行查询,所以成本非常低。通常来说,执行COUNT()都需要扫描大量的行才能获取到精确的数据,因此很难优化,MySQL层面还能做得也就只有覆盖索引了。如果不还能解决问题,只有从架构层面解决了,比如添加汇总表,或者使用redis这样的外部缓存系统。
优化关联查询
在大数据场景下,表与表之间通过一个冗余字段来关联,要比直接使用JOIN有更好的性能。如果确实需要使用关联查询的情况下,需要特别注意的是:
- 确保ON和USING字句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表A和表B用列c关联的时候,如果优化器关联的顺序是A、B,那么就不需要在A表的对应列上创建索引。没有用到的索引会带来额外的负担,一般来说,除非有其他理由,只需要在关联顺序中的第二张表的相应列上创建索引(具体原因下文分析)。
- 确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列,这样MySQL才有可能使用索引来优化。
要理解优化关联查询的第一个技巧,就需要理解MySQL是如何执行关联查询的。当前MySQL关联执行的策略非常简单,它对任何的关联都执行
嵌套循环关联操作,即先在一个表中循环取出单条数据,然后在嵌套循环到下一个表中寻找匹配的行,依次下去,直到找到所有表中匹配的行为为止。然后根据各个表匹配的行,返回查询中需要的各个列。
太抽象了?以上面的示例来说明,比如有这样的一个查询:
SELECT A.xx,B.yy
FROM A INNER JOIN B USING(c)
WHERE A.xx IN (5,6)
假设MySQL按照查询中的关联顺序A、B来进行关联操作,那么可以用下面的伪代码表示MySQL如何完成这个查询:
outer_iterator = SELECT A.xx,A.c FROM A WHERE A.xx IN (5,6);
outer_row = outer_iterator.next;
while(outer_row) {
inner_iterator = SELECT B.yy FROM B WHERE B.c = outer_row.c;
inner_row = inner_iterator.next;
while(inner_row) {
output[inner_row.yy,outer_row.xx];
inner_row = inner_iterator.next;
}
outer_row = outer_iterator.next;
}
可以看到,最外层的查询是根据A.xx列来查询的,A.c上如果有索引的话,整个关联查询也不会使用。再看内层的查询,很明显B.c上如果有索引的话,能够加速查询,因此只需要在关联顺序中的第二张表的相应列上创建索引即可。
优化LIMIT分页
当需要分页操作时,通常会使用LIMIT加上偏移量的办法实现,同时加上合适的ORDER BY字句。如果有对应的索引,通常效率会不错,否则,MySQL需要做大量的文件排序操作。
一个常见的问题是当偏移量非常大的时候,比如:LIMIT 10000 20这样的查询,MySQL需要查询10020条记录然后只返回20条记录,前面的10000条都将被抛弃,这样的代价非常高。
优化这种查询一个最简单的办法就是尽可能的使用覆盖索引扫描,而不是查询所有的列。然后根据需要做一次关联查询再返回所有的列。对于偏移量很大时,这样做的效率会提升非常大。考虑下面的查询:
SELECT film_id,description FROM film ORDER BY title LIMIT 50,5;
如果这张表非常大,那么这个查询最好改成下面的样子:
SELECT film.film_id,film.description
FROM film INNER JOIN (
SELECT film_id FROM film ORDER BY title LIMIT 50,5
) AS tmp USING(film_id);
这里的延迟关联将大大提升查询效率,让MySQL扫描尽可能少的页面,获取需要访问的记录后在根据关联列回原表查询所需要的列。
有时候如果可以使用书签记录上次取数据的位置,那么下次就可以直接从该书签记录的位置开始扫描,这样就可以避免使用OFFSET,比如下面的查询:
SELECT id FROM t LIMIT 10000, 10;
改为:
SELECT id FROM t WHERE id > 10000 LIMIT 10;
其它优化的办法还包括使用预先计算的汇总表,或者关联到一个冗余表,冗余表中只包含主键列和需要做排序的列。
哪些情况需要建立索引
- 1.主键自动建立唯一索引
- 2.频繁作为查询条件的字段应该建立索引
- 3.查询中与其他表关联的字段,外键关系建立索引
- 4.频繁更新的字段不适合创建索引 --- 因为每次更新不单单是更新了记录还会更新索引,加重了IO负担
- 5.where条件里用不到的字段不创建所以
- 6.单键/组合索引的选择问题,who?(在高并发下倾向创建组合索引)
- 7.查询中排序的字段,排序字段若通过索引去访问将大大提高排序的速度
- 8.查询中统计或者分组字段
哪些情况不要创建索引
1.表记录太少
2.经常增删改的表 (why:提高了查询速度,同时却会降低更新表的速度,如对表进行insert,update,delete,因为在更新表时,mysql不仅要保存数据,还需要保存一下索引文件)。
3.数据重复且分布平均的表字段,因此应该只为最经常查询和最经常排序的数据列建立索引。
注意:如果某个数据列包含许多重复数据的内容,为它建立索引就没有太大的实际效果。
数据库函数
1.plsql,首创于oracle。
2.plsql,有两种,有返回值的叫函数,没有返回值的,叫存储过程。(一般都是用来执行一系列的复杂的数据库操作,比如自动生成表数据,
比如对多张表的关联增删改查。)
开启执行函数。
show variables like 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators = 1