数据库--sql性能优化学习整理

1.基本概念

1.1 SQL查询的执行流程：

 这是从网上找来的一张流程图，根据流程图我们可以清晰的了解到一条sql查询流程步骤；

 可以分为按照通俗的步骤和层级性的概括两种理解：

sql执行流程讲述

按照常规步骤理解	层级性概括理解
第一步：客户端通过连接器进行权限校验，将SQL指令传输给MySql服务器。 第二步：服务器收到指令后会先查询缓存（在MySql8.0后好像就取消这个功能了） 第三步：若没有命中缓存，分析器会对sql指令进行词法和语法的分析，会帮我们简单的分析sql要干什么，然后在检查语句的语法是否正确。 第四步：优化器会生成执行计划然后选择索引 第五步：权限校验后开始执行sql语句，然后从存储引擎返回数据。	第一层：首先客户端通过连接服务，将要执行的sql指令传输过来。 第二层：然后服务器对其进行解析并且优化所要执行的sql语句，生成最终的执行计划，然后执行。 第三层：存储引擎，负责数据的存储和提取。

 1.2 SQL的执行顺序

在MySql中，sql的执行顺序也会影响到sql的性能，和我们平时写sql不同，SQL的执行顺序并不是从SELECT开始，而是从FROM开始的。下面一起简单看下：

 

 按照上图，sql的执行顺序应为：FROM--LEFT JOIN --ON--WHERE--GROUP BY--SUM/COUNT---HAVING--SELECT--DISTINCT--ORDER BY--LIMIT

• FROM:先去获取from里面的表，拿到所对应的数据，此时生成虚拟表1；
• ON:对虚拟表1进行ON筛选，则符合条件的数据生成虚拟表2；
• LEFT JOIN:再根据LEFT JOIN去执行左连接操作，此时获得对应的数据，生成虚拟表3；
• WHERE:在对虚拟表3的数据进行条件筛选，符合要求的数据则生成虚拟表4；
• GROUP BY：根据GROUP BY,对虚拟表4的列进行分组操作，生成虚拟表5；
• SUM/COUNT(聚合函数)：结合特定的聚合函数生成虚拟表6；
• HAVING:对虚拟表6在进行条件筛选，生成虚拟表7；
• SELECT:选择查询指定的列，生成虚拟表8；
• DISTINCT:进行数据去重，生成虚拟表9；
• ORDER BY:对虚拟表9中的数据进行指定列的排序，生成虚拟表10；
• LIMIT:取出指定行的记录，生成虚拟表11，返回给查询用户；

 以上为sql的逻辑执行顺序简述

 2.性能分析

我们在写运行sql的时候，经常会出现执行很慢的SQL，那么这个时候就要分析导致他变慢的原因，常见原因如下：

2.1 表结构相关分析：

•  数据类型设置不够合理
• 表中的数据量过大
• 未设置读写分离

 2.2 查询语句相关分析：

• 查询语句写的不合理
• 表连接顺序不合理
• 排序、聚合等函数导致慢查询
• 查询搜索了不必要的子弹，导致慢查询
• 未设置查询的条件范围或超出了搜索范围，导致查询了全表，从未出现慢查询情况

2.3 索引相关分析：

• 未建立索引，导致整张表查询起来没有索引
• 索引不合理，低效的索引会降低查询的效率
• 索引为命中，虽然创建了索引，但是在部分查询条件下索引没用上。

2.4 其他方面分析

• 相同的内容进行了多次查询（可以列用redis缓存，减少查询次数，提高查询效率）
• 出现死锁现象，多个事务锁定资源，而又继续请求对方已经获取的资源。

3 表结构的优化

3.1 数据类型的选择

• 尽量使用可以存下数据最小的数据类型
• 尽量使用一些简单的数据类型。例如：int要比varchar类型处理简单些。
• 尽量使用tinyint、smallint、mediumint作为整数类型，而不是一贯的使用的int类型。
• 尽量使用not null来定义字段，因为null占用4字节空间。数字可以默认为0，字符串可默认为" "
• 尽量减少使用text类型，一定要用的时候可以考虑是否需要分表；
• 尽量使用timestamp而非datetime;
• 单表情况下尽量不要设置太多的字段，建议在20个以内；

3.2 表的拆分

当数据库中的数据非常大时，查询优化方案也不能解决查询速度慢的问题时，我们可以考虑拆分表，让每张表的数据量变小，从而提高查询效率。

• 垂直拆分:将表中的多个列分开放到不同的表中。例如用户表中的一些字段经常被访问，将这些字段放在一张表中，另外一些不常用的字段放在另一张表中。插入数据时，使用事务来确保两张表的数据一致性。
• 水平拆分：按照行来进行拆分。例如用户表中，使用用户ID，对用户ID取10的余数，将用户数据均匀的分配到0-9的10个用户表中。查找时也按照这个规则查询数据。

