精华内容
下载资源
问答
  • 最近一段时间一直和MySQL数据库打交道,由于数据库的海量数据(数据大小达到5G多),进行模糊查找的时候使用like %xxx%,速度十分慢,影响用户体验,给数据库添加索引的话,又因为在MySQL中使用like %xxx%,索引就会...

    最近一段时间一直和MySQL数据库打交道,由于数据库的海量数据(数据大小达到5G多),进行模糊查找的时候使用like %xxx%,速度十分慢,影响用户体验,给数据库添加索引的话,又因为在MySQL中使用like %xxx%,索引就会失效,不得已,通过在internet上海找资料,最后总结了一套方法。

    首先,几点说明

    1、修改数据表的引擎格式为MyISAM

    命令行为: alter table表名 type=MyISAM

    2、mysql的全文索引只能用在char、varchar、text字段上

    3、对于大数据量,应该先加载数据,然后再添加全文索引

    命令行为:altertable 表名add fulltext index索引名(字段名)

    4、配置文件

    Windos下:在my.ini[mysqld]后面添加ft_min_word_len= 1

    Linux下:在my.cof[mysqld]后面添加ft_min_word_len=1            

    5、重新启动mysql服务器

    停止命令行:net stop mysql,启动命令行:net start mysql             

    6、重新设置全文索引

    命令行:REPAIR TABLE表名 QUICK;

    7、使用方法

    命令行: select * from表名 where MATCH(字段名) AGAINST(‘关键字’ in boolean mode);

      至此,我们就完成了全文索引的建立和使用,结果如何呢,我针对我的数据库做了一个对比,我的数据库里面全是视频文件,按照关键字搜索,使用全文检索前后的耗时对比如下表:

     

    关键字

    全文检索使用前耗时(单位:)

    全文检索使用后耗时(单位:)

    康佳

    34

    0.4

    哈工大

    38

    0.7

    倚天屠龙记

    40

    4.4

    喜羊羊

    30

    0.2

    赵薇

    41

    4.3

    赵本山

    11

    3.7

    乡村爱情故事

    23

    0.7

    北京爱情故事

    37

    1.8

    大秦帝国

    37

    0.8

    让子弹飞

    36

    2.8

    龙门飞甲

    39

    2.4

    暮光之城

    45

    1.6

    功夫熊猫

    40

    2.3

    英雄

    12

    4.7

    金刚

    17

    0.8

    济公

    41

    1.1

    白蛇传说

    41

    0.8

    甄嬛传

    41

    1.6

    北京青年

    41

    1.4

    爱情公寓

    47

    2.2

    笑傲江湖

    39

    4.2

    步步惊心

    41

    5.2

    武林外传

    41

    1.8

     通过上面的对比,可以看到,检索速度提高了10-40倍。

     

    展开全文
  • B-树数据库索引为什么要使用树结构存储呢?这个还不简单,树的查询效率高,而且可以保持有序。既然这样,为什么索引没有使用二叉查找树来实现呢?这就不明白了,明明二叉查找树时间复杂度是O(logN),性能已经足够高...

    B-树

    数据库索引为什么要使用树结构存储呢?

    这个还不简单,树的查询效率高,而且可以保持有序。

    既然这样,为什么索引没有使用二叉查找树来实现呢?

    这就不明白了,明明二叉查找树时间复杂度是O(logN),性能已经足够高了,难道B树可以比它更快?

    其实从算法逻辑来讲,二叉树查找树的查找速度和比较次数都是最小的,但是我们不得不考虑一个现实问题:磁盘IO ;数据索引是存储在磁盘上的,当数据量比较大的时候,索引的大小可能有几个G甚至更多。

    当我们利用索引查询的时候,能把整个索引全部加载到内存吗?显然不可能。能做的只有逐一加载每个磁盘页,这里的磁盘页对应索引树的节点。

    在二叉查找树里,磁盘的IO次数等于索引树的高度。

    既然如此,为了减少磁盘的IO次数,我们就需要把原本“瘦高”的树结构变成“矮胖”,这是B-树的特征之一。

    B-树是一种多路平衡查找树,它的每一个节点最多包含k个孩子,k被称为B树的阶。

    k的大小取决于磁盘页的大小。

    下面来具体介绍以下B-树(Balance Tree),一个m阶的B树具有如下几个特征:

    根节点至少有两个子女;

    每个中间节点都包含k-1个元素和k个孩子,其中m/2<=k<=m;

    每一个叶子节点都包含k-1个元素,其中m/2<=k<=m;

    所有叶子节点都位于同一层;

    每个节点中的元素从小到大排列,节点当中k-1个元素正好是k个孩子包含的元素的值域划分。

    下面以3阶的B-树为例,来看看B-树的具体结构。

    【 9 】

    / \

    / \

    【2 6】 【12】

    / | \ / \

    / | \ / \

    / | \ / \

    1 【3 5】 8 11 【13 15】

    这棵树中,重点看【2 6】节点,该节点有两个元素2和6,又有三个孩子1,【3 5】和8。

    其中1小于元素2,6之间,8大于【3 5】,正好符合刚刚所列的几条特征。

    演示下B-树的查询过程,假如我们要查询数值为5的节点。

    第一次IO,查到 9,比较9

    第二次IO,查到 【2 6】 比较2,比较6

    第三次IO,查到 【3 5】 比较3, 比较5

    虽然 比较次数 并不比二叉查找树少,尤其当单一节点中的元素数量很多时。

    但是相比磁盘IO的速度,内存中 比较的操作 耗时几乎可以忽略,所以只要树的高度足够低,IO次数足够少,就可以提高查找性能。

    相比之下节点内部元素多一些并没关系,最多就是几次内存交换操作而已,只要不超过磁盘页的大小,这就是B-树的优势之一。

    但B-树,插入新节点的过程就比较复杂,而且分成很多种情况。所以我们举个典型的例子,上图中插入4.

    自顶下查找4的节点位置,发现4应该插入【3 5】之间

    节点【3 5】已经是两个元素节点,根据特征2要求,无法再增加了。父节点【2 6】也是两元素节点,也无法再增加,根节点9是单元素节点,可以升级为两元素节点。于是拆分节点【3 5】和节点【2 6】,让根节点升级为两元素节点【4 9】,节点6独立成为根节点的第二个孩子。

    【 4 9 】

    / | \

    / | \

    2 6 【12】

    / | / \ / \

    / | / \ / \

    / | / \ / \

    1 3 5 8 11 【13 15】

    虽然维护树结构麻烦,但也正因为如此,让B-树能够始终维持多路平衡。这就是B-树的一大优势:自平衡。

    下面在举例说说B-树的删除,继续上一颗树,我们要删除元素11

    自顶向下查找元素11的节点位置。

    删除11后,节点12只有一个孩子,不符合特征1和5 (不好意思,我猜的,原文就直说不符合B树规范)。

    因此找出12,13,15这个节点的中位数,取代节点12,而节点12自身下已成为第一个孩子。(此过程为左旋)

    【 4 9 】

    / | \

    / | \

    2 6 13

    / | / \ / \

    / | / \ / \

    / | / \ / \

    1 3 5 8 12 15

    虽然B-树的插入和删除,很复杂,没看懂也没关系,关键是理解B-树的核心思想:B-树主要应用于文件系统以及部分数据库索引,比如著名的非关系型数据库MongoDB。

    不过大部分关系型数据库,比如MySql,则使用B+树作为索引。

    很多文档里,有时写B-树,有些写B树,但都是指balance tree,而不是balance binary tree

    B+树

    B+树是基于B-树的一种变体,有着比B-树更高的查询性能。

    一个m阶的B+树具有如下几个特征:

    有k个子树的中间节点包含k个元素(B-树中是k-1个元素),每个元素不保存数据,只用来索引,所有数据都保存在叶子节点。

    所有叶子节点中包含了全部元素的信息,及指向含这些元素记录的指针,且叶子节点本身 按关键字的大小自小二大的顺序链接。

    所有的中间节点元素都同时存在于子节点,在子节点中是最大(或最小)元素。

    【 8 15 】

    / \

    / \

    【2 5 8】 【11 15】

    / | \ / \

    / | \ / \

    / | \ / \

    【1 2】->【3 5】->【6 8】->【9 11】--->【13 15】

    在上面棵B+树中,根节点元素8是子节点【2 5 8】的最大元素,也是叶子节点【6 8】的最大元素

    根节点元素15也是子节点【11 15】的最大元素,也是叶子节点的【13 15】的最大元素

    需要注意的是,根节点的最大元素(这里是15),也就等同于整个B+树的最大元素。以后无论插入删除多少元素,始终要保持最大元素的根节点当中。

    至于叶子节点,由于父节点的元素出现在子节点,因此所有叶子节点包含了全量元素信息。

    并且每一个叶子节点都带有指向下一个节点的指针,形成了一个有序链表。

    B+树还有一个特点,这个特点是在索引之外,确实是至关重要的特点,那就是【卫星数据】的位置。

    所谓卫星数据,指的是索引元素所指向的数据记录,比如数据库中的某一行。在B-树中,无论中间节点还是叶子节点都带有卫星数据。

    而在B+树中,只有叶子节点带有卫星数据,其余中间节点仅仅是索引,没有任何数据关联。

    需要补充的是,在数据库的聚集索引(Clustered Index)中,叶子节点直接包含卫星数据。在非聚集索引(NonClustered Index)中,叶子节点带有指向卫星数据的指针。

    B+树的好处主要体现在查询性能上:

    在单元素查询的时候,B+树会自顶向下逐层查找节点,最终找到匹配的叶子节点。 这跟B-树不一样的两点是,首先,B+树的中间节点没有卫星数据,所以同样大小的磁盘页可以容纳更多的节点元素。这意味,数据量相同的情况下,B+树的结果比B-树更加”矮胖“,因此查询时IO次数也更少。第二,B+树的查询必须最终查找到叶子节点,而B-树只要找到匹配元素即可,无论匹配元素处于中间节点还是在叶子节点,因此,B-树是查找性能并不稳定(最好情况是只查根节点,最坏情况是查到叶子节点)。而B+树的每一次查找都是稳定的。

    在范围查询的时候,B-树只能依靠繁琐的中序遍历。而B+树,很简单,只需要在链表上做遍历即可。

    综合起来,B+树相比B-树的优势有三个:

    IO次数更少;

    查询性能稳定;

    范围查询简便。

    Mysql的阶树计算

    我们可以来算一笔账,以InnoDB存储引擎中默认每个页的大小为16KB来计算,假设以int型的ID作为索引关键字,那么 一个int占用4byte,由上图可以知道还有其他的除主键以外的数据,姑且页当成4byte,那么这里就是8byte,那么16KB=161024byte,那么我们在这种场景下,可以定义这个B-Tree的阶树为 (161024)/8=2048.那么这个树将会有2048-1路,也就是原来平衡二叉树(两路)的1024倍左右,从而大大提高了查找效率与降低IO读写次数。

    Mysql Innodb数据页

    看来无论是这里的页,还是操作系统内存的页page的概念,都是类似c语言的structure结构体的概念。

    B+树索引的分裂优化

    在4阶的B+树中(为了图好看直观,*代表是页面的可用空间)

    【1 3 * *】

    / \

    / \

    / \

    【1 2 * *】->【3 4 5 6】

    插入记录7时,由于叶节点的页面(下文简称叶页面)中只能存放4条记录,插入记录7时,会导致叶页面分裂,产生一个新的叶页面。

    【1 3 5 *】

    / | \

    / | \

    / | \

    / | \

    【1 2 * *】->【3 4 * *】->【5 6 7 *】

    传统B+树页面裂变操作及分析:

    按照原页面中50%的数据量进行分裂,针对当前这个分裂操作,3,4记录保留在原有页面,5,6记录移动到新的页面。最后将新记录7插入到新的页面中;

    50%分裂策略的优势:

    分裂之后,亮哥页面的空间利用率是一样的;如果新的插入是随机在两个页面中挑选进行,那么下一个分裂的操作就会更晚触发。

    50%分裂策略的劣势:

    空间利用率不高:按照传统50%的页面分裂策略,索引页面的空间利用率在50%左右;

    分裂频率较大:针对如上所示的递增插入(递减插入),每新插入两条记录,就会导致最后的叶页面在此发送分裂。

    由于传统50%分裂的策略有不足之处。因此,针对B+树索引的递增/递减插入进行了优化(目前所有的关系型数据库,包括Oracle/InnoDB/PostgreSQL)。经过优化,上述B+树索引,在记录6插入完毕,记录7插入引起分裂之后,新的B+树结构如下:

    【1 3 5 *】

    / | \

    / | \

    / | \

    / | \

    【1 2 * *】->【3 4 5 6】->【7 * * *】

    对比上下两个插入记录7之后,B+树索引的结构图,可以发现二者有很多不同之处:

    新的分裂策略,在插入7时,不移动原有页面的任何记录,只是将新插入的记录7写到新的页面中。

    原有页面的利用率,仍旧是100%;

    优化分裂策略的优势:

    索引分裂的代价小:不需要移动记录;

    索引分裂的概率降低:如果接下来的插入,仍旧是递增插入,那么需要插入4条记录,才能再次引起页面的分裂。50%分裂策略,分裂的概念降低了一半。

    索引页面的空间利用率提高:新的分裂策略,能够保证分裂前的页面,仍旧保持100%的利用率,提高索引的空间利用率。

    优化分裂策略的优势:

    如果新的插入,不再满足递增插入的条件,而是插入到原有页面,那么就会导致原有页面在此分裂,增加分裂的概率。

    因此,此分裂的优化策略,仅仅是针对递增递减插入有效,针对随机插入,就是去了优化的意义,反而带来更高的分裂概率。

    在InnoDB的实现中,为每个索引页面维护了一个上次插入的位置,以及上次的插入是递增/递减的标识。根据这些信息,InnoDB能够判断出新插入的页面中的记录,是否仍旧满足递增/递减的约束,若满足约束,则采用优化后的分裂策略;若不满足越苏,则退回到50%的分类策略。这里提下,递增和递减,指的是趋势,所以Snowflake算法生成的序列是满足递增的约束。

    展开全文
  • 数据库设计规范

    2018-10-30 19:28:40
    1)从数据检索速度来说,字段越少越好,但是太少,很多情况下,势必要使用表连接; 2)字段太多则会影响数据的检索速度,超过50个字段需要分析评审; 3)最低要求是,表中的任意一条记录的数据总和不能大于一个数据...

    6.1 数据表
    设计数据库表时,表上的字段数量应该合适。可以从以下几个方面考虑:
    1)从数据检索速度来说,字段越少越好,但是太少,很多情况下,势必要使用表连接;
    2)字段太多则会影响数据的检索速度,超过50个字段需要分析评审;
    3)最低要求是,表中的任意一条记录的数据总和不能大于一个数据块的大小。可使用如下SQL进行检查:select t.table_name,sum(t.data_length) from user_tab_cols t group by t.table_name having sum(t.data_length) > 8192
    4)字段建议设置默认值,以便更好的使用索引。
    6.2 包
    相同模块的多个函数或存储过程可以考虑放到一个包中;
    多个存在关联的函数或存储过程可以考虑放到一个包中;
    包注释中应注明所属项目组及责任人。
    6.3 存储过程
    存储过程格式严格按照以下格式编写:
    Create or replace procedure 存储过程名 [(输入输出变量列表)]
    As
    [内部变量列表]
    Begin
    存储过程内容;
    End 存储过程名;
    输入参数以“i_开头,输出参数以“o_”开头,输入输出参数用io_开头;
    存储过程中引用数据库对象时,不得含有schema信息;
    存储过程调用其他存储过程或函数的层级不能超过3级,如果超过3层一定要有对应的设计文档,并在注释中写明配置库存放路径,以避免阅读困难;
    存储过程中的DML操作尽量加上事务控制语句commit或者rollback;
    存储过程中尽量不要用游标,如果一定要用,尽可能使用显示游标,避免使用隐式游标;
    重复调用的业务规则、处理逻辑,应封装到单独的函数或存储过程中,以便于维护;
    所有存储过程必须在结束前统一捕获系统异常,并将异常记录到日志表中。
    6.4 函数
    函数格式严格按照以下格式编写:
    Create or replace function 函数名 [(输入变量列表)] return 返回变量类型
    As
    [内部变量列表]
    Begin
    函数内容;
    End 函数名;
    函数中引用数据库对象时,不得含有schema信息;
    函数调用其他存储过程或函数的层级不能超过3级,如果超过3层一定要有对应的设计文档,并在注释中写明配置库存放路径,以避免阅读困难;
    一般情况下函数中不使用事务,如果要使用必须加上事务控制语句commit或者rollback;
    函数中尽量不要用游标,如果一定要用,尽可能使用显示游标,避免使用隐式游标;
    重复调用的业务规则、处理逻辑,应封装到单独的函数或存储过程中,以便于维护;
    所有函数必须在结束前统一捕获系统异常。
    6.5 约束
    数据库约束主要是用来维护数据的完整性,在数据的增删改前做检查,这个检查可能会带来一部分性能损耗。可以从以下方面考虑:
    1) 每个表必须有主键。
    2) 如果对数据完整性要求不是很高,可以不使用外键约束,建议使用代码控制;
    6.6 触发器
    设计表触发器时要考虑以下两点:
    1) 要避免链式触发,那样很容易产生性能灾难;
    2) 触发器的个数要适中,关于触发器对性能的影响参见附件《触发器测试说明》。
    6.7 定时器
    定时器尽量安排在业务闲暇时段执行,操作大数据量的定时器严禁在业务繁忙时段启动。
    需要在业务繁忙时段执行的定时器需要满足处理事务量小、执行时间短等特点。
    定时器应注明所属项目组及责任人。
    开发组提交给技术管理组审核,审核后由配置组专人管理。

    6.8 索引
    数据库表上的索引可以加快数据检索的速度,但同时会降低数据增删改的性能。所以需要在权衡利弊后建立正确的索引。可以从以下两个方面考虑:
    1) 如果增删改频率很高,那么索引个数不宜多;
    2) 如果增删改的频率低,而查询的要求多样,可以考虑创建更多合适的索引。
    6.9 分区
    在设计时,预计到数据量比较大的表,可以考虑使用分区的方法来提高性能。
    分区表的分区字段的确立要根据业务情况来判定,只有在业务中频繁使用的字段才可以考虑作为分区字段。比如:时间字段常用来做范围分区表,地市局代码常用来做列表分区表等。
    分区需求要进行评审,参与人:DBA、技术管理组、开发组、配置管理组。
    6.10 分表
    当一个表中的数据使用频繁度存在非常明显的差异时,可以考虑分表,将使用频繁度低的数据转储到其他表中,只保留使用频繁度高的数据。
    一张业务表随着业务的增长,数据量会越来越大。设计时,应该考虑到这些数据量的增长,对于大数据量的情况,应考虑分表。可以从以下几个方面考虑:
    1) 将适时数据和历史数据分成不同的表;
    2) 适时数据参与业务流程,历史数据只做统计分析;
    3) 根据数据量以及查询的方便,确定分表的时间粒度。

    展开全文
  • 数据库索引

    2018-09-15 10:43:00
    优点:加快检索速度、唯一索引保证每条数据唯一性等等,对生产系统的性能有质的提升; 缺点:索引会大大增加表记录的DML开销; 拓展:索引对DML(INSERT,UPDATE,DELETE)附加的开销有多少? 这个没有固定的比例,与...

    索引

    创建并使用正确的索引【减少数据访问】

    优点:加快检索速度、唯一索引保证每条数据唯一性等等,对生产系统的性能有质的提升;

    缺点:索引会大大增加表记录的DML开销;

    拓展:索引对DML(INSERT,UPDATE,DELETE)附加的开销有多少?

    这个没有固定的比例,与每个表记录的大小及索引字段大小密切相关,以下是一个普通表测试数据,仅供参考:

    索引对于Insert性能降低56%

    索引对于Update性能降低47%

    索引对于Delete性能降低29%

    因此对于写IO压力比较大的系统,表的索引需要仔细评估必要性,另外索引也会占用一定的存储空间。

    正确的索引可以让性能提升100,1000倍以上,不合理的索引也可能会让性能下降100倍,因此在一个表中创建什么样的索引需要平衡各种业务需求。

    类别

    主键索引(聚集索引)、唯一索引、普通索引、组合索引、全文索引

    主键索引(聚集索引):primary key

    例子:

    ALTER TABLE `test_a` ADD INDEX `primary_key` (`id` ASC);

    唯一索引:unique index

    例子:

    create unique index name_season_unique on test_a(name, season);

    或者:

    ALTER TABLE `test_a` ADD UNIQUE INDEX `name_season_unique` (`name` ASC, `season` ASC);

    建议:尽量不用UNIQUE,由程序保证约束

    普通索引:index

    常用于

    • 搜索时经常使用到的字段
    • 用于连接其它表的字段
    • 用于外键字段
    • 高选中性的字段
    • order by子句中使用到的字段
    • xml类型

    不适用于:
    唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件,例如性别
    更新非常频繁的字段不适合创建索引,例如登录次数,最后登录时间
    不会出现在 where 子句中的字段不该创建索引
    例子:

    create index name_index on test_a(name);

    或者:

    ALTER TABLE `test_a` ADD INDEX `name_index` (`name` ASC);

    组合索引(也叫复合索引、多列索引)

    类似于普通索引,只是索引是放在多个列名上,例如字典表中部首+笔画的组合索引

    create index name_no_index on test_a(name, season);

    全文索引:fulltext index

    create fulltext index name_fulltext_index on test_a(name);
    -- 创建用于展示效果的数据
    INSERT INTO `test_a` (`id`, `name`, `season`) VALUES ('6', '不知水果', '不知季度');
    -- 展示效果
    select match(name) against('不知水果') from test_a where id in(1, 6);
    drop index name_fulltext_index on test_a;

    0表示没有匹配到,或者你的词是停止词,是不会建立索引的.
    使用全文索引,不能使用like语句,如果使用like这样就不会使用到全文索引了

    在建表的时候建立唯一键、普通索引、唯一索引的方式如下:

    PRIMARY KEY (`id`),
    UNIQUE KEY `name_season_unique` (`name`,`season`),
    KEY `name_index` (`name`)

    拓展:
    删除索引

    DROP INDEX index_name ON table_name;

    或者:

    ALTER TABLE `table_name` DROP INDEX `index_name`;

    查看索引:

    show index from table_name;

    修改索引
    我们一般是先删除再重新创建,也可以直接进行修改

    索引注意点

    • 索引并不是越多越好,要根据查询有针对性的创建,考虑在WHERE和ORDER BY命令上涉及的列建立索引,可根据EXPLAIN来查看是否用了索引还是全表扫描
    • 应尽量避免在WHERE子句中对字段进行NULL值判断,否则将导致引擎放弃使用索引而进行全表扫描
    • 值分布很稀少的字段不适合建索引,例如”性别”这种只有两三个值的字段
    • 字符字段只建前缀索引
    • 字符字段最好不要做主键
    • 不用外键,由程序保证约束
    • 使用多列索引时主意顺序和查询条件保持一致,同时删除不必要的单列索引
    • 查询要建立索引最重要的条件是查询条件中需要使用索引
    • 如果MySQL估计使用全表扫描要比使用索引快,则不使用索引

    SQL什么条件会使用索引?

    当字段上建有索引时,通常以下情况会使用索引:

    • INDEX_COLUMN = ?
    • INDEX_COLUMN > ?
    • INDEX_COLUMN >= ?
    • INDEX_COLUMN < ?
    • INDEX_COLUMN <= ?
    • INDEX_COLUMN between ? and ?
    • INDEX_COLUMN in (?,?,...,?)
    • INDEX_COLUMN like ?||'%'(后导模糊查询)
    • T1. INDEX_COLUMN=T2. COLUMN1(两个表通过索引字段关联)

    SQL什么条件建了索引但不会使用索引?

    查询条件

    不能使用索引原因

    示例

    INDEX_COLUMN <> ?

    INDEX_COLUMN not in (?,?,...,?)

    不等于操作不能使用索引

     

    function(INDEX_COLUMN) = ?

    INDEX_COLUMN + 1 = ?

    INDEX_COLUMN || 'a' = ?

    经过普通运算或函数运算后的索引字段不能使用索引

     

    INDEX_COLUMN like '%'||?

    INDEX_COLUMN like '%'||?||'%'

    含前导模糊查询的Like语法不能使用索引

    对于使用like的查询,'aaa%' 会使用到索引,查询如果是'%aaa'不会使用到索引。 explain select * from dept where name like '研发部%'; -- 不会使用索引 explain select * from dept where name like '%研发部';

    INDEX_COLUMN is null

    B-TREE索引里不保存字段为NULL值记录,因此IS NULL不能使用索引

     

    NUMBER_INDEX_COLUMN='12345'

    CHAR_INDEX_COLUMN=12345

    在做数值比较时需要将两边的数据转换成同一种数据类型,如果两边数据类型不同时会对字段值隐式转换,相当于加了一层函数处理,所以不能使用索引。

    如果列类型是字符串,那一定要在条件中将数据使用引号引用起来。否则不使用索引。(添加时,字符串必须'') -- 会应用索引 explain select * from dept where name = '研发部'; -- 不会使用索引并且报错 explain select * from dept where name = 研发部;

    a.INDEX_COLUMN=a.COLUMN_1

    给索引查询的值应是已知数据,不能是未知字段值。

     

    对于创建的组合索引,只要查询条件使用了最左边的列,索引一般就会被使用,不是使用的第一部分,则不会使用索引

    组合索引都是从第一个字段开始找起,看第一个条件字段是否是组合索引的第一个字段,如果是则使用索引,否则不使用

    alter table dept add index name_loc (name, loc); explain select * from dept where name = '研发部'; explain select * from dept where name = '研发部' and loc = '大厦10楼1001'; -- 不会使用索引 explain select * from dept where loc = '大厦10楼1001';

    如果条件中有or,即使其中有条件带索引也不会使用。

     

    -- 我们把组合索引删除,然后只在name上加入索引 alter table dept drop index name_loc; alter table dept add index name_index (name); -- 使用了索引 explain select * from dept where name = '研发部'; -- key为NULL explain select * from dept where name = '研发部' or loc = '大厦10楼1001';

    注:

    经过函数运算的字段要使用索引可以使用函数索引,这种需求建议与DBA沟通。

    我们一般在什么字段上建索引?

    这是一个非常复杂的话题,需要对业务及数据充分分析后再能得出结果。主键及外键通常都要有索引,其它需要建索引的字段应满足以下条件:

    1、字段出现在查询条件中,并且查询条件可以使用索引;

    2、语句执行频率高,一天会有几千次以上;

    3、通过字段条件可筛选的记录集很小,那数据筛选比例是多少才适合?

    这个没有固定值,需要根据表数据量来评估,以下是经验公式,可用于快速评估:

    小表(记录数小于10000行的表):筛选比例<10%

    大表:(筛选返回记录数)<(表总记录数*单条记录长度)/10000/16

    单条记录长度≈字段平均内容长度之和+字段数*2

    例如:emp表中有600万的数据,单条记录长度 ≈ (7+6+6+3+8+4+1+10+7+6) + 11 * 2 = 70

    筛选返回记录数就需要小于 6000000 * 70 / 10000 / 16 = 3625 左右即可

    在emp的name字段中添加了索引,查看索引筛选比例如下,351小于预期值3625,说明在这个字段上建立索引是非常合理的

    image

    如何知道SQL是否使用了正确的索引?

    简单SQL可以根据索引使用语法规则判断,复杂的SQL不好办,判断SQL的响应时间是一种策略,但是这会受到数据量、主机负载及缓存等因素的影响,有时数据全在缓存里,可能全表访问的时间比索引访问时间还少。要准确知道索引是否正确使用,需要到数据库中查看SQL真实的执行计划。

    只通过索引访问数据

    有些时候,我们只是访问表中的几个字段,并且字段内容较少,我们可以为这几个字段单独建立一个组合索引,这样就可以直接只通过访问索引就能得到数据,一般索引占用的磁盘空间比表小很多,所以这种方式可以大大减少磁盘IO开销。

    如:select id,name from company where type='2';

    如果这个SQL经常使用,我们可以在type,id,name上创建组合索引

    create index my_comb_index on company(type,id,name);

    有了这个组合索引后,SQL就可以直接通过my_comb_index索引返回数据,不需要访问company表。

    切记,性能优化是无止境的,当性能可以满足需求时即可,不要过度优化。在实际数据库中我们不可能把每个SQL请求的字段都建在索引里,所以这种只通过索引访问数据的方法一般只用于核心应用,也就是那种对核心表访问量最高且查询字段数据量很少的查询。

    优化SQL执行计划

    SQL执行计划是关系型数据库最核心的技术之一,它表示SQL执行时的数据访问算法。由于业务需求越来越复杂,表数据量也越来越大,程序员越来越懒惰,SQL也需要支持非常复杂的业务逻辑,但SQL的性能还需要提高,因此,优秀的关系型数据库除了需要支持复杂的SQL语法及更多函数外,还需要有一套优秀的算法库来提高SQL性能。

    目前ORACLE有SQL执行计划的算法约300种,而且一直在增加,所以SQL执行计划是一个非常复杂的课题,一个普通DBA能掌握50种就很不错了,就算是资深DBA也不可能把每个执行计划的算法描述清楚。虽然有这么多种算法,但并不表示我们无法优化执行计划,因为我们常用的SQL执行计划算法也就十几个,如果一个程序员能把这十几个算法搞清楚,那就掌握了80%的SQL执行计划调优知识。

    查看索引的使用情况

    show status like 'Handler_read%';

     

    大家可以注意:
    handler_read_key:这个值越高越好,越高表示使用索引查询到的次数。
    handler_read_rnd_next:这个值越高,说明查询低效。

    这时我们会看到handler_read_rnd_next值很高,这是因为我们前面没有加索引的时候,做过多次查询的原因.

    定时清除不需要的数据,定时整理索引锁片

    当创建了索引并且索引正常的工作,但性能仍然不好,那就可能是产生了索引碎片,需要进行索引碎片处理。
    optimize table table_name;

    转载于:https://www.cnblogs.com/tufujie/p/9650224.html

    展开全文
  • 应用MySQL数据库

    2017-04-26 16:15:09
    应用MySQL数据库时,并非用户在查询数据的时候总需要应用索引来优化查询,凡事都有两面性,使用索引可以提高检索数据的速度,但是,创建和维护索引需要耗费时间,并且该耗费时间与数据量大小成正比,另外,索引需要...
  • 前言数据库索引本质上是一种数据结构(存储结构+算法),目的是为了加快目标数据检索速度。目录1.索引的本质与原理?2.索引的分类?1.索引的本质与原理我们先看一个问题:假设现在有100000条从0到10000且从大到小...
  • JAVA面试——数据库

    2019-01-08 21:17:00
    基础: 基本存储结构:页(大小16KB) 各个数据页组成双向链表,每个数据页的记录组成单向链表 ...按照数据存放的物理位置为顺序,提高多行检索速度 加快单行检索 聚集索引 & 非聚集索引: ...
  • 数据库常见问题总结

    2020-09-05 12:59:56
    缺点是它减慢了数据录入的速度,同时也增加了数据库的尺寸大小。 标题什么样的字段适合建索引 唯一、不为空、经常被查询的字段 索引类型有哪些? 逻辑上: Single column 单行索引 Concatenated 多行索引 Unique ...
  • 数据库相关面试题

    2019-10-06 21:52:41
    1.索引的作用?和它的优点缺点是什么?...缺点是它减慢了数据录入的速度,同时也增加了数据库的尺寸大小。 2.什么样的字段适合建索引? (1) 经常被查询的字段 (2) 不为空且字段值不重复 (3) 字段的值...
  • MySQL数据库索引解析

    2018-05-12 13:36:15
    MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。 考虑如下...
  • 度地提高了数据库管理系统处理多媒体的能力和速度; 语言集数据库的定义、査询、更新、控制、维护、恢复、安全等一系列操作于 体,每一项操作都只需一种操作符表示,格式规范,风格一致,简单方便,很容易为用户所掌 握...
  • 数据库索引本质上是一种数据结构(存储结构+算法),目的是为了加快目标数据检索速度。 目录 1.索引的本质与原理? 2.索引的分类? 3.福利彩蛋 1.索引的本质与原理 我们先看一个问题: 假设现在有100000条...
  • 我们在创建数据库时,肯定遇到选择数据库引擎的问题,那么INNODB和MYISAM的区别是什么呢?...MYISAM 节约空间,速度较快 INNODB 安全性高,支持事务处理,多表多用户操作 存储空间的异同 相同点 所有的数据库文件都
  • 我环顾了古尔,但没有找到任何好的答案... 对于访问速度,它使用固定大小的列和行,因此访问特定记录比扫描所有文本更容易。 它还可以保留数据索引,以便更快地进行检索。 默认和最常用的引擎是我的isam。 其他包括i...
  • SIFT特征是基于物体上的一些局部外观的兴趣点而与影像的大小和旋转无关。对于光线、噪声、微视角改变的容忍度也相当高。基于这些特性,它们是高度显著而且相对容易撷取,在母数庞大的特征数据库中,很容易辨识物体...
  • X使用的是数据库应用程序,所以,当数据库大小、帖子的数目、会员的数目,这些因素都会影响到程序的检索速度,尤其是当论坛的影响力大了,这个问题就更为突出了,虽然,康盛对Discuz进行了更高的追求,但是,除去...
  • 巧用这五招提升Discuz!论坛运行速度

    千次阅读 2017-07-03 18:28:39
    X使用的是数据库应用程序,所以,当数据库大小、帖子的数目、会员的数目,这些因素都会影响到程序的检索速度,尤其是当论坛的影响力大了,这个问题就更为突出了,虽然,康盛对Discuz进行了更高的追求,但是,除去...
  • Struts+Oracle分页显示数据的实现

    千次阅读 2005-08-02 17:52:00
    其中比较好的分页做法应该是每次翻页的时候只从数据库检索页面大小的块区的数据。这样虽然每次翻页都需要查询数据库,但查询出的记录数很少,网络传输数据量不大,如果使用连接池更可以略过最耗时的建立数据库连接...
  • [mysql] 5.索引

    2021-01-09 16:43:08
    索引可以加快检索速度,但同时也降低索引列插入、删除、更新的速度,索引维护需要代价。 Mysql中的索引类型 普通索引 唯一索引 主键索引 组合索引 全文索引 其具体含义可见: ...
  • 索引的定义:查询方式通常有两种:一种是:全表扫描;一种是:利用数据表上建立的索引进行扫描。这时候就需要索引了!...缺点是它减慢了数据录入的速度,同时也增加了数据库大小。索引的类型:1.普通索引:...
  • 索引就一种特殊的查询表,数据库的搜索引擎可以利用它加速对数据的检索。 它很类似与现实生活中书的目录,不需要查询整本书内容就可以找到想要的...缺点是它减慢了数据录入的速度,同时也增加了数据库的尺寸大小 ...
  • a.索引就一种特殊的查询表,数据库的搜索引擎可以利用它加速对数据的检索。 b.它很类似与现实生活中书的目录,不需要查询整本书内容就可以找到想要...缺点是它减慢了数据录入的速度,同时也增加了数据库的尺寸大小 ...

空空如也

空空如也

1 2 3 4 5 6
收藏数 101
精华内容 40
关键字:

数据库大小检索速度