Home Tags Posts tagged with "SQL"

SQL

0 54

MySQL 数据类型选择

MySQL 中定义数据字段的类型对你数据库的优化是非常重要的。

MySQL 支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型。


数值类型

MySQL 支持所有标准 SQL 数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC),以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)。

关键字INT是INTEGER的同义词,关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值,并且支持 MyISAM、MEMORY、InnoDB 和 BDB表。

作为 SQL 标准的扩展,MySQL 也支持整数类型 TINYINT、MEDIUMINT 和 BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

img

无论在什么数据库中,数值和字符串这两种数据字段类型都用的最多,并且往往量级非常大,在这种背景下,选择的字段类型的优劣对性能影响非常大,因此我们有必要熟知上表中各种类型的使用及差别。

一、TINYINT

  1. 表示范围 :(-128,127) or (0,255)

  2. 占用字节:1字节

  3. 【强制】表达是与否概念的字段,必须使用is_xxx的方式命名,数据类型是unsigned tinyint

  4. 最小显示长度(TINYINT(1) 和 TINYINT(2)的区别)

    TINYINT(m)

 m表示的是显示数据宽度,不同中数据类型它的数据宽度也是有差别的。数据宽度和数据类型的取值范围彼此之间是相互独立的,没有任何联系。 bigint(m)默认宽度为20,如果创建时表中设置了zerofill关键字(默认是用0填充的)。zerofill含义是:往表中插入的数值比定义的长度小的时候,会在数 值前进行补值。 img

  1. TINYINT和ENUM的区别

  TINYINT(1)或ENUM(’真’,’假’)?
        用ENUM枚举当存储只有2个值时只占用一个位的宽度,0或1,但会花更多的时间去寻找枚举查询的开始。
        用TINYINT(1)默认就会占用4个位的宽度(0000)
        得出结论:比如要存储一个介于0-9之间的值,为了查询获取这个值,建议用TINYINT(1)会更快,但如果你是为了大量记录枚举(“真”,“假”),那么用ENUM( ‘true’ , ‘false’) 搜索会更快。一般的,咱们如果存的是纯数字的话,建议用tinyint,如果是字符串,且是固定长度的,建议用char,而enum的枚举字段,使用的使用需要慎重考虑,避免带来不必要的麻烦

       注:TINYINT(1)在使用Mybatis代码生成器时,会自动转为Boolean类型!!!

二、INT或INTEGER

  1. 表示范围 :(-2 147 483 648,2 147 483 647) or (0,4 294 967 295)

  2. 占用字节:4字节

  3. The keyword INT is a synonym for INTEGER

  4. 如果不需要存取负值,最好加上unsigned

三、BIGINT

1.Mysql里有个数据类型bigint在java转换成实体对象时,处理不当容易出现以下异常:

java.lang.ClassCastException: java.lang.Long cannot be cast to java.math.BigInteger

只需要注意以下情况,就可避免此类异常:当数据库中该属性添加unsigned,则在对象中对应的属性类型应该为BigInteger; ​ 当数据库中该属性未添加unsigned,则在对象中对应的属性类型应该为Long。可以成功映射为Long的表用的是BIGINT(20),但是出问题的表使用的是BIGINT(20) UNSIGNED。如果不是无符号类型,BIGINT(20)的取值范围为-9223372036854775808~9223372036854775807。与Java.lang.Long的取值范围完全一致,mybatis会将其映射为Long;而BIGINT(20) UNSIGNED的取值范围是0 ~ 18446744073709551615,其中一半的数据超出了Long的取值范围,Mybatis将其映射为BigInteger。mysql数据库字段bigint使用 – 一心二念 – 博客园 (cnblogs.com)

2.存储手机号码用bigint(11)还是varchar(11)

空间:BIGINT——8字节CHAR——11字节
效率:BIGINT效率更高
使用:32位某些应用中bigint太大会溢出,要进行各种转换,麻烦
结论:使用char(11)更好

3.用 BIGINT 做主键,而不是 INT;

