精华内容
下载资源
问答
  • 什么是索引?什么条件适合建立索引?什么条件不适合建立索引
    千次阅读
    2019-04-25 21:33:44

    索引用来快速地寻找那些具有特定值的记录,所有MySQL索引都以B-树的形式保存。如果没有索引,执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录,直至找到符合要求的记录。表里面的记录数量越多,这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引,MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录,通过索引查找记录至少要比顺序扫描记录快100倍。

    1.主键自动建立唯一索引;

    2.频繁作为查询条件的字段应该创建索引;

    3.查询中与其他表有关联的字段,例如外键关系;

    4.频繁更新的字段不适合创建索引,因为每次更新不单单是更新记录,还会更新索引,保存索引文件;

    5.where条件里用不到的字段,不创建索引;

    6.高并发的情况下一般选择复合索引;

    7.查询中排序的字段创建索引将大大提高排序的速度(索引就是排序加快速查找);

    8.查询中统计或者分组的字段;

    9.表记录太少,不需要创建索引;

    10.经常增删改的表;

    11.数据重复且分布平均的字段,因此为经常查询的和经常排序的字段建立索引。注意某些数据包含大量重复数据,因此他建立索引就没有太大的效果,例如性别字段,只有男女,不适合建立索引。

    索引的类型

    普通索引
    这是最基本的索引类型,而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建:
    创建索引,例如CREATE INDEX <索引的名字> ON tablename (列的列表);
    修改表,例如ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表);
    创建表的时候指定索引,例如CREATE TABLE tablename ( […], INDEX [索引的名字] (列的列表) );

    唯一性索引
    这种索引和前面的“普通索引”基本相同,但有一个区别:索引列的所有值都只能出现一次,即必须唯一。唯一性索引可以用以下几种方式创建:
    创建索引,例如CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表);
    修改表,例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表);
    创建表的时候指定索引,例如CREATE TABLE tablename ( […], UNIQUE [索引的名字] (列的列表) );

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

    全文索引
    MySQL从3.23.23版开始支持全文索引和全文检索。在MySQL中,全文索引的索引类型为FULLTEXT。全文索引可以在VARCHAR或者TEXT类型的列上创建。它可以通过CREATE TABLE命令创建,也可以通过ALTER TABLE或CREATE INDEX命令创建。对于大规模的数据集,通过ALTER TABLE(或者CREATE INDEX)命令创建全文索引要比把记录插入带有全文索引的空表更快。本文下面的讨论不再涉及全文索引,要了解更多信息,请参见MySQL documentation。

    单列索引与多列索引
    索引可以是单列索引,也可以是多列索引。下面我们通过具体的例子来说明这两种索引的区别

    1.多个单列索引:
    定义:即是在表中在需要索引的字段上为每个字段设计一个索引;
    特点:简单,索引个数多

    2.多列索引:
    定义:即是在表中根据查询需求在多个字段上设计一个索引;
    特点:稍微复杂,需要考虑索引顺序;

    3.覆盖索引
    定义:查询所需数据通过索引可以直接获取到,不需要回表查询数据行;

    Select查询返回列包含在索引列中
    where条件包含索引列或复合索引的前导列
    查询结果的总字段长度可以接受

    4.索引合并
    在索引设计不太合理的时候,MySql可能会对一些查询执行索引合并

    在多个单列索引的情况下,
    where条件的索引列使用or连接时,会触发索引合并
    where条件的索引列使用and连接时,通常会选择限制最严格的索引,这个时候联合索引(多列索引)比较合适
    合并标识
    使用 explain 可看到 type:index_merge
    触发索引合并,说明当前索引设计的比较糟糕

    选择索引列
    在性能优化过程中,选择在哪些列上创建索引是最重要的步骤之一。可以考虑使用索引的主要有两种类型的列:在WHERE子句中出现的列,在join子句中出现的列

    索引的缺点
    到目前为止,我们讨论的都是索引的优点。事实上,索引也是有缺点的。

    首先,索引要占用磁盘空间。通常情况下,这个问题不是很突出。但是,如果你创建每一种可能列组合的索引,索引文件体积的增长速度将远远超过数据文件。如果你有一个很大的表,索引文件的大小可能达到操作系统允许的最大文件限制。

    第二,对于需要写入数据的操作,比如DELETE、UPDATE以及INSERT操作,索引会降低它们的速度。这是因为MySQL不仅要把改动数据写入数据文件,而且它还要把这些改动写入索引文件。

    更多相关内容
  • 实验 数据库表索引的定义 实验目的 通过本实验加深SQL Server 2000环境下数据定义语言的理解能够熟练地建立数据库并通过SQL语句来创建和修改基本表创建和取消索引 二实验内容 使用SQL Server 2000软件练习创建...
  • 在特殊情况下,它们可能是一多或多一的关系,即一张原始单证对应多个实体,或多张原始单证对应一个实体。  这里的实体可以理解为基本表。明确这种对应关系后,我们设计录入界面大有好处。   〖例1〗...

    1.数据库设计14规则

    1. 原始单据与实体之间的关系 
      可以是一对一、一对多、多对多的关系。在一般情况下,它们是一对一的关系:即一张原始单据对应且只对应一个实体。 
    在特殊情况下,它们可能是一对多或多对一的关系,即一张原始单证对应多个实体,或多张原始单证对应一个实体。 
    这里的实体可以理解为基本表。明确这种对应关系后,对我们设计录入界面大有好处。 


      〖例1〗:一份员工履历资料,在人力资源信息系统中,就对应三个基本表:员工基本情况表、社会关系表、工作简历表。 
            这就是“一张原始单证对应多个实体”的典型例子。 


    2. 主键与外键 
      一般而言,一个实体不能既无主键又无外键。在E—R 图中, 处于叶子部位的实体, 可以定义主键,也可以不定义主键 
      (因为它无子孙), 但必须要有外键(因为它有父亲)。 


      主键与外键的设计,在全局数据库的设计中,占有重要地位。当全局数据库的设计完成以后,有个美国数据库设计专 
      家说:“键,到处都是键,除了键之外,什么也没有”,这就是他的数据库设计经验之谈,也反映了他对信息系统核 
      心(数据模型)的高度抽象思想。因为:主键是实体的高度抽象,主键与外键的配对,表示实体之间的连接。 


    3. 基本表的性质 
      基本表与中间表、临时表不同,因为它具有如下四个特性: 
       (1) 原子性。基本表中的字段是不可再分解的。 
       (2) 原始性。基本表中的记录是原始数据(基础数据)的记录。 
       (3) 演绎性。由基本表与代码表中的数据,可以派生出所有的输出数据。 
       (4) 稳定性。基本表的结构是相对稳定的,表中的记录是要长期保存的。 
      理解基本表的性质后,在设计数据库时,就能将基本表与中间表、临时表区分开来。 


    4. 范式标准 
      基本表及其字段之间的关系, 应尽量满足第三范式。但是,满足第三范式的数据库设计,往往不是最好的设计。 
      为了提高数据库的运行效率,常常需要降低范式标准:适当增加冗余,达到以空间换时间的目的。 


      〖例2〗:有一张存放商品的基本表,如表1所示。“金额”这个字段的存在,表明该表的设计不满足第三范式, 
      因为“金额”可以由“单价”乘以“数量”得到,说明“金额”是冗余字段。但是,增加“金额”这个冗余字段, 
      可以提高查询统计的速度,这就是以空间换时间的作法。 
      在Rose 2002中,规定列有两种类型:数据列和计算列。“金额”这样的列被称为“计算列”,而“单价”和 
      “数量”这样的列被称为“数据列”。 


      表1 商品表的表结构 
      商品名称 商品型号 单价 数量 金额 
      电视机 29吋 2,500 40 100,000 
       
    5. 通俗地理解三个范式 
      通俗地理解三个范式,对于数据库设计大有好处。在数据库设计中,为了更好地应用三个范式,就必须通俗地理解 
      三个范式(通俗地理解是够用的理解,并不是最科学最准确的理解): 
      第一范式:1NF是对属性的原子性约束,要求属性具有原子性,不可再分解; 
      第二范式:2NF是对记录的惟一性约束,要求记录有惟一标识,即实体的惟一性; 
      第三范式:3NF是对字段冗余性的约束,即任何字段不能由其他字段派生出来,它要求字段没有冗余。 


      没有冗余的数据库设计可以做到。但是,没有冗余的数据库未必是最好的数据库,有时为了提高运行效率,就必须降 
      低范式标准,适当保留冗余数据。具体做法是:在概念数据模型设计时遵守第三范式,降低范式标准的工作放到物理 
      数据模型设计时考虑。降低范式就是增加字段,允许冗余。 


    6. 要善于识别与正确处理多对多的关系 
      若两个实体之间存在多对多的关系,则应消除这种关系。消除的办法是,在两者之间增加第三个实体。这样,原来一 
      个多对多的关系,现在变为两个一对多的关系。要将原来两个实体的属性合理地分配到三个实体中去。这里的第三个 
      实体,实质上是一个较复杂的关系,它对应一张基本表。一般来讲,数据库设计工具不能识别多对多的关系,但能处 
      理多对多的关系。 


      〖例3〗:在“图书馆信息系统”中,“图书”是一个实体,“读者”也是一个实体。这两个实体之间的关系,是一 
      个典型的多对多关系:一本图书在不同时间可以被多个读者借阅,一个读者又可以借多本图书。为此,要在二者之 
      间增加第三个实体,该实体取名为“借还书”,它的属性为:借还时间、借还标志(0表示借书,1表示还书),另外, 
      它还应该有两个外键(“图书”的主键,“读者”的主键),使它能与“图书”和“读者”连接。 


    注视:


    图书  1 和 该实体取名为“借还书” n 
    读者  1 和 该实体取名为“借还书” n 


    7. 主键PK的取值方法 
       PK是供程序员使用的表间连接工具,可以是一无物理意义的数字串, 由程序自动加1来实现。也可以是有物理意义 
      的字段名或字段名的组合。不过前者比后者好。当PK是字段名的组合时,建议字段的个数不要太多,多了不但索引 
      占用空间大,而且速度也慢。 


    8. 正确认识数据冗余 
      主键与外键在多表中的重复出现, 不属于数据冗余,这个概念必须清楚,事实上有许多人还不清楚。非键字段的重 
      复出现, 才是数据冗余!而且是一种低级冗余,即重复性的冗余。高级冗余不是字段的重复出现,而是字段的派生出现。 


      〖例4〗:商品中的“单价、数量、金额”三个字段,“金额”就是由“单价”乘以“数量”派生出来的,它就是冗余, 
      而且是一种高级冗余。冗余的目的是为了提高处理速度。只有低级冗余才会增加数据的不一致性,因为同一数据,可 
      能从不同时间、地点、角色上多次录入。因此,我们提倡高级冗余(派生性冗余),反对低级冗余(重复性冗余)。 


    9. E--R图没有标准答案 
      信息系统的E--R图没有标准答案,因为它的设计与画法不是惟一的,只要它覆盖了系统需求的业务范围和功能内容, 
      就是可行的。反之要修改E--R图。尽管它没有惟一的标准答案,并不意味着可以随意设计。好的E—R图的标准是: 
      结构清晰、关联简洁、实体个数适中、属性分配合理、没有低级冗余。 


    10 . 视图技术在数据库设计中很有用 
      与基本表、代码表、中间表不同,视图是一种虚表,它依赖数据源的实表而存在。视图是供程序员使用数据库的 
      一个窗口,是基表数据综合的一种形式, 是数据处理的一种方法,是用户数据保密的一种手段。为了进行复杂处理、 
      提高运算速度和节省存储空间, 视图的定义深度一般不得超过三层。 若三层视图仍不够用, 则应在视图上定义临时表, 
       在临时表上再定义视图。这样反复交迭定义, 视图的深度就不受限制了。 


      对于某些与国家政治、经济、技术、军事和安全利益有关的信息系统,视图的作用更加重要。这些系统的基本表完 
      成物理设计之后,立即在基本表上建立第一层视图,这层视图的个数和结构,与基本表的个数和结构是完全相同。 
      并且规定,所有的程序员,一律只准在视图上操作。只有数据库管理员,带着多个人员共同掌握的“安全钥匙”, 
      才能直接在基本表上操作。请读者想想:这是为什么? 


    11. 中间表、报表和临时表 
      中间表是存放统计数据的表,它是为数据仓库、输出报表或查询结果而设计的,有时它没有主键与外键(数据仓 
      库除外)。临时表是程序员个人设计的,存放临时记录,为个人所用。基表和中间表由DBA维护,临时表由程序员 
      自己用程序自动维护。 


    12. 完整性约束表现在三个方面 
      域的完整性:用Check来实现约束,在数据库设计工具中,对字段的取值范围进行定义时,有一个Check按钮,通 
      过它定义字段的值城。 
      参照完整性:用PK、FK、表级触发器来实现。 
      用户定义完整性:它是一些业务规则,用存储过程和触发器来实现。 


    13. 防止数据库设计打补丁的方法是“三少原则” 
       (1) 一个数据库中表的个数越少越好。只有表的个数少了,才能说明系统的E--R图少而精,去掉了重复的多余的 
        实体,形成了对客观世界的高度抽象,进行了系统的数据集成,防止了打补丁式的设计; 


       (2) 一个表中组合主键的字段个数越少越好。因为主键的作用,一是建主键索引,二是做为子表的外键,所以组 
        合主键的字段个数少了,不仅节省了运行时间,而且节省了索引存储空间; 


       (3) 一个表中的字段个数越少越好。只有字段的个数少了,才能说明在系统中不存在数据重复,且很少有数据冗 
        余,更重要的是督促读者学会“列变行”,这样就防止了将子表中的字段拉入到主表中去,在主表中留下许 
        多空余的字段。所谓“列变行”,就是将主表中的一部分内容拉出去,另外单独建一个子表。这个方法很简 
        单,有的人就是不习惯、不采纳、不执行。 


      数据库设计的实用原则是:在数据冗余和处理速度之间找到合适的平衡点。“三少”是一个整体概念,综合观点, 
      不能孤立某一个原则。该原则是相对的,不是绝对的。“三多”原则肯定是错误的。试想:若覆盖系统同样的功 
      能,一百个实体(共一千个属性) 的E--R图,肯定比二百个实体(共二千个属性) 的E--R图,要好得多。 


      提倡“三少”原则,是叫读者学会利用数据库设计技术进行系统的数据集成。数据集成的步骤是将文件系统集成 
      为应用数据库,将应用数据库集成为主题数据库,将主题数据库集成为全局综合数据库。集成的程度越高,数据 
      共享性就越强,信息孤岛现象就越少,整个企业信息系统的全局E—R图中实体的个数、主键的个数、属性的个数 
      就会越少。 


      提倡“三少”原则的目的,是防止读者利用打补丁技术,不断地对数据库进行增删改,使企业数据库变成了随意 
      设计数据库表的“垃圾堆”,或数据库表的“大杂院”,最后造成数据库中的基本表、代码表、中间表、临时表 
      杂乱无章,不计其数(即动态创表而增加表数量),导致企事业单位的信息系统无法维护而瘫痪。 


       “三多”原则任何人都可以做到,该原则是“打补丁方法”设计数据库的歪理学说。“三少”原则是少而精的 
      原则,它要求有较高的数据库设计技巧与艺术,不是任何人都能做到的,因为该原则是杜绝用“打补丁方法” 
      设计数据库的理论依据。 


    14. 提高数据库运行效率的办法 
      在给定的系统硬件和系统软件条件下,提高数据库系统的运行效率的办法是: 
       (1) 在数据库物理设计时,降低范式,增加冗余, 少用触发器, 多用存储过程。 
       (2) 当计算非常复杂、而且记录条数非常巨大时(例如一千万条),复杂计算要先在数据库外面,以文件系统方 
        式用C++语言计算处理完成之后,最后才入库追加到表中去。这是电信计费系统设计的经验。 
       (3) 发现某个表的记录太多,例如超过一千万条,则要对该表进行水平分割。水平分割的做法是,以该表主键 
        PK的某个值为界线,将该表的记录水平分割为两个表(即可以表维护 表行数过大 手动分割为两个  建个两表union的视图对程序透明)。若发现某个表的字段太多,例如超过八十个,则 
        垂直分割该表,将原来的一个表分解为两个表。 
       (4) 对数据库管理系统DBMS进行系统优化,即优化各种系统参数,如缓冲区个数。 
       (5) 在使用面向数据的SQL语言进行程序设计时,尽量采取优化算法。 
        总之,要提高数据库的运行效率,必须从数据库系统级优化、数据库设计级优化、程序实现级优化,

    2.表、字段命名规则

    .数据库表命名规范:
    (1)表名前应该加上前缀,表的前缀一个用系统或模块的英文名称缩写,前缀全部大写或首字母大写,表名中包含的单词首字母大写。
    (2)数据库表名应该有意义,并且易于理解,最好使用可以表达功能的英文单词或缩写,如果用英文单词表示,建议使用完整的英文单词。
    (3)表名不可以太长,最好不要超过3个英文单词长度(22个字母)。
    (4)在数据库表命名时应该用英文单词的单数形式,如员工表命名:应该为Employee而不是Employees .
    (5)如果是后台表命名时应该在表名基础上加上后缀_b(back 首字母 )
    (6)在表创建完成前,应该为表添加表的注释。

    2.表字段命名规范:
    (1)数据库表字段应该是有意义而且易于理解的,最好是能够表达字段含义的英文字母
    (有人认为如果用英文单词作为字段,因为翻译工具不同,而字段不统一建议使用汉语拼音首字母缩写;有人认为用汉语拼音缩写看起来不直观,老半天也不知道到底这个字段是干什么的)
    (2)系统中所有属于内码,即仅用于标识唯一性和程序内部用到的标识性字段,字段名称建议取为 ID ,采用类型为整型或长整型.
    (3)系统中属于是业务内的编号字段,代表一定业务信息,建议字段命名为code , 如工作单编号wf_code .
    (4) 不要在数据库表字段(列名)中包含数据类型,如:datetime
    (5)不要在数据库表字段(列名)命名时重复表名,可以使用表名首字母(不包含数据库表名前缀)
    注意:不要在数据库表字段(列名)命名时不建议使用数据库关键字,如:name,time ,datetime password 等

    3.表设计规范:
    (1)所有字段在设计时,除以下数据类型timestamp、image、datetime、smalldatetime、uniqueidentifier、binary、sql_variant、binary、varbinary外,必须有默认值。字符型的默认值为一个空字符值串’’;数值型的默认值为数值0;逻辑型的默认值为数值0;
    其中:系统中所有逻辑型中数值0表示为“假”;数值1表示为“真”。
    datetime、smalldatetime类型的字段没有默认值,必须为NULL。
    (2)当字段定义为字符串形时建议使用varchar而不用nvarchar。
    注:在MySQL5.0以上的版本中,varchar数据类型的长度支持到了65535,也就是说可以存放65532个字节的数据,起始位和结束位占去了3个字节。
    (3)建议在大多数表中(如工作单),应都有以下字段:
    字段名说明类型默认值
    CreatorID创建者int默认值为0
    CreatedTime创建时间Datetime默认值为NULL
    (4)字段的描述
    a.字段必须填写描述信息(注释)
    b.尽量遵守第三范式的标准(3NF)
    表内的每一个值只能被表达一次(列名不重复)
    表内的每一行都应当被唯一的标示(标识唯一性,如自动增长主键)
    表内不应该存储依赖于其他键的非键信息
    (5)加索引规则
    a.表建好后数据库自动为表生成一个索引(为自动增长的列生成唯一索引),如果在对这列添加索引,数据库会给一个警告,内容大概是,已经为这列添加了索引,建议修改索引名称和自动增长列名保持一致,为了方便使用。
    b.如果在添加索引时,建议索引名称和数据库列名保持一致,为了方便使用
    c.如果字段事实上是与其它表的关键字相关联而未设计为外键引用,需建索引。
    d.如果字段与其它表的字段相关联,需建索引。
    e.如果字段需做模糊查询之外的条件查询,需建索引。
    f.除了主关键字允许建立簇索引外,其它字段所建索引必须为非簇索引。

    4.存储过程命名规范
    (1)存贮过程的命名请遵循以下命名规范:USP_ + 系统模块缩写(与表前缀类似)+ + 功能标识 + 代表存贮过程操作的主要表名(不带前缀)或功能的英文单词或英文单词缩写。
    如果一个存贮过程只对一个表进行操作,建议存贮过程的名称就用存贮过程所操作的表的表名(不带前缀)。这样有利于根据表名找到相应的存贮过程。例如:
    用于新增的存贮过程USP_MESSAGE_Add_Model
    用于修改的存贮过程USP
    MESSAGE_Upt_Model
    用于删除的存贮过程USP_ MESSAGE_Del_ Modele
    注:USP是user stored procedure缩写
    5.存储过程设计规范
    在存贮过程中必须说明以下内容:
    (1)目的:说明此存贮过程的作用。
    (2)作者:首次创建此存贮过程的人的姓名。在此请使用中文全名,不允许使用英文简称。
    (3)创建日期:创建存贮过程时的日期。
    (4)修改记录:
    修改记录需包含修改顺序号、修改者、修改日期、修改原因,修改时不能直接在原来的代码上修改,也不能删除原来的代码,只能先将原来的代码注释掉,再重新增加正确的代码。修改顺序号的形式为:log1,log2,log3。。。,根据修改次数顺序增加,同时在注释掉的原来的代码块和新增的正确代码块前后注明修改顺序号。
    (5)对存贮过程各参数及变量的中文注解。
    建议:在数据库中创建一个文本文件保存创建脚本

    6.视图命名规范
    视图的命名请遵循以下命名规范:UV + 系统模块缩写(与表前缀类似)+ + 功能标识 + 代表视图查询的主要表名(不带前缀)或功能的英文单词或英文单词缩写。
    如果一个视图只对一个表进行查询,建议视图的名称就用视图所查询的表的表名(不带前缀)。这样有利于根据表名找到相应的视图。
    注:UV是userView缩写
    7.视图设计规范
    在视图中必须说明以下内容:
    (1)目的:说明此视图的作用。
    (2)创建者:首次创建此视图的人的姓名。在此请使用中文全名,不允许使用英文简称。
    (3)修改者、修改日期、修改原因:如果有人对此视图进行了修改,则必须在此视图的前面加注修改者姓名、修改日期及修改原因。
    (4)对视图各参数及变量的中文注解
    建议:在数据库中创建一个文本文件保存创建脚本
    8.触发器命名规范
    Insert触发器加'_i',Delete触发器加'_d',Update触发器加'_u'
    9.触发器设计规范
    在视图中必须说明以下内容:
    (1)目的:说明此触发器的作用。
    (2)创建者:首次创建此触发器的人的姓名。在此请使用中文全名,不允许使用英文简称。
    (3)修改者、修改日期、修改原因:如果有人对此触发器进行了修改,则必须在此触发器的前面加注修改者姓名、修改日期及修改原因。
    (4)对触发器各参数及变量的中文注解
    建议:在数据库中创建一个文本文件保存创建脚本


    3.mysql索引建立原则

    数据库索引的设计原则:

    为了使索引的使用效率更高,在创建索引时,必须考虑在哪些字段上创建索引和创建什么类型的索引。
    那么索引设计原则又是怎样的?
    1.选择唯一性索引
    唯一性索引的值是唯一的,可以更快速的通过该索引来确定某条记录。
    例如,学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。
    如果使用姓名的话,可能存在同名现象,从而降低查询速度。

    2.为经常需要排序、分组和联合操作的字段建立索引
    经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段,排序操作会浪费很多时间。
    如果为其建立索引,可以有效地避免排序操作。

    3.为常作为查询条件的字段建立索引
    如果某个字段经常用来做查询条件,那么该字段的查询速度会影响整个表的查询速度。因此,
    为这样的字段建立索引,可以提高整个表的查询速度。

    4.限制索引的数目
    索引的数目不是越多越好。每个索引都需要占用磁盘空间,索引越多,需要的磁盘空间就越大。
    修改表时,对索引的重构和更新很麻烦。越多的索引,会使更新表变得很浪费时间。

    5.尽量使用数据量少的索引
    如果索引的值很长,那么查询的速度会受到影响。例如,对一个CHAR(100)类型的字段进行全文
    检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。

    6.尽量使用前缀来索引
    如果索引字段的值很长,最好使用值的前缀来索引。例如,TEXT和BLOG类型的字段,进行全文检索
    会很浪费时间。如果只检索字段的前面的若干个字符,这样可以提高检索速度。

    7.删除不再使用或者很少使用的索引
    表中的数据被大量更新,或者数据的使用方式被改变后,原有的一些索引可能不再需要。数据库管理
    员应当定期找出这些索引,将它们删除,从而减少索引对更新操作的影响。

     
    8.小表不应建立索引;包含大量的列并且不需要搜索非空值的时候可以考虑不建索引
     

    mysql索引相关小常识:

    一,经常被用来过滤记录的字段。

    1. primary key 字段, 系统自动创建主键的索引;
    2. unique key 字段,系统自动创建对应的索引;

    3. foreign key 约束所定义的作为外键的字段;

    4. 在查询中用来连接表的字段;

    5. 经常用来作为排序(order by 的字段)基准的字段;


    二. 索引会占用磁盘空间,创建不必要的索引只会形成浪费.

    三. 索引的创建必须考虑数据的操作方式。

    1.内容很少变动,经常被查询,为它多创建几个索引無所谓;

    2.经常性,例行性变动的表而言,则需要谨慎地创建确实必要的索引;


    四. primary key 和 unique key的区别
    1. 作为Primary Key的域/域组不能为null。而Unique Key可以。
    2. 在一个表中只能有一个Primary Key,而多个Unique Key可以同时存在。
    更大的区别在逻辑设计上, Primary Key一般在逻辑设计中用作记录标识,这也是设置
    Primary Key的本来用意, 而Unique Key只是为了保证域/域组的唯一性。


    五.复合索引和单一索引

      复合索引是指多字段联合索引,查询时经常需要这几个字段组合一起为条件再查询 
      唯一索引主要是用主键ID索引,存储结构顺序与物理结构一致

    如:create  index  idx  on  tbl(a,b)  
     先按a排序, a相同的按b排序,所以当你查a或ab的时候, 
    能够利用到这个索引.但当你只查b的时候,索引对你的帮助不大.可能可以跳跃查找.


     
    添加和删除索引的情况:
     
    1、表的主键、外键必须有索引;

    2、数据量超过300w的表应该有索引;

    3、经常与其他表进行连接的表,在连接字段上应该建立索引;

    4、经常出现在Where子句中的字段,特别是大表的字段,应该建立索引;

    5、索引应该建在选择性高的字段上;

    6、索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引;

    7、复合索引的建立需要进行仔细分析;尽量考虑用单字段索引代替:

       A、正确选择复合索引中的主列字段,一般是选择性较好的字段;

       B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中?单字段查询是否极少甚至没有?如果是,则可以建立复合索引;否则考虑单字段索引;

       C、如果复合索引中包含的字段经常单独出现在Where子句中,则分解为多个单字段索引;

       D、如果复合索引所包含的字段超过3个,那么仔细考虑其必要性,考虑减少复合的字段;

       E、如果既有单字段索引,又有这几个字段上的复合索引,一般可以删除复合索引;

    8、频繁进行数据操作的表,不要建立太多的索引;

    9、删除无用的索引,避免对执行计划造成负面影响;


    展开全文
  • 这两天有非常强烈的感觉就是自己在一些特别的情况下还是hold不住,脑子easy放空或者说下子不知道怎么去分析问题了,比方,问“hash和btree索引的差别”,这非常难吗。仅仅要掌握了这两种数据结构稍加分析就能...

    这两天有个非常强烈的感觉就是自己在一些特别的情况下还是hold不住,脑子easy放空或者说一下子不知道怎么去分析问题了,比方,问“hash和btree索引的差别”,这非常难吗。仅仅要掌握了这两种数据结构稍加分析就能得出答案,结果是一下子不知道从何说起。进入正题吧。这两者有啥差别。

    1. hash索引查找数据基本上能一次定位数据。当然有大量碰撞的话性能也会下降。

    而btree索引就得在节点上挨着查找了,非常明显在数据精确查找方面hash索引的效率是要高于btree的。

    2. 那么不精确查找呢,也非常明显,由于hash算法是基于等值计算的。所以对于“like”等范围查找hash索引无效,不支持;

    3. 对于btree支持的联合索引的最优前缀。hash也是无法支持的。联合索引中的字段要么全用要么全不用。提起最优前缀竟然都泛起迷糊了,看来有时候放空得太厉害。

    4. hash不支持索引排序。索引值和计算出来的hash值大小并不一定一致。

    勤动脑,少放空。

    索引是在数据库表或者视图上创建的对象,目的是为了加快对表或视图的查询的速度。

    依照存储方式分为:聚集与非聚集索引

    依照维护与管理索引角度分为:唯一索引、复合索引和系统自己主动创建的索引。

    索引的结构是由:根节点--->非叶节点--->非叶节点--->叶节点

    1、聚集索引:表中存储的数据依照索引的顺序存储,检索效率比普通索引高,但对数据新增/改动/删除的影响比較大。

    逻辑顺序决定了表中对应行的物理顺序。

    特点:

    (1) 一个表能够最多能够创建249个索引

    (2) 先建聚集索引才干创建非聚集索引

    (3) 非聚集索引数据与索引不同序

    (4) 数据与索引在不同位置

    (5) 索引在叶节点上存储,在叶节点上有一个"指针"直接指向要查询的数据区域

    (6) 数据不会依据索引键的顺序又一次排列数据

    (7)假设在该字段上进行范围查询,或者该表非常少做增删改

    创建聚集索引的语法:

    create NONCLUSTERED INDEX idximpID ON EMP(empID)

    2、非聚集索引:不影响表中的数据存储顺序,检索效率比聚集索引低,对数据新增/改动/删除的影响非常少

    是通过二叉树的数据结构来描写叙述的,逻辑顺序,特点:

    (1) 无索引。数据无序

    (2) 有索引。数据与索引同序

    (3) 数据会依据索引键的顺序又一次排列数据

    (4) 一个表仅仅能有一个索引

    (5) 叶节点的指针指向的数据也在同一位置存储

    语法:

    create CLUSTERED INDEX idxempID on emp(empID)

    3、惟一索引:惟一索引能够确保索引列不包括反复的值.

    能够用多个列,可是索引能够确保索引列中每一个值组合都是唯一的

    姓   名

    李   二

    张   三

    王   五

    语法: create unique index idxempid on emp(姓,名)

    4、复合索引:假设在两上以上的列上创建一个索引,则称为复合索引。

    那么。不可能有两行的姓和名是反复的

    语法:

    create index indxfullname on addressbook(firstname,lastname)

    注意:假设把复合的聚集索引字段分开查询。

    带着这个问题。我们来看一下下面的查询速度(结果集都是25万条数据):(日期列fariqi首先排在复合聚集索引的起始列。usernameneibuyonghu排在后列):

    (1)select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>''2004-5-5''

    查询速度:2513毫秒

    (2)select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>''2004-5-5'' and neibuyonghu=''办公室''

    查询速度:2516毫秒

    (3)select gid,fariqi,neibuyonghu,title from Tgongwen where neibuyonghu=''办公室''

    查询速度:60280毫秒

    从以上试验中,我们能够看到假设仅用聚集索引的起始列作为查询条件和同一时候用到复合聚集索引的所有列的查询速度是差点儿一样的。甚至比用上所有的复合索引列还要略快(在查询结果集数目一样的情况下);而假设仅用复合聚集索引的非起始列作为查询条件的话,这个索引是不起不论什么作用的。当然,语句1、2的查询速度一样是由于查询的条目数一样。假设复合索引的所有列都用上,并且查询结果少的话,这样就会形成“索引覆盖”。因而性能能够达到最优。同一时候,请记住:不管您是否常常使用聚合索引的其它列,但其前导列一定要是使用最频繁的列。

    5、系统自建的索引:在使用T_sql语句创建表的时候使用PRIMARY KEY或UNIQUE约束时。会在表上自己主动创建一个惟一索引

    自己主动创建的索引是无法删除的

    语法:

    create table ABc

    ( empID int primary key,

    firstname varchar(50)UNIQUE,

    lastname   varchar(50)UNIQUE,

    )

    这种结果就出来了三个索引,但仅仅有一个聚集索引哦

    6、创建索引的方法:

    1、企业管理器中

    (1)右击某个表,全部任务---管理索引,打开管理索引。单击“新建”就能够创建索引

    (2)在设计表中进行设计表,管理索引/键

    (3)在关系图中,加入表后右击关系图中的某个表,就有“索引/键”

    (4)通过向导,数据库---创建索引向导

    (5)通过T-SQL语句

    2、能过“索引优化向导”来优化索引的向导。通过它能够决定选择哪些列做为索引列

    二、何时使用聚集索引或非聚集索引

    以下的表总结了何时使用聚集索引或非聚集索引(非常重要):

    动作描写叙述

    使用聚集索引

    使用非聚集索引

    列常常被分组排序

    返回某范围内的数据

    不应

    一个或极少不同值

    不应

    不应

    小数目的不同值

    不应

    大数目的不同值

    不应

    频繁更新的列

    不应

    外键列

    主键列

    频繁改动索引列

    不应

    展开全文
  • 数据库 - 索引基本表创建与删除

    千次阅读 2015-05-05 10:04:43
    修改基本表ALTER TABLE <表名> [ ADD <新列名> <数据类型> [ 完整性约束 ] ] [ DROP <完整性约束名> ] [ ALTER COLUMN<列名> <数据类型> ]; [例8]向Student增加“入学...不论基本表中原来是否已有数据,新增加的列

    修改基本表

    ALTER TABLE <表名>
    [ ADD <新列名> <数据类型> [ 完整性约束 ] ]
    [ DROP <完整性约束名> ]
    [ ALTER COLUMN<列名> <数据类型> ];
    
    [例8]向Student表增加“入学时间”列,其数据类型为日期型。
         ALTER TABLE Student ADD S_entrance DATE;
    不论基本表中原来是否已有数据,新增加的列一律为空值。 
    [例9]将年龄的数据类型由字符型(假设原来的数据类型是字符型)改为整数。
                ALTER TABLE Student ALTER COLUMN Sage INT;
    [例10]增加课程名称必须取唯一值的约束条件。
                ALTER TABLE Course ADD UNIQUE(Cname); 
    

    删除基本表

    DROP TABLE <表名>[RESTRICT| CASCADE];
    RESTRICT:删除表是有限制的。
    欲删除的基本表不能被其他表的约束所引用
    如果存在依赖该表的对象,则此表不能被删除
    CASCADE:删除该表没有限制。
    在删除基本表的同时,相关的依赖对象一起删除 
    
        [例11]  删除Student表
         DROP TABLE  Student  CASCADE ;
    基本表定义被删除,数据被删除
    表上建立的索引、视图、触发器等一般也将被删除 
    
    [例12]若表上建有视图,选择RESTRICT时表不能删除   
        CREATE VIEW IS_Student      
        AS 
            SELECT Sno,Sname,Sage
            FROM  Student
                WHERE Sdept='IS'DROP TABLE Student RESTRICT;   
              --ERROR: cannot drop table Student because other 
                                objects depend on it
    
    [例12]如果选择CASCADE时可以删除表,视图也自动被删除 
    DROP TABLE Student CASCADE;         
     --NOTICE: drop cascades to view IS_Student
    SELECT * FROM IS_Student;
    --ERROR: relation " IS_Student " does not exist 
    

    索引的建立与删除

    建立索引的目的:加快查询速度
    谁可以建立索引
    DBA 或 表的属主(即建立表的人)
    DBMS一般会自动建立以下列上的索引
    PRIMARY KEY
    UNIQUE
    谁 维护索引
    DBMS自动完成
    使用索引
    DBMS自动选择是否使用索引以及使用哪些索引
    RDBMS中索引一般采用B+树、HASH索引来实现
    B+树索引具有动态平衡的优点
    HASH索引具有查找速度快的特点
    采用B+树,还是HASH索引 则由具体的RDBMS来决定
    索引是关系数据库的内部实现技术,属于内模式的范畴
    CREATE INDEX语句定义索引时,可以定义索引是唯一索引、非唯一索引或聚簇索引

    语句格式
    CREATE [UNIQUE] [CLUSTER] INDEX <索引名> 
    ON <表名>(<列名>[<次序>][,<列名>[<次序>] ]…);
    [例13] CREATE CLUSTER INDEX Stusname 
               ON   Student(Sname);
    在Student表的Sname(姓名)列上建立一个聚簇索引
    在最经常查询的列上建立聚簇索引以提高查询效率 
    一个基本表上最多只能建立一个聚簇索引 
    经常更新的列不宜建立聚簇索引 
    
      [例14]为学生-课程数据库中的Student,Course,SC三个表建  立索引。
    
    CREATE UNIQUE INDEX  Stusno ON Student(Sno);
    CREATE UNIQUE INDEX  Coucno ON Course(Cno);
    CREATE UNIQUE INDEX  SCno ON SC(Sno ASC,Cno DESC);
    
          Student表按学号升序建唯一索引
          Course表按课程号升序建唯一索引
          SC表按学号升序和课程号降序建唯一索引
    
    DROP INDEX <索引名>;
    删除索引时,系统会从数据字典中删去有关该索引的
    描述。
    [例15]  删除Student表的Stusname索引
           DROP INDEX Stusname;
    
    展开全文
  • 数据库结构及索引设计

    千次阅读 2021-12-13 20:31:52
    在数据库设计上有很重要的设计准则,称为范式设计。 范式设计 什么是范式? 范式来自英文Normal Form,简称NF。MySQL是关系型数据库,但是要想设计—好的关系,必须使关系满足一定的约束条件,此约束已经...
  • 索引分类: 主键索引: 不能为null,唯一索引可以为null 单值索引: 单列,每个表可以有多 ...、create 索引类型 索引名 on 单值: create index dep_index on tb(dept); 唯一 create unique index...
  • 搜索引擎索引之如何建立索引

    千次阅读 2016-09-29 20:11:30
     正如前述章节所述,索引结构如果建立好了,可以增加搜索的速度,那么给定一个文档集合,索引是如何建立起来的呢?建立索引的方式有很多种,本节叙述比较实用的三种建立索引的方法。  3.4.1两遍文档遍历法(2-...
  • SQL语句 创建、模式、索引

    千次阅读 2020-04-10 11:16:45
    一、数据定义语句 一、 数据定义: 操作对象 创建 删除 修改 模式 CREATE SCHEMA DROP ~ CREATE TABLE DROP ~ ALTER TABLE ...索引 ...一个数据库中可以建立多个模式,一个模式下通常包括多个...
  • mysql建立索引的几大原则

    千次阅读 2019-08-23 11:02:07
    1.选择唯一性索引 唯一性索引的值是唯一的,可以更快速的...2.为经常需要排序、分组和联合操作的字段建立索引 经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段,排序操作会浪费很多时间。如果为其建立...
  • 连接和索引总结

    千次阅读 2018-07-16 10:37:34
    nest loops hash join sort merge join 原理 先返回第一个表的结果集,然后依次循环地用第一个表的结果集中的一条结果,去匹配第二个中的数据。因此,第一个表取出多少条数据,第二个就会被访问多少次。 先分别...
  • 数据库的主键、唯一约束和索引

    千次阅读 2021-11-23 17:31:46
    (1)一个表可以没有主键,而且最多只能有一个主键。 (2)主键值必须唯一标识中的每一行,且不能为 NULL,即同一个表中不可能存在两行数据有相同的主键值。 2、MySQL 的 唯一约束。  MySQL唯一约束...
  • mysql 索引建立规则

    千次阅读 2014-03-17 09:46:13
    3、经常与其他进行连接的,在连接字段上应该建立索引 4、经常出现在Where子句中的字段,特别是大的字段,应该建立索引;  5、索引应该建在选择性高的字段上; 6、索引应该建在小字段上,对于大的文本...
  • 实验三 基本表的定义、删除与修改

    千次阅读 2020-06-02 10:50:57
    掌握建立索引的二种方法,即在基本表中建立和用命令方式建立。; 掌握删除索引的方法。 实验设备:装有SQL SERVER 2005的电脑 实验时数:2课时 类 别:验证 实验步骤: 启动Microsoft SQL Server 2005; ...
  • MySQL 索引

    千次阅读 2020-07-06 09:17:38
    花了好久整理的 MySQL 索引笔记,赶紧收藏
  • 为表字段添加索引

    万次阅读 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唯一索引:...
  • 数据库实验5——创建和删除索引

    千次阅读 2020-04-27 20:11:07
    文章目录创建和删除索引一、实验目的二、实验要求三、实验内容四、实验步骤五、 实验总结 创建和删除索引 、实验目的 1.了解索引的类型和作用。 2.学会用SQL语句对表创建和删除索引 二、实验要求 了解索引类型并...
  • 应且只对应一个实体。在特殊情况下,它们可能是一多或多一的关系,即一张原始单证对应多个实 体,或多张原始单证对应一个实体。这里的实体可以理解为基本表。明确这种对应关系后,我们设计 录入界面大有好处...
  • Mysql索引整理总结

    万次阅读 多人点赞 2018-07-18 18:17:17
    索引数据库列或多列的值进行排序的种结构,使用索引可快速访问数据库中的特定信息。 举例说明索引:如果把数据库中的某一张看成本书,那么索引就像是书的目录,可以通过目录快速查找书中指定内容的...
  • 数据库及数据库中等数据库对象的建立实验(包括关系图、完整性、权限控制、视图、索引等内容)实验1 SQL Server的安装安装过程展示实验2 设计数据库、创建数据库和数据实验5 视图实验6 完整性约束实验7 权限管理...
  • 世界上并没有完美的程序,但是我们并不因此而沮丧,因为写程序就是一个不断追求完美的过程。 文章目录创建基本索引创建索引索引数据索引单个文档索引多个文档 创建基本索引 elasticsearch可以自动识别数据类型 本...
  • 系列的文章是只试图通过产品角度出发去理解复杂庞大搜索引擎的汪写出来的,不足之处很多,欢迎广大技术、非技术同学阅读后指正错误,我们一起探讨共同进步。 本篇主要介绍搜索引擎的架构、网络爬虫、及索引...
  • 索引只是提高效率的一个因素,因此在建立索引的时候应该遵循以下原则: 在经常需要搜索的列上建立索引,可以加快搜索的速度。 在作为主键的列上创建索引,强制该列的唯一性,并组织中数据的排列结构。 在经常使用...
  • 文学懂经典算法系列之:索引查找

    千次阅读 多人点赞 2021-04-20 13:41:50
    本文关键字:经典算法、查找算法、元素查找、索引查找、...索引查找主要分为基本索引查找和分块查找,核心思想是对于无序的数据集合,先建立索引表,使得索引有序或分块有序,结合顺序查找与索引查找的方法完成查找。
  • 索引建立与删除       SQL是怎样的语言?SQL有何特点(P76)? 结构化查询语言 集数据定义语言(DDL),数据操纵语言(DML),数据控制语言(DCL)功能于一体。 SQL只要提出“做什么”,无须了解存取...
  • Mysql索引详解及基本用法

    万次阅读 2018-09-05 21:11:13
    索引是一种特殊的文件(InnoDB数据上的索引空间的一个组成部分),它们包含着数据表里所有记录的引用指针。更通俗的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度。&nbsp; &nbsp;&...
  • MySQL 创建索引索引效率验证

    千次阅读 多人点赞 2019-10-27 17:41:44
    MySQL 创建索引索引效率验证 对于一张存储数据的...优化的方式很多,一个比较简单且低成本的方式就是创建索引。 一、索引简介 索引目的是为了提高数据的查询效率。 索引的作用类似于字典前面的拼音,笔...
  • 一. 面试题及剖析 1. 今日面试题 MySQL有哪些索引索引的底层原理你熟悉吗?...如果你对数据库索引不熟悉,那基本就会被面试官贴上一个对数据库不熟悉的标签,如果他们认为你对数据库不熟悉,那这次面试的结果就

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 126,840
精华内容 50,736
关键字:

对一个基本表建立索引的目的