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执行计划
主要用来查询sql的执行计划。
参考我的博客：https://www.cnblogs.com/dongyaotou/p/14580054.html
-- 创建表
CREATE TABLE L1(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );
CREATE TABLE L2(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );
CREATE TABLE L3(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );
CREATE TABLE L4(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );
-- 每张表插入3条数据
INSERT INTO L1(title) VALUES('ruyuan001'),('ruyuan002'),('ruyuan003');
INSERT INTO L2(title) VALUES('ruyuan004'),('ruyuan005'),('ruyuan006');
INSERT INTO L3(title) VALUES('ruyuan007'),('ruyuan008'),('ruyuan009');
INSERT INTO L4(title) VALUES('ruyuan010'),('ruyuan011'),('ruyuan012');
使用 EXPLAIN 关键字可以模拟优化器来执行SQL查询语句，从而知道MySQL是如何处理我们的SQL语句的，分析出查询语句或是表结构的性能瓶颈。
通过Explain我们可以获得以下信息：
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可能被使用
- 哪些索引真正被使用
- 表的直接引用
- 每张表的有多少行被优化器查询了
Explain select * from Users
输出格式：
| 专栏 | JSON名称 | 说明 |
|---|---|---|
id |
select_id |
SELECT标识符，顺序编号 |
select_type |
无 | SELECT类型，简单SELECT（不使用UNION或子查询） |
table |
table_name |
输出行的表格 |
partitions |
partitions |
匹配的分区，对于非分区表，值为NULL |
type |
access_type |
连接类型 |
possible_keys |
possible_keys |
可能选择的索引，列指示MySQL可以选择在此表中找到行的索引。 |
key |
key |
实际选择的索引 |
key_len |
key_length |
所选密钥的字节长度 |
ref |
ref |
列与索引的比较 |
rows |
rows |
估计要审查的行数，rows表示MySQL认为必须检查的行数来执行查询。 |
filtered |
filtered |
按表格条件过滤的行百分比 |
Extra |
无 |
（1）ID字段
ID相同，执行顺序由上至下
EXPLAIN SELECT * FROM L1,L2,L3 WHERE L1.id=L2.id AND L2.id = L3.id;
id|select_type|table|partitions|type |possible_keys|key |key_len|ref |rows|filtered|Extra|
--+-----------+-----+----------+------+-------------+-------+-------+-------------+----+--------+-----+
1|SIMPLE |L1 | |ALL |PRIMARY | | | | 3| 100.0| |
1|SIMPLE |L2 | |eq_ref|PRIMARY |PRIMARY|4 |world_x.L1.id| 1| 100.0| |
1|SIMPLE |L3 | |eq_ref|PRIMARY |PRIMARY|4 |world_x.L1.id| 1| 100.0| |
ID不同，如果是子查询，ID的序号会递增，ID值越大优先级越高，越先被执行
EXPLAIN SELECT * FROM L2 WHERE id = (
SELECT id FROM L1 WHERE id = (SELECT L3.id FROM L3 WHERE L3.title = 'ruyuan007'));
id|select_type|table|partitions|type |possible_keys|key |key_len|ref |rows|filtered|Extra |
--+-----------+-----+----------+-----+-------------+-------+-------+-----+----+--------+-----------+
1|PRIMARY |L2 | |const|PRIMARY |PRIMARY|4 |const| 1| 100.0| |
2|SUBQUERY |L1 | |const|PRIMARY |PRIMARY|4 |const| 1| 100.0|Using index|
3|SUBQUERY |L3 | |ALL | | | | | 3| 33.33|Using where|
（2）select_type字段
表示查询类型，主要用于区别普通查询，联合查询，子查询等的复杂查询。
simple: 简单的select查询，查询中不包含子查询或者UNIONprimary: 查询中若包含任何复杂的子部分，最外层查询被标记subquery: 在select或where列表中包含了子查询union: union连接的两个select查询，第一个查询是dervied派生表，除了第一个表外，第二个以后的表select_type都是unionderived: 在from列表中包含的子查询被标记为derived（派生表），MySQL会递归执行这些子查询，把结果放到临时表中union result: UNION 的结果
EXPLAIN SELECT * FROM (select * from L3 union select * from L4)a;
id|select_type |table |partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra |
--+------------+----------+----------+----+-------------+---+-------+---+----+--------+---------------+
1|PRIMARY |<derived2>| |ALL | | | | | 6| 100.0| |
2|DERIVED |L3 | |ALL | | | | | 3| 100.0| |
3|UNION |L4 | |ALL | | | | | 3| 100.0| |
|UNION RESULT|<union2,3>| |ALL | | | | | | |Using temporary|
EXPLAIN select a.* from L1 a
union
select b.* from L2 b
id|select_type |table |partitions|type |possible_keys|key |key_len|ref|rows|filtered|Extra |
--+------------+----------+----------+-----+-------------+---------+-------+---+----+--------+---------------+
1|PRIMARY |a | |index| |idx_title|403 | | 3| 100.0|Using index |
2|UNION |b | |ALL | | | | | 3| 100.0| |
3|UNION RESULT|<union1,2>| |ALL | | | | | | |Using temporary|
（3）type字段
type字段显示的是连接类型 ( join type表示的是用什么样的方式来获取数据)，它描述了找到所需数据所使用的扫描方式, 是较为重要的一个指标。
完整的连接类型比较多
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL简化之后,我们可以只关注一下几种
system > const > eq_ref > ref > range > index > ALL
下面介绍type字段不同值表示的含义:
-
**system: ** 表中就仅有一行数据的时候. 这是const连接类型的一个特例,很少出现。
-
**const: ** const表示命中主键索引(primary key) 或者唯一索引(unique),表示通过索引一次就找到数据记录.
因为只匹配一条记录，所以被连接的部分是一个常量。(如果将主键放在 where条件中, MySQL就能将该查询转换为一个常量) 这种类型非常快。
例如以下查询:
explain select * from L1 where id = 3; -- 为L1表的title字段添加唯一索引 alter table L1 add unique(title); explain select * from L1 where title = 'ruyuan001'; id|select_type|table|partitions|type |possible_keys|key |key_len|ref |rows|filtered|Extra | --+-----------+-----+----------+-----+-------------+-----+-------+-----+----+--------+-----------+ 1|SIMPLE |L1 | |const|title |title|403 |const| 1| 100.0|Using index| -
eq_ref : 对于前一个表中的每个一行，后表只有一行被扫描。除了system和const类型之外，这是最好的连接类型。只有当联接使用索引的部分都是主键或惟一非空索引时，才会出现这种类型。
EXPLAIN SELECT L1.id,L1.title FROM L1 left join L2 on L1.id = L2.id;
id|select_type|table|partitions|type |possible_keys|key |key_len|ref |rows|filtered|Extra |
--+-----------+-----+----------+------+-------------+-------+-------+-------------+----+--------+-----------+
1|SIMPLE |L1 | |index | |title |403 | | 3| 100.0|Using index|
1|SIMPLE |L2 | |eq_ref|PRIMARY |PRIMARY|4 |world_x.L1.id| 1| 100.0|Using index|
- ref : 非唯一性索引扫描（使用了普通索引）, 对于前表的每一行，后表可能有多于一行的数据被扫描,它返回所有匹配某个单独值的行。
-- 为L1表的title字段添加普通索引,需要先删除之前的为索引
show index from L1;
alter table L1 add index idx_title (title) ;
EXPLAIN SELECT * FROM L1 inner join L2 on L1.title = L2.title;
id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra |
--+-----------+-----+----------+----+-------------+---------+-------+---------------+----+--------+-----------+
1|SIMPLE |L2 | |ALL | | | | | 3| 100.0|Using where|
1|SIMPLE |L1 | |ref |idx_title |idx_title|403 |mytest.L2.title| 1| 100.0|Using index|
- **range **: 索引上的范围查询，检索给定范围的行，between、in函数、> 都是典型的范围查询, 例如以下查询：
EXPLAIN SELECT * FROM L1 WHERE L1.id between 1 and 10;
id|select_type|table|partitions|type |possible_keys |key |key_len|ref|rows|filtered|Extra |
--+-----------+-----+----------+-----+-----------------+-------+-------+---+----+--------+-----------+
1|SIMPLE |L1 | |range|PRIMARY,idx_title|PRIMARY|4 | | 3| 100.0|Using where|
注: 当in函数中的数据很大时,可能会导致效率下降,最终不走索引
- index : 出现index 是 SQL 使用了索引， 但是没有通过索引进行过滤，需要扫描索引上的全部数据 ( 查找所有索引树,比ALL快一些,因为索引文件要比数据文件小 )，一般是使用了索引进行排序分组。
EXPLAIN SELECT * FROM L2 group by id order by id;
-- 该count查询需要通过扫描索引上的全部数据来计数
EXPLAIN SELECT count(*) FROM L2;
id|select_type|table|partitions|type |possible_keys|key |key_len|ref|rows|filtered|Extra |
--+-----------+-----+----------+-----+-------------+-------+-------+---+----+--------+-----------+
1|SIMPLE |L2 | |index| |PRIMARY|4 | | 3| 100.0|Using index|
- ALL : 没有使用到任何索引, 连接查询时对于前表的每一行，后表都要被全表扫描。
EXPLAIN SELECT * FROM L3 inner join L4 on L3.title = L4.title ;
id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra |
--+-----------+-----+----------+----+-------------+---+-------+---+----+--------+-------------+
1|SIMPLE |L3 | |ALL | | | | | 3| 100.0| |
1|SIMPLE |L4 | |ALL | | | | | 3| 33.33|Using where; |
| type类型 | 解释 |
|---|---|
| system | 不进行磁盘IO，查询系统表，仅仅返回一条数据,说白了就是数据库中该表只有一条记录。 |
| const | 查找主键索引，最多返回1条或0条数据，属于精确查找，说白了就是利用主键唯一值进行的查询。 |
| eq_ref | 查找唯一性索引，返回数据最多一条，属于精确查找，说白了就是利用主键进行关联查询，被关联的表的type就是这个值。 |
| ref | 查找非唯一性索引，返回匹配某一条件的多条数据，属于精确查找，数据返回可能是多条，说白了就是使用非唯一索引进行的关联查询，索引在哪个表上，哪个表的type就是该值。 |
| range | 查找某个索引的部分索引，只检索给定范围的行,属于范围查找，比如: > 、 < 、in 、between |
| index | 查找所有索引树，比ALL快一些,因为索引文件要比数据文件小 |
| ALL | 不使用任何索引，直接进行全表扫描 |
（4）possible_keys 与 key说明
-
possible_keys
显示可能应用到这张表上的索引， 一个或者多个。
查询涉及到的字段上若存在索引，则该索引将被列出,，但不一定被查询实际使用。
-
key
实际使用的索引，若为null，则没有使用到索引。（两种可能，1.没建立索引, 2.建立索引，但索引失效）。
查询中若使用了覆盖索引，则该索引仅出现在key列表中。
（5）rows
MySQL查询优化器会根据统计信息，估算SQL要查询到结果需要扫描多少行记录。原则上rows是越少效率越高，可以直观的了解到SQL效率高低。
（6）key_len
表示查询使用了索引的字节数量。
（7）filtered
filtered = 结果行数/rows(说白了就是要扫描的行数)X100
filtered列表示按表条件过滤的表行的估计百分比。最大值为100，这意味着没有对行进行过滤。从100减少的值表示过滤量增加。
（8）Extra表示很多额外的信息，各种操作会在Extra提示相关信息，常见几种如下：
Using index : 使用覆盖索引的时候就会出现
using index condition：查找使用了索引，但是需要回表查询数据
Using where ：在查找使用索引的情况下，需要回表去查询所需的数据
using index & using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
Using temporary：需要使用临时表来存储结果集，常见于排序和分组查询
Using filesort：无法利用索引完成的排序操作称为“文件排序”；
很多场景都是索引是一个字段，order by 排序的字段与索引字段不一致，导致的Using fileSort;
此时可以给排序字段和where条件字段，添加为组合索引，同时保证索引查询的数据不超过总量的15%，避免fileSort