3.3 读写分离

一般情况下对数据库而言都是“读多写少”。换句话说，数据库的压力大多数都是因为大量的读取数据的操作而导致的。所以我们可以采取数据库集群的方案，使用一个库作为主库，负责写入数据；其他的库为从库，负责读取数据。这样就可以有效的缓解对数据库的访问压力。

4 查询优化分析

4.1 影响指标

• 响应时间
• 扫描的行
• 返回的行

4.2 查询优化点

4.2.1 避免使用select *

当你想在select子句中列出所有的列时，使用“ ”是一个方便的方法，但这是一种非常低效的方法。sql解析过程中，还需要把“ ”依次转换为所有的列名，这个工作需要查询数据字典完成！

SELECT empno,ename,category FROM emp WHERE empno = 7369;    -- 推荐
SELECT * FROM emp WHERE empno = 7369;   -- 不推荐

4.2.2 多表联查时，小表在前，大表在后

from 后的表关联查询是从左往右执行的（Oracle相反），第一张表会涉及到全表扫描，所以将小表放在前面，先扫小表，扫描快效率较高，在扫描后面的大表

4.2.3 调整where子句中的连接顺序

where子句是从左往右，自上而下的顺序执行的（Oracle相反），根据这个原理，应将过滤数据多的条件往前放，最快速度缩小结果集。

4.2.4 调整group by 和order by 子句中的顺序

group by和order by子句是从左往右的顺序执行的，根据这个原理，应将排序影响数据多的条件往前放，最快速度缩小结果集。

4.2.5 用exists、not exists和in、not in相互代替

exists以外层表为驱动表、先被访问，适合用于外表小，内表大的情况。

in则是先执行子查询，适合外表大，内表小的情况，一般情况不推荐使用not in 。因为效率不是很高。

原则是哪个的子查询产生的结果集小，就选哪个

select * from t1 where x in (select y from t2);
select * from t1 where exists (select null from t2 where y =x);

4.2.6 避免产生笛卡尔积

含有多表的sql语句，必须指明各表的连接条件，以避免产生笛卡尔积。N个表连接需要N-1个连接条件。

4.2.7 用where子句替换having子句

where子句搜索条件在进行分组操作之前应用；而having子句条件在进行分组操作之后应用。

避免使用having子句，having子句只会在检索出所有纪录之后才对结果集进行过滤，这个处理需要排序，总计等操作。如果能通过where子句限制记录的数目，那就能减少这方面的开销。

4.2.8 分段和分页操作

使用合理的分页方式，在数据表量级逐渐增加的时候，limit分页查询的效率会降低。

优化思想：避免数据量大时扫描过多的记录

5 索引优化

5.1 索引使用场景

5.1.1 适合使用的场景

• 主键自动创建唯一索引
• 频繁作为查询条件的子弹
• 查询中与其他表相关联的字段
• 查询中排序的字段
• 查询中统计或分组字段（聚合函数或group by ）

5.1.2 不适合使用的场景

• 表记录太少
• 频繁更新的字段
• 经常删改的表
• where条件中用不到的字段
• 字段的值的差异性不大或者重复性过高

5.2 索引创建和使用原则

• 单表查询：哪个列作为查询条件，就在该列创建索引
• 多表查询：left join时，索引添加到右表关联字段；right join时，索引添加到左表关联字段。
• 不要对索引列进行任何操作（运算符、计算、函数、类型转换）
• 索引列不要为空，并且不要是用is null或者 is not null判断
• 索引字段是字符串类型，查询条件的值要加单引号，避免底层类型自动转换
• 违背上述原则可能会导致索引失效，具体情况需要使用explain命令进行查看

5.3 索引失效情况

5.3.1 尽量避免在字段开头模糊查询:

优化方式：

a.尽量在字段后面使用模糊查询。如下：

SELECT * FROM t WHERE username LIKE '%陈%'; -- 不走索引
SELECT * FROM t WHERE username LIKE '陈%'; -- 走索引

b.修改前端代码

例如，把查询条件的供应商名称一栏由原来的文本输入改为下拉选择列表，用户模糊输入供应商名称时，直接在前台就帮忙定位到具体的供应商，这样在调用后台程序时，这列就可以直接用等于来关联了。

c.修改后台逻辑

例如，根据输入条件，先查出符合条件的供应商，并把相关记录保存在一个临时表里头，然后再用临时表去做复杂关联。

5.3.2 尽量避免使用not in和in（连续）

优化方式：

a.如果是连续数值，可以用between代替。如下：

SELECT * FROM t WHERE id IN (2,3); -- 不走索引
SELECT * FROM t WHERE id BETWEEN 2 AND 3; -- 走索引

