精华内容
下载资源
问答
  • 更改索引日期类型: df.index = pd.to_datetime(df.index)

    更改索引为日期类型:

    df.index = pd.to_datetime(df.index)

    展开全文
  • MySql性能调优(一)字段类型的选取之时间类型
  • Mysql 索引问题-日期索引使用

    万次阅读 2018-03-16 15:07:50
    最近在使用日期索引时,通过explain发现一直不走日期索引,在网上查询了下,发现使用过程中要注意以下情况:1、在查询数据条数约占总条数五分之一以下时能够使用到索引,但超过五分之一时,则使用全表扫描了。...

    最近在使用日期索引时,通过explain发现一直不走日期索引,在网上查询了下,发现使用过程中要注意以下情况:

    1、在查询数据条数约占总条数五分之一以下时能够使用到索引,但超过五分之一时,则使用全表扫描了。

    2、查询条件有日期索引和其他条件的话,只有所有条件都有索引的情况下,才会走日期索引,例如:

    WHERE stringId='1223333' and dayid <='2018-03-15 00:00:00';如果想走dayid 的索引,表中必须有stringId字段的索引才能走,暂时没发现为啥会出现此问题,有知道可以分享下哦

    展开全文
  • MySql索引类型

    万次阅读 2018-07-04 22:15:19
    Mysql支持哪几种索引索引是在MySql的存储引擎层中实现的,而不是在服务器层从数据结构角度1、B+树索引(O(log(n))):关于B+树索引,可以参考 MySQL索引背后的数据结构及算法原理BTREE在MyISAM里的形式和Innodb稍有...

    Mysql支持哪几种索引

    索引是在MySql的存储引擎层中实现的,而不是在服务器层

    从数据结构角度

    1、B+树索引(O(log(n))):关于B+树索引,可以参考 MySQL索引背后的数据结构及算法原理

    BTREE在MyISAM里的形式和Innodb稍有不同

    在 Innodb里,有两种形态:一是primary key形态,其leaf node里存放的是数据,而且不仅存放了索引键的数据,还存放了其他字段的数据。二是secondary index,其leaf node和普通的BTREE差不多,只是还存放了指向主键的信息.

    而在MyISAM里,主键和其他的并没有太大区别。不过和Innodb不太一样的地方是在MyISAM里,leaf node里存放的不是主键的信息,而是指向数据文件里的对应数据行的信息.

    MyISAM的B+Tree的叶子节点上的data,并不是数据本身,而是数据存放的地址。主索引和辅助索引没啥区别,只是主索引中的key一定得是唯一的。这里的索引都是非聚簇索引。
    MyISAM还采用压缩机制存储索引,比如,第一个索引为“her”,第二个索引为“here”,那么第二个索引会被存储为“3,e”,这样的缺点是同一个节点中的索引只能采用顺序查找。

    InnoDB的数据文件本身就是索引文件,B+Tree的叶子节点上的data就是数据本身,key为主键,这是聚簇索引。非聚簇索引,叶子节点上的data是主键(所以聚簇索引的key,不能过长)。为什么存放的主键,而不是记录所在地址呢,理由相当简单,因为记录所在地址并不能保证一定不会变,但主键可以保证。

    myisam引擎的数据在物理磁盘上是按照顺序存储的,而innodb引擎的表数据是随机分布的;
    MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图:
    这里设表一共有三列,假设我们以Col1为主键,则上图是一个MyISAM表的主索引(Primary key)示意。可以看出 MyISAM的索引文件仅仅保存数据记录的地址 。在MyISAM中,主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求key是唯一的,而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引,则此索引的结构如下图所示:
    此图中叶子结点的34的地址应为0x07
     
    同样也是一颗B+Tree,data域保存数据记录的地址。因此,MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其data域的值,然后以data域的值为地址,读取相应数据记录。
    MyISAM的索引方式也叫做“ 非聚集 ”的,之所以这么称呼是为了与InnoDB的聚集索引区分。


    InnoDB索引实现
    虽然InnoDB也使用B+Tree作为索引结构,但具体实现方式却与MyISAM截然不同。
    第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道,MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。而在InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。
    上图是InnoDB主索引(同时也是数据文件)的示意图,可以看到叶节点包含了完整的数据记录。这种索引叫做 聚集索引 。因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没有),如果没有显式指定,则MySQL 系统 会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。
     
    第二个与MyISAM索引的不同是InnoDB的 辅助索引data域存储相应记录主键的值而不是地址 。换句话说,InnoDB的所有辅助索引都引用主键作为data域。例如,下图为定义在Col3上的一个辅助索引:
    这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。
     
    了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助,例如知道了InnoDB的索引实现后,就很容易明白为什么不建议使用过长的字段作为主键,因为所有辅助索引都引用主索引,过长的主索引会令辅助索引变得过大。再例如,用非单调的字段作为主键在InnoDB中不是个好主意,因为InnoDB数据文件本身是一颗B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。

    聚簇索引的优缺点:

    • 可以把相关数据保存在一起。例如实现电子邮箱时,可以根据用户ID来聚集数据,这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引,则每封邮件都可能导致一次磁盘I/O.
    • 数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中,因此从聚簇索引中获取数据通常比非聚簇索引中查找要快。
    • 使用覆盖索引扫描的查询可以直接使用页节点的主键值。
    同时聚簇索引还有一些缺点:
    • 插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。(这种情况可以用主键auto_increment自增列解决)
    • 更新聚簇索引列的代价很高,因为会强制InnoDB将每个被更新的行移动到新的位置。
    • 二级索引(非聚簇索引)可能比想象的要更大,因为在二级索引的叶子节点包含了引用行的主键列。
    • 二级索引的访问需要两次索引查找,而不是一次。
    最后一点可能让人有些疑惑,为什么二级索引需要两次索引查找?答案在于二级索引中保存的“行指针”的实质。要记住,二级索引叶子节点保存的不是指向行的物理位置的指针,而是行的主键值。
    这意味着通过二级索引查找行,存储引擎需要找到二级索引的叶子节点获得对应的主键值,然后根据这个值去聚簇索引中查找对应的行。这里做了重复的工作:两次B-Tree查找而不是一次。

    在InnoDB表中按主键顺序插入行

    如果正在使用InnoDB并且没有什么数据需要聚集,那么可以定义一个代理键作为主键,这种主键的数据应该和应用无关,最简单的方法是使用AUTO_INCREMENT自增列。这样可以保证数据行是按顺序写入,对于根据主键做关联操作的性能也会更好。
    最好避免随机的(不连续且值的分布范围都非常大)聚簇索引,特别是对于I/O密集型的应用。例如,从性能的角度考虑,使用UUID来作为聚簇索引则会很糟糕:它使得聚簇索引的插入变得完全随机,这是最坏的情况,使得数据没有任何的聚集特性。为了说明,我们下面举个例子:
    两个表:userinfo,自增主键,userinfo_uuid:UUID主键。
    下图对测试结果进行了比较:

    注意到项UUID主键插入行不仅花费的时间更长,而且索引占用的空间也更大。这一方面是由于主键字段更长;另一方面毫无疑问是由于页分裂和碎片导致的。

    这是因为主键的值是顺序的,所以InnoDB把每一条记录都存储在上一条记录的后面 当达到页的最大填充因子后,下一条记录就会在新的页中 。一旦按照这个顺序的方式加载,主键页就会近似于被顺序的记录填满,这也正是所期望的结果(然而,二级索引页可能是不一样的)。
    对比一下看看UUID举措索引的插入表数据,看看有什么不同:
    因为新行的主键值不一定比之前插入的大,所以InnoDB无法简单地总是把新行插入到索引的最后,而是需要为新的行寻找合适的位置 ——通常是已有数据的中间位置——并且分配空间 。这会增加很多的额外工作,并导致数据分布不够优化。下面是总结的一些缺点:
    • 写入的目标页可能已经刷到磁盘上并从缓存中移除,或者是还没有被加载到缓存中,InnoDB在不得不在插入新行之前先找到并从磁盘读取到内存中。这将导致大量的随机I/O。
    • 因为写入是乱序的,InnoDB不得不频繁地做页分裂操作,以便为新的行分配空间。页分裂会导致移动大量数据,一次插入最少需要修改三个页而不是一个页。
    • 由于频繁的页分裂,页会变得稀疏并被不规则地填充,所以最终数据会有碎片。
    从这个案例可以看出,使用InnoDB时应该尽可能地按主键顺序插入数据,并且尽可能使用单调增加的聚簇键的值来插入新行。

    顺序的主键什么时候会造成更坏的结果?

    对于高并发工作负载,在InnoDB中按主键顺序插入可能会造成明显的争用。主键的上界会成为“热点”。因为所有的插入都发生在这里,所以并发插入可能导致间歇性竞争。另一个热点可能是AUTO_INCREMENT锁机制;如果遇到这个问题,则可能需要考虑下重新设计表或者应用,或者更改innodb_autoinc_lock-mode配置。如果你的服务器版本还不支持innodb_autoinc-lock_mode参数,可以升级到新版本的InnoDB,可能对这种场景工作的更好。

    总结

    a、关于innoDB中索引的使用

          了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助,例如知道了InnoDB的索引实现后,就很容易明白为什么不建议使用过长的字段作为主键,因为所有辅助索引都引用主索引,过长的主索引会令辅助索引变得过大。再例如,用非单调的字段作为主键在InnoDB中不是个好主意,因为InnoDB数据文件本身是一颗B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。

    b、什么时候选用myisam

          myisam的主键索引的叶子节点只存放数据在物理磁盘上的指针,其他次索引也是一样的; 
    innodb的主键索引的叶子节点下面直接存放数据,其他次索引的叶子节点指向主键id; 
    由此可以挖掘出一个问题,就是如果Innodb有大数据列,比如 varchar(300),这种比较多的话,那么排序的时候用主键id排序会比较慢,因为id主键下面放着所有数据列,而Myisam就不需要扫描数据列,要解决这个问题的话可以再建一个和主键id一起的联合索引;

          MyISAM表索引在处理文本索引时更具优势,而INNODB表索引在其它类型上更具效率优势。比如全文索引一般在CHAR、VARCHAR或TEXT列上创建,MyISAM表支持而INNODB表不支持,常见主要针对文本进行索引。同时MySQL高并发需要事务场景时,只能使用INNODB表。

    c、该如何选用两个存储引擎呢

    此处参考链接:MySQL中MyISAM与InnoDB区别及选择 
          因为MyISAM相对简单所以在效率上要优于InnoDB.如果系统读多,写少。对原子性要求低。那么MyISAM最好的选择。且MyISAM恢复速度快。可直接用备份覆盖恢复。 
    如果系统读少,写多的时候,尤其是并发写入高的时候。InnoDB就是首选了。 
    两种类型都有自己优缺点,选择那个完全要看自己的实际类弄。

    2、hash索引
            a 仅仅能满足"=","IN"和"<=>"查询,不能使用范围查询
            b 其检索效率非常高,索引的检索可以一次定位,不像B-Tree 索引需要从根节点到枝节点,最后才能访问到页节点这样多次的IO访问,所以 Hash 索引的查询效率要远高于 B-Tree 索引
            c 只有Memory存储引擎显示支持hash索引

    (1)Hash 索引仅仅能满足"=","IN"和"<=>"查询,不能使用范围查询。 
    由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值,所以它只能用于等值的过滤,不能用于基于范围的过滤,因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系,并不能保证和Hash运算前完全一样。 
    (2)Hash 索引无法被用来避免数据的排序操作。 
    由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值,而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样,所以数据库无法利用索引的数据来避免任何排序运算; 
    (3)Hash 索引不能利用部分索引键查询。 
    对于组合索引,Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值,而不是单独计算 Hash 值,所以通过组合索引的前面一个或几个索引键进行查询的时候,Hash 索引也无法被利用。 
    (4)Hash 索引在任何时候都不能避免表扫描。 
    前面已经知道,Hash 索引是将索引键通过 Hash 运算之后,将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中,由于不同索引键存在相同 Hash 值,所以即使取满足某个 Hash 键值的数据的记录条数,也无法从 Hash 索引中直接完成查询,还是要通过访问表中的实际数据进行相应的比较,并得到相应的结果。 
    (5)Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。 
    对于选择性比较低的索引键,如果创建 Hash 索引,那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦,会浪费多次表数据的访问,而造成整体性能低下。

    hash值即为通过特定算法由指定列数据计算出来,磁盘地址即为所在数据行存储在硬盘上的地址(也有可能是其他存储地址,其实MEMORY会将hash表导入内存)。

    这样,当我们进行WHERE age = 18 时,会将18通过相同的算法计算出一个hash值==>在hash表中找到对应的储存地址==>根据存储地址取得数据

    所以,每次查询时都要遍历hash表,直到找到对应的hash值,如(4),数据量大了之后,hash表也会变得庞大起来,性能下降,遍历耗时增加,如(5)。

    InnoDB存储引擎有一个特别的功能,叫自适应哈希索引。当InnoDB注意到一些索引被很频繁的访问的时候,会在B-Tree索引的顶端为这些值建立起内存中的索引。这个过程是自动的,既不能控制,也不能配置它。

    3、FULLTEXT索引(现在MyISAM和InnoDB引擎都支持了)

    主要用来查找文本中的关键字,而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同,它更像是一个搜索引擎,而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用,而不是一般的where语句加like。

    1. --创建article
    2. CREATE TABLE article (
    3.     id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    4.     title VARCHAR(200),
    5.     content TEXT,
    6.     FULLTEXT (title, content) --在titlecontent列上创建全文索引
    7. );

    例如,我们想要在article表的titlecontent列中全文检索指定的查询字符串,可以如下编写SQL语句:

    SELECT * FROM article WHERE MATCH(title, content) AGAINST('查询字符串')
    它可以在create table,alter table ,create index使用,不过目前只有char、varchar,text 列上可以创建全文索引。值得一提的是,在数据量较大时候,先将数据放入一个没有全局索引的表中,然后再用CREATE index创建fulltext索引,要比先为一张表建立fulltext然后再将数据写入的速度快很多。 如果可能,请尽量先创建表并插入所有数据后再创建全文索引,而不要在创建表时就直接创建全文索引,因为前者比后者的全文索引效率要高。

    全文索引并不是和MyISAM一起诞生的,它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。在没有全文索引之前,这样一个查询语句是要进行遍历数据表操作的,可见,在数据量较大时是极其的耗时的,如果没有异步IO处理,进程将被挟持,很浪费时间。
    (1)创建表的适合添加全文索引

    复制代码
    CREATE TABLE `table` (
        `id` int(11) NOT NULL AUTO_INCREMENT ,
        `title` char(255) CHARACTER NOT NULL ,
        `content` text CHARACTER NULL ,
        `time` int(10) NULL DEFAULT NULL ,
        PRIMARY KEY (`id`),
        FULLTEXT (content)
    );
    复制代码

    (2)修改表结构添加全文索引

    ALTER TABLE article ADD FULLTEXT index_content(content)

    (3)直接创建索引

    CREATE FULLTEXT INDEX index_content ON article(content)

    4、R-Tree索引(空间索引)(用于对GIS数据类型创建SPATIAL索引)

    索引MyISAM引擎InnoDB引擎Memory引擎
    B-Tree索引支持支持支持
    HASH索引支持支持支持
    R-Tree索引支持支持支持
    Full-text索引支持暂不支持(现在支持)支持

    从物理存储角度

    1、聚集索引(clustered index)

    2、非聚集索引(non-clustered index)

    聚集索引:

      一种索引,该索引中键值的逻辑顺序决定了表中相应行的物理顺序。即:只要索引是相邻的,那么对应的数据一定也是相邻地存放在磁盘上的。
      聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿,后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序,因此一个表只能包含一个聚集索引。但该索引可以包含多个列(组合索引),就像电话簿按姓氏和名字进行组织一样。    

      聚集索引对于那些经常要搜索范围值的列特别有效使用聚集索引找到包含第一个值的行后,便可以确保包含后续索引值的行在物理相邻。例如,如果应用程序执行的一个查询经常检索某一日期范围内的记录,则使用聚集索引可以迅速找到包含开始日期的行,然后检索表中所有相邻的行,直到到达结束日期。这样有助于提高此 类查询的性能。同样,如果对从表中检索的数据进行排序时经常要用到某一列,则可以将该表在该列上聚集(物理排序),避免每次查询该列时都进行排序,从而节 省成本。    

      当索引值唯一时,使用聚集索引查找特定的行也很有效率。例如,使用唯一雇员 ID 列 emp_id 查找特定雇员的最快速的方法,是在 emp_id 列上创建聚集索引或 PRIMARY KEY 约束。

    如果涉及到大数据量的排序、全表扫描、count之类的操作的话,还是MyISAM占优势些,因为索引所占空间小,这些操作是需要在内存中完成的。

    非聚集索引:

      非聚集索引,必须先查到目录中查到每一项数据对应的页码,然后再根据页码查到具体内容,该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。记录的物理顺序与逻辑顺序没有必然的联系

      索引是通过B-Tree的数据结构来描述的,我们可以这么理解聚簇索引:索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点,只不过有一个指针指向对应的数据块。

    备注:每个表只能有一个聚簇索引,因为一个表中的记录只能以一种物理顺序存放。但是,一个表可以有不止一个非聚簇索引。聚集索引一张表只能创建一个,非聚集索引一张表可以创建多个,在mysqlInnoDB引擎是唯一支持聚集索引的存储引擎。InnoDB按照主键(Primary Key)进行聚集,如果没有定义主键,InnoDB会试着使用唯一的非空索引来代替。如果没有这种索引,InnoDB就会定义隐藏的主键然后在上面进行聚集

    非聚簇索引需要大量的硬盘空间和内存。另外,虽然非聚簇索引可以提高从表中取数据的速度,它也会降低向表中插入和更新数据的速度。每当你改变了一个建立了非聚簇索引的表中的数据时,必须同时更新索引。 如果你预计一个表需要频繁地更新数据,那么不要对它建立太多非聚簇索引。另外,如果硬盘和内存空间有限,也应该限制使用非聚簇索引的数量。


    从逻辑角度

    1、普通索引或者单列索引

    是最基本的索引,它没有任何限制。它有以下几种创建方式:
    (1)直接创建索引

    CREATE INDEX index_name ON table(column(length))

    (2)修改表结构的方式添加索引

    ALTER TABLE table_name ADD INDEX index_name ON (column(length))

    (3)创建表的时候同时创建索引

    复制代码
    CREATE TABLE `table` (
        `id` int(11) NOT NULL AUTO_INCREMENT ,
        `title` char(255) CHARACTER NOT NULL ,
        `content` text CHARACTER NULL ,
        `time` int(10) NULL DEFAULT NULL ,
        PRIMARY KEY (`id`),
        INDEX index_name (title(length))
    )
    复制代码

    (4)删除索引

    DROP INDEX index_name ON table

    2、唯一索引或者非唯一索引

    与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。它有以下几种创建方式:
    (1)创建唯一索引

    CREATE UNIQUE INDEX indexName ON table(column(length))

    (2)修改表结构

    ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

    (3)创建表的时候直接指定

    复制代码
    CREATE TABLE `table` (
        `id` int(11) NOT NULL AUTO_INCREMENT ,
        `title` char(255) CHARACTER NOT NULL ,
        `content` text CHARACTER NULL ,
        `time` int(10) NULL DEFAULT NULL ,
        UNIQUE indexName (title(length))
    );
    复制代码

    3、主键索引主键索引是一种特殊的唯一索引,一个表只能有一个主键,不允许有空值,一般是在建表的时候同时创建主键索引:

    CREATE TABLE `table` (
        `id` int(11) NOT NULL AUTO_INCREMENT ,
        `title` char(255) NOT NULL ,
        PRIMARY KEY (`id`)
    );

    4、多列索引(组合索引):

    指多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。使用组合索引时遵循最左前缀集合

    ALTER TABLE `table` ADD INDEX name_city_age (name,city,age); 

    5、空间索引:空间索引是对空间数据类型的字段建立的索引,MYSQL中的空间数据类型有4种,分别是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL关键字进行扩展,使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列,必须将其声明为NOT NULL,空间索引只能在存储引擎为MYISAM的表中创建

    CREATE TABLE table_name[col_name data type]
    [unique|fulltext|spatial][index|key][index_name](col_name[length])[asc|desc]

    1、unique|fulltext|spatial为可选参数,分别表示唯一索引、全文索引和空间索引;

    2、index和key为同义词,两者作用相同,用来指定创建索引

    3、col_name为需要创建索引的字段列,该列必须从数据表中该定义的多个列中选择;

    4、index_name指定索引的名称,为可选参数,如果不指定,MYSQL默认col_name为索引值;

    5、length为可选参数,表示索引的长度,只有字符串类型的字段才能指定索引长度;

    6、asc或desc指定升序或降序的索引值存储

    缺点

    1.虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行insert、update和delete。因为更新表时,不仅要保存数据,还要保存一下索引文件。
    2.建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会增长很快。
    索引只是提高效率的一个因素,如果有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。

    注意事项

    使用索引时,有以下一些技巧和注意事项:
    1.索引不会包含有null值的列
    只要列中包含有null值都将不会被包含在索引中,复合索引中只要有一列含有null值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为null。
    2.使用短索引
    对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个char(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
    3.索引列排序
    查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。
    4.like语句操作
    一般情况下不推荐使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
    5.不要在列上进行运算
    这将导致索引失效而进行全表扫描,例如

    SELECT * FROM table_name WHERE YEAR(column_name)<2017;

    6.不使用not in和<>操作


    展开全文
  • mysql 日期索引问题

    万次阅读 2018-07-02 21:05:00
    日期类型可以直接和string格式的字符串比较 select * from xxx where event_time>'2018-06-02' 可以使用索引, mysql默认会把后面的字符串转成date类型。可以使用between and select * from xxx where date...

     

    日期类型可以直接和string格式的字符串比较

    select * from xxx where event_time>'2018-06-02' 可以使用索引, mysql默认会把后面的字符串转成date类型。可以使用between and

    select * from xxx where date(event_time)>'2018-06-02'   不能使用索引

    如果时间戳日期和时间都要比较, 最好使用两个字段保存这个时间戳, 这样可以利用索引

    select * from xxx where event_time>1239237428734; --  使用错误, 不能这么用。可以使用from_unixtime()将数字转成日期类型

     

     

     将date和time合并成一个datetime

    select str_to_date(concat(ICDate,' ',ICTime),'%m/%d/%y %h:%i:%s %s')   from XXXX

     

    参考: https://stackoverflow.com/questions/2758486/mysql-compare-date-string-with-string-from-datetime-field

     

     

    其他索引总结:

    where条件等号两边字段类型不同,不走索引

    like 'XXX%' 走索引, '%XXX%'不走索引

    对字段进行函数运算不走索引

    组合索引 只使用后面的字段不走索引,使用前后的字段走索引. 第一个字段有参于(而且字段类型匹配 没有函数运算),那么会走索引, 第一个字段可以在sql中的任意位置

     组合索引遇到第一个不等值条件 即中断后面字段使用索引

     字段类型不匹配,不走索引

     

     示例:

    name varchar, addr varchar, age int。 创建组合索引为name + addr + age

    select * from t_user where name like '123%' and age>19;  走索引 type=range

    select * from t_user where age>19 and name like '123%' ;  走索引 type=range

    select * from t_user where name =123 不走索引 type=all

    select * from t_user where name='123' 走索引 type=ref

    select * from t_user where addr like '上城%' and age>30; 不走索引 type=all

     

    explain 是否使用了索引

    type=ALL是全表扫描

    只要不是ALL, 都能使用索引,只是使用索引的方式不同,性能也有差异==>依次从好到差:system,const,eq_ref,ref,fulltext,ref_or_null,unique_subquery,index_subquery,range,index_merge,index,ALL

    关于explain各个字段的解释:https://www.cnblogs.com/david97/p/8072164.html

     

    索引不是越多越好, 两个index进行merge有时还不如只使用一个index

    组合索引创建的依据:select distinct(xxx)/count(*) 值越大说明区分度越高 组合索引的价值越高 

    什么时候创建组合索引?  单列上查询出来的数据量都很大,但是两个组合查询的结果很小 ,此时创建组合索引就比较有意义

     (原始的离线度都小,组合的离散度会大???)    0.8*0.8=0.64       0.8*0.5=0.4    如果整体的数据量级别很大,也有点效果

     

     

    索引的类型

     BTREE的时间复杂度: https://blog.csdn.net/weixin_38399962/article/details/79409118

     

    orderby 是否使用索引

    https://my.oschina.net/u/912810/blog/325177

    orderby用到的索引和where中的索引如果一致, 可以提升不少性能

     

     

    字符类型的索引需要指定长度

    ALTER TABLE t_yyy ADD INDEX `xxx` (`request_type` ASC, `request_param`(15) ASC);

     

    转载于:https://www.cnblogs.com/yszzu/p/9255769.html

    展开全文
  • 索引

    千次阅读 多人点赞 2018-09-07 23:27:20
    类型:普通索引,唯一索引,主键索引,复合索引,聚族索引。 唯一索引:不允许具有索引值相同的行,即每一行数据的索引的值唯一。 复合索引:对多列添加一个索引。 复合索引遵循最左原则,即创建复合索引时的第一...
  • 产生原因:左边数据库...解决方法:去掉jdbcType=TIMESTAMP,日期后面加0,会直接强制转成date类型 正确格式: AND CREATE_DATE_TIME >= #{createDateTimeFrom}+0 AND CREATE_DATE_TIME 错误格式: AND C
  • 1. MongoDB索引策略和索引类型–简介 MongoDB是一个开放源代码,面向文档的跨平台数据库,它使用C ++开发,并且是最流行和使用最广泛的NoSQL类型数据库之一。 它可在具有键-值对的类JSON文档的顶部运行,其键值对在...
  • pg数据库日期字段索引查询优化

    万次阅读 2018-11-23 17:32:01
    = 来判断日期范围的,当数据量大了,你会发现查询速度就会很慢,这时候我们可以做些优化,给日期字段建立索引,更改查询语句,如下例子:  select * from fcz_moment where dep_airport_code='CTU' and air_date ~...
  • elasticsearch日期索引匹配

    千次阅读 2018-12-10 15:34:22
    日期数学索引名称解析使您可以搜索一系列时间序列索引,而不是搜索所有时间序列索引并过滤结果或维护别名。 限制搜索的索引数可以减少集群上的负载并提高执行性能。 例如,如果要在日常日志中搜索错误,则可以使用...
  • 使用python读取tushare股市数据后,如何将float类型的日期,转化为日期类型,并且设定为索引 直接代码如下: import pandas as pd import tushare as ts TOKEN = 'c0**********8c' pro = ts.pro_api(token=TOKEN)...
  • ES系列之一文带你避开日期类型存在的坑 概述 时间相关的字段是ElasticsSearch(以下简称ES)最常用的字段了,几乎所有的索引应用场景都会有时间字段,一般用于基于时间范围的搜索,聚合等场景。但是由于时区的问题,...
  • Mysql: mysql between 日期索引 ...这两天发现原来的查询效率慢了,使用explain 查看,居然没有使用索引, 我的索引日期类型的,首先想到的是mysql对日期类型索引的处理机制是不是不同,在where条件里试了几种...
  • char类型字段使用where查询的时候,该字段的值必须要加引号(哪怕是纯数字),说明这是个字符串,不然无法使用索引,导致全表查询。
  • mysql 索引类型以及使用场景

    万次阅读 2018-07-10 10:44:11
    关于MySQL索引的好处,如果正确合理设计并且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。对于没有索引的表,单表查询可能几十万数据就是瓶颈,而通常大型网站单日就可能...
  • 日期类型不用设置分词器。 通常日期类型的字段用于排序。 示例使用 #给example索引新增一个birthday字段,类型为date, 格式可以是yyyy-MM-dd 或 yyyy-MM-dd HH:mm:ss #添加日期类型的映射 PUT test_label_supplier/...
  • Elasticsearch索引rollover按日期滚动

    千次阅读 2020-05-26 09:17:59
    从前面的学习中,我们知道es的字段建立后就不能被修改,假设原本是一个string类型的数据,被识别成date类型,之后再传一个string类型,由于无法转换格式就报错。。这个时候我们的做法就只能加一个字段去处理,,...
  •  关于MySQL索引的好处,如果正确合理设计并且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。对于没有索引的表,单表查询可能几十万数据就是瓶颈,而通常大型网站单日就...
  • Oracle时间索引的使用

    千次阅读 2011-10-08 11:42:11
    Oracle中我们经常使用Date字段类型记录日期和时间,有的时候还在这个字段上建立索引。 然后通过Java程序访问数据库的时候,我们很自然的类似这样使用:select * from table where endDate&gt;? and endDate&...
  • python将索引变为日期

    千次阅读 2019-02-18 20:41:21
    1.生成一个日期序列,这里假设原来数据是100行的。 dates = pd.date_range('20170520',periods=100) 2.修改原来DataFrame的index。 df.index = dates  
  • postgresql常用的索引类型有btree,hash,gin,gist,spgist,brin这六种,btree和hash不同多说,最长用的了,基本都支持,下边针对gin,gist,spgist以及brin这四种,他们所支持的数据类型总结如下: 名字 ...
  • 最近在做mysql索引测试,新建了一个数据表,为两个字段分别建立普通索引,本来是为了测试其他的情况做准备,开始之前,...不甘心,我又测试几遍,type类型都是ALL,全表查询,为什么,难道where 字段名=字段值本来
  • ![图片说明](https://img-ask.csdn.net/upload/201712/11/1512979741_409335.png)
  • 参考网址: 【官网】https://www.elastic.co/guide/cn/elasticsearch/guide/current/_creating_an_index.html 【api信息】...对es中索引index的思考,排序自定义日期date 一个 Elasticsea...
  • 146、Elasticsearch的索引类型

    千次阅读 2019-08-11 20:15:47
    一、Elasticsearch的索引 ...索引可以在添加文档数据时,通过动态映射的方式自动生成索引类型索引也可以手动创建,通过手动创建,可以控制主分片数目、分析器和类型映射。 PUT /my_index { "se...
  • pandas新增一列相同值 df['C1'] = df['C']#复制一... df['C1'] = pd.to_datetime(df['C1']) # 将object类型时间转化为datetime64[ns] df['C1'] = df['C1'].map(lambda x: x.strftime('%Y')) # 设置显示‘年’ df[...
  • 为时间字段加索引

    千次阅读 2020-03-24 18:29:58
    文章目录为时间字段加索引(待更进)一、问题1、描述:日期不一致2、原因:时区不同3、解决方法: 时区修改二、datetime和varchar类型效率比较1、背景2、开始三、时间字段加索引1、聚集索引2、非聚集索引3、时间字段...
  • 假设目前已经引入了 pandas,同时也拥有 pandas 的 DataFrame 类型数据。import pandas as pd 数据集如下df.head(3) date open close high low volume code 0 2006-12-18 3.905 3.886 3.943 3.867 1...
  • 几天前听培训课时听老师随口问了下一个关于索引的知识,然后简单的说了几句,顿时感觉我对索引的认识还只是最肤浅的阶段,今天特意找了找,找到四篇文章,感觉不错,特转载分享。 第一篇深入浅出索引结构  ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 167,118
精华内容 66,847
关键字:

日期类型索引