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有更好的性能。如果确实需要使用关联查询的情况下，需要特别注意的是：

1. 确保ON和USING字句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表A和表B用列c关联的时候，如果优化器关联的顺序是A、B，那么就不需要在A表的对应列上创建索引。没有用到的索引会带来额外的负担，一般来说，除非有其他理由，只需要在关联顺序中的第二张表的相应列上创建索引（具体原因下文分析）。
2. 确保任何的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