在真实业务场景中,整型类型最常见的就是在业务中用来表示某件物品的数量。例如上述表的销售数量,或电商中的库存数量、购买次数等。在业务中,整型类型的另一个常见且重要的使用用法是作为表的主键,即用来唯一标识一行数据。

整型结合属性 auto_increment,可以实现自增功能,但在表结构设计时用自增做主键,希望你特别要注意以下两点,若不注意,可能会对业务造成灾难性的打击:

用 BIGINT 做主键,而不是 INT;自增值并不持久化,可能会有回溯现象(MySQL 8.0 版本前)。 从表 1 可以发现,INT 的范围最大在 42 亿的级别,在真实的互联网业务场景的应用中,很容易达到最大值。例如一些流水表、日志表,每天 1000W 数据量,420 天后,INT 类型的上限即可达到。可以看到,当达到 INT 上限后,再次进行自增插入时,会报重复错误,MySQL 数据库并不会自动将其重置为 1。

第二个特别要注意的问题是,MySQL 8.0 版本前,自增不持久化,自增值可能会存在回溯问题!(23条消息) MySQL-数字类型自增是真的坑_Five在努力的博客-CSDN博客

其实,在海量互联网架构设计过程中,为了之后更好的分布式架构扩展性,不建议使用整型类型做主键,更为推荐的是字符串类型。合适的字符存储长度,不但节约数据库表空间、节约索引存储,更重要的是提升检索速度。

正例:人的年龄用unsigned tinyint(表示范围0-255,人的寿命不会超过255岁);海龟就必须是smallint,但如果是太阳的年龄,就必须是int;如果是所有恒星的年龄都加起来,那么就必须使用bigint。

四、FLOAT,DOUBLE,DECIMAL

  1. 表示范围 :较大

  2. 占用字节:4字节,8字节

  3. MySQL 中使用浮点数和定点数来表示小数。

浮点类型有两种,分别是单精度浮点数FLOAT)和双精度浮点数DOUBLE);定点类型只有一种,就是 DECIMAL。浮点类型和定点类型都可以用(M, D)来表示,其中M称为精度,表示总共的位数;D称为标度,表示小数的位数。浮点数类型的取值范围为 M(1~255)和 D(1~30,且不能大于 M-2),分别表示显示宽度和小数位数。M 和 D 在 FLOAT 和DOUBLE 中是可选的,FLOAT 和 DOUBLE 类型将被保存为硬件所支持的最大精度。DECIMAL 的默认 D 值为 0、M 值为 10。

  1. DOUBLE 实际上是以字符串的形式存放的(精准)

  2. 长度一定下,浮点数表示范围更大,缺点则是精度问题

在 MySQL 中,定点数以字符串形式存储,在对精度要求比较高的时候(如货币、科学数据),使用 DECIMAL 的类型比较好,另外两个浮点数进行减法和比较运算时也容易出问题,所以在使用浮点数时需要注意,并尽量避免做浮点数比较。

  1. 单精度float比双精度效率更高

  2. 浮点数判断相等不能用== 或者equals,应当使用

  3. 【强制】小数类型为decimal,禁止使用float和double。

说明:float和double在存储的时候,存在精度损失的问题,很可能在值的比较时,得到不正确的结果。如果存储的数据范围超过decimal的范围,建议将数据拆成整数和小数分开存储。


日期类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个”零”值,当指定不合法的MySQL不能表示的值时使用”零”值。

TIMESTAMP类型有专有的自动更新特性,将在后面描述。

img


字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。img

一、CHAR和VARCHAR

CHAR数据类型是MySQL中的固定长度的字符类型。我们经常声明CHAR类型的长度,指定我们要存储的最大字符数。例如,CHAR(20)最多可以容纳20个字符。

如果要存储的数据是固定大小,则应使用CHAR数据类型。与VARCHAR这种情况相比,您将获得更好的性能。

CHAR数据类型的长度可以是0到255之间的任何值。当您存储CHAR值时,MySQL会将其值用空格填充到您声明的长度。

当您查询CHAR值时,MySQL将删除尾随空格。

