sql优化的方案记录

前言

相信我们都碰见过sql优化的问题,而大部分人的做法就是建立索引,在InnoDB引擎中索引分为 B+tree 和 Hash, 
Hash index : 
    Hash的应用场景不多,我们简单的聊几句带过,Hash 顾名思义是通过hash算法将索引列进行计算确定存储指针位置,优点就是查询非常快,缺点就是不能进行范围或者不等于查询,而且不合适的hash算法会造成大量的hash冲突

B+tree index : 
    这里涉及了一些数据结构的知识,需要我们去了解一下,B+tree也是由 二叉树 --> 平衡二叉树 --> B-tree(B杠tree) --> B+tree演化优化而来,这里有一篇不错的介绍,感兴趣的可以看看里面的具体演化过程和对InnoDB引擎的介绍
    文章地址: https://www.toutiao.com/i6709447784136704523/

方案

sql优化的方案记录:
    1. 尽量索引全值匹配
    2. varchar类型的引号不可省略
    3. 组合索引,索引要遵守最左匹配原则
    4. 不在索引列上做任何操作[计算,函数,(自动 or 手动)类型转换]会导致索引失效而进行全表扫描
    5. 范围条件放到最后[存储引擎不能使用索引中范围条件右边的列]
    6. 不等于要慎用[在使用不等于(!= 或者 <>)的时候无法使用索引会导致全表扫描]
    7. OR 改 UNION 效率高
    8. like百分号写最右边(为了遵守最左匹配原则)

测试案例

建表DDL语句(测试数据量大概200万):

CREATE TABLE `srcorder_item` (
  `order_item_no` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '订单行号',
  `order_no` varchar(20) DEFAULT NULL COMMENT '订单号',
  `cust_no` varchar(20) DEFAULT NULL COMMENT '会员编码',
  `commdty_code` varchar(20) DEFAULT NULL COMMENT '商品编码',
  `quantiy` int(11) DEFAULT NULL COMMENT '数量',
  `unit_price` decimal(28,6) DEFAULT NULL COMMENT '单价',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '最后更新时间',
  `order_header_no` bigint(20) DEFAULT NULL,
  `status` varchar(30) DEFAULT NULL COMMENT '订单行状态',
  `gc_order_no` varchar(20) DEFAULT NULL COMMENT '订单号',
  `gc_item_no` varchar(20) DEFAULT NULL COMMENT '订单行号',
  `invoice_status` char(1) DEFAULT NULL COMMENT '发票是否已开状态',
  PRIMARY KEY (`order_item_no`),
  KEY `srcorder_item_idx_1` (`order_no`) USING BTREE,
  KEY `srcorder_item_idx_2` (`cust_no`) USING BTREE,
  KEY `srcorder_item_idx5` (`create_time`,`status`) USING BTREE,
  KEY `srcorder_item_idx_7` (`gc_order_no`) USING BTREE,
  KEY `srcorder_item_idx_8` (`gc_item_no`) USING BTREE,
  KEY `srcorder_item_idx_10` (`update_time`) USING BTREE,
  KEY `srcorder_item_idx_3` (`order_header_no`,`commdty_code`) USING BTREE,
  KEY `srcorder_item_idx_11` (`update_time`,`cust_no`) USING BTREE,
  KEY `srcorder_item_idx_12` (`cust_no`,`gc_order_no`,`create_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1550000 DEFAULT CHARSET=utf8 COMMENT='订单行信息';

   Test1. 尽量索引全值匹配

explain 
select * from srcorder_item where cust_no = '6022517106' and gc_order_no ='1002296352' and create_time = STR_TO_DATE('2015-01-28 16:10:35', '%Y-%m-%d %H:%i:%s');

执行计划:

提示: 按索引全值匹配,应该走 srcorder_item_idx_12 这个索引,但实际 srcorder_item_idx_7 ,这里有一点需要注意,搜索引擎会优化选择合适的索引,但不一定是最合适的,我们可以强制让sql走 srcorder_item_idx_12 这个索引,测试一下平均查询时间

explain 
select * from srcorder_item force index(srcorder_item_idx_12) where cust_no = '6022517106' and gc_order_no ='1002296352' and create_time = STR_TO_DATE('2015-01-28 16:10:35', '%Y-%m-%d %H:%i:%s');

1)非强制执行5次的执行时间分别为      [0.022, 0.038, 0.040, 0.039, 0.025]  平均 = 0.0328

2)强制索引执行5次的执行时间分别为  [0.033, 0.010, 0.013, 0.021, 0.028]  平均 = 0.021

由此可见,有时候走不到自己的全值匹配索引时,可以考虑一下强制索引的写法

   Test2. varchar类型的引号不可省略

select * from srcorder_item where order_no = 1002296352
select * from srcorder_item where order_no = '1002296352'

    Test3. 组合索引,索引要遵守最左匹配原则

explain select * from srcorder_item where create_time = STR_TO_DATE('2015-01-28 16:10:35', '%Y-%m-%d %H:%i:%s') and status='00'

explain select * from srcorder_item force index(srcorder_item_idx_12) where  gc_order_no ='1002296352' and create_time >= '2015-01-01 16:10:35' and create_time < '2015-01-31 16:10:35';

    Test4. 不在索引列上做任何操作[计算,函数,(自动 or 手动)类型转换]会导致索引失效而进行全表扫描

explain select * from srcorder_item  where DATE_FORMAT(update_time, '%Y-%m-%d') = CURDATE()

   Test5. 范围条件放到最后[存储引擎不能使用索引中范围条件右边的列]

explain select * from srcorder_item where create_time > STR_TO_DATE('2015-01-28 16:10:35', '%Y-%m-%d %H:%i:%s') and status='00' 

    Test6. 不等于要慎用[在使用不等于(!= 或者 <>)的时候无法使用索引会导致全表扫描]

explain select * from srcorder_item where create_time != STR_TO_DATE('2015-01-28 16:10:35', '%Y-%m-%d %H:%i:%s') and status='00' 

    Test7. OR 改 UNION 效率高

select * from srcorder_item where order_no = '1002296352' or order_no='1418580809770'
修改后
select * from srcorder_item where order_no = '1002296352'
UNION
select * from srcorder_item where order_no = '1418580809770'

前者5次平均:[0.034,0.036,0.273,0.038,0.037] 平均 = 0.0836

后者5次平均[0.035,0.024,0.010,0.021,0.021] 平均 = 0.0222

    Test8. like百分号写最右边(为了遵守最左匹配原则)

select * from srcorder_item where order_no like '1418580809770%'
非特殊需求,尽量不要用下面的写法(执行计划来看,会走全表扫描)
select * from srcorder_item where order_no like '%1418580809770%'

小结

全值匹配我最爱,最左前缀要遵守

带头大哥不能死,中间兄弟不能断

索引列上少计算,范围之后全失效

LIKE百分写最右,覆盖索引不写*[意思是索引(a,b),你查询的时候 select a,b from t where a=? and b=?,索引直接有值,避免回表]

不等空值还有OR,索引影响要注意

VAR引号不能丢,SQL优化有诀窍

重要重要重要(引用某大牛说的): 数据库的优化,远远比不上应用层面上的优化,有可能是设计的问题,也值得考虑,而不是直指SQL

例外

查询是否用到索引,有时候并不是我们建立了索引,就一定会走索引,搜索引擎还会做优化

1.例如走全表扫描比索引快时,执行计划中就走不到我们建立的索引

2.通过索引扫描的记录超过20%~30%,可能会变成全表扫描,常见于数据量大,根据时间范围查询的时候