b.如果是子查询，可以用exists代替。如下：

select * from A where A.id in (select id from B);   -- 不走索引
select * from A where exists (select * from B where B.id = A.id);   -- 走索引

5.3.3 尽量避免使用or或in（不连续）

优化方式：可以使用union代替or或者in。如下所示：

SELECT * FROM t WHERE id = 1 OR id = 3; -- 不走索引
SELECT * FROM t WHERE id in (1, 3); -- 不走索引
SELECT * FROM t WHERE id = 1
   UNION
SELECT * FROM t WHERE id = 3; -- 走索引

5.3.4 尽量避免使用查询条件!=或<>

使用索引列作为条件进行查询时，需要避免使用<>或者!=等判断条件。如确实业务需要，使用到不等于符号，需要在重新评估索引建立，避免在此字段上建立索引，改由查询条件中其他索引字段代替。 

5.3.5 尽量避免进行null值的判断

优化方式：可以给字段添加默认值，0，对0值进行判断。如下：

SELECT * FROM t WHERE score IS NULL; -- 不走索引
SELECT * FROM t WHERE score = 0; -- 走索引

5.3.6 尽量避免在where条件中等号的左侧进行表达式、函数的操作

SELECT * FROM T WHERE score/10 = 9; -- 不走索引
SELECT * FROM T WHERE score = 9*10; -- 走索引

5.3.7 尽量避免使用复合索引时，不使用第一个索引列

复合（联合）索引包含key1，key2，key3三列，SQL语句没有包含索引前置列"key1"

优化方案：按照联合索引的最左匹配原则，where语句包含前置列

select col1 from table where key2=2 and key3=3; -- 不走索引
select col1 from table where key1=1 and key2=2 and key3=3; -- 走索引

5.3.8 order by条件要与where中条件一致，否则order by不会利用索引进行排序

SELECT * FROM t order by age; -- 不走age索引
SELECT * FROM t where age > 0 order by age; -- 走age索引

对于上面的语句，数据库的处理顺序是：

• • 第一步：根据where条件和统计信息生成执行计划，得到数据。

  • 第二步：将得到的数据排序。当执行处理数据（order by）时，数据库会先查看第一步的执行计划，看order by 的字段是否在执行计划中利用了索引。如果是，则可以利用索引顺序而直接取得已经排好序的数据。如果不是，则重新进行排序操作。

  • 第三步：返回排序后的数据。

当order by 中的字段出现在where条件中时，才会利用索引而不再二次排序，更准确的说，order by 中的字段在执行计划中利用了索引时，不用排序操作。

这个结论不仅对order by有效，对其他需要排序的操作也有效。比如group by 、union 、distinct等。

5.4 执行计划分析

可以通过explain可以知道mysql是如何处理语句的，并分析出查询或是表结构的性能瓶颈，其实就是在干查询优化器的事，通过expalin可以得到：

• 表的读取顺序
• 表的读取操作的操作类型
• 哪些索引可以使用
• 哪些索引实际被使用
• 表之间的引用情况
• 每张表有多少行被优化器查询

执行explain时，会得到一个表格，这个表格就是分析结果，下面对表格的表头进行一一说明：

id: MySQL QueryOptimizer 选定的执行计划中查询的序列号。表示查询中执行select 子句或操作表的顺序，id 值越大优先级越高，越先被执行。id 相同，执行顺序由上至下。

select_type: 一共有9中类型,只介绍常用的4种:

（     SIMPLE: 简单的 select 查询,不使用 union 及子查询
         PRIMARY: 最外层的 select 查询
         UNION: UNION 中的第二个或随后的 select 查询,不 依赖于外部查询的结果集
         DERIVED: 用于 from 子句里有子查询的情况。 MySQL 会 递归执行这些子查询, 把结果放在临时表里。）

*table:* 输出行所引用的表名，如果使用了别名则显示别名

partitions：使用的哪个分区

possible_keys : 哪些索引可能有助于查询。如果为空,说明没有可用的索引。

key*:* 实际从 possible_key选择使用的索引，如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。

key_len: 使用的索引的长度。在不损失精确性的情况 下,长度越短越好。

ref: 显示索引的哪一列被使用了

rows: 请求数据返回的大概行数

filtered：表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例。

extra: 其他信息，出现Using filesort、Using temporary 意味着不能使用索引,效率会受到重大影响。应尽可能对此进行优化。

其中重要的几个就是 Type、*key* 、*rows*、*extra*。

• • Type一般需要达到 ref、eq_ref 级别，范围查找需要达到 range

  • *key*为null、all 、index时，需要调整、优化索引。

  • rows可以直观看出优化结果

  • extra有Using filesort、Using temporary 的一定需要优化