请注意,如果启用PAD_CHAR_TO_FULL_LENGTH SQL模式,MySQL将不会删除尾随空格。

区别:

  1. CHAR的长度是不可变的,而VARCHAR的长度是可变的

  2. CHAR的存取速度要比VARCHAR快得多

  3. 存储方式不同

varchar比char节省空间,但在效率上比char稍微差一些。varchar比char节省空间,是因为varchar是可变字符串,比如:用varchar(5)存储字符串“abc”,只占用3个字节的存储空间,而用char(5)存储,则占用5个字节(“abc ”)。varchar比char效率稍差,是因为,当修改varchar数据时,可能因为数据长度不同,导致数据迁移(即:多余I/O)。其中,oracle对此多余I/O描述的表达是:“行迁移”(Row Migration)。

  1. “行迁移”(Row Migration)

    *“当一行的记录初始插入时是可以存储在一个block中的,由于更新操作导致行增加了,而block的自由空间已经完全满了,这个时候就产生了行迁移。在这种情况下,oracle将会把整行数据迁移到一个新的block中,oracle会保留被迁移的行的原始指针指向新的存放行数据的block,这就意味着被迁移行的ROW ID是不会改变的。"*
    
      其中要解释一下:block是oracle中最小的数据组织与管理单位,是数据文件磁盘储存空间单位,也是**数据库I/O最小单位(****也就是说,读和写都是一个block的大小,所以如果block没满时,更新内容长度变更的varchar字段,和更新内容长度没变的varchar字段,I/O次数是一样,不存在额外消耗,只有在block满时,才会出现额外I/O,所以char和varchar性能之间的性能差异,是相当细微的,绝大多数情况下可以忽略不计,所以上文描述的“稍”差的含义)**。
    
      所以,我的开发经验是:“**用varchar代替char的效率有所下降,但不大**”。
  2. 占用空间不同

CHAR的存储方式是,一个英文字符(ASCII)占用1个字节,一个汉字占用两个字节;而VARCHAR的存储方式是,一个英文字符占用2个字节,一个汉字也占用2个字节。

varchar是可变长字符串,不预先分配存储空间(这里说的是存储时不预先分配存储空间,但是在查询需要创建临时表时,会按照设置的长度预先分配内存空间,因此这个设置长度时不可以随意,详情见下varchar的长度设计),长度不要超过5000,如果存储长度大于此值,定义字段类型为TEXT,独立出来一张表,用主键来对应,避免影响其它字段索引效率。

     6.VARCHAR类型的实际长度是它的值的实际长度+1。为什么”+1″呢?

这一个字节用于保存实际使用了多大的长度。


二、MySQL中的varchar长度设计

 之前看到一篇博文,讲的是在定义varchar(n)时,需要用多大就设置多大,而我当时在分析时,对数据量估算错误了,设置了varchar(5000),被leader和导师批评了一番,因此特意学习varchar的长度设计:
尽管varchar(n)是存储可变长字符串的,但是其n也不是越大越好,理论如下
当MySql在查询需要创建临时表的时候(union,order by、group by,子查询),在MySql读取数据之前,是只知道varchar的长度n,不知道实际数据的长度的,但是读取数据之前需要预分配内存空间,MySql是根据varchar(n)中的n来进行分配内存的,这样也是最合理的方式,不可能分配小于n个字符的空间,因此针对varchar(1000)设置就会预先分配1000个字符空间,很显然这个是十分不靠谱的设计。

三、varchar(50)和varchar(255)有性能上的差别么?

