精华内容
下载资源
问答
  • 1、表的主键、外键必须有索引; 2、数据量超过300的表应该有索引; 3、经常与其他表进行连接的表,在连接字段...A、正确选择复合索引中的主列字段,一般是选择性较好的字段; B、复合索引的几个字段是否经常同时...

    1、表的主键、外键必须有索引;

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

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

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

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

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

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

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

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

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

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

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

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

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

    索引的建立必须慎重,对每个索引的必要性都应该经过仔细分析,要有建立的依据。

    因为太多的索引与不充分、不正确的索引对性能都毫无益处:在表上建立的每个索引都会增加存储开销,索引对于插入、删除、更新操作也会增加处理上的开销。

    另外,过多的复合索引,在有单字段索引的情况下,一般都是没有存在价值的;相反,还会降低数据增加删除时的性能,特别是对频繁更新的表来说,负面影响更大。​

    展开全文
  • 一、如何选择合适的列建立索引 1、在where从句,group by从句,order by从句,on从句的列添加索引 2、索引字段越小越好(因为数据库数据存储...1、 两个或更多个列上的索引被称作联合索引,又被称为是复合索引。 2...

    一、如何选择合适的列建立索引

    1、在where从句,group by从句,order by从句,on从句中的列添加索引
    2、索引字段越小越好(因为数据库数据存储单位是以“页”为单位的,数据存储的越多,IO也会越大)
    3、离散度大的列放到联合索引的前面(离散度:字段中不同的记录的数量,数量越多,则表明离散程度越大)

    什么是联合索引

    1、 两个或更多个列上的索引被称作联合索引,又被称为是复合索引。
    2、 利用索引中的附加列,您可以缩小搜索的范围,但使用一个具有两列的索引 不同于使用两个单独的索引。复合索引的结构与电话簿类似,人名由姓和名构成,电话簿首先按姓氏对进行排序,然后按名字对有相同姓氏的人进行排序。如果您知 道姓,电话簿将非常有用;如果您知道姓和名,电话簿则更为有用,但如果您只知道名不姓,电话簿将没有用处。
    所以说创建复合索引时,应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时,复合索引非常有用;仅对后面的任意列执行搜索时,复合索引则没有用处。

    二、使用索引的注意事项

    设计好MySql的索引可以让你的数据库飞起来,大大的提高数据库效率。设计MySql索引的时候有一下几点注意:

    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、索引不会包含有NULL值的列

    只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

    4、使用短索引

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

    5、排序的索引问题

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

    6、like语句操作

    一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

    7、不要在列上进行运算

    select * from users where YEAR(adddate)

    8、不使用NOT IN操作

    NOT IN操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替。

    展开全文
  • Oracle数据库如何提高访问性能 a) 避免在索引列上... 对于复合索引,如果每个列都为空,索引同样不存在此记录. 如果至少有一个列不为空,则记录存在于索引. 举例:  如果唯一性索引建立在表的A列和B列上,

    Oracle数据库如何提高访问性能

    a)        避免在索引列上使用ISNULL和IS NOT NULL 操作

    避免在索引中使用任何可以为空的列,ORACLE将无法使用该索引 .对于单列索引,如果列包含空值,索引中将不存在此记录. 对于复合索引,如果每个列都为空,索引中同样不存在此记录. 如果至少有一个列不为空,则记录存在于索引中.
    举例: 
    如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记录的A,B值为(123,null) , ORACLE将不接受下一条具有相同A,B值(123,null)的记录(插入). 然而如果所有的索引列都为空,ORACLE将认为整个键值为空而空不等于空. 因此你可以插入1000 条具有相同键值的记录,当然它们都是空! 因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引.

    b)        用UNION替换OR (适用于索引列) 
    通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低.

    c)        分离表和索引 
    总是将你的表和索引建立在不同的表空间内(TABLESPACES). 决不要将不属于ORACLE内部系统的对象存放到SYSTEM表空间里. 同时,确保数据表空间和索引表空间置于不同的硬盘上(通过指定表空间的方式可以实现这个要求).

    d)        共享SQL语句

    为了不重复解析相同的SQL语句,在第一次解析之后, ORACLE将SQL语句存放在内存中.这块位于系统全局区域SGA(systemglobal area)的共享池(sharedbuffer pool)中的内存可以被所有的数据库用户共享. 因此,当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同, ORACLE就能很快获得已经被解析的语句以及最好的执行路径. ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用. 
    当你向ORACLE提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句. 
    这里需要注明的是,ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语句必须完全相同(包括空格,换行等).

    e)        WHERE子句中的连接顺序. 
    ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾. 
    例如:

    示例1.(低效,执行时间156.3秒) 
    SELECT* FROM EMP E 
    WHERE SAL > 50000 
    AND JOB = ‘MANAGER' AND 25 < (SELECT COUNT(*) FROM EMP 
    WHERE MGR=E.EMPNO); 
    示例2(高效,执行时间10.6秒) 
    SELECT … FROM EMP E 
    WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO) 
    AND SAL > 50000 
    AND JOB = ‘MANAGER';

    f)         SELECT子句中避免使用 ‘ * ‘ 
    当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 ‘*' 是一个方便的方法.不幸的是,这是一个非常低效的方法. 实际上,ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间.

    g)        使用DECODE函数来减少处理时间 
    使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表. 
    例如: 
    SELECT COUNT(*),SUM(SAL) 
    FROM EMP 
    WHERE DEPT_NO = 0020 
    AND ENAME LIKE ‘SMITH%';


    SELECT COUNT(*),SUM(SAL) 
    FROM EMP 
    WHERE DEPT_NO = 0030 
    AND ENAME LIKE ‘SMITH%'; 
    你可以用DECODE函数高效地得到相同结果 
    SELECT COUNT(DECODE(DEPT_NO,0020,'X',NULL)) D0020_COUNT, 
    COUNT(DECODE(DEPT_NO,0030,'X',NULL)) D0030_COUNT, 
    SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL, 
    SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL 
    FROM EMP WHERE ENAME LIKE ‘SMITH%'; 
    类似的,DECODE函数也可以运用于GROUP BY 和ORDER BY子句中.

    h)        用TRUNCATE替代DELETE 
    当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短. 
    (注意: TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML)

    i)          尽量多使用COMMIT
    只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少: 
    COMMIT所释放的资源: 
    a. 回滚段上用于恢复数据的信息. 
    b. 被程序语句获得的锁 
    c. redo log buffer 中的空间 
    d. ORACLE为管理上述3种资源中的内部花费 
    (注意: 在使用COMMIT时必须要注意到事务的完整性,现实中效率和事务完整性往往是鱼和熊掌不可得兼)

    j)          用Where子句替换HAVING子句 
    避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销. 
    例如: 
    低效: 
    SELECT REGION,AVG(LOG_SIZE) 
    FROM LOCATION 
    GROUP BY REGION 
    HAVING REGION REGION != ‘SYDNEY' 
    AND REGION != ‘PERTH' 

    高效 
    SELECT REGION,AVG(LOG_SIZE) 
    FROM LOCATION 
    WHERE REGION REGION != ‘SYDNEY' 
    AND REGION != ‘PERTH' 
    GROUP BY REGION 
    (注意: HAVING 中的条件一般用于对一些集合函数的比较,如COUNT() 等等. 除此而外,一般的条件应该写在WHERE子句中)

    k)        用EXISTS替代IN 
    在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 
    低效: 
    SELECT * 
    FROM EMP (基础表) 
    WHERE EMPNO > 0 
    AND DEPTNO IN (SELECT DEPTNO 
    FROM DEPT 
    WHERE LOC = ‘MELB') 

    高效: 
    SELECT * 
    FROM EMP (基础表) 
    WHERE EMPNO > 0 
    AND EXISTS (SELECT ‘X' 
    FROM DEPT 
    WHERE DEPT.DEPTNO = EMP.DEPTNO 
    AND LOC = ‘MELB')

    l)          用NOTEXISTS替代NOTIN 
    在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS. 
    例如: 
    SELECT … 
    FROM EMP 
    WHERE DEPT_NO NOT IN (SELECT DEPT_NO 
    FROM DEPT 
    WHERE DEPT_CAT='A'); 

    为了提高效率.改写为: 
    (方法一: 高效,左外连接) 
    SELECT …. 
    FROM EMP A,DEPT B 
    WHERE A.DEPT_NO = B.DEPT(+) 
    AND B.DEPT_NO IS NULL 
    AND B.DEPT_CAT(+) = ‘A' 

    (方法二: 最高效) 
    SELECT …. 
    FROM EMP E 
    WHERE NOT EXISTS (SELECT ‘X' 
    FROM DEPT D 
    WHERE D.DEPT_NO = E.DEPT_NO 
    AND DEPT_CAT = ‘A');

    展开全文
  • 为字段name,status,address建立复合索引 1、最左匹配法则: 如果索引了多列,要遵守这个法则。指的是查询从索引的最左前列开始,并且不能跳过中间的列。 1.1、全值匹配,联合索引生效 1.2、使用name字段,索引也...

    Mysql在什么情况下索引失效,如何避免索引失效

    1、环境准备
    mysql的版本
    在这里插入图片描述
    2、数据库表
    在这里插入图片描述

    下面来看各种情况:

    为字段name,status,address建立复合索引
    在这里插入图片描述

    1、最左匹配法则:
    如果索引了多列,要遵守这个法则。指的是查询从索引的最左前列开始,并且不能跳过中间的列。

    1.1、全值匹配,联合索引生效
    在这里插入图片描述
    1.2、使用name字段,索引也生效,只是索引的长度发生了变化,使用了联合索引中的name字段
    在这里插入图片描述
    1.3、使用name字段和status字段,索引生效,使用了联合索引中的name和status字段。
    在这里插入图片描述
    1.4、使用status和address字段,索引失效。不走索引,因为跳过了最左列的name。
    在这里插入图片描述
    1.5、下面这种情况也是走索引的,有很多人认为这种情况是不走索引,其实是错误的,一开始我们介绍了联合索引的概念,和where条件后顺序是没有关系的,只是看它包不包含这一列,包含了并且无跳过就会走索引。
    在这里插入图片描述
    1.6、使用name字段和address字段,索引还是生效的,只是走的是name字段的索引,后面address字段不走索引。
    在这里插入图片描述
    1.7、范围查询右边的列,不能走索引。在status字段使用范围查询,该列后面的字段索引失效。
    在这里插入图片描述
    1.8、不要在索引列上进行运算操作,索引会失效。
    在这里插入图片描述
    1.9、隐式转换,字符串类型不加单引号,会造成索引失效。
    在这里插入图片描述

    1.10、尽量使用覆盖索引,表避免使用select * ,虽然下面这种情况是使用了索引,但是进行了回表查询,在Extra字段中描述了该信息,Using index condition
    在这里插入图片描述
    如果我们使用查询的全部是索引列,执行计划就会发生改变
    在这里插入图片描述
    1.11、使用or分割开的条件,如果or前的条件中的列有索引而后面的列没有索引,那么涉及的索引的列都不会被用到。name字段有索引,nicename字段没有索引,name字段的索引也是失效。
    在这里插入图片描述
    为了解决or索引失效,解决的办法是:使用union。

    1.12、以%开头的like模糊匹配索引会失效。
    在这里插入图片描述
    我们发现,以%在左侧的索引会失效,但是在%在右侧的不会失效。
    我们可以使用覆盖索引来解决%在左侧的like索引失效问题
    在这里插入图片描述
    1.13、is null和 is not null 有时索引失效问题
    在这里插入图片描述
    is null和 is not null 索引失效和生效问题,根据数据库中的条数来决定。若是null 的条数远多(区分度很明显)is null 是会走索引的,is not null 一样,取决去mysql的底层自行决定是走索引还是全表扫描。
    1.14、in 和not in
    在这里插入图片描述
    证明in走索引,not in 不走索引。

    展开全文
  • MySQL查询语句如何避免索引失效 前言 索引是数据库优化最常用也是最重要的手段之一, 通过索引通常可以帮助用户解决大多数的MySQL的性能优化问题。...# 建立复合索引 create index idx_seller_name_sta_addr on tb_sel
  • 需要用索引来解决,索引的创建规则如下:1、表的主键、外键必须有索引;2、数据量超过300的表应该有索引;3、经常与其他表进行连接的表,在...7、复合索引建立需要进行仔细分析;尽量考虑用单字段索引代替:A...
  • Oracle索引建立及SQL优化

    千次阅读 2016-10-31 09:19:56
    最近忙着处理会员积分数据迁移的工作,从原先SAP系统中将会员数据迁移到新开发的CRM系统,有好几张表...复合索引之说 如何某表的某个字段有主键约束和唯一性约束,则Oracle 则会自动在相应的约束列上建议唯一索引
  • 数据库基础

    2012-09-22 16:27:04
    §3.5.1 建立索引 112 §3.5.2 修改索引 114 §3.5.3 删除索引 115 §3.6 新索引类型 115 §3.6.1 基于函数的索引 115 §3.6.2 反向键索引 117 §3.6.3 索引组织表 117 §3.7 抽象数据类型的使用 118 §3.8 大数据...
  • 关于mysql索引的总结

    2019-07-17 17:03:47
    什么是索引:索引是对数据库一列或多列的值进行排序的一种结构,使用索引可快速访问数据库的特定信息。...复合索引建立要符合最左前缀原则。 Mysql索引数据结构: hash索引 哈希索引就是采用一...
  • 如果是,则可以建立复合索引;否则考虑单字段索引; C、如果复合索引包含的字段经常单独出现在Where子句,则分解为多个单字段索引; D、如果复合索引所包含的字段超过3个,那么仔细考虑其必要性,考虑减少复合...
  • 文章目录MySQL优化常用手段索引优化B树与B+树B树B+树比较数据库为什么使用B+树Btree索引的特点如何选择在什么列上建立索引WHERE子句的列(在筛选性好的列建立索引)多表JOIN的关联列如何选择复合索引键的顺序索引...
  • Oracle8i_9i数据库基础

    2010-03-03 14:16:58
    §3.5.1 建立索引 112 §3.5.2 修改索引 114 §3.5.3 删除索引 115 §3.6 新索引类型 115 §3.6.1 基于函数的索引 115 §3.6.2 反向键索引 117 §3.6.3 索引组织表 117 §3.7 抽象数据类型的使用 118 §3.8 大数据...
  • 24210.10.2 设计一个月产品销售交叉表查询 24510.10.3 使用固定列标题的交叉表查询 24610.11 从其他数据库中的表创建查询 24710.12 疑难解答 24810.13 现实世界—优化多表查询 249第11章 用操作查询修改数据 25111.1...
  • 24510.10.3 使用固定列标题的交叉表查询 24610.11 从其他数据库中的表创建查询 24710.12 疑难解答 24810.13 现实世界—优化多表查询 249第11章 用操作查询修改数据 25111.1 操作查询入门 25111.2 用生成表查询创建新...
  • 24510.10.3 使用固定列标题的交叉表查询 24610.11 从其他数据库中的表创建查询 24710.12 疑难解答 24810.13 现实世界—优化多表查询 249第11章 用操作查询修改数据 25111.1 操作查询入门 25111.2 用生成表查询创建新...
  • 24510.10.3 使用固定列标题的交叉表查询 24610.11 从其他数据库中的表创建查询 24710.12 疑难解答 24810.13 现实世界—优化多表查询 249第11章 用操作查询修改数据 25111.1 操作查询入门 25111.2 用生成表查询创建新...
  • 24510.10.3 使用固定列标题的交叉表查询 24610.11 从其他数据库中的表创建查询 24710.12 疑难解答 24810.13 现实世界—优化多表查询 249第11章 用操作查询修改数据 25111.1 操作查询入门 25111.2 用生成表查询创建新...
  • 24510.10.3 使用固定列标题的交叉表查询 24610.11 从其他数据库中的表创建查询 24710.12 疑难解答 24810.13 现实世界—优化多表查询 249第11章 用操作查询修改数据 25111.1 操作查询入门 25111.2 用生成表查询创建新...
  • 24510.10.3 使用固定列标题的交叉表查询 24610.11 从其他数据库中的表创建查询 24710.12 疑难解答 24810.13 现实世界—优化多表查询 249第11章 用操作查询修改数据 25111.1 操作查询入门 25111.2 用生成表查询创建新...
  • 24510.10.3 使用固定列标题的交叉表查询 24610.11 从其他数据库中的表创建查询 24710.12 疑难解答 24810.13 现实世界—优化多表查询 249第11章 用操作查询修改数据 25111.1 操作查询入门 25111.2 用生成表查询创建新...
  • 24510.10.3 使用固定列标题的交叉表查询 24610.11 从其他数据库中的表创建查询 24710.12 疑难解答 24810.13 现实世界—优化多表查询 249第11章 用操作查询修改数据 25111.1 操作查询入门 25111.2 用生成表查询创建新...
  • 24510.10.3 使用固定列标题的交叉表查询 24610.11 从其他数据库中的表创建查询 24710.12 疑难解答 24810.13 现实世界—优化多表查询 249第11章 用操作查询修改数据 25111.1 操作查询入门 25111.2 用生成表查询创建新...
  • 24510.10.3 使用固定列标题的交叉表查询 24610.11 从其他数据库中的表创建查询 24710.12 疑难解答 24810.13 现实世界—优化多表查询 249第11章 用操作查询修改数据 25111.1 操作查询入门 25111.2 用生成表查询创建新...
  • 24510.10.3 使用固定列标题的交叉表查询 24610.11 从其他数据库中的表创建查询 24710.12 疑难解答 24810.13 现实世界—优化多表查询 249第11章 用操作查询修改数据 25111.1 操作查询入门 25111.2 用生成表查询创建新...
  • 24510.10.3 使用固定列标题的交叉表查询 24610.11 从其他数据库中的表创建查询 24710.12 疑难解答 24810.13 现实世界—优化多表查询 249第11章 用操作查询修改数据 25111.1 操作查询入门 25111.2 用生成表查询创建新...
  • 24510.10.3 使用固定列标题的交叉表查询 24610.11 从其他数据库中的表创建查询 24710.12 疑难解答 24810.13 现实世界—优化多表查询 249第11章 用操作查询修改数据 25111.1 操作查询入门 25111.2 用生成表查询创建新...
  • 24510.10.3 使用固定列标题的交叉表查询 24610.11 从其他数据库中的表创建查询 24710.12 疑难解答 24810.13 现实世界—优化多表查询 249第11章 用操作查询修改数据 25111.1 操作查询入门 25111.2 用生成表查询创建新...

空空如也

空空如也

1 2 3 4 5
收藏数 95
精华内容 38
关键字:

数据库中如何建立复合索引