精华内容
下载资源
问答
  • 数据库建立索引常用的规则如下:1、表的主键、外键必须有...6、索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引;7、复合索引的建立需要进行仔细分析;尽量考虑用单字段索引代替:A、正确选择复...

    数据库建立索引常用的规则如下:

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

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

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

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

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

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

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

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

    B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中?单字段查询是否

    极少甚至没有?如果是,则可以建立复合索引;否则考虑单字段索引;

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

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

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

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

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

    以上是一些普遍的建立索引时的判断依据。

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

    因为太多的索引与不充分、不正确的索引对性能都毫无益处:在表上建立的每个索引都会增加存储开销,索引对于插入、删除、更新操作也会增加处理上的开销。 另外,过多的复合索引,在有单字段索引的情况下,一般都是没有存在价值的;相反,还会降低数据增加删除时的性能,特别是对频繁更新的表来说,负面影响更大。

    总的来说,小型表肯定不建索引,

    或者数据库记录在亿条数据级以上,还是建议使用非关系型数据库。

    还有些特殊字段的数据库,比如BLOB,CLOB字段肯定也不适合建索引。

    其实这个问题更感觉偏向于做软件项目的一种经验。

    对千万级MySQL数据库建立索引的事项及提高性能的手段

    一、注意事项:

    首先,应当考虑表空间和磁盘空间是否足够。我们知道索引也是一种数据,在建立索引的时候势必也会占用大量表空间。因此在对一大表建立索引的时候首先应当考虑的是空间容量问题。

    其次,在对建立索引的时候要对表进行加锁,因此应当注意操作在业务空闲的时候进行。

    二、性能调整方面:

    首当其冲的考虑因素便是磁盘I/O。物理上,应当尽量把索引与数据分散到不同的磁盘上(不考虑阵列的情况)。逻辑上,数据表空间与索引表空间分开。这是在建索引时应当遵守的基本准则。

    其次,我们知道,在建立索引的时候要对表进行全表的扫描工作,因此,应当考虑调大初始化参数db_file_multiblock_read_count的值。一般设置为32或更大。

    再次,建立索引除了要进行全表扫描外同时还要对数据进行大量的排序操作,因此,应当调整排序区的大小。

    9i之前,可以在session级别上加大sort_area_size的大小,比如设置为100m或者更大。

    9i以后,如果初始化参数workarea_size_policy的值为TRUE,则排序区从pga_aggregate_target里自动分配获得。

    最后,建立索引的时候,可以加上nologging选项。以减少在建立索引过程中产生的大量redo,从而提高执行的速度。

    MySql在建立索引优化时需要注意的问题

    设计好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 “�a%” 不会使用索引而like “aaa%”可以使用索引。

    7,不要在列上进行运算

    select * from users where

    YEAR(adddate)

    8,不使用NOT IN和操作

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

    展开全文
  • MySQL 索引原理及建索引原则剖析索引类型非聚集索引:像字典一样最前面的几页索引,或者书的目录聚集索引:像书的页码下面的数字Innodb 表存储引擎(聚集索引)索引保存的类型:索引列的值指向数据行的指针Innodb 中...

    MySQL 索引原理及建索引原则剖析

    索引类型

    非聚集索引:像字典一样最前面的几页索引,或者书的目录

    聚集索引:像书的页码下面的数字

    Innodb 表存储引擎(聚集索引)

    索引保存的类型:

    索引列的值

    指向数据行的指针

    Innodb 中只有一个聚集索引:

    默认会拿主键 ID 作为聚集索引

    如果没有主键,会取非空的唯一索引作为聚集索引

    如果上面的都没有,innodb 会自己维护一个唯一 ID 来作为聚集索引

    当存在多个索引时:

    因为 Innodb 只有一个聚集索引,所以聚集索引会包含索引列,以及指向行数据的指针,而其他的索引包仅仅含索引列,对应的其他数据则是指向相应的聚集索引,通过聚集索引查找对应的其他值

    Innodb 索引特点:

    idb 文件:既存索引,又存对应的数据

    MyIsam 的索引特点:

    MYI: 存储索引

    MYD: 存储数据

    FRM: 存储数据结构

    索引的优缺点及建索引的原则

    索引的优点:提高检索速度,降低磁盘 I/O

    索引的缺点:索引需要存储,需要磁盘空间,索引实际上就是一张表,当字段更新(INSERT, DELETE,UPDATE)的时候,存在性能损耗

    建索引的原则:

    适合建立索引:

    频繁作为 WHERE 条件的字段

    关联字段可以建索引,例如外键

    ORDER BY , GROUP BY(底层实现是先做排序,然后分组) 后面的可以建索引

    不适合建立索引:

    WHERE 条件中用不到的字段不适合建立索引

    频繁更新的字段不适合建立索引

    数据值分布比较均匀的不适合建立索引(区分度不高)

    表的数据可以确定行数的,而且数据量很少

    索引失效

    什么情况下索引失效

    Order by

    SELECT * FROM USER ORDER BY NAME;

    上面的情况索引生效,如果存在下面的索引:

    如果加上 Index(name, age)复合索引

    则下面的查询索引失效

    SELECT * FROM USER ORDER BY AGE; -- 失效SELECT * FROM USER WHERE AGE = 18 AND NAME = 'ZHANGSANG' -- 失效

    必须按照下面的排序,则不会索引失效

    SELECT * FROM USER ORDER BY NAME, AGE;

    为什么会失效呢,比如这个复合索引像一个楼梯。第一层使用 NAME,第二层使用 AGE,则必须先上一层,才能上二层

    替换索引为 index(age, name),sql 改为如下方式:

    SELECT * FROM USER WHERE AGE > 18 AND NAME = 'ZHANGSANG' -- 前面的范围索引有效,后半部分索引失效

    展开全文
  • 在了解了索引的基础知识及B+树索引的原理后(如需复习请点这里),这一节我们了解一下有哪些建索引的原则,来指导我们去建索引建索引的原则1. 联合索引我们可能听一些数据库方面的专业人士说过:“把 Where 条件里面...

    在了解了索引的基础知识及B+树索引的原理后(如需复习请点这里),这一节我们了解一下有哪些建索引的原则,来指导我们去建索引。

    建索引的原则

    1. 联合索引

    我们可能听一些数据库方面的专业人士说过:“把 Where 条件里面的列都建上索引”,从而给每个列给每个列建独立的索引,这个理解是非常错误的。

    如果 Where 条件里有多种组合的查询条件,可以尝试建联合索引来减少索引数量,同时提升查询性能。

    2. 覆盖索引

    普通索引查到主键后,回到主键索引搜索的过程,称为回表。

    当使用普通索引查询时,普通索引有我们所需结果的所有信息(字段),就可以直接提供查询结果,而不需要回表。也就是说,在这个查询里,索引已经被“覆盖了”查询需求,称为覆盖索引。

    mysql> create table T (

    ID int primary key,

    k int NOT NULL DEFAULT 0,

    s varchar(16) NOT NULL DEFAULT '',

    index k(k))

    engine=InnoDB;

    select ID from T where k between 3 and 5

    上面的例子中,k索引树上已经包括了ID的值,就不需要回表了。

    由于覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段。

    在使用覆盖索引时,要注意如果需要返回的字段较多,就要权衡空间和时间。因为覆盖索引要覆盖较多字段就需要更多的空间。

    3. 最左前缀原则

    假设我们有2个字段,例如身份证号和地址,现在有三种查询需求:按身份证号查询、按地址查询,以及按身份证号和地址查询。

    如果每种需要都要创建一个索引,就显得有利浪费。

    从上一篇文章里,我们了解到B+树的特点是有序的,因此我们可以只创建两个索引即满足上面的三种需求,分别是(身份证号,地址)和(地址)。这就是B+树索引的“最左前缀”原则。

    在建立联合索引的时候,如何安排索引内的字段顺序:

    第一原则是,如果通过调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用的。

    第二原则是,空间。当两个字段存在一个明显比另一个大时,例如name和age,明显name要比age要大,此时应该建一个(name,age)的联合索引和一个(age)的单字段索引。

    4. 前缀索引和索引选择性

    有时候需要索引很长的字符列,这会让索引变得大且慢。一种策略是模拟哈希索引。

    通常可以索引开始的部分字符,这样可以大大节约索引空间,从而提高索引效率。但这样也会降低索引的选择性。

    索引的选择性指,不重复的索引值(也称为基数,cardinality)和数据表的记录总数(T)的比值,范围从1/T到1之间。

    如何确定前缀索引的长度及创建前缀索引

    假设有个订单表,其中有包括城市名称的字段(city,且城市名称为英文),下面来介绍一下如何确定前缀索引的长度。

    mysql> create table orders (

    ID int primary key,

    city varchar(16) NOT NULL

    )engine=InnoDB;

    一种方法是计算完整列的选择性,并使前缀的选择性接近于完整列的选择性。

    计算完整列的选择性:

    mysql> SELECT COUNT(DISTINCT city)/COUNT(*) FROM orders;

    在同一个查询中计算不同前缀长度的选择性:

    mysql> SELECT COUNT(DISTINCT LEFT(city, 3))/COUNT(*) AS sel3,

    COUNT(DISTINCT LEFT(city, 4))/COUNT(*) AS sel4,

    COUNT(DISTINCT LEFT(city, 5))/COUNT(*) AS sel5,

    COUNT(DISTINCT LEFT(city, 6))/COUNT(*) AS sel6,

    COUNT(DISTINCT LEFT(city, 7))/COUNT(*) AS sel7

    FROM orders;

    创建前缀索引的方法:

    mysql> ALTER TABLE orders ADD KEY (city(7));

    5. 选择合适的索引顺序

    在联合索引中,索引列的顺序是按照从左到右逐列进行排序的。因此索引可以按照升序或降序进行扫描,以满足精确符合列顺序的 ORDERY BY、GROUP BY 和 DISTINCT 等子句的查询需求。

    因此联合索引的列顺序很重要。

    如何选择索引的列顺序有一个经验法则:将选择性最高的列放到索引最前列。

    这个经验法则在某些场景下有用,但可能在另外场景下,可能就没效了,这要根据具体情况进行分析。

    参考资料

    展开全文
  • Mysql什么时候建索引、什么时候不适合建索引 哪些情况需要创建索引 主键自动建立唯 一 索引 频繁作为查询条件的字段应该创建索引 查询中与其它表关联的字段,外键关系建立索引 单键和组和索引的选择问题,who? 在高...

    Mysql什么时候建索引、什么时候不适合建索引

    脑图:
    在这里插入图片描述

    哪些情况需要创建索引

    • 主键自动建立唯 一 索引
    • 频繁作为查询条件的字段应该创建索引
    • 查询中与其它表关联的字段,外键关系建立索引
    • 单键和组和索引的选择问题,who? 在高并发下优先组合索引
    • 查询中排序的字段,排序字段若通过索引访问将大大提高排序速度
    • 查询中统计或者分组字段

    哪些情况下不要建立索引

    表记录太少

    Why:提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE.

    经常增删改的表

    Why:因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件数据重复且分布平均的表字段,因此应该只为最经常查询和最经常排序的数据列建立索引。

    注意,如果某个数据列包含许多重复的内容,为它建立索弓|就没有太大的实际效果。

    哪些形况下索引失效

    1. 查询条件使用函数在索引列上,或者对索引列进行运算,运算包括(+,-,*,/,! 等)
    2. 对小表查询
    3. 隐式转换导致索引失效
    4. 字符型字段为数字时在where条件里不添加引号
    5. 对索引列进行运算.需要建立函数索引.
    6. not in ,not exist,or
    7. 随着表的增长,where条件出来的数据太多,大于15%,使得索引失效(CBO计算走索引花费大于走全表)
    8. 如果MySQL估计使用索引比全表扫描更慢,则不使用索引
    展开全文
  • 适合建索引 1)主键自动建立唯一索引 2)频繁作为查询条件的字段 3)查询中与其他表关联的字段,外键关系建立索引 4)单键/组合索引的选择问题,组合索引性价比更高 5)查询中排序的字段,排序字段若通过索引去...
  • Oracle建索引时,我们为了建索引快,会加上并行,加上并行之后,此列索引就会是并行了。访问有并行度的索引时,CBO可能可能会考虑Oracle建索引时,我们为了建索引快,会加上并行,加上并行之后,此列索引就会是并行...
  • 需要建立索引的情况 ... 对于数据量很大或者很小的列如text, image和bit不适合建索引; 修改的性能大于检索的性能时不适合建索引。 索引失效的情况 组合索引中不能有列为null,如果存在则该列索...
  • 数据库建立索引常用的规则如下: 1、表的主键、外键必须有索引; 2、数据量超过300的表应该有...6、索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引; 7、复合索引的建立需要进行仔细分析;...
  • 数据库建立索引常用的规则如下:1、表的主键、外键必须有...6、索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引;7、复合索引的建立需要进行仔细分析;尽量考虑用单字段索引代替:A、正确选择复...
  • 数据库建立索引常用的规则如下: 1、表的主键、外键必须有索引; ...6、索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引; 7、复合索引的建立需要进行仔细分析;尽量考虑用...
  • ES 建索引

    2020-04-29 13:54:07
    ES 建动态模板索引,设置String 类型写入时,在ES里面设置索引类型为keyword ,避免提前建索引。 PUT /index_name { "settings": { "index": { "number_of_shards": "5", "refresh_interval": "5s", "number_...
  • mongodb建索引

    2019-05-29 15:10:25
    7839 2578条 建索引耗时467.5s,4核8G db.channelTradeLog.createIndex( { "userId" : 1 }, { "name" : "userId_", "background" : true }) 在MongoDB上建索引可能会对MongoDB集群对可用性产生负面影响。在...
  • 一、哪些情况下适合建索引  1. 频繁作为where条件语句查询的字段  2. 关联字段需要建立索引,例如外键字段,student表中的classid, classes表中的schoolid 等  3. 排序字段可以建立索引  4. 分组字段可以...
  • 数据库建立索引常用的规则如下:1、表的主键、外键必须有...6、索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引;7、复合索引的建立需要进行仔细分析;尽量考虑用单字段索引代替:A、正确选择复...
  • 1.先建索引后插数据 2.先插数据后建索引 结论:同为四百万级数据,先建索引后插数据共用1316s 先插数据后建索引共用330+233=563s 先后试了几次数据量越大两者差距越大 ...
  • 一、哪些情况下适合建索引  1. 频繁作为where条件语句查询的字段  2. 关联字段需要建立索引,例如外键字段,student表中的classid, classes表中的schoolid 等  3. 排序字段可以建立索引  4. 分组字段可以...
  • 索引使用经验:什么情况适合建索引 转载 适合建索引?不适合建索引? 数据库建立索引常用的规则如下: 1、表的主键、外键必须有索引; 2、数据量超过300的表应该有索引; 3、经常与其他表进行连接的表,在连接字段...
  • 哪些情况适合建索引

    2020-08-26 21:16:49
    哪些情况适合建索引? 主键自动建立唯一索引。 频繁作为查询条件的字段应该创建索引。 3.查询中与其他表关联的字段,外键关系建立索引。 单键/组合索引的选择问题,who?(在高并发下倾向创建组合索引)。 查询中...
  • MYSQL 索引原理及建索引原则剖析索引类型非聚集索引:像字典一样最前面的几页索引,或者书的目录聚集索引:像书的页码下面的数字Innodb 表存储引擎(聚集索引)索引保存的类型:索引列的值指向数据行的指针Innodb 中...
  • 我感觉无论先建还是后建索引,当有数据时都需要update索引数据,问题是有索引的情况下插数据与有数据的情况下建立索引,各自的消耗。实验:100w记录,1、先创建表和索引,再插入数据,大约1.3min。2、...
  • Only applies to CREATE INDEX statement.Not used for subsequent inserts(先建索引,再插入数据(不受pctfree的影响)。与先插入数据,再建索引(受pctfree的影响)。这两种情 况对空间使用完全不同)例如:先插入数据...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 10,453
精华内容 4,181
关键字:

建索引