精华内容
下载资源
问答
  • 一个表建立多个索引
    千次阅读
    2022-02-19 20:39:47

    往InnoDB表新增数据时,都会基于主键给自动建立聚簇索引。 随着我们不停的在表里插入数据,会不停的在数据页里插入数据。一个数据页放满后,就会分裂成多个数据页,这时就需要索引页去指向各个数据页。

    若数据页太多,那么索引页里里的数据页指针也就会太多了,索引页也必然会放满的,于是索引页也会分裂,再形成更上层的索引页。

    默认MySQL建立的聚簇索引都是基于主键的值来组织索引,聚簇索引的叶子节点都是数据页,里面放的就是插入的一行行完整数据。

    • 数据页/索引页里面的记录都是组成一个单向链表,按数据大小有序排列
    • 数据页/索引页互相之间组成双向链表,也都按数据大小有序排列

    所以B+索引树是个完全有序的数据结构,无论是页内还是页间,这才能让查找数据时,直接从根节点开始按照数据值大小一层一层往下找,效率很高。

    MySQL的表里建立一些字段对应的索引,有啥好处?

    可直接根据某个字段的索引B+树来查找数据,无需全表搜索,性能提升很高。但也有坏处:

    空间

    每个B+树都要占用很多磁盘空间,索引太多,就很费磁盘空间。

    搞很多索引,增删改查时,每次都需要维护各个索引的数据有序性,因为每个索引B+树都要求页内是按照值大小排序的,页之间也是有序的:

    下 一 个 页 的 所 有 值 必 须 > 上 一 个 页 的 所 有 值 下一个页的所有值必须>上一个页的所有值

    所以不停增删改查,必然会导致各个数据页之间的值大小可能会没有顺序,比如下一个数据页里插入了一个比较小的值,居然比上一个数据页的值要小!此时就没办法了,只能进行数据页的挪动,维护页之间的顺序。

    时间

    不停插入数据,各索引的数据页就要不停分裂,不停增加新的索引页,这过程都很耗时。

    所以你要是一个表里搞的索引太多,很可能导致你的增删改的速度较差,也许查询速度确实是可以提高,但是增删改就会受到影响,因此不建议一个表里搞的索引太多的!

    更多相关内容
  • mysql一个表可以创建多个唯一索引

    千次阅读 2018-09-06 11:44:00
    mysql一个表是 可以有多个 唯一索引 的。 如果能确定某个数据列将只包含彼此各不相同的值,在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个 唯一索引 。 转载于:...

    mysql一个表是可以有多个唯一索引的。

    如果能确定某个数据列将只包含彼此各不相同的值,在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引

    转载于:https://my.oschina.net/zjllovecode/blog/1973479

    展开全文
  • 、可以创建多个索引嘛? 可以创建多个索引的。 需要开发人员根据实际的用途以及数据库中记录的情况,来进行判断。 通常来说,的索引越多,其查询的速度也就越快。但是,的insert/update速度则会降低。这主要是...

    一、可以创建多个索引嘛?

    可以创建多个索引的。
    需要开发人员根据实际的用途以及数据库中记录的情况,来进行判断。

    通常来说,表的索引越多,其查询的速度也就越快。但是,表的insert/update速度则会降低。这主要是因为,在更新记录的同时需要更新相关的索引信息。为此,到底在表中创建多少索引合适,就需要在这个更新速度与查询速度之间取得一个均衡点。

    如对于一些数据仓库数据库系统,其主要用来进行查询。相关的记录往往是在数据库初始化的时候倒入。此时,设置的索引多一点,可以提高数据库的查询性能。同时因为记录不怎么更新,所以索引比较多的情况下,也不会影响到更新的速度。即使在起初的时候需要导入大量的数据,此时也可以先将索引禁用掉。等到数据导入完毕后,再启用索引。可以通过这种方式来减少索引对数据更新的影响。

    如果那些表中经常需要更新记录,如一些事务型的应用系统,数据更新操作是家常便饭的事情。此时如果在一张表中建立过多的索引,则会影响到更新的速度。由于更新操作比较频繁,所以对其的负面影响,要比查询效率提升要大的多。此时就需要限制索引的数量,只在一些必要的字段上建立索引。

    二、创建索引

    1.ALTER TABLE

    ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

    ALTER TABLE table_name ADD INDEX index_name (column_list)

    ALTER TABLE table_name ADD UNIQUE (column_list)

    ALTER TABLE table_name ADD PRIMARY KEY (column_list)

    其中table_name是要增加索引的表名,column_list指出对哪些列进行索引,多列时各列之间用逗号分隔。索引名index_name可选,缺省时,MySQL将根据第一个索引列赋一个名称。另外,ALTER TABLE允许在单个语句中更改多个表,因此可以在同时创建多个索引。

    2.CREATE INDEX

    CREATE INDEX可对表增加普通索引或UNIQUE索引。

    CREATE INDEX index_name ON table_name (column_list)

    CREATE UNIQUE INDEX index_name ON table_name (column_list)

    table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义,索引名不可选。另外,不能用CREATE INDEX语句创建PRIMARY KEY索引。

    三、索引类型

    在创建索引时,可以规定索引能否包含重复值。如果不包含,则索引应该创建为PRIMARY KEY或UNIQUE索引。对于单列惟一性索引,这保证单列不包含重复的值。对于多列惟一性索引,保证多个值的组合不重复。

    PRIMARY KEY索引和UNIQUE索引非常类似。
    事实上,PRIMARY KEY索引仅是一个具有名称PRIMARY的UNIQUE索引。这表示一个表只能包含一个PRIMARY KEY,因为一个表中不可能具有两个同名的索引。

    下面的SQL语句对students表在sid上添加PRIMARY KEY索引。
    ALTER TABLE students ADD PRIMARY KEY (sid)

    四、删除索引

    可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句,DROP INDEX可以在ALTER TABLE内部作为一条语句处理,语法如下。

    DROP INDEX index_name ON talbe_name

    ALTER TABLE table_name DROP INDEX index_name

    ALTER TABLE table_name DROP PRIMARY KEY

    其中,前两条语句是等价的,删除掉table_name中的索引index_name。

    第3条语句只在删除PRIMARY KEY索引时使用,因为一个表只可能有一个PRIMARY KEY索引,因此不需要指定索引名。如果没有创建PRIMARY KEY索引,但表具有一个或多个UNIQUE索引,则MySQL将删除第一个UNIQUE索引。

    如果从表中删除了某列,则索引会受到影响。对于多列组合的索引,如果删除其中的某列,则该列也会从索引中删除。如果删除组成索引的所有列,则整个索引将被删除。

    五、单一索引和复合索引

    1.单一索引:
    单一索引是指索引列为一列的情况,即新建索引的语句只实施在一列上;

    2.复合索引:
    复合索引也叫组合索引,用户可以在多个列上建立索引,这种索引叫做复合索引(组合索引)。

    复合索引在数据库操作期间所需的开销更小,可以代替多个单一索引;

    3.使用:

    创建复合索引 :
    CREATE INDEX columnId ON table1(col1,col2,col3) ;

    查询语句:
    select * from table1 where col1= A and col2= B and col3 = C
    这时候查询优化器,不在扫描表了,而是直接从索引中拿数据,因为索引中有这些数据,这叫覆盖式查询,这样的查询速度非常快;

    4.注意事项:

    (1)对于复合索引,在查询使用时,最好将条件顺序按找索引的顺序,这样效率最高;
    select * from table1 where col1=A AND col2=B AND col3=D
    如果使用
    where col2=B AND col1=A
    或者
    where col2=B
    将不会使用索引。

    (2)何时是用复合索引 根据where条件建索引是极其重要的一个原则; 注意不要过多用索引,否则对表更新的效率有很大的影响,因为在操作表的时候要化大量时间花在创建索引中

    (3)复合索引会替代单一索引么 如果索引满足窄索引的情况下可以建立复合索引,这样可以节约空间和时间

    (4)对一张表来说,如果有一个复合索引 on (col1,col2),就没有必要同时建立一个单索引 on col1;

    如果查询条件需要,可以在已有单索引 on col1的情况下,添加复合索引on (col1,col2),对于效率有一定的提高;

    同时建立多字段(包含5、6个字段)的复合索引没有特别多的好处,相对而言,建立多个窄字段(仅包含一个,或顶多2个字段)的索引可以达到更好的效率和灵活性。

    六、创建索引需要注意的问题

    1.创建索引
    对于查询占主要的应用来说,索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的,或者说没有添加更为有效的索引导致。如果不加

    索引的话,那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描,如果一张表的数据量很大而符合条件的结果又很少,那么不加索引会引起致命的性能下降。
    但是也不是什么情况都非得建索引不可,比如性别可能就只有两个值,建索引不仅没什么优势,还会影响到更新速度,这被称为过度索引。

    2.复合索引
    比如有一条语句是这样的:select * from users where area=’beijing’ and age=22;

    如果我们是在area和age上分别创建单个索引的话,由于mysql查询每次只能使用一个索引,所以虽然这样已经相对不做索引时全表扫描提高了很多效

    率,但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age,salary)的复合索引,那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引,这被称为最佳左前缀特性。
    因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边,依次递减。

    3.使用短索引
    对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的 列,如果在前10 个或20 个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

    4.排序的索引问题
    mysql查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。

    5.不使用NOT IN和操作
    NOT IN和操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替,id3则可使用id>3 or id

    七、索引失效的情况

    1.当语句中有or时:select * from user where name = ‘aaa’ or age = 18;
    2.当语句索引like带%时:select * from user where name like %’aaa’;
    3.如果列类型是字符串,一定要将数据用引号引起来:
    Select * from user where name = 123;
    4.在索引上进行操作时:比如age为索引,select * from user where age – 1 > 11;
    5.Mysql在使用全表扫描比使用索引扫描快时,则不会走索引
    6.只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
    7.条件中包含&lt、&gt(<>)

    八、索引的数据结构为什么是B+数而不是hashmap或者B树?

    1.B+树的磁盘读写代价更低
    B+树的内部节点并没有指向关键字具体信息的指针,因此其内部节点相对B树更小,如果把所有同一内部节点的关键字存放在同一盘块中,那么盘块所能容纳的关键字数量也越多,一次性读入内存的需要查找的关键字也就越多,相对IO读写次数就降低了。

    B+树的查询效率更加稳定
    由于非叶子节点只存索引信息,而没有真正的数据信息,所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同,导致每一个数据的查询效率相当。

    B+树更加适合在区间查询的情况
    由于B+树的数据都存储在叶子结点中,非叶子结点均为索引,只需要扫一遍叶子结点即可得到所有数据信息,但是B树因为其非叶子结点同样存储着数据,我们要找到具体的数据,需要进行一次中序遍历按序来扫,所以B树更加适合在区间查询的情况,通常B+树用于数据库索引。

    展开全文
  • SQL - 多表建立索引

    千次阅读 2019-05-09 14:38:48
    card 为外键, 关联2 个表 案例: type 是ALL , row 20 性能差 加索引, 先给book 表的card 字段加索引 book 是右表, 加了索引。 所以性能比class 表高 将右表book 的index 删除, 将index 加在坐标class上 ...

    1.建表
    在这里插入图片描述

    查看class 表的数据
    在这里插入图片描述
    查看 book 表单的数据
    在这里插入图片描述
    card 为外键, 关联2 个表
    在这里插入图片描述

    案例:

    在这里插入图片描述

    type 是ALL , row 20 性能差
    在这里插入图片描述
    加索引, 先给book 表的card 字段加索引
    在这里插入图片描述
    book 是右表, 加了索引。 所以性能比class 表高
    在这里插入图片描述

    将右表book 的index 删除, 将index 加在坐标class上
    在这里插入图片描述
    性能如下
    不如将index 在在右表book 上

    在这里插入图片描述
    在这里插入图片描述
    left join 将索引建立在 右表上, 因为左表一定都有, 右表按索引查询
    反之亦然

    三个表建立索引

    建立第三个表
    在这里插入图片描述

    在这里插入图片描述

    案例:
    三表联查
    在这里插入图片描述

    在这里插入图片描述
    性能如下
    在这里插入图片描述

    开始建立索引
    将两个右侧的表加上索引
    在这里插入图片描述
    优化后性能如下:
    在这里插入图片描述

    在这里插入图片描述

    结论
    在这里插入图片描述

    展开全文
  • Oracle创建索引的基本规则

    千次阅读 2021-05-03 05:20:03
    在WHERE子句中最频繁使用的字段联接语句中的联接字段选择高选择性的字段(如果很少的字段拥有相同值,即有很独特值,则选择性很好)Oracle在UNIQUE和主键字段上自动建立索引在选择性很差的字段上建索引只有在这字段...
  • mysql如何创建多个联合索引

    千次阅读 2021-01-18 19:09:07
    mysql创建多个联合索引的方法:可以使用CREATE INDEX或ALTER TABLE语句来创建。在创建联合索引时,应该仔细考虑列的顺序。MySQL中索引规则:1、需要加索引的字段,要在where条件中2、数据量少的字段不需要加索引3、...
  • mysql中一个表可以有几个唯一索引

    千次阅读 2021-09-04 04:39:38
    mysql中一个表可以有多个唯一索引。 如果能确定某个数据列只包含彼此各不相同的值,那么在为这个数据列创建索引时,就应该使用关键字UNIQUE把它定义为一个唯一索引。 内容 如果能确定某个数据列将只包含彼此各不...
  • MySQL索引篇,创建表创建索引

    千次阅读 2021-01-27 02:34:24
    索引对于MySQL数据库查询速度具有无可取代的作用,一个合适的索引能给数据查询的效率带来巨大的提升,本文来给大家讲讲如何创建索引索引的最佳创建方式是在建表的时候就确定好要索引的字段并建立索引索引的...
  • 多表关联如何建立索引

    千次阅读 2019-10-22 11:23:16
    我是用的三张表进行关联的,大两小。下面看一下三张表的具体结构。 三张图对应三张表,然后下面是我写的查询sql select a.*, b.*, c.* from statjiankong_etl a left join ibnr b on a.anadate = b....
  • 2、查询时使用联合索引一个字段,如果这个字段在联合索引中所有字段的第一个,那就会用到索引,否则就无法使用到索引。 3、联合索引IDX(字段A,字段B,字段C,字段D),当仅使用字段A查询时,索引IDX就会使用到;如果...
  • MySQL创建数据索引

    千次阅读 2021-01-18 19:38:01
    三、数据字段属性:1、unsigned 可以让空间增加倍,如果不希望在字段中插入负值就使用此属性,另外只能用在数值型字段。2、zerofill 只能用在数值型字段,前导0(例如id int(5) zerofill,如果插入数据为11,会自动...
  • 关于MySQL的最多能建多少个索引

    千次阅读 2021-01-18 21:18:31
    在某个网站上,忽然看到了个简短的问答“在MySQL数据库单个普通上,最多可以创建多少个索引?”老实说,看到这个问题的瞬间我是有点懵的状态,我原本只只知道Innodb引擎的,最多只有1017列(至于为什么不是1024,...
  • 最近工作一直做的是数据迁移方面的工作,在数据库中核对索引时发现mysql中的对一个字段创建了两个不同名字的索引,这个也不是复合索引,只是一个普通的索引。当初不明白如何这样做。这样是不是有问题呢,经过了解...
  • 面试题:一个表一般建多少索引

    千次阅读 2020-11-12 15:34:50
    一个表哪怕只做查询操作,索引也不宜过多, 因为所以太会导致查询选择索引出现开销(当然指定了索引可以最低限度的降低开销). 从我自己的实际工作情况来看, 所以得建立要全局考虑,就是不要仅仅只考虑一张表的索引怎么...
  •  最近在一次面试中,讨论了一个这样的问题:主键和索引有什么区别?当时我的回答是这样的:“主键就是加了唯一性约束的聚集索引。” “你确定你所说的是对的?” 面试官反问到。 “应该是对的。” 我不加思索地回答...
  • 怎么创建一个索引

    千次阅读 2021-02-21 12:52:35
    一个索引的名称,第二个是索引的列,比如我们是要对id 创建索引还是对name创建索引。后面两个很重要,一个索引类型。 在InnoDB 里面,索引类型有三种,普通索引、唯一索引(主键索引是特殊的唯一索引)、全文...
  • 索引使用简介、 关于索引的知识要写出运行效率高的sql,需要对索引的机制...对于dml操作比较频繁的索引的个数不宜太。2、 什么样的列需要建索引?经常用于查询、排序和分组的列(即经常在where、order或grou...
  • 发现mysql一个表可以有几个唯一索引

    万次阅读 2018-04-26 09:07:44
    mysql一个表可以有多个唯一索引。如果能确定某个数据列将只包含彼此各不相同的值,在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。这么做的好处:一是简化了MySQL对这个索引的管理工作,...
  • 在已经存在的创建索引

    千次阅读 2021-02-03 20:03:22
    在已经存在的中,可以直接为上的一个或几个字段创建索引。基本形式如下:CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEX索引名ON表名(属性名[(长度)][ASC|DESC]);其中,UNIQUE是可选参数,表示索引为唯一性索引;...
  • mysql索引详解

    千次阅读 2021-01-18 18:50:36
    创建多索引 在t_userid,userName,email字段上创建多索引(该只有此索引): alter table t_user add index USER_INDEX(id, userName, email); 能够利用该索引的查询 符合leftmost index prefixes原则的查询 ...
  • MYSQL 引擎innodb ,设置了唯一索引的列值 允许 多个NULL值存在。 示例: 字段 userCardNum 添加了唯一索引 证实是允许存在的多个NULL值数据的: 原由: 因为这里 NULL 的定义 ,是指 未知值。 所以...
  • MySQL提供了多种在单列或列上创建索引的方法:种是在创建表时制定索引列;另种是使用ALTER TABLE 语句在已存在的创建索引,或者使用CREATE_INDEX 在已存在的创建索引。 1. 在 创建表创建【普通】...
  • sql多表联查,索引

    千次阅读 2021-12-27 14:16:52
    sql多表联查,索引
  • oracle创建分区以及索引

    千次阅读 2022-04-15 17:32:55
    对于10gR2而言,ORACLE对于分区方式其实就是将分段存储,一般普通表格是一个段存储,而分区会分成多个段,所以查找数据过程都是先定位根据查询条件定位分区范围,即数据在那个分区或那几个内部,然后在分区...
  • mysql创建单个和联合索引

    千次阅读 2021-02-07 03:34:09
    进入 首先创建一个表:create table t1 (id int primary key,username varchar(20),password varchar(20)); 创建单个索引的语法:create index 索引名 on 表名(字段名) 索引名一般是:表名_字段欢迎进入Linux社区...
  • 背景: 为了提高数据库效率,建索引是家常便饭;那么...
  • 全文索引----创建多表solr索引

    万次阅读 热门讨论 2016-05-07 21:10:39
     我们在使用solr作为索引服务器时,通常会将多个表多个字段作为联合索引,对多个表快速的数据查询也是solr服务器高效率的体现。这片文章介绍下如何基于多个数据表创建索引。   无关联多表创建索引  1.1 ...
  • 1.150000E+08 total rows at 1m30s, row rate 1990897.15/sec (period), row rate 1941106.77/sec (overall), 1.747000E+08 total rows 亿条数据,只需要 1 分钟左右。 综合看,如果用 HDD,瓶颈大概率在硬盘...
  • mysql 如何创建索引?mysql 如何创建索引呢,这个其实很简单 create index或者为己有字段增加索引 ALTER TABLE `table_name...特别是当数据量非常大,查询涉及多个表时,使用索引往往能使查询速度加快成千上万倍。mys...
  • mysql 建立多个索引,加快查询速度

    千次阅读 2016-08-03 10:45:08
    最近有一个接口,客户端接到...在mysql里用sql查询了一下,发现是0.64s,请龙哥帮忙看了一下,他的建议是建立多个索引,加快查询速度。 案例1: 一. 获取查询情况 explain sql语句龙哥,发现没有索引 二.查

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,195,429
精华内容 478,171
关键字:

一个表建立多个索引