SQL优化技巧-优快云博客

本文链接：https://blog.youkuaiyun.com/wcfcangzhuo/article/details/107751869

在进行 sql 优化之前，我们可以先进行如下检测：

1. 是否向数据库请求了不需要的数据，比如多表关联查询时返回全部列。

2. 数据库是否扫描了额外的行记录。

测试sql语句效率的常用方法：

1. 慢查询日志

慢查询是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阈值的语句。

默认情况下，MySQL数据库并不启动慢查询日志，需要手动来设置这个参数。如果不是调优需要，一般不建议启动该参数，开启慢查询日志会带来一定的性能影响。

// 查看慢查询配置
show VARIABLES like '%slow%'

// 查看慢查询的时间阀值
show VARIABLES like 'long_query_time'
// 设置慢查询的时间阀值
set long_query_time=2

// 开启慢查询
set global slow_query_log='ON'

开启慢查询后，超过阀值的sql查询语句的信息会在日志文件中显示。

日志文件中会显示出耗时 sql 的 响应时间、扫描的行数和返回的行数。

2. 使用explain命令

explain命令在解决数据库性能上是第一推荐使用命令，大部分的性能问题可以通过此命令来解决，explain可以用来查看 SQL 语句的执行效果，可以帮助选择更好的索引和优化查询语句。

explain SELECT * from users where name='zhangsan'

其中关键的参数：

select_type：select_type就是select的类型，有以下几种类型:

SIMPLE：简单SELECT(不使用UNION或子查询等)
PRIMARY：最外面的SELECT
UNION：UNION中的第二个或后面的SELECT语句
DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询
UNION RESULT：UNION的结果。
SUBQUERY：子查询中的第一个SELECT
DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询
DERIVED：导出表的SELECT(FROM子句的子查询)

type：访问方式。这列最重要，显示了连接使用了哪种类别，有无使用索引，是使用Explain命令分析性能瓶颈的关键项之一。

结果值从好到坏依次是：

system > const > eq_ref > ref >  ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说，得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题。

system:  表仅有一行。这是 const 连接类型的一个特例。

const:  const 用于用常数值比较 PRIMARY KEY 时。

eq_ref: 查询使用了索引为主键或唯一键的全部时使用。即：通过索引关键字可能查找到一个符合条件的行。

ref:  通过索引关键字可能查找到多个符合条件的行。

ref_or_null:  如同 ref, 但是 MySQL 必须在初次查找的结果里找出 null 条目,然后进行二次查找。

index_merge: 说明索引合并优化被使用了。

unique_subquery:  在某些 IN 查询中使用此种类型,而不是常规的 ref:valueIN (SELECT primary_key FROM single_table WHERE some_expr)

index_subquery:  在 某 些 IN 查 询 中 使 用 此 种 类 型 , 与unique_subquery 类似,但是查询的是非唯一 性索引

range:  检索给定范围的行。当使用 <>、>、>=、<、<=、BETWEEN 或者 IN 操作符时,会使用到range。

index:  全表扫描,只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。

all: 最坏的情况,从头到尾全表扫描。

possible_keys：可能使用到的索引。

key：实际使用的索引，如果没有使用索引，值为null。

key_len：索引的长度，在保证精确性的前提下，索引长度越短越好。

rows：MySQL认为执行查询时必须要检查的行数。

Extra：执行查询的详细信息，有以下几种取值：

Distinct
一旦MYSQL找到了与行相联合匹配的行，就不再搜索了

Not exists
MYSQL 优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了

Range checked for each Record（index map:#）
没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一

Using filesort
看到这个的时候，查询就需要优化了，表示 为了得到所需结果集，需要对所有记录进行文件排序。

Using index
列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表 的全部的请求列都是同一个索引的部分的时候

Using temporary
看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，通常是因为group by的列上没有索引，也有可能是因为同时有group by和order by，但group by和order by的列又不一样。

Using where
使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index， 这就会发生，或者是查询有问题

当type 显示为 “index” 时，并且Extra显示为“Using Index”，表明使用了覆盖索引。

上面两种方法可以结合起来使用，当一个系统存在大量sql查询语句时，可以先通过慢查询找到超过时间阀值的查询语句，再通过explain对其进行分析。

1.通过索引进行优化

1.1 如何添加索引？

MySQL索引通常用于提高where条件匹配时的查询速度。

创建并使用自增数字来建立主键索引

1. 如果使用自增ID作主键，每次插入一行记录，记录就会顺序地放在原数据的后面。能够提高查询和插入的性能。

2. 如果不使用自增数字作主键，插入数据时，会造成频繁的页分裂和页旋转，导致插入效率低下。

经常作为where查询条件的字段建立索引
可考虑使用联合索引并进行索引覆盖（联合索引是实现索引覆盖的方式）

索引不是越多越好，索引的本质是一种维护底层数据的数据结构，索引太多会导致占用太多存储空间。数据变更需要维护索引，索引太多会增加维护成本。

可适当删除一些不必要的索引以及低效索引。

添加索引：

1.添加PRIMARY KEY（主键索引） 
mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

2.添加UNIQUE(唯一索引) 
mysql>ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 

3.添加INDEX(普通索引) 
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 

4.添加FULLTEXT(全文索引) 
mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 

5.添加组合索引 
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

索引覆盖：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

（在我上一篇博客中写道：InnoDB 辅助索引的B+树叶节点中不存储数据，存储的是主键值）

我举个例子来理解索引覆盖：

我的这个表中，id为主键索引，name为普通索引

