sql优化实战—优化启蒙篇

前言

    以前看到需要sql优化的时候往往无从下手，近期再一次学习了《mysql技术内幕Innodb存储引擎一书》，同时优化了一个线上的sql。整个过程由于是OLAP中间结果集较大，效果是由6S降低至2S的过程。在此记录一下理论结合sql的实际分享。以下理论只能说在整体方向上是没有问题的。如有偏差，还请各位大佬指点一二。

条件

    目前有order表  、订单记录表(会记录入库出库状态操作)order_record， 表结构分别如下。（表结构只是为了举列）

    goods:   goods_id 商品id

                  goods_name 商品名称

                  goods_type 商品类型 1、玩具

    order:    order_id    主键          假设表里数据量为300W

                 goods_id 商品id

                 create_time 创建时间

                 order_status 订单状态。只记录当前状态

    order_record:   假设表里数据量为1000W

                 order_record_id 主键

                 order_id  关联键订单id

                 order_status 订单状态。 1、待处理   2、已处理 3、退款

                 create_time 创建实际

优化案例一、适时的创建索引

现查询本月创建的订单

    select XX,XX from order where create_time > '2020-12-01' and create_time < '2021-01-01'

    如果create_time列上没有索引，mysql的查询会从聚簇索引关联的表数据找到所有的数据页，并进行每一个数据的字段找到create_time字段进行比较。

    如果create_time列上有索引。首先通过create_time的索引直接找到当前索引的所有符合条件的数据页。（如果占用了100W个数据页的话，那么按照二叉树的理论，则仅需要20次就可以查找到对应的数据范围）然后通过此索引关联的数据页获取具体的数据。

    不考虑其他的因素，在数据页的读取上添加索引能翻的时间倍数应该约为总数据量/12月份的数据总量，另外由于create_time不是聚簇索引，因此存在寻址读取的问题，所以效率应该会略低于这个倍数。 (数据量特别大的表，可能会受到实际内存或者CPU资源竞争的影响，因此出现倍数远大于理论倍数的问题)  此理论为猜测，如有疑义，欢迎评论讨论。

    1、索引的实际目的，加快查询效率

    2、索引附加意义，减少内存使用。减少服务器CPU、内存使用。

    注：索引是数据结构，不要乱加索引。

优化案例二、业务选择：是使用连表还是单表（以下是使用连表的案例，单表的暂时没有想到好的素材。暂时不妄下定义）

查询订单详情，其中包括商品名称，订单创建实际，当前订单状态，订单处理时间字段

    现有方案1、代码查询1、select t1.* from order t1 where t1.order_id  = #{id}，代码查询2、select t2.* from goods t2 where t2.goods_id = #{goods_id}, 代码查询3、select t3.* from order_record t3 where t3.order_record_id = #{order_record_id}

    现有方案2， select t1.goods_name, t2.create_time, t3. create_time as record_time from goods t1, orde t2, order_record t3 where t2.order_id  = t3.order_id and t1.goods_id = t2.goods_id and t2.order_id = #{order_id}

    假设关联键上均存在索引，方案2查询通过explain查看执行计划会发现如下三条记录：

    id                table                type                 key

    1                order                const               PRIMARY

    1                goods                ref                  IDX_GOODS_ID

    1                order_record     ref                 IDX_ORDER_ID

   首先执行是遵循explain顺序的，按照上述explain即为以下执行步骤：1、通过传入的order_id这个唯一的数据，以及order_id这个主键索引进行查找数据。此时会得到一条，然后根据关联方式获取到了这一条数据的GOODS_ID,，查找goods表中的信息，然后再根据这条数据的ORDER_ID，查找order_records表中的信息。由此可见两种方案其实对于数据库来说查找都是一致的。

    那么采用方案几呢？ 答案是方案2，减少和数据库进行交互的次数。（可以参考性能优化一书，书本推荐度不高，有些内容已经老旧，比如String的subString方法现在已经不会出现内存泄漏问题了。但是可以开阔一下自己的视野）

优化案例三、不要轻易使用子查询（子查询的反面案例）

 查询今天处理的订单

现有sql1：select t1.order_id, t1.create_time from order1 where exists(select 1 from order_record t2 where t1.order_id = t2.order_id and t2.create_time > '2020-12-01' and t2.create_time < '2021-12-02')

现有sql2：select t1.order_id, t1.create_time from order1, order2 where t1.order_id = t2.order_id and t1 t2.create_time > '2020-12-01' and t2.create_time < '2021-12-02'

explain查看sql1的执行计划：

 id                table                type                 key

1                  order               all                    没有

1                  order_record   ref                   IDX_ORDER_ID

explain查看sql2的执行计划：

 id                table                type                 key

1                  order_record   range              IDX_CREATE_TIME

1                  order               ref                   PRIMARY

由此可见1走了全表扫描，2走了索引。此时数据查询效率必然是2比1要高数个数量级。

优化案例四、减少驱动表的数据量

 查询今天创建的所有处理过的订单(假设字段create_time上没有索引)

 现有sql1：SELECT t1.create_time form order t1 where exists(select 1 from order_record t2 where t1.order_id = t2.order_id and t1.create_time > '2012-12-01' and t1.create_time < '2012-12-02' and t2.order_status = 2)

 现有sql2：SELECT t1.create_time form order t1 where t1.create_time > '2012-12-01' and t1.create_time < '2012-12-02' and exists(select 1 from order_record t2 where t1.order_id = t2.order_id and t2.order_status = 2)

 两个sql的区别仅是把查询条件从exists里拿到了exists外，但是我遇到过的类似sql，效率会相差300倍（具体倍数由数据量等因素决定）。主要区别在于sql1拿t1的全表数据与t2表进行关联，然后通过全表过滤。sql是使用过滤后数据的语句与sql2进行关联。因此效  率自然相差很多

注：可以结合exists和in来查看，两种查询方式驱动方式不一致，不要轻易相信exists效率高于in的假话。

优化案例五、适当的添加冗余字段，降低驱动表的数据量

 查询今天新建的索引商品类型为玩具的且已经处理的订单

现有sql1：select t1.create_time from order t1, goods t2, order_record t3 where t1.goods_id = t2. goods_id and t1.order_id = t3.order_id and t2.goods_type = 1 and t3.create_time > '2012-12-01' and t3.create_time < '2012-12-02'

此时sql1的explain为：

    id                table                type                 key

    1                order_record    range              IDX_CREATE_TIME

    1                goods                ref                  PRIMARY

    1                goods_record    ref                  PRIMARY

由此可见为先通过时间减少查询范围，然后通过索引进行查询。

此时方案2：order_records表中添加goods_id的记录

则此时sql可以更改为 select t1.create_time from order t1, order_record t3 where  t1.order_id = t3.order_id and t3.goods_type = 1 and t3.create_time > '2012-12-01' and t3.create_time < '2012-12-02'

此时会降低驱动表的数据量，从而增快查询效率。

优化思想六、强制指定索引，改变驱动表

 只是理论。mysql会按照自己的思想选择合适的索引，但是当你的索引较多时，mysql优化器的选择可能不是性能最高的情况。这个时候可以使用force_index进行强制指定索引，再强制指定索引的时候，可能会发生驱动表的变化，以尽量减少驱动表数据量的思想去尝试，或许会有其他的意外之喜。

实际理论基础可参考本人的其他关于数据库基础知识点介绍的博客。

https://mp.csdn.net/editor/html/110375562    索引

https://mp.csdn.net/editor/html/111155454    事务

https://mp.csdn.net/editor/html/111378563    锁

 

案例只是为了举例一种优化的思想，稍有不妥的地方，还请包含。