精华内容
下载资源
问答
  • 表单字段原先设计为字符串类型4000个长度,但由于改用fckeditor后,内容为html,数据库字段长度不够,只能改用CLOB字段。 首先看看如何将 VARCHAR2(4000)转成CLOB类型: 基本需要以下几步就可以搞定: 第一步:将...

    表单字段原先设计为字符串类型4000个长度,但由于改用fckeditor后,内容为html,数据库字段长度不够,只能改用CLOB字段。

    首先看看如何将 VARCHAR2(4000)转成CLOB类型:

    基本需要以下几步就可以搞定:

    第一步:将原来 VARCHAR2类型的字段 A 改名为 A1。

    SQL语句:ALTER  TABLE   表名  RENAME  COLUMN  A TO  A1;

            第二步:增加一个临时列,该列类型为Long。

    SQL语句:ALTER  TABLE  表名  ADD  A   LONG;

    第三步:将改变后的列的值赋新增的列。

    SQL语句:UPDATE  表名  SET  A= A1 ;

    第四步:将改变后的列类型更改为CLOB。

    SQL语句:ALTER  TABLE  表名  MODIFY(A  CLOB);

    第五步:将无用的列删除。

    SQL语句:ALTER TABLE 表名  DROP COLUMN A1;

    到此为至,就将VARCHAR2类型转成CLOB了。

    我这样修改后,结果hibernate保存时,提示:index   "XXX.PK_CXXX"   or   partition   of   such   index   is   in   unusable   state 

    意思是 索引失效。

    用sql语句查询此索引的状态:

    select index_name,index_type,tablespace_name,table_type,status from user_indexes 

    会看到status列显示为:“UNUSABLE”

    彻底解决办法:

    首先:alter session set skip_unusable_indexes=false;     //设置不要跳过失效索引

    然后:alter index 索引名称 rebuild;     //重建改索引

    最后,在执行查询语句,可以看到状态为:valid 了,至此就OK了。

    这样就可以正常保存了。


    展开全文
  • 当PK是字段名的组合,建议字段的个数不要太多,多了不但索引   占用空间大,而且速度也慢。  8. 正确认识数据冗余   主键与外键在多表中的重复出现, 不属于数据冗余,这个概念必须清楚,事实上有...

    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、删除无用的索引,避免对执行计划造成负面影响;


    展开全文
  • MySql索引类型

    万次阅读 2018-07-04 22:15:19
    Mysql支持哪几种索引索引是在MySql的存储引擎层中实现的,而不是在服务器层从数据...Innodb里,有两种形态:一是primary key形态,其leaf node里存放的是数据,而且不仅存放了索引键的数据,还存放了其他字段的数据...

    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和<>操作


    展开全文
  • 数据库建立索引 数据库、数据表建立索引的原则 数据库建立索引的原则 1,确定针对该表的操作是大量...修改复合索引将消耗更长时间,同时,复合索引也占磁盘空间。 4,对于小型的表,建立索引可能会影响性能 ...

    数据库建立索引

    数据库、数据表建立索引的原则
    数据库建立索引的原则 
    1,确定针对该表的操作是大量的查询操作还是大量的增删改操作。 
    
    2,尝试建立索引来帮助特定的查询。检查自己的sql语句,为那些频繁在where子句中出现的字段建立索引。 
    
    3,尝试建立复合索引来进一步提高系统性能。修改复合索引将消耗更长时间,同时,复合索引也占磁盘空间。 
    
    4,对于小型的表,建立索引可能会影响性能 
    
    5,应该避免对具有较少值的字段进行索引。 
    
    6,避免选择大型数据类型的列作为索引。 
    
    索引建立的原则 
    
    索引查询是数据库中重要的记录查询方法,要不要进入索引以及在那些字段上建立索引都要和实际数据库系统的查询要求结合来考虑,下面给出实际中的一些通用的原则: 
    
    1. 在经常用作过滤器的字段上建立索引; 
    
    2. 在SQL语句中经常进行GROUP BY、ORDER BY的字段上建立索引; 
    
    3. 在不同值较少的字段上不必要建立索引,如性别字段; 
    
    4. 对于经常存取的列避免建立索引; 
    
    5. 用于联接的列(主健/外健)上建立索引; 
    
    6. 在经常存取的多个列上建立复合索引,但要注意复合索引的建立顺序要按照使用的频度来确定; 
    
    7. 缺省情况下建立的是非簇集索引,但在以下情况下最好考虑簇集索引,如:含有有限数目(不是很少)唯一的列;进行大范围的查询;充分的利用索引可以减少表扫描I/0的次数,有效的避免对整表的搜索。当然合理的索引要建立在对各种查询的分析和预测中,也取决于DBA的所设计的数据库结构。 
    ------------------------------------------------------------------------------
    这是你的表结构,有三个字段,分别是id,name,cid 
    CREATE TABLE `student` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) DEFAULT NULL,
      `cid` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `name_cid_INX` (`name`,`cid`),
    ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
    索引方面:id是主键,(name,cid)是一个多列索引。
    -----------------------------------------------------------------------------
    下面是你有疑问的两个查询:
    
    EXPLAIN SELECT * FROM student WHERE   cid=1;
    
    EXPLAIN SELECT * FROM student WHERE   cid=1 AND name='小红';
    
    
    你的疑问是:sql查询用到索引的条件是必须要遵守最左前缀原则,为什么上面两个查询还能用到索引?
    ---------------------------------------------------------------------------------------------------------------------------
    
    讲上面问题之前,我先补充一些知识,因为我觉得你对索引理解是狭隘的:
    上述你的两个查询的explain结果中显示用到索引的情况类型是不一样的。,可观察explain结果中的type字段。你的查询中分别是:
    1. type: index 
    2. type: ref
    
    解释:
    index:这种类型表示是mysql会对整个该索引进行扫描。要想用到这种类型的索引,对这个索引并无特别要求,只要是索引,或者某个复合索引的一部分,mysql都可能会采用index类型的方式扫描。但是呢,缺点是效率不高,mysql会从索引中的第一个数据一个个的查找到最后一个数据,直到找到符合判断条件的某个索引。
    
    所以:对于你的第一条语句:
    EXPLAIN SELECT * FROM student WHERE   cid=1;
    判断条件是cid=1,而cid是(name,cid)复合索引的一部分,没有问题,可以进行index类型的索引扫描方式。explain显示结果使用到了索引,是index类型的方式。
    
    ---------------------------------------------------------------------------------------------------------------------------
    
    ref:这种类型表示mysql会根据特定的算法快速查找到某个符合条件的索引,而不是会对索引中每一个数据都进行一 一的扫描判断,也就是所谓你平常理解的使用索引查询会更快的取出数据。而要想实现这种查找,索引却是有要求的,要实现这种能快速查找的算法,索引就要满足特定的数据结构。简单说,也就是索引字段的数据必须是有序的,才能实现这种类型的查找,才能利用到索引。
    
    
    有些了解的人可能会问,索引不都是一个有序排列的数据结构么。不过答案说的还不够完善,那只是针对单个索引,而复合索引的情况有些同学可能就不太了解了。
    
    下面就说下复合索引:
    以该表的(name,cid)复合索引为例,它内部结构简单说就是下面这样排列的:
    
                                                                          
    
    mysql创建复合索引的规则是首先会对复合索引的最左边的,也就是第一个name字段的数据进行排序,在第一个字段的排序基础上,然后再对后面第二个的cid字段进行排序。其实就相当于实现了类似 order by name cid这样一种排序规则。
    所以:第一个name字段是绝对有序的,而第二字段就是无序的了。所以通常情况下,直接使用第二个cid字段进行条件判断是用不到索引的,当然,可能会出现上面的使用index类型的索引。这就是所谓的mysql为什么要强调最左前缀原则的原因。
    
    那么什么时候才能用到呢?
    当然是cid字段的索引数据也是有序的情况下才能使用咯,什么时候才是有序的呢?观察可知,当然是在name字段是等值匹配的情况下,cid才是有序的。发现没有,观察两个name名字为 c 的cid字段是不是有序的呢。从上往下分别是4 5。
    这也就是mysql索引规则中要求复合索引要想使用第二个索引,必须先使用第一个索引的原因。(而且第一个索引必须是等值匹配)。
    ---------------------------------------------------------------------------------------------------------------------------
    所以对于你的这条sql查询:
    EXPLAIN SELECT * FROM student WHERE   cid=1 AND name='小红';
    没有错,而且复合索引中的两个索引字段都能很好的利用到了!因为语句中最左面的name字段进行了等值匹配,所以cid是有序的,也可以利用到索引了。
    
    你可能会问:我建的索引是(name,cid)。而我查询的语句是cid=1 AND name='小红'; 我是先查询cid,再查询name的,不是先从最左面查的呀?
    
    好吧,我再解释一下这个问题:首先可以肯定的是把条件判断反过来变成这样 name='小红' and cid=1; 最后所查询的结果是一样的。
    那么问题产生了?既然结果是一样的,到底以何种顺序的查询方式最好呢?
    
    所以,而此时那就是我们的mysql查询优化器该登场了,mysql查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高,最后才生成真正的执行计划。所以,当然是我们能尽量的利用到索引时的查询顺序效率最高咯,所以mysql查询优化器会最终以这种顺序进行查询执行。
    ---------------------------------------------------------------------------
    索引创建规则:
     1、表的主键、外键必须有索引;
     2、数据量超过300的表应该有索引; 
     3、经常与其他表进行连接的表,在连接字段上应该建立索引
     4、经常出现在Where子句中的字段,特别是大表的字段,应该建立索引;
     5、索引应该建在选择性高的字段上; 
     6、索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引;
     7、复合索引的建立需要进行仔细分析;尽量考虑用单字段索引代替: 
     A、正确选择复合索引中的主列字段,一般是选择性较好的字段;
     B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中?单字段查询是否极少甚至没有?如果是,
    则可以建立复合索引;否则考虑单字段索引; 
     C、如果复合索引中包含的字段经常单独出现在Where子句中,则分解为多个单字段索引;
     D、如果复合索引所包含的字段超过3个,那么仔细考虑其必要性,考虑减少复合的字段;
     E、如果既有单字段索引,又有这几个字段上的复合索引,一般可以删除复合索引;
     8、频繁进行数据操作的表,不要建立太多的索引;
     9、删除无用的索引,避免对执行计划造成负面影响; 以上是一些普遍的建立索引时的判断依据。
    一言以蔽之,索引的建立必须慎重,对每个索引的必要性都应该经过仔细分析,要有建立的依据。
    因为太多的索引与不充分、不正确的索引对性能都毫无益处:在表上建立的每个索引都会增加存储开销,
    索引对于插入、删除、更新操作也会增加处理上的开销。另外,过多的复合索引,在有单字段索引的情况下,
    一般都是没有存在价值的;相反,还会降低数据增加删除时的性能,特别是对频繁更新的表来说,负面影响更大。 
    
    oracle的索引陷阱
    一个表中有几百万条数据,对某个字段加了索引,但是查询时性能并没有什么提高,这主要可能是oracle的索引限制造成的。 
    oracle的索引有一些索引限制,在这些索引限制发生的情况下,即使已经加了索引,oracle还是会执行一次全表扫描,查询的性能不会比不加索引有所提高,反而可能由于数据库维护索引的系统开销造成性能更差。 
    下面是一些常见的索引限制问题。
     
    1、使用不等于操作符(<>, !=)
    下面这种情况,即使在列dept_id有一个索引,查询语句仍然执行一次全表扫描 
    select * from dept where staff_num <> 1000; 
    但是开发中的确需要这样的查询,难道没有解决问题的办法了吗? 
    有! 
    通过把用 or 语法替代不等号进行查询,就可以使用索引,以避免全表扫描:上面的语句改成下面这样的,就可以使用索引了。 
    select * from dept shere staff_num < 1000 or dept_id > 1000; 
     
    2、使用 is null 或 is not null
    使用 is null 或is nuo null也会限制索引的使用,因为数据库并没有定义null值。如果被索引的列中有很多null,就不会使用这个索引(除非索引是一个位图索引,关于位图索引,会在以后的blog文章里做详细解释)。在sql语句中使用null会造成很多麻烦。 
    解决这个问题的办法就是:建表时把需要索引的列定义为非空(not null)
     
    3、使用函数
    如果没有使用基于函数的索引,那么where子句中对存在索引的列使用函数时,会使优化器忽略掉这些索引。下面的查询就不会使用索引: 
    select * from staff where trunc(birthdate) = '01-MAY-82'; 
    但是把函数应用在条件上,索引是可以生效的,把上面的语句改成下面的语句,就可以通过索引进行查找。 
    select * from staff where birthdate < (to_date('01-MAY-82') + 0.9999); 
     
    4、比较不匹配的数据类型
    比较不匹配的数据类型也是难于发现的性能问题之一。
    下面的例子中,dept_id是一个varchar2型的字段,在这个字段上有索引,但是下面的语句会执行全表扫描。 
    select * from dept where dept_id = 900198; 
    这是因为oracle会自动把where子句转换成to_number(dept_id)=900198,就是3所说的情况,这样就限制了索引的使用。
    把SQL语句改为如下形式就可以使用索引 
    select * from dept where dept_id = '900198';
     
    
     
    
    二,
    
     各种索引使用场合及建议
    
     
    
    (1)B*Tree索引。
    
    常规索引,多用于oltp系统,快速定位行,应建立于高cardinality列(即列的唯一值除以行数为一个很大的值,存在很少的相同值)。
    
     Create index indexname on tablename(columnname[columnname...])
    
    (2)反向索引。
    
    B*Tree的衍生产物,应用于特殊场合,在ops环境加序列增加的列上建立,不适合做区域扫描。
    
     Create index indexname on tablename(columnname[columnname...]) reverse
    
    (3)降序索引。
    
    B*Tree的衍生产物,应用于有降序排列的搜索语句中,索引中储存了降序排列的索引码,提供了快速的降序搜索。
    
     Create index indexname on tablename(columnname DESC[columnname...])
    
    (4)位图索引。
    
    位图方式管理的索引,适用于OLAP(在线分析)和DSS(决策处理)系统,应建立于低cardinality列,
    适合集中读取,不适合插入和修改,提供比B*Tree索引更节省的空间。
    
     Create BITMAP index indexname on tablename(columnname[columnname...])
    
    在实际应用中,如果某个字段的值需要频繁更新,那么就不适合在它上面创建位图索引。
    在位图索引中,如果你更新或插入其中一条数值为N的记录,
    那么相应表中数值为N的记录(可能成百上千条)全部被Oracle锁定,
    这就意味着其它用户不能同时更新这些数值为N的记录,其它用户必须要等第一个用户提交后,
    才能获得锁,更新或插入数据,bitmap index它主要用于决策支持系统或静态数据。
    
    (5)函数索引。
    
    B*Tree的衍生产物,应用于查询语句条件列上包含函数的情况,
    索引中储存了经过函数计算的索引码值。可以在不修改应用程序的基础上能提高查询效率。
    
    索引创建策略 
    1.导入数据后再创建索引 
    2.不需要为很小的表创建索引 
    3.对于取值范围很小的字段(比如性别字段)应当建立位图索引 
    4.限制表中的索引的数目 
    5.为索引设置合适的PCTFREE值 
    6.存储索引的表空间最好单独设定
    
    唯一索引和不唯一索引都只是针对B树索引而言. 
    Oracle最多允许包含32个字段的复合索引 
    由此估计出一个查询如果使用某个索引会需要读入的数据块块数。
    需要读入的数据块越多,则 cost 越大,Oracle 也就越有可能不选择使用 index
    
     
    
    三,
    
    能用唯一索引,一定用唯一索引 
    能加非空,就加非空约束 
    一定要统计表的信息,索引的信息,柱状图的信息。 
    联合索引的顺序不同,影响索引的选择,尽量将值少的放在前面 
    只有做到以上四点,数据库才会正确的选择执行计划。
    
    ====================================================================================================================================
    
     一、 ROWID的概念
    
      存储了row在数据文件中的具体位置:64位编码的数据,A-Z, a-z, 0-9, +, 和 /,
    
      row在数据块中的存储方式
    
      SELECT ROWID, last_name FROM hr.employees WHERE department_id = 20;
    
      比如:OOOOOOFFFBBBBBBRRR
    
      OOOOOO:data object number, 对应dba_objects.data_object_id
    
      FFF:file#, 对应v$datafile.file#
    
      BBBBBB:block#
    
      RRR:row#
    
      Dbms_rowid包
    
      SELECT dbms_rowid.rowid_block_number('AAAGFqAABAAAIWEAAA') from dual;
    
      具体到特定的物理文件
    
      二、 索引的概念
    
      1、 类似书的目录结构
    
      2、 Oracle 的“索引”对象,与表关联的可选对象,提高SQL查询语句的速度
    
      3、 索引直接指向包含所查询值的行的位置,减少磁盘I/O
    
      4、 与所索引的表是相互独立的物理结构
    
      5、 Oracle 自动使用并维护索引,插入、删除、更新表后,自动更新索引
    
      6、 语法:CREATE INDEX index ON table (column[, column]...);
    
      7、 B-tree结构(非bitmap):
    
      [一]了解索引的工作原理:
    
      表:emp
    
      
    
      目标:查询Frank的工资salary
    
      建立索引:create index emp_name_idx on emp(name);
     
    
    
     
    
    
     [试验]测试索引的作用:
      1. 运行/rdbms/admin/utlxplan 脚本
    
      2. 建立测试表
    
      create table t as select * from dba_objects;
    
      insert into t select * from t;
    
      create table indextable
    
      as select rownum id,owner,object_name,subobject_name,
    
      object_id,data_object_id,object_type,created
    
      from t;
    
      3. set autotrace trace explain
    
      4. set timing on
    
      5. 分析表,可以得到cost
    
      6. 查询 object_name=’DBA_INDEXES’
    
      7. 在object_name列上建立索引
    
      8. 再查询
    
    三、 唯一索引
    
      1、 何时创建:当某列任意两行的值都不相同
    
      2、 当建立Primary Key(主键)或者Unique constraint(唯一约束)时,唯一索引将被自动建立
    
      3、 语法:CREATE UNIQUE INDEX index ON table (column);
    
      4、 演示
    
      四、 组合索引
    
      1、 何时创建:当两个或多个列经常一起出现在where条件中时,则在这些列上同时创建组合索引
    
      2、 组合索引中列的顺序是任意的,也无需相邻。但是建议将最频繁访问的列放在列表的最前面
    
      3、 演示(组合列,单独列)
    
      五、 位图索引
    
      1、 何时创建:
    
      列中有非常多的重复的值时候。例如某列保存了 “性别”信息。
    
      Where 条件中包含了很多OR操作符。
    
      较少的update操作,因为要相应的跟新所有的bitmap
    
      2、 结构:位图索引使用位图作为键值,对于表中的每一数据行位图包含了TRUE(1)、FALSE(0)、或NULL值。
    
      3、 优点:位图以一种压缩格式存放,因此占用的磁盘空间比标准索引要小得多
    
      4、 语法:CREATE BITMAP INDEX index ON table (column[, column]...);
    
      5、 掩饰:
    
      create table bitmaptable as select * from indextable where owner in('SYS','PUBLIC');
    
      分析,查找,建立索引,查找
    
      六、 基于函数的索引
    
      1、 何时创建:在WHERE条件语句中包含函数或者表达式时
    
      2、 函数包括:算数表达式、PL/SQL函数、程序包函数、SQL函数、用户自定义函数。
    
      3、 语法:CREATE INDEX index ON table (FUNCTION(column));
    
      4、 演示
    
      必须要分析表,并且query_rewrite_enabled=TRUE
    
      或者使用提示/*+ INDEX(ic_index)*/
    
     七、 反向键索引
    
      目的:比如索引值是一个自动增长的列:
    
    
      多个用户对集中在少数块上的索引行进行修改,容易引起资源的争用,比如对数据块的等待。此时建立反向索引。
    
      性能问题:
    
      语法:
    
      重建为标准索引:反之不行
    
      八、 键压缩索引
    
      比如表landscp的数据如下:
    
      site feature job
    
      Britten Park, Rose Bed 1, Prune
    
      Britten Park, Rose Bed 1, Mulch
    
      Britten Park, Rose Bed 1,Spray
    
      Britten Park, Shrub Bed 1, Mulch
    
      Britten Park, Shrub Bed 1, Weed
    
      Britten Park, Shrub Bed 1, Hoe
    
      ……
    
      查询时,以上3列均在where条件中同时出现,所以建立基于以上3列的组合索引。但是发现重复值很多,所以考虑压缩特性。
    
      Create index zip_idx
    
      on landscp(site, feature, job)
    
      compress 2;
    
      将索引项分成前缀(prefix)和后缀(postfix)两部分。前两项被放置到前缀部分。
    
      Prefix 0: Britten Park, Rose Bed 1
    
      Prefix 1: Britten Park, Shrub Bed 1
    
      实际所以的结构为:
    
      0 Prune
    
      0 Mulch
    
      0 Spray
    
      1 Mulch
    
      1 Weed
    
      1 Hoe
    
      特点:组合索引的前缀部分具有非选择性时,考虑使用压缩。减少I/O,增加性能。
    
      九、 索引组织表(IOT)
    
      将表中的数据按照索引的结构存储在索引中,提高查询速度。
    
      牺牲插入更新的性能,换取查询性能。通常用于数据仓库,提供大量的查询,极少的插入修改工作。
    
      必须指定主键。插入数据时,会根据主键列进行B树索引排序,写入磁盘。
    
      十、 分区索引
    
      簇:
    
      A cluster is a group of tables that share the same data blocks because they share common columns and are often used together.
    
    
     
    
    ====================================================================================================================================
    
     
    
    为什么要创建索引呢?这是因为,创建索引可以大大提高系统的性能。 
    第一,通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。 
    第二,可以大大加快 数据的检索速度,这也是创建索引的最主要的原因。 
    第三,可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。 
    第四,在使用分组和排序 子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。 
    第五,通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。 
    
    也许会有人要问:增加索引有如此多的优点,为什么不对表中的每一个列创建一个索引呢?这种想法固然有其合理性,然而也有其片面性。虽然,索引有许多优点, 但是,为表中的每一个列都增加索引,是非常不明智的。这是因为,增加索引也有许多不利的一个方面。 
    
    第一,创建索引和维护索引要耗费时间,这种时间随着数据 量的增加而增加。 
    第二,索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大。 
    第三,当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。 
    
    索引是建立在数据库表中的某些列的上面。因此,在创建索引的时候,应该仔细考虑在哪些列上可以创建索引,在哪些列上不能创建索引。一般来说,应该在这些列 上创建索引,例如: 
    
    在经常需要搜索的列上,可以加快搜索的速度; 
    在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构; 
    在经常用在连接的列上,这 些列主要是一些外键,可以加快连接的速度; 
    在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的; 
    在经常需要排序的列上创 建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间; 
    在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。 
    
    
    同样,对于有些列不应该创建索引。一般来说,不应该创建索引的的这些列具有下列特点: 
    
    第一,对于那些在查询中很少使用或者参考的列不应该创建索引。这是因 为,既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。 
    第二,对于那 些只有很少数据值的列也不应该增加索引。这是因为,由于这些列的取值很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比 例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度。 
    第三,对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为,这些列的数据量要么相当大,要么取值很少。 
    第四,当修改性能远远大于检索性能时,不应该创建索 引。这是因为,修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因 此,当修改性能远远大于检索性能时,不应该创建索引。 
    
    创建索引的方法和索引的特征 
    创建索引的方法 
    创建索引有多种方法,这些方法包括直接创建索引的方法和间接创建索引的方法。直接创建索引,例如使用CREATE INDEX语句或者使用创建索引向导,间接创建索引,例如在表中定义主键约束或者唯一性键约束时,同时也创建了索引。虽然,这两种方法都可以创建索引,但是,它们创建索引的具体内容是有区别的。 
    使用CREATE INDEX语句或者使用创建索引向导来创建索引,这是最基本的索引创建方式,并且这种方法最具有柔性,可以定制创建出符合自己需要的索引。在使用这种方式创建索引时,可以使用许多选项,例如指定数据页的充满度、进行排序、整理统计信息等,这样可以优化索引。使用这种方法,可以指定索引的类型、唯一性和复合性,也就是说,既可以创建聚簇索引,也可以创建非聚簇索引,既可以在一个列上创建索引,也可以在两个或者两个以上的列上创建索引。 
    
    通过定义主键约束或者唯一性键约束,也可以间接创建索引。主键约束是一种保持数据完整性的逻辑,它限制表中的记录有相同的主键记录。在创建主键约束时,系统自动创建了一个唯一性的聚簇索引。虽然,在逻辑上,主键约束是一种重要的结构,但是,在物理结构上,与主键约束相对应的结构是唯一性的聚簇索引。换句话说,在物理实现上,不存在主键约束,而只存在唯一性的聚簇索引。同样,在创建唯一性键约束时,也同时创建了索引,这种索引则是唯一性的非聚簇索引。因此,当使用约束创建索引时,索引的类型和特征基本上都已经确定了,由用户定制的余地比较小。 
    
    当在表上定义主键或者唯一性键约束时,如果表中已经有了使用CREATE INDEX语句创建的标准索引时,那么主键约束或者唯一性键约束创建的索引覆盖以前创建的标准索引。也就是说,主键约束或者唯一性键约束创建的索引的优先级高于使用CREATE INDEX语句创建的索引。 
    
    索引的特征 
    索引有两个特征,即唯一性索引和复合索引。 
    唯一性索引保证在索引列中的全部数据是唯一的,不会包含冗余数据。如果表中已经有一个主键约束或者唯一性键约束,那么当创建表或者修改表时,SQL Server自动创建一个唯一性索引。然而,如果必须保证唯一性,那么应该创建主键约束或者唯一性键约束,而不是创建一个唯一性索引。当创建唯一性索引时,应该认真考虑这些规则:当在表中创建主键约束或者唯一性键约束时,SQL Server自动创建一个唯一性索引;如果表中已经包含有数据,那么当创建索引时,SQL Server检查表中已有数据的冗余性;每当使用插入语句插入数据或者使用修改语句修改数据时,SQL Server检查数据的冗余性:如果有冗余值,那么SQL Server取消该语句的执行,并且返回一个错误消息;确保表中的每一行数据都有一个唯一值,这样可以确保每一个实体都可以唯一确认;只能在可以保证实体完整性的列上创建唯一性索引,例如,不能在人事表中的姓名列上创建唯一性索引,因为人们可以有相同的姓名。 
    
    复合索引就是一个索引创建在两个列或者多个列上。在搜索时,当两个或者多个列作为一个关键值时,最好在这些列上创建复合索引。当创建复合索引时,应该考虑这些规则:最多可以把16个列合并成一个单独的复合索引,构成复合索引的列的总长度不能超过900字节,也就是说复合列的长度不能太长;在复合索引中,所有的列必须来自同一个表中,不能跨表建立复合列;在复合索引中,列的排列顺序是非常重要的,因此要认真排列列的顺序,原则上,应该首先定义最唯一的列,例如在(COL1,COL2)上的索引与在(COL2,COL1)上的索引是不相同的,因为两个索引的列的顺序不同;为了使查询优化器使用复合索引,查询语句中的WHERE子句必须参考复合索引中第一个列;当表中有多个关键列时,复合索引是非常有用的;使用复合索引可以提高查询性能,减少在一个表中所创建的索引数量。
    
    
    ---
    
    索引的类型 
    根据索引的顺序与数据表的物理顺序是否相同,可以把索引分成两种类型。一种是数据表的物理顺序与索引顺序相同的聚簇索引,另一种是数据表的物理顺序与索引顺序不相同的非聚簇索引。 
    
    聚簇索引的体系结构 
    索引的结构类似于树状结构,树的顶部称为叶级,树的其它部分称为非叶级,树的根部在非叶级中。同样,在聚簇索引中,聚簇索引的叶级和非叶级构成了一个树状结构,索引的最低级是叶级。在聚簇索引中,表中的数据所在的数据页是叶级,在叶级之上的索引页是非叶级,索引数据所在的索引页是非叶级。在聚簇索引中,数据值的顺序总是按照升序排列。 
    
    应该在表中经常搜索的列或者按照顺序访问的列上创建聚簇索引。当创建聚簇索引时,应该考虑这些因素:每一个表只能有一个聚簇索引,因为表中数据的物理顺序只能有一个;表中行的物理顺序和索引中行的物理顺序是相同的,在创建任何非聚簇索引之前创建聚簇索引,这是因为聚簇索引改变了表中行的物理顺序,数据行按照一定的顺序排列,并且自动维护这个顺序;关键值的唯一性要么使用UNIQUE关键字明确维护,要么由一个内部的唯一标识符明确维护,这些唯一性标识符是系统自己使用的,用户不能访问;聚簇索引的平均大小大约是数据表的百分之五,但是,实际的聚簇索引的大小常常根据索引列的大小变化而变化;在索引的创建过程中,SQL Server临时使用当前数据库的磁盘空间,当创建聚簇索引时,需要1.2倍的表空间的大小,因此,一定要保证有足够的空间来创建聚簇索引。 
    
    当系统访问表中的数据时,首先确定在相应的列上是否存在有索引和该索引是否对要检索的数据有意义。如果索引存在并且该索引非常有意义,那么系统使用该索引访问表中的记录。系统从索引开始浏览到数据,索引浏览则从树状索引的根部开始。从根部开始,搜索值与每一个关键值相比较,确定搜索值是否大于或者等于关键值。这一步重复进行,直到碰上一个比搜索值大的关键值,或者该搜索值大于或者等于索引页上所有的关键值为止。 
    
    非聚簇索引的体系结构 
    非聚簇索引的结构也是树状结构,与聚簇索引的结构非常类似,但是也有明显的不同。 
    在非聚簇索引中,叶级仅包含关键值,而没有包含数据行。非聚簇索引表示行的逻辑顺序。 非聚簇索引有两种体系结构:一种体系结构是在没有聚簇索引的表上创建非聚簇索引,另一种体系结构是在有聚簇索引的表上创建非聚簇索引。 
    
    如果一个数据表中没有聚簇索引,那么这个数据表也称为数据堆。当非聚簇索引在数据堆的顶部创建时,系统使用索引页中的行标识符指向数据页中的记录。行标识符存储了数据所在位置的信息。数据堆是通过使用索引分配图(IAM)页来维护的。IAM页包含了数据堆所在簇的存储信息。在系统表sysindexes 中,有一个指针指向了与数据堆相关的第一个IAM页。系统使用IAM页在数据堆中浏览和寻找可以插入新的记录行的空间。这些数据页和在这些数据页中的记录没有任何的顺序并且也没有链接在一起。在这些数据页之间的唯一的连接是IAM中记录的顺序。当在数据堆上创建了非聚簇索引时,叶级中包含了指向数据页的行标识符。行标识符指定记录行的逻辑顺序,由文件ID、页号和行ID组成。这些行的标识符维持唯一性。非聚簇索引的叶级页的顺序不同于表中数据的物理顺序。这些关键值在叶级中以升序维持。 
    
    当非聚簇索引创建在有聚簇索引的表上的时候,系统使用索引页中的指向聚簇索引的聚簇键。聚簇键存储了数据的位置信息。如果某一个表有聚簇索引,那么非聚簇索引的叶级包含了映射到聚簇键的聚簇键值,而不是映射到物理的行标识符。当系统访问有非聚簇索引的表中数据时,并且这种非聚簇索引创建在聚簇索引上,那么它首先从非聚簇索引来找到指向聚簇索引的指针,然后通过使用聚簇索引来找到数据。 
    当需要以多种方式检索数据时,非聚簇索引是非常有用的。当创建非聚簇索引时,要考虑这些情况:在缺省情况下,所创建的索引是非聚簇索引;在每一个表上面,可以创建不多于249个非聚簇索引,而聚簇索引最多只能有一个。 
    系统如何访问表中的数据 
    一般地,系统访问数据库中的数据,可以使用两种方法:表扫描和索引查找。第一种方法是表扫描,就是指系统将指针放置在该表的表头数据所在的数据页上,然后按照数据页的排列顺序,一页一页地从前向后扫描该表数据所占有的全部数据页,直至扫描完表中的全部记录。在扫描时,如果找到符合查询条件的记录,那么就将这条记录挑选出来。最后,将全部挑选出来符合查询语句条件的记录显示出来。第二种方法是使用索引查找。索引是一种树状结构,其中存储了关键字和指向包含关键字所在记录的数据页的指针。当使用索引查找时,系统沿着索引的树状结构,根据索引中关键字和指针,找到符合查询条件的的记录。最后,将全部查找到的符合查询语句条件的记录显示出来。 
    在SQL Server中,当访问数据库中的数据时,由SQL Server确定该表中是否有索引存在。如果没有索引,那么SQL Server使用表扫描的方法访问数据库中的数据。查询处理器根据分布的统计信息生成该查询语句的优化执行规划,以提高访问数据的效率为目标,确定是使用表扫描还是使用索引。 
    索引的选项 
    在创建索引时,可以指定一些选项,通过使用这些选项,可以优化索引的性能。这些选项包括FILLFACTOR选项、PAD_INDEX选项和SORTED_DATA_REORG选项。 
    使用FILLFACTOR选项,可以优化插入语句和修改语句的性能。当某个索引页变满时,SQL Server必须花费时间分解该页,以便为新的记录行腾出空间。使用FILLFACTOR选项,就是在叶级索引页上分配一定百分比的自由空间,以便减少页的分解时间。当在有数据的表中创建索引时,可以使用FILLFACTOR选项指定每一个叶级索引节点的填充的百分比。缺省值是0,该数值等价于100。在创建索引的时候,内部索引节点总是留有了一定的空间,这个空间足够容纳一个或者两个表中的记录。在没有数据的表中,当创建索引的时候,不要使用该选项,因为这时该选项是没有实际意义的。另外,该选项的数值在创建时指定以后,不能动态地得到维护,因此,只应该在有数据的表中创建索引时才使用。 
    PAD_INDEX 选项将FILLFACTOR选项的数值同样也用于内部的索引节点,使内部的索引节点的填充度与叶级索引的节点中的填充度相同。如果没有指定FILLFACTOR选项,那么单独指定PAD_INDEX选项是没有实际意义的,这是因为PAD_INDEX选项的取值是由FILLFACTOR选项的取值确定的。 
    当创建聚簇索引时,SORTED_DATA_REORG选项清除排序,因此可以减少建立聚簇索引所需要的时间。当在一个已经变成碎块的表上创建或者重建聚簇索引时,使用SORTED_DATA_REORG选项可以压缩数据页。当重新需要在索引上应用填充度时,也使用该选项。当使用 SORTED_DATA_REORG选项时,应该考虑这些因素:SQL Server确认每一个关键值是否比前一个关键值高,如果都不高,那么不能创建索引;SQL Server要求1.2倍的表空间来物理地重新组织数据;使用SORTED_DATA_REORG选项,通过清除排序进程而加快索引创建进程;从表中物理地拷贝数据;当某一个行被删除时,其所占的空间可以重新利用;创建全部非聚簇索引;如果希望把叶级页填充到一定的百分比,可以同时使用 FILLFACTOR选项和SORTED_DATA_REORG选项。 
    索引的维护 
    为了维护系统性能,索引在创建之后,由于频繁地对数据进行增加、删除、修改等操作使得索引页发生碎块,因此,必须对索引进行维护。 
    使用DBCC SHOWCONTIG语句,可以显示表的数据和索引的碎块信息。当执行DBCC SHOWCONTIG语句时,SQL Server浏览叶级上的整个索引页,来确定表或者指定的索引是否严重碎块。DBCC SHOWCONTIG语句还能确定数据页和索引页是否已经满了。当对表进行大量的修改或者增加大量的数据之后,或者表的查询非常慢时,应该在这些表上执行 DBCC SHOWCONTIG语句。当执行DBCC SHOWCONTIG语句时,应该考虑这些因素:当执行DBCC SHOWCONTIG语句时,SQL Server要求指定表的ID号或者索引的ID号,表的ID号或者索引的ID号可以从系统表sysindexes中得到;应该确定多长时间使用一次 DBCC SHOWCONTIG语句,这个时间长度要根据表的活动情况来定,每天、每周或者每月都可以。 
    使用DBCC DBREINDEX语句重建表的一个或者多个索引。当希望重建索引和当表上有主键约束或者唯一性键约束时,执行DBCC DBREINDEX语句。除此之外,执行DBCC DBREINDEX语句还可以重新组织叶级索引页的存储空间、删除碎块和重新计算索引统计。当使用执行DBCC DBREINDEX语句时,应该考虑这些因素:根据指定的填充度,系统重新填充每一个叶级页;使用DBCC DBREINDEX语句重建主键约束或者唯一性键约束的索引;使用SORTED_DATA_REORG选项可以更快地创建聚簇索引,如果没有排列关键值,那么不能使用DBCC DBREINDEX语句;DBCC DBREINDEX语句不支持系统表。另外,还可以使用数据库维护规划向导自动地进行重建索引的进程。 
    统计信息是存储在SQL Server中的列数据的样本。这些数据一般地用于索引列,但是还可以为非索引列创建统计。SQL Server维护某一个索引关键值的分布统计信息,并且使用这些统计信息来确定在查询进程中哪一个索引是有用的。查询的优化依赖于这些统计信息的分布准确度。查询优化器使用这些数据样本来决定是使用表扫描还是使用索引。当表中数据发生变化时,SQL Server周期性地自动修改统计信息。索引统计被自动地修改,索引中的关键值显著变化。统计信息修改的频率由索引中的数据量和数据改变量确定。例如,如果表中有10000行数据,1000行数据修改了,那么统计信息可能需要修改。然而,如果只有50行记录修改了,那么仍然保持当前的统计信息。除了系统自动修改之外,用户还可以通过执行UPDATE STATISTICS语句或者sp_updatestats系统存储过程来手工修改统计信息。使用UPDATE STATISTICS语句既可以修改表中的全部索引,也可以修改指定的索引。 
    使用SHOWPLAN和STATISTICS IO语句可以分析索引和查询性能。使用这些语句可以更好地调整查询和索引。SHOWPLAN语句显示在连接表中使用的查询优化器的每一步以及表明使用哪一个索引访问数据。使用SHOWPLAN语句可以查看指定查询的查询规划。当使用SHOWPLAN语句时,应该考虑这些因素。SET SHOWPLAN_ALL语句返回的输出结果比SET SHOWPLAN_TEXT语句返回的输出结果详细。然而,应用程序必须能够处理SET SHOWPLAN_ALL语句返回的输出结果。SHOWPLAN语句生成的信息只能针对一个会话。如果重新连接SQL Server,那么必须重新执行SHOWPLAN语句。STATISTICS IO语句表明输入输出的数量,这些输入输出用来返回结果集和显示指定查询的逻辑的和物理的I/O的信息。可以使用这些信息来确定是否应该重写查询语句或者重新设计索引。使用STATISTICS IO语句可以查看用来处理指定查询的I/O信息。 
    就象SHOWPLAN语句一样,优化器隐藏也用来调整查询性能。优化器隐藏可以对查询性能提供较小的改进,并且如果索引策略发生了改变,那么这种优化器隐藏就毫无用处了。因此,限制使用优化器隐藏,这是因为优化器隐藏更有效率和更有柔性。当使用优化器隐藏时,考虑这些规则:指定索引名称、当 index_id为0时为使用表扫描、当index_id为1时为使用聚簇索引;优化器隐藏覆盖查询优化器,如果数据或者环境发生了变化,那么必须修改优化器隐藏。
    ------------------------------------------------------------------------------
    mysql建立索引的一些小规则
    1、表的主键、外键必须有索引;
    
    2、数据量超过300的表应该有索引;
    
    3、经常与其他表进行连接的表,在连接字段上应该建立索引;
    
    4、经常出现在Where子句中的字段,特别是大表的字段,应该建立索引;
    
    5、索引应该建在选择性高的字段上;
    
    6、索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引;
    
    7、复合索引的建立需要进行仔细分析;尽量考虑用单字段索引代替:
    
           A、正确选择复合索引中的主列字段,一般是选择性较好的字段;
           B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中?单字段查询是否极少甚至没有?如果是,则可以建立复合索引;否则考虑单字段索引;
           C、如果复合索引中包含的字段经常单独出现在Where子句中,则分解为多个单字段索引;
           D、如果复合索引所包含的字段超过3个,那么仔细考虑其必要性,考虑减少复合的字段;
           E、如果既有单字段索引,又有这几个字段上的复合索引,一般可以删除复合索引;
    
    8、频繁进行数据操作的表,不要建立太多的索引;
    
    9、删除无用的索引,避免对执行计划造成负面影响;
    
    以上是一些普遍的建立索引时的判断依据。一言以蔽之,索引的建立必须慎重,对每个索引的必要性都应该经过仔细分析,要有建立的依据。因为太多的索引与不充分、不正确的索引对性能都毫无益处:在表上建立的每个索引都会增加存储开销,索引对于插入、删除、更新操作也会增加处理上的开销。另外,过多的复合索引,在有单字段索引的情况下,一般都是没有存在价值的;相反,还会降低数据增加删除时的性能,特别是对频繁更新的表来说,负面影响更大。
    ------------------------------------------------------------------------

    数据表创建规则

    写在前边:
    规范是一种习惯,在Oracle数据库中笔者认为表中的字段命名规范需要引起注意,当考虑使用SSM作为框架的时候,Mybatis 的逆向工程能够根据数据库表名和数据库名字段名自动生成pojo类,其中pojo类中的属性名称就是根据数据库字段名来进行命名的。 
    当然笔者建议例如用户ID 数据库字段应写为“USER_ID”。 
    这样逆向工程生成的pojo属性 是“userId“” 符合java的驼峰命名法。
    
    数据库字段命名	Mybatis逆向工程pojo属性名
    USER_ID	userId
    user_name	userName
    1、编写目的
      使用统一的命名和编码规范,使数据库命名及编码风格标准化,以便于阅读、理解和继承。
    
    2、适用范围
      本规范适用于公司范围内所有以ORACLE作为后台数据库的应用系统和项目开发工作。
    
    3、对象命名规范
    3.1 数据库和SID
      数据库名定义为系统名+模块名
    
      ★ 全局数据库名和例程SID 名要求一致
    
      ★ 因SID 名只能包含字符和数字,所以全局数据库名和SID 名中不能含有“_”等字符
    
    3.2 表相关
    3.2.1 表空间
      ★ 面向用户的专用数据表空间以用户名+_+data命名 ,如Aud 用户专用数据表空间可命名为Aud_data
    
      ★ 面向用户的专用索引表空间以用户名+_+idx命名
    
      ★ 面向用户的专用临时表空间以用户名+_+tmp命名
    
      ★ 面向用户的专用回滚段表空间以用户名+_+rbs 命名
    
      ★ 面向应用的表空间以应用名+_data/应用名+_idx/应用名+_tmp/应用名+_rbs 命名
    
      ★ LOB 段数据专用表空间以其数据表空间+_+lobs 命名,如上例中数据表空间为Aud_data,则LOB 段表空间可命名为Aud_data_lobs
    
    3.2.2 表空间文件
      表空间文件命名以表空间名+两位数序号(序号从01开始)组成,如Aud_data01 等
    
      3.2.3 表
    
      表命名要遵循以下原则:
    
      ★ 一般表采用“系统名+t_+模块名+_+表义名” 格式构成
    
      ★ 若数据库中只含有单个模块,命名可采用“系统名+t_+表义名”格式构成
    
      ★ 模块名或表义名均以其汉语拼音的首字符命名,表义名中汉语拼音均采用小写,且字符间不加分割符;
    
      ★ 表别名命名规则:取表义名的前3 个字符加最后一个字符。如果存在冲突,适当增加字符(如取表义名的前4 个字符加最后一个字符等)
    
      ★ 临时表采用“系统名+t_tmp_+表义名” 格式构成
    
      ★ 表的命名如
    
      dft_gy_cbap:系统名(电费 df)+t_+模块名(高压 gy)++表义名(抄表安排 cbap) dft_cbbj: 系统名(电费 df)+t+表义名(抄表标记 cbbj) dft_tmp_hj: 系统名(电费 df)+tmp+表义名(合计hj)(此处为临时表) ★ 关联表命名为Re_表A_表B,Re 是Relative的缩写,表A 和表B均采用其表义名或缩写形式。
    
    3.2.4 属性(列或字段)
      属性命名遵循以下原则:
    
      ★ 采用有意义的列名,为实际含义的汉语拼音的首字符,且字符间不加任何分割符
    
      ★ 属性名前不要加表名等作为前缀
    
      ★ 属性后不加任何类型标识作为后缀
    
      ★ 不要使用“ID”作为列名
    
      ★ 关联字段命名以 “cd++关联表的表义名(或缩写)++字段名”进行
    
    3.2.5 主键
      ★ 任何表都必须定义主键
    
      ★ 表主键命名为:“pk++表名(或缩写)++主键标识”如“pk_YHXX_IDKH”等
    
    3.2.6 外键
      表外键命名为: “fk++表名(或缩写)+主表名(或缩写)+_+主键标识”如“fk_YHLX_YHXX_SFZH”等
    
    3.2.7 CHECK约束
      CHECK 约束命名为: “chk+_+CHECK约束的列名(或缩写)”
    
    3.2.8 UNIQUE约束
      UNIQUE 约束命名为: “unq+_+UNIQUE约束的列名(或缩写)”
    
    3.2.9 索引
      索引的命名为:“表名(或缩写)+_+列名+_idx”。其中多单词组成的属性列列名取前几个单词首字符再加末单词首字符组成如yd_kh 表khid 上的index: yd_kh_khid_idx
    
    3.2.10 触发器
      ★ AFTER型触发器
    
      系统名+tr_+<表名>_+ +[_row]
    
      ★ BEFORE型触发器
    
      系统名+tr_+<表名>+bef +[_row]
    
      ★ INSTEAD OF型触发器
    
      系统名+ti_+<表名>+_+ +[_row]
    
      ★ 各种类型的触发器中
    
      i,u,d 分别表示insert、update 和delete行级触发器,后加_row 标识,语句级触发器不加,如 yddftr_CSH_i_row
    
    3.2.11 簇
      簇以簇中要存储的各个表(或表别名)及表间加and的组成 命名,即表“A+And+表B…”,如存储GR(工人)和GRJN(工人技能)表的簇命名为GRAndGRJN
    
    3.3 视图
      视图命名以系统名v_+模块名作为前缀,其他命名规则和表的命名类似
    
    3.4 序列
      序列命名以seq_+含义名组成
    
    3.5 同义词
      同义词命名与其基础对象的名称一致,但要去除其用户前缀或含有远程数据库链接的后缀
    
    3.6 存储对象相关
    3.6.1 存储过程
      存储过程命名由“系统名+sp+_+存储过程标识(缩写)”组成存储过程标识要以实际含义的汉语拼音的首字符构成,并用下划线分割各个组成部分。如增加代理商的帐户的存储过程为“sfsp_ZJDLSZH”。
    
    3.6.2 函数
      函数命名由“系统名+f+_+函数标识”组成
    
    3.6.3 包
      包命名由“系统名+pkg+_+包标识”组成
    
    3.6.4 函数文本中的变量采用下列格式命名:
      ★ 参数变量命名采用“i (o或io)+_+名称”形式,前缀i 或o 表输入还是输出参数
    
      ★ 过程变量命名采用“l+_+名称”形式
    
      ★ 全局包变量命名采用“g+_+名称”形式
    
      ★ 游标变量命名采用“名称+_+cur”形式
    
      ★ 常量型变量命名采用“c+_+名称”形式
    
      ★ 变量名采用小写,若属于词组形式,用下划线分隔每个单词
    
      ★ 变量用来存放表中的列或行数据值时,使用%TYPE、%ROWTYPE 方式声明变量,使变量声明的类型与表中的保持同步,随表的变化而变化
    
    3.7 用户及角色
      ★ 用户命名由“系统名称++user++名词(或缩写)或名词短语(或缩写)”组成
    
      ★ 角色命名由“系统名称++role++名词(或缩写)或名词短语(或缩写)”组成
    
    3.8 数据库链接
      ★ 数据库链接命名由“远程服务器名++数据库名++link”组成
    
      ★ 若远程服务器名和数据库名一致,上式“_+数据库名”部分省去
    
    3.9 命名中的其它注意事项
      ★ 命名都不得超过30个字符。
    
      ★ 不要在对象名的字符之间留空格
    
      ★ 小心保留词,要保证你的命名没有和保留词、数据库系统或者常用访问方法冲突 4、 编码规范
    
    4.1 一般性注释
    4.1.1 注释尽可能简洁、详细而全面
      4.1.2 创建每一数据库对象时都要加上COMMENT ON注释,以说明该对象的功能和用途;建表时,对某些数据列也要加上COMMENT ON注释,以说明该列和/或列取值的含义。如:XX 表中有CZZT列属性为NUMBER(10, 0)可加COMMENT ON 注释如下COMMENT ON COLUMN XX.CZZT IS ‘0 = 正常, 1 = 等待, 2 = 超时, 3 = 登出’
    
    4.1.3 注释语法包含两种情况:单行注释、多行注释
      单行注释:注释前有两个连字符(–),一般对变量、条件子句可以采用该类注释。
    
      多行注释:符号/和/之间的内容为注释内容。对某项完整的操作建议使用该类注释。
    
    4.2 函数文本注释
    4.2.1 在每一个块和过程(存储过程、函数、包、触发器、视图等)的开头放置注释
    /*************************************** ************* name : –函数名 *function : –函数功能 *input : –输入参数 *output : –输出参数 *author : –作者 *CreateDate : –创建时间 *UpdateDate : –函数更改信息(包括作者、时间、更改内容等) ***************************************** *************/ CREATE [OR REPLACE] PROCEDURE dfsp_xxx … 4.2.2 传入参数的含义应该有所说明。如果取值范围确定,也应该一并说明。取值有特定含义的变量(如boolean类型变量),应给出每个值的含义。
    
    4.2.3 在每一个变量声明的旁边添加注释。说
      明该变量要用作什么
    
      通常,简单使用单行注释就行了,例如l_sfzh CHAR(11) –身份证号码
    
    4.2.4 在块的每个主要部分之前添加注释
      在块的每个主要部分之前增加注释,解释下—组语句目的,最好是说明该段语句及算法的目的以及要得到的结果,但不要对其细节进行过多的描述
    
    4.2.5 在块和过程的开头注释中还可以增加要访问的数据库等信息
    4.3 常用SQL 语句的编写规范
      4.3.1 CREATE语句 4.3.2 SELECT语句 查询语句采用以下原则编写(可最大化重用共享池中的SQL 语句,提高应用程序性能):
    
      ★ 将SELECT 语句分为5部分:
    
      (1) 由SELECT 开头,后跟一个显示查询结果的列表;
    
      (2) 由FROM 开头,后跟一个或多个获取数据所涉及的表;
    
      (3) 由WHERE 开头,后跟一个或多个确定所需值的条件;
    
      (4) 由GROUP BY开头,后跟一个或多个表列名,通过这些列以对查询结果进行汇总;
    
      (5) 由ORDER BY开头,后跟一个或多个表列名,通过这些列以对查询结果进行排序。
    
      ★ 每个部分分行编写,将每一行的第一个关键字与第一行的SELECT尾部对齐,如 ★ 关键字用大写,列名和表名采用小写
    
      ★ 语句中嵌入逗号时,在逗号后面加一空格,当逗号是最后一个字符时,把它放在本行
    
      ★ 当语句的同一部分要延续到下一行时,按下列格式排列: ★ 将语句中WHERE 和AND 部分格式化,书写布局类似于 ★ 当语句中出现括号时,括号的两边不留空格
    
      ★ 在SQL 语句使用运算符时,操作两边应各留一个空格,如 4.3.3 INSERT语句 4.3.4 UPDATE语句 4.3.5 DELETE语句 4.4 条件执行语句(IF)编写规范
    
      条件执行语句IF…ELSE 按以下格式编写 (1) 在IF…THEN和ELSE(或ELSIF)及ELSE…THEN和END IF间可包含一条或多条PL/SQL
    
      语句,而不需要加BEGIN 和END
    
      (2) IF…ELSE…ENDIF 语句可以嵌套
    
      (3) 注意ELSIF的写法
    
    4.5 循环语句编写规范
    4.5.1 简单循环语句
    4.5.2 FOR循环语句
      FOR 变量 IN [变量取值范围] 4.5.3 WHILE循环语句
    
      WHILE <条件表达式> 4.6 函数文本(存储过程、函数和包等)
    
      ★ 对于存储过程、函数等程序块都要有异常处理部分,在异常部分的最后都要设置OTHERS异常情态处理器,以提高程序的自检能力,格式如下: ★ 对于子程序、触发器、包等带名的程序块,要使用结束标识,如 CREATE OR REPLACE PROCEDURE XXXsp_XXX IS … BEGIN … END XXXsp_XXX;/* 此处的过程名XXXsp_XXX是可选的,规范要求写上,与块开始的CREATE相对应 */
    ------------------------------------------------------------------------
    
    
    

    备用字段 / 保留字段 / 预留字段

    数据库设计误区:备用字段 / 保留字段 / 预留字段
    
    【现象描述】
    在数据表中,不仅设计了当前所需要的字段,而且还在其中留出几个字段作为备用。
    比方说,我设计了一个人员表(Person),其中已经添加了各种必要的字段,包括姓名(Name)、性别(Sex)、出生年月日(birthday)等等。大功告成之后,我忽然想到,将来系统中应该还会有很多其它与人相关的内容吧,比方说毕业院校,比方说工作单位等等,尽管现在根本不需要填写,以后可能还是会用到的吧。拍脑袋一项,那就加入5个varchar2型的字段,分别叫做Text1、Text2……Text5,然后又想,应该还有一些日期型的字段需要备用,就又建立了三个date型的字段,分别起名叫做date1、date2、date3,……
    
    
    【原因分析】
    大家应该已经看出问题了,在这个数据表中存在大量暂时无用的字段,我们可以称之为备用字段,它们的作用是什么呢?就是以防万一,防备可能的情况。
    这似乎可以叫做防患于未然,等到需要的时候,就不需在表中增加新的字段了,而且这样做的话,一个表的数据应该会被存储在相邻的物理空间中,这对于性能也是有好处的。
    另外的原因就是,在古老的数据库中,如果改变数据库的定义(包括增加字段、改变字段的类型、删除字段等等),那么其中所有的数据就会丢失,所以这项工作非常麻烦,我们需要先建立临时表,将数据备份出来,然后创建新表,将数据导入其中,最后再删除原来的表。
    
    
    【问题所在】
    这样的做法对于项目会导致很多问题,而且原先想要解决的问题并不一定能够解决,不信的话,请往下看。
    问题一:增加大量备用字段,必定会浪费很多空间,尽管其中可能都没有具体的数据,但是仅仅是空字段也会占据一定的空间的。
    问题二:由于命名的特点,如果没有完善的文档管理流程,用不了多久(可能也就是两三年),就没有人能够说清楚到底哪个字段代表的是什么意义了。就算有文档管理,这些管理工作也会比较麻烦,而且在每次使用的时候都需要申请,还有可能会出现冲突的情况。
    问题三:增加了这些备用字段就真的会够用吗?不一定,因为我们只是每个类型的字段留出几个备用,如果数量超过,或者要使用特殊的、不常用的类型的时候,还是需要增加新的字段。比方说在上述的Person表中,我们要存储照片,那么可能就要增加一个blob类型的photo字段,这在初期设计的时候可不一定会留出这样的备用字段。而且如果没有完善的管理,谁又能说清楚倒底哪个字段已经被使用,哪个字段还可以使用呢?到时候还不是要增加新的字段。
    
    
    【解决方案】
    其实上面的这种设计方式就是一种“过度设计”,我们应该做的就是“按需设计”,在经过详细有效的分析之后,在数据表中只放置必要的字段,而不要留出大量的备用字段。
    当需要增加相关的信息的时候,就要具体情况具体分析:
    1. 如果数量很少,而且信息的性质与原表密切相关,那么就可以直接在原表上增加字段,并将相关的数据更新进去;
    2. 如果数量较大,或者并非是原表对象至关重要的属性,那么就可以新增一个表,然后通过键值连接起来;
    3. 对于表的数据的存储位置所导致的性能问题,我们可以通过在特定时间对数据库的数据进行重组来解决,而这项工作对于长期运行的数据库来说,也是需要定期进行的。
    --------------------- 
    项目设计数据库表时是否需要在表中加备用预留字段?
    
    背景:以前做项目,有用过SSH框架,或者SSM框架,数据库有Oracle,DB2。在开发过程中,有时因数据库设计者未考虑周到,业务实体有一个属性没有对应的字段,因此需要在数据库表加一个字段,又由于此字段要求不可为空,并且在开发阶段,测试数据不多,有时是drop掉了原来的表,增加了一个字段再重新建了一张表。有时一些表,设计表时会在后面加几个类型为varchar的预留字段。
    
    最近和朋友聊到这个问题,就是:为什么要这么做,好处是什么,怎么权衡这个问题。
    
    在遇到这个问题之后引起我思考:预留字段这个通用的做法是否能减少开发阶段由于考虑不周到,或后续维护阶段因为需求变更或者扩展改造而需要增加字段而造成的麻烦?
    
    就此与一些朋友进行了讨论,根据以往的项目经验和设计原则给出了一些解答,以及怎样的设计能确保数据库健壮,可扩展。大家意见不一,以下是正反方的一些意见和看法。
    
    
    
    ———————————————
    
    正反观点:需要
    原因:
    1. 持久层的设计,数据库表结构不应轻易变更。因此应设置备用字段。启用备用字段后,只修改代码,在代码中增加注释和并文档说明即可,不需要改动数据库结构,更方便。
    2. 如果没有备用字段,如果后期要加字段的话,用add column的方法会改变原先的数据库存储结构,造成数据移动,移动需要时间,而且会移动到其他数据块,add column会影响数据库性能。
    3. 对于反方提到的规范问题,只要代码和文档规范是可以避免这样的问题的,即使遇到这样的问题,也比修改表名带来的危险要小,除了要修改代码、存储过程、配置文件中的表名,还要考虑数据的迁移等问题,如此多的改动难免会出现这样那样的问题,因此保证系统的稳定性来看,携带几个扩展字段为了后续使用也无妨。
    
    
    
    ———————————————
    
    反方观点:不需要
    
    1. 如果要预留字段的话,第一个需要全面考虑的问题,如何评估:
        a. 预留多少个字段;
        b. 预留什么类型的;
        c. 预留的字段不适用怎么办——比如长度/精度不够;
        d. 预留的字段允许不允许空值呢;
    2. 数据库设置备用字段无法在字段名上体现其意义,不规范,后期维护麻烦。在需要增加字段的时候如果直接add column,也不会有太大工作,但能保证数据库字段的规范。虽然在启用备用字段的时候可以文档说明,但在POJO上对应其属性为attribute1,attribute2等,代码的可读性不强。而且,预留字段全部统一为varchar,也不太合适。
    3. 预留字段毕竟是数据库表字段,会占用数据库存储空间。
    
    4. 添加字段出现的性能问题,我之前的项目中一般都是定期对数据库进行数据整理、重组操作。
    
    
    
    
    
    各方案都有不同的侧重点,最终的你会选择选择哪种方案呢?
    
    
    
    ——————————————————————————————
    
    CSDN有另一篇博文,地址是:http://blog.csdn.net/iw1210/article/details/44752771,
    
    分析也很不错,给出了相应的解决方案,详细内容如下:
    
    
    
    数据库设计误区:备用字段 / 保留字段 / 预留字段
    
    【现象描述】
    在数据表中,不仅设计了当前所需要的字段,而且还在其中留出几个字段作为备用。
    比方说,我设计了一个人员表(Person),其中已经添加了各种必要的字段,包括姓名(Name)、性别(Sex)、出生年月日(birthday)等等。大功告成之后,我忽然想到,将来系统中应该还会有很多其它与人相关的内容吧,比方说毕业院校,比方说工作单位等等,尽管现在根本不需要填写,以后可能还是会用到的吧。拍脑袋一项,那就加入5个varchar2型的字段,分别叫做Text1、Text2……Text5,然后又想,应该还有一些日期型的字段需要备用,就又建立了三个date型的字段,分别起名叫做date1、date2、date3,……
    
    
    【原因分析】
    大家应该已经看出问题了,在这个数据表中存在大量暂时无用的字段,我们可以称之为备用字段,它们的作用是什么呢?就是以防万一,防备可能的情况。
    这似乎可以叫做防患于未然,等到需要的时候,就不需在表中增加新的字段了,而且这样做的话,一个表的数据应该会被存储在相邻的物理空间中,这对于性能也是有好处的。
    另外的原因就是,在古老的数据库中,如果改变数据库的定义(包括增加字段、改变字段的类型、删除字段等等),那么其中所有的数据就会丢失,所以这项工作非常麻烦,我们需要先建立临时表,将数据备份出来,然后创建新表,将数据导入其中,最后再删除原来的表。
    
    
    【问题所在】
    这样的做法对于项目会导致很多问题,而且原先想要解决的问题并不一定能够解决,不信的话,请往下看。
    问题一:增加大量备用字段,必定会浪费很多空间,尽管其中可能都没有具体的数据,但是仅仅是空字段也会占据一定的空间的。
    问题二:由于命名的特点,如果没有完善的文档管理流程,用不了多久(可能也就是两三年),就没有人能够说清楚到底哪个字段代表的是什么意义了。就算有文档管理,这些管理工作也会比较麻烦,而且在每次使用的时候都需要申请,还有可能会出现冲突的情况。
    问题三:增加了这些备用字段就真的会够用吗?不一定,因为我们只是每个类型的字段留出几个备用,如果数量超过,或者要使用特殊的、不常用的类型的时候,还是需要增加新的字段。比方说在上述的Person表中,我们要存储照片,那么可能就要增加一个blob类型的photo字段,这在初期设计的时候可不一定会留出这样的备用字段。而且如果没有完善的管理,谁又能说清楚倒底哪个字段已经被使用,哪个字段还可以使用呢?到时候还不是要增加新的字段。
    
    
    【解决方案】
    其实上面的这种设计方式就是一种“过度设计”,我们应该做的就是“按需设计”,在经过详细有效的分析之后,在数据表中只放置必要的字段,而不要留出大量的备用字段。
    当需要增加相关的信息的时候,就要具体情况具体分析:
    1. 如果数量很少,而且信息的性质与原表密切相关,那么就可以直接在原表上增加字段,并将相关的数据更新进去;
    2. 如果数量较大,或者并非是原表对象至关重要的属性,那么就可以新增一个表,然后通过键值连接起来;
    3. 对于表的数据的存储位置所导致的性能问题,我们可以通过在特定时间对数据库的数据进行重组来解决,而这项工作对于长期运行的数据库来说,也是需要定期进行的。
    
    
    --------------------- 
    

     

    展开全文
  • Django model字段类型清单

    千次阅读 2016-04-26 18:06:58
    Django 通过 models 实现数据库的创建、修改、删除等操作,本文为模型中一般常用的类型的清单,便于查询和使用:AutoField:一个自动递增的整型字段,添加记录它会自动增长。你通常不需要直接使用这个字段;如果你...
  • SQL数据库字段数据类型说明

    千次阅读 2018-03-15 23:23:47
    SQL数据库常用字段数据类型说明这里先总结数据类型。MySQL中的数据类型大的方面来分,可以分为:日期和时间、数值,以及字符串。下面就分开来进行总结。日期和时间数据类型MySQL数据类型含义date3字节,日期,格式:...
  • Django Model层字段类型详解

    千次阅读 2016-01-13 23:45:29
    以下参数适用所有字段类型。所有这些都是可选的。 null Field.null 如果为True,Django将在数据库中存储空值NULL,默认是False。 需要注意的是空字符串值总是在数据库中存储为空字符串,而不是NULL。只有像...
  • Mysql 字段类型分析

    万次阅读 2013-02-22 13:21:08
    mysql建表初步知识及字段类型 一、如何登录phpMyAdmin环境  phpMyAdmin可以对mysql数据库进行完全管理。在IE地址中输入地址,输入数据库帐号和密码,登录phpMyAdmin。 二、几个概念 1、数据库:在...
  • Mysql常用类型字段属性

    千次阅读 2018-08-27 11:57:00
    好比C++中,定义int类型需要多少字节,定义double类型需要多少字节一样,MySQL对表每个中的数据也会实行严格控制,这是数据驱动应用程序成功的关键。M前言 好比C++中,定义int类型需要多少字节,定义double类型...
  • 知识点:使用SQL语句创建完整版数据库、使用SQL语句创建表、数据库表创建好以后新增加字段、修改字段、删除字段的方法。删除数据库表和数据库的方法。 需要掌握的知识: SQL Server数据库第一课:常用数据库介绍、...
  • PB数据窗口对象之字段修改属性

    千次阅读 2014-07-04 11:00:51
    当数据窗口的数据来源不止一个数据表,可以用脚本控制数据窗口的修改属性来对数据表逐个进行修改。 当数据窗口是用来接受用户的手工录入数据,应该设置两方面的属性才能确保数据窗口能够用来修改数据表。一个...
  • Django model 字段类型及选项解析

    千次阅读 2018-06-22 16:12:26
    model field 类型 1、AutoField 一个自增的IntegerField,一般不直接使用,Django会自动给每张表添加一个自增的primary key。 2、BigIntegerField 64位整数, -9223372036854775808 到 9223372036854775807。...
  • 文章目录一,建立ER图 一,建立ER图 1,新建model,如下图所示,点击“New Model” 2、双击“add Diagram.“ 3,新建表,点击下图红框中内容,在点击右方... 4,双击表框,对表内容及字段进行设置,如下图所示 ...
  • /* 表引擎 */ $table->.../* 类类型 */ // - 数字 $table->bigInteger('id'); $table->integer('id'); $table->mediumInteger('id'); $table->smallInteger('id'); $table-...
  • MYSQL数据类型、范式及索引总结

    千次阅读 2021-01-02 15:42:53
    MySQL基础(MySQL5.1)MySQL的数据类型MySQL数据类型简介整数类型、浮点数类型和定点数类型日期与时间类型字符串类型二进制类型MySQL数据类型选择MySQL范式MySQL范式介绍MySQL范式联系与转化MySQL索引索引的含义及...
  • 前文阐述了创建索引要注意的索引宽度、索引顺序、索引字段的唯一值比例、索引字段的数据类型选择等,本文将重点说明索引类型的选择问题。 SQLServer 2012之前主要的索引为行索引,即我们常见的聚集索引和非聚集索引...
  • 各种Oracle索引类型介绍 逻辑上: Single column 单行索引 Concatenated 多行索引 Unique 唯一索引 NonUnique 非唯一索引 Function-based函数索引 Domain 域索引 物理上: Partitioned 分区索引 NonPartitioned ...
  • 首先 possible_keys 字段告诉我们可能用到的索引 resource_id_idx,可是为什么 key 字段里没有真正用到索引呢?这应该是 Mysql 优化器认为使用索引对该查询优化空间不大,或者说可能会使性能更差。加上 Extra 字段里...
  • MySQL数据类型和常用字段属性总结

    千次阅读 2016-12-01 15:23:56
    好比C++中,定义int类型需要多少字节,定义double类型需要多少字节一样,MySQL对表每个中的数据也会实行严格控制,这是数据驱动应用程序成功的关键。M前言 好比C++中,定义int类型需要多少字节,定义double类型...
  •   更有开发效率地使用CSV文件 ...这个工具可以对CSV文件进行简单地配置,自动生成这个CSV文件对应的C++数据结构和字段类型解析函数代码。 工程项目只要加入这些自动生成的代码,就可以更方便地使用
  • 关于MySQL索引知识与小妙招 — 学到了!

    千次阅读 多人点赞 2020-12-19 15:28:52
    一、索引基本知识 1.1 索引的优点 1、大大减少了服务器需要扫描的数据量 2、帮助服务器避免排序和临时表 3、将随机io变成顺序io 1.2 索引的用处 ...6、如果排序或分组在可用索引的最左前缀上完成的,则对表进行
  • 索引

    千次阅读 2019-07-10 18:14:31
    答: 建索引 索引是什么? 索引是一种优化查询的数据数据结构 索引为什么快? 使用B+tree的数据结构,能够快速筛选出需要的记录,避免全表扫描 为什么B+Tree快? 1 ,B+Tree拥有B-Tree的优点,深度浅,数据块大 2 ,...
  • 1、创建索引的几种方式; 2、mysql索引知识; 3、mysql索引优化;
  • 数据库索引简介

    千次阅读 2019-04-17 14:29:11
    建立的索引只对该字段有用,如果查询的字段改变,那么这个索引也就无效了,比如图书馆的书是按照书名的第一个字母排序的,那么你想要找作者叫张三的就不能用该索引了; 如果索引太多也会降低查询的速度 3、索引是...
  • Django数据库字段类型(Field types) AutoField class AutoField(**options) 它是一个根据 ID 自增长的 IntegerField 字段。通常,你不必直接使用该字段。如果你没在别的字段上指定主键,Django 就
  • 一、聚簇索引定义: 并不是一种单独索引类型,而是一种数据存储方式。 聚簇索引的顺序就是数据的物理存储顺序,而对非聚簇索引索引顺序与...具体的细节依赖于其实现方式,但InnoDB的聚簇索引实际上在用一个结构中保存
  • 三目运算来计算总页数 totalpage=sum/pagesize+sum%pagesize==0?0:1;//计算总页数,sum为总记录数 ...2.常用子查询将rownum作为另一结果集的字段来实现分页。 select ee.* from(select e.* , rownum rr
  • mysql 索引建立规则

    千次阅读 2014-03-17 09:46:13
    索引创建规则:  1、表的主键、外键必须有索引;  2、数据量超过300的表应该有... 6、索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引;  7、复合索引的建立需要进行仔细分析;尽量考虑...
  • 一,修改表 PostgreSQL 提供了一族命令用于修改现有表。 可以实现: 增加字段, 删除字段, 增加约束, 删除约束, 修改默认值, 重命名字段, 重命名表。 这些操作可以用:ALTER TABLE命令执行的。 1,增加字段 要...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 80,613
精华内容 32,245
关键字:

修改字段类型时索引依赖于列