一、SQL

结构化查询语言（Structured Query Language），一种非过程化的编程语言，通过 SQL 访问数据库，进行数据的增删改查操作，用户无需知道数据底层的存储方式，只需要站在高层的方式，去访问数据库

二、慢 SQL 语句的几种常见诱因

1、无索引、索引失效导致慢查询

以一个没有索引的列作为查询条件，解决方法就是建立合适的索引

一些特定的场景，索引出现失效的情况，进而导致慢查询

SELECT id,img FROM sxyp_user WHERE mobile > 15910441034;

2、锁等待

MySql 常用的存储引擎 InnoDB 和 MyISAM ，前者支持行锁和表锁，后者支持表锁

对于行锁而言，锁定对象粒度细小，适用于并发场景，但也会导致死锁

当使用大量的行锁，事务执行效率下降，导致 MySql 性能下降，因此，行锁升级为表锁

对于表锁而言，当进行更新操作时，其他的事务只能等待，严重影响性能

3、不恰当的 SQL 语句

习惯使用 <SELECT *>

<SELECT COUNT(*)> SQL 语句

在大数据表中使用 <LIMIT M,N> 分页查询

对非索引字段进行排序

三、优化 SQL 语句的步骤

1、通过 EXPLAIN 分析 SQL 执行计划

SQL 先后查询哪些表，是否使用索引，数据从哪里获取，获取到的数据记录有多少行

2、通过 Show Profile 分析 SQL 执行性能

深入 MySql 内核，从执行线程的状态和时间上分析 SQL ，选择 Show Profiles 查询最近发送给 MySql 的语句，再使用 Show Profile For Query ID 查看具体的 SQL 语句执行过程中的状态信息

语法

SHOW PROFILE [type [, type] ... ][FOR QUERY n][LIMIT row_count [OFFSET offset]]type参数：| ALL：显示所有开销信息| BLOCK IO：阻塞的输入输出次数| CONTEXT SWITCHES：上下文切换相关开销信息| CPU：显示CPU的相关开销信息 | IPC：接收和发送消息的相关开销信息| MEMORY ：显示内存相关的开销，目前无用| PAGE FAULTS ：显示页面错误相关开销信息| SOURCE ：列出相应操作对应的函数名及其在源码中的调用位置(行数) | SWAPS：显示swap交换次数的相关开销信息

3、SQL 工具实现 SQL 性能分析与监控

打开慢 SQL 配置项，记录都有哪些 SQL 超出了预期的最大执行时间

Show variables like 'slow_query%';Show variables like 'long_query_time';

set global slow_query_log='ON'; //开启慢SQL日志set global slow_query_log_file='/var/lib/mysql/test-slow.log';//记录日志地址set global long_query_time=1;//最大执行时间

四、常用 SQL 优化

1、优化分页查询

使用 ORDER BY + <LIMIT offset,rows> 实现分页查询

（1）没有索引条件支持

查询需要做大量的文件排序操作（file sort），性能非常糟糕

（2）具有索引条件支持

前面部分的数据查询效率比较不错，越往后，则查询性能变差

案例

SELECT * FROM sxyp_user ORDER BY id LIMIT 10000,20;

SQL 语句需要查询 10020 条数据记录，然后获取最后的 20 条返回给请求端，查询性能也会非常差

解决方案：利用子查询优化分页查询

之前的 SQL 查询，需要获取 10020 条记录，可以通过优化，先查询出所需要的 20 行记录中的最小值，然后，通过偏移量返回需要的 20 行记录

SELECT * FROM sxyp_user WHERE id >= (SELECT id FROM sxyp_user ORDER BY id LIMIT 10000,1) LIMIT 20;

2、优化 SELECT COUNT(*)

COUNT()是聚合函数，主要统计行数，也会统计某一列的行数（不统计 NULL 值的行）

存储引擎 MyISAM 和 InnoDB

没有查询条件下，MyISAM 优于 InnoDB，因为 MyISAM 自身就会记录整个表的行数，无需遍历统计，而 InnoDB 需要扫描表来统计具体的行数

具有查询条件下，MyISAM 和 InnoDB 都需要遍历扫描表的行记录

优化方案

（1）使用 EXPLAIN 获取近似值