对于INNODB,varchar(50)varchar(255)这两者在存放方式上完全一样:1-2 byte保存长度,实际的字符串存放在另外的位置,每个字符1 byte到4 byte不定(视编码和实际存储的字符而定)。所以将一个字段从varchar(50)长度改成varchar(100)长度不会导致表的重建。但如果把长度从varchar(50)改成varchar(256)就不一样了,表示长度会需要用到2 byte或更多。既然255长度以下对INNODB都一样,而且我们平时基本上也不太会使用到MYISAM,那么是不是为了省心,我们就可以把255长度以下的字段的类型都设置成varchar(255)了呢?
非也。
因为内存表(临时表)介意。
虽然我们不会明文创建内存表,但所有的中间结果都会被数据库引擎存放在内存表(MySQL在有些查询情况下需要创建内存表)。我们可以通过EXPLAIN或者SHOW STATUS可以查看MYSQL是否使用了内存表用来帮助完成某个操作。
而内存表会按照定义的varchar(n)的n来分配内存。以utf-8编码为例,对于varchar(255),每一行所占用的内存就是长度的2 byte + 3 * 255 byte。对于100条数据,光一个varchar字段就占约1GB内存。如果我们该用varchar(50),就可以剩下来约80%的内存空间。
除此之外,255长度也可能会对索引造成坑。MySQL在5.6版本及之前的最大长度是767 byte。但MySQL 5.5版本后开始支持4个byte的字符集utf8mb4(沙雕表情用到的字符太多,长度不够用)。255 * 4 > 767,所以索引就放不下varchar(255)长度的字段了。虽然MySQL在5.7版本后将限制改成了3072 byte,但如果是多字段的联合索引还是有可能会超过这个限制

所以我们的结论就是:在长度够用的情况下,越短越好。

四、varchar(255)和varchar(256)的区别

在varchar长度接近256时,varchar长度设置成255的好处:

1、方便InnoDB建索引,对于 MyISAM,可以对前 1000 个字节做索引,对于 InnoDB,则只有 767 字节。(来源依据)。255X3=765

2、少申请一个字节,记录字符创长度,一个8位的tinyint,可以表示的无符号数值的范围是,0-255,如果长度超过了255,需要在申请个字节

五、varchar(n)长度定义知识总结

1.在长度够用的情况下,越短越好(因为长了会导致查询生成临时表时降低性能且占用内存)。

2.在varchar长度接近256时,varchar长度设置成255更好

六、参考文章

0 68

disinct 关键字

 

至少SQL Server,HQL,Oracle都有效

通常SQL中对表中数据去重,会首先想到 distinct 关键字,

能实现的需求

1. distinct可以对单个字段去重
select distinct name from A


2. 对多个字段去重时,此时所列的字段需要同时满足才会起到去重效果,否则不会去重

select distinct name, id from A       --name和id同时重复才能去重,有一个不一样都不会去重

        --上面的写法是对name和id列都去重,而不是仅对name去重,id不去重

select distinct * from A      --*所代表的所有字段都重复时,才能去重

不能实现的需求

3. 不能实现指定字段去重,其他字段不去重的效果
select name, distinct id from A   --仅希望对id列进行去重,name列不去重,xxx这样是不行的,无法实现 ,而且会提示错误,因为distinct必须放在开头

问题:
如果想实现针对某一字段去重,其他字段是否重复不关心的效果怎么处理?
答:
可以采用row_number()的窗口函数
转换问题,可以转换为对重复的行取top1,这样使用over()函数,对指定列分组,排序,然后结合row_number()给每一组的数据一列序列,再对集合取序列为1的行

结合一些用例更容易懂一些。

0 69

Structured Query Language 结构化查询语言

为啥不叫非结构化查询语言呢?NoSQL

为啥不叫结构化操作语言呢?Operate

为啥不叫结构化查询操作呢?

因为这个是针对结构化数据的,查询

 

那么什么是结构化数据呢?一摞书,一摞笔记本,一捆钢笔。这是半理想状态。

书随意摆放,笔记本随意摆放,钢笔夹在书里,笔记本里,放在笔筒里,这是最不理想的状态

一摞按照书名首字母排序摆放在书架的书,一摞按照科目(语文、英语、化学…)顺序摆放的笔记本,一捆按照品牌顺序摆放的钢笔。这是最理想的状态。显然这样的状态是结构化,有规律,按照一定规则安排、处理的。(最直观的是图书馆及图书管理学)如果不是实际物品,而是抽象的概念,那么这个结构化就是现在的关系型数据库(DataBase)

