精华内容
下载资源
问答
  • mysql索引类型
    千次阅读
    2021-12-01 20:50:39

    问题1:mysql索引类型normal,unique,full text的区别是什么?

    normal:表示普通索引

    unique:表示唯一的,不允许重复的索引,如果该字段信息保证不会重复例如身份证号用作索引时,可设置为unique

    full textl: 表示 全文搜索的索引。 FULLTEXT 用于搜索很长一篇文章的时候,效果最好。用在比较短的文本,如果就一两行字的,普通的 INDEX 也可以。

    总结,索引的类别由建立索引的字段内容特性来决定,通常normal最常见。

    问题2:在实际操作过程中,应该选取表中哪些字段作为索引?

    为了使索引的使用效率更高,在创建索引时,必须考虑在哪些字段上创建索引和创建什么类型的索引,有7大原则:

    1.选择唯一性索引

    2.为经常需要排序、分组和联合操作的字段建立索引

    3.为常作为查询条件的字段建立索引

    4.限制索引的数目

    5.尽量使用数据量少的索引

    6.尽量使用前缀来索引

    7.删除不再使用或者很少使用的索引

    一、 MySQL: 索引以B树格式保存  Memory存储引擎可以选择Hash或BTree索引,Hash索引只能用于=或<=>的等式比较。

    1、普通索引:create index on Tablename(列的列表)

    alter table TableName add index (列的列表)

    create table TableName([…], index [IndexName] (列的列表)

    2、唯一性索引:create unique index

    alter … add unique

    主键:一种唯一性索引,必须指定为primary key

    3、全文索引:从3.23.23版开始支持全文索引和全文检索,FULLTEXT,

    可以在char、varchar或text类型的列上创建。

    4、单列索引、多列索引:

    多个单列索引与单个多列索引的查询效果不同,因为:

    执行查询时,MySQL只能使用一个索引,会从多个索引中选择一个限制最为严格的索引。

    5、最左前缀(Leftmost Prefixing):多列索引,例如:fname_lname_age索引,以下的搜索条件MySQL都将使用

    fname_lname_age索引:firstname,lastname,age;firstname,lastname;firstname,其他情况将不使用。

    二、根据sql查询语句确定创建哪种类型的索引,如何优化查询  选择索引列:

    a.性能优化过程中,选择在哪个列上创建索引是最重要的步骤之一。可以考虑使用索引的主要有

    两种类型的列:在where子句中出现的列,在join子句中出现的列。

    b.考虑列中值的分布,索引的列的基数越大,索引的效果越好。

    c.使用短索引,如果对字符串列进行索引,应该指定一个前缀长度,可节省大量索引空间,提升查询速度。

    d.利用最左前缀

    e.不要过度索引,只保持所需的索引。每个额外的索引都要占用额外的磁盘空间,并降低写操作的性能。

    在修改表的内容时,索引必须进行更新,有时可能需要重构,因此,索引越多,所花的时间越长。

    MySQL只对一下操作符才使用索引:,>=,between,in,

    以及某些时候的like(不以通配符%或_开头的情形)。

    mysql 索引分类

    在数据库表中,对字段建立索引可以大大提高查询速度。通过善用这些索引,可以令 MySQL的查询和运行更加高效。索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是很重要的。下面介绍几种常见的MySQL索引类型。

    1、普通型索引这是最基本的索引类型,而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建:

    (1)创建索引,例如CREATE INDEX 索引的名字 ON tablename (列名1,列名2,…);

    (2)修改表,例如ALTER TABLE tablename ADD INDEX 索引的名字 (列名1,列名2,…);

    (3)创建表的时候指定索引,例如CREATE TABLE tablename ( […], INDEX 索引的名字 (列名1,列名

    2,…) );

    2、唯一索引这种索引和前面的“普通索引”基本相同,但有一个区别:索引列的所有值都只能出现一次,即必须唯一。唯一性索引可以用以下几种方式创建:

    (1)创建索引,例如CREATE UNIQUE INDEX 索引的名字 ON tablename (列的列表);

    (2)修改表,例如ALTER TABLE tablename ADD UNIQUE 索引的名字 (列的列表);

    (3)创建表的时候指定索引,例如CREATE TABLE tablename ( […], UNIQUE 索引的名字 (列的列

    表) );

    3、主键主键是一种唯一性索引,但它必须指定为“PRIMARY KEY”。如果你曾经用过AUTO_INCREMENT类型的列,你可能已经熟悉主键之类的概念了。主键一般在创建表的时候指定,例如“CREATE TABLE tablename ( […], PRIMARY KEY (列的列表) ); ”。但是,我们也可以通过修改表的方式加入主键,例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ”。每个表只能有一个主键。 (主键相当于聚合索引,是查找最快的索引)

    4、单列索引和多列索引索引可以是单列索引,也可以是多列索引。

    (1)单列索引就是常用的一个列字段的索引,常见的索引。

    (2)多列索引就是含有多个列字段的索引

    alter table student add index sy(name,age,score);

    索引sy就为多列索引,多列索引在以下几中情况下才能有效:

    select * from student where name=‘jia’ and age>=‘12’ //where条件中含有索引的首列字段和

    第二个字段

    select * from student where name=‘jia’ //where条件中只含有首列字段

    select * from student where name=‘jia’ and score<60//where条件中含有首列字段和第三个字

    总结:多列索引只有在where条件中含有索引中的首列字段时才有效

    5、选择索引列应该怎样选择索引列,首先要看查询条件,一般将查询条件中的列作为索引

    更多相关内容
  • MYSQL 索引类型

    千次阅读 多人点赞 2021-02-18 12:29:44
    一、索引类型 在数据库表中,对字段建立索引可以大大提高查询速度。假如我们创建了一个 mytable表 代码如下: CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL ); 我们随机向里面插入了10000...

    一、索引类型

    在数据库表中,对字段建立索引可以大大提高查询速度。假如我们创建了一个 mytable表

    代码如下:

    CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL );

    我们随机向里面插入了10000条记录,其中有一条:5555, admin。

    在查找username="admin"的记录 SELECT * FROM mytable WHERE username=‘admin’;时,如果在username上已经建立了索引,MySQL无须任何扫描,即准确可找到该记录。相反,MySQL会扫描所有记录,即要查询10000条记录。

    索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索包含多个列。

    MySQL索引类型包括:

    1、普通索引

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

    1).创建索引

    代码如下:

    CREATE INDEX indexName ON mytable(username(length));

    如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length,下同。

    2).修改表结构

    代码如下:

    ALTER mytable ADD INDEX [indexName] ON (username(length)) –
    创建表的时候直接指定

    CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT
    NULL, INDEX [indexName] (username(length)) );

    – 删除索引的语法:

    DROP INDEX [indexName] ON mytable;

    2、唯一索引

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

    代码如下:

    CREATE UNIQUE INDEX indexName ON mytable(username(length))
    – 修改表结构 ALTER mytable ADD UNIQUE [indexName] ON (username(length))
    – 创建表的时候直接指定 CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );

    3、主键索引

    它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候同时创建主键索引:

    代码如下:

    CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) );

    当然也可以用 ALTER 命令。记住:一个表只能有一个主键。

    4、组合索引

    为了形象地对比单列索引和组合索引,为表添加多个字段:

    代码如下:

    CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL );

    为了进一步榨取MySQL的效率,就要考虑建立组合索引。就是将 name, city, age建到一个索引里:

    代码如下:

    ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);

    二、什么情况下有索引,但用不上?

    索引并不是时时都会生效的,比如以下几种情况,将导致索引失效:

    如果条件中有or,即使其中有部分条件带索引也不会使用(这也是为什么尽量少用or的原因),例子中user_id无索引

    在这里插入图片描述

    注意:要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引

    2.对于多列索引,不是使用的第一部分,则不会使用索引

    3.like查询是以%开头

    在这里插入图片描述

    4.存在索引列的数据类型隐形转换,则用不上索引,比如列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引

    在这里插入图片描述

    5.where 子句里对索引列上有数学运算,用不上索引

    在这里插入图片描述

    6.where 子句里对有索引列使用函数,用不上索引

    在这里插入图片描述
    7.如果mysql估计使用全表扫描要比使用索引快,则不使用索引。

    比如数据量极少的表

    三、什么情况下不推荐使用索引?

    1)数据唯一性差(一个字段的取值只有几种时)的字段不要使用索引

    比如性别,只有两种可能数据。意味着索引的二叉树级别少,多是平级。这样的二叉树查找无异于全表扫描。

    2) 频繁更新的字段不要使用索引

    比如logincount登录次数,频繁变化导致索引也频繁变化,增大数据库工作量,降低效率。

    3)字段不在where语句出现时不要添加索引,如果where后含IS NULL /IS NOT NULL/ like ‘%输入符%’等条件,不建议使用索引

    只有在where语句出现,mysql才会去使用索引

    4) where 子句里对索引列使用不等于(<>),使用索引效果一般

    展开全文
  • 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索引类型normal,unique,full text的区别是什么? normal:表示普通索引 CREATE INDEX index_name ON table(column);ALTER TABLE table_name ADD INDEX index_name (column); unique:表示唯一的,不允许...

    mysql索引类型normal,unique,full text的区别是什么?

    在这里插入图片描述

    normal:表示普通索引

    CREATE INDEX index_name ON table(column);ALTER TABLE table_name ADD INDEX index_name (column);
    

    unique:表示唯一的,不允许重复的索引,如果该字段信息保证不会重复例如身份证号用作索引时,可设置为unique

    CREATE UNIQUE INDEX indexName ON table(column);
    ALTER TABLE table_name ADD UNIQUE indexName (column);
    

    full textl: 表示 全文搜索的索引。 FULLTEXT 用于搜索很长一篇文章的时候,效果最好。用在比较短的文本,如果就一两行字的,普通的 INDEX 也可以。

    ALTER TABLE article ADD FULLTEXT index_content(content);CREATE FULLTEXT INDEX index_content ON article(content);
    
    

    总结,索引的类别由建立索引的字段内容特性来决定,通常normal最常见。

    在实际操作过程中,应该选取表中哪些字段作为索引?

    为了使索引的使用效率更高,在创建索引时,必须考虑在哪些字段上创建索引和创建什么类型的索引,有7大原则:

    1.选择唯一性索引
    2.为经常需要排序、分组和联合操作的字段建立索引
    3.为常作为查询条件的字段建立索引
    4.限制索引的数目
    5.尽量使用数据量少的索引
    6.尽量使用前缀来索引
    7.删除不再使用或者很少使用的索引

    索引的缺点

    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.不要在列上进行运算,避免在条件查询的列上使用函数

    这将导致索引失效而进行全表扫描*

    展开全文
  • mysql索引类型

    千次阅读 2021-03-01 09:49:04
    MySQL目前主要有以下几种索引类型: 1.普通索引 2.唯一索引 3.主键索引 4.组合索引 5.全文索引 二、语句 CREATE TABLE table_name[col_name data type] [unique|fulltext][index|key][index_name](col_name[length])...
  • MySQL索引类型大汇总

    2021-01-19 15:02:52
    本文主要介绍了7种不同类型的MySQL索引类型。在MySQL数据库表中,对字段进行建立索引是可以大幅度的提高其实际查询速度。通过对这些索引的巧妙的运用,我们可以令MySQL的查询和运行更加高效。索引是快速搜索的关键。...
  • 主要介绍了Mysql索引类型与基本用法,结合实例形式分析了Mysql索引类型中普通索引、唯一索引、主键索引、组合索引、全文索引基本概念、原理与使用方法,需要的朋友可以参考下
  • MySQL索引类型(type)分析

    千次阅读 2021-01-20 19:10:31
    type索引类型:ststem > const > eq_ref > ref > range > index > all优化级别从左往右递减,没有索引的一般为’all’,需要对type进行优化前提是有索引。其中’system’和’const’只是理想型,...
  • Mysql索引类型

    千次阅读 2022-03-21 13:18:44
    索引分类 1.按功能划分 普通索引 就是最最基础的索引,这种索引没有任何的约束作用,它存在的主要意义就是提高查询效率。 唯一性索引 ...在普通索引的基础上增加了数据唯一性的...其实我们很少在 MySQL 中用,如果项目
  • mysql索引类型和索引方式

    万次阅读 2018-11-24 12:28:52
    MySQL中,通常我们所指的索引类型,有以下几种: 主键索引(PRIMARY KEY) 也简称主键。它可以提高查询效率,并提供唯一性约束。一张表中只能有一个主键。被标志为自动增长的字段一定是主键,但主键不一定是自动...
  • mysql索引类型:FULLTEXT、NORMAL、SPATIAL、UNIQUE的详细介绍Normal 普通索引Unique 唯一索引Full Text 全文索引SPATIAL 空间索引btree索引和hash索引的区别在实际操作过程中,应该选取表中哪些字段作为索引?...
  • Mysql索引类型以及适用场景

    千次阅读 2018-07-31 18:25:33
    1.普通索引  最基本的索引,没有任何限制  2.唯一索引  索引列的值必须唯一,但允许有空值  3.主键索引  主键索引属于一种特殊的唯一索引,不允许有空值  4.单列索引  单个多列索引(组合索引)效率高于多个...
  • mysql 常用索引类型

    千次阅读 2021-01-19 08:26:59
    一、索引的类型mysql索引的四种类型:主键索引、唯一索引、普通索引和全文索引。通过给字段添加索引可以提高数据的读取速度,提高项目的并发能力和抗压能力。索引优化时mysql中的一种优化方式。索引的作用相当于图书...
  • Mysql 索引类型+索引方法

    千次阅读 2021-01-19 14:25:17
    要想用到这种类型索引,只要是索引,或者某个联合索引的一部分,mysql都可能会采用index类型的方式扫描。但是呢,缺点是效率不高; ref:这种类型表示mysql会根据特定的算法快速查找到某个符合条件的索引,不会对...
  • 看一下MySQL索引类型

    万次阅读 2020-10-14 15:51:58
    MySQL目前主要有以下几种索引类型: 1.普通索引 2.唯一索引 3.主键索引 4.组合索引 5.全文索引 二、语句 CREATE TABLE table_name[col_name data type] [unique|fulltext][index|key][index_name](col_name...
  • MySQL索引的概念以及七种索引类型介绍。
  • mysql索引类型以及适用场景

    千次阅读 2017-02-08 15:32:20
    1.普通索引 最基本的索引,没有任何限制 2.唯一索引 索引列的值必须唯一,但允许有空值 3.主键索引 主键索引属于一种特殊的唯一索引,不允许有空值 4.单列索引 单个多列索引(组合索引)效率高于多个单列...
  • mysql 索引类型以及使用场景

    万次阅读 2018-07-10 10:44:11
    关于MySQL索引的好处,如果正确合理设计并且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。对于没有索引的表,单表查询可能几十万数据就是瓶颈,而通常大型网站单日就可能...
  • 之前松哥在前面的文章中介绍 MySQL索引时,有小伙伴表示被概念搞晕了,主键索引、非主键索引、聚簇索引、非聚簇索引、二级索引、辅助索引等等,今天咱们就来捋一捋这些概念。 1. 按照功能划分 按照功能来划分,...
  • 问题1:mysql索引类型normal,unique,full text的区别是什么? normal:表示普通索引 unique:表示唯一的,不允许重复的索引,如果该字段信息保证不会重复例如身份证号用作索引时,可设置为unique full text: ...
  •  关于MySQL索引的好处,如果正确合理设计并且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。对于没有索引的表,单表查询可能几十万数据就是瓶颈,而通常大型网站单日就...
  • MySQL索引类型

    千次阅读 2018-05-04 21:35:18
    MySQL支持哪几种索引类型呢?从不同的角度讨论不一样,这篇文章是从数据结构的角度去分类,还有物理和逻辑角度只做简单说明。从物理存储角度1、聚集索引(clustered index)2、非聚集索引(non-clustered index)从...
  • Mysql常见的索引类型

    千次阅读 2021-01-30 22:07:19
    1、Mysql常见的索引唯一索引主键索引全文索引普通索引2、索引的创建类型2.1 利用Alter table 来创建索引1、 Alter table table_name add index index_name ( column_name );2、Alter table table_name add unique ...
  • mysql索引类型及索引方法

    千次阅读 2018-06-20 14:13:15
    mysql索引类型normal,unique,full text的区别是什么?normal:表示普通索引unique:表示唯一的,不允许重复的索引,如果该字段信息保证不会重复例如身份证号用作索引时,可设置为uniquefull textl: 表示 全文搜索...
  • mysql索引有哪些类型

    千次阅读 2021-08-20 14:02:15
    MySQL目前主要有的索引类型为:普通索引、唯一索引、主键索引、组合索引、全文索引。 通过给字段添加索引可以提高数据的读取速度,提高项目的并发能力和抗压能力。索引优化时mysql中的一种优化方式。索引的作用相当...
  • 下面介绍几种常见的MySQL索引类型。 在数据库表中,对字段建立索引可以大大提高查询速度。假如我们创建了一个 mytable表: CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL ); 我们...
  • mysql系列:全网最全索引类型汇总

    千次阅读 多人点赞 2020-09-26 01:45:06
    今天和大家一起总结mysql中有哪些索引类型。 一、mysql中有哪些索引类型? 聚簇索引 (Clustered Index) 非聚簇索引 主键索引(PRIMARY KEY) 辅助索引(Secondary Indexes) HASH索引 BTREE索引 T-TREE索引 R-Tree索引 ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 312,928
精华内容 125,171
关键字:

mysql索引类型

mysql 订阅