当不需要返回精确的 COUNT 值时，可以使用 EXPLAIN 对表进行估算，返回一个近似值，EXPLAIN 不会对表执行真正的查询

（2）增加汇总统计

需要精确的 COUNT 值，可以新增汇总统计表或者缓存字段来统计 COUNT 值，新增数据和删除数据时会有成本，但可以大大提高 COUNT()性能

3、优化 SELECT *

不使用查询条件，查询性能肯定非常糟糕，尤其数据量巨大的情况下

使用查询条件，在特定情况下也会存在性能损耗。InnoDB 的主键索引为聚簇索引，基于 B+树构成，行记录存储在叶子节点上，MyISAM 的主键索引和二级索引，以及 InnoDB 的二级索引都属于非聚簇索引，基于 B+树构成，叶子节点存储主键值

假设基于 InnoDB 存储引擎，存在 order_no 和 status 两列组成的组合索引，根据订单号查询订单表 status，使用 select * from order where order_no='xxx’ 查询，则先会查询组合索引，通过组合索引查询主键 ID，再通过主键 ID 去主键索引查询行记录

如果使用 select order_no, status from order where order_no='xxx’ 查询，则只会查询组合索引，通过组合索引获取对应的 order_no 和 status

减少两个表联合查询的压力，将两个表进行分别查询

五、场景实例

1、问题一

问题描述

假设有一张订单表 order，主要包含了主键订单编码 order_no、订单状态 status、提交时间 create_time 等列，并且创建了 status 列索引和 create_time 列索引。此时通过创建时间降序获取状态为 1 的订单编码，以下是具体实现代码

select order_no from order where status =1 order by create_time desc

问题在哪？如何优化？

解决思路

实际案例 SQL 性能分析

sxyp_user 二级索引

（1）使用主键 ID 作为显示列

EXPLAIN SELECT id FROM sxyp_user;

（2）使用二级索引作为查询条件

EXPLAIN SELECT id FROM sxyp_user WHERE mobile > 15910441034;

（3）显示列包含非索引字段

EXPLAIN SELECT id,img FROM sxyp_user WHERE mobile > 15910441034;

（4）显示列包含索引字段

EXPLAIN SELECT id,open_id FROM sxyp_user WHERE mobile > 15910441034;

（5）使用其他索引字段排序

EXPLAIN SELECT id FROM sxyp_user WHERE mobile > 15910441034 ORDER BY uname;

结论

基于以上分析，SQL 语句查询，查询条件通过 status 索引结构返回主键订单编号 order_no，由于使用 Extra 列 create_time 进行数据排序，这里并没有使用到 create_time 索引结构，故排序会使用 filesort 文件排序，导致查询性能极大降低

可以通过构建 status 和 create_time 联合索引，提高本条 SQL 语句的查询效率

2、问题二

品质商城项目，基于配置的活动商品，活动时间范围内普通商品设置活动价格，前端页面正确展示商品价格，商品处于非活动时间范围内，则商品的活动价格恢复为默认值，整个商品的活动价格变更基于批处理程序实现

（1）非活动上架时间，将具有活动价格的商品，更新活动价格为默认值

SELECT DISTINCTa.id AS "id",a.product_no AS "productNo",a.sku AS "sku",a.type AS "type",a.product_name AS "productName",a.category_type AS "categoryType",a.up_category_type AS "upCategoryType",a.saleunit AS "saleunit",a.weight AS "weight",a.wareqd AS "wareqd",a.product_area AS "productArea",a.main_image_path AS "mainImagePath",a.param AS "param",a.brandname AS "brandname",a.upc AS "upc",a.category AS "category",a.jd_state AS "jdState",a.state AS "state",a.introduction AS "introduction",a.create_time AS "createTime",a.online_time AS "onlineTime",a.offline_time AS "offlineTime",a.bus_productno AS "busProductno",a.downpayment_ratio AS "downpaymentRatio",a.price AS "price",a.sales_volume AS "salesVolume",a.view_volume AS "viewVolume",	a.supplier_no AS "supplierNo" FROMquality_product aLEFT JOIN quality_activity_product b ON a.product_no = b.product_no	LEFT JOIN quality_activity c ON c.activity_no = b.activity_no WHERE	a.activity_price IS NOT NULL AND ((			b.online_time > CURDATE() 			OR b.offline_time < CURDATE() OR b.start_time >= CURTIME() 			OR b.end_time <= CURTIME()) OR (			b.online_time <= CURDATE() AND b.offline_time >= CURDATE() 			AND b.start_time <= CURTIME() AND b.end_time >= CURTIME() 			AND c.isSynchronizePrice = 0 		) OR b.id IS NULL)