查询结构化数据的语言就是结构化语言。查询化学这个笔记本,只需要定位到笔记本存放的位置(笔记本统一放置在一起,一个地方,甚至是一摞这种状态),第三个就是了。

而select * from notebook where notebook_name =“化学”就是比较合理的查询语句,翻译过来就是从笔记本中找出名字是化学的那个东西(*不管是什么,都直接拿过来给我就可以)

继而形成了SQL。

作者:LazyYoun
链接:https://www.zhihu.com/question/349924681/answer/851943256

一.背景
在很多业务场景下我们需要去拦截sql,达到不入侵原有代码业务处理一些东西,比如:分页操作,数据权限过滤操作,SQL执行时间性能监控等等,这里我们就可以用到Mybatis的拦截器Interceptor

二.Mybatis核心对象介绍
从MyBatis代码实现的角度来看,MyBatis的主要的核心部件有以下几个:

Configuration 初始化基础配置,比如MyBatis的别名等,一些重要的类型对象,如,插件,映射器,ObjectFactory和typeHandler对象,MyBatis所有的配置信息都维持在Configuration对象之中
SqlSessionFactory  SqlSession工厂
SqlSession 作为MyBatis工作的主要顶层API,表示和数据库交互的会话,完成必要数据库增删改查功能
Executor MyBatis执行器,是MyBatis 调度的核心,负责SQL语句的生成和查询缓存的维护
StatementHandler   封装了JDBC Statement操作,负责对JDBC statement 的操作,如设置参数、将Statement结果集转换成List集合。
ParameterHandler   负责对用户传递的参数转换成JDBC Statement 所需要的参数,
ResultSetHandler    负责将JDBC返回的ResultSet结果集对象转换成List类型的集合;
TypeHandler          负责java数据类型和jdbc数据类型之间的映射和转换
MappedStatement   MappedStatement维护了一条<select|update|delete|insert>节点的封装,
SqlSource            负责根据用户传递的parameterObject,动态地生成SQL语句,将信息封装到BoundSql对象中,并返回
BoundSql 表示动态生成的SQL语句以及相应的参数信息

 

Mybatis拦截器只能拦截四类对象,分别为:Executor、ParameterHandler、StatementHandler、ResultSetHandler,而SQL数据库的操作都是从Executor开始,因此要记录Mybatis数据库操作的耗时,需要拦截Executor类,代码实现如下:

复制代码
/**
 * 数据库操作性能拦截器,记录耗时
 * @Intercepts定义Signature数组,因此可以拦截多个,但是只能拦截类型为:
 *         Executor
 *         ParameterHandler
 *         StatementHandler
 *         ResultSetHandler
 * */
@Intercepts(value = { 
        @Signature (type=Executor.class,
                method="update",
                args={MappedStatement.class,Object.class}),
        @Signature(type=Executor.class,
        method="query",
        args={MappedStatement.class,Object.class,RowBounds.class,ResultHandler.class,
                CacheKey.class,BoundSql.class}),
        @Signature(type=Executor.class,
        method="query",
        args={MappedStatement.class,Object.class,RowBounds.class,ResultHandler.class})})
public class TimerInterceptor implements Interceptor {

    private static final Logger logger = Logger.getLogger(TimerInterceptor.class);
    
    /**
     * 实现拦截的地方
     * */
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        Object target = invocation.getTarget();
        Object result = null;
        if (target instanceof Executor) {
            long start = System.currentTimeMillis();
            Method method = invocation.getMethod();
            /**执行方法*/
            result = invocation.proceed();
            long end = System.currentTimeMillis();
            logger.info("[TimerInterceptor] execute [" + method.getName() + "] cost [" + (end - start) + "] ms");
        }
        return result;
    }

    /**
     * Plugin.wrap生成拦截代理对象
     * */
    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    @Override
    public void setProperties(Properties properties) {

    }

}
复制代码

完成上面的拦截后,需要将该类在Mybatis配置文件中声明,如下:

<plugins><!-- SQL性能拦截器 --><plugin interceptor="com.quar.interceptor.TimerInterceptor" /></plugins>