精华内容
下载资源
问答
  • --查看索引 select * from all_indexes where table_name = '表名(大写)'; --查看索引和列名 select* from all_ind_columns where table_name = '表名(大写)'; --增加索引 create index I_IC_HANDRESERVE_...

    --查看索引

    select * from all_indexes where table_name = '表名(大写)';

    --查看索引和列名

    select* from all_ind_columns where table_name = '表名(大写)';

    --增加索引

    create index I_IC_HANDRESERVE_26 ON IC_HANDRESERVE(CBIZID);

    create index 索引名 ON 表名(字段名);

     

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

    千次阅读 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)无论使用哪种凡是,都无法进行覆盖索引,也无法使用范围查找

    展开全文
  • mysql为字段加索引

    千次阅读 2018-09-14 16:44:46
    1、添加普通索引 ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 案例:ALTER TABLE ts_storage_partinfo_order_batch ADD INDEX IDX_ISB (id_source_bill); 2、添加主键索引 ALTER TABLE `table...

    1、添加普通索引

    ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
    案例:ALTER TABLE ts_storage_partinfo_order_batch ADD INDEX IDX_ISB (id_source_bill);

    2、添加主键索引

    ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 

    3、添加唯一索引 (UNIQUE)

    ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 
    唯一索引在此处可以保证数据记录的唯一性,在许多场合,创建唯一索引并不是为了加快访问速度,而是为了限制数据的唯一性。

    4、全文索引 (FULLTEXT)

    ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 

    5、多列索引

    ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

     

    展开全文
  • 现在,几乎所有的系统都支持邮箱登录,如何在邮箱这样的字段上建立合理的索引,是我们今天要讨论的问题。假设,你现在维护一个支持邮箱登录的系统,用户表是这么定义的:mysql> create table SUser( ID bigint ...

    现在,几乎所有的系统都支持邮箱登录,如何在邮箱这样的字段上建立合理的索引,是我们今天要讨论的问题。

    假设,你现在维护一个支持邮箱登录的系统,用户表是这么定义的:

    mysql> create table SUser(
    ID bigint unsigned primary key,
    email varchar(64), 
    ... 
    )engine=innodb; 
    复制代码

    由于要使用邮箱登录,所以业务代码中一定会出现类似于这样的语句:

    mysql> select f1, f2 from SUser where email='xxx';
    复制代码

    从第 4 和第 5 篇讲解索引的文章中,我们可以知道,如果 email 这个字段上没有索引,那么这个语句就只能做全表扫描。

    同时,MySQL 是支持前缀索引的,也就是说,你可以定义字符串的一部分作为索引。默认地,如果你创建索引的语句不指定前缀长度,那么索引就会包含整个字符串。

    比如,这两个在 email 字段上创建索引的语句:

    mysql> alter table SUser add index index1(email);
    或
    mysql> alter table SUser add index index2(email(6));
    复制代码

    第一个语句创建的 index1 索引里面,包含了每个记录的整个字符串;而第二个语句创建的 index2 索引里面,对于每个记录都是只取前 6 个字节。

    那么,这两种不同的定义在数据结构和存储上有什么区别呢?如图 2 和 3 所示,就是这两个索引的示意图。


                                                        图 1 email 索引结构

                                                          图 2 email(6) 索引结构

    从图中你可以看到,由于 email(6) 这个索引结构中每个邮箱字段都只取前 6 个字节(即:zhangs),所以占用的空间会更小,这就是使用前缀索引的优势。

    但,这同时带来的损失是,可能会增加额外的记录扫描次数。

    接下来,我们再看看下面这个语句,在这两个索引定义下分别是怎么执行的。

    select id,name,email from SUser where email='zhangssxyz@xxx.com';
    复制代码

    (即 email 整个字符串的索引结构),执行顺序是这样的:

    1. 从 index1 索引树找到满足索引值是’zhangssxyz@xxx.com’的这条记录,取得 ID2 的值;
    2. 到主键上查到主键值是 ID2 的行,判断 email 的值是正确的,将这行记录加入结果集;
    3. 取 index1 索引树上刚刚查到的位置的下一条记录,发现已经不满足 email='zhangssxyz@xxx.com’的条件了,循环结束。

    这个过程中,只需要回主键索引取一次数据,所以系统认为只扫描了一行。

    如果使用的是 index2(即 email(6) 索引结构),执行顺序是这样的:

    1. 从 index2 索引树找到满足索引值是’zhangs’的记录,找到的第一个是 ID1;
    2. 到主键上查到主键值是 ID1 的行,判断出 email 的值不是’zhangssxyz@xxx.com’,这行记录丢弃;
    3. 取 index2 上刚刚查到的位置的下一条记录,发现仍然是’zhangs’,取出 ID2,再到 ID 索引上取整行然后判断,这次值对了,将这行记录加入结果集;
    4. 重复上一步,直到在 idxe2 上取到的值不是’zhangs’时,循环结束。

    在这个过程中,要回主键索引取 4 次数据,也就是扫描了 4 行。

    通过这个对比,你很容易就可以发现,使用前缀索引后,可能会导致查询语句读数据的次数变多。

    但是,对于这个查询语句来说,如果你定义的 index2 不是 email(6) 而是 email(7),也就是说取 email 字段的前 7 个字节来构建索引的话,即满足前缀’zhangss’的记录只有一个,也能够直接查到 ID2,只扫描一行就结束了。

    也就是说使用前缀索引,定义好长度,就可以做到既节省空间,又不用额外增加太多的查询成本。

    于是,你就有个问题:当要给字符串创建前缀索引时,有什么方法能够确定我应该使用多长的前缀呢?

    实际上,我们在建立索引时关注的是区分度,区分度越高越好。因为区分度越高,意味着重复的键值越少。因此,我们可以通过统计索引上有多少个不同的值来判断要使用多长的前缀。

    首先,你可以使用下面这个语句,算出这个列上有多少个不同的值:

    mysql> select count(distinct email) as L from SUser;
    复制代码

    然后,依次选取不同长度的前缀来看这个值,比如我们要看一下 4~7 个字节的前缀索引,可以用这个语句:

    mysql> select 
      count(distinct left(email,4))as L4,
      count(distinct left(email,5))as L5,
      count(distinct left(email,6))as L6,
      count(distinct left(email,7))as L7,
    from SUser;
    复制代码

    当然,使用前缀索引很可能会损失区分度,所以你需要预先设定一个可以接受的损失比例,比如 5%。然后,在返回的 L4~L7 中,找出不小于 L * 95% 的值,假设这里 L6、L7 都满足,你就可以选择前缀长度为 6。

    前缀索引对覆盖索引的影响

    前面我们说了使用前缀索引可能会增加扫描行数,这会影响到性能。其实,前缀索引的影响不止如此,我们再看一下另外一个场景。

    你先来看看这个 SQL 语句:

    select id,email from SUser where email='zhangssxyz@xxx.com';
    复制代码

    与前面例子中的 SQL 语句

    select id,name,email from SUser where email='zhangssxyz@xxx.com';
    复制代码

    相比,这个语句只要求返回 id 和 email 字段。

    所以,如果使用 index1(即 email 整个字符串的索引结构)的话,可以利用覆盖索引,从 index1 查到结果后直接就返回了,不需要回到 ID 索引再去查一次。而如果使用 index2(即 email(6) 索引结构)的话,就不得不回到 ID 索引再去判断 email 字段的值。

    即使你将 index2 的定义修改为 email(18) 的前缀索引,这时候虽然 index2 已经包含了所有的信息,但 InnoDB 还是要回到 id 索引再查一下,因为系统并不确定前缀索引的定义是否截断了完整信息。

    也就是说,使用前缀索引就用不上覆盖索引对查询性能的优化了,这也是你在选择是否使用前缀索引时需要考虑的一个因素。

    其他方式

    对于类似于邮箱这样的字段来说,使用前缀索引的效果可能还不错。但是,遇到前缀的区分度不够好的情况时,我们要怎么办呢?

    比如,我们国家的身份证号,一共 18 位,其中前 6 位是地址码,所以同一个县的人的身份证号前 6 位一般会是相同的。

    假设你维护的数据库是一个市的公民信息系统,这时候如果对身份证号做长度为 6 的前缀索引的话,这个索引的区分度就非常低了。

    按照我们前面说的方法,可能你需要创建长度为 12 以上的前缀索引,才能够满足区分度要求。

    但是,索引选取的越长,占用的磁盘空间就越大,相同的数据页能放下的索引值就越少,搜索的效率也就会越低。

    那么,如果我们能够确定业务需求里面只有按照身份证进行等值查询的需求,还有没有别的处理方法呢?这种方法,既可以占用更小的空间,也能达到相同的查询效率。

    答案是,有的。

    第一种方式是使用倒序存储。如果你存储身份证号的时候把它倒过来存,每次查询的时候,你可以这么写:

    mysql> select field_list from t where id_card = reverse('input_id_card_string');
    复制代码

    由于身份证号的最后 6 位没有地址码这样的重复逻辑,所以最后这 6 位很可能就提供了足够的区分度。当然了,实践中你不要忘记使用 count(distinct) 方法去做个验证。

    第二种方式是使用 hash 字段。你可以在表上再创建一个整数字段,来保存身份证的校验码,同时在这个字段上创建索引。

    mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);
    复制代码

    然后每次插入新记录的时候,都同时用 crc32() 这个函数得到校验码填到这个新字段。由于校验码可能存在冲突,也就是说两个不同的身份证号通过 crc32() 函数得到的结果可能是相同的,所以你的查询语句 where 部分要判断 id_card 的值是否精确相同。

    mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'
    复制代码

    这样,索引的长度变成了 4 个字节,比原来小了很多。

    接下来,我们再一起看看使用倒序存储和使用 hash 字段这两种方法的异同点。

    首先,它们的相同点是,都不支持范围查询。倒序存储的字段上创建的索引是按照倒序字符串的方式排序的,已经没有办法利用索引方式查出身份证号码在 [ID_X, ID_Y] 的所有市民了。同样地,hash 字段的方式也只能支持等值查询。

    它们的区别,主要体现在以下三个方面:

    1. 从占用的额外空间来看,倒序存储方式在主键索引上,不会消耗额外的存储空间,而 hash 字段方法需要增加一个字段。当然,倒序存储方式使用 4 个字节的前缀长度应该是不够的,如果再长一点,这个消耗跟额外这个 hash 字段也差不多抵消了。
    2. 在 CPU 消耗方面,倒序方式每次写和读的时候,都需要额外调用一次 reverse 函数,而 hash 字段的方式需要额外调用一次 crc32() 函数。如果只从这两个函数的计算复杂度来看的话,reverse 函数额外消耗的 CPU 资源会更小些。
    3. 从查询效率上看,使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来的值虽然有冲突的概率,但是概率非常小,可以认为每次查询的平均扫描行数接近 1。而倒序存储方式毕竟还是用的前缀索引的方式,也就是说还是会增加扫描行数。

    在实际应用中,你要根据业务字段的特点选择使用哪种方式。

    好了,又到了最后的问题时间。

    如果你在维护一个学校的学生信息数据库,学生登录名的统一格式是”学号 @gmail.com", 而学号的规则是:十五位的数字,其中前三位是所在城市编号、第四到第六位是学校编号、第七位到第十位是入学年份、最后五位是顺序编号。

    系统登录的时候都需要学生输入登录名和密码,验证正确后才能继续使用系统。就只考虑登录验证这个行为的话,你会怎么设计这个登录名的索引呢?

    你可以把你的分析思路和设计结果写在留言区里,我会在下一篇文章的末尾和你讨论这个问题。感谢你的收听,也欢迎你把这篇文章分享给更多的朋友一起阅读。

    上期问题时间

    在上一篇文章最后,我给你留的问题是,为什么经过这个操作序列,explain 的结果就不对了?这里,我来为你分析一下原因。

    delete 语句删掉了所有的数据,然后再通过 call idata() 插入了 10 万行数据,看上去是覆盖了原来的 10 万行。

    但是,session A 开启了事务并没有提交,所以之前插入的 10 万行数据是不能删除的。这样,之前的数据每一行数据都有两个版本,旧版本是 delete 之前的数据,新版本是标记为 deleted 的数据。

    这样,索引 a 上的数据其实就有两份。

    然后你会说,不对啊,主键上的数据也不能删,那没有使用 force index 的语句,使用 explain 命令看到的扫描行数为什么还是 100000 左右?(潜台词,如果这个也翻倍,也许优化器还会认为选字段 a 作为索引更合适)

    是的,不过这个是主键,主键是直接按照表的行数来估计的。而表的行数,优化器直接用的是 show table status 的值。

    这个值的计算方法,我会在后面有文章为你详细讲解。



    转载于:https://juejin.im/post/5d034449f265da1bab29aaa4

    展开全文
  • 表结构中字段是否添加索引判断依据是什么? -- 字段是否是查询条件或者是排序条件。   是否将所有的字段都添加索引,来加快查询? -- 不行的   1、 索引会占用存储空间,索引越多,使用的存储空间越多 2...
  • 为时间字段加索引

    千次阅读 2020-03-24 18:29:58
    文章目录为时间字段加索引(待更进)一、问题1、描述:日期不一致2、原因:时区不同3、解决方法: 时区修改二、datetime和varchar类型效率比较1、背景2、开始三、时间字段加索引1、聚集索引2、非聚集索引3、时间字段...
  • 字段添加索引

    千次阅读 2019-04-30 12:43:37
    1.添加PRIMARY KEY(主键索引): ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) ; 2.添加UNIQUE(唯一索引): ALTER TABLE `table_name` ADD UNIQUE (`column`) ; 3.添加INDEX(普通索引) : ALTER ...
  • 标签 PostgreSQL , 索引 , 所有字段 ,...如何快速表的所有字段,每个字段都加上索引。 《PostgreSQL 设计优化case - 大宽表任意字段组合查询索引如何选择(btree, gin, rum) - (含单个索引列数超过32列的方法)》 满...
  • 给字段添加索引,可以加快数据查询的速度。当然了,在数据量足够大的情况下,加索引和不加索引的差距是很明显的。但是索引之后,在保存方面大概是会很浪费时间的。 1、单一索引 mongodb中使用以下命令来...
  • MySQL某个字段添加索引

    千次阅读 2020-06-03 17:57:42
    1、为cht_apps表的META_AID字段添加主键索引(PRIMARY KEY): ALTER TABLE cht_apps ADD PRIMARY KEY (META_AID) 执行结果: 表结构: 一张表只能有一个主键索引,再次添加会报重复主键: 2、为cht_apps表的...
  • mysql表的字段加索引

    万次阅读 2017-07-25 15:42:54
    1、添加普通索引ALTER TABLE `table_name` ADD INDEX index_name ( `column` )2、添加主键索引ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 3、添加唯一索引 (UNIQUE)ALTER TABLE `table_name` ADD ...
  • 时间字段加索引

    万次阅读 2018-11-14 22:05:20
    微软的SQL SERVER提供了两种索引:聚集索引(clustered index,也称聚类索引、簇集索引)和非聚集索引(nonclustered index,也称非聚类索引、非簇集索引)。下面,我们举例来说明一下聚集索引和非聚集索引的区别:...
  • 主要大家介绍了关于MySQL分区字段列是否有必要再单独建索引的相关资料,文中通过示例进行了验证,对大家的理解和学习具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧。
  • 第一,基础方法增加字段基本方法,该方法适合十几万的数据量,可以直接进行加字段操作。1ALTER TABLE tbl_tpl ADD title(255) DEFAULT '' COMMENT '标题' AFTER id;但是,线上的一张表如果数据量很大,执行加字段...
  • 哪些字段可以加索引

    千次阅读 2019-03-27 10:05:58
    2.经常与其他表进行连接的表,在连接字段上应该建立索引; 3.复合索引的几个字段是否经常同时以AND方式出现在Where子句中?如果是,则可以建立复合索引; 4.一个表如果是复合索引,尽量不要超过3个. 5.频繁进行...
  • 会发现随着表中记录的增多,查询的速度也会也来越慢,特别是那种日志记录,少则几十万,多则上百万,甚至上千万数据,如果查询一次耗时太长,会严重影响业务逻辑,这时候可以考虑经常作为条件的字段添加索引了...
  • 而oracle在查询时,会将查询字段变成大写的,结果就是USER中没有大写的USERNAME字段,只有小写的username字段,所以查询不到.而EMPLOYEEFORECOLOGY 中的字段都是大写的,自然是能够查询到ACCOUNT字段. 最后的解决办法,...
  • 通常我们库中数据量大时,往往会降低我们的查询效率,那么增加索引可以提高查询效率。 CREATE INDEX IDEN_IDNUMBER-->索引名称 ON SIDENTITY-->表名 ( IDNUMBER-->字段名 );
  • MySQL表里的某个字段建立索引

    千次阅读 2019-04-15 15:29:41
    注:`table_name` 和 `column` 中的``是Esc下方的一个键 ~`,可省略不写``,直接写表名和字段名 1.添加PRIMARY KEY(主键索引): ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 2.添加UNIQUE(唯一索引)...
  • Mysql时间字段加索引是否生效的问题

    千次阅读 2020-04-17 19:07:14
    在查询数据条数约占总条数五分之一以下时能够使用到索引,但超过五分之一时,则使用全表扫描了。 参考1:https://www.cnblogs.com/tiancai/p/9518730.html 参考2:...
  • 字段索引 前缀索引 举例: mysql> create table SUser( ID bigint unsigned primary key, email varchar(64), ... )engine=innodb; 可以对 email 字段创建全字段索引,或者前缀索引。 mysql> ...
  • Mysql索引-可以text字段索引吗?

    千次阅读 2021-04-06 21:11:03
    结论,直接在text字段索引会报错;指定索引前缀的长度,并且长度小于等于767(utf8mb4字符集)、长度小于等于1023(utf8字符集)则可以创建索引。 例如对于Mysql5.7,如下的语句会报错”索引长度最大为3072...
  • 建表时添加 DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `stu_id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`stu_id`... UNIQUE KEY `UK_studen...
  • 为表字段添加索引

    万次阅读 2019-06-25 17:36:44
    (1)使用ALTER TABLE语句创建索引,其中包括普通索引、UNIQUE索引和PRIMARY KEY索引3种创建索引的格式: PRIMARY KEY主键索引:mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) NIQUE唯一索引:...
  • sql如何为表字段添加索引

    千次阅读 2020-06-23 19:42:19
    添加主键索引:ALTER TABLE ‘table_name’ ADD PRIMARY KEY (‘column’) 添加唯一索引:ALTER TALBE ‘table_name’ ADD UNIQUE KEY(‘column’) 添加普通索引:ALTER TABLE ‘table_name’ ADD INDEX index_name...
  • 修改原有字段名称及类型: ALTER TABLE bulletin CHANGE uid username VARCHAR(50) NOT NULL DEFAULT ''; 添加新字段: alter table bulletin add citycode varchar(6) not null default 0; # 城市代码 1...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 568,495
精华内容 227,398
关键字:

怎么给字段加索引