（2）活动上架时间范围内，更新商品的活动价格

SELECTc.id,c.product_no AS "productNo",	b.activity_price AS "activityPrice" FROMquality_activity a,quality_activity_product b,	quality_product c WHERE	a.activity_no = b.activity_no 	AND b.product_no = c.product_no 	AND ( c.activity_price = 0 OR c.activity_price IS NULL OR c.activity_price <> b.activity_price ) 	AND a.STATUS = 1 	AND a.isSynchronizePrice = 1 	AND a.online_time <= CURDATE() AND a.offline_time >= CURDATE() 	AND a.start_time <= CURTIME() AND a.end_time >= CURTIME() 	AND ( b.is_syn = '1' OR b.is_syn IS NULL ) AND b.STATUS = 1

问题的关键在于同一个商品会配置在不同的活动之下。虽然同一个时间段内，只能有一个活动的活动价格起作用，但是，这个时间段内，也会有不在活动时间内的活动

假设具有两个活动，即 A 活动和 B 活动，当前处于 A 活动的活动时间内，商品应当设置为 A 活动的活动价格，而 B 活动处于活动时间范围以外，那么，商品应当设置为默认价格

按照程序处理逻辑，一个商品只是处于一个活动当中，没有任何问题。但是处于两个活动之中，商品的价格刚好恢复为默认价格，马上又更新为活动价格，不断重复读取数据和修改数据，造成数据库的请求压力增加，降低数据库性能

痛点：商品的活动价格反复恢复和更新

解决方案

核心：增加是否同步过的字段 is_syned ，用于避免重复恢复和更新

1，扫描当前时间下，处于活动时间内，需要同步活动价格，但是还未同步过活动价格的商品，更新活动商品表和商品表信息

2，管理后台控制不会出现同一个时间段同步多个活动价格的情况

3，将当前时间下，具有活动价格，但未处于活动时间内的商品的活动价格置为空。程序有一个小技巧，便是先查询出当前时间下处于活动中并且已经同步过的商品，然后，找出商品的活动价格不为空，不在以上数据集合中的商品，进而恢复为默认值

4，把已同步过活动价格的活动商品表数据，处于活动时间以外，并且处于上线状态的数据，更新为未同步，以便下一个活动时间继续同步活动价格

3、问题三

信审后台管理系统首页位置，为系统用户展示信审业务统计数据信息，统计数据需要通过图表进行展示，便于系统用户直观查看

1.1、今日展示统计数据

1)今日申请用户数

2)今日自动审核通过用户数

3)今日额度激活成功用户数

4)今日放款笔数/总额

5)今日还款笔数/总额

1.2、累计展示统计数据

1)累计申请用户数

2)累计额度激活成功用户数

3)累计放款总笔数/金额

4)累计还款总笔数/金额

1.3、信审统计数据趋势

1)最近 7 天申请用户数

2)最近 7 天审核通过用户数

3)最近 15 天放款总金额

4)最近 15 天还款总金额

1.4、用户风险统计

1)风险事件统计

该数据统计出在一定周期内的用户申请情况，目前统计周期以 7 天为限，分为审核通过、审核拒绝、等待审核

2)今日申请用户 top10

按照申请人所在申请省份统计

解决方案

当时的解决方案，就是直接实时查询数据库数据，并没有进行 SQL 优化，由于前期数据量较少，故没有暴露问题，当数据库急剧上升时，查询效率就非常慢，故下面的方案为 SQL 方案

这里需要说明一点，基于当时的需求，为了更好的展示统计数据，故而引入了成熟开源的百度前端 echarts 组件，将数据可视化，带给客户生动直观的感受数据

任务排期

图片展示省略

数据展示，分为实时数据和历史数据，为了提高查询效率，减少 Select Count(*)带来数据访问压力，当带有查询条件，并具有索引时，效率会提高，但没有查询条件，需要遍历所有数据，效率下降

实时数据继续使用 Select Count(*)，历史数据建立统计表保存数据，便于快速查询展示