精华内容
下载资源
问答
  • 一个表只能创建一个索引
    千次阅读
    2021-09-04 04:39:38

    前言

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

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

    内容

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

    优点:

    1. 简化了MySQL对这个索引的管理工作,这个索引也因此而变得更有效率。
    2. MySQL会在有新记录插入数据表时,自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了;如果是,MySQL将拒绝插入那条新记录。

    总结

    也就是说,唯一索引可以保证数据记录的唯一性。事实上,在许多场合,人们创建唯一索引的目的往往不是为了提高访问速度,而只是为了避免数据出现重复。

    更多相关内容
  • MySQL创建数据索引

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

    除非注明,文章均为易水寒原创,版权属于易水寒博客,转载请注明出处,谢谢。

    三、数据字段属性:

    1、unsigned 可以让空间增加一倍,如果不希望在字段中插入负值就使用此属性,另外只能用在数值型字段。

    2、zerofill 只能用在数值型字段,前导0(例如id int(5) zerofill,如果插入数据为11,会自动存储为00011),该字段自动应用unsigned。

    3、AUTO_INCREMENT 只能是整数,数据每增加一条就会自动增1,字段的值不允许重复。

    4、NULL和NOT NULL 控制字段插入数据是否可以为空值。

    5、default 缺省值。在某个表里,某个字段不插入值的话,就会启用缺省值。

    CREATE TABLE users(

    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(30) NOT NULL DEFAULT '',

    height DOUBLE(10,2) NOT NULL DEFAULT 0.00,

    age INT NOT NULL DEFAULT 0,

    sex CHAR(4) NOT NULL DEFAULT '男'

    );

    四、创建索引:

    1、主键索引

    主要作用是确定数据库表里一条特定数据记录的位置。

    最好为每一张数据表定义为一个主键索引。

    一个表只能指定一个主键,主键的值不能为空。

    指定主键有2种方法:

    (1)

    CREATE TABLE users(

    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY

    );

    (2)

    CREATE TABLE users(

    id INT UNSIGNED NOT NULL AUTO_INCREMENT,

    PRIMARY KEY(id)

    );

    2、唯一索引

    unique

    它和主键索引一样,都可以防止创建重复的值,不同的是,每个表可以有多个唯一索引。

    3、常规索引

    最重要的技术,提高数据库性能,索引优化首先考虑常规索引。

    查询提高了,增删改减慢了。

    key和index是同义词,用哪个都可以。

    创建方法:

    单独创建常规索引:

    CREATE index 索引名称 on 表名(字段名,多个用逗号分隔);

    删除:

    drop index 索引名称 on 表名;

    创建表时创建常规索引:

    CREATE TABLE users(

    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

    uid INT NOT NULL,

    index 索引名称(uid)

    );

    4、全文索引

    fulltext类型索引,只能在MyISAM表类型上使用,只有在varchar,char,text等文本字符串上使用,也可以多个数据列使用。

    CREATE TABLE books(

    id int,

    bookname varchar(30),

    price double,

    detail text not null,

    fulltext(detail,bookname),

    index ind(price),

    primary key(id)

    );

    正常查询:select * from books where bookname like '%php%';

    全文索引查询:select bookname,price from books where MATCH(detail) AGAINST('php');

    以上两种查询来比,全文索引查询效率比较高。

    展开全文
  • 一直以来都没有特别关注单列索引和联合索引的使用区别,今天同事拿个sql来问我优化问题,让我感觉把这方面知识补起来了起因以下是一个不完整的课程,我只把我需要的字段保留下来了,创建了两个单独的索引 idx_city...

    一直以来都没有特别关注单列索引和联合索引的使用区别,今天同事拿个sql来问我优化问题,让我感觉把这方面知识补起来了

    起因

    以下是一个不完整的课程表,我只把我需要的字段保留下来了,创建了两个单独的索引 idx_city_id 和idx_start_course_time

    CREATE TABLE `t_lesson` (

    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,

    `start_course_time` DATETIME NOT NULL COMMENT '预计直播课上课时间',

    `end_course_time` DATETIME NOT NULL COMMENT '预计直播课下课时间',

    `city_id` INT(11) NOT NULL,

    PRIMARY KEY (`id`),

    INDEX `idx_city_id` (`city_id`),

    INDEX `idx_start_course_time` (`start_course_time`),

    );

    执行以下语句:

    explain select * from t_lesson where start_course_time = '2019-08-25 06:00:00' and city_id=4;

    key 代表 MySQL 实际会使用的索引,是idx_start_course_time

    34194ea5a4a3

    explain

    当时脑子出现了以下几个问题:

    MySQL 同时只能使用一个索引吗?

    什么情况下能使用两个索引呢?

    为什么存在两个索引的情况下却只使用了一个索引呢?

    解决问题

    MySQL 同时只能使用一个索引吗?

    网上随处可见,MySQL5.0之后是有索引合并这个概念的,所以第一个问题解决了,MySQL可以同时使用多个索引

    什么情况下能使用两个索引呢?

    以下只做个例子,具体情况可以具体看下索引合并文档

    explain select * from t_lesson where start_course_time = '2019-08-25 06:00:00' or city_id=4;

    34194ea5a4a3

    索引合并.png

    为什么存在两个索引的情况下却只使用了一个索引呢?

    这是我觉得写的比较好的回答:数据库中查询记录时是否每次只能使用一个索引

    引用其中的一句话:"与其说是数据库只支持一条查询语句只使用一个索引,倒不如说N条独立索引同时在一条语句使用的消耗比只使用一个索引还要慢。"

    展开全文
  • 一、可以创建多个索引嘛? 可以创建多个索引的。...为此,到底在创建多少索引合适,就需要在这个更新速度与查询速度之间取得一个均衡点。 如对于一些数据仓库数据库系统,其主要用来进行查询。相...

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

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

    通常来说,表的索引越多,其查询的速度也就越快。但是,表的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+树用于数据库索引。

    展开全文
  • mysql如何创建联合索引

    千次阅读 2021-01-18 19:09:07
    mysql创建多个联合索引的方法:可以使用CREATE INDEX或ALTER TABLE语句来创建。在创建联合索引时,应该仔细考虑列的顺序。MySQL中索引规则:1、需要加...对于复合索引,Mysql从左到右的使用索引中的字段,一个查询...
  • mysql 如何创建索引?mysql 如何创建索引呢,这个其实很简单 create index或者为己有字段增加索引 ALTER TABLE `table_name...特别是当数据量非常大,查询涉及多个表时,使用索引往往能使查询速度加快成千上万倍。mys...
  • 索引使用简介、 关于索引的知识要写出运行效率高的sql,需要对索引的机制有一定了解,下面对索引的基本知识做介绍。1、 索引的优点和局限索引可以提高查询的效率,但会降低dml操作的效率。所以建立索引时需要...
  • Mysql 创建索引

    千次阅读 2022-02-23 10:52:47
    数据库建立索引常用的规则如下: 1、的主键、外键必须有索引;2、数据量超过300的应该有索引; 3、经常与其他进行连接的,在连接字段上应该建立索引;...B、复合索引的几字段是否经常同...
  • MySQL只能使用一个索引

    千次阅读 2018-03-07 17:33:11
    那么,如果在firstname、lastname、age这三个列上分别创建单列索引,效果是否和创建一个firstname、lastname、 age的多列索引一样呢?答案是否定的,两者完全不同。当我们执行查询的时候,MySQL只能使用一个索引。...
  • 发现mysql一个表可以有几个唯一索引

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

    万次阅读 2021-01-18 21:18:31
    ”老实说,看到这问题的瞬间我是有点懵的状态,我原本只只知道Innodb引擎的,最多只有1017列(至于为什么不是1024,可以百度一下),下意识地就觉得索引最多可以创建1017,但是仔细想,不对啊, 索引可以是...
  • MySQL——在创建数据创建索引

    万次阅读 2018-05-15 18:53:58
     在建立数据创建索引 在创建数据创建索引的基本语法结构: CREATE TABLE table_name( 属性名 数据类型[约束条件], …… 属性名 数据类型 [UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY ...
  • Oracle分区及分区索引创建

    万次阅读 多人点赞 2018-07-08 11:21:22
    关于分区和分区索引(About Partitioned Tables and Indexes)对于10gR2而言,基本上可以分成几类:• Range(范围)分区• Hash(哈希)分区• List(列表)分区• 以及组合分区:Range-Hash,Range-List。 对于而...
  • ES 索引的操作 创建,删除,查询

    千次阅读 2022-03-04 15:02:11
    1. 创建索引; 默认的分片是5 Default for number_of_shards is 5 默认的副本是1 Default for number_of_replicas is 1 (ie one replica for each primary shard) { "settings" : { "index" : { "number_of_...
  • 创建表时报错: > 1118 - Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline. > 时间: 0.43s ● MySQL ...
  • Elasticsearch是一个分布式的、RESTful风格的搜索和数据分析引擎。 安装Elasticsearch 安装Elasticsearch,这里选择在Windows电脑中安装。[Elasticsearch官网Windows下载地址](Past Releases of Elastic Stack ...
  •  最近在一次面试中,讨论了一个这样的问题:主键和索引有什么区别?当时我的回答是这样的:“主键就是加了唯一性约束的聚集索引。” “你确定你所说的是对的?” 面试官反问到。 “应该是对的。” 我不加思索地回答...
  • mysql 中 创建索引很慢,怎么解决

    千次阅读 2021-01-19 20:01:14
    from ...里面的数据大概就是 800w 条左右,当然以后也可能会更多,这个表会频繁的更新!我现在的处理是:每次更新 都会先truncate 这张表(因为里面的数据 已经不需要了),然后我会 drop index !...
  • elasticSearch创建索引库、映射、文档

    千次阅读 2022-04-13 16:32:31
    创建索引库 使用postman或curl这样的工具创建 put http://localhost:9200/索引库名称 参数: { "settings": { "index": { "number_of_shards": 1, "number_of_replicas": 0 } } } number_of_shards:设置分片...
  • MySQL的索引分类及创建原则

    千次阅读 2022-03-19 16:05:58
    唯一索引:添加唯一索引的列不允许出现重复值,否则报错,但允许有空值(一个表可以有多个唯一索引)。 主键索引:一种添加了非空限制的特殊唯一索引,因为数据存储在文件中只能按一种顺序存储,所以一张表中只允许...
  • 细说 MySQL 创建表的三种方法

    万次阅读 多人点赞 2021-08-15 21:38:02
    SQL 标准使用 CREATE TABLE 语句创建数据;MySQL 则实现了三种创建表的方法,支持自定义结构或者通过复制已有的结构(CREATE TABLE ... LIKE 以及 CREATE TABLE ... SELECT)来创建,本文给大家分别介绍...
  • mysql中创建视图、索引

    千次阅读 2021-02-03 22:17:56
    外模式一、视图1、什么是视图:视图是从一个或多个表中导出来的表,是一种虚拟存在的表。视图就像一个窗口,通过这个窗口可以看到系统专门提供的数据。这样,用户可以不用看到整个数据库中的数据,而之关心对自己...
  • DBCC showcontig('Table')执行重建索引命令:DBCC DBREINDEX('T_CEmployeeRegisterHoliday')1、没有聚集索引查询2、有聚集索引查询3、没有非聚集索引查询4、有非聚集索引查询5、查询表索引语句EXEC sp_helpindex A6...
  • SQL Server 创建索引(index)

    千次阅读 多人点赞 2019-06-14 18:01:13
    索引分为聚集索引和非聚集索引,数据库中的索引类似于本书的目录,在本书中通过目录可以快速找到你想要的信息,而不需要读完全书。 索引主要目的是提高了SQLServer系统的性能,加快数据的查询速度与减少系统的...
  • 数据库的主键、唯一约束和索引

    千次阅读 2021-11-23 17:31:46
    (1)一个表可以没有主键,而且最多只能有一个主键。 (2)主键值必须唯一标识表中的每一行,且不能为 NULL,即同一个表中不可能存在两行数据有相同的主键值。 2、MySQL 的 唯一约束。  MySQL唯一约束...
  • 几乎所有的小伙伴都可以随口说几句关于创建索引的优缺点,也知道什么时候创建索引能够提高我们的查询性能,什么时候索引会更新,但是你有没有注意到,即使你设置了索引,有些时候索引他是不会生效的!这不仅考察了...
  • 特点:数据列不允许重复,不能为null,一张表只能一个主键;Mysql主动将该字段进行排序 ALTER TABLE 表名 ADD Primary key (col); 2.添加唯一索引 特点:索引的值是唯一的,可以为null;Mysql主动将该字段进行...
  • 数据库建表添加索引

    万次阅读 2019-05-09 19:22:09
    特别是当数据量非常大,查询涉及多个表时,使用索引往往能使查询速度加快成千上万倍。 例如,有3个未索引的表t1、t2、t3,分别只包含列c1、c2、c3,每个表分别含有1000行数据组成,指为1~1000的数值,查找对应值...
  • MySQL单列索引和联合索引

    千次阅读 多人点赞 2022-09-02 11:11:33
    多个单列索引在多条件查询时优化器会优先选择最优索引策略,可能只用一个索引,也可能将多个索引全用上。但多个单列索引底层会创建多个B+索引树,比较占用空间,也会浪费一定搜索效率,故如果只有多条件联合查询时...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 494,107
精华内容 197,642
热门标签
关键字:

一个表只能创建一个索引