查询id，name，可见使用了 name_index这个索引。Extra:Using index 表示查询的列完全被索引覆盖，即SQL所需要返回的所有列数据均在一棵索引树上，并且where筛选条件是索引列。

查询id,name,email，仍使用到了 name_index 这个索引。但Extra为Using index condition，表示查询的列未被索引全部覆盖，需要通过“回表”来查找未被覆盖的列。而回表会导致再查一遍主索引，虽然效率也较高，但不如 Using index。

优化：添加组合索引实现索引覆盖

ALTER TABLE users add INDEX combine_index(name,email)

此时，使用的是组合索引，Extra：Using index，SQL所需要返回的所有列数据均在一棵索引树上。

Extra的其他表现形式：

Using filesort：

Extra为Using filesort说明，得到所需结果集，需要对所有记录进行文件排序。这类SQL语句性能极差，需要进行优化。

在一个没有建立索引的列上进行 order by，就会触发filesort，常见的优化方案，在order by的列上添加索引，避免每次查询都全量排序。

1.2 索引失效的情况

1.2.1在列上进行运算和使用函数，将导致索引失效而进行全表扫描

select id from t where substring(name,1,3)='abc'
应改为:
select id from t where name like 'abc%'

1.2.2 like模糊查询时，%开头会导致索引失效

select id from t where name like '%abc' ，会导致索引失效。

1.2.3 对索引进行判空处理，会导致索引失效

select id from t where name is null

2. 拆分查询

1. 将一个复杂的大查询拆分成多个简单的查询

MySQL 的连接和建立连接都是轻量级的，并且MySQL内部每秒能够扫描上百万行数据，但把响应数据给客户端就慢得多。

所以在某些情况下将一个复杂的大查询拆分成多个简单的查询就很有必要。

2. 分解关联查询

将复杂的多表关联查询，优化成多个单表查询，然后将查询结果在业务逻辑中进行关联。

优点：

1. 让MySQL的查询缓存效率更高。

在这里介绍一下MySQL的查询缓存：数据库可以对查询数据进行缓存，当相同的查询sql语句到来时，MySQL会立刻返回缓存结果。查询缓存系统会跟踪查询涉及到的所有表，如果某个表的表结构或数据发生变化，那么和这个表相关的所有缓存都会失效。所以，查询缓存非常适合读多写少的场景。

为充分利用查询缓存，应尽量减少像 NOW() 和 RAND() 或是其它的诸如此类的SQL函数，因为这些函数的返回值是不定的，查询缓存不会生效。

2. 将查询分解后，执行单个查询可以减少锁的竞争。单个查询的本身效率可能会有所提升。

3. 理解 sql语句的加载顺序

1. FROM

sql操作的基本单位是表，所以sql语句操作前首先要找到数据表，而数据源是由FROM表明的。

当我们需要从多个表中获取数据时，可以连接多个表来查询，通过 JOIN 和 ON来实现连接，表之间的连接类型可以分为三种：

内连接：只返回出符合条件的列

例：select * from A INNER JOIN B ON A.A1=B.B1

外连接：

分为左外连接和右外连接。左外连接：左表数据会全部显示，右表只返回满足条件的数据。

例：select * from A LEFT JOIN B ON A.A1=B.B1

笛卡尔积

例：select * from A,B

在笛卡尔积运算后，再通过where A.A1=B.B1对两表数据进行连接筛选，与内连接效果相同。

由此可见，通过ON 和 WHERE都可对两表进行连接筛选，但ON的效率会更高一些。

2. WHERE

where子句用来处理FROM子句中生成的临时表。此时数据并没有流到select子句，所以在where子句中不能使用select子句中定义的别名。

ON和where，通常将复杂的过滤操作放在where中，ON语句常用来对表进行连接。

3. GROUP BY

group by子句把上一步生成的临时表中的数据进行分组。

在分组返回的结果中，每个分组最多只能返回一行（多于一行将会被覆盖）。在group by后面的处理语句，如select、having，只能使用group by出现的列或使用聚合函数。

当然这并不是语法上的强制要求。但是如果不是这样的话，返回结果没有任何意义。结果如下：虽然返回结果中也可以返回name项，但是同一组中只有一项返回了，其余的都被覆盖了，这样的话，其实没有太大意义。

4. HAVING

having子句用来过滤group by生成的临时表：

select age,count(id) as nums 
from table 
group by age
having count(id)>1

这里有个很容易出错的问题：count(*)和count(column)等价吗？
答案是：不等价，count(*)是返回的结果集中有多少记录（record），而count(column)是返回的结果集中某一列的行数，但是会忽略NULL的内容。因为NULL代表为空，空的意思是未定义，未定义的意思是可能为任何内容，这里很有意思的内容，就是如果我们希望读取某字段为空的内容。那如果使用where column=NULL，这样是错误的。因为它会返回column字段中名字为NULL的行。
这里，一般会建议不要用未定义列，如果一定会存在未定义的列，那可以使用特殊的字符内容，比如-1或者就添加NULL来进行标识。

5. SELECT

select选择出信息，生成虚拟表，返回给调用者。

select子句主要分两个阶段：计算表达式，处理distinct。

6. ORDER BY

order by对select生成的虚拟表进行排序。order by是唯一一个可以使用select子句创建的别名的地方。

如果orderby后的字段恰好是索引，我们知道索引排布通常是有序的，所以此时并没有真正的排序操作，此时速度也是最快的（优化手段之一）；不是索引咋办？那就需要进行文件排序。文件排序是指将取出的数据在内存中进行排序，所需要的内存区域大小可以通过sort_buffer_size系统变量来设置，在内存中进行排序会导致效率低，所以最好在排序字段建立索引。