MySql实战-SQL优化（一）

最新推荐文章于 2023-05-02 15:55:27 发布

小琦佩猪

最新推荐文章于 2023-05-02 15:55:27 发布

阅读量419

点赞数

分类专栏： MySql 文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/weixin_44616096/article/details/128064873

版权

MySql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

文章目录

前言
建表规范
- 总结
SQL优化

前言

本章主要讲讲SQL的优化，小编也是来源于公司生产环境实战的优化，不过看本章之前，建议先了解InnerDB索引的数据结构和原理，可以看看前两篇文章，或者可能小编写的不太细节，也可以自行找些大佬的文章进行了解。本章主要针对数据量大的时候对sql的相关优化，以及相关的处理方式，包括在实际工作中我们究竟怎么优化我们的DDL，DQL语句。

建表规范

CREATE TABLE `v_loan_order_history` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(100) DEFAULT ' ',
  `agent_id` bigint(20) DEFAULT '0',
  `merchant_id` bigint(20) DEFAULT '0',
  `province_code` varchar(100) DEFAULT ' ',
  `province_name` varchar(100) DEFAULT ' ',
  `city_code` varchar(100) DEFAULT ' ',
  `city_name` varchar(100) DEFAULT ' ',
  `order_no` bigint(20) DEFAULT '0',
  `wb_id` bigint(20) DEFAULT '0',
  `main_order_id` bigint(20) DEFAULT '0',
  `is_transfer` tinyint(4) DEFAULT '0',
  `release_loan_type` varchar(100) DEFAULT ' ',
  `merchant_channel` varchar(100) DEFAULT ' ',
  `finance_id` int(11) DEFAULT '0',
  `finance_name` varchar(100) DEFAULT ' ',
  `release_loan_date` varchar(100) DEFAULT ' ',
  `confirm_amount` decimal(18,2) DEFAULT NULL,
  `order_status` int(11) DEFAULT '0',
  `create_time` varchar(100) DEFAULT ' ',
  `loan_rate` decimal(18,4) DEFAULT NULL,
  `fund_code` varchar(100) DEFAULT ' ',
  `car_brand_model` varchar(100) DEFAULT ' ',
  `vin_code_apply` varchar(100) DEFAULT ' ',
  `sync_is_valid` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否有效。0：有效。1:无效',
  `sync_create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `sync_update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `extend_order_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '扩展订单id',
  `batch_no` varchar(10) DEFAULT NULL COMMENT '主订单和扩展订单序号',
  `develop_mode` varchar(10) DEFAULT NULL COMMENT '展业模式',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1045222 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

这是一张实际业务中的表结构的部分字段，可以看到此时已经有了100多万条的数据了，并且每天都在增长，大家不用关注表中的字段，这里之所以用这么多字段是为了后续方便测试。先看表结构首先是没有备注，然后就是对于varchar这些动态字符串的长度给的太大，浪费内存，还有一点就是除了主键其他字段都是可以为NULL值(不排除后加的字段)。
这里主要说说NULL值的影响，看过我第一章关于每一行记录数据格式的知道，mysql把NULL值列和其他非NULL值分开存储，需要花费额外的空间来专门存储NULL值，NULL越多开销越大，同样的NULL在查询的时候也费尽，必须使用 is NULL 或者 is not Null或者ifnull函数来判断，有时候甚至存入空串，这样前两个sql就查不出来，有时候还会进行范围查找>=，<=等语句，就会严重影响性能。所以结合小编建议DDL应该遵循以下几点。

总结

1. 建立自增主键(聚簇索引)，并且使用bigint类型
2. 使用删除字段标记记录是否删除，禁止物理删除(防止数据碎片化)
3. 对新建表的字段除了date,datetime属性或者特殊场景的字段，其他字段应该都是NOT NULL DEFAULT语句
4. 所有字段都应该有备注（同样类型字段代码应该有枚举，并且在数据表实体字段上标记枚举位置）
5. 对于基本类型字段无需指定长度(指定也没用，定长)，对于变长字段结合实际业务给定长度
6. 对于索引应该以idx开头，例如idx_字段名_字段名，唯一索引使用uniq_字段名称进行命名

SQL优化

在这里插入图片描述

可以看到刚刚那个数据表有将近90w条数据，这在实际生成环境中很常见，我们先开执行一下全表扫描需要多长时间。
在这里插入图片描述
一次全表查询将近6秒这在实际业务中肯定是不允许的,所以开发中应当避免全表扫描。接下来我们建一个二级索引。

大家可以看到我们给一个字段建立索引耗时了将近4.7秒，这是因为表的数据比较大，创建索引就意味着先对字段记录进行查询，然后生成对应的B+树索引，所以一开始我们就应该预估表的大小，并且建立适当的索引。

建立索引就一定快吗？

我们此时通过刚刚建立的索引作为查询条件执行一下下看看：
在这里插入图片描述

可以看到同样都是一个字段作为条件，这两种结果效率天差地别。我们再来看看执行计划。

在这里插入图片描述

我们先来看看两者查询条件的执行计划，可以看到type一个是range，一个是ALL，range证明用到了索引，并且级别不算太低，而后者没有用到索引而是扫描的了全表。有一个有意思的地方就是两者的possible_keys都是一样的，证明mysql一开始都尝试用这个索引去查询数据，但是实际key的结果不一样，第二条语句实际上没有走索引，大家再关注一下rows这个字段，前者是1后者是80多万，rows代表查询记录数预估值，这也证明后者确实扫了全表。那么出现这两个的根本原因是什么。大家可以看到一个是>1000，一个是<1000，两者扫描范围不一样，虽然理论上一个是（负无穷,1000），一个是（1000,正无穷）但是由于mysql的索引树是尽量保持有序的，而我们数据中此数据都是大于0的索引实际的扫描区间是（0,1000），这也是为什么二者扫描行数区别这么大从而影响效率。所以扫描区间范围会影响性能。
其实还有一个疑问，为什么扫描区间大了，mysql就不走索引了而是选择全表的方式就行查询，其实关键在于查询的字段，接下来我们减少一下查询字段。
在这里插入图片描述

大家可以看到，我此时只查询id和当前字段发现效率快了一倍，而且索引级别也是range,扫描行数也少了一半。主要是原因在于回表，当前order_no索引树是有主键id这个字段的，所以只需要查询这一个索引树就行了，而我们使用*查询的时候，就需要查到order_no索引树上没有的字段，这时候通过id需要回到聚簇索引(主键索引叶子结点拥有全部列的信息)上面去查,而且是查询一条记录就回表一次，mysql考虑到表的数据太多，回表这么多次还没有直接查全表来的快，关于索引和全表的查询方式我在前两章也介绍过了，感兴趣的可以去看一下。这也是为什么我们会要求查询字段尽量少，并且尽量用主键作为条件来查询。

使用聚簇索引就一定快吗？

上文讲到了查询的时候尽量用主键作为条件，这样就会走聚簇索引，避免回表。但是并不是所有场景都会这样，在一些极端的情况下还不如全表来的快。
在这里插入图片描述

可以看到此时用查询id>10的数据实际耗时居然有10秒，可以对比一下前面不加条件直接查询全表的耗时，居然比那个还耗时很多。而且看了执行计划，级别是range，索引用的是主键索引。
小编查了很多资料也没用具体很详细的理由，小编认为，由于你使用是聚簇索引作为条件，mysql认为走聚簇索引查询就是最快的，所以就必定会去查询聚簇索引树，而通过B+树的结构可以看出，他是需要先进行多叉树的查询，然后再到叶子节点的双向链表进行二分法查询。但由于此时查询范围过大。这个是非常耗时的，而对于全表扫描的方式，他的数据结构就是一个双向链表，通过二分法遍历，而此时范围过大的时候，全表的查询效率是要优于于B+树的，所以此时全表查询效率会快。如果有大佬知道具体原因麻烦告知一下。

使用组合索引

在这里插入图片描述
我们在实际业务中也会用到组合索引，那么组合索引的

我们先建立order_no,user_name两个字段的组合索引，再来看上面两条sql语句，后者要慢与前者，通过前面的分析可以知道由于agent_id这个字段并没有建立在order_no,user_name两个字段的组合索引上所以需要进行回表，由于扫描范围过大，此时mysql会选择查询全表的方式，通过二者执行计划也看出，前者使用了索引而且级别是rang而后者是ALL，但是虽然走的是全表，但是查询的字段很少，所以效率也要优于*很多。所以选择合适的组合索引和查询字段也会优化查询效率。

总结

1.尽量用主键索引作为条件查询(聚簇索引叶子结点拥有所有列的记录)，但是应该避免大范围的主键查询
2.避免大范围的查询
3.查询的列字段在符合业务的情况下尽量少
4.提前规划表的大小和查询条件使用率多的字段，建立适当索引
5.对自己写的sql应该观察其执行计划和耗时，找出最优解