sql int转varchar_数据库优化之 - 提高SQL运行效率优化

最新推荐文章于 2021-01-25 04:02:11 发布
最新推荐文章于 2021-01-25 04:02:11 发布
阅读量215 收藏
点赞数
文章标签： sql int转varchar sql int转换为varchar sql varchar转int类型 sql 按小时备份的文件都很大 sql不等于效率优化 sql判断时间差值

a578a45959148276c4020ac5cee98d1c.png

44a198531f039ac2d9798c6a1caeba02.gif

本文从SQL角度进行数据库优化，提升SQL运行效率。

判断问题SQL

判断SQL是否有问题时可以通过两个表象进行判断：

  • 系统级别表象
    • CPU消耗严重
    • IO等待严重
    • 页面响应时间过长
    • 应用的日志出现超时等错误

可以使用sar命令，top命令查看当前系统状态。

6433b70ee6be8c9e2cd35f07813bf9c1.png

也可以通过Prometheus、Grafana等监控工具观察系统状态。（感兴趣的可以翻看我之前的文章）

e9b0de235dbaf2893bf080640dd0cbfb.png
  • SQL语句表象
    • 冗长
    • 执行时间过长
    • 从全表扫描获取数据
    • 执行计划中的rows、cost很大

冗长的SQL都好理解，一段SQL太长阅读性肯定会差，而且出现问题的频率肯定会更高。更进一步判断SQL问题就得从执行计划入手，如下所示：

8480cf7c0a561c87de455b5c2eccc163.png

执行计划告诉我们本次查询走了全表扫描Type=ALL，rows很大(9950400)基本可以判断这是一段"有味道"的SQL。

获取问题SQL

不同数据库有不同的获取方法，以下为目前主流数据库的慢查询SQL获取工具

  • MySQL
  • 慢查询日志
  • 测试工具loadrunner
  • Percona公司的ptquery等工具

Oracle

  • AWR报告
  • 测试工具loadrunner等
  • 相关内部视图如v$sql、v$session_wait等
  • GRID CONTROL监控工具

达梦数据库

  • AWR报告
  • 测试工具loadrunner等
  • 达梦性能监控工具（dem）
  • 相关内部视图如v$sql、v$session_wait等

SQL编写技巧

SQL编写有以下几个通用的技巧：

• 合理使用索引

索引少了查询慢；索引多了占用空间大，执行增删改语句的时候需要动态维护索引，影响性能
选择率高（重复值少）且被where频繁引用需要建立B树索引；一般join列需要建立索引；复杂文档类型查询采用全文索引效率更好；索引的建立要在查询和DML性能之间取得平衡；复合索引创建时要注意基于非前导列查询的情况
  • 使用UNION ALL替代UNION
UNION ALL的执行效率比UNION高，UNION执行时需要排重；UNION需要对数据进行排序

• 避免select * 写法

执行SQL时优化器需要将 * 转成具体的列；每次查询都要回表，不能走覆盖索引。

• JOIN字段建议建立索引

一般JOIN字段都提前加上索引

• 避免复杂SQL语句

提升可阅读性；避免慢查询的概率；可以转换成多个短查询，用业务端处理

• 避免where 1=1写法

• 避免order by rand()类似写法

RAND()导致数据列被多次扫描

SQL优化

执行计划

完成SQL优化一定要先读执行计划，执行计划会告诉你哪些地方效率低，哪里可以需要优化。我们以MYSQL为例，看看执行计划是什么。（每个数据库的执行计划都不一样，需要自行了解）explain sql

783f48bf834e014b1d01eaf65f4c0214.png

2a8244ee064f369ed6273b4ae637dfc3.png

接下来我们用一段实际优化案例来说明SQL优化的过程及优化技巧。

