精华内容
下载资源
问答
  • 1 使用前缀索引,定义好长度,可以做到既节省空间,又不用额外增加太多的查询成本。 2 如何确定多长的长度,才叫合适? 我们在建立索引时,关注的这个索引上的...4 为字符串字段添加索引的其他方式 倒序存储和使用h...

    1 使用前缀索引,定义好长度,可以做到既节省空间,又不用额外增加太多的查询成本。
    2 如何确定多长的长度,才叫合适?
    我们在建立索引时,关注的这个索引上的区分度。区分度越高,意味着重复的键值越少。因此,我们可以通过统计索引上有多少个不同的值来判断要使用多长的前缀
    3 前缀索引对覆盖索引的影响
    使用前缀索引,就用不上覆盖索引对查询呢性能的优化了。
    4 为字符串字段添加索引的其他方式
    倒序存储和使用hash字段。两者的异同点如下:
    同:都不支持范围查询。
    异:
    首先,从占用额外空间上看,倒序存储方式在主键索引上,不会消耗额外的存储空间,而hash字段方法需要增加一个字段。
    其次,在CPU消耗方面,倒序方式每次写和读的时候,都需要额外调用一次reverse函数,而hash字段的方式需要额外调用一次src32()函数。
    最后,从查询效率上看,使用hash字段方式的查询性能相对稳定一些。

    展开全文
  • 如何给字符串字段索引

    千次阅读 2019-10-31 17:15:16
    字段索引 alter table user add index index1(email); 不建议用这种方式,因为索引树需要存储字段的全部值造成不必要的空间浪费 使用前缀索引 alter table user add index index1(email(6)); 这种方式可以节约...

    如果有这样一个场景,某个字段保存的是邮箱,然后需要给这个字段建立索引,有如下几种解决方案:

    全字段索引

    alter table user add index index1(email);
    

    不建议用这种方式,因为索引树需要存储字段的全部值造成不必要的空间浪费

    使用前缀索引

    alter table user add index index1(email(6));
    

    这种方式可以节约索引的存储空间,但是如何合理的设置前缀的长度呢?
    (1)可以先计算email这个字段上有多少行不重复的行数

    select count(distinct email) as email_num from user;
    

    (2)然后依次选取不同长度的前缀来看这个值,再做适当的设计

    select count(distinct left(email,4)) as email_num4, 
            count(distinct left(email,5)) as email_num5,
            ...
    

    使用前缀索引的最大的一个缺点是一定会回表,如果设置的前缀长度小于字段实际长度,肯定是需要回表做精确比较的,但是即使把前缀的长度设置为字段的实际大小,也就是辅助索引已经存储了该字段的全部值,但是也会进行一次回表比对,因为系统并不确定前缀的长度是否包含了完整信息。也就是说,使用前缀索引就无法使用覆盖索引。

    对于邮箱这样的前缀在一定长度上重复率不大的情况下,使用前缀索引是不错的选择,但是如果有这样一个需求,需要对身份证号码做索引,而且这个表存储的只是一个市的居民身份证号码,身份证号码规则是这样的:一个18位,前6位的地址码,所以同一个地区的人的身份证号码前6位是一样的,这种情况下前缀索引就不好用了,下面给出解决方案。

    字符串逆序之后再使用前缀索引

    这种方式很适合上面提到的一个市的身份证号码字段,还有一种情况是该字段时一个时间戳,那前几个数字相似度很高等等。

    使用hash字段

    在数据库表上再增加一个字段存储需要建立的索引字段的hash值,对应的hash值可以用数据库(MySQL)的crc32()或者crc64()函数来计算,这种方式会有不同的字段值对应的hash值相同,因此每次查询的时候where后面加上索引比较之后还要进行精确值的比较:

    select  field from t where id_card_crc=crc32('input_id_card') and id_card=id_input_id_card
    

    总结

    (1)从占用额外的空间上看,采用哈希值字段索引会占用一个额外的字段空间,但是如果前缀的长度比较长的话,这个差距就可以忽略不计。
    (2)从cpu消耗上看,使用哈希索引需要调用crc32()函数。
    (3)从查询效率上看,使用hash值字段索引性能更稳定,因为crc64()结合了md5算法将冲突的概率降到了很低,因此平均扫描次数接近1。使用前缀索引会增加扫描次数。
    (4)无论使用哪种凡是,都无法进行覆盖索引,也无法使用范围查找

    展开全文
  • 字符串类型的字段在各关系数据库中均占有重要地位。比如Oracle数据库中用于存储字符串类型数据的字段类型就超过了5种。遗憾的是,在日常工作中笔者发现很多开发者对这些类型并没有完整的认识,更不用说设计表结构时...

    本文来自李明子csdn博客(http://blog.csdn.net/free1985),商业转载请联系博主获得授权,非商业转载请注明出处!

    1 引子

    字符串类型的字段在各关系数据库中均占有重要地位。比如Oracle数据库中用于存储字符串类型数据的字段类型就超过了5种。遗憾的是,在日常工作中笔者发现很多开发者对这些类型并没有完整的认识,更不用说设计表结构时正确的选择字段类型了。本文将辨析Oracle数据库中表示字符串的各字段类型以及设计表结构时字段类型的选择依据。

    2 字段类型介绍

    Oracle数据库中,用于表示字符串类型的字段类型包括CHAR、VARCHAR2、VARCHAR、NCHAR、NVARCHAR2、CLOB和NCLOB等几种,下面我们来一一介绍这些类型。
    2.1 CHAR
    CHAR类型用于存储定长的字符串,字段长度的取值范围是1到2000字节,默认为1字节。因为CHAR是定长的,所以,当存储的字符串小于设置的字段长度时,Oracle将在字符串尾填充空格占位。比如我们定义了一个字段“TEST_CHAR CHAR(2)”,而插入的记录对应字段值为“a”,则从表中读取的记录的对应字段是填充了一个空格的“a ”。
    对于CHAR类型值的比较,Oracle会在填充空格补齐到设置长度后进行。比如对于上例,当检索条件为“where TEST_CHAR=’a’”时是可以得到记录“a ”的。
    另外,在定义CHAR的长度时可以使用单位名称BYTE或CHAR,默认为BYTE。即“TEST_CHAR CHAR(2 BYTE)”意为长度为2个字节的CHAR,而“TEST_CHAR CHAR(2 CHAR)”意为长度为2个字符的CHAR。
    这里说的“字节”是存储字段信息所占用的物理存储空间,而“字符”则是指存储的信息的最小逻辑单位。当数据库使用单字节字符集时,一个字符的存储空间就是一个字节,而当数据库使用多字节字符集时,一个字符的存储空间可能是1到n个字节。
    2.2 VARCHAR2
    VARCHAR2类型用于存储变长的字符串,字段长度的取值范围是1到4000字节。VARCHAR2是变长的,这意味着实际存储长度与具体的字符串有关。
    对于VARCHAR2类型的比较,Oracle不会预先填充空格,而是直接比较值。比如我们定义一个字段“TEST_VARCHAR2 VARCHAR2(2)”,插入对应字段值为“a ”的记录。当检索条件为“where TEST_ VARCHAR2=’a’”时是无法得到记录“a ”的。
    另外,与CHAR一样,在定义VARCHAR2类型字段的最大长度时可以使用单位名称BYTE或CHAR,默认为BYTE。即“TEST_ VARCHAR2 VARCHAR2(2 BYTE)”意为最大长度为2个字节的VARCHAR2,而“TEST_ VARCHAR2 VARCHAR2 (2 CHAR)”意为长度为2个字符的VARCHAR2。
    2.3 VARCHAR
    在Oracle中,VARCHAR被定义为VARCHAR2的别名,其一切行为、特征与VARCHAR2相同。据传,起初,Oracle预留了VARCHAR字段类型用于与其他数据库兼容。而时至今日,它仍作为VARCHAR2的别名存在,其意义恐怕只是对Oracle数据库自身的向下兼容了。
    2.4 NCHAR
    NCHAR类型用于存储定长的Unicode字符串,其字符集只能是AL16UTF16或UTF8,与数据库安装时指定的字符集相同。有关Oracle数据库字符集的相关知识可参见Oracle官网在线文档Database Globalization Support Guide( https://docs.oracle.com/database/121/NLSPG/applocaledata.htm#NLSPG014)。
    在设置NCHAR类型字段的长度时,不支持设置单位名称,即单位名称均为“字符”。比如“TEST_NCHAR NCHAR(2)”指长度为2个字符的NCHAR类型字段。NCHAR字段的最大长度是2000个字节。这意味着它最多可以存储2000个字符,但这些字符的实际存储空间不能超过2000个字节。
    NCHAR的其他特性与CHAR相同,恕不赘述。
    2.5 NVARCHAR2
    NVARCHAR2类型用于存储变长的Unicode字符串,其特性参见VARCHAR2和NCHAR,恕不赘述。
    2.6 CLOB
    CLOB类型用于存储最大不超过128TB的变长字符串。CLOB支持事务,但不支持跨事务和会话的定位。下面的示例定义了一个叫做CLOB_TEST的CLOB类型的字段:CLOB_TEST CLOB。
    因为CLOB类型的字段在表中记录的是实际信息的指针,因此无法在sql语句中使用比较运算符。如果我们需要查找CLOB_TEST值为“a”的记录可以使用查询条件“where dbms_lob.compare(t.clob_test,’a’)=0”。
    2.7 NCLOB
    NCLOB类型用于存储最大不超过128TB的Unicode变长字符串。NCLOB的其他特性与CLOB相同,恕不赘述。

    3 字段类型选择依据

    既然Oracle数据库提供了多种字段类型用于字符串类型数据的存储,那么我们在设计数据库表结构时应该如何选择呢?笔者认为应该从各字段类型的区别、限制入手,抓住字符串类型数据的关键特性,从而选择出最适合的字段类型。下面将逐一介绍这些关键特性。
    3.1 最大长度
    从存储信息的角度来说,字段支持的字符串长度是信息能否被完整保留的重要条件。CHAR和NCHAR的最大长度是2000字节,VARCHAR2和NVARCHAR2的最大长度是4000字节,CLOB和NCLOB的最大长度是128TB。当我们要选择一个字段类型来存储字符串信息时,应当首先根据业务模型判断字符串的最大值,排除掉无法满足最大存储需求的字段类型。因为CLOB(NCLOB)类型使用时的诸多不便,当CHAR(NCHAR)和VARCHAR2(NVARCHAR)字段类型的最大长度限制可以满足业务需求时,通常不使用CLOB(NCLOB)类型。
    3.2 是否定长
    CHAR(NCHAR)类型与VARCHAR2(NVARCHAR2)类型最大的区别在于是否定长。CHAR(NCHAR)类型是定长的,这意味着它拥有更高的访问效率。但是,当实际存储的信息小于设置的字段长度时,Oracle会用空格来填充,此时会浪费一定的存储空间。虽然对于单条记录这个影响可以忽略不计,但对于海量记录,这个浪费就需要引起足够的重视了。
    VARCHAR2(NVARCHAR2)的情形与CHAR(NCHAR)刚好相反。它通过牺牲访问效率获得了更高的空间利用率。
    3.3 存储内容包含的字符
    NCHAR、NVARCHAR2、NCLOB类型存储Unicode字符串,仅支持AL16UTF16或UTF8字符集。虽然实际工作中很少遇到,但如果要存储的字符超出AL16UTF16和UTF8字符集范围,那就无法使用NCHAR、NVARCHAR2、NCLOB等字段类型了。
    3.4 是否需要建立索引
    如果需要为存储字符串的字段建立索引,那么我们要格外注意其对字段类型的限制。在CLOB和NCLOB类型的字段上是不能建立普通索引的。而在NCLOB类型的字段上是不能建立文本索引(如CONTEXT)的。
    3.5 是否可能出现数据库迁移
    如果可以预见在将来数据库可能发生迁移,那么设计数据库表结构时应该充分考虑字段类型的兼容性。比如,将Oracle中的NCHAR类型迁移到IBM DB2时,就需要通过CCSID子句创建兼容Unicode字符集的CHAR或VARCHAR。

    4 常见场景的字段类型选择

    第3节中从技术角度讨论了存储字符串类型数据时的选择依据,属于设计表结构时的一些指导原则。本节将以工作指导书的形式介绍在工作中经常涉及的几个业务场景中的字段类型选择及原因,帮助读者更深刻的理解各字段类型的差异。
    4.1 记录状态及可枚举值
    在数据库设计中,我们常常需要表示记录状态的字段。比如,用“R”表示记录已发布,“O”表示记录被检出;再比如用“O”表示树节点已展开,用“C”表示树节点已关闭;甚至表示布尔,用“1”表示真,用“0”表示假。
    这类场景存储的字符串具有定长(或最大长度可知)、可枚举、信息通常为英文字母或数字、经常作为检索过滤条件等特点。
    对于这类场景,我们通常使用CHAR类型字段进行存储。
    4.2 GUID、UUID与MD5
    GUID和UUID是我们经常使用的全局唯一标识符。它们具有长度、格式统一,字符可枚举(16进制数字)的特点。相应的,用于校验的MD5、SH-11、CRC等也有类似的特征。
    对于这类字符串,我们通常使用CHAR类型字段来存储。
    4.3 单据号、证件号
    业务系统中经常需要存储符合一定格式的单据号、证件号。它们除了具有定长、字符可枚举的特点外,还是一些信息的编码集合。以身份证为例,18位数字中就包含了户籍地址、出生日期、性别、校验码等信息。因此,它们通常会被用于创建文本索引或函数索引。
    对于单据号、证件号类型的字符串,我们通常使用CHAR类型字段来存储。
    4.4 普通字符串
    对于诸如“产品型号”、“产品规格”等一般业务属性字符串,通常符合变长、长度不超过4000字节的特征。
    因此,我们可以使用VARCHAR2来存储这个类型的字符串。
    4.5 静态页面、大文本
    在一些web项目中,我们会将静态页面文件存储在数据库表中。一些基于诸如FreeMarker等模版引擎的项目也会将模版文件存储于数据库表中。另外,一些类似论坛、博客、新闻功能的应用也存在存储大量字符串的需求。
    对于以上类型的需求,用排除法可知,能够几乎不受长度限制进行大文本存储的类型只有CLOB和NCLOB。当然,依前所述,我更倾向于使用CLOB。

    展开全文
  • 那么,类似邮箱地址这样的字符串,我们应该如何给它创建索引呢?这里简单介绍几种方法。 几种方法 比如我们有一张表user_info,存储了自增主键ID、邮箱地址、对应密码。 这里我们经常会用到的SQL操作是select email,...

    背景

    我们在业务场景中经常会碰到通过某个字符串查询对应记录的情况。比如常见的邮箱登录、或是手机号登录。
    如果不给它创建索引,则MySQL就会进行全局扫描,非常耗时。那么,类似邮箱地址这样的字符串,我们应该如何给它创建索引呢?这里简单介绍几种方法。

    几种方法

    比如我们有一张表user_info用于存储用户登录信息,包括自增主键ID、邮箱地址、对应密码。
    这里我们经常会用到的SQL操作是select email, password from user_info where email="XXXXXXX"

    直接创建索引

    第一种方法当然是直接使用email来创建索引。这种方法虽然简单粗暴且会占用大量的存储空间,但是有一个好处,就是回表操作只需一次就行。

    使用前缀索引

    所谓前缀索引,比如前面的示例,就是将email字段的前缀截取下来,然后作为索引来使用。

    添加索引的SQL语句为:alter table user_info add index index_eamil(email(9)),这里就添加了一个email字段前9位字符为前缀索引。

    假如email字段的前9位字符就可以唯一的标示一个email地址,比如前9位是不同的数字,后面都是@qq.com。那么这样就做到了完全的区分度。在选择某email时,比如select email, password from user_info where email="123456789@qq.com",MySQL会拿前9位去索引查找,查找得到则会回表到主键树中获取到对应的记录。

    使用前缀索引,一定要权衡好前缀选取的长度,因为前缀的长度就意味着区分度。如果前缀索引可以完全区分,那当然是最好的,这样只需要回表一次就可以拿到数据了。区分度越高越好,区分度越高,也就使得回表操作次数越少。

    只要前缀长度取的合适,就可以既有满意的区分度,又有满意的存储空间占用。

    不过使用前缀索引也有一个劣势,在于使用前缀索引会影响到覆盖索引的使用。

    倒序存储

    倒序存储即是将对应字段倒序过来再取前缀创建索引。针对于前缀区分度低,但后缀区分度高时使用。

    比如我们需要存储一个user_info用户信息表,存储的是自增ID、身份证号、密码。身份证号就属于前缀区分度低(前缀都是一些通用代号),但后缀区分度高的字符串。

    在存储以及查询时也需要将身份证号倒过来使用。比如,在查询时,使用语句:select user_id, password from user_info where user_id=reverse(XXXXXX)

    hash存储

    将对应字段hash之后作为索引存储,hash之后冲突的概率较低,也可大大降低回表的次数。只是由于需要存储字段hash之后的数据,会增加额外存储空间的占用。

    总结

    前面简单介绍了一下几种不同的对字符串创建索引的方法。
    总结如下:

    索引方式 优势 劣势 适用场景
    直接创建索引 回表次数少 索引空间占用大,且每次字符串匹配时间较长 数据量不大或字符串不长的情况
    前缀索引 索引空间占用小,且每次字符串匹配时间较短 可能增加回表次数 选取出的前缀可以有较高的区分度
    倒序存储 无需添加新字段 只能支持单条查询,不支持范围查询 前缀区分度低,后缀区分度高
    hash存储 冲突概率低,得出的索引区分度较高 只能支持单条查询,不支持范围查询,且需要增加新的存储字段 无需范围查询且普适性较高
    展开全文
  • 现在,几乎所有的系统都支持邮箱登录,如何在邮箱这样的字段上建立合理的索引,是我们今天要讨论的问题。假设,你现在维护一个支持邮箱登录的系统,用户表是这么定义的:mysql> create table SUser( ID bigint ...
  • ![图片说明](https://img-ask.csdn.net/upload/201712/11/1512979741_409335.png)
  • mysql字符串前缀索引

    千次阅读 2020-07-13 16:57:54
    mysql字符串前缀索引
  • 利用MySQL 字符串函数 find_in_set()函数 SELECT * FROM user_info WHERE find_in_set('aa@sina.com', sina); SELECT * FROM userInfo WHERE and LOCATE(userId,'0006f7c7c4db48389147a1f5cf15e0ad,0009e
  • 原文链接:方法二:利用mysql 字符串函数 find_in_set();... 这样是可以的,怎么理解呢...mysql有很多字符串函数 find_in_set(str1,str2)函数是返回str2中str1所在的位置索引,str2必须以","分割开。e.g.复...
  • 原文链接 方法一: SELECT * FROM users WHERE emails like “%b@email.com%”; 方法二: 利用mysql 字符串函数 find_in_set(); SELECT * FROM users WHERE find_in_set(‘aa@email....mysql有很多字符串函数 fin
  • 数据库的哪些字段适合添加索引

    千次阅读 2018-03-18 14:14:31
    接着上一篇博文,谈一谈数据库中哪一些字段适合添加索引。1. 表的某个字段值得离散度越高,该字段越适合选作索引的关键字。主键字段以及唯一性约束字段适合选作索引的关键字,原因就是这些字段的值非常离散。MySQL ...
  • pg数据库日期字段索引查询优化

    万次阅读 2018-11-23 17:32:01
    = 来判断日期范围的,当数据量大了,你会发现查询速度就会很慢,这时候我们可以做些优化,给日期字段建立索引,更改查询语句,如下例子:  select * from fcz_moment where dep_airport_code='CTU' and air_date ~...
  • 创建数据库 CREATE DATABASE [IF NOT EXISTS] westos; 删除数据库 DROP DATABASE [IF EXISTS] westos 使用数据库USEschool`` 查看数据库SHOW DATABASES 学习思路: 对照 sqlyog可视化历史记录查看sql 固定的语法或...
  • mysql对字符串字段的操作未加引号

    千次阅读 2018-09-15 13:09:00
    select * from power where power.serialNumber=0 错误 导致大量数据被删除 ...数据库中serialNumber字段字符串类型 而对于无法隐式转换类型的serialNumber字段,默认变为0 mysql 字符类型转换:http...
  • mysql如何针对很长的字符串建立索引

    千次阅读 2019-10-10 20:30:07
    今天面试小米,面试官出了一道题,一个表有url和rank两个字段,其中url代表的是链接,值唯一,如www.baidu.com/1/2,rank代表的是这个链接的权重,范围在(1,10)。假如这张表中有1w条数据,现在要求给定一个url,...
  • oracle中对字符串字段进行排序

    千次阅读 2018-04-13 11:28:37
    配置在初始化参数NLS_SORT中,这可以在数据库创建时指定,也可以通过alter session来修改.如果是前者,则在所有session中生效.例如: 使用select * from NLS_SESSION_PARAMETERS;语句可以看到NLS_SORT的值. 更改配置...
  • 数据库的五种索引类型

    万次阅读 多人点赞 2019-03-15 21:24:13
    本文从如何建立mysql索引以及介绍mysql的索引类型,再讲mysql索引的利与弊,以及建立索引时需要注意的地方 首先:先假设有一张表,表的数据有10W条数据,其中有一条数据是nickname='css',如果要拿这条数据的话需要些的...
  • 字段设计规范 1. 优先选择符合存储需要的最小的数据类型1 原因:列的字段越大,建立索引时所需要的空间... 将字符串转换成数字类型存储,如:将 IP 地址转换成整形数据 MySQL 提供了两个方法来处理 ip 地址 inet_ato...
  • 有这样一个需求,在Mysql数据库字符串字段中,用户有多个抄送人,分别被‘,’分开,现在要取出抄送人所有成员列表。 如: 10,90,900,15 方法一 很简单可以用%来实现 select * FROM noboss_oa_approve_case WHERE ...
  • Mysql数据库字符串常用函数

    千次阅读 2018-10-30 16:54:21
    字符串常用函数 函数 功能 concat(str1,str2,...... 将字符串str,从索引x开始,y个字符长度的子串替换为instr lower(str) 将str中的字符全转换为小写 upper(str) 将str中的字符全转换为大写 ...
  • 数据库索引 1. 如果不建立索引,那么查询都需要全表扫描;如果建立了索引,则数据库会保存一个索引文件通常是特殊的结构比如b树,这样查询起来不需要全表扫描,一下子能够找到满足要求的记录。 2. 一般是对...
  •  有这样一个需求,在Mysql数据库字符串字段(权限)中,用户有多个不同的邮箱,分别被‘,’分开,现在要取出某个邮箱的所有成员列表。 假设有个表:  1 CREATE TABLE users(id int(6) NOT ...
  • MySQL字符串类型字段值大小写问题

    千次阅读 2018-08-13 16:44:40
    MySQL数据库默认情况下,字符串字段的所有相关运算是大小写“不敏感”的。这一点与其它流行的数据库都不相同。 本文介绍了三种方法解决这个问题。 其中一种在查询时指定大小写敏感,但可能存在性能风险。 另外两...
  • 数据库索引:联合索引基本知识

    千次阅读 2015-03-27 11:26:06
    数据库索引:联合索引基本知识
  • 前面两篇文章 《解析B+树比B树更加适合做数据库索引的原因 》 和《从底层解析B+索引提高查询速度的原因》是从数据结构的角度分析了B+索引,并分别介绍了B+索引在两个主流存储引擎InnoDB和MyISAM中的实现。...
  • 数据库索引的理解及适合建立索引字段

    万次阅读 多人点赞 2018-03-06 13:17:43
    转载深入浅出数据库索引原理,哪些字段适合建立索引 问题 为什么要给表加上主键? 为什么加索引后会使查询变快? 为什么加索引后会使写入、修改、删除变慢? 什么情况下要同时在两个字段上建索引? 这些问题...
  • 数据库表及字段命名规范

    万次阅读 2018-03-09 10:15:09
    1.数据库表命名规范:(1)表名前应该加上前缀,表的前缀一个用系统或模块的英文名称缩写,前缀全部大写或首字母大写,表名中包含的单词首字母大写。(2)数据库表名应该有意义,并且易于理解,最好使用可以表达功能的...
  • 而输入空字符串,则是这里的“有对(列)变量输入数据”这一类情况,即有输入数据,只是输入的数据为空字符串(含有终止符的还是)。从这个意义上讲,如int t;语句,也是属于这里的“有对(列)变量输入数据”这...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 161,548
精华内容 64,619
关键字:

数据库字符串字段索引