优化案例

  • 表结构
    CREATE TABLE `a`
    (
        `id`          int(11) NOT NULLAUTO_INCREMENT,
        `seller_id`   bigint(20)                                       DEFAULT NULL,
        `seller_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
        `gmt_create`  varchar(30)                                      DEFAULT NULL,
        PRIMARY KEY (`id`)
    );
    CREATE TABLE `b`
    (
        `id`          int(11) NOT NULLAUTO_INCREMENT,
        `seller_name` varchar(100) DEFAULT NULL,
        `user_id`     varchar(50)  DEFAULT NULL,
        `user_name`   varchar(100) DEFAULT NULL,
        `sales`       bigint(20)   DEFAULT NULL,
        `gmt_create`  varchar(30)  DEFAULT NULL,
        PRIMARY KEY (`id`)
    );
    CREATE TABLE `c`
    (
        `id`         int(11) NOT NULLAUTO_INCREMENT,
        `user_id`    varchar(50)  DEFAULT NULL,
        `order_id`   varchar(100) DEFAULT NULL,
        `state`      bigint(20)   DEFAULT NULL,
        `gmt_create` varchar(30)  DEFAULT NULL,
        PRIMARY KEY (`id`)
    );

三张表关联，查询当前用户在当前时间前后10个小时的订单情况，并根据订单创建时间升序排列，具体SQL如下

    select a.seller_id,
           a.seller_name,
           b.user_name,
           c.state
    from a,
         b,
         c
    where a.seller_name = b.seller_name
      and b.user_id = c.user_id
      and c.user_id = 17
      and a.gmt_create
        BETWEEN DATE_ADD(NOW(), INTERVAL – 600 MINUTE)
        AND DATE_ADD(NOW(), INTERVAL 600 MINUTE)
    order by a.gmt_create；复制代码

查看数据量

      ![image](https://user-gold-cdn.xitu.io/2019/11/2/16e2a10eeb735db2?w=763&h=155&f=png&s=9625)

原执行时间

    ![image](https://user-gold-cdn.xitu.io/2019/11/2/16e2a10f86740a83?w=875&h=361&f=png&s=26616)

原执行计划

    ![image](https://user-gold-cdn.xitu.io/2019/11/2/16e2a10fa66c37e7?w=1151&h=391&f=png&s=41284)

初步优化思路

  1. SQL中 where条件字段类型要跟表结构一致，表中user_id 为varchar(50)类型，实际SQL用的int类型，存在隐式转换，也未添加索引。将b和c表user_id 字段改成int类型。
  2. 因存在b表和c表关联，将b和c表user_id创建索引
  3. 因存在a表和b表关联，将a和b表seller_name字段创建索引
  4. 利用复合索引消除临时表和排序

初步优化SQL

    alter table b modify `user_id` int(10) DEFAULT NULL;
    alter table c modify `user_id` int(10) DEFAULT NULL;
    alter table c add index `idx_user_id`(`user_id`);
    alter table b add index `idx_user_id_sell_name`(`user_id`,`seller_name`);
    alter table a add index `idx_sellname_gmt_sellid`(`gmt_create`,`seller_name`,`seller_id`);复制代码
  • 查看优化后执行时间
![image](https://user-gold-cdn.xitu.io/2019/11/2/16e2a10fc97978e7?w=679&h=362&f=png&s=24798)
  • 查看优化后执行计划

2eb6d2e20c3e2d9b5caf6618ec55a673.png
  • 查看warnings信息

71f3e199d757d68e9ed961422b299424.png

继续优化

alter table a modify "gmt_create" datetime DEFAULT NULL;

查看执行时间

    ![image](https://user-gold-cdn.xitu.io/2019/11/2/16e2a110294bd394?w=631&h=360&f=png&s=24634)

查看执行计划

    ![image](https://user-gold-cdn.xitu.io/2019/11/2/16e2a1104a9fe5b8?w=1143&h=496&f=png&s=41402)

优化总结

  1. 查看执行计划 explain
  2. 如果有告警信息，查看告警信息 show warnings;
  3. 查看SQL涉及的表结构和索引信息
  4. 根据执行计划，思考可能的优化点
  5. 按照可能的优化点执行表结构变更、增加索引、SQL改写等操作
  6. 查看优化后的执行时间和执行计划
  7. 如果优化效果不明显，重复第四步操作

参考原文链接https://blog.csdn.net/xiyue001/article/details/102872532

weixin_39623671
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL数据库中把int转化varchar引发的慢查询
12-15
最近一周接连处理了2个由于int向varchar转换无法使用索引，从而引发的慢查询。 CREATE TABLE `appstat_day_prototype_201305` ( `day_key` date NOT NULL DEFAULT '1900-01-01', `appkey` varchar(20) NOT NULL DEFAULT '', `user_total` bigint(20) NOT NULL DEFAULT '0', `user_activity` bigint(20) NOT NULL DEFAULT '0', `times_total` bigint(20) N
sql int 转换varchar
cipu7364的博客
11-08 393
select right('00000000' convert(varchar(8) , fields) , 8)[@more@] ...
sql语句中int转varchar
weixin_34261739的博客
11-29 1903
SELECT parent_id_list+','+cast(id AS varchar(10)) FROM mytable 或者cast也可以用convert来代替 同时看到有网友有这样的需求，它想把1转成varchar的”000001″这样的串，但是cast函数是只会转成”1″，所以要借用right函数select right(’00000000′ CAST(@i as varchar),...
sql int转换为varchar_Excel 表转换为MySQL的SQL 文件
weixin_39992788的博客
12-09 161
在很多情形下，我们需要将Excel表格数据，转换为sql文件，以便插入数据库表，以MySQL为范例，可以通过如下方式进行，Oracle 等数据库相同。例如，具有Excel表格数据如下：idareapid100000China0110000Beijing100000可以在数据库中创建数据表如下：create table emap(id int primary key,area varcha...
MySQL int转换成varchar引发的慢查询
零越
05-30 782
CREATE TABLE `appstat_day_prototype_201305` ( `day_key` date NOT NULL DEFAULT '1900-01-01', `appkey` varchar(20) NOT NULL DEFAULT '', `user_total` bigint(20) NOT NULL DEFAULT '0', `use...
数据库_性能优化篇-2(1).docx
04-29
- 存储空间最小化：尽可能使用最小的数据类型，如能用int就不用varchar，能用varchar(16)就不用varchar(256)，节省存储空间，提高查询效率。 3. **索引规范**： - 简洁明了的索引命名：如user_name_index，便于...
Transact-SQL数据库基本操作
12-14
在SQL语言中，Transact-SQL是微软SQL Server所使用的扩展语法，用于处理数据库的各种操作。本篇文章将详细讲解如何使用Transact-SQL进行数据库、数据表以及数据记录的基本操作。 1. **创建、修改、删除数据库** ...
数据库技术复习题1---选择题.zip_300_sql_sql 习题_sql选择练习题_数据库选择题
09-23
2. **数据类型与运算符**：了解和掌握SQL中的不同数据类型（如INT、VARCHAR、DATE等）及其相互之间的运算规则，以及比较、算术和逻辑运算符的用法。 3. **表的创建与修改**：题目可能会测试你对CREATE TABLE、ALTER...
sqlserver中将varchar类型转换为int型再进行排序的方法
12-15
在SQL Server中，数据类型的转换是一项常见...总的来说，正确地将varchar转换为int进行排序是保证数据正确性和查询效率的关键步骤，而理解并灵活运用各种排序方法和技巧，对于提升SQL查询性能和满足业务需求至关重要。
SQL.rar_sql_数据库 总结
09-20
2. 数据类型：SQL支持多种数据类型，如整型（INT）、浮点型（FLOAT）、字符串（VARCHAR）、日期/时间（DATE/TIME）和布尔型（BOOLEAN）等。 3. DDL（Data Definition Language）：用于创建和修改数据库结构，如...
select 字符varchar转数据类型int_「SQL优化」varchar字符串按照int整型排序的实现方案及性能对比...
weixin_35572076的博客
01-25 540
「SQL小贴士」Mysql将varchar字符串按照int整型排序的实现方案和性能对比前言在设计表的时候将用户的“会员卡号”创建为varchar类型，实际上用户的卡号是有规则的数字类型。这样的设计在使用过程中并没有出现问题，但是最近要根据“会员开好”进行排序的时候发现排序的结果有问题，具体信息如下：SQL示例：select * from user order by member_no desc l...
sql int转换为varchar_常见SQL优化实践
weixin_39737757的博客
12-04 3222
(1)负向条件查询不能使用索引select * from user where status!=0 ，not in/not exists都不是好习惯可以优化为in查询：select * from user where status in(1,2)数据准备：CREATE TABLE `user` ( `id` bigint(20) NOT NULL, `name` varchar(32) DEFAU...
mysql char_对比MySQL中int、char以及varchar的性能
weixin_42117485的博客
01-18 482
网络上有许多似是而非的“谣言”，当然都不是恶意，绝大部分都是开发者不愿意自己主动研究，反而轻信其他人的信口之言。关于数据库的谣言也有不少，比如“int性能比char高很多”。我最近针对int、long、char、varchar进行了一次性能测试，发现它们其实并没有太大的性能差距：备注：c8=char(8), s8=varchar(8), i8=(bigint), c4=char(4), s4=va...
sql int转varchar_SQL面试36
weixin_39606244的博客
12-01 158
有两个表的表结构如下：表class（班级）：字段类型备注SidInt主键NameVarchar班级名称表student_old（学生表，旧表）：字段类型备注SidInt主键NameVarchar学生姓名SexInt性别 1：男 2：女AgeInt年龄Class_IDInt所属班级ID表student_new（学生表，新表）：字段类型备注SidInt主键NameVarchar学生姓名Sex_na...
char、varchar 哪种的搜索效率高
weixin_30940783的博客
06-04 745
在MySQL 中char 和 varchar 都是存储字符串的，区别在于char有固定的长度，而varchar属于可变长的字符类型。char(M)类型的数据列里，每个值都占用M个字节，如果某个长度小于M，mysql就会在它的右边用空格字符补足．（在检索操作中那些填补出来的空格字符将被去掉）在varchar(M)类型的数据列里，每个值只占用刚好够用的字节再加上一个用来记录其长度的字节（即总...
sqlserver int转varchar_SQL server 常用数据类型讲解
weixin_39831705的博客
12-08 2684
(1)整数数据类型整数数据类型是SQL Server 2014 中最常用的数据类型之一，主要用于存储整数值，如存放“年龄”、“工龄”等信息，数值型的数据可以直接进行运算处理。具体来说：SQL Server 2014的整数类型包含如下4种：①Int (integer)Int(或integer)的存储容量为4个字节，其中一个二进制位表示正负符号，一个字节8位。根据字节...
Mysql 数字类型转换函数
iteye_11864的博客
10-26 160
1、将Int 转为varchar经常用 concat函数，比如concat(8,'0') 得到字符串 '80' 2、将varchar 转为Int 用 cast(a as signed) a为varchar类型的字符串 总结：类型转换和SQL Server一样,就是类型参数有点点不同 : CAST(xxx AS 类型) , CONVERT(xxx,类型) 可用的类型　 二进制,同带binar...
msyql varchar转数字
wutao8818的专栏
10-13 967
1+""好像文档里面写在一个角落，实在是难为大家了，今天终于知道了，只要加一个数字，这个字符就转为数字了
SQL Server数据类型转换方法和性能比较
culuo4781的博客
07-17 1522
When you define SQL Server database tables, local variables, expressions or parameters, you should specify what kind of data will be stored in those objects, such as text data, numbers, money or d...
sqlserver数据库把int类型转换为varchar类型
最新发布
09-06
在SQL Server中，将int类型转换为varchar类型可以使用内置的转换函数CAST或CONVERT。 使用CAST函数，可以按照以下格式进行转换： ``` SELECT CAST(int_column AS varchar(10)) AS varchar_column FROM